大数据学习第十二天（mysql不会的查询1）

1、数据

/*创建部门表*/
CREATE TABLE dept(
    deptno        INT     PRIMARY KEY,
    dname        VARCHAR(50) comment '部门名称',
    loc         VARCHAR(50) comment '工作地点'
);

/*创建雇员表*/
CREATE TABLE emp(
    empno        INT     PRIMARY KEY,
    ename        VARCHAR(50),
    job        VARCHAR(50) comment '职位',
    mgr        INT comment '上级',
    hiredate    DATE comment '入职时间',
    sal        DECIMAL(7,2) comment '薪资',
    COMM         DECIMAL(7,2) comment '奖金',
    deptno        INT comment '所属部门id'
);

/*创建工资等级表*/
CREATE TABLE salgrade(
    grade        INT     PRIMARY KEY,
    losal        INT comment '最低薪资',
    hisal        INT comment '最高薪资'
);

/*创建学生表*/
CREATE TABLE stu(
    sid        INT     PRIMARY KEY,
    sname        VARCHAR(50),
    age        INT,
    gander        VARCHAR(10),
    province    VARCHAR(50),
    tuition        INT
);
 

2、表的显示

        1）部门表

        2）员工表

        3）工资等级表

        4）学生表

3、题目

1）取得每个部门最高薪水(工资)的人员名称

# （1）
select e.ename
from emp e
         left join dept d on e.deptno = d.deptno
         left join
     (select d.dname, max(sal) m
      from emp e
               left join dept d on e.deptno = d.deptno
      group by d.dname) temp on d.dname = temp.dname
where temp.dname = d.dname
  and temp.m = e.sal;

# （2）
select ename, sal, emp.deptno, msal
from emp
         join (select deptno, max(sal) msal from emp group by deptno) tb1 on tb1.deptno = emp.deptno
where sal = tb1.msal;

2）列出各种工作的最低工资以及从事此工作的雇员姓名

# （1）
select e.ename, e.job
from emp e
         left join
         (select job, min(sal) m from emp group by job) temp on e.job = temp.job
where e.sal in (temp.m)
  and e.job in (temp.job);

# （2）
select emp.job, ename, sal, msal
from emp
         join (select job, min(sal) msal from emp group by job) tb1 on tb1.job = emp.job
where sal = tb1.msal;

3）列出所有员工的姓名及其直接上级的姓名

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

4）列出薪金等于部门30中员工的薪金的非30号部门的员工的姓名和薪金

select ename, if(sal + COMM is null, sal, sal + COMM)
from emp
where if(sal + COMM is null, sal, sal + COMM) in
      (select if(sal + COMM is null, sal, sal + COMM) from emp where deptno = 30)
  and deptno <> 30;

5）列出所有部门的详细信息和部门人数

如果要统计那些数量中带有空值的，一般用if(empno is not null,1,0)这种表达

select dname, empno
from dept
         left join emp e on dept.deptno = e.deptno;
select dname, sum(if(empno is not null, 1, 0)) as cnt
from dept
         left join emp e on dept.deptno = e.deptno
group by dname;