实验六：视图实验

最新推荐文章于 2024-08-26 19:43:03 发布

hellenionia

最新推荐文章于 2024-08-26 19:43:03 发布

阅读量191

点赞数

分类专栏：【数据库原理】数据库系统概论（第五版）文章标签：数据库 sql mysql

本文链接：https://blog.csdn.net/weixin_56463218/article/details/128481537

版权

【数据库原理】数据库系统概论（第五版）专栏收录该内容

6 篇文章 2 订阅

订阅专栏

【实验目的】

能够数量使用sql语句创建、修改和删除视图，并能够使用所建视图进行数据管理

【实验准备】

一、完成实验四完整性实验1-4内容，根据要求建立

处方主表(处方日期,处方号,处方总金额)

处方明细表(处方日期,处方号,序号,药品代码,药品名称,药品规格,数量,单价,使用天数,处方明细唯一号)

药品价表(药品代码,药品规格,单价,开始时间,结束时间)

药品名称字典(药品代码,药品名称)

这4张表。

二、运行以下SQL语句

BEGIN

INSERT INTO 处方主表(处方日期,处方号,处方总金额) VALUES (to_date('2022-12-05','yyyy-mm-dd'),10001,54.5);

INSERT INTO 处方主表(处方日期,处方号,处方总金额) VALUES (to_date('2022-12-06','yyyy-mm-dd'),10001,1070);

INSERT INTO 处方主表(处方日期,处方号,处方总金额) VALUES (to_date('2022-12-07','yyyy-mm-dd'),10001,1095);

INSERT INTO 处方主表(处方日期,处方号,处方总金额) VALUES (to_date('2022-12-08','yyyy-mm-dd'),10001,33.6);

INSERT INTO 处方主表(处方日期,处方号,处方总金额) VALUES (to_date('2022-12-09','yyyy-mm-dd'),10001,90);

INSERT INTO 处方主表(处方日期,处方号,处方总金额) VALUES (to_date('2022-12-09','yyyy-mm-dd'),10002,182);

INSERT INTO 处方主表(处方日期,处方号,处方总金额) VALUES (to_date('2022-12-10','yyyy-mm-dd'),10001,62.3);

INSERT INTO 处方主表(处方日期,处方号,处方总金额) VALUES (to_date('2022-12-11','yyyy-mm-dd'),10001,105.6);

INSERT INTO 处方明细表 VALUES(to_date('20221205','yyyymmdd'),10001,1,'D00001','酮替芬分散片','1mg*24',1,18,9,'20221205100011');

INSERT INTO 处方明细表 VALUES(to_date('20221205','yyyymmdd'),10001,2,'D00002','地诺孕素片','2mg*28',1,36.5,7,'20221205100012');

INSERT INTO 处方明细表 VALUES(to_date('20221206','yyyymmdd'),10001,1,'D00003','注射用重组人Ⅱ型肿瘤坏死因子受体-抗体融合蛋白','12.5mg',5,216,5,'20221206100011');

INSERT INTO 处方明细表 VALUES(to_date('20221207','yyyymmdd'),10001,1,'D00002','地诺孕素片','2mg*28',30,36.5,30,'20221207100011');

INSERT INTO 处方明细表 VALUES(to_date('20221208','yyyymmdd'),10001,1,'D00004','复方风湿宁片','0.21g*60',2,7.8,6,'20221208100011');

INSERT INTO 处方明细表 VALUES(to_date('20221208','yyyymmdd'),10001,2,'D00001','酮替芬分散片','1mg*24',1,18,6,'20221208100012');

INSERT INTO 处方明细表 VALUES(to_date('20221209','yyyymmdd'),10001,1,'D00001','酮替芬分散片','1mg*24',1,18,6,'20221209100011');

INSERT INTO 处方明细表 VALUES(to_date('20221209','yyyymmdd'),10001,2,'D00002','地诺孕素片','2mg*28',2,36.5,4,'20221209100012');

INSERT INTO 处方明细表 VALUES(to_date('20221210','yyyymmdd'),10001,1,'D00001','酮替芬分散片','1mg*24',1,18,6,'20221210100011');

INSERT INTO 处方明细表 VALUES(to_date('20221210','yyyymmdd'),10001,2,'D00002','地诺孕素片','2mg*28',2,36.5,4,'20221210100012');

INSERT INTO 处方明细表 VALUES(to_date('20221210','yyyymmdd'),10001,3,'D00004','复方风湿宁片','0.21g*60',2,7.8,6,'20221210100013');

INSERT INTO 处方明细表 VALUES(to_date('20221211','yyyymmdd'),10001,1,'D00001','酮替芬分散片','1mg*24',1,18,6,'20221211100011');

INSERT INTO 处方明细表 VALUES(to_date('20221211','yyyymmdd'),10001,2,'D00002','地诺孕素片','2mg*28',2,36.5,4,'20221211100012');

INSERT INTO 处方明细表 VALUES(to_date('20221211','yyyymmdd'),10001,3,'D00004','复方风湿宁片','0.21g*60',2,7.8,6,'20221211100013');

COMMIT;

END;

-----------

create table 药品名称字典
(药品代码 varchar2(100) primary key,
药品名称 varchar2(100)
); 

INSERT INTO 药品名称字典 VALUES ('D00001','酮替芬分散片');
INSERT INTO 药品名称字典 VALUES ('D00002','地诺孕素片');
INSERT INTO 药品名称字典 VALUES ('D00003','注射用重组人Ⅱ型肿瘤坏死因子受体-抗体融合蛋白');
INSERT INTO 药品名称字典 VALUES ('D00004','复方风湿宁片');

select * from 药品名称字典 for update;

------------

create table 药品价表
(药品代码 varchar2(100),
药品规格 varchar2(100),
单价 number(10,3),
开始时间 date not null,
结束时间 date,
primary key (药品代码，药品规格)
);

INSERT INTO 药品价表 VALUES ('D00001','1mg*24',18,to_date('2022-01-01 09:00:00','yyyy-mm-dd hh24:mi:ss'),NULL);
INSERT INTO 药品价表 VALUES ('D00002','2mg*28',36.5,to_date('2022-01-15 09:20:00','yyyy-mm-dd hh24:mi:ss'),to_date('2022-11-17 17:21:11','yyyy-mm-dd hh24:mi:ss'));
INSERT INTO 药品价表 VALUES ('D00003','12.5mg',216,to_date('2022-04-07 08:00:00','yyyy-mm-dd hh24:mi:ss'),NULL);
INSERT INTO 药品价表 VALUES ('D00004','0.21g*60',7.8,to_date('2022-06-19 08:00:00','yyyy-mm-dd hh24:mi:ss'),NULL); 

select * from 药品价表 for update;

----------

create table 处方明细表
(处方日期 date,
处方号 number(30),
序号 number(30),
药品代码 varchar2(100),
药品名称 varchar2(100),
药品规格 varchar2(100),
数量 number(30),
单价 number(10,2),
使用天数 number(30)check(使用天数 >=1 and 使用天数 <=30),
处方明细唯一号 varchar2(100) unique not null,
primary key(处方日期,处方号,序号),
foreign key(药品代码) references 药品名称字典(药品代码),
foreign key(药品代码,药品规格) references 药品价表(药品代码,药品规格)
);

INSERT INTO 处方明细表 VALUES (to_date('2022-05-26','yyyy-mm-dd'),10001,1,'D00001','酮替芬分散片','1mg*24',1,18,6,'20220526100011');
INSERT INTO 处方明细表 VALUES (to_date('2022-05-26','yyyy-mm-dd'),10001,2,'D00002','地诺孕素片','2mg*28',1,36.5,7,'20220526100012');

select * from 药品价表 for update;
select * from 处方明细表 for update;

--------

create table 处方主表
(处方日期 date,
处方号 number(30),
处方总金额 number(10,2),
primary key(处方日期,处方总金额)
);
INSERT INTO 处方主表 VALUES (to_date('2022-05-26','yyyy-mm-dd'),10001,54.5);

select * from 处方主表 for update;

---------

BEGIN
INSERT INTO 处方主表(处方日期,处方号,处方总金额) VALUES (to_date('2022-12-05','yyyy-mm-dd'),10001,54.5);
INSERT INTO 处方主表(处方日期,处方号,处方总金额) VALUES (to_date('2022-12-06','yyyy-mm-dd'),10001,1070);
INSERT INTO 处方主表(处方日期,处方号,处方总金额) VALUES (to_date('2022-12-07','yyyy-mm-dd'),10001,1095);
INSERT INTO 处方主表(处方日期,处方号,处方总金额) VALUES (to_date('2022-12-08','yyyy-mm-dd'),10001,33.6);
INSERT INTO 处方主表(处方日期,处方号,处方总金额) VALUES (to_date('2022-12-09','yyyy-mm-dd'),10001,90);
INSERT INTO 处方主表(处方日期,处方号,处方总金额) VALUES (to_date('2022-12-09','yyyy-mm-dd'),10002,182);
INSERT INTO 处方主表(处方日期,处方号,处方总金额) VALUES (to_date('2022-12-10','yyyy-mm-dd'),10001,62.3);
INSERT INTO 处方主表(处方日期,处方号,处方总金额) VALUES (to_date('2022-12-11','yyyy-mm-dd'),10001,105.6);

INSERT INTO 处方明细表 VALUES(to_date('20221205','yyyymmdd'),10001,1,'D00001','酮替芬分散片','1mg*24',1,18,9,'20221205100011');
INSERT INTO 处方明细表 VALUES(to_date('20221205','yyyymmdd'),10001,2,'D00002','地诺孕素片','2mg*28',1,36.5,7,'20221205100012');
INSERT INTO 处方明细表 VALUES(to_date('20221206','yyyymmdd'),10001,1,'D00003','注射用重组人Ⅱ型肿瘤坏死因子受体-抗体融合蛋白','12.5mg',5,216,5,'20221206100011');
INSERT INTO 处方明细表 VALUES(to_date('20221207','yyyymmdd'),10001,1,'D00002','地诺孕素片','2mg*28',30,36.5,30,'20221207100011');
INSERT INTO 处方明细表 VALUES(to_date('20221208','yyyymmdd'),10001,1,'D00004','复方风湿宁片','0.21g*60',2,7.8,6,'20221208100011');
INSERT INTO 处方明细表 VALUES(to_date('20221208','yyyymmdd'),10001,2,'D00001','酮替芬分散片','1mg*24',1,18,6,'20221208100012');
INSERT INTO 处方明细表 VALUES(to_date('20221209','yyyymmdd'),10001,1,'D00001','酮替芬分散片','1mg*24',1,18,6,'20221209100011');
INSERT INTO 处方明细表 VALUES(to_date('20221209','yyyymmdd'),10001,2,'D00002','地诺孕素片','2mg*28',2,36.5,4,'20221209100012');
INSERT INTO 处方明细表 VALUES(to_date('20221210','yyyymmdd'),10001,1,'D00001','酮替芬分散片','1mg*24',1,18,6,'20221210100011');
INSERT INTO 处方明细表 VALUES(to_date('20221210','yyyymmdd'),10001,2,'D00002','地诺孕素片','2mg*28',2,36.5,4,'20221210100012');
INSERT INTO 处方明细表 VALUES(to_date('20221210','yyyymmdd'),10001,3,'D00004','复方风湿宁片','0.21g*60',2,7.8,6,'20221210100013');
INSERT INTO 处方明细表 VALUES(to_date('20221211','yyyymmdd'),10001,1,'D00001','酮替芬分散片','1mg*24',1,18,6,'20221211100011');
INSERT INTO 处方明细表 VALUES(to_date('20221211','yyyymmdd'),10001,2,'D00002','地诺孕素片','2mg*28',2,36.5,4,'20221211100012');
INSERT INTO 处方明细表 VALUES(to_date('20221211','yyyymmdd'),10001,3,'D00004','复方风湿宁片','0.21g*60',2,7.8,6,'20221211100013');
COMMIT;
END;

【实验内容】

1、建立视图，视图名称为：平均药品金额视图，视图查询当前正在使用的药品中（结束时间为空），上周（2022年12月5日至12月11日）每种药品平均每天的处方总金额，保留2位小数，视图的属性为：药品代码、药品名称、平均每天药品金额

个人答案：

create view 平均药品金额视图 as
select a.药品代码,
       a.药品名称,
       round(Sum(a.数量*a.单价)/7.2)平均每天药品金额
from 处方明细表 a,
     药品价表 b
where a.药品代码=b.药品代码
and a.药品规格=b.药品规格
and b.结束时间 is null
and a.处方日期>=to_date('20221205','yyyymmdd')
and a.处方日期<=to_date('20221211','yyyymmdd')
group by a.药品代码,
         a.药品名称;
         
select * from 平均药品金额视图;

参考答案：

CREATE VIEW 平均药品金额视图 AS
SELECT a.药品代码,
       a.药品名称,
       round(SUM(a.数量*a.单价)/7,2) 平均每天药品金额
FROM 处方明细表 a,
     药品价表 b
WHERE a.药品代码 = b.药品代码
AND   a.药品规格 = b.药品规格
AND   b.结束时间 IS NULL
AND   a.处方日期 >= to_date('2022-12-05','yyyy-mm-dd')
AND   a.处方日期 <= to_date('2022-12-11','yyyy-mm-dd')
GROUP BY a.药品代码,
         a.药品名称;

2、建立视图，视图名称为：大处方药品视图，视图查询药品名称字典中，存在于处方总金额大于1000的处方主表所对应的处方明细表中的药品。视图的属性为：药品代码，药品名称。要求建立的视图能够正常完成下列的sql语句。

INSERTINTO 大处方药品视图 VALUES ('D00005','维生素AD滴剂');

个人答案：

create view 大处方药品视图 as
select a.药品代码,
       a.药品名称
from 药品名称字典 a
where a.药品代码 in
(select c.药品代码
from 处方主表 b,
     处方明细表 c
where b.处方日期=c.处方日期
and b.处方号=c.处方号
and b.处方总金额>1000);

INSERT INTO 大处方药品视图 VALUES ('D00005','维生素AD滴剂');

select * from 大处方药品视图;

参考答案、：

CREATE VIEW 大处方药品视图 AS
SELECT a.药品代码,
       a.药品名称
FROM 药品名称字典 a
WHERE a.药品代码 IN 
(SELECT c.药品代码
 FROM 处方主表 b,
      处方明细表 c
WHERE b.处方日期 = c.处方日期
AND   b.处方号 = c.处方号
AND   b.处方总金额 > 1000);

3、建立视图，视图名称为：异常处方主表视图，视图查询对应处方明细表有数据，并且对应处方明细表中药品金额相加的总金额不等于处方主表中处方总金额的处方主表信息，视图的属性为：处方日期，处方号，处方总金额。视图要求添加限定WITH CHECK OPTION。要求下列语句能够正常执行：

UPDATE异常处方主表视图a SET a.处方总金额 = 200
WHERE a.处方日期 = to_date('20221205','yyyymmdd')
AND a.处方号 = 10001;

个人答案：

create view 异常处方主表视图 as
select 处方日期,
       处方号,
       处方总金额
from 处方主表 aa
where not exists
(select *
from
(select b.处方日期,b.处方号,Sum(b.数量*b.单价)处方总金额
from 处方明细表 b
group by b.处方日期,b.处方号
)bb
where aa.处方总金额 = bb.处方总金额
and aa.处方日期 = bb.处方日期
and aa.处方号 = bb.处方号)
with check option;

UPDATE 异常处方主表视图 a SET a.处方总金额 = 200
WHERE a.处方日期 = to_date('20221205','yyyymmdd')
AND   a.处方号 = 10001;

select * from 异常处方主表视图;

4、验证以下sql语句：

UPDATE异常处方主表视图a SET a.处方总金额 = 106.6
WHERE a.处方日期 = to_date('20221210','yyyymmdd')
AND a.处方号 = 10001;

个人答案：

【注意】3、4题不同的视图可能有不同的校验结果：

示例一：

建立视图，视图名称为：异常处方主表视图，视图查询对应处方明细表有数据，并且对应处方明细表中药品金额相加的总金额不等于处方主表中处方总金额的处方主表信息，视图的属性为：处方日期，处方号，处方总金额。视图要求添加限定WITH CHECK OPTION。要求下列语句能够正常执行：

UPDATE 异常处方主表视图 a SET a.处方总金额 = 200
WHERE a.处方日期 = to_date('20221205','yyyymmdd')
AND a.处方号 = 10001;

CREATE 异常处方主表视图 AS
SELECT 处方日期,处方号,处方总金额
FROM 处方主表 a
WHERE a.处方总金额 <>
(SELECT SUM(b.数量*b.单价) 处方总金额
FROM 处方明细表 b
WHERE a.处方日期 = b.处方日期
AND a.处方号 = b.处方号)
WITH CHECK OPTION;

验证以下sql语句：

UPDATE 异常处方主表视图 a SET a.处方总金额 = 106.6
WHERE a.处方日期 = to_date('20221210','yyyymmdd')
AND a.处方号 = 10001;

执行失败：

示例二：

UPDATE 异常处方主表视图 a SET a.处方总金额 = 200
WHERE a.处方日期 = to_date('20221205','yyyymmdd')
AND a.处方号 = 10001;

CREATE OR replace VIEW 异常处方主表视图 AS
SELECT 处方日期,处方号,处方总金额
FROM 处方主表 aa
WHERE NOT EXISTS
(SELECT 1
FROM
(SELECT b.处方日期,b.处方号,
SUM(b.数量*b.单价) 处方总金额
FROM 处方明细表 b
GROUP BY b.处方日期,b.处方号) bb
WHERE aa.处方总金额 = bb.处方总金额)
WITH CHECK OPTION;

4、验证以下sql语句：

UPDATE 异常处方主表视图 a SET a.处方总金额 = 106.6
WHERE a.处方日期 = to_date('20221210','yyyymmdd')
AND a.处方号 = 10001;

执行失败：

5、删除前面新建的3张视图。

个人答案：

drop view 平均药品金额视图;
drop view 大处方药品视图;
drop view 平异常处方主表视图;

参考答案：

DROP VIEW 平均药品金额视图;
DROP VIEW 大处方药品视图;
DROP VIEW 异常处方主表视图;

hellenionia

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
打赏
0
评论
实验六：视图实验

能够数量使用sql语句创建、修改和删除视图，并能够使用所建视图进行数据管理
复制链接

扫一扫

专栏目录