工作中用到的mysql知识总结

一、字符函数
#1.length 获取参数值的字节个数 （注：utf8、jbk）

select length('join');
select length('张三丰hahaha');    //utf8:15 一个汉字3字节
								 //jbk:12  一个汉字2字节

#2.concat 拼接字符串

select concat('a','b','c');   //abc

#3.upper、lower 大小写转换
#4.substr 字符串截取 （注：索引从1开始）

select substr('李莫愁爱上陆展元',7) out_put;  //'陆展元'
select substr('李莫愁爱上陆展元',1,3) out_put; //'李莫愁'

三、日期函数

#now

select now();
select curdate();  //当前日期，不包含时间
select curtime(); //当前时间，不包含日期
select year(now());//年
select month(now());//月
select monthname(now());//月 英文

#str_to_date() 注：通过指定合法格式转换

四、流程控制函数

select if(10>5,'大于','小于');
select case();

#case函数的使用：
/**
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1；
when 常量1 then 要显示的值1或语句1；
else 要显示的值n或者语句n;
end；
**/

#案例：获取员工工资
如果部门=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;    //判断等值的sql

#案例：查新员工的工资情况
如果工资>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;

五、分组函数
分类：
sum 求和、avg 平均值、max最大值、min最小值、count 计算个数
注：1.sum、avg用于处理数值型
max、min、count可以处理任何类型
以上分组函数，都忽略null值，不参与运算
2.可以和distinct 搭配使用

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

select sum(distinct salary),sum(salary) from employees;
select count(distinct salary),count(salary) from employees; //有几种类型的工资

#count函数的详细介绍 count(*) count(1) 用于统计行数

select count(*) from employees;

#和分组函数一同查询的字段有限制

#DATEDIFF() 求日期差 （天数）

select datediff(now(),'2021-10-01')    //前-后=天

六、分组函数
group by

分组筛选分两种：
分组前筛选： group by 之前 where 建议使用：分组前筛选
分组后筛选：group by 之后 having
注：分组函数做筛选条件肯定是放在having子句中

七、相关子查询 及 不相关子查询

#查询的嵌套

select * from emp where sal > (select sal from emp where ename = 'CLARK')

#不相关子查询： 子查询可以独立运行；先运行子查询，在运行外查询 实例如上
1.单行子查询： （指子查询只存在一个值）直接比较即可 实例如上

#案例：查询工资高于平均工资的员工名字和工资

select ename,sal from emp where sal>(select avg(sal) from emp)

#案例：查询和CLARK同一部门且比他工资低的员工名称和工资

select ename,sal from emp where job = (select job from emp where ename='CLARK') and sal >  (select sal from emp where ename='CLARK')

2.多行子查询： 不可以直接比较，需借助 in any all 实例如下

select * from emp where deptno = 20 and job in (select job from emp where deptno = 10)

select * from emp where deptno = 20 and job = any (select job from emp where deptno = 10)

in == 集合中的任何一个 或 = any (集合)

#案例：查询工资比所有的‘SALESMAN’都高的员工编号、姓名、工资

select empno,ename.sal from emp where sal > (select max(sal) from emp where job = 'SALESMAN')


select empno,ename.sal from emp where sal > all (select sal from emp where job = 'SALESMAN')

all 所有的

#相关子查询：子查询不可以独立运行；先运行子查询，在运行外查询
#案例：查询所有部门最高工资的员工

select * from emp e where e.deptno = 10 and sal = (select max(sal) from emp where depno=10)
union
select * from emp e where e.deptno = 20 and sal = (select max(sal) from emp where depno=20)
union
select * from emp e where e.deptno = 30 and sal = (select max(sal) from emp where depno=30)

如果部门多这样写有点繁琐,语句合并

select * from emp e where sal = (select max(sal) from emp where depno = e.deptno )  //相关子查询

#案例：查询工资高于其所在部门的平均工资的那些员工

select * from emp  e where sal> (select avg(sal) from emp where deptno = e.deptno)

#案例：一张成绩表，里面有3个字段：语文，数学，英语。请用一条sql语句查询这表里的记录并按以下条件显示出来：>=80 优秀 >=60 && <80 及格 <60不及格
显示格式：
语文 数学 英语
及格 优秀 不及格

select 
case chinese
	when chinese >= 80 then '优秀'
	when chinese >= 60 then '及格'
	when chinese <60 then '不及格'
end 语文,
case maths
	when maths >= 80 then '优秀'
	when maths >= 60 then '及格'
	when maths <60 then '不及格'
end 数学,
case english
	when english >= 80 then '优秀'
	when english >= 60 then '及格'
	when english <60 then '不及格'
end 英语
 from table1 

#案例：
表内容：
2005-05-09 胜
2005-05-10 胜
2005-05-10 负
2005-05-10 胜
2005-05-11 胜
2005-05-11 负
生成下列结果：
比赛日期 胜 负
2005-05-09 1 0
2005-05-10 2 1
2005-05-11 1 1

select gameday as 比赛日期,
count(if(result == '胜')) as 胜,
count(if(result == '负')) as 负
from table2
group by gameday


select gameday as 比赛日期,
sum(if(result == '胜','1','0')) as 胜,
sum(if(result == '负','1','0')) as 负
from table2
group by gameday

八、定义变量
举例：查询员工工资并排名。

select (@i:=@i+1) ranking, playerid, num from activity_ad,(select @i:=0) t ORDER BY num DESC limit 10

set @i := 0;
select (@i:=@i+1) ranking, playerid, num from activity_ad ORDER BY num DESC limit 10