学完MySql多表查询应付工作中的业务足够了
一、单元概述
通过本章的学习能够了解MySQL数据库中多表查询的含义,掌握多表查询的基本写法,掌握特殊的多表查询的写法,掌握内连接和外连接的区别
二、教学重点与难点
重点:
- 掌握多表查询的基本写法
- 掌握内连接和外连接的区别
难点:
- 外连接的用法和写法
- 特殊的多表查询的写法:自连接、非等值连接等
3.1 多表查询
3.1.1 什么是多表查询
- 从多个表中获取数据
- 思考如下问题?
-
- 写一条查询语句,查询员工姓名、部门名称、工作地点?
- 写一条查询语句,查询员工姓名、部门名称、工作地点?
3.1.2 什么是连接
- 连接是在多个表之间通过一定的连接条件,使表之间发生关联,进而能从多个表之间获取数据。
- 语法为:
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;
- 在 WHERE子句中书写连接条件。
- 如果在多个表中出现相同的列名,则需要使用表名作为来自该表的列名的前缀。
- N个表相连时,至少需要N-1个连接条件
3.1.3 多表连接类型
- 按连接条件分:
-
- 等值连接
- 非等值连接
- 按其他连接方法分
-
- 外连接
- 内连接3.1.4 多表连接写法
- 多表连接包含多种写法,我们主要介绍:
-
- 基本写法:绝大多数符合SQL标准,其它关系型数据也适用。
- ANNSI 99写法:ANSI标准提供的写法,所有关系型数据库必须支持。
3.2 笛卡尔积
3.2.1 笛卡尔积
- 第一个表中的所有行和第二个表中的所有行都发生连接。3.2.2 笛卡尔积产生情况
- 笛卡尔积在下列情况产生:
-
- 连接条件被省略
- 连接条件是无效的
- 为了避免笛卡尔积的产生,通常需要在WHERE子句中包含一个有效的连接条件。3.2.3 笛卡尔积写法
- 笛卡尔积的写法
SELECT emp.empno, emp.ename, emp.deptno, dept.deptno, dept.loc
FROM emp, dept;
- 笛卡尔积结果
3.3 等值连接
3.3.1 什么是等值连接
- 查询所有员工编号,姓名,部门编号,工作地点
SELECT emp.empno, emp.ename, emp.deptno, dept.deptno, dept.loc
FROM emp, dept
WHERE emp.deptno=dept.deptno;
EMPNO | ENAME | DEPTNO | DEPTNO | LOC | |
---|---|---|---|---|---|
7839 | KING | 10 | 10 | NEW YORK | |
7698 | BLAKE | 30 | 30 | CHICAGO | |
7782 | CLARK | 10 | 10 | NEW YORK | |
7566 | JONES | 20 | 20 | DALLAS | |
... |
- 现在只想查询工作地点在NEW YORK的员工编号,姓名,部门编号,工作地点
3.3.2 使用AND运算符增加其它查询条件
SELECT emp.empno, emp.ename, emp.deptno, dept.deptno, dept.loc
FROM emp, dept
WHERE emp.deptno=dept.deptno and loc= ‘NEW YORK’;
EMPNO | ENAME | DEPTNO | DEPTNO | LOC |
---|---|---|---|---|
7839 | KING | 10 | 10 | NEW YORK |
7782 | CLARK | 10 | 10 | NEW YORK |
... |
3.4 限制歧义列名
- 在用到多个表时可以使用表名作前缀来限定列;
- 通过使用表前缀可以提高性能;
- 通过使用列的别名可以区分来自不同表但是名字相同的列;
3.5 使用表的别名
- 通过使用表的别名来简化查询语句
SELECT emp.empno, emp.ename, emp.deptno, dept.deptno, dept.loc
FROM emp, dept
WHERE emp.deptno=dept.deptno;
SELECT e.empno, e.ename, e.deptno, d.deptno, d.loc
FROM emp e, dept d
WHERE e.deptno= d.deptno;
练习
- 写一个查询,显示所有员工姓名,部门编号,部门名称。
- 写一个查询,显示所有工作在CHICAGO并且奖金不为空的员工姓名,工作地点,奖金
- 写一个查询,显示所有姓名中含有A字符的员工姓名,工作地点。
3.6 非等值连
- 查询每个员工的姓名,工资,工资等级
SELECT e.ename, e.sal, s.grade
FROM emp e, salgrade s
WHERE e.sal
BETWEEN s.losal AND s.hisal;
ENAME | SAL | GRADE |
---|---|---|
JAMES | 950 | 1 |
SMITH | 800 | 1 |
ADAMS | 1100 | 1 |
... |
3.7 多于两个表的连接
- 约定:1个客户可以有多个订单,1个订单可以包含多个商品。
- 查询每个顾客都订购了哪些商品?
SELECT c.name,o.itemid
FROM customer c, order o,item i
WHERE c.custid = o.custid and o.ordid = i.ordid;
- 多个表连接和两个表连接一样,在构造SQL语句时,需要多考虑一个表之间的关联条件。
3.8 多表连接的写法分析
- 分析要查询的列都来自于哪些表,构成FROM子句;
- 分析这些表之间的关联关系,如果表之间没有直接关联关系,而是通过另一个中间表关联,则也要在FROM子句中补充中间关联表;
- 接下来在WHERE子句中补充表之间的关联关系,通常N个表,至少要有N-1个关联关系;
- 分析是否还有其它限制条件,补充到WHERE子句的表关联关系之后,作为限制条件;
- 根据用户想要显示的信息,补充SELECT子句。
- 分析是否有排序要求,如果排序要求中还涉及到其它表,则也要进行第2步补充排序字段所在的表,并且添加表之间的关联关系;
练习
- 查询每个员工的编号,姓名,工资,工资等级,所在工作城市,按照工资等级进行升序排序。
3.9 自连接
- 思考:查询每个员工的姓名和直接上级姓名?
- 自身连接,也叫自连接,是一个表通过某种条件和本身进行连接的一种方式,就如同多个表连接一样。
SELECT worker.ename ‘WNAME’,manager.ename ‘LNAME’
FROM emp worker, emp manager
WHERE worker.mgr = manager.empno;
WNAME | LNAME |
---|---|
SMITH | FORD |
ALLEN | BLACK |
WARD | BLACK |
... |
练习
- 查询所有工作在NEW YORK和CHICAGO的员工姓名,员工编号,以及他们的经理姓名,经理编号。
3.10 ANSI SQL:标准的连接语法
3.10.1 ANSI SQL:1999标准的连接语法
- 除了上述自己的连接语法外,同时支持美国国家标准协会(ANSI)的SQL:1999标准的连接语法。
SELECT table1.column, table2.column
FROM table1
[JOIN table2
ON(table1.column_name = table2.column_name)] |
[LEFT | RIGHT OUTER JOIN table2
ON (table1.column_name = table2.column_name)];
3.10.2 外部连接
- 在多表连接时,可以使用外部连接来查看哪些行,按照连接条件没有被匹配上。
-
- 左外连接以FROM子句中的左边表为基表,该表所有行数据按照连接条件无论是否与右边表能匹配上,都会被显示出来。
- 右外连接以FROM子句中的右边表为基表,该表所有行数据按照连接条件无论是否与左边表能匹配上,都会被显示出来
3.10.3 左外连接写法
- 查询所有雇员姓名,部门编号,部门名称,包括没有部门的员工也要显示出来
SELECT e.ename,e.deptno,d.loc
FROM emp e
LEFT OUTER JOIN dept d
ON (e.deptno = d.deptno);
ENAME | DEPTNO | LOC |
---|---|---|
MILLER | 10 | NEW YORK |
KING | 10 | NEW YORK |
CLARK | 10 | NEW YORK |
FORD | 20 | DALLAS |
... |
3.10.4 右外连接写法
- 查询所有雇员姓名,部门编号,部门名称,包括没有员工的部门也要显示出来
SELECT e.ename,e.deptno,d.loc
FROM emp e
RIGHT OUTER JOIN dept d
ON (e.deptno = d.deptno);
ENAME | DEPTNO | LOC |
---|---|---|
SMITH | 20 | DALLAS |
ALLEN | 30 | CHICAGO |
JONES | 20 | DALLAS |
MARTIN | 30 | CHICAGO |
.... |
练习 使用SQL-99写法,完成如下练习
- 创建一个员工表和部门表的交叉连接。
- 使用自然连接,显示入职日期在80年5月1日之后的员工姓名,部门名称,入职日期
- 使用左连接,查询每个员工的姓名,经理姓名,没有经理的King也要显示出来。
- 使用右连接,查询每个员工的姓名,经理姓名,没有经理的King也要显示出来。
3.11 本章小结
- 等值连接
- 不等值连接
- 自身连接
- 外连接
3.12 课后作业
- 显示员工SMITH的姓名,部门名称,直接上级名称
- 显示员工姓名,部门名称,工资,工资级别,要求工资级别大于4级。
- 显示员工KING和FORD管理的员工姓名及其经理姓名。
- 显示员工姓名,参加工作时间,经理名,参加工作时间,要求参加时间比经理早。