1、多表关系
在数据表中，各个表结构之间存在着各种关系（一对一、一对多、多对多）。
一对一关系：
示例：学生与学生详情的关系，一个学生对应一个详细情况，一个详细情况对应一个学生。
实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的UNIQUE。
一对多关系：
示例：学生与班级的关系，一个班级对应多个学生，一个学生对应一个班级。
实现：在一对多关系中，在多的一方建立外键，指向一的一方的主键。
多对多关系：
示例：学生与课程的关系，一个学生可以选修多门课程，一门课程也可以给多个学生选择。
实现：在多对多关系中，建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
代码语言：javascript复制-- 一方，主表
CREATE TABLE dept(
d_id INT PRIMARY KEY AUTO_INCREMENT,
d_name VARCHAR(20),
d_location VARCHAR(20)
);
-- 创建员工表
-- 多方，从表
CREATE TABLE emp(
e_id INT PRIMARY KEY AUTO_INCREMENT,
e_name VARCHAR(20)not null,
e_age INT,
d_id INT, -- 外键对应主表的主键
-- CONSTRAINT emp_ibfk_1 可以省略不写
-- CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`d_id`) REFERENCES `dept` (`d_id`)
FOREIGN KEY (`d_id`) REFERENCES `dept` (`d_id`)
);
-- 添加 2 个部门
INSERT INTO dept VALUES(NULL, '研发部','广州'),(NULL, '销售部', '深圳');
SELECT * FROM dept;
-- 添加员工,dep_id 表示员工所在的部门
INSERT INTO emp (e_name, e_age, d_id) VALUES ('张三', 20, 3);
INSERT INTO emp (e_name, e_age, d_id) VALUES ('李四', 21, 3);
INSERT INTO emp (e_name, e_age, d_id) VALUES ('王五', 20, 3);
INSERT INTO emp (e_name, e_age, d_id) VALUES ('老王', 20, 4);
INSERT INTO emp (e_name, e_age, d_id) VALUES ('大王', 22, 4);
INSERT INTO emp (e_name, e_age, d_id) VALUES ('小王', 18, 4);
SELECT * FROM emp;
-- 1.笛卡尔乘积
-- 加限定条件，解决了笛卡尔乘积：数据冗余问题。
select * from dept,emp where dept.d_id=emp.d_id;
# 2.注意事项：
-- 1） 主表不能删除从表已引用的数据
DELETE from dept where d_id=1;
-- 2） 从表不能添加主表未拥有的数据
INSERT INTO emp (e_name, e_age, d_id) VALUES ('小王', 18, 3);
-- 3） 先添加主表数据再添加从表数据
-- 也成主从表为父子表;
-- 4） 先删除从表数据再删除主表数据
-- 正确逻辑
-- 5） 外键约束允许为空但不能是错的
update emp set d_id=null where e_id=1;
select * from emp;
DELETE from dept where d_id=2;
--
-- 1学生表
create table student(
s_id int primary key auto_increment,
s_name varchar(20),
s_city varchar(20),
s_age int
);
-- 2老师表
create table teacher(
t_id int primary key auto_increment,
t_name varchar(20)
);
-- 3课程表
create table course(
c_id int primary key auto_increment,
c_name varchar(20),
t_id int,
foreign key(t_id) references teacher (t_id)
);
-- 4学生课程表
create table stu_course(
id int primary key auto_increment,
score double,
s_id int,
c_id int,
foreign key(s_id) references student(s_id),
foreign key(c_id) references course(c_id)
);
-- ctrl+h
-- teacher
INSERT INTO `zhucong`.`teacher`(`t_id`, `t_name`) VALUES (1, '张老师');
INSERT INTO `zhucong`.`teacher`(`t_id`, `t_name`) VALUES (2, '王老师');
INSERT INTO `zhucong`.`teacher`(`t_id`, `t_name`) VALUES (3, '李老师');
INSERT INTO `zhucong`.`teacher`(`t_id`, `t_name`) VALUES (4, '江老师');
-- student
INSERT INTO `zhucong`.`student`(`s_id`, `s_name`, `s_city`, `s_age`) VALUES (1, 'jack', '上海', 88);
INSERT INTO `zhucong`.`student`(`s_id`, `s_name`, `s_city`, `s_age`) VALUES (2, 'rose', '上海', 99);
INSERT INTO `zhucong`.`student`(`s_id`, `s_name`, `s_city`, `s_age`) VALUES (3, 'tom', '郑州', 66);
INSERT INTO `zhucong`.`student`(`s_id`, `s_name`, `s_city`, `s_age`) VALUES (4, 'yiyan', '北京', 27);
-- course
INSERT INTO `zhucong`.`course`(`c_id`, `c_name`, `t_id`) VALUES (1, '语文', 1);
INSERT INTO `zhucong`.`course`(`c_id`, `c_name`, `t_id`) VALUES (2, '数学', 1);
INSERT INTO `zhucong`.`course`(`c_id`, `c_name`, `t_id`) VALUES (3, '物理', 2);
INSERT INTO `zhucong`.`course`(`c_id`, `c_name`, `t_id`) VALUES (4, '生物', 2);
INSERT INTO `zhucong`.`course`(`c_id`, `c_name`, `t_id`) VALUES (5, '英语', 3);
INSERT INTO `zhucong`.`course`(`c_id`, `c_name`, `t_id`) VALUES (6, '前端', NULL);
-- stu_course
INSERT INTO `zhucong`.`stu_course`(`id`, `score`, `s_id`, `c_id`) VALUES (1, 89, 1, 1);
INSERT INTO `zhucong`.`stu_course`(`id`, `score`, `s_id`, `c_id`) VALUES (2, 90, 1, 2);
INSERT INTO `zhucong`.`stu_course`(`id`, `score`, `s_id`, `c_id`) VALUES (3, 78, 2, 1);
INSERT INTO `zhucong`.`stu_course`(`id`, `score`, `s_id`, `c_id`) VALUES (4, 88, 3, 4);
INSERT INTO `zhucong`.`stu_course`(`id`, `score`, `s_id`, `c_id`) VALUES (5, 99, 4, 3);
INSERT INTO `zhucong`.`stu_course`(`id`, `score`, `s_id`, `c_id`) VALUES (6, 67, 3, 3);
INSERT INTO `zhucong`.`stu_course`(`id`, `score`, `s_id`, `c_id`) VALUES (7, 100, 4, 4);
-- 1.老师教了什么课 2个表
SELECT t.t_name,c.c_id,c.c_name
FROM teacher t,course c -- 表加别名；
where t.t_id=c.t_id; -- 两个表的等值条件;
-- 2.学生选了什么课，每一课得了多少分 3个表
SELECT s.s_name 学生姓名,c.c_name 课程名,sc.score 成绩
from student s,stu_course sc,course c
where s.s_id=sc.s_id and sc.c_id=c.c_id;
-- 3.学生选了什么课，每一课得了多少分，对应的代课老师是谁 4个表
SELECT s.s_name 学生姓名,c.c_name 课程名,sc.score 成绩,t.t_name 授课教师
from student s,stu_course sc,course c,teacher t
where s.s_id=sc.s_id and sc.c_id=c.c_id and t.t_id=c.t_id;