目录
实验一：建立数据库
-
购买云服务器后，启动，并使用cloudshell远程登陆
- 使用DataStudio远程连接
- 创建学籍与成绩管理系统
包含以下信息： 课程名称、课程代号、课程类型（必修、选修、任选）、学分、任课教师姓名、教师编号、教师职称、教师所属学院名称、教师所属学院代号、教师所授课程学生姓名、学生学号、学生所属学院名称、学生所属学院代号、学生所选课程、学生成绩。
-- 建立数据表
CREATE TABLE xyb (
ydh CHAR(2) PRIMARY KEY,
ymc VARCHAR(30) NOT NULL
);
CREATE TABLE xs (
xh CHAR(10) PRIMARY KEY,
xm VARCHAR(8) NOT NULL,
ydh CHAR(2),
bj VARCHAR(8),
chrq DATE,
xb VARCHAR(10),
FOREIGN KEY (ydh) REFERENCES xyb(ydh)
);
CREATE TABLE js (
jsbh CHAR(10) PRIMARY KEY,
xm VARCHAR(8) NOT NULL,
zc VARCHAR(6),
ydh CHAR(2),
FOREIGN KEY (ydh) REFERENCES xyb(ydh)
);
ALTER TABLE js
ALTER COLUMN xm TYPE VARCHAR(16);
ALTER TABLE js
ALTER COLUMN zc TYPE VARCHAR(16);
CREATE TABLE kc (
kcbh CHAR(3) PRIMARY KEY,
kc VARCHAR(20) NOT NULL,
lx VARCHAR(10),
xf NUMERIC(5,1)
);
CREATE TABLE sk (
kcbh CHAR(3),
jsbh CHAR(10),
PRIMARY KEY (kcbh, jsbh),
FOREIGN KEY (kcbh) REFERENCES kc(kcbh),
FOREIGN KEY (jsbh) REFERENCES js(jsbh)
);
CREATE TABLE xk (
xh CHAR(10),
kcbh CHAR(3),
jsbh CHAR(10),
cj NUMERIC(5,1),
PRIMARY KEY (xh, kcbh, jsbh),
FOREIGN KEY (xh) REFERENCES xs(xh),
FOREIGN KEY (kcbh, jsbh) REFERENCES sk(kcbh, jsbh)
);
CREATE INDEX idx_xs_ydh ON xs(ydh);
CREATE INDEX idx_js_ydh ON js(ydh);
CREATE INDEX idx_xk_cj ON xk(cj);实验二：数据库查询
-
插入数据
-- 插入数据
INSERT INTO xyb (ydh, ymc) VALUES
('01', '计算机学院'),
('02', '数学学院');
INSERT INTO xs (xh, xm, ydh, bj, chrq, xb) VALUES
('2023000001', '张三', '01', '计科一班', DATE '2004-09-01', '男'),
('2023000002', '李四', '01', '计科一班', DATE '2004-06-13', '女'),
('2023000003', '王五', '02', '数学一班', DATE '2003-11-23', '男'),
('2023000004', '赵六', '02', '数学二班', DATE '2004-03-03', '女'),
('2023000005', '孙七', '01', '计科二班', DATE '2005-01-17', '男');
-- 插入教师
INSERT INTO js (jsbh, xm, zc, ydh) VALUES
('T001', '王老师', '副教授', '01'),
('T002', '赵老师', '讲师', '02');
-- 插入课程
INSERT INTO kc (kcbh, kc, lx, xf) VALUES
('C01', '数据库系统', '必修', 3.0),
('C02', '高等数学', '必修', 4.0),
('C03', '操作系统', '选修', 2.0);
-- 授课关系
INSERT INTO sk (kcbh, jsbh) VALUES
('C01', 'T001'),
('C02', 'T002'),
('C03', 'T001');
-- 学生 2023000001 选课记录
INSERT INTO xk (xh, kcbh, jsbh, cj) VALUES
('2023000001', 'C01', 'T001', 85), -- 张三选了“数据库系统”
('2023000001', 'C02', 'T002', 90), -- 张三选了“高等数学”
('2023000001', 'C03', 'T001', 76); -- 张三选了“操作系统”
-- 学生 2023000002 选课记录
INSERT INTO xk (xh, kcbh, jsbh, cj) VALUES
('2023000002', 'C01', 'T001', 80), -- 李四选了“数据库系统”
('2023000002', 'C02', 'T002', 70), -- 李四选了“高等数学”
('2023000002', 'C03', 'T001', 85); -- 李四选了“操作系统”
-- 学生 2023000003 选课记录
INSERT INTO xk (xh, kcbh, jsbh, cj) VALUES
('2023000003', 'C01', 'T001', 88), -- 王五选了“数据库系统”
('2023000003', 'C02', 'T002', 75), -- 王五选了“高等数学”
('2023000003', 'C03', 'T001', 92); -- 王五选了“操作系统”
-- 学生 2023000004 选课记录
INSERT INTO xk (xh, kcbh, jsbh, cj) VALUES
('2023000004', 'C01', 'T001', 90), -- 赵六选了“数据库系统”
('2023000004', 'C02', 'T002', 60), -- 赵六选了“高等数学”
('2023000004', 'C03', 'T001', 78); -- 赵六选了“操作系统”
-- 学生 2023000005 选课记录
INSERT INTO xk (xh, kcbh, jsbh, cj) VALUES
('2023000005', 'C01', 'T001', 83), -- 孙七选了“数据库系统”
('2023000005', 'C02', 'T002', 91), -- 孙七选了“高等数学”
('2023000005', 'C03', 'T001', 87); -- 孙七选了“操作系统”-
测试外键约束：尝试插入一个不存在的外键值
-- 假设学生学号 9999999999 并不存在
-- 假设课程 C99 或教师 T99 也不存在
-- 插入一条错误记录以测试外键约束
INSERT INTO xk (xh, kcbh, jsbh, cj) VALUES
('9999999999', 'C99', 'T99', 100);实验结果:
-
查询语句练习
查询“数据库系统”这门课的任课教师姓名
SELECT js.xm
FROM js
JOIN sk ON js.jsbh = sk.jsbh
JOIN kc ON kc.kcbh = sk.kcbh
WHERE kc.kc = '数据库系统';查询结果
查询选修课程的学生学号、姓名、课程名称、成绩
SELECT xs.xh, xs.xm, kc.kc, xk.cj
FROM xk
JOIN xs ON xk.xh = xs.xh
JOIN kc ON xk.kcbh = kc.kcbh;查询结果
查询高等数学成绩在 80 分以上的学生学号、姓名及成绩
SELECT xs.xh, xs.xm, xk.cj
FROM xk
JOIN xs ON xk.xh = xs.xh
JOIN kc ON xk.kcbh = kc.kcbh
WHERE kc.kc = '高等数学' AND xk.cj >= 80;查询结果
查询张三选修课程的课程名称及对应成绩
SELECT kc.kc, xk.cj
FROM xk
JOIN xs ON xk.xh = xs.xh
JOIN kc ON xk.kcbh = kc.kcbh
WHERE xs.xm = '张三';查询结果
查询所有学生的总成绩、平均成绩，并按总成绩降序排列
SELECT xs.xh, xs.xm, SUM(xk.cj) AS 总成绩, AVG(xk.cj) AS 平均成绩
FROM xk
JOIN xs ON xk.xh = xs.xh
GROUP BY xs.xh, xs.xm
ORDER BY 总成绩 DESC;查询结果
查询没有选修“数据库系统”的学生姓名
SELECT xm
FROM xs
WHERE xh NOT IN (
SELECT xk.xh
FROM xk
JOIN kc ON xk.kcbh = kc.kcbh
WHERE kc.kc = '数据库系统'
);查询结果
无结果
查询每门课程的最高成绩
SELECT kc.kc, MAX(xk.cj) AS 最高分
FROM xk
JOIN kc ON xk.kcbh = kc.kcbh
GROUP BY kc.kc;查询结果
查询至少选修了两门课程的学生姓名及课程数
SELECT xs.xm, COUNT(*) AS 课程数
FROM xk
JOIN xs ON xk.xh = xs.xh
GROUP BY xs.xh, xs.xm
HAVING COUNT(*) >= 2;查询结果
实验三：数据库的物理设计
创建数据分区表
-
创建学生选课分区表
-- 创建主表（学生选课表），按照学号（xh）进行分区
CREATE TABLE xk_partitioned (
xh CHAR(10),
kcbh CHAR(3),
jsbh CHAR(10),
cj NUMERIC(5,1),
PRIMARY KEY (xh, kcbh, jsbh)
) PARTITION BY RANGE (xh)
(
PARTITION part_1 VALUES LESS THAN ('2023000011'), -- 学号范围 2023000001 到 2023000010
PARTITION part_2 VALUES LESS THAN ('2023000021'), -- 学号范围 2023000011 到 2023000020
PARTITION part_3 VALUES LESS THAN ('2023000031'), -- 学号范围 2023000021 到 2023000030
PARTITION part_4 VALUES LESS THAN (MAXVALUE) -- 其他学号
);-
向分区中插入数据
-- 向分区表中插入数据
INSERT INTO xk_partitioned (xh, kcbh, jsbh, cj) VALUES
('2023000001', 'C01', 'T001', 85), -- 属于分区1
('2023000002', 'C02', 'T002', 90), -- 属于分区1
('2023000003', 'C03', 'T001', 76), -- 属于分区1
('2023000004', 'C01', 'T001', 88), -- 属于分区1
('2023000022', 'C02', 'T002', 72), -- 属于分区2
('2023000023', 'C01', 'T001', 80), -- 属于分区2
('2023000024', 'C02', 'T002', 70), -- 属于分区2
('2023000032', 'C03', 'T001', 85), -- 属于分区3
('2023000033', 'C01', 'T001', 75), -- 属于分区3
('2023000034', 'C02', 'T002', 92); -- 属于分区3-
查询主表中的数据，以及分区中的数据
SELECT * FROM xk_partitioned;
-- 查询 part_1 分区的数据，学号范围 2023000001 到 2023000010
SELECT * FROM xk_partitioned PARTITION (part_1);
-- 查询 part_2 分区的数据，学号范围 2023000011 到 2023000020
SELECT * FROM xk_partitioned PARTITION (part_2);
-- 查询 part_3 分区的数据，学号范围 2023000021 到 2023000030
SELECT * FROM xk_partitioned PARTITION (part_3);
-- 查询 part_4 分区的数据，学号范围 2023000031 以后
SELECT * FROM xk_partitioned PARTITION (part_4);无结果
体会外键约束
-
创建外键约束
-- 为 xk 表添加外键约束（学号引用 xs 表，课程编号和教师编号引用 sk 表）
ALTER TABLE xk
ADD CONSTRAINT fk_xh FOREIGN KEY (xh) REFERENCES xs(xh);
ALTER TABLE xk
ADD CONSTRAINT fk_kcbh_jsbh FOREIGN KEY (kcbh, jsbh) REFERENCES sk(kcbh, jsbh);-
测试外键约束
-- 向 xk 表插入无效数据（学号 '9999999999' 不存在于 xs 表中）
INSERT INTO xk (xh, kcbh, jsbh, cj) VALUES ('9999999999', 'C01', 'T001', 85);
-- 插入数据（正确的学号和课程编号）
INSERT INTO xk (xh, kcbh, jsbh, cj) VALUES ('2023000001', 'C01', 'T001', 88);执行结果
-
添加索引
-- 在学号（xh）和课程编号（kcbh）上创建索引
CREATE INDEX idx_xh_kcbh ON xk_partitioned (xh, kcbh);
-- 在教师编号（jsbh）上创建索引
CREATE INDEX idx_jsbh ON xk_partitioned (jsbh);当数据量很大时，就会体现出索引的高效率
-
权限管理
要注意的是，需要超级用户才能创建新的用户，所以需要切换到超级用户！！！
实验四：数据库开发
视图
-
创建视图
-- 创建一个视图，显示每个学生的总学分和成绩
CREATE VIEW student_summary AS
SELECT xs.xh, xs.xm, SUM(kc.xf) AS total_credits, AVG(xk.cj) AS average_score
FROM xk
JOIN xs ON xk.xh = xs.xh
JOIN kc ON xk.kcbh = kc.kcbh
GROUP BY xs.xh, xs.xm;-
查询视图
-- 查询视图中的学生总学分和成绩
SELECT * FROM student_summary;查询结果
储存过程
-
创建存储过程：计算并保存学生的总成绩和总学分
CREATE OR REPLACE PROCEDURE chen.calculate_student_summary()
AS DECLARE BEGIN
-- 将学生的总成绩和学分保存到另一个表中
INSERT INTO student_summary (xh, xm, total_credits, average_score)
SELECT xs.xh, xs.xm,
SUM(kc.xf) AS total_credits, -- 计算总学分
AVG(xk.cj) AS average_score -- 计算平均成绩
FROM xk
JOIN xs ON xk.xh = xs.xh -- 连接学生表
JOIN kc ON xk.kcbh = kc.kcbh -- 连接课程表
GROUP BY xs.xh, xs.xm; -- 按学号和姓名分组
END;调用储存过程
-- 调用存储过程，计算并保存学生的总成绩和平均分
CALL calculate_student_summary();执行结果
注意：因为前面创建试图时，使用的名称就是student_summary，而在此创建的储存过程中也同样使用到了这个名称，但是我们知道视图并不是一张实际存在的表，只是记录了数据间的逻辑关系。所以insert操作是无法进行的，而解决方案就是，先将开始的视图删除，然后新创建一张表，进行储存过程操作。
触发器
-
创建触发器
-- 创建触发器函数：记录成绩更新操作
CREATE OR REPLACE FUNCTION record_grade_update()
RETURNS TRIGGER AS $$
BEGIN
-- 在成绩更新时记录操作
INSERT INTO grade_update_log (xh, old_grade, new_grade, update_time, update_user)
VALUES (NEW.xh, OLD.cj, NEW.cj, CURRENT_TIMESTAMP, current_user);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- 创建触发器：当学生成绩更新时触发
CREATE TRIGGER grade_update_trigger
AFTER UPDATE ON xk
FOR EACH ROW
EXECUTE FUNCTION record_grade_update();讨论视图，储存过程，触发器的使用范围以及优缺点
-
视图
- 视图主要用于简化复杂的查询。通过将复杂的 SQL 查询封装在视图中，用户可以像查询普通表一样查询视图，从而提高查询的易用性和可维护性。
- 视图可以作为数据访问的接口，限制用户对基础数据表的访问，只允许用户查询视图中的数据，从而提高数据的安全性。
- 优点：简化复杂查询、安全性、易于维护
- 缺点：性能开销比较大、视图不能直接更新
-
储存过程
- 存储过程用于封装一组 SQL 语句，处理复杂的数据操作逻辑。
- 可以通过存储过程确保一系列数据库操作要么全部成功，要么全部失败（通过事务处理）。
- 优点：储存过程是预编译（性能好）、可用于管理事务（保证操作、数据的一致性）
- 缺点：可维护性低、不同的数据库的储存过程可能不同（可迁移性弱）
-
触发器
- 实现自动化操作
- 触发器常用于自动记录数据的更改历史或日志
- 优点：能够自动执行、具有数据验证机制
- 缺点：性能开销大、触发器复杂时导致数据库操作的行为难以预测
扫一扫
1912
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
