高级查询

数据库示例：

学生信息表student

+-----+------+-----+-----+
| id  | name | age | sex |
+-----+------+-----+-----+
| 001 | 张三 |  18 | 男  |
| 002 | 李四 |  20 | 女  |
| 003 | 王五 |  18 | 男  |
+-----+------+-----+-----+

成绩表score

+----+------------+------------+-------+
| id | student_id | subject_id | score |
+----+------------+------------+-------+
|  1 | 001        | 1001       |    80 |
|  2 | 002        | 1002       |    60 |
|  3 | 001        | 1001       |    70 |
|  4 | 002        | 1002       |  60.5 |
+----+------------+------------+-------+

一、子查询

一个内层查询语句(select-from-where)块可以嵌套在另外一个外层查询块的where子句中，其中外层查询也称为父查询，主查询。内层查询也称子查询，从查询。
例如查询张三的各个科目的成绩：

mysql> SELECT subject_id, score FROM score WHERE student_id = (SELECT id FROM student WHERE name = '张三');
+------------+-------+
| subject_id | score |
+------------+-------+
| 1001       |    80 |
| 1002       |    70 |
+------------+-------+
2 rows in set (0.00 sec)

子查询可以较方便地对两个或多个表进行查询，过程也比较好理解。但是当查询的表过多(超过3个)时嵌套的查询就比较复杂，会可读性。而且外部的查询的返回结果不能包括内部查询的结果。

二、联结查询

联结查询可以将表进行关联，从而显示出来自多张表的数据。

1. 内联结

返回两个或者多个表之间相等关系的数据，从数学关系来看，相当于求交集。从左表中取出每一条记录，去右表中与所有的记录进行匹配：匹配必须是某个条件在左表中与右表中相同最终才会保留结果，否则不保留。

mysql>  SELECT * FROM student INNER JOIN score ON student.id = score.student_id;
+-----+------+-----+-----+----+------------+------------+-------+
| id  | name | age | sex | id | student_id | subject_id | score |
+-----+------+-----+-----+----+------------+------------+-------+
| 001 | 张三 |  18 | 男  |  1 | 001        | 1001       |    80 |
| 002 | 李四 |  20 | 女  |  2 | 002        | 1001       |    60 |
| 001 | 张三 |  18 | 男  |  3 | 001        | 1002       |    70 |
| 002 | 李四 |  20 | 女  |  4 | 002        | 1002       |  60.5 |
+-----+------+-----+-----+----+------------+------------+-------+
4 rows in set (0.00 sec)

注：表名太长时可以使用别名来区分同名字段；可以使用where代替on关键字，但效率差很多。

2. 外联结

以某张表为主,取出里面的所有记录，然后每条与另外一张表进行连接：不管能不能匹配上条件，最终都会保留：能匹配，正确保留；不能匹配，其他表的字段都置空NULL。

• 左外联结：以左边为主表，返回左表中的所有数据：

mysql> SELECT * FROM student LEFT JOIN score ON student.id = score.student_id;
+-----+------+-----+-----+------+------------+------------+-------+
| id  | name | age | sex | id   | student_id | subject_id | score |
+-----+------+-----+-----+------+------------+------------+-------+
| 001 | 张三 |  18 | 男  |    1 | 001        | 1001       |    80 |
| 002 | 李四 |  20 | 女  |    2 | 002        | 1001       |    60 |
| 001 | 张三 |  18 | 男  |    3 | 001        | 1002       |    70 |
| 002 | 李四 |  20 | 女  |    4 | 002        | 1002       |  60.5 |
| 003 | 王五 |  18 | 男  | NULL | NULL       | NULL       |  NULL |
+-----+------+-----+-----+------+------------+------------+-------+
5 rows in set (0.00 sec)

• 右外联结：以右边为主表，返回右表中的所有数据：

mysql> SELECT * FROM student RIGHT JOIN score ON student.id = score.student_id;
+------+------+------+------+----+------------+------------+-------+
| id   | name | age  | sex  | id | student_id | subject_id | score |
+------+------+------+------+----+------------+------------+-------+
| 001  | 张三 |   18 | 男   |  1 | 001        | 1001       |    80 |
| 001  | 张三 |   18 | 男   |  3 | 001        | 1002       |    70 |
| 002  | 李四 |   20 | 女   |  2 | 002        | 1001       |    60 |
| 002  | 李四 |   20 | 女   |  4 | 002        | 1002       |  60.5 |
+------+------+------+------+----+------------+------------+-------+
4 rows in set (0.00 sec)

3. 交叉联结

从一张表中循环取出每一条记录，每条记录都去另外一张表进行匹配：匹配一定保留(没有条件匹配)，而连接本身字段就会增加(保留)，最终形成的结果叫做：笛卡尔积。

SELECT * FROM table1 AS t1 CROSS JOIN table2 AS t2;

4. 自联结

有时需要在同一张表中进行联结条件的匹配或字段比较，可以使用自联结。

SELECT * FROM table t1, table t2 WHERE t1.column1=t2.column2;

三、组合查询

多数SQL查询都只包含从一个或多个表中返回数据的单条SELECT语句。MySQL也允许执行多个查询(多条SELECT语句)，并将结果作为单个查询结果集返回。一般在以下两种情况中使用：

• 在单个查询中，从不同表中返回类似结构的数据。
• 对单个表执行多个查询，按照单个查询返回数据。

SELECT * FROM table1 WHERE condition 
UNION
SELECT * FROM table2 WHERE condition;

第一种情况，例：

mysql> SELECT student_id,score FROM score WHERE score > 65 
    -> UNION 
    -> SELECT id,age FROM student WHERE age >= 20;
+------------+-------+
| student_id | score |
+------------+-------+
| 001        |    80 |
| 001        |    70 |
| 002        |    20 |
+------------+-------+
3 rows in set (0.00 sec)

第二种情况，例：

mysql> SELECT * FROM score WHERE score > 65 UNION SELECT * FROM score WHERE id = 4;
+----+------------+------------+-------+
| id | student_id | subject_id | score |
+----+------------+------------+-------+
|  1 | 001        | 1001       |    80 |
|  3 | 001        | 1002       |    70 |
|  4 | 002        | 1002       |  60.5 |
+----+------------+------------+-------+
3 rows in set (0.00 sec)

此种情况下（即情况2），可以用一个SELECT语句加上WHERE条件来实现。

注：

• UNION中的每个查询必须包含相同的列、表达式或聚集函数。
• UNION中的每个SELECT语句返回的列类型必须兼容。可以不必完全相同，但是可以隐式转换。
• UNION默认会消除重复的行，如果要返回所有行：请用UNION ALL。
• 再用UNION查询时，只能使用一条ORDER BY子句，它必须出现在最后一条SELECT之后。