多表查询有如下几种：
-
连接查询
-
内连接
-
外连接
-
左外连接
-
右外连接
-
-
自连接
-
子查询
-
创表语句
emp表
--创建表
CREATE TABLE emp (
empno INT,
ename VARCHAR (50),
job VARCHAR (50),
mgr INT,
hiredate DATE,
sal DECIMAL (7, 2),
comm DECIMAL (7, 2),
deptno INT
) ;
--插入数据
INSERT INTO emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
dept表
CREATE TABLE dept (
deptno INT,
dname VARCHAR (14),
loc VARCHAR (13)
) ;
--插入数据
INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES(20, 'RESEARCH', 'DALLAS');
INSERT INTO dept VALUES(30, 'SALES', 'CHICAGO');
INSERT INTO dept VALUES(40, 'OPERATIONS', 'BOSTON');
联合查询
1.作用：合并结果集就是把两个select语句的查询结果合并到一起
2.合并结果集有两种方式：
UNION：去除重复记录，例如：SELECT * FROM t1 UNION SELECT * FROM t2；
UNION ALL：不去除重复记录，例如：SELECT * FROM t1 UNION ALL SELECT * FROM t2
连接查询
连接查询就是求出多个表的乘积，例如t1连接t2，那么查询出的结果就是t1*t2。
使用主外键关系做为条件来去除无用信息
SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;上面查询结果会把两张表的所有列都查询出来，也许你不需要那么多列，这时就可以指定要查询的列了。
SELECT emp.ename,emp.sal,emp.comm,dept.dname FROM emp,dept WHERE emp.deptno=dept.deptno;还可以为表指定别名，然后在引用列时使用别名即可。
SELECT e.ename,e.sal,e.comm,d.dname FROM emp AS e,dept AS dWHERE e.deptno=d.deptno;JOIN 类型
JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段（跨表查询）
（1）inner join 内连接
【1】等值连接，返回两个表中连接字段相等的行（条件是等量关系）
【2】非等值连接，条件不是一个等量关系
【3】自连接，同一张表看成多张表
注意：inner可以省略，两张表没有主次之分
语法如下：
SELECT 字段列表 FROM 表1 [ inner ] JOIN 表2 ON 条件 ... ;示例如下：
#查询 "SALES" 和 "ACCOUNTING" 的所有员工信息
#: 表连接查询
select e.* from emp e
join dept d
on e.deptno = d.deptno
where d.dname in ('SALES','ACCOUNTING');（2）outer join 外连接
左外连接：left (outer) join———即使右表中没有匹配，也从左表返回所有的行（将join关键字左边的表看成主表，主要是为了将左表的数据全部查询出来，捎带着关联查询右边的表）
右外连接：right (outer) join ———即使左表中没有匹配，也从右表返回所有的行（将join关键字右边的表看成主表，主要是为了将右表的数据全部查询出来，捎带着关联查询左边的表）
全外连接：full (outer) join———外连接，只要其中一个表中存在匹配，则返回；即返回两个表中的行：left join + right join
注意：outer可以省略，两张表有主次之分
语法如下：
--左外连接
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;
--右外连接
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;
--全外连接
SELECT 字段列表 FROM 表1 FULL [ OUTER ] JOIN 表2 ON 条件 ... ;示例如下：
查询部门编号，部门编号所对应的部门名称，以及部门人数
这里通过两表中有相同的部门编号将两个表连接起来
使用左外连接，显示部门表中所有部门
select d.deptno,d.dname,t2.num from
dept d
left join
(select deptno,count(1) num from emp group by deptno) t2//统计各部门的人数，按部门编号分组
on d.deptno = t2.deptno;自连接
自连接查询，顾名思义，就是自己连接自己，也就是把一张表连接查询多次。我们先来学习一下自连接的查询语法：
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;示例如下：
select
a.ename as '员工', b.ename as '领导'
from
emp a
join
emp b
on
a.mgr = b.empno; //员工的领导编号 = 领导的员工编号子查询
子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了。
SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个
分类:
子查询出现的位置：
-
where后，作为条件的一部分；
-
from后，作为被查询的一条表；
-
SELECT之后 ,作为被查询的一列;
当子查询出现在where后作为条件时，还可以使用如下关键字：
-
any
-
all
子查询结果集的形式：
根据子查询结果不同，分为：
A. 标量子查询（子查询结果为单个值）
B. 列子查询(子查询结果为一列)
C. 行子查询(子查询结果为一行)
D. 表子查询(子查询结果为多行多列)
标量子查询
子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。
常用的操作符：= <> > >= < <=
示例：工资高于ALLEN的员工。
分析：
查询条件：工资>ALLEN工资，其中ALLEN工资需要一条子查询。
第一步：查询ALLEN的工资
SELECT sal FROM emp WHERE ename='ALLEN'第二步：查询高于ALLEN工资的员工
SELECT * FROM emp WHERE sal > (${第一步})结果：
SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='ALLEN')列子查询
子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。
常用的操作符：IN 、NOT IN 、 ANY 、SOME 、 ALL
1.查询 "SALES" 和 "ACCOUNTING" 的所有员工信息
分解为以下两步:
①. 查询 "销售部" 和 "市场部" 的部门ID
select id from dept where name = 'SALES' or name = 'ACCOUNTING';②. 根据部门ID, 查询员工信息
select * from emp where dept_id in (select id from dept where name = 'SALES' or name = 'ACCOUNTING');2.工资高于30部门所有人的员工信息
分析：
查询条件：工资高于30部门所有人工资，其中30部门所有人工资是子查询。高于所有需要使用all关键字。
①. 查询30部门所有人工资
SELECT sal FROM emp WHERE deptno=30;②. 查询高于30部门所有人工资的员工信息
SELECT * FROM emp WHERE sal > ALL (${第一步})结果：
SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30)3.查询比SALES部其中任意一人工资高的员工信息
分解为以下两步:
①. 查询SALES部所有人工资
select salary from emp where dept_id = (select id from dept where name = 'SALES');②. 比SALES部其中任意一人工资高的员工信息
select * from emp where salary > any ( select salary from emp where dept_id = (select id from dept where name = 'SALES') );行子查询
子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。
常用的操作符：= 、<> 、IN 、NOT IN
查询工作和工资与MARTIN完全相同的员工信息
分析：
查询条件：工作和工资与MARTIN完全相同，这是子查询
①.查询出殷天正的工作和工资
SELECT job,sal FROM emp WHERE ename='MARTIN'②. 查询出与MARTIN工作和工资相同的人
SELECT * FROM emp WHERE (job,sal) IN (${第一步})结果：
SELECT * FROM emp WHERE (job,sal) IN (SELECT job,sal FROM emp WHERE ename='MARTIN')表子查询
子查询返回的结果是多行多列，这种子查询称为表子查询。
常用的操作符：IN
查询员工编号为7369的员工名称、员工工资、部门名称、部门地址
分析：
查询列：员工名称、员工工资、部门名称、部门地址
查询表：emp和dept，分析得出，不需要外连接（外连接的特性：某一行（或某些行）记录上会出现一半有值，一半为NULL值）
条件：员工编号为7369
第一种方式: 使用表连接查询
第一步：去除多表，只查一张表，这里去除部门表，只查员工表
SELECT ename, sal FROM emp e WHERE empno=7369第二步：让第一步与dept做内连接查询，添加主外键条件去除无用笛卡尔积
SELECT e.ename, e.sal, d.dname, d.loc FROM emp e, dept d WHERE e.deptno=d.deptno AND empno=7369第二种方式:使用子查询
第二步中的dept表表示所有行所有列的一张完整的表，这里可以把dept替换成所有行，但只有dname和loc列的表，这需要子查询。
第一步：查询dept表中dname和loc两列，因为deptno会被作为条件，用来去除无用笛卡尔积，所以需要查询它。
SELECT dname,loc,deptno FROM dept;第二步：替换第二步中的dept
SELECT e.ename, e.sal, d.dname, d.loc FROM emp e, (SELECT dname,loc,deptno FROM dept) d WHERE e.deptno=d.deptno AND e.empno=7369
扫一扫
1935
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
