mysql中常见的单行函数笔记

/*
mysql中常见的单行函数
*/

/*
--------------------------------------------------------------------------------------
*/

/*
大小写控制函数
lower(str) 装换大小写混合的字符串为小写字符串
upper(str) 装换大小写混合的字符串为大写字符串
*/

-- 示列将employees表中雇员名字转换为大写

select upper(last_name) from employees;

-- 示列将employees表中雇员名字转换为小写

select lower(last_name) from employees;

/*
字符处理函数
substr(str,pos,len)从str的第pos为(范围1~str.lenth)开始截取长度为len的字符串
concat(str1,str2.....)将str1,,str2等字符串连接起来
length(str)返回字符串的存储长度
lpad(str, len, padstr)在源字符串的左边填充给定的字符padstr到指定的长度len，返回填充后的字符串
rpad(str, len, padstr)在源字符串的右边填充给定的字符padstr到指定的长度len，返回填充后的字符串
insert(str, substr)从源字符串str中返回子串substr第一次出现的位置
replace(str, from_str, to_str)在源字符串str中查找所有的子串form_str（大小写敏感），找到后使用替代字符串to_str替换它。返回替换后的字符串
trim(str)从str中删除头尾两侧空格
ltrim(str)/rtrim(str)从str删除左/右开头空格
*/

-- substr(str,pos,len)截取字符串

select substr('mysql',2,3) from dual;

-- concat(str1,str2.....)连接字符串

select concat('my','sql') from dual;

-- length(str)返回字符串的存储长度

select length('mysql') from dual;

-- lpad(str, len, padstr)/rpad(str, len, padstr)左右填充

select lpad('mysql',10,'*') from dual;

select rpad('mysql',10,'*') from dual;

-- insert(str, substr)从源字符串str中返回子串substr第一次出现的位置

select instr('mysql','l') from dual;

-- replace(str, from_str, to_str)在源字符串str中查找所有的子串form_str（大小写敏感），找到后使用替代字符串to_str替换它。返回替换后的字符串

select replace('mysql','sql','bitis') from dual;

-- trim(str)/ltrim(str)/rtrim(str)去除空格

select trim('   mysql  ') from dual;

select ltrim('   mysql  ') from dual;

select rtrim('   mysql  ') from dual;

/*
--------------------------------------------------------------------------------------
*/

/*
数字函数
round(arg1,arg2)返回agr1的四舍五入值,保留arg2位小数
round(arg1)四舍五入保留整数
truncate(arg1,arg2)截断指定小数值,不做四舍五入
mod(arg1,arg2)取余
*/

-- round(arg1,arg2)/round(arg1)四舍五入

select round(3.1415926,3) from dual;

select round(3.1415926) from dual;
 
-- truncate(arg1,arg2)截断

select truncate(3.1415926,3) from dual;

-- mod(arg1,arg2)取余

select mod(100,6) from dual;

/*
-------------------------------------------------------------------------------------
*/

/*
日期函数
sysdate()或者now()返回系统当前时间,格式为YYYY-MM-DD hh-mm-ss
curdate()返回系统当前日期 不包括时间
curtime()返回系统当前时间 不包括日期
dayofmonth(date)计算日期date是本月的第几天
dayofweek(date)计算日期date今天是星期几 1是星期日
dayofyear(date)返回指定年的天数
dayname(date)返回date日期是星期几
last_day(date)返回date日期当月最后一天
*/

-- sysdate()/now()返回当前时间

select sysdate() from dual;

select now() from dual;

-- curdate()返回系统当前日期 不包括时间

select curdate() from dual;

-- curtime()返回系统当前时间 不包括日期

select curtime() from dual;

-- dayofmonth(date)计算日期date是本月的第几天

select dayofmonth(sysdate()) from dual;

-- dayofweek(date)计算日期date今天是星期几 1是星期日

select dayofweek(now()) from dual;

-- dayofyear(date)返回指定年的天数

select dayofyear(sysdate()) from dual;


-- dayname(date)返回date日期是星期几

select dayname(now()) from dual;

-- last_day(date)返回date日期当月最后一天

select last_day(now()) from dual;

/*
-------------------------------------------------------------------------------------
*/

/*
转换函数
date_format(date,format)将日期装换为字符串(类似oracle中的to_char)
str_to_date(str,format)将字符串转换成日期(类似oracle中的to_date)
格式
%Y四位的年,%y两位的年
%M月名,%m月数值
%D带有英文前缀的月中天,%d月的天数值(00~31),%e月的天数值(0~31)
%H小时(00~23),%h小时(01~12)
%i分钟数值(00~59)
%S秒(00~59)
*/

-- date_format(date,format)将日期装换为字符串(类似oracle中的to_char)

select date_format(now(),'%Y年%m月%d日%H时%i分%S秒') from dual;

-- str_to_date(str,format)将字符串转换成日期(类似oracle中的to_date)

select str_to_date('2020年03月20日15时48分41秒','%Y年%m月%d日%H时%i分%S秒') from dual;

select * from employees;

-- 示列向employees表中添加一条数据,,名字:King,email:king@163.com,部门ID:1,薪水9000,入职时间:2018年5月1日,佣金:0.6

insert into employees values(null,'King','king@163.com',1,9000,0.6,str_to_date('2018年5月1日','%Y年%m月%d日'));

-- 示列查询employees表中雇员名字为King的雇员的入职日期,要求显示格式为yyyy年MM月dd日

select date_format(hire_date,'%Y年%m月%d日') from employees where last_name='King';

/*
-------------------------------------------------------------------------------------
*/

/*
通用函数
ifnull(expr1,expr2)判断expr1是否为null,如果为null,则用expr2来代替null(类似于oracle的nvl()函数)
nullif(expr1,expr2)判断expr1和expr2是否相等,如果相等则返回null,如果不相等则返回expr1
if(expr1,expr2,expr3)如果expr1的值为true,则返回expr2的值,如果expr1的值为false,则返回expr3的值。
coalesce(value)函数需要许多参数，并返回第一个非NULL参数。如果所有参数都为NULL，则COALESCE函数返回NULL。
case when then else end 条件函数
*/

-- ifnull(expr1,expr2)判断expr1是否为null,如果为null,则用expr2来代替null(类似于oracle的nvl()函数)
select ifnull(null,'ll') from dual;

-- nullif(expr1,expr2)判断expr1和expr2是否相等,如果相等则返回null,如果不相等则返回expr1

select nullif(1,2) from dual;
select nullif(1,1) from dual;

-- if(expr1,expr2,expr3)IF(expr1,expr2,expr3),如果expr1的值为true,则返回expr2的值,如果expr1的值为false,则返回expr3的值。	

select if(1>2,'yes','no') from dual;

-- coalesce(value)函数需要许多参数，并返回第一个非NULL参数。如果所有参数都为NULL，则COALESCE函数返回NULL。

select coalesce(null,null,2,9) from dual;