多表查询
多个表之间是有关系的,那么关系靠谁来维护
?
多表约束:外键列
多表的关系
一对多/多对一关系
客户和订单,分类和商品,部门和员工
.
一对多建表原则:在多的一方创建一个字段,字段作为外键指向一的一方的主键
.
多对多关系
学生和课程
多对多关系建表原则:需要创建第三张表
,
中间表中至少两个字段,这两个字段分别作为外键指向各自一
方的主键
.
一对一关系
在实际的开发中应用不多
.
因为一对一可以创建成一张表
.
两种建表原则:
唯一外键对应:假设一对一是一个一对多的关系,在多的一方创建一个外键指向一的一方的主键,将外
键设置为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
表示往前推日期