1、子查询（嵌套查询）
mysql> select * from people;
+------+-----+
| name | age |
+------+-----+
| 甲 | 29 |
| 乙 | 35 |
+------+-----+
2 rows in set (0.00 sec)
mysql> select * from houses where name in (select name from people where age<30);
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲 | 天河 | 1997 |
| 甲 | 天河 | 1998 |
+------+----------------+-----------------+
2 rows in set (0.10 sec)
mysql> select * from houses;
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲 | 天河 | 1997 |
| 甲 | 天河 | 1998 |
+------+----------------+-----------------+
2 rows in set (0.00 sec)
mysql> select * from houses where name='甲';
+------+----------------+-----------------+
| name | house_location | purchasing_year |
+------+----------------+-----------------+
| 甲 | 天河 | 1997 |
| 甲 | 天河 | 1998 |
+------+----------------+-----------------+
2 rows in set (0.00 sec)
mysql> select * from houses where name in (select name from people where age >100);
Empty set (0.00 sec)2、分组查询（group by）
mysql> select house_location from houses group by house_location;
+----------------+
| house_location |
+----------------+
| 天河 |
+----------------+
1 row in set (0.00 sec)3、聚合查询（count(), sum(), max(), min()）
mysql> select house_location, count(*) as total from houses group by house_location;
+----------------+-------+
| house_location | total |
+----------------+-------+
| 天河 | 2 |
+----------------+-------+
1 row in set (0.06 sec)
mysql> select sum(age) from people;
+----------+
| sum(age) |
+----------+
| 64 |
+----------+
1 row in set (0.04 sec)
mysql> select * from people;
+------+-----+
| name | age |
+------+-----+
| 甲 | 29 |
| 乙 | 35 |
+------+-----+
2 rows in set (0.00 sec)
mysql> select max(age) from people;
+----------+
| max(age) |
+----------+
| 35 |
+----------+
1 row in set (0.04 sec)
mysql> select min(age) from people;
+----------+
| min(age) |
+----------+
| 29 |
+----------+
1 row in set (0.00 sec)4、组合查询（union）
mysql> select name from houses;
+------+
| name |
+------+
| 甲 |
| 甲 |
+------+
2 rows in set (0.00 sec)
mysql> select name from people;
+------+
| name |
+------+
| 甲 |
| 乙 |
+------+
2 rows in set (0.00 sec)
mysql> select name from houses union distinct select name from people;
+------+
| name |
+------+
| 甲 |
| 乙 |
+------+
2 rows in set (0.03 sec)
mysql> select name from houses union all select name from people;
+------+
| name |
+------+
| 甲 |
| 甲 |
| 甲 |
| 乙 |
+------+
4 rows in set (0.00 sec)
mysql> select name from houses union select name from people;/*等同于 union distinct*/
+------+
| name |
+------+
| 甲 |
| 乙 |
+------+
2 rows in set (0.00 sec)