快捷導(dǎo)航

SQL查詢語法知識梳理總結(jié)

更新時(shí)間：2021年10月12日 10:27:59 作者：Ezail_xdu

這篇文章主要為大家介紹了關(guān)于SQL查詢語法的知識梳理總結(jié)，文中附含詳細(xì)的查詢語法示例，有需要的朋友可以借鑒參考下，希望能夠有所幫助

基本查詢

SELECT * FROM <表名>

select也可以用作計(jì)算，但不是他的強(qiáng)項(xiàng)，select語句可以用來判斷數(shù)據(jù)庫的連接是否有效例如：許多檢測工具會(huì)執(zhí)行一條SELECT 1;來測試數(shù)據(jù)庫連接。

條件查詢

SELECT * FROM <表名> WHERE <條件表達(dá)式>

條件表達(dá)式可以用<條件1> AND <條件2>表達(dá)滿足條件1并且滿足條件2。

第二種條件是<條件1> OR <條件2>，表示滿足條件1或者滿足條件2。

第三種條件是NOT <條件>，表示“不符合該條件”的記錄。例如，寫一個(gè)“不是2班的學(xué)生”這個(gè)條件，可以先寫出“是2班的學(xué)生”：class_id = 2，再加上NOT：NOT class_id = 2：

上述NOT條件NOT class_id = 2其實(shí)等價(jià)于class_id <> 2，因此，NOT查詢不是很常用。

要組合三個(gè)或者更多的條件，就需要用小括號()表示如何進(jìn)行條件運(yùn)算。例如，編寫一個(gè)復(fù)雜的條件：分?jǐn)?shù)在80以下或者90以上，并且是男生：

SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';

如果不加括號，條件運(yùn)算按照NOT、AND、OR的優(yōu)先級進(jìn)行，即NOT優(yōu)先級最高，其次是AND，最后是OR。加上括號可以改變優(yōu)先級。

常用的條件表達(dá)式

條件	表達(dá)式舉例1	表達(dá)式舉例2	說明
使用=判斷相等	score = 80	name = ‘a(chǎn)bc'	字符串需要用單引號括起來
使用>判斷大于	score > 80	name > ‘a(chǎn)bc'	字符串比較根據(jù)ASCII碼，中文字符比較根據(jù)數(shù)據(jù)庫設(shè)置
使用>=判斷大于或相等	score >= 80	name >= ‘a(chǎn)bc'
使用<判斷小于	score < 80	name <= ‘a(chǎn)bc'
使用<=判斷小于或相等	score <= 80	name <= ‘a(chǎn)bc'
使用<>判斷不相等	score <> 80	name <> ‘a(chǎn)bc'
使用LIKE判斷相似	name LIKE ‘a(chǎn)b%'	name LIKE ‘%bc%'	%表示任意字符，例如'ab%‘將匹配'ab'，‘a(chǎn)bc'，‘a(chǎn)bcd'

投影查詢

如果我們只希望返回某些列的數(shù)據(jù)，而不是所有列的數(shù)據(jù)，我們可以用SELECT 列1, 列2, 列3 FROM ...，讓結(jié)果集僅包含指定列。這種操作稱為投影查詢。

SELECT id, score, name FROM students;

這樣返回的結(jié)果集就只包含了我們指定的列，并且，結(jié)果集的列的順序和原表可以不一樣。

使用SELECT 列1, 列2, 列3 FROM ...時(shí)，還可以給每一列起個(gè)別名，

這樣，結(jié)果集的列名就可以與原表的列名不同。

它的語法是SELECT 列1 別名1, 列2 別名2, 列3 別名3 FROM ...

例如，以下SELECT語句將列名score重命名為points，而id和name列名保持不變：

SELECT id, score points, name FROM students;

投影查詢同樣可以接WHERE條件，實(shí)現(xiàn)復(fù)雜的查詢。

SELECT id, score points, name FROM students WHERE gender = 'M';

排序

我們使用SELECT查詢時(shí)，默認(rèn)查詢結(jié)果集通常是按照id排序的，也就是根據(jù)主鍵排序。這也是大部分?jǐn)?shù)據(jù)庫的做法。如果我們要根據(jù)其他條件排序怎么辦？可以加上ORDER BY子句。例如按照成績從低到高進(jìn)行排序：

SELECT id, name, gender, score FROM students ORDER BY score;

如果要反過來，按照成績從高到底排序，我們可以加上DESC表示“倒序”：

SELECT id, name, gender, score FROM students ORDER BY score DESC;

如果score列有相同的數(shù)據(jù)，要進(jìn)一步排序，可以繼續(xù)添加列名。例如，使用ORDER BY score DESC, gender表示先按score列倒序，如果有相同分?jǐn)?shù)的，再按gender列排序:

SELECT id, name, gender, score FROM students ORDER BY score DESC, gender;

默認(rèn)的排序規(guī)則是ASC：“升序”，即從小到大。ASC可以省略，即ORDER BY score ASC和ORDER BY score效果一樣。

如果有WHERE子句，那么ORDER BY子句要放到WHERE子句后面。例如，查詢一班的學(xué)生成績，并按照倒序排序：

SELECT id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC;

分頁查詢

使用SELECT查詢時(shí)，如果結(jié)果集數(shù)據(jù)量很大，比如幾萬行數(shù)據(jù)，放在一個(gè)頁面顯示的話數(shù)據(jù)量太大，不如分頁顯示，每次顯示100條。

要實(shí)現(xiàn)分頁功能，實(shí)際上就是從結(jié)果集中顯示第1100條記錄作為第1頁，顯示第101200條記錄作為第2頁，以此類推。

因此，分頁實(shí)際上就是從結(jié)果集中“截取”出第M~N條記錄。這個(gè)查詢可以通過LIMIT <M> OFFSET <N>子句實(shí)現(xiàn)。

OFFSET是可選的，如果只寫LIMIT 15，那么相當(dāng)于LIMIT 15 OFFSET 0。

在MySQL中，LIMIT 15 OFFSET 30還可以簡寫成LIMIT 30, 15。

使用LIMIT <M> OFFSET <N>分頁時(shí)，隨著N越來越大，查詢效率也會(huì)越來越低。

SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0;

聚合查詢

如果我們要統(tǒng)計(jì)一張表的數(shù)據(jù)量，例如，想查詢students表一共有多少條記錄，可以使用SQL內(nèi)置的COUNT()函數(shù)查詢：

SELECT COUNT(*) FROM students;

COUNT(*)表示查詢所有列的行數(shù)，要注意聚合的計(jì)算結(jié)果雖然是一個(gè)數(shù)字，但查詢的結(jié)果仍然是一個(gè)二維表，只是這個(gè)二維表只有一行一列，并且列名是COUNT(*)。

通常，使用聚合查詢時(shí)，我們應(yīng)該給列名設(shè)置一個(gè)別名，便于處理結(jié)果：

SELECT COUNT(*) num FROM students;

另外注意，聚合查詢同樣可以使用WHERE條件，因此我們可以方便地統(tǒng)計(jì)出有多少男生、多少女生、多少80分以上的學(xué)生等：

SELECT COUNT(*) boys FROM students WHERE gender = 'M';

除了COUNT()函數(shù)外，SQL還提供了如下聚合函數(shù)：

函數(shù)	說明
SUM	計(jì)算某一列的合計(jì)值，該列必須為數(shù)值類型
AVG	計(jì)算某一列的平均值，該列必須為數(shù)值類型
MAX	計(jì)算某一列的最大值
MIN	計(jì)算某一列的最小值

注意，MAX()和MIN()函數(shù)并不限于數(shù)值類型。如果是字符類型，MAX()和MIN()會(huì)返回排序最后和排序最前的字符。

要統(tǒng)計(jì)男生的平均成績，我們用下面的聚合查詢：

SELECT AVG(score) average FROM students WHERE gender = 'M';

要特別注意：如果聚合查詢的WHERE條件沒有匹配到任何行，COUNT()會(huì)返回0，而SUM()、AVG()、MAX()和MIN()會(huì)返回NULL

分組

如果我們要統(tǒng)計(jì)一班的學(xué)生數(shù)量，我們知道，可以用

SELECT COUNT(*) num FROM students WHERE class_id = 1;

如果要繼續(xù)統(tǒng)計(jì)二班、三班的學(xué)生數(shù)量，難道必須不斷修改WHERE條件來執(zhí)行SELECT語句嗎？

對于聚合查詢，SQL還提供了“分組聚合”的功能。我們觀察下面的聚合查詢：

SELECT COUNT(*) num FROM students GROUP BY class_id;

執(zhí)行這個(gè)查詢，COUNT()的結(jié)果不再是一個(gè)，而是3個(gè)，這是因?yàn)椋?code>GROUP BY子句指定了按class_id分組，因此，執(zhí)行該SELECT語句時(shí)，會(huì)把class_id相同的行先分組，再分別計(jì)算，因此，得到了3行結(jié)果。

但是這3行結(jié)果分別是哪三個(gè)班級的，不好看出來，所以我們可以把class_id列也放入結(jié)果集中：

SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;

但是不可把name放入結(jié)果集，因?yàn)樵谌我庖粋€(gè)分組中，只有class_id都相同，name是不同的，SQL引擎不能把多個(gè)name的值放入一行記錄中。因此，聚合查詢的列中，只能放入分組的列。

也可以使用多個(gè)列進(jìn)行分組。例如，我們想統(tǒng)計(jì)各班的男生和女生人數(shù)：

SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;

多表查詢

SELECT查詢不但可以從一張表查詢數(shù)據(jù)，還可以從多張表同時(shí)查詢數(shù)據(jù)。

查詢多張表的語法是：SELECT * FROM <表1> <表2>。

例如，同時(shí)從students表和classes表的“乘積”，即查詢數(shù)據(jù)，可以這么寫：

SELECT * FROM students, classes;

這種一次查詢兩個(gè)表的數(shù)據(jù)，查詢的結(jié)果也是一個(gè)二維表，它是students表和classes表的“乘積”，即students表的每一行與classes表的每一行都兩兩拼在一起返回。結(jié)果集的列數(shù)是students表和classes表的列數(shù)之和，行數(shù)是students表和classes表的行數(shù)之積。

這種多表查詢又稱笛卡爾查詢，使用笛卡爾查詢時(shí)要非常小心，由于結(jié)果集是目標(biāo)表的行數(shù)乘積，對兩個(gè)各自有100行記錄的表進(jìn)行笛卡爾查詢將返回1萬條記錄，對兩個(gè)各自有1萬行記錄的表進(jìn)行笛卡爾查詢將返回1億條記錄。

你可能還注意到了，上述查詢的結(jié)果集有兩列id和兩列name，兩列id是因?yàn)槠渲幸涣惺?code>students表的id，而另一列是classes表的id，但是在結(jié)果集中，不好區(qū)分。兩列name同理

要解決這個(gè)問題，我們?nèi)匀豢梢岳猛队安樵兊摹霸O(shè)置列的別名”來給兩個(gè)表各自的id和name列起別名：

SELECT
    students.id sid,
    students.name,
    students.gender,
    students.score,
    classes.id cid,
    classes.name cname
FROM students, classes;

注意，多表查詢時(shí)，要使用表名.列名這樣的方式來引用列和設(shè)置別名，這樣就避免了結(jié)果集的列名重復(fù)問題。但是，用表名.列名這種方式列舉兩個(gè)表的所有列實(shí)在是很麻煩，所以SQL還允許給表設(shè)置一個(gè)別名，讓我們在投影查詢中引用起來稍微簡潔一點(diǎn)：

SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c;

多表查詢也是可以添加WHERE條件的。

連接查詢

連接查詢是另一種類型的多表查詢。連接查詢對多個(gè)表進(jìn)行JOIN運(yùn)算，簡單地說，就是先確定一個(gè)主表作為結(jié)果集，然后，把其他表的行有選擇性地“連接”在主表結(jié)果集上。

例如，我們想要選出students表的所有學(xué)生信息：

SELECT s.id, s.name, s.class_id, s.gender, s.score FROM students s;

id	name	class_id	gender	score
1	小明	1	M	90
2	小紅	1	F	95
3	小軍	1	M	88
4	小米	1	F	73
5	小白	2	F	81
6	小兵	2	M	55
7	小林	2	M	85
8	小新	3	F	91
9	小王	3	M	89
10	小麗	3	F	88

但是，假設(shè)我們希望結(jié)果集同時(shí)包含所在班級的名稱，上面的結(jié)果集只有class_id列，缺少對應(yīng)班級的name列。

現(xiàn)在問題來了，存放班級名稱的name列存儲在classes表中，只有根據(jù)students表的class_id，找到classes表對應(yīng)的行，再取出name列，就可以獲得班級名稱。

這時(shí)，連接查詢就派上了用場。我們先使用最常用的一種內(nèi)連接——INNER JOIN來實(shí)現(xiàn)：

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
INNER JOIN classes c
ON s.class_id = c.id;

id	name	class_id	class_name	gender	score
1	小明	1	一班	M	90
2	小紅	1	一班	F	95
3	小軍	1	一班	M	88
4	小米	1	一班	F	73
5	小白	2	二班	F	81
6	小兵	2	二班	M	55
7	小林	2	二班	M	85
8	小新	3	三班	F	91
9	小王	3	三班	M	89
10	小麗	3	三班	F	88

注意INNER JOIN查詢的寫法是：

先確定主表，仍然使用FROM <表1>的語法；再確定需要連接的表，使用INNER JOIN <表2>的語法；然后確定連接條件，使用ON <條件...>，這里的條件是s.class_id = c.id，表示students表的class_id列與classes表的id列相同的行需要連接；可選：加上WHERE子句、ORDER BY等子句。

有內(nèi)連接（INNER JOIN）就有外連接（OUTER JOIN）

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
RIGHT OUTER JOIN classes c
ON s.class_id = c.id;

id	name	class_id	class_name	gender	score
1	小明	1	一班	M	90
2	小紅	1	一班	F	95
3	小軍	1	一班	M	88
4	小米	1	一班	F	73
5	小白	2	二班	F	81
6	小兵	2	二班	M	55
7	小林	2	二班	M	85
8	小新	3	三班	F	91
9	小王	3	三班	M	89
10	小麗	3	三班	F	88
NULL	NULL	NULL	四班	NULL	NULL

執(zhí)行上述RIGHT OUTER JOIN可以看到，和INNER JOIN相比，RIGHT OUTER JOIN多了一行，多出來的一行是“四班”，但是，學(xué)生相關(guān)的列如name、gender、score都為NULL。

這也容易理解，因?yàn)楦鶕?jù)ON條件s.class_id = c.id，classes表的id=4的行正是“四班”，但是，students表中并不存在class_id=4的行。

有RIGHT OUTER JOIN，就有LEFT OUTER JOIN，以及FULL OUTER JOIN。它們的區(qū)別是：

INNER JOIN只返回同時(shí)存在于兩張表的行數(shù)據(jù)，由于students表的class_id包含1，2，3，classes表的id包含1，2，3，4，所以，INNER JOIN根據(jù)條件s.class_id = c.id返回的結(jié)果集僅包含1，2，3。

RIGHT OUTER JOIN返回右表都存在的行。

如果某一行僅在右表存在，那么結(jié)果集就會(huì)以NULL填充剩下的字段。

LEFT OUTER JOIN則返回左表都存在的行。

如果我們給students表增加一列，并添加class_id=5，由于classes表并不存在id=5的列，所以，LEFT OUTER JOIN的結(jié)果會(huì)增加一列，對應(yīng)的class_name是NULL

我們使用FULL OUTER JOIN，它會(huì)把兩張表的所有記錄全部選擇出來，并且，自動(dòng)把對方不存在的列填充為NULL