庆峰的博客

流水要争先，争的是滔滔不绝。

0%

MySQL基础（二）

发表于 更新于 分类于
本文字数： 27k 阅读时长 ≈ 24 分钟

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

  • 在 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。
  • 子查询严格以上是指嵌套在查询内部，且必须始终出现在圆括号内
  • 子查询可以包含多个关键字：DISTINCT、GROUP BY、ORDER BY、LIMIT等函数。
  • 子查询的外层可以是：INSERT、UPDATE、SELECT 、DELETE、SET、DO等操作。
  • 子查询可以返回结果，这个结果可以是标量、一行、一列，或者子查询。
  • 子查询的引发条件：这里需要特别注意，并不是显示的才是子查询。
    • 使用比较运算符的子查询：= , > , < , >= , <= , <> , != , <=>
    • 使用结构语法：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
25
# 添加测试数据
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)
  • 这里使用一些聚合函数来做测试比如AVG、SUM、MIN、MAX。
使用 运算符 的子查询
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)
  • 通过 >= 配合 ANY、SOME 、ALL关键做了测试，那么其它几种运算符就不演示了。
使用 [ 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
38
# 这个操作相对比较好理解 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
52
# 创建一张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_id 和brand_id 由于不是物理外键，所以约束机会没有。也就是说哪怕插入t_goods表的时候，cate_id 和 brand_id 在t_goods_cates 和 t_goods_brand表中都不存在，依然是可以插入。但是数据不完整。
内连接

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

左外连接

1
2
3
4
# 这里为了演示其效果，我们在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
5
# 在 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
46
# 创建一张商品类型表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