查询操作是关系数据库中使用最为频繁的操作，也是构成其他构架语句（如DELETE、UPDATE）的基础。当要删除或更新某些记录时，首先查询出这些记录，然后再对其进行相应的SQL操作。因此基于SELECT的查询操作是显得非常重要。对于查询处理，可将其分为逻辑查询处理及物理查询处理。逻辑查询处理表示执行查询应该产生什么样的结果，而物理查询代表MySQL数据库是如何得到该结果的。两种查询的方法可能完全不同，但是得到的结果必定是相同的。
| 查询过程 | |
|---|---|
| (8)SELECT (9)DISTINCT ＜select_list＞ | |
| (1)FROM ＜left_table＞ | |
| (3)＜join_type> 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> |
查询处理的各个阶段
1）FROM：对FROM子句中的左表＜left_table>和右表＜right_table>执行笛卡儿积Cartesan product),产生虚拟表 VT1。
2）ON：对虚拟表VT1 应用ON 筛选，只有那些符合＜join_condition>的行才被插入虚拟表 VT2中。
3）JOIN：如果指定了OUTER JOIN (如LEFT OUTER JOIN、RIGHT OUTER JOIN），那么保留表中未匹配的行作为外部行添加到虚拟表VT2中，产生虚拟表VT3。如果FROM子句饮食两个以上表，则对上一个连接生成的结果表VT3和下一个表重复执行步骤1）~步骤3），直到处理完所有的表为止。
4）WHERE：对虚拟表VT3 应用过滤条件，只有符合＜where_condition>的记录才被插入虚拟表VT4中。
5）GROUP BY：根据GROUP BY子句中的列，对VT4中的记录进行分组操作，产生VT5.
6）CUBE | ROLLUP：对表VT5进行CUBE或ROLLUP操作，产生VT6。
7）HAVING：对虚拟表VT6应用HAVING过滤器，只有符合＜having_condition>的记录才被插入虚拟表VT7中。
8）SELECT：对第二次执行SELECT操作，选择指定的列，插入虚拟表VT8中。
9）DISTINCT：去除重复数据，产生虚拟表VT9。
10）ORDER BY：将虚拟表VT9中的记录按照＜order_by_list>进行排序操作，产生虚拟表VT10。
11）LIMIT：取出指定行的记录，产生虚拟表VT11，并返回给查询用户。
建两张表customers、orders并插入数据
create table customers
(
customer_id varchar(10) not null,
city varchar(10) not null,
primary key(customer_id)
)engine=innodb;
insert into customers values('139','Hangzhou');
insert into customers values('guo','Shanghai');
insert into customers values('tx','Hangzhou');
insert into customers values('baidu','Shanghai');
mysql> select * from customers;
+-------------+----------+
| customer_id | city |
+-------------+----------+
| 139 | Hangzhou |
| baidu | Shanghai |
| guo | Shanghai |
| tx | Hangzhou |
+-------------+----------+
4 rows in set (0.00 sec)
create table orders
(
order_id int not null auto_increment,
customer_id varchar(10),
primary key(order_id)
)engine=innodb;
insert into orders values(null,'139');
insert into orders values(null,'139');
insert into orders values(null,'guo');
insert into orders values(null,'guo');
insert into orders values(null,'guo');
insert into orders values(null,'tx');
insert into orders values(null,null);
mysql> select * from orders;
+----------+-------------+
| order_id | customer_id |
+----------+-------------+
| 1 | 139 |
| 2 | 139 |
| 3 | guo |
| 4 | guo |
| 5 | guo |
| 6 | tx |
| 7 | NULL |
+----------+-------------+
7 rows in set (0.00 sec)
执行笛卡儿积
mysql> select * from customers c join orders o;
+-------------+----------+----------+-------------+
| customer_id | city | order_id | customer_id |
+-------------+----------+----------+-------------+
| 139 | Hangzhou | 1 | 139 |
| baidu | Shanghai | 1 | 139 |
| guo | Shanghai | 1 | 139 |
| tx | Hangzhou | 1 | 139 |
| 139 | Hangzhou | 2 | 139 |
| baidu | Shanghai | 2 | 139 |
| guo | Shanghai | 2 | 139 |
| tx | Hangzhou | 2 | 139 |
| 139 | Hangzhou | 3 | guo |
| baidu | Shanghai | 3 | guo |
| guo | Shanghai | 3 | guo |
| tx | Hangzhou | 3 | guo |
| 139 | Hangzhou | 4 | guo |
| baidu | Shanghai | 4 | guo |
| guo | Shanghai | 4 | guo |
| tx | Hangzhou | 4 | guo |
| 139 | Hangzhou | 5 | guo |
| baidu | Shanghai | 5 | guo |
| guo | Shanghai | 5 | guo |
| tx | Hangzhou | 5 | guo |
| 139 | Hangzhou | 6 | tx |
| baidu | Shanghai | 6 | tx |
| guo | Shanghai | 6 | tx |
| tx | Hangzhou | 6 | tx |
| 139 | Hangzhou | 7 | NULL |
| baidu | Shanghai | 7 | NULL |
| guo | Shanghai | 7 | NULL |
| tx | Hangzhou | 7 | NULL |
+-------------+----------+----------+-------------+
28 rows in set (0.00 sec)
用ON过滤
mysql> select * from 大专栏 查询处理customers c join orders o on c.customer_id=o.customer_id;
+-------------+----------+----------+-------------+
| customer_id | city | order_id | customer_id |
+-------------+----------+----------+-------------+
| 139 | Hangzhou | 1 | 139 |
| 139 | Hangzhou | 2 | 139 |
| guo | Shanghai | 3 | guo |
| guo | Shanghai | 4 | guo |
| guo | Shanghai | 5 | guo |
| tx | Hangzhou | 6 | tx |
+-------------+----------+----------+-------------+
6 rows in set (0.00 sec)
连接（join）
连接分为内连接、外连接、交叉连接，其中外连接又分为左外连接、右外连接、全外连接。左外连接、右外连接中outer关键字都可省略。
左外连接，左表保留，右表符全条件的保留
mysql> select * from customers c left join orders o on c.customer_id=o.customer_id;
+-------------+----------+----------+-------------+
| customer_id | city | order_id | customer_id |
+-------------+----------+----------+-------------+
| 139 | Hangzhou | 1 | 139 |
| 139 | Hangzhou | 2 | 139 |
| guo | Shanghai | 3 | guo |
| guo | Shanghai | 4 | guo |
| guo | Shanghai | 5 | guo |
| tx | Hangzhou | 6 | tx |
| baidu | Shanghai | NULL | NULL |
+-------------+----------+----------+-------------+
7 rows in set (0.00 sec)
右外连接，右表保留，左表符全条件的保留
mysql> select * from customers c right join orders o on c.customer_id=o.customer_id;
+-------------+----------+----------+-------------+
| customer_id | city | order_id | customer_id |
+-------------+----------+----------+-------------+
| 139 | Hangzhou | 1 | 139 |
| 139 | Hangzhou | 2 | 139 |
| guo | Shanghai | 3 | guo |
| guo | Shanghai | 4 | guo |
| guo | Shanghai | 5 | guo |
| tx | Hangzhou | 6 | tx |
| NULL | NULL | 7 | NULL |
+-------------+----------+----------+-------------+
7 rows in set (0.00 sec)
到目前为止MySQL 5.7仍不支持全外连接，
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.10 |
+-----------+
1 row in set (0.00 sec)
mysql> select * from a full outer join b on a.id=b.id;
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 'outer join b on a.id=b.id' at line 1
mysql> select * from a left outer join b on a.id=b.id;
+------+------+------+------+
| ID | QTY | ID | QTY |
+------+------+------+------+
| 2 | 20 | 2 | 30 |
| 1 | 10 | NULL | NULL |
+------+------+------+------+
2 rows in set (0.00 sec)
mysql>
where过滤
mysql> select * from customers c left outer join orders o on c.customer_id=o.customer_id where city='Hangzhou';
+-------------+----------+----------+-------------+
| customer_id | city | order_id | customer_id |
+-------------+----------+----------+-------------+
| 139 | Hangzhou | 1 | 139 |
| 139 | Hangzhou | 2 | 139 |
| tx | Hangzhou | 6 | tx |
+-------------+----------+----------+-------------+
3 rows in set (0.00 sec)
分组
分组会自动去重
mysql> select * from customers c left outer join orders o on c.customer_id=o.customer_id where city='Hangzhou' group by c.customer_id;
+-------------+----------+----------+-------------+
| customer_id | city | order_id | customer_id |
+-------------+----------+----------+-------------+
| 139 | Hangzhou | 1 | 139 |
| tx | Hangzhou | 6 | tx |
+-------------+----------+----------+-------------+
2 rows in set (0.00 sec)
having过滤
having过滤是对分组条件进行过滤的筛选器
mysql> select * from customers c left outer join orders o on c.customer_id=o.customer_id where city='Hangzhou' group by c.customer_id having count(o.order_id)<2;
+-------------+----------+----------+-------------+
| customer_id | city | order_id | customer_id |
+-------------+----------+----------+-------------+
| tx | Hangzhou | 6 | tx |
+-------------+----------+----------+-------------+
1 row in set (0.00 sec)
虽然SELECT是查询中最先指定的部分，但是直到步骤8）时才真正进行处理。
mysql> select c.customer_id ,count(o.order_id) from customers c left outer join orders o on c.customer_id=o.customer_id group by c.customer_id having count(o.order_id)<2;
+-------------+-------------------+
| customer_id | count(o.order_id) |
+-------------+-------------------+
| baidu | 0 |
| tx | 1 |
+-------------+-------------------+
2 rows in set (0.00 sec)
排序
mysql> select c.customer_id ,count(o.order_id) total_order from customers c left outer join orders o on c.customer_id=o.customer_id group by c.customer_id having count(o.order_id)<2 order by total_order desc;
+-------------+-------------+
| customer_id | total_order |
+-------------+-------------+
| tx | 1 |
| baidu | 0 |
+-------------+-------------+
2 rows in set (0.00 sec)
limit
mysql> select c.customer_id ,count(o.order_id) total_order from customers c left outer join orders o on c.customer_id=o.customer_id group by c.customer_id having count(o.order_id)<2 order by total_order desc limit 1;
+-------------+-------------+
| customer_id | total_order |
+-------------+-------------+
| tx | 1 |
+-------------+-------------+
1 row in set (0.00 sec)