数据库系统概论Sql 练习表student、sc、course

数据库系统概论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);