MySQL教程 SQL基礎查詢

標籤：執行   分頁   輕鬆   現在   min()   where   null   har   計算   

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

基本文法

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 setmysql> 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 setmysql> 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 setmysql> 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 setmysql> 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

 

MySQL教程 SQL基礎查詢