9. 多表查询
9.1 表与表之间的关系
- 实际开发中，一个项目通常需要很多张表才能完成。例如：一个商城项目就需要分类表(category)、 商品表(products)、订单表(orders)等多张表。
-
一对一的关系
- 一对一的关系很少见，如一个身份证号对应一个人，这种关系不需要多表，一张表就可以了。
-
一对多的关系
- 一对多的关系比较常中见，如一个班级有很多学习，一个部分有很多员工。
- 这种情况下，一般会建立两张表，表之间使用一个公共子段来连接。
-
多对多的关系
- 多对多的关系也行常见，如多个老师对多个学生。学生和课程也是多个学生对多个课程。
- 这种情况下，一般会两两张表之间建立一个中间表，中间表与两张这间采用一对多的关系来建立连接。
多表连接不宜过多， 如果表中数据比较多，可以尽量避免3张以上的多表连接。
9.2 多表连接与笛卡尔积
- 不同的数据放入不同的表中，在查询的时候，数据可能来自多张表，这就是多表连接。
- 语法： select 表1.字段, 表2.字段 .. from 表1， 表2
-- 查询员工信息 (first_name 和 部门ID 和 部门名称)
SQL> SELECT s_emp.first_name, s_emp.dept_id, s_dept.name AS dept_name FROM s_emp, s_dept;
-
在这个例子中，我们查询出来的记录条数比表中的记录还，这是因为两张表之间查询记录时逐条匹配，形成了笛卡尔积。
-
在多表查询中一定要消除笛卡尔积。
-
使用连接条件可以避免笛卡尔积
- 语法： select 表1.字段, 表2.字段 .. from 表1， 表2 连接条件。
9.3 内连接
9.3.1 隐式内连接
- 使用 where 连接条件来连接多个表，这种方式就是隐式内连接
- 语法: select 表1.字段1, 表1.字段2, ... 表n.字段n from 表1, ..., 表n where 连接条件;
-
如果查询的表的字段
没有重复，表名.可以省略 -
为了简化SQL结构语句，可以使用别名
- 表一旦使用了别名，在语句中只能使用别名，不能再使用原来的名称。
- 注意： 表的别名使用空格，ORACLE中不能使用as关键字。
-
多表连接必须要有连接条件 ， N个表，必须要有N-1个连接条件。
- select 表1别名.字段1, 表1别名.字段2, ... 表n别名.字段n from 表1 表1别名, ..., 表n 表n别名 where 连接条件;
-- 查询员工信息 (first_name 和 部门ID 和 部门名称)
-- mysql和oracle都可以
SQL> SELECT e.first_name, d.id, d.name FROM s_emp e, s_dept d
WHERE e.dept_id = d.id;
-- MYSQL
SQL> SELECT e.first_name, d.id, d.name FROM s_emp AS e, s_dept AS d
WHERE e.dept_id = d.id;
9.3.2 显式内连接
-
使用
from a表 [inner] join b表 on 连接条件- inner可以省略
-- 把每个部门名和对应的地区名显示出来
SQL> SELECT d.id, r.name FROM s_dept d INNER JOIN s_region r ON d.region_id = r.id;
9.3.3 练习
- 使用两种内连接方式查询如下信息： 查询员工表、部门表、区域名(region)取出 last_name, 部门名称、区域名称
-- 查询员工表、部门表、区域名(region)取出 last_name, 部门名称、区域名称
SQL> SELECT last_name, d.name dept_name , r.name region_name
FROM s_emp e, s_dept d, s_region r
WHERE e.dept_id = d.id AND d.region_id = r.id;
-- 显式内链接
SQL> SELECT last_name, d.name dept_name , r.name region_name
FROM s_emp e JOIN s_dept d JOIN s_region r
ON e.dept_id = d.id AND d.region_id = r.id;
9.4 自连接
9.4 自连接
-
内连接有三种方式
-
等值连接 - 用等号做连接条件
-
非等值连接 - 不用等号做连接条件，比如用大于号等。
- 便如查询在某个范围内的值
-
自连接 - 本表之间的连接（就一张表）
-
- 自连接是逻辑上把一张表当成两张表来使用。
- 自连接时，表名必须使用别名。
-- 练习：找出领导
--找出员工的id, first_name, manager_id
SQL> SELECT id, first_name, manager_id FROM s_emp;
-- 列出员工与领导的信息
SQL> SELECT e.id, e.first_name, m.id, m.first_name FROM
s_emp e, s_emp m
WHERE e.manager_id = m.id;
-- 保留领导的信息，并去除重复的
SQL> SELECT DISTINCT m.id, m.first_name FROM
s_emp e, s_emp m
WHERE e.manager_id = m.id;
自连接是一个比较难理解的点，但是我们用后面学习的
子查询一样可以达到这种效果。
