07表与表之间的关系
1、表关系分类:
总体可以分为三类:一对一、一对多(多对一)、多对多
2、
(1)一对多
创建一
创建多,并使用外键关联一
(2)一对一
创建一,确保唯一
创建一,确保唯一,并关联上一
(3)多对多,请看代码
#//建立学生表 CREATE TABLE student( id VARCHAR(10) PRIMARY KEY, sname VARCHAR(12), age INT, sex CHAR(1) ); INSERT INTO student VALUES('S0001','王军',20,1); INSERT INTO student VALUES('S0002','张宇',21,1); INSERT INTO student VALUES('S0003','刘飞',22,1); INSERT INTO student VALUES('S0004','赵燕',18,0); INSERT INTO student VALUES('S0005','曾婷',19,0); INSERT INTO student VALUES('S0006','周慧',21,0); INSERT INTO student VALUES('S0007','小红',23,0); INSERT INTO student VALUES('S0008','杨晓',18,0); INSERT INTO student VALUES('S0009','李杰',20,1); INSERT INTO student VALUES('S0010','张良',22,1); # //建立课程表 CREATE TABLE course( id VARCHAR(10) PRIMARY KEY, sname VARCHAR(12), credit DOUBLE(2,1), teacher VARCHAR(12) ); INSERT INTO course VALUES('C001','Java',3.5,'李老师'); INSERT INTO course VALUES('C002','高等数学',5.0,'赵老师'); INSERT INTO course VALUES('C003','JavaScript',3.5,'王老师'); INSERT INTO course VALUES('C004','离散数学',3.5,'卜老师'); INSERT INTO course VALUES('C005','数据库',3.5,'廖老师'); INSERT INTO course VALUES('C006','操作系统',3.5,'张老师'); # //建立选修表 CREATE TABLE sc( sid VARCHAR(10), cid VARCHAR(10), PRIMARY KEY(sid,cid), CONSTRAINT fk_student FOREIGN KEY(sid) REFERENCES student(id), CONSTRAINT fk_course FOREIGN KEY(cid) REFERENCES course(id) ); INSERT INTO sc VALUES('S0001','C001'); INSERT INTO sc VALUES('S0001','C002'); INSERT INTO sc VALUES('S0001','C003'); INSERT INTO sc VALUES('S0002','C001'); INSERT INTO sc VALUES('S0002','C004'); INSERT INTO sc VALUES('S0003','C002'); INSERT INTO sc VALUES('S0003','C005'); INSERT INTO sc VALUES('S0004','C003'); INSERT INTO sc VALUES('S0005','C001'); INSERT INTO sc VALUES('S0006','C004'); INSERT INTO sc VALUES('S0007','C002'); INSERT INTO sc VALUES('S0008','C003'); INSERT INTO sc VALUES('S0009','C001'); INSERT INTO sc VALUES('S0009','C005');
保持对斗争的信仰!
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步