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

一、创建多对多

1.学生表

?

1 2 3 4 5 6 7

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

2.课程表

?

1 2 3 4 5 6 7

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

3.中间表

?

1 2 3 4 5 6 7 8 9 10 11 12 13

create table stu_cour (    id int not null primary key auto_increment    course_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.插入数据

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

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选了哪些科目

?

1 2 3 4 5 6 7

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

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

?

1 2 3 4 5 6 7

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

 二、MySQL 创建一对一关系

1.一对一

创建用户表：

?

1 2 3 4 5 6 7

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

用户信息表：

?

1 2 3 4 5 6 7 8 9 10 11 12

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.插入数据

?

1 2 3 4 5 6

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);

查询人的全部信息：

?

1 2	select * from users inner join users_info on users_info.user_id =users.id;

到此这篇关于MySQL 创建多对多和一对一关系方法的文章就介绍到这了,更多相关MySQL 创建多对多和一对一内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！

原文链接：https://blog.51cto.com/u_15173612/5102580