文章目录
你还在手撸SQL？笑晕在厕所 一、背景
经典3表设计：
学生表 S（SNO，SNAME，AGE，SEX），其属性表示学生的学号、姓名、年龄和性别；
选课表 SC（SNO，CNO，GRADE），其属性表示学生的学号、所学课程的课程号和成绩；
课程表 C（CNO，CNAME，），其属性表示课程号、课程名称和任课教师姓名。
目标：
写一套增删查改证明绝非浪得虚名。
二、面向Chat编程 1. 数据库设计
提问：
现在你是一名资深数据库工程师，请根据以下描述设计数据库表，使用中文注释：
学生表 S（SNO，SNAME，AGE，SEX），其属性表示学生的学号、姓名、年龄和性别；选课表 SC（SNO，CNO，GRADE），其属性表示学生的学号、所学课程的课程号和成绩；课程表 C（CNO，CNAME，），其属性表示课程号、课程名称和任课教师姓名。
回答：
根据描述，可以设计三个表：学生表 S、选课表 SC 和课程表 C。
学生表 S：
字段名数据类型描述
SNO
字符串
学生的学号
SNAME
字符串
学生的姓名
AGE
整数
学生的年龄
SEX
字符串
学生的性别
选课表 SC：
字段名数据类型描述
SNO
字符串
学生的学号，外键
CNO
字符串
所学课程的课程号，外键
GRADE
浮点数
学生的成绩
课程表 C：
字段名数据类型描述
CNO
字符串
课程的课程号
CNAME
字符串
课程的名称
字符串
任课教师的姓名
 PRIMARY KEY,
SNAME VARCHAR(20) NOT NULL,
AGE INT NOT NULL,
SEX CHAR(2) NOT NULL
);
-- 创建课程表 C
CREATE TABLE C (
CNO CHAR(10) PRIMARY KEY,
CNAME VARCHAR(20) NOT NULL,
TEACHER VARCHAR(20) NOT NULL
);
-- 创建选课表 SC
CREATE TABLE SC (
SNO CHAR(10),
CNO CHAR(10),
GRADE FLOAT(3, 1),
PRIMARY KEY (SNO, CNO),
FOREIGN KEY (SNO) REFERENCES S(SNO) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (CNO) REFERENCES C(CNO) ON DELETE CASCADE ON UPDATE CASCADE
);
这些语句使用了 CHAR 和 数据类型来存储字符串，使用 INT 和 FLOAT 数据类型来存储数字。在 SC 表中， KEY 用来定义联合主键， KEY 用来定义外键，并且使用了 ON 和 ON 规则来实现级联删除和更新。
思考：
建表语句还像那么回事，就是没注释啊，差评
3. 加中文注释
提问：
针对刚才的数据库设计，在建表语句中加上中文注释
回答：
以下是基于描述的MySQL数据库建表语句，同时增加了中文注释：
-- 创建学生表 S
CREATE TABLE S (
SNO CHAR(10) PRIMARY KEY COMMENT '学生的学号',
SNAME VARCHAR(20) NOT NULL COMMENT '学生的姓名',
AGE INT NOT NULL COMMENT '学生的年龄',
SEX CHAR(2) NOT NULL COMMENT '学生的性别'
) COMMENT='学生表';
-- 创建课程表 C
CREATE TABLE C (
CNO CHAR(10) PRIMARY KEY COMMENT '课程的课程号',
CNAME VARCHAR(20) NOT NULL COMMENT '课程的名称',
TEACHER VARCHAR(20) NOT NULL COMMENT '任课教师的姓名'
) COMMENT='课程表';
-- 创建选课表 SC
CREATE TABLE SC (
SNO CHAR(10) COMMENT '学生的学号，外键',
CNO CHAR(10) COMMENT '所学课程的课程号，外键',
GRADE FLOAT(3, 1) COMMENT '学生的成绩',
PRIMARY KEY (SNO, CNO),
FOREIGN KEY (SNO) REFERENCES S(SNO) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (CNO) REFERENCES C(CNO) ON DELETE CASCADE ON UPDATE CASCADE
) COMMENT='选课表';
这些语句在每个表的注释中描述了该表的用途，同时也在每个字段的注释中说明了字段的含义。在 SC 表中，注释也说明了 SNO 和 CNO 字段是外键，并指定了参考的主键。注释可以提高代码的可读性和可维护性，让开发者更容易理解表的设计和用途。
思考：
！
然而没数据~，神啊，来点数据吧！
4. 数据模拟
提问：
根据刚才设计的表结构，写出脚本生成测试数据
回答：
以下是插入脚本，可以用来生成测试数据：
-- 插入学生数据
INSERT INTO S (SNO, SNAME, AGE, SEX) VALUES
('S001', '张三', 20, '男'),
('S002', '李四', 21, '男'),
('S003', '王五', 19, '女'),
('S004', '赵六', 20, '女'),
('S005', '钱七', 22, '男'),
('S006', '孙八', 20, '女'),
('S007', '周九', 19, '男'),
('S008', '吴十', 21, '男');
-- 插入课程数据
INSERT INTO C (CNO, CNAME, TEACHER) VALUES
('C001', '数据库系统', '张老师'),
('C002', '计算机网络', '李老师'),
('C003', '算法设计', '王老师'),
('C004', '操作系统', '赵老师'),
('C005', '编译原理', '钱老师');
-- 插入选课数据
INSERT INTO SC (SNO, CNO, GRADE) VALUES
('S001', 'C001', 80),
('S001', 'C002', 85),
('S001', 'C003', 90),
('S002', 'C001', 75),
('S002', 'C002', 85),
('S002', 'C003', 80),
('S003', 'C002', 90),
('S003', 'C003', 95),
('S004', 'C002', 80),
('S004', 'C003', 85),
('S005', 'C001', 90),
('S005', 'C002', 80),
('S005', 'C003', 85),
('S006', 'C001', 85),
('S006', 'C003', 90),
('S007', 'C003', 75),
('S008', 'C001', 80),
('S008', 'C002', 75),
('S008', 'C003', 85);
这个脚本向学生表 S，课程表 C 和选课表 SC 中分别插入了 8，5 和 18 条记录，用于模拟测试数据。这些数据包括学生的学号、姓名、年龄和性别，课程的课程号、课程名称和任课教师，以及学生选修的课程号和成绩。可以根据需要进行修改。
思考：
太强了~
现在真正的考验来了，增删查改会不会？
5. 查询成绩
提问：
查询课程 算法设计 成绩在60分以上的学生姓名、性别、年龄
回答：
以下是查询课程 “算法设计” 成绩在60分以上的学生姓名、性别、年龄的SQL语句：
SELECT S.SNAME, S.SEX, S.AGE
FROM S
INNER JOIN SC ON S.SNO = SC.SNO
INNER JOIN C ON SC.CNO = C.CNO
WHERE C.CNAME = '算法设计' AND SC.GRADE >= 60;
这个查询使用了内连接，将学生表 S，选课表 SC 和课程表 C 进行联结。通过在 WHERE 子句中设置限制条件，我们只保留选修 “算法设计” 并且成绩 >= 60 分的学生。最后，我们从结果中选择所需的学生信息，包括姓名、性别和年龄，并返回这些信息。
思考：
太强了~
提问：
写个SQL语句，查询每门课程前五名的学生
回答：
以下是查询每门课程前五名的学生的SQL语句：
SELECT C.CNO, C.CNAME, SC.SNO, S.SNAME, SC.GRADE
FROM C
INNER JOIN SC ON C.CNO = SC.CNO
INNER JOIN S ON SC.SNO = S.SNO
WHERE (
SELECT COUNT(*)
FROM SC AS SC2
WHERE SC2.CNO = SC.CNO AND SC2.GRADE > SC.GRADE
) < 5
ORDER BY C.CNO, SC.GRADE DESC;
该查询使用了一个子查询来计算每个学生在每门课程中的排名。子查询检查每个学生的成绩是否比其他学生高，并计算出他们的排名。在外层的查询中，我们可以使用子查询计算每门课程前五名的学生，并返回这些学生的成绩和其他信息。最后，使用 ORDER BY 子句按照课程号和成绩降序排序。
6. 修改课程任课老师
提问：
更改编译原理课程的老师为郑老师
回答：
以下是更改编译原理课程的老师为郑老师的 SQL 语句：
UPDATE C
SET TEACHER = '郑老师'
WHERE CNAME = '编译原理';
这个语句使用了 命令来更新课程表 C 中的记录。通过 WHERE 子句限定课程名称为 “编译原理”，然后使用 SET 子句将课程对应的老师名称修改为 “郑老师”。这样，所有 “编译原理” 课程都将对应着这位新的老师。
思考：
能分解出我的问题，不错
7. 删除课程
提问：
删除操作系统课程
回答：
以下是删除操作系统课程的 SQL 语句：
DELETE FROM C
WHERE CNAME = '操作系统';
这个语句使用了 命令从课程表 C 中删除记录。通过 WHERE 子句限定课程名称为 “操作系统”，这样只有 “操作系统” 课程对应的记录将被删除。注意，该操作将不仅删除课程表中的记录，还将删除选课表中所有选修该课程的学生的成绩记录。
思考：
这个课程没人选？换一个
8. 删除一个有关联数据的课程
提问：
删除计算机网络课程
回答：
以下是删除计算机网络课程的 SQL 语句：
DELETE FROM C
WHERE CNAME = '计算机网络';
这个语句使用了 命令从课程表 C 中删除记录。通过 WHERE 子句限定课程名称为 “计算机网络”，这样只有 “计算机网络” 课程对应的记录将被删除。注意，该操作将不仅删除课程表中的记录，还将删除选课表中所有选修该课程的学生的成绩记录。
思考：
验证成功
总结
这个经典的数据库设计和基本增删查改，对于来说，简直是小菜一碟。
而且扎心的是，每步操作都有理有据有提示，这是要干掉搜索引擎的节奏？
相关文章
