子查询

select 语句中嵌套 select 语句的查询叫做子查询。

select
  ...(select)...
from
  ...(select)...
where
 ...(select)...

where 子句中出现的子查询

mysql> select name,age,grade,math from student where math > (select min(math) from student);
+------+------+-----------+------+
| name | age  | grade     | math |
+------+------+-----------+------+
| 王维 |   33 | 三年级1班 |   98 |
| 杜甫 |   34 | 三年级3班 |   96 |
| 郭靖 |   36 | 三年级1班 |   96 |
| 王良 |   66 | 三年级3班 |   90 |
| 刘邦 |   46 | 三年级2班 |   93 |
+------+------+-----------+------+
5 rows in set (0.00 sec)

from 子句中的子查询

可以将子查询的查询结构当做一张临时表。

mysql> select t.*,g.id from (select math,avg(math) as math1 from student group by math) t join grade g on t.math1 between g.lowgrade and g.highgrade;
+------+---------+------+
| math | math1   | id   |
+------+---------+------+
|   88 | 88.0000 |    3 |
|   90 | 90.0000 |    3 |
|   93 | 93.0000 |    3 |
|   96 | 96.0000 |    4 |
|   98 | 98.0000 |    4 |
+------+---------+------+
5 rows in set (0.00 sec)

select 子句后面出现的子查询(了解即可)

mysql> select e.name ,(select d.depart_name from department d where d.id = e.depart_id) from employ e;
+------+-------------------------------------------------------------------+
| name | (select d.depart_name from department d where d.id = e.depart_id) |
+------+-------------------------------------------------------------------+
| 王维 | 研发部                                                            |
| 李白 | 销售部                                                            |
| 杜甫 | 研发部                                                            |
| 郭靖 | 销售部                                                            |
| 王良 | 研发部                                                            |
| 刘邦 | 销售部                                                            |
| 王维 | 研发部                                                            |
+------+-------------------------------------------------------------------+
7 rows in set (0.00 sec)

union 合并查询结果集

mysql> select name ,math from student where address = '天津' union select name,math from student where address = '北京';
+------+------+
| name | math |
+------+------+
| 王维 |   98 |
| 杜甫 |   96 |
| 王良 |   90 |
| 郭靖 |   96 |
+------+------+
4 rows in set (0.00 sec)

mysql> select name,math from student where address = '天津' or address = '北京';
+------+------+
| name | math |
+------+------+
| 王维 |   98 |
| 杜甫 |   96 |
| 郭靖 |   96 |
| 王良 |   90 |
+------+------+
4 rows in set (0.00 sec)

mysql> select name,math from student where address in ('天津','北京');
+------+------+
| name | math |
+------+------+
| 王维 |   98 |
| 杜甫 |   96 |
| 郭靖 |   96 |
| 王良 |   90 |
+------+------+
4 rows in set (0.00 sec)

union 的效率要高于其他查询方法。其实就是将表的查询结果的乘法做了个加法运算。

在进行结果集合并的时候，列数需要相同。否则无法合并结果。列的数据类型也的相同(其他数据库)。

limit 将查询结果的一部分取出来

mysql> select * from student order by math limit 0, 5;
+------+-----------+------+---------+--------+---------------------+------+---------+---------+-----------+
| id   | name      | age  | chinese | gender | birthday            | math | english | address | grade     |
+------+-----------+------+---------+--------+---------------------+------+---------+---------+-----------+
|    2 | 李白      |   43 |      99 | 女     | 1913-01-01 00:00:00 |   88 |      89 | 上海    | 三年级2班 |
|    7 | WANGGONG  |   35 |    NULL | 男     | 1943-01-01 00:00:00 |   88 |      89 | NULL    | 三年级3班 |
|    7 | WANG GONG |   35 |    NULL | 男     | 1943-01-01 00:00:00 |   88 |      89 | NULL    | 三年级3班 |
|    5 | 王良      |   66 |      97 | 女     | 1943-01-01 00:00:00 |   90 |      88 | 天津    | 三年级3班 |
|    6 | 刘邦      |   46 |      95 | 男     | 1953-01-01 00:00:00 |   93 |      88 | NULL    | 三年级2班 |
+------+-----------+------+---------+--------+---------------------+------+---------+---------+-----------+
5 rows in set (0.00 sec)

limit 后面只跟一个数值就是前几。

limit startindex , length。

常用于分页。

在order by 后使用。

分页算法：每页显示pagesize条记录：

第pageNo页： limit (pageNo -1)* pagesize , pagesize;