How MySQL creates many-to-many and one-to-one relationships

This article mainly introduces "how MySQL creates many-to-many and one-to-one relationships". In daily operations, I believe many people have doubts about how MySQL creates many-to-many and one-to-one relationships. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful for you to answer the doubts of "how MySQL creates many-to-many and one-to-one relationships". Next, please follow the editor to study!

First, create many to many 1. Student form 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. Intermediate table 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. Insert data insert into students values (0, "Xiao Wang"); insert into students values (0, "Xiao Song"); insert into students values (0, "Xiao Li"); insert into courses values (0, "Chinese"); insert into courses values (0, "Mathematics"); insert into courses values (0, "English"); insert into stu_cour values (0meme 1); insert into stu_cour values Insert into stu_cour values (0meme2); insert into stu_cour values (0magent3); insert into stu_cour values (0meme3). Inquire which subjects student 1 has chosen 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. Find out who chose id=2 math 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

2. MySQL creates an one-to-one relationship. one-for-one

Create a user table:

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

User information table:

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 data insert into users values (0, "Xiao Wang"); insert into users values (0, "Xiao Song"); insert into users_info values (0meme12) 13812345678); insert into users_info values (014)

All the information of the inquiring person:

Select * from users inner join users_info onusers_info.user_id = users.id; this is the end of the study on "how MySQL creates many-to-many and one-to-one relationships". I hope I can solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!