當前位置：首頁 > 人文社科 > 生活经验 >内容正文

生活经验

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

發布時間：2023/11/27 生活经验 18 豆豆

生活随笔收集整理的這篇文章主要介紹了数据库实验：数据库和表、查询、视图与安全性、存储过程及游标、触发器、综合实验-水果商店进阶小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

數據庫實驗：數據庫和表、查詢、視圖與安全性、存儲過程及游標、觸發器、綜合實驗-水果商店進階

實驗一、數據庫和表
- 源碼1：
- 源碼2：
- 小結
實驗二、查詢
- 源碼
- 小結
實驗三、視圖、安全性
- 源碼：
- 小結
實驗四、存儲過程及游標
- 源碼
- 小結
實驗五、觸發器
- 源碼：
- 小結
實驗六、綜合實驗-水果商店進階一
- 源碼：
- 小結
實驗七、綜合實驗-水果商店進階二
- 源碼
- 小結
實驗八、綜合實驗
- 源碼
- 小結

實驗一、數據庫和表

一、實驗目的與要求：
１、掌握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=10000GRANT 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=100GRANT 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、掌握游標的工作原理、定義及操作方法

二、實驗內容：

創建函數，用來自動統計給定訂單號的訂單總金額

源碼

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()
BEGINDECLARE 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()
BEGINDECLARE 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()
BEGINDECLARE 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;ENDCALL 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;
ENDINSERT 
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) DOUPDATE 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;
ENDCREATE 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;
ENDINSERT 
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;
ENDDELETE 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;
ENDUPDATE 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，根據已購買的累計金額自動更新等級，如累計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;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數據庫的用處之廣。通過多種操作的綜合運用，能夠更加熟練的更加便捷的使用數據庫系統。同時也了解了更多數據庫在實際生活中的應用。

實驗八、綜合實驗

一、實驗目的與要求：

設計并繪出此數據庫的E-R圖
將E-R圖轉換成關系模式
針對已給定的數據庫及應用需求，提出優化或改進措施并實現附上代碼（至少三項）
二、實驗內容：
設計并繪出此數據庫的E-R圖
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) DOUPDATE 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) DOUPDATE 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數據庫的用處之廣。通過多種操作的綜合運用，能夠更加熟練的更加便捷的使用數據庫系統。同時也了解了更多數據庫在實際生活中的應用。

總結

以上是生活随笔為你收集整理的数据库实验：数据库和表、查询、视图与安全性、存储过程及游标、触发器、综合实验-水果商店进阶的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇：朴素贝叶斯预测是否为垃圾短信
下一篇：【ios开发/Xcode】实现简易省会问