编辑代码

CREATE DATABASE xs;
use xs;
CREATE TABLE kcxx(
  课程编号 CHAR(3) NOT NULL COMMENT '课程唯一编号',
  课程名称 VARCHAR(20) NOT NULL COMMENT '课程名称',
  开课学期 VARCHAR(20) DEFAULT NULL COMMENT '课程开设的学期',
  学时 TINYINT NOT NULL COMMENT '课程总学时',
  学分 TINYINT NOT NULL COMMENT '课程学分',
  PRIMARY KEY (课程编号) 
) ;

INSERT INTO kcxx VALUES 
('104', '计算机文化基础', '1', 60, 3),
('108', 'C语言程序设计', '2', 96, 5),
('202', '数据结构', '3', 72, 4),
('207', '数据库信息管理系统', '4', 72, 4),
('212', '计算机组成原理', '4', 72, 4),
('305', '数据库原理', '5', 72, 4),
('308', '软件工程', '5', 72, 4),
('312', 'Java应用与开发', '5', 96, 5);

CREATE TABLE xscj (
  学号 CHAR(6) NOT NULL,
  课程编号 CHAR(3) NOT NULL,
  成绩 TINYINT NOT NULL,
  PRIMARY KEY (学号, 课程编号) USING BTREE,
  FOREIGN KEY (课程编号) REFERENCES kcxx (课程编号)
) ;

INSERT INTO xscj VALUES 
('201601', '104', 81), ('201601', '108', 100), ('201601', '202', 89), ('201601', '207', 90),
('201602', '104', 92), ('201602', '108', 95), ('201602', '202', 93), ('201602', '207', 90),
('201603', '104', 65), ('201603', '108', 60), ('201603', '202', 69), ('201603', '207', 73),
('201604', '104', 88), ('201604', '108', 76), ('201604', '202', 80), ('201604', '207', 94),
('201605', '104', 68), ('201605', '108', 70), ('201605', '202', 89), ('201605', '207', 75),
('201606', '104', 94), ('201606', '108', 91), ('201606', '202', 93), ('201606', '207', 86),
('201607', '104', 83), ('201607', '108', 75), ('201607', '202', 80), ('201607', '207', 96),
('201701', '104', 75), ('201702', '104', 70), ('201703', '104', 90), ('201704', '104', 60),
('201705', '104', 60), ('201706', '104', 75), ('201707', '104', 90);

CREATE TABLE xsda (
  学号 CHAR(6) NOT NULL COMMENT '学生学号',
  姓名 VARCHAR(8) NOT NULL COMMENT '学生姓名',
  性别 CHAR(2) NOT NULL COMMENT '学生性别',
  系别 VARCHAR(10) NOT NULL COMMENT '学生所在系别',
  出生日期 DATE NOT NULL COMMENT '学生出生日期',
  民族 VARCHAR(4) NOT NULL COMMENT '学生民族',
  总学分 TINYINT NOT NULL COMMENT '学生总学分',
  备注 TEXT COMMENT '学生备注信息',
  PRIMARY KEY (学号) USING BTREE
) ;

INSERT INTO xsda VALUES 
('201601', '王红', '女', '信息', '1996-02-14', '汉', 60, NULL),
('201602', '刘林', '男', '信息', '1996-05-20', '汉', 54, NULL),
('201603', '曹红雷', '男', '信息', '1995-09-24', '汉', 50, NULL),
('201604', '方平', '女', '信息', '1997-08-11', '回', 52, '三好学生'),
('201605', '李伟强', '男', '信息', '1995-11-14', '汉', 60, '一门课不及格'),
('201606', '周新民', '男', '信息', '1996-01-20', '回', 62, NULL),
('201607', '王丽丽', '女', '信息', '1997-06-03', '汉', 60, NULL),
('201699', '白云', '女', '信息', '1996-10-20', '苗', 58, NULL),
('201701', '孙燕', '女', '管理', '1997-05-20', '汉', 54, NULL),
('201702', '罗德敏', '男', '管理', '1998-07-18', '汉', 64, '获得一等奖学金'),
('201703', '孔详林', '男', '管理', '1996-05-20', '汉', 54, NULL),
('201704', '王华', '女', '管理', '1997-04-16', '汉', 60, NULL),
('201705', '刘林', '男', '管理', '1996-05-30', '回', 54, NULL),
('201706', '陈希', '女', '管理', '1997-03-22', '汉', 60, '转专业'),
('201707', '李刚', '男', '管理', '1998-05-20', '汉', 54, NULL);