Select进阶查询·查询结果处理
- 数据库
- 技术老男孩
- 0
一、定义:
- 对用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)
免责申明:本站发布的内容(图片、视频和文字)以转载和分享为主,文章观点不代表本站立场,如涉及侵权请联系站长邮箱:xbc-online@qq.com进行反馈,一经查实,将立刻删除涉嫌侵权内容。