编辑代码

-- 创建数据库
CREATE DATABASE test;
USE test;

-- 创建学生表
CREATE TABLE STUDENT (
    SNO CHAR(20) PRIMARY KEY,
    SNAME CHAR(20),
    GENDER CHAR(10),
    CNO CHAR(20)
);

-- 插入学生数据
INSERT INTO STUDENT (SNO, SNAME, GENDER, CNO)
VALUES ('S001', '张三', '男', 'C001'),
       ('S002', '李四', '女', 'C002'),
       ('S003', '王五', '男', 'C001'),
       ('S004', '赵六', '女', 'C002');

-- 创建班级表
CREATE TABLE CLASS (
    CNO CHAR(20) PRIMARY KEY,
    DEPARTMENT CHAR(50),
    SPECIALITY CHAR(50),
    MONITOR CHAR(20)
);

-- 插入班级数据
INSERT INTO CLASS (CNO, DEPARTMENT, SPECIALITY, MONITOR)
VALUES ('C001', '计算机学院', '计算机科学与技术', 'S001'),
       ('C002', '信息学院', '信息管理与信息系统', 'S002');

-- 创建课程表
CREATE TABLE LESSON (
    LNO CHAR(20) PRIMARY KEY,
    LNAME CHAR(50),
    BOOK CHAR(50),
    CREDIT INT
);

-- 插入课程数据
INSERT INTO LESSON (LNO, LNAME, BOOK, CREDIT)
VALUES ('L001', '数据库', '《数据库系统概论》', 4),
       ('L002', 'Java编程', '《Java核心技术》', 3),
       ('L003', '软件工程', '《软件工程导论》', 3);

-- 创建教师表
CREATE TABLE TEACHER (
    TID CHAR(20) PRIMARY KEY,
    TNAME CHAR(20),
    DEPARTMENT CHAR(50)
);

-- 插入教师数据
INSERT INTO TEACHER (TID, TNAME, DEPARTMENT)
VALUES ('T001', '王老师', '计算机学院'),
       ('T002', '李老师', '信息学院'),
       ('T003', '张老师', '计算机学院');

-- 创建选课表
CREATE TABLE ELECTION (
    CNO CHAR(20),
    LNO CHAR(20),
    TID CHAR(20),
    SYEAR CHAR(20),
    SEMESTER CHAR(20),
    PRIMARY KEY (CNO, LNO, SYEAR, SEMESTER),
    FOREIGN KEY (CNO) REFERENCES CLASS(CNO),
    FOREIGN KEY (LNO) REFERENCES LESSON(LNO),
    FOREIGN KEY (TID) REFERENCES TEACHER(TID)
);

-- 插入选课数据
INSERT INTO ELECTION (CNO, LNO, TID, SYEAR, SEMESTER)
VALUES ('C001', 'L001', 'T001', '2025', '1'),
       ('C001', 'L003', 'T001', '2025', '1'),
       ('C002', 'L002', 'T002', '2025', '2'),
       ('C002', 'L001', 'T003', '2025', '2');

-- 创建成绩表
CREATE TABLE GRADE (
    SNO CHAR(20),
    LNO CHAR(20),
    SCORE INT,
    PRIMARY KEY (SNO, LNO),
    FOREIGN KEY (SNO) REFERENCES STUDENT(SNO),
    FOREIGN KEY (LNO) REFERENCES LESSON(LNO)
);

-- 插入成绩数据
INSERT INTO GRADE (SNO, LNO, SCORE)
VALUES ('S001', 'L001', 90),
       ('S002', 'L002', 85),
       ('S002', 'L003', 92),
       ('S003', 'L001', 76);

-- 查询班长信息
SELECT s.SNO, s.SNAME, c.CNO, c.SPECIALITY
FROM STUDENT s
JOIN CLASS c ON s.CNO = c.CNO
WHERE s.SNO = c.MONITOR;

-- 查询2025年讲授过两门或两门以上课程的教师
SELECT t.TID, t.TNAME, COUNT(DISTINCT e.LNO) AS course_count
FROM TEACHER t
JOIN ELECTION e ON t.TID = e.TID
WHERE e.SYEAR = '2025'
GROUP BY t.TID, t.TNAME
HAVING COUNT(DISTINCT e.LNO) >= 2;

-- 查询选修了"数据库"但没有选修"软件工程"的班级信息
SELECT 
    c.CNO,
    c.SPECIALITY,
    COUNT(DISTINCT s.SNO) AS student_count
FROM 
    CLASS c
    JOIN STUDENT s ON c.CNO = s.CNO
    JOIN GRADE g ON s.SNO = g.SNO
    JOIN LESSON l ON g.LNO = l.LNO
WHERE 
    l.LNAME = '数据库'
    AND c.CNO NOT IN (
        SELECT DISTINCT c2.CNO
        FROM CLASS c2
        JOIN STUDENT s2 ON c2.CNO = s2.CNO
        JOIN GRADE g2 ON s2.SNO = g2.SNO
        JOIN LESSON l2 ON g2.LNO = l2.LNO
        WHERE l2.LNAME = '软件工程'
    )
GROUP BY 
    c.CNO, c.SPECIALITY;

-- 创建视图v1
CREATE VIEW v1 AS
SELECT 
    s.SNO,
    s.SNAME,
    s.GENDER,
    c.CNO,
    g.SCORE
FROM 
    STUDENT s
    JOIN CLASS c ON s.CNO = c.CNO
    JOIN GRADE g ON s.SNO = g.SNO
    JOIN LESSON l ON g.LNO = l.LNO
WHERE 
    c.DEPARTMENT = '计算机学院'
    AND l.LNAME = '数据库';

-- 查询视图v1
SELECT * FROM v1;