数据库系统原理与应用实验报告

《数据库系统原理及应用》 实验报告 姓名： 院系：计算机学院 班级: 学号： 班内序号: 实验一 定义 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 和数据库完整性 一、目的和 要求 对教师党员的评价套管和固井爆破片与爆破装置仓库管理基本要求三甲医院都需要复审吗 1．了解SQL Server 数据库的逻辑结构和物理结构。 2．了解表的结构特点。 3．了解SQL Server 的基本数据类型。 4．学会在企业管理器中创建数据库和表。 5．学会使用T-SQL 语句创建数据库和表。 二、实验准备 1．要明确能够创建数据库的用户必须是系统管理员，或是被授权使用CREATE DATABASE语句的用户。 2．创建数据库必须要确定数据库名、所有者（即创建数据库的用户）、数据库大小（最初的大小、最大的大小、是否允许增长及增长的方式）和存储数据的文件。 3．确定数据库包含哪些表以及包含的各表的结构，还要了解SQL Server 的常用数据类型，以创建数据库的表。 4．了解常用的创建数据库和表的方法。 三、实验内容 1、 使用SQL 语句创建表 CREATE TABLE Customers( CustomerID int IDENTITY(1,1) PRIMARY KEY, Cname varchar(8) NOT NULL, Address varchar(50), City varchar(10), Tel varchar(20) UNIQUE, Company varchar(50), Birthday datetime, Type tinyint DEFAULT 1); CREATE TABLE Goods( GoodsID int CONSTRAINT C1 PRIMARY KEY, GoodsName varchar(20) NOT NULL, Price money, Description varchar(200), Storage int, Provider varchar(50), Status tinyint DEFAULT(0)); CREATE TABLE Orders( OrderID INT IDENTITY(1,1) CONSTRAINT C2 PRIMARY KEY, GoodsID INT NOT NULL REFERENCES Goods(GoodsID) ON DELETE CASCADE, CustomerID int NOT NULL FOREIGN KEY(CustomerID) REFERENCES Customers(CustomerID) ON DELETE NO ACTION, Quantity int NOT NULL CONSTRAINT C3 CHECK(Quantity>0), OrderSum money NOT NULL, OrderDate datetime DEFAULT(getdate())); 建表结果如下图所示： 实验二 表数据的插入、修改和删除 一、目的和要求 1．学会在企业管理器中对表进行插入、修改和删除数据操作。 2．学会使用T-SQL 语句对表进行插入、修改和删除数据操作。 3．了解T-SQL 语句对表数据库操作的灵活控制功能。 二、实验准备 1．了解表的更新操作，即数据的插入、修改和删除，对表数据的操作可以在企业管理 器中进行，也可以由T-SQL 语句实现。 2．掌握T-SQL 中用于对表数据进行插入（INSERT）、修改（UPDATE）和删除（DELETE 或TRANCATE TABLE）命令的用法。 3．了解使用T-SQL 语句在对表数据进行插入、修改及删除时，比在企业管理器中操作 表数据灵活，功能更强大。 三、实验内容 1．向表中输入数据，结果如下图所示： Customers表 Goods表： Orders表： 在表中完成插入、删除、修改数据操作 插入数据： INSERT INTO Goods VALUES('6','耳机','20','电子类','100','赛格商城','1'); INSERT INTO Goods VALUES('7','衬衫','50','服装','','森马服装有限公司','1'); 结果如图： 删除数据： DELETE FROM Goods WHERE GoodsName='耳机'; 结果如图： 修改数据： UPDATE Goods SET Price=80 WHERE GoodsID='7' 结果如图： 实验三 数据库的简单查询和连接查询 一、目的与要求 1．掌握SELECT 语句的基本语法。 2．掌握子查询的表示。 3．掌握连接查询的表示。 二、实验准备 1．了解SELECT 语句的基本语法格式。 2．了解SELECT 语句的执行方法。 3．了解子查询的表示方法。 三、实验内容 1、查找所有上海客户的信息 语句： SELECT CustomerID,Cname FROM Customers WHERE City='上海'; 结果： 查找所有商品的名称、库存量、价格以及折价25%后的价格，并使用别名“Dicount”标识折扣价，结果按价格由低到高排序 语句：SELECT GoodsName,Storage,Price,0.25*Price Discount FROM Goods ORDER BY Price ASC; 结果： 查找商品名中包含“computer”的商品的编号、名称及价格 语句： SELECT GoodsID,GoodsName,Price FROM Goods WHERE GoodsName LIKE 'computer'; 结果： 查找库存量大于100小于500的商品的名称、库存量和单价 语句： SELECT GoodsName,Storage,price FROM Goods WHERE Storage BETWEEN 100 AND 500; 结果： 查找2007年7月1日至2007年12月31日期间，订货金额大于3000的所有订单的客户姓名，商品名称、单价、订货数量和订货金额。 语句： SELECT Cname,GoodsName,Price,Quantity,OrderSum，OrderDate FROM Customers,Goods,Orders WHERE OrderDate>'2007-7-1' AND OrderDate<'2007-12-31' AND Goods.GoodsID=Orders.GoodsID AND Customers.CustomerID=Orders.CustomerID; 结果： 实验四 数据库的复杂查询 一、目的与要求 1．掌握约束的定义及其删除方法。 2．掌握规则的创建、使用和删除方法。 3．掌握默认对象的创建、使用和删除方法。 4．掌握SELECT 语句的GROUPBY 和ORDERBY 子句的作用和使用方法。 二、实验准备 1．了解数据完整性概念。 2．了解约束的类型。 3．了解创建约束和删除约束的语法。 4．了解创建规则和删除规则的语法。 三、实验内容 1、查找订单编号、商品编号和客户编号，要求按日期对订单进行分组，并只显示订单数量超过40的订单信息。 语句: SELECT OrderID,GoodsID,CustomerID FROM Orders WHERE Quantity>'40' ORDER BY OrderDate; 结果: 2、查找所有北京客户的订单信息，要求用不相关子查询完成。 语句: SELECT OrderID,GoodsID,CustomerID,Quantity,OrderSum,OrderDate FROM Orders WHERE CustomerID IN (SELECT CustomerID FROM Customers WHERE City='北京'); 结果: 查找所有西安客户的相关信息以及他们的订单情况。 语句: SELECT Cname,Address,City,Tel,Company,Birthday,Type,GoodsName,Quantity,OrderSum,OrderDate FROM Customers,Orders,Goods WHERE City='西安' AND Customers.CustomerID=Orders.CustomerID AND Goods.GoodsID=Orders.GoodsID; 结果: 实验五 索引和视图 一、目的与要求 1．掌握创建视图的SQL 语句的用法。 2．掌握使用企业管管理器创建视图的方法。 3．掌握使用创建视图向导创建视图的方法。 4．掌握查看视图的系统存储过程的用法。 5．掌握修改视图的方法。 二、实验准备 1．了解创建视图方法。 2．了解修改视图的SQL 语句的语法格式。 3．了解视图更名的系统存储过程的用法。 4．了解删除视图的SQL 语句的用法。 三、实验内容 1. 请为北京客户建立一个订单情况的视图，包括订单编号、商品名称、订货数量、客户编号、客户名称。 语句：CREATE VIEW C_Customers AS SELECT OrderID,GoodsName,Quantity,Orders.CustomerID,Cname FROM Customers,Goods,Orders WHERE City='北京' AND Orders.CustomerID=Customers.CustomerID AND Orders.GoodsID=Goods.GoodsID WITH CHECK OPTION; 结果： 针对1中定义的视图，完成查询，查找客户常洁琳的所有订单信息。 语句：SELECT * FROM C_Customers WHERE Cname='常洁琳'; 结果： 实验七 存储过程 一、目的与要求 1．掌握创建存储过程的方法和步骤。 2．掌握存储过程的使用方法。 二、实验准备 1．了解存储过程基本概念和类型。 2．了解创建存储过程的SQL 语句的基本语法。 3．了解查看、执行、修改和删除存储过程的SQL 命令的用法。 三、实验内容 1、编写存储过程sp_shanghai,查看上海客户的信息 语句：CREATE PROCEDURE cp_shanghai AS SELECT * FROM Customers WHERE City='上海'; 结果： 编写存储过程sp_Goods，查看指定商品的信息，商品编号作为输入参数。 语句： CREATE PROCEDURE sp_Goods @GoodsID int AS SELECT * FROM Goods WHERE GoodsID=@GoodsID; EXECUTE sp_Goods 2; 结果： 3、编写存储过程sp_GoodsSum，查看指定客户的所有订单的订货总金额数。客户编号作为输入参数，订货总金额数作为输出参数 语句：CREATE PROCEDURE sp_GoodsSum @CustomerID int, /*注意此处要加逗号！！*/ @OrderSum money OUTPUT AS SELECT @OrderSum=sum(OrderSum) FROM Orders WHERE CustomerID=@CustomerID; DECLARE @OrderSum money; EXECUTE sp_GoodsSum 6 ,@OrderSum OUTPUT; SELECT 'The result is;',@OrderSum; 结果： 4、编写存储过程sp_insertGoods，向Goods表中插入一条 记录 混凝土 养护记录下载土方回填监理旁站记录免费下载集备记录下载集备记录下载集备记录下载 语句： CREATE PROCEDURE sp_insertGoods @GoodsID int,@GoodsName varchar(20),@Price money,@Description varchar(200),@Storage int,@Provider varchar(50),@Status tinyint AS INSERT INTO Goods VALUES (@GoodsID,@GoodsName,@Price,@Description,@Storage,@Provider,@Status); EXECUTE PROCEDURE sp_insertGoods '8','人生','25','书籍','150','新华出版社','1'; 实验七 触发器 一、目的与要求 1．掌握创建触发器的方法和步骤。 2．掌握触发器的使用方法。 二、实验内容 1、在Customers表上建立删除触发器，实现Customers表和Orders表的级联删除 CREATE TRIGGER CustomersDELETE ON Customers AFTER DELETE AS DELETE FROM Orders WHERE CustomerID IN (SELECT deleted.CustomerID from deleted); 2、在Orders表上建立插入触发器，当向表中添加一条订货记录时。若订单中的商品状态为整理（Satus=1）则不能插入该条记录 CREATE TRIGGER Orders_insert ON Orders AFTER insert AS IF(SELECT Status FROM Goods,inserted WHERE Goods.GoodsID=inserted.GoodsID)=1 BEGIN PRINT '订单中该商品状态为整理，不能插入该记录' ROLLBACK TRANSACTION END; 在Orders表上建立插入触发器，当添加订单时，减少Goods表中相应商品的库存量。 CREATE TRIGGER insert_Orders ON Orders after insert AS update Goods set Storage=Storage-(SELECT quantity FROM inserted) WHERE GoodsID in (SELECT GoodsID FROM inserted); 添加订单之前的Goods表： 插入： insert into Orders values('26','1','3','100','600','2009-10-1'); 4、在Orders表上建立触发器，不允许对订单日期进行修改 CREATE TRIGGER Date_update ON Orders AFTER UPDATE AS IF UPDATE(OrderDate) BEGIN PRINT'商品的生产日期不能修改' ROLLBACK TRANSACTION END; 验证触发器： update Orders set OrderDate='2012-01-01' where OrderID='1'; 结果： 5、建立触发器，实现参照完整性约束，即若在Orders表中添加一条订单记录时，则该订单中的商品必须是在Goods表中存在，否则不允许添加该记录。 CREATE TRIGGER Order_insert ON Orders after insert as if(SELECT Goods.GoodsID FROM Goods,inserted WHERE Goods.GoodsID=inserted.GoodsID)=0 BEGIN PRINT '该商品不存在，不能插入订单' ROLLBACK TRANSACTION END; 实验总结及心得体会： 在做实验之前，总觉得SQL语句都非常简单，不过是把一些明了的英语单词写成代码就好。当做到复杂查询后，就发现以前在作业中写的代码，很多都是不严谨的，甚至是错误的。只有在执行调试后才会知道其细节和原理，在实验进行过程中，遇到了很多令人费解的错误，有时因为对聚合函数的理解不深刻，有时是由于大小写命名的混乱，在复杂查询时总会出现各种错误，要感谢老师的耐心指导，最终顺利的完成实验，并对数据库SQL语句的理解更加深刻。