编辑代码

CREATE DATABASE IF NOT EXISTS student_course_management;
USE student_course_management;


CREATE TABLE SS(
    SCODE CHAR(5) PRIMARY KEY NOT NULL,
    SSNAME CHAR(16) NOT NULL
);

CREATE TABLE S (
    S_ID CHAR(9) PRIMARY KEY,
    SNAME CHAR(16) NOT NULL,
    SSEX CHAR(2) CHECK(SSEX IN ('男','女')),
    SBIRTHIN DATETIME NOT NULL,
    PLACEOFB CHAR(16),
    SCODE CHAR(5) NOT NULL,
    CLASS CHAR(6) NOT NULL,
    FOREIGN KEY (SCODE) REFERENCES SS(SCODE)
);

CREATE TABLE C (
    CID CHAR(9) PRIMARY KEY,
    CNAME CHAR(16) NOT NULL,
    CLASSH CHAR(3) NOT NULL
);

CREATE TABLE SC (
    S_ID CHAR(9),                  
    CID CHAR(9),                   
    GRADE SMALLINT DEFAULT 0,     
    PRIMARY KEY (S_ID, CID),
    FOREIGN KEY (S_ID) REFERENCES S(S_ID),
    FOREIGN KEY (CID) REFERENCES C(CID)
    );

CREATE TABLE SBC (
    SCODE CHAR(5),
    S_ID CHAR(9),
    CLASS CHAR(6),
    PRIMARY KEY (S_ID, SCODE),
    FOREIGN KEY (S_ID) REFERENCES S(S_ID),
    FOREIGN KEY (SCODE) REFERENCES SS(SCODE)
);

CREATE TABLE CS (
    SCODE CHAR(5),
    CID CHAR(9),
    PRIMARY KEY (SCODE, CID),
    FOREIGN KEY (SCODE) REFERENCES SS(SCODE),
    FOREIGN KEY (CID) REFERENCES C(CID)
);

INSERT INTO SS (SCODE, SSNAME) VALUES
('S0401', '计算机科学与技术'),
('S0402', '信息工程'),
('S0403', '网络安全'),
('S0404', '信息对抗');

INSERT INTO S (S_ID, SNAME, SSEX, SBIRTHIN, PLACEOFB, SCODE, CLASS) VALUES
('202301001', '张华', '男', '2005-01-15', '北京', 'S0401', '202301'),
('202301002', '李建平', '男', '2005-03-22', '上海', 'S0401', '202301'),
('202301003', '王丽丽', '女', '2005-08-10', '广州', 'S0401', '202301'),
('202301004', '赵强', '男', '2005-04-05', '深圳', 'S0402', '202302'),
('202301005', '陈明', '男', '2005-06-18', '杭州', 'S0402', '202302'),
('202301006', '周琳', '女', '2005-09-25', '南京', 'S0402', '202302'),
('202301007', '吴伟', '男', '2005-11-30', '成都', 'S0403', '202303'),
('202302001', '王明', '男', '2005-01-01', '北京', 'S0402', '202302'),
('202302002', '王子俊', '女', '2005-02-02', '上海', 'S0402', '202302'),
('202303001', '刘翔', '男', '2005-03-03', '广州', 'S0403', '202303');

INSERT INTO C (CID, CNAME, CLASSH) VALUES
('C401001', '数据结构', 70),
('C401002', '操作系统', 60),
('C402001', '指挥信息系统', 60),
('C402002', '数据库原理', 50),
('C403001', '计算机网络', 60),
('C403002', '网络安全', 60),
('C404001', '信息编码与加密', 60),
('C404002', '信息论', 55),
('C406002', '通信原理', 50);

INSERT INTO SC (S_ID, CID, GRADE) VALUES
('202301001', 'C401001', 90),
('202301001', 'C402002', 90),
('202301001', 'C403001', 85),
('202301002', 'C401001', 75),
('202301002', 'C402002', 88),
('202301003', 'C402002', 69),
('202302001', 'C401001', 87),
('202302001', 'C401002', 90),
('202302002', 'C403001', 92),
('202303001', 'C403001', 83);

INSERT INTO CS (SCODE, CID) VALUES
('S0401', 'C401001'),
('S0401', 'C402001'),
('S0401', 'C402002'),
('S0401', 'C403001'),
('S0402', 'C402001'),
('S0402', 'C402002'),
('S0402', 'C403001'),
('S0403', 'C403001'),
('S0403', 'C403002'),
('S0404', 'C404001'),
('S0404', 'C404002');

INSERT INTO SBC (SCODE, S_ID, CLASS) VALUES
('S0401', '202301001', '202301'),
('S0401', '202301002', '202301'),
('S0401', '202301003', '202301'),
('S0402', '202301004', '202302'),
('S0402', '202301005', '202302'),
('S0402', '202301006', '202302'),
('S0403', '202301007', '202303'),
('S0402', '202302001', '202302'),
('S0402', '202302002', '202302'),
('S0403', '202303001', '202303');

ALTER TABLE SC ADD COLUMN COMMENT VARCHAR(100) DEFAULT '无';
SELECT * FROM SC;

DROP TABLE CS;

DELETE FROM SC WHERE S_ID = '202302001';
DELETE FROM SBC WHERE S_ID = '202302001';
DELETE FROM S WHERE S_ID = '202302001';
SELECT * FROM SC;
SELECT * FROM SBC;
SELECT * FROM S;

UPDATE S 
SET SBIRTHIN = '2005-02-15', 
    PLACEOFB = '天津市'
WHERE S_ID = '202301001';
SELECT * FROM S;

SELECT 
    s.S_ID AS 学号,
    s.SNAME AS 姓名,
    SUM(sc.GRADE) AS 总成绩
FROM S s
LEFT JOIN SC sc ON s.S_ID = sc.S_ID
GROUP BY s.S_ID, s.SNAME
ORDER BY 总成绩 DESC;