5.26

今日学习内容：

实验报告
1. 插入数据到 student 表
Sql
-- 插入学生数据
INSERT INTO stumanage.student (sno, sname, sex, sdept)
VALUES ('20050101', '王飞', '男', '计算机系'),
('20050102', '李丽', '女', '信息系');
2. 插入数据到 course 表
Sql
-- 插入课程数据
INSERT INTO stumanage.course (cno, cname)
VALUES ('01', '数据结构'),
('02', '数据库原理');
3. 插入数据到 sc 表
Sql；/；；；/
-- 插入选课及成绩数据
INSERT INTO stumanage.sc (sno, cno, grade)
VALUES ('20050101', '01', 70),
('20050102', '02', 90);
11. 求每门课的平均成绩
Sql
-- 查询并创建新表存储每门课的平均成绩
CREATE TABLE stumanage.course_avg_grade AS
SELECT c.cno, c.cname, AVG(s.grade) AS avg_grade
FROM stumanage.course c
JOIN stumanage.sc s ON c.cno = s.cno
GROUP BY c.cno, c.cname;
12. 更新并删除 sc 表中的记录
Sql
-- 更新成绩
UPDATE stumanage.sc
SET grade = 85
WHERE sno = '20050102' AND cno = '02';

-- 删除更新后的记录
DELETE FROM stumanage.sc
WHERE sno = '20050102' AND cno = '02';
13. 修改特定课程所有学生的成绩为 0
Sql
UPDATE stumanage.sc
SET grade = 0
WHERE cno IN (SELECT cno FROM stumanage.course WHERE cname = '数据库原理');
14. 删除特定学生的所有选课记录
Sql
DELETE FROM stumanage.sc
WHERE sno IN (SELECT sno FROM stumanage.student WHERE sname = '李丽');
15. 清空三个表的数据
Sql
TRUNCATE TABLE stumanage.student;
TRUNCATE TABLE stumanage.course;
TRUNCATE TABLE stumanage.sc;
16. 删除三个表结构
Sql
DROP TABLE stumanage.student;
DROP TABLE stumanage.course;
DROP TABLE stumanage.sc;
17. 删除数据库
Sql
DROP DATABASE stumanage;