1.2.4 SQL基础 - DQL语言的学习 - 常见函数

DQL语言的学习-常见函数

DQL语言的学习（Data Query Language）

进阶4：常见函数

• 功能：类似于Java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
• 好处：1、隐藏了实现细节；2、提高代码的重用性
• 调用：select 函数名（实参列表）【from 表】（函数的参数用到了表里的字段，需要加from 表）
• 特点：①叫什么（函数名）②干什么（函数功能）
• 分类：
  ① 单行函数: 如concat、length、ifmull （输入参数，输出一个结果）
  ② 分组函数：做统计使用、又称为统计函数、聚合函数、组函数（输出一组数，输出一个结果）

一、单行函数

1、字符函数（参数类型为字符型）

	length 获取字节个数
	concat拼接
	substr截取子串
	upper转换成大写
	lower转换成小写
	trim去前后指定的空格和字符
	ltrim去左边空格
	rtrim去右边空格
	replace替换
	lpad左填充
	rpad右填充
	instr返回子串第一次出现的索引

1） length 获取字节（不是字符个数）个数

select length('John') ;        #返回4
select length('张三123abc');   #返回12

2）concat拼接

select concat(last_name,'_','first_name') as 姓名 from employees;

3） upper转换成大写、lower转换成小写

将姓变大写，名变小写，然后拼接

select concat(upper(last_name),'_',lower(first_name)) as 姓名 from employees;

4） substr或substring截取子串 （索引从1开始）

substr(str,pos)：截取从索引位置开始后面所有的字符
substr(str,pos，length）：截取从索引处指定字符长度的字符

select substr('李莫愁爱上了陆展元',7) out_put;     # 展示结果为  陆展元
select substr('李莫愁爱上了陆展元',1,3) out_put;   # 展示结果为  李莫愁

案例：姓名中首字符大写，其他字符小写，然后用_拼接，显示出来

select concat(upper(substr(last_name,1,1)),'_',lower(substr(last_name,2))) out_put
from employees;

5） instr返回子串第一次出现的索引,若找不到，返回0

instr(str,substr)

6） trim去前后指定的空格和字符；ltrim去左边空格，rtrim去右边空格；

select trim('    张翠山    ') AS out_put;    #返回张翠山
select trim('a' from'aaaaaa张aaa翠山aaaa') AS out_put;   #返回张aaa翠山 
select trim('aa' from'aaaaaaa张aaa翠山aaaa') AS out_put;    #返回a张aaa翠山

7）lpad左填充 rpad右填充：用指定的字符实现左（右）填充指定长度，超过会截断

select lpad('殷素素',10,'*') as out_put;    #返回  *******殷素素
select lpad('殷素素',2,'*') as out_put;     #返回  殷素

8）replace替换

select replace('张无忌爱上周芷若周芷若','周芷若','赵敏') as out_put;
-------------------------
张无忌爱上赵敏赵敏

2、数学函数（数值型）

1）round 四舍五入

select round(-1.55);
--------------------------
-2
select round(-1.555,2);   #小数点后保留两位
--------------------------------
-1.56

2）ceil向上取整：返回>=该参数的最小整数

select ceil(-1.002);
----------------------
-1
select ceil(-1.00);
--------------------
-1

3）floor向下取整： 返回<=该参数的最大整数

select floor(-1.002);   # 返回-2
select floor(-1.00);    # 返回-1

4）truncate截断，保留指定小数位数

select truncate(1.78,1);   # 小数点后保留一位
---------
1.7

5）mod取余

select mod(10,3);     # 1
select mod(10,-3);    # 1
select 10%-3;         # 1

6）rand 随机数：获取0-1之间的随机数，无限接近于1，但取不到1

3、日期函数（日期型）

1）now当前系统日期+时间；curdate当前系统日期，不包含时间；curtime当前系统时间，不包含日期

select now();
------------------
2021-06-24 20:56:20

select curdate();
-----------------------
2021-06-24

select curtime();
------------------
20:56:20

2）获取指定的部分，年year()、月month()或英文月monthname()、日day、小时hour、分钟minute、秒second

select year(now());
select year('1998-1-1');

3）str_to_date 将日期格式的字符转换成指定格式的日期；date_format将日期转换成字符

日期格式

str_to_date 将日期格式的字符转换成指定格式的日期；

select str_to_date('9-3-1999','%m-%d-%Y');   按照'%m-%d-%Y'的格式解析它
----------------------
1999-09-13

案例： 查询入职日期为1992-4-3的员工信息

select * 
from employees
where hiredate ='1992-4-3';

select * 
from employees
where hiredate =str_to_date('4-3 1992','%c-%d %Y');
select str_to_date('4-3 1992','%c-%d %Y');

date_format将日期转换为指定格式的字符

select date_format('2018/6/16','%Y年%m月%d日');
--------------------------
2018年06月16日

select date_format(now(),'%Y年%m月%d日');

案例： 查询有奖金的员工名和入职日期（xx月/xx日 xx年）

select last_name,date_format(hiredate,'%m月%d日 %y年')
from employees
where commission_pct is not null;

4）datediff 返回两个日期相差的天数

4、其他函数【补充】

1）version：版本

select version();

2）database：当前库

select database();

3）user：当前连接用户

select user();

4）MD5 返回字符的加密形式

MD5（’字符‘）

select md5('周');

5、流程控制函数【实现分支结构】

1）if 处理双分支

if (条件表达式，表达式1，表达式2) ;如果表达式成立，返回表达式1，否则返回表达式2

select if(10>5,'对','错');     
------------------------------
对

select last_name,commission_pct,IF(commission_pct is null,'有奖金','没奖金') 备注
from employees;

2）case语句 处理多分支

使用1：处理等值判断

case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1；
when 常量2 then 要显示的值2或语句2；
when 常量3 then 要显示的值3或语句3；
·····
else 要显示的值n或语句n；
end

案例：查询员工的工资
部门号=30，显示的工资为1.1倍；
部门号=40，显示的工资为1.2倍；
部门号=50，显示的工资为1.3倍；
其他部门，显示原工资；

select salary 原始工资,department_id,
case department_id
when 30 then salary*1.1
when 40 then salary*1.2
when 50 then salary*1.3
else salary
end as 新工资
from employees;

使用2：处理条件判断，类似于多重if

case 
when 条件1 then 要显示的值1或语句1；
when 条件2 then 要显示的值2或语句2；
when 条件3 then 要显示的值3或语句3；
······
else 条件3 then 要显示的值n或语句n；
end 

案例：查看员工的工资情况
如果工资大于20000，显示A级别；
如果工资大于15000，显示B级别；
如果工资大于10000，显示C级别；
否则显示D级别；

select salary,
case 
when salary>20000 then 'A'
when salary>15000 then 'B'
when salary>10000 then 'C'
else 'D'
end as 工资级别
from employees;

二、分组函数

sum 求和、max 最大值、min 最小值、avg 平均值、count 计数（计算非空字符个数）

特点：

1、sum(),avg()一般用于处理数值型；max(),min()、count可以处理任何类型
2、以上分组函数都忽略Null值
3、都可以搭配distinct使用，用于统计去重后的结果
4、count函数的单独介绍，一般使用count(*)用作统计结果集的行数；count(字段) 统计非空值的个数
5、和分组函数一同查询的字段要求是group by 后的字段

1. 简单使用

select sum(salary) from employees;
select avg(salary) from employees;
select max(salary) from employees;
select min(salary) from employees;
select count(salary) from employees;

select sum(salary) 和, avg(salary) 均值,max(salary) 最大值,min(salary) 最小值,count(salary) 和
from employees;

select sum(salary) 和, round(avg(salary),2) 均值,max(salary) 最大值,min(salary) 最小值,count(salary) 和
from employees;

2. 参数支持类型

#  字符型与数值型没有意义
select sum(last_name) 和, avg(last_name) 均值
from employees;

select sum(hiredate) 和, avg(hiredate) 均值
from employees;

select max(last_name) 最大值,min(last_name) 最小值
from employees;

select max(hiredate) 最大值,min(hiredate) 最小值
from employees;

3. 和distinct搭配，去重之后再运算（distinct expr）

select sum(distinct salary) from employees;
select avg(distinct salary) from employees;
select max(distinct salary) from employees;
select min(distinct salary) from employees;
select count(distinct salary) from employees;

4. count函数的详细介绍

count(*)统计对象个数：一行有一个字段不为空，就计数
count(1)统计对象个数：一行有一个字段不为空，就计数（除1，还可以是其他任意常量值）

select count(salary) from employees;
select count(*) from employees;
select count(*) from employees;

5. 和分组函数同一查询的字段有限制

select count(salary),employee_id from employees;   #不规范，无意义