数据库实验：数据库和表、查询、视图与安全性、存储过程及游标、触发器、综合实验-水果商店进阶

已于 2022-04-22 10:51:30 修改
阅读量7.5k 收藏 73
点赞数 17
分类专栏： 大数据 文章标签： 1024程序员节 数据库 MYSQL SQL
于 2021-10-24 23:03:43 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_43605229/article/details/120942604
版权

数据库实验：数据库和表、查询、视图与安全性、存储过程及游标、触发器、综合实验-水果商店进阶

实验一、数据库和表

一、实验目的与要求：
１、掌握MySQL中如何创建数据库和表的方法
２、掌握navicat中创建数据库、表，导入及导出表结构及数据。
3、熟练掌握MySQL的数据类型、主键、实体完整性的设置。

二、实验内容：
１、创建名为fruitshop的数据库，并创建数据表fruits、customers（客户）、orderitems（订单详单）、suppliers（供货商）和orders（订单总表），表结构和约束条件如下：
在这里插入图片描述
在这里插入图片描述

源码1：

按题目一段一段复制

-- 表 1   fruits表结构

create table fruits(
	f_id char(10) primary key not null,
	s_id int not null,
	f_name char(255) not null,
	f_price decimal(8,2) not null
)

-- 表 2   customers表结构

create table customers(
	c_id int primary key auto_increment not null,
	c_name char(50) not null,
	c_address char(50),
	c_city char(50),
	c_zip char(10),
	c_contact char(50),
	c_email char(255)
)

-- 表 3   orderitems表结构

create table orderitems(
	o_num int not null,
	o_item int not null,
	f_id char(10) not null,
	quantity int not null,
	item_price decimal(8,2) not null,
	primary key(o_num,o_item)
)

-- 表 4   suppliers表结构

create table suppliers(
	s_id int primary key auto_increment not null,
	s_name char(50) not null,
	s_city char(50),
	s_zip char(10),
  s_call char(50) not null
)

-- 表 5   orders表结构

create table orders(
	o_num int primary key auto_increment not null,
	o_date datetime not null,
	c_id int not NULL
)

运行测试结果截图:
在这里插入图片描述
在这里插入图片描述
2、向数据表fruits、customers、orderitems和suppliers、orders中插入给定的如下数据
fruits表数据
在这里插入图片描述
customers表数据
在这里插入图片描述
orderitems表数据
在这里插入图片描述
suppliers表数据
在这里插入图片描述
orders表数据
在这里插入图片描述
3.向数据表customers和suppliers中分别插入两条记录，新记录customers的C_ID属性值统一为10000，在suppliers的S_ID属性值统一为100，S_NAME数据为JMU，其余属性值为本人的真实信息；向数据表fruits、orderitems、orders表中插入分别插入五条与本人相关的新记录

源码2：

按题目一段一段复制

-- 表 1   fruits表数据插入
insert into fruits values('a0',00,'sekai-ichi',52);
insert into fruits values('a1',101,'apple',5.2);
insert into fruits values('a2',103,'apricot',2.2);
insert into fruits values('b1',101,'blackberry',10.2);
insert into fruits values('b2',104,'berry',7.6);
insert into fruits values('b3',104,'lemon',6.4);
insert into fruits values('b5',107,'pear',3.6);
insert into fruits values('bs1',102,'orange',11.2);
insert into fruits values('bs2',105,'melon',8.2);
insert into fruits values('c0',101,'plum',3.2);
insert into fruits values('ml',106,'mango',15.6);
insert into fruits values('m2',105,'watermelon',2.6);
insert into fruits values('m3',105,'cherry',11.6);
insert into fruits values('o2',103,'cocount',9.2);
insert into fruits values('t1',102,'banana',10.3);
insert into fruits values('t2',102,'grape',5.3);
insert into fruits values('t4',107,'peanut',3.6);
-- 表 2   customers表数据插入
insert into customers values(10000,'LinChuanTao','YanPingQv','NanPing',353000,'LinChuanTao','1804211700@qq.com');
insert into customers values(10001,'RedHook','200 Street','Tianjin',300000,'LiMing','LiMing@163.com');
insert into customers values(10002,'Stars','333 Fromgae Lane','Dalian',116000,'Zhangbo','Jerry@hotmail.com');
insert into customers values(10003,'Netbhood','1 Sunny Place','Qingdao',266000,'LuoCong',null);
insert into customers values(10004,'JOTO','829 Riverside Drive','Haikou',570000,'YangShan','sam@hotmail.com');
-- 表 3   orderitems表数据插入
insert into orderitems values(30000,0,'a0',99999999,0.09);
insert into orderitems values(30001,1,'a1',10,5.2);
insert into orderitems values(30001,2,'b2',3,7.6);
insert into orderitems values(30001,3,'bs1',5,11.2);
insert into orderitems values(30001,4,'bs2',15,9.2);
insert into orderitems values(30002,1,'b3',2,20);
insert into orderitems values(30003,1,'c0',100,10);
insert into orderitems values(30004,1,'o2',50,2.5);
insert into orderitems values(30005,1,'c0',5,10);
insert into orderitems values(30005,2,'b1',10,8.99);
insert into orderitems values(30005,3,'a2',10,2.2);
insert into orderitems values(30005,4,'m1',5,14.99);
-- 表 4   suppliers表数据插入
insert into suppliers values(100,'JMU','XiaMen','361021','6180039');
insert into suppliers values(101,'FastFruit Inc.','Tianjin','300000','48075');
insert into suppliers values(102,'LT Supplies','Chongqing','400000','44333');
insert into suppliers values(103,'ACME','Shanghai','200000','90046');
insert into suppliers values(104,'FNK Inc.','Zhongshan','528437','11111');
insert into suppliers values(105,'Good Set','Taiyuang','030000','22222');
insert into suppliers values(106,'Just Eat Ours','Beijing','010','45678');
insert into suppliers values(107,'DK Inc.','Zhengzhou','450000','33332');
-- 表 5   orders表数据插入
insert into orders values(30000,'2020-04-15 09:59:59',10000);
insert into orders values(30001,'2018-09-01 00:00:00',10001);
insert into orders values(30002,'2018-09-12 00:00:00',10003);
insert into orders values(30003,'2018-09-30 00:00:00',10004);
insert into orders values(30004,'2018-10-03 00:00:00',10002);
insert into orders values(30005,'2018_10-08 00:00:00',10001);

运行测试结果截图:
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

小结

1.实验中遇到的问题及解决过程

刚开始实验时由于对Navicat Premium可视化系统不够熟悉，导致在创建表等操作时不知从何入手，后经过老师的帮助与互联网工具的查找正确地在Navicat Premium可视化系统中执行了【新建连接】、【新建数据库】、【新建查询】、【转储SQL文件】等操作。

2.实验中产生的错误及原因分析

实验中由于不够细心将一些符号的中英文形式混合输入及忘记在一些字符的插入时输入单引号而导致出现error。

3.实验体会和收获。

通过该实验，在Navicat Premium可视化系统中对数据库进行一些列操作，掌握了创建数据库、表，导入及导出表结构及数据的知识。以及掌握MySQL的数据类型、主键、实体完整性的设置。
同时在老师同学与互联网的帮助下解决了出现的问题与错误，感受到了数据库的有趣之处。

实验二、查询

一、实验目的与要求：
1、掌握SQL单表及多表之间的查询
2、掌握统计及分组函数
二、实验内容：

源码

按题目一段一段复制

-- 1.简单查询
①　从fruits表中检索s_id为100的供货商所供货的水果名和价格

select f_name, f_price
from fruits
where s_id = 100

-- ②　查找名称为“apple”的水果的价格

select f_price
from fruits
where f_name = 'apple'

-- ③　查询价格在2.00元到10.20元之间的水果名称和价格，先按f_price降序排序，再按f_name排序。
select f_name, f_price
from fruits
where f_price >= 2.00 and f_price <= 10.20
order by f_price desc,f_name

-- ④　在fruits表中，查询f_name中包含字母‘g’的记录
select*from fruits
where f_name like '%g%'

-- ⑤　查询customers表中c_email不为空的记录的c_id、c_name和c_email字段值
select c_id, c_name, c_email
from customers
where c_email is not null

-- ⑥　根据s_id对fruits表中的数据进行分组，并显示水果种类大于1的分组信息，效果如下图所示（GROUP_CONCAT函数）

select s_id,group_concat(f_name) Names
from fruits
group by s_id
having count(s_id)>1

-- 2.多表查询
-- ①　查询供应‘a1’的水果供应商提供的其他水果种类
select f1.f_id, f1.f_name
from fruits as f1, fruits as f2
where f1.s_id = f2.s_id and f2.f_id = 'a1'

-- ②　在orderitems表中查询f_id为c0的订单号，并显示具有该订单号的客户c_id
select o1.o_num, o2.c_id
from orderitems as o1, orders as o2
where o1.o_num = o2.o_num and o1.f_id = 'c0'

-- ③　查询客户c_id为10000的所有订单的总价格
select c_id,
sum(quantity * item_price) as priceSUM
from orderitems as o1, orders as o2
where c_id = 10000 and o1.o_num = o2.o_num

-- ④　查询s_city等于“Tianjin”的供应商，并显示所有该供应商提供的水果的种类，效果如下图所示
select o1.s_id, f_name
from fruits as o1, suppliers o2
where o1.s_id = o2.s_id and s_city = 'Tianjin'

-- ⑤　查询订单为‘30005’的所有水果供货商的名称
select o1.o_num,o3.s_name
from orderitems as o1, fruits as o2, suppliers o3
where o1.o_num = 30005 and o1.f_id = o2.f_id
and o2.s_id = o3.s_id

-- ⑥　查询销量最多的水果名称
select o1.quantity ,o2.f_name
from orderitems as o1, fruits as o2
where o1.f_id = o2.f_id
order by quantity desc
limit 1

小结

1．实验中遇到的问题及解决过程
在实验过程中新建的查询未保存导致，在查看表数据后内容丢失，后保存查询解决了该问题。

2.实验中产生的错误及原因分析
在实验过程中，由于粗心误看数据，导致结果与预期不符，后经过多次观察改动，解决了错误。

3．实验体会和收获。
通过该实验，在Navicat Premium可视化系统中对数据库进行一些列操作，掌握了SQL单表及多表之间的查询统计与分组函数的应用。更深入的了解了数据库原理课程的内容。
同时在老师同学与互联网的帮助下解决了出现的问题与错误，感受到了数据库管理方式的多样与数据库的魅力。

实验三、视图、安全性

一、实验目的与要求：
１、掌握参照完整性约束
2、设计用户子模式
3、根据实际需要创建不同的用户授以不同的权限
4、针对不同级别的用户定义不同的视图，以保证系统的安全性

二、实验内容：
1.为fruitshop数据库的各表构造合适的外键，并设置外键级联删除或更新操作，比如表fruits的s_id字段是外键，取值来源于是suppliers表的s_id字段。

源码：

--1）表fruits的s_id字段是外键，取值来源于是suppliers表的s_id字段

ALTER TABLE fruits
ADD CONSTRAINT FOREIGN KEY(s_id) REFERENCES suppliers(s_id)
ON DELETE CASCADE ON UPDATE CASCADE;

--2）表orderitems的f_id字段是外键，取值来源于是fruits表的f_id字段

ALTER TABLE orderitems
ADD CONSTRAINT FOREIGN KEY(f_id) REFERENCES fruits(f_id)
ON DELETE CASCADE ON UPDATE CASCADE;

--3）表orders的c_id字段是外键，取值来源于是customers表的c_id字段

ALTER TABLE orders
ADD CONSTRAINT FOREIGN KEY(c_id) REFERENCES customers(c_id)
ON DELETE CASCADE ON UPDATE CASCADE;

--4）表orderitems的o_num字段是外键，取值来源于是orders表的o_ num字段
ALTER TABLE orderitems
ADD CONSTRAINT FOREIGN KEY(o_num) REFERENCES orders(o_num)
ON DELETE CASCADE ON UPDATE CASCADE;

2.创建四类用户，每类客户创建一个用户，统一设置初始密码为’123456’：
客户：C10000，对应customs表内的10000客户，任意主机地址
供货商：S100，对应suppliers表内的100供货商，任意主机地址
商家销售工作人员：B001，任意主机地址
管理员：A001，本机

CREATE USER 'C10000'@'%' IDENTIFIED BY '123456';
CREATE USER 'S100'@'%' IDENTIFIED BY '123456';
CREATE USER 'B001'@'%' IDENTIFIED BY '123456';
CREATE USER 'A001'@'localhost' IDENTIFIED BY '123456'

3.为以上四类用户设计合适的权限，并定义对应的视图（附上源码，创建用户并授权成功后在交互式运行状态下查看每个用户权限并截图）
要求：
客户和供货商只能查看与之相关的信息，如C10000用户只可以查看customs表内关于自己的所有信息，可以修改（update）c_id和C_name以外的关于自己的基本信息，可以查看自己的购买信息（在订单order及详单表orderitems中）；同理设置供货商S100的相关权限；
商家销售工作人员B001可以查看并录入信息，但不能随意修改、删除信息；
管理员拥有所有权限。

-- 客户：
CREATE VIEW CS1 AS SELECT*
FROM   orders
WHERE  c_id=10000
CREATE VIEW CS2 AS SELECT*
FROM   customers
WHERE  c_id=10000

GRANT SELECT,UPDATE(c_address,c_city,c_zip,c_contact,c_email) ON CS2 
TO 'C10000'@'%';
GRANT SELECT ON CS1
TO 'C10000'@'%';
GRANT SELECT ON orderitems
TO'C10000'@'%';

在这里插入图片描述

-- 供货商:
CREATE VIEW CS3 AS SELECT*
FROM   suppliers
WHERE  s_id=100

GRANT SELECT,UPDATE(s_city,s_zip,s_call) ON CS3 
TO 'S100'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;

在这里插入图片描述

商家销售工作人员：
GRANT SELECT,INSERT ON fruitshop.* 
TO 'B001'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;

在这里插入图片描述

--管理员：
GRANT ALL ON fruitshop.* 
TO 'A001'@'localhost' IDENTIFIED BY '123456' WITH GRANT OPTION;

在这里插入图片描述
4.退出已用root账号登陆的MySQL服务器，用这四类用户分别登陆MySQL服务器并测试其功能（在交互式运行状态下测试，先附上测试源码，关键结果截图）。
测试要求：退出root帐号,重新登陆指定帐号，显示当前数据库中所有表，查看某张表内的所有信息，对某张表进行增、删、改操作并查看能否正确执行这些操作，如果执行错误请说明导致此错误的原因。
测试客户：
错误：INSERT INTO orderitems VALUES(23333,4,‘m1’,5,14.99);
在这里插入图片描述
原因：没有插入权限
正确：
SELECT f_id
FROM orderitems
WHERE o_num = 30000
在这里插入图片描述
测试供货商:
错误：DELETE FROM cs3.s_city
在这里插入图片描述
原因：没有删除权限
正确：
SELECT s_city
FROM cs3

测试商家销售工作人员：
错误：
UPDATE fruits SET f_id = ‘a10’
WHERE f_id = ‘a0’
在这里插入图片描述
原因：没有修改权限

正确：
SELECT f_id
FROM orderitems
WHERE o_num = 30000
在这里插入图片描述
测试管理员：
SELECT o_date
FROM orders
WHERE o_num = 30000
在这里插入图片描述

小结

1．实验中遇到的问题及解决过程
1)用户权限设置问题：通过创建视图，让用户获得部分对视图操作的权限
2)用户账号切换问题：点击【连接】→选择【MYSQL】→输入账号密码
2．实验中产生的错误及原因分析
1)在用户创建时出现如下错误，原因为语句后的分号遗漏。
2)在外键设置时出现错误“[Err] 1452 - Cannot add or update a child row”，原因为在十一插入数据时误将字母L当做数字1录入，导致外键确保取值不满足参照完整性约束条件。
3)在用户登录是出现以下错误，按照提示应该修改“localhost 3306”,但修改该名称后发现无法连接到数据库，于是多次操作，发现修改的并非连接名而是地址名，修改正确后用户账号成功登录。
在这里插入图片描述
3．实验体会和收获
通过该实验，在Navicat Premium可视化系统中对数据库进行一些列操作，掌握参照完整性约束、设计用户子模式等功能，并且根据实际需要创建了不同的用户授以不同的权限，以此来针对不同级别的用户定义不同的视图，保证了系统的安全性。
同时在实验过程中有遇到很多的问题与错误，但是都在不断的实验与老师同学的帮助下解决了出现的问题与错误。感受到即使是表面上看上去很简单的题目，要去实践也会遇到很多不容易解决的问题与错误。

实验四、存储过程及游标

一、实验目的与要求：
1、掌握存储过程的工作原理、定义及操作方法
2、掌握函数的工作原理、定义及操作方法
3、掌握游标的工作原理、定义及操作方法

二、实验内容：

  1. 创建函数，用来自动统计给定订单号的订单总金额

源码

create function OSum(n int)
returns decimal(8,2)
return (select sum(quantity*item_price) as ordersum
from orderitems 
where o_num = n);

运行测试结果截图（如输入订单号’30001’测试结果）：
在这里插入图片描述
2.创建存储过程，自动搜索并添加客户及供货商帐号信息到新建的用户信息表。
①增加用户表信息user
在这里插入图片描述

create table user(
	id int(11) primary key auto_increment not null,
	u_id int(11) unique not null,
	pwd blob not null,
	remake varchar (255) not null
)

② 创建两个存储过程，分别把客户表的c_id和供货商表s_id的字段自动添加到用户信息表,补充pwd和remark字段。
要求：id字段自动增加，u_id 字段即客户或供货商的编号，pwd字段用ENCODE函数加密，密码统一设置为’123456’，密钥是’hello’; remark字段内容是‘customer’或’supplier’

-- 添加客户表帐号：
CREATE PROCEDURE insertC()
BEGIN

DECLARE numer INT;
DECLARE u_id INT(11); 
DECLARE inCus CURSOR FOR 
SELECT c_id from customers;
DECLARE CONTINUE HANDLER FOR 
NOT FOUND SET numer=1;
SET numer=0;
OPEN inCus;
FETCH next FROM inCus INTO u_id;
WHILE (numer=0) DO
INSERT INTO `user`(u_id,pwd,remake)
VALUES(u_id,ENCODE(123456,'hello'),'customers'); 
FETCH next FROM inCus INTO u_id;
END WHILE;
CLOSE inCus;

END

在这里插入图片描述

--添加供货商帐号：
CREATE PROCEDURE insertS()
BEGIN

DECLARE numer INT;
DECLARE u_id INT(11); 
DECLARE inSup CURSOR FOR 
SELECT s_id from suppliers;
DECLARE CONTINUE HANDLER FOR 
NOT FOUND SET numer=1;
SET numer=0;
OPEN inSup;
FETCH next FROM inSup INTO u_id;
WHILE (numer=0) DO
INSERT INTO `user`(u_id,pwd,remake)
VALUES(u_id,ENCODE(123456,'hello'),'supplier'); 
FETCH next FROM inSup INTO u_id;
END WHILE;
CLOSE inSup;

END

在这里插入图片描述

小结

1．实验中遇到的问题及解决过程
在函数创建过程中，由于对该方面知识不够熟练遇到参数设置错误、属性不匹配等问题，后通过询问老师与同学及互联网工具解决了这些问题。

2.实验中产生的错误及原因分析
在实验过程中出现以下错误，后经询问老师分析原因为
在这里插入图片描述

3．实验体会和收获。
通过该实验，在Navicat Premium可视化系统中对数据库进行一些列操作，掌握了存储过程、函数与游标的工作原理、定义及操作方法。
同时在实验中通过解决出现的问题与错误，在解决问题的过程中学到了更多的Mysql数据库相关的其他内容。通过后台的操控了解了更多可视化数据库管理系统的功能多样与数据库的用处之广。

实验五、触发器

一、实验目的与要求：
掌握触发器的工作原理、定义及操作方法
二、实验内容：
1.自动保存对所有表（至少fruits表和customers表）的插入、更新、删除操作到opertaion表中。
①增加opertaion表
在这里插入图片描述

源码：

create table opertaion(
	id int(11) primary key auto_increment not null,
	tablename varchar(50) not null,
	opname varchar(50) not null,
	optime datetime not null
)

②创建基于fruits表和customers表的插入、更新、删除触发器,该类触发器的功能是在执行相关操作时把操作相关信息写入opertaion表中。例如在fruits表中成功插入一项新的元组后，在opertaion表中自动增加一个元组，该元组描述了是对fruits表进行了插入操作。

CREATE TRIGGER insertFr AFTER INSERT ON fruits FOR EACH ROW
INSERT INTO opertaion(opname,optime) VALUES('INSERT',now());
CREATE TRIGGER updateFr AFTER UPDATE ON fruits FOR EACH ROW
INSERT INTO opertaion(opname,optime) VALUES('UPDATE',now());
CREATE TRIGGER deleteFr AFTER DELETE ON fruits FOR EACH ROW
INSERT INTO opertaion(opname,optime) VALUES('DELETE',now());
CREATE TRIGGER insertCu AFTER INSERT ON customers FOR EACH ROW
INSERT INTO opertaion(opname,optime) VALUES('INSERT',now());
CREATE TRIGGER updateCu AFTER UPDATE ON customers FOR EACH ROW
INSERT INTO opertaion(opname,optime) VALUES('UPDATE',now());
CREATE TRIGGER deleteCu AFTER DELETE ON customers FOR EACH ROW
INSERT INTO opertaion(opname,optime) VALUES('DELETE',now());

在这里插入图片描述
2.在fruits表中增加一个属性quantity(库存商品数量) ，数据类型int（5），默认值为1000

ALTER TABLE fruits
ADD quantity INT(5)
DEFAULT 1000

在这里插入图片描述
2. 创建触发器实现当客户下订单后，fruits表对应此商品的数量自动的发生变化（减少或增加）：
具体要求：

①在orderitems表中修改某个订单的商品数量后时， fruits表对应此商品的数量发生相应的改变。
②在orderitems表中增加一个购买详单（增加一个元组）时， fruits表对应此商品的数量发生相应的改变。

--①
CREATE TRIGGER  updateF2  
AFTER UPDATE ON orderitems 
FOR EACH ROW 
BEGIN
UPDATE fruits 
SET quantity = quantity + old.quantity - new.quantity
WHERE fruits.f_id = old.f_id;
END

--②
CREATE TRIGGER  insertF2  
AFTER INSERT ON orderitems 
FOR EACH ROW 
BEGIN
UPDATE fruits SET quantity = quantity - new.quantity
WHERE fruits.f_id = new.f_id;
END

在这里插入图片描述

小结

1．实验中遇到的问题及解决过程
实验过程中遇到如何通过一个数值的改变来改变另一个数值问题，后通过查找资料与实验解决了该问题。

2.实验中产生的错误及原因分析
实验中产生数据无法修改与插入的错误，后经检测与验证是由于修改或插入的数据导致主键重复了。

3．实验体会和收获。
通过该实验，在Navicat Premium可视化系统中对数据库进行一些列操作，掌握了触发器的工作原理、定义及操作方法。
同时在实验中通过解决出现的问题与错误，在解决问题的过程中学到了更多的Mysql数据库相关的其他内容。通过后台的操控了解了更多可视化数据库管理系统的功能多样与数据库的用处之广。

实验六、综合实验-水果商店进阶一

一、实验目的与要求：
综合运用SQL语言相关知识如变量、游标、函数、触发器等解决实际问题。

二、实验内容：
设计并完成以下实验，要求附上源码（非截图），测试效果截图
1.修正订单详情表orderitems中的水果价格与水果表fruits中的价格一致。

源码：

CREATE PROCEDURE priceOF()
BEGIN

DECLARE number INT;
DECLARE item_price decimal(8,2); 
DECLARE priceOrdFru CURSOR FOR 
SELECT f_price from fruits;
DECLARE CONTINUE HANDLER FOR 
NOT FOUND SET number=1;
SET number=0;
OPEN priceOrdFru;
FETCH next FROM priceOrdFru INTO item_price;

WHILE (number=0) DO
UPDATE orderitems 
SET item_price=(SELECT f_price FROM fruits
WHERE fruits.f_id = orderitems.f_id);
FETCH next FROM priceOrdFru INTO item_price;
END WHILE;
CLOSE priceOrdFru;

END

CALL priceOF();
SELECT* FROM orderitems

在这里插入图片描述
2.在订单详情表orderitems插入新订单时自动获得水果价格。

CREATE TRIGGER updateOrd_Fru BEFORE INSERT 
ON orderitems FOR EACH ROW
BEGIN
DECLARE price decimal(8,2);
SELECT f_price INTO price 
FROM fruits WHERE f_id=new.f_id;
SET new.item_price=price;
END

INSERT 
INTO orderitems(o_num,o_item,f_id,quantity)
VALUES(30006,5,'a0',52);
SELECT* FROM orderitems

在这里插入图片描述
3.在总的订单表orders中新增“原价格”、“折扣”“应付款”三个属性，三个属性要求如下：
①　属性名分别为original_price、discount、pay，数据类型都是decimal(10,2)；
②　“原价格”是自动统计“订单详情”表orderitems中同一订单的总金额，该属性要求非空，初值0；
③　“折扣”是当前折扣信息（后续根据VIP等级自动更新，本题用初值1计算），该属性要求非空，初值1；
“应付款”是打折后的价格，该属性要求非空，初值0。

ALTER TABLE orders
ADD  original_price decimal(10,2) not null
DEFAULT 0;
ALTER TABLE orders
ADD discount decimal(10,2) not null
DEFAULT 1;
ALTER TABLE orders
ADD pay decimal(10,2) not null
DEFAULT 0;

SELECT* FROM orders

在这里插入图片描述
设计实验完成以下三项功能
①　对总订单表orders修改已销售总订单

CREATE PROCEDURE updateOrd_Fru()
BEGIN
DECLARE number INT;
DECLARE original_price decimal(10,2); 
DECLARE updateOrdFru CURSOR FOR 
SELECT sum(quantity * item_price) AS SumSI
from orderitems;
DECLARE CONTINUE HANDLER FOR 
NOT FOUND SET number=1;
SET number=0;
OPEN updateOrdFru;
FETCH next FROM updateOrdFru INTO original_price;
WHILE (number=0) DO

UPDATE orders 
SET original_price =(SELECT sum(quantity * item_price)
AS SumSI
FROM orderitems
WHERE orderitems.o_num = orders.o_num);

FETCH next FROM updateOrdFru INTO original_price ;
END WHILE;
CLOSE updateOrdFru;
END
CALL updateOrd_Fru();
UPDATE orders SET pay = original_price * discount;
SELECT* FROM orders

在这里插入图片描述
②　在订单详情表orderitems新增订单项时同步修改总订单表orders相关属性值

CREATE TRIGGER  insertOrd_Ord1  
AFTER INSERT ON orderitems 
FOR EACH ROW 
BEGIN
UPDATE orders 
SET original_price = original_price + new.quantity * new.item_price
WHERE orders.o_num = new.o_num;
END

CREATE TRIGGER  insertOrd_Ord1  
AFTER INSERT ON orderitems 
FOR EACH ROW 
BEGIN
UPDATE orders 
SET original_price = original_price + new.quantity * new.item_price
WHERE orders.o_num = new.o_num;
END

INSERT 
INTO orderitems(o_num,o_item,f_id,quantity)
VALUES(30005,5,'a1',5);
SELECT* FROM orders

在这里插入图片描述
③　在订单详情表orderitems删除订单项时同步修改总订单表orders相关属性值

CREATE TRIGGER  deleteOrd_Ord1  
AFTER DELETE ON orderitems 
FOR EACH ROW 
BEGIN
UPDATE orders 
SET original_price = original_price - old.quantity * old.item_price
WHERE orders.o_num = old.o_num;
END

DELETE FROM orderitems
WHERE o_num = 30005 AND f_id = 'a1';
SELECT* FROM orders

在这里插入图片描述
④　在订单详情表orderitems修改订单项时同步修改总订单表orders相关属性值

CREATE TRIGGER  updateOrd_Ord0
AFTER UPDATE ON orderitems 
FOR EACH ROW 
BEGIN
UPDATE orders 
SET original_price = original_price + 
(new.quantity-old.quantity) * old.item_price
WHERE orders.o_num = new.o_num;
END

UPDATE orderitems
SET quantity = 15
WHERE o_num = 30005 AND f_id = 'a2';
SELECT* FROM orders

在这里插入图片描述

小结

1．实验中遇到的问题及解决过程
1.数据无法正确的逐个插入问题，通过询问老师与互联网工具最后通过游标工具正确的解决了该问题

2.在修改价格时，pay值未正确改变，通过认真审题与研究再次创建触发器解决了该问题。

2．实验中产生的错误及原因分析
1.[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘UPDATE orderitems
SET item_price=(SELECT f_price FROM fruits
WHERE fruits.f_i’ at line 7
原因：系统中为设置空值默认输入，需要输入语句：SET @@GLOBAL.sql_mode=“NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”;

2.[Err] 1442 - Can’t update table ‘orderitems’ in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
原因：在一个表的触发器中不能同时进行insert/update/delete操作，需要改为set操作

3．实验体会和收获。
通过该实验，在Navicat Premium可视化系统中对数据库进行一些列操作，实现了综合运用SQL语言相关知识如变量、游标、函数、触发器等解决实际问题。
同时在实验中通过解决出现的问题与错误，在解决问题的过程中学到了更多的Mysql数据库相关的其他内容。通过后台的操控了解了更多可视化数据库管理系统的功能多样与数据库的用处之广。通过多种操作的综合运用，能够更加熟练的更加便捷的使用数据库系统。

实验七、综合实验-水果商店进阶二

一、实验目的与要求：
综合运用SQL语言相关知识如变量、游标、函数、触发器等解决实际问题。

二、实验内容：
设计并完成以下实验，要求附上源码（非截图），测试效果截图

1.在客户表customers中添加“VIP”字段，默认值0，根据已购买的累计金额自动更新等级，如累计100199，一星VIP折扣9.9，200499二星VIP折扣9.7，500999三星VIP折扣9.5，10001999，四星VIP折扣9，后8.5折。
根据已购买情况修改客户表的VIP信息

源码

ALTER TABLE customers
ADD VIP decimal(8,2)
DEFAULT 0

CREATE PROCEDURE VIPCus()
BEGIN
DECLARE number INT;
DECLARE VIP bigint(50); 
DECLARE VIPps CURSOR FOR 
SELECT original_price from orders;
DECLARE CONTINUE HANDLER FOR 
NOT FOUND SET number=1;
SET number=0;
OPEN VIPps;
FETCH next FROM VIPps INTO VIP;
WHILE (number=0) DO

UPDATE customers,orders
SET VIP = CASE WHEN orders.original_price >= 100 
AND orders.original_price <= 199
THEN 0.99
WHEN orders.original_price >= 200 
AND orders.original_price <= 499
THEN 0.97 
WHEN orders.original_price >= 500 
AND orders.original_price <= 999
THEN 0.95 
WHEN orders.original_price >= 1000 
AND orders.original_price <= 1999
THEN 0.90
WHEN orders.original_price >= 2000 
THEN 0.85
WHEN orders.original_price <= 100 
THEN 0
END
WHERE orders.c_id = customers.c_id;

FETCH next FROM VIPps INTO VIP;
END WHILE;
CLOSE VIPps;
END

在这里插入图片描述
在订单表orders中插入新订单时自动计算并插入当前客户的折扣信息，注不修改原订单的折扣

UPDATE orders,customers 
SET discount = VIP
WHERE orders.c_id = customers.c_id;


CREATE TRIGGER discount_ord BEFORE INSERT 
ON orders FOR EACH ROW
BEGIN
DECLARE OP decimal(10,2);
SELECT original_price INTO OP
FROM orders
WHERE new.c_id=c_id;

SET new.discount=CASE 
WHEN OP+new.original_price >= 100 
AND OP+new.original_price <= 199
THEN 0.99
WHEN OP+new.original_price >= 200 
AND OP+new.original_price <= 499
THEN 0.97 
WHEN OP+new.original_price >= 500 
AND OP+new.original_price <= 999
THEN 0.95 
WHEN OP+new.original_price >= 1000 
AND OP+new.original_price <= 1999
THEN 0.90
WHEN OP+new.original_price >= 2000 
THEN 0.85
WHEN OP+new.original_price <= 100 
THEN 0
END;

END

在这里插入图片描述
查询指定客户的“总优惠金额”，即累计每次购买时为客户优惠金额。

SELECT SUM(original_price-pay) 
AS "总优惠金额" FROM orders
WHERE c_id = 10002

在这里插入图片描述

小结

1．实验中遇到的问题及解决过程
在实验过程中遇到discount与VIP字段的数据未根据预期的值改变问题，后通过修改触发器的语句顺序与数据更新方式解决了该问题。
2．实验中产生的错误及原因分析
2.1[Err] 1054 - Unknown column ‘original_price’ in ‘where clause’
由于在UPDATE语句中没有在UPDATE于SET之间写出调用的表，而导致表中的字段无法识别

2.2[Err] 1264 - Out of range value for column ‘VIP’ at row 1
由于在定义数值时未注意数值大小，而导致数值的范围超出限制而产生。

2.3[Err] 1359 - Trigger already exists
在对以创建触发器进行修改时，由于修改过程中从触发器的定义语句中修改，导致触发器重名，无法修改。

3．实验体会和收获。
通过该实验，在Navicat Premium可视化系统中对数据库进行一些列操作，实现了综合运用SQL语言相关知识如变量、游标、函数、触发器等解决实际问题。
同时在实验中通过解决出现的问题与错误，在解决问题的过程中学到了更多的MYSQL数据库相关的其他内容。通过数据库后台系统的操控了解了更多可视化数据库管理系统的功能与MYSQL数据库的用处之广。通过多种操作的综合运用，能够更加熟练的更加便捷的使用数据库系统。同时也了解了更多数据库在实际生活中的应用。

实验八、综合实验

一、实验目的与要求：

  1. 设计并绘出此数据库的E-R图
  2. 将E-R图转换成关系模式
  3. 针对已给定的数据库及应用需求，提出优化或改进措施并实现附上代码（至少三项）
    二、实验内容：
  4. 设计并绘出此数据库的E-R图
    5.在这里插入图片描述
  5. 将E-R图转换成关系模式
    客户（客户编号#，客户名，客户地址，城市，邮编，联系人，电子邮箱）
    订单（订单号#，订单项#，数量，单价）
    水果（水果编号#，水果名，价格）
    供应商（供应商编号#，供应商名，邮编，电话，城市）
    下（客户编号#，订单号#，订购日期）
    订购（订单号#，订单项#，水果编号#）
    提供（供应商编号#，水果编号#）

3.针对已给定的数据库及应用需求，提出优化或改进措施并实现附上代码（至少三项）
①.在customers表中插入price字段，实现自动计算用户的订单总价格，不需要跳转到其他表在通过订单号查看价格。节省时间成本，方便查看总价格。

源码

ALTER TABLE customers
ADD price decimal(10,2)

CREATE PROCEDURE updateCus_P1()
BEGIN
DECLARE number INT;
DECLARE price decimal(10,2); 
DECLARE updateOrdCUS CURSOR FOR 
SELECT sum(pay) AS SumSI
from orders;
DECLARE CONTINUE HANDLER FOR 
NOT FOUND SET number=1;
SET number=0;
OPEN updateOrdCUS;
FETCH next FROM updateOrdCUS INTO price;
WHILE (number=0) DO

UPDATE customers
SET price =(SELECT sum(pay)
AS SumSI
FROM orders
WHERE orders.c_id = customers.c_id);

FETCH next FROM updateOrdCUS INTO price ;
END WHILE;
CLOSE updateOrdCUS;
END

在这里插入图片描述
②.在原先的基础上创建触发器，使得能够自动保存对所有表的插入、更新、删除操作到opertaion表中，而不仅限于fruits表与customers表

CREATE TRIGGER insertORS AFTER INSERT ON orderitems FOR EACH ROW
INSERT INTO opertaion(tablename,opname,optime) 
VALUES('orderitems','INSERT',now());
CREATE TRIGGER updateORS AFTER UPDATE ON orderitems FOR EACH ROW
INSERT INTO opertaion(tablename,opname,optime) 
VALUES('orderitems','UPDATE',now());
CREATE TRIGGER deleteORS AFTER DELETE ON orderitems FOR EACH ROW
INSERT INTO opertaion(tablename,opname,optime) 
VALUES('orderitems','DELETE',now());

CREATE TRIGGER insertORD AFTER INSERT ON orders FOR EACH ROW
INSERT INTO opertaion(tablename,opname,optime) 
VALUES('orders','INSERT',now());
CREATE TRIGGER updateORD AFTER UPDATE ON orders FOR EACH ROW
INSERT INTO opertaion(tablename,opname,optime) 
VALUES('orders','UPDATE',now());
CREATE TRIGGER deleteORD AFTER DELETE ON orders FOR EACH ROW
INSERT INTO opertaion(tablename,opname,optime) 
VALUES('orders','DELETE',now());

CREATE TRIGGER insertSUP AFTER INSERT ON suppliers FOR EACH ROW
INSERT INTO opertaion(tablename,opname,optime) 
VALUES('suppliers','INSERT',now());
CREATE TRIGGER updateSUP AFTER UPDATE ON suppliers FOR EACH ROW
INSERT INTO opertaion(tablename,opname,optime) 
VALUES('suppliers','UPDATE',now());
CREATE TRIGGER deleteSUP AFTER DELETE ON suppliers FOR EACH ROW
INSERT INTO opertaion(tablename,opname,optime) 
VALUES('suppliers','DELETE',now());

在这里插入图片描述③.在suppliers表中添加fruit字段，使得可以直观的看到供应商可以提供哪些编号的水果，方便商店对供应商的选择

ALTER TABLE suppliers
ADD fruit char(10)

CREATE PROCEDURE updateSUP_Fru1()
BEGIN
DECLARE number INT;
DECLARE fruit char(10); 
DECLARE updateSUPFru CURSOR FOR 
SELECT GROUP_CONCAT(f_id) AS SumSI
from fruits;
DECLARE CONTINUE HANDLER FOR 
NOT FOUND SET number=1;
SET number=0;
OPEN updateSUPFru;
FETCH next FROM updateSUPFru INTO fruit;
WHILE (number=0) DO

UPDATE suppliers SET fruit = (
SELECT GROUP_CONCAT(f_id)
AS SumSI
FROM fruits
WHERE suppliers.s_id = fruits.s_id
GROUP BY s_id
);

FETCH next FROM updateSUPFru INTO fruit ;
END WHILE;
CLOSE updateSUPFru;
END

在这里插入图片描述

小结

1．实验中遇到的问题及解决过程
在E-B图的绘制过程中，由于对该数据库的部分属性不够了解，在各属性关系中不清楚是一对多还是多对多的关系。后通过仔细观察各个属性表的主外键等关系解决了该问题。
2．实验中产生的错误及原因分析
① [Err] 1364 - Field ‘s_name’ doesn’t have a default value
系统设置问题，没有设置插入空值自动填充，在系统中输入语句SET@@GLOBAL.sql_mode=“NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”;在重启系统即可解决。
② [Err] 1062 - Duplicate entry ‘100’ for key ‘PRIMARY’
对主键的输入重复了，将该值替换为其他值在输入
3．实验体会和收获
通过该实验，在Navicat Premium可视化系统中对数据库进行一些列操作，实现了综合运用SQL语言相关知识如变量、游标、函数、触发器等解决实际问题。
同时在实验中通过解决出现的问题与错误，在解决问题的过程中学到了更多的MYSQL数据库相关的其他内容。通过数据库后台系统的操控了解了更多可视化数据库管理系统的功能与MYSQL数据库的用处之广。通过多种操作的综合运用，能够更加熟练的更加便捷的使用数据库系统。同时也了解了更多数据库在实际生活中的应用。

Lin-CT
关注
专栏目录
数据库实验报告-存储过程、触发器
05-29
通过这些实验步骤，学生能够深入理解存储过程和触发器的工作原理及其在实际数据库应用中的作用。同时，这些操作也展示了如何在数据库管理系统中编写和管理这些数据库对象，有助于提升数据库设计和管理能力。
数据库编程-存储过程-触发器-级联更新-SQL-server-游标、全网最全最详细编程实战
05-30
- 在创建数据库对象前,如表、视图或存储过程等,需要检查是否已有同名对象存在,以避免命名冲突。 #### 四、注意事项 1. **调用存储过程时传递参数**: - 当调用存储过程时,确保按照参数列表正确传递参数。 - 参数...
数据库中视图、索引、存储过程的作用
yuer7916555的博客
07-31 586
      视图可以被看成是虚拟表或存储查询。可通过视图访问的数据不作为独特的对象存储在数据库内。数据库内存储的是 SELECT 语句。SELECT 语句的结果集构成视图所返回的虚拟表。用户可以用引用表时所使用的方法，在 Transact-SQL 语句中通过引用视图名称来使用虚拟表。使用视图可以实现下列任一或所有功能： 将用户限定在表中的特定行上。      例如，只允许雇员看见工作跟踪...
数据库实验六综合实验-水果商店进阶一
qq_43741419的博客
05-05 5828
文章目录一、实验目的与要求：二、实验内容1. 修正订单详情表orderitems中的水果价格与水果表fruits中的价格一致。2. 在订单详情表orderitems插入新订单时自动获得水果价格。3. 在总的订单表orders中新增“原价格”、“折扣”“应付款”三个属性，三个属性要求如下：三、实验小结 一、实验目的与要求： 综合运用SQL语言相关知识如变量、游标、函数、触发器等解决实际问题。 二、实验内容 设计并完成以下实验，要求附上源码（非截图），测试效果截图 1. 修正订单详情表orderitems中的
数据库原理实验二 数据库管理
最新发布
在下的博客
07-28 2244
基础实验参考2
数据表记录的操作和数据查询
weixin_75253589的博客
11-04 159
数据的插入，修改，删除，以及基本查询。。
2SQL数据库中的触发器和游标总结
m0_56022510的博客
08-22 431
SQL数据库中的触发器和游标总结 触发器 触发器是一种自动执行特定操作的结构，当对数据进行UPDATE，INSERT，DELECT的操作时触发器就会执行。 SQLserver支持DML（响应数据操作语言操作数据事件），DDL（响应数据定义语言事件），登录触发器（响应LOGON事件）三种触发器。 触发器的创作语句——GREAT TRIGGER参数详见下图 删除触发器语句——DROP TRIGGER 触发器名； 触发器有两种类型：前触发器（使用FOR或AFTER选项）和后触发器（使用INSTEAL OF选项）
实验八数据库编程技术——游标、存储过程与触发器.pdf
10-04
数据库编程技术——游标、存储过程与触发器 数据库编程技术是数据库管理系统中的一种重要技术，用于实现数据库的自动化管理和数据处理。本节实验重点介绍游标、存储过程和触发器三种数据库编程技术的应用。 一、...
数据库大型实验设计 包含游标触发器存储过程
12-22
本实验设计涉及的核心概念是数据库中的高级特性，如存储过程、触发器和游标，这些都是数据库管理系统（DBMS）中实现复杂业务逻辑和自动化操作的关键工具。 首先，我们来深入理解一下“存储过程”。存储过程是一组...
存储过程-触发器-ODBC数据库编程-实验报告.pdf
09-30
本报告详细介绍了使用ODBC（Open Database Connectivity）在数据库中进行编程的相关实验，包括了存储过程、触发器的创建和使用。ODBC是一种数据库访问技术，它允许应用程序通过标准化的方法连接不同的数据库管理系统...
《流程控制语句》实验报告
07-10
3、范例：输入若干字符，统计其中数字字符，白字符和其他字符的个数，输入EOF结束
MyPetShop源码
10-03
MyPetShop源码，适合学习研究，保证可运行
数据库基本操作总结（下）【数据控制、安全性和完整性】
nefu_ljw的博客
07-07 1158
一、授权（grant） 1.创建用户“S学号u1”，“S学号u2”，“S学号u3”，“S学号u4”，并为其赋予connect角色。 其中，学号是指你的学号，比如你的学号为2018214184，则你创建的用户应该是S2018214184u1，S2018214184u2，S2018214184u3，S2018214184u4。 create user S2018214184u1 identified by abc; # identified by 设置密码 grant connect to S201821418
数据库、数据表的基本操作及查询数据
04-01 292
数据库的基本操作 创建数据库 CREATE DATABASE database_name database_name为要创建的数据库的名称 删除数据库 DROP DATABASE database_name database_name为要删除的数据库的名称 数据库存储引擎 数据库存储引擎是数据库底层软件组成，数据库管理系统(DBMS)使用数据引擎进行创建、查询、更新和删除数据操作。 M...
MySQL数据库的查询操作与安全控制
jrrve的博客
09-01 320
MySQL单表查询 单表查询 ====================================== 准备测试表：company.employee5 雇员编号 id int 雇员姓名 name varchar(30) 雇员性别 sex enum 雇用时期 hire_date date 职位 post varchar(50) 职位描述 job_description varchar(100) 薪水 salary
数据库基本操作总结（上）【数据定义、数据操纵、数据查询】
nefu_ljw的博客
07-05 4881
以下操作均在Oracle数据库下运行，在某些地方的语法可能与MySQL不同。 另外，是否区分大小写也有不同。（MySQL与Oracle的大小写问题） SQL语言集数据定义、数据操纵、数据查询、数据控制功能于一体。 数据定义：create，drop，alter 数据操纵：增(insert into … )，删(delete from …)，改(update … set …) 数据查询：select 数据控制：grant(授予权限)，revoke(收回权限) 一、数据定义（create，alter，drop）
数据表基本操作——创建表、查看表、修改表、删除表
2402_83284138的博客
04-14 784
create table表名 （字段名1（空格）数据类型（空格）约束条件，字段名2（空格）数据类型（空格）约束条件，字段名3（空格）数据类型（空格）约束条件，……
MySQL数据库（五）视图、函数，存储过程，触发器，事件
崔庆贺的博客
06-14 618
成功不易，加倍努力！1 VIEW 视图2 FUNCTI3 PROCEDURE 存储过程4 TRIGGER触发器5 Event 事件5.1 Event 事件介绍5.2 Event 管理 1 VIEW 视图 视图：虚拟表，保存有实表的查询结果，相当于别名 创建方法： CREATE VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION] 查看视图定义： SHOW CREATE VI
数据库学习(四)：数据库安全性
wangqianqianya的博客
06-26 2094
数据库的安全性 指保护数据库以防止不合法的使用所造成的数据泄露、更改或破坏。 实现数据库系统安全性的主要技术和方法： 用户标识与识别 自主存取控制DAC：通过授权，控制对敏感数据的存取 强制存取控制MAC：对数据进行密级标注，符合密级要求的用户才可操纵数据。 视图 ：通过对不同用户定义不同视图，可以把要保密的数据对无权访问用户隐藏 审计：把用户对数据库的操作存入审计日志，便于重现。 ...
数据库实验：创建触发器与存储过程详解
"这篇实验报告主要探讨了数据库中的触发器和存储过程的创建，旨在帮助理解触发器的工作机制和不同类型，以及如何运用存储过程。实验内容涵盖了不同类型的触发器和存储过程的创建，包括限制工资额度、防止非法删除、...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值