Mysql语句及其应用

dept：部门表：
deptno：部门编号，dname：部门名称，loc：部门位置

salgrade：薪水表
grade：职级，losal：最低薪水，hisal：最高薪水

emp：雇员信息表
empno：雇员工号，ename：雇员名字，job：雇员工种，mgr：上级经理，hiredate：入职日期，sal：薪水，comm：津贴，deptno：从属哪个部门

//select语句
select * from emp	//查询emp表单
select ename, sal from emp		//从emp表单中查询ename，sal两列
select ename, sal*12 as `annule sal` from emp	//计算一年的薪水
select ename, sal*12+(case when comm is null then 0 else comm end) as `annule sal`	//计算年终总薪水
select distinct deptno from emp		//将该列表去除重复值
select distinct deptno, job from emp	//两个列表的组合去重

select * from emp where deptno = 10		//查询关于部门10的所有员工
select * from emp where deptno <> 10	//查询部门10以外的所有员工
select * from emp where sal>=800 and sal<=1500	//查询薪水在800-1500之间的所有员工
select * from emp where sal between 800 and 1500	//查询薪水在800-1500之间的所有员工
select * from emp where comm is null	//查询津贴为空的员工
select * from emp where comm is not null	//查询津贴不为空的员工
select * from emp where sal in (800,1500)	//查询薪水为800和1500的员工
select * from emp where ename in ('SMITH','KING')	//查询名字为'SMITH','KING'的员工
select * from emp where hiredate>'1981-02-01'	//查询入职日期为'1981-02-01'以后的员工

select * from emp where ename like	'%A%'	//%表示0个或者多个字母，表示查询含有A字母的员工名字
select * from emp where ename like	'_A%'	//_表示有1个字母，表示A左边有一个字母，右边有若干个字母，的员工名字
select * from emp where ename like	'%\%%'	//\表示转义字符，表示%前后各有若干个字符

select * from emp order by sal asc	//按薪水排序，默认升序排列，低端数值最大
select * from emp order by sal desc		//按薪水排序，降序排列，低端数值最小
select * from emp where deptno <>10 order by sal desc	//先查询10部门以外的部门员工，然后按薪水倒序排列，低端最小
select ename, sal, deptno from emp order by deptno asc, sal desc	//在部门信息(升序排列)相同的情况下按照薪水降序排列

//常用函数
select lower(ename) from emp  //大写转小写upper()为大写
select concat('www.','baidu.','com') as url 	//拼接字符串为一个
select char_lenth('aaaa') as lenth	//查询长度
select substring('goodgoodstudy', 0, 5)	//截取0-5号位字符
select ltrim/rtrim/trim('  aaa   ')		//去除左边空格、右边空格、两边空格
select round(),abs(),ceil(),floor()		//绝对值，向上取值，向下取值
select round(3.1415926,3)		//四舍五入，保留三位小数
select truncate(3.1415926,3)	//截断，只保留3位小数，不考虑四舍五入

select curdate()	//当前日期2021-04-15
select curtime()	//当前时间09:26:30
select now()	//日期加时间
select month(curdate())		//获取月份
select ename, hiredate from emp order by year(hiredate)		//按入职年月日排序
select monthname(curdate())			//获取月份名称
select WEEKDAY('2021-04-13 22:23:00')	//返回date的星期索引(0=星期一，1=星期二, ……6=星期天)
select DAYOFWEEK('1998-02-03')	//返回日期date的星期索引(1=星期天，2=星期一, ……7=星期六)

//组函数，针对一列所用的函数
select max(sal) from emp	//查询这薪水这一列的最大值
select ename from emp where sal = (select max(sal) from emp		//查询薪水最高的若干人的名字
select min(sal) from emp	//查询这薪水这一列的最小值
select avg(sal) from emp	//查询这薪水这一列的平均值
select sum(sal) from emp	//查询这薪水这一列的平均值
select count(*) from emp	//一共有多少条记录
select count(comm) from emp	//有津贴的有多少人

表的连接，如下：

select deptno, max(sal) max_sal from emp group by deptno	
//每个部门的最高薪水就一个,所以该句法可行，表格如下

select ename, sal, emp.deptno from emp join
//将emp表和table_dept表相连接，由于deptno信息两个表都有，故需要指定是哪个表的deptno
(select deptno, max(sal) max_sal from emp group by deptno) table_dept
//查询两个表部门相同&&薪水相同的人的名字、薪水、部门
where emp.deptno=table_dept.deptno and emp.sal=table_dept.max_sal
//结果如下

where是对每条记录进行限制，having是对分组进行限制，如下：

select avg(sal),deptno from emp group by depyno having avg(sal)>2000
//平均薪水大于2000的部门

总结：
select xx from 表名 ：取数据
where 条件 ：数据过滤
group by 进行分组：分组
having 对分组进行限制：对分组进行限制
order by 排序：排序

列子：
select avg(sal),deptno from emp where sal>1200 
group by deptno 
having avg(sal)>1500 
order by avg(sal) desc

//分析该语句分4步走：
//1.由于where是对记录进行限制，所以，先去除sal>1200的数据，获得[平均薪水,对应的部门号]；
//2.将[平均薪水,对应的部门号]进行分组，这里为唯一分组，一个部门号对应唯一平均薪水
//3.having是限制组的，所以将平均薪水>1500的数据去除；
//4.将剩下的[平均薪水,部门号]按降序排列；

子查询：核心思想就是把中间的select语句所查询出来的数据当成一张表

//问题1：哪些人的工资比平均工资高
select ename, sal from emp where sal>(select avg(sal) from emp)

//问题2：按照部门进行分组之后，每个部门工资最高的人可能有多个，要求显示他的名字，薪水，部门编号
select ename, deptno from emp 
join (select max(sal) max_sal, deptno from emp group by deptno) table_max_sal
on (emp.sal=table_max_sal.sal and emp.deptno=table_max_sal.deptno)

//问题3：把部门的人员和该人员的上级对照列出来
select e1.ename emp,e2.ename mgr from emp e1 left join emp e2 
on/where e1.mgr=e2.empno

更新中。。。