【数据库学习笔记3】—— 单行/多行处理函数；分组查询

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

单行处理函数

• 数据处理函数的特点：一个输入对应一个输出
• 具体由如下数据处理函数

函数名称	作用
lower	转换为小写
upper	转换为大写
substr	取子串（substr（被截取的字符串，起始下标，截取的长度））
length	取长度
trim	去掉空格
str_to_date	将字符串转换为日期
date_format	格式化日期
round	四舍五入
rand()	生成随机数
ifnull	可以将null转换为一个具体的值
format	设置千分位

lower —— 转小写

mysql> select lower(name) as name from emp;

upper —— 转大写

mysql> select upper(name) as name from emp;

substr —— 取子串

## substr（被截取的字符串，起始下标，截取的长度）
mysql> select substr(name, 1, 1) as name from emp;

• 例子：找出员工名字第一个字母为a的员工信息

  • 第一种方式：模糊查询

  mysql> select name from emp where name like 'a%';
  

  • 第二种方式：substr函数

  mysql> select name from emp where substr(name, 1, 1) = 'a';
  

length —— 取长度

mysql> select length(name) as length from emp;

concat —— 进行字符串拼接

mysql> select concat(empno, name) from emp;

• 例子：首字母大写

mysql> select concat(upper(substr(name, 1, 1)), substr(name, 2, length(name) - 1)) as name from emp;

trim —— 去除前后空格

mysql> select * from emp where ename = trim('    King');

ifnull —— 可以将null转换为一个具体值

mysql> select name, (sal + ifnull(comm, 0)) * 12 from emp;

注意：NULL参与运算，那么最后结果一定是NULL！为了避免这种情况，所以要使用ifnull

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NdEEoQwv-1666335956281)(C:\Users\HUAWEI\AppData\Roaming\Typora\typora-user-images\image-20221011140358473.png)]

• case…when…then…when…then…else…end：这个函数可以当成if…else

• 例如：

mysql> select sum(销售额) from 订单;
+----------------+
| sum(销售额)    |
+----------------+
|      139556000 |
+----------------+
1 row in set (0.01 sec)

四、分组函数/聚合函数/多行处理函数

多行处理函数

• 多行处理函数特点：多个输入对应一个输出（输入多行，最终输出一行）

sum	求和
count	计数
avg	求平均值
max	求最大值
min	求最小值

注意：

分组函数在使用的时候必须先进行分组，然后才能使用

如果没有对数据进行分组，那么整张表就是默认为一组

mysql> select max(销售额) from 订单;
+----------------+
| max(销售额)    |
+----------------+
|        6000000 |
+----------------+
1 row in set (0.01 sec)

mysql> select min(销售额) from 订单;
+----------------+
| min(销售额)    |
+----------------+
|        1500000 |
+----------------+
1 row in set (0.00 sec)

mysql> select avg(销售额) from 订单;
+----------------+
| avg(销售额)    |
+----------------+
|   3033826.0870 |
+----------------+
1 row in set (0.00 sec)

mysql> select count(销售额) from 订单;
+------------------+
| count(销售额)    |
+------------------+
|               46 |
+------------------+
1 row in set (0.00 sec)

mysql> select sum(销售额) from 订单;
+----------------+
| sum(销售额)    |
+----------------+
|      139556000 |
+----------------+
1 row in set (0.00 sec)

• 分组函数使用的时候需要注意的几点：

  1. 分组函数自动忽略NULL，不需要提前处理NULL

  2. 分组函数中count（*） 和count（具体字段）的区别

     • count（*）：表示统计该表的所有行数
     • count（具体字段）：表示统计该字段下所有不为NULL的元素总数

  3. 分组函数不能够直接使用在where字句中

     • 例子：找出比最低工资高的员工信息

     ###错误写法### 因为在where语句执行前没有分组，自然不可能使用分组函数(yuan'li分组查询)
     mysql> select name, sal from emp where sal > min(sal);
     
     
     ###正确写法###
     

  4. 所有分组函数可以一起使用

五、分组查询（非常重要！！）

分组查询的应用情况

需要先进行分组，然后对每一组的数据进行操作

分组查询格式

mysql> select ... from ... group by ...

数据库关键字执行顺序：

select ...
from ...
where ...
group by ...
order by ...

##执行顺序为##
1. from
2. where
3. group by
4. select 
5. order by

• 例子：查询每个部门，不同工作岗位的最高工资

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

• 例子：找出每个部门最高薪资，并且显示最高薪资大于3000的

##having 语句可以是分完组后的数据进一步进行过滤，

having 必须和 group by 联合使用。不能单独使用

##方法一：先分组，后过滤
mysql>
select max(sal)
from emp
group by deptno
having mas(sal) > 3000;

##方法二：先过滤，后分组
mysql>
select max(sal)
from emp
where sal > 3000
group by deptno