MySQL從刪庫到跑路(五)——SQL查詢

来源:https://www.cnblogs.com/cxydczzl/archive/2018/11/01/9888338.html
-Advertisement-
Play Games

作者:天山老妖S 鏈接:http://blog.51cto.com/9291927 1、查詢所有欄位 在SELECT語句中使用星號“”通配符查詢所有欄位在SELECT語句中指定所有欄位select from TStudent; 2、查詢指定欄位 查詢多個欄位select Sname,sex,emai ...


作者:天山老妖S

鏈接:http://blog.51cto.com/9291927

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&gt;20 and studentid&lt;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&lt;some (select distinct studentid from TScore where mark&gt;98);

4、帶ALL關鍵字的子查詢

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

 

喜歡的小伙伴們可以搜索我們個人的微信公眾號“程式員的成長之路”點擊關註或掃描下方二維碼


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 小活中用到mssql,於是在自己lnmp環境中安裝各mssql資料庫 步驟如下: 源碼編譯安裝 (1)下載freetds-stable-0.91源碼:http://download.csdn.net/download/xhu_eternalcc/7457555(花了4積分,沒有積分的找我要,微信zm ...
  • 背景:生產環境SQL語句查詢過慢(數據總量在350萬左右),日誌中心一直報警 解決過程:分析無果後,求助於公司的DBA,DBA分析後建議在語句中指定索引 解決:在SQL語句中指定索引,效果相當明顯,親測有效 優化前SQL: 優化後SQL(指定使用索引IX_CityId): 註意事項:使用指定索引後, ...
  • 一直以來打算自己做一個博客網站,前段時間開始準備做了,正好碰上新睿雲伺服器免費一年的活動,趕緊拿下。裝好了sqlserver ,用本地訪問沒有問題,但是關鍵是外網訪問一直不行找了好多資料最終才搞定。下麵我把解決方案全部列出來供大家一個參考 右鍵點擊資料庫,屬性中選擇連接,勾上下麵的允許遠程連接到此服 ...
  • ①問題:遇到一個很有意思的問題,這裡記錄一下, 就是在使用max函數的時候發現取得的最大值其實不是最大值. 比如: 某一列中有10000000,和9999999, 其最大值應該是10000000但是查到的值是9999999, ②原因:因為字元串類型大小比較是先比較首字元,然後依次往後進行比較 ③解決 ...
  • 作者:天山老妖S 鏈接:http://blog.51cto.com/9291927 一、插入數據 1、為表的所有欄位插入數據 使用基本的INSERT語句插入數據要求指定表名稱和插入到新記錄的值。 2、為表的指定欄位插入數據 為表的指定欄位插入數據,就是在INSERT語句中只向部分欄位中插入值,而其他 ...
  • Java HotSpot(TM) 64-Bit Server VM warning: INFO: os::commit_memory(0x0000000085330000, 2060255232, 0) failed; error='Cannot allocate memory' (errno=12 ...
  • 通過binlog查看資料庫最近都執行了哪些查詢(show binlog events) ...
  • 最近來有位同學前來咨詢,他留言說:很喜歡大數據,也看好大數據的前景,但是自己沒有Java基礎,不確信自己有沒有能力學好大數據,自信心嚴重不足。其實,這種情況並不少見,很多同學都在大數據大門前徘徊,承受各種困擾折磨,而顧慮中的第一攔路虎便是Java。所以好程式員今日就和大家分析下,學習大數據一定要學J ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發(一):使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說,近來想嘗試開發一款安卓APP,考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP,畢竟是使用Visual Studio開發工具,使用起來也比較的順手,結合微軟官方的教程進行了安卓 ...
  • 前言 QuestPDF 是一個開源 .NET 庫,用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫,它徹底改變了我們生成 PDF 文檔的方 ...
  • 項目地址 項目後端地址: https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址: ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址: http://tree ...
  • 話不多說,直接開乾 一.下載 1.官方鏈接下載: https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe,運行開始下載SQL server; 二. ...
  • 前言 隨著物聯網(IoT)技術的迅猛發展,MQTT(消息隊列遙測傳輸)協議憑藉其輕量級和高效性,已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫,為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog支持多種接收器用於日誌存儲,增強器用於添加屬性,LogContext管理動態屬性,支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項,適用於不同需求。 ...
  • 目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據,爬取信息時需要模擬瀏覽器行為,否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下: 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • 1.前言 什麼是熱更新 游戲或者軟體更新時,無需重新下載客戶端進行安裝,而是在應用程式啟動的情況下,在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR,Xlua,ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles,Addressable, ...
  • 本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺,其實像阿裡雲,騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後,它會送 ...
  • 通過以下方式可以高效,並保證數據同步的可靠性 1.API設計 使用RESTful設計,確保API端點明確,並使用適當的HTTP方法(如POST用於創建,PUT用於更新)。 設計清晰的請求和響應模型,以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證,確保接收到的數據符合預期格 ...