今日分享開始啦,請大家多多指教~
1。聯合查詢
定義及資料準備
實際開發中往往資料來自不同的表,所以需要多表聯合查詢。多表查詢是對多張表的資料取笛卡爾積:
關於笛卡爾積透過一個例子理解:
如在a表中有a1,a2,a3,在b表中有b1,b2,b3等資料,因此笛卡爾積就為
a1b1,a1b2,a1b3
a2b1,a2b2,a2b3,
a3b1,a3b2,a3b3
表a中的所有資料都能跟表b的所有資料有所對應;
首先把準備好三張表和其對應的資料:
班級表:
學生表:
課程表:
分數表:
2。內連線
透過兩張表共有的列來篩選資訊,達到儘快查詢的目的。
語法:
select 欄位 from 表1 別名1 [inner] join 表2 別名2 on 連結條件 and 其他條件
select 欄位 from 表1 別名1,表2 別名2 where 連結條件 and 其他條件
2。1 例子1:
查詢許仙的成績;
具體看看整個查詢的過程;
首先直接進行學生表和分數表的連結查詢:
select * from student inner join score;
以及
查詢到的表中共有189行資料,每個人都對應了成績表中的所有成績,也就是說每個人有了21個成績,所以9個學生有189條資料,與上述的笛卡爾積相互對應起來。
接下來要給查詢加上條件:我們知道在score表中,有student_id和course_id,因此要從score表中篩選出sutdnet_id和student中的id相等的分數。
select * from student inner join score on student。id=score。student_id;
此時,我們看到的表中,所有人的成績和本人都對應起來,現在就可以去篩選出許仙了;
select * from student inner join score on student。id=score。student_id and student。id =4;
到這一步其實查詢已經結束了,我們已經獲得了許仙的各科成績了,但是隻有課程id不知道課程的名字。
進行改進:
select stu。sn,stu。name,sco。score,cou。name from student stu inner join score sco on stu。id=sco。student_id
inner join course on score。course_id=course。id and student。id=4;
分析上述程式碼,要查詢的是學生表中的學生sn和name,以及成績表中的score,以及課程表中的name;
首先透過分數表中的學生id和學生表中的id對應查到當前每個學生的成績,然後透過分數表中的課程id在課程表中查詢相對應的課程名;
上述程式碼也可以不使用inner join來查詢:
2。2 查詢所有學生的總成績
重點關注查詢的條件,首先stu。id=sco。student_id,此條件用於查詢屬於每個學生的對應成績。
select stu。sn,stu。name,sum(score) from student stu,score sco where stu。id=sco。student_id;
這樣子查詢到的結果並沒有將學生分開,而是查詢到了單個學生的成績,並且也不是單個學生的總成績,而是將成績表中的所有的成績進行了加和,但是為什麼只顯示了李逵呢,去掉sum再試試。
select stu。sn,stu。name,score from student stu,score sco where stu。id=sco。student_id;
由此可以看出,因為沒有設定對單個學生的查詢條件,顯示結果將所有的score加和到了一起,並且只顯示了第一個學生。
對上述程式碼進行改進:加上分組將每個學生的成績都分開;
3。外連線
外連線分為左外連線和右外連線。如果聯合查詢,左側的表完全顯示我們就說是左外連線;右側的表完全顯示我們就說是右外連線。
首先是左連線,以左表為主表,顯示左表中所有的內容。
select stu。id,stu。name,sco。score from student stu left join score sco on stu。id=sco。student_id group by stu。id;
右連線,以右表為主,顯示右表中有的內容:
select stu。id,stu。name,sco。score from student stu right join score sco on stu。id=sco。student_id group by stu。id;
可以看出左連結中顯示了左表也就是student表的全部內容而右連結,顯示了右表score中的全部內容,null的內容不顯示,如果不指定左右連結,就會顯示兩張表的交集都不為空的情況。
再來看看如何區分左右表:
select stu。id,stu。name,sco。score from score sco left join student stu on stu。id=sco。student_id group by stu。id;
在from之後將score和student的位置進行了互換,可以看出當前結果以score表為主表,顯示其所有內容,null的就不顯示。
4。 自連線
例子:查詢所有計算機原理比java成績高的分數資訊。
首先看看課程表中的資訊:
可以看到:計算機原理的id=3;java的id=1;
因此我們需要在score表中去搜索id=3的成績>id=1的成績;
需要進行自己與自己的內容進行對比,這種方式成為自連結;
自連結的實現:
需要注意的是:我們在實現該次查詢的時候,要注意學生的id需要相等,因為我們查詢的是同一個學生的計算機原理比java成績高的成績資訊。
查詢結果中只顯示了計算機原理的成績,這是由於我們聯合查詢中 select s2。*;條件中查詢的是 s2。course_id=3的課程資訊。
以上查詢可以表示為,查詢s1和s2表中,學生id相等的情況下;表1中的course_id=1和course_id=3且1的成績小於3的成績,並顯示錶二中資訊,
其實就是在對比表1和表2中的資訊,首先篩選出學生id相等的資訊,然後篩選出表2中的courseid=3的成績大於表1中coureseid=1的資訊,並將其顯示出來。
5。子查詢
子查詢是指嵌入在其他sql語句中的select語句,也叫巢狀查詢
例子:查詢許仙的同班同學;
首先得知道許仙在哪個班級;
select * from student where classes_id = (select classes_id from student stu where stu。name=‘許仙’);
多行子查詢:
查詢語文或英文課程的成績資訊:
關鍵字IN和NOT IN
結合上述的巢狀做法,先得知道語文或英文的課程id。
select * from score where course_id in(select id from course where name=‘語文’ or name=‘英語’);
6。合併查詢
在實際應用中,為了合併多個select的執行結果,可以使用集合運算子 union,union all。使用UNION和UNION ALL時,前後查詢的結果集中,欄位需要一致。
重點:查詢的結果格式需要保持一致:
查詢id小於3,或者名字為“英文”的課程:
union all
該運算子用於取得兩個結果集的並集。當使用該運算子時,不會去掉結果集中的重複性。
案例:查詢id小於3,或者名字為“Java”的課程
今日份分享已結束,請大家多多包涵和指點!
如何獲取?
轉發分享此文,後臺私信小編:“1”即可獲取。(注:轉發分享,感謝大家)