MySQL复合查询与内外连接

1笛卡尔积1什么是笛卡尔积笛卡尔积就是两张表所有记录的所有可能组合。举个最简单的例子表 A 有 2 条记录[苹果香蕉]表 B 有 3 条记录[红色黄色绿色]它们的笛卡尔积就是 2×36 条记录(苹果红色), (苹果黄色), (苹果绿色),(香蕉红色), (香蕉黄色), (香蕉绿色)在 MySQL 中当你直接查询两张表不加任何条件时得到的就是笛卡尔积。2下面所有实验的基础准备表-- 创建部门表DEPT CREATE TABLE DEPT ( DEPTNO INT PRIMARY KEY, -- 部门编号 DNAME VARCHAR(14), -- 部门名称 LOC VARCHAR(13) -- 部门位置 ); -- 创建员工表EMP CREATE TABLE EMP ( EMPNO INT PRIMARY KEY, -- 员工编号 ENAME VARCHAR(10), -- 员工姓名 JOB VARCHAR(9), -- 工作岗位 MGR INT, -- 上级领导编号 HIREDATE DATE, -- 入职日期 SAL DECIMAL(7,2), -- 工资 COMM DECIMAL(7,2), -- 奖金 DEPTNO INT, -- 部门编号 FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO) ); -- 创建工资等级表SALGRADE CREATE TABLE SALGRADE ( GRADE INT, -- 工资等级 LOSAL INT, -- 该等级最低工资 HISAL INT -- 该等级最高工资 ); -- 插入部门数据 INSERT INTO DEPT VALUES (10, ACCOUNTING, NEW YORK), (20, RESEARCH, DALLAS), (30, SALES, CHICAGO), (40, OPERATIONS, BOSTON); -- 插入员工数据 INSERT INTO EMP VALUES (7369, SMITH, CLERK, 7902, 1980-12-17, 800, NULL, 20), (7499, ALLEN, SALESMAN, 7698, 1981-02-20, 1600, 300, 30), (7521, WARD, SALESMAN, 7698, 1981-02-22, 1250, 500, 30), (7566, JONES, MANAGER, 7839, 1981-04-02, 2975, NULL, 20), (7654, MARTIN, SALESMAN, 7698, 1981-09-28, 1250, 1400, 30), (7698, BLAKE, MANAGER, 7839, 1981-05-01, 2850, NULL, 30), (7782, CLARK, MANAGER, 7839, 1981-06-09, 2450, NULL, 10), (7788, SCOTT, ANALYST, 7566, 1987-04-19, 3000, NULL, 20), (7839, KING, PRESIDENT, NULL, 1981-11-17, 5000, NULL, 10), (7844, TURNER, SALESMAN, 7698, 1981-09-08, 1500, 0, 30), (7876, ADAMS, CLERK, 7788, 1987-05-23, 1100, NULL, 20), (7900, JAMES, CLERK, 7698, 1981-12-03, 950, NULL, 30), (7902, FORD, ANALYST, 7566, 1981-12-03, 3000, NULL, 20), (7934, MILLER, CLERK, 7782, 1982-01-23, 1300, NULL, 10); -- 插入工资等级数据 INSERT INTO SALGRADE VALUES (1, 700, 1200), (2, 1201, 1400), (3, 1401, 2000), (4, 2001, 3000), (5, 3001, 9999);3看到笛卡尔积的产生-- 第一步先看一下DEPT表有多少条记录 SELECT COUNT(*) FROM DEPT; -- 结果4条记录 -- 第二步看一下EMP表有多少条记录 SELECT COUNT(*) FROM EMP; -- 结果14条记录 -- 第三步直接查询两张表不加任何条件产生笛卡尔积 -- 注意这会返回4×1456条记录 SELECT * FROM EMP, DEPT; -- 只看前10条感受一下结果是什么样的 SELECT EMP.ename, DEPT.dname FROM EMP, DEPT LIMIT 10;员工 SMITH 实际上只属于 RESEARCH 部门20 号但笛卡尔积把 SMITH 和所有 4 个部门都组合了一遍这显然是错误的、无意义的数据笛卡尔积本身是数学概念但在数据库中几乎没有直接使用价值多表查询的本质就是先产生笛卡尔积 → 再用连接条件过滤掉错误的组合忘记加连接条件是初学者最常见的错误会导致结果集爆炸式增长2多表查询基础内连接的传统写法1连接条件连接条件就是用来从笛卡尔积中筛选出正确关联记录的条件。对于员工表和部门表正确的连接条件是EMP.deptno DEPT.deptno意思是只保留员工的部门编号和部门的部门编号相等的记录这样就能得到每个员工真正所属的部门信息2正确的多表查询关联员工和部门-- 写法1完整写法指定表名.字段名 -- 原理先产生56条笛卡尔积再筛选出deptno相等的14条正确记录 SELECT EMP.ename, EMP.sal, DEPT.dname FROM EMP, DEPT WHERE EMP.deptno DEPT.deptno; -- 写法2给表起别名简化代码推荐 -- e是EMP的别名d是DEPT的别名 SELECT e.ename, e.sal, d.dname FROM EMP e, DEPT d WHERE e.deptno d.deptno; -- 增加额外的筛选条件只显示10号部门的员工 SELECT e.ename, e.sal, d.dname FROM EMP e, DEPT d WHERE e.deptno d.deptno -- 连接条件必须写 AND d.deptno 10; -- 额外筛选条件可选 -- 关联三张表显示员工姓名、工资、部门名称和工资等级 SELECT e.ename, e.sal, d.dname, s.grade FROM EMP e, DEPT d, SALGRADE s WHERE e.deptno d.deptno -- 员工和部门的连接条件 AND e.sal BETWEEN s.losal AND s.hisal; -- 员工和工资等级的连接条件第一个查询返回 14 条记录正好是员工表的记录数每个员工都只和自己真正所属的部门关联关联三张表时需要写两个连接条件N 张表需要 N-1 个连接条件3常见错误-- 错误1忘记写连接条件产生56条笛卡尔积 SELECT e.ename, d.dname FROM EMP e, DEPT d; -- 错误2连接条件写错比如写成e.deptno d.loc SELECT e.ename, d.dname FROM EMP e, DEPT d WHERE e.deptno d.loc; -- 错误3字段名冲突没有指定表名 -- 错误原因deptno字段在两张表中都存在MySQL不知道用哪个 SELECT ename, sal, deptno, dname FROM EMP e, DEPT d WHERE e.deptno d.deptno; -- 正确写法指定表名 SELECT e.ename, e.sal, e.deptno, d.dname FROM EMP e, DEPT d WHERE e.deptno d.deptno;3自连接查询同一张表自己连自己1自连接自连接就是把同一张表当作两张不同的表来进行连接查询。使用场景当表内的数据存在层级关系或父子关系时员工表员工和领导都是员工领导的 empno 是员工的 mgr分类表商品分类和子分类地区表省份和城市2查询员工的上级领导-- 先看一下员工表的结构理解mgr字段的含义 -- mgr是员工的上级领导的员工编号(empno) SELECT empno, ename, mgr FROM EMP; -- 需求查询员工FORD的上级领导的编号和姓名 -- 方法一使用子查询分步思考 -- 第一步先查FORD的mgr是多少 SELECT mgr FROM EMP WHERE ename FORD; -- 结果7566 -- 第二步再查empno7566的员工是谁 SELECT empno, ename FROM EMP WHERE empno 7566; -- 结果JONES -- 合并成一个子查询 SELECT empno, ename FROM EMP WHERE empno (SELECT mgr FROM EMP WHERE ename FORD); -- 方法二使用自连接推荐性能更好 -- 给同一张表起两个不同的别名 -- worker代表普通员工表 -- leader代表领导表 SELECT leader.empno, leader.ename FROM EMP worker, EMP leader WHERE worker.mgr leader.empno -- 连接条件员工的mgr 领导的empno AND worker.ename FORD; -- 筛选条件找FORD的领导 -- 拓展查询所有员工的姓名和他们领导的姓名 -- 注意KING没有领导所以不会出现在结果中 SELECT worker.ename AS 员工姓名, leader.ename AS 领导姓名 FROM EMP worker, EMP leader WHERE worker.mgr leader.empno;两种方法都能得到正确结果FORD 的领导是 JONES编号 7566自连接的性能通常比子查询好特别是当数据量较大时自连接的关键是给表起两个有意义的别名把一张表当成两张表用4子查询嵌套查询、1子查询子查询就是嵌入在其他 SQL 语句中的 SELECT 语句也叫嵌套查询。子查询可以出现在WHERE 子句中最常用FROM 子句中当作临时表SELECT 子句中较少用根据返回结果的不同子查询分为单行子查询返回一行一列数据多行子查询返回多行一列数据多列子查询返回一行或多行多列数据2单行子查询-- 单行子查询子查询只返回一行一列数据 -- 可以使用单行比较运算符、、、、、 -- 需求1显示工资最高的员工的名字和工作岗位 -- 第一步先查最高工资是多少 SELECT MAX(sal) FROM EMP; -- 结果5000 -- 第二步再查工资等于5000的员工 SELECT ename, job FROM EMP WHERE sal 5000; -- 合并成子查询 SELECT ename, job FROM EMP WHERE sal (SELECT MAX(sal) FROM EMP); -- 需求2显示工资高于平均工资的员工信息 SELECT ename, sal FROM EMP WHERE sal (SELECT AVG(sal) FROM EMP); -- 需求3显示和SMITH同一部门的员工 SELECT * FROM EMP WHERE deptno (SELECT deptno FROM EMP WHERE ename SMITH);常见错误-- 错误单行子查询返回了多行数据 -- 错误原因如果有多个员工叫SMITH子查询会返回多行 -- 此时不能使用应该使用IN SELECT * FROM EMP WHERE deptno (SELECT deptno FROM EMP WHERE ename SMITH);3多行子查询掌握多行子查询的语法学会使用 IN、ALL、ANY 三个关键字-- 多行子查询子查询返回多行一列数据 -- 必须使用多行比较运算符IN、ALL、ANY -- 1. IN关键字判断值是否在子查询的结果集中 -- 需求查询和10号部门的工作岗位相同的雇员不包含10号部门自己 -- 第一步先查10号部门有哪些岗位 SELECT DISTINCT job FROM EMP WHERE deptno 10; -- 结果CLERK、MANAGER、PRESIDENT -- 第二步再查其他部门中岗位在这个集合中的员工 SELECT ename, job, sal, deptno FROM EMP WHERE job IN (CLERK, MANAGER, PRESIDENT) AND deptno 10; -- 合并成子查询 SELECT ename, job, sal, deptno FROM EMP WHERE job IN (SELECT DISTINCT job FROM EMP WHERE deptno 10) AND deptno 10; -- 2. ALL关键字和子查询结果集中的所有值比较 -- 需求显示工资比部门30的所有员工的工资都高的员工 -- 部门30的最高工资是2850所以只要工资2850就满足条件 SELECT ename, sal, deptno FROM EMP WHERE sal ALL(SELECT sal FROM EMP WHERE deptno 30); -- 等价于 SELECT ename, sal, deptno FROM EMP WHERE sal (SELECT MAX(sal) FROM EMP WHERE deptno 30); -- 3. ANY关键字和子查询结果集中的任意一个值比较 -- 需求显示工资比部门30的任意员工的工资高的员工 -- 部门30的最低工资是950所以只要工资950就满足条件 SELECT ename, sal, deptno FROM EMP WHERE sal ANY(SELECT sal FROM EMP WHERE deptno 30); -- 等价于 SELECT ename, sal, deptno FROM EMP WHERE sal (SELECT MIN(sal) FROM EMP WHERE deptno 30);关键字含义等价于IN等于结果集中的任意一个 ANYALL大于结果集中的所有值 MAX()ALL小于结果集中的所有值 MIN()ANY大于结果集中的任意一个 MIN()ANY小于结果集中的任意一个 MAX()4多列子查询理解多列子查询的概念掌握多列子查询的语法-- 多列子查询子查询返回多个列的数据 -- 需求查询和SMITH的部门和岗位完全相同的所有雇员不含SMITH本人 -- 第一步先查SMITH的部门和岗位 SELECT deptno, job FROM EMP WHERE ename SMITH; -- 结果(20, CLERK) -- 第二步再查部门20且岗位CLERK的员工 SELECT ename FROM EMP WHERE deptno 20 AND job CLERK AND ename SMITH; -- 合并成多列子查询更简洁 SELECT ename FROM EMP WHERE (deptno, job) (SELECT deptno, job FROM EMP WHERE ename SMITH) AND ename SMITH;5在from字句中使用子查询掌握将子查询当作临时表使用的技巧解决需要先统计再查询的复杂问题-- 在FROM子句中使用子查询把子查询的结果当作一张临时表 -- 这是非常强大的技巧能解决很多复杂问题 -- 需求1显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资 -- 分析 -- 1. 首先需要统计每个部门的平均工资 -- 2. 然后把这个统计结果当作临时表和员工表关联 -- 3. 最后筛选出工资高于部门平均工资的员工 -- 第一步统计每个部门的平均工资 SELECT AVG(sal) AS avg_sal, deptno FROM EMP GROUP BY deptno; -- 第二步把上面的结果当作临时表tmp和员工表关联 SELECT e.ename, e.deptno, e.sal, FORMAT(tmp.avg_sal, 2) AS 部门平均工资 FROM EMP e, (SELECT AVG(sal) AS avg_sal, deptno FROM EMP GROUP BY deptno) tmp WHERE e.deptno tmp.deptno -- 连接员工表和临时表 AND e.sal tmp.avg_sal; -- 筛选工资高于平均的员工 -- 需求2查找每个部门工资最高的人的姓名、工资、部门、最高工资 SELECT e.ename, e.sal, e.deptno, tmp.max_sal FROM EMP e, (SELECT MAX(sal) AS max_sal, deptno FROM EMP GROUP BY deptno) tmp WHERE e.deptno tmp.deptno AND e.sal tmp.max_sal;子查询必须给一个别名上面的 tmp子查询中的字段也要给别名方便外面引用这种方法可以把复杂问题分解成多个简单步骤5合并查询1合并查询合并查询就是把多个 SELECT 语句的结果集合并成一个结果集。使用场景当需要从多个表中查询相似结构的数据时当一个复杂查询可以拆分成多个简单查询时2UNION和UNION ALL的区别掌握合并查询的语法理解 UNION 和 UNION ALL 的区别知道什么时候用哪个-- 合并查询的要求 -- 1. 多个SELECT语句的列数必须相同 -- 2. 对应列的数据类型必须兼容 -- 需求将工资大于2500或职位是MANAGER的人找出来 -- 方法一用OR条件简单情况 SELECT ename, sal, job FROM EMP WHERE sal 2500 OR job MANAGER; -- 方法二用UNION自动去重 -- 先查工资大于2500的员工 SELECT ename, sal, job FROM EMP WHERE sal 2500 UNION -- 再查职位是MANAGER的员工 SELECT ename, sal, job FROM EMP WHERE job MANAGER; -- 方法三用UNION ALL不去重 SELECT ename, sal, job FROM EMP WHERE sal 2500 UNION ALL SELECT ename, sal, job FROM EMP WHERE job MANAGER;UNION 结果6 条记录JONES 和 BLAKE 既是 MANAGER工资又大于 2500只出现一次UNION ALL 结果8 条记录JONES 和 BLAKE 出现了两次UNION自动去掉结果集中的重复行会对结果进行排序性能较差UNION ALL不去掉重复行直接合并性能好很多最佳实践如果确定两个结果集没有重复数据优先使用 UNION ALL6内外连接详解标准 JOIN 语法我们之前学的多表查询是传统写法用逗号分隔表WHERE 写连接条件这其实是内连接的一种写法。SQL 标准提供了更清晰、更强大的 JOIN 语法INNER JOIN内连接和传统写法等价LEFT JOIN左外连接RIGHT JOIN右外连接标准 JOIN 语法的优点连接条件和筛选条件分离可读性更好支持外连接传统写法不支持是 SQL 标准兼容性更好1内连接INNER JOIN-- 标准内连接语法 -- SELECT 字段 FROM 表1 INNER JOIN 表2 ON 连接条件 WHERE 筛选条件; -- 需求显示SMITH的名字和部门名称 -- 传统写法 SELECT e.ename, d.dname FROM EMP e, DEPT d WHERE e.deptno d.deptno AND e.ename SMITH; -- 标准内连接写法推荐 SELECT e.ename, d.dname FROM EMP e INNER JOIN DEPT d ON e.deptno d.deptno -- 连接条件写在ON后面 WHERE e.ename SMITH; -- 筛选条件写在WHERE后面 -- 关联三张表的标准写法 SELECT e.ename, e.sal, d.dname, s.grade FROM EMP e INNER JOIN DEPT d ON e.deptno d.deptno INNER JOIN SALGRADE s ON e.sal BETWEEN s.losal AND s.hisal;传统写法连接条件和筛选条件都写在 WHERE 后面容易混淆标准写法连接条件写在 ON 后面筛选条件写在 WHERE 后面逻辑更清晰2左外连接左外连接左边的表完全显示右边的表只显示匹配的记录匹配不到显示 NULL-- 先创建两张简单的测试表方便理解 CREATE TABLE stu (id INT, name VARCHAR(30)); -- 学生表 INSERT INTO stu VALUES(1, jack), (2, tom), (3, kity), (4, nono); CREATE TABLE exam (id INT, grade INT); -- 成绩表 INSERT INTO exam VALUES(1, 56), (2, 76), (11, 8); -- 先看一下内连接的结果 SELECT * FROM stu INNER JOIN exam ON stu.id exam.id; -- 结果只有jack和tom有成绩kity和nono没有成绩所以不显示 -- 左外连接左边的stu表完全显示 SELECT * FROM stu LEFT JOIN exam ON stu.id exam.id;内连接只返回两张表都匹配的记录左外连接返回左表的所有记录即使右表没有匹配3左外连接和右外连接的转换-- 左外连接 SELECT d.dname, e.* FROM DEPT d LEFT JOIN EMP e ON d.deptno e.deptno; -- 等价的右外连接交换表的位置 SELECT d.dname, e.* FROM EMP e RIGHT JOIN DEPT d ON d.deptno e.deptno;