一、定義：
- 對(duì)用select 命令查找到的數(shù)據(jù)再做處理，
- 類似于系統(tǒng)命令管道 例如 ps aux | grep httpd
二、語(yǔ)法格式：
語(yǔ)法格式1：不加條件查找數(shù)據(jù)
select 字段名列表 from 庫(kù).表 分組|排序|過(guò)濾|分頁(yè) ;
語(yǔ)法格式2：添加條件查找數(shù)據(jù)
select 字段名列表 from 庫(kù).表 where 篩選條件 分組|排序|過(guò)濾|分頁(yè) ;
三、關(guān)鍵詞：
| 分類 | 命令/符號(hào)/... | 說(shuō)明/注意 |
|---|---|---|
| 分組 | group by 表頭名 | 表頭值相同為一組，值只顯示一次 注意： 分組命令只能單獨(dú)使用， 或與聚集函數(shù)一起使用 |
| 排序 | order by 表頭名 | 對(duì)表頭下的數(shù)據(jù)進(jìn)行排序 針對(duì)數(shù)值類型的表頭進(jìn)行排序 |
| order by 表頭名 asc | 升序,不寫(xiě)asc為默認(rèn)升序 | |
| order by 表頭名 desc | 降序 | |
| 過(guò)濾 | having 篩選條件 | 對(duì)select查詢到的數(shù)據(jù) 做篩選 |
| 分頁(yè) | limit n1 | n1：只顯示查詢結(jié)果的第n1行 |
| limit n1,n2 | n1：表示起始行(從0開(kāi)始計(jì)數(shù)) n2：表示顯示的總行數(shù) |
四、使用案例：
- 分組group by：
# 查看每個(gè)部門(mén)的人數(shù)
# 按照部門(mén)編號(hào)分組統(tǒng)計(jì)員工名的個(gè)數(shù)
mysql>select dept_id, count(name) from tarena.employees group by dept_id;
+---------+-------------+
| dept_id | count(name) |
+---------+-------------+
| 1 | 8 |
| 2 | 5 |
| 3 | 6 |
| 4 | 55 |
+---------+-------------+
4 rows in set (0.00 sec)
- 排序order by：運(yùn)算符橫著計(jì)算，統(tǒng)計(jì)函數(shù)豎著計(jì)算
# 把2018年每個(gè)員工的總收入由高到底排序
mysql> select employee_id, sum(basic+bonus) as total
-> from tarena.salary where year(date)=2018 group by
-> employee_id order by total desc;
+-------------+--------+
| employee_id | total |
+-------------+--------+
| 117 | 374923 |
| 31 | 374923 |
| 37 | 362981 |
| 68 | 360923 |
...
+-------------+--------+
120 rows in set (0.01 sec)
- 過(guò)濾having：
# 查找部門(mén)人數(shù)少于10人的部門(mén)名稱及人數(shù)
mysql> select dept_id,count(name),from,tarena.employees
-> group by dept_id having count(name)<10;
+---------+-------------+
| dept_id | count(name) |
+---------+-------------+
| NULL | 3 |
| 1 | 8 |
| 2 | 5 |
| 3 | 6 |
| 6 | 9 |
| 8 | 3 |
+---------+-------------+
6 rows in set (0.00 sec)
- 分頁(yè)limit：
# 只顯示查詢結(jié)果的前3行
mysql> select * from tarena.user where shell is not null limit 3;
+----+--------+----------+------+------+---------+---------+---------------+
| id | name | password | uid | gid | comment | homedir | shell |
+----+--------+----------+------+------+---------+---------+---------------+
| 1 | root | x | 0 | 0 | root | /root | /bin/bash |
| 2 | bin | x | 1 | 1 | bin | /bin | /sbin/nologin |
| 3 | daemon | x | 2 | 2 | daemon | /sbin | /sbin/nologin |
+----+--------+----------+------+------+---------+---------+---------------+
3 rows in set (0.01 sec)
# 只顯示查詢結(jié)果的第1行 到 第3
# 0：表示查詢結(jié)果的第1行
# 3：表示查詢的行數(shù)
mysql> select * from user where shell is not null limit 0,3;
+----+--------+----------+------+------+---------+---------+---------------+
| id | name | password | uid | gid | comment | homedir | shell |
+----+--------+----------+------+------+---------+---------+---------------+
| 1 | root | x | 0 | 0 | root | /root | /bin/bash |
| 2 | bin | x | 1 | 1 | bin | /bin | /sbin/nologin |
| 3 | daemon | x | 2 | 2 | daemon | /sbin | /sbin/nologin |
+----+--------+----------+------+------+---------+---------+---------------+
3 rows in set (0.00 sec)
