Mysql学习笔记:查询进阶(一)多表查询

select * from emp, dept

SELECT * FROM dept, emp        WHERE emp.deptno = dept.deptno

-- 如何显示部门号为 10 的部门名、员工名和工资SELECT ename,sal,dname,emp.deptno FROM dept, emp        WHERE emp.deptno = dept.deptno AND emp.deptno =10

-- 显示各个员工的姓名，工资，及其工资的级别-- 方法一： between and 引导SELECT *         FROM emp, salgrade        WHERE sal BETWEEN losal AND hisal-- 方法二：SELECT *         FROM emp, salgrade        WHERE losal < sal AND sal < hisal

SELECT*FROM emp, emp

SELECT *         FROM emp AS worker, emp AS boss

-- 思考题：显示公司员工和他的上级的名字SELECT worker.ename AS '员工名', boss.ename AS '上级名'        FROM emp AS worker, emp AS boss        WHERE worker.mgr = boss.empno

SELECT deptno        FROM emp        WHERE ename = 'SMITH'

SELECT *        FROM emp        WHERE deptno = 20

SELECT *        FROM emp        WHERE deptno = (        SELECT deptno        FROM emp        WHERE ename = 'SMITH'	)

多行子查询，就是子查询会返回「多行、但只有一列」的结果，相当于得到一个值列表，主查询不能用 => 直接比较，必须搭配 IN / ANY / ALL 来使用。

• 如何查询和部门10的员工工作相同的雇员的

• 名字、岗位、工资、部门号，但是不含10号部门自己的员工信息

SELECT ename, job, sal, deptno    FROM emp    WHERE job IN (        SELECT DISTINCT job        FROM emp        WHERE deptno = 10    ) AND deptno != 10

• 注意我们在提到工作与10部门员工工作相同时，我们所用的判断语句是IN，而不是 =，还有后面的不等于号也是要注意

子查询当作临时表，专业叫法是表子查询 / 派生表，核心就一句话：把一个查询的结果，当成一张临时存在的小表，放在 FROM 后面，再对这张 “临时表” 做二次查询。

具体任务：查询emp表中各部门中，工资最高的员工。思路分解：

• 1.先找出每个部门中的最高工资

SELECT deptno, MAX(sal)FROM empGROUP BY deptno

• 2.然后根据上面得到的表的数据，通过匹配部门与最高工资来定位最高工资的员工。

• 3.分析上面思路，好像只能通过联合两张表来完成，用到了多表查询。具体实现， max + group by deptno，把子查询当做临时表

SELECT enameFROM (SELECT deptno, MAX(sal) AS hisalFROM empGROUP BY deptno	) AS temp_tab, empWHERE emp.deptno=temp_tab.deptno 	        AND emp.sal = temp_tab.hisal

SELECT deptno, ename,MAX(sal)FROM empGROUP BY deptno

GROUP BY 的作用是按指定列分组，对每组数据做聚合计算（比如求最大值、总和、平均值）。标准 SQL 强制规定：SELECT 后面的列，只能是两种：

• 一是GROUP BY 后面的分组列（这里是 deptno）
• 二是用聚合函数包裹的列（这里是 MAX(sal)）

而 ename 既不是分组列，也没有用聚合函数 → 违反 SQL 标准！学废了

在多行子查询中使用all操作符。思考1：显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

-- 方法一，使用all，就是比其中所有的都比较SELECT ename,sal,deptnoFROM empWHERE sal> ALL(     SELECT sal FROM emp WHERE deptno = 30	)

-- 方法二：使用max,只要比其中的最大值高就可以SELECT ename,sal,deptnoFROM empWHERE sal> (SELECT max(sal) FROM emp WHERE deptno = 30	)

思考2：显示工资比部门30中任何一个员工的工资都高的员工的姓名、工资和部门号

-- 方法一，使用ANY，就是比其中任意一个进行比较SELECT ename,sal,deptnoFROM empWHERE sal> ANY(SELECT sal FROM emp WHERE deptno = 30	)

-- 方法二，使用min，就是和其中最小的那个进行比较SELECT ename,sal,deptnoFROM empWHERE sal> (SELECT min(sal) FROM emp WHERE deptno = 30	)