Select进阶查询·查询结果处理

一、定义：

• 对用select 命令查找到的数据再做处理，
• 类似于系统命令管道 例如 ps aux | grep httpd

二、语法格式：

语法格式1：不加条件查找数据

select 字段名列表 from 库.表 分组|排序|过滤|分页 ;

语法格式2：添加条件查找数据

select 字段名列表 from 库.表 where 筛选条件 分组|排序|过滤|分页 ;

三、关键词：

四、使用案例：

• 分组group by：

# 查看每个部门的人数  
# 按照部门编号分组统计员工名的个数
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：运算符横着计算，统计函数竖着计算

# 把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：

# 查找部门人数少于10人的部门名称及人数
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：

# 只显示查询结果的前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)

# 只显示查询结果的第1行 到 第3 
# 0：表示查询结果的第1行
# 3：表示查询的行数
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)