練習鏈接
1 查詢所有數(shù)據(jù)
查詢students表的所有數(shù)據(jù)
SELECT * FROM students;
SELECT可以用作計算，直接計算出表達式的結(jié)果，但它并不是SQL的強項。
但是不帶FROM子句的SELECT語句，可用來判斷當前到數(shù)據(jù)庫的連接是否有效。
許多檢測工具會執(zhí)行一條SELECT 1，來測試數(shù)據(jù)庫連接。
2 條件查詢
1）查詢分數(shù)在80分以上的學生記錄。
SELECT * FROM students WHERE score >= 80;
2）符合條件“分數(shù)在80分或以上”，并且還符合條件“男生”
SELECT * FROM students WHERE score >= 80 AND gender = 'M';
3）符合條件“分數(shù)在80分或以上”或者“男生”
SELECT * FROM students WHERE score >= 80 OR gender = 'M';
4）符合條件“不是2班的學生”，NOT查詢不是很常用
SELECT * FROM students WHERE NOT class_id = 2;
5）分數(shù)在80以下或者90以上，并且是男生
SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';
NOT優(yōu)先級最高，其次是AND，最后是OR。加上括號可以改變優(yōu)先級。
3 投影查詢
1）只希望返回某些列的數(shù)據(jù)，而不是所有列的數(shù)；
例如，從students表中返回id、score和name這三列：
SELECT id, score, name FROM students;
2）還可以給每一列起個別名，這樣，結(jié)果集的列名就可以與原表的列名不同。
例如，以下SELECT語句將列名score重命名為points，而id和name列名保持不變：
SELECT id, score points, name FROM students;
4 排序
查詢結(jié)果集通常是按照id排序的，也就是根據(jù)主鍵排序。
1）加上ORDER BY子句，改為其他條件排序，例如按照成績從低到高進行排序：
SELECT id, name, gender, score FROM students ORDER BY score;
2）如要反過來，按照成績從高到底排序，末尾加上DESC表示“倒序”：
SELECT id, name, gender, score FROM students ORDER BY score DESC;
3）如果score列有相同數(shù)據(jù)，進一步排序，可以繼續(xù)添加列名。
例如，使用ORDER BY score DESC, gender表示先按score列倒序，如果有相同分數(shù)的，再按gender列排序：
SELECT id, name, gender, score FROM students ORDER BY score DESC, gender;
4）有WHERE子句，ORDER BY要放到WHERE后。
例如，查詢一班的學生成績，并按照倒序排序：
SELECT id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC;
5 分頁查詢
SELECT查詢時，如幾萬行數(shù)據(jù)，一個頁面顯示數(shù)據(jù)量太大，不如分頁顯示，每次顯示100條。
步驟如下：
1）我們先把所有學生按照成績從高到低進行排序：
SELECT id, name, gender, score FROM students ORDER BY score DESC;
2）把結(jié)果集分頁，每頁3條記錄。
查詢第1頁記錄，可以使用LIMIT 3 OFFSET 0：
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0;
如果要查詢第2頁，需要“跳過”頭3條記錄。（每頁記錄條數(shù)* (頁碼 - 1)）
也就是對結(jié)果集從3號記錄開始查詢，把OFFSET設(shè)定為3：
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 3;
如果原本記錄集一共就10條記錄，但我們把OFFSET設(shè)置為20，顯示結(jié)果如下：
Empty result set
OFFSET超過了查詢的最大數(shù)量并不會報錯，而是得到一個空的結(jié)果集。
6 聚合查詢
統(tǒng)計一張表的數(shù)據(jù)量，例如，想查詢students表一共有多少條記錄：
SELECT COUNT(*) FROM students;
通常，使用聚合查詢時，我們應(yīng)該給列名設(shè)置一個別名，便于處理結(jié)果：
SELECT COUNT(*) num FROM students;
除了COUNT()函數(shù)外，SQL還提供了如下聚合函數(shù)：
分組聚合
SQL還提供了“分組聚合”的功能，按class_id分組:
SELECT COUNT(*) num FROM students GROUP BY class_id;
可以把class_id列也放入結(jié)果集中，便于查看班級：
SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;
聚合查詢的列中,只能放入分組的列。
并沒有按照name分類，以下報錯：
SELECT name, class_id, COUNT(*) num FROM students GROUP BY class_id;
也可以使用多個列進行分組。
例如，我們想統(tǒng)計各班的男生和女生人數(shù)：
SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;
7?多表查詢
1) SELECT可以從多張表同時查詢數(shù)據(jù)。
例如，同時從students表和classes表的“乘積”,又稱笛卡爾查詢:
SELECT * FROM students, classes;
以上為students表的每一行，與classes表的每一行，兩兩拼在一起返回。
結(jié)果集列數(shù) = students列數(shù) + classes列數(shù)
結(jié)果集行數(shù) = students行數(shù) * classes行數(shù)
2) 上述結(jié)果集有兩列id和兩列name，不好區(qū)分。
要解決這個問題，可以利用投影查詢的“設(shè)置列的別名”來給兩個表各自的id和name列起別名：
SELECT
? ? students.id sid,
? ? students.name,
? ? students.gender,
? ? students.score,
? ? classes.id cid,
? ? classes.name cname
FROM students, classes;
SQL還允許給表設(shè)置一個別名，讓我們在投影查詢中引用起來稍微簡潔一點：
SELECT
? ? s.id sid,
? ? s.name,
? ? s.gender,
? ? s.score,
? ? c.id cid,
? ? c.name cname
FROM students s, classes c;
多表查詢也是可以添加WHERE條件的，我們來試試：
SELECT? ??
? ??s.id sid,
? ? s.name,
? ? s.gender,
? ? s.score,
? ? c.id cid,
? ? c.name cname
FROM students s, classes c
WHERE s.gender = 'M' AND c.id = 1;
8 連接查詢
連接查詢對多個表進行JOIN運算。
先確定一個主表作為結(jié)果集，然后把其他表的行，有選擇性地“連接”在主表結(jié)果集上。
例如，我們想要選出students表的所有學生信息，可以用一條簡單的SELECT語句完成：
SELECT s.id, s.name, s.class_id, s.gender, s.score FROM students s;
上面的結(jié)果集只有class_id列，缺少對應(yīng)班級的name列。
存放班級名稱的name列存儲在classes表中，
需要根據(jù)students表的class_id，找到classes表對應(yīng)的行的name列，就可得班級名稱。
此時可通過 內(nèi)連接——INNER JOIN來實現(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;
有內(nèi)連接（INNER JOIN）就有外連接（OUTER JOIN）。
我們把內(nèi)連接查詢改成外連接查詢：
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;
效果如下：
多出來的一行“四班”，但是，學生相關(guān)的列如name、gender、score都為NULL。
這也容易理解，因為根據(jù)ON條件s.class_id = c.id，classes表的id=4的行正是“四班”，但是，students表中并不存在class_id=4的行。
此外有?LEFT OUTER JOIN，以及?FULL OUTER JOIN。它們的區(qū)別是：
1. INNER JOIN只返回同時存在于兩張表的行數(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。
2. RIGHT OUTER JOIN返回右表都存在的行。
如果某一行僅在右表存在，那么結(jié)果集就會以NULL填充剩下的字段。
3. LEFT OUTER JOIN則返回左表都存在的行。
如果我們給students表增加一列，并添加class_id=5，由于classes表并不存在id=5的列，所以，LEFT OUTER JOIN的結(jié)果會增加一列，對應(yīng)的class_name是NULL.
4.?FULL OUTER JOIN，它會把兩張表的所有記錄全部選擇出來。
并且，自動把對方不存在的列填充為NULL。
