SQL常用函数（持续更新中）

(一) 单行函数

1、字符函数

------------length 查询参数值的字节数

select length("alice"); select length("函数");

------------concat 拼接字符串

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

-------------upper/lower 变大写/变小写

select upper ("joy");  select lower ("TOM");

--------------substr/substring 截取字符

select substr("人生苦短用Python",4)  out_put; 截取指定索引处后所有字符

select substr("人生苦短用Python",1,3)  out_put; 截取指定索引处指定字符长度的字符

select concat(upper(substr(last_name,1,1)),"_",lower(substr(first_name,1,1))) as out_put from employees;

--------------instr 返回子串中第一次出现的索引，若无则返回0

select instr("我去上学时我饿了","上学") as out_put;

--------------trim 去除首尾指定字符(leading/tailing/both)

select trim (leading'a' from "a函数");

--------------lpad 用指定字符实现左填充指定长度/rpad 用指定字符实现右填充指定长度

select lpad ("haha",10,'*') as out_put;  select rpad ("haha",10,'*') as out_put;

--------------replace 替换

select replace("人生苦短用Python","Python","SQL") as out_put;

2、数学函数

---------------round 四舍五入

select round (1.4567,2);

---------------ceil 向上取整，返回>=该参数的最小整数

select ceil(-9.008);

---------------floor 向下取整，返回<=该参数的最大整数

select floor (-9.008);

----------------truncate 截断指定长度

select truncate(1.69999,2);

3、日期函数

----------------now 返回系统当前日期 + 时间

select now();

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

select curdate();

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

select curtime();

-----------------获取指定年、月、日、小时、分、秒

select year (hiredate) as "年" from employees;

-----------------str_to_date 将字符通过指定格式转换为日期("Y"四位年份，"y"两位年份)

select str_to_date("1995-9-10","%Y-%c-%d");

------------------date_format 将日期转换为字符

select date_format (now(),"%y年%c月%d日");

select last_name ,date_format(hiredate,"%m月/%d日 %y年")as "入职日期"

from employees where commission_pct is not null;

4、流程控制函数

-----------------if 函数：if else 效果

select if(10<5,"大","小");

-----------------case 函数：when case else效果

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;

select salary,case

when salary > 20000 then "A级别"

when salary > 10000 then "B级别"

else  "C级别"

end as "工资级别" from employees;

（二）分组函数/聚合函数（统计使用）

sum 求和、avg 平均、max 最大值、min 最小值、count 计算个数

1、sum 、avg 一般用于处理数值型，max 、min、count 可处理任何类型；

2、以上分组函数都忽略Null值；

3、可以和distinct 搭配实现去重运算(数据量小可用distinct，去重多用group by)

4、count 函数一般使用count(*)效率最高/也可count(1)

5、group by 分组查询 where/having

 

select sum(salary) as"求和",round(avg(salary),2) as "平均",count(salary) as "个数" from employees;

select MIN(hiredate),MAX(hiredate) from employees;

select count(commission_pct) ,count(DISTINCT commission_pct)from employees;

select max(salary) as "最大值",min(salary) as "最小值",round(avg(salary) ,2) as "平均值",sum(salary) as "求和"from employees;

select DATEDIFF(max(hiredate),min(hiredate)) as "相差天数" from employees;

select count(*)from employees where department_id=90;