MySQL-常用函数

1. 单行函数

1.1字符函数

length()   获取参数值的字节个数
concat()   拼接字符串
upper()    将字符串转换为大写
lower()    将字符串转换为小写
substr()   截取指定数量字符的
instr()    返回子串第一次出现的索引，如果找不到则返回0
trim()     剔除首尾的空格或指定字符
lpad()     用指定的字符实现左填充到指定长度(按照字符数计算)
rpad()     用指定的字符实现右填充到指定长度(按照字符数计算)
replace()  替换指定字符

# 代码示例

select length('HelloWorld你好世界')  //22(采用UTF-8编码，英文字母占1字节，汉字占用3字节)

select concat(lastname,'-',firstname)  //拼接lastname和firstname,中间使用'—'隔开

select upper('abc')   //ABC
select lower('ABc')   //abc

注意：SQL语言索引从1开始
select substr('HelloWorld',4)   //loWorld
select substr('HelloWorld',1,5)   //Hello

select instr('!!!HelloHelloWorldWorld','Hello')   //4

select trim('  Hello  ')   //hello(剔除了空格)
select trim('a' from 'aaHelloaaWorldaa')   //HelloaaWorld

select lpad('Hello',10,'a')   //aaaaaHello
select rpad('Hello',10,'a')   //Helloaaaaa

select replace('HelloHelloWorldWorld','Hello','Hi')   //HiHiWorldWorld   

1.2 数学函数

round()     四舍五入
ceil()      向上取整
floor()     向下取整
truncate()  截断(保留小数点后指定位数)
mod()       取余

1.3 日期函数

now()   返回当前系统时间+日期
curdate() 返回当前系统日期
curtime() 返回当前系统时间
year() 获取指定年份
month()获取指定月份
day()获取指定日
str_to_date()将字符通过指定格式转换为日期
date_format() 将日期转换为字符

select str_to_date('2000-01-01',%Y-%m-%d)  //2020-01-01

select date_format(01/01/2020,'%d-%m-%Y')   ///2020-01-01

1.4 流程控制函数

• if 函数( 效果相当于三元运算符)

# 员工有奖金就输出 'good' 否则输出 '加油'
select name,commission_pct,IF(commission_pct IS NULL,'good','加油') as comment from employees;

• case 函数

# 用法1
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
...
else 要显示的值n或语句n;
end

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

# 用法1示例
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;

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

2. 分组函数

AVG()    按列计算平均值
SUM()    按列计算值的总和
MAX()    求一列中的最大值
MIN()    求一列中的最小值
COUNT()  按列值计个数

• AVG( )、SUM( ) 、MAX( )、MIN( )忽略列值为NULL的行。
  使用COUNT( )时如果指定列名那么会忽略为NULL的行，如果是COUNT( * )则不会忽略为NULL的行
• Distinct 用于消除重复行，* (ALL)表示所有。COUNT( * )和Distinct不能混用

# 求学号为2020的学生的总分和平均分
select SUM(score) as Total_Score,AVG(score) as Avg_Score fron SC where SNo='2020'

# 求选修C1号课程的最高分、最低分及之间相差的分数
select MAX(score) as Max_score,MIN(score) as Min_Score,MAX(score)-MIN(score) as sub
from SC where CNo='C1'

# 求学校中共有多少个系
select COUNT(Distinct Dept) as Num_Dept from S

# 求计算机系学生的总数
select COUNT(*) from S where CName='计算机'