Mysql常用函数

卑微的cv工程师
254 阅读6分钟

字符串函数

基本使用

函数用法
CONCAT(S1,S2,......,Sn)连接S1,S2,......,Sn为一个字符串
CONCAT_WS(separator, S1,S2,......,Sn)连接S1一直到Sn，并且中间以separator作为分隔符
CHAR_LENGTH(s)返回字符串s的字符数
LENGTH(s)返回字符串s的字节数，和字符集有关
INSERT(str, index , len, instr)将字符串str从第index位置开始，len个字符长的子串替换为字符串instr
UPPER(s) 或 UCASE(s)将字符串s的所有字母转成大写字母
LOWER(s) 或LCASE(s)将字符串s的所有字母转成小写字母
LEFT(s,n)返回字符串s最左边的n个字符
RIGHT(s,n)返回字符串s最右边的n个字符
LPAD(str, len, pad)用字符串pad对str最左边进行填充，直到str的长度为len个字符
RPAD(str ,len, pad)用字符串pad对str最右边进行填充，直到str的长度为len个字符
LTRIM(s)去掉字符串s左侧的空格
RTRIM(s)去掉字符串s右侧的空格
TRIM(s)去掉字符串s开始与结尾的空格
TRIM(【BOTH 】s1 FROM s)去掉字符串s开始与结尾的s1
TRIM(【LEADING】s1 FROM s)去掉字符串s开始处的s1
TRIM(【TRAILING】s1 FROM s)去掉字符串s结尾处的s1
REPEAT(str, n)返回str重复n次的结果
REPLACE（str, a, b）用字符串b替换字符串str中所有出现的字符串a
STRCMP(s1,s2)比较字符串s1,s2
SUBSTRING(s,index,len)返回从字符串s的index位置其len个字符

练习

CREATE TABLE t_user (
  id int(11) NOT NULL AUTO_INCREMENT,
  uname varchar(40) DEFAULT NULL,
  age int(11) DEFAULT NULL,
  sex int(11) DEFAULT NULL,
  PRIMARY KEY (id)
);

insert  into t_user values (null,'zs',18,1);
insert  into t_user values (null,'ls',20,0);
insert  into t_user values (null,'ww',23,1);
insert  into t_user values (null,'zl',24,1);
insert  into t_user values (null,'lq',15,0);
insert  into t_user values (null,'hh',12,0);
insert  into t_user values (null,'wzx',60,null);

select * from t_user
insert  into t_user values (null,'lb',null,null);

-- 合并字符串
select CONCAT("hello",uname) 合并 from t_user

-- 合并字符串按某个符号分隔
select concat_ws(",","hello",uname) from t_user

-- 查询字符数
select CHAR_LENGTH(concat("张三","11"))

-- 查询字
select LENGTH(concat("张三","11"))

-- 将字符串str从第index位置开始，len个字符长的子串替换为字符串instr
select INSERT("hello",1,2,"a")


-- 转大写
select ucase("hello")

-- 转小写
select lcase("HEllo")

-- 返回左边几个字符
select LEFT("hello",2)

-- 右边
select right("hello",1)


-- 用字符串pad对str最左边进行填充，直到str的长度为len个字符
select LPAD("hello",10,"aa")
-- 右边
select RPAD("hello",10,"aa")

-- 返回str重复n次的结果
SELECT REPEAT("HELLO",3)

-- 比较两个字符串，左边大返回1，相等0，右边大返回-1
SELECT STRCMP("hello1","hello1")

数值函数

基本使用

函数用法
ABS(x)返回x的绝对值
CEIL(x)返回大于x的最小整数值
FLOOR(x)返回小于x的最大整数值
MOD(x,y)返回x/y的模
RAND()返回0~1的随机值
ROUND(x,y)返回参数x的四舍五入的有y位的小数的值
TRUNCATE(x,y)返回数字x截断为y位小数的结果
SQRT(x)返回x的平方根
POW(x,y)返回x的y次方

练习

-- 返回x的绝对值
select ABS(-1)

-- 返回大于x的最小整数值
select ceil(-9.3)

-- 返回x/y的模
SELECT mod(1,2)

-- 0-1随机数
select RAND()

-- 返回参数x的四舍五入的有y位的小数的值
SELECT ROUND(7.1111111111,2)

日期函数

基本使用

函数用法
CURDATE() 或 CURRENT_DATE()返回当前日期
CURTIME() 或 CURRENT_TIME()返回当前时间
NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP()返回当前系统日期时间
YEAR(date) / MONTH(date) / DAY(date) / HOUR(time) / MINUTE(time) / SECOND(time)返回具体的时间值
WEEK(date) / WEEKOFYEAR(date)返回一年中的第几周
DAYOFWEEK()返回今天是一周的第几天，注意：周日是1，周一是2，。。。周六是7
WEEKDAY(date)返回周几，注意，周1是0，周2是1，。。。周日是6
DAYNAME(date)返回星期：MONDAY,TUESDAY.....SUNDAY
MONTHNAME(date)返回月份：January，。。。。。
DATEDIFF(date1,date2) / TIMEDIFF(time1, time2)返回date1 - date2的日期间隔 / 返回time1 - time2的时间间隔
DATE_ADD(datetime, INTERVAL expr type)返回与给定日期时间相差INTERVAL时间段的日期时间
DATE_FORMAT(datetime ,fmt)按照字符串fmt格式化日期datetime值
STR_TO_DATE(str, fmt)按照字符串fmt对str进行解析，解析为一个日期

DATE_ADD(datetime,INTERVAL expr type)

参数类型参数类型
YEARYEAR_MONTH
MONTHDAY_HOUR
DAYDAY_MINUTE
HOURDAY_SECOND
MINUTEHOUR_MINUTE
SECONDHOUR_SECOND
MINUTE_SECOND

DATE_FORMAT(datetime,fmt) 和 STR_TO_DATE(str, fmt)

格式符说明格式符说明
%Y4位数字表示年份%y表示两位数字表示年份
%M月名表示月份（January,....）%m两位数字表示月份（01,02,03。。。）
%b缩写的月名（Jan.，Feb.，....）%c数字表示月份（1,2,3,...）
%D英文后缀表示月中的天数（1st,2nd,3rd,...）%d两位数字表示月中的天数(01,02...)
%e数字形式表示月中的天数（1,2,3,4,5.....）
%H两位数字表示小数，24小时制（01,02..）%h和%I两位数字表示小时，12小时制（01,02..）
%k数字形式的小时，24小时制(1,2,3)%l数字形式表示小时，12小时制（1,2,3,4....）
%i两位数字表示分钟（00,01,02）%S和%s两位数字表示秒(00,01,02...)
%W一周中的星期名称（Sunday...）%a一周中的星期缩写（Sun.，Mon.,Tues.，..）
%w以数字表示周中的天数(0=Sunday,1=Monday....)
%j以3位数字表示年中的天数(001,002...)%U以数字表示年中的第几周，（1,2,3。。）其中Sunday为周中第一天
%u以数字表示年中的第几周，（1,2,3。。）其中Monday为周中第一天
%T24小时制%r12小时制
%pAM或PM%%表示%

练习

-- 当前日期时间 2021-08-30 17:01:26
select now()

-- 当前日期curdate，  当前时间curtime
select CURDATE(),CURTIME()

-- 返回具体的某个单位的时间值
select YEAR(now()),MONTH(now())

-- 返回一年中的第几周
select week(now())

-- 返回周几，注意，周1是0，周2是1，。。。周日是6
SELECT WEEKDAY(NOW())

-- 返回date1 - date2的日期间隔 / 返回time1 - time2的时间间隔
select DATEDIFF(NOW(),'2021-01-01')  

-- 返回与给定日期时间相差INTERVAL时间段的日期时间
select DATE_ADD(NOW(),INTERVAL 1 YEAR) -- 一年后的今天
select DATE_ADD(NOW(),INTERVAL 1 MONTH) -- 一个月后的今天
select DATE_ADD(NOW(),INTERVAL 1 DAY) -- 明天

select DATE_ADD(NOW(),INTERVAL -1 YEAR) -- 一年前的今天

-- 返回指定格式的字符串
select DATE_FORMAT(LOCALTIME(),"%Y-%m-%d %H:%i:%S") -- 年月日时分秒
select DATE_FORMAT(LOCALTIME(),"%Y-%m-%d") -- 年月日
select DATE_FORMAT(LOCALTIME(),"%Y-%m") -- 年月
select DATE_FORMAT(LOCALTIME(),"%m")  -- 单个月

流程函数

基本使用

函数用法
IF(value,t ,f)如果value是真，返回t，否则返回f
IFNULL(value1, value2)如果value1不为空，返回value1，否则返回value2
CASE WHEN 条件1 THEN result1 WHEN 条件2 THEN result2 .... [ELSE resultn] END相当于Java的if...else if...else...
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 .... [ELSE 值n] END相当于Java的switch...case...

练习

SELECT uname,IF(sex=1,1,0) FROM t_user -- 等于1返回1，不等与返回0

SELECT uname,IFNULL(sex,1) FROM t_user -- 为空返回1

select uname, case when IFNULL(age,0) < 10 then '儿童'
when age < 20 then '青年'
else '老年' END '描述' from t_user

其他函数

函数用法
database()返回当前数据库名
version()返回当前数据库版本
user()返回当前登录用户名
password(str)返回字符串str的加密版本，41位长的字符串
md5(str)返回字符串str的md5值，也是一种加密方式
avatar
文章
阅读
粉丝