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);

六、使用正則表達式查詢

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

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、匹配指定字符串

正則表達式可以匹配指定字符串，只要匹配字符串在查詢文本中即可，如要匹配多個字符串，多個字符串之間使用分隔符‘|’隔開。
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}';