MySQL函数

MySQL函数

字符串函数

• concat(str1,str2,strn......)：字符串拼接函数
• lower(str)：将字符串转换为小写
• upper(str)：将字符串转换为大写
• lpad(str,n,pad)：在字符串左侧填充pad，n为填充几位
• rpad(str,n,pad)：在字符串右侧填充pad，n为填充几位
• trim(str)：去掉字符串左右两边的空格
• substring(str,start,len)：返回字符串str从start位置开始的len个长度的字符串

-- concat
mysql> select concat('Hello','world');
Helloworld

-- lower
mysql> select lower('Hello');
hello

-- upper
mysql> select upper('Hello');
HELLO 

-- lpad
mysql> select lpad('01',5,'-');
---01

-- rpad
mysql> select rpad('01',5,'-'); 
01---

-- trim
mysql> select trim('  hello  world  ');
hello  world

-- substring
mysql> select substring('012345',2,3);
123 

练习：将员工表中的员工工号统一设置为5位数

-- 将员工表中的员工工号统一设置为5位数，不足五位的前面补0，例如1号员工补完后为00001
mysql> update emp set workno = lpad(workno,5,'0');

数值函数

• ceil(x)：向下取整
• floor(x)：向下取整
• mod(x,y)：取模
• rand()：返回0-1之间的随机数
• round(x,y)：求参数x的四舍五入的值，保留y位小数

练习：返回一个6位数的随机验证码

-- 通过数据库函数，返回一个6位数的随机验证码
mysql> select rpad(round(rand()*1000000 , 0),6,'0');

日期函数

函数名称	描述
NOW()	返回当前日期和时间（YYYY-MM-DD HH:MM:SS 格式）。
CURDATE()	返回当前日期（YYYY-MM-DD 格式）。
CURTIME()	返回当前时间（HH:MM:SS 格式）。
DATE()	提取日期部分（YYYY-MM-DD）。例如：DATE('2024-09-15 12:34:56') 返回 2024-09-15。
TIME()	提取时间部分（HH:MM:SS）。例如：TIME('2024-09-15 12:34:56') 返回 12:34:56。
YEAR()	提取年份（YYYY）。例如：YEAR('2024-09-15') 返回 2024。
MONTH()	提取月份（MM）。例如：MONTH('2024-09-15') 返回 9。
DAY() 或 DAYOFMONTH()	提取日期中的天数（DD）。例如：DAY('2024-09-15') 返回 15。
DATE_ADD() 或 DATE_SUB()	添加或减去指定的时间间隔。例如：DATE_ADD('2024-09-15', INTERVAL 1 YEAR) 返回 2025-09-15。
DATEDIFF()	计算两个日期之间的天数差。例如：DATEDIFF('2024-09-15', '2024-01-01') 返回 258。

练习：查询所有员工的入职天数

-- 查询所有员工的入职天数,并倒序排序
mysql> select name,datediff(curdate(),entrydate) as 'Days' from emp order by Days desc;

流程控制函数

• if(value,t,f)：如果value为True，返回t，否则返回f
• ifnull(value1,value2)：如果value1不为null，返回value1，为null返回value2
• case when [val1] then [res1] else [default1] end：如果val1为True，返回res1，否则返回default1默认值
• case 列名 when [val1] then [res1] else [default1] end：如果列中的值等于val1，返回res1，否则返回default1默认值

mysql> select 
    -> name,
    -> (case work_address when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作地点'
    -> from emp;

练习：统计班级各学员成绩

-- 统计班级各学员成绩,展示规则如下：
	>=90,展示优秀
	>=80,展示及格
	否则不及格
	
select 
	id,
	name,
	(case when math>=90 then '优秀' when math>=80 then '及格' else '不及格' end) as '数学',
	(case when english>=90 then '优秀' when english>=80 then '及格' else '不及格' end) as '英语',
	(case when chinese>=90 then '优秀' when chinese>=80 then '及格' else '不及格' end) as '语文',
	(case when sport>=90 then '优秀' when sport>=80 then '及格' else '不及格' end) as '体育'
	from score;