文章目录
一. mysql数据库中常见表关系
- 单表操作,表之间没有关联关系
- 两表关联关系:一对一、一对多、多对多关系
二. 一对一关系
1. 特点
- 一对一的两个表中,通常通过外键约束来实现表关联。
- 一对一表关系中,一张表的主键也是另外一张表的外键。
- 外键所在的表我们通常可以叫从表,关联的另外一张表通常叫主表。
- 一对一表关系中,增加表数据的时候,需要先添加主表数据,然后增加从表数据。
- 一对一表关系中,删除表数据的时候,需要先删除从表数据,然后删除主表数据。
设置表的外键约束:
- ALTER TABLE 从表名
- ADD CONSTRAINT 外键名称
- FOREIGN KEY 从表关联外键
- REFERENCES 关联主表名称(主表id)
2. 示例代码
一对一表关系:
- 手动创建丈夫表和妻子表
- 通过sql代码添加表外键约束
- 测试数据的添加和删除效果
#设置外键约束:
ALTER TABLE husband #设置从表名称
ADD CONSTRAINT fk_husband_wife #外键约束名称
FOREIGN KEY (hid) #外键字段
REFERENCES wife(wid); #主表及关联的主键字段
#增加数据:增加丈夫表数据的时候需要先增加妻子表数据
INSERT INTO wife(wname) VALUES("刘贝");
INSERT INTO husband (hname) VALUES("张三丰");
#删除数据:如果存在关联数据,先删除丈夫表数据,再删除妻子表,如果没有关联数据则直接删除数据
DELETE FROM husband ;
DELETE FROM wife ;
#删除某一条不关联的妻子数据
DELETE FROM wife WHERE wid=1;
三. 一对多关系
1. 特点
- 一对多关系中,一的一方叫主表,多的一方叫从表
- 外键存在于从表中,在从表中专门设置一个字段作为外键关联主表的主键id字段
- 一对多表关系中,增加表数据的时候,需要先添加主表数据,然后增加从表数据。
- 一对多表关系中,删除表数据的时候,需要先删除从表数据,然后删除主表数据。
设置外键约束方法和一对一设置一样操作。
2. 示例代码
一对多关系
- 手动创建一张主表:商品分类表
- 手动创建一张从表:商品表
- 通过sql代码设置两个表的外键约束
- 测试增加和删除效果
ALTER TABLE goods #设置从表名称
ADD CONSTRAINT fk_goods_sorts #外键约束名称
FOREIGN KEY (sort_id) #外键字段
REFERENCES sorts(sid); #主表及关联的主键字段
#增加数据:先增加分类数据再增加商品数据
INSERT INTO sorts(sname) VALUES("电脑");
INSERT INTO goods (gname,price,sort_id)VALUES("苹果电脑",6500,2);
#删除数据:先删除商品数据,再删除分类数据
DELETE FROM goods WHERE gid=3;
DELETE FROM sorts WHERE sid=2;
四. 多对多关系
1. 特点
- 多对多关系中,需要有一个专门的外键表存在,用来存储两个表的关联外键
- 多对多关系中,外键所在的表叫从表,两个多对多关系表叫主表
- 多对多表关系中,增加表数据的时候,需要先添加主表数据,然后增加从表数据。
- 多对多表关系中,删除表数据的时候,需要先删除从表数据,然后删除主表数据。
2. 示例代码
多对多关系
- 手动创建一张学生表
- 手动创建一张课程表
- 手动创建一张外键表
- 通过sql代码设置外键关联
- 测试数据增加和删除
#设置外键关联:学生表关联
ALTER TABLE fks #设置从表名称
ADD CONSTRAINT fk_students #外键约束名称
FOREIGN KEY (student_id) #外键字段
REFERENCES students(sid); #主表及关联的主键字段
#设置外键关联:课程表关联
ALTER TABLE fks #设置从表名称
ADD CONSTRAINT fk_course #外键约束名称
FOREIGN KEY (course_id) #外键字段
REFERENCES course(cid); #主表及关联的主键字段
#测试增加:需要先增加学生和课程数据,再增加中间表外键数据
INSERT INTO students(sname,phone)VALUES("张三","13312354568");
INSERT INTO course(cname)VALUES("大数据");
INSERT INTO fks(student_id,course_id)VALUES(1,2);
#删除数据:先删除外键中间表数据,再删除学生和课程数据
DELETE FROM fks WHERE student_id=1;
DELETE FROM students WHERE sid=1;
五. 两表关联查询
1. 分类
mysql数据库中,涉及到两表或者多表关联查询的时候
通常有以下几种方式:
- 内连接查询
- 外连接查询
- 自连接查询
2. 内连接查询
2.1 语法
1.隐式内连接:where关键字使用
select * from A,B where A.外键=B.主键
2.显式内连接:inner join使用
select * from A inner join B on A.外键=B.主键
3.1 示例代码
内连接查询练习
#查询所有商品和商品对应的分类信息
#隐式内连接查询:通过关联的外键和主键相等来实现
SELECT * FROM sorts ,goods
WHERE sorts.`sid`=goods.`sort_id`;
#显示内连接
SELECT * FROM sorts INNER JOIN goods
ON sorts.`sid`=goods.`sort_id`;
3. 外连接查询
3.1 语法
1.左外连接查询:
select * from A left join B on A.外键=B.主键
效果:left左边的A表数据会全部显示出来,无论是否两表有关联的数据都会显示
2.右外连接查询
select * from A right join B on A.外键=B.主键
效果:right右边的B表数据会全部显示出来,无论是否两表有关联的数据都会显示
3.2 示例
外连接查询
#左外链接查询
SELECT * FROM sorts LEFT JOIN goods
ON sorts.`sid`=goods.`sort_id`;
#右外连接查询
SELECT * FROM goods RIGHT JOIN sorts
ON sorts.`sid`=goods.`sort_id`;
4. 自连接查询
4.1 概述
所谓自连接查询,其实查询的是一张表数据,需要在执行查询的时候,虚拟出另外一张表的操作。表中主键id会和表的其中一个字段关联
4.2 示例代码
自连接查询
需要虚拟出一张表进行操作
#查询员工姓名以及员工对应的领导姓名
SELECT e.`ename` 直属领导 ,p.`ename` 员工姓名
FROM emp e,emp p
WHERE e.`empno`=p.`mgr`;