数据库系统原理及应用实验报告.doc

数据库系统原理及应用实验报告院系：计算机学院 班级：计科1102班 姓名：杨康 学号：04111043 序号：7号实验二 定义表和数据库完整性1. 使用T-SQL语句在数据库Market中创建客户基本信息表customers，货品信息表goods，订单信息表orders。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(quantity0), ordersum money not null, orderdate datetime default (getdate();创建表：实验三 表数据的插入、修改和删除1.向表中输入数据，结果如下图所示：customers表：goods表：orders表：2.在表中完成删除、修改数据操作删除数据：delete from orderswhere orderid=2;结果如图：修改数据：update ordersset quantity=100where goodsid in(select goodsidfrom goodswhere goodsid=3);结果如图：实验四 数据库的简单查询和连接查询1.查找所有西安客户的信息.语句：select *from customerswhere city=西安;结果：2.查找所有商品的名称、库存量、价格以及折价25%后的价格，并使用别名“Dicount”标识折扣价，结果按价格由低到高排序.语句：select goodsname ,storage,price,0.75*price Discountfrom goodsorder by price asc;结果：3.查找2013年5月9日至2013年5月20日期间，订货金额大于80的所有订单的客户姓名，商品名称、单价、订货数量和订货金额。语句：select cname,goodsname,price,quantity,ordersumfrom customers,goods,orderswhere customers.customerid=orders.customerid and goods.goodsid=orders.goodsid andorderdate2013-5-9 and orderdate80;结果：实验五 数据库的复杂查询1.查找订单编号、商品编号和客户编号，要求按日期对订单进行分组，并只显示订单数量超过50的订单信息。语句: select *from orderswhere quantity=50order by orderdate;结果: 2.查找所有西安客户的订单信息，要求用不相关子查询完成。语句:select *from orderswhere customerid in (select customeridfrom customerswhere city=西安);结果:3.查找所有杭州客户的相关信息以及他们的订单情况。语句: select orders.customerid,cname,city,address,company,birthday,orderdate,ordersum,quantityfrom customers,orderswhere city=杭州 and customers.customerid=orders.customerid；结果：实验六 索引和视图1.请为成都客户建立一个订单情况的视图，包括订单编号、商品名称、订货数量、客户编号、客户名称。语句：create view c_customeras select orderid,goodsname,quantity,orders.customerid,cname from orders,goods,customers where city=成都 and orders.customerid=customers.customerid and orders.goodsid=goods.goodsid with check option;结果：2.针对1中定义的视图，完成查询，查找客户周俨的所有订单信息。语句：select *from c_customerswhere cname=周俨;结果：无信息，已被删除。实验七 存储过程1.编写存储过程sp_beijing,查看北京客户的信息语句：create procedure sp_beijingas select * from customers where city=北京; execute sp_beijing; 结果：2.编写存储过程goods，查看指定商品的信息，商品编号作为输入参数。语句：create procedure goodsgoodsid intas select * from goods where goodsid=goodsid; execute goods goodsid=1;结果：3创建一个存储过程g_orders.查看任何指定货品的订单情况，包括订单号、订货客户的姓名以及订货数量等语法：create procedure g_ordersgoodsid intas select orders.orderid,cname,quantity from orders,customerswhere goodsid=goodsid and orders.customerid=customers.customerid; execute g_orders goodsid=2; 结果：实验八 触发器1.在customers表上建立删除触发器，实现customers表和orders表的级联删除。create trigger cusdelete on customers after deleteas delete from orderswhere customerid in (select deleted.customerid from deleted);2.在orders表上建立触发器，当向表中添加一条订货记录时，若订单中的商品状态为整理（status=1）,则不能插入该条记录。create trigger orinsert on orders after insert as if(select status from goods,inserted where goods.goodsid=inserted.goodsid)=1 begin print 商品正在整理，不能插入该记录！ rollback transaction end;3.在orders表上建立触发器，不允许订单日期进行修改create trigger orupdate on ordersafter updateas if update(orderdate) begin print 订货日期不能修改！ rollback transaction end;实验总结本次实验，让我有很大的进步，之前对这个不是很了解，在做完实验之后，发现对数据库有了更深入的理解，特别是在执行调试后才会知道其细节和原理。不过做实验室才发现出现了好多问题，语法不严谨不简洁，而且自己也不太细心，导致许多小问题出现。不过要感谢老师的耐心指导，在实验过程中，让我能一步一步克服这些问题，请教老师或同学最终完满顺利的完成了本次试验。 实验源代码create database Market;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(quantity0), ordersum money not null, orderdate datetime default (getdate();insert into customers(cname,address,city,tel,company,birthday,type)values(杨康,东长安街,西安航天所,1991-12-12,1);insert into customers(cname,address,city,tel,company,birthday,type)values(周俨,高新立交,成都大庆油田,1992-3-11,1);insert into customers(cname,address,city,tel,company,birthday,type)values(李云涯,西单,北京哇哈哈总公司,1993-5-19,1);insert into customers(cname,address,city,tel,company,birthday,type)values(冯佳欣,西湖东路,杭州中国银行,1993-8-28,1);insert into goodsvalues(1,集成电路板,70,电子产品,300,联想,1);insert into goodsvalues(2,风扇,45,家用电器,600,格力,1);insert into goodsvalues(3,蚊帐,20,生活用品,500,温家,1);insert into goodsvalues(4,月饼,10,食品,400,米琪,1);insert into orders(goodsid,customerid,quantity,ordersum,orderdate)values(2,4,20,900,2013-5-10);insert into orders(goodsid,customerid,quantity,ordersum,orderdate)values(1,3,30,2100,2013-5-15);insert into orders(goodsid,customerid,quantity,ordersum,orderdate)values(4,2,100,1000,2013-5-19);insert into orders(goodsid,customerid,quantity,ordersum,orderdate)values(3,1,10,200,2013-5-20);delete from orderswhere orderid=2;update ordersset quantity=100where goodsid in(select goodsidfrom goodswhere goodsid=3);select *from customerswhere city=西安;select goodsname,storage,price,0.75*price Discountfrom goodsorder by Price asc;select cname,goodsname,price,quantity,ordersumfrom customers,goods,orderswhere customers.customerid=orders.customerid and goods.goodsid=orders.goodsid andorderdate2013-5-9 and orderdate80;select *from orderswhere quantity=50order by orderdate;select orders.customerid,cname,city,address,company,birthday,orderdate,ordersum,quantityfrom customers,orderswhere city=杭州 and customers.customerid=orders.customerid create view c_customeras select orderid,goodsname,quantity,orders.customerid,cname from orders,goods,customers where city=成都 and orders.customerid=customers.customerid and orders.goodsid=goods.goodsid with check option; select *from c_customerswhere cname=周俨;create procedure sp_beijingas select * from customers where city=北京; execute sp_beijing;