當(dāng)前位置：首頁(yè) > 运维知识 > 数据库 >内容正文

数据库

MySQL数据库基础（五）——SQL查询

發(fā)布時(shí)間：2023/12/9 数据库 32 豆豆

生活随笔收集整理的這篇文章主要介紹了 MySQL数据库基础（五）——SQL查询小編覺得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

MySQL數(shù)據(jù)庫(kù)基礎(chǔ)（五）——SQL查詢

一、單表查詢

1、查詢所有字段

在SELECT語(yǔ)句中使用星號(hào)“”通配符查詢所有字段
在SELECT語(yǔ)句中指定所有字段
select from TStudent;

2、查詢指定字段

查詢多個(gè)字段
select Sname,sex,email from TStudent;

3、查詢指定記錄

在SELECT 語(yǔ)句中通過WHERE子句，對(duì)數(shù)據(jù)進(jìn)行過濾，語(yǔ)法格式為：
SELECT 字段名1,字段名2,…,字段名n FROM 表名WHERE 查詢條件
select Sname,sex,email,Class from TStudent where class='java';

4、帶IN關(guān)鍵字的查詢

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

5、帶BETWEEN AND的范圍查詢

查詢某個(gè)范圍內(nèi)的值，該操作符需要兩個(gè)參數(shù)，即范圍的開始值和結(jié)束值，如果字段值滿足指定的范圍查詢條件，則這些記錄被返回。
以下查詢條件，查詢學(xué)號(hào)100到150的學(xué)生，包括100和150
select from TStudent where convert(studentid,signed) between 100 and 150
等價(jià)于
select from TStudent where convert(studentid,signed)>=100
and convert(studentid,signed)<=150
自動(dòng)轉(zhuǎn)換類型
select * from TStudent where studentid between 100 and 150

6、帶LIKE的字符匹配查詢

百分號(hào)通配符‘%’，匹配任意長(zhǎng)度的字符，甚至包括零字符
下劃線通配符‘_’，一次只能匹配任意一個(gè)字符
查找姓名中間字為“志”字的學(xué)生
select from TStudent where sname like '志' ;
查找姓名中有“志”字的學(xué)生
select from TStudent where sname like '%志%';

7、查詢空值

在SELECT語(yǔ)句中使用IS NULL子句，可以查詢某字段內(nèi)容為空記錄。
查找郵箱是空值的記錄
select * from s where email is null;

8、帶AND的多條件查詢

使用AND連接兩個(gè)甚至多個(gè)查詢條件，多個(gè)條件表達(dá)式之間用AND分開。
select * from TStudent where sex='男' and Class='net' and studentid>20 and studentid<50;

9、帶OR的多條件查詢

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

10、查詢結(jié)果不重復(fù)

在SELECT語(yǔ)句中可以使用DISTINCE關(guān)鍵字指示MySQL消除重復(fù)的記錄值。
SELECT DISTINCT 字段名 FROM 表名;
查詢一共有幾個(gè)班
select distinct class from TStudent;

11、用LIMIT限制查詢結(jié)果的數(shù)量

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

12、合并查詢結(jié)果

利用UNION關(guān)鍵字，可以給出多條SELECT語(yǔ)句，并將它們的結(jié)果組合成單個(gè)結(jié)果集。合并時(shí)，兩個(gè)表對(duì)應(yīng)的列數(shù)和數(shù)據(jù)類型必須相同。各個(gè)SELECT語(yǔ)句之間使用UNION或UNION ALL關(guān)鍵字分隔。
要求第一個(gè)SQL語(yǔ)句返回的列和第二條返回的列數(shù)相同，
select studentid,sname from TStudent where studentid<=10
union
select studentid, sname from TStudent where sname like '王%';

13、為表和字段取別名

為字段取別名
MySQL可以指定列別名，替換字段或表達(dá)式。
列名 [AS] 列別名
select studentid as 學(xué)號(hào),sname as 姓名,sex as 性別 from TStudent
select studentid 學(xué)號(hào),sname 姓名,sex 性別 from TStudent
為表取別名
為了方便操作或者需要多次使用相同的表時(shí)，可以為表指定別名，用別名替代表原來的名稱。
表名 [AS] 表別名
select a.studentid 學(xué)號(hào),a.sname 姓名,a.sex 性別 from TStudent as a;
select a.studentid 學(xué)號(hào),a.sname 姓名,a.sex 性別 from TStudent a;

二、多表連接查詢

1、內(nèi)連接查詢

內(nèi)連接(INNER JOIN)使用比較運(yùn)算符根據(jù)每個(gè)表共有的列的值匹配兩個(gè)表中的行，并列出表中與連接條件相匹配的數(shù)據(jù)行，組合成新的記錄。在內(nèi)連接查詢中，只有滿足條件的記錄才能出現(xiàn)在結(jié)果關(guān)系中。
語(yǔ)句3：隱式的內(nèi)連接，沒有INNER JOIN，形成的中間表為兩個(gè)表的笛卡爾積。
select a.StudentID, a.Sname, b.mark from TStudent a, TScore b where a.StudentID=b.StudentID;
語(yǔ)句4：顯示的內(nèi)連接，一般稱為內(nèi)連接，有INNER JOIN，形成的中間表為兩個(gè)表經(jīng)過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;

如果某列只在一張表中，就可以不用指明是哪個(gè)表中的列。
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、外連接查詢

外連接分為左連接、右連接、全連接。
外連接返回到查詢結(jié)果集合中的不僅包含符合連接條件的行，而且還包括左表(左外連接或左連接)、右表(右外連接或右連接)或兩個(gè)邊接表(全外連接)中的所有數(shù)據(jù)行。
外連不但返回符合連接和查詢條件的數(shù)據(jù)行，還返回不符合條件的一些行。外連接分三類：左外連接（LEFT OUTER JOIN）、右外連接（RIGHT OUTER JOIN）和全外連接（FULL OUTER JOIN）。
三者的共同點(diǎn)是都返回符合連接條件和查詢條件（即：內(nèi)連接）的數(shù)據(jù)行。不同點(diǎn)如下：
左外連接還返回左表中不符合連接條件單符合查詢條件的數(shù)據(jù)行。
右外連接還返回右表中不符合連接條件單符合查詢條件的數(shù)據(jù)行。
全外連接還返回左表中不符合連接條件單符合查詢條件的數(shù)據(jù)行，并且還返回右表中不符合連接條件單符合查詢條件的數(shù)據(jù)行。全外連接實(shí)際是上左外連接和右外連接的數(shù)學(xué)合集（去掉重復(fù)），即“全外=左外 UNION 右外”。
左連接
包含左邊表的全部行（不管右邊的表中是否存在與它們匹配的行），以及右邊表中全部匹配的行。
左連接的結(jié)果集包括?LEFT OUTER子句中指定的左表的所有行，而不僅僅是連接列所匹配的行。如果左表的某行在右表中沒有匹配行，則在相關(guān)聯(lián)的結(jié)果集行中右表的所有選擇列表列均為空值。?
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;

全連接：
全連接返回左表和右表中的所有行。當(dāng)某行在另一個(gè)表中沒有匹配行時(shí)，則另一個(gè)表的選擇列表列包含空值。如果表之間有匹配行，則整個(gè)結(jié)果集行包含基表的數(shù)據(jù)值。MySQL不支持全外連接。可以通過左外和右外求合集來獲取全外連接的查詢結(jié)果。
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子句中的表或視圖可通過內(nèi)連接或全連接按任意順序指定；但是，用左或右向外連接指定表或視圖時(shí)，表或視圖的順序很重要。
隱式交叉連接，沒有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查詢的原理

第一、單表查詢：根據(jù)WHERE條件過濾表中的記錄，形成中間表；然后根據(jù)SELECT的選擇列選擇相應(yīng)的列進(jìn)行返回最終結(jié)果。
第二、兩表連接查詢：對(duì)兩表求積（笛卡爾積）并用ON條件和連接連接類型進(jìn)行過濾形成中間表；然后根據(jù)WHERE條件過濾中間表的記錄，并根據(jù)SELECT指定的列返回查詢結(jié)果。實(shí)例如下：
select a.StudentID, a.Sname, b.mark from TStudent a left join TScore b on a.StudentID=b.StudentID where a.StudentID < 10;
第三、多表連接查詢：先對(duì)第一個(gè)和第二個(gè)表按照兩表連接做查詢，然后用查詢結(jié)果和第三個(gè)表做連接查詢，以此類推，直到所有的表都連接上為止，最終形成一個(gè)中間的結(jié)果表，然后根據(jù)WHERE條件過濾中間表的記錄，并根據(jù)SELECT指定的列返回查詢結(jié)果。

5、過濾條件

ON條件：過濾兩個(gè)連接表笛卡爾積形成中間表的約束條件。
WHERE條件：在有ON條件的SELECT語(yǔ)句中是過濾中間表的約束條件。在沒有ON的單表查詢中，是限制物理表或者中間查詢結(jié)果返回記錄的約束。在兩表或多表連接中是限制連接形成最終中間表的返回結(jié)果的約束。
將WHERE條件移入ON后面是不恰當(dāng)?shù)摹Ｍ扑]的做法是ON只進(jìn)行連接操作，WHERE只過濾中間表的記錄。

6、連接查詢的適用場(chǎng)景

連接查詢是SQL查詢的核心，連接查詢的連接類型選擇依據(jù)實(shí)際需求。如果選擇不當(dāng)，非但不能提高查詢效率，反而會(huì)帶來一些邏輯錯(cuò)誤或者性能低下。兩表連接查詢選擇方式的依據(jù)：
A、查兩表關(guān)聯(lián)列相等的數(shù)據(jù)用內(nèi)連接。
B、Col_L是Col_R的子集時(shí)用右連接。
C、Col_R是Col_L的子集時(shí)用左連接。
E、 Col_R和Col_L彼此有交集但彼此互不為子集時(shí)候用全連接。
F、求差操作的時(shí)候用聯(lián)合查詢。

三、對(duì)查詢結(jié)果排序

MySQL中可以通過在SELECT使用ORDER BY子句對(duì)查詢的結(jié)果進(jìn)行排序。

1、單列排序

ASC代表結(jié)果會(huì)以由小往大的順序列出，而?DESC?代表結(jié)果會(huì)以由大往小的順序列出。默認(rèn)升序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、分組查詢簡(jiǎn)介

分組查詢是對(duì)數(shù)據(jù)按照某個(gè)或多個(gè)字段進(jìn)行分組。
// 分組查詢格式
SELECT column
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition] // 過濾條件為聚合函數(shù)，使用having
[ORDER BY column];
聚合函數(shù)只能出現(xiàn)在SELECT列表、HAVING子句和ORDER BY子句中，不能出現(xiàn)在WHERE子句中。如果要限制分組結(jié)果，只能使用HAVING子句。
使用group by關(guān)鍵字時(shí)，在select列表中可以指定的項(xiàng)目是有限制的，select語(yǔ)句中僅允許是被分組的列，或是為每個(gè)分組返回一個(gè)值的表達(dá)式，例如用一個(gè)列名作為參數(shù)的聚合函數(shù)。
Where子句：從數(shù)據(jù)源去掉不符合搜索條件的數(shù)據(jù);
GROUP BY子句：分組，使用統(tǒng)計(jì)函數(shù)（聚合函數(shù)）為每組計(jì)算統(tǒng)計(jì)值;
HAVING子句：在分好的組中去掉每組中不符合條件的數(shù)據(jù)行。

2、使用聚合函數(shù)查詢

COUNT()函數(shù)
select class,COUNT(*) from TStudent group by class;
SUM()函數(shù)
查詢每個(gè)學(xué)生總分
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()函數(shù)
統(tǒng)計(jì)每個(gè)班平均分
Select class,AVG(mark) from TStudent a join TScore b on a.StudentID=b.StudentID group by class;

3、多字段分組

統(tǒng)計(jì)每班每科平均分，需要按兩列分組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的學(xué)生
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;
能夠統(tǒng)計(jì)每班每科的平均成績(jī)，每班的平均成績(jī)也能統(tǒng)計(jì)，全部班級(jí)的全部課程平均成績(jī)也能統(tǒng)計(jì)。

五、子查詢

1、帶IN關(guān)鍵字的子查詢

IN關(guān)鍵字進(jìn)行子查詢時(shí)，內(nèi)層查詢語(yǔ)句僅僅返回一個(gè)數(shù)據(jù)列，數(shù)據(jù)列里的值將提供給外層查詢語(yǔ)句進(jìn)行比較操作。
select * from TStudent where studentid in (select distinct studentid from TScore where mark>98);

2、帶EXISTS關(guān)鍵字的子查詢

EXISTS關(guān)鍵字后面的參數(shù)是一個(gè)任意的子查詢，系統(tǒng)對(duì)子查詢進(jìn)行運(yùn)算以判斷子查詢是否返回行，如果至少返回一行，那么EXISTS的結(jié)果為true，此時(shí)外層查詢語(yǔ)句將進(jìn)行查詢；如果子查詢沒有返回任何行，那么EXISTS返回的結(jié)果是false，此時(shí)外層語(yǔ)句將不進(jìn)行查詢。
select from TStudent where studentid='01001' and exists (select from TScore where studentid='01001');

3、帶ANY、SOME關(guān)鍵字的子查詢

ANY和SOME關(guān)鍵字是同義詞，表示滿足其中任一條件，允許創(chuàng)建一個(gè)表達(dá)式對(duì)子查詢的返回值列表進(jìn)行比較，只要滿足內(nèi)層子查詢中的任何一個(gè)比較條件，就返回一個(gè)結(jié)果作為外層查詢的條件。
select from TStudent where studentid=any (select distinct studentid from TScore where mark>98)
等價(jià)于
select from TStudent where studentid=some (select distinct studentid from TScore where mark>98);
等價(jià)于
select from TStudent where studentid in (select distinct studentid from TScore where mark>98);
子查詢時(shí)還可以使用其他的比較運(yùn)算符，如<、<=、=、>=和!=等。
以下SQL語(yǔ)句子查詢查出考試成績(jī)大于98的學(xué)生的studentid，比如查出的結(jié)果有三個(gè)‘00010’，‘00021’，‘00061’，外查詢將會(huì)查詢比00010學(xué)號(hào)大的學(xué)生。
select from TStudent where studentid>some (select distinct studentid from TScore where mark>98)
以下SQL語(yǔ)句子查詢查出考試成績(jī)大于98的學(xué)生的studentid，比如查出的結(jié)果有三個(gè)‘00010’，‘00021’，‘00061’，外查詢將會(huì)查詢比00061學(xué)號(hào)小的學(xué)生。
select * from TStudent where studentid<some (select distinct studentid from TScore where mark>98);

4、帶ALL關(guān)鍵字的子查詢

ALL關(guān)鍵字與ANY和SOME不同，使用ALL時(shí)需要同時(shí)滿足所有內(nèi)層查詢的條件。
以下SQL語(yǔ)句子查詢查出考試成績(jī)大于98的學(xué)生的studentid，比如查出的結(jié)果有三個(gè)‘00010’，‘00021’，‘00061’，外查詢將會(huì)查詢比00010學(xué)號(hào)小的學(xué)生。
select * from TStudent where studentid<all (select distinct studentid from TScore where mark>98)
以下SQL語(yǔ)句子查詢查出考試成績(jī)大于98的學(xué)生的studentid，比如查出的結(jié)果有三個(gè)‘00010’，‘00021’，‘00061’，外查詢將會(huì)查詢比00061學(xué)號(hào)大的學(xué)生。
select * from TStudent where studentid>all (select distinct studentid from TScore where mark>98);

六、使用正則表達(dá)式查詢

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

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

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

2、查詢以特定字符或字符串結(jié)尾的記錄

字符‘$’匹配以特定字符或者字符串結(jié)尾的文本。
select * from TStudent where cardid regexp '36$';

3、用符號(hào)"."來替代字符串中的任意一個(gè)字符

字符‘.’匹配任意一個(gè)字符。
select * from TStudent where sname regexp '.康.';

4、使用"*"和"+"來匹配多個(gè)字符

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

5、匹配指定字符串

正則表達(dá)式可以匹配指定字符串，只要匹配字符串在查詢文本中即可，如要匹配多個(gè)字符串，多個(gè)字符串之間使用分隔符‘|’隔開。
select * from TStudent where sname regexp '武|尹|羅';

6、匹配指定字符中的任意一個(gè)

方括號(hào)“[]”指定一個(gè)字符集合，只匹配其中任何一個(gè)字符，即為所查找的文本。不支持漢字。
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}來指定字符串連續(xù)出現(xiàn)的次數(shù)

“字符串{n,}”表示至少匹配n次前面的字符。“字符串{n,m}”表示匹配前面的字符串不少于n次，不多于m次。
查找×××中出現(xiàn)138并且后面有8位0-9的數(shù)字的學(xué)生。
select * from TStudent where cardid regexp '138[0-9]{15}';

轉(zhuǎn)載于:https://blog.51cto.com/9291927/2092195

總結(jié)

以上是生活随笔為你收集整理的MySQL数据库基础（五）——SQL查询的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。

上一篇： React开发（214）:React中的
下一篇： React开发（152）:注意替换路径