实验一：图书销售管理系统数据库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] functionname ( [ [ 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 )

SQL程序代码：

grant select on Author to R_Customer;grant select on Publisher to R_Customer;grant select on Book to R_Customer;grant select,insert,update,delete on Author to R_Seller;grant select,insert,update,delete on Publisher to R_Seller;grant select,insert,update,delete on Book to R_Seller;grant select,insert,update,delete on Customer to R_Seller;grant select,insert,update,delete on Sale to R_Seller;grant select on Bookstock to R_Seller;

运行操作界面：

结果说明：
成功将定义的权限赋予给了客户角色R_Customer和商家角色R_Seller。

（4）在数据库中，分别创建客户用户U_Customer、商家用户U_Seller。

（5）分别为客户用户U_Customer、商家用户U_Seller分派客户（R_Client）、商家（R_Seller）角色。

SQL程序代码：
 

create user U_Customer withloginconnection limit -1in role R_Customerpassword '123456';create user U_Seller withloginconnection limit -1in role R_Sellerpassword '123456';

 运行操作界面：

结果说明：

成功创建了客户用户U_Customer和商家用户U_Seller。

（6）分别以客户用户U_Customer、商家用户U_Seller身份访问图书销售管理数据库，验证所实现数据存取权限控制模型的正确性。

SQL程序代码：

select * from Book;delete from Book where Book_ISBN = '9787121021961';select * from Author;insert into Author values('444444444444444444','大锤','男');select * from Sale;

以客户用户身份登录数据库：

查看Book表： 

 删除Book表中的数据：

 查看Author表：

向Author表中插入一条数据：

查看Sale表：

结果说明：

客户用户U_Customer可查看Book表中的数据。

在删除Book表中的数据时，结果显示失败，客户用户不能完成删除操作。

客户用户可以查看Author表中的数据。

在向Author表中插入一条数据时，结果显示失败，客户用户不能完成插入操作。

在查看Sale表中的数据时，结果显示失败，客户用户不能完成查看操作。

客户用户对数据库表的权限的测试都正确，即创建的客户用户赋予权限的操作是正确的。

以商家用户身份登录数据库：

SQL程序代码：

select * from Customer;insert into Author values('444444444444444444','大锤','男');select * from Author;select * from Bookstock;delete from Bookstock where Stock_ID = 'SA00001';update Sale set Sale_Date = '2022-05-01' where Sale_ID = 'SA00001';select * from Sale;

查看Customer表：

向Author表中插入一条数据：

从Bookstock表中删除一条数据：

更新Sale表中的一条数据：

结果说明：

商家用户U_Seller可查看Customer表中的数据。

通过与客户用户步骤中查询的作家信息表进行对比，Author表中的数据新增加了一条，商家用户具有对Author表的插入权限。

在从Bookstock表中删除一条数据时，结果显示失败，商家用户不能完成删除操作。

通过对比，Sale表中的数据发生了更新，商家用户具有对Sale表的更新权限。

商家用户对数据库表的权限的测试都正确，即创建的商家用户赋予权限的操作是正确的。

总结及心得体会

在SQL Shell (psql)的使用中遇到了多次报错，在网上搜索实践后解决，例如postgreSQL: password authentication failed for user "postgres"，从postgreSQL: password authentication failed for user "postgres"_森林先森的博客-CSDN博客获得解决。我从中理解到，互联网可以帮助解决大部分问题