MySQL資料庫基礎（五）——SQL查詢

標籤：MySQL SQL查詢

MySQL資料庫基礎（五）——SQL查詢一、單表查詢1、查詢所有欄位

在SELECT語句中使用星號“”萬用字元查詢所有欄位
在SELECT語句中指定所有欄位
select from TStudent;

2、查詢指定欄位

查詢多個欄位
select Sname,sex,email from TStudent;

3、查詢指定記錄

在SELECT 語句中通過WHERE子句，對資料進行過濾，文法格式為：
SELECT 欄位名1,欄位名2,…,欄位名n FROM 表名WHERE 查詢條件
select Sname,sex,email,Class from TStudent where class=‘java‘;

4、帶IN關鍵字的查詢

查詢滿足指定範圍內的條件的記錄，使用IN操作符，將所有檢索條件用括弧括起來，檢索條件用逗號分隔開，只要滿足條件範圍內的一個值即為匹配項。
查新姓 王 劉 石的學生
select * from TStudent where left(sname,1) in (‘王‘,‘劉‘,‘石‘);

5、帶BETWEEN AND的範圍查詢

查詢某個範圍內的值，該操作符需要兩個參數，即範圍的開始值和結束值，如果欄位值滿足指定的範圍查詢條件，則這些記錄被返回。
以下查詢條件，查詢學號100到150的學生，包括100和150
select from TStudent where convert(studentid,signed) between 100 and 150
等價於
select from TStudent where convert(studentid,signed)>=100
and convert(studentid,signed)<=150
自動轉換類型
select * from TStudent where studentid between 100 and 150

6、帶LIKE的字元匹配查詢

百分比符號萬用字元‘%’，匹配任意長度的字元，甚至包括零字元
底線萬用字元‘_’，一次只能匹配任意一個字元
尋找姓名中間字為“志”字的學生
select from TStudent where sname like ‘志‘ ;
尋找姓名中有“志”字的學生
select from TStudent where sname like ‘%志%‘;

7、查詢空值

在SELECT語句中使用IS NULL子句，可以查詢某欄位內容為空白記錄。
尋找郵箱是空值的記錄
select * from s where email is null;

8、帶AND的多條件查詢

使用AND串連兩個甚至多個查詢條件，多個條件運算式之間用AND分開。
select * from TStudent where sex=‘男‘ and Class=‘net‘ and studentid>20 and studentid<50;

9、帶OR的多條件查詢

OR操作符，表示只需要滿足其中一個條件的記錄即可返回。OR也可以串連兩個甚至多個查詢條件，多個條件運算式之間用AND分開。
select * from TStudent where sname like ‘%志%‘ or class=‘net‘;

10、查詢結果不重複

在SELECT語句中可以使用DISTINCE關鍵字指示MySQL消除重複的記錄值。
SELECT DISTINCT 欄位名 FROM 表名;
查詢一共有幾個班
select distinct class from TStudent;

11、用LIMIT限制查詢結果的數量

LIMIT關鍵字可以返回指定位置的記錄。
LIMIT [位置位移量,] 行數
返回前10個學生
select from TStudent limit 10;
返回第11-20個學生，位移量是10，就意味著從第11個開始取10條記錄。
select from TStudent limit 10,10;

12、合并查詢結果

利用UNION關鍵字，可以給出多條SELECT語句，並將它們的結果組合成單個結果集。合并時，兩個表對應的列數和資料類型必須相同。各個SELECT語句之間使用UNION或UNION ALL關鍵字分隔。
要求第一個SQL語句返回的列和第二條返回的列數相同，
select studentid,sname from TStudent where studentid<=10
union
select studentid, sname from TStudent where sname like ‘王%‘;

13、為表和欄位取別名

為欄位取別名
MySQL可以指定列別名，替換欄位或運算式。
列名 [AS] 列別名
select studentid as 學號,sname as 姓名,sex as 性別 from TStudent
select studentid 學號,sname 姓名,sex 性別 from TStudent
為表取別名
為了方便操作或者需要多次使用相同的表時，可以為表指定別名，用別名替代表原來的名稱。
表名 [AS] 表別名
select a.studentid 學號,a.sname 姓名,a.sex 性別 from TStudent as a;
select a.studentid 學號,a.sname 姓名,a.sex 性別 from TStudent a;

二、多表串連查詢1、內串連查詢

內串連(INNER JOIN)使用比較子根據每個表共有的列的值匹配兩個表中的行，並列出表中與串連條件相匹配的資料行，組合成新的記錄。在內串連查詢中，只有滿足條件的記錄才能出現在結果關係中。
語句3：隱式的內串連，沒有INNER JOIN，形成的中間表為兩個表的笛卡爾積。
select a.StudentID, a.Sname, b.mark from TStudent a, TScore b where a.StudentID=b.StudentID;
語句4：顯示的內串連，一般稱為內串連，有INNER JOIN，形成的中間表為兩個表經過ON條件過濾後的笛卡爾積。
select a.StudentID, a.Sname, b.mark from TStudent a inner joinTScore b on a.StudentID=b.StudentID;
select a.StudentID,a.Sname,c.subJectName,b.mark from TStudent a join TScore b on a.StudentID=b.StudentID join TSubject c on b.subJectID=c.subJectID;

如果某列只在一張表中，就可以不用指明是哪個表中的列。
select a.StudentID,a.Sname,subJectName,mark from TStudent a join TScore b on a.StudentID=b.StudentID join TSubject c on b.subJectID=c.subJectID;

2、外串連查詢

外串連分為左串連、右串連、全串連。
外串連返回到查詢結果集合中的不僅包含符合串連條件的行，而且還包括左表(左外串連或左串連)、右表(右外串連或右串連)或兩個邊接表(全外串連)中的所有資料行。
外連不但返回符合串連和查詢條件的資料行，還返回不合格一些行。外串連分三類：左外串連（LEFT OUTER JOIN）、右外串連（RIGHT OUTER JOIN）和全外串連（FULL OUTER JOIN）。
三者的共同點是都返回符合串連條件和查詢條件（即：內串連）的資料行。不同點如下：
左外串連還返回左表中不符合串連條件單符合查詢條件的資料行。
右外串連還返回右表中不符合串連條件單符合查詢條件的資料行。
全外串連還返回左表中不符合串連條件單符合查詢條件的資料行，並且還返回右表中不符合串連條件單符合查詢條件的資料行。全外串連實際是上左外串連和右外串連的數學合集（去掉重複），即“全外=左外 UNION 右外”。
左串連
包含左邊表的全部行（不管右邊的表中是否存在與它們匹配的行），以及右邊表中全部匹配的行。
左串連的結果集包括?LEFT OUTER子句中指定的左表的所有行，而不僅僅是串連列所匹配的行。如果左表的某行在右表中沒有匹配行，則在相關聯的結果集行中右表的所有挑選清單列均為空白值。?
select a.StudentID, a.Sname, b.mark from TStudent a left join TScore b on a.StudentID=b.StudentID;

右串連：
右串連包含右邊表的全部行（不管左邊的表中是否存在與它們匹配的行），以及左邊表中全部匹配的行。
右串連是左串連的反向串連。將返回右表的所有行。如果右表的某行在左表中沒有匹配行，則將為左表返回空值。????
select a.StudentID, a.Sname, b.mark from TScore b right join TStudent a on a.StudentID=b.StudentID;

全串連：
全串連返回左表和右表中的所有行。當某行在另一個表中沒有匹配行時，則另一個表的挑選清單列包含空值。如果表之間有匹配行，則整個結果集行包含基表的資料值。MySQL不支援全外串連。可以通過左外和右外求合集來擷取全外串連的查詢結果。
select a.StudentID, a.Sname, b.mark from TStudent a left join
TScore b on a.StudentID=b.StudentID
union
select b.StudentID, c.subJectName, b.mark from TScore b right join
TSubject c on b.subJectID=c.subJectID;

3、交叉串連

交叉串連返回左表中的所有行，左表中的每一行與右表中的所有行組合。交叉串連有顯式的和隱式的，不帶ON子句，返回的是兩表的乘積，也叫笛卡爾積。
FROM子句中的表或視圖可通過內串連或全串連按任意順序指定；但是，用左或右向外串連指定表或視圖時，表或視圖的順序很重要。
隱式交叉串連，沒有cross join
select a.StudentID, a.Sname, b.mark from TStudent a,TScore b where a.StudentID < 4;
顯示交叉串連，有cross join
select a.StudentID, a.Sname, b.mark from TStudent a cross join TScore b where a.StudentID < 4;

4、SQL查詢的原理

第一、單表查詢：根據WHERE條件過濾表中的記錄，形成中間表；然後根據SELECT的選擇列選擇相應的列進行返回最終結果。
第二、兩表串連查詢：對兩表求積（笛卡爾積）並用ON條件和串連連線類型進行過濾形成中間表；然後根據WHERE條件過濾中間表的記錄，並根據SELECT指定的列返回查詢結果。執行個體如下：
select a.StudentID, a.Sname, b.mark from TStudent a left join TScore b on a.StudentID=b.StudentID where a.StudentID < 10;
第三、多表串連查詢：先對第一個和第二個表按照兩表串連做查詢，然後用查詢結果和第三個表做串連查詢，以此類推，直到所有的表都串連上為止，最終形成一個中間的結果表，然後根據WHERE條件過濾中間表的記錄，並根據SELECT指定的列返回查詢結果。

5、過濾條件

ON條件：過濾兩個串連表笛卡爾積形成中間表的約束條件。
WHERE條件：在有ON條件的SELECT語句中是過濾中間表的約束條件。在沒有ON的單表查詢中，是限制物理表或者中間查詢結果返回記錄的約束。在兩表或多表串連中是限制串連形成最終中間表的返回結果的約束。
將WHERE條件移入ON後面是不恰當的。推薦的做法是ON只進行串連操作，WHERE只過濾中間表的記錄。

6、串連查詢的適用情境

串連查詢是SQL查詢的核心，串連查詢的連線類型選擇依據實際需求。如果選擇不當，非但不能提高查詢效率，反而會帶來一些邏輯錯誤或者效能低下。兩表串連查詢選擇方式的依據：
A、查兩表關聯列相等的資料用內串連。
B、Col_L是Col_R的子集時用右串連。
C、Col_R是Col_L的子集時用左串連。
E、 Col_R和Col_L彼此有交集但彼此互不為子集時候用全串連。
F、求差操作的時候用聯集查詢。

三、對查詢結果排序

MySQL中可以通過在SELECT使用ORDER BY子句對查詢的結果進行排序。

1、單列排序

ASC代表結果會以由小往大的順序列出，而?DESC?代表結果會以由大往小的順序列出。預設升序ASC排序。
select from TStudent order by birthday asc;
select from TStudent order by birthday desc;

2、多列排序

可以分別指定排序方向。
select a.StudentID,a.Sname,subJectName,mark from TStudent a join TScore b on a.StudentID=b.StudentID join TSubject c on b.subJectID=c.subJectID where c.subJectID=‘0001‘ order by mark desc,a.studentID desc;

四、分組查詢1、分組查詢簡介

分組查詢是對資料按照某個或多個欄位進行分組。
// 分組查詢格式
SELECT column
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition] // 過濾條件為彙總函式，使用having
[ORDER BY column];
彙總函式只能出現在SELECT列表、HAVING子句和ORDER BY子句中，不能出現在WHERE子句中。如果要限制分組結果，只能使用HAVING子句。
使用group by關鍵字時，在select列表中可以指定的項目是有限制的，select語句中僅允許是被分組的列，或是為每個分組返回一個值的運算式，例如用一個列名作為參數的彙總函式。
Where子句：從資料來源去掉不符合搜尋條件的資料;
GROUP BY子句：分組，使用統計函數（彙總函式）為每組計算統計值;
HAVING子句：在分好的組中去掉每組中不合格資料行。

2、使用彙總函式查詢

COUNT()函數
select class,COUNT(*) from TStudent group by class;
SUM()函數
查詢每個學生總分
select concat(a.StudentID,‘ ‘,a.sname) ss,SUM(b.mark) from TStudent a join TScore b on a.StudentID=b.StudentID group by ss;
AVG()函數
統計每個班平均分
Select class,AVG(mark) from TStudent a join TScore b on a.StudentID=b.StudentID group by class;

3、多欄位分組

統計每班每科平均分，需要按兩列分組class和subJectName
select class,subJectName,AVG(mark) from TStudent a join TScore b on a.StudentID=b.StudentID join TSubject c on b.subJectID=c.subJectID group by class,subJectName;

4、使用HAVING過濾分組

查詢平局分大於80的學生
select concat(a.StudentID,‘ ‘,a.sname) ss,avg(b.mark) m from TStudent a join TScore b on a.StudentID=b.StudentID group by ss having m>80;

5、GROUP BY和ORDER BY使用

尋找平均分大於80分，按平均分排序。
select concat(a.StudentID,‘ ‘,a.sname) ss,avg(b.mark) m from TStudent a join TScore b on a.StudentID=b.StudentID group by ss having m>80 order by m;

6、在GROUP BY子句中使用WITH ROLLUP

使用GROUP BY的WITH ROLLUP子句可以檢索出更多的分組彙總資訊，不僅僅可以檢索出各組的彙總資訊，還能檢索出本組類的整體彙總資訊。
select class,subJectName,AVG(mark) from TStudent a join TScore b on a.StudentID=b.StudentID join TSubject c on b.subJectID=c.subJectID group by class,subJectName with rollup;
能夠統計每班每科的平均成績，每班的平均成績也能統計，全部班級的全部課程平均成績也能統計。

五、子查詢1、帶IN關鍵字的子查詢

IN關鍵字進行子查詢時，內層查詢語句僅僅返回一個資料列，資料列裡的值將提供給外層查詢語句進行比較操作。
select * from TStudent where studentid in (select distinct studentid from TScore where mark>98);

2、帶EXISTS關鍵字的子查詢

EXISTS關鍵字後面的參數是一個任意的子查詢，系統對子查詢進行運算以判斷子查詢是否返回行，如果至少返回一行，那麼EXISTS的結果為true，此時外層查詢語句將進行查詢；如果子查詢沒有返回任何行，那麼EXISTS返回的結果是false，此時外層語句將不進行查詢。
select from TStudent where studentid=‘01001‘ and exists (select from TScore where studentid=‘01001‘);

3、帶ANY、SOME關鍵字的子查詢

ANY和SOME關鍵字是同義字，表示滿足其中任一條件，允許建立一個運算式對子查詢的傳回值列表進行比較，只要滿足內層子查詢中的任何一個比較條件，就返回一個結果作為外層查詢的條件。
select from TStudent where studentid=any (select distinct studentid from TScore where mark>98)
等價於
select from TStudent where studentid=some (select distinct studentid from TScore where mark>98);
等價於
select from TStudent where studentid in (select distinct studentid from TScore where mark>98);
子查詢時還可以使用其他的比較子，如<、<=、=、>=和!=等。
以下SQL語句子查詢查出考試成績大於98的學生的studentid，比如查出的結果有三個‘00010’，‘00021’，‘00061’，外查詢將會查詢比00010學號大的學生。
select from TStudent where studentid>some (select distinct studentid from TScore where mark>98)
以下SQL語句子查詢查出考試成績大於98的學生的studentid，比如查出的結果有三個‘00010’，‘00021’，‘00061’，外查詢將會查詢比00061學號小的學生。
select * from TStudent where studentid<some (select distinct studentid from TScore where mark>98);

4、帶ALL關鍵字的子查詢

ALL關鍵字與ANY和SOME不同，使用ALL時需要同時滿足所有內層查詢的條件。
以下SQL語句子查詢查出考試成績大於98的學生的studentid，比如查出的結果有三個‘00010’，‘00021’，‘00061’，外查詢將會查詢比00010學號小的學生。
select * from TStudent where studentid<all (select distinct studentid from TScore where mark>98)
以下SQL語句子查詢查出考試成績大於98的學生的studentid，比如查出的結果有三個‘00010’，‘00021’，‘00061’，外查詢將會查詢比00061學號大的學生。
select * from TStudent where studentid>all (select distinct studentid from TScore where mark>98);

六、使用Regex查詢

Regex作用是匹配文本，將一個模式（Regex）與一個文本串進行比較。MySQL用WHERE子句對Regex提供了初步的支援，允許指定用Regex過濾SELECT檢索出的資料。
在SQL查詢語句中，查詢條件REGEXP後所跟的東西作為Regex處理。

1、查詢以特定字元或字串開頭的記錄

字元‘^’匹配以特定字元或者字串開頭的文本。
select * from TStudent where sname regexp ‘^劉平‘;

2、查詢以特定字元或字串結尾的記錄

字元‘$’匹配以特定字元或者字串結尾的文本。
select * from TStudent where cardid regexp ‘36$‘;

3、用符號"."來替代字串中的任意一個字元

字元‘.’匹配任意一個字元。
select * from TStudent where sname regexp ‘.康.‘;

4、使用"*"和"+"來匹配多個字元

星號‘’匹配前面的字元任意多次，包括0次。
加號‘+’匹配前面的字元至少一次。
找出×××以19開始，以6結束的學生
select from TStudent where cardid regexp ‘^19.6$‘
找出×××號中有123的學生
select from TStudent where cardid regexp ‘.123+.‘;

5、匹配指定字串

Regex可以匹配指定字串，只要匹配字串在查詢文本中即可，如要匹配多個字串，多個字串之間使用分隔字元‘|’隔開。
select * from TStudent where sname regexp ‘武|尹|羅‘;

6、匹配指定字元中的任意一個

方括弧“[]”指定一個字元集合，只匹配其中任何一個字元，即為所尋找的文本。不支援漢字。
select from TStudent where email regexp ‘[w-z]‘;
select from TStudent where cardid regexp ‘^[1-3,7]‘;

7、匹配指定字元以外的字元

“[^字元集合]”匹配不在指定集合中的任何字元。
select * from TStudent where cardid regexp ‘^[^1-7]‘;

8、使用{M}或者{M,N}來指定字串連續出現的次數

“字串{n,}”表示至少匹配n次前面的字元。“字串{n,m}”表示匹配前面的字串不少於n次，不多於m次。
尋找×××中出現138並且後面有8位0-9的數位學生。
select * from TStudent where cardid regexp ‘138[0-9]{15}‘;

MySQL資料庫基礎（五）——SQL查詢