MySQL 查询3

一、上节课内容回顾：
复习：查询每个部门中没有“佣金（COMM）”员工的最低工资、最高工资和平均工资。
结果只显示平均工资在1500以上的信息，并将查询结果按照部门号降序排序。

select MIN(SAL),MAX(SAL),AVG(SAL) from emp
where comm is NULL 
group by deptno
having avg(sal)>1500
order by deptno

二、本节课主要任务：
要求：每道题目给出SQL命令及运行结果截图。
题目1：查询雇员的姓名、编号及所在部门名称；

SELECT ename,empno,dname
FROM emp INNER JOIN dept ON emp.deptno=dept.deptno

题目2：查询所有经理的姓名，所在部门名称、月收入及部门所在位置；

SELECT ename,dname,sal,loc
FROM emp INNER JOIN dept ON emp.deptno=dept.deptno
WHERE job='manager'

题目3：查询每个员工的姓名、所在部门名及对应领导的姓名。

SELECT e1.ename 员工姓名,dname 部门名,e2.ename 经理姓名
FROM emp e1 INNER JOIN emp e2 ON 
e1.mgr=e2.empno INNER JOIN dept ON dept.deptno=e1.deptno

题目4：查询每位员工的姓名、编号及工资等级。

SELECT ename,empno,grade
FROM emp,salgrade
WHERE sal BETWEEN salgrade.losal AND salgrade.hisal

练习:
（1）查询部门的名称、所在地及每个部门的人数。

SELECT d.dname,d.loc,COUNT(*)
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY d.dname,d.loc

（2）查询每个部门的部门名称及工作时间最长的员工雇佣时间。

SELECT dname,MIN(hiredate)
FROM emp INNER JOIN dept ON emp.deptno=dept.deptno
GROUP BY dname,loc

（3）查询所有员工及其上层管理者信息,要求查询结果显示员工姓名，
上层领导姓名及所在部门号及部门名,并按照部门号降序排序

SELECT e1.ename 员工姓名,e2.ename 领导姓名, e1.deptno 部门号,dept.dname 部门名
from emp e1 inner join dept on e1.deptno=dept.deptno
inner join emp e2 on e1.deptno=e2.deptno where e1.mgr=e2.empno
order by e1.deptno desc

外连接

准备工作：
首先使用insert语句在emp表中插入新记录（注意没有为deptno列插入值，即它们的值为null）

INSERT  INTO  emp (empno,ename)
VALUES(8888,'Bob')

1.实现emp表和dept表之间通过deptno列进行左外连接。

SELECT * 
from emp LEFT JOIN dept ON emp.deptno=dept.deptno

2.实现emp表和dept表之间通过deptno列进行右外连接。

SELECT * 
from emp RIGHT JOIN dept ON emp.deptno=dept.deptno

3.实现emp表和dept表之间通过deptno列进行完全外连接。

SELECT * FROM 
emp LEFT JOIN dept on emp.deptno=dept.deptno
union
SELECT * FROM 
emp right JOIN dept on emp.deptno=dept.deptno

练习：查询所有部门的名称、所在地及每个部门的人数。

select dname,loc,COUNT(empno) FROM
emp right JOIN dept on emp.deptno=dept.deptno
GROUP BY dname