Mysql-DQL之单表查询之数据处理函数

DQL之数据处理函数

数据处理函数/单行处理函数

1. 数据处理函数又被称为单行处理函数
   单行处理函数的特点：一个输入对应一个输出
   和单行处理函数相对的是多行处理函数。
   多行处理函数的特点：多个输入对应一个输出
2. 常见单行处理函数
   1.lower 转换小写
   select lower(name) as cap_name from table;
   2.upper 转换大写
   select upper(name) as low_name from table;
   3.substr 取子串（substr（被截取的字符串，起始下标，截取的长度））
   select substr(name,1,2) from table;
   起始下标从1开始，没有0
   实例：
   找出员工名字第一个字母是A的员工信息

第一种方式：模糊查询
select name from table where name like 'A%';
第二种方式：数据处理函数substr()
select 
	name
from
	table
where
	substr(name,1,1) = 'A';

4.length 取长度
select length(name) nameLength from table;//加空格也可以起别名（即as可省略）
5.trim 去空格（去除前后空白）
trim(' string')//即’string’
6.str_to_date 将字符串转换成日期
7.date_format 格式化日期
8.format 设置千分位
对数字进行格式化
9.round 四舍五入
select 字段 from 表名
select 'abc' from table;//select后面直接跟字面量或者字面值会根据表的结构在表的列尾创建一列字面量（字面值）如：
select 'abc' as bieming from table;

结论：select后面可以跟某个表的字段名（可以等同看做变量名），也可以跟字面量/字面值（数据）
select round(12.56,0) as result from table;
round(12.56)就等价与select后面跟了13这个字面值
0-保留整数位；1-1位…等等
-1-保留到十位；-2保留到百位
如

10.rand() 生成随机数
select rand() from table；
rand()生成0到1之间的随机数
round(rand()*100,0)——100以内的随机数等等
11.ifnull 可以将null转换成一个具体值
ifnull是空处理函数，专门处理空的
在所有数据库当中，只要有null参与的数学运算，最终结构都是null（不管加减乘除）
select name,(sal + ifnull(comn,0))*12 as yearsal from table
为了避免null这个bug需要使用ifnull函数。
ifnull函数用法：ifnull(数据，被当做哪个值)：即当数据为null的时候我们把它当做哪个值
12.concat 字符串拼接
select concat(name,score) from table;
也不一定非的是字符串，如数字也可以拼接
13.case when…then…when…then…else…end
当什么时候怎么做…当什么时候怎么做…其它怎么处理…结束
应用场景：当员工的工作岗位是MANAGER的时候，工资上调10%，当工作岗位是SALESMAN的时候，工资上调50%（注意，不修改数据库，只是将查询结果显示为工资上调后的数据）
select name,job,sal from table;

select 
	name,
	job, 
	sal as oldsal,
	(case sal when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newsal 
from 
	table; 

可以模拟Java中的if-else语句

分组函数/多行处理函数（group function）-5个

多行处理函数的特点：输入多行，输出一行

1. count：计数
   计算员工数量总和
   select count(name) from table;
2. sum: 求和
   计算工资和
   select sum(sal) from table;
3. avg: 平均值
   计算工资平均值
   select avg(sal) from table;
4. max: 最大值
   找出最高工资
   select max(sal) from table;
5. min: 最小值
   找出最低工资
   select main(sal) from table;
   注意：分组函数在使用的时候必须先进行分组，然后才能使用。如果没有对数据进行分组，整张表默认为一组。

分组函数使用注意事项

1. 分组函数自动忽略null，你不需要提前对null进行处理（**注意：**count在计数的时候对null的忽略）
2. 分组函数中count(*)和count(具体的某个字段)的区别：
   count(*)：和select*使用一样，统计表当中的总行数，只要有一行数据则count++，因为每一行记录不可能都为null，一行数据当中有一列不为null，则这行数据是有效的。
   count(具体字段)：表示统计该字段下所有不为null的数据的总数
3. 分组函数不能够直接使用在where子句中。
4. 所有的分组函数可以组合起来一起用
   select sum(sal),min(sal),max(sal),count(*),avg(sal) from table;
5. 分组查询（group by）-非常重要
   在实际的应用中，可能有这样的需求，需要先进行分组，然后对每一组的数据进行操作。这个时候我们需要使用分组查询，分组查询语法：
   select
   …
   from
   …
   group by
   …
   如：计算每个部门的工资和
   计算每个工作岗位的平均薪资
   找出每个工作岗位的最高薪资
   …
   将之前的关键字全部组合到一起来看一下他们的执行顺序
   select
   …
   from
   …
   where
   …
   group by
   …
   order by
   …
   以上是SQL语句关键字的编写顺序，但是关键字的执行顺序并不是这样，关键字的执行顺序如下，不能颠倒，需要记忆
   关键字执行顺序：
   1. from
   2. where
   3. group by
   4. select
   5. order by

那为什么分组函数不能够直接使用在where子句中：
因为分组函数在使用的时候必须先分组之后才能使用，where执行的时候还没有分组，所以where后面的子句不能出现分组函数
对于select max(sal) from tbale;，这个没有分组，为什么max可以使用呢？
因为select在group by之后执行，没有显式写明group by是因为整个一张表自动就分成了一组。
案例：找出每个工作岗位的工资和
实现思路：按照工作岗位分组，然后对工资求和

select 
	job,sum(sal)
from
	table
group by
	job;

以上这个语句的执行顺序：

1. 现从table中查询数据（from）
2. 根据job字段进行分组（group by）
3. 然后对每一组的数据进行sum(sal) （select）
   select语句中如果有group by语句的话，那么select的后面只能跟参加分组的字段以及分组函数，别的字段写上去是没有意义的（虽然在mysql中会执行，但是在oracle中会报错——mysql的语法相对来说松散一些）
   实例：找出每个部门，不同工作岗位的最高薪资

select 
	debt,job,max(sal)
from
	table
group by
	deptno,job;

group by后面可以跟多个字段，select后面的字段要和group by后面的字段顺序一致
使用having子句可以对分完组后的数据进一步过滤，注意：having不能单独使用，不能代替where，having必须和group by联合使用。
如：

select 
	debt，max(sal)
from
	table
group by
	deptno;
having
	max(sal) > 3000；

以上语句执行效率执行比较低，实际上可以这样考虑：现将薪资大于3000的先找出来再进行分组。如：

select 
	debt,max(sal)
from
	table
where
	sal > 3000;
group by
	deptno;

==优化策略：where和having，优先选择where，where实在完成不了的才用having。如：找出每个部门平均薪资，要求显示平均薪资大于2500的。

单标查询总结

select
…
from
…
where
…
group by
…
having
…
order by
…
以上关键字只能按照如下顺序执行（即查询一张表时的逻辑顺序）
关键字执行顺序：
1. from
2. where
3. group by
4. having
5. select
6. order by
从某张表中查询数据，先经过where条件筛选出有价值的数据，对这些有价值的数据进行分组，分组之后可以使用having继续筛选。
联系：找出每个岗位的平均薪资，要求显示平均薪资大于1500的，出MANAGE之外，要求按照平均薪资进行降序排列：

select 
	job,avg(sal) as avgsal
from
	table
where 
	job is not 'MANAGE' 
group by 
	job 
having 
	avg(sal) > 1500 
order by 
	avgsal desc;