投稿
  1. 首页
  2. sql

实验报告：图书销售管理系统数据库SQL应用编程

code • 2022-5-21 • sql • 阅读 18

实验报告：图书销售管理系统数据库SQL应用编程,第1张

实验目的

针对图书销售管理数据库开发，了解SQL语言DDL、DML、DQL类型语句在数据库 *** 作访问中的应用方法，培养数据库SQL编程访问能力。同时也掌握基本的数据库触发器、存储过程SQL编程方法，培养数据库后端编程能力。本实验完成图书销售管理系统数据库的SQL数据 *** 作访问和后端数据处理功能。

实验原理

首先对图书销售管理系统进行数据需求分析，定义组成系统数据结构的实体、实体属性以及实体之间的关系。采用实体关系图（E-R模型图）方法来展示图书销售管理系统的概念数据模型与逻辑数据模型。利用PowerDesigner数据库软件系统进行系统物理数据模型设计，对设计的图书销售管理系统数据库模型进行检验与完善，并对系统进行数据库设计，给出设计方案。

基于数据库设计方案，通过SQL编程执行来完成对数据库的创建与数据访问 *** 作以及相应的后端编程 *** 作。在本实验中，使用SQL语句完成对数据库、关系表、索引、视图、触发器、存储过程的创建，并编写SQL语句对数据库表进行数据的增删查改 *** 作，以及利用视图、存储过程、触发器实现业务数据处理。

数据库基本 *** 作数据：

创建数据库：Create database <数据库名>;
创建数据库表：Create table <表名>;
插入数据：Insert into <基本表>[<列名表>] values(v1,v2,v3,….);
删除数据：Delete from <表名> [where <条件表达式>];
更新数据：Update <基本表> set <列名 1> = <表达式 1>,[<列名 2> = <表达式 2>…] [where <条件表达式>];
Add修改方式：Alter table <表名> add <新列名称><数据类型>[完整性约束];

视图是一种从基础数据库表中获取数据所组成的虚拟表，在数据库中只需存储视图结构定义，而不存储视图所包含的数据，使用视图可以简化复杂的SQL查询，提高数据访问安全性，提供一定的数据逻辑独立性，可以集中展示用户所感兴趣的特定数据。

创建视图：Create view <视图名>[(列名 1), (列名 2), …] as <SELECT 查询>;
查看视图：Select * from <视图名>;
删除视图：Drop view <视图名>;

索引是一种针对表中指定列的值进行排序的数据结构，使用它可以加快表中数据的查询。在数据表中建立类似于图书目录的索引结构，并将索引列的值及索引指针 *** 作保存在索引结构中。此后在对数据表进行数据查询时，首先在索引结构中查找符合条件的索引指针值， 然后再根据索引指针快速找到对应的数据记录，这样可实现快速检索元组数据的目的。

创建索引：Create unique index <索引名> on <表名><(列名[,…])>;
修改索引：Alter index <索引名> rename to <新索引名>;
删除索引：Drop index <索引名>;

存储过程是由一组能完成特定功能的SQL语句构成，它把经常会反复使用的SQL语句逻辑块封装起来，经编译后，存储在数据库服务端，当再次调用时，不需再次编译，用户连接到数据库服务端时，用户通过指定存储过程的名称并给出参数，数据库就可以找到相应的存储过程并予以调用。

创建存储过程：
Create [or replace] function
name ( [ [ argmode ] [ argname ] argtype [{ default | =} default_expr ] [,…] ] ) [returns retype | returns table ( column_name column_type [,…] ) ]
As $$ Declare
--声明段
Begin
--函数体语句End；
$$ language lang_name;
调用存储过程：
Select function_name;

触发器与存储过程不同，存储过程通过其他程序启动运行或直接启动运行，而触发器由一个事件触发启动运行，也就是触发器在某个事件发生时自动隐式运行。Postgresql触发器在系统执行某种特定类型的 *** 作时，数据库将自动执行指定的特殊函数。触发器常用于定义逻辑计较复杂的完整性约束，或某种业务规则的约束。

创建触发器:
Create [constraint] Trigger name
{ before | after |instead of}{event[or…]}
On table_name
[ from referenced_table_name] [ for [each ]{ row | statement } ] [when (condition ) ]
Execute procedure function_name ( arguments )
实验内容

使用pgAdmin4数据库管理工具对图书销售管理系统数据库进行SQL编程 *** 作，并完成触发器、存储过程后端编程，具体实验内容如下：
（1）图书销售管理系统数据库BookSale创建 *** 作。
（2）在图书销售管理系统数据库BookSale中创建数据库表、视图、索引等对象。
（3）对图书销售管理系统数据库表进行数据增、删、查、改SQL *** 作。
（4）编写存储过程Pro_CurrentSale，实现当日图书销售量及销售金额汇总统计。
（5）编写过程语句块，实现对存储过程Pro_CurrentSale的调用，并输出统计结果。
（6）编写图书销售表Insert触发器Tri_InsertSale，实现图书库存数据同步修改处理。
（7）对图书销售表Insert触发器Tri_InsertSale程序进行功能验证。

在实验计算机上，利用pgAdmin4数据库管理工具及SQL语句，完成图书销售管理系统数据库SQL应用编程 *** 作，同时记录实验过程的步骤、 *** 作、运行结果界面等数据，为撰写实验报告提供素材。

实验器材

管理工具：pgAdmin 4
DBMS系统：PostgreSQL 11

实验步骤

（1）图书销售管理系统数据库BookSale创建 *** 作。
采用SQL语句执行方式，创建图书销售管理系统数据库BookSale。

（2）在图书销售管理系统数据库BookSale中创建数据库表、视图、索引等对象。
采用SQL语句执行方式，创建图书表Book、作者表Author、出版社表Publisher、库存流水表Bookstock、客户表Customer、销售表Sale，以及各表主键和外键的创建，并为各表创建索引。

（3）对图书销售管理系统数据库表进行数据增、删、查、改SQL *** 作。
为 Book、Author、Publisher、Customer表准备样本数据，采用SQL语句执行方式，将样本数据插入到表中。
对各表进行数据修改、删除、查询、统计等访问 *** 作。

（4）编写存储过程Pro_CurrentSale，实现当日图书销售量及销售金额汇总统计。

（5）编写过程语句块，实现对存储过程Pro_CurrentSale的调用，并输出统计结果。

（6）编写图书销售表Insert触发器Tri_InsertSale，实现图书库存数据同步修改处理。
编写图书销售表Insert触发器Tri_InsertSale，实现在Sale表数据插入时，级联 *** 作 Bookstock表，将图书的库存流水进行记录，同时级联更新Book表中对应图书的库存数据。

（7）对图书销售表Insert触发器Tri_InsertSale程序进行功能验证。
为 Sale表准备样本数据，将样本数据插入到表中之后查看Bookstock表是否有对应的更新，并对比插入数据前后Bookstock表中对应数据的修改情况。

实验结果

（1）图书销售管理系统数据库BookSale创建 *** 作。

SQL程序代码：

create database BookSale;

运行 *** 作界面：

在目录处刷新，查看语句运行结果：

结果说明：
成功创建了BookSale数据库。

（2）在图书销售管理系统数据库BookSale中创建数据库表、视图、索引等对象。

SQL程序代码：

create table Author
(
	Author_ID		char(18)	not null,
	Author_Name		varchar(20)	not null,
	Author_Gender	char(2)		not null,
	constraint Author_PK primary key(Author_ID)
);
create table Publisher
(
	Publisher_ID	char(11)	not null,
	Publisher_Name	varchar(20)	not null,
	Publisher_phone	varchar(15)	not null,
	constraint Publisher_PK primary key(Publisher_ID)
);
create table Book
(
	Book_ISBN		char(13)	not null,
	Book_Name		varchar(50)	not null,
	Book_Pubdate	date		not null,
	Book_Price		money		not null,
	Book_Stock		int4		not null,
	Author_ID		char(18)	null,
	Publisher_ID	char(11)	null,
	constraint Book_PK primary key(Book_ISBN)
);
create table Customer
(
	Customer_ID		char(18)	not null,
	Customer_Name	varchar(20)	not null,
	Customer_phone	varchar(15)	not null,
	constraint Customer_PK primary key(Customer_ID)
);
create table Sale
(
	Sale_ID			varchar(11)	not null,
	Sale_Date		date		not null,
	Sale_Number		int4		not null,
	Sale_Amount		money		not null,
	Book_ISBN		char(13)	null,
	Customer_ID		char(18)	null,
	constraint Sale_PK primary key(Sale_ID)
);
create table Bookstock
(
	Stock_ID		varchar(10)	not null,
	Stock_Date		date		not null,
	Stock_Operation	char(4)		not null,
	Stock_Change	int4		not null,
	Book_ISBN		char(13)	null,
	constraint Stock_PK primary key(Stock_ID)
);
alter table Book add constraint Book_Author_FK foreign key(Author_ID)
references Author(Author_ID) on delete restrict on update restrict;
alter table Book add constraint Book_Publisher_FK foreign key(Publisher_ID)
references Publisher(Publisher_ID) on delete restrict on update restrict;
alter table Sale add constraint Sale_Book_FK foreign key(Book_ISBN)
references Book(Book_ISBN) on delete restrict on update restrict;
alter table Sale add constraint Sale_Customer_FK foreign key(Customer_ID)
references Customer(Customer_ID) on delete restrict on update restrict;
alter table Bookstock add constraint Stock_Book_FK foreign key(Book_ISBN)
references Book(Book_ISBN) on delete restrict on update restrict;

运行 *** 作界面：

结果说明：
成功创建了数据库表对象。

SQL程序代码：

create index Author_Name_Idx on Author(Author_Name);
create index Publisher_Name_Idx on Publisher(Publisher_Name);
create index Book_Name_Idx on Book(Book_Name);
create index Customer_ID_Idx on Customer(Customer_ID);
create index Sale_Date_Idx on Sale(Sale_Date);
create index Stock_ID_Idx on Bookstock(Stock_ID);

运行 *** 作界面：

结果说明：
成功创建了各个数据库表对应的索引。

（3）对图书销售管理系统数据库表进行数据增、删、查、改SQL *** 作。

SQL程序代码：

insert into Author values('130126199904080314','小明','男');
insert into Author values('120857198811123587','小红','女');
insert into Author values('310109199803143088','小刚','男');
insert into Publisher values('PUB00000001','高等教育出版社','010-65321854');
insert into Publisher values('PUB00000002','电子工业出版社','010-65329857');
insert into Publisher values('PUB00000003','人民邮电出版社','010-65325987');
insert into Book values('9787115502742','Web前端开发实践','2016-01-01','50','80','130126199904080314','PUB00000001');
insert into Book values('9783253213434','计算思维导论','2014-02-02','45','100','120857198811123587','PUB00000002');
insert into Book values('9641543566465','数据库原理','2015-03-03','42','120','120857198811123587','PUB00000002');
insert into Book values('9865365975234','编译原理','2016-07-07','55','150','310109199803143088','PUB00000003');
insert into Customer values('330172199203042748','李华','13344556677');
insert into Customer values('365211199311084214','张亮','13321234234');
insert into Customer values('231332199603283242','陈斌','13363635451');
select * from Publisher;

运行 *** 作界面：

样本数据插入完成后，对出版社表内的数据进行查询 *** 作，查看插入 *** 作是否正确：

结果说明：
成功在各个数据库表中插入了样本数据，经过查询结果验证，插入 *** 作无误。

SQL程序代码：

insert into Publisher values('PUB00000004','清华大学出版社','010-65323169');
select * from Publisher;

运行 *** 作界面：

结果说明：
成功在Publisher表中增加了新的一行数据，经过查询输出后证明增加数据 *** 作无误。

SQL程序代码：

update Publisher set Publisher_Phone = '010-61234414' where Publisher_ID = 'PUB00000004';
select * from Publisher;

运行 *** 作界面：

结果说明：
成功在Publisher表中实现了修改数据的 *** 作，经过查询输出后证明修改 *** 作无误。

SQL程序代码：

delete from Publisher where Publisher_ID = 'PUB00000004';
select * from Publisher;

运行 *** 作界面：

结果说明：
成功在Publisher表中删除了新添加的一行数据，经过查询输出后证明删除 *** 作无误。

（4）编写存储过程Pro_CurrentSale，实现当日图书销售量及销售金额汇总统计。

SQL程序代码：

create or replace function Pro_CurrentSale(out amount int4, out allmoney money) as $count$
	begin
		select sum(Sale_Number) into amount from Sale where Sale_Date = '2018-02-23';
		select sum(Sale_Amount) into allmoney from Sale where Sale_Date = '2018-02-23';
	end;
$count$ LANGUAGE plpgsql;

运行 *** 作界面：

结果说明：
成功创建了Pro_CurrentSale存储过程。

（5）编写过程语句块，实现对存储过程Pro_CurrentSale的调用，并输出统计结果。

SQL程序代码：

select * from Pro_CurrentSale();

运行 *** 作界面：

结果说明：
调用存储过程后输出数据符合实际情况，输出的总销售量和总金额数据正确，创建存储过程Pro_CurrentSale正确。

（6）编写图书销售表Insert触发器Tri_InsertSale，实现图书库存数据同步修改处理。

SQL程序代码：

create or replace function InsertSale()
	returns trigger as $$
	begin
		insert into Bookstock values(new.Sale_ID,new.Sale_Date,'出库',new.Sale_Number,new.Book_ISBN);
		update Book set Book_Stock = Book_Stock - new.Sale_Number where Book.Book_ISBN = new.Book_ISBN;
		return new;
	end;
$$ language plpgsql;

运行 *** 作界面：

结果说明：
成功创建了InsertSale()触发器函数。

SQL程序代码：

create trigger Tri_InsertSale after insert on Sale
for each row execute procedure InsertSale();

运行 *** 作界面：

结果说明：
成功创建了Tri_InsertSale触发器。

（7）对图书销售表Insert触发器Tri_InsertSale程序进行功能验证。

SQL程序代码：

insert into Sale
values('SA00001','2018-02-22','10','500','9787115502742','330172199203042748');
insert into Sale
values('SA00002','2018-02-23','5','250','9787115502742','365211199311084214');
insert into Sale
values('SA00003','2018-02-23','10','450','9783253213434','365211199311084214');
insert into Sale
values('SA00004','2018-02-23','20','900','9783253213434','231332199603283242');
insert into Sale
values('SA00005','2018-02-24','30','1260','9641543566465','231332199603283242');
insert into Sale
values('SA00006','2018-02-24','10','550','9865365975234','231332199603283242');
select * from Bookstock;
select * from Book;

运行 *** 作界面：

查看Bookstock表的数据：

对比Book表的数据：

结果说明：
Bookstock表中本来没有数据，在Sale表插入数据之后，将库存数据进行记录，记录数据正确，同时发现 Book表中库存量都发生了变化，分别减少了与销售表中记录的销售件数相应值的大小，更新数据正确，可以证明触发器的功能都实现了，触发器功能正确。

数据库原理及应用实验：

图书销售管理系统数据库SQL应用编程图书销售管理系统数据库安全管理

欢迎分享，转载请注明来源：内存溢出

原文地址: https://outofmemory.cn/sjk/991235.html

赞 (0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
code code 管理员组
0 0
上一篇 2022-05-21
下一篇 2022-05-21

发表评论

登录后才能评论

评论列表（0条）

保存