怎样在SQL中高效使用聚合函数、日期函数和字符串函数：实用本领与案例分析

聚合函数

函数阐明COUNT([DISTINCT] expr)返回查询到的数据的 数量SUM([DISTINCT] expr)返回查询到的数据的 总和，不是数字没故意义AVG([DISTINCT] expr)返回查询到的数据的 均匀值，不是数字没故意义MAX([DISTINCT] expr)返回查询到的数据的 最大值，不是数字没故意义MIN([DISTINCT] expr)返回查询到的数据的 最小值，不是数字没故意义

• 这个是查询的效果
  
  
  
  

• COUNT
  
  
  
  
  
  
  
  
  
  
  
  
  

1. -- 使用 * 做统计，不受 NULL 影响
2. count(*) from students;
3. +----------+
4. | count(*) |
5. +----------+
6. |        4 |
7. +----------+
8. 1 row in set (0.00 sec)
9. -- 使用表达式做统计
10. select count(1) from students;
11. +----------+
12. | count(1) |
13. +----------+
14. |        4 |
15. +----------+
16. 1 row in set (0.00 sec)
17. 统计班级收集的 qq 号有多少
18. -- NULL 不会计入结果
19. select count(qq) from students;
20. +-----------+
21. | count(qq) |
22. +-----------+
23. |         1 |
24. +-----------+
25. 1 row in set (0.00 sec)
26. 统计本次考试的数学成绩分数个数
27. --COUNT(math) 统计的是全部成绩
28. select count(math) from exam_result;
29. +-------------+
30. | count(math) |
31. +-------------+
32. |           7 |
33. +-------------+
34. 1 row in set (0.00 sec)

复制代码

• SUM
  统计数学结果总分
  
  
  
  
  
  
  

1. select sum(math) from exam_result;
2. +-----------+
3. | sum(math) |
4. +-----------+
5. |       583 |
6. +-----------+
7. 1 row in set (0.00 sec)
8. -- 不及格 < 60 的总分，没有结果，返回 NULL SELECT SUM(math) FROM exam_result WHERE math< 60;
9. select sum(math) from exam_result where math < 60;
10. +-----------+
11. | sum(math) |
12. +-----------+
13. |      NULL |
14. +-----------+
15. 1 row in set (0.00 sec)

复制代码

• AVG
  统计均匀总分
  
  
  
  

1. select avg(chinese + math + english) 平均分 from exam_result;
2. +--------------------+
3. | 平均分             |
4. +--------------------+
5. | 221.42857142857142 |
6. +--------------------+
7. 1 row in set (0.00 sec)

复制代码

• MAX
  返回英语最高分
  
  
  
  

1. select MAX(english) from exam_result;
2. +--------------+
3. | MAX(english) |
4. +--------------+
5. |           90 |
6. +--------------+
7. 1 row in set (0.00 sec)

复制代码

返回 > 70 分以上的数学最低分

1. select min(math) from exam_result where math > 70;
2. +-----------+
3. | min(math) |
4. +-----------+
5. |        73 |
6. +-----------+
7. 1 row in set (0.00 sec)

复制代码

group by子句的使用

在select中使用group by 子句可以对指定列举行分组查询
语法：select column1, column2, .. from table group by column;
案例：

• 准备工作，创建一个雇员信息表（来自oracle 9i的经典测试表）
  
• 
  
  
  
  • EMP员工表
    
  
  
• 
  
  
  
  • DEPT部分表
    
  
  
• 
  
  
  
  • SALGRADE工资品级表
    
  
  
• 怎样表现每个部分的均匀工资和最高工资
  

1. select deptno,avg(sal),max(sal) from EMP group by deptno;

复制代码

• 表现每个部分的每种岗位的均匀工资和最低工资
  

1. select avg(sal),min(sal),job, deptno from EMP group by deptno, job;

复制代码

• 表现均匀工资低于2000的部分和它的均匀工资
  
• 
  
  
  
  • 统计各个部分的均匀工资
    
  
  

1. select avg(sal) from EMP group by deptno

复制代码

• 
  
  
  
  • having和group by共同使用，对group by效果举行过滤
    
  
  

1. select avg(sal) as myavg from EMP group by deptno having myavg<2000;
2. --having经常和group by搭配使用，作用是对分组进行筛选，作用有些像where。

复制代码

实战OJ

牛客：批量插入数据
牛客：找出全部员工当前薪水salary情况
牛客：查找最晚入职员工的全部信息
牛客：查找入职员工时间排名倒数第三的员工全部信息
查找薪水记载凌驾15条的员工号emp_no以及其对应的记载次数t
日期函数

• 得到年代日：
  
  
  
  

1. select current_date();
2. +----------------+
3. | current_date() |
4. +----------------+
5. | 2025-03-21     |
6. +----------------+
7. 1 row in set (0.00 sec)

复制代码

• 得到时分秒：
  
  
  
  

1. select current_time();
2. +----------------+
3. | current_time() |
4. +----------------+
5. | 09:21:46       |
6. +----------------+
7. 1 row in set (0.00 sec)

复制代码

• 得到时间戳：
  
  
  
  

1. select current_timestamp();
2. +---------------------+
3. | current_timestamp() |
4. +---------------------+
5. | 2025-03-21 09:22:22 |
6. +---------------------+
7. 1 row in set (0.00 sec)

复制代码

• 在日期的根本上加日期：
  
  
  
  

1. select date_add('2017-10-28',interval 10 day);
2. +----------------------------------------+
3. | date_add('2017-10-28',interval 10 day) |
4. +----------------------------------------+
5. | 2017-11-07                             |
6. +----------------------------------------+
7. 1 row in set (0.04 sec)

复制代码

• 在日期的根本上减去时间：
  
  
  
  

1. select date_sub('2017-10-28',interval 10 day);
2. +----------------------------------------+
3. | date_sub('2017-10-28',interval 10 day) |
4. +----------------------------------------+
5. | 2017-10-18                             |
6. +----------------------------------------+
7. 1 row in set (0.00 sec)

复制代码

• 盘算两个日期之间相差多少天：
  
  
  
  

1. mysql> select datediff('2017-10-10', '2016-9-1');
2. +------------------------------------+
3. | datediff('2017-10-10', '2016-9-1') |
4. +------------------------------------+
5. |                                404 |
6. +------------------------------------+
7. 1 row in set (0.00 sec)

复制代码

案例-1：

• 创建一张表，记载生日
  

1. create table tmp(
2. id int primary key auto_increment,
3. birthday date
4. );

复制代码

• 添加当前日期：
  

1. mysql> insert into tmp(birthday) values(current_date());
2. Query OK, 1 row affected (0.01 sec)
4. mysql> select * from tmp;
5. +----+------------+
6. | id | birthday   |
7. +----+------------+
8. |  1 | 2025-03-21 |
9. +----+------------+
10. 1 row in set (0.00 sec)

复制代码

案例-2：

• 创建一个留言表
  

1. mysql> create table msg (
2. id int primary key auto_increment,
3. content varchar(30) not null,
4. sendtime datetime
5. );

复制代码

插入数据

1. mysql>  insert into msg(content,sendtime) values('hello1', now());
2. Query OK, 1 row affected (0.01 sec)
4. mysql>  insert into msg(content,sendtime) values('hello2', now());
5. Query OK, 1 row affected (0.00 sec)

复制代码

• 表现全部留言信息，发布日期只表现日期，不消表现时间
  

1. select content,date(sendtime) from msg;

复制代码

• 请查询在2分钟内发布的帖子
  

1. select * from msg where date_add(sendtime, interval 2 minute) > now();
2. 理解：
3. ------------------------------|-----------|-------------|------------------
4.                            初始时间     now()       初始时间+2min           

复制代码

字符串函数

案例:

• 获取emp表的ename列的字符集
  

1. select charset(ename) from EMP;

复制代码

• 要求表现exam_result表中的信息，表现格式：“XXX的语文是XXX分，数学XXX分，英语XXX分”
  

1. select concat(name, '的语文是',chinese,'分，数学是',math,'分') as '分数' from student;

复制代码

• 修业生表中门生姓名占用的字节数
  

1. select length(name), name from student;

复制代码

  注意：length函数返回字符串长度，以字节为单元。假如是多字节字符则盘算多个字节数；假如是单字节字符则算作一个字节。好比：字母，数字算作一个字节，中文表现多个字节数（与字符集编码有关）
  

• 将EMP表中全部名字中有S的更换成’上海’
  

1. select replace(ename, 'S', '上海') ,ename from EMP;

复制代码

• 截取EMP表中ename字段的第二个到第三个字符
  

1. select substring(ename, 2, 2), ename from EMP;

复制代码

• 以首字母小写的方式表现全部员工的姓名
  

1. select concat(lcase(substring(ename, 1, 1)),substring(ename,2)) from EMP;

复制代码

数学函数

• 绝对值
  
  
  
  

1. select abs(-100.2);

复制代码

• 向上取整
  
  
  
  

1. select ceiling(23.04);

复制代码

• 向下取整
  
  
  
  

1. select floor(23.7);

复制代码

• 生存2位小数位数（小数四舍五入)
  

1. select format(12.3456, 2);

复制代码

• 产生随机数
  
  
  
  

1. select rand();

复制代码

获取0 ~ 99的随机数

别的函数

• user() 查询当前用户
  

1. select user();

复制代码

• md5(str)对一个字符串举行md5择要，择要后得到一个32位字符串
  

1. select md5('admin')
2. +----------------------------------+
3. | md5('admin')                     |
4. +----------------------------------+
5. | 21232f297a57a5a743894a0e4a801fc3 |
6. +----------------------------------+

复制代码

• database()表现当前正在使用的数据库
  

1. select database();

复制代码

• password()函数，MySQL数据库使用该函数对用户加密
  

1. select password('root');
2. +-------------------------------------------+
3. | password('root')                          |
4. +-------------------------------------------+
5. | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
6. +-------------------------------------------+

复制代码

• ifnull（val1， val2） 假如val1为null，返回val2，否则返回val1的值
  

1. select ifnull('abc', '123');
2.    +----------------------+
3.    | ifnull('abc', '123') |
4.    +----------------------+
5.    | abc                 |
6.    +----------------------+
7.    1 row in set (0.01 sec)
8.    
9. select ifnull(null, '123');
10.    +---------------------+
11.    | ifnull(null, '123') |
12.    +---------------------+
13.    | 123                 |
14.    +---------------------+
15.    1 row in set (0.00 sec)

复制代码