MySQL的多表联查

MySQL的多表联查

多表查询：

嵌套查询

where关联查询

join连接查询（左联，右联，内联）

嵌套查询 (不推荐)

在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件的查询称为嵌套查询。

先处理内查询由内向外查询

mysql> select max(age) from stu;

+----------+
| max(age) |
+----------+
|       29     |
+----------+
1 row in set (0.00 sec)


mysql> select * from stu where age=29;

+----+---------+------+-----+---------+
| id | name    | age  | sex | classid |
+----+---------+------+-----+---------+
|  9 | xiaoli     |   29 | w    |       2 |
| 14 | zhangle |   29 | m   |       5 |
+----+---------+------+-----+---------+
2 rows in set (0.01 sec)

– 查询年龄最大的所有学生信息

mysql> select * from stu where age=(select max(age) from stu);

+----+---------+------+-----+---------+
| id | name    | age  | sex | classid |
+----+---------+------+-----+---------+
|  9 | xiaoli  |   29 | w   |       2 |
| 14 | zhangle |   29 | m   |       5 |
+----+---------+------+-----+---------+
2 rows in set (0.01 sec)

– 查询python02期的所有学生信息

mysql> select * from stu where classid=(select id from classes where name='python02');
mysql> select * from stu where classid in(select id from classes where name='python02');


+----+---------+------+-----+---------+
| id | name    | age  | sex | classid |
+----+---------+------+-----+---------+
|  2 | lisi    |   25 | m   |       2 |
|  6 | uu02    |   25 | m   |       2 |
|  7 | uu03    |   28 | w   |       2 |
|  9 | xiaoli  |   29 | w   |       2 |
| 11 | xiaoyan |   22 | m   |       2 |
| 13 | wangwen |   27 | w   |       2 |
+----+---------+------+-----+---------+
6 rows in set (0.00 sec)

**where关联查询

查询所有学生信息，并跨表显示对应的班级名称信息**

mysql> select s.*,c.name cname from stu s,classes c where s.classid=c.id;
+----+-----------+------+-----+---------+----------+
| id | name      | age  | sex | classid | cname    |
+----+-----------+------+-----+---------+----------+
|  1 | zhangsan  |   20 | w   |       1 | python01 |
|  2 | lisi      |   25 | m   |       2 | python02 |
|  3 | wangwu    |   22 | w   |       5 | python04 |
|  4 | zhaoliu   |   21 | m   |       4 | python03 |
|  5 | uu01      |   27 | w   |       1 | python01 |
|  6 | uu02      |   25 | m   |       2 | python02 |
|  7 | uu03      |   28 | w   |       2 | python02 |
|  8 | uu05      |   22 | m   |       4 | python03 |
|  9 | xiaoli    |   29 | w   |       2 | python02 |
| 10 | xiaozhang |   19 | w   |       1 | python01 |
| 11 | xiaoyan   |   22 | m   |       2 | python02 |
| 12 | xiaoxin   |   28 | w   |       4 | python03 |
| 13 | wangwen   |   27 | w   |       2 | python02 |
| 14 | zhangle   |   29 | m   |       5 | python04 |
+----+-----------+------+-----+---------+----------+
14 rows in set (0.01 sec)

– 统计每个班级的人数

mysql> select c.name,count(s.id) from classes c,stu s where c.id=s.classid group by c.id;
+----------+-------------+
| name     | count(s.id) |
+----------+-------------+
| python01 |           3 |
| python02 |           6 |
| python03 |           3 |
| python04 |           2 |
+----------+-------------+
4 rows in set (0.00 sec)

mysql> select c.name,count(s.id) from classes c,stu s where c.id=s.classid group by c.id order by count(s.id) desc;
+----------+-------------+
| name     | count(s.id) |
+----------+-------------+
| python02 |           6 |
| python01 |           3 |
| python03 |           3 |
| python04 |           2 |
+----------+-------------+
4 rows in set (0.00 sec)

JOIN连接查询（左联，右联，内联）

JOIN 按照功能大致分为如下三类：

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）：与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
– 查询新闻信息，并补齐新闻类别信息

mysql> select n.id,n.title,t.name from news n,ntype t where n.ntid=t.id;
mysql> select n.id,n.title,t.name from news n inner join ntype t on n.ntid=t.id;
+----+-------------------------------------------------------------+--------------+
| id | title                                                       | name         |
+----+-------------------------------------------------------------+--------------+
|  1 | 骑共享单车违法 将被禁用或冻结账户                           | 娱乐新闻     |
|  2 | 武警特战排爆手                                              | 军事新闻     |
|  3 | 国外武装直升机型号发展与作战能力分析                        | 军事新闻     |
|  4 | 俄战机过去一周在边境拦截外国侦察机11次                      | 军事新闻     |
|  5 | 中国空军战机进行远洋训练。                                  | 军事新闻     |
|  6 | 中国驻韩使馆举行中韩建交25周年纪念招待会                    | 国际新闻     |
+----+-------------------------------------------------------------+--------------+
6 rows in set (0.00 sec)

-- 同上，但采用的是左联查询left join
mysql> select n.id,n.title,t.name from news n left join ntype t on n.ntid=t.id;
+----+-------------------------------------------------------------+--------------+
| id | title                                                       | name         |
+----+-------------------------------------------------------------+--------------+
|  1 | 骑共享单车违法 将被禁用或冻结账户                           | 娱乐新闻     |
|  2 | 武警特战排爆手                                              | 军事新闻     |
|  3 | 国外武装直升机型号发展与作战能力分析                        | 军事新闻     |
|  4 | 俄战机过去一周在边境拦截外国侦察机11次                      | 军事新闻     |
|  5 | 中国空军战机进行远洋训练。                                  | 军事新闻     |
|  6 | 中国驻韩使馆举行中韩建交25周年纪念招待会                    | 国际新闻     |
+----+-------------------------------------------------------------+--------------+
6 rows in set (0.01 sec)

-- 统计每个新闻类别下的新闻数量，采用where关联统计
mysql> select t.name,count(n.id) from ntype t,news n where t.id=n.ntid group by t.id;
+--------------+-------------+
| name         | count(n.id) |
+--------------+-------------+
| 娱乐新闻     |           1 |
| 国际新闻     |           1 |
| 军事新闻     |           4 |
+--------------+-------------+
3 rows in set (0.01 sec)

-- 统计每个新闻类别下的新闻数量，采用左联统计
mysql> select t.name,count(n.id) from ntype t left join news n on t.id=n.ntid group by t.id;
+--------------+-------------+
| name         | count(n.id) |
+--------------+-------------+
| 娱乐新闻     |           1 |
| 体育新闻     |           0 |
| 国际新闻     |           1 |
| 军事新闻     |           4 |
+--------------+-------------+
4 rows in set (0.01 sec)

其实 左右可以更换位置，也起到了left join right join的作用，例如： n left join t ,t left join n.（不建议使用）