MySQL|MySQL 创建多对多和一对一关系方法 MySQL创建多对多和一对一关系方

一、创建多对多

1.学生表
2.课程表
3.中间表
4.插入数据
5.查询学生1选了哪些科目
6.查询id=2数学被谁选了

二、MySQL 创建一对一关系

1.一对一
2.插入数据

一、创建多对多
1.学生表

create table students (id int not null primary key auto_increment,name varchar(45) not null)engine=innodb default charset=utf8;

2.课程表

create table courses (id int not null primary key auto_increment,name varchar(45) not null )engine=innodb default charset=utf8;

3.中间表

create table stu_cour (id int not null primary key auto_incrementcourse_id int not null,stu_id int not null,constraint cour foreign key(course_id) references courses(id),constraint stu foreign key(stu_id) references students(id))engine=innodb default charset=utf8;

4.插入数据

insert into students values (0,"小王"); insert into students values (0,"小宋"); insert into students values (0,"小李"); insert into courses values (0,"语文"); insert into courses values (0,"数学"); insert into courses values (0,"英语"); insert into stu_cour values (0,1,1); insert into stu_cour values (0,1,2); insert into stu_cour values (0,1,3); insert into stu_cour values (0,2,1); insert into stu_cour values (0,2,3); insert into stu_cour values (0,3,2); insert into stu_cour values (0,3,3);

5.查询学生1选了哪些科目

SELECT courses.id,courses.name FROM courses INNER JOIN stu_cour ON stu_cour.course_id=courses.idINNER JOIN students ON students.id= 1 and students.id = stu_cour.stu_id;

文章图片

6.查询id=2数学被谁选了

SELECT students.name FROM students INNER JOIN stu_cour ON stu_cour.stu_id =students.idINNER JOIN courses ON courses.id= 2 and stu_cour.course_id = courses.id;

文章图片

二、MySQL 创建一对一关系
1.一对一
创建用户表：

CREATE TABLE users (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50) NOT NULL)ENGINE=InnoDB DEFAULT CHARSET=utf8;

用户信息表：

CREATE TABLE users_info (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,age int NOT NULL,phone varchar(11) NOT NULL,user_id int not null,constraint user_info foreign key(user_id) references users(id))ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.插入数据

insert into users values (0,"小王"); insert into users values (0,"小宋"); insert into users_info values (0,12,'13812345678',1); insert into users_info values (0,14,'13812345679',2);

【MySQL|MySQL 创建多对多和一对一关系方法】查询人的全部信息：