编辑代码

CREATE DATABASE test;
use test;
-- 创建表T
CREATE TABLE T (
Tno INT PRIMARY KEY,
Title VARCHAR(10) CHECK (Title IN ('讲师', '副教授', '教授'))
);
-- 插入一些测试数据
INSERT INTO T (Tno, Title) VALUES (1, '讲师');
INSERT INTO T (Tno, Title) VALUES (2, '副教授');
INSERT INTO T (Tno, Title) VALUES (3, '教授');

-- 查询并输出表T的内容
SELECT * FROM T;

CREATE TABLE C (
Cno INT PRIMARY KEY,
Tno INT,
FOREIGN KEY (Tno) REFERENCES T(Tno)
);

-- 插入测试数据
INSERT INTO C (Cno, Tno) VALUES (101, 1);
INSERT INTO C (Cno, Tno) VALUES (102, 2);
SELECT * FROM C;

ALTER TABLE C ADD COLUMN Cname VARCHAR(50);
ALTER TABLE C ADD CONSTRAINT unique_Cname UNIQUE (Cname);

-- 插入测试数据
INSERT INTO C (Cno, Tno, Cname) VALUES (103, 3, '课程A');
SELECT * FROM C;

ALTER TABLE C ADD COLUMN Credit INT;

-- 更新测试数据的Credit值
UPDATE C SET Credit = 3 WHERE Cno = 101;
UPDATE C SET Credit = 4 WHERE Cno = 102;
UPDATE C SET Credit = 3 WHERE Cno = 103;
SELECT * FROM C;

ALTER TABLE C DROP COLUMN Credit;
SELECT * FROM C;

ALTER TABLE C DROP INDEX unique_Cname;
SELECT * FROM C;

CREATE TABLE SC (
Sno INT,
Cno INT,
Grade DECIMAL(4, 1),
PRIMARY KEY (Sno, Cno),
FOREIGN KEY (Cno) REFERENCES C(Cno),
CHECK (Grade >= 0 AND Grade <= 100)
);

-- 插入测试数据
INSERT INTO SC (Sno, Cno, Grade) VALUES (2023001, 101, 85.5);
INSERT INTO SC (Sno, Cno, Grade) VALUES (2023002, 102, 90.0);
SELECT * FROM SC;