Mysql笔记之（四）常见函数之单行函数

调用：select 函数名（实参列表）【from 表】；
特点：（1）函数名 （2）函数功能
分类：

1. 单行函数 如concat、length、ifnull等，传一个参数进去会有一个返回值
2. 分组函数（又名统计函数、聚合函数、组函数）：做统计使用，传进去一组值最终返回一个值

1.字符函数

（1）length，获取参数值的字节

 select length ('john');

》》4

 select length ('张新成');

》》9

注意：utf8的格式下一个汉字3个字节，gbk一个汉字两个字节
#显示字符集，可以知道是什么格式

 SHOW VARIABLES like '%char%';

（2）concat 拼接字符串

 select concat (first_name,'_',last_name) as 姓名 
 from employees;

（3）upper 、lower变大小写

 select upper('john');

#例：将姓变大写，名变小写，然后拼接

select concat(upper(last_name),lower(first_name))as 姓名 
from employees;

（4）substr、substring 截取字符串

#注意：mysql中索引从1开始

#截取从指定索引处后面所有的字符（截取从当前索引开始的所有字符）

select SUBSTR('今天是晴天',4) out_put; 

》》晴天

#截取从指定索引处指定字符长度（不是字节长度）的字符

select SUBSTR('今天是晴天',1,2) out_put; 

》》今天

#例：姓名中首字符大写，其他字符小写，然后用_拼接，显示出来

select concat(upper(substr(last_name,1,1)),
lower(substr(last_name,2)),'_',lower(first_name)) output 
from employees;

（5）instr 返回子串在字符串中第一次出现的位置,如果找不到返回0

select instr('冰糖炖雪梨好甜','雪梨') as output；

#>>4

select instr('冰糖炖雪梨好甜冰糖炖雪梨好甜','雪梨') as output；

#>>4

（6）trim 去掉前后的空格或字符

select length(trim('   张新成   ')) as output;

#>>9

select trim('a' from 'aaaaaaa张新成aaaaaa') as output;

#>>张新成

（7）Lpad 用指定的字符实现左填充指定长度

select LPAD('黎语冰',10,'*') as output;

（8）rpad 用指定的字符实现右填充指定长度

（9）replace 替换

select replace('棠雪棠雪棠雪黎语冰和棠雪','棠雪','小可爱') as output;

2.数学函数

（1）round 四舍五入

select round(1.45);

#>>1

#第二个参数代表保留小数点之后的位数

select round(1.45678,2);

#>>1.45

（2）ceil 向上取整,返回大于等于该参数的最小整数

select ceil(1.002);
#>>2

select ceil(1.00);
#>>1

（3）floor向下取整，返回小于等于该参数的最大整数

（4）TRUNCATE(X,D) 截断,截断小数点n位之后的数，无论是什么都去掉

select truncate(1.99999,1);

#>>1.9

（5）mod 取余

select mod(10,3);

#等价于

select 10%3;

3.日期函数

（1）now 返回当前系统日期加时间

select now();

（2）curdate 返回当前系统日期，不包含时间

select curdate();

（3）curtime 返回当前时间

#获取指定的部分，年、月、日、小时、分钟、秒

select year(now()) 年;

select year(hiredate) 年 from employees;

select month(now()) 年;

select monthname(now()) 月;

（4）str_to_date:将日期格式的字符转换为指定格式的日期

select str_to_date('4-11-2020','%m-%d-%Y') as output;

#例：查询入职日期为1992-4-3的员工信息

select * from employees where hiredate = '1992-4-3';

#例：查询入职日期为1992-4-3的员工信息,不限制输入顺序

select * from employees where hiredate = str_to_date('4-3 1992','%c-%d %Y');

（5）date_format 将日期转换成字符

select date_format(now(),'%y年%m月%d日') as output;

#查询有奖金的员工名和入职日期（xx月/xx日 xx年）

 select last_name,date_format(hiredate,'%m月%d日 %y年') 
 from employees;

4.其他函数

查看版本

select VERSION();

查询数据库

select database();

查询用户

select user();

5.流程控制函数

（1）if函数：if else，类似三目运算符

select if(10<5,'大','小')；

（2）case函数的使用

   CASE expr 
   WHEN v1 
   THEN r1
    [WHEN v2 THEN r2]
    [ELSE rn] 
    END

如果expr值等于某个vn，可以是等于v1或v2…vn，则返回对应位置THEN后面的结果。如果与所有值都不相等，则返回ELSE后面的rn。

select job_id as job,
case job_id
when 'AD_PRES' then 'A'
when 'ST_MAN' then 'B'
when 'IT_PROG' then 'C'
end as Grade
from employees;

注：date_format函数参数说明

https://www.w3school.com.cn/sql/func_date_format.asp

%a 缩写星期名
%b 缩写月名
%c 月，数值
%D 带有英文前缀的月中的天
%d 月的天，数值(00-31)
%e 月的天，数值(0-31)
%f 微秒
%H 小时 (00-23)
%h 小时 (01-12)
%I 小时 (01-12)
%i 分钟，数值(00-59)
%j 年的天 (001-366)
%k 小时 (0-23)
%l 小时 (1-12)
%M 月名
%m 月，数值(00-12)
%p AM 或 PM
%r 时间，12-小时（hh:mm:ss AM 或 PM）
%S 秒(00-59)
%s 秒(00-59)
%T 时间, 24-小时 (hh:mm:ss)
%U 周 (00-53) 星期日是一周的第一天
%u 周 (00-53) 星期一是一周的第一天
%V 周 (01-53) 星期日是一周的第一天，与 %X 使用
%v 周 (01-53) 星期一是一周的第一天，与 %x 使用
%W 星期名
%w 周的天 （0=星期日, 6=星期六）
%X 年，其中的星期日是周的第一天，4 位，与 %V 使用
%x 年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y 年，4 位
%y 年，2 位