04_MySql数据查询语言DQL之常见函数

常见函数
1.字符函数 length	获取参数值的字节个数
select length("zhangsan") as 结果;//8
select length("zhangsan ") as 结果;//9

2.拼接字符串 concat
select concat(last_name," ",first_name) as 姓名 from employees

3.upper转大写  lower转小写
select upper("lisi") as 结果; //LISI
select lower("WANGWU") as 结果;//wangwu
将姓变大写,名变小写,再拼接
select concat(upper(last_name)," ",lower(first_name)) as 姓名 from employees

4.字符串截取 substr,substring 
索引从1开始, 从指定索引处开始截取
select substr("zhaoliu",5) as 结果;//liu
截取从指定索引处指定字符长度的字符
select substring("mingtian",3,3) as 结果;//ngt
姓中首字符大写,其他字母小写,用_拼接
select concat(upper(substr(last_name,1,1)),"_",lower(substr(last_name,2))) as 结果 from employees;

5.instr 返回子串第一次出现的索引，如果找不到返回0
select instr("zhangsan","an") as 结果;//3

6.trim 去除空白字符
select length(trim("   zhangsan ")) as 结果;//8
去除指定字符
select trim("a" from "aaaabbaaaaaaaaa") as 结果;//bb

7.lpad 用指定的字符实现左填充指定长度
返回字符串str，将其左填充字符串padstr至len个字符的长度。
如果str大于len，则返回值缩短为len个字符。
select lpad("zhangsan",3,"*") as 结果; //zha
select lpad("zhangsan",12,"*") as 结果; // ****zhangsan

8.rpad 用指定的字符实现右填充指定长度
select rpad("lisi",2,"*") as 结果; //li
select rpad("lisi",6,"*") as 结果; //lisi**

9.replace 替换
把a替换为z
select replace("aba","a","z") as 结果;//zbz



数学函数
round 四舍五入
select round(2.3) as 结果;//2
select round(-2.3) as 结果;//-2
select round(-2.5) as 结果;//-3

ceil 向上取整,返回>=该参数的最小整数
select ceil(-1.02) as 结果;//-1

floor 向下取整,返回<=该参数的最大整数
select floor(-9.99) as 结果;//-10

truncate 截断
select truncate(1.69999,2) as 结果;//1.69

mod 取余
select mod(10,3) as 结果;//1
select 10%3 as 结果;//1


日期函数
now 返回当前系统日期和时间
select now() as 结果;//2021-07-03 12:38:03

curdate 返回当前系统日期,不包含时间
select curdate() as 结果;//2021-07-03

curtime 返回当前时间,不包含日期
select curtime() as 结果;//12:38:03

可以获取指定的部分，年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) as 年;//2021
SELECT YEAR('1998-1-1') as 年;

SELECT  YEAR(hiredate) 年 FROM employees;

SELECT MONTH(NOW()) as 月;//7
SELECT MONTHNAME(NOW()) as 月;//July

str_to_date 将字符通过指定的格式转换成日期
select str_to_date('2020-5-6','%Y-%c-%d') as 日期;//2020-05-06

date_format 将日期转成字符
select date_format(now(),"%Y年%m月%d日") as 结果;//2021年07月03日

查询有奖金的员工名和入职日期(yyyy年-mm月-dd日)
select last_name as 员工名,date_format(hiredate,"%Y年%m月%d日") as 入职日期 from employees where commission_pct is null

其它函数
select version();//查询当前数据库版本号
select database();//查询当前使用的数据库
select user();//查询当前用户

流程控制函数(if  case)
select if(10>3,"true","false") as 结果 

查询员工名和奖金率(有奖金显示为有奖金,否则无)
select last_name,commission_pct,if(commission_pct,"有奖金","无") as 备注 from employees order by commission_pct desc


函数case使用一：switch case 
询员工的工资，要求
部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为1.3倍
其他部门，显示的工资为原工资
select last_name as 员工名, salary as 原始工资, department_id as 部门号,
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 order by department_id desc

case 函数的使用二：类似于 多重if
查询员工的工资的情况
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000，显示C级别
否则，显示D级别
select last_name,salary, 
case 
when salary>20000 then 'A' 
when salary>15000 then 'B'
when salary>10000 then 'C'
else 'D'
end as 工资级别
from employees order by salary desc;

分组函数
功能：用作统计使用，又称为聚合函数或统计函数或组函数
分类：
sum 求和、avg 平均值、max 最大值 、min 最小值 、count 计算个数
特点：
1、sum、avg一般用于处理数值型
   max、min、count可以处理任何类型
2、以上分组函数都忽略null值
3、可以和distinct搭配实现去重的运算
4、count函数的单独介绍
一般使用count(*)用作统计行数
5、和分组函数一同查询的字段要求是group by后的字段

select sum(salary) 和,round(avg(salary),2) 平均,max(salary) 最高,min(salary) 最低,count(salary) 个数
from employees;

以上分组函数都忽略null值
select count(commission_pct) as 结果 from employees;//35
select commission_pct as 结果 from employees;//107

可以和distinct搭配实现去重的运算
select count(salary) as 去重前,count(DISTINCT salary) as 去重后 from employees;//107,57

count函数的详细介绍
select count(salary) from employees;//107
select count(*) from employees;//107
select count(1) from employees;//107
效率：
MYISAM存储引擎下，count(*)的效率高
INNODB存储引擎下，count(*)和count(1)的效率差不多，比count(字段)要高一些

和分组函数一同查询的字段有限制
查询每个工种的最低工资
select last_name as 员工名, job_id as 工种, min(salary) as 最低工资 from employees group by job_id;