数据处理函数及查询
1. 数据处理函数
1.1 数据处理函数 / 单行处理函数
常用的函数
| 函数 | 作用 |
|---|---|
| lower | 转换小写 |
| upper | 转换大写 |
| substr | 取子串（substr（被截取的字符串,起始下标, 截取的长度）） |
| concat | 字符串拼接 （conca（第一个字符串，第二个字符串） |
| length | 取长度 |
| trim | 去空格 |
| str_to_date | 将字符串转换成日期 |
| date_format | 格式化日期 |
| format | 设置千分位 |
| round | 四舍五入 |
| rand() | 生成随机数 |
| ifnull | 可以将 null 转换成一个具体值 |
1.2 lower
例：查询员工，将员工姓名全部转换成小写
mysql> select lower(ename) as ename from emp;
+--------+
| ename |
+--------+
| smith |
| allen |
| ward |
| jones |
| martin |
| blake |s
| clark |
| scott |
| king |
| turner |
| adams |
| james |
| ford |
| miller |
+--------+
14个输入，最后还是14个输出。这是单行处理函数的特点。
1.3 upper
例：将学生名字转换为大写
mysql> select * from t_student; // 转换前
+----------+
| name |
+----------+
| zhangsan |
| lisi |
| wangwu |
| jack_son |
+----------+
mysql> select upper(name) as name from t_student;
+----------+
| name |
+----------+
| ZHANGSAN |
| LISI |
| WANGWU |
| JACK_SON |
+----------+
1.4 substr
? substr ( 被截取的字符串, 起始下标,截取的长度)
注意：起始下标从1开始，没有0.
例：找出员工名字第一个字母是A的员工信息？
mysql> select ename from emp where substr(ename,1,1)='A';
+-------+
| ename |
+-------+
| ALLEN |
| ADAMS |
+-------+
1.5 concat
例：将员工编号与员工姓名拼接
mysql> select concat(empno,ename) from emp;
+---------------------+
| concat(empno,ename) |
+---------------------+
| 7369SMITH |
| 7499ALLEN |
| 7521WARD |
| 7566JONES |
| 7654MARTIN |
| 7698BLAKE |
| 7782CLARK |
| 7788SCOTT |
| 7839KING |
| 7844TURNER |
| 7876ADAMS |
| 7900JAMES |
| 7902FORD |
| 7934MILLER |
+---------------------+
1.6 length
例：取员工姓名的长度
mysql> select length(ename) enamelength from emp;
+-------------+
| enamelength |
+-------------+
| 5 |
| 5 |
| 4 |
| 5 |
| 6 |
| 5 |
| 5 |
| 5 |
| 4 |
| 6 |
| 5 |
| 5 |
| 4 |
| 6 |
+-------------+
1.7 trim
例：取得工作岗位为 manager 的所有员工
mysql> select * from emp where job=trim(upper('manager '));
+-------+-------+---------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+---------+------+------------+---------+------+--------+
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
+-------+-------+---------+------+------------+---------+------+--------+
注意：trim 会去首尾空格，不会去除中间的空格
1.8 round
mysql> select round(1236.567, 0) as result from emp; //保留整数位。
+--------+
| result |
+--------+
| 1237 |
| 1237 |
| 1237 |
| 1237 |
| 1237 |
| 1237 |
| 1237 |
| 1237 |
| 1237 |
| 1237 |
| 1237 |
| 1237 |
| 1237 |
| 1237 |
+--------+
select round(1236.567, 1) as result from emp; //保留1个小数
select round(1236.567, 2) as result from emp; //保留2个小数
select round(1236.567, -1) as result from emp; // 保留到十位。
+--------+
| result |
+--------+
| 1240 |
| 1240 |
| 1240 |
| 1240 |
| 1240 |
| 1240 |
| 1240 |
| 1240 |
| 1240 |
| 1240 |
| 1240 |
| 1240 |
| 1240 |
| 1240 |
+--------+
select round(1236.567, -2) as result from emp; // 保留到百位。
+--------+
| result |
+--------+
| 1200 |
| 1200 |
| 1200 |
| 1200 |
| 1200 |
| 1200 |
| 1200 |
| 1200 |
| 1200 |
| 1200 |
| 1200 |
| 1200 |
| 1200 |
| 1200 |
+--------+
1.9 rand
例：生成 100 以内的随机数
mysql> select round(rand()*100,0) from emp;
+---------------------+
| round(rand()*100,0) |
+---------------------+
| 76 |
| 29 |
| 15 |
| 88 |
| 95 |
| 9 |
| 63 |
| 89 |
| 54 |
| 3 |
| 54 |
| 61 |
| 42 |
| 28 |
+---------------------+
1.10 ifnull
? 可以将 null 转换成一个具体值
? ifnull是空处理函数。专门处理空的。
? 在所有数据库当中，只要有NULL参与的数学运算，最终结果就是NULL。
mysql> 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 |
+--------+---------+
例：计算每个员工的年薪？
? 年薪 = (月薪 + 月补助) * 12
select ename, (sal + comm) * 12 as yearsal from emp;
+--------+----------+
| ename | yearsal |
+--------+----------+
| SMITH | NULL |
| ALLEN | 22800.00 |
| WARD | 21000.00 |
| JONES | NULL |
| MARTIN | 31800.00 |
| BLAKE | NULL |
| CLARK | NULL |
| SCOTT | NULL |
| KING | NULL |
| TURNER | 18000.00 |
| ADAMS | NULL |
| JAMES | NULL |
| FORD | NULL |
| MILLER | NULL |
+--------+----------+
注意：
NULL只要参与运算，最终结果一定是NULL。为了避免这个现象，需要使用ifnull函数。
ifnull函数用法：
? ifnull(数据, 被当做哪个值)
? 如果“数据”为NULL的时候，把这个数据结构当做哪个值。
? 补助为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 |
+--------+----------+
1.11 case…when…then…when…then…else…end
例：当员工的工作岗位是MANAGER的时候，工资上调10%，当工作岗位是SALESMAN的时候，工资上调50%,其它正常。
mysql> 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;
+--------+-----------+---------+---------+
| 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 | 3272.50 |
| MARTIN | SALESMAN | 1250.00 | 1875.00 |
| BLAKE | MANAGER | 2850.00 | 3135.00 |
| CLARK | MANAGER | 2450.00 | 2695.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 |
+--------+-----------+---------+---------+
1.12 首字母大写
select concat(upper(substr(name,1,1)),substr(name,2,length(name) - 1)) as result from t_student;
+----------+
| result |
+----------+
| Zhangsan |
| Lisi |
| Wangwu |
| Jack_son |
+----------+
2. 分组函数
2.1 分组函数 / 聚合函数 / 多行处理函数
| 函数 | 作用 |
|---|---|
| count | 取得记录数 |
| sum | 求和 |
| avg | 取平均数 |
| max | 取最大的数 |
| min | 取最小的数 |
注意：
分组函数在使用的时候必须先进行分组，然后才能用。如果你没有对数据进行分组，整张表默认为一组。
分组函数自动忽略空值，不需要手动的加 where 条件排除空值。
select count(*) from emp where xxx; // 符合条件的所有记录总数。 select count(comm) from emp; comm // 这个字段中不为空的元素总数。分组函数不能直接使用在 where 关键字后面。
mysql> select ename,sal from emp where sal > avg(sal); ERROR 1111 (HY000): Invalid use of group function所有的分组函数可以组合起来一起用。
mysql> select sum(sal),min(sal),max(sal),avg(sal),count(*) from emp; +----------+----------+----------+-------------+----------+ | sum(sal) | min(sal) | max(sal) | avg(sal) | count(*) | +----------+----------+----------+-------------+----------+ | 29025.00 | 800.00 | 5000.00 | 2073.214286 | 14 | +----------+----------+----------+-------------+----------+
2.2 count
例：计算员工数量？
mysql> select count(ename) from emp;
+--------------+
| count(ename) |
+--------------+
| 14 |
+--------------+
分组函数在使用的时候需要注意哪些
-
分组函数自动忽略NULL，你不需要提前对NULL进行处理。
mysql> select comm from emp; +---------+ | comm | +---------+ | NULL | | 300.00 | | 500.00 | | NULL | | 1400.00 | | NULL | | NULL | | NULL | | NULL | | 0.00 | | NULL | | NULL | | NULL | | NULL | +---------+ mysql> select count(comm) from emp; +-------------+ | count(comm) | +-------------+ | 4 | +-------------+ -
分组函数中count(*)和count(具体字段)有什么区别
mysql> select count(*) from emp; +----------+ | count(*) | +----------+ | 14 | +----------+ mysql> select count(comm) from emp; +-------------+ | count(comm) | +-------------+ | 4 | +-------------+count(具体字段)：表示统计该字段下所有不为NULL的元素的总数。
count(*)：统计表当中的总行数。（只要有一行数据count则++）
因为每一行记录不可能都为NULL，一行数据中有一列不为NULL，则这行数据就是有效的。
2.3 sum
? Sum 可以取得某一个列的和，null 会被忽略
例：取得薪水的合计
mysql> select sum(sal) from emp;
+----------+
| sum(sal) |
+----------+
| 29025.00 |
+----------+
2.4 avg
例：计算平均工资
mysql> select avg(sal) from emp;
+-------------+
| avg(sal) |
+-------------+
| 2073.214286 |
+-------------+
2.5 max
例：找出最高工资
mysql> select max(sal) from emp;
+----------+
| max(sal) |
+----------+
| 5000.00 |
+----------+
2.6 min
例：找出最低工资
mysql> select sum(sal) from emp;
+----------+
| sum(sal) |
+----------+
| 29025.00 |
+----------+
3. 分组查询
3.1 什么是分组查询
? 在实际的应用中，可能有这样的需求，需要先进行分组，然后对每一组的数据进行操作。
? 这个时候我们需要使用分组查询
select
...
from
...
group by
...
3.2 分组查询
将之前的关键字全部组合在一起，来看一下他们的执行顺序
select
...
from
...
where
...
group by
...
order by
...
以上关键字的顺序不能颠倒，需要记忆。
执行顺序
- from
- where
- group by
- select
- order by
为什么分组函数不能直接使用在where后面？
select ename,sal from emp where sal > min(sal); // 报错。
? 因为分组函数在使用的时候必须先分组之后才能使用。
? where执行的时候，还没有分组。所以where后面不能出现分组函数。
select sum(sal) from emp;
? 这个没有分组，为啥 sum() 函数可以用呢？
? 因为 select 在 group by 之后执行。
例：找出每个工作岗位的工资和
实现思路：按照工作岗位分组，然后对工资求和
mysql> select job,sum(sal) from emp group by job;
+-----------+----------+
| job | sum(sal) |
+-----------+----------+
| CLERK | 4150.00 |
| SALESMAN | 5600.00 |
| MANAGER | 8275.00 |
| ANALYST | 6000.00 |
| PRESIDENT | 5000.00 |
+-----------+----------+
以上这个语句的执行顺序：
先从emp表中查询数据。
根据job字段进行分组。
然后对每一组的数据进行sum(sal)
mysql> select ename,job,sum(sal) from emp group by job;
+-------+-----------+----------+
| ename | job | sum(sal) |
+-------+-----------+----------+
| SMITH | CLERK | 4150.00 |
| ALLEN | SALESMAN | 5600.00 |
| JONES | MANAGER | 8275.00 |
| SCOTT | ANALYST | 6000.00 |
| KING | PRESIDENT | 5000.00 |
+-------+-----------+----------+
以上语句在mysql中可以执行，但是毫无意义。
以上语句在oracle中执行报错。
oracle的语法比mysql的语法严格。（mysql的语法相对来说松散一些！）
重点结论：
在一条select语句当中，如果有group by语句的话，select后面只能跟：参加分组的字段，以及分组函数。其它的一律不能跟。
例：找出每个部门的最高薪资
mysql> select deptno,max(sal) from emp group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
| 20 | 3000.00 |
| 30 | 2850.00 |
| 10 | 5000.00 |
+--------+----------+
例：找出“每个部门，不同工作岗位”的最高薪资
+--------+-----------+---------+--------+
| ename | job | sal | deptno |
+--------+-----------+---------+--------+
| MILLER | CLERK | 1300.00 | 10 |
| KING | PRESIDENT | 5000.00 | 10 |
| CLARK | MANAGER | 2450.00 | 10 |
| FORD | ANALYST | 3000.00 | 20 |
| ADAMS | CLERK | 1100.00 | 20 |
| SCOTT | ANALYST | 3000.00 | 20 |
| JONES | MANAGER | 2975.00 | 20 |
| SMITH | CLERK | 800.00 | 20 |
| BLAKE | MANAGER | 2850.00 | 30 |
| MARTIN | SALESMAN | 1250.00 | 30 |
| ALLEN | SALESMAN | 1600.00 | 30 |
| TURNER | SALESMAN | 1500.00 | 30 |
| WARD | SALESMAN | 1250.00 | 30 |
| JAMES | CLERK | 950.00 | 30 |
+--------+-----------+---------+--------+
技巧：两个字段联合成1个字段看。（两个字段联合分组）
mysql> select deptno,job,max(sal) from emp group by deptno,job;
+--------+-----------+----------+
| deptno | job | max(sal) |
+--------+-----------+----------+
| 20 | CLERK | 1100.00 |
| 30 | SALESMAN | 1600.00 |
| 20 | MANAGER | 2975.00 |
| 30 | MANAGER | 2850.00 |
| 10 | MANAGER | 2450.00 |
| 20 | ANALYST | 3000.00 |
| 10 | PRESIDENT | 5000.00 |
| 30 | CLERK | 950.00 |
| 10 | CLERK | 1300.00 |
+--------+-----------+----------+
3.3 having
? 使用having可以对分完组之后的数据进一步过滤。
? having不能单独使用，having不能代替where，having必须和group by联合使用。
例：找出每个部门最高薪资，要求显示最高薪资大于3000的
-
第一步：找出每个部门最高薪资
按照部门编号分组，求每一组最大值。mysql> select deptno,max(sal) from emp group by deptno; +--------+----------+ | deptno | max(sal) | +--------+----------+ | 10 | 5000.00 | | 20 | 3000.00 | | 30 | 2850.00 | +--------+----------+ -
第二步：要求显示最高薪资大于3000
mysql> select deptno,max(sal) from emp group by deptno having max(sal)>3000; +--------+----------+ | deptno | max(sal) | +--------+----------+ | 10 | 5000.00 | +--------+----------+
思考一个问题：以上的sql语句执行效率是不是低？
比较低，实际上可以这样考虑：先将大于3000的都找出来，然后再分组。
mysql> 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的。
-
第一步：找出每个部门平均薪资
mysql> select deptno,avg(sal) from emp group by deptno; +--------+-------------+ | deptno | avg(sal) | +--------+-------------+ | 10 | 2916.666667 | | 20 | 2175.000000 | | 30 | 1566.666667 | +--------+-------------+ -
第二步：要求显示平均薪资高于2500的
mysql> select deptno,avg(sal) from emp group by deptno having avg(sal)>2500; +--------+-------------+ | deptno | avg(sal) | +--------+-------------+ | 10 | 2916.666667 | +--------+-------------+
4. select 语句总结
一个完整的 select 语句格式如下
select 字段
from 表名
where ……
group by ……
having …… (就是为了过滤分组后的数据而存在的—不可以单独的出现)
order by ……
以上语句的执行顺序：
- 首先执行 where 语句过滤原始数据
- 执行 group by 进行分组
- 执行 having 对分组数据进行操作
- 执行 select 选出数据
- 执行 order by 排序
原则：能在 where 中过滤的数据，尽量在 where 中过滤，效率较高。having 的过滤是专门对分组之后的数据进行过滤 的。
