多表查询
介绍
多表查询有如下几种
(1)合并结果集:union,union all,行合并
(2)连接查询,
内连接 [inner] join on
外连接 outer join on
左外连接:left [outer] join
右外连接:right [outer]join
全外连接:full join(MySQL不支持)
(3)自然连接 natural join
(4)子查询
合并结果集(行连接)
(0)要求:合并的2个结果集:列数,列类型必须相同
(1)作用:就是把 2个 select 语言查询的结果合并到一起,
(2)合并结果集有2种方式
Union:去除重复的记录,
Union all:不去除重复数据
准备工作:
CREATE TABLE t1(
id INT,
NAME VARCHAR(10),
sex VARCHAR(10)
);
INSERT INTO t1 VALUES(1,'a','a');
INSERT INTO t1 VALUES(2,'b','b');
INSERT INTO t1 VALUES(3,'c','c');
CREATE TABLE t2(
id INT,
NAME VARCHAR(10)
);
INSERT INTO t2 VALUES(4,'d');
INSERT INTO t2 VALUES(5,'e');
INSERT INTO t2 VALUES(2,'b');
去除重复
SELECT
id,NAME
FROM
t1
UNION
SELECT
*
FROM
t2;
不去除重复
SELECT
id,NAME
FROM
t1
UNION ALL
SELECT
*
FROM
t2;
连接查询
连接查询就是求出多个表的乘积,例如t1连接t2,那么查询出来的结果就是t1*t2,如下:
SELECT
*
FROM
emp,dept;
连接查询会产生笛卡儿积,假如集合A={a,b},集合B={0,1,2},那么2个集合的笛卡儿积为
{(a,0), (a,1), (a,2), (b,0), (b,1), (b,2)}
得到的这个集合,并不是我们想要的,那么我们需要去除重复的,不想要的记录,当然是通过条件过滤
通常要查询的多个表之间是存在关联关系的,那么就通过关联关系去除笛卡儿积。
雇员和部门表,如果笛卡儿积15*4=60条记录,将所有员工和部门信息全部弄出来,我们需要使用关联关系去除无用数据。
案例如下
CREATE TABLE emp(
empno INT COMMENT '员工编号',
ename VARCHAR(50) COMMENT '员工名字',
job VARCHAR(50) COMMENT '职位',
mgr INT COMMENT '领导 编号',
hiredate DATE COMMENT '入职日期',
sal DECIMAL(7,2) COMMENT '月薪',
comm DECIMAL(7,2) COMMENT '奖金',
deptno INT COMMENT '部门编号'
);
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);
CREATE TABLE dept(
deptno INT COMMENT '部门编号',
dename VARCHAR(100) COMMENT '部门名称',
loc VARCHAR(50) COMMENT '部门所在地'
)
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');
加上条件查询
SELECT
*
FROM
emp,dept
WHERE
emp.deptno=dept.deptno;
查询指定列
SELECT
emp.empno,emp.ename,emp.deptno,dept.dename
FROM
emp,dept
WHERE
emp.deptno=dept.deptno;
查询指定列,给表起别名
SELECT
e.empno,e.ename,e.deptno,d.dename
FROM
emp AS e,dept d
WHERE
e.deptno=d.deptno;
内连接
介绍
上面的连接语句就是内连接,但是它不是SQL标准中查询方式,可以理解为方言,SQL语句中标准的内连接
select
e.empno,e.ename,e.deptno,d.dename
from
emp e
inner join
dept d
on
e.deptno = d.deptno
内连接,inner可以省略,不用where 用on
内连接特点:
查询结果必须满足条件,比如我们插入一条部门id=50
其中deptno=50,在dept表中不存在,我们的条件emp.detpno = dept.deptno该条记录不满足要求。
外连接(左连接、右连接)
外连接查询的特点:查询出的结果存在不满足条件的可能
Outer可以省略
左连接
左连接是先查询出左边(也就是以左边为主表)然后查询右表,
右表中满足条件的显示出来,不满足条件的实现null
SELECT
*
FROM
emp AS e
LEFT OUTER JOIN
dept d
ON
e.deptno=d.deptno;
右连接
右连接就是先把右表中所有记录查询出来
然后左表满足条件的显示出现,不满足要求的显示null.
SELECT
*
FROM
emp AS e
RIGHT OUTER JOIN
dept d
ON
e.deptno=d.deptno;
连接查询心得:
连接不限于2个表,连接查询可以是3张,4张。。。。甚至更多。通常情况下连查询不可能需要整个笛卡儿积,而是只需要其中一部分数据,那么这时候需要使用条件去除不需要的记录,这个条件往往是:主外键关系。
2个表连接,一定有1个主外键关系,
3个表连接,一定有至少有2个主外键关系
自然连接
我们都知道自然连接会产生笛卡儿积,我们通常使用主外键关系去消除,而自然连接,无需我们给出主外键关系,它会自动找到 这关系。。
2张连接的表中列名称与列类型是一样,将作为条件,比如emp和dept表都有deptno列,并且他们类型一致。
#自然连接:内连接
SELECT
*
FROM
emp e
NATURAL JOIN
dept d;
#自然连接:左连接
SELECT
*
FROM
emp e
NATURAL LEFT JOIN
dept d;
#自然连接:右连接
SELECT
*
FROM
emp e
NATURAL RIGHT JOIN
dept d;
子查询
一个Select语句中包含另外一个Select
子查询就是嵌套查询,既Select中包含Select,如果一个语句中存在2个或者2个以上Select,那么就是子查询语句了。
子查询出现的位置
Where后:作为条件查询的一部分
From后:作表
当子查询出现在Where后面的时候,还可以使用关键字
Any,All
查询上表中工资高于 JONES的员工.
SELECT
*
FROM
emp
WHERE
sal >( SELECT
sal
FROM
emp
WHERE
ename='JONES');
查询与SCOTT同一分部的员工
SELECT
*
FROM
emp
WHERE
deptno=(SELECT
deptno
FROM
emp
WHERE
ename='SCOTT');
查询工资高于30号部门所有的员工信息
SELECT
*
FROM
emp
WHERE
sal > ALL(SELECT
sal
FROM
emp
WHERE
deptno=30);
查询工作与MARTIN完全相同的员工信息
SELECT
*
FROM
emp
WHERE
(job,sal) IN(SELECT
job,sal
FROM
emp
WHERE
ename='MARTIN');
查询拥有2个以上直接下属的员工的信息
SELECT
*
FROM
emp
WHERE
empno IN(SELECT
mgr
FROM
emp
GROUP BY
mgr
HAVING
COUNT(mgr)>=2);
查询员工编号7788的员工名称,部门名称,部门地址
方法一:
SELECT
a.empno,a.ename,d.dename,d.loc
FROM
(SELECT
*
FROM
emp
WHERE
empno=7788) a,
dept d
WHERE
d.deptno=a.deptno;
方法二:
SELECT
e.empno,e.ename,d.dename,d.loc
FROM
emp e
JOIN
dept d
ON
e.deptno = d.deptno
WHERE
e.empno=7788;
部门平均工资高于2000的部门
SELECT
*
FROM
(SELECT
deptno,AVG(sal) AS avg_sal
FROM
emp
GROUP BY
deptno) t
WHERE
t.avg_sal > 2000;
自连接:自己连接自己