学生选课.sql

/*
Navicat MySQL Data Transfer

Source Server         : mysql1
Source Server Version : 50505
Source Host           : localhost:3306
Source Database       : 学生选课

Target Server Type    : MYSQL
Target Server Version : 50505
File Encoding         : 65001

Date: 2021-04-12 10:21:41
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `cno` char(4) NOT NULL,
  `cname` varchar(40) DEFAULT NULL,
  `cperiod` int(11) DEFAULT NULL,
  `credit` decimal(3,1) DEFAULT NULL,
  `ctno` char(4) DEFAULT NULL,
  PRIMARY KEY (`cno`),
  UNIQUE KEY `cname` (`cname`),
  KEY `fk_teacher_course` (`ctno`),
  CONSTRAINT `fk_teacher_course` FOREIGN KEY (`ctno`) REFERENCES `teacher` (`tno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('c001', '文学欣赏', '40', '1.5', 't001');
INSERT INTO `course` VALUES ('c002', '中国历史文化', '60', '2.0', 't003');
INSERT INTO `course` VALUES ('c003', '视频编辑', '70', '2.5', 't002');
INSERT INTO `course` VALUES ('c004', '音乐欣赏', '40', '1.5', 't005');

-- ----------------------------
-- Table structure for elective
-- ----------------------------
DROP TABLE IF EXISTS `elective`;
CREATE TABLE `elective` (
  `sno` char(8) NOT NULL,
  `cno` char(4) NOT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`sno`,`cno`),
  KEY `fk_course_elective` (`cno`),
  CONSTRAINT `fk_course_elective` FOREIGN KEY (`cno`) REFERENCES `course` (`cno`),
  CONSTRAINT `fk_studentInfo_elective` FOREIGN KEY (`sno`) REFERENCES `studentinfo` (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of elective
-- ----------------------------
INSERT INTO `elective` VALUES ('10101001', 'c001', '73');
INSERT INTO `elective` VALUES ('10101001', 'c002', '67');
INSERT INTO `elective` VALUES ('10101001', 'c004', '51');
INSERT INTO `elective` VALUES ('10101002', 'c001', '78');
INSERT INTO `elective` VALUES ('10101002', 'c003', '77');
INSERT INTO `elective` VALUES ('10101003', 'c003', '69');
INSERT INTO `elective` VALUES ('10102001', 'c001', '50');
INSERT INTO `elective` VALUES ('10102002', 'c003', '84');
INSERT INTO `elective` VALUES ('10102002', 'c004', '88');
INSERT INTO `elective` VALUES ('10102003', 'c001', '85');
INSERT INTO `elective` VALUES ('10102003', 'c002', '77');
INSERT INTO `elective` VALUES ('10102003', 'c003', '66');
INSERT INTO `elective` VALUES ('10102003', 'c004', null);
INSERT INTO `elective` VALUES ('11101001', 'c001', '49');
INSERT INTO `elective` VALUES ('11101001', 'c002', '75');
INSERT INTO `elective` VALUES ('11101001', 'c004', '69');
INSERT INTO `elective` VALUES ('11101002', 'c001', '67');
INSERT INTO `elective` VALUES ('11101002', 'c002', null);
INSERT INTO `elective` VALUES ('11101003', 'c002', '76');
INSERT INTO `elective` VALUES ('11101003', 'c003', '80');
INSERT INTO `elective` VALUES ('11101003', 'c004', '87');

-- ----------------------------
-- Table structure for studentinfo
-- ----------------------------
DROP TABLE IF EXISTS `studentinfo`;
CREATE TABLE `studentinfo` (
  `sno` char(8) NOT NULL,
  `sname` varchar(10) NOT NULL,
  `sgender` char(2) DEFAULT NULL,
  `sbirth` date DEFAULT NULL,
  `sclass` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of studentinfo
-- ----------------------------
INSERT INTO `studentinfo` VALUES ('10101001', '张永峰', '男', '1993-08-01', '电子商务101');
INSERT INTO `studentinfo` VALUES ('10101002', '何小丽', '女', '1992-11-03', '电子商务101');
INSERT INTO `studentinfo` VALUES ('10101003', '张宇', '男', '1992-08-21', '电子商务101');
INSERT INTO `studentinfo` VALUES ('10102001', '王斌', '男', '1991-07-14', '网络技术101');
INSERT INTO `studentinfo` VALUES ('10102002', '包玉明', '男', '1993-11-15', '网络技术101');
INSERT INTO `studentinfo` VALUES ('10102003', '孙平平', '女', '1992-02-27', '网络技术101');
INSERT INTO `studentinfo` VALUES ('10102004', '翁静静', '女', '1992-05-09', '网络技术101');
INSERT INTO `studentinfo` VALUES ('11101001', '刘淑芳', '女', '1994-06-10', '电子商务111');
INSERT INTO `studentinfo` VALUES ('11101002', '王亚旭', '男', '1993-03-18', '电子商务111');
INSERT INTO `studentinfo` VALUES ('11101003', '高磊', '男', '1993-05-11', '电子商务111');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `tno` char(4) NOT NULL,
  `tname` varchar(10) NOT NULL,
  `tgender` char(2) DEFAULT NULL,
  `tedu` varchar(10) DEFAULT NULL,
  `tpro` varchar(8) DEFAULT '讲师',
  PRIMARY KEY (`tno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('t001', '吴亚飞', '男', '本科', '讲师');
INSERT INTO `teacher` VALUES ('t002', '李琦', '男', '硕士研究生', '副教授');
INSERT INTO `teacher` VALUES ('t003', '王艳红', '女', '硕士研究生', '讲师');
INSERT INTO `teacher` VALUES ('t004', '马志超', '男', '博士研究生', '教授');
INSERT INTO `teacher` VALUES ('t005', '万丽', '女', '硕士研究生', '助理讲师');