MySQL學習（三） SQL基礎查詢

不能差不多發表於2018-07-11
MySql

其實在資料庫最經常用的當屬查詢操作

基本語法

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    欄位列表 AS 欄位別名
    [FROM 表名
    WHERE 條件表示式
    GROUP BY 欄位名|表示式
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING 條件表示式]
    [ORDER BY 欄位名|表示式
      [ASC | DESC] , ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]

所有被使用的子句必須按語法說明中顯示的順序嚴格地排序。例如，一個HAVING子句必須位於GROUP BY子句之後，並位於ORDER BY子句之前。

ALL, DISTINCT和DISTINCTROW選項指定是否重複行應被返回，如果沒有指定則預設值為ALL（返回所有匹配的行），DISTINCT和DISTINCTROW表示去重（如果是要刪除重複的行，那麼所有的欄位都需要相同）

資料準備

CREATE TABLE IF NOT EXISTS score (
    id INT, -- 學生id
    name VARCHAR(10), -- 課程名稱
    score NUMERIC(4, 1)); -- 分數

INSERT INTO score VALUES(1, `語文`, 90);
INSERT INTO score VALUES(1, `數學`, 95);
INSERT INTO score VALUES(1, `英語`, 98);
INSERT INTO score VALUES(2, `語文`, 92);
INSERT INTO score VALUES(2, `數學`, 88);
INSERT INTO score VALUES(2, `英語`, 90);
INSERT INTO score VALUES(3, `語文`, 96);
INSERT INTO score VALUES(3, `數學`, 100);
INSERT INTO score VALUES(3, `英語`, 98);

欄位別名：當資料進行查詢出來的時候，有時候資料表的欄位並不能符合我們的需求（多表查詢的時候，可能會有同名的欄位），這時候就需要對欄位進行重新命名

注意：在一個WHERE子句中使用列別名是不允許的，因為當執行WHERE子句時，列值可能還沒有被確定。

mysql> SELECT name, score FROM score; -- 沒有使用別名
+------+-------+
| name | score |
+------+-------+
| 語文 | 90    |
| 數學 | 95    |
| 英語 | 98    |
| 語文 | 92    |
| 數學 | 88    |
| 英語 | 90    |
| 語文 | 96    |
| 數學 | 100   |
| 英語 | 98    |
+------+-------+
9 rows in set

mysql> SELECT name AS `課程名稱`, score `分數` FROM score; -- 使用別名，score欄位使用了AS關鍵字
+----------+------+
| 課程名稱 | 分數 |
+----------+------+
| 語文     | 90   |
| 數學     | 95   |
| 英語     | 98   |
| 語文     | 92   |
| 數學     | 88   |
| 英語     | 90   |
| 語文     | 96   |
| 數學     | 100  |
| 英語     | 98   |
+----------+------+
9 rows in set

使用AS明確地指定列的別名，把它作為習慣，是一個良好的操作規範。

條件過濾WHERE

在SELECT語句中，資料根據WHERE子句中指定的搜尋條件來進行過濾，在搜尋條件中用來判斷條件的有比較運算子與邏輯運算子，其中

比較運算子有：>,<,>=,<=,!=,<>,like,between and,in/not in

邏輯運算子有：&&(and),||(or),!(not)

當SQL執行到WHERE子句時，會先從磁碟中根據搜尋條件進行逐條判斷，如果成立則儲存到記憶體中，否則跳過。

注意：WHERE子句返回的結果只有0或者1（要麼成立，要麼不成立），其中0代表false，1代表true。

mysql> SELECT * FROM score WHERE id = 1; -- 查詢id為1的學生資訊
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | 語文 | 90    |
|  1 | 數學 | 95    |
|  1 | 英語 | 98    |
+----+------+-------+
3 rows in set

mysql> SELECT * FROM score WHERE id = 1 OR id = 2; -- 查詢id為1或者id為2的學生資訊
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | 語文 | 90    |
|  1 | 數學 | 95    |
|  1 | 英語 | 98    |
|  2 | 語文 | 92    |
|  2 | 數學 | 88    |
|  2 | 英語 | 90    |
+----+------+-------+
6 rows in set

mysql> SELECT * FROM score WHERE score BETWEEN 95 AND 98; -- 查詢課程分數在95到98之間的學生資訊
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | 數學 | 95    |
|  1 | 英語 | 98    |
|  3 | 語文 | 96    |
|  3 | 英語 | 98    |
+----+------+-------+
4 rows in set

分組函式GROUP BY

GROUP BY從語義上面來看意思是根據BY後面的欄位名或者表示式進行分組，所謂的分組就是將SELECT出來的資料分成若干個組，相同的放一組），通常分組是為了做資料統計分析，所以常常配合聚合（統計）函式進行使用

常用的聚合（統計）函式有：

COUNT()：返回SELECT語句檢索到的行中非NULL值的數目，若找不到匹配的行，則COUNT() 返回 0，COUNT(*)則包含非NULL值

SUM()： 統計每組資料的總數，表中列值為NULL的行不參與計算，若找不到匹配的行，則返回NULL

AVG()：統計每組資料的平均值，表中列值為NULL的行不參與計算，若找不到匹配的行，則返回 NULL

MAX()：統計每組中的最大值，如果統計的列中只有NULL值，那麼返回NULL

MIN()：統計每組中的最小值，如果統計的列中只有NULL值，那麼返回NULL

聚合函式的特點：只有一個返回值

mysql> SELECT name, AVG(score), SUM(score) FROM score GROUP BY name; -- 統計各科的平均成績與總成績
+------+------------+------------+
| name | AVG(score) | SUM(score) |
+------+------------+------------+
| 數學 | 94.33333   | 283.0      |
| 英語 | 95.33333   | 286.0      |
| 語文 | 92.66667   | 278.0      |
+------+------------+------------+
3 rows in set

分組會根據分組的欄位進行預設排序，這裡的排序指的是對每個組的結果集這個整體進行排序，而不是分組中每一條記錄，實際上分組後每組也就一條記錄了。

現在有個需求，想要對上面的結果再進行一次彙總，那麼可能會考慮到用聯合查詢，不過MySQL中提供了WITH ROOLUP關鍵字就能輕鬆完成這件事情

mysql> SELECT name, AVG(score), SUM(score) FROM score GROUP BY name WITH ROLLUP;
+------+------------+------------+
| name | AVG(score) | SUM(score) |
+------+------------+------------+
| 數學 | 94.33333   | 283.0      |
| 英語 | 95.33333   | 286.0      |
| 語文 | 92.66667   | 278.0      |
| NULL | 94.11111   | 847.0      |
+------+------------+------------+
4 rows in set

與GROUP BY相比，在查詢的最後一行多了對平均成績與總成績的彙總。對單個維度的彙總並不能體現出ROLLUP的優勢，下面對id與name進行彙總統計

mysql> SELECT id, name, AVG(score), SUM(score) FROM score GROUP BY id, name WITH ROLLUP;
+------+------+------------+------------+
| id   | name | AVG(score) | SUM(score) |
+------+------+------------+------------+
|    1 | 數學 | 95         | 95.0       |
|    1 | 英語 | 98         | 98.0       |
|    1 | 語文 | 90         | 90.0       |
|    1 | NULL | 94.33333   | 283.0      |
|    2 | 數學 | 88         | 88.0       |
|    2 | 英語 | 90         | 90.0       |
|    2 | 語文 | 92         | 92.0       |
|    2 | NULL | 90         | 270.0      |
|    3 | 數學 | 100        | 100.0      |
|    3 | 英語 | 98         | 98.0       |
|    3 | 語文 | 96         | 96.0       |
|    3 | NULL | 98         | 294.0      |
| NULL | NULL | 94.11111   | 847.0      |
+------+------+------------+------------+
13 rows in set

其中(NULL, NULL)與GROUP BY  name WITH ROLLUP類似，表示對最後資料的彙總

(id, NULL)表示對學生進行分組後的聚合結果，這裡表示對每個學生的成績進行彙總

(id, name)表示對學生與科目進行分組後的聚合結果，這裡表示對每個學生的各科成績進行彙總

MySQL 擴充套件了 GROUP BY的用途，因此你可以使用SELECT 列表中不出現在GROUP BY語句中的列或運算。例如

mysql> SELECT id, name, AVG(score), SUM(score) FROM score GROUP BY id;
+----+------+------------+------------+
| id | name | AVG(score) | SUM(score) |
+----+------+------------+------------+
|  1 | 語文 | 94.33333   | 283.0      |
|  2 | 語文 | 90         | 270.0      |
|  3 | 語文 | 98         | 294.0      |
+----+------+------------+------------+
3 rows in set

從上面的結果可以看出分組函式的特點：返回值為該組中的第一條記錄

在標準SQL中，你必須將 name新增到 GROUP BY子句中。假如你從GROUP BY部分省略的列在該組中不是唯一的，那麼不要使用這個功能！你會得到非預測性結果。例如根據學生查詢最高成績時所對應課程名稱為每組中第一條記錄值，這並不是我們想要的

mysql> SELECT id, name, AVG(score), MAX(score) FROM score GROUP BY id;
+----+------+------------+------------+
| id | name | AVG(score) | MAX(score) |
+----+------+------------+------------+
|  1 | 語文 | 94.33333   | 98         |
|  2 | 語文 | 90         | 92         |
|  3 | 語文 | 98         | 100        |
+----+------+------------+------------+
3 rows in set

如果需要在一行中顯示每個學生的各科成績，可以用GROUP_CONCAT函式，該函式通常配合GROUP BY使用，如果沒有GROUP BY，將返回列中的所有值

mysql> SELECT id, GROUP_CONCAT(score) FROM score GROUP BY id;
+----+---------------------+
| id | GROUP_CONCAT(score) |
+----+---------------------+
|  1 | 90.0,95.0,98.0      |
|  2 | 92.0,88.0,90.0      |
|  3 | 96.0,100.0,98.0     |
+----+---------------------+
3 rows in set

mysql> SELECT id, GROUP_CONCAT(score) FROM score;
+----+-----------------------------------------------+
| id | GROUP_CONCAT(score)                           |
+----+-----------------------------------------------+
|  1 | 90.0,95.0,98.0,92.0,88.0,90.0,96.0,100.0,98.0 |
+----+-----------------------------------------------+
1 row in set

過濾分組HAVING

HAVING是用來對分組後的資料進行資料篩選的，例如要查詢平均成績小於95的學生資訊，使用having時，此時資料已經在記憶體中了。

mysql> SELECT id, AVG(score) FROM score GROUP BY id HAVING AVG(score) < 95;
+----+------------+
| id | AVG(score) |
+----+------------+
|  1 | 94.33333   |
|  2 | 90         |
+----+------------+
2 rows in set

排序ORDER BY

根據某個欄位進行升序（預設）或者降序排序，依賴校對集

mysql> SELECT * FROM score WHERE id = 1 ORDER BY score DESC; -- 查詢學生1的成績，並按照成績由高到低進行排序
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | 英語 | 98    |
|  1 | 數學 | 95    |
|  1 | 語文 | 90    |
+----+------+-------+
3 rows in set

數量限定LIMIT

兩種使用方式

1、LIMIT row_count：row_count表示數量，如

mysql> SELECT * FROM score LIMIT 2; -- 查詢列表前兩條資料
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | 語文 | 90    |
|  1 | 數學 | 95    |
+----+------+-------+
2 rows in set

2、LIMIT begin,offset：begin表示起始位置，offset表示數量

mysql> SELECT * FROM score LIMIT 2,3; -- 從第二條開始，取出三條資料，通常用於分頁
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | 英語 | 98    |
|  2 | 語文 | 92    |
|  2 | 數學 | 88    |
+----+------+-------+
3 rows in set

 

相關文章