1
-- 创建数据库
2
DROP DATABASE IF EXISTS school_management;
3
CREATE DATABASE school_management;
4
USE school_management;
5

6
-- 创建学生表
7
DROP TABLE IF EXISTS student;
8
CREATE TABLE student (
9
    sid INT PRIMARY KEY,
10
    sname VARCHAR(20) NOT NULL,
11
    gender VARCHAR(2) NOT NULL DEFAULT '男',
12
    class VARCHAR(20) NOT NULL
13
) COMMENT '学生表';
14

15
-- 创建课程表
16
DROP TABLE IF EXISTS course;
17
CREATE TABLE course (
18
    cid INT PRIMARY KEY,
19
    cname VARCHAR(30) NOT NULL,
20
    credit INT NOT NULL DEFAULT 2
21
) COMMENT '课程表';
22

23
-- 创建选课表
24
DROP TABLE IF EXISTS sc;
25
CREATE TABLE sc (
26
    scid INT PRIMARY KEY AUTO_INCREMENT,
27
    sid INT NOT NULL,
28
    cid INT NOT NULL,
29
    score INT,
30
    sc_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
31
    FOREIGN KEY(sid) REFERENCES student(sid),
32
    FOREIGN KEY(cid) REFERENCES course(cid),
33
    CONSTRAINT sid_cid_unique UNIQUE(sid, cid)
34
) COMMENT '选课表';

1
-- 插入学生数据
2
INSERT INTO student(sid, sname, gender, class) VALUES
3
(1, '张三', '男', '计算机2101'),
4
(2, '李四', '男', '计算机2101'),
5
(3, '王芳', '女', '计算机2101'),
6
(4, '赵敏', '女', '软件工程2101'),
7
(5, '刘伟', '男', '软件工程2101'),
8
(6, '陈静', '女', '计算机2102'),
9
(7, '杨洋', '男', '计算机2102'),
10
(8, '周杰', '男', '软件工程2102'),
11
(9, '林琳', '女', '软件工程2102'),
12
(10, '钱七', '男', '信息安全2101');
13

14
-- 插入课程数据
15
INSERT INTO course(cid, cname, credit) VALUES
16
(101, '高等数学', 4),
17
(102, '大学英语', 3),
18
(103, '数据结构', 3),
19
(104, '操作系统', 3),
20
(105, '数据库原理', 3),
21
(106, '计算机网络', 3);
22

23
-- 插入选课数据
24
INSERT INTO sc(sid, cid, score) VALUES
25
(1, 101, 90),
26
(1, 102, 85),
27
(1, 103, 88),
28
(2, 101, 75),
29
(2, 103, 92),
30
(3, 101, 88),
31
(3, 102, 95),
32
(3, 104, 80),
33
(4, 102, 87),
34
(4, 105, 91),
35
(5, 103, 78),
36
(5, 105, 85),
37
(6, 101, 93),
38
(6, 106, 89),
39
(7, 102, 82),
40
(7, 103, 90),
41
(8, 104, 76),
42
(8, 105, 88),
43
(9, 101, 95),
44
(9, 106, 92),
45
(10, 103, 84),
46
(10, 104, 87),
47
(10, 105, 93);