MariaDB数据库SQL语句实战（大学teaches表、student表、advisor表）

DROP TABLE IF EXISTS `teaches`;

CREATE TABLE `teaches` (
  `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,
  PRIMARY KEY (`ID`,`course_id`,`sec_id`,`semester`,`year`),
  FOREIGN KEY (`course_id`,`sec_id`,`semester`,`year`) references `section`(`course_id`,`sec_id`,`semester`,`year`) on delete restrict,
  FOREIGN KEY (`ID`) references `instructor`(`ID`) on delete RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `teaches` VALUES ('10101','CS-101','1','Fall',2009),('10101','CS-315','1','Spring',2010),('10101','CS-347','1','Fall',2009),('12121','FIN-201','1','Spring',2010),('15151','MU-199','1','Spring',2010),('22222','PHY-101','1','Fall',2009),('32343','HIS-351','1','Spring',2010),('45565','CS-101','1','Spring',2010),('45565','CS-319','1','Spring',2010),('76766','BIO-101','1','Summer',2009),('76766','BIO-301','1','Summer',2010),('83821','CS-190','1','Spring',2009),('83821','CS-190','2','Spring',2009),('83821','CS-319','2','Spring',2010),('98345','EE-181','1','Spring',2009);

 

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
  `ID` varchar(5) NOT NULL,
  `name` varchar(20) NOT NULL,
  `dept_name` varchar(20),
  `tot_cred` int,
  PRIMARY KEY (`ID`),
  FOREIGN KEY (`dept_name`) references `department`(`dept_name`) on delete RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `student` VALUES ('00000', 'ShinHwan Kang', 'Comp. Sci.', 100), ('00001', 'HoeHoon Jung', 'Comp. Sci.', 100), ('00128','Zhang','Comp. Sci.',102),('12345','Shankar','Comp. Sci.',32),('19991','Brandt','History',80),('23121','Chavez','Finance',110),('44553','Peltier','Physics',56),('45678','Levy','Physics',46),('54321','Williams','Comp. Sci.',54),('55739','Sanchez','Music',38),('70557','Snow','Physics',0),('76543','Brown','Comp. Sci.',58),('76653','Aoi','Elec. Eng.',60),('98765','Bourikas','Elec. Eng.',98),('98988','Tanaka','Biology',120);

 

DROP TABLE IF EXISTS `advisor`;

CREATE TABLE `advisor` (
  `s_ID` varchar(5) NOT NULL,
  `i_ID` varchar(5) NOT NULL,
  PRIMARY KEY (`s_ID`),
  FOREIGN KEY (`i_ID`) REFERENCES `instructor` (`ID`) on DELETE RESTRICT,
  FOREIGN KEY (`s_ID`) REFERENCES `student` (`ID`) on DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `advisor` (`s_ID`, `i_ID`) VALUES
('12345', '10101'),
('44553', '22222'),
('45678', '22222'),
('00128', '45565'),
('76543', '45565'),
('23121', '76543'),
('98988', '76766'),
('76653', '98345'),
('98765', '98345');