MySQL入门系列7-多表查询

在我们日常开发中，表与表之间的关系通常分为以下几种:一对多(多对一)、’多对多、一对一。

一、一对多(多对一)

我们之前的部门和员工表就是一个一对多的关系，一个部门有多个员工，部门就是一方，员工就是多方。

员工表的外键关联了部门表

二、多对多

学生和课程的关系可以理解为多对多的关系，一个学生可以学习多门课程，一门课程也可以被多个学生选择。这种情况下我们需要建立一个中间表来存储，中间表需要包含两个外键，分别关联双方的主键。

学生表建表SQL语句:

CREATE TABLE t_student (
id BIGINT PRIMARY KEY auto_increment,
name VARCHAR ( 50 ) NOT NULL COMMENT '学生姓名')

课程表建表SQL语句:

CREATE TABLE t_course (
id BIGINT PRIMARY KEY auto_increment,
name VARCHAR ( 50 ) NOT NULL COMMENT '课程名称')

学生课程中间表SQL语句:

CREATE TABLE t_student_course (
	id BIGINT PRIMARY KEY auto_increment,
	student_id BIGINT COMMENT '学生ID',
	course_id BIGINT COMMENT '课程ID',
CONSTRAINT fk_student FOREIGN KEY ( student_id ) REFERENCES t_student ( id ),
CONSTRAINT fk_course FOREIGN KEY ( course_id ) REFERENCES t_course ( id ))

执行这些SQL语句之后我们查看中间表已经添加了学生和课程表的外键约束。

三、一对一

我们的用户和用户详情表之间或者订单和订单详情表之间就是一对一的关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另外一张表中，在详情表中设置外键关联主表的主键，实现一对一的关系。

用户表SQL语句:

CREATE TABLE `t_user`  (
  `id` bigint primary key auto_increment NOT NULL,
  `name` varchar(50)  NOT NULL,
  `age` int NULL DEFAULT NULL,
) ENGINE = InnoDB

用户详情表SQL语句:

CREATE TABLE t_user_info (
	id BIGINT PRIMARY KEY auto_increment,
	native_place VARCHAR ( 50 ) COMMENT '籍贯',
	address VARCHAR ( 50 ) COMMENT '地址',
user_id BIGINT COMMENT '所属用户',
CONSTRAINT fk_user FOREIGN KEY ( user_id ) REFERENCES t_user ( id ))

四、多表查询

1.隐式内连接

语法:

SELECT 字段列表 FROM 表1,表2 WHERE 限制条件;

SQL语句:

select d.*,e.* from t_department d,t_employee e WHERE d.id=e.dept_id

2.显式外连接

语法:

SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;

SQL语句:

select d.*,e.* from t_department d inner join t_employee e on d.id=e.dept_id

3.外连接

外连接分为:左外连接和右外连接。左外连接相当于查询左表的所有数据，也包含左表和右表交集的部分的数据。右外连接相当于查询右表的所有数据，也包含左表和右表交集部门的数据。

左外连接语法:

SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;

SQL语句:

select d.*,e.* from t_department d left join t_employee e on d.id=e.dept_id

右外连接语法:

SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;

SQL语句:

select d.*,e.* from t_department d right join t_employee e on d.id=e.dept_id

4.联合查询

联合查询就是把多次查询的结果合并起来，形成一个新的查询结果集。

语法:

SELECT 字段列表 FROM 表A  ...   # 查询结果集1
UNION [ ALL ]
SELECT 字段列表 FROM 表B  ....;  # 查询结果集2

注意 :

1. 对于联合查询的多张表的字段列表必须保持一致，字段类型也需要保持一致，如果不一致将会报错。
2. union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重。

SQL语句:

select * FROM t_employee WHERE id=6
UNION ALL
select * FROM t_employee WHERE id=7

5.子查询

在SQL语句中嵌套其他select语句，称为子查询。

子查询的结果为单个值:

select * from t_employee WHERE dept_id=(SELECT id FROM t_department WHERE `name`='软件部');

子查询返回多行数据:

select * from t_employee WHERE dept_id in(SELECT id FROM t_department);