第四章 多表查询

概念

基于多张表进行查询，针对FROM子句进行拓展

格式

原理

两表结合遵循笛卡尔积的规则
两表中 每条数据 相互结合 的所有可能
总数 是 两表数据条数 的 乘积
在实际运用中，我们要消除无效笛卡尔积

关联

等式关联

例子

SELECT  empno,job,sal,e.deptno,dname,loc FROM emp e,dept d WHERE e.deptno=d.deptno;

非等式关联

例子

SELECT empno,ename,job,sal,hiredate,grade 
FROM emp e,salgrade s 
WHERE sal BETWEEN losal AND hisal
ORDER BY sal DESC;

例子

SELECT empno,ename,job,hiredate,sal,grade,dname,loc 
FROM emp e,dept d,salgrade s 
WHERE e.deptno=d.deptno AND sal BETWEEN losal AND hisal;

连接方式

分类

说明

SQLite中不支持右外连接和全外连接

内连接

之前多表查询查询为内连接
使用的是基于SQL89标准的语法
除此还有SQL99标准语法

例子

查询所有雇员的姓名和部门名称

-- SQL89
SELECT ename,dname FROM emp e,dept d WHERE e.deptno=d.deptno;

--SQL99
SELECT ename,dname FROM emp e JION dept d ON e.deptno=d.deptno;

外连接

引入外连接

emp表中增加一个没有部门的雇员

INSERT INTO emp(empno,ename,job,hiredate,sal)
VALUES(6666,'JSAON','MANAGER',DATETIME('now'),10000);

结果

emp表中就有15个雇员

执行内连接语句

结果

少了一个人的数据

原因

JASON的deptno为NULL，所以不满足部门号相等的条件。

执行外连接语句

SQLite只支持基于SQL99标准的左外连接：

-- LEFT表示让左边的表完整显示
SELECT ename,dname FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno;

结果

显示15人，全部雇员信息

例子

查询所有雇员的姓名、月综合收入（sal+comm）与位置，要求显示所有雇员的信息

SELECT ename,sal+IFNULL(comm,0) asal,loc 
FROM emp LEFT JOIN dept ON emp.deptno=dept.deptno;

自连接

查询过程中多次用到一张表

例子

查询每个雇员的编号、姓名、职位、领导姓名。

SELECT e1.empno,e1.ename,e1.job,e2.ename 
FROM emp e1 LEFT JOIN emp e2 ON e1.mgr=e2.empno;

查询所有雇员的姓名、工资等级，以及他们的领导的姓名

SELECT e1.ename,grade,e2.ename 
FROM emp e1 
LEFTJOIN salgrade s ON e1.sal BETWEEN losal AND hisal 
LEFT JOIN emp e2 ON e1.mgr=e2.empno;

其他连接

交叉连接

关键字

CROSS JOIN
两种写法能够产生笛卡尔积

SELECT * FROM emp CROSS JOIN dept;
SELECT * FROM emp,dept;

自然连接

关键字

NATURAL JION
可以自动找到多表之间的关联字段消除笛卡尔积，属于内连接。

-- 自动建立emp与dept的等值连接
SELECT * FROM emp NATURAL JOIN dept;

USING指定关联字段

关键字

JOIN …USING(字段)
使用USING指出多表查询的关联字段。

SELECT * FROM emp JOIN dept USING(deptno);

交集与并集

并集**（不显示重复记录）**

关键字

UNION

作用

将两个查询结果合并，且不显示重复记录

例子

查询所有20部门或者工资高于1500的雇员信息

SELECT * FROM emp WHERE deptno=20
UNION
SELECT * FROM emp WHERE sal>1500;

并集（显示重复记录）

关键字

UNION ALL

作用

可以将两个查询结果合并，且显示重复记录（橙色区域显示两遍）。

例子

查询所有20部门或者工资高于1500的雇员信息

SELECT * FROM emp WHERE deptno=20
UNION ALL
SELECT * FROM emp WHERE sal>1500;

交集

关键字

INTERSECT

作用

可以获得两个查询结果的交集。

例子

查询部门20且工资高于1500的雇员信息。

SELECT * FROM emp WHERE deptno=20
INTERSECT
SELECT * FROM emp WHERE sal>1500;

注意

交集与并集的使用 需要 两个查询的 格式相同，查询结果 的 列 一一对应。

写在最后

删除之前添加的数据。

DELETE FROM emp WHERE empno=6666;

over，谢谢