实验四:数据库开发
实验任务:
-
建立“学籍与成绩管理系统”表格;
- 不建立表之间的参照关系
- 输入数据,以便在表上进行各种操作
-
视图
- 计算学生的总学分、总成绩;
- 建立适当的视图,使得可以直接单表查询就可以知道学生的总学分、总成绩;
- 建立适当的视图,将所有的表连接起来,观察数据,体会建立多个表的好处;
- 建立单表的视图,练习通过视图来更新、删除数据;
- 建立多表的视图,练习通过视图来更新、删除数据;
- 计算学生的总学分、总成绩;
-
存储过程
- 在“学籍与成绩管理系统”表格中输入不符合系统要求的数据(如学生学籍表中学号重复),建立适当的存储过程,分别查找和删除这些不合法的数据;
- 建立适当的存储过程,计算学生的总学分、总成绩,并保存在另外一张表中;
- 查询总成绩表,并进行排序。
以下实验使用的相关软件和参考文档可以通过这个网盘地址下载:
链接: https://pan.baidu.com/s/1Utlmw6Gfrspybw-eV88eEw
提取码: 3g8z
实验步骤:
一:将数据库恢复初始状态,建立无参照关系的表格
这次建立的表数据和实验一相同,只不过这回表中无主键和外键,表中也不存在索引。
DROP TABLE IF EXISTS xk CASCADE;
DROP TABLE IF EXISTS sk CASCADE;
DROP TABLE IF EXISTS js CASCADE;
DROP TABLE IF EXISTS kc CASCADE;
DROP TABLE IF EXISTS xs CASCADE;
DROP TABLE IF EXISTS xyb CASCADE;
CREATE TABLE xyb(
ydh VARCHAR(2) NOT NULL,
ymc VARCHAR(30) NOT NULL
);
CREATE TABLE xs(
xm VARCHAR(24) NOT NULL,
xh VARCHAR(10) NOT NULL,
ydh VARCHAR(2),
bj VARCHAR(8),
chrq DATE,
xb VARCHAR(6)
);
CREATE TABLE kc(
kcbh VARCHAR(3) NOT NULL,
kc VARCHAR(48) NOT NULL,
lx VARCHAR(12),
xf DECIMAL(5,1)
);
CREATE TABLE js(
xm VARCHAR(24) NOT NULL,
jsbh VARCHAR(10) NOT NULL,
zc VARCHAR(18),
ydh VARCHAR(2)
);
CREATE TABLE sk(
kcbh VARCHAR(3) NOT NULL,
bh VARCHAR(10) NOT NULL
);
CREATE TABLE xk(
xh VARCHAR(10) NOT NULL,
kcbh VARCHAR(3) NOT NULL,
jsbh VARCHAR(10) NOT NULL,
cj DECIMAL(5,1)
);
由于我们对表进行了删除和重新建立,因此我们需要重新将数据插入进去(插入数据详情见实验三)。同理,我们也可以把这个插入过程定义为一个存储过程proc_restore_database_2()
;
然后要调用这个函数的话,直接使用以下语句即可。
CALL proc_restore_database_2();
执行这个函数之后,数据库就变回这个实验的初始状态。
二:建立视图
1. 建立适当的视图,使得可以直接单表查询就可以知道学生的总学分、总成绩;
我们先创建名字为cj_view的视图,其中cj_view代表各个学生选择课程最高分的那次成绩,以及课程对应的学分。
sql源码如下:
DROP VIEW IF EXISTS cj_view;
CREATE VIEW cj_view(xm,xh,kc,kcbh,xf,cj) AS (
SELECT xm,xh,kc,kcbh,NVL(xf,0),NVL(MAX(cj),0) FROM (
SELECT xs.xm,xs.xh,kc.kc,kc.kcbh,kc.xf,xk.cj FROM (
xs LEFT JOIN xk ON (xs.xh = xk.xh) LEFT JOIN kc ON (xk.kcbh = kc.kcbh)
)
) GROUP BY (xm,xh,kc,kcbh,xf)
);
然后我们先通过SELECT * FROM cj_view
观察所有的数据:
之后,我们可以运行以下sql语句查询各个学生的总学分、总成绩,按照总成绩,总学分从高到低排序:
SELECT
xm,
xh,
sum(xf) AS zxf,
CASE WHEN sum(xf) = 0 THEN 0 ELSE ROUND(sum(cj * xf)/sum(xf),3) END AS zcj
FROM cj_view
GROUP BY xm,xh
ORDER BY zcj DESC,zxf DESC;
代码运行结果为:
由此可见,建立适当的视图可以很容易直接进行单表查询就可以知道总学分,总成绩以及对应排名,大大简化代码,提高编写效率。
2. 建立适当的视图,将所有的表连接起来,观察数据,体会建立多个表的好处;
通过多层JOIN语句,可以建立适当的视图将所有的表连接起来。
这里我们假设以同学的选课表为主体,所有的表连接起来的结果为:
(学生姓名,学生学号,学生学院名称,学生学院代号,班级,出生日期,性别,课程,课程编号,授课教师姓名,教师编号,教师学院名称,教师学院代号,教师职称,课程类型,课程学分,学生成绩)
sql源码如下:
DROP VIEW IF EXISTS showall;
CREATE VIEW showall AS (
SELECT
xs.xm AS xs_xm,
xs.xh AS xs_xh,
xyb_1.ymc AS xs_ymc,
xs.ydh AS xs_ydh,
xs.bj,
xs.chrq,
xs.xb,
kc.kc,
kc.kcbh,
js.xm AS js_xm,
js.jsbh,
xyb_2.ymc AS js_ymc,
js.ydh AS js_ydh,
js.zc,
kc.lx,
kc.xf,
xk.cj
FROM (
xs JOIN xyb AS xyb_1 ON (xs.ydh = xyb_1.ydh)
JOIN xk ON (xs.xh = xk.xh)
JOIN kc ON (xk.kcbh = kc.kcbh)
JOIN js ON (js.jsbh = xk.jsbh)
JOIN xyb AS xyb_2 ON (js.ydh = xyb_2.ydh)
)
ORDER BY (xs_xm,js_xm)
);
然后运行SELECT * FROM cj_view
,结果如下:
可以看到所有的表都已经连接了起来。
在实验感受中将会详细阐述建立多个表的好处。
3. 建立单表的视图,练习通过视图来更新、删除数据;
我们假设这样的情景:
P1. 查找分数<60分的成绩,对他们的成绩进行“开根号再乘以十”的操作。
P2. 进行完P1操作后,再查找分数<60分的成绩,并且删除这些数据。
由于我们只是对于成绩进行操作,因此我们不关心课程名称,我们只需要提取学号,课程编号,课程成绩这一栏即可。
这时候,我们可以建立以下的单表视图。为了更方便调试输出结果,我们可以先调用将数据库恢复到初始状态的存储过程。
/*Restore the database*/
CALL proc_restore_database_2();
/*Create single view*/
CREATE OR REPLACE VIEW single_view("学生学号","课程编号","课程成绩") AS (
SELECT xh,kcbh,cj FROM xk
);
/*Select Data from single view*/
SELECT * FROM single_view;
输出结果如下图所示,我们注意到single_view('学生学号','课程编号','课程成绩')
相当于对返回的内容进行重命名。
由于opengauss使用的数据库视图默认为只读模式,不支持直接更新和删除操作,因此要想使用视图来更新、删除数据,必须使用规则(Rule)实现。
因此,我们还需要定义对于更新和删除操作的规则。
CREATE OR REPLACE RULE single_view_UPDATE AS ON UPDATE TO single_view DO INSTEAD (
UPDATE xk SET cj = NEW."课程成绩" WHERE (xk.xh,xk.kcbh) = (OLD."学生学号",OLD."课程编号")
);
CREATE OR REPLACE RULE single_view_DELETE AS ON DELETE TO single_view DO INSTEAD (
DELETE FROM xk WHERE (xk.xh,xk.kcbh) = (OLD."学生学号",OLD."课程编号")
);
定义完这些规则之后,我们就可以直接对视图中的数据进行更改。
P1. 找到分数<60分的成绩,对他们的成绩进行“开根号再乘以十”的操作。
相关语句如下:
SELECT * FROM single_view WHERE "学生学号" = '1887592443';
UPDATE single_view
SET "课程成绩" = sqrt("课程成绩") * 10
WHERE "课程成绩" < 60;
SELECT * FROM single_view WHERE "学生学号" = '1887592443';
在执行这个语句的同时,我们注意到学生学号为1887592443
同学的信息变化:
执行上述操作后,输出结果为:
可以知道这条信息已经更新,并且这科小于60分的课程成绩已经成功进行了操作。
( 11.4 ≈ 1.3 × 10 11.4 \approx \sqrt{1.3} \times 10 11.4≈1.3×10)
之后我们关注原表信息的变化:
SELECT * FROM xk WHERE xh = '1887592443';
可知在视图上的更新操作已经成功作用到原表上:
由此证明视图不是一个真实的表,而是虚拟的表,而视图中存储的数据实际上都在原表中。
P2. 进行完P1操作后,再查找分数<60分的成绩,并且删除这些数据。
SELECT * FROM single_view WHERE "学生学号" = '1887592443';
DELETE FROM single_view WHERE "课程成绩" < 60;
SELECT * FROM single_view WHERE "学生学号" = '1887592443';
我们还是关注学号1887592443
:
执行上述操作后,输出结果为:
然后再去查询原表的信息,可以发现其中的行数比原来要少,且已经没有60分的数据:
建立单表视图以及在其上进行更新、删除数据的工作完成。
4. 建立多表的视图,练习通过视图来更新、删除数据;
在openGauss直接执行带有分号嵌套的语句会出现问题(不能定义用分号分隔的多条命令),因此以下的操作均在putty中编写相应的sql语句进行。
使用SELECT和WHERE语句可以同时在多个表中查询信息,从而建立多表视图,比如每位同学的姓名,学号,选择课程的编号和对应成绩。
下面是一个多表视图的例子。
我们先在控制台中创建名字为Multi_View.sql
的文件:
CALL proc_restore_database_2();
DROP VIEW IF EXISTS multi_view;
CREATE OR REPLACE VIEW multi_view("学生姓名","学生学号","课程编号","课程成绩")
AS (
SELECT xs.xm,xs.xh,kcbh,cj FROM xs,xk WHERE xs.xh = xk.xh
);
CREATE OR REPLACE RULE multi_view_UPDATE AS ON UPDATE TO multi_view DO INSTEAD (
UPDATE xs SET xm = NEW."学生姓名" WHERE xs.xh = OLD."学生学号";
UPDATE xk SET cj = NEW."课程成绩" WHERE (xk.xh,xk.kcbh) = (OLD."学生学号",OLD."课程编号");
);
注:以上的语句如果直接复制到Data Studio运行,则会出现以下的问题:
问题产生的根本原因是Data Studio没法区分在语句中的分号以及在末尾的分号,导致Data Studio在选择这些语句时,遇到第一个UPDATE语句的分号就会误认为语句已经输入结束,从而产生syntax error。
因此在输入这样的带分号嵌套的语句时,尽量使用控制台以及sql文件,而不是依赖于Data Studio客户端的调试功能。
这个问题解决的参考:RULE with multiple commands
以下是在控制台中运行的结果:
接下来我们想要进行以下操作:
- 在这个视图中找到成绩小于60的课程成绩,将这些成绩的值更改为NULL,同时在对应的学生姓名后面加上_F(代表学生挂过科)。
则相应代码如下所示:
UPDATE multi_view
SET "课程成绩" = NULL, "学生姓名" = "学生姓名" || '_F'
WHERE "课程成绩" < 60;
因为我们已经定义好了视图和相应规则,这条语句在控制台或者Data Studio运行均可。
在Data Studio的实验结果为:
我们可以看到xk表的成绩,以及xs表的姓名都发生了更新。
同理,如果我们要进行以下操作:
- 在这个视图中找到成绩小于60的课程成绩,将这个课程成绩删除。
则相应代码为:
CREATE OR REPLACE RULE multi_view_DELETE AS ON DELETE TO multi_view DO INSTEAD (
DELETE FROM xk WHERE (xk.xh,xk.kcbh) = (OLD."学生学号",OLD."课程编号")
);
DELETE FROM multi_view WHERE "课程成绩" < 60;
实验结果如下:
建立多表视图以及在其上进行更新、删除数据的工作完成。
三:建立存储过程
1. 在“学籍与成绩管理系统”表格中输入不符合系统要求的数据(如学生学籍表中学号重复),建立适当的存储过程,分别查找和删除这些不合法的数据;
对于查找和删除操作,我们可以定义以下的存储过程:
DROP PROCEDURE IF EXISTS delete_duplicates;
CREATE PROCEDURE delete_duplicates() AS
BEGIN
DELETE FROM xs WHERE xh IN(
SELECT xh FROM (
SELECT MAX(xh) as xh,COUNT(xh) AS cnt
FROM xs
GROUP BY xh
) WHERE cnt > 1
);
END;
为了验证这个存储过程的正确性,我们先在学生中插入一条和原先的Nardack相同学号的数据:
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('Nardack_2','女','341433','zy','08012001','11/19/1987');
之后我们查看xs表的数据:
之后我们调用这个存储过程:
CALL delete_duplicates();
之后我们再去观察xs表中的数据,可以发现含有重复学号(341433)的两条数据都已经被全部删除:
注意:这样处理只能保证当出现含有重复学号数据时,含有这个学号的所有列都被删除。如果要实现“只保留一个”的需求,最好的方法还是建立主键。
附:在Putty控制台中输入存储过程的方法:
将上面的语句复制完以后,在最后面加上一个正斜杠(/),代表整个语句输入结束。
2. 建立适当的存储过程,计算学生的总学分、总成绩,并保存在另外一张表中;
这个存储过程的源码如下:
CREATE PROCEDURE calculate_score() AS
BEGIN
DROP TABLE IF EXISTS xs_score;
CREATE TABLE xs_score(
xm VARCHAR(24) NOT NULL,
xh VARCHAR(10) NOT NULL,
zxf DECIMAL(5,1),
zcj DECIMAL(5,1)
);
INSERT INTO xs_score SELECT xm,xh,SUM(xf) AS zxf,SUM(xf*cj) AS zcj FROM (
SELECT xm,xh,kcbh,NVL(xf,0) AS xf,NVL(MAX(cj),0) AS cj FROM (
SELECT xs.xm,xs.xh,xk.kcbh,kc.xf,xk.cj FROM (
xs LEFT JOIN xk ON (xs.xh = xk.xh) LEFT JOIN kc ON (xk.kcbh = kc.kcbh)
)
) GROUP BY (xm,xh,kcbh,xf)
) GROUP BY (xm,xh);
END;
这个存储过程先是创建了一个名为xs_score的表,然后计算出总学分,总姓名,把姓名,学号,总学分,总成绩存储到这个表中。
运行完这个存储过程后,查询xs_score的结果为:
3. 查询总成绩表,并进行排序。
我们已经从存储过程2中得到了xs_score这个表,之后我们如果要对总成绩排序,只需要执行以下语句就可以了:
SELECT * FROM xs_score ORDER BY zcj DESC;
运行结果如下:
不需要这个表的话,直接把这个表删除即可:
DROP TABLE xs_score;
从以上的实验过程中,我们可以看到定义视图和存储过程可以简化代码,并使得代码更加清晰。