数据库开发（四）多表查询

 

 

查询：

SELECT 列
FROM 表
WHERE
ORDER BY;

---

多表查询：

SELECT 表名.字段，表名.字段...
FROM 表1,表2，...,表n;

部门表：id，name，region_id   （12个部门）

区域表：id，name  (5个区域）

需求：查询每个部门名，以及对应的区域名

套用一个表查询语法：

--下列sql语句会查出60条结果
SELECT s_dept.id,s_dept.name,region_id,s_region.id,s_region.name
FROM s_dept, s_region;


--筛选出正确的12行
SELECT s_dept.id,s_dept.name,region_id,region.id,region.name
FROM s_dept, s_region
WHERE region_id=s_region.id;

第一个表的每一行都会和第二个表的每一行连接，所以结果一部分正确，一部分无意义

如何找到正确的：引用的列和被应引用的列值相等即是正确的

 

---

多表联查

语法：

SELECT字段
FROM 表1,表2，...,表n
WHERE 条件（拼接条件（必须），其他条件）
ORDER BY;

查询时没有加任何条件的结果就是笛卡尔积

笛卡尔积：

一张表的笛卡尔积是它本身

两张表的笛卡尔积的结果是：m*n行，（行数相乘，列数相加）

三张表的笛卡尔积的结果是：m*n*k行

多表联查：避免笛卡尔积

注意：

1. 执行流程：FROM，笛卡尔积的结果->WHERE筛选出主外键相等的->SELECT筛选出列->ORDER BY，排序（如有需要）
2. 多表查询时，取主外键值相等的记录才是正确的
   1. 拼接条件个数：表的个数减一
3. 可以给表取别名，不能三心二意（一旦起别名，就只能使用别名）
4. 在多表联查时，尽量给每一个列都加表前缀

eg1：查询出每个员工的编号，名字，对应的部门名

SELECT s_emp.id,first_name,name
FROM s_emp,s_dept
WHERE dept_id=s_dept.id;

SELECT e.id,e.first_name, d.name
FROM s_emp e,s_dept d
WHERE e.dept_id=d.id;

eg2：求薪资高于1000的员工的编号，名字，薪资，部门名

SELECT e.id,e.first_name,e.salary,d.name
FROM s_emp e,s_dept d
WHERE e.dept_id=d.id
AND e.salary>1000;

eg3：求客户的名字，及其对应的销售的名字

SELECT c.name,e.first_name
FROM s_customer c, s_emp e
WHERE c.sales_rep_id=e.id;

eg4：查询每个员工的名字，对应的部门名，对应的区域名

SELECT e.first_name,d.name AS dname,r.name AS rname
FROM s_emp e,s_dept d,s_region r
WHERE e.dept_id=d.id 
AND d.region_id=r.id;

eg5：查询3号区域所有的员工编号，员工名，区域名

--员工表与区域表无直接关系，通过部门表连接
SELECT e.id, e.first_name,r.name
FROM s_emp e,s_dept d,s_region r
WHERE e.dept_id=d.id AND d.region_id=r.id
AND r.id=3
ORDER BY e.first_name ASC;

多表连接：

CREATE TABLE s_salgrade(
  losal number(4),
  hisal number(4),
  grade char(1)  
);

INSERT INTO s_salgrade VALUES(0,999,'D');
INSERT INTO s_salgrade VALUES(1000,1499,'C');
INSERT INTO s_salgrade VALUES(1500,1999,'B');
INSERT INTO s_salgrade VALUES(2000,2500,'A');
COMMIT;

• 等价连接，连接条件是=，常用的方式
• 不等价连接，连接条件不是=
  • eg6：查询每个员工的id，名字，薪资，薪资等级

    SELECT e.id, e.first_name,e.salary,g.grade
    FROM s_emp e,s_salgrade g
    WHERE e.salary BETWEEN g.losal AND g.hisal;

 

• 外连接：基于等价连接
  • 左外连接：结果包含，等价连接的结果和左表不匹配的数据（如考生和成绩，若有考生无成绩，也要查出来）
    • 语法：Oracle中独有特色语法，接近等价连接
    • 特色语法：

      SELECT 字段
      FROM  表1，表2
      WHERE  表1.列=表2.列(+);

      • 注：WHERE中=号左边的表称为左表，=右边的表称为右表
    • eg7：查询“每个”客户的名字，及其对应的销售的名字，左外连接（有客户无销售也查出来）

      SELECT c.name,e.first_name
      FROM s_customer c, s_emp e
      WHERE c.sales_rep_id=e.id(+);

    • 标准语法：

      SELECT 字段
      FROM  左表 LEFT OUTER JOIN 右表   --OUTER可省略
      ON 连接条件
      WHERE 其它筛选条件;

  • 右外连接：结果包含，等价连接的结果和右表不匹配的数据
    • eg7.1：查询“每个”客户的名字，及其对应的销售的名字，右外连接（有客户无销售也查出来）

      SELECT c.name,e.first_name
      FROM s_customer c, s_emp e
      WHERE e.id(+)=c.sales_rep_id;

    • 标准语法：

      SELECT 字段
      FROM  左表 RIGHT OUTER JOIN 右表   --OUTER可省略
      ON 连接条件
      WHERE 其它筛选条件;

  • 全外连接（全连接）：结果包含，等价连接的结果和左表，右表不匹配的数据
    • 语法（标准语法，并非Oracle特有）：

      SELECT 字段
      FROM  左表 FULL OUTER JOIN 右表   --OUTER可省略
      ON 连接条件
      WHERE 其它筛选条件;

    • eg7.2：查询客户的名字，及其对应的销售的名字，全连接(有客户无销售和有销售无客户也查出来）

      SELECT c.name,e.first_name
      FROM s_customer c FULL OUTER JOIN s_emp e
      WHERE e.id=c.sales_rep_id;

• 内连接：结果与等价连接一样
  • 语法：

    SELECT 字段
    FROM  左表 INNER JOIN 右表   --INNER可省略
    ON 连接条件
    WHERE 其它筛选条件;

  • 查询客户的名字，及其对应的销售的名字（结果与eg3相同）

    SELECT c.name,e.first_name
    FROM s_customer c INNER JOIN s_emp e
    WHERE e.id=c.sales_rep_id;

• 自连接：自己跟自己连接
  • 如果外键引用了自己表的主键列或唯一列，此时可能用到自连接
  • eg：查询每个员工的名字，及其领导的名字

    SELECT e1.first_name,e2.first_name AS manager_name
    FROM s_emp e1,s_emp e2
    WHERE e1.manager_id=e2.id;
    --查不到无领导员工（如老板），可用外连接，如下：
    --WHERE e1.manager_id=e2.id(+); 
    

  • 查询薪资高于其领导的员工名，员工薪资，对应的领导名，领导薪资

    SELECT e1.first_name,e1.salary,e2.first_name AS manager_name,e2.salary AS M_SALARY
    FROM s_emp e1,s_emp e2
    WHERE e1.manager_id=e2.id
    AND e1.salary>e2.salary;
    

集合运算符，重点：rownum

• union：并集
• union all：并
• intersect：交集
• minus：减（差集）

 

SELECT id,first_name
FROM s_emp
WHERE id<15;

SELECT id,first_name
FROM s_emp
WHERE id>5;

UNION：并集，包含两个结果集全部的结果，重复的只保留一份，会按照结果集第一列升序排序

--总共有25个
SELECT id,first_name
FROM s_emp
WHERE id<15  --有1到14
UNION
SELECT id,first_name
FROM s_emp
WHERE id>5;  --6到25

--结果是1到25

UNION ALL：把两个结果集的结果叠加，不会排序，不会去重

--总共有25个
SELECT id,first_name
FROM s_emp
WHERE id<15  --有1到14
UNION ALL
SELECT id,first_name
FROM s_emp
WHERE id>5;  --6到25

--结果是1到25加上6到14的重复，结果又34个结果

INTERSECT：交集，取两个结果集中都有的结果，会按照结果集第一列升序排序，去重（只有一份）

--总共有25个
SELECT id,first_name
FROM s_emp
WHERE id<15  --有1到14
INTERSECT
SELECT id,first_name
FROM s_emp
WHERE id>5;  --6到25

--结果是6到14

MINUS：差集，第一个结果集减去第一个和第二个都有的，会按照结果集第一列升序排序，去重

--总共有25个
SELECT id,first_name
FROM s_emp
WHERE id<15  --有1到14
MINUS
SELECT id,first_name
FROM s_emp
WHERE id>5;  --6到25

--结果是1到5

rownum（重点）：

• 代表查询得到的结果集中的行号
• 是一个伪列
• 作用：可以用来分页
• 注：
  • 行号永远从1开始
  • rownum条件只要不是从1开始，就不会有结果

SELECT rownum,id,first_name
FROM s_emp
WHERE id>5; 

取用户表的前5五个用户：

SELECT rownum,id,first_name
FROM s_emp
WHERE rownum<6; 

取用户表的第6~10个用户：

--下面查询并没有从1开始查询，所以不会有任何结果
SELECT rownum,id,first_name
FROM s_emp
WHERE rownum>5
AND rownum<11; 

--正确结果
SELECT rownum,id,first_name
FROM s_emp
WHERE rownum<11  --此时查询结束后rownum已经相当于一个普通列，而不是伪列，所以使用MINUS时不用从1开始
MINUS
SELECT rownum,id,first_name
FROM s_emp
WHERE rownum<6; 
--1到11减去1到5

 

排序（排序之前行号已经确定了，排序后行号会打乱顺序）

执行流程：FROM->SELECT，结果集确定，行号就确定了->ORDER    先有行号，再排序的

--排序
SELECT rownum,id,first_name
FROM s_emp
WHERE rownum<6
ORDER BY first_name;