MYSQL学习笔记

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;