select length("测试字符串") from dual -- 字符串长度
select lower("JYQ") from dual -- 转小写
select upper("jyq") from dual -- 转大写
select substr("测试截取字符串",2,5) from dual -- 截取字符串 (字符串内容,起始位置,截至位置-如果不填默认结尾)
select instr("测试字符串第一次出现的下标位置如果没有返回0","第一次") as instr from dual -- 返回字符串下标 (字符串内容,要查询的字符串)
select trim(" 测试去除前后空格 ") as trim from dual -- 去除字符串前后空格
select lpad("jyq",10,0) as lpad from dual -- 向左填充 (本身内容,字段长度,填充的内容)
select rpad("jyq",10,0) as rpad from dual -- 向右填充 (本身内容,字段长度,填充的内容)
select round(1.3415926,3) as round from dual -- 四舍五入 正数:正常四舍五入 负数: 先把符号丢一边 然后四舍五入再加上符号
select round(-1.3415926,3) as round from dual
select ceil(1.9) as ceil from dual -- 向上取整
select ceil(1.1) as ceil from dual
select floor(1.9) as floor from dual -- 向下取整
select floor(1.1) as floor from dual
select mod(10,3) as modtest from dual -- 取余;mod(被除数,除数) 当被除数为正数,结果就是正数。当被除数为负数,结果就是负数
select pow(2,3) as pow from dual -- 计算指数函数,pow(x,D) x为底,D为指数
select now() as now from dual -- 当前时间 带时分秒
select curdate() as curdate from dual -- 当前时间 不带时分秒
select curtime() as curtime from dual -- 当前时间 不带日期(年月日)
-- 获取年份:year() 获取月份:month() 获取日:day() 获取小时:hour() 获取分钟:minute() 获取秒数:second()
select weekofyear(now()) as weekofyear from dual --当前时刻所属周数
select quarter(now()) as quartertest from dual --当前时刻所属季度
select str_to_date("2022.2.22","%Y.%m.%d") as str_to_date from dual -- 字符串转为日期类型
select date_format(now(),"%Y-%m-%d %H:%i:%S")date_format from dual -- 日期类型转为字符串
select date_add(current_time(),interval 1 year) date_add_year from dual -- 向前、向后偏移日期和时间,正号为向后,负号为向前,hour(小时),minute(分钟),second(秒)
select date_add(current_time(),interval -1 month) date_add_month from dual
select date_add(current_time(),interval 1 day) date_add_day from dual
select last_day(curtime())last_day from dual -- 提取某个月最后一天的日期
select datediff(now(),"2002-9-19")datediff from dual -- 计算两个时间相差的天数 datediff(end_date,start_date) end: now()start:1999
select timestampdiff(hour,"2002-9-19",curdate())timestampdiff from dual -- 计算两个时间相差返回的年/月/天数; timestampdiff(unit,start_date,end_date) unit参数:year:年份、month:月份、day:天、hour:小时、minute 分钟、second:秒、microsecond:微秒、week:周数、quarter:季度
select if(1=1,"及格","不及格") from dual -- if(expr,v1,v2) 如果expr是true,返回v1。如果expr是false,返回v2
select ifnull(NULL,1) from dual -- 判断值是否为null,是null用指定值填充
select ifnull(1,2) from dual
-- case…when case 要判断的字段或表达式 when 常量1 then 要显示的值1或语句1 when 常量2 then 要显示的值2或语句2 ...else 要显示的值n或语句n end