五、外键和表关系

学完了DDL，DML和DQL我们已经了解怎么去使用数据库操作库，表和怎么去查询这些字段，接下来介绍一下外键和表的关系然后再看剩下的DCL数据库控制语言

1、mysql数据库是关系型数据库，表和表会存在关系，通过外键去约束这种关系
2、外键关键字: foreign key
3、表关系:
  1、一对一 ： 人---身份证号       丈夫---妻子
  2、一对多 ： 用户---订单    一个用户对应着多个订单，一个订单只能对应着一个用户
  3、多对多 ： 学生---课程    一个学生选择多门课程， 一个课程可以被多个学生选择
4、笛卡尔积结果:两个表的数据进行交叉对应
5、外键: 在一个表中设置一个字段，参考连接着另外一张表的主键，称该字段为该表的外键

一、一对多

1、比如用户和订单是一对多的关系
2、一对多表结构分析:
在订单表中增加一个字段，设置为外键，参考连接着用户表的主键，称该字段为订单表的外键
3、用户和订单，用户是1，订单是多
4、一对多表结构的设计:
将一表看成主表，将多表看成从表，在从表中设置一个字段为外键，参考连接着主表的主键，称该字段为从表的外键

5、创建表和添加数据的sql:
create database day08 charset utf8;
use day08;
-- 用户表
create table `user`(
	uid int primary key,
	uname varchar(20)
);
-- 订单表
create table `order`(
	oid int primary key,
	oname varchar(20)
);
-- 添加数据
insert into `user` values(1,'张三'),(2,"李四");
insert into `order` values(1001,'手机'),(1002,'平板');
-- 手机订单是张三的   平板订单是李四的
-- 下面这种查询结果是笛卡尔积效果
-- (x,y)  (1,2)  x1  y2        x1 x2 y1 y2
select * from `user`,`order`;
-- 添加数据
insert into `user` values(3,'王五'),(4,"赵六");
insert into `order` values(1003,'零食'),(1004,'口红'),(1005,'玩具');

6、表创建好之后设置外键

-- 修改订单表的表结构 添加字段uid
alter table `order` add uid int;
--  表创建好之后需要设置外键
-- add 添加  constraint约束  `order_fk1`外键名 不设置mysql会自动给一个外键名称
-- foreign key外键   references参考
alter table `order` add constraint `order_fk1` foreign key (uid) references `user`(uid);

7、在架构设计器中查看表关系

8、手动设置外键

9、添加数据和删除数据的注意事项
将用户表看成主表，将订单表看成从表
添加数据: 先添加主表，在添加从表
删除数据: 先删除从表，在删除主表

10、查询
-- 查询张三有那些订单
-- 先查询张三的uid
select uid from `user` where uname = '张三'; -- 1
-- 在通过uid查询订单
select * from `order` where uid = 1;

11、整体代码
create database day08 charset utf8;
use day08;
-- 用户表
create table `user`(
	uid int primary key,
	uname varchar(20)
);
-- 订单表
create table `order`(
	oid int primary key,
	oname varchar(20)
);
-- 添加数据
insert into `user` values(1,'张三'),(2,"李四");
insert into `order` values(1001,'手机'),(1002,'平板');
-- 手机订单是张三的   平板订单是李四的
-- 下面这种查询结果是笛卡尔积效果
-- (x,y)  (1,2)  x1  y2        x1 x2 y1 y2
select * from `user`,`order`;
-- 添加数据
insert into `user` values(3,'王五'),(4,"赵六");
insert into `order` values(1003,'零食'),(1004,'口红'),(1005,'玩具');


-- 修改订单表的表结构 添加字段uid
alter table `order` add uid int;
--  表创建好之后需要设置外键
-- add 添加  constraint约束  `order_fk1`外键名 不设置mysql会自动给一个外键名称
-- foreign key外键   references参考
alter table `order` add constraint `order_fk1` foreign key (uid) references `user`(uid);


-- 查询张三有那些订单
-- 先查询张三的uid
select uid from `user` where uname = '张三'; -- 1
-- 在通过uid查询订单
select * from `order` where uid = 1;

二、多对多

1、学生—课程 多对多的关系

2、多对多的表结构设计

• 将多对多拆成两个一对多进行处理
• 不改变原来两张表的表结构，创建一个新的中间表(表1_表2)，中间表里面设置两个字段(sid,cid)，将sid设置为外键，连接参考着学生表主键，将cid也设置为外键，连接着课程表的主键
• 将表1和表2看成主表，将中间表看成从表
  添加数据的时候，先添加主表，在添加从表
  删除数据的时候，先删除从表，在删除主表

4、创建表

-- 多对多
USE day07;
-- 创建学生表

CREATE TABLE student(
	sid INT PRIMARY KEY,
	sname VARCHAR(20)
);
-- 课程表
CREATE TABLE course(
	cid INT PRIMARY KEY,
	cname VARCHAR(20)
);
-- 中间表
CREATE TABLE student_course(
	sid INT,
	cid INT,
	-- 在创建表设置外键
	CONSTRAINT fk_sc1 FOREIGN KEY(sid) REFERENCES student(sid),
	CONSTRAINT fk_sc2 FOREIGN KEY(cid) REFERENCES course(cid)
);

5、在架构设计器查看表关系

6、手动设置外键

7、查询

/*
	多对多
	1、表结构设计
		在不改变两张表的表结构，创建一个新的中间表(表1_表2)，在中间表中
		新建两个字段，sid 和 cid，sid去参考连接着student表的主键，cid参考连接
		着课程表的主键
*/
create table student(
	sid int primary key,
	sname varchar(20)
);
create table course(
	cid int primary key,
	cname varchar(20)
);
create table student_course(
	sid int,
	cid int,
	-- 创建表时添加外键
	constraint `sc_fk01` foreign key(sid) references student(sid),
	constraint `sc_fk02` foreign key(cid) references course(cid)
);

-- 添加数据
insert into student values(1,'张三'),(2,'李四'),(3,'金宝'),(4,'杨老板');
insert into course values(1001,'java'),(1002,'厨师'),(1003,'挖掘机'),(1004,'美容美发'),(1005,'盲人按摩');
insert into student_course values(1,1002),(1,1004),(2,1003),(2,1005),(3,1001),(3,1003),
(4,1001),(4,1002),(4,1002),(4,1004),(4,1005);

-- 设置外键之后  添加数据和删除数据注意事项
/*
	把学生表和课程表看成主表，中间表看成从表
	添加数据: 先添加主表，在添加从表
	删除数据:先删除从表，在删除主表
*/
-- 查询张三所选的课程
-- 隐式内连接
select * 
from student s,student_course sc,course c
where s.sid = sc.sid
and sc.cid = c.cid
and s.sname = '张三';

-- 显示内连接
select *
from student s
inner join student_course sc
on s.sid = sc.sid
inner join course c
on sc.cid = c.cid
where s.sname = '张三';

select * 
from student s
inner join student_course sc
inner join course c
on s.sid = sc.sid 
and sc.cid = c.cid
where s.sname = '张三';

三、一对一

丈夫 — 妻子

/*
	丈夫----妻子
	将妻子表的主键设置为外键，参考连接着丈夫表的主键，称该字段为外键
*/
create table husband(
	hid int primary key,
	hname varchar(20)
);
create table wife(
	wid int primary key,
	wname varchar(20),
	constraint `wife_fk01` foreign key(wid) references husband(hid)
);
-- 查询 杨过的妻子是谁
select * from husband h,wife w where h.hid = w.wid and h.hname = '杨过';