数据库-表关系 多表查询

本文已参与「新人创作礼」活动，一起开启掘金创作之路

表关系

（一）概念和分类

现实生活中，实体与实体之间是存在关系的，比如：老公和老婆，部门和员工，老师和学生等。我们设计的表是对现实中实体的描述，那么我们在设计表的时候，就应该体现出表与表之间的这种关系。

表与表之间的关系主要包括：一对多（多对一）、一对一、多对多三种。

（二）一对多（多对一）

一对多（1:n）。 例如：班级和学生，部门和员工，客户和订单，分类和商品等

一对多建表原则: 在从表(多方)创建一个字段，字段作为外键指向主表(一方)的主键。

建表操作和讲解外键时相同。

（三）多对多

多对多（m:n）。例如：老师和学生，学生和课程，用户和角色。

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

示例

-- 创建学生表student
create table student(
     id int primary key auto_increment,
	 name varchar(20)
)
-- 创建课程表course
create table course(
     id int primary key auto_increment,
	 name varchar(20)
)

-- 创建中间关系表 student_course,中间关系表中student_id和course_id联合做主键，分别做外键参照多方的两端
create table student_course(
      student_id int,
	  course_id int,
	  primary key(student_id,course_id),
	  constraint foreign key(student_id) references student(id),
	  constraint foreign key(course_id) references course(id)
)

（四）一对一

一对一（1:1）。 在实际应用中并不多见，因为一对一可以创建成一张表。

1. 主键关联方式

主表的主键和从表的主键，形成主外键关系。

示例

-- 创建丈夫表husband
create table husband(
      id int primary key auto_increment,
	  name varchar(20)
)

-- 创建妻子表wife，wife的主键列id作为外键参照husband的主键列id
create table wife(
      id int primary key,
	  name varchar(20),
	  constraint foreign key(id) references husband(id)
)

2. 唯一外键方式

主表的主键和从表的外键（唯一），形成主外键关系。

示例

-- 创建person表
create table person(
     id int primary key auto_increment,
	 name varchar(20)
);
-- 创建idcard表，idcard表中的person_id做外键参照person表的id，在person_id列上添加唯一约束，就表示出了person和idcard的一对一关系。
create table idcard(
	 id int primary key,
	 card_num varchar(20),
	 person_id int unique,
	 constraint foreign key(person_id) references person(id)
) 

多表查询

（一）什么是多表查询

之前我的查询都是从一张表中返回数据。同时查询多张表获取到需要的数据，就是多表查询。比如：要查询员工的姓名和所在的部门名称，需要从employee表和department表两个表中查询数据。

准备数据

-- 创建部门表 dept
CREATE TABLE dept (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20)
);
-- 添加数据
INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部');

-- 创建员工表
CREATE TABLE emp (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(10),
  gender CHAR(1),   -- 性别
  salary DOUBLE,   -- 工资
  join_date DATE,  -- 入职日期
  dept_id INT
);

-- 添加数据
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2013-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1);

（二）笛卡尔积

1. 什么是笛卡尔积现象

多表查询时左表的每条数据和右表的每条数据组合，这种效果成为笛卡尔积。

语法

select * | 列1,列2,...,列n from 表1,表2,...,表n

示例

-- 需求：查询部门和员工信息
SELECT * FROM dept, emp;

以上数据其实是左表的每条数据和右表的每条数据组合。左表有3条，右表有5条，最终组合后3*5=15条数据。

左表的每条数据和右表的每条数据组合，这种效果称为笛卡尔乘积，笛卡尔积中有很多没有意义的数据。

2. 如何消除笛卡尔积现象

我们发现不是所有的数据组合都是有用的，只有员工表.dept_id = 部门表.id 的数据才是有用的。所以需要通过条件过滤掉没用的数据。

SELECT * FROM dept, emp WHERE emp.dept_id = dept.id; 

（三）多表查询

要清除笛卡尔积现象，可以使用连接查询。连接查询分为内连接和外连接。

1. 内连接

用左边表的记录去匹配右边表的记录，符合条件的才显示。内连接又分为隐式内连接和显示内连接两种。

（1）隐式内连接

隐式内连接：不使用JOIN关键字，条件使用WHERE指定。

语法

SELECT 字段名 FROM 左表, 右表 WHERE 条件

示例

-- 查询员工的姓名和所在的部门的名称
select emp.name emp_name,dept.name dept_name from emp,dept where emp.dept_id = dept.id
-- 可以使用表别名的方式简化查询语句
select e.name emp_name,d.name dept_name from emp e,dept d where e.dept_id = d.id

where 条件中的 e.dept_id = d.id 就是连接条件

-- 查询员工孙悟空的信息和他所在的部门名称
select e.*,d.name dept_name from emp e,dept d where e.dept_id=d.id and e.name='孙悟空'

（2）显示内连接

显示内连接：使用INNER JOIN ... ON语句，可以省略INNER。查询的效果和隐式内连接相同。

语法

SELECT 字段名 FROM 左表 INNER JOIN 右表 ON 条件；

示例

-- 查询唐僧的信息，显示员工id、姓名、性别、工资和所在的部门名称，我们发现需要联合两张表才能同时查询出需要的数据，我们使用内连接。
select e.id emp_id,e.name emp_name,e.gender emp_gender,e.salary emp_salary,d.name dept_name from emp e inner join dept d on e.dept_id=d.id and e.name='唐僧'

2. 外连接

（1）左外连接

左外连接：使用LEFT OUTER JOIN ... ON，OUTER可以省略。

左表数据全部显示，右表显示和左表有关联的数据。

语法

 SELECT 字段名 FROM 左表 LEFT OUTER JOIN 右表 ON 条件

用左边表的记录去匹配右边表的记录，如果符合条件的则显示；否则，显示NULL。

示例

新入职了一名员工 金角大王，但该员工没有分配部门。

现在要查询员工的信息和所在部门的名称。如果使用内连接查询，没有部门的员工将不会显示，因为不满足连接条件。

select e.*,d.name dept_name from emp e,dept d where e.dept_id=d.id

想要显示全部员工，可以把员工emp 当成左表，部门dept表当成右表，使用左外连接查询。

（2）右外连接

右外连接：使用RIGHT OUTER JOIN ... ON，OUTER可以省略。右表数据全部显示，左表显示和右表有关联的数据。

语法

SELECT 字段名 FROM 左表 RIGHT OUTER JOIN 右表 ON 条件；

用右边表的记录去匹配左边表的记录，如果符合条件的则显示；否则，显示NULL。

示例

-- 使用右外连接解决上面左外连接解决的问题
select e.*,d.name dept_name from dept d right outer join emp e on e.dept_id = d.id;

使用右外连接，把原来的左右表位置互换，产生同样的查询效果。

（四）子查询

一条SELECT语句结果作为另一条SELECT语法的一部分。

语法

select 字段，... from 表  where 字段  运算符 (select  字段,... from 表)

子查询 (内查询) 在主查询之前执行一次。

子查询的结果被用于主查询 (外查询)

示例

-- 查询出工资最高的员工信息
SELECT * FROM employee WHERE salary=(SELECT MAX(salary) FROM employee)
-- 子查询先查询出最高工资，主查询查询出拥有最高工资的员工信息

1. 子查询使用规则

1、子查询放在圆括号中。

2、将子查询放在比较条件的右边。（非强制要求）

3、在子查询中的 ORDER BY 子句不需要。

4、在单行子查询中用单行运算符，在多行子查询中用多行运算符。

2. 单行子查询

子查询的结果是一个值

单行运算符

= > >= < <= != 等我们学习过的众多运算符都是单行运算符

示例

-- 查询工资比白骨精高的员工信息
select * from emp where salary>(select salary from emp where name='白骨精')
-- 子查询先查询出'白骨精'的工资，作为条件交给主查询使用

3. 多行子查询

子查询结果是单例多行

多行运算符

in、any、all

示例

-- 1. 查询工资大于5000的员工，来自于哪些部门的名字 
-- 先查询大于5000的员工所在的部门id
SELECT dept_id FROM emp WHERE salary > 5000

-- 再查询在这些部门id中部门的名字
SELECT dept.name FROM dept WHERE dept.id IN (SELECT dept_id FROM emp WHERE salary > 5000)

-- 2. 查询开发部与财务部所有的员工信息
-- 先查询开发部与财务部的id
SELECT id FROM dept WHERE NAME IN('开发部','财务部');

-- 再查询在这些部门id中有哪些员工
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME IN('开发部','财务部'))

any 和 all 不是单独使用的运算符，要和 比较运算符一起使用

示例

修改表中数据，把蜘蛛精的name修改为白骨精

select salary from emp where name='白骨精'

any表示任意值（比最小的大）

select name,salary from emp where salary >any(select salary from emp where name='白骨精')

all表示全部值（比最大的大）

select name,salary from emp where salary >all(select salary from emp where name='白骨精')

4. 多列子查询

子查询结果是单列，在WHERE后面作为条件 子查询结果

子查询结果是多列，肯定在FROM后面作为表

语法

SELECT 查询字段 FROM （子查询） 表别名 WHERE 条件
-- 子查询结果只要是多行多列，肯定在FROM后面作为表 。
-- 子查询作为表需要取别名，否则这张表无法访问表中的字段。

示例

-- 查询出2011年以后入职的员工信息，包括部门名称
-- 在员工表中查询2011-1-1以后入职的员工
SELECT * FROM emp WHERE join_date > '2011-1-1'

-- 查询所有的部门信息，与上面的虚拟表中的信息组合，找出所有部门id等于的dept_id
SELECT e.id emp_id,e.name emp_name,e.gender emp_gender,e.salary emp_salary,e.join_date emp_join_date,d.id dept_id,d.name dept_name FROM dept d, (SELECT * FROM emp WHERE join_date > '2011-1-1') e WHERE e.dept_id = d.id