MySQL 多表查詢

union 結果集合併

使用多個select分別查詢不同的表，把多個select查到的記錄合併在一起

一個select查到m條記錄，另一個select查到n條記錄，合併之後就是m+n條記錄

#查詢全校師生的id、name，使用2個select分別從tb_teacher、tb_student中查，然後將記錄合併

select id，name from tb_student union [distinct] select id,name from tb_teacher;  #如果合併後的結果集中有重覆的記錄，預設只保留其中一個。union預設會去重，distinct可以省略不寫

select id，name from tb_student union all select id,name from tb_teacher;   #如果有重覆的記錄，全都保留

#2個select選中的列（2個結果集中的列），列的名稱、個數、數據類型都要相同，這樣才能合併



#要合併多個select的結果集，兩兩之間用union連接即可

select語句1 union [distinct|all] select語句2 union [distinct|all] select語句3 ...

select都可以帶子句，後續不再說明

子查詢

一個select中要使用其他select查詢的結果

#在where中使用子查詢

#eg.有2張表，tb_dep 一條記錄即一個系的信息，tb_student 一條記錄即一個學生的信息，查詢計算系所有學生的學號、姓名

#思路：先從tb_dep中查出電腦系的id，再從tb_student查出dep_id是該id的的學生

select id,name from tb_student where dep_id=(select id from tb_dep where dep_name='電腦系'); #子查詢要用()括起來


#查詢電腦系、數學系、物理系三個系所有學生的學號、姓名
select id,name from tb_student where dep_id in (select id from tb_dep where dep_name in ('電腦系','數學系','物理系'); 

#在having中使用子查詢

select * from tb_score group by score having score>(select avg(score) from tb_score)；  #查詢成績大於平均分的所有學生信息。

#group by 主鍵，按主鍵分組，一個分組就是一條記錄，對分組的過濾就是對記錄的過濾





#where、having的條件過濾中有2個常用關鍵字：any、all

where xxx > any(子查詢)  #any表示任何一個，大於結果集中的任何一個元素即可

where xxx > all(子查詢)  #all表示所有，大於結果集中的所有元素才行

#在from中使用子查詢

select 列名 from (select子查詢);  #先用子查詢對原表中的記錄過濾一遍，把子查詢的結果集作為一張臨時表，從臨時表中查數據（再次過濾）。列名是臨時表中的列名

select 列名 from （select子查詢）[別名] [子句]； #臨時表和原表的使用方式完全一樣，可以起別名，可以帶子句，可以直接使用臨時表中的列名

join  連接查詢

1、笛卡爾積

2個集合相乘，得到的是笛卡爾積，比如：

{A,B,C} * {a,b,c} = { (A,a)，(A,b)，(A,c)，(B,a)，(B,b)，(B,c)，(C,a)，(C,b)，(C,c) }    #用一個集合的每個元素去乘另一個集合的每個元素

2、連接查詢

一個select查多張表

sql 99中連接查詢的語法：

select  表1、表2中的列名  from

表1 [別名]  連接類型 join  表2 [別名]

on 連接條件

[where子句]

[group by子句]

[having子句]

[order by子句]

[limit子句]

sql 99的連接查詢都帶關鍵字join，稱之為顯式寫法

在sql 99之前還有一個sql語法版本：sql 92，顧名思義，1992年制定的標準

sql 92的連接查詢不帶join，也具有相同的功能，現在還能用，稱之為隱式寫法

連接查詢根據連接類型可分為4大類：

• 交叉連接
• 內連接：又分為等值連接、非等值連接、自連接
• 外連接：又分為左外連接（左連接）、右外連接（右連接）、全外連接（mysql不支持，oracle支持）
• 自然連接

#交叉連接，得到的是笛卡爾積

select * from tb_student cross join tb_score;

select st.id,st.name,sc.score from tb_student st cross join tb_score sc;

  #假設tb_student有30條記錄，tb_score有20條記錄（10人因缺考、緩考等原因暫時沒有成績），

  #2張表以學號（id）為主鍵，或者tb_student以學號為主鍵、tb_score以外鍵形式關聯tb_student的主鍵

  #把2張表作為集合，一條記錄即集合中的一個元素，2張表相乘得到笛卡爾積（30*20條記錄）

  #返回600條記錄，這肯定不是我們想要的。基本不使用交叉連接

#交叉連接的隱式寫法

select * from tb_student,tb_score; 

select st.id,st.name,sc.score from tb_student st,tb_score sc; 

#內連接

select * from tb_student st [inner] join tb_score sc on st.id=sc.id;

select stu.id,stu.name,sc.chinese,sc.math,sc.english from tb_student st [inner] join tb_score sc on st.id=sc.id;

#join預設就是內連接，inner可以省略不寫

#內連接是以記錄少的為準進行篩選（2張表都要有）。結果集中只有20條記錄，成績為空的10人不要了

#on指定2張表的連接條件（關聯關係），使用on對笛卡爾積中的記錄進行篩選。如果是單張表的條件，比如st.id>10，寫在where中

#查詢2個表中的所有列，只寫一個*,不能寫成*,*的形式, 如果要查詢一個表的所有列、另一個表的部分列，寫成 表1.*,表2.列名 的形式

#等值連接：on中使用等號進行篩選、判斷，故而稱為

#非等值連接：on中使用>、>=、<、<=、between and等連接2張表（不使用等號）

#內連接的隱式寫法

select * from tb_student st,tb_score sc where st.id=sc.id;  #在where中指定連接條件

select st.id,st.name,sc.score from tb_student st,tb_score sc where st.id=sc.id;

###外連接###

select * from tb_student st left [outer] join tb_score sc on st.id=sc.id;

select stu.id,stu.name,sc.chinese,sc.math,sc.english from tb_student st left [outer] join tb_score sc on st.id=sc.id;

#outer可以省略不寫，left是左外連接，right是右外連接，full是全外連接（mysql不支持，oracle支持）

#內連接以少的表為準（主表），左外連接以join左邊的表為準（主表），右連接是以join右邊的表為準（主表），full是2張表都保留（在另一張表中找不到對應|關聯的記錄都會被保留）。

#eg. tb_student left join tb_score，以左邊的為準，結果集有30條記錄，成績為空的學生記錄會保留

#eg. tb_student right join tb_score，以右邊的為準，結果集中有20記錄，成績為空（找不到對應成績）的學生信息不要了

#自然連接

select * from tb_student natural join tb_score;

select st.id,st.name,sc.score from tb_student st natural join tb_score sc;

#自然連接算是一種特殊的等值連接，作用和等值連接相同，但不需要使用on指定連接條件，會自動把2張表中相同的列（列名、數據類型都要相同）作為連接條件

#比如兩張表都有id欄位，會自動加 on 表1.id=表2.id，不需要我們手動加





#多張表，寫多個join就行

select 表1的欄位,表2的欄位,表3的欄位 from 表1 [別名]
left join 表2 [別名] on 表1.id=表2.id
left join 表3 [別名] on 表2.id=表3.id