MySQL建库建表、数据插入

Posted on |
Words count in article: 2.6k | Reading time ≈ 11

连接与断开服务器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[root@docker-study var]#   mysql --host=hostname  --user=user  --password
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 37
Server version: 5.6.43 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

输入查询

  介绍几个输入查询的基本原则来熟悉mysql的工作原理。
  这是一个简单的查询，要求服务器说明它的版本号和当前日期。

1
2
3
4
5
6
7
8
9
mysql>  SELECT VERSION(), CURRENT_DATE;
+-----------+--------------+
| VERSION() | CURRENT_DATE |
+-----------+--------------+
| 5.6.43    | 2019-02-18   |
+-----------+--------------+
1 row in set (0.00 sec)

mysql>

  关键字可以输入任何字母。以下查询是等效的，即不区分大小写：

1
2
3
mysql> SELECT VERSION(), CURRENT_DATE;
mysql> select version(), current_date;
mysql> SeLeCt vErSiOn(), current_DATE;

  这是另一个查询。它演示了您可以将 mysql用作简单的计算器：

1
2
3
4
5
6
7
mysql> SELECT SIN(PI()/4), (4+1)*5;
+------------------+---------+
| SIN(PI()/4)      | (4+1)*5 |
+------------------+---------+
| 0.70710678118655 |      25 |
+------------------+---------+
1 row in set (0.02 sec)

  到目前为止显示的查询是相对较短的单行语句。也可以在一行中输入多个语句。用分号结束每一个：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> SELECT VERSION(); SELECT NOW();
+-----------+
| VERSION() |
+-----------+
| 5.6.43    |
+-----------+
1 row in set (0.00 sec)

+---------------------+
| NOW()               |
+---------------------+
| 2019-02-18 11:01:14 |
+---------------------+
1 row in set (0.00 sec)

  多行的冗长查询不是问题,mysql通过查找终止分号来确定语句的结束位置，而不是查找输入行的结尾。
这是一个简单的多行语句：

1
2
3
4
5
6
7
8
9
10
mysql> select 
    -> user()
    -> ,
    -> current_date;
+----------------+--------------+
| user()         | current_date |
+----------------+--------------+
| web@172.19.0.3 | 2019-02-18   |
+----------------+--------------+
1 row in set (0.00 sec)

  如果决定不想执行正在输入的查询，请键入\c以下命令取消它 ：

1
2
3
4
mysql> SELECT
    -> USER()
    -> \c
mysql>

  在这里，也请注意提示。它会mysql>在键入后切换回来 \c，提供反馈以指示mysql已为新查询做好准备。
  下表显示了可能看到的每个提示，并总结了它们对mysql所处状态的含义 。

提示 含义
mysql> 准备好进行新查询
-> 等待下一行的多行查询
‘> 等待下一行，以单引号（’）开头的字符串
“> 等待下一行，以双引号开头的字符串（”）
`> 等待下一行，以反引号（`）开头的标识符
/*> 等待下一行，以/*开头的描述

  在MySQL中，可以编写由任一字符’或”字符包括的字符串（例如’hello’或”goodbye”),并且 mysql允许输入跨越多行的字符串。看到’>或 “>提示时，表示输入的行包含以a ‘ 或”quote字符开头的字符串，但没有输入终止字符串的匹配引号。这通常表明遗漏了引号字符。例如：

1
2
mysql> SELECT * FROM my_table WHERE name = 'Smith AND age < 30;
    '>

  通过转义字符取消查询，如果只输入\c，会被认为是引号内的内容

1
2
3
mysql> SELECT * FROM my_table WHERE name = 'Smith AND age < 30;
    '> '\c
mysql>

  `>提示类似于 ‘>和”>提示，但表示你已经开始但尚未完成反引号引用的标识符。

创建数据库

1
2
mysql> CREATE DATABASE 16netlab;
Query OK, 1 row affected (0.01 sec)

  在Unix下，数据库名称是区分大小写的（不像SQL关键字）。表名也是如此。（在Windows下，此限制不适用，但必须在给定查询中使用相同的字母表引用数据库和表。但是，出于各种原因，建议的最佳做法始终是使用相同字母。）
注意

  • 如果收到错误 ERROR 1044（42000）：ERROR 1044 (42000): Access denied for user ‘micah‘@’localhost’ to database ‘menagerie’，这意味着用户帐户没有必要的权限。

  要使16netlab为当前数据库，请使用以下语句：

1
2
mysql> use 16netlab;
Database changed

  数据库只需创建一次，但每次开始mysql 会话时都必须选择它才能使用。通过如上所示的语句来完成此操作。或者可以在调用mysql时在命令行上选择数据库。需要提供的所有连接参数之后指定其名称。例如：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[root@docker-study ~]# docker exec -it web mysql -h mysql2 -u web -p 16netlab
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 348
Server version: 5.6.43 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

   SELECT DATABASE() 查看当前选择使用哪个数据库

1
2
3
4
5
6
7
8
9
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| 16netlab   |
+------------+
1 row in set (0.00 sec)

mysql>

创建表

  创建数据库很容易，但此时它是空的

1
2
mysql> SHOW TABLES;
Empty set (0.00 sec)

  更难的部分是决定数据库的结构应该是什么：需要哪些表以及每个表中应该包含哪些列。
  例如一张包含每只宠物记录的表格。这可以称为pet表格，它应该包含每个动物名称以及长度限制值。如果家中有多个人饲养宠物，列出每只动物的主人，记录一些基本的描述性信息，如物种和性别，年龄。随着时间的推移，年龄会发生变化，这意味着必须经常更新记录。相反，最好存储固定值，如出生日期。如果需要年龄，可以将其计算为当前日期和出生日期之间的差异。MySQL提供了进行日期算术的功能。

  你可能会想到在pet表格中有用的其他类型的信息，但到目前为止确定的信息是足够的：名称，所有者，物种，性别，出生和死亡日期。
  使用CREATE TABLE语句指定表的布局：

1
2
3
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
    -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
Query OK, 0 rows affected (0.20 sec)

  VARCHAR数据类型对于name、 owner和species 列是不错的选择，因为列值的长度不同。定义列的长度时根据实际情况合理选择。通常，可以选择1-65535中的任何长度。

  可以选择几种类型的值来表示动物记录中的性别，例如’m’和 ‘f’，或者可能’male’和 ‘female’。最简单的是使用单个字符’m’和’f’。
DATE对于birth和death 列 使用数据类型是一个相当明显的选择。
  创建表后，SHOW TABLES应该产生一些输出：

1
2
3
4
5
6
7
mysql> SHOW TABLES;
+--------------------+
| Tables_in_16netlab |
+--------------------+
| pet                |
+--------------------+
1 row in set (0.00 sec)

  要验证表是否按预期方式创建，使用DESCRIBE语句：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> describe pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql>

添加数据到表

  创建表后需要填充它。使用LOAD DATA和 INSERT语句
  假设宠物记录可以如此处所示。

名字 主人 种类 性别 出生日期 死亡日期
Fluffy Harold cat f 1993-02-04
Claws Gwen cat m 1994-03-17
Buffy Harold dog f 1989-05-13
Fang Benny dog m 1990-08-27
Bowser Diane dog m 1979-08-31 1995-07-29
Chirpy Gwen bird f 1998-09-11
Whistler Gwen bird 1997-12-09
Slim Benny snake m 1996-04-29

  因为从空表开始，所以填充它的一种简单方法是为每个动物创建一个包含行的文本文件，然后使用单个语句将文件内容加载到表中。

  可以创建一个文本文件pet.txt，每行包含一个记录，其值由制表符分隔，并按照CREATE TABLE语句中列出的顺序给出 。对于缺失值（例如未知性别或仍然生活的动物的死亡日期），可以使用NULL 值。要在文本文件中表示这些，请使用 \N（反斜杠，大写-N）。例如，惠斯勒鸟的记录看起来像这样（值之间的空格是单个制表符）：

1
Whistler        Gwen    bird    \N      1997-12-09      \N

  要将文本文件加载pet.txt到 pet表中，请使用以下语句：

1
mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;

  如果需要，可以在LOAD DATA语句中显式指定列值分隔符和行结束标记，但默认值为制表符和换行符。这些语句正确读取pet.txt。

  如果语句失败，则默认情况下MySQL安装可能没有启用本地文件功能。

  如果要一次添加一条新记录，该 INSERT语句很有用。在最简单的形式中，按照CREATE TABLE语句中列出的列的顺序为每列提供值 。假设黛安得到了一只名为“ Puffball ”的仓鼠。可以使用如下INSERT语句添加 ：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
mysql> INSERT INTO pet
       VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
字符串和日期值在此处指定为带引号的字符串。此外，可以直接插入NULL 以表示缺失值。
mysql> INSERT INTO pet
    ->  VALUES ('Fluffy','Harold','cat','f','1999-02-04',NULL);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO pet  VALUES ('Claws','Gwen','cat','m','1994-03-17',NULL);
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO pet  VALUES ('Buffy','Harold','dog','f','1989-05-13',NULL);
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO pet  VALUES ('Fang','Benny','dog','m','1990-08-27',NULL);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO pet  VALUES ('Bowser','Diane','dog','m','1979-08-31','1995-07-29');
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO pet  VALUES ('Chirpy','Genw','bird','f','1998-09-11',null);
Query OK, 1 row affected (0.42 sec)

mysql> SELECT * FROM pet;
+--------+--------+---------+------+------------+------------+
| name   | owner  | species | sex  | birth      | death      |
+--------+--------+---------+------+------------+------------+
| Fluffy | Harold | cat     | f    | 1999-02-04 | NULL       |
| Claws  | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang   | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy | Genw   | bird    | f    | 1998-09-11 | NULL       |
+--------+--------+---------+------+------------+------------+
6 rows in set (0.00 sec)
------ end ------
0%