查询处理


查询操作是关系数据库中使用最为频繁的操作,也是构成其他构架语句(如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)
上一篇:如何修改DNN默认的DOCTYPE


下一篇:string函数