本文內容: 連接查詢 聯合查詢 子查詢 from子查詢 where子查詢 exists子查詢 首發日期:2018-04-11 連接查詢: 連接查詢就是將多個表聯合起來查詢,連接查詢方式有內連接、外連接、自然連接、交叉連接。連接查詢使得可以同時查看多張表中數據。 內連接:有條件連接,多個表之間依據指定... ...
本文內容:
- 連接查詢
- 聯合查詢
- 子查詢
- from子查詢
- where子查詢
- exists子查詢
首發日期:2018-04-11
連接查詢:
- 連接查詢就是將多個表聯合起來查詢,連接查詢方式有內連接、外連接、自然連接、交叉連接。連接查詢使得可以同時查看多張表中數據。
- 內連接:有條件連接,多個表之間依據指定條件連接,匹配結果是保留符合匹配結果的記錄。
- 外連接:與內連接不同的是不管匹配符不符合都保留,根據外連接連接方式來決定保留哪張表,比如保留左表的話,那麼左表無法匹配右表時,保留左表數據,然後置右表欄位數據為null.
- 自然連接:有條件連接,自動依據“同名欄位”連接(多個同名欄位就都作為條件)。
- 交叉連接cross join:無條件連接,將每一條記錄與另外一個表的每一條記錄連接(笛卡爾積),結果是欄位數等於原來欄位數之和,記錄數等於之前各個表記錄數之乘積。
-- 實驗表結構 create table student( id int, name varchar(15), gender varchar(15), cid int ); create table class( cid int, cname varchar(15) ); drop table student,class; -- 實驗表數據: insert into student values(1,"lilei","male",1),(2,"hanmeimei","male",2),(3,"jack","male",1),(4,"alice","female",4); --這裡特意創建了一個class中沒有的4 insert into class values(1,"linux"),(2,"python"),(3,"java"),(5,"html5");--這裡特意創建了一個student中沒有的5 select * from student; select * from class;
內連接:
- 從左表中取出每一條記錄,去右表中與所有的記錄進行匹配,保留匹配成功的記錄,並將兩份記錄拼接。
- 語法:select 欄位列表 from 左表 [inner] join 右表 on 左表.欄位 = 右表.欄位;
- 不使用on條件的時候,結果與交叉連接相同
-- 內連接 -- select * from student inner join class; --結果與交叉連接相同 select * from student join class on student.cid = class.cid; select * from student inner join class on student.cid = class.cid;
外連接:
- 與內連接不同的是主表記錄不管匹配符不符合都保留,方式有左外連接、右外連接,左外連接是保留左表,右外連接是保留右表
- 語法:
- 左外連接:select 欄位列表 from 左表 left join 右表 on 左表.欄位 = 右表.欄位;
- 右外連接:select 欄位列表 from 左表 right join 右表 on 左表.欄位 = 右表.欄位;
select * from student left join class on student.cid = class.cid;
select * from student right join class on student.cid = class.cid;
自然連接:
- 自動匹配連接條件,系統以欄位名字作為匹配模式(同名欄位就作為條件,多個同名欄位就都作為條件)
- 自然內連接:類似內連接,但不提供連接條件。
- 自然外連接:類似外連接,但不提供連接條件。
- 語法:
- 自然內連接:select 欄位列表 from 表名 natural join 表名;
- 自然外連接:select 欄位列表 from 表名 natural left\right join 表名;
select * from student natural join class;
select * from student natural left join class;
交叉連接:
- 將每一條記錄與另外一個表的每一條記錄連接
- 語法:
- select 欄位列表 from 表名 cross join 表名;
- select 欄位列表 from 表名 ,表名;
select * from student cross join class; select * from student,class;
補充:
- 在多個表中,為了區分每個表,以及簡便使用,可以使用表別名。
select * from student inner join class on student.cid = class.cid;-- 原本結果 select id,name,gender,c.cid,cname from student as s inner join class as c on s.cid = c.cid;-- 使用表別名
聯合查詢:
- 聯合查詢是將多個查詢結果在記錄上進行拼接。(相當於將其他表的查詢記錄結果連接到第一個表的後面)【因為是拼接,所以多個查詢結果的欄位數必須相同】【拼接不在意數據類型,比如第一個表的第一個欄位是int,但後面的表中的varchar依然可以拼接到第一列中】
- 語法:select語句 union select語句…;
select name,gender from student union select * from class; -- 因為class就兩個欄位,所以第一個只選出兩個欄位
補充:
- union可以有選項,加在union 後面,all選項是不對相同去重,distinct是去重。
- 聯合查詢一般用來處理同一表中以不同方法顯示不同數據。(比如,想將學生各科成績表中的兩科(這裡假設為python 跟linux)同時查詢出來)
- union和order by同時使用報錯時
- 如果你想針對聯合查詢中的某個查詢結果排序,需要將這個select語句用括弧括起來。【另外,由於聯合查詢的拼接機制,需要在order by 後面加上limit子句才行,limit的數量可以是一個很大的值。】
- 如果是針對最終的聯合查詢結果,就在最後一個select語句中使用order by 【建議給最後一個欄位加上括弧,再加ordery by,使用情況是有同名欄位】
子查詢:
- 子查詢是嵌套在查詢語句中的查詢。
- 子查詢按照出現的位置可以分為三類:
- from子查詢:子查詢跟在from之後的;一般用在“先查出二維表,再處理”的情況。
- 比如:
-- 這是一個無意義的例子。僅為舉例使用 select cid,cname from (select * from class where cname="python") as c;
- where子查詢:子查詢跟在where條件中;一般用在“先查詢出指定條件再查詢”的情況
- 比如:
select * from student where cid=(select cid from class where cname="python");
- exist子查詢:子查詢在exist語句裡面;一般用作“存在才做”的情況
- 比如:
-- 這是一個如果學生沒有選擇cid=1的課,那麼不輸出對應課程信息的例子 select * from class where exists(select * from student where cid=1) and cid=1;
- 事實上,有些人認為union之後跟著的也是子查詢,不過這裡不把這些當作子查詢,只把上面幾個與“查詢”非常緊密相關的當作子查詢。
補充:
- 事實上,還可以根據結果來分類子查詢:
- 標量子查詢,子查詢得到的結果是一行一列,情況一般發生於where子查詢只查詢出一行一列的情況。
- 列子查詢,子查詢得到的結果是一列多行,情況一般發生於where子查詢查詢出一列多行的情況。
- 行子查詢,子查詢得到的結果是多列一行(或者多行多列) ,情況一般發生在where子查詢查詢出多列一行的情況。
- 表子查詢,子查詢得到的結果是多行多列 ,情況一般發生在from子查詢中
- where子查詢中有時候還使用一些其他關鍵字,如any,all,some,但對=來說,=基本可以實現他們的功能了。