SQL基礎（2）_ZenDei技術網路在線

之前的SQL基礎1中已經介紹了部分Select的內容，但是，實際使用中select 還有很多其他的用法，本文會再介紹部分select的其他用法。 1. 去重查詢 1.1 創建演示表創建2張表用於演示，表名分別為student和class，後續也將繼續用這2張表演示，2張表的數據如下： studen ...

之前的SQL基礎1中已經介紹了部分Select的內容，但是，實際使用中select 還有很多其他的用法，本文會再介紹部分select的其他用法。

1. 去重查詢

1.1 創建演示表

創建2張表用於演示，表名分別為student和class，後續也將繼續用這2張表演示，2張表的數據如下：

student表

class表

1.2 查詢student表的所有class_no內容

mysql> select  class_no from  student;
+----------+
| class_no |
+----------+
| 201801   |
| 201901   |
| 201901   |
| 201902   |
| 201902   |
| 201902   |
| 201902   |
+----------+

可見，查詢結果中很多重覆的情況。

1.3 去重查詢所有的class_no

去重使用 DISTINCT 關鍵字即可

mysql> select  distinct class_no from  student;
+----------+
| class_no |
+----------+
| 201801   |
| 201901   |
| 201902   |
+----------+
3 rows in set (0.00 sec)

2. 條件查詢

條件查詢可以有很多種組合，其中用 AND 或 OR連接不同的條件，同時可以用in , not in , >、>=、 <、<=、 =等條件進行範圍查詢等

2.1 AND條件

AND的意義相當於“且”，也就是AND前後的條件必須同時成立，例如：

查詢class_no為201901 並且age>=22的學生

mysql> select  * from student where age>=22 and class_no='201901';
+----+----------+------+----------+
| id | stu_name | age  | class_no |
+----+----------+------+----------+
|  1 | 李白     |   25 | 201901   |
+----+----------+------+----------+
1 row in set (0.00 sec)

2.2 OR條件

OR意味著“或”，即OR前後條件中的一個滿足條件及成立，例如：

查詢student表中age>=24 後者班級號為201801的學生

mysql> select  * from student where age>=24 or  class_no='201801';
+----+----------+------+----------+
| id | stu_name | age  | class_no |
+----+----------+------+----------+
|  1 | 李白     |   25 | 201901   |
|  4 | 喬峰     |   30 | 201801   |
|  7 | 杜甫     |   24 | 201902   |
+----+----------+------+----------+
3 rows in set (0.00 sec)

2.3 AND和OR的優先順序

AND和OR的優先順序順序是 AND大於OR，有括弧的先處理括弧的。即 AND和OR同時出現時，先處理AND 再與OR判斷，但是出現括弧，有出現括弧的先處理括弧里的。例如：

/**  沒有括弧 先處理and 最後處理or   **/
mysql> select  * from student where age<23  and class_no='201902'  or  class_no='201801';
+----+----------+------+----------+
| id | stu_name | age  | class_no |
+----+----------+------+----------+
|  4 | 喬峰     |   30 | 201801   |
|  3 | 王維     |   21 | 201902   |
|  5 | 陳六     |   22 | 201902   |
+----+----------+------+----------+
3 rows in set (0.00 sec)

/**  有括弧時，先庫哦李括弧里的or,最後處理 外層的AND ** /
mysql> select  * from student where age<23  and (class_no='201902'  or  class_no='201801');
+----+----------+------+----------+
| id | stu_name | age  | class_no |
+----+----------+------+----------+
|  3 | 王維     |   21 | 201902   |
|  5 | 陳六     |   22 | 201902   |
+----+----------+------+----------+
2 rows in set (0.00 sec)

建議：實際使用時，如果嵌套的關係太多，當確定需要先處理哪個關係時建議都加上括弧，已避免寫法錯誤導致結果與預期不一致。

2.4 IN或NOT IN

IN 或 NOT IN的使用頻率也是非常高的，例如：

/**  in  **/
mysql> select * from  student where class_no in ('201901','201902');
+----+----------+------+----------+
| id | stu_name | age  | class_no |
+----+----------+------+----------+
|  1 | 李白     |   25 | 201901   |
|  2 | 蘇軾     |   20 | 201901   |
|  3 | 王維     |   21 | 201902   |
|  5 | 陳六     |   22 | 201902   |
|  7 | 杜甫     |   24 | 201902   |
|  8 | 岳飛     |   23 | 201902   |
+----+----------+------+----------+
6 rows in set (0.00 sec)

/**  not in  **/
mysql> select * from  student where class_no  not in ('201901','201902');
+----+----------+------+----------+
| id | stu_name | age  | class_no |
+----+----------+------+----------+
|  4 | 喬峰     |   30 | 201801   |
+----+----------+------+----------+
1 row in set (0.00 sec)

3. 排序及分頁

3.1 排序

排序使用order by來進行，可以指定一個或多個欄位進行排序，同時可以指定升序（ASC，預設的是升序）或降序（DESC）。

/**   按照age升序排序 **/
mysql> select  * from  student order by age asc;
+----+----------+------+----------+
| id | stu_name | age  | class_no |
+----+----------+------+----------+
|  2 | 蘇軾     |   20 | 201901   |
|  3 | 王維     |   21 | 201902   |
|  5 | 陳六     |   22 | 201902   |
|  8 | 岳飛     |   23 | 201902   |
|  7 | 杜甫     |   24 | 201902   |
|  1 | 李白     |   25 | 201901   |
|  4 | 喬峰     |   30 | 201801   |
+----+----------+------+----------+
7 rows in set (0.00 sec)

/** 按照age降序排列  **/
mysql> select  * from  student order by age desc;
+----+----------+------+----------+
| id | stu_name | age  | class_no |
+----+----------+------+----------+
|  4 | 喬峰     |   30 | 201801   |
|  1 | 李白     |   25 | 201901   |
|  7 | 杜甫     |   24 | 201902   |
|  8 | 岳飛     |   23 | 201902   |
|  5 | 陳六     |   22 | 201902   |
|  3 | 王維     |   21 | 201902   |
|  2 | 蘇軾     |   20 | 201901   |
+----+----------+------+----------+
7 rows in set (0.00 sec)

/**  先按照class_no升序，class_no 相同時按照age降序排列 **/
mysql> select  * from  student order by class_no,age desc;
+----+----------+------+----------+
| id | stu_name | age  | class_no |
+----+----------+------+----------+
|  4 | 喬峰     |   30 | 201801   |
|  1 | 李白     |   25 | 201901   |
|  2 | 蘇軾     |   20 | 201901   |
|  7 | 杜甫     |   24 | 201902   |
|  8 | 岳飛     |   23 | 201902   |
|  5 | 陳六     |   22 | 201902   |
|  3 | 王維     |   21 | 201902   |
+----+----------+------+----------+
7 rows in set (0.00 sec)

註：當表的數據量較大時，建議排序欄位上有索引。

3.2 分頁

分頁查詢在數據展示上是使用頻率非常高的功能之一，1其語法為：

select field_names
from  tbname
where filter
order by oderby_fieldname
limit offset,row_counts

其中 offset是偏移量，即之前遍歷了的數據量，row_counts指每頁的數據量。

例如，分頁遍歷其中一個表的記錄，每頁3條記錄，例如：

/** 首頁  **/

mysql> select  *  from  student order  by id  limit 3*0,3;
+----+----------+------+----------+
| id | stu_name | age  | class_no |
+----+----------+------+----------+
|  1 | 李白     |   25 | 201901   |
|  2 | 蘇軾     |   20 | 201901   |
|  3 | 王維     |   21 | 201902   |
+----+----------+------+----------+
3 rows in set (0.00 sec)

/**  第二頁 **/
mysql> select  *  from  student order  by id  limit 3*1,3;
+----+----------+------+----------+
| id | stu_name | age  | class_no |
+----+----------+------+----------+
|  4 | 喬峰     |   30 | 201801   |
|  5 | 陳六     |   22 | 201902   |
|  7 | 杜甫     |   24 | 201902   |
+----+----------+------+----------+
3 rows in set (0.00 sec)

/**  第三頁 **/
mysql> select  *  from  student order  by id  limit 3*2,3;
+----+----------+------+----------+
| id | stu_name | age  | class_no |
+----+----------+------+----------+
|  8 | 岳飛     |   23 | 201902   |
+----+----------+------+----------+
1 row in set (0.00 sec)

註： order by 的欄位上一定要有索引,否則可能遍歷額結果有重覆，相關例子可以自行測試。

4. 聚合查詢

很多情況下，我們都需要進行一些彙總類的操作，即聚合查詢。聚合查詢通常需要配合GROUP BY關鍵字進行分組聚合。下麵使用幾種常見的聚合查詢操作。

4.1 count

count是指統計記錄條數。

4.1.1 不分組的情況下的聚合：

/** 查看student表的記錄總數 **/
mysql> select  count(*) from  student;
+----------+
| count(*) |
+----------+
|        7 |
+----------+
1 row in set (0.00 sec)

/**  查詢age大於24的記錄數  **/
mysql> select  count(*) from  student where age>=24;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

4.1.2 分組情況下聚合：

/** 按照class_no分組統計，即查詢每個班級的人數 **/
mysql> select  class_no, count(*) from  student group  by  class_no;
+----------+----------+
| class_no | count(*) |
+----------+----------+
| 201801   |        1 |
| 201901   |        2 |
| 201902   |        4 |
+----------+----------+
3 rows in set (0.00 sec)

/**查詢每個班級的人數，同時按照人數降序排列 **/
mysql> select  class_no, count(*) from  student group  by  class_no  order  by count(*) desc ;
+----------+----------+
| class_no | count(*) |
+----------+----------+
| 201902   |        4 |
| 201901   |        2 |
| 201801   |        1 |
+----------+----------+
3 rows in set (0.00 sec)


/**查詢每個班級的人數，同時只返回人數大於1個的class_no及人數 **/

mysql> select  class_no, count(*) from  student group  by  class_no  having  count(*)>1 ;
+----------+----------+
| class_no | count(*) |
+----------+----------+
| 201901   |        2 |
| 201902   |        4 |
+----------+----------+
2 rows in set (0.00 sec)

4.2 min、max 、avg 、sum

除了count的聚合操作外，還有min（最小）、max（最大）、avg （平均）、sum（求和）等聚合操作，其操作和count類似。

例如：

mysql> select  max(age),min(age),avg(age),sum(age)  from  student;
+----------+----------+----------+----------+
| max(age) | min(age) | avg(age) | sum(age) |
+----------+----------+----------+----------+
|       30 |       20 |  23.5714 |      165 |
+----------+----------+----------+----------+
1 row in set (0.00 sec)

5. 子查詢

當進行查詢下的時候需要查詢的條件是另外一個select語句的結果的時候可以用到子查詢來處理。此時要用in、not in 、exists、not exists以及=、!=等。

例如：

/**  查詢存在於class表的student的記錄 **/
mysql> select  *  from  student where  class_no in (select class_no from class);
+----+----------+------+----------+
| id | stu_name | age  | class_no |
+----+----------+------+----------+
|  1 | 李白     |   25 | 201901   |
|  2 | 蘇軾     |   20 | 201901   |
|  3 | 王維     |   21 | 201902   |
|  5 | 陳六     |   22 | 201902   |
|  7 | 杜甫     |   24 | 201902   |
|  8 | 岳飛     |   23 | 201902   |
+----+----------+------+----------+
6 rows in set (0.00 sec)

/**  查詢不存在於class表的student的記錄 **/
mysql> select  *  from  student where  class_no not  in (select class_no from class);
+----+----------+------+----------+
| id | stu_name | age  | class_no |
+----+----------+------+----------+
|  4 | 喬峰     |   30 | 201801   |
+----+----------+------+----------+
1 row in set (0.00 sec)

exists和not exists在此時（子查詢不存在null的情況下）查詢結果是等價的（查詢效率有差異，且和數據量有關），對於此問題，各位可以自己測一下。

6. 表連接

當需要同時展示多個表的欄位時，需要用表連接的方式將多張表的欄位在一個查詢中展示。

表連接的方式從大類上來說可以分為內連接和外連接。

6.1 內連接

內連接是查詢2張表同時存在的記錄，即兩張表的交集。

例如：

/**  同時查詢兩張表中class_no 相同的student及class表的所有欄位內容 **/
mysql> select  * from  student a,class b 
    -> where a.class_no=b.class_no;
+----+----------+------+----------+----------+--------------+------------------+
| id | stu_name | age  | class_no | class_no | class_name   | location         |
+----+----------+------+----------+----------+--------------+------------------+
|  1 | 李白     |   25 | 201901   | 201901   | 2019級01班   | 博學北樓A401     |
|  2 | 蘇軾     |   20 | 201901   | 201901   | 2019級01班   | 博學北樓A401     |
|  3 | 王維     |   21 | 201902   | 201902   | 2019級02班   | 博學北樓B401     |
|  5 | 陳六     |   22 | 201902   | 201902   | 2019級02班   | 博學北樓B401     |
|  7 | 杜甫     |   24 | 201902   | 201902   | 2019級02班   | 博學北樓B401     |
|  8 | 岳飛     |   23 | 201902   | 201902   | 2019級02班   | 博學北樓B401     |
+----+----------+------+----------+----------+--------------+------------------+
6 rows in set (0.00 sec)

註：

a) 例子中是列舉出所有欄位，所有可以用* ，當需要列出指定欄位時，可以列出指定欄位名展示，通過表名.欄位名的方式列出

b) 內連接的寫法可以向上述例子中那樣，也可以用inner join ... on...這種方式來寫，其中inner可以省略，例如：

mysql>