[数据库]–函数（字符,数学,日期,流程控制,分组函数）

SQL语言–函数

概念：

将一组逻辑语句封装在方法体内，对外暴露方法名

优点：

隐藏了实现细节，提高了代码的重用性

调用语法：

SELECT 函数名(实参列表) 【FROM 表】;

特点：
函数名与函数功能

分类：

1. 单行函数，例如:CONCAT(str1,str2,…),LENGTH(str)等等
2. 分组函数（也叫统计函数，聚合函数，组函数），做统计使用

---

字符函数

# 常见函数

/*
概念：将一组逻辑语句封装在方法体内，对外暴露方法名

优点：隐藏了实现细节，提高了代码的重用性

调用语法：
SELECT 函数名(实参列表) 【FROM 表】;

特点：
函数名与函数功能

分类：
1. 单行函数，例如:CONCAT(str1,str2,...),LENGTH(str)等等
2. 分组函数（也叫统计函数，聚合函数，组函数），做统计使用
*/

# 单行函数又分为：字符函数，数学函数，日期函数，其他函数，流程控制函数

# 字符函数


# LENGTH(str)函数返回字节长度，字节长度取决于编码方式
# utf8中一个字母占一个字节，一个汉字占3个字节
SELECT LENGTH('John');
SELECT LENGTH('张三');  

#CONCAT(str1,str2,...) 用于拼接字符串
SELECT CONCAT(last_name,' ',first_name) 姓名 FROM employees;

# 转大小写
SELECT UPPER('Leslie');
SELECT LOWER('Leslie');

# SUBSTR(str FROM pos FOR len),截取部分字符串(SQL语言中索引从1开始)
SELECT SUBSTR('Hello World',7) AS result;

# 注意这里第二个参数是截取开始的位置，第三个参数是字符长度，不是结束截取的位置
SELECT SUBSTR('Hello World',1,5) AS result;  

# INSTR(str,substr)  ,返回子字符串在原字符串中的索引
SELECT INSTR('Hello World','Wor') AS result;

# TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) 取出左右空格
SELECT TRIM('    Hello     ') AS result;
SELECT TRIM('a' FROM 'aaaaaaaHELaaaaaaLOaaaaaaaa') AS result;  # 指定要去掉的字符，并且只去掉左右两端

# LPAD(str,len,padstr)  ,左填充函数，第一个参数是目标字符串，第二个参数是最终长度，第三个参数是填充字符
# 填充字符可以是多个字符，与此对应同样有右填充RPAD
SELECT LPAD('HELLO',10,'*') AS result;

# REPLACE(str,from_str,to_str)  替换函数（替换所有）
SELECT REPLACE('Hello World','World','Leslie') AS result;

数学函数

# 数学函数

# ROUND(X)  四舍五入
SELECT ROUND(4.555);
SELECT ROUND(1.567,2);  # 小数点后保留两位

# CEIL(X) 向上取整
SELECT CEIL(1.05);
# FLOOR(X) 向下取整
SELECT FLOOR(1.05);

# TRUNCATE(X,D)  截断函数
SELECT TRUNCATE(1.69999,1);

# MOD(N,M)  取余函数  等价于% 本质是 MOD(a,b)等价于a-a/b*a;
SELECT MOD(10,1);

日期函数

# 日期函数

# NOW() 返回当前系统日期+时间
SELECT NOW();

# CURDATE() 返回当前系统日期，不包含时间
SELECT CURDATE();

# CURTIME() 返回当前系统时间，不返回日期
SELECT CURTIME();

# 获取指定部分的日，月，年等信息(小时，分钟，秒)
SELECT YEAR(NOW());
SELECT MONTH('1998-1-1');
SELECT DAY('2020-12-21');

# STR_TO_DATE(str,format) 将日期格式字符转换为指定个式的日期
SELECT STR_TO_DATE('03-11-2019','%d-%m-%Y') AS 日期;

# 查询入职日期为1992-4-3的员工信息
SELECT * FROM employees WHERE hiredate=STR_TO_DATE('4-3 1992','%m-%d %Y');

# DATE_FORMAT(date,format)  将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日--%H时%i分钟%s秒') AS 当前日期;

格式符	功能
%Y	四位的年份
%y	两位的年份
%m	月份（01，02，03…,12）
%c	月份（1，2，3…12）
%d	日（01，02，03…）
%H	小时（二十四小时制）
%h	小时（十二小时制）
%i	分钟（00，01…59）
%s	秒（00，01…59）

---

其他函数

# 其他函数

# VERSION() 查看当前版本号
SELECT VERSION();
# DATABASE() 查看当前所在的库
SELECT DATABASE();
# USER() 查询当前用户
SELECT USER();

流程控制函数

# 流程控制函数

# IF(expr1,expr2,expr3) 实现类似if...else的效果
# 类似于三元运算符，表达式1的值成立返回表达式2的值，否则返回表达式3的值
SELECT IF('10>5','大于','小于') AS result;



# CASE函数，实现类似于switch...case效果
/* 

格式
CASE case_value
    WHEN when_value THEN
        statement_list
    ELSE
        statement_list
END CASE;


*/

# 查询员工工资，要求部门号=30的显示的工资为1.1倍
# 部门号为40的，显示的工资为1.2倍，其他部门原价显示

SELECT
    last_name,
    department_id,
    salary 工资,
CASE department_id
    WHEN 30 THEN
        salary*1.1
    WHEN 40 THEN
        salary*1.2
    ELSE salary
END AS 新工资 
FROM
    employees;

# case函数的使用二
# 工资大于20000，显示A级别
# 工资大于15000，显示B级别
# 工资大于10000，显示C级别
SELECT
    last_name,
    salary,
CASE 
    WHEN salary>20000 THEN 'A'
    WHEN salary>15000 THEN 'B'
    WHEN salary>10000 THEN 'C'
    ELSE 'D'
END AS 工资等级
FROM
    employees;

---

---

分组函数

分类：

sum 求和，avg 平均值，max 最大值，min 最小值

特点：

1. sum,avg可以处理数值型数据，max,min,count可以处理任何类型数据
2. 以上几个分组函数都会自动忽略null值
3. 可以和distinct搭配使用

# 分组函数

/*
分类：sum 求和，avg 平均值，max 最大值，min 最小值

特点：
1. sum,avg可以处理数值型数据，max,min,count可以处理任何类型数据
2. 以上几个分组函数都会自动忽略null值
3. 可以和distinct搭配使用
*/



# sum求和
SELECT SUM(salary) FROM employees;
# avg求平均值
SELECT AVG(salary) FROM employees;
# max最大值
SELECT MAX(salary) FROM employees;
# min最小值
SELECT MIN(salary) FROM employees;

# count统计有效数据个数（非null值）
SELECT COUNT(salary) FROM employees;


# 和distinct搭配使用,取出重复内容
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;

# count()函数
SELECT COUNT(*) FROM employees;  # 可以用来统计实际有效行数
SELECT COUNT(1) FROM employees;

分组查询

语法：

SELECT 分组函数,列（要求出现在group_by后面）
FROM 表
【WHERE 筛选条件】
GROUP BY 分组列表
【ORDER BY 子句】

注意：

查询列表比较特殊，要求是分组函数和group_by后出现的字段

分组查询中的筛选可以分为两类

1. 分组前的筛选：分组前的筛选也就是筛选的内容在数据库中就存在，
   可以直接利用对应列筛选，利用where语句筛选，位置在group_by字句的前面
2. 分组后的筛选：分组后的筛选是利用已经重新分配的组内的信息进行筛选，这些信息不直接存储于数据库中。利用having语句筛选，位置在group_by字句的后面

# 分组查询
/*
语法：

SELECT 分组函数,列（要求出现在group_by后面）
FROM 表
【WHERE 筛选条件】
GROUP BY 分组列表
【ORDER BY 子句】

注意：查询列表比较特殊，要求是分组函数和group_by后出现的字段

分组查询中的筛选可以分为两类
1. 分组前的筛选：分组前的筛选也就是筛选的内容在数据库中就存在，
可以直接利用对应列筛选，利用where语句筛选，位置在group_by字句的前面
2. 分组后的筛选：分组后的筛选是利用已经重新分配的组内的信息进行筛选，
这些信息不直接存储于数据库中。利用having语句筛选，位置在group_by字句的后面
*/

# 查询每个部门平均工资
SELECT AVG(salary) AS 平均工资,job_id AS 部门
FROM employees
GROUP BY job_id;

# 查询每个位置上的部门个数
SELECT COUNT(*) ,location_id
FROM departments
GROUP BY location_id; 

# 添加筛选条件
# 查询邮箱中包含A字符的，每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
WHERE email LIKE '%A%'
GROUP BY department_id;

# 查询哪个部门员工个数大于2（添加分组后的筛选）
/*
这里不是利用employees表中的原数据进行筛选，
而是根据筛选后的结果进行二次筛选，
所以不能再用where关键字，而是在最后追加having关键字
*/
SELECT COUNT(*) ,department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;


# 查询每个部门有奖金的最高工资
SELECT MAX(salary) ,job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id;


# 查询领导编号大于102，并且其手下最低工资大于5000的领导
SELECT MIN(salary) ,manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000;