數據庫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)
