SQL语句（更新ing）

前言

主要记录在2020-2021小学期实习期间，学习到的SQL语句的相关知识、心得

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、SQL基础语句

示例申明

emp：员工表：
empno 工号
ename 工名
job 工作
mgr 经理
hiredate 租用日期
sal 工资
comm 奖金
deptno 部门编号

dept 部门：
deptno 部门编号
dname 部门名称
loc 部门所在地

salgrade 工资等级：

grade 等级
losal 最低工资
hisal 最高工资

1. join on OR inner join on

个人理解：改语法将一个表 join（添加） 到另一个表是 on（基于）我们规定的法则，即结合多个表

详细用法—参考菜鸟教程：https://www.runoob.com/sql/sql-join.html

1.1 双表连接

求员工中工资大于2000的工名与部门名称？

我们基于两个表中deptno是相通的，把emp与dept结合到一起

# 标准写法
select emp.ename,dept.dname from  emp join dept 
	on emp.deptno=dept.deptno 
	where sal>2000;
# Mysql写法
select emp.ename,dept.dname from emp,dept
	where emp.deptno=dept.deptno
		and emp.sal>2000;

1.2 多表连接

查询所有员工的姓名、部门名称、工资级别？

先基于deptno，将emp与dept结合
再基于salgrade与emp的逻辑关系，将emp与salgrade结合，从而实现三表结合

# 标准写法
select emp.ename,dept.dname,salgrade.grade
	from emp join dept on emp.deptno=dept.deptno
	join salgrade on emp.sal between salgrade.losal and salgrade.hisal;
# Mysql 写法
SELECT ename,dname,grade
	FROM emp,salgrade,dept
	WHERE emp.sal>=salgrade.losal 
		AND emp.sal<=salgrade.hisal 
		AND emp.deptno=dept.deptno;

1.3 表自连接

个人理解：实际上就是自己和自己通过特定条件连接在一起
下例：让 e 表的经理工号 与 m 表的员工工号 进行对等匹配，然后将两个表连接在一起

SELECT e.ename '员工',m.ename '经理'
from emp e
 inner join emp m on e.mgr = m.empno;

查询工资高于直属经理的员工的姓名、工资及经理姓名:

只需在where后增加条件：该行的数据的员工工资是高于经理工资的就输出

SELECT e.ename '员工',e.sal '员工工资',m.ename '经理'
from emp e
 inner join emp m on e.mgr = m.empno
where e.sal>m.sal;

2. between … and …

查询emp表格中工资在1000-2000的员工名？

select emp.ename from emp where emp.sal between 1000 and 2000;

3. order by 与 limit i,n

查询工资前三的员工名？

select emp.ename from emp order by sal desc limit 0,3; 

• order by sal ：将sal升序排列
• order by sal desc：将sal降序排列
• limit i,n ：从该列的第 i 个位置开始，向下查询 n 条数据

查询部门人数最多的部门名称、部门工资总和？

利用join on先将emp与dept合并
然后统计emp中部门编号的总和(即部门中的总人数)，并进行排列
最后限制输出，只输出1个人

select dept.dname,sum(emp.sal) from dept join emp 
	on dept.deptno=emp.deptno
	order by count(emp.deptno) desc limit 1;

• count(emp.deptno)：统计部门编号
• sum(emp.sal)：计算工资总和

4.插入数据

 insert into emp(empno,ename,job,mgr,sal,deptno) values(1000,'张三','测试',7788,5000,40);

5.更新数据

update emp set sal=6000,comm=500 where ename='张三';

6. ifnull(x,y)

在表格中，若有一行中的某个数据没有填写即为NULL，Mysql在处理数据表达式时含有NULL的表达式其结果都为NULL，故出错，所以增加个判断语句ifnull：ifnull(emp.comm,num):如果emp.comm为NULL，则使用num，反之使用emp.comm，num数值我们自己根据情况设定即可

查询所有员工的年收入，并降序排列:

select emp.ename,12*emp.sal+ifnull(emp.comm,0) income
from emp
group by income desc;

7.Mysql处理日期

1.获取表格中日期的年、月、日

select year(hiredate),month(hiredate),DATE(hiredate)
from emp;

• year(Date)：获取日期类数据的年份
• month(Date)：获取月份
• DATE(Date)：获取日份

2. 获取系统的当前时间及其年、月、日

year(NOW())
month(NOW())
DATE(NOW())

• NOW( )：即获取系统当前的年月日

7.Group by与视图

• group by xxx having … ：根据我们分组的依据 xxx 进行分组，使用group by 后不能使用where，得使用having来添加其他条件
• 视图就像包装函数，当这段Select被多次在不同位置使用时，我们就封装为视图，然后调用视图即可

查询部门人数最多的部门名称、部门工资总和

create view v_empdept
as
select d.deptno,d.dname,sum(e.sal) sal
	from emp e
	inner join dept d on e.deptno=d.deptno
	here e.deptno in(select deptno 
						from emp 
						group by deptno 
						having count(empno)=(select distinct count(empno)c 
												from emp 
												group by deptno 
												order by c desc
												limit 0,1)) 
	group by d.deptno,d.dname ;

解读：

• 最内层：先根据emp中的deptno，所有行数据中deptno相同的分为一组，并统计每组的empno，然后按照每组的empno总数进行降序排序，取第一名即员工人数最多的那个组
• 中间层：根据emp的deptno分组后，匹配内层的查询结果，即找到员工最多的组，即确定了人数最多的那个部门
• 最外层：连接emp与dept两个表，然后匹配我们中间层的查询结果，即匹配人数最多那个部门的deptno，最后输出dname等等数据

8.distinct

注意数据相同时进行排列，会存在并列的问题，要使用到去重，distinct

查询工资前三名员工的姓名

select distinct emp.ename,emp.sal
	from emp 
	order by sal desc