四、常见函数
4.1 概念
将一组逻辑语句封装在方法提中，对外暴露方法名。类似于python中的方法
优点：
1、隐藏了实现细节
2、提高代码的重用性
调用：select 函数名(实参列表) [ from 表名];
分类：
1、单行函数
如：concat、ifnull、length
包含：字符函数、数学函数、日期函数、其他函数、流程控制函数-if函数、流程控制函数-case结构
2、分组函数
功能：做统计使用又称统计函数（聚合函数、组函数）。
4.2 单行函数
4.2.1 字符函数
1、length获取参数值的字节个数
# 特别需要注意的是，对于非 ASCII 字符（如汉字），LENGTH 函数返回的不是字符的个数，
# 而是字符串的字节数
select length('hello'); select length('你好hello');2、CONCAT拼接字符串
# CONCAT 函数用于将两个或多个字符串连接成一个字符串。
# 它可以连接任意数量的字符串，并返回一个组合后的字符串。
# CONCAT_WS函数，用于指定分隔符连接字符串
SELECT CONCAT_WS('-', '2024', '07', '14');SELECT CONCAT('2026','_','8','_','18');3、upper、lower
# UPPER 函数用于将字符串转换为大写。
# LOWER 函数用于将字符串转换为小写。
SELECT UPPER('hongshaojichi');SELECT LOWER('HONGSHAOJICHI');4、substr
# substr，substring函数用于从字符串中提取子串。
# string 是原始字符串。
# start_position 是子字符串的起始位置（从 1 开始）。
# length 要提取的子串的长度（可选）。
# 注意：索引从1开始
SELECT SUBSTR('我爱吃红烧鸡翅',4,3) out_put;5、instr返回子串第一次出现的索引，如果找不到返回0
# INSTR 函数用于返回一个子字符串在另一个字符串中第一次出现的位置。
# 如果子字符串不在字符串中，则返回 0。INSTR 函数区分大小写。
SELECT INSTR('我爱吃红烧鸡翅','红烧鸡翅');6、trim函数用于删除字符串开头和结尾的空格字符或其他指定的字符。
SELECT TRIM('s'from'ssssssssssss红烧鸡翅sssssss') out_put;7、lpad用指定的字符实现左填充指定长度
rpad用指定的字符实现右填充指定长度
SELECT lpad('红烧鸡翅',8,'*') out_put;SELECT rpad('红烧鸡翅',8,'*') out_put;8、replace 替换
SELECT REPLACE('我要吃红烧鸡翅','鸡翅','排骨') out_put;4.2.2 数学函数
1、round
ROUND 函数用于将数值四舍五入到指定的小数位数。
语法：
ROUND(number, decimals)
其中：
number：要进行四舍五入的数值。
decimals：要保留的小数位数。如果省略，默认值为 0。
SELECT ROUND(3.1415,3) out_put;2、ceil
CEIL 函数（或 CEILING 函数）用于将数值向上取整，返回大于或等于该数值的最小整数。
语法：
CEIL(number)
其中：
number：要向上取整的数值。
SELECT CEIL(3.14) out_put;3、floor
FLOOR 函数用于将数值向下取整，返回小于或等于该数值的最大整数。
语法：
FLOOR(number)
其中：
number：要向下取整的数值。
SELECT FLOOR(3.14) out_put;4、truncate
TRUNCATE 函数用于将数值截断到指定的小数位数，直接去掉多余的小数位而不进行四舍五入。
语法：
TRUNCATE(number, decimals)
其中：
number：要截断的数值。
decimals：要保留的小数位数。
SELECT TRUNCATE(3.1415, 2);5、mod取余
MOD 函数用于计算两个数之间的余数（模运算）。
MOD(N, M) 返回 N 除以 M 的余数。如果 M 为 0，则返回 NULL。
SELECT MOD(10,3);4.2.3 日期函数
1、now
NOW 函数用于返回当前的日期和时间。
语法：
NOW()
结果格式为：YYYY-MM-DD HH:MM:SS
2、curdate
CURDATE 函数用于返回当前的日期，不包括时间部分。
语法：
CURDATE()
结果格式为：YYYY-MM-DD
3、curtime
CURTIME 函数用于返回当前的时间，不包括日期部分。
语法：
CURTIME()
结果格式为：HH:MM:SS
4、str_to_date（常用）
STR_TO_DATE 函数用于将字符串转换为日期和时间格式。
语法：
STR_TO_DATE(string, format)
其中：
string：要转换的日期和时间字符串。
format：指定字符串的格式。
格式化符号：
%Y：四位数字的年份。
%y：两位数字的年份。
%m：两位数字的月份（01 到 12）。
%c：月份，数值（0 到 12）。
%d：两位数字的日期（00 到 31）。
%e：日期，数值（0 到 31）。
%H：两位数字的小时，24 小时制（00 到 23）。
%h：两位数字的小时，12 小时制（01 到 12）。
%i：两位数字的分钟（00 到 59）。
%s：两位数字的秒（00 到 59）。
%p：AM 或 PM。
# eg:查询入职日期为1992-4-3的员工信息
SELECT * FROM employees WHERE hiredate = STR_TO_DATE( '4-3 1992', '%c-%d %Y' );5、date_format
DATE_FORMAT 函数用于将日期或日期时间值格式化为指定的字符串格式。
语法：
DATE_FORMAT(date, format)
其中：
date：要格式化的日期或日期时间值。
format：指定结果字符串的格式。
格式化符号：
%Y：四位数字的年份。
%y：两位数字的年份。
%M：月份名称（January 到 December）。
%m：两位数字的月份（01 到 12）。
%c：月份，数值（1 到 12）。
%D：带有英文序数后缀的月份中的天（1st, 2nd, 3rd, …）。
%d：两位数字的日期（00 到 31）。
%e：日期，数值（0 到 31）。
%H：两位数字的小时，24 小时制（00 到 23）。
%h：两位数字的小时，12 小时制（01 到 12）。
%i：两位数字的分钟（00 到 59）。
%s：两位数字的秒（00 到 59）。
%p：AM 或 PM。
%W：星期名称（Sunday 到 Saturday）。
%w：星期中的天（0 = Sunday, 6 = Saturday）。
%j：一年中的天数（001 到 366）。
# eg 查询有奖金的员工名和入职日期(xx月/xx日 xx年)
SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') AS 入职日期 FROM employees WHERE commission_pct IS NOT NULL;4.2.4 流程控制函数
1、if 函数
IF 函数是用于在查询中进行条件判断的流程控制函数。
语法：
IF(condition, true_value, false_value)
其中：
condition：要评估的条件表达式。如果条件为真（非零或非空），则返回 true_value；否则返回 false_value。
true_value：条件为真时返回的值。
false_value：条件为假时返回的值。
# eg 查询员工的姓和名以及奖金率，如果有奖金率则返回有，没有则返回无并以备注为列名
SELECT last_name, first_name, commission_pct, IF(commission_pct IS NOT NULL,"有","无") as 备注 FROM employees;2、case 函数
CASE 函数（或 CASE 表达式）是一种流程控制函数，类似于编程语言中的 switch 语句
简单形式：
CASE case_expression
WHEN when_expression1 THEN result1
WHEN when_expression2 THEN result2
...
ELSE else_result
END
其中：
case_expression：需要进行比较的表达式或列。
when_expression1, when_expression2, ...：与 case_expression 进行比较的表达式或值。
result1, result2, ...：当 case_expression 等于 when_expression 时返回的结果。
else_result：如果没有 when_expression 匹配时返回的默认结果。
搜索形式：
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE else_result
END
其中：
condition1, condition2, ...：条件表达式，可以是任何布尔表达式。
result1, result2, ...：当条件表达式为真时返回的结果。
else_result：如果没有条件表达式为真时返回的默认结果。
#eg 查询员工的工资，要求：
（1）部门号=30，显示的工资为1.1倍
（2）部门号=40，显示的工资为1.2倍
（3）部门号=50，显示的工资为1.3倍
（4）其他部门，显示的工资为原工资
简单形式：
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;搜索形式：
SELECT salary, department_id, CASE WHEN department_id=30 THEN salary*1.1 WHEN department_id=40 THEN salary*1.2 WHEN department_id=50 THEN salary*1.3 ELSE salary END AS 新工资 FROM employees;五、分组函数
5.1 概念
功能：用作统计使用，又称为聚合函数或统计函数或组函数
分类：
（1）sum 求和
（2）avg 平均值
（3）max 最大值
（4）min 最小值
（5）count 计算个数
特点：
（1）sum、avg一般用于处理数值型，max、min、count可以处理任何类型
（2）以上分组函数都忽略null值
（3）可以和distinct搭配实现去重的运算
（4）一般使用count(*)做统计函数
（5）和分组函数一同查询的字段要求是group by后的字段
5.2 案例
5.2.1 sum函数
SUM 函数用于计算数值列的总和。
语法：
SUM(expression)
其中：
expression：要求和的列或表达式。
SELECT SUM(salary) from employees;5.2.2 avg函数
AVG 函数用于计算数值列的平均值。
语法：
AVG(expression)
其中：
expression：要求平均值的列或表达式。
SELECT AVG(salary) from employees;5.2.3 max/min函数
MAX 函数用于计算数值列的最大值。
MIN 函数用于计算数值列的最小值。
SELECT MAX(salary), MIN(salary) FROM employees;5.2.4 count函数
COUNT 函数用于计算行数或者满足指定条件的行数。
语法：
COUNT(expression)
其中：
expression：可选项，要计数的列或表达式。如果不提供参数，则计算整个结果集的行数。
SELECT COUNT(salary) FROM employees;六、分组查询
6.1 概念
select 分组函数，列（要求出现在group by的后面）
from 表名
[where 筛选条件]
group by 分组的列表
[order by 子句]
特点：
1、分组查询中的筛选条件分为两类：
数据源 位置 关键字
分组前筛选 原始表 group by子句的前面 where
分组后筛选 分组后的结果集 group by子句的后面 having
（1）分组函数做条件肯定是放在having子句中
(2) 能用分组前筛选的，就优先考虑使用分组前筛选
2、group by 子句支持单个字段分组，多个字段分组（多个字段之间用逗号隔开没有顺序要求），
表达式或函数（用的较少）
3、也可以添加排序（排序放在整个分组查询的最后）
6.2 案例
#查询邮箱中包含a字符的，每个部门的平均工资(分组前筛选)
SELECT department_id, avg(salary) FROM employees WHERE email like "%a%" GROUP BY department_id;# 查询哪个部门的员工个数大于2 （分组后筛选）
SELECT COUNT(*), department_id FROM employees GROUP BY department_id HAVING COUNT(*)>2;-- 按表达式或者函数分组
# eg 按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数大于5的有哪些
SELECT count(*), LENGTH(CONCAT(last_name,first_name)) as len FROM employees GROUP BY LENGTH(CONCAT(last_name,first_name)) HAVING count(*)>5;-- 按多个字段分组
# eg 查询每个部门每个工种的员工的平均工资
SELECT department_id, job_id, AVG(salary) FROM employees GROUP BY department_id, job_id;-- 添加排序
# eg 查询每个部门每个工种的员工的平均工资，并且按平均工资的高低显示
SELECT department_id, job_id, AVG(salary) FROM employees GROUP BY department_id, job_id ORDER BY AVG(salary) desc;七、连接查询
7.1 概念
概念：又称多表查询，当查询的字段来自多个表时，就会用到连接查询。
分类：
（1）按年代分类：
sql99标准（推荐）：支持内连接+外连接（左外、右外）+交叉连接
（2）按功能分类：
内连接：等值连接 非等值连接 自连接
外连接：左外连接 右外连接 全外连接
交叉连接
7.2 案例
7.2.1 内连接
语法：
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件；
分类：等值连接 非等值连接 自连接
特点：
（1）添加排序、分组、筛选
（2）inner 可以省略
（3）筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
（4）inner join连接和sql92语法中的等值连接效果时一样的，都是查询多表的交集
-- 等值连接
# eg 查询哪个部门的员工个数大于3的部门名和员工个数，并按个数降序
SELECT count(*), department_name FROM employees e INNER JOIN departments d ON e.department_id=d.department_id GROUP BY department_name HAVING count(*)>3 ORDER BY count(*) desc;-- 非等值连接
# eg 查询工资级别的个数大于20 的个数，并且按工资级别降序
SELECT count(*), grade_level FROM employees e INNER JOIN job_grades j ON e.salary BETWEEN lowest_sal AND highest_sal GROUP BY grade_level HAVING count(*)>20 ORDER BY grade_level desc;-- 自连接
# 查询名中包含字符e的员工的名字、上级的名字
SELECT e.first_name, m.first_name FROM employees e INNER JOIN employees m ON e.manager_id = m.employee_id WHERE e.first_name LIKE '%e%';7.2.2 外连接
-- 外连接
应用场景：用于查询一个表中有，另一个表中没有的记录。
特点：
（1）外连接的查询结果为主表中的所有记录，如果从表中有和它匹配的，则显示匹配的值，如果
从表中没有和它匹配的，则显示null。
外连接查询结果 = 内连接结果 + 主表中有而从表中没有的记录
（2）左外连接，left join左边的是主表；右外连接，right join右边的是主表
（3）左外和右外交换两个表的顺序，可以实现同样的效果
（4）全外连接 = 内连接的结果 + 表1中有但表2中没有的+表2中有但表1中没有的
数据准备：
eg 查询男朋友不在男生表中的女生名。
-- 左外连接
SELECT b.name, bo.* FROM beauty b LEFT JOIN boys bo ON b.boyfriend_id=bo.id WHERE bo.id IS NULL;-- 右外连接
SELECT b.NAME, bo.* FROM boys bo RIGHT OUTER JOIN beauty b ON b.boyfriend_id = bo.id WHERE bo.id IS NULL;
