数据库多表操作

多表操作

MySQL关系型数据库
非关系型数据库：ES、Redis …

多表之间的关系

• 一对一：一夫一妻制度
• 一对多：一夫多妻制度
• 多对多：群居制度

表跟表之间的关系，大家可以理解为是实体跟实体的关系的一种映射。比如，导师与学员，订单与客 户，部门与员工等等。

主要关系有三种：

一对一：比如，一个男的只能取一个女的当老婆。
一对多：比如，客户与订单，一个客户可以在商城中下多个订单。
多对多：比如，学生与课程，一个学校有很多学生，学生都可以学很多课程。

1）一对一关系

在实际工作中，一对一在开发中应用不多，因为一对一完全可以创建成一张表

建立两表的唯一,一对一的关系：

• 第一步：将被关联的表主键唯一
• 第二步：建立外键约束，管理该表的唯一主键

案例：一个丈夫只能有一个妻子

CREATE TABLE wife(
    id INT PRIMARY KEY , 
	wname VARCHAR(20), sex CHAR(1)
);

CREATE TABLE husband(
	id INT PRIMARY KEY,
	hname VARCHAR(20),
    sex CHAR(1)
);

外键唯一

一对一关系创建方式 1 之外键唯一：

添加外键列 wid，指定该列的约束为唯一（不加唯一约束就是一对多关系）

ALTER TABLE 表 ADD 字段 INT UNIQUE;

ALTER TABLE husband ADD wid INT	UNIQUE;

添加外键约束

alter table husband add foreign key (wid) references wife(id);

主键做外键

一对一关系创建方式 2 之主键做外键（练）

思路：使用主表的主键作为外键去关联从表的主键

2）一对多关系

常见实例：一个分类对应多个商品，客户和订单，分类和商品，部门和员工.

总结：有外键的就是多的一方。

注意事项：一对多关系和一对一关系的创建很类似，唯一区别就是外键不唯一

一对多关系创建：

• 添加外键列
• 添加外键约束

3）多对多关系

常见实例：学生和课程、用户和角色

注意事项：需要中间表去完成多对多关系的创建，多对多关系其实就是两个一对多关系的组合

多对多关系创建：

• 创建中间表，并在其中创建多对多关系中两张表的外键列

• 在中间表中添加外键约束

• 在中间表中添加联合主键约束

多表连接查询

多表查询的作用：

比如：我们现在有一个员工，希望通过员工查询到对应的部门相关信息，部门名称，部门经理，部门收支等等。

• 员工 worker
• 部门 department

希望通过一条SQL语句进行查询多张相关的表，然后拿到的查询结果，其实是从多张表中综合而来的。

比如：我们想获取张三的部门经理

准备工作：

CREATE TABLE department ( id INT PRIMARY KEY auto_increment, NAME VARCHAR ( 50 ) );

-- 插入部门数据

INSERT INTO department ( NAME )
VALUES
	('技术研发'),
	('市场营销'),
	('行政财务');
	
	-- 员工表
	CREATE TABLE worker (
		id int PRIMARY key auto_increment,
		name VARCHAR(50),
		sex CHAR(2),
		money DOUBLE,
		inWork_data DATE,
		depart_id int,
		foreign key(depart_id) REFERENCES department(id)
);
	
-- 	DROP TABLE worker;

INSERT INTO worker(name, sex, money, inWork_data, depart_id)
VALUES ('cuihua', '女', '10000','2019-5-5',1);
INSERT INTO worker(name, sex, money, inWork_data, depart_id)
VALUES ('guoqiang', '男', '20000','2018-5-5',2);
INSERT INTO worker(name, sex, money, inWork_data, depart_id)
VALUES ('qiange', '男', '30000','2018-7-5',3);
INSERT INTO worker(name, sex, money, inWork_data, depart_id)
VALUES ('ttw', '女', '10000','2019-5-5',1);

笛卡尔集

什么是，笛卡尔集：在多表查询的时候，两个表进行组合的一种现象。组合是左边的表的每条记录与右边的每条记录随机的组合在一起。

SELECT * FROM 表, 表;
SELECT * FROM worker, department;

会出现worker表中的数据与department表中每条记录随机的组合在一起

怎么避免无意义的笛卡尔集呢？

• 内连接
• 外连接（左外连接、右外连接）

👆 就是为了筛选，正常笛卡尔集查出来的数据是没有意义的，通过筛选为了让结果有意义。

背景：笛卡尔集现象背后的三个诉求：

• 求交集 （内连接查询）
• 求左边表关联的右表数据（左外连接查询）
• 求右边表关联的左表数据（右外连接查询）

内连接查询

内连接查询：作用，用左边表中的记录匹配右边表中的记录【求交集部分】

【案例】

-- 隐士内连接查询
-- 查询员工的部门 正常查询两个表是没有意义
-- 但是可以赋予它意义哪就用内连接，使用内连接的时候就会体现出他们两个的关系，查询员工的部门是什么
-- 就是把两个表有关联关系的数据作为条件查询
SELECT * FROM worker, department WHERE worker.depart_id = department.id;

• 隐士内连接查询：通过隐式的条件连接查询，where后面的条件

• 显示内连接查询：使用inner join 表 … on …

-- 显示内连接查询
SELECT * FROM woeker INNER JOIN department on worker.depart_id = department.id

【优化—别名-取除不想显示的字段】

SELECT
	w.id,
	w.NAME,
	w.sex,
	d.NAME 
FROM
	worker AS w
	INNER JOIN department AS d ON w.depart_id = d.id

【优化-可以缩写关键字】【连接查询不写，默认就是内连接】INNER（省略建议写上）

SELECT
	w.id,
	w.NAME,
	w.sex,
	d.NAME 
FROM
	worker AS w
JOIN department AS d ON w.depart_id = d.id

外连接查询

什么是外连接查询？

作用：连表查询中，查询两表数据连接的对应外表数据。

举例：A表、B表，内连接求的是两表的公共部分

左外连接

• 左外连接求的是A表的所有记录，关联B表的相应记录。

-- 语法
select * from A表 left join B表 on a.id = b.id;

【案例】

SELECT * FROM department as d LEFT JOIN worker as w on d.id = w.depart_id;

右外连接

• 与左面整好相反

-- 语法
select * from A表 right join B表 on a.id = b.id;

【案例】

SELECT * FROM department as d RIGHT JOIN worker as w on d.id = w.depart_id;