MYSQL常用函数 读书笔记

此书的学习笔记

字符串函数

函数	功能	用例
查询字符串信息
length(string)	string的字节个数	LENGTH(‘adc哈’) -> 6
char_length(string)	string的字符个数	char_length(‘abc哈’) -> 4
charset(str)	返回字串字符集	charset(‘qwe’) -> utf8
拼接类型
CONCAT(S1,S2,…Sn)	把传入的参数连接成为一个字符串 任何字符串与NULL进行连接的结果都将是NULL	concat(‘aaa’, ‘bbb’, ‘ccc’) ->aaabbbccc concat(‘aaa’,null) -> Null
LPAD(str,n,pad) 和 RPAD(str,n,pad)	用字符串pad对str最左边和最右边进行填充，直到长度为n个字符长度。	lpad(‘2008’,20,‘beijing’) -> beijingbeijingbe2008 rpad(‘beijing’,20,‘2008’) -> beijing2008200820082
REPEAT(str,x)	返回str重复x次的结果	repeat('mysql ',3) -> mysql mysql mysql
截取类型
substring(str, position [,length])	从str的position开始,取length个字符 下标从1开始	substring(‘beijing2008’,8,4) -> 2008 substring(‘beijing2008’,1,7) -> beijing substring(‘beijing2008’,3) -> ijing2008
LEFT(str,x) 和 RIGHT(str,x)	分别返回字符串最左边的x个字符和最右边的x个字符。 如果第二个参数是NULL，那么将不返回任何字符串。 下标从1开始	LEFT(‘beijing2008’,7) -> beijing LEFT(‘beijing’,null) -> Null RIGHT(‘beijing2008’,4) -> 2008
替换类型
replace(str ,search_str ,replace_str)	在 str 中用 replace_str 替换 search_str	replace(‘beijing_2010’,’_2010’,‘2008’) -> beijing2008
INSERT(str,x,y,instr)	将字符串str从第x位置开始，y个字符长的子串替换为字符串instr 下标从1开始	INSERT(‘beijing2008you’,12,3, ‘me’) -> beijing2008me
instr(string ,substring)	返回substring首次在string中出现的位置	instr(‘qwer’ ,‘e’) -> 3
locate(substring, string [,start_position])	同instr,但可指定开始位置	locate(‘qw’, ‘qwertqw’ ,3) -> 6
去空格
LTRIM(str) 和 RTRIM(str)	去掉字符串str左侧和右侧空格	ltrim(’ beijing’) -> beijing rtrim('beijing ') -> beijing
TRIM(str)	去掉目标字符串的开头和结尾的空格	trim(’ $ beijing2008 $ ') -> $ beijing2008 $
STRCMP(s1,s2)	比较字符串s1和s2的ASCII码值的大小。 S1<S2 -1 S1=S2 0 S1>S2 1	strcmp(‘a’,‘b’) -> -1 strcmp(‘b’,‘b’) -> 0 strcmp(‘c’,‘b’) -> 1
LOWER(str) 和 UPPER(str)	把字符串转换成小写或大写	LOWER(‘BEIJING2008’) -> beijing2008 UPPER(‘beijing2008’) -> BEIJING2008
load_file(file_name)	从文件读取内容

数值函数

函数	功能	用例
pi()	获得圆周率	pi() -> 3.141593
pow(m, n)	m^n	pow(3, 2) = 9
sqrt(x)	算术平方根	sqrt(9) = 3
format(x, d)	格式化千分位数值	format(1234567.456, 2) = 1,234,567.46
abs(x)	返回x的绝对值	abs(-0.8) =0.8 abs(0.8) =0.8
CEIL(x)	返回大于x的最小整数	CEIL(-0.8) =0 CEIL(0.8) =1
FLOOR(x)	返回小于x的最大整数，和CEIL的用法刚好相反。	FLOOR(-0.8) =-1 FLOOR(0.8) =0
round(x,y)	返回参数x的四舍五入的有y位小数的值。 如果是整数，将会保留y位数量的0； 如果不写y，则默认y为0，即将x四舍五入后取整。	ROUND(1.234,2) =1.23 ROUND(1.235,2) =1.24 ROUND(1,3) = 1 ROUND(1.4) = 1 ROUND(1.5) = 2 ROUND(1.1,2) =1.1
TRUNCATE(x,y)	返回数字x截断为y位小数的结果 注意TRUNCATE和ROUND的区别在于TRUNCATE仅仅是截断，而不进行四舍五入	TRUNCATE(1.234,2) =1.23 TRUNCATE(1.235,2) =1.23
MOD(x,y) x%y的结果相同	返回x/y的模。 模数和被模数任何一个为NULL结果都为NULL	MOD(15,10) =5 MOD(1,11) =1 MOD(NULL,10) =Null
RAND()	返回0～1内的随机值 利用此函数可以取任意指定范围内的随机数	RAND() =0.8762537929008285 RAND() =0.32581121132318974 ceil(100 * rand()) =1 ceil(100 * rand()) =3

日期和时间函数

函数	功能	用例
NOW() / CURRENT_TIMESTAMP()	返回当前的日期和时间，年月日时分秒全都包含	NOW() -> 2021-11-07 21:54:54
CURDATE() / CURRENT_DATE() / date(now())	返回当前日期，只包含年月日	CURDATE() -> 2021-11-07 CURRENT_DATE () -> 2021-11-07 date(now()) -> 2021-11-07
CURTIME() / CURRENT_TIME() / time(now())	返回当前时间，只包含时分秒	CURTIME() ->21:54:54 CURRENT_TIME () -> 21:54:54 time(now()) -> 21:54:54
YEAR(DATE)	返回所给的日期是哪一年	YEAR(now()) -> 2021
MONTHNAME(date)	返回date的英文月份名称	MONTHNAME(now()) -> November
WEEK(DATE)	返回所给的日期是一年中的第几周	WEEK(now()) -> 45
HOUR(time)	返回所给时间的小时	HOUR(CURTIME()) ->23
MINUTE(time)	返回所给时间的分钟	MINUTE(CURTIME()) ->34
UNIX_TIMESTAMP(date)	返回日期date的UNIX时间戳	UNIX_TIMESTAMP(now()) ->1636293419
FROM_UNIXTIME(unixtime)	返回 UNIXTIME 时间戳的日期值， 和 UNIX_TIMESTAMP(date)互为逆操作	FROM_UNIXTIME(1636293419) ->2021-11-07 21:56:59
DATE_FORMAT(date,fmt)	按字符串 fmt 格式化日期 date 值 格式参考图1	DATE_FORMAT(now(),’%M,%D,%Y’) -> November,7th,2021
DATEDIFF（date1，date2）	用来计算两个日期之间相差的天数	DATEDIFF(‘2022-08-08’,now()) -> 274
DATE_ADD(date,INTERVAL expr type)	返回与所给日期date相差 INTERVAL时间段的日期 格式参考图2	now() -> 2021-11-07 23:39:00 date_add(now(), INTERVAL 31 DAY) ->2021-12-08 23:39:00 date_add(now(),INTERVAL ‘1_2’ year_month) ->2023-01-07 23:39:00 date_add(now(), INTERVAL -31 DAY) ->2021-10-07 23:39:00 date_add(now(),INTERVAL ‘-1_-2’ year_month) ->2020-09-07 23:39:00

图1：

图2：

流程函数

函数	功能	用例
create table salary (userid int,salary decimal(9,2)); insert into salary values(1,1000),(2,2000), (3,3000),(4,4000),(5,5000), (1,null);		1000.00 2000.00 3000.00 4000.00 5000.00 NULL
IF(value,t,f)	select if(salary>2000, ‘high’, ‘low’) from salary	low low high high high low
IFNULL(value1,value2)	这个函数一般用来替换 NULL 值，我们知道 NULL 值是不能参与数值运算的 select ifnull(salary,0) from salary;	1000.00 2000.00 3000.00 4000.00 5000.00 0.00
case when [condition] then result [when [condition] then result …] [else result] end	select case when salary<=2000 then ‘low’ else ‘high’ end from salary	low low high high high high
CASE [expr] WHEN [value1] THEN[result1]…ELSE[default]END	select case salary when 1000 then ‘low’ when 2000 then ‘mid’ else ‘high’ end from salary;	low mid high high high high