MySQL进阶

October 12, 2020 19856

表的连接

创建信息表

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
mysql> alter table score add evaluation char(20) default 'normal';
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into score values (1,'zhao',85.25,'excellent'),(2,'qian',75.80,'normal'),(3,'sun',92.40,'excellent'),(4,'li',59.00,'weak');
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> create table info(
    -> name char(20) not null;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2
mysql> create table info(
    -> name char(20) not null,
    -> phone char(20),
    -> address char(20));
Query OK, 0 rows affected (0.08 sec)

mysql> insert into values('zhao',13199999999,'beijing'),('qian','13288888888','harbin'),('sun',13166666666,'shanghai');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'values('zhao',13199999999,'beijing'),('qian','13288888888','harbin'),('sun',1316' at line 1
mysql> insert into info values('zhao',13199999999,'beijing'),('qian','13288888888','harbin'),('sun',13166666666,'shanghai');
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from info;
+------+-------------+----------+
| name | phone       | address  |
+------+-------------+----------+
| zhao | 13199999999 | beijing  |
| qian | 13288888888 | harbin   |
| sun  | 13166666666 | shanghai |
+------+-------------+----------+
3 rows in set (0.00 sec)

等值连接（内连接）

1
2
3
4
5
6
7
8
9
mysql> select score.id,score.name,info.phone from score inner join info on score.name = info.name;
+----+------+-------------+
| id | name | phone       |
+----+------+-------------+
|  1 | zhao | 13199999999 |
|  2 | qian | 13288888888 |
|  3 | sun  | 13166666666 |
+----+------+-------------+
3 rows in set (0.04 sec)

select 表A.字段1,表A.字段2，表B. 字段3 from 表A inner join 表B on 表A.字段2=表B.字段2；

左连接

1
2
3
4
5
6
7
8
9
10
mysql> select score.id,score.name,info.phone from score left join info on score.name = info.name;
+----+------+-------------+
| id | name | phone       |
+----+------+-------------+
|  1 | zhao | 13199999999 |
|  2 | qian | 13288888888 |
|  3 | sun  | 13166666666 |
|  4 | li   | NULL        |
+----+------+-------------+
4 rows in set (0.00 sec)

右连接

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
mysql> insert into info values('li','13255555555','Hongkong'),('jack','123456789','canada'); #为使其效果明显增加其数据
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select score.id,score.name,info.phone from score right join info on score.name = info.name;
+------+------+-------------+
| id   | name | phone       |
+------+------+-------------+
|    1 | zhao | 13199999999 |
|    2 | qian | 13288888888 |
|    3 | sun  | 13166666666 |
|    4 | li   | 13255555555 |
| NULL | NULL | 123456789   |
+------+------+-------------+
5 rows in set (0.00 sec)

mysql> select score.id,info.name,info.phone from score right join info on score.name = info.name;
+------+------+-------------+
| id   | name | phone       |
+------+------+-------------+
|    1 | zhao | 13199999999 |
|    2 | qian | 13288888888 |
|    3 | sun  | 13166666666 |
|    4 | li   | 13255555555 |
| NULL | jack | 123456789   |
+------+------+-------------+
5 rows in set (0.00 sec)

inner join：表A和表B的交集 left join：表A全部 right join：表B全部

索引

普通索引

添加索引

方法一
1
2
3
4
5
6
7
8
9
10
11
12
mysql> create index ind_score on score(name); #对score表中name字段创建一个索引，名为ind_score
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from score; #查看当前索引
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| score |          0 | PRIMARY   |            1 | id          | A         |           4 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| score |          1 | ind_score |            1 | name        | A         |           4 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.05 sec)

格式：create index 索引名 on 表名(字段名);

方法二
1
2
3
4
5
6
7
8
9
10
11
12
mysql> alter table score add index ind_score(name);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from score;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| score |          0 | PRIMARY   |            1 | id          | A         |           4 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| score |          1 | ind_score |            1 | name        | A         |           4 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.05 sec)

添加表时直接创建索引

1
2
3
4
5
mysql> create table index_test(
    -> id int(4) not null,
    -> name char(20) not null,
    -> index ind_test(name));
Query OK, 0 rows affected, 1 warning (0.08 sec)

格式：········index 表名(字段名));

删除索引

方法一

1
2
3
4
5
6
7
8
9
10
11
mysql> drop index ind_score on score;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from score;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| score |          0 | PRIMARY  |            1 | id          | A         |           4 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.03 sec)

格式：drop index 索引名 on 表名;

方法二

1
2
3
4
5
6
7
8
9
10
11
mysql> alter table score drop index ind_score;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from score;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| score |          0 | PRIMARY  |            1 | id          | A         |           4 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.04 sec)

唯一索引

创建唯一索引

方法一
1
2
3
4
5
6
7
8
9
10
11
12
mysql> create unique index a on score(name);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from score;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| score |          0 | PRIMARY  |            1 | id          | A         |           4 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| score |          0 | a        |            1 | name        | A         |           4 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.05 sec

格式：create unique index 索引名 on 表名(字段名)；

方法二
1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> alter table score add unique(name);
Query OK, 0 rows affected, 1 warning (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> show index from score;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| score |          0 | PRIMARY  |            1 | id          | A         |           4 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| score |          0 | a        |            1 | name        | A         |           4 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| score |          0 | name     |            1 | name        | A         |           4 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.04 sec)

格式：alter table 表格名称 add unique(字段名称)；

删除唯一索引

1
2
3
4
5
6
7
8
9
10
11
12
mysql> alter table score drop index name;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from score;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| score |          0 | PRIMARY  |            1 | id          | A         |           4 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| score |          0 | a        |            1 | name        | A         |           4 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.04 sec)

格式：alter table 表格名称 drop index 字段名;

外键

外键：一个表的某个字段指向另个表的主键，称为外键。 主表（父表）：被指向的表 从表（子表）：主键所在表

创建作者表

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> create table author_table(
    -> author_id int(4) not null primary key auto_increment,
    -> author_name char(20) not null);
Query OK, 0 rows affected, 1 warning (0.10 sec)

mysql> desc author_table;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| author_id   | int      | NO   | PRI | NULL    | auto_increment |
| author_name | char(20) | NO   |     | NULL    |                |
+-------------+----------+------+-----+---------+----------------+
2 rows in set (0.05 sec)

创建文章表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> create table article_table(
    -> article_id int(4) not null primary key auto_increment,
    -> article_title char(20) not null,
    -> author_id int(4) not null,
    -> foreign key(author_id) references author_table(author_id)); #设置外键，指向作者表的author_id字段
Query OK, 0 rows affected, 2 warnings (0.08 sec)


mysql> desc article_table;
+---------------+----------+------+-----+---------+----------------+
| Field         | Type     | Null | Key | Default | Extra          |
+---------------+----------+------+-----+---------+----------------+
| article_id    | int      | NO   | PRI | NULL    | auto_increment |
| article_title | char(20) | NO   |     | NULL    |                |
| author_id     | int      | NO   | MUL | NULL    |                |
+---------------+----------+------+-----+---------+----------------+
3 rows in set (0.05 sec)

添加数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> insert into author_table values
    -> (1,'zhao'),
    -> (2,'qian'),
    -> (3,'sun'),
    -> (4,'li');
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into article_table values
    -> (1001,'C++',1),
    -> (1002,'Java',2),
    -> (1003,'Python',3),
    -> (1004,'MySQL',1),
    -> (1005,'JavaScript',4);
Query OK, 5 rows affected (0.04 sec)
Records: 5  Duplicates: 0  Warnings: 0

那是否能添加第五个数据呢？

1
2
mysql> insert into article_table values(1006,'Swift',5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`article_table`, CONSTRAINT `article_table_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `author_table` (`author_id`))

主表中没有id为5的作者，添加一个作者id是5的文章数据是不可以的。同理，我们无法删除父表中作者id为4的文章数据。

查看一个表的外键

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> show create table article_table; #查看表结构
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table         | Create Table                                                                                                                                                                                                                                                                                                                                                                                            |
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| article_table | CREATE TABLE `article_table` (
  `article_id` int NOT NULL AUTO_INCREMENT,
  `article_title` char(20) NOT NULL,
  `author_id` int NOT NULL,
  PRIMARY KEY (`article_id`),
  KEY `author_id` (`author_id`),
  CONSTRAINT `article_table_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `author_table` (`author_id`) 
) ENGINE=InnoDB AUTO_INCREMENT=1006 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

article_table_ibfk_1为外键名

格式：show create table 表名;

删除一个表的外键

1
2
3
mysql> alter table article_table drop foreign key article_table_ibfk_1;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

格式：alter table 表名 drop foreign key 外键名;

给两表设置级联操作

1
2
3
4
5
6
7
mysql> alter table article_table #alter命令添加一个外键
    -> add foreign key fk_id(author_id)
    -> references author_table(author_id)
    -> on delete cascade
    -> on update cascade;
Query OK, 5 rows affected (0.11 sec)
Records: 5  Duplicates: 0  Warnings: 0

cascade 表示关联操作，如果父表中数据被删除或更新，子表同步操作。

set null：子表数据不指向父表任何记录

restrict：拒绝主表的相关操作

删除作者表一条数据

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
35
36
37
38
39
40
41
42
43
44
45
mysql> select * from author_table;
+-----------+-------------+
| author_id | author_name |
+-----------+-------------+
|         1 | zhao        |
|         2 | qian        |
|         3 | sun         |
|         4 | li          |
+-----------+-------------+
4 rows in set (0.05 sec)

mysql> select * from article_table;
+------------+---------------+-----------+
| article_id | article_title | author_id |
+------------+---------------+-----------+
|       1001 | C++           |         1 |
|       1002 | Java          |         2 |
|       1003 | Python        |         3 |
|       1004 | MySQL         |         1 |
|       1005 | JavaScript    |         4 |
+------------+---------------+-----------+
5 rows in set (0.00 sec)

mysql> delete from author_table where author_id = 4;
Query OK, 1 row affected (0.04 sec)

mysql> select * from author_table;
+-----------+-------------+
| author_id | author_name |
+-----------+-------------+
|         1 | zhao        |
|         2 | qian        |
|         3 | sun         |
+-----------+-------------+
3 rows in set (0.00 sec)

mysql> select * from article_table;
+------------+---------------+-----------+
| article_id | article_title | author_id |
+------------+---------------+-----------+
|       1001 | C++           |         1 |
|       1002 | Java          |         2 |
|       1003 | Python        |         3 |
|       1004 | MySQL         |         1 |
+------------+---------------+-----------+