第1个回答 2023-03-29
DROP DATABASE IF EXISTS `sct`;
CREATE DATABASE IF NOT EXISTS `sct`;
USE `sct`;
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`SNO` char(5) NOT NULL,
`SNAME` char(8) NOT NULL,
`SAGE` smallint(6) DEFAULT NULL,
`SSEX` char(2) DEFAULT NULL,
`SDEPT` varchar(16) DEFAULT NULL,
PRIMARY KEY (`SNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`CNO` char(2) NOT NULL,
`CNAME` varchar(10) DEFAULT NULL,
`CREDIT` smallint(6) NOT NULL,
`CPNO` char(2) DEFAULT NULL,
PRIMARY KEY (`CNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
`SNO` char(5) NOT NULL,
`CNO` char(2) NOT NULL,
`GRADE` decimal(5,1) DEFAULT NULL,
PRIMARY KEY (`SNO`,`CNO`),
KEY `sc_ibfk_2` (`CNO`),
CONSTRAINT `sc_ibfk_1` FOREIGN KEY (`SNO`) REFERENCES `student` (`sno`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `sc_ibfk_2` FOREIGN KEY (`CNO`) REFERENCES `course` (`cno`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `student` VALUES('95001','赵刚',20,'男','计算机');
INSERT INTO `student` VALUES('95002','王明',21,'女','计算机');
INSERT INTO `student` VALUES('95003','钱芳',20,'女','外语');
INSERT INTO `student` VALUES('95004','钱元昊',21,'男','外语');
INSERT INTO `student` VALUES('95005','孙东方',21,'男','通信');
INSERT INTO `student` VALUES('95006','李淑娟',19,'女','通信');
INSERT INTO `student` VALUES('95007','周一伟',20,'男','通信');
INSERT INTO `student` VALUES('95008','陈建国',21,'男','通信');
INSERT INTO `student` VALUES('95009','王语琪',19,'女','计算机');
INSERT INTO `course` VALUES ('01','数据库',4,'05'),('02','数学',3,NULL),('03','通信技术',3,'01'),('04','操作系统',4,'06'),('05','数据结构',4,'07'),('06','数据处理',2,NULL),('07','机器学习',4,'06'),('08','微观_耶',3,NULL);
INSERT INTO `sc` VALUES ('95001','01',85.0),('95001','04',90.0),('95001','05',NULL),('95002','01',70.0),('95002','04',50.0),('95002','06',40.0),('95003','02',40.0),('95003','04',70.0),('95003','07',NULL),('95004','02',95.0),('95004','04',65.0),('95004','07',NULL),('95005','03',80.0),('95005','04',54.0),('95006','01',81.0),('95006','02',78.0),('95006','03',86.0),('95007','01',89.0),('95007','02',52.0),('95007','03',77.0),('95007','04',95.0),('95008','02',73.0),('95008','03',86.0),('95008','04',67.0),
('95008','06',NULL),('95009','01',80.0),('95009','02',85.0);