MariaDB数据库SQL语句实战（大学takes表、prereq表）

最新推荐文章于 2022-07-17 04:05:10 发布

ROBIN.FANG

最新推荐文章于 2022-07-17 04:05:10 发布

阅读量1.4k

点赞数

分类专栏： MySQL Database

本文链接：https://blog.csdn.net/weixin_38134491/article/details/88956467

版权

MySQL Database 专栏收录该内容

32 篇文章 1 订阅

订阅专栏

DROP TABLE IF EXISTS `takes`;

CREATE TABLE `takes` (
  `ID` varchar(5) NOT NULL,
  `course_id` varchar(8) NOT NULL,
  `sec_id` varchar(8) NOT NULL,
  `semester` varchar(6) NOT NULL,
  `year` int NOT NULL,
  `grade` varchar(2) DEFAULT NULL,
  PRIMARY KEY (`ID`,`course_id`,`sec_id`,`semester`,`year`),
  FOREIGN KEY (`ID`) REFERENCES `student` (`ID`) on DELETE RESTRICT,
  FOREIGN KEY (`course_id`,`sec_id`,`semester`,`year`) references `section`(`course_id`,`sec_id`,`semester`,`year`) on delete restrict
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `takes` VALUES ('00000', 'CS-347', '1', 'Fall', '2009', 'A+'), ('00001', 'CS-347', '1', 'Fall', '2009', 'A+'), ('00128','CS-101','1','Fall',2009,'A'),('00128','CS-347','1','Fall',2009,'A-'),('12345','CS-101','1','Fall',2009,'C'),('12345','CS-190','2','Spring',2009,'A'),('12345','CS-315','1','Spring',2010,'A'),('12345','CS-347','1','Fall',2009,'A'),('19991','HIS-351','1','Spring',2010,'B'),('23121','FIN-201','1','Spring',2010,'C+'),('44553','PHY-101','1','Fall',2009,'B-'),('45678','CS-101','1','Fall',2009,'F'),('45678','CS-101','1','Spring',2010,'B+'),('45678','CS-319','1','Spring',2010,'B'),('54321','CS-101','1','Fall',2009,'A-'),('54321','CS-190','2','Spring',2009,'B+'),('55739','MU-199','1','Spring',2010,'A-'),('76543','CS-101','1','Fall',2009,'A'),('76543','CS-319','2','Spring',2010,'A'),('76653','EE-181','1','Spring',2009,'C'),('98765','CS-101','1','Fall',2009,'C-'),('98765','CS-315','1','Spring',2010,'B'),('98988','BIO-101','1','Summer',2009,'A'),('98988','BIO-301','1','Summer',2010,NULL);

DROP TABLE IF EXISTS `prereq`;

CREATE TABLE `prereq` (
  `course_id` varchar(8) NOT NULL,
  `prereq_id` varchar(8) NOT NULL,
  PRIMARY KEY (`course_id`,`prereq_id`),
  FOREIGN KEY (`course_id`) REFERENCES `course` (`course_id`) on DELETE RESTRICT,
  FOREIGN KEY (`prereq_id`) REFERENCES `course` (`course_id`) on DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `prereq` VALUES ('BIO-301','BIO-101'),('BIO-399','BIO-101'),('CS-190','CS-101'),('CS-315','CS-101'),('CS-319','CS-101'),('CS-347','CS-101'),('EE-181','PHY-101');