MySQL学习之——多表查询
上节课我们不是学习了外键吗?外键是在两张表之间建立联系。其实就能够引申到多表查询的范畴。
如果两张表没有通过外键建立连接的时候,这个时候能不能联合查询呢?
一、多表关系
1.1 一对多
案例:部门和员工
关系:一个部门对应多个员工
实现:在多的一方建立外键,指向另一方的主键。也就是在员工表建立外键references 部门表(主键)。
1.2 多对多
案例:学生和课程
关系:一个学生可以选多门课程,一个课程也可以被多个学生选择
实现:建立一张中间表格,设置两个外键分别指向学生表主键和课程表主键。多对多可以拆分成一对多。
1.3 一对一
案例:用户和用户详情
关系:一个用户只有一个用户详情。这个主要是用于单表拆分的,将一张表的基础字段一张表,把详情放到另一个表里。这样能够提升操作效率。
实现:在任意一方加入外键,指向另一张表的主键,并且外键设为(UNIQUE)
二、多表查询
2.1内连接查询
如果我们直接使用
SELECT * FROM 表1,表2;
这样会得到表1和表2的所有组合,就是我们所说的笛卡尔积;
本来每个人只有一个部门的,即employee表中的DEPART_ID对应department表中的DEPART_ID。
但现在一看就是两张表的所有组合。
为了消除无效组合,我们需要加入条件查询两张表的共有数据。
如:
SELECT * FROM 表1,表2 WHERE 条件;
eg:
SELECT
*
FROM
employee,department
WHERE
employee.DEPART_ID=department.DEPART_ID
;
这种查询实际上是是内连接INNER查询的缩写形式,实际上的形式应当为
SELECT * FROM 表1 INNER JOIN 表2 WHERE 条件;
eg:
SELECT * FROM employee as e INNER JOIN department as d ON e.DEPART_ID=d.DEPART_ID;
ps:内外连接的条件用ON 不用WHERE;
2.2 外连接
外连接分为左外连接和右外连接。
左外连接是显示左表所有数据和右表中两表有交集的数据
SELECT 表1全字段,表2想要的字段 FROM 表一 LEFT [OUTER] JOIN 表二 ON 条件;
eg:
SELECT e.*,d.DEPART_ID FROM employee as e LEFT OUTER JOIN department as d ON e.DEPART_ID=d.DEPART_ID;
右外连接是显示右表所有数据和左表中两表有交集的数据
SELECT 表1想要字段,表2全字段 FROM 表一 RIGHT [OUTER] JOIN 表二 ON 条件;
eg:
SELECT e.DEPART_ID,d.* FROM employee as e RIGHT OUTER JOIN department as d ON e.DEPART_ID=d.DEPART_ID;
2.3 自连接
我觉得这个就很神奇,比如说下面这张图中,每一行数据有MANAGER_ID,也就是这个人的主管的工号。那我想知道主管是谁怎么办?
这就可以用到内连接的概念了!
内连接示例
SELECT a.NAME ,b.NAME FROM employee a, employee b where a.MANAGER_ID=b.EMPLOYEE_ID;
相当于把一张表看成了两张表来用。
2.4 联合查询
SELECT * FROM 表1
UNION [ALL]
SELECT * FROM 表2;
联合查询的一些规则:
1、多条查询语句的查询列数必须保持一致,否则报错。
2、多条查询语句的列值类型和顺序最好保持一致。
3、UNION 关键字默认不查询重复记录,但只要有一个字段不一样就认为是两条记录。但UNION ALL 可以显示重复数据
这么看来联合查询像是很多数据分布在不同的表里面,想要同时用这些表,就用联合查询。
2.5 子查询
子查询与我们经常说的子表有些联系,就是你在一个表中查询的结果可以作为下一次查询的依据,这个一句可以是条件值 WHERE,来源FROM, 显示字段SELECT后。
查询出来的类型也有不同的种类:
1.标量子查询(一个值)
2.列子查询(查询结果为一列,一个字段的多条数据)
3.行子查询(查询结果为一行,多个字段的一条数据)
4.表子查询(查询结果为一个表)
1)标量子查询
标量子查询一般放在WHERE后面当条件值,如
查询一个研发部的所有员工;
SELECT * FROM employee WHERE DEPART_ID=$研发部ID;
但研发部ID只有在department表中,所以我们要先去deparment表中查询‘研发部’的DEPART_ID
SELECT DEPART_ID FROM department WHERE DEPART='研发部';
所以合并之后就是
SELECT * FROM employee WHERE DEPART_ID=(SELECT DEPART_ID FROM department WHERE DEPART='研发部');
查询结果如下:
2)列子查询
列子查询会让我们想到是多个值,也是可以放在WHERE后面当条件值的。
常常会用在以下运算符之中
那我们显示研发部和销售部的所有员工数据应该怎么写呢?
SELECT * FROM employee WHERE DEPART_ID IN (SELECT DEPART_ID FROM department WHERE DEPART='研发部' OR DEPART='销售部');