编辑代码

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)
);

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;
alter table course add year VARCHAR(4);
SHOW TABLES;


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);


SELECT s.sno, s.sname, s.dno
FROM student s
JOIN student_course sc ON s.sno = sc.sno
WHERE sc.tcid = 1 AND s.dno = '1001';


SELECT s.sno, s.sname, sc.score
FROM student s
JOIN student_course sc ON s.sno = sc.sno
WHERE sc.tcid = 1 AND sc.score > 90;


SELECT s.sno, s.sname, s.dno, sc.score
FROM student s
JOIN student_course sc ON s.sno = sc.sno
WHERE sc.tcid = 4
ORDER BY sc.score DESC;


SELECT s.sno, s.sname, c.cname, sc.score
FROM student s
JOIN student_course sc ON s.sno = sc.sno
JOIN teacher_course tc ON sc.tcid = tc.tcid
JOIN course c ON tc.cno = c.cno;


SELECT s.sno, s.sname, s.dno
FROM student s
LEFT JOIN student_course sc ON s.sno = sc.sno
WHERE sc.sno IS NULL;



SELECT *
FROM student
WHERE sno IN (
    SELECT sc.sno
    FROM student_course sc
    JOIN teacher_course tc ON sc.tcid = tc.tcid
    JOIN course c ON tc.cno = c.cno
    WHERE c.cname = '数据库原理'
);


SELECT s.sno, s.sname
FROM student s
JOIN student_course sc ON s.sno = sc.sno
WHERE sc.tcid = 1 AND sc.score = (
    SELECT MAX(score)
    FROM student_course
    WHERE tcid = 1
);


SELECT DISTINCT s.sno, s.sname
FROM student s
JOIN student_course sc ON s.sno = sc.sno
WHERE s.dno = '1001' AND sc.score > 80;


SELECT s.sno, sc.score
FROM student s
JOIN student_course sc ON s.sno = sc.sno
WHERE sc.tcid = 1 AND sc.score > (
    SELECT sc2.score
    FROM student s2
    JOIN student_course sc2 ON s2.sno = sc2.sno
    WHERE s2.sname = '李四' AND sc2.tcid = 1
);


SELECT s.sno, s.sname, s.dno
FROM student s
WHERE s.sno NOT IN (
    SELECT sno
    FROM student_course
    WHERE tcid = 1
);