系统信息函数, 数学函数,字符串函数,日期函数,条件判断函数
目录
字符截取函数 left(),right(), mid(), substr()
去除首尾空格函数 trim(),ltrim(),rtrim()
日期时间戳转换函数 UNIX_TIMESTAMP(),FROM_UNIXTIME(timestamp)
获取日期的年,季度,月,日, 星期 函数 year(),quater(),month(),day(),dayname()
系统信息函数
select database();
select version();
select user();
数学函数
绝对值函数 abs()
select abs(-10) as '绝对值';
select abs(-10) ====> 10
保留小数位函数 round()
select round(5.419, 2) as '四舍五入'; ------ 第二个参数是保留几位小数的意思
select round(5.419,2) ====>5.42
求两数余数函数 mod()
select mod(5,2); ------ 求余数
select mod(5,2) ====>1
字符串函数
求字符串长度 length()
select LENGTH('asdcee') ------- 字符串长度
select LENGTH('asdcee') ====>6
字符串拼接函数 concat() ,concat_ws()
select concat('xixi', 'haha') as '字符串连接';
select concat('xixi', 'haha') ====> xixihaha
select concat_ws('-','xixi', 'haha') as '指定字符拼接字符串';
select concat_ws('-','xixi', 'haha') ====> xixi-haha
字符替换函数 replace()
select replace('asdcee', 'd', 'D') as '替换d为D'
select replace('asdcee', 'd', 'D') ====> asDcee
大小写转换函数 upper(),lower()
select UPPER('asdcee') as '转换为大写'
select UPPER('asdcee') ====>ASDCEE
SELECT LOWER('ASDCEE') as '转换为小写'
SELECT LOWER('ASDCEE')====>asdcee
字符截取函数 left(),right(), mid(), substr()
select left('xixihaha', 2) as le_sub; ------ 从左侧提取两位
select left('xixihaha', 2) ====> xi
select right('xixihaha', 2) as re_sub; -----从右侧提取两位
select right('xixihaha', 2)====>ha
select mid('xixihaha', 3,5) as mid_sub; ----- 从第三位开始取,取5位字符
select mid('xixihaha', 3,5)====>xihah
select substr('xixihaha',1,4) 截取第一位到第四位字符
select substr('xixihaha',1,4) ====> xixi
去除首尾空格函数 trim(),ltrim(),rtrim()
select trim(' xixihaha ') as 去掉字符串前后空格;
select trim(' xixihaha ') ====>(xixihaha)
select ltrim(' xixihaha ') as 去掉字符串左边空格;
select trim(' xixihaha ') ====>'xixihaha '
select rtrim(' xixihaha ') as 去掉字符串右边空格;
select trim(' xixihaha ') ====>' xixihaha'
去除首尾指定字符函数 trim()
SELECT TRIM('/' from '/acdee/') as 去掉字符串前后 /;
SELECT TRIM('/' from '/acdee/') ====>acdee
SELECT TRIM(LEADING '/' from '/acdee/') as 去掉字符串开头 /
SELECT TRIM(LEADING '/' from '/acdee/')====>acdee/
SELECT TRIM(TRAILING '/' from '/acdee/')as 去掉字符串结尾 /
SELECT TRIM(TRAILING '/' from '/acdee/')====>/acdee
字符串反转函数 reverse()
SELECT REVERSE('acdee') as '字符串反转'
SELECT REVERSE('acdee')====> eedca
日期函数
select curdate() as 当前日期;
select curtime() as 当前时间;
select now() as 当前日期和时间;
日期时间戳转换函数 UNIX_TIMESTAMP(),FROM_UNIXTIME(timestamp)
SELECT UNIX_TIMESTAMP(NOW()) as '日期转时间戳'
SELECT UNIX_TIMESTAMP(NOW()) ====>1684737826
SELECT FROM_UNIXTIME(1684737826) as '时间戳转日期'
SELECT FROM_UNIXTIME(1684737826)====>2023-05-22 14:43:46
获取日期的年,季度,月,日, 星期 函数 year(),quater(),month(),day(),dayname()
SELECT YEAR(NOW()) as '获取年份'
SELECT YEAR(NOW())====>2023
SELECT QUARTER(NOW()) as '获取季度'
SELECT QUARTER(NOW())====>2
SELECT MONTH(NOW()) as '获取月份'
SELECT MONTH(NOW())====>5
SELECT DAY(NOW()) as '获取日'
SELECT DAY(NOW())====>22
SELECT DAYNAME(NOW()) as '获取星期'
SELECT DAYNAME(NOW())====>Monday
日期 增减函数 adddate(), subdate()
SELECT ADDDATE(NOW(), INTERVAL 1 year) AS '增加1年'
SELECT ADDDATE(NOW(), INTERVAL 1 QUARTER) AS '增加1季度'
SELECT ADDDATE(NOW(), INTERVAL 1 MONTH) AS '增加1月'
SELECT ADDDATE(NOW(), INTERVAL 1 DAY) AS '增加1天'
SELECT SUBDATE(NOW(), INTERVAL 1 year) AS '减少1年' FROM DUAL
SELECT SUBDATE(NOW(), INTERVAL 1 QUARTER) AS '减少1季度' FROM DUAL
SELECT SUBDATE(NOW(), INTERVAL 1 MONTH) AS '减少1月' FROM DUAL
SELECT SUBDATE(NOW(), INTERVAL 1 DAY) AS '减少1天' FROM DUAL
返回两日期间间隔天数函数 datediff()
SELECT DATEDIFF(NOW(),SUBDATE(NOW(), INTERVAL 1 year)) as '返回间隔天数'
SELECT DATEDIFF(NOW(),SUBDATE(NOW(), INTERVAL 1 year)) ====> 365
返回日期所在月的最后一天函数 LAST_DAY(date)
SELECT LAST_DAY(NOW())
字符转日期函数 str_to_date()
SELECT STR_TO_DATE('2019-01-01','%Y-%m-%d')
条件判断函数
select if(10>5,10,5) as 最大值
select if(10>2,10,2);
select pname, if(price>2000, '奢侈品','普通品') '商品性质' from producs;
select pname, if(price>2000, price-1000, price) '优惠后的价格' from products;
case
when 条件1 then 结果1
when 条件2 then 结果2
when 条件3 then 结果3
else 结果 n+1
end
select case when 10>5 then 10 else 5 end as 最大值;