MySQL学习笔记--逻辑查询处理

介绍

本文内容主要来自于《MySQL技术内幕:SQL编程》,逻辑查询是指SQL语句中的SELECT语句能查出的结果。一条SELECT语句包含FROM、JOIN、ON、WHERE、ORDER BY、 HAVING、LIMIT等关键字,不了解这些关键字的执行顺序,也不能编写出可用的SQL查询语句,无法获得想要的结果。好了,下面我们看一下SQL查询语句究竟是怎么执行的。

建表

新建两张表:客户表和订单表。客户表记录客户id和客户所属城市;订单表记录订单id和客户id。

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
CREATE TABLE `customers`  (
`customer_id` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`city` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (`customer_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

INSERT INTO `customers` VALUES ('163', 'HangZhou');
INSERT INTO `customers` VALUES ('9you', 'ShangHai');
INSERT INTO `customers` VALUES ('baidu', 'HangZhou');
INSERT INTO `customers` VALUES ('TX', 'HangZhou');


CREATE TABLE `orders` (
`order_id` int NOT NULL AUTO_INCREMENT,
`customer_id` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

INSERT INTO `orders` VALUES (1, '163');
INSERT INTO `orders` VALUES (2, '163');
INSERT INTO `orders` VALUES (3, '9you');
INSERT INTO `orders` VALUES (4, '9you');
INSERT INTO `orders` VALUES (5, 'TX');
INSERT INTO `orders` VALUES (6, '9you');
INSERT INTO `orders` VALUES (7, NULL);

逻辑查询处理

1
2
3
4
5
6
7
8
9
10
(8)SELECT (9)DISTINCT <slect list>
(1)FROM <left_table>
(3)<join_tpye> JOIN <right_table>
(2)ON <join_condition>
(4)WHERE <where_condition>
(5)GROUP BY<group_by_list>
(6)WITH(CUBE|ROLLUP)
(7)HAVING<having_condition>
(10)ORDER BY<order_by_list>
(11)LIMIT<limit_number>

首先执行FROM操作,最后执行LIMIT操作,每一步操作都会产生虚拟表,每一个虚拟表会作为下一个操作的输入,最后的虚拟表返回给用户。

FROM

首先执行FROM操作,从数据库的表中获取数据。左表和右表执行笛卡尔积,产生虚拟表VT1.

如下所示,对order和customers表执行笛卡尔积,虚拟表VT1会生成4*7条数据。

1
SELECT * FROM orders JOIN customers;
order_id customer_id customer_id(1) city
1 163 163 HangZhou
1 163 9you ShangHai
1 163 baidu HangZhou
1 163 TX HangZhou
2 163 163 HangZhou
2 163 9you ShangHai
2 163 baidu HangZhou
2 163 TX HangZhou
3 9you 163 HangZhou
3 9you 9you ShangHai
3 9you baidu HangZhou
3 9you TX HangZhou
4 9you 163 HangZhou
4 9you 9you ShangHai
4 9you baidu HangZhou
4 9you TX HangZhou
5 TX 163 HangZhou
5 TX 9you ShangHai
5 TX baidu HangZhou
5 TX TX HangZhou
6 9you 163 HangZhou
6 9you 9you ShangHai
6 9you baidu HangZhou
6 9you TX HangZhou
7 163 HangZhou
7 9you ShangHai
7 baidu HangZhou
7 TX HangZhou

ON

对虚拟表VT1执行ON操作,只有符合的数据才会被插入虚拟表VT2。

如下所示,ON操作的条件是<orders.customer_id = customers.customer_id>,在VT1中符合该条件的数据会插入虚拟表VT2

1
SELECT * FROM orders JOIN customers on orders.customer_id = customers.customer_id;
order_id customer_id customer_id(1) city
1 163 163 HangZhou
2 163 163 HangZhou
3 9you 9you ShangHai
4 9you 9you ShangHai
6 9you 9you ShangHai
5 TX TX HangZhou

JOIN

如果指定了OUTER JOIN,如LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOIN,可以省略掉OUTER,OUTER就是指外部行。LEFT OUTER JOIN把左表记为保留表,RIGHT OUTER JOIN把右表记为保留表,FULL OUTER JOIN把左右表都记为保留表,添加外部行的工作就是在VT2表的基础上添加保留表中被过滤条件过滤掉的数据,非保留表中的数据被赋予NULL值,最后生成虚拟表VT3。

如下例子所示,使用LEFT JOIN后,根据ON操作的条件过滤后变成6条数据,还会将非保留表(customers)中的数据赋值为NULL并插入,生成虚拟表VT3。

所以使用OUTER JOIN后,要删除非保留表中的数据,还要使用WHERE条件过滤。

1
SELECT * FROM orders LEFT JOIN customers on orders.customer_id = customers.customer_id;
order_id customer_id customer_id(1) city
1 163 163 HangZhou
2 163 163 HangZhou
3 9you 9you ShangHai
4 9you 9you ShangHai
5 TX TX HangZhou
6 9you 9you ShangHai
7

WHERE

对虚拟表VT3进行WHERE条件过滤,符合的记录才会放到虚拟表VT4中。

在这一步,可以将ON中添加的外部行过滤掉。

1
SELECT * FROM orders LEFT JOIN customers on orders.customer_id = customers.customer_id where customers.customer_id is not null;
order_id customer_id customer_id(1) city
1 163 163 HangZhou
2 163 163 HangZhou
3 9you 9you ShangHai
4 9you 9you ShangHai
6 9you 9you ShangHai
5 TX TX HangZhou

GROUP BY

根据GROUP BY中的列,将虚拟表VT4的记录分组,获得虚拟表VT5.

1
SELECT * FROM orders LEFT JOIN customers on orders.customer_id = customers.customer_id where customers.customer_id is not null GROUP BY orders.customer_id;
order_id customer_id customer_id(1) city
1 163 163 HangZhou
3 9you 9you ShangHai
5 TX TX HangZhou

WITH

WITH很少用,不介绍

HAVING

前面介绍了ON、WHERE等过滤条件,HAVING是最后一个条件过滤条件,对分组后的内容进行筛选。

对虚拟表VT5应用HAVING过滤器,只有符合的记录才被插入虚拟表VT6中。

1
SELECT * FROM orders LEFT JOIN customers on orders.customer_id = customers.customer_id where customers.customer_id is not null GROUP BY orders.customer_id HAVING order_id <=3;
order_id customer_id customer_id(1) city
1 163 163 HangZhou
3 9you 9you ShangHai

SELECT

在这一步,执行SELECT操作,选择指定的列,插入到虚拟表VT7中。

1
SELECT order_id as o, o+1 as n FROM orders LEFT JOIN customers on orders.customer_id = customers.customer_id where customers.customer_id is not null GROUP BY orders.customer_id HAVING order_id <=3;

值得注意的是,别名在SELECT的其他别名中不能使用,上面的语句会报错。

1
> 1054 - Unknown column 'o' in 'field list'

DISTINCT

去除重复数据,产生虚拟表VT8。

DISTINCT会在内存中创建一张临时表,并在DISTINCT的列上增加唯一索引,以此来去除重复数据,内存中放不下就放在磁盘上。

对于使用了GROUP BY查询的语句,再使用DISTINCT多余。

ORDER BY

将虚拟表VT8中的记录按照进行排序操作,产生虚拟表VT9。

还可以在ORDER BY子句中指定SELECT列表中列的序列号,如下面的语句:

1
SELECT order_id, orders.customer_id FROM orders LEFT JOIN customers on orders.customer_id = customers.customer_id where customers.customer_id is not null ORDER BY 1,2;

等同于:

1
SELECT order_id, orders.customer_id FROM orders LEFT JOIN customers on orders.customer_id = customers.customer_id where customers.customer_id is not null ORDER BY order_id, orders.customer_id;
order_id customer_id
1 163
2 163
3 9you
4 9you
5 TX
6 9you

关于默认排序

在MySQL中,一个SELECT语句返回的数据并不是默认按照主键id的顺序排列,也就是说没有加ORDER BY的语句返回数据的顺序是不确定的。

MyISAM引擎中是按照物理存储的顺序显示,InnoDB中同样不会按照主键顺序排列。

LIMIT

取出指定行的记录,产生虚拟表VT10,并返回给查询用户。

1
LIMIT n,m

如上所示,LIMIT支持从第n条数据开始选择m条,对于小规模数据可以这样使用,但数据量大时会扫描到数据的位置再获取m条数据,效率非常低。