一件已、定義：
- 對用select 命令查找到的數(shù)據(jù)再做處理，
- 類似于系統(tǒng)命令管道 例如 ps aux | grep httpd
二髓废、語法格式：
語法格式1：不加條件查找數(shù)據(jù)
select 字段名列表 from 庫.表 分組|排序|過濾|分頁 ;
語法格式2：添加條件查找數(shù)據(jù)
select 字段名列表 from 庫.表 where 篩選條件 分組|排序|過濾|分頁 ;
三履澳、關(guān)鍵詞：
| 分類 | 命令/符號/... | 說明/注意 |
|---|---|---|
| 分組 | group by 表頭名 | 表頭值相同為一組赂毯，值只顯示一次 注意： 分組命令只能單獨使用， 或與聚集函數(shù)一起使用 |
| 排序 | order by 表頭名 | 對表頭下的數(shù)據(jù)進行排序 針對數(shù)值類型的表頭進行排序 |
| order by 表頭名 asc | 升序,不寫asc為默認升序 | |
| order by 表頭名 desc | 降序 | |
| 過濾 | having 篩選條件 | 對select查詢到的數(shù)據(jù) 做篩選 |
| 分頁 | limit n1 | n1：只顯示查詢結(jié)果的第n1行 |
| limit n1,n2 | n1：表示起始行(從0開始計數(shù)) n2：表示顯示的總行數(shù) |
四萍丐、使用案例：
- 分組group by：
# 查看每個部門的人數(shù)
# 按照部門編號分組統(tǒng)計員工名的個數(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：運算符橫著計算轩端，統(tǒng)計函數(shù)豎著計算
# 把2018年每個員工的總收入由高到底排序
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)
- 過濾having：
# 查找部門人數(shù)少于10人的部門名稱及人數(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)
- 分頁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)
