MySQL 关联查询

建两张表，一张员工表，一张部门表，用作关联查询。其中，部门表与员工表是一对多的关系，因此，在员工表中有一个部门编号（deptno）的外键列去关联部门表

show CREATE table dept;      --查看建表语句
--员工表
CREATE TABLE `emp` (
  `empno` int(11) NOT NULL COMMENT '员工编号',
  `ename` varchar(20) COLLATE utf8mb4_bin NOT NULL COMMENT '员工姓名',
  `job` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '工作',
  `mgp` int(11) DEFAULT NULL COMMENT '上级编号',
  `hiredate` datetime DEFAULT NULL COMMENT '受雇日期',
  `sal` decimal(10,2) DEFAULT NULL COMMENT '薪金',
  `comm` decimal(10,2) DEFAULT NULL COMMENT '佣金',
  `deptno` int(11) DEFAULT NULL COMMENT '部门编号',
  PRIMARY KEY (`empno`) USING BTREE,
  KEY `emp_dno_fk` (`deptno`) USING BTREE,
  CONSTRAINT `emp_dno_fk` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC
--部门表
CREATE TABLE `dept` (
  `deptno` int(11) NOT NULL COMMENT '部门编号',
  `dname` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '部门名称',
  `loc` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '部门区域',
  PRIMARY KEY (`deptno`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC

下面是两张表进行关联查询的练习题

-- 1．列出至少有一个员工的所有部门
select dname,COUNT(empno) from emp e LEFT JOIN dept d ON e.deptno=d.deptno GROUP BY 1;
-- 2．列出薪金比“SMITH”多的所有员工
select * from emp where sal>(select sal from emp where ename='SMITH');
-- 3．列出所有员工的姓名及其直接上级的姓名
select e.ename,m.ename from emp e LEFT JOIN emp m on e.mgp=m.empno;
-- 4．列出受雇日期早于其直接上级的所有员工
select e.ename,e.hiredate,m.ename,m.hiredate from emp e join emp m on e.mgp=m.empno where e.hiredate<m.hiredate;
-- 5. 列出部门名称和这些部门的雇员，同时列出那些没有雇员的部门
select dname,e.* from emp e RIGHT JOIN dept d on e.deptno=d.deptno;
-- 6．列出所有“CLERK”（办事员）的姓名及其部门名称
select ename,dname from emp e join dept d on e.deptno=d.deptno where e.job='CLERK';
-- 7．列出最低薪金大于3000的各种工作
select job from emp GROUP BY 1 HAVING MIN(sal)>3000;
-- 8．列出在部门“SALES”（销售部）工作的员工的姓名
select ename from emp e join dept d on e.deptno=d.deptno where d.dname='SALES'; 
-- 9．列出薪金高于公司平均薪金的所有员工
select * from emp where sal>(select AVG(sal) from emp); 
-- 10．列出与“SCOTT”从事相同工作的所有员工
select * from emp where job=(select job from emp where ename='SCOTT') and ename<>'SCOTT';
-- 11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金
select ename,sal from emp where sal in(select sal from emp where deptno=30) and deptno<>30;
-- 12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金
select ename,sal from emp where sal>(select MAX(sal) from emp where deptno=30) and deptno<>30;
-- 13．列出在每个部门工作的员工数量、平均薪金和平均服务期限
select dname,COUNT(e.empno),AVG(sal),AVG(DATEDIFF(CURRENT_DATE(),hiredate)) from emp e RIGHT JOIN dept d on e.deptno=d.deptno group by d.deptno;
-- 14．列出所有员工的姓名、部门名称和工资
select ename,dname,sal from emp e left join dept d on e.deptno=d.deptno;
-- 15．列出从事同一种工作但属于不同部门的员工的一种组合
select e.ename,e.job,e.deptno,m.ename,m.job,m.deptno from emp e join emp m on e.job=m.job where e.deptno<>m.deptno and e.ename<m.ename;
-- 16．列出所有部门的详细信息和部门人数
select d.*,COUNT(empno) from emp e RIGHT JOIN dept d on e.deptno=d.deptno GROUP BY d.deptno;
-- 17．列出各种工作的最低工资
select job,min(sal) from emp GROUP BY 1;
-- 18．列出各个部门的MANAGER（经理）的最低薪金
select MIN(sal) from emp where job='MANAGER' GROUP BY deptno;
-- 19．列出所有员工的年工资,按年薪从低到高排序
select ename 员工,(sal+IFNULL(comm,0))*12 年薪 from emp ORDER BY 2;
-- 20. 找出月薪相同的员工
select e1.ename,e1.sal,e2.ename from emp e1 join emp e2 on e1.sal=e2.sal where e1.ename<e2.ename;
-- 21. 找到第四处于第四位的薪水金额
select DISTINCT(sal) from emp ORDER BY 1 desc LIMIT 3,1;