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

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

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

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;

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

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;

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;

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;

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

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