目录
实验一：数据库和表
一、实验目的与要求：
１、掌握MySQL中如何创建数据库和表的方法
- 熟练掌握MySQL的数据类型、主键实体完整性的设置
- 参照完整性的定义及应用
- 插入数据
- 数据库的备份操作
二、实验内容：
１、创建名为fruitshop的数据库，并创建数据表fruits、customers（客户）、orderitems（订单详单）、suppliers（供货商）和orders（订单总表），表结构和约束条件如下：
表 1 fruits表结构
| 字段名 | 字段说明 | 数据类型 | 主键 | 外键 | 非空 | 唯一 | 自增 |
| f_id | 水果编号 | char(10) | Y | ？ | Y | Y | N |
| s_id | 供应商编号 | INT | N | ？ | Y | N | N |
| f_name | 水果名 | char(255) | N | ？ | Y | N | N |
| f_price | 价格 | decimal(8,2) | N | ？ | Y | N | N |
表 2 customers表结构
| 字段名 | 字段说明 | 数据类型 | 主键 | 外键 | 非空 | 唯一 | 自增 |
| c_id | 客户编号 | int | Y | ？ | Y | Y | Y |
| c_name | 客户名 | char(50) | N | ？ | Y | N | N |
| c_address | 客户地址 | char(50) | N | ？ | N | N | N |
| c_city | 城市 | char(50) | N | ？ | N | N | N |
| c_zip | 邮编 | char(10) | N | ？ | N | N | N |
| c_contact | 联系人 | char(50) | N | ？ | N | N | N |
| c_email | 电子邮箱 | char(255) | N | ？ | N | N | N |
表 3 orderitems表结构
| 字段名 | 字段说明 | 数据类型 | 主键 | 外键 | 非空 | 唯一 | 自增 |
| o_num | 订单号 | int | Y | ？ | Y | N | N |
| o_item | 订单项 | int | Y | ？ | Y | N | N |
| f_id | 水果编号 | char(10) | N | ？ | Y | N | N |
| quantity | 数量 | int | N | ？ | Y | N | N |
| item_price | 单价 | decimal(8,2) | N | ？ | Y | N | N |
注：orderitems表中o_num和o_item的组合构成主键。
表 4 suppliers表结构
| 字段名 | 字段说明 | 数据类型 | 主键 | 外键 | 非空 | 唯一 | 自增 |
| s_id | 供应商编号 | int | Y | ？ | Y | Y | Y |
| s_name | 供应商名 | char(50) | N | ？ | Y | N | N |
| s_city | 城市 | char(50) | N | ？ | N | N | N |
| s_zip | 邮编 | char(10) | N | ？ | N | N | N |
| s_call | 电话 | char(50) | N | ？ | Y | N | N |
表 5 orders表结构
| 字段名 | 字段说明 | 数据类型 | 主键 | 外键 | 非空 | 唯一 | 自增 |
| o_num | 订单号 | int | Y | ？ | Y | Y | Y |
| o_date | 订购日期 | datetime | N | ？ | Y | N | N |
| c_id | 客户编号 | int | N | ？ | Y | N | N |
源码：
create table fruits(
f_id char(10)not null comment'水果编号',
s_id int not null comment'供应商编号->,
f_name char(255)not null comment'水果名',
f_price decimal(8,2)not null comment'价格',
primary key(f_id),
constraint fk_s idforeign key(s_id)references suppliers(s_id)on delete cascade on update cascade
);create table customers(
c_id int not null auto_increment comment'客户编号',
c_name char(50)not null comment'客户名',
c_address char(50)default null comment'客户地址',
c_city char(50)default null comment'地址',
c_contact char(50)default null comment'联系人',
c_email char(255)default null comment'电子邮箱',
primary key(c_id)
);create table orderitems(
o_num int not null comment'订单号',
o_item int not null comment'订单项',
f_id char(10)not null comment'水果编号',
quantity int not null comment'数量',
item_price decimal(8,2)not null comment'单价',
primary key(o_num,o_item),
constraint fk_onum foreion key(o_num)references orders(onum)on delete cascade on update cascade,
constraint fkfid forcign kevf_d)references fuits(f-id)on delete cascade en update cascade
);create table suppliers(
s_id int not nuLL auto_increment comment'供应商编号',
s_name char(50)not null comment'供应商名',
s_city char(50)default null comment'城市',
s_zip char(10)default null comment'邮编',
s_call char(50)not null comment'电话',
primary key(s_id)
);create table orders(
o_num int not null auto_increment comment'订单号',
o_date datetime not null comment'订购日期',
c_id int not null comment'客户编号',
primary key(o_num),
constraint fk_C d foreion key(Cid)references customersCC-d)on delete cascade on upcate cascade
);运行测试结果截图:
2、向数据表fruits、customers、orderitems和suppliers、orders中插入给定的如下数据
fruits表数据
customers表数据
orderitems表数据
suppliers表数据
orders表数据
源码：
insert into fruits values('al','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('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('cO','101','plum','3.2');
insert into fruits values('b3','104','lemon','104');
insert into fruits values('m1','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','coconut','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');insert into customers values('10001','ReadHook','200 Street','Tianjin','300000','LiMing','LMing@163.com');
insert into customers values('10002','Stars','333 Fromage Lane','Dalian','116000','Zhangbo','JerryQhotmail.com');
insert into customers values('10003','Netbhood','I unny Place','Qingdao','266000','LuoCong','');
insert into customers values('10004','JOTO','829 Riverside Drive','Haikou','570000','YangShan','sam@hotmail.com');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',19.2');
insert into orderitems values('30002','1','b3','2','20');
insert into orderitems values('30003','1','cO','100','10');
insert into orderitems values('30004','1','o2','50','2.5');
insert into orderitems values('30005','1','cO','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');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');insert into orders values('30001','2018-09-0100:00:00','10001');
insert into orders values('30002','2018-09-1200:00:00','10003');
insert into orders values('30003','2018-09-3000:00:00','10004');
insert into orders values('30004','2018-10-0300:00:00','10002');
insert into orders values('30005','2018-10-0800:00:00','10001');
3、向数据表fruits、customers、orderitems和suppliers、orders中插入给定的如下数据,要求如下：
向数据表customers和suppliers中分别插入一条记录，新记录customers的C_ID属性值统一为10000，在suppliers的S_ID属性值统一为100，S_NAME数据为JMU，其余属性值为本人的真实信息；向orders表中插入编号为“50001”和“50002”，客户编号为“10000”（本人）的两条记录；向数据表fruits、orderitems表中插入分别插入五条与本人相关（即在对应表中的C_ID属性为10000，S_ID属性为100）的新记录；
源码：
insert into customers values ('10000',' Liang ','6#5#106',' Xiamen ',361021',' Zhoglian ','1990097142@qq.com');
insert into suppliers values ('100',' JMU ',' Xiamen ','361021','17895882825');
insert into orders values ('50001','2024-04-0112:00:00','10000');
insert into orders values ('50002','2024-04-0212:00:00','10000');
insert into fruits values ('f1','100',' peach ','5.5');
insert into fruits values (' el ','100',' blueberry ','8.8');
insert into fruits values ('d1','100',' pineapple ','7.7');
insert into fruits values (' gl ','100',' pitaya ','9.9');
insert into fruits values ('h1','100',' pumelo ','3.3');
insert into orderitems values ('50001','11','f1','33','5.5');
insert into orderitems values ('50002','22',' el ','44','8.8');
insert into orderitems values ('50001','33','d1','55','7.7');
insert into orderitems values ('50001','37',' gl ','88','3.7');
insert into orderitems values ('50002','47','h1','98','8.7');运行测试结果截图:
3.完成本次实验数据库的备份及还原操作：
（1）备份：mysqldump -uroot -p123456 数据库名 >目的地址及文件名.sql
源码：
mysqldump -uroot -p123456 --databases fruitshop>D:\mysql\fruitshop.sql
运行测试结果截图:
(2)还原备份数据 source 源地址及文件名.sql
先备份好数据库fruitshop，然后在MySQL中删除数据库，再显示数据库，发现数据库中已无原数据，使用source还原，引用原保存数据库的路径，回车显示数据已全部还原，表和表中数据均已恢复，说明还原数据库成功。
三、实验小结：
1．实验中遇到的问题及解决过程
（1）问题：在创建数据库和表时，由于未确定整体数据的外码就先创建表格，导致在表中实现表级完整性约束条件时出现报错，无法正确定义表级完整性约束条件。
解决过程：先整体分析各个表格中数据之间的联系，确定主码和外码，先定义无需外码的表格，再依次定义其他需要外码的表格，就可以正确定义约束条件了。
（2）问题：在插入数据时，发生字符串拼写和其他数据类型定义错误，导致后面插入对应数据时发生错误。
解决过程：认真检查前面插入的数据是否正确，检查到插入数据错误，使用delete ...from table...where...进行删除操作再重新插入，或者使用update table set...where...语句进行修改操作，修改完成后就可以正确插入后续语句了。
（3）问题：数据库备份出现错误，无法正确备份解决过程：认真检查并查看提示信息后，发现数据库备份操作不应该在MySQL控制台进行，而是应该实验cmd控制台进行备份，这样就可以成功备份了。
2.实验中产生的错误及原因分析
（1）错误：MySQL语法错误和拼写错误
原因分析：查看错误提示，发现没有正确按照MySQL的语法进行操作，导致无法正确定义表和插入更新数据。
（2）错误：插入的数据类型与字段定义不符
原因分析：在插入数据的过程中，部分数据类型和插入的值与字段定义不匹配，部分数据超出字段约束。
3．实验体会和收获。
通过本次实验，我深入理解了数据库的基本概念，对MySQL的基本操作和使用有了一定的了解，对于数据库的创建、表的创建以及表中定义、插入、删除、修改等基本操作有了熟练的掌握，在这次实验中，我学会了参照完整性的定义及应用等知识，对主码、外码的判断也掌握的更加牢固，我意识到数据安全和数据备份的重要性，并且学会了对数据库进行备份和还原。通过实验，我认识到理论和实践的差距，理解了不断学习和改进的重要性，我会在以后的实验中更加认真操作，牢固掌握数据库的重要知识。
实验二：查询
一、实验目的与要求：
1、掌握SQL单表及多表之间的查询
2、掌握统计及分组函数
二、实验内容：
1.简单查询
①从fruits表中检索s_id为100的供货商所供货的水果名和价格
源码：
select f_id,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 between 2.00 and 10.20
order by f_price desc,f_name;运行测试结果截图:
④在fruits表中，查询f_name中包含字母‘g’的记录
源码：
select f_id,f_name 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函数）
group_concat是一种用于MySQL数据库的聚合函数，用于将查询结果中的多个值连接成一个字符串，并返回一个包含这些值的单个字符串。通常用于将查询结果进行合并和展示。
或
源码：
select s_id,group_concat(f_name) as names from fruits group by s_id having count(f_name)>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 c_id from orders where o_num in(select o_num from orderitems where 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 s_id,f_name from fruits
where s_id=(select s1.s_id from suppliers as s1 where s1.s_city='Tianjin');运行测试结果截图:
⑤查询订单为‘30005’的所有水果供货商的名称
源码：
select o1.o_num,o3.s_name from orderitems as o1,fruits as o2,suppliers as o3
where o1.o_num=30005 and o1.f_id=o2.f_id and o2.s_id=o3.s_id;运行测试结果截图:
⑥查询销量最多的水果名称
源码：
select fruits.f_name from fruits
join orderitems on orderitems.f_id=fruits.f_id
group by fruits.f_name
order by sum(quantity)desc
limit 1;运行结果：
⑦显示购买了’107’号供货商所有水果的用户信息
步骤：先向数据表orders表中插入两条记录（50010,当前时间,10000）和（50008,当前时间,10004），在orderitems表中插入五条记录（50010，1，b5，10，3.6）、（50010，2，b2，5，7.6）、（50010，3，t2，7，3.6）、（50008，1，b1，10，102）、（50008，2，b5，10，3.6）；然后再做题
源码：
insert into orders values('50010','2024-04-05 15:45:00','10000');
insert into orders values('50008','2024-04-05 15:45:00','10004');
insert into orderitems values('50010','1','b5','10','3.6');
insert into orderitems values('50010','2','b2','5','7.6');
insert into orderitems values('50010','3','t2','7','3.6');
insert into orderitems values('50008','1','b1','10','102');
insert into orderitems values('50008','2','b5','10','3.6');
运行测试结果截图:
源码：
select distinct c.* from customers c where not exists(
select * from suppliers s join fruits f on f.s_id=s.s_id
where s.s_id = '107' and not exists(
select * from orderitems oi
join orders o on o.o_num = oi.o_num
where oi.f_id = f.f_id and o.c_id =c.c_id)
);运行结果：
三、思考题：
结合实际应用并分析查找给定表中结构或数据是否存在问题，如何改进？
在上述查询中，可能存在以下问题：
1. 数据冗余：如果供应商提供了相同的水果给同一个供应商，可能会导致重复的结果。
2. 性能问题：如果表中数据量很大，如按水果名称、供应商或客户查询，上述查询可能会导致性能问题。
3.敏感信息：如电话号码、地址等信息得到适当保护，且只有授权用户能够访问。
为了改进这些问题，可以考虑以下方案：
1. 使用DISTINCT关键字去除重复的结果，对数据库表的设计进行规范化，以消除数据冗余。
2. 可以对数据库表进行适当的索引优化，以提高查询性能。
3.限制对敏感数据的访问，确保只有授权用户能够查看或修改这些信息，使用加密技术保护敏感数据，如电话号码。
另外，在实际应用中，还需要根据具体的业务需求和数据库性能进行优化，可能需要进一步的调整和改进。
四、实验小结：
1．实验中遇到的问题及解决过程
（1）问题：查询结果不符合预期。
解决过程：认真检查查询操作输入的内容是否正确，发现存在输入内容的表名错误和查询项列名不正确等错误，修改后再重新输入，得到预期查询结果。
（2）问题：在插入数据时，发生字符串拼写和其他数据类型定义错误，导致后面插入对应数据时发生错误。
解决过程：认真检查前面插入的数据是否正确，检查到插入数据错误，使用delete ...from table...where...进行删除操作再重新插入，或者使用update table set...where...语句进行修改操作，修改完成后就可以正确插入后续语句了。
2.实验中产生的错误及原因分析
（1）错误：MySQL语法错误和拼写错误
原因分析：查看错误提示，发现没有正确按照MySQL的规范语法进行查询操作，导致无法正确查询表中数据和连接表格进行查询。
（2）错误：插入的数据类型与字段定义不符
原因分析：在插入数据的过程中，部分数据类型和插入的值与字段定义不匹配，部分数据超出字段约束。
3．实验体会和收获。
通过本次实验，我对MySQL单表查询和多表查询的基本操作和使用有了一定的了解，对于统计及分组函数的应用也有了熟练的掌握，在这次实验中，我学会了通过表中相同项对不同表进行连接，对MySQL的各项操作也掌握的更加牢固，并且提高了自己解决问题的能力 。通过实验，我认识到理论和实践的差距，理解了不断学习和改进的重要性，我会在以后的实验中更加认真操作，牢固掌握数据库的重要知识。
实验三：安全性
一、实验目的与要求：
1、设计用户子模式
2、根据实际需要创建用户角色及用户，并授权
3、针对不同级别的用户定义不同的视图，以保证系统的安全性
二、实验内容：
1.先创建四类用户角色：
管理员角色Admin、客户角色Cusm、供货商角色Supp、商家销售工作人员角色Salor。
源码：
create role Admin;
create role Cusm;
create role Supp;
create role Salor;2.创建客户视图、供货商视图
（1）客户视图要求显示所有客户号及对应客户下的所有订单及每个订单的价格
源码：
create view customers_view as
select customers.c_id,orders.o_num,sum(quantity*item_price) as price_sum
from customers
join orders on customers.c_id = orders.c_id
join orderitems on orderitems.o_num = orders.o_num
group by orders.o_num;效果：
（2）供货商视图要求显示所有供货商号及供货商所供水果信息
源码：
create view suppliers_view as
select f.s_id,f.f_name,f.f_id,f.f_price from fruits f
join suppliers s on f.s_id = s.s_id;效果：
3.为以上四类用户角色授予合适的权限，要求：
（1）管理员拥有所有权限:
源码：
grant all on fruitshop.* to'Admin';（2）客户只能查看与之相关的信息（基本信息，购买信息）
源码：
create view c_view as
select c.c_id,c.c_name,c.c_address,c.c_city,c.c_zip,c.c_contact,c.c_email,o.o_num,o.o_date,oi.f_id
from customers c
join orders o on c.c_id=o.c_id
join orderitems oi on o.o_num=oi.o_num;
create view my_view as
select * from c_view
where c_id = cast(user() as unsigned);
grant select on my_view to ‘Cusm’;（3）供货商只能查看与之相关的信息(可以查看自己供货的水果信息)。
源码：
create view s_view as
select s.s_id,s.s_name,s.s_city,s.s_zip,s.s_call,f.f_id,f.f_name,f.f_price
from suppliers s
join fruits f on s.s_id = f.s_id;
create view mysupp_view as
select * from s_view
where s_id = cast(user() as unsigned);
grant select on mysupp_view to 'Supp';（4）商家销售工作人员可以查看数据库并录入信息，但不能修改、删除；
源码：
grant select,insert on fruitshop.* to 'Salor';
revoke update,delete on fruitshop.* from 'Salor';4.为每类用户角色至少创建一个用户（新建用户并分配对应的角色，统一设置初始密码为’123456’）
注意：附上源码，创建用户并授权成功后在交互式运行状态下查看每个用户权限并截图
客户用户：C001，任意主机地址，授权为客户角色的所有权限；
供货商用户：S001，任意主机地址，授权为供货商角色的所有权限；；
商家销售工作人员用户：B001，任意主机地址，授权为销售工作人员角色的所有权限；；
管理员用户：A001，本机，拥有所有权限
源码：
create user'C001'@'%' identified by'123456';
grant Cusm to'C001'@'%';
create user'S001'@'%' identified by'123456';
grant Supp to'S001'@'%';
create user'B001'@'%' identified by'123456';
grant Salor to'B001'@'%';
create user'A001'@'localhost' identified by'123456';
grant Admin to'A001'@'localhost';5．测试：
退出已用root账号登陆的MySQL服务器，用这四类用户帐号分别登陆MySQL服务器并测试其功能（在交互式运行状态下测试，先附上测试源码，关键结果截图）。
测试要求：退出root帐号,重新登陆指定帐号，显示当前数据库中所有表，查看权限内某张表内的所有信息，对权限内某张表进行增、删、改、查操作并查看能否正确执行这些操作，如果执行错误请说明导致此错误的原因。
注意：每次登录后先设置当前角色，否则无效果：set role 角色名；
测试客户用户：
C:\Users\asus>mysql -uC001 -p123456
set role Cusm;
use fruitshop;
select * from my_view;
由于C001在数据库中没有信息，所以查询为空
create user'10001'@'localhost' identified by'123456';grant Cusm to'10001'@'localhost';在MySQL控制台创建用户‘10001’并将客户角色的权限授予给用户：
C:\Users\asus>mysql -u10001 -p123456
set role Cusm;
use fruitshop;
select * from my_view;
查询用户名为‘10001’的基本信息和购买信息，可以正确查询，但无删除等其他权限
测试供货商用户:
C:\Users\asus>mysql -uS001 -p123456
set role Supp;
use fruitshop;
select * from mysupp_view;
由于S001在数据库中没有信息，所以查询为空
create user '101'@'localhost' identified by'123456';grant Supp to'101'@'localhost';在MySQL控制台创建用户‘101’并将客户角色的权限授予给用户：
C:\Users\asus>mysql -u101 -p123456
set role Supp;
use fruitshop;
select * from mysupp_view;
查询用户名为‘101’的基本信息和购买信息，可以正确查询，但无删除等其他权限
测试商家销售工作人员用户：
C:\Users\asus>mysql -uB001 -p123456
set role Salor;
use fruitshop;
select * from fruits;
insert into fruits values('x1','101','pingguo','4.20');可以查询和插入数据
不能进行删除和修改操作
测试管理员用户：
C:\Users\asus>mysql -uA001 -p123456
set role Admin;
use fruitshop;
select * from orders;
管理员拥有所有权限
三、实验小结：
1．实验中遇到的问题及解决过程
（1）问题：在授予客户和供货商相应的权限时，发现虽然可以授权成功，但是无法实现只能查看自己的信息这一条件，仍能看到其他用户的全部信息。
解决过程：先根据需要查询的内容新建一个试图，然后再创建一个仅供指定用户使用的视图，使用cast(user() as unsigned)这一函数来实现此功能，每次查询任意用户时，只需在MySQL控制台中新建用户并授予权限，即可查询该用户的个人信息而不能查询到其他用户的信息。
（2）问题：用cmd命令行打开窗口进行测试时，无法正常进行登录。
解决过程：认真检查输入的内容是否有误，检查后发现登录的用户名并未创建，于是使用已有的用户名进行登录测试，发现可以正常进行测试。
（3）问题：创建用户的过程中忽略了对用户的授权，在测试阶段显示权限为空。
解决过程：创建用户后对用户授予相应的权限，将角色的权限转移给用户，使得具体的用户也拥有对数据进行操作的权限。
2.实验中产生的错误及原因分析
（1）错误：对客户授权失误
原因分析：代码实现出现错误，无法实现客户的一对一查询，只能实现一对多的查询，容易造成信息泄露。
（2）错误：测试用户阶段无法使用fruitshop的数据库
原因分析：在创建对应的角色和用户后，忽略了将角色权限转授给用户的操作，使得激活角色后用户无法对数据库进行操作。
3.实验体会和收获。
通过本次实验，我学会了如何设计用户子模式，并且学会了如何根据需要创建不同的用户及角色，并且对用户和角色进行授权，我明白了要针对不同级别的用户定义不同的视图，以保证系统的安全性。在这次实验中，我也遇到了很多问题，通过上网查询和询问同学，才得以顺利解决问题。通过实验，我深刻认识到理论和实践的差距，理解了不断学习和改进的重要性，我会在以后的实验中更加认真操作，牢固掌握数据库的重要知识。
实验四 存储过程及函数
一、实验目的与要求：
1、掌握存储过程的工作原理、定义及操作方法
2、掌握函数的工作原理、定义及操作方法
3、掌握游标的工作原理、定义及操作方法
二、实验内容：
1. 创建存储过程，用来自动统计给定订单号的订单总金额
源码：
CREATE PROCEDURE order_sum(IN o_num INT)
BEGIN
DECLARE sum_amount DECIMAL(8, 2);
SELECT SUM(quantity * item_price) INTO sum_amount
FROM orderitems
join orders on orderitems.o_num = orders.o_num;
SELECT sum_amount;
END运行测试结果截图（输入订单号'50010'测试结果）：
2.创建存储过程，自动搜索并添加客户及供货商帐号信息到新建的用户信息表。
①增加用户表信息user
表1 user表结构
| 字段名 | 字段说明 | 数据类型 | 主键 | 外键 | 非空 | 唯一 | 自增 |
| id | ID号 | int (11) | Y | N | Y | Y | Y |
| u_id | 用户编号 | int (11) | N | N | Y | Y | N |
| pwd | 密码 | blob | N | N | Y | N | N |
| remark | 注释 | varchar (255) | N | N | Y | N | N |
源码：
create table user(
id int(11) not null unique auto_increment comment'ID号',
u_id int(11) not null unique comment'用户编号',
pwd blob not null comment'密码',
remark varchar(255) not null comment'注释',
primary key(id)
);② 创建两个存储过程，分别把客户表的c_id和供货商表s_id的字段自动添加到用户信息表,补充pwd和remark字段。
要求：id字段自动增加，u_id 字段即客户或供货商的编号，pwd字段用AES_ENCRYPT函数加密，密码统一设置为用户编号u_id的值连接123456（如在当前表中u_id为10001，则其密码是10001123456），密钥是'hello'; remark字段内容是‘customer'或'supplier’
源码：
添加客户表帐号：
CREATE PROCEDURE insertCusm()
BEGIN
INSERT INTO user(u_id,pwd,remark)
SELECT c_id,AES_ENCRYPT(CONCAT(c_id, '123456'), 'hello'),'customer' FROM customers;
END运行测试结果截图：
添加供货商帐号：
CREATE PROCEDURE insertSupp()
BEGIN
INSERT INTO user(u_id,pwd,remark)
SELECT s_id,AES_ENCRYPT(CONCAT(s_id, '123456'), 'hello'),'supplier' FROM suppliers;
END运行测试结果截图：
3.创建存储过程或函数来批量修正订单详情表orderitems中的水果价格与水果表fruits中的价格一致。
提示：用游标
源码：
CREATE FUNCTION update_orderitems_price()
RETURNS INT DETERMINISTIC
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE fruit_id CHAR(10);
DECLARE fruit_price DECIMAL(8, 2);
DECLARE cur CURSOR FOR
SELECT f_id,f_price FROM fruits;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
read_loop: LOOP
FETCH cur INTO fruit_id, fruit_price;
IF done THEN
LEAVE read_loop;
END IF;
UPDATE orderitems
SET item_price = fruit_price
WHERE f_id = fruit_id;
END LOOP;
CLOSE cur;
RETURN 1;
END 运行测试结果截图:
三、实验小结：
1．实验中遇到的问题及解决过程
（1）问题：创建存储过程时出现错误，不能正常运行
解决过程：认真检查存储过程中格式是否正确，发现在编写代码时忘记添加数据类型以及连接表出错，重新添加修改后可以正常运行。
（2）问题：创建自动添加信息表的过程中密码设置错误
解决过程：编写代码时，在密码‘123456’前添加‘u_id’字段，将‘u_id’的值与‘123456’连接，可以成功设置密码。
2.实验中产生的错误及原因分析
（1）错误：创建自动添加信息表的过程中信息不匹配
原因分析：将客户表的c_id自动添加到用户信息表中时，由于未设置remark字段内容，导致出现报错。
（2）错误：无法更新orderitems表中的数据
原因分析：在循环中，没有从游标中读取数据，因此无法更新orderitems表中的数据，需要在循环中使用FETCH语句来获取游标中的数据。
3．实验体会和收获。
通过本次实验，我掌握了存储过程、函数和游标的工作原理、定义和操作方法。通过对这些概念和内容的实践以及实际操作，我更加深入地理解了数据库编程的概念和技术，这次实验大大提高了我在数据库开发和管理中的能力和效率，同时还加深了我对数据库系统的整体理解。在这次实验中，我也遇到了很多问题，通过上网查询和询问同学，才得以顺利解决问题。通过实验，我深刻认识到理论和实践的差距，理解了不断学习和改进的重要性，我会在以后的实验中更加认真操作，牢固掌握数据库的重要知识。
实验五 审记及触发器
一、实验目的与要求：
1.了解MySQL审计功能及实现方式
2.掌握触发器的工作原理、定义及操作方法
二、实验内容：
注：
- 在同一个触发器内编写多行代码，需要用结构begin ……end
- 函数current_user()获得当前登录用户名
1.自动保存对所有表（至少fruits表和orderitems表）的插入、更新、删除操作到opertaion表中。
①增加opertaion表
表1 opertaion表结构
| 字段名 | 字段说明 | 数据类型 | 主键 | 外键 | 非空 | 唯一 | 自增 |
| id | ID号 | int (11) | Y | N | Y | Y | Y |
| tablename | 表名 | varchar (50) | N | N | Y | N | N |
| opname | 操作类型 | varchar (50) | N | N | Y | N | N |
| optime | 操作时间 | datetime | N | N | Y | N | N |
| ConnUser | 用户名 | varchar(30) | N | N | Y | N | N |
源码：
create table opertaion(
id int(11) primary key auto_increment not null unique comment'ID号',
tablename varchar(50) not null comment'表名',
opname varchar(50) not null comment'操作类型',
optime datetime not null comment'操作时间',
ConnUser varchar(30) not null comment'用户名'
);②创建基于fruits表和orderitems表的插入、更新、删除触发器,该类触发器的功能是在执行相关操作时把操作相关信息写入opertaion表中。例如在fruits表中成功插入一项新的元组后，在opertaion表中自动增加一个元组，该元组描述了是对fruits表进行了插入操作。
源码：
CREATE TRIGGER insert_fruit
AFTER INSERT ON fruits
FOR EACH ROW
BEGIN
INSERT INTO opertaion (tablename, opname, optime, ConnUser)
VALUES ('fruits', 'INSERT', NOW(), USER());
END;
CREATE TRIGGER update_fruit
AFTER UPDATE ON fruits
FOR EACH ROW
BEGIN
INSERT INTO opertaion (tablename, opname, optime, ConnUser)
VALUES ('fruits', 'UPDATE', NOW(), USER());
END;
CREATE TRIGGER delete_fruit
AFTER DELETE ON fruits
FOR EACH ROW
BEGIN
INSERT INTO opertaion (tablename, opname, optime, ConnUser)
VALUES ('fruits', 'DELETE', NOW(), USER());
END;
CREATE TRIGGER insert_orderitem
AFTER INSERT ON orderitems
FOR EACH ROW
BEGIN
INSERT INTO opertaion (tablename, opname, optime, ConnUser)
VALUES ('orderitems', 'INSERT', NOW(), USER());
END;
CREATE TRIGGER update_orderitem
AFTER UPDATE ON orderitems
FOR EACH ROW
BEGIN
INSERT INTO opertaion (tablename, opname, optime, ConnUser)
VALUES ('orderitems', 'UPDATE', NOW(), USER());
END;
CREATE TRIGGER delete_orderitem
AFTER DELETE ON orderitems
FOR EACH ROW
BEGIN
INSERT INTO opertaion (tablename, opname, optime, ConnUser)
VALUES ('orderitems', 'DELETE', NOW(), USER());
END;③测试：用B001帐号（即销售人员帐号）登录，在fruits表中插入一条水果信息，再查看opertaion表是否实现自动添加更新记录。
运行测试结果截图：
2. 实现自动更新fruits表中商品数量
①在fruits表中增加一个属性quantity(库存商品数量) ，数据类型int（5），默认值为1000
源码：
alter table fruits add quantity int(5) default 1000;运行测试结果截图：
②创建触发器实现当客户下订单后，fruits表对应此商品的数量自动的发生变化（减少或增加）：
具体要求：
（1）在orderitems表中修改某个订单的商品数量后时， fruits表对应此商品的数量发生相应的改变。
源码：
CREATE TRIGGER update_fruit_quantity
AFTER UPDATE ON orderitems
FOR EACH ROW
BEGIN
DECLARE a INT;
SET a=(SELECT quantity FROM fruits WHERE f_id = NEW.f_id);
IF OLD.quantity <> NEW.quantity AND NEW.quantity <= a THEN
UPDATE fruits
SET quantity = quantity + OLD.quantity - NEW.quantity
WHERE f_id = NEW.f_id;
END IF;
IF NEW.quantity > a THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid value for column, insert operation aborted.';
END IF;
END;运行测试结果截图：
当把quantity改为10时，水果商店的库存会减少，由1000变为995
当quantity=3000时，远超于水果商店的库存量，此时会出现报错，而原数据库的内容不会发生更改
（2）在orderitems表中增加一个购买详单（增加一个元组）时， fruits表对应此商品的数量发生相应的改变。
源码：
CREATE TRIGGER insert_fruit_quantity
AFTER INSERT ON orderitems
FOR EACH ROW
BEGIN
DECLARE a INT;
SET a=(SELECT quantity FROM fruits WHERE f_id = NEW.f_id);
IF NEW.quantity <= a THEN
UPDATE fruits
SET quantity = quantity - NEW.quantity
WHERE f_id = NEW.f_id;
END IF;
IF NEW.quantity > a THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid value for column, insert operation aborted.';
END IF;
END; 运行结果截图：
3．（可选做）用Mysql8审计功能实现保存非root用户的更新操作（ binlog + init-connect ）：
(1)创建审计数据库auditdb及表accesslog；
源码：
(2)为普通用户赋予写入表'accesslog'权限（每个非Root用户都要先授权，否则无法与数据库建立连接）；
源码：
(3)配置init-connect，用于当普通用户对目标数据库进行操作时自动写入审记表中，在my.ini（Linux系统下是my.cnf）中配置；
源码：
(4)开启mysql-binlog日志记录，在my.ini中配置：
(5)重启mysql
源码：
测试效果截图：
三、实验小结：
1．实验中遇到的问题及解决过程
（1）问题：创建触发器时，遇到了语法错误
解决过程：认真检查SQL的语法，确认是否遵循了正确的触发器定义语法，仔细检查更改后可以正确实现功能。
（2）问题：创建完触发器进行测试时无法正确插入数据
解决过程：仔细分析问题提示，发现由于先在水果表中添加了数量的属性，导致插入内容与表中定义不匹配，删除数量属性再进行插入时可以正确执行。
2.实验中产生的错误及原因分析
（1）错误：触发器执行后，数据没有按预期更新。
原因分析：触发器的操作语句可能存在逻辑错误，或者触发器的触发条件设置不正确。
（2）错误：在orderitems表中插入一组元组时，无法正确插入
原因分析：插入元组时未考虑元组中的数据是否符合参照完整性，插入了一些其他表中未有的数据，发生插入错误。
3．实验体会和收获。
通过本次实验，我对MySQL的审计功能和实现方式有了具体的了解，同时也更深入掌握了触发器的工作原理、定义及方法。审计功能可以记录数据库的操作日志，有助于监控数据库的安全性和完整性，提高数据的可靠性。触发器是一种特殊的存储过程，可以在某些特定的数据库操作发生时自动触发执行相应的操作，可以用于实现数据约束、数据一致性等功能。这次实验大大提高了我在数据库开发和管理中的能力和效率，同时还加深了我对数据库系统的整体理解。通过实验，我深刻认识到理论和实践的差距，理解了不断学习和改进的重要性，我会在以后的实验中更加认真操作，牢固掌握数据库的重要知识。
附：
1．解密mysql-bin查看内容（日志文件）
注意进入日志文件路径后再操作
C:\ProgramData\MySQL\MySQL Server 8.0\Data>mysqlbinlog --base64-output=decode-row -vv mysql-bin.000002 > ./mysql-bin.000002--vv.log
2．查看日志文件内容
C:\ProgramData\MySQL\MySQL Server 8.0\Data>more mysql-bin.000002--vv.log
3．根据thread_id号去找init-connect记录表的登录用户和源ip
mysql -u root -p123456 -e "select * from auditdb.accesslog where connectionID=8 ;"
4.下载并安装MySQL Binlog Digger可视化分析日志
模拟插入或删除表中的一行，再用MySQL Binlog Digger显示
实验六 综合实验-水果商店进阶一
一、实验目的与要求：
综合运用SQL语言相关知识如变量、游标、函数、触发器等解决实际问题。
二、实验内容：
设计并完成以下实验，要求附上源码（非截图），测试效果截图
- 在订单详情表orderitems插入新订单时自动获得水果价格。
源码：
CREATE TRIGGER InsertOrderitems
BEFORE INSERT ON orderitems
FOR EACH ROW
BEGIN
DECLARE fruit_price DECIMAL(8,2);
SELECT f_price INTO fruit_price FROM fruits WHERE f_id = NEW.f_id;
IF fruit_price IS NOT NULL THEN
SET NEW.item_price = fruit_price;
END IF;
END运行测试结果截图:
2.在orders表(总订单)中新增三个属性original_price、discount、pay，说明如下：
①属性名分别为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;运行测试结果截图:
设计实验完成以下三项功能
①对总订单表orders修改已销售总订单
源码：
update orders o join(
select o_num,sum(quantity * item_price) as total_price from orderitems
group by o_num)
sub on o.o_num = sub.o_num
set o.original_price = sub.total_price;update orders set pay = original_price * discount;运行测试结果截图:
②在订单详情表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;
UPDATE orders
SET pay = original_price * discount;
END运行测试结果截图:
Original_price增加520，变为5497.40
③在订单详情表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;
UPDATE orders
SET pay = original_price * discount;
END运行测试结果截图:
Original_price减少520，变为4977.40
④在订单详情表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;
UPDATE orders
SET pay = original_price * discount;
END运行测试结果截图:
Quantity由400变为200，Original_price减少2240，变为2737.40
三、实验小结：
1．实验中遇到的问题及解决过程
问题：创建触发器时，遇到了语法错误
解决过程：认真检查SQL的语法，确认是否遵循了正确的触发器定义语法，仔细检查更改后可以正确实现功能。
2.实验中产生的错误及原因分析
（1）错误：触发器执行后，数据没有按预期更新。
原因分析：触发器的操作语句可能存在逻辑错误，或者触发器的触发条件设置不正确。
（2）错误：在orders表中更新元组时，无法正确更新
原因分析：更新元组时未考虑元组中的数据是否符合参照完整性，更新部分列的属性在其他表中不存在，发生了错误。
3．实验体会和收获。
通过本次实验，我学会了运用SQL语言中的变量、游标、函数和触发器等知识来解决实际问题。变量在SQL语言中可以帮助我们存储和操作数据，在实验中我使用变量来存储中间结果，简化操作过程，提高代码的可读性和可维护性。触发器可以在数据库中监视某些事件的发生，并在事件发生时自动执行相应的操作。在实验中，我使用触发器来实现数据的自动更新和校验，提高数据的完整性和一致性。这次实验大大提高了我在数据库开发和管理中的能力和效率，同时还加深了我对数据库系统的整体理解。通过实验，我深刻认识到理论和实践的差距，理解了不断学习和改进的重要性，我会在以后的实验中更加认真操作，牢固掌握数据库的重要知识。
实验七 事务
一、实验目的与要求：
1.熟悉提交事务
2.回滚事务
3.检查点技术
注：可以用可视化软件来实现
二、实验内容：
基于现有数据库设计事务提交、事务回滚、及检查点实验，观察比较提交前后执行结果并分析。
源码：
C:\Users\asus>mysql -uA001 -p123456
set role Admin;
use fruitshop;
set autocommit = 0;
select * from test;
start transaction;
insert into test values(1,'a');
commit;start transaction;
insert into test values(2,'b');
select * from test;
rollback;
select * from test;start transaction;
insert into test values(2,'b');
savepoint L1;
insert into test values(3,'c');
savepoint L2;
insert into test values(4,'d');
select * from test;
rollback to L2;
select * from test;
rollback to L3;
ERROR 1305 (42000): SAVEPOINT L3 does not exist
commit;运行测试结果截图:
1.插入数据（1，’a’），并提交，表中出现插入数据
2.在命令行中查询，数据成功添加，但由于未提交，仍为原数据
3.插入数据（2，’b’），回滚到上次提交的事务，test表中仍为原数据，未出现新插入数据
4.再插入数据(2,’b’),设置检查点L1，插入数据(3,’c’),设置检查点L2，插入数据(4,’d’),查询test表
5.回滚至检查点L2，再提交，L2之后的事务将不被执行，此时在可视化软件中查看已插入数据
解释原因：
插入数据（1,’a’）时，正在执行此事务，需要提交后才结束事务，实现成功插入；之后再插入数据(2,’b’),由于正在执行此事务，回滚后事务重新开始但始终未结束，test表中仍为原来的数据；然后再插入数据(2,’b’)，设置检查点L1，插入数据(3,’c’)，设置检查点L2，插入数据(4,’d’)，回滚至L2，正在执行的只有插入数据(2,’b’)和插入数据(3,’c’)的事务，不执行插入数据(4,’d’),回滚至L3，未设置检查点L3，再提交，最终test表中只有前三条数据，而无数据(4,’d’)。
结论：无检查点的事务，要成功commit后才可成功执行；
有检查点的事务，只有回滚到指定检查点前的事务才可成功执行。
三、实验小结：
1．实验中遇到的问题及解决过程
问题：可视化软件中未出现插入的数据
解决过程：在命令行中输入commit，提交事务后可以正确插入数据。
2.实验中产生的错误及原因分析
错误：回滚到错误的检查点
原因分析：输入了未设置的检查点，导致操作无法执行。
3．实验小结：
本次实验主要围绕提交事务、回滚事务以及检查点技术展开。通过实际操作，我深入了解了如何提交事务以确保数据的完整性和一致性，以及如何回滚事务来撤销对数据库的更改。此外，我还学习了检查点技术，它可以帮助在数据库发生故障时恢复到事务执行之前的状态。在实验过程中，我发现提交事务是非常重要的，它可以确保数据的安全性，避免数据丢失或损坏。而回滚事务则是一种有效的手段，可以在事务执行错误时撤销已经做出的更改，保证数据的一致性。通过本次实验，我更加熟悉了提交事务、回滚事务和检查点技术的操作方法，对数据库的管理和维护有了更深入的理解。在今后的实践中我会更加熟练地运用这些技术，保障数据库的安全性和稳定性。
附：
set role 角色名； 作用是当前帐号生效
set autocommit=0; 关闭自动提交
show databases; 查看所有数据库
show grants; 查看当前用户权限
COMMIT; 事务提交
ROLLBACK; 回滚
SAVEPOINT a; 插入检查点，a表示检查点标签
ROLLBACK to a; 回滚到检查点a
实验八 综合实验-水果商店进阶二
一、实验目的与要求：
综合运用SQL语言相关知识如变量、游标、函数、触发器等解决实际问题。
二、实验内容：
设计并完成以下实验，要求附上源码（非截图），测试效果截图
1.在客户表customers中添加“VIP”字段，默认值0，根据已购买的累计金额自动更新等级，如累计100~199，一星VIP折扣9.9，200~499二星VIP折扣9.7，500~999三星VIP折扣9.5，1000~1999，四星VIP折扣9，后8.5折。
①根据已购买情况自动修改客户表中所有客户的VIP信息
源码：
ALTER TABLE customers
ADD VIP decimal(8,2)
DEFAULT 0CREATE 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) DOUPDATE 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;
UPDATE orders
SET pay = original_price * discount;
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;
SET new.pay = new.original_price * new.discount;
END运行测试结果截图:
2.查询指定客户的“总优惠金额”，即累计每次购买时为客户优惠金额。
源码：
SELECT SUM(original_price-pay) AS "总优惠金额" FROM orders WHERE c_id = 10002运行测试结果截图:
3.数据库设计
（1）根据本学期完成的实验内容叙述本数据库可以实现哪些现实应用功能；
①可以建立水果商店应用系统相应数据的表格和视图，便于查询和存储水果店、客户、供货商、订单项、订单等具体信息，可以对数据进行插入、删除和修改。
②可以通过已建立好的表查询想要了解的属性和各属性之间的关系，通过不同表中的相同属性进行多表连接。
③可以为不同的角色和用户授权，使不同的用户和角色拥有不同的权限，避免发生数据泄露和破坏，保证数据库的安全性。
④可以通过各个表中的联系，对一个表中的数据进行更新，其他表中对应的相同属性的数据也会发生相应的更新，避免数据的滞后性和不连续性。
⑤可以通过客户的消费金额确定客户的VIP等级，从而确定客户之后再进行消费的折扣。
（2）使用PowerDesigner软件将备份后的数据库fruitshop反向生成概念数据模型（CDM）：conceptual data model和物理数据模型(PDM):pyshical data model
（3）针对已给定的数据库及应用需求，提出优化或改进措施并实现（至少三项）
①数据库索引优化：确保数据库表上的关键列上有适当的索引，以加快检索速度并提高查询性能。
②查询优化：定期审查和优化数据库查询，避免复杂查询和不必要的连接，以减少数据库负载并提高响应速度。
③缓存机制：考虑引入缓存机制，如使用缓存服务器或内存缓存，以减少对数据库的频繁访问，提高数据访问速度。
④数据库分区：根据数据访问模式将数据库表进行分区，可以提高查询效率和管理大量数据的性能。
4．总结本学期的实验（完成情况、对实验的意见和建议）
三、实验小结：
1．实验中遇到的问题及解决过程
（1）问题:在实验过程中，discount与VIP字段的数据未根据预期的值改变
解决过程:修改触发器的语句顺序与数据更新语句，可以正确解决
（2）问题:更新过程中对应变量名不同，无法正确运行代码
解决过程:修改对应变量名，使更新前和更新时相应的变量名保持一致，可以正确运行
2.实验中产生的错误及原因分析
（1)错误:出现触发器已存在的报错
原因分析:在对以创建触发器进行修改时，由于修改过程中从触发器的定义语句中修改，导致触发器重名，无法修改。
(2)错误:更新折扣信息后，应付价钱未更新
原因分析:创建存储过程或触发器的过程中忽略了对应付价钱的更新，导致应付价钱还是原价，未发生变化。
3．实验体会和收获
通过本次实验，我学会了使用使用PowerDesigner软件将备份后的数据库fruitshop反向生成概念数据模型和物理数据模型，实现了综合运用SQL语言相关知识如变量、游标、函数、触发器等来解决实际问题。同时在实验中通过解决出现的问题与错误，我学到了更多的MYSQL数据库相关的其他内容。通过数据库后台系统的操控我了解了更多可视化数据库管理系统的功能与MYSQL数据库的用处之广。通过多种操作的综合运用，我能够更加熟练和便捷地使用数据库系统，同时也了解了更多数据库在实际生活中的应用。
扫一扫
5万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
