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条数据，效率非常低。