MySQL資料庫的簡單查詢、條件查詢、分組查詢、排序查詢、查詢結果去重等實例講解

本文包括以下內容

簡單查詢 條件查詢:where 分組查詢:group by 排序查詢:order by 查詢結果去重:distinct

限制查詢量:limit

0 準備

先建立兩個表employee和department:

mysql> select * from employee;
+----+----------+--------+--------+------+------+---------+
| id | name     | gender | salary | age  | gmr  | dept_id |
+----+----------+--------+--------+------+------+---------+
|  1 | lisan    | f      | 100.00 |   20 |    4 |    1001 |
|  2 | Will     | f      | 130.00 |   28 |    3 |    1001 |
|  3 | lucy     | m      | 500.70 |   18 |    2 |    1002 |
|  4 | 李雷     | 男     | 670.00 |   20 |    4 |    1003 |
|  5 | WangYong | m      | 700.90 |   25 |    5 |    NULL |
|  6 | Sam      | m      | 600.90 |   25 |    5 |    1004 |
+----+----------+--------+--------+------+------+---------+

mysql> select * from department;
+------+-----------+--------------+
| id   | name      | description  |
+------+-----------+--------------+
| 1001 | 人事部    | 人事管理     |
| 1002 | 行政部    | 行政管理     |
| 1003 | 研發部    | 研發         |
| 1004 | 質量部    | 質量保障     |
+------+-----------+--------------+

1 簡單查詢

select * from employee;
//
select id,name,salary from employee;

2 條件查詢

where 條件表達式

//查詢條件有以下幾種

比較:=、<、>、>=,<=,!=,<> 指定范圍:between [] and [],NOT between [] and [] 指定集合:IN [],NOT IN [] 匹配字符:like,NOT LIKE 是否為空:IS NULL, IS NOT NULL 多個查詢條件: AND ,OR

//比較
select * from employ where age>20;
select * from employee where age<>20;

//指定范圍
select * from employee where age between 18 and 25;

//指定集合
select * from employee where name in ("Will","lucy");

//匹配字符,其中為"%"為通配符,"_"匹配單一字符
select * from employee where name like "w%";
+----+----------+--------+--------+------+------+---------+
| id | name     | gender | salary | age  | gmr  | dept_id |
+----+----------+--------+--------+------+------+---------+
|  2 | Will     | f      | 130.00 |   28 |    3 |    1001 |
|  5 | WangYong | m      | 700.90 |   25 |    5 |    NULL |
+----+----------+--------+--------+------+------+---------+

select * from employee where name like "w_l";//查詢結果為空
變成
select * from employee where name like "w_ll";就可以查到這一條數據瞭。

//AND
select * from employ where age>20 and salary<500; 

select * from employee where name like "w%" AND age>20 AND salary>500;

//OR 
select * from employee where name like "w%" AND age>25 OR salary>500;

3 查詢結果去重

使用DISTINCT關鍵字來消除重復的記錄

select distinct [value Name]

//eg:
select distinct age from employee;

4 排序

排序分為降序DESC,升序ASC兩種,使用ORDER BY關鍵字。

ORDER BY [value] [ASC | DESC]
默認情況下安裝ASC方式排序

示例:

//升序
select * from employee order by age ASC;
//降序
select * from employee order by age DESC;
//多個排序條件:先按照age降序,相同age的字段按照id升序排序
select * from employee order by age DESC,id ASC;

5 分組查詢

GROUP BY [value] [HAVAING 條件表達式] [WITH ROLLUP]

其中,value是按照該字段值分組,HAVING用來限制分組後的顯示,WITH ROLLUP將會在坐在記錄的最後加一條記錄,該記錄是上面所有記錄的總和。

5.1 單獨使用group by查詢

//單獨使用group by分組,查詢結果隻顯示分組的一條記錄
select * from employee group by age;

5.2 與GRIOUP_CONCAT()函數一起使用

每個分組指定字段都顯示出來

//與"GROUP_CONCAT()"函數一起使用
select age,group_concat(name) from employee group by age;

+------+--------------------+
| age  | group_concat(name) |
+------+--------------------+
|   18 | lucy               |
|   20 | lisan,李雷         |
|   25 | WangYong,Sam       |
|   28 | Will               |
+------+--------------------+
結果顯示分為4組,每組所有人的name 列出來瞭。

5.3 與集合函數一起使用

通過集合函數計算分組中的總記錄最大值、最小值。

//按照age分組查詢,age相同的為一組,然後對每組用集合函數count()計算出每一組的記錄數
select age,count(age) from employee group by age;

5.4與HAVING條件表達式一起使用

加上HAVCING…,可以限制輸出的結果,隻有滿足條件的才會顯示。

//與HAVING一起使用
//按照age字段查詢,然後顯示記錄大於等於2的分組。
select age,count(age) from employee group by age HAVING count(age)>=2;

+------+------------+
| age  | count(age) |
+------+------------+
|   20 |          2 |
|   25 |          2 |
+------+------------+

5.5 多個字段分組

//先按照age分組,age中有相同的記錄用gender再分組
select * from employee group by age,gender;

+----+----------+--------+--------+------+------+---------+
| id | name     | gender | salary | age  | gmr  | dept_id |
+----+----------+--------+--------+------+------+---------+
|  3 | lucy     | m      | 500.70 |   18 |    2 |    1002 |
|  1 | lisan    | f      | 100.00 |   20 |    4 |    1001 |
|  4 | 李雷     | 男     | 670.00 |   20 |    4 |    1003 |
|  5 | WangYong | m      | 700.90 |   25 |    5 |    NULL |
|  2 | Will     | f      | 130.00 |   28 |    3 |    1001 |
+----+----------+--------+--------+------+------+---------+

5.6 與WITH ROLLUP一起使用

WITH ROLLUP將會在坐在記錄的最後加一條記錄,該記錄是上面所有記錄的總和。

//最後一條是總和
select age,count(*) from employee group by age WITH ROLLUP;

+------+----------+
| age  | count(*) |
+------+----------+
|   18 |        1 |
|   20 |        2 |
|   25 |        2 |
|   28 |        1 |
| NULL |        6 |
+------+----------+

6 限制查詢量

6.1 不指定初始位置

記錄的條數從第一條開始顯示。

limit [num]
//隻顯示前兩條

select * from employee limit 2;

6.2 指定初始位置

從指定位置開始顯示,第一條記錄為0.

limit [start],[limitNUm]
//指定初始,顯示第二到第四條
select * from employee limit 1,3;

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *