MySQL基础(二)

本章是整理知识内容,为强化知识长期更新。

  • MySQL基础(一) 已经介绍了MYSQL最常用基础的操作,由于篇幅已经很大,所以分开记录。

MySql基础操作

子查询与连接

在MYSQL实际操作中,最大的操作就是查询,下面就关于子查询和链接做记录。

子查询

  • 子查询(SubQuery)是指出现在其它SQL语句中的SELECT子句。比如
1
2
SELECT * FROM table_1 AS t1 WHERE columns_1 = (SELECT columns_2 FROM table_2 AS t2);
# 这里的SELECT * FROM table_1 AS t1 称之为 Outer Query/Outer Statement;SELECT columns_2 FROM table_2 AS t2 成为 SubQuery。
  • 子查询严格以上是指嵌套在查询内部,且必须始终出现在圆括号内
  • 子查询可以包含多个关键字:DISTINCTGROUP BYORDER BYLIMIT等函数。
  • 子查询的外层可以是:INSERTUPDATESELECTDELETESETDO等操作。
  • 子查询可以返回结果,这个结果可以是标量一行一列,或者子查询。
  • 子查询的引发条件:这里需要特别注意,并不是显示的才是子查询。
    • 使用比较运算符的子查询:= , > , < , >= , <= , <> , != , <=>
    • 使用结构语法:operand comparison_operator subquery ,可以使用一下修饰符来配合比较运算符。
      • operand comparison_operator ANY(subquery)
      • operand comparison_operator SOME(subquery)
      • operand comparison_operator ALL(subquery)
运算符\关键字 ANY SOME ALL
>>= 最大值 最小值 最大
<<= 最大值 最大值 最小值
= 任意值 任意值
<>!= 任意值
  • 首先创建一个测试表,并准备测试数据。我们用最经典商城goods表来做测试。
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
# 创建测试表t_goods
[root@localhost][Demo]> CREATE TABLE t_goods(
-> goods_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> goods_name VARCHAR(150) NOT NULL,
-> godds_cate VARCHAR(40) NOT NULL,
-> goods_price DECIMAL(16,3) UNSIGNED NOT NULL DEFAULT 0,
-> brand_name VARCHAR(40) NOT NULL,
-> is_show BOOLEAN NOT NULL DEFAULT 1,
-> is_saleoff BOOLEAN NOT NULL DEFAULT 0
-> );
Query OK, 0 rows affected (0.13 sec)

# 哎哟写错了字段名字了。。。
[root@localhost][Demo]> ALTER TABLE t_goods CHANGE godds_cate goods_cate VARCHAR(40) NOT NULL;
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0

#检查表结构
[root@localhost][Demo]> SHOW COLUMNS FROM t_goods;
+-------------+------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------------+------+-----+---------+----------------+
| goods_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| goods_name | varchar(150) | NO | | NULL | |
| goods_cate | varchar(40) | NO | | NULL | |
| goods_price | decimal(16,3) unsigned | NO | | 0.000 | |
| brand_name | varchar(40) | NO | | NULL | |
| is_show | tinyint(1) | NO | | 1 | |
| is_saleoff | tinyint(1) | NO | | 0 | |
+-------------+------------------------+------+-----+---------+----------------+
7 rows in set (0.01 sec)
  • 这里需要注意的是price价格,并没有使用浮点型,笔者之前公司用的是TINTINT就是存到分。这里用的是DECIMAL定点型,保证金额的准确。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
# 添加测试数据
INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('R510VC 15.6英寸笔记本','笔记本','华硕','3399',DEFAULT,DEFAULT);
INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Y400N 14.0英寸笔记本电脑','笔记本','联想','4899',DEFAULT,DEFAULT);
INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('G150TH 15.6英寸游戏本','游戏本','雷神','8499',DEFAULT,DEFAULT);
INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X550CC 15.6英寸笔记本','笔记本','华硕','2799',DEFAULT,DEFAULT);
INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X240(20ALA0EYCD) 12.5英寸超极本','超级本','联想','4999',DEFAULT,DEFAULT);
INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('U330P 13.3英寸超极本','超级本','联想','4299',DEFAULT,DEFAULT);
INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('SVP13226SCB 13.3英寸触控超极本','超级本','索尼','7999',DEFAULT,DEFAULT);
INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iPad mini MD531CH/A 7.9英寸平板电脑','平板电脑','苹果','1998',DEFAULT,DEFAULT);
INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iPad Air MD788CH/A 9.7英寸平板电脑 (16G WiFi版)','平板电脑','苹果','3388',DEFAULT,DEFAULT);
INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' iPad mini ME279CH/A 配备 Retina 显示屏 7.9英寸平板电脑 (16G WiFi版)','平板电脑','苹果','2788',DEFAULT,DEFAULT);
INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('IdeaCentre C340 20英寸一体电脑 ','台式机','联想','3499',DEFAULT,DEFAULT);
INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Vostro 3800-R1206 台式电脑','台式机','戴尔','2899',DEFAULT,DEFAULT);
INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iMac ME086CH/A 21.5英寸一体电脑','台式机','苹果','9188',DEFAULT,DEFAULT);
INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('AT7-7414LP 台式电脑 (i5-3450四核 4G 500G 2G独显 DVD 键鼠 Linux )','台式机','宏碁','3699',DEFAULT,DEFAULT);
INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Z220SFF F4F06PA工作站','服务器/工作站','惠普','4288',DEFAULT,DEFAULT);
INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('PowerEdge T110 II服务器','服务器/工作站','戴尔','5388',DEFAULT,DEFAULT);
INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Mac Pro MD878CH/A 专业级台式电脑','服务器/工作站','苹果','28888',DEFAULT,DEFAULT);
INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' HMZ-T3W 头戴显示设备','笔记本配件','索尼','6999',DEFAULT,DEFAULT);
INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT);
INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X3250 M4机架式服务器 2583i14','服务器/工作站','IBM','6888',DEFAULT,DEFAULT);
INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('玄龙精英版 笔记本散热器','笔记本配件','九州风神','',DEFAULT,DEFAULT);
INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' HMZ-T3W 头戴显示设备','笔记本配件','索尼','6999',DEFAULT,DEFAULT);
INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT);
  • 我们检查下数据,这里贴图片。

  • 当然还另外一种显示方式 SELECT * FROM t_goods \G:
  • 另外goods_name名称中存在空格,我们使用 TRIM函数处理一下。
1
2
3
# 更新goods_name字段。
[root@localhost][Demo]> UPDATE t_goods SET goods_name = TRIM(goods_name);
Query OK, 4 rows affected (0.00 sec)
  • 这里使用一些聚合函数来做测试比如AVGSUMMINMAX
使用 运算符 的子查询
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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
# 获取goods_price的平均值。
[root@localhost][Demo]> SELECT AVG(goods_price) FROM t_goods;
+------------------+
| AVG(goods_price) |
+------------------+
| 5636.3636364 |
+------------------+
1 row in set (0.00 sec)

# 查询价格大于平均值的商品。
[root@localhost][Demo]> SELECT goods_id , goods_name , goods_price FROM t_goods WHERE goods_price >= 5636;
+----------+-----------------------------------------+-------------+
| goods_id | goods_name | goods_price |
+----------+-----------------------------------------+-------------+
| 3 | G150TH 15.6英寸游戏本 | 8499.000 |
| 7 | SVP13226SCB 13.3英寸触控超极本 | 7999.000 |
| 13 | iMac ME086CH/A 21.5英寸一体电脑 | 9188.000 |
| 17 | Mac Pro MD878CH/A 专业级台式电脑 | 28888.000 |
| 18 | HMZ-T3W 头戴显示设备 | 6999.000 |
| 20 | X3250 M4机架式服务器 2583i14 | 6888.000 |
| 21 | HMZ-T3W 头戴显示设备 | 6999.000 |
+----------+-----------------------------------------+-------------+
7 rows in set (0.00 sec)

#那么问题来了,我们想一条SQL语句就完成该怎么处理。使用子查询就可以满足。
[root@localhost][Demo]> SELECT goods_id , goods_name , goods_price FROM t_goods WHERE goods_price >= (SELECT AVG(goods_price) FROM t_goods );
+----------+-----------------------------------------+-------------+
| goods_id | goods_name | goods_price |
+----------+-----------------------------------------+-------------+
| 3 | G150TH 15.6英寸游戏本 | 8499.000 |
| 7 | SVP13226SCB 13.3英寸触控超极本 | 7999.000 |
| 13 | iMac ME086CH/A 21.5英寸一体电脑 | 9188.000 |
| 17 | Mac Pro MD878CH/A 专业级台式电脑 | 28888.000 |
| 18 | HMZ-T3W 头戴显示设备 | 6999.000 |
| 20 | X3250 M4机架式服务器 2583i14 | 6888.000 |
| 21 | HMZ-T3W 头戴显示设备 | 6999.000 |
+----------+-----------------------------------------+-------------+
7 rows in set (0.00 sec)

# 在来演示下查询 goods_cate 是超级本的商品信息。
[root@localhost][Demo]> SELECT goods_id , goods_name , goods_price , goods_cate FROM t_goods WHERE goods_cate = '超级本';
+----------+---------------------------------------+-------------+------------+
| goods_id | goods_name | goods_price | goods_cate |
+----------+---------------------------------------+-------------+------------+
| 5 | X240(20ALA0EYCD) 12.5英寸超极本 | 4999.000 | 超级本 |
| 6 | U330P 13.3英寸超极本 | 4299.000 | 超级本 |
| 7 | SVP13226SCB 13.3英寸触控超极本 | 7999.000 | 超级本 |
+----------+---------------------------------------+-------------+------------+
3 rows in set (0.00 sec)

# 实际上在子查询中,经常会出现返回的值不是一条的问题。如下演示,我们想要查询goods_cate是超极本的商品价格。
[root@localhost][Demo]> SELECT goods_id , goods_name , goods_price FROM t_goods WHERE goods_price >= ( SELECT goods_price FROM t_goods WHERE goods_cate = '超级本' );
ERROR 1242 (21000): Subquery returns more than 1 row
# 这里会抛出异常,指明返回的结果集超过了一行。

#上面的列子中,使用了比较运算符,所以可以使用子查询。那配合修饰关键字试试。
# 使用大于等于 >= 添加ANY关键字的时候,获取最小的。
[root@localhost][Demo]> SELECT goods_id , goods_name , goods_price FROM t_goods WHERE goods_price >= ANY( SELECT goods_price FROM t_goods WHERE goods_cate = '超级本' );
+----------+-----------------------------------------+-------------+
| goods_id | goods_name | goods_price |
+----------+-----------------------------------------+-------------+
| 2 | Y400N 14.0英寸笔记本电脑 | 4899.000 |
| 3 | G150TH 15.6英寸游戏本 | 8499.000 |
| 5 | X240(20ALA0EYCD) 12.5英寸超极本 | 4999.000 |
| 6 | U330P 13.3英寸超极本 | 4299.000 |
| 7 | SVP13226SCB 13.3英寸触控超极本 | 7999.000 |
| 13 | iMac ME086CH/A 21.5英寸一体电脑 | 9188.000 |
| 16 | PowerEdge T110 II服务器 | 5388.000 |
| 17 | Mac Pro MD878CH/A 专业级台式电脑 | 28888.000 |
| 18 | HMZ-T3W 头戴显示设备 | 6999.000 |
| 20 | X3250 M4机架式服务器 2583i14 | 6888.000 |
| 21 | HMZ-T3W 头戴显示设备 | 6999.000 |
+----------+-----------------------------------------+-------------+
11 rows in set (0.00 sec)

# 使用大于等于 >= 添加SOME关键字 依然是最小。
[root@localhost][Demo]> SELECT goods_id , goods_name , goods_price FROM t_goods WHERE goods_price >= SOME( SELECT goods_price FROM t_goods WHERE goods_cate = '超级本' );
+----------+-----------------------------------------+-------------+
| goods_id | goods_name | goods_price |
+----------+-----------------------------------------+-------------+
| 2 | Y400N 14.0英寸笔记本电脑 | 4899.000 |
| 3 | G150TH 15.6英寸游戏本 | 8499.000 |
| 5 | X240(20ALA0EYCD) 12.5英寸超极本 | 4999.000 |
| 6 | U330P 13.3英寸超极本 | 4299.000 |
| 7 | SVP13226SCB 13.3英寸触控超极本 | 7999.000 |
| 13 | iMac ME086CH/A 21.5英寸一体电脑 | 9188.000 |
| 16 | PowerEdge T110 II服务器 | 5388.000 |
| 17 | Mac Pro MD878CH/A 专业级台式电脑 | 28888.000 |
| 18 | HMZ-T3W 头戴显示设备 | 6999.000 |
| 20 | X3250 M4机架式服务器 2583i14 | 6888.000 |
| 21 | HMZ-T3W 头戴显示设备 | 6999.000 |
+----------+-----------------------------------------+-------------+
11 rows in set (0.00 sec)

# 使用大于等于 >= 添加ALL关键字 则是最大。
[root@localhost][Demo]> SELECT goods_id , goods_name , goods_price FROM t_goods WHERE goods_price >= ALL( SELECT goods_price FROM t_goods WHERE goods_cate = '超级本' );
+----------+-----------------------------------------+-------------+
| goods_id | goods_name | goods_price |
+----------+-----------------------------------------+-------------+
| 3 | G150TH 15.6英寸游戏本 | 8499.000 |
| 7 | SVP13226SCB 13.3英寸触控超极本 | 7999.000 |
| 13 | iMac ME086CH/A 21.5英寸一体电脑 | 9188.000 |
| 17 | Mac Pro MD878CH/A 专业级台式电脑 | 28888.000 |
+----------+-----------------------------------------+-------------+
4 rows in set (0.00 sec)
  • 通过 >= 配合 ANYSOMEALL关键做了测试,那么其它几种运算符就不演示了。
使用 [ NOT ] IN 的子查询
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
# 这个操作相对比较好理解 IN 包含 NOT IN 不包含
[root@localhost][Demo]> SELECT goods_id , goods_name , goods_price FROM t_goods WHERE goods_price IN ( SELECT goods_price FROM t_goods WHERE goods_cate = '超级本' );
+----------+---------------------------------------+-------------+
| goods_id | goods_name | goods_price |
+----------+---------------------------------------+-------------+
| 5 | X240(20ALA0EYCD) 12.5英寸超极本 | 4999.000 |
| 6 | U330P 13.3英寸超极本 | 4299.000 |
| 7 | SVP13226SCB 13.3英寸触控超极本 | 7999.000 |
+----------+---------------------------------------+-------------+
3 rows in set (0.00 sec)

# 使用NOT IN 就和上面的结果截然相反
[root@localhost][Demo]> SELECT goods_id, goods_price FROM t_goods WHERE goods_price NOT IN ( SELECT goods_price FROM t_goods WHERE goods_cate = '超级本' );
+----------+-------------+
| goods_id | goods_price |
+----------+-------------+
| 1 | 3399.000 |
| 2 | 4899.000 |
| 3 | 8499.000 |
| 4 | 2799.000 |
| 8 | 1998.000 |
| 9 | 3388.000 |
| 10 | 2788.000 |
| 11 | 3499.000 |
| 12 | 2899.000 |
| 13 | 9188.000 |
| 14 | 3699.000 |
| 15 | 4288.000 |
| 16 | 5388.000 |
| 17 | 28888.000 |
| 18 | 6999.000 |
| 19 | 99.000 |
| 20 | 6888.000 |
| 21 | 6999.000 |
| 22 | 99.000 |
+----------+-------------+
19 rows in set (0.00 sec)
使用[NOT] EXISTS 子查询
  • 为了演示该子查询,需要在添加一张表与其配合。
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
46
47
48
49
50
51
# 创建一张t_goods_cate表存储商品分类信息。
[root@localhost][Demo]> CREATE TABLE IF NOT EXISTS t_goods_cates(
-> cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT ,
-> cate_name VARCHAR(40) NOT NULL
-> );
Query OK, 0 rows affected (0.07 sec)

[root@localhost][Demo]> SHOW COLUMNS FROM t_goods_cates;
+-----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------------------+------+-----+---------+----------------+
| cate_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| cate_name | varchar(40) | NO | | NULL | |
+-----------+----------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

# 现在这张表的数据是空的,需要将t_goods 中 goods_cate信息写入进来。
# 先看t_goods 有哪些分类;这里使用GROUP BY 来做分组查询。
[root@localhost][Demo]> SELECT goods_cate FROM t_goods GROUP BY goods_cate;
+---------------------+
| goods_cate |
+---------------------+
| 台式机 |
| 平板电脑 |
| 服务器/工作站 |
| 游戏本 |
| 笔记本 |
| 笔记本配件 |
| 超级本 |
+---------------------+
7 rows in set (0.00 sec)

# 使用INSERT...SEKECT 方式来插入数据到t_goods_cates。
[root@localhost][Demo]> INSERT INTO t_goods_cates(cate_name) SELECT goods_cate FROM t_goods GROUP BY goods_cate;
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0

# OK检查下数据是否录入成功。
[root@localhost][Demo]> SELECT * FROM t_goods_cates;
+---------+---------------------+
| cate_id | cate_name |
+---------+---------------------+
| 1 | 台式机 |
| 2 | 平板电脑 |
| 3 | 服务器/工作站 |
| 4 | 游戏本 |
| 5 | 笔记本 |
| 6 | 笔记本配件 |
| 7 | 超级本 |
+---------+---------------------+
7 rows in set (0.00 sec)
  • 这个时候打算把操作反过来,让t_goods 表中的 goods_cate 存储 t_goods_cates 中的主键信息。而不在存储内容。

连接查询

  • 将多张表(至少大于等于2张表)按照某张指定的条件成立进行查询。

table_reference { [INNER | CROSS] JOIN | { LEFT | RIGHT } JOIN} table_reference ON conditional_expr

  • 连接类型
    • INNER JOIN内连接
    • 在Mysql中 JOIN , CROSS JION 和 INNER JOIN是等价的。
    • LEFT [OUTER] JOIN 左外连接。
    • RIGHT [OUTER] JOIN 右外连接。
  • 连接条件
    • 可以使用ON来设置连接条件,也可以使用WHERE来代替。
    • 但是通常使用ON关键字来设置条件,WHERE关键进行结果集的过滤。
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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
# 使用INNER JOIN 内连接来完成t_goods表中 goods_cate字段记录的更新。
[root@localhost][Demo]> UPDATE t_goods INNER JOIN t_goods_cates ON t_goods.goods_cate = t_goods_cates.cate_name SET t_goods.goods_cate = t_goods_cates.cate_id;
Query OK, 22 rows affected (0.01 sec)
Rows matched: 22 Changed: 22 Warnings: 0

# 那我们在检查一下
[root@localhost][Demo]> SELECT goods_id , goods_cate FROM t_goods;
+----------+------------+
| goods_id | goods_cate |
+----------+------------+
| 1 | 5 |
| 2 | 5 |
| 3 | 4 |
| 4 | 5 |
| 5 | 7 |
| 6 | 7 |
| 7 | 7 |
| 8 | 2 |
| 9 | 2 |
| 10 | 2 |
| 11 | 1 |
| 12 | 1 |
| 13 | 1 |
| 14 | 1 |
| 15 | 3 |
| 16 | 3 |
| 17 | 3 |
| 18 | 6 |
| 19 | 6 |
| 20 | 3 |
| 21 | 6 |
| 22 | 6 |
+----------+------------+
22 rows in set (0.00 sec)

# 这里最好将t_goods 里面 goods_cate 的字段类型修改一下,毕竟已经不是存储字符串了,而是存储关联表主键。
# 将t_goods 表中 goods_cate 修改成 cate_id 类型为 SMALLINT UNSIGNED;

[root@localhost][Demo]> ALTER TABLE t_goods MODIFY cate_id SMALLINT UNSIGNED NOT NULL;
Query OK, 0 rows affected (0.22 sec)
Records: 22 Duplicates: 0 Warnings: 0

# t_goods表中有一个字段 brand_name 在此对表进行改造。这里创建一张品牌表 t_goods_brand。
[root@localhost][Demo]> CREATE TABLE t_goods_brand(
-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> brand_name VARCHAR(40) NOT NULL
-> )
-> ;
Query OK, 0 rows affected (0.11 sec)

# 检查表结构
[root@localhost][Demo]> SHOW COLUMNS FROM t_goods_brand;
+------------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| brand_name | varchar(40) | NO | | NULL | |
+------------+----------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)


# 将t_goods表中的brand_name 去重后添加到t_goods_brand中。
[root@localhost][Demo]> INSERT t_goods_brand(brand_name) SELECT brand_name FROM t_goods GROUP BY brand_name;
Query OK, 9 rows affected (0.01 sec)
Records: 9 Duplicates: 0 Warnings: 0

# 查询结果
[root@localhost][Demo]> SELECT * FROM t_goods_brand;
+----+------------+
| id | brand_name |
+----+------------+
| 1 | IBM |
| 2 | 华硕 |
| 3 | 宏碁 |
| 4 | 惠普 |
| 5 | 戴尔 |
| 6 | 索尼 |
| 7 | 联想 |
| 8 | 苹果 |
| 9 | 雷神 |
+----+------------+
9 rows in set (0.00 sec)

# 更新t_goods表中brand_name 字段时期存储t_goods_brand主键信息。
[root@localhost][Demo]> UPDATE t_goods INNER JOIN t_goods_brand ON t_goods.brand_name = t_goods_brand.brand_name SET t_goods.braand_name = t_goods_brand.id;
Query OK, 22 rows affected (0.01 sec)
Rows matched: 22 Changed: 22 Warnings: 0

# 检查下效果
[root@localhost][Demo]> SELECT goods_id , brand_name FROM t_goods;
+----------+------------+
| goods_id | brand_name |
+----------+------------+
| 1 | 2 |
| 2 | 7 |
| 3 | 9 |
| 4 | 2 |
| 5 | 7 |
| 6 | 7 |
| 7 | 6 |
| 8 | 8 |
| 9 | 8 |
| 10 | 8 |
| 11 | 7 |
| 12 | 5 |
| 13 | 8 |
| 14 | 3 |
| 15 | 4 |
| 16 | 5 |
| 17 | 8 |
| 18 | 6 |
| 19 | 6 |
| 20 | 1 |
| 21 | 6 |
| 22 | 6 |
+----------+------------+
22 rows in set (0.00 sec)

# OK 再把t_goods 中 brand_name 字段进行修改。
[root@localhost][Demo]> ALTER TABLE t_goods CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;
Query OK, 22 rows affected (0.22 sec)
Records: 22 Duplicates: 0 Warnings: 0
  • 为什么一定要修改字段的属性,这样做主要是为了做适当调整结构。数字和字符串占用的大小要小的多。
  • 这里t_goods 表中的 cate_idbrand_id由于不是物理外键,所以约束机会没有。也就是说哪怕插入t_goods表的时候,cate_idbrand_id 在t_goods_cates 和 t_goods_brand表中都不存在,依然是可以插入。但是数据不完整。
内连接

  • 内连接查询的是相交部分的数据

左外连接

1
2
3
# 这里为了演示其效果,我们在t_goods表中添加一个模拟数据,这条数据的cates_id 是不存在与t_goods_cates表中的。
INSERT t_goods(goods_name,cate_id,brand_id,goods_price) VALUES(' LaserJet Pro P1606dn 黑白激光打印机','12','4','1849');
Query OK, 1 row affected (0.00 sec)
  • INNER JOIN 改成 LEFT JOIN看看结果

  • 这里可以看低23条记录的cate_name是null。
右外连接

  • LEFT JOIN 改成 RIGHT JOIN 试试。
1
2
3
4
# 在 t_goods_cates 表中添加记录
[root@localhost][Demo]> INSERT t_goods_cates(cate_name) VALUES('路由器'),('交换机'),('网卡');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

  • 这里可以看到优先显示t_goods_cates表中的数据。和左外连接刚刚好相反。
多表连接

现在以及存在t_goods 、 t_goods_cates 、 t_goods_brand 表,这次同时查询一个商品表结果集合。

1
2
# 内链接三张表查询结果集合。
[root@localhost][Demo]> SELECT g.goods_id , g.goods_name , g.goods_price , gc.cate_name , gb.brand_name FROM t_goods AS g INNER JOIN t_goods_cates AS gc ON g.cate_id = gc.cate_id INNER JOIN t_goods_brand AS gb ON g.brand_id = gb.id ;

  • 这里只会吧符合内连接条件的数据展示出来。
注意事项
  • t_goods LEFT JOIN t_goods_cates join_condition
    • t_goods_cates 表中的结果集依赖 t_goods
    • 如果使用内连接查找记录在连接数据表中不存在,并且在WHERE 语句中尝试定义了: columns_name IS NULL时(就是这个字段可以为NULL的时候),如果columns_name被定义为NOT NULL(这个字段不能为NULL),Mysql将在找到符合条件的记录后停止搜索更多的行。因为这个操作是冲突的。
自连接

很多情况下,可能会出现一张表自关联,特别是在商城项目的商品类型这种。所以这里在举个例子。

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
# 创建一张商品类型表t_goods_types
[root@localhost][Demo]> CREATE TABLE t_goods_types(
-> type_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> type_name VARCHAR(20) NOT NULL,
-> parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0
-> );
Query OK, 0 rows affected (0.13 sec)

INSERT t_goods_types(type_name,parent_id) VALUES('家用电器',DEFAULT);
INSERT t_goods_types(type_name,parent_id) VALUES('电脑、办公',DEFAULT);
INSERT t_goods_types(type_name,parent_id) VALUES('大家电',1);
INSERT t_goods_types(type_name,parent_id) VALUES('生活电器',1);
INSERT t_goods_types(type_name,parent_id) VALUES('平板电视',3);
INSERT t_goods_types(type_name,parent_id) VALUES('空调',3);
INSERT t_goods_types(type_name,parent_id) VALUES('电风扇',4);
INSERT t_goods_types(type_name,parent_id) VALUES('饮水机',4);
INSERT t_goods_types(type_name,parent_id) VALUES('电脑整机',2);
INSERT t_goods_types(type_name,parent_id) VALUES('电脑配件',2);
INSERT t_goods_types(type_name,parent_id) VALUES('笔记本',9);
INSERT t_goods_types(type_name,parent_id) VALUES('超级本',9);
INSERT t_goods_types(type_name,parent_id) VALUES('游戏本',9);
INSERT t_goods_types(type_name,parent_id) VALUES('CPU',10);
INSERT tdb_goods_types(type_name,parent_id) VALUES('主机',10);

# 检查插入数据结果。
[root@localhost][Demo]> SELECT * FROM t_goods_types;
+---------+-----------------+-----------+
| type_id | type_name | parent_id |
+---------+-----------------+-----------+
| 1 | 家用电器 | 0 |
| 2 | 电脑、办公 | 0 |
| 3 | 大家电 | 1 |
| 4 | 生活电器 | 1 |
| 5 | 平板电视 | 3 |
| 6 | 空调 | 3 |
| 7 | 电风扇 | 4 |
| 8 | 饮水机 | 4 |
| 9 | 电脑整机 | 2 |
| 10 | 电脑配件 | 2 |
| 11 | 笔记本 | 9 |
| 12 | 超级本 | 9 |
| 13 | 游戏本 | 9 |
| 14 | CPU | 10 |
+---------+-----------------+-----------+
14 rows in set (0.00 sec)
  • 这里可以看到t_goods_typs中存在一个字段parent_id,如果不是0即表示有上级类别,而上级列表是指type_id。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 尝试查询一次参考子类的信息。
[root@localhost][Demo]> SELECT t.type_id , t.type_name , p.type_name AS parent_name FROM t_goods_types AS t LEFT JOIN t_goods_types AS p ON t.paarent_id = p.type_id;
+---------+-----------------+-----------------+
| type_id | type_name | parent_name |
+---------+-----------------+-----------------+
| 1 | 家用电器 | NULL |
| 2 | 电脑、办公 | NULL |
| 3 | 大家电 | 家用电器 |
| 4 | 生活电器 | 家用电器 |
| 5 | 平板电视 | 大家电 |
| 6 | 空调 | 大家电 |
| 7 | 电风扇 | 生活电器 |
| 8 | 饮水机 | 生活电器 |
| 9 | 电脑整机 | 电脑、办公 |
| 10 | 电脑配件 | 电脑、办公 |
| 11 | 笔记本 | 电脑整机 |
| 12 | 超级本 | 电脑整机 |
| 13 | 游戏本 | 电脑整机 |
| 14 | CPU | 电脑配件 |
+---------+-----------------+-----------------+
14 rows in set (0.00 sec)
连接查询小结

JOIN作为经常使用的关键字之一,使用的是否正确直接影响到查询结果和查询效率。

SQL标准的JOIN类型

  • 内连接INNER
  • 全外连接FULL OUTER 在mysql中并不能直接使用。
  • 左外连接LEFT OUTER
  • 右外连接RIGHT OUTER
  • 交叉连接CROSS