MySQL常用函数

常用函数

字符串相关函数

# 所有操作不改变原表中数据，仅显示时不同
select charset(`name`) from `user`;			-- name 列的字符集: utf8mb4
	
-- 将 name列、"的年龄"、age列 拼接为一列命名为 introduce
select concat(name,'的年龄:',age) as 'introduce' from `user`;
	
-- "df"在"asdfgh"中出现的位置: 3；dual:亚元表，系统默认的测试表
select instr('asdfgh', 'df')from dual;
	
-- 转化为大写: SMITH；小写:	smith
select ucase(`name`) from `user`;
select lcase(`name`) from `user`;
    
-- 从 string 中
select left(name, 2) from `user`; 	-- 左边起取2个字符
select right(name, 3) from `user`; 	-- 右边起取3个字符
    
-- 查找到性别为'男'的换成'女'显示
select name, replace(sex,'男','女') as sex from user;
	
-- 在name列从第二个位置开始取两个字符	name: mi
select substring(name,2,2) as name from user;
	
-- 将所有名字首字母小写，后面大写显示
select concat(Lcase(left(name,1)),ucase(substring(name,2))) from user;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

数学函数

• rand()：返回范围为在[0, 1.0]之间的随机浮点数
  • 若指定整数参数N，则被用做根值，产生重复序列

select conv(8, 10, 2) from dual;			-- 将 10 进制的 8 转换为 2 进制输出: 1000
select format(54.6445,3) from dual;			-- 将数字保留三位(四舍五入)输出: 54.645
select mod(10,3) from dual	 				-- -- 求余: 1；10 % 3
# 返回随机数 
select format( rand(), 2) as num1 from dual;	-- num1: 0.48  每次运行都会变
select format( rand(4), 2) as num2  from dual； -- num2: 0.41；第一次随机值，之后不变，除非改变根植才重新生成
1
2
3
4
5
6

时间日期函数

• date_add/sub(date, interval d_value d_type)
  • date：原数据的日期
    • 可以是 date、datetime、timestamp 类型
  • d_value：要计算的时间值
  • d_type：年year、月month、日day、时hour、分minute、秒second
• datediff(date1, date2)：得到 date1 - date2 的差值，单位是天
• year/month/day(date)：只得到date中对应的字段
• from_unitime(毫秒数，日期格式)：秒数转为指定格式日期
  • 从 1970-10-01 开始计算的秒数转换为对应的日期
    • 只写秒数默认格式：年月日 时分秒
  • %Y-%m-%d %H-%i-%s：年月日 时分秒的固定格式
  • 在开发中可以用 int 存放一个 unix时间戳，再用该函数转换
• 日期数据可以直接比较；格式最好一样

-- 原date: 2021-10-1 12:20:20，date() 是只显示日期部分，即 now: 2021-10-1
select date(`date`) as now from news;

-- date_add/sub(date(原时间), interval 时间值 时间类型)；十分钟以内发送
select * from mes 
where date_add(`date`, interval 10 minute) >= now();		-- 发送时间 + 10 minute >= 现在时间
# where daate_sub(now(), interval 10 minute) <= `date`;		-- 或 当前时间 - 10分钟 <= 发送时间

-- 1999-10-07到现在时间差 date: 8087 (天)；嵌套表达式计算年份差等: date / 365
select datediff( now(), '1999-10-07') as date;

-- 得到特定字段日期: year 2021  month 11  day 27
select year(now()) as year;
select month(now()) as month;
select day(now()) as day;

-- 把秒数转为固定格式日期 下面语句等同于now()
select from_unixtime(unix_timestamp(), '%Y-%m-%s') 

-- 可直接比较日期
select now() > '2012-01-20'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

加密函数

• md5：将字符串（密码）加密为32位的字符串
  • 一般存放密码时定义数据类型为 char(32)
  • 使用 md5(password) 存放密码
• password()：加密，数据库的密码默认使用此方式加密
• mysql.user：数据库名.表名；在一个库中直接查询另一个库的表

-- 显示当前登录MySQL的所有用户和IP	root@localhost
select user();
-- 显示当前使用的数据库	demo01
select database();
-- md5()存放密码:	md5()将密码password转换为32位字符串
insert into values(01,'name',md5(password));
1
2
3
4
5
6

流程控制函数

• when ... then ... end
  • 当第一个条件为true返回第二个值，为false则进行下一个when，直到end结束
• 判断为空： is null； 不为空：is not null
• 显示某条记录某一列字段为空：is null

-- 显示上级为空
select * from user where '上级' is null;
	
-- expr1为true返回expr2，为false则判断expr3，
-- expr3为true返回expr4，否则返回expr5
select case 
when expr1 then expr2					-- 当expr1为true这时是expr2，否则进入下一轮
when expr3 then expr4 else expr5 end  	-- 当expr3为true这时是expr4，否则返回expr5
1
2
3
4
5
6
7
8