create database sql_test; //创建名为sql_test的数据库
CREATE TABLE `student` ( `Sno` char(20) NOT NULL, `Sname` char(20) DEFAULT NULL, `Ssex` char(2) DEFAULT NULL, `Sage` smallint DEFAULT NULL, `Sdept` char(20) DEFAULT NULL, PRIMARY KEY (`Sno`), UNIQUE KEY `Sname` (`Sname`) ); //直接复制即可
CREATE TABLE `course` ( `Cno` char(4) NOT NULL, `Cname` char(40) NOT NULL, `Cpno` char(4) DEFAULT NULL, `Ccredit` smallint DEFAULT NULL, PRIMARY KEY (`Cno`), KEY `Cpno` (`Cpno`), CONSTRAINT `course_ibfk_1` FOREIGN KEY (`Cpno`) REFERENCES `course` (`Cno`) );
CREATE TABLE `sc` ( `Sno` char(20) NOT NULL, `Cno` char(4) NOT NULL, `Grade` smallint DEFAULT NULL, PRIMARY KEY (`Sno`,`Cno`), KEY `Cno` (`Cno`), CONSTRAINT `sc_ibfk_1` FOREIGN KEY (`Sno`) REFERENCES `student` (`Sno`), CONSTRAINT `sc_ibfk_2` FOREIGN KEY (`Cno`) REFERENCES `course` (`Cno`) );
(1)student
INSERT INTO `student` VALUES ('201215121', '李勇', '男', 20, 'CS'); INSERT INTO `student` VALUES ('201215122', '刘晨', '女', 19, 'CS'); INSERT INTO `student` VALUES ('201215123', '王敏', '女', 19, 'MA'); INSERT INTO `student` VALUES ('201215125', '张立', '男', 19, 'IS');
(2)course
insert into COURSE(Cno,Cname)values('1','数据库'); insert into COURSE(Cno,Cname)values('2','数学'); insert into COURSE(Cno,Cname)values('3','信息系统'); insert into COURSE(Cno,Cname)values('4','操作系统'); insert into COURSE(Cno,Cname)values('5','数据结构'); insert into COURSE(Cno,Cname)values('6','数据处理'); insert into COURSE(Cno,Cname)values('7','PASCAL语言'); //先插入第一段,因为参照完整性规则此表含有student表的外键不能一次性插入 update COURSE set Cpno='5',Ccredit = 4 where Cno = '1'; update COURSE set Cpno='4',Ccredit = 2 where Cno = '2'; update COURSE set Cpno='1',Ccredit = 4 where Cno = '3'; update COURSE set Cpno='6',Ccredit = 3 where Cno = '4'; update COURSE set Cpno='7',Ccredit = 4 where Cno = '5'; update COURSE set Cpno='5',Ccredit = 2 where Cno = '6'; update COURSE set Cpno='6',Ccredit = 4 where Cno = '7'; //这个是第二段
(3)sc
insert into SC(Sno,Cno,Grade) values('201215121','1',92); insert into SC(Sno,Cno,Grade) values('201215121','2',85); insert into SC(Sno,Cno,Grade) values('201215121','3',88); insert into SC(Sno,Cno,Grade) values('201215122','2',90); insert into SC(Sno,Cno,Grade) values('201215122','3',80);