编辑代码

CREATE DATABASE test;
use test;
CREATE TABLE S
(
SNO VARCHAR(16) PRIMARY KEY ,
SN VARCHAR(10) NOT NULL ,
SD VARCHAR(16),
SB DATE,
SEX VARCHAR(2) DEFAULT '男',
CID VARCHAR(6),
ENDATE DATE,
CHECK (SEX IN ('男', '女'))
);

CREATE TABLE C
(
CNO VARCHAR(6) PRIMARY KEY,
CN VARCHAR(10),
PC VARCHAR(6),
FOREIGN KEY (PC) REFERENCES C(CNO)
);

CREATE TABLE SC
( SNO VARCHAR(6),
 CNO VARCHAR(6),
 GRADE DEC(4,1),
 PRIMARY KEY (SNO, CNO), 
 FOREIGN KEY (SNO) REFERENCES S(SNO)
    ON UPDATE CASCADE
    ON DELETE NO ACTION,
 FOREIGN KEY (CNO) REFERENCES C(CNO)
    ON UPDATE CASCADE
    ON DELETE NO ACTION,
 CHECK (GRADE BETWEEN 0 AND 100)
);

INSERT INTO C(CNO, CN, PC) values('C01', '高等数学', NULL);
INSERT INTO C(CNO, CN, PC) values('C11', 'C语言程序设计', NULL);
INSERT INTO C(CNO, CN, PC) values('C02', '数据结构', 'C11');
INSERT INTO C(CNO, CN, PC) values('C03', '操作系统', 'C02');
INSERT INTO C(CNO, CN, PC) values('C05', '作战指挥', 'C01');
INSERT INTO C(CNO, CN, PC) values('C06', '离散数学', 'C01');

INSERT INTO S(SNO, SN, SD, SB, SEX, CID, ENDATE) VALUES
('s01', '王玲', '计算机', '2000-06-30', '女', 'C01', '2018-09-01'),
('s02', '李渊', '计算机', '1995-03-23', '男', 'C02', '2013-09-01'),
('s03', '罗军', '计算机', '1995-08-12', '男', 'C03', '2013-09-01'),
('s04', '赵泽', '计算机', '1997-09-12', '女', 'C04', '2015-09-01'),
('s05', '许若', '自动化', '1999-06-27', '男', 'C05', '2017-09-01'),
('s06', '王仙华', '自动化', '1996-05-20', '男', 'C06', '2014-09-01'),
('s07', '朱祝', '自动化', '1998-07-10', '女', 'C07', '2016-09-01'),
('s20', '李国民', '数学', '1999-12-31', '男', 'C08', '2017-09-01'),
('s21', '陈浩然', '计算机', '2000-10-15', '男', 'C09', '2018-09-01');

INSERT INTO S (SNO, SN, SD, SB, SEX, CID, ENDATE) VALUES
('s27', '刘洋', 'sp03', '1998-05-16', '男', 'C10', '2016-09-01'),
('s28', '王芳', 'sp03', '1997-11-20', '女', 'C11', '2015-09-01'),
('s29', '张伟', 'sp01', '1996-03-10', '男', 'C12', '2014-09-01'),
('s30', '李敏', 'sp03', '2026-07-25', '女', 'C13', '2024-09-01'),
('s31', '陈强', 'sp01', '1995-04-30', '男', 'C14', '2013-09-01');

INSERT INTO SC(SNO, CNO, GRADE) values('s04', 'C06', 90.0);
INSERT INTO SC(SNO, CNO, GRADE) values('s04', 'C11', 87.0);
INSERT INTO SC(SNO, CNO, GRADE) values('s05', 'C03', 79.5);
INSERT INTO SC(SNO, CNO, GRADE) values('s05', 'C05', 88.0);
INSERT INTO SC(SNO, CNO, GRADE) values('s05', 'C02', 90.0);
INSERT INTO SC(SNO, CNO, GRADE) values('s05', 'C01', 93.0);
INSERT INTO SC(SNO, CNO, GRADE) values('s05', 'C06', 99.0);
INSERT INTO SC(SNO, CNO, GRADE) values('s05', 'C11', 72.0);
INSERT INTO SC(SNO, CNO, GRADE) values('s04', 'C02', 90.0);
INSERT INTO SC(SNO, CNO, GRADE) values('s06', 'C03', 88.0);
INSERT INTO SC(SNO, CNO, GRADE) values('s07', 'C11', 88.0);
INSERT INTO SC(SNO, CNO, GRADE) values('s21', 'C05', NULL);

UPDATE S
SET CID = 'C01'
WHERE SD = 'sp01' AND YEAR(ENDATE) = 2013;

DELETE FROM S
WHERE TIMESTAMPDIFF(YEAR, SB, CURDATE()) < 20 AND SD = 'sp03';

INSERT INTO S (SNO, SN, SEX, SB, SD, CID, ENDATE)
VALUES ('13030004', '张三', '男', '1995-08-08', 'sp03', '0302', '2013-09-01');

UPDATE S
SET ENDATE = NULL
WHERE SNO = (
    SELECT SNO FROM (
        SELECT SNO
        FROM S
        ORDER BY TIMESTAMPDIFF(YEAR, SB, CURDATE()) ASC
        LIMIT 1
    ) AS temp_table
);

UPDATE S
SET SD = 'new1'
WHERE SD = (
    SELECT SD FROM (
        SELECT SD
        FROM S
        GROUP BY SD
        ORDER BY AVG(TIMESTAMPDIFF(YEAR, SB, CURDATE())) ASC
        LIMIT 1
    ) AS temp_table
);