数据库系统概论Sql 练习表student、sc、course。
先创建一个数据库
create database sql_test; //创建名为sql_test的数据库
创建三张表student、sc、course
(1)student
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`)
); //直接复制即可
(2)course
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`)
);
(3)sc
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);