Mysql【33道SQL】

DQL（数据查询语言）：查询语句，只要select语句都是DQL。

DML（数据操作语言）：insert delete update，对表当中的数据进行增删改。

DDL（数据定义语言）：create drop alter，对表结构的增删改。

TCL（事务控制语言）：commit提交事务，rollback回滚事务。(TCL中的T是Transaction)

DCL（数据控制语言）：grant授权、revoke撤销权限等。

员工表：

select * from emp;

部门表：

select * from dept;

薪水等级表：

select * from salgrade;

总条数：count()       

select count(*) from emp;

select count(*) from 表名；

去重：distinct

select distinct mgr from emp where mgr is not null;

select distinct 字段名 from 表名 where 字段名 去除空null;

函数：最大值 max()，最小值 min()，平均值 avg()，求和   sum()

1、取得每个部门最高薪水的人员名称

第一步：取得每个部门最高薪水
    select deptno, max(sal) maxsal from emp group by deptno;

第二步：将以上结果当做临时表t，t表和emp e表进行连接，条件是:t. deptno = e. deptno and t.maxsal=e. sal

select 
    e.ename, t.* 
from 
    (select deptno,max(sal) maxsal from emp group by deptno) t 
join 
    emp e 
on 
    t.deptno = e.deptno and t.maxsal = e.sal;

2、那些人的薪水在部门平均薪水之上

第一步：取出每个部门的平均薪水
    select deptno,avg(sal) avgsal from emp group by deptno;

第二步：把上表看成零时表t 和emp e 连接，连接条件t.deptno=e.deptno and t.avgsal<e.sal
select 
    e.ename, e.sal, t.* 
from 
    (select deptno, avg(sal) avgsal from emp group by deptno) t 
join 
    emp e 
on 
    t.deptno = e.deptno and t.avgsal < e.sal;

3、取得部门中（所有人的）平均的薪水等级

平均的薪水等级：先计算每一个薪水的等级，然后找出薪水等级的平均值。

平均薪水的等级：先计算平均薪水，然后找出每个平均薪水的等级值。

第一步：先取出所有人的平均薪水等级
select 
    e.ename, e.deptno, e.sal, s.grade 
from 
    emp e 
join 
    salgrade s 
on 
    sal between s.losal and s.hisal;

第二步：按照上面结果来分组deptno，求grade平均值
select 
    e.deptno, avg(grade) 
from 
    emp e 
join 
    salgrade s 
on 
    sal between s.losal and s.hisal group by e.deptno;

4、不准用组函数（Max ），取得最高薪水

第一种：sal降序，limit 1 取数据
    select e.ename,e.sal from emp e order by sal desc limit 1;

第二种：用max（）函数
    select e.eanme,max(sal) from emp e order by sal;

第三种：表的自连接
select 
    ename, sal 
from 
    emp 
where 
    sal not in(select distinct a.sal from emp a join emp b on a.sal < b.sal);

5、取得平均薪水最高的部门的部门编号

第一种：降序，大到小，取第一个

第一步：取出每个部门的平均薪水
    select deptno, avg(sal) from emp group by deptno;

第二步：降序取数据
    select deptno, avg(sal) avgsal from emp group by deptno order by avgsal desc limit 1;

第二种：max（）取值

第一步：取出每个部门的平均薪水
    select deptno, avg(sal) from emp group by deptno;

第二步：找出上，面结果的最大值
select 
    deptno, max(avgsal) 
from 
    (select deptno, avg(sal) avgsal from emp group by deptno) t;

6、取得平均薪水最高的部门的部门名称

第一种：

第一步：取出平均薪水最高的部门
select 
    deptno, avg(sal) avgsal 
from 
    emp 
group by 
    deptno order by avgsal desc limit 1;

第二步：把上面表看成 t 和dept d表连接，连接条件d.deptno=t.deptno
select 
    d.deptno, d.dname, t.avgsal 
from 
    (select deptno, avg(sal) avgsal from emp group by deptno order by avgsal desc limit 1)t 
join 
    dept d 
on 
    d.deptno=t.deptno;

☆第二种：

select 
    d.dname, avg(e.sal) avgsal 
from 
    emp e 
join 
    dept d 
on 
    e.deptno = d.deptno group by d.dname order by avgsal desc limit 1;

7、求平均薪水的等级最低的部门的部门名称

第一步：找出每个部门的平均薪水
    select deptno, avg(sal) avgsal from emp group by deptno;

第二步：找出每个部门的平均薪水等级，并排序，取第一条数据
select 
    t.deptno, t.avgsal, s.grade 
from 
    (select deptno,avg(sal) avgsal from emp group by deptno) t 
join 
    salgrade s 
on 
    avgsal between s.losal and s.hisal order by avgsal limit 1;

☆8、取得比普通员工(员工代码没有在 mgr 字段上出现的) 的最高薪水还要高的领导人姓名

第一步：员工编号没有在以上范围内的都是普通员工
    select distinct mgr, sal from emp where mgr is not null;

第二步：找出普通员工的最高薪水
select 
    max(sal) 
from 
    emp 
where 
    empno not in(select distinct mgr from emp where mgr is not null);

第三步：找出高于1600的
select 
    ename, sal 
from 
    emp 
where 
    sal > (select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null));

9、取得薪水最高的前五名员工

select ename, sal from emp order by sal desc limit 4;

10、取出薪水最高的第六到第十名员工

select ename, sal from emp order by sal desc limit 5,5;

11、取得最后入职的 5 名员工（日期也可以降序，升序）

select ename, hiredate from emp order by hiredate desc limit 5;

select ename, hiredate from emp order by hiredate limit 9,5;   

12、取得每个薪水等级有多少员工        

第一步：取出每个员工的薪水等级
select 
    e.ename, e.sal, s.grade 
from 
    emp e 
join 
    salgrade s 
on 
    e.sal between s.losal and s.hisal;

第二步：按照grade来分组        
select 
    e.ename, e.sal, s.grade 
from 
    emp e 
join 
    salgrade s 
on 
    e.sal between s.losal and s.hisal order by grade;

13、列出所有员工及领导的姓名

select a.ename '员工',b.ename '领导' from emp a left join emp b on a.mgr=b.empno;

14、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称

第一种：

select
    a.mgr '员工编号',a.ename '姓名',a.hiredate '入职日期',
    b.mgr '领导编号',b.ename '姓名',b.hiredate '入职日期',d.dname '部门名称'
from 
    emp a 
join 
    emp b 
on 
    a.mgr=b.empno 
join 
    dept d 
on 
    a.deptno=d.deptno where a.hiredate<b.hiredate;

第二种：

select
    a.mgr '员工编号',a.ename '姓名',a.hiredate '入职日期',
    b.mgr '领导编号',b.ename '姓名',b.hiredate '入职日期',d.dname '部门名称'
from 
    emp a 
join 
    emp b 
on 
    a.mgr = b.empno and a.hiredate<b.hiredate 
join 
    dept d 
on 
    a.deptno=d.deptno;

15、 列出部门名称和这些部门的员工信息, 同时列出那些没有员工的部门

select e.*, d.dname from emp e right join dept d on e.deptno = d.deptno;

16、列出至少有 5 个员工的所有部门

select deptno from emp group by deptno having count(*) >= 5;

17、列出薪金比"SMITH" 多的所有员工信息

select ename, sal from emp where sal > (select sal from emp where ename = 'smith');

☆18、 列出所有"CLERK"( 办事员) 的姓名及其部门名称, 部门的人数

select ename,job from emp where job = 'CLERK';

select 
    e.ename, e.job, d.dname, d.deptno 
from 
    emp e 
join 
    dept d 
on 
    e.deptno = d.deptno where e.job = 'CLERK';

//每个部门的人数？
select deptno, count(*) as deptcount from emp group by deptno;

select 
    t1.*, t2.deptcount 
from 
    (select e.ename, e.job, d.dname, d.deptno from emp e join dept d on e.deptno = d.deptno where e.job = 'CLERK') t1 
join 
    (select deptno, count(*) as deptcount from emp group by deptno) t2 on t1.deptno = t2.deptno;

19、列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数

select job, count(*) from emp group by job having min(sal) > 1500;

☆20、列出在部门"SALES"< 销售部> 工作的员工的姓名, 假定不知道销售部的部门编号

select * from dept where dname = 'sales';

select ename from emp where deptno=(select deptno from dept where dname = 'sales');

★21、列出薪金高于公司平均薪金的所有员工, 所在部门, 上级领导, 雇员的工资等级.

select 
    e.ename '员工', d.dname,l.ename '领导', s.grade 
from 
    emp e 
join 
    dept d on e.deptno =d.deptno 
left join 
    emp l 
on 
    e.mgr = l.empno 
join 
    salgrade s 
on 
    e.sal between s.losal and s.hisalwhere e.sal > (select avg(sal) from emp);

☆22、列出与"SCOTT" 从事相同工作的所有员工及部门名称

select * from emp where ename = 'scott';

select 
    e.ename, e.job, d.dname 
from 
    emp e 
join 
    dept d 
on 
    e.deptno=d.deptno 
where 
    e.job = (select job from emp where ename='scott') and e.ename <> 'scott';

☆23、列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金

select 
    ename, sal 
from 
    emp 
where 
    sal in(select distinct sal from emp where deptno = 30) and deptno <> 30;

☆24、列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金. 部门名称

select 
    e.ename, e.sal, d.dname 
from 
    emp e 
join 
    dept d 
on 
    e.deptno=d.deptno where e.sal > (select max(sal) from emp where deptno=30);

25、列出在每个部门工作的员工数量, 平均工资和平均服务期限

没有员工的部门，部门人数是0
select
    d.deptno, count(e.ename) ecount, ifnull(avg(e.sal),0) as avgsal, ifnull(avg(timestampdiff(YEAR, hiredate, now())), 0) as avgservicetime
from
    emp e 
right join
    dept d
on
    e.deptno = d.deptno
group by
    d.deptno;

在mysql当中怎么计算两个日期的“年差”，差了多少年？
TimeStampDiff(间隔类型, 前一个日期, 后一个日期)
timestampdiff(YEAR, hiredate, now())

间隔类型：
        SECOND   秒，
        MINUTE   分钟，
        HOUR   小时，
        DAY   天，
        WEEK   星期
        MONTH   月，
        QUARTER   季度，
        YEAR   年

26、 列出所有员工的姓名、部门名称和工资。

select e.ename, e.sal, d.dname from emp e join dept d on e.deptno = d.deptno;

27、列出所有部门的详细信息和人数

select 
    d.deptno, d.dname, d.loc, count(e.ename) 
from 
    emp e 
join 
    dept d 
on 
    e.deptno=d.deptno 
group by 
    d.deptno, d.dname, d.loc;

28、列出各种工作的最低工资及从事此工作的雇员姓名        

第一步：select job, min(sal) minsal from emp group by job;

第二步：
select 
    e.ename, t.* 
from 
    emp e 
join 
    (select job,min(sal) minsal from emp group by job) t 
on 
    e.job=t.job and e.sal=t.minsal;

29、列出各个部门的 MANAGER( 领导) 的最低薪金        

select deptno, job, min(sal) from emp where job='manager' group by deptno;

30、列出所有员工的 年工资, 按 年薪从低到高排序

select ename, (sal+ifnull(comm,0))*12 nianxin from emp order by nianxin;

☆31、求出员工领导的薪水超过3000的员工名称与领导

select 
    a.ename '员工',b.ename '领导' 
from 
    emp a 
join 
    emp b 
on 
    a.mgr=b.empno 
where 
    b.sal>3000;

☆32、求出部门名称中, 带'S'字符的部门员工的工资合计、部门人数

select 
    d.deptno, d.dname, d.loc,count(e.ename), ifnull(sum(e.sal),0) sumsal 
from 
    emp e 
right join 
    dept d 
on 
    e.deptno=d.deptno 
where 
    d.dname 
like 
    '%s%' 
group by 
    d.deptno, d.dname, d.loc;

★33、给任职日期超过 30 年的员工加薪 10%

update emp set sal = sal * 1.1 where timestampdiff(YEAR, hiredate, now()) > 30;