DAY2
1、排序
查询所有员工的薪资,排序?
默认是升序
select ename,sal from emp order by sal;
指定降序
select ename,sal from emp order by sal desc;
指定升序
select ename,sal from emp order by sal asc;
可以两个字段排序吗?或者按照多个字段?
查询所有员工的薪资和名字,薪资升序,若薪资一样,则名字升序排列?
sal在前起主导,只有sal相同才会考虑ename排序
select ename,sal from emp order by sal asc,ename asc;
找出工资在1250到3000之间的员工信息,要求按照薪资降序排列?
select ename,sal from emp where sal between 1250 and 3000 order by sal desc;
关键字的顺序不能变
select … from … where … order by …
执行顺序:from —> where —> select —> order by
2、函数
数据处理函数又称为单行处理函数
单行处理函数的特点:一个输入对应一个输出
单行处理函数相对的是:多行处理函数(多行函数的处理特点:多个输入,对应一个输出)
函数 | 含义 |
---|---|
concat(s1,s2) | 字符串拼接 |
upper | 转换大写 |
substr | 取子串(substr(字符串,起始下标,截取长度)) |
length(参数) | 取长度 |
trim | 去空格 |
str_to_date | 将字符串转换为日期 |
date_format | 格式化日期 |
round | 四舍五入 |
format | 设置千分位 |
rand | 生成随机数(0~1) |
ifnull | 可以将null转换为一个具体的值 |
- 将员工姓名转为小写
select lower(ename) as ename from emp;
- 截取员工姓名前一个字符
select substr(ename,1,1) as ename from emp;
起始下标从1开始,没有0
- 找出员工名字第一个字母是‘A’
select ename from emp where ename like 'A%';
select ename from emp where substr(ename,1,1) = 'A';
- select 后面可以跟某个表的字段名(变量)或字面量(数据)
mysql> select 'abc' as ename from emp;
+-------+
| ename |
+-------+
| abc |
| abc |
| abc |
+-------+
- round(值,保留小数位数)
select round(1234.567,0) as result from emp;
- ifnull空处理函数,专门处理空的。null与其他值相加为null。在所有数据库中,只要有null参与的数学运算,最终结果为null
- ifnull 函数用法:ifnull(数据,被当做那个值)
计算员工的年薪
select ename,(sal+ifnull(comm,0))*12 as yearsal from emp;
- case … when … then … when … then … else …end
- 当员工的工作岗位是MANAGER的时候,工资上调10%,当工作岗位是SALESMAN时,工资上调50%,其他正常
select
ename,
job,
sal as oldsal,
(case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newsal
from
emp;
3、分组函数/聚合函数/多行处理函数
多行函数的处理特点:输入多行,最终输出一行
5个
- count 计数
- sum 求和
- avg 平均值
- max 最大值
- min 最小值
🍋分组函数在使用时必须先分组才能使用。如果没有对数据进行分组,整张表默认为一组。
求所有员工的薪资和
select sum(sal) from emp;
分组函数需要注意什么?
①分组函数自动忽略NULL,不需要提前对NULL进行处理。(null不是一个值,是什么也没有即空
②count(*)与count(具体字段)的区别?
- count(*):统计表中总行数(只要有一行数据count则++)因为每一行记录不可能都为NULL,一行数据中只要有一列数据不为NULL,则这行数据就是有效的
- count(具体字段):统计该字段下所有不为NULL的元素总数
③分组函数不能直接使用在where子句中。
例如:找出比最低工资高的员工信息
select ename,sal from emp where sal > min(sal);
Invalid use of group function(无效使用分组函数)
报错原因:因为分组函数在使用的时候必须先分组之后才能使用,where执行的时候还没有分组。所以where后面不能出现分组函数。
select sum(sal) from emp; 这个没分组但能用的原因:因为select在分组/group by之后执行
④所有的分组函数可以组合起来一起用
select sum(sal),min(sal),max(sal),avg(sal),count(*) from emp;
4、分组查询(重要)
什么是分组查询?
在实际的应用中,可以有这样的需求,需要先进行分组,然后对每组数据进行操作,在这个时候需要使用分组查询。
怎么进行分组查询?
select … from … group by…
select … from … where … group by … order by…
**执行顺序:**from –> where —> group by —>select —>order by
group by
取得每个工作岗位的工资合计,要求显示岗位名称和工资合计?
实现思路:按照工作岗位分组,然后对工资求和
select job,sum(sal) from emp group by job;
先从emp表中查询数据,根据job字段进行分组,然后对每一组数据进行sum(sal)
select ename,job,sum(sal) from emp group by job;
会报错,无意义
因此,在select语句中,如果有group by语句的话,select后面只能跟分组的字段以及分组函数,其他的不能跟。
找出“每个部门,不同岗位”的最高薪资?
技巧:把两个字段联合成1个字段看(两个字段联合分组)
select deptno,job,max(sal) from emp group by deptno,job;
having
使用having可以对分完组之后的数据进一步过滤。
having不能单独使用,不能代替where,having必须和group by联合使用
找出每个部门最高薪资,要求显示最高薪资大于2000的?
select deptno,max(sal) from emp group by deptno having max(sal)>2000;
思考:以上SQL语句的执行效率是不是低?
比较低,可以这样考虑:先将大于2000的抖找出来,然后再进行分组。
select deptno,max(sal) from emp where sal >2000 group by deptno;
优化策略:where和having,优先选择where,where实在完成不了,再选择having
举例:where完成不了的
找出每个部门的平均薪资,要求显示平均薪资高于2500的?
select deptno,avg(sal) from emp group by deptno having avg(sal)>2500;