编辑代码

CREATE DATABASE students;

USE students;

CREATE TABLE stu (
    学号 CHAR(4) NOT NULL PRIMARY KEY,
    姓名 CHAR(8),
    性别 CHAR(2),
    出生日期 DATE
);

CREATE TABLE sc (
    学号 CHAR(4) NOT NULL,
    课号 CHAR(4) NOT NULL,
    成绩 DECIMAL(5, 2),
    CONSTRAINT fk_sno FOREIGN KEY (学号) REFERENCES stu(学号),
    CHECK (成绩 >= 0 AND 成绩 <= 100),
    PRIMARY KEY (学号, 课号)
);

CREATE TABLE course (
    课号 CHAR(4) NOT NULL,
    课名 CHAR(20),
    学分 INT,
    CONSTRAINT un_cname UNIQUE (课名)
);

CREATE TABLE test (
    date_time VARCHAR(50)
);

ALTER TABLE course
ADD CONSTRAINT pk_course_cno PRIMARY KEY (课号);

ALTER TABLE sc
ADD CONSTRAINT fk_cno FOREIGN KEY (课号) REFERENCES course(课号)
ON UPDATE CASCADE;

ALTER TABLE stu
ADD CONSTRAINT uq_sname UNIQUE (姓名);

ALTER TABLE course
ADD CONSTRAINT ck_xf CHECK (学分 > 0);

DELIMITER //
CREATE TRIGGER test_trig
AFTER INSERT ON stu
FOR EACH ROW
BEGIN
    INSERT INTO test (date_time) VALUES (SYSDATE());
END //
DELIMITER ;

INSERT INTO stu (学号, 姓名, 性别, 出生日期) VALUES ('0009', '李华', '男', '2001-01-01');

SELECT * FROM test;

ALTER TABLE sc
DROP FOREIGN KEY fk_cno,
DROP FOREIGN KEY fk_sno;

ALTER TABLE stu
DROP PRIMARY KEY;

ALTER TABLE course
DROP INDEX un_cname;

DELIMITER //
CREATE TRIGGER del_trig
AFTER DELETE ON course
FOR EACH ROW
BEGIN
    DELETE FROM sc WHERE 课号 = OLD.课号;
END //
DELIMITER ;

INSERT INTO course (课号, 课名, 学分) VALUES ('0006', '示例课程', 3);

INSERT INTO sc (学号, 课号, 成绩) VALUES ('0001', '0006', 85);

DELETE FROM course WHERE 课号 = '0006';

SELECT * FROM sc;