mysql多表的关联查询

-- 一方，主表
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;