sql语句的函数整理部分

单行函数

字符函数

ifnull

判断某字段或表达式是否为null，如果是返回指定值，否则返回原本值

//显示出user表的全部列，（处理空值）
select CONCAT(`first_name`,",",IFNULL(last_name,0)) from user  

IFNULL的第二参0是如果last_name为空则用0代替

isnull

判断某字段或表达式是否为null，如果是返回1，否则返回0

length

获取参数值的字节个数

select length('haha')    # 4 utf-8下
select length('洋haha')   # 7 utf-8下

concat

拼接字符

select concat(fitst,"_",lastname) as '名字' from users

upper、lower

转大小写

select concat(upper(fitst),lower(lastname)) as '名字' from users

substr、substring

从某个文本字段提取字符

 语法：	SubString(字段，1，end)
 
 
 
 

instr

返回字串第一次出现的索引，如果找不到就返回0

select insrt( 'abcdefg','a') as out_str // 0
select insrt( 'abcdefg','b') as out_str // 1

trim

去除空格

select trim( '   abcdefg   ') as out_str // 'abcdefg'
elect trim('aa' from 'aaa等等bb') as out_str // 'a等等bb'

lpad

用指定的字符实现左填充指定长度

select LPAD('啊洋啊',5,"*") outPrint;  //  **啊洋啊

rpad

用指定的字符实现右填充指定长度

select RPAD('啊洋啊',5,"*") outPrint;  //  啊洋啊**

replace

select REPALCE('洋啊洋','洋','6')  // 6啊6

数学函数

round

四舍五入

select round(-1.55)  // -2
select round(-1.55,1)  // -1.6   保留一位小数

ceil

向上取整,返回比 此参数大的整数

select ceil(-1.2)  // -1
select ceil(2.5)   // 3

floor

向下取整,返回比 此参数小的整数

select floor(-9.99)   // -10
select floor(9.99)    // 9

truncate

截断

select truncate(1.69999,1)  // 1.6
select truncate(1.69999,2)  // 1.69

mod

取余

select mod(11,3) // 2
等价于     select 11%3
select mod(-10,-3)  // -1  数学问题

日期函数

now

返回当前的系统日期+时间

select now()   // 2020-04-14 12:25:09

curdate

返回当前的系统日期，不包含时间

select curdate() // 2020-04-14

curtime

返回当前的系统时间，不包含日期

select curdate() // 2020-04-14

指定年、月、日、小时、分、秒

select year(now()) 年    // 2020
select month(now()) 月   // 4
// 英文版的
select monthname(now()) 月 // April

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cjQXjAgL-1587093134072)(C:\Users\thinkbook15\AppData\Roaming\Typora\typora-user-images\image-20200414144652631.png)]

符号表格

格式符	功能
%Y	四位的年份
%y	二位的年份
%m	月份（01，02，03）
%c	月份（1，2，3，4）
%d	日(01，02，03）
%H	小时（24小时值）
%h	小时（12小时制）
%i	分钟（01，02，03）
%s	秒（00，01，02）

str_to_data

将字符通过指定的格式转换为日期

select str_to_date('2020-04-14','%Y-%c-%d') as 日期     # 2020-04-14

查询入职日期（hiredate）为1992–4-3的员工信息

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

date_format

将日期转化为字符

select date_format(now(),'%y年%m月%d日') as 日期     # 20年04月14日

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

select name,date_format(hirdate,'%年/%d月/%d日') 入职日期
from employees
whrere commission is not null;

datadiff

计算日期相差

查询员工表(employees)中的最大入职时间(hiredate)和最小入职时间的相差天数（aaa）

select datadiff(max(hiredate),min(hiredate)) aaa
from employees

其他函数

version

select version // 查看数据库版本

database

select database(); // 查看当前的数据库

user

// 查看用户
select user();  //root@localhost

流程控制函数

if函数

select if(10>5,'大','小')   # 大

switch语句

case 要判断的字段或者表达式
when 常量1 then 要显示的值或语句1
when 常量2 then 要显示的值或语句2
...
else 要显示的值n或语句n              #相当于switch的default
end;

查询员工的工资（salary）,要求

​ 部门号===30,显示的工资为1.1倍

​ 部门号===40,显示的工资为1.2倍

​ 部门号===50,显示的工资为1.3倍

​ 其他部门，显示的工资为原工资

select salary 原始工资,department_id,
case department_id
when 30 then salary* 1.1
when 40 then salary* 1.2
when 50 then salary* 1.3
else salary
end as 新工资
from employees;

if-else语句

case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
...
else 要显示的值n或语句n
end;

查询员工的工资（salary）情况

​ 如果工资>20000，显示A级别

​ 如果工资>15000，显示B级别

​ 如果工资>10000，显示C级别

​ 否则显示D级别

select salary,
case 
when salary>20000, then 'A'
when salary>15000, then 'B'
when salary>10000, then 'C'
else 'D'
end as 工资级别
from employees;

多行函数

又称分组函数,聚合函数，统计函数

基本

以下的分组函数都忽略了null值

select sum(salary)和,avg(salary)平均,max(salary)最大,min(salary)最小,count(salary),个数 from employees

和distinct搭配

select sum(distinct salary) from employees

count

效率对比：

​ myisam存储引擎下，count(*)的效率高

​ INNODB存储引擎下,count(*)和count(1)的效率差不多,比count(字段)要高一些

和分组函数一同查询的字段要求

和分组函数一同查询的字段要求是group by后的字段