多表操作

 

多表查询

多个表之间是有关系的，那么关系靠谁来维护 ?

多表约束：外键列

多表的关系

一对多/多对一关系

客户和订单，分类和商品，部门和员工 .

一对多建表原则：在多的一方创建一个字段，字段作为外键指向一的一方的主键 .

多对多关系

学生和课程

多对多关系建表原则：需要创建第三张表 , 中间表中至少两个字段，这两个字段分别作为外键指向各自一

方的主键 .

一对一关系

在实际的开发中应用不多 . 因为一对一可以创建成一张表 .

两种建表原则：

唯一外键对应：假设一对一是一个一对多的关系，在多的一方创建一个外键指向一的一方的主键，将外 键设置为unique.

主键对应：让一对一的双方的主键进行建立关系 .

多表查询

多表查询有如下几种：

1. 合并结果集 :UNION 、 UNION ALL

2. 连接查询

• 内连接 [INNER] JOIN ON
• 外连接 OUTER JOIN ON
• 左外连接 LEFT [OUTER] JOIN
• 右外连接 RIGHT [OUTER] JOIN
• 全外连接（MySQL不支持）FULL JOIN
• 自然连接 NATURAL JOIN

3. 子查询

ALTER TABLEscore1 ADD CONSTRAINT fk_stu_score FOREIGN KEY(sid) REFERENCES
stu(id);

 

合并结果集

作用：合并结果集就是把两个 select 语句的查询结果合并到一起！

合并结果集有两种方式：

//UNION：去除重复记录，例如：
SELECT* FROM t1 UNION SELECT * FROM t2；

//UNION ALL：不去除重复记录，例如：
SELECT * FROM t1 UNION ALL SELECT * FROM t2;

注意：被合并的两个结果：列数、列类型必须相同。

连接查询

连接查询就是求出多个表的乘积，例如 t1 连接 t2 ，那么查询出的结果就是 t1*t2 。

 

连接查询会产生 笛卡尔积 ，假设集合 A={a,b} ，集合 B={0,1,2} ，则两个集合的笛卡尔积为 {(a,0),(a,1), (a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。 那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢，当然是通过 条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔。

//示例 1：现有两张表:emp-员工表，dept-部门表
CREATE TABLE dept1( deptno int primary key, dname varchar(14), loc varchar(13) );
insert into dept1 values(10,'服务部','北京'); insert into dept1 values(20,'研发部','北京'); insert into dept1 values(30,'销售部','北京'); insert into dept1 values(40,'主管部','北京'); 

CREATE TABLE emp1( empno int, ename varchar(50), job varchar(50), mgr int, hiredatedate,sal double, comm double, deptno int );
insert into emp1 values(1001,'张三','文员',1006,'2019-1-1',1000,2010,10);
insert into emp1 values(1002,'李四','程序员',1006,'2019-2-1',1100,2000,20); 
insert into emp1 values(1003,'王五','程序员',1006,'2019-3-1',1020,2011,20); 
insert into emp1 values(1004,'赵六','销售',1006,'2019-4-1',1010,2002,30); 
insert into emp1 values(1005,'张猛','销售',1006,'2019-5-1',1001,2003,30); 
insert into emp1 values(1006,'谢娜','主管',1006,'2019-6-1',1011,2004,40);

//多表查询
select * from emp,dept;

//使用主外键关系做为条件来去除无用信息
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;

一：内连接

上面的连接语句就是内连接，但它不是 SQL 标准中的查询方式，可以理解为方言！

语法：

select 列名 from 表1 inner join 表2 on 表1.列名=表2.列名 //外键列的关系 where.....;

//等价于：
select 列名 from 表1,表2 where 表1.列名=表2.列名 and ...(其他条件);

 

注 :<1> 表 1 和表 2 的顺序可以互换

<2> 找两张表的等值关系时 , 找表示相同含义的列作为等值关系。

<3> 点操作符表示 “ 的 ”, 格式 : 表名 . 列名

<4> 可以使用 as, 给表名起别名 , 注意定义别名之后 , 统一使用别名

示例：

//查询学生表中的学生姓名和分数表中的分数 
select name,score from student as s inner join scores as c on s.studentid=c.stuid;
//等价于： 
select name,score from student as s,scores as c where s.studentid=c.stuid;

 三表联查：

语法：

select 列名 from 表1
 inner join 表2 on 表1.列名=表2.列名
 inner join 表3 on 表1或表2.列名=表3.列名 where

//等价于:
 select 列名 from 表1,表2,表3
 where 表1.列名=表2.列名 and 表1/表2.列名=表3.列名

SQL标准的内连接为：

SELECT * FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;

内连接的特点：查询结果必须满足条件。

二：外连接

包括左外连接和右外连接，外连接的特点：查询出的结果存在不满足条件的可能。

-- 显示还没有员工的部门名称 ?

-- 外联查询

-- 左外联:

select 列名 from 主表 left join 次表 on 主表.列名=次表.列名

• 主表数据全部显示，次表数据匹配显示，能匹配到的显示数据，匹配不成功的显示null
• 主表和次表不能随意调换位置
• 使用场景:一般会作为子查询的语句使用

select depname,name from (select e.*,d.depname from department d left join employee e on e.depid=d.depid ) aa where aa.name is null;

-- 右外联:

select 列名 from 次表 right join 主表 on 主表.列名=次表.列名;

a. 左外连接

SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno;

 左连接是先查询出左表（即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的 显示NULL。

insert into emp values(1007,'何炅','主管',1006,'2019-6-1',1011,2004,50);

我们还是用上面的例子来说明。其中 emp 表中 “ 张三 ” 这条记录中，部门编号为 50 ，而 dept 表中不存在部 门编号为50 的记录，所以 “ 张三 ” 这条记录，不能满足 e.deptno=d.deptno 这条件。但在左连接中，因为 emp表是左表，所以左表中的记录都会查询出来，即 “ 张三”这条记录也会查出，但相应的右表部分显示 NULL。

b. 右外连接

右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示 NULL 。例如在 dept表中的 40 部门并不存在员工，但在右连接中，如果 dept 表为右表，那么还是会查出 40 部门，但相 应的员工信息为NULL 。

insert into dept values(60,'颜值部','成都');

SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno;

 

 

连接查询心得 ：

连接不限与两张表，连接查询也可以是三张、四张，甚至 N 张表的连接查询。通常连接查询不可能需要 整个笛卡尔积，而只是需要其中一部分，那么这时就需要使用条件来去除不需要的记录。这个条件大多 数情况下都是使用主外键关系去除。 两张表的连接查询一定有一个主外键关系，三张表的连接查询就一定有两个主外键关系，所以在大家不 是很熟悉连接查询时，首先要学会去除无用笛卡尔积，那么就是用主外键关系作为条件来处理。如果两 张表的查询，那么至少有一个主外键条件，三张表连接至少有两个主外键条件。

三 . 自然连接

自然连接（ NATURAL INNER JOIN ）：自然连接是一种特殊的等值连接 ，他要求两个关系表中进行连

接的必须是相同的属性列（名字相同），无须添加连接条件， 并且在结果中消除重复的属性列。 . 下面给

出几个例子。

语句:

select * from emp e natural join dept d;

子查询

一个 select 语句中包含另一个完整的 select 语句。

子查询就是嵌套查询，即 SELECT 中包含 SELECT ，如果一条语句中存在两个，或两个以上 SELECT ，那么 就是子查询语句了。

子查询出现的位置：

a. where 后，作为条为被查询的一条件的一部分；

b. from 后，作表；

l 当子查询出现在 where 后作为条件时，还可以使用如下关键字：

a. any

b. all

子查询结果集的形式：

• 单行单列（用于条件）
• 单行多列（用于条件）
• 多行单列（用于条件）
• 多行多列（用于表）

 示例：

//1. 工资高于JONES的员工。
//分析：查询条件：工资>JONES工资，其中JONES工资需要一条子查询
//第一步：查询JONES的工资
SELECT sal FROM emp WHERE ename='JONES';

//第二步：查询高于甘宁工资的员工
SELECT * FROM emp WHERE sal > (第一步结果);

//结果：
SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='JONES');

//2. 查询与SCOTT同一个部门的员工。
//分析：
//查询条件：部门=SCOTT的部门编号，其中SCOTT 的部门编号需要一条子查询。
//第一步：查询SCOTT的部门编号
SELECT deptno FROM emp WHERE ename='SCOTT';

//第二步：查询部门编号等于SCOTT的部门编号的员工
SELECT * FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename='SCOTT');

//3. 工资高于30号部门所有人的员工信息
//分析：
SELECT * FROMemp WHERE sal>(SELECT MAX(sal) FROM emp WHERE deptno=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);
//子查询作为条件
//子查询形式为多行单列（当子查询结果集形式为多行单列时可以使用ALL或ANY关键字）

扩展

1.多行新增

insert into 表名(列名) values (列值),(列值),(列值)；

2.多表更新

update 表1,表2 set 列名=列值 where 表1.列名=表2.列名 and 其他限定条件;

update 表1 inner join 表2 on 表1.列名=表2.列名 set 列名=列值 where 限定条件;

//示例：
update employee e,salary s set title='助工',salary=1000 where e.empid=s.empid and name='李四;

3.多表删除

//语法：
 delete 被删除数据的表 from 删除操作中使用的表 where 限定条件
//注:多张表之间使用逗号间隔
//示例：
//删除人事部的信息
delete d,e,s from department d,employee e,salary s where d.depid=e.depid and s.empid=e.empid and depname='人事部';

4.日期运算函数

now() 获得当前系统时间

year( 日期值 ) 获得日期值中的年份

date_add( 日期 ,interval 计算值 计算的字段 );

注：计算值大于 0 表示往后推日期，小于 0 表示往前推日期