编辑代码

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(14),
    entime DATE
);


CREATE TABLE course (
    cno CHAR(6) not null,
    spno CHAR(4),
    cname CHAR(20) not null,
    ctno TINYINT(1),
    lecture TINYINT(1),
    semester TINYINT(1),
    credit TINYINT(1),
    year VARCHAR(4)
);


CREATE TABLE student_course (
    sno CHAR(8) not null,
    tcid INT(2) not null,
    score TINYINT(1)
);

CREATE TABLE teacher (
    tno CHAR(5) not null,
    tname CHAR(8) not null,
    sex CHAR(2),
    birthday DATE,
    dno CHAR(6),
    pno TINYINT(1),
    home VARCHAR(40),
    zipcode CHAR(6),
    tel VARCHAR(40),
    email VARCHAR(40)
);


CREATE TABLE teacher_course (
    tcid INT(2) not null,
    tno CHAR(5),
    spno CHAR(4),
    classno CHAR(4),
    cno CHAR(6) not null,
    semester CHAR(6),
    schoolyear CHAR(10),
    classtime VARCHAR(40),
    classroom VARCHAR(40),
    weektime TINYINT(1)
);


ALTER TABLE student MODIFY birthday DATE NOT NULL;


INSERT INTO student (sno, sname, sex, native, birthday, dno, spno, classno, entime)
VALUES 
('20230001', '张三', '男', '北京', '2000-01-01', '1001', '101', '2023CS01', '2023-09-01'),
('20230002', '李四', '女', '上海', '2001-05-15', '1002', '102', '2023CS02', '2023-09-01');

INSERT INTO course (cno, spno, cname, ctno, lecture, semester, credit, year)
VALUES 
('C001', '101', '数据库原理', 1, 1, 1, 3, '2023'),
('C002', '102', '操作系统', 2, 2, 2, 4, '2023');

INSERT INTO student_course (sno, tcid, score)
VALUES 
('20230001', 1, 85),
('20230002', 2, 90);


INSERT INTO teacher (tno, tname, sex, birthday, dno, pno, home, zipcode, tel, email)
VALUES 
('T001', '王老师', '男', '1980-03-15', '1001', 1, '北京市海淀区', '100080', '13800138000', 'wang@example.com'),
('T002', '李老师', '女', '1975-07-20', '1002', 2, '上海市浦东新区', '200120', '13900139000', 'li@example.com');

INSERT INTO teacher_course (tcid, tno, spno, classno, cno, semester, schoolyear, classtime, classroom, weektime)
VALUES 
(1, 'T001', '101', 'CS01', 'C001', '1', '2023-2024', '周一 8:00-10:00', '教学楼101', 16),
(2, 'T002', '102', 'CS02', 'C002', '2', '2023-2024', '周三 10:00-12:00', '教学楼202', 16);


UPDATE student SET classno = 'C01' WHERE spno = 'sp01' AND YEAR(entime) = 2013;


DELETE FROM student WHERE spno = 'sp03' AND TIMESTAMPDIFF(YEAR, birthday, CURDATE()) < 20;

INSERT INTO student (sno, sname, sex, birthday, spno, classno, entime) VALUES ('13030004', '张三', '男', '1995-08-08', 'sp03', '0302', '2013-09-01');


UPDATE student SET entime = NULL WHERE sno = 
(SELECT sno FROM ( SELECT sno FROM student ORDER BY birthday DESC LIMIT 1 ) AS subquery );


UPDATE student SET spno = 'new1' WHERE dno = ( SELECT dno FROM ( SELECT dno, 
AVG(TIMESTAMPDIFF(YEAR, birthday, CURDATE())) AS avg_age FROM student 
GROUP BY dno ORDER BY avg_age ASC LIMIT 1) AS subquery);

SELECT * FROM student;
SELECT * FROM course;
SELECT * FROM student_course;
SELECT * FROM teacher;
SELECT * FROM teacher_course;