数据库MySQL-多表查询
- 2020 年 3 月 27 日
- 笔记
1.2 多表查询
1.2.1 内连接
规则：返回两个表的公共记录
语法：
-- 语法一 select * from 表1 inner join 表2 on 表1.公共字段=表2.公共字段 -- 语法二 select * from 表1，表2 where 表1.公共字段=表2.公共字段
例题
-- inner join mysql> select * from stuinfo inner join stumarks on stuinfo.stuno=stumarks.stuno; +--------+----------+--------+--------+---------+------------+---------+--------+-------------+---------+ | stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | examNo | stuNo | writtenExam | labExam | +--------+----------+--------+--------+---------+------------+---------+--------+-------------+---------+ | s25303 | 李斯文 | 女 | 22 | 2 | 北京 | s271811 | s25303 | 80 | 58 | | s25302 | 李文才 | 男 | 31 | 3 | 上海 | s271813 | s25302 | 50 | 90 | | s25304 | 欧阳俊雄 | 男 | 28 | 4 | 天津 | s271815 | s25304 | 65 | 50 | | s25301 | 张秋丽 | 男 | 18 | 1 | 北京 | s271816 | s25301 | 77 | 82 | | s25318 | 争青小子 | 男 | 26 | 6 | 天津 | s271819 | s25318 | 56 | 48 | +--------+----------+--------+--------+---------+------------+---------+--------+-------------+---------+ 5 rows in set (0.00 sec) -- 相同的字段只显示一次 mysql> select s.stuno,stuname,stusex,writtenexam,labexam from stuinfo s inner join stumarks m on s.stuno=m.stuno; +--------+----------+--------+-------------+---------+ | stuno | stuname | stusex | writtenexam | labexam | +--------+----------+--------+-------------+---------+ | s25303 | 李斯文 | 女 | 80 | 58 | | s25302 | 李文才 | 男 | 50 | 90 | | s25304 | 欧阳俊雄 | 男 | 65 | 50 | | s25301 | 张秋丽 | 男 | 77 | 82 | | s25318 | 争青小子 | 男 | 56 | 48 | +--------+----------+--------+-------------+---------+ 5 rows in set (0.00 sec) -- 使用where mysql> select * from stuinfo,stumarks where stuinfo.stuno=stumarks.stuno; +--------+----------+--------+--------+---------+------------+---------+--------+-------------+---------+ | stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | examNo | stuNo | writtenExam | labExam | +--------+----------+--------+--------+---------+------------+---------+--------+-------------+---------+ | s25303 | 李斯文 | 女 | 22 | 2 | 北京 | s271811 | s25303 | 80 | 58 | | s25302 | 李文才 | 男 | 31 | 3 | 上海 | s271813 | s25302 | 50 | 90 | | s25304 | 欧阳俊雄 | 男 | 28 | 4 | 天津 | s271815 | s25304 | 65 | 50 | | s25301 | 张秋丽 | 男 | 18 | 1 | 北京 | s271816 | s25301 | 77 | 82 | | s25318 | 争青小子 | 男 | 26 | 6 | 天津 | s271819 | s25318 | 56 | 48 | +--------+----------+--------+--------+---------+------------+---------+--------+-------------+---------+ 5 rows in set (0.00 sec)
多学一招：
-- 1、内连接中inner可以省略 select * from 表1 join 表2 on 表1.公共字段=表2.公共字段 mysql> select * from stuinfo join stumarks on stuinfo.stuno=stumarks.stuno; +--------+----------+--------+--------+---------+------------+---------+--------+-------------+---------+ | stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | examNo | stuNo | writtenExam | labExam | +--------+----------+--------+--------+---------+------------+---------+--------+-------------+---------+ | s25303 | 李斯文 | 女 | 22 | 2 | 北京 | s271811 | s25303 | 80 | 58 | | s25302 | 李文才 | 男 | 31 | 3 | 上海 | s271813 | s25302 | 50 | 90 | | s25304 | 欧阳俊雄 | 男 | 28 | 4 | 天津 | s271815 | s25304 | 65 | 50 | | s25301 | 张秋丽 | 男 | 18 | 1 | 北京 | s271816 | s25301 | 77 | 82 | | s25318 | 争青小子 | 男 | 26 | 6 | 天津 | s271819 | s25318 | 56 | 48 | +--------+----------+--------+--------+---------+------------+---------+--------+-------------+---------+ 5 rows in set (0.00 sec) -- 如何实现三表查询 select * from 表1 inner join 表2 on 表1.公共字段=表2.公共字段 inner join 表3 on 表2.公共字段=表3.公共字段 -- 表连接越多，效率越低
思考：
select * from 表1 inner join 表2 on 表1.公共字段=表2.公共字段 和 select * from 表2 inner join 表1 on 表1.公共字段=表2.公共字段 一样吗？ 答：一样的
1.2.2 左外连接
规则：以左边的表为准，右边如果没有对应的记录用null显示
语法：
select * from 表1 left join 表2 on 表1.公共字段=表2.公共字段
例题：
mysql> select stuname,writtenexam,labexam from stuinfo left join stumarks on stuinfo.stuno=stumarks.stuno; +----------+-------------+---------+ | stuname | writtenexam | labexam | +----------+-------------+---------+ | 张秋丽 | 77 | 82 | | 李文才 | 50 | 90 | | 李斯文 | 80 | 58 | | 欧阳俊雄 | 65 | 50 | | 诸葛丽丽 | NULL | NULL | | 争青小子 | 56 | 48 | | 梅超风 | NULL | NULL | +----------+-------------+---------+ 7 rows in set (0.01 sec)
思考：
select * from 表1 left join 表2 on 表1.公共字段=表2.公共字段 和 select * from 表2 left join 表1 on 表1.公共字段=表2.公共字段 一样吗？ 答：不一样，第一个SQL以表1为准，第二个SQL以表2为准。
1.2.3 右外连接
规则：以右边的表为准，左边如果没有对应的记录用null显示
语法：
select * from 表1 right join 表2 on 表1.公共字段=表2.公共字段
例题：
mysql> select stuname,writtenexam,labexam from stuinfo right join stumarks on stuinfo.stuno=stumarks.stuno; +----------+-------------+---------+ | stuname | writtenexam | labexam | +----------+-------------+---------+ | 李斯文 | 80 | 58 | | 李文才 | 50 | 90 | | 欧阳俊雄 | 65 | 50 | | 张秋丽 | 77 | 82 | | 争青小子 | 56 | 48 | | NULL | 66 | 77 | +----------+-------------+---------+ 6 rows in set (0.00 sec)
思考
select * from 表1 left join 表2 on 表1.公共字段=表2.公共字段 和 select * from 表2 right join 表1 on 表1.公共字段=表2.公共字段 一样吗？ 答：一样
1.2.4 交叉连接
语法，返回笛卡尔积
select * from 表1 cross join 表2
例题
-- 交叉连接 mysql> select * from stuinfo cross join stumarks; -- 交叉连接有连接表达式与内连接是一样的 mysql> select * from stuinfo cross join stumarks on stuinfo.stuno=stumarks.stuno; +--------+----------+--------+--------+---------+------------+---------+--------+-------------+---------+ | stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | examNo | stuNo | writtenExam | labExam | +--------+----------+--------+--------+---------+------------+---------+--------+-------------+---------+ | s25303 | 李斯文 | 女 | 22 | 2 | 北京 | s271811 | s25303 | 80 | 58 | | s25302 | 李文才 | 男 | 31 | 3 | 上海 | s271813 | s25302 | 50 | 90 | | s25304 | 欧阳俊雄 | 男 | 28 | 4 | 天津 | s271815 | s25304 | 65 | 50 | | s25301 | 张秋丽 | 男 | 18 | 1 | 北京 | s271816 | s25301 | 77 | 82 | | s25318 | 争青小子 | 男 | 26 | 6 | 天津 | s271819 | s25318 | 56 | 48 | +--------+----------+--------+--------+---------+------------+---------+--------+-------------+---------+ 5 rows in set (0.00 sec)
小结
1、交叉连接如果没有连接条件返回笛卡尔积
2、如果有连接条件和内连接是一样的。
1.2.5 自然连接
自动判断条件连接，判断的条件是依据同名字段
1、自然内连接（natural join）
mysql> select * from stuinfo natural join stumarks; +--------+----------+--------+--------+---------+------------+---------+-------------+---------+ | stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | examNo | writtenExam | labExam | +--------+----------+--------+--------+---------+------------+---------+-------------+---------+ | s25303 | 李斯文 | 女 | 22 | 2 | 北京 | s271811 | 80 | 58 | | s25302 | 李文才 | 男 | 31 | 3 | 上海 | s271813 | 50 | 90 | | s25304 | 欧阳俊雄 | 男 | 28 | 4 | 天津 | s271815 | 65 | 50 | | s25301 | 张秋丽 | 男 | 18 | 1 | 北京 | s271816 | 77 | 82 | | s25318 | 争青小子 | 男 | 26 | 6 | 天津 | s271819 | 56 | 48 | +--------+----------+--------+--------+---------+------------+---------+-------------+---------+ 5 rows in set (0.00 sec)
2、自然左外连接（natural left join）
mysql> select * from stuinfo natural left join stumarks; +--------+----------+--------+--------+---------+------------+---------+-------------+---------+ | stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | examNo | writtenExam | labExam | +--------+----------+--------+--------+---------+------------+---------+-------------+---------+ | s25301 | 张秋丽 | 男 | 18 | 1 | 北京 | s271816 | 77 | 82 | | s25302 | 李文才 | 男 | 31 | 3 | 上海 | s271813 | 50 | 90 | | s25303 | 李斯文 | 女 | 22 | 2 | 北京 | s271811 | 80 | 58 | | s25304 | 欧阳俊雄 | 男 | 28 | 4 | 天津 | s271815 | 65 | 50 | | s25305 | 诸葛丽丽 | 女 | 23 | 7 | 河南 | NULL | NULL | NULL | | s25318 | 争青小子 | 男 | 26 | 6 | 天津 | s271819 | 56 | 48 | | s25319 | 梅超风 | 女 | 23 | 5 | 河北 | NULL | NULL | NULL | +--------+----------+--------+--------+---------+------------+---------+-------------+---------+ 7 rows in set (0.00 sec)
3、自然右外连接（natural right join）
mysql> select * from stuinfo natural right join stumarks; +--------+---------+-------------+---------+----------+--------+--------+---------+------------+ | stuNo | examNo | writtenExam | labExam | stuName | stuSex | stuAge | stuSeat | stuAddress | +--------+---------+-------------+---------+----------+--------+--------+---------+------------+ | s25303 | s271811 | 80 | 58 | 李斯文 | 女 | 22 | 2 | 北京 | | s25302 | s271813 | 50 | 90 | 李文才 | 男 | 31 | 3 | 上海 | | s25304 | s271815 | 65 | 50 | 欧阳俊雄 | 男 | 28 | 4 | 天津 | | s25301 | s271816 | 77 | 82 | 张秋丽 | 男 | 18 | 1 | 北京 | | s25318 | s271819 | 56 | 48 | 争青小子 | 男 | 26 | 6 | 天津 | | s25320 | s271820 | 66 | 77 | NULL | NULL | NULL | NULL | NULL | +--------+---------+-------------+---------+----------+--------+--------+---------+------------+ 6 rows in set (0.00 sec)
小结：
1、表连接是通过同名字段来连接的
2、如果没有同名字段就返回笛卡尔积
3、同名的连接字段只显示一个，并且将该字段放在最前面
1.2.6 using
using用来指定连接字段
mysql> select * from stuinfo inner join stumarks using(stuno); +--------+----------+--------+--------+---------+------------+---------+-------------+---------+ | stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | examNo | writtenExam | labExam | +--------+----------+--------+--------+---------+------------+---------+-------------+---------+ | s25303 | 李斯文 | 女 | 22 | 2 | 北京 | s271811 | 80 | 58 | | s25302 | 李文才 | 男 | 31 | 3 | 上海 | s271813 | 50 | 90 | | s25304 | 欧阳俊雄 | 男 | 28 | 4 | 天津 | s271815 | 65 | 50 | | s25301 | 张秋丽 | 男 | 18 | 1 | 北京 | s271816 | 77 | 82 | | s25318 | 争青小子 | 男 | 26 | 6 | 天津 | s271819 | 56 | 48 | +--------+----------+--------+--------+---------+------------+---------+-------------+---------+ 5 rows in set (0.00 sec)
using的结果也会对公共字段进行优化，优化的规则和自然连接是一样的；
1.2.7 练习
1、显示地区及每个地区参加笔试的人数，并按人数降序排列
-- 第一步： 显示地区及每个地区参加笔试的人数 mysql> select stuaddress,count(writtenexam) from stuinfo left join stumarks using(stuno) group by stuaddress; +------------+--------------------+ | stuaddress | count(writtenexam) | +------------+--------------------+ | 上海 | 1 | | 北京 | 2 | | 天津 | 2 | | 河北 | 0 | | 河南 | 0 | +------------+--------------------+ 5 rows in set (0.00 sec) -- 第二步：将结果降序排列 mysql> select stuaddress,count(writtenexam) c from stuinfo left join stumarks using(stuno) group by stuaddress order by c desc; +------------+---+ | stuaddress | c | +------------+---+ | 北京 | 2 | | 天津 | 2 | | 上海 | 1 | | 河北 | 0 | | 河南 | 0 | +------------+---+ 5 rows in set (0.00 sec)
2、显示有学生参加考试的地区
-- having筛选 mysql> select stuaddress,count(writtenexam) c from stuinfo left join stumarks using(stuno) group by stuaddress having c>0; +------------+---+ | stuaddress | c | +------------+---+ | 上海 | 1 | | 北京 | 2 | | 天津 | 2 | +------------+---+ 3 rows in set (0.00 sec) -- 表连接实现 -- 第一步：右连接获取有成绩的地区 mysql> select stuaddress from stuinfo right join stumarks using(stuno); +------------+ | stuaddress | +------------+ | 北京 | | 上海 | | 天津 | | 北京 | | 天津 | | NULL | +------------+ 6 rows in set (0.00 sec) -- 第二步：去重复 mysql> select distinct stuaddress from stuinfo right join stumarks using(stuno); +------------+ | stuaddress | +------------+ | 北京 | | 上海 | | 天津 | | NULL | +------------+ 4 rows in set (0.00 sec) -- 去除null mysql> select distinct stuaddress from stuinfo right join stumarks using(stuno) having stuaddress is not null; +------------+ | stuaddress | +------------+ | 北京 | | 上海 | | 天津 | +------------+ 3 rows in set (0.00 sec)
3、显示男生和女生的人数
-- 方法一： 分组查询 mysql> select stusex,count(*) from stuinfo group by stusex; +--------+----------+ | stusex | count(*) | +--------+----------+ | 女 | 3 | | 男 | 4 | +--------+----------+ 2 rows in set (0.00 sec) -- 方法二： union mysql> select stusex,count(*) from stuinfo where stusex='男' union select stusex,count(*) from stuinfo where stusex='女'; +--------+----------+ | stusex | count(*) | +--------+----------+ | 男 | 4 | | 女 | 3 | +--------+----------+ 2 rows in set (0.00 sec) -- 方法三：直接写条件 mysql> select sum(stusex='男') 男,sum(stusex='女') 女 from stuinfo; +------+------+ | 男 | 女 | +------+------+ | 4 | 3 | +------+------+ 1 row in set (0.00 sec)
4、显示每个地区男生、女生、总人数
mysql> select stuaddress,count(*) 总人数,sum(stusex='男') 男,sum(stusex='女') 女 from stuinfo group by stuaddress; +------------+--------+------+------+ | stuaddress | 总人数 | 男 | 女 | +------------+--------+------+------+ | 上海 | 1 | 1 | 0 | | 北京 | 2 | 1 | 1 | | 天津 | 2 | 2 | 0 | | 河北 | 1 | 0 | 1 | | 河南 | 1 | 0 | 1 | +------------+--------+------+------+ 5 rows in set (0.00 sec)
