这篇文章主要介绍“MySQL如何创建多对多和一对一关系”,在日常操作中,相信很多人在Mysql如何创建多对多和一对一关系问题上存在疑惑,小编查阅了各式资料,整理出简单好用的操作方法,希望对大家解答”mysql如何创建多对多和一对一关系”的疑
这篇文章主要介绍“MySQL如何创建多对多和一对一关系”,在日常操作中,相信很多人在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.idINNER 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.idINNER 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 onusers_info.user_id =users.id;
到此,关于“MySQL如何创建多对多和一对一关系”的学习就结束了,希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习,快去试试吧!若想继续学习更多相关知识,请继续关注编程网网站,小编会继续努力为大家带来更多实用的文章!
--结束END--
本文标题: MySQL如何创建多对多和一对一关系
本文链接: https://www.lsjlt.com/news/325607.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-16
2024-05-16
2024-05-16
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0