编辑代码

create database EDUC;
use EDUC;
create table student
(
    sno char(8) not null,
    sname char(8) not null,
    sex char(2),
    native char(20),
    birthday date,
    dno char(6),
    spno char(4),
    classno char(4),
    entime date,
    primary key(sno)
);
create table course
(
    cno char(6) not null,
    spno char(4),
    cname char(20) not null,
    ctno int,
    experiment int,
    lecture int,
    semester int,
    credit int,
    primary key(cno)
);
create table student_course
(
    sno char(8) not null,
    tcid int not null,
    score int,
    primary key (sno,tcid)
);
create table teacher
(
    tno char(5) not null,
    tname char(8) not null,
    sex char(2),
    birthday date,
    dno char(6),
    pno int,
    home varchar(40),
    zipcode char(6),
    tle varchar(40),
    email varchar(40),
    primary key (tno)
);
create table teacher_course
(
    tcid int not null,
    tno char(5),
    spno char(4),
    calssno char(4),
    cno char(6),
    semester char(6),
    schoolyear char(10),
    classtime varchar(40),
    classroom varchar(40),
    weektime int,
    primary key(tcid)
);
ALTER TABLE student MODIFY COLUMN birthday DATE NOT NULL;
ALTER TABLE course
ADD COLUMN year VARCHAR(4) DEFAULT '';
-- 插入学生数据
INSERT INTO student (sno, sname, sex, native, birthday, dno, spno, classno, entime)
VALUES 
('00000001', '张三', '男', '北京', '1996-01-01', '000001', '0001', '0001', '2020-09-01'),
('00000002', '李四', '女', '上海', '1997-02-02', '000001', '0001', '0001', '2020-09-01'),
('00000003', '王五', '男', '广州', '1998-03-03', '000002', '0002', '0002', '2020-09-01'),
('00000004', '赵六', '女', '深圳', '1999-04-04', '000002', '0002', '0002', '2020-09-01'),
('00000005', '孙七', '男', '杭州', '1995-05-05', '000001', '0001', '0001', '2020-09-01'),
('00000006', '周八', '女', '南京', '1996-06-06', '000002', '0002', '0002', '2020-09-01'),
('00000007', '吴九', '男', '武汉', '1997-07-07', '000001', '0001', '0001', '2020-09-01'),
('00000008', '郑十', '女', '成都', '1998-08-08', '000002', '0002', '0002', '2020-09-01'),
('00000009', '陈十一', '男', '重庆', '1996-09-09', '000001', '0001', '0001', '2020-09-01'),
('00000010', '杨十二', '女', '天津', '1997-10-10', '000002', '0002', '0002', '2020-09-01'),
('00000011', '刘十三', '男', '西安', '1998-11-11', '000001', '0001', '0001', '2020-09-01'),
('00000012', '黄十四', '女', '苏州', '1999-12-12', '000002', '0002', '0002', '2020-09-01'),
('00000013', '林十五', '男', '长沙', '1995-01-13', '000001', '0001', '0001', '2020-09-01'),
('00000014', '何十六', '女', '青岛', '1996-02-14', '000002', '0002', '0002', '2020-09-01'),
('00000015', '郭十七', '男', '宁波', '1997-03-15', '000001', '0001', '0001', '2020-09-01'),
('00000016', '马十八', '女', '沈阳', '1998-04-16', '000002', '0002', '0002', '2020-09-01');

-- 插入课程数据
INSERT INTO course (cno, spno, cname, ctno, experiment, lecture, semester, credit, year)
VALUES 
('000001', '0001', '计算机基础', 1, 20, 40, 1, 3, '2020'),
('000002', '0002', '信息管理', 2, 10, 30, 2, 2, '2020'),
('000003', '0001', '编程语言', 3, 30, 30, 1, 3, '2020'),
('000004', '0002', '数据结构', 4, 20, 20, 2, 2, '2020'),
('000005', '0001', '算法设计', 5, 25, 25, 2, 3, '2020'),
('000006', '0002', '数据库原理', 6, 15, 25, 1, 2, '2020'),
('000007', '0001', '计算机网络', 7, 20, 30, 2, 3, '2020'),
('000008', '0002', '软件工程', 8, 10, 30, 1, 2, '2020');

-- 插入教师数据
INSERT INTO teacher (tno, tname, sex, birthday, dno, pno, home, zipcode, tle, email)
VALUES 
('00001', '张老师', '男', '1970-01-01', '000001', 1, '北京', '100000', '13800138000', 'zhang@example.com'),
('00002', '李老师', '女', '1975-02-02', '000002', 2, '上海', '200000', '13900139000', 'li@example.com'),
('00003', '王老师', '男', '1972-03-03', '000001', 3, '广州', '510000', '13700137000', 'wang@example.com'),
('00004', '赵老师', '女', '1977-04-04', '000002', 4, '深圳', '518000', '13600136000', 'zhao@example.com'),
('00005', '孙老师', '男', '1973-05-05', '000001', 5, '杭州', '310000', '13500135000', 'sun@example.com'),
('00006', '周老师', '女', '1978-06-06', '000002', 6, '南京', '210000', '13400134000', 'zhou@example.com'),
('00007', '吴老师', '男', '1974-07-07', '000001', 7, '武汉', '430000', '13300133000', 'wu@example.com'),
('00008', '郑老师', '女', '1979-08-08', '000002', 8, '成都', '610000', '13200132000', 'zheng@example.com');

-- 插入教师课程数据
INSERT INTO teacher_course (tcid, tno, spno, calssno, cno, semester, schoolyear, classtime, classroom, weektime)
VALUES 
(1, '00001', '0001', '0001', '000001', '01', '2020-2021', '周一上午', 'A101', 1),
(2, '00002', '0002', '0002', '000002', '02', '2020-2021', '周二下午', 'B202', 2),
(3, '00003', '0001', '0001', '000003', '01', '2020-2021', '周三上午', 'A102', 3),
(4, '00004', '0002', '0002', '000004', '02', '2020-2021', '周四下午', 'B203', 4),
(5, '00005', '0001', '0001', '000005', '02', '2020-2021', '周五上午', 'A103', 5),
(6, '00006', '0002', '0002', '000006', '01', '2020-2021', '周一下午', 'B204', 1),
(7, '00007', '0001', '0001', '000007', '02', '2020-2021', '周二上午', 'A104', 2),
(8, '00008', '0002', '0002', '000008', '01', '2020-2021', '周三下午', 'B205', 3);

-- 插入学生课程数据
INSERT INTO student_course (sno, tcid, score)
VALUES 
('00000001', 1, 85),
('00000002', 1, 90),
('00000003', 2, 70),
('00000004', 2, 75),
('00000005', 3, 80),
('00000006', 3, 88),
('00000007', 4, 72),
('00000008', 4, 78),
('00000001', 2, 82),
('00000002', 3, 92),
('00000003', 4, 68),
('00000004', 1, 77),
('00000009', 5, 83),
('00000010', 5, 79),
('00000011', 6, 86),
('00000012', 6, 81),
('00000013', 7, 74),
('00000014', 7, 76),
('00000015', 8, 89),
('00000016', 8, 84),
('00000009', 1, 75),
('00000010', 2, 87),
('00000011', 3, 73),
('00000012', 4, 88);