数据处理函数/单行处理函数(用法一般都是“函数名(参数值)”)
1、lower 转换小写
2、upper 转换大写
3、substr 取字串【substr(被截取的字符串,起始下标,截取的长度)】
4、length 取长度
5、trim 去除空格
6、str_to_date 将字符串转换成日期
7、date_formt 格式化日期
8、format 设置千分位
9、round 四舍五入
10、rand() 生成随机数
11、ifnull 可以将null转换成一个具体的值
12、concat() 拼接字符串
13、now() 用于获取当前系统时间
数据处理函数
1、数据处理函数又被称为单行处理函数。【因为他是一行一行处理的】
单行处理函数的特点：一个输入对应一个输出。
和单行处理函数相对应的是：多行处理函数，例如（sum(列名)）多行处理函数特点：多个输入，对应1个输出
示例：查找“工资(sal)”在3000以上的员工的工资的总和。【多行处理函数，只有一个输出】
例如：mysql> select sum(sal) from emp;
+----------+
| sum(sal) |
+----------+
| 29025.00 |
+----------+
【重点】无论是什么查询，查询结果集的“字段名”都是
select后面的那个东西，可以通过as重命名
在本例中是“sum(sal)”。
【重点】查询的如果要是一个“常量(包括数字和字符串等)”
那么查询结果集中的“字段名”是select后面的东西，可以通过as重命名
查询结果集该字段对应的每一行的值就是“常量”本身（如果是数字就算出来值），
查询结果集有多少条记录那么就显示出来多少条重复的该常量值：【本例一共有5条记录】
因为简单查询(没有条件)salgrade中的记录，因为salgrade表中一共有5条记录，所以就显示出来5行
mysql> select 10*10 from salgrade;
+-------+
| 10*10 |
+-------+
| 100 |
| 100 |
| 100 |
| 100 |
| 100 |
+-------+
【重点】select *,ename form emp;
*:可以与别的字段连用，但是*必须出现在第一个。
详解单行处理函数
1、lower 转成小写：语法结构lower(常量或者字段名)，返回转换成小写之后的字符串
【执行到某行的时候，字段名可以看作是一个变量，函数操作到本行的时候，字段名就代表本行的值，函数操作完，如果有返回值，那么也返回到本行的本字段名下】
示例：查询"SMITH"员工的“姓名”和“工作”，并且要求员工姓名和工作都是小写输出：
select lower(ename),lower(job) from emp where ename='SMITH';
查询结果集的列名是：lower(ename) 、lower(job)。【可以通过as关键字改名字】
mysql> select lower(ename),lower(job) from emp where ename='SMITH';
+--------------+------------+
| lower(ename) | lower(job) |
+--------------+------------+
| smith | clerk |
+--------------+------------+
不使用lower函数之前
mysql> select ename,job from emp where ename='SMITH';
+-------+-------+ +--------------+------------+
| ename | job | | lower(ename) | lower(job) |
+-------+-------+ +--------------+------------+
| SMITH | CLERK | | smith | clerk |
+-------+-------+ 使用之后--> +--------------+------------+
因为lower函数是单行处理函数。
执行过程首先将“SMITH”传进去，返回“smith”，然后将“CLERK”传进去返回“clerk”
下一行执行是新的开始，重新调用lower函数，跟这行没有关系，所以叫做单行处理函数。
以下所有单行处理函数的特点都类似lower。
2、upper:转成大写，返回转换成大写之后的字符串。本函数的用法跟lower函数的用法相同。
函数名不区分大小写。大写小写都一样的效果。
3、substr 取字串，返回截取到的子字符串
【语法结构：substr(被截取的字符串,起始下标,截取的长度)】
被截取的字符串：
可以是：字段的名字
也可以是：字符串常量
起始下标：
从1开始
截取的长度可以省略不写：
省略不写默认到最后。
示例：找出员工名字第一个字母是A的员工的信息：
第一种方式：
mysql> select * from emp where ename like 'A%';
+-------+-------+----------+------+------------+---------+--------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+----------+------+------------+---------+--------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
+-------+-------+----------+------+------------+---------+--------+--------+
第二种方式：
mysql> select * from emp where substr(ename,1,1)='A';【这里的a不区分大小写】
+-------+-------+----------+------+------------+---------+--------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+----------+------+------------+---------+--------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
+-------+-------+----------+------+------------+---------+--------+--------+
【重点】：通过第二个例子可以验证，函数不仅可以用在select后面还可以用在where后面，
只要弄清函数的“传入”什么“返回”什么就可以，
只要是合法的可以用在任何SQL语句中的任何位置。
4、concat() 拼接字符串，返回拼接完成的那个新字符串
concat (字符串1,字符串2[,字符串3,...])
字符串，可以是字符串常量，可以是字段名，还可以是数字
5、综合案例：
查询所有员工的姓名，输出形式：姓名首字母大写。
mysql> select concat(upper(substr(ename,1,1)),lower(substr(ename,2,length(ename)-1))) from emp;
upper(substr(ename,1,1))表示：截取姓名的第一个字符，并且将该字符转换成小写。
length(ename):表示获取ename的长度。
lower(substr(ename,2,length(ename)-1))：表示将姓名从第二个字符开始截取到末尾，并将其转换为小写。【length(ename)-1：表示去掉第一个字符，所以要减1】
concat() 将第一个大写字母和后面的小写字母拼接在一起
6、length 取长度，返回长度值
语法结构：
【重点】length(待计算长度的数据)
待计算长度的数据：
只能有一个值
可以是常量值(包括字符串、和数字)、字段名。
7、trim 去除空格，返回值是去除空白之后的那个数据的字符串。
语法结构：trim(待去除前后空白的数据)
待去除前后空白的数据：
只能有一个数据；
可以是：常量值(字符串、数字)、可以是字段名
8、round 四舍五入，返回四舍五入之后的那个值
语法结构：round(数据,保留几位小数)
数据可以是：字符串、数字、字段名
保留几位小数可以是：字符串、数字、字段名
因为这个是对数字的四舍五入的函数，所以两个参数是字符串的时候，
从第一个字符开始寻找数字，一直找到不是数字的那个字符为止，把找出来的数字字符串转换成整数类型
如果第一个字符就不是数字字符，那么返回“0”。
select round(1236.567,-1) as result form emp; //"-1"代表保留到十位，result=1240
select round(1236.567,-2) as result form emp; //"-2"代表保留到百位，result=1200
9、rand() 生成随机数，返回生成的那个随机数
生成随机数在[0,1)之间【包括0，不包括1】。
floor(rand()*N)：用于生成[0,N-1]之间的随机数【floor表示向下取整】
ceil(rand()*N)：用于生成[1,N]之间的随机数【ceil表示向上取整】
abs(数据)：用于取绝对值
10、ifnull 可以将null转换成一个具体的值
ifnull是空处理函数，专门处理null的。
在所有数据库当中只要有null参与的数学运算，最终结果都是null。为了避免这个现象，需要使用ifnull函数
语法结构：
ifnull(数据,被当作哪个值)
如果“数据”为null，返回“被当作哪个值”，如果“数据”不是null，那么就返回“数据”本身。
示例：计算每个人的年薪：（工资+津贴）*12
mysql> select *,(sal+ifnull(comm,0))*12 '年薪' from emp;
11、case ... when ... then ... when ... then ...end；函数
可以相当于java中的if语句。
when ... then ... 分支的数量不限
最后一定要有end
语法格式：
case 字段名 when 值1 then 返回值 when 值2 返回值2 else 返回值3 end;
当“字段名”对应的值是“值1”的时候整个case函数返回“返回值1”
还可以出现在select语句的后面。
数据处理函数/多行处理函数又叫做“分组函数”【输入多行，最终输出一行】
1、count 计数 ，统计该字段下所有不为null的元素的总数。
语法结构：
count(数据)
数据可以是：常量、字段名
2、sum 求和
语法结构：
sum(数据);
数据可以是：常量(数字)、字段名
如果是字段名：返回查询结果集中该字段的所有的值的总和
如果是常量：返回查询结果集“记录条数*数据”
3、avg 平均值
语法结构：
avg(数据)
数据可以是常量和字段名
常量：直接返回常量。
字段名：返回该字段所有值的平均值。
4、max 最大值
max(数据)：
数据要是字段名：返回该字段所有值中的最大值
数据要是常量：返回这个常量
5、min 最小值，与max用法类似
分组函数，在使用的时候必须先进行分组，然后才能使用。
注意：
1、如果没有对数据进行分组，默认整张表为一组。
2、分组函数自动忽略null，不需要提前对null进行处理。
3、分组函数中count(*)和count(具体字段)有什么区别？
count(*)：只要这行中有一个字段不是null就统计【在数据库中一行上所有的字段值都是null这样的数据不存在】所以count(*)就是统计表中的记录总数
count(具体字段)：只要这个字段为null就不统计了。
4、分组函数不能直接使用在where子句中：
mysql> select * from emp where sal > min(sal);
ERROR 1111 (HY000): Invalid use of group function
因为分组函数必须在分完组之后才能使用，分完组的标志是group by执行完毕，
因为在where执行的时候，group by还没有执行，还没有分组，所以不能直接使用分组函数。
那为什么？select sun(sal) from emp;就可以直接执行呐？
因为select在group by之后执行，没有group by，默认整张表是一组。
5、所有的分组函数可以联合在一起使用：
mysql> select avg(sal),min(sal),max(sal),sum(sal),count(*) from emp;
+-------------+----------+----------+----------+----------+
| avg(sal) | min(sal) | max(sal) | sum(sal) | count(*) |
+-------------+----------+----------+----------+----------+
| 2073.214286 | 800.00 | 5000.00 | 29025.00 | 14 |
+-------------+----------+----------+----------+----------+
分组查询
1、在实际的应用中，可能有这样的需求，需要先进行分组，然后对每一组的数据进行操作。
在这个时候我们需要使用分组查询。
select
...
from
...
where
...
group by
...
order by
...
以上关键字的顺序不能颠倒。
执行顺序是什么？
1.from
2.where
3.group by
4.select
5.order by
例如：计算每个部门的工资总和。计算每个岗位的平均薪资。
分组的时候可以按照多个字段进行分组，多个字段之间使用逗号分隔。
2、示例：找出每个岗位的工资总和：
mysql> 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 |
+-----------+----------+
group by job;“job”是岗位
代表按照工作岗位进行分组，job字段值一样的记录被分为同一组数据。
3、示例：找出每个部门的最高薪资：
mysql> select deptno,max(sal) from emp group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
| 10 | 5000.00 |
| 20 | 3000.00 |
| 30 | 2850.00 |
+--------+----------+
按照部门进行分组，一个部门是一组[言外之意：deptno字段值相同的分成一组]
，找出来这一组中薪资最高的用max(sal).
4、【重要结论】在一条select语句当中，如果有group by语句的话，
select后面只能跟参加分组的字段，以及分组函数，
跟其他的没有意义。
按照“某个字段”进行分组“某个字段”值相同就是一组，就只能在查询结果集中出现一次。
【也就是一组只能有一条记录输出】类似(limit 1;)
按照“某个字段”进行分组之后一般配合“分组函数”联合使用。
mysql> select * from emp order by deptno;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
+-------+--------+-----------+------+------------+---------+---------+--------+
分组函数的作用是：在同一个组中，根据某一个字段，找出组中符合条件的“值”，返回的是这个字段中的某一个“值”【不是一整条记录】。
比如上述案例：在部门10中找出最高薪资。使用max(sal),
max(sal)的作用是从2450.00、5000.00、1300.00中找出最大值，最终返回的是5000.00.这个值，并不是整条记录。
select 后面只能跟“参加分组的字段”以及“分组函数”。如果根别的没有任何意义：
mysql> select ename,deptno,max(sal) from emp group by deptno;【ename就没有任何意义】
+-------+--------+----------+
| ename | deptno | max(sal) |
+-------+--------+----------+
| CLARK | 10 | 5000.00 |
| SMITH | 20 | 3000.00 |
| ALLEN | 30 | 2850.00 |
+-------+--------+----------+
ename返回的只是在10部门这组中，默认排序是第一个的员工的姓名，不是工资是5000的员工的姓名。
为啥能根“参加分组的字段”？
实际上跟上参加分组的字段也是返回默认排序是第一个的员工的该字段的“值”，
只是因为在这一组中这个字段的值都是一样的，所以就有意义。
5、找出每个部门，不同工作岗位的最高薪资：
mysql> 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 |
+--------+-----------+----------+
分析题意：每个部门，说明不同的部门是不同的组，按照部门进行分组。
不同工作岗位：说明不同的工作岗位是不同的组，按照工作岗位进行分组。
所以在同一个部门并且工作岗位相同的才是一组。
最高薪资：分组之后使用max(sal),求得最高薪资。
【部门编号和工作岗位都一样才可以分到一组中】
6、找出每个部门最高薪资，要求显示最高薪资大于3000的。
第一种方式：mysql> select deptno,max(sal) from emp where sal > 3000 group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
| 10 | 5000.00 |
+--------+----------+
第二种方式：mysql> select deptno,max(sal) from emp group by deptno having max(sal)>3000;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
| 10 | 5000.00 |
+--------+----------+
可以使用having对分完组之后的数据进一步过滤。
having不能单独只用，必须和group by 联合使用。
having不能代替where，
having后面可以用分组函数，实际上是对查询结果集再次进行过滤，条件只可以写查询结果集中有的字段(可以是原字段名也可以是使用as之后的新字段名)。
【讲究】能用where先用where，不能使用where在使用having，where效率高
7、去除重复记录：使用“distinct”关键字可以去除搜索结果集的重复记录，但是不会影响原表记录
语法结构：
select distinct 字段名1[,字段名2,...] from 表名;
去除查询结果集中“字段名1”并且“字段名2...”都相同的记录。
[如果有相同记录，则返回相同记录中的第一行]
distinct只能出现在所有字段的最前方。
示例：统计以下工作岗位的数量：
select count(distinct(job)) from emp;
扫一扫
275
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
