MySQL常用函数

欧尼熊不懂

于 2022-08-28 20:20:09 发布

阅读量483

点赞数

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/qq_66991094/article/details/126573844

版权

常用函数

文章目录

字符串相关函数

函数	用法
`charset(str)`	返回字符串字符集
`concat(str2 , ...)`	连接字符串
`ucase(string)`	转换成大写
`lcase(string)`	转换成小写
`length(string)`	`string`长度（按字节）
`replace(str, search_str, replace_str)`	在 `str` 中用`replace_str`替换`search_str`
`substring(str, a [, b])`	从`str`的 a 开始（从1开始）取 b 个字符
`instr(string, str)`	返回`str`在string中出现的位置，没有返回0
`left(string, a) / right`	从`string`中左边/右边起取 a 个字符
`strcmp(string1, string2)`	逐字符比较两字符串大小
`ltrim(string) / rtrim(string)`	去除前端 / 后端空格
`trim(string)`	去除两端空格

# 所有操作不改变原表中数据，仅显示时不同
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;

数学函数

函数	用法
`abs(num)`	绝对值
`ceiling(num)`	向上取整
`floor(num)`	向下取整
`format(number, decimal_places)`	保留小数位数
`rand([seed])`	范围是 [ 0, 1.0 ]
`bin(decimal_number`	十进制转二进制
`conv(num, from_base, to_base)`	进制转换
`hex(decimalNumber)`	转十六进制
`least(num1,num2 [, ...])`	求最小值
`mod(numerator,denominator)`	求余

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；第一次随机值，之后不变，除非改变根植才重新生成

时间日期函数

函数	用法
`now()`	当前时间（年月日时分秒）
`current_date()`	当前日期（年月日）
`current_time()`	当前时间（时分秒）
`current_timestamp()`	当前时间戳（年月日时分秒）
`date(datetime)`	返回datetime的日期部分
`date_add(date2, interval d_value d_type)`	在date2中加上日期或时间
`date_sub(date2, interval d_value d_type)`	在date2中减去一个时间
`datediff(date1, date2)`	时间差（天数差）
`timediff(date1, date2)`	时间差（时分秒的差）
`year / month / date(datetime)`	年/月/日
`unix_timestamp()`	1970-10-01至今的秒数
`from_unixtime()`	把秒数转为指定格式日期

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'

加密函数

函数	用法
`user()`	查询用户
`database()`	数据库名称
`MD5(str)`	为字符串算出一个MD5 32位的字符串（用户密码）加密
`password(str)`	从原文密码`str`计算并返回密码字符串

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));

流程控制函数

函数	用法
`if(expr1, expr2, expr3)`	如果expr1为true返回expr2，否则返回expr3
`ifnull(expr1, expr2)`	如果expr1不为null返回expr1，否则返回expr2
`select case when expr1 then expr2 when expr3 then expr4 else expr5 end`	如果expr1为true返回expr2，若expr3为true返回expr4，否则返回expr5

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