【Mysql数据库基础02】各种函数大汇总：字符函数、数学函数、日期函数

1 单行函数

1.1 常用函数

1.1.1 length 字符串的长度

LENGTH() 函数用于返回一个字符串的字符数（即字符串的长度）
例如：姓名字节长度

length(last_name)

1.1.2 ifnull 判断表达式是否为空

IFNULL() 函数用于判断一个表达式是否为 NULL，如果为 NULL，则返回指定的替代值；如果不为 NULL，则返回原始值。
例如：如果奖金率的值为 NULL，那么返回 0；否则返回奖金率的实际值。

ifnull(commission_pct,0)

1.2 字符函数

1.2.1 substr 提取自串

SUBSTR() 函数用于提取字符串的子串（子序列）。
例如：提取从第7位开始的5个字符，结果为 ‘World’

substr('Hello World', 7, 5); 

1.2.2 转换大小写

1. LOWER() 函数：将字符串转换为小写。

SELECT LOWER('Hello World'); -- 输出 'hello world'

2. UPPER() 函数：将字符串转换为大写。

SELECT UPPER('Hello World'); -- 输出 'HELLO WORLD'

1.2.3 instr 返回起始索引

INSTR() 函数用于返回一个字符串在另一个字符串中第一次出现的位置。
例如：'World’在’Hello World’中从第7个位置开始，返回 7

SELECT INSTR('Hello World', 'World'); 

1.2.4 trim 去除两端指定字符

TRIM() 函数用于去除字符串开头、结尾或两端指定字符（或空格）。
例如：

SELECT LTRIM('   Hello   '); -- 输出 'Hello'，去除了开头和结尾的空格
SELECT LTRIM('x' FROM 'xxxHelloxxxxxx'); -- 输出 'Hello'，去除了开头和结尾的 'x'

1.2.5 lpad 左填充指定长度

LPAD() 函数用于向字符串的左侧填充指定的字符，使字符串达到指定的长度。
例如：

SELECT LPAD('hello', 10, '*'); -- 输出 '*****hello'

1.2.6 substring_index()

SUBSTRING_INDEX() 函数用于在字符串中根据指定分隔符获取子串的部分内容。它的语法如下：

SUBSTRING_INDEX(str, delim, count)

• str 是要进行处理的字符串。
• delim 是分隔符，函数会根据这个分隔符来确定子串的边界。
• count 是指定截取子串的部分内容的次数。
  • 如果 count > 0，则从左往右截取子串，返回前 count 个分隔符之前的内容。
  • 如果 count < 0，则从右往左截取子串，返回后 -count 个分隔符之后的内容。

下面是一个示例，假设有一个字符串 str = 'apple,banana,cherry,date'，我们想根据逗号分隔符获取第一个和最后一个子串：

SELECT 
    SUBSTRING_INDEX(str, ',', 1) AS first_word,
    SUBSTRING_INDEX(str, ',', -1) AS last_word
FROM
    (SELECT 'apple,banana,cherry,date' AS str) AS data;

在这个例子中，SUBSTRING_INDEX(str, ',', 1) 返回第一个逗号之前的内容，即 'apple'；SUBSTRING_INDEX(str, ',', -1) 返回最后一个逗号之后的内容，即 'date'。

1.2.7 replace 替换字符

REPLACE 函数用于替换字符串中的子字符串。REPLACE 函数的语法如下：

REPLACE(str, old_str, new_str)

例如：将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005

update titles_test
set emp_no = replace(emp_no,10001,10005)
where id = 5;

1.2.8 group_contact( ) 分组内的多行数据拼接成一个字符串

GROUP_CONCAT() 函数用于将分组内的多行数据拼接成一个字符串，并返回一个包含所有值的字符串。
举个例子，如果有一个 students 表包含学生名字和对应的成绩，您想将每个班级的成绩汇总成一个字符串，可以使用 GROUP_CONCAT() 函数：

SELECT class, GROUP_CONCAT(score separator ', ') AS scores
FROM students
GROUP BY class;

上面的查询将返回每个班级的学生成绩作为一个字符串，并用逗号分隔。例如，如果有一个名为 math 的班级，对应的成绩分别为 85、90 和 88，那么结果可能会是 85, 90, 88。

例如：

select dept_no,group_concat(emp_no separator ',') as employees
from dept_emp
group by dept_no

1.3 数学函数

1.3.1 round 四舍五入

ROUND() 函数用于对一个数进行四舍五入，可以指定保留小数点后的位数。
例如：

SELECT ROUND(3.14159); -- 输出结果为 3，将小数部分四舍五入
SELECT ROUND(3.14159, 2); -- 输出结果为 3.14，将小数部分四舍五入保留两位

1.3.2 ceil 向上取整

CEIL(x)：向上取整，返回大于或等于x的最小整数。

1.3.3 floor 向下取整

FLOOR(x)：向下取整，返回小于或等于x的最大整数。

1.3.4 truncate 截断

TRUNCATE(x, d)：截断，将x保留到指定小数位数d。

1.3.5 mod 取余

MOD(x, y)：取余，返回x除以y的余数。

---

SELECT CEIL(3.14); -- 输出 4
SELECT FLOOR(3.14); -- 输出 3
SELECT TRUNCATE(3.14159, 2); -- 输出 3.14
SELECT MOD(10, 3); -- 输出 1

1.4 日期函数

1.4.1 now 返回当前日期+时间

NOW()：返回当前日期和时间。

select now();

1.4.2 curdate 返回当前日期

CURDATE()：返回当前日期。

1.4.3 curtime 返回当前时间

CURTIME()：返回当前时间。

1.4.4 year month day获取时间的指定部分

YEAR(date)：返回日期date的年份部分。
MONTH(date)：返回日期date的月份部分。
DAY(date)：返回日期date的日份部分。

1.4.5 str_to_date 通过字符转换为日期

例如：

select str_to_date('2001-2-28','%Y-%c-%d') as output;

1.4.6 date_format 将日期转换为字符

1.5 流程控制函数

1.5.1 if

例如：

select if(10>5,'big','small');

1.5.2 case

基本语法：

CASE case_expression
    WHEN when_expression_1 THEN result_1
    WHEN when_expression_2 THEN result_2
    ...
    ELSE else_result
END

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

select 
	salary as 原始工资,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：
查询员工的工资的情况
如果工资>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;

1.6 其他函数

1. version( ) 查看版本号
2. database( ) 查看数据库
3. user( ) 查看用户

1.7 练习

一、

select now();

二、

select
	employee_id,last_name,salary,salary*(1+0.2) as "new salary"
from
	employees;

三、

select
	last_name,length(last_name) as 姓名的长度
from
	employees
order by
	substr(last_name,1,1) asc;

四、

select
	concat(last_name," earns ",salary," monthly but wants ",salary*3) as "Dream Salary"
from
	employees
where
	salary = 24000;

五、

select
	last_name,job_id,
	case job_id
		when 'AD_PRES' then 'A'
		when 'ST_MAN' then 'B'
		when 'IT_PROG' then 'C'
		when 'SA_REP' then 'D'
		when 'ST_CLERK' then 'E'
	end as Grade
from
	employees;

2 排序查询

在MySQL中，ORDER BY 子句用于对查询结果进行排序。可以按照一个或多个列对结果进行排序，以便以特定的顺序呈现数据。
升序asc 降序desc

2.1 练习

一、查询员工的姓名和部门号和年薪，按年薪降序，按姓名升序

select
	last_name,department_id,salary*12*(1+ifnull(commission_pct,0) as 年薪
from
	employees
order by
	年薪 desc,last_name asc;

二、选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序

select 
	last_name,salary
from 
	employees
where 
	salary not between 8000 and 17000
order by 
	salary desc;

三、查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序

select
	*
from
	employees
where
	email like '%e%'
order by
	length(email) desc,department_id asc;