Mysql 一对一，一对多，多对多关系

 

-- 1. 一对多(foreign key):              
-- 学生表要关联班级表,多个学生属于一个班级. 班级是被关联的表. 一般在多的一方建立外键

create table class(
		id int primary key auto_increment,
		caption  char(20)
	);

	insert into class(caption) values('三年二班'),('一年三班'),('三年一班');

	create table student (
		id int primary key auto_increment,
		name varchar(20),
		sex enum('male','famale') default 'male',
		class_id int,
		foreign key(class_id) references class(id)
	);
    
 insert into student(name,sex,class_id) values('张三','male',1);
 insert into student(name,sex,class_id) values('小芳','famale',2);
insert into student(name,sex,class_id) values('小花','famale',3);
 
    
    select  *from class;
    
    select name,caption from student
        inner join class 
          on student.class_id = class.id;
          
    select name, sex,caption from student as s
        inner join class  as c
          on s.class_id = c.id ;
          
          
     select name,caption from student
        right join class 
          on student.class_id = class.id;

-- 2 一对一   #对这个班级来说,一门课程只有一个老师,一个老师只会教一门课程. 所以是一对一的关系
 

create table teacher(
		id int primary key auto_increment,
		tname varchar(20) 	
     );

	insert into teacher(tname) value('张老师'),('马老师#'),('曹老师');
    
 
 
	create table course (
		id int primary key auto_increment,
		cname varchar(20),
		teacher_id int unique,
		foreign key(teacher_id) references teacher(id) ON DELETE CASCADE ON UPDATE CASCADE
	);




select *from teacher;		
select *from course;		
insert into course values(1,'数学',1);
insert into course values(2,'英语',2);
drop table course;
drop table teacher;

 select tname,cname from teacher
        left join course 
          on teacher.id = course.teacher_id;         
          
delete from teacher where id =1;

-- 3.(多对多) 

 

--  多对多  https://blog.csdn.net/qq_39343376/article/details/77929411


	create table coder(
		id int primary key auto_increment,
		name varchar(20),
		salary varchar(20)
	 
	);
    
      insert into coder (name,salary) values("张三","10000"),("李四","12000");
    
 	create table project(
		id int primary key auto_increment,
		name varchar(10)
 
	);
	insert into project (name) values("工程师"),("项目经理");

	create table coder_project(
		coder_id int ,
		project_id int ,
		-- 添加外键		 
		foreign key (coder_id) references coder(id),
		foreign key (project_id )  references project(id ),
		primary key (coder_id,project_id)                    -- l联合主键保证两个至少有一个不相同

	);
    
 
    
    insert into coder_project(coder_id,project_id) values(1,1);
    insert into coder_project(coder_id,project_id) values(1,2);
    insert into coder_project(coder_id,project_id) values(2,1);
    insert into coder_project(coder_id,project_id) values(2,2);
       
     select  *from  coder_project;