MySQL 常用函数 流程控制

单行函数

1. 单行函数

字符函数
-- length()获取长度

-- lower() 转小写

-- upper()转大写

-- 左填充 lpad()

-- 右填充 rpad()

-- 去掉左右两次的空格 trim()

-- 截取字符串substring() 索引从1开始

-- concat() 字符串的拼接
    

    ## 字符函数
select length(first_name),first_name from employees;

select upper(first_name) from employees;

select lower(first_name) from employees;

## 左填充
select LPAD(last_name,10,'x')  from employees;

##右填充
select RPAD(last_name,10,'x')  from employees;

#去除空格  ltrim  rtrim  trim
select ltrim('   abc'),'   abc';
select rtrim('abc    '),'abc    ';
select trim('      abc    '),'    abc    ';

select trim('abc'),'abc';


#截取字符串  字符初始位置从1开始
#SUBSTRING(str,pos,len)    str 表示需要截取的字符串  pos 截取起始位置 len 截取的长度
select SUBSTRING(last_name,2,1) from employees;
select SUBSTR(last_name,2,1) from employees;

select CONCAT('疯狂','的','石头');

​

案例：
案例1: 查询员工的姓名，将姓名拼接起来，显示在一个字段下 
first_name  last_name
select concat(first_name,last_name) as 姓名 from employees;

数学函数 
-- ceil() 向上取整

-- floor() 向下取整


-- round() 四舍五入


-- 取绝对值abs()

-- rand()生成随机数 [0,1)

select ceil(3.456);

select floor(3.456);

select round(3.556);

select abs(-1);

select RAND();

日期函数
-- 获取当前时间  日期+时间 now()

-- 获取年月日  year() month() day()

-- 获取当前日期

-- 获取当前时间

#获取当前系统时间
select now();  # 年月日 时分秒
select YEAR(now());  #提取所传时间的 year部分
select month(now()); #提取所传时间的 month
select day(now());   #提取所传时间的 day
select DATE(now());  #提取所传时间的 年月日部分
select TIME(now());  #提取所传时间的 时分秒部分

#流程控制解构
1.if();
2.  case 
	when 条件表达式 then 值或者表达式
	when 条件表达式 then 值或者表达式
	...
	else 值或者表达式
	end  
	(相当于  if()...else if()...else if()... else )结构
	
案例2：查询员工的薪资和姓名，如果薪资大于10000，是一个高级员工，小于等于10000初级员工
select salary,last_name,
IF(salary>10000,'高级员工','初级员工') as 级别
from employees;


案例3：查询员工的旧薪资和姓名，新薪资，如果部门id>100 薪资显示 3倍,如果部门id>50薪资显示2倍，如果部门id>30 薪资不变,如果部门id <30 薪资减半.

select salary,last_name,
case 
	when department_id > 100 then salary*3
	when department_id > 50 then salary*2
	when department_id > 30 then salary
	else  salary/2
	end  as 新薪资
from employees

  

多行函数（分组函数）

常见的分组函数
sum(字段):求该字段的所有值的和
avg(字段):求该字段的平均值
max(字段):求最大值
min(字段):求最小值
count[字段):计算该字段中非空的值的个数

代码

#案例1：查询有奖金的员工的平均工资
select avg(salary) as 有奖金的员工的平均薪资 from employees 
where commission_pct is not null;

#案例2：查询年薪的平均值
#年薪 = （薪资+奖金）*12
select avg(  (salary+ ifnull(commission_pct,0))*12  ) as 年薪平均值 
from employees;

#案例3 统计员工数量 使用count统计数量时，优先选择 主键
select count(employee_id) from employees;


#案例4 分别统计每个工作的员工数量
#分组后 显示字句中 一般只会显示分组字段，以及分组函数，其他字段没有意义
select job_id,count(job_id) from employees group by job_id;

#案例5 分别统计每个部门的员工数量
select department_id,count(employee_id) from employees group by department_id;

#案例6 查询最低工资都比5000高的工作
select job_id,min(salary) 工作最低薪资 from employees 
group by job_id having min(salary) > 5000;


# where 分组前过滤  行过滤,where 不支持分组函数
# having 分组后过滤 组过滤,having 支持分组函数

#目前为止的查询关键字 where,having,group by,select,from,order by
#这些关键字存在书写顺序，运行顺序
#select -> from - > where -> group by -> having -> order by