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
#多張表的隱式寫法
select 表1的欄位,表2的欄位,表3的欄位 from 表1 [別名]，表2 [別名]，表3 [別名] where 表1.id=表2.id and 表2.id=表3.id
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/74688.html
標籤：MySQL
上一篇：MySQL 鎖的一些簡單概念