数据库管理的艺术

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (

`sno` char(7) NOT NULL,

`sname` char(10) NOT NULL,

`ssex` char(2) DEFAULT NULL,

`sage` tinyint(4) DEFAULT NULL,

`sdept` char(20) DEFAULT NULL,

PRIMARY KEY (`sno`)

) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

-- Records of student

-- ----------------------------

INSERT INTO `student` VALUES ('9512101', '李勇', '男', '19', '计算机系');

INSERT INTO `student` VALUES ('9512102', '刘晨', '男', '20', '计算机系');

INSERT INTO `student` VALUES ('9512103', '王敏', '女', '20', '计算机系');

INSERT INTO `student` VALUES ('9521101', '张立', '男', '22', '信息系');

INSERT INTO `student` VALUES ('9521102', '吴宾', '女', '21', '信息系');

INSERT INTO `student` VALUES ('9521103', '张海', '男', '20', '信息系');

INSERT INTO `student` VALUES ('9531101', '钱小平', '女', '18', '数学系');

INSERT INTO `student` VALUES ('9531102', '王大力', '男', '19', '数学系');

DROP TABLE IF EXISTS `course`;

CREATE TABLE `course` (

`cno` char(10) NOT NULL,

`cname` char(20) NOT NULL,

`ccredit` tinyint(4) DEFAULT NULL,

`semester` tinyint(4) DEFAULT NULL,

PRIMARY KEY (`cno`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of course

-- ----------------------------

INSERT INTO `course` VALUES ('c01', '计算机文化学', '3', '1');

INSERT INTO `course` VALUES ('c02', 'VB', '2', '3');

INSERT INTO `course` VALUES ('c03', '计算机网络', '4', '7');

INSERT INTO `course` VALUES ('c04', '数据库基础', '6', '6');

INSERT INTO `course` VALUES ('c05', '高等数学', '8', '2');

INSERT INTO `course` VALUES ('c06', '数据结构', '5', '4');

-- Table structure for sc

-- ----------------------------

DROP TABLE IF EXISTS `sc`;

CREATE TABLE `sc` (

`sno` char(7) NOT NULL,

`cno` char(10) NOT NULL,

`grade` tinyint(4) DEFAULT NULL,

`category` char(4) DEFAULT NULL,

PRIMARY KEY (`sno`,`cno`),

KEY `FK_CNO` (`cno`),

CONSTRAINT `FK_CNO` FOREIGN KEY (`cno`) REFERENCES `course` (`cno`),

CONSTRAINT `FK_SNO` FOREIGN KEY (`sno`) REFERENCES `student` (`sno`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of sc

-- ----------------------------

INSERT INTO `sc` VALUES ('9512101', 'c01', '90', '必修');

INSERT INTO `sc` VALUES ('9512101', 'c02', '86', '选修');

INSERT INTO `sc` VALUES ('9512101', 'c06', null, '必修');

INSERT INTO `sc` VALUES ('9512102', 'c02', '78', '选修');

INSERT INTO `sc` VALUES ('9512102', 'c04', '66', '必修');

INSERT INTO `sc` VALUES ('9521102', 'c01', '82', '选修');

INSERT INTO `sc` VALUES ('9521102', 'c02', '75', '选修');

INSERT INTO `sc` VALUES ('9521102', 'c04', '92', '必修');

INSERT INTO `sc` VALUES ('9521102', 'c05', '50', '必修');

INSERT INTO `sc` VALUES ('9521103', 'c06', null, '必修');

INSERT INTO `sc` VALUES ('9531101', 'c01', '80', '选修');

INSERT INTO `sc` VALUES ('9531101', 'c05', '95', '必修');

INSERT INTO `sc` VALUES ('9531102', 'c05', '85', '必修');