【mysql 创建单表外键关联多表】创建students表关联country,position,gender三张表
create table country(
cid int primary key auto_increment,
cname varchar(10) NOT NULL
)ENGINE=INNODB DEFAULT CHARSET=utf8;
create table `position`(
pid int primary key auto_increment,
pname varchar(10) NOT NULL
)ENGINE=INNODB DEFAULT CHARSET=utf8;
create table Gender(
gid int primary key auto_increment,
gname varchar(10) NOT NULL
)ENGINE=INNODB DEFAULT CHARSET=utf8;
create table students(
sid int primary key auto_increment,
sname varchar(100) not null,
countryId int,
positionId int,
GenderId int,
Capability int,
constraint fk_stu_cid foreign key(countryId)
references country(cid),
constraint fk_stu_pid foreign key(positionId)
references `position`(pid),
constraint fk_stu_gid foreign key(GenderId)
references Gender(gid)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
insert into students(sname,countryId,positionId,GenderId,Capability) values('刘备',3,2,1,80);
insert into students(sname,countryId,positionId,GenderId,Capability) values('曹操',1,1,1,76);
idname countryId positionId GenderId Capability
刘备蜀打野男80
曹操魏top男76
孙策
孙尚香吴AD女88
诸葛亮蜀ap男87
周瑜
黄忠
关羽
张飞
赵云
小乔
大乔
貂蝉
吕布
夏侯惇insert into country(cname) values('魏');
idcountry
1魏
2蜀
3吴insert into `position`(pname) values('top');
insert into `position`(pname) values('jungle');
insert into `position`(pname) values('ap');
insert into `position`(pname) values('ad');
insert into `position`(pname) values('sup');
idposition
1top
2jungle
3ap
4ad
5supinsert into Gender(gname) values('男');
insert into Gender(gname) values('女');
idGender
1female
2male查询select * from students s,country c,position p,Gender g where s.countryId = c.cid and s.positionId = p.pid and s.GenderId = g.gid;
推荐阅读
- 数据库|SQL行转列方式优化查询性能实践
- mysql|一文深入理解mysql
- 达梦数据库|DM8表空间备份恢复
- 数据技术|一文了解Gauss数据库（开发历程、OLTP&OLAP特点、行式&列式存储，及与Oracle和AWS对比）
- SqlServer|sql server的UPDLOCK、HOLDLOCK试验
- 谈灾难恢复指标（RTO与RPO是什么鬼（））
- RPO与RTO
- 数据库|效率最高的Excel数据导入---（c#调用SSIS Package将数据库数据导入到Excel文件中【附源代码下载】）...