[大数据]MySQL-数据处理函数

数据处理函数

数据处理函数又称为单行处理函数；单行处理函数特点：一个输入对应一个输出。和单行处理函数相对的是多行处理函数：多个输入对应一个输出。

常见的单行处理函数

• Lower 转换小写
  

select lower(ename) from emp;

• upper 转换大写

select username from t_user;

  	+-----------+
	| username  |
	+-----------+
	| 123456q   |
	| 123456qq  |
	| admin     |
	| admin124  |
	| chendikai |
	| doctor1   |
	| huanz1    |
	| test1     |
	| zhangsan  |
	+-----------+

select upper(username) from t_user;

+-----------------+
| upper(username) |
+-----------------+
| 123456Q         |
| 123456QQ        |
| ADMIN           |
| ADMIN124        |
| CHENDIKAI       |
| DOCTOR1         |
| HUANZ1          |
| TEST1           |
| ZHANGSAN        |
+-----------------+

• substr 取子串 用法：substr(被截取的字段，起始下标，截取的长度)

select substr(ename,1,1) from emp;

+-------------------+
| substr(ename,1,1) |
+-------------------+
| S                 |
| A                 |
| W                 |
| J                 |
| M                 |
| B                 |
| C                 |
| S                 |
| K                 |
| T                 |
| A                 |
| J                 |
| F                 |
| M                 |
+-------------------+

用法实例
找出员工名字第一个字母是A的员工信息

• 1.模糊查询

select ename as useLikeUname from emp where ename like 'a%';

+--------------+
| useLikeUname |
+--------------+
| ALLEN        |
| ADAMS        |
+--------------+

• 2.使用substr函数

select ename as useSubstrUname from emp where substr(ename,1,1) = 'A';

+----------------+
| useSubstrUname |
+----------------+
| ALLEN          |
| ADAMS          |
+----------------+

• concat 字符串拼接用法：concat(字段1，字段2)

select concat(empno,ename) from emp;

+---------------------+
| concat(empno,ename) |
+---------------------+
| 7369SMITH           |
| 7499ALLEN           |
| 7521WARD            |
| 7566JONES           |
| 7654MARTIN          |
| 7698BLAKE           |
| 7782CLARK           |
| 7788SCOTT           |
| 7839KING            |
| 7844TURNER          |
| 7876ADAMS           |
| 7900JAMES           |
| 7902FORD            |
| 7934MILLER          |
+---------------------+

找出员工名字并首字母大写

select concat(substr(ename,1,1),lower(substr(ename,2,length(ename)-1))) as result from emp;//as可省略

+--------+
| result |
+--------+
| Smith  |
| Allen  |
| Ward   |
| Jones  |
| Martin |
| Blake  |
| Clark  |
| Scott  |
| King   |
| Turner |
| Adams  |
| James  |
| Ford   |
| Miller |
+--------+

• length 取长度

select length(ename) as enamelength from emp;//as可省略

+-------------+
| enamelength |
+-------------+
|           5 |
|           5 |
|           4 |
|           5 |
|           6 |
|           5 |
|           5 |
|           5 |
|           4 |
|           6 |
|           5 |
|           5 |
|           4 |
|           6 |
+-------------+

• trim 移除掉一个字串中的字头或字尾,如果没有指定内容，则仅删除空格

select * from emp where ename = '  KING';
//Empty set (0.00 sec)
select * from emp where ename = trim('   KING');

+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
+-------+-------+-----------+------+------------+---------+------+--------+

• str_to_date 将字符串转换成日期
• date_format 格式化日期
• format 设置千分位
• round 四舍五入，保留小数用法：round(字段名,保留N位小数);N可以为负数

 select round(sal,1) from emp;

+--------------+
| round(sal,1) |
+--------------+
|        800.0 |
|       1600.0 |
|       1250.0 |
|       2975.0 |
|       1250.0 |
|       2850.0 |
|       2450.0 |
|       3000.0 |
|       5000.0 |
|       1500.0 |
|       1100.0 |
|        950.0 |
|       3000.0 |
|       1300.0 |
+--------------+

select round(sal,-3) from emp;

+---------------+
| round(sal,-3) |
+---------------+
|          1000 |
|          2000 |
|          1000 |
|          3000 |
|          1000 |
|          3000 |
|          2000 |
|          3000 |
|          5000 |
|          2000 |
|          1000 |
|          1000 |
|          3000 |
|          1000 |
+---------------+

• rand() 生成随机数

select round(rand()*100,0) from emp;// 100以内的随机数

+---------------------+
| round(rand()*100,0) |
+---------------------+
|                  90 |
|                  37 |
|                  13 |
|                  54 |
|                  32 |
|                  98 |
|                  93 |
|                  72 |
|                  80 |
|                  84 |
|                  81 |
|                  52 |
|                  18 |
|                  33 |
+---------------------+

select 'abc' from emp; // select后面直接跟“字面量/字面值”
select 'abc' as bieming from emp;

+---------+
| bieming |
+---------+
| abc     |
| abc     |
| abc     |
| abc     |
| abc     |
| abc     |
| abc     |
| abc     |
| abc     |
| abc     |
| abc     |
| abc     |
| abc     |
| abc     |
+---------+

select 1000 as num from emp; // 1000 也是被当做一个字面量/字面值。

+------+
| num  |
+------+
| 1000 |
| 1000 |
| 1000 |
| 1000 |
| 1000 |
| 1000 |
| 1000 |
| 1000 |
| 1000 |
| 1000 |
| 1000 |
| 1000 |
| 1000 |
| 1000 |
+------+

结论：select后面可以跟某个表的字段名（可以等同看做变量名），也可以跟字面量/字面值（数据）。

select 21000 as num from dept;

+-------+
| num   |
+-------+
| 21000 |
| 21000 |
| 21000 |
| 21000 |
+-------+

• ifnull 空处理函数，可以将null转换成一个具体数。用法：ifnull(数据,被转换的值)
  数据库中只要有NULL参与数学运算，最终结果就是NULL

select ename, sal + comm as salcomm from emp;

+--------+---------+
| ename  | salcomm |
+--------+---------+
| SMITH  |    NULL |
| ALLEN  | 1900.00 |
| WARD   | 1750.00 |
| JONES  |    NULL |
| MARTIN | 2650.00 |
| BLAKE  |    NULL |
| CLARK  |    NULL |
| SCOTT  |    NULL |
| KING   |    NULL |
| TURNER | 1500.00 |
| ADAMS  |    NULL |
| JAMES  |    NULL |
| FORD   |    NULL |
| MILLER |    NULL |
+--------+---------+

使用ifnull函数：补助为NULL的时候，将补助当做0

select ename, (sal + ifnull(comm, 0)) * 12 as yearsal from emp;

+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 22800.00 |
| WARD   | 21000.00 |
| JONES  | 35700.00 |
| MARTIN | 31800.00 |
| BLAKE  | 34200.00 |
| CLARK  | 29400.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
| TURNER | 18000.00 |
| ADAMS  | 13200.00 |
| JAMES  | 11400.00 |
| FORD   | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+

• case when then when then else end
  case字段名when条件1then操作1when条件2then操作2其他条件操作3end
  例如
  当员工工作岗位是MANAGER的时候，工资上调10%，当工作岗位是SALESMAN的时候，工资上调50%，其他正常。（注意：不修改数据库，只是将查询结果显示为工资上调）

select 
	ename,job,sal as oldsal,(case job when 'MNAAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal*1 end) as newsal 
from 
	emp;

+--------+-----------+---------+---------+
| ename  | job       | oldsal  | newsal  |
+--------+-----------+---------+---------+
| SMITH  | CLERK     |  800.00 |  800.00 |
| ALLEN  | SALESMAN  | 1600.00 | 2400.00 |
| WARD   | SALESMAN  | 1250.00 | 1875.00 |
| JONES  | MANAGER   | 2975.00 | 2975.00 |
| MARTIN | SALESMAN  | 1250.00 | 1875.00 |
| BLAKE  | MANAGER   | 2850.00 | 2850.00 |
| CLARK  | MANAGER   | 2450.00 | 2450.00 |
| SCOTT  | ANALYST   | 3000.00 | 3000.00 |
| KING   | PRESIDENT | 5000.00 | 5000.00 |
| TURNER | SALESMAN  | 1500.00 | 2250.00 |
| ADAMS  | CLERK     | 1100.00 | 1100.00 |
| JAMES  | CLERK     |  950.00 |  950.00 |
| FORD   | ANALYST   | 3000.00 | 3000.00 |
| MILLER | CLERK     | 1300.00 | 1300.00 |
+--------+-----------+---------+---------+

简单用法：select + 函数+(字段名)+from+表名+;
多个函数可以嵌套使用

常见的分组函数

多个输入对应一个输出，使用时必须先进行分组，然后才能使用。如果没有对数据进行分组，整张表将默认为一组。

• count 计数
• sum 求和
• avg 平均值
• max 最大值
• min 最小值
  分组函数使用注意
• 分组函数自动忽略null，不需要对null提前处理

select sum(comm) from emp;

+-----------+
| sum(comm) |
+-----------+
|   2200.00 |
+-----------+

select count(comm) from emp;

+-------------+
| count(comm) |
+-------------+
|           4 |
+-------------+

select avg(comm) from emp;

+------------+
| avg(comm)  |
+------------+
| 550.000000 |
+------------+

• count（*）和count（具体字段）的区别

select count(*) from emp;
±---------+
| count(*) |
±---------+
| 14 |
±---------+
select count(comm) from emp;
±-----------------+
| count(comm) |
±------------------+
| 4 |
±------------------+
count（具体字段）:表示统计改字段下的所有不为null的元素的总数。
count（*）：表示统计表中的总行数。只要有一行数据，count就++，每一行数据都不可能全是null，只要有一列不是null，这行数据就是有效的。

• 分组函数不能直接使用在where子句中

select ename,sal from emp where sal > min(sal);
ERROR 1111 (HY000): Invalid use of group function

• 所有的分组函数可以组合在一起使用

select sum(sal),min(sal),max(sal),avg(sal),count(*) from emp;

• 分组函数在使用的时候必须先进行分组，然后才能使用。如果没有对数据分组，整张表默认为一组。

分组查询

分组查询：在实际应用中，可能有这样的需求，需要新进行分组，然后对每一组的数据进行操作。这个适合我们需要使用分组查询。

select
....
from
....
group by
....
;

计算每个部门的薪资和
计算每个工作岗位的平均薪资
计算 每个工作岗位的最高薪资

• 执行顺序是什么？
  1. from
  2. where
  3. group by
  4. select
  5. order by

    为什么分组函数不能直接使用在where后面？
    	select ename,sal from emp where sal > min(sal);//报错。
    	因为分组函数在使用的时候必须先分组之后才能使用。
    	where执行的时候，还没有分组。所以where后面不能出现分组函数。
  
    	select sum(sal) from emp; 
    	这个没有分组，为啥sum()函数可以用呢？
    		因为select在group by之后执行。
  

• 找出每个工作岗位的工资和

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

			+-----------+----------+
			| job       | sum(sal) |
			+-----------+----------+
			| ANALYST   |  6000.00 |
			| CLERK     |  4150.00 |
			| MANAGER   |  8275.00 |
			| PRESIDENT |  5000.00 |
			| SALESMAN  |  5600.00 |
			+-----------+----------+

以上这个语句的执行顺序？
先从emp表中查询数据。
根据job字段进行分组。
然后对每一组的数据进行sum(sal)

select ename,job,sum(sal) from emp group by job;
//以上语句在mysql中可以执行，但是毫无意义。以上语句在oracle中执行报错。oracle的语法比mysql的语法严格。
//mysql的语法相对来说松散一些

		+-------+-----------+----------+
		| ename | job       | sum(sal) |
		+-------+-----------+----------+
		| SCOTT | ANALYST   |  6000.00 |
		| SMITH | CLERK     |  4150.00 |
		| JONES | MANAGER   |  8275.00 |
		| KING  | PRESIDENT |  5000.00 |
		| ALLEN | SALESMAN  |  5600.00 |
		+-------+-----------+----------+

重点结论：
在一条select语句当中，如果有group by语句的话，
select后面只能跟：参加分组的字段，以及分组函数。
其它的一律不能跟。

• 找出每个部门的最高薪资
  实现思路:
  按照部门编号分组，求每一组的最大值。

select deptno,max(sal) from emp group by deptno;

		+--------+----------+
		| deptno | max(sal) |
		+--------+----------+
		|     10 |  5000.00 |
		|     20 |  3000.00 |
		|     30 |  2850.00 |
		+--------+----------+

select ename,deptno,max(sal) from emp group by deptno;//select后面添加ename字段没有意义，另外oracle会报错。

		+-------+--------+----------+
		| ename | deptno | max(sal) |
		+-------+--------+----------+
		| CLARK |     10 |  5000.00 |
		| SMITH |     20 |  3000.00 |
		| ALLEN |     30 |  2850.00 |
		+-------+--------+----------+

• 找出“每个部门，不同工作岗位”的最高薪资
  技巧：两个字段联合成1个字段看。（两个字段联合分组）

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

	+--------+-----------+----------+
	| deptno | job       | max(sal) |
	+--------+-----------+----------+
	|     10 | CLERK     |  1300.00 |
	|     10 | MANAGER   |  2450.00 |
	|     10 | PRESIDENT |  5000.00 |
	|     20 | ANALYST   |  3000.00 |
	|     20 | CLERK     |  1100.00 |
	|     20 | MANAGER   |  2975.00 |
	|     30 | CLERK     |   950.00 |
	|     30 | MANAGER   |  2850.00 |
	|     30 | SALESMAN  |  1600.00 |
	+--------+-----------+----------+

• 使用having可以对分完组之后的数据进一步过滤。
  having不能单独使用，having不能代替where，having必须
  和group by联合使用。
  例如：找出每个部门最高薪资，要求显示最高薪资大于3000的
  第一步：找出每个部门最高薪资
  按照部门编号分组，求每一组最大值。

select deptno,max(sal) from emp group by deptno;

		+--------+----------+
		| deptno | max(sal) |
		+--------+----------+
		|     10 |  5000.00 |
		|     20 |  3000.00 |
		|     30 |  2850.00 |
		+--------+----------+

第二步：要求显示最高薪资大于3000

select 
				deptno,max(sal) 
			from 
				emp 
			group by 
				deptno
			having
				max(sal) > 3000;

		+--------+----------+
		| deptno | max(sal) |
		+--------+----------+
		|     10 |  5000.00 |
		+--------+----------+

思考一个问题：以上的sql语句执行效率是不是低？
比较低，实际上可以这样考虑：先将大于3000的都找出来，然后再分组。

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

		+--------+----------+
		| deptno | max(sal) |
		+--------+----------+
		|     10 |  5000.00 |
		+--------+----------+

优化策略：
where和having，优先选择where，where实在完成不了了，再选择
having。
where无法完成的

• 找出每个部门平均薪资，要求显示平均薪资高于2500的。
  第一步：找出每个部门平均薪资

select deptno,avg(sal) from emp group by deptno;

			+--------+-------------+
			| deptno | avg(sal)    |
			+--------+-------------+
			|     10 | 2916.666667 |
			|     20 | 2175.000000 |
			|     30 | 1566.666667 |
			+--------+-------------+

第二步：要求显示平均薪资高于2500的

select 
					deptno,avg(sal) 
				from 
					emp 
				group by 
					deptno
				having
					avg(sal) > 2500;

		+--------+-------------+
		| deptno | avg(sal)    |
		+--------+-------------+
		|     10 | 2916.666667 |
		+--------+-------------+

总结

select
…
from
…
where
…
group by
…
having
…
order by
…

以上关键字只能按照这个顺序来，不能颠倒。

执行顺序？
	1. from
	2. where
	3. group by
	4. having
	5. select
	6. order by

从某张表中查询数据，
先经过where条件筛选出有价值的数据。
对这些有价值的数据进行分组。
分组之后可以使用having继续筛选。
select查询出来。
最后排序输出！

• 找出每个岗位的平均薪资，要求显示平均薪资大于1500的，除MANAGER岗位之外，
  要求按照平均薪资降序排。

select 
			job, avg(sal) as avgsal
		from
			emp
		where
			job <> 'MANAGER'
		group by
			job
		having
			avg(sal) > 1500
		order by
			avgsal desc;

	+-----------+-------------+
	| job       | avgsal      |
	+-----------+-------------+
	| PRESIDENT | 5000.000000 |
	| ANALYST   | 3000.000000 |
	+-----------+-------------+