数据库原理及应用崔巍书后.docx

1、数据库原理及应用崔巍书后 数据库原理及应用崔巍-书 后上机实验 数据库 上机实验报告1 一、实验目的：理解SQL Server数据库的存储结构，掌握SQL Server数据库的建立方法和维护方法。 二、实验内容：在SQL Server环境下建立数据库和维护数据库。 三、程序源代码： -1 CREATE DATABASE test1 ON (NAME=test1_dat, FILENAME=f:DBdatatest1dat.mdf, SIZE= 10, MAXSIZE= 50, FILEGROWTH= 5 ) LOG ON (NAME=order_log, FILENAME=f:DBdatate

2、st1log.ldf, SIZE= 5MB, MAXSIZE= 25MB, FILEGROWTH= 5MB) -2 create database test2 on primary (name=test2_dat1, filename=f:DBdatatest2dat1.mdf), (name=test2_dat2, filename=f:DBdatatest2dat2.ndf), (name=test2_dat3, filename=f:DBdatatest2dat3.ndf) log on (name=test2_log1, filename=f:DBdatatest2log1.ldf),

3、 (name=test2_log2, filename=f:DBdatatest2log2.ldf) -3 create database test3 on primary (name=test3_dat1, filename=f:DBdatatest3dat1.mdf), (name=test3_dat2, filename=f:DBdatatest3dat2.mdf), filegroupg2 (name=test3_dat3, filename=d:DBdatatest3dat3.ndf), (name=test3_dat4, filename=d:DBdatatest3dat4.ndf

4、), filegroupg3 (name=test3_dat5, filename=e:DBdatatest3dat5.ndf), (name=test3_dat6, filename=e:DBdatatest3dat6.ndf) log on (name=test3_log, filename=f:DBdatatest3log.ldf) -4 alter database test1 add file (name=test1new_dat, filename=f:DBdatatest1newdat.ndf, size=5MB) -5 alter database test1 modify f

5、ile (name=test1_dat, size=15MB) -6 dropdatabasetest3 四、实验数据、结果分析： 若没有指定size，则默认为1MB，没有指定Maxsize，文件可以增长到磁盘满为止，没有指定Filegrowth，则默认为10%。 五、总结： CREATE DATABASE dataname 创建数据库 ALTER DATABASE database 修改数据库 DROP DATABASE dataname 删除数据库 数据库系统及应用 上机实验报告2 一、实验目的：掌握表和索引的建立方法及表结构的修改方法，并实践DBMS提供的数据完整性功能，加深对数据完整性

6、的理解。 二、实验内容：建立表，修改表结构，建立索引，数据完整性描述。 三.、程序源代码： create database 订单管理 create table 客户( 客户号 char(8) primary key check(客户号 like A-z%), 客户名称 varchar(40) not null, 联系人 char(8), 地址 varchar(40), 邮政编码 char(6) check(邮政编码 like 0-90-90-90-90-90-9), 电话 char(12) check(电话 like 10-90-90-90-90-90-90-90-90-90-90-9) cr

7、eate table 产品( 产品号 char(8) primary key check(产品号 like A-ZA-Z%), 产品名称 varchar(40), 规格说明 char(40) constraint uni unique, 单价 smallmoney constraint dj check(单价0) create table 订购单( , 客户references key foreign tnull no char(8) 客户号 订单号 char(8) primary key, 订购日期 datetime default getdate() create table 订单名细(

8、订单号 char(8) foreign key references 订购单, 序号 tinyint, 产品号 char(8) not null foreign key references 产品, 数量 smallin tconstraint sl check(数量0), primarykey(订单号,序号) -修改产品表 -1 alter table 产品drop constraint uni alter table 产品alter column 规格说明 varchar(40) alter table 产品add constraint uni unique (规格说明) -2 alter

9、 table 订购单 add 完成日期 datetime default null -3 alter table 订单名细 drop constraint sl alter table 订单名细 add constraint sl check(数量0 and 数量1000) -建立索引 -1 create index cus_idx on 客户(客户名称) -2 create unique index gdn_nui on 产品(产品名称) -3 创建表时，primary key 隐式地创建了聚集索引，而一个表中只能有一个聚集索引。 create clustered index oid_clu

10、s on 订购单(订单号) -失败 -4 create index item_idx on 订单名细(订单号,序号,数量 desc) 数据库系统及应用 上机实验报告3 一、实验目的：为实验2建立的表设计一组数据进行插入、删除、修改等操作，并体会数据完整性约束的作用，加深对数据完整性及其约束的理解。 二、实验内容：数据的插入、更新和删除。 三.、程序源代码： -1、部分记录 insert into 客户 values(C001,A公司,小明,北京,123456,143567568457) insert into 客户 values(C002,B公司,小李,上海,234567,1463890456

11、34) insert into 客户(客户号,客户名称) values(C009,J商场) insert into 产品 values(GD001,iphone,4s,5000) insert into 产品 values(GD002,ipad,2g,5500) insert into 产品(产品号,产品名称) values(GD010,Mac Pro) insert into 订购单(客户号,订单号) values(C001,or01) insert into 订购单(客户号,订单号) values(C001,or02) insert into 订购单(订单号) values(or19) i

12、nsert into 订单名细 values(or01,1,GD001,4) insert into 订单名细 values(or01,2,GD001,3) insert into 订单名细 values(or01,3,GD002,6) insert into 订单名细(订单号,序号,产品号) values(or14,28,GD006) -2 delete from 客户 where 客户号=C001 -受参照完整性约束 delete from订购单 where 订单号=or01 -受参照完整性约束 -3 update 订购单 set 订单号=null where 客户号=C001 -受实体完

13、整性约束 update 订购单 set 客户号=C011 where 订单号=or04-受参照完整性约束 update 订购单 set 客户号=C009 where 订单号=or07 -更新成功 update 订单名细 set 数量=0 where序号=9 -受用户定义完整性约束 -4 update 订单名细 set 数量=数量+10 from 订购单 where 订购单.订单号=订单名细.订单号 and 客户号=C002 -5 delete from 订单名细 from 订购单 where 订购单.订单号=订单名细.订单号 and 客户号=C002 四、实验数据、结果分析： -2 在删除客户

14、号为“C002”的记录时无法删除，因为客户表被订购单表参照。 删除订购单号为“or01”的记录时无法删除，因为订单名细表参照订购单表。 -3 第一个更新根据实体完整性约束，订单号是主关键字，不能为空值。 第二个更新根据参照完整性约束，订购单表参照客户表，而客户表中没有客户号为“C011”的客户。 第四个更新根据用户定义完整性约束，数量必须为正整数。 -4 使客户号为C002的订购单的订购数量增加10. -5 删掉客户号为C002的订单名细记录。 五、总结： 插入:INSERT INTO (,) values(,) 更新：update set =,= from where 删除：DELETE F

15、ROM FROM WHERE 完整性约束影响插入、更新和删除等操作 数据库系统及应用 上机实验报告4 一、实验目的：熟练掌握SQL SELECT语句，能够运用该语句完成各种查询。 二、实验内容：用SQL SELECT语句完成各种数据查询。 三.、程序源代码： -1 select * from 客户 -2 select 客户号 from 订购单 -3 select * from 产品 where 单价=5000 -4 select * from 产品 where 单价5000 and 产品名称=Macbook -5 select * from 产品 where 单价6000 and 产品名称 i

16、n(Macbook,ipad) -6 select c.客户名称,c.联系人,c.电话,o.订单号 from 客户 c,订购单 o where o.订购日期 between 2011-10-30 and 2011-12-1 and c.客户号=o.客户号 -7 select distinct 客户名称,联系人,电话 from 客户 c,产品 g,订购单 o,订单名细 d where 产品名称=iphone and g.产品号=d.产品号 and d.订单号=o.订单号 and o.客户号=c.客户号 -8 select * from 订单名细 where 产品号 in( select 产品号

17、产品from where 产品名称=Macbook) -9 select * from 订购单 where 订单号 in( select 订单号 from 订单名细 where 数量10) -10 select * from 产品 where 单价 =(select 单价 from 产品 where 规格说明=4s) -11 select * from 产品 where 单价 between 1000 and 5000 -12 select * from 客户 where 客户名称 like %集团 -13 select * from 客户 where 客户名称 not like %商场 -1

18、4 select * from 产品 order by 单价 -15 select * from 产品 order by 产品名称,单价 -16 select COUNT(产品号) from 产品 -17 select SUM(数量) from 订单名细 where 产品号=(select 产品号 from 产品 where 产品名称=ipad) -18 select SUM(数量*单价) 总金额 from 产品,订单名细 where 产品.产品号=订单名细.产品号 and 产品名称=ipod nano -19 select COUNT(distinct 订单号)as 订购单个数,AVG(数量

19、*单价)as 平均金额 from 产品,订单名细 where 产品.产品号=订单名细.产品号 -20 select 订单号,COUNT(订单号) 项目数,SUM(g.单价*o.数量) 总金额 from 产品 g,订单名细 o 产品号=o.产品号where g. group by 订单号 -21 select i.订单号,MAX(数量*单价) 最高金额,MIN(数量*单价) 最低金额 from 订购单 o,产品 g,订单名细 i where o.订单号=i.订单号 and g.产品号=i.产品号 and 产品名称=iphone group by i.订单号 -22 select 订单号,COUN

20、T(*) 项目数,AVG(数量*单价) 平均金额 from 产品 g,订单名细 i where g.产品号=i.产品号 group by 订单号 having COUNT(*)=2 -23 select 客户名称,联系人,电话,订单号 from 客户 c,订购单 o where c.客户号=o.客户号 and 订购日期 is null -24 select 客户名称,联系人,电话,订单号,订购日期 from 客户,订购单 where 客户.客户号=订购单.客户号 and 订购日期2011-10-10 -25 select * from 产品 outa where 单价=(select MAX(

21、单价) from 产品 innera where outa.产品名称=innera.产品名称) -26 select 客户号 from 客户 where not exists( select * from 订购单 where 客户.客户号=订购单.客户号) -27 select * from 客户 where exists( select * from 订购单 ) 客户号.订购单=客户号.客户where -28 select 产品名称 from 产品 where 单价= any(select 单价/2 from 产品) -29 select 产品名称 from 产品 where 单价 all(

22、select max(单价) from 产品) -30 -cross select * from 客户 cross join 订购单 where 客户.客户号=订购单.客户号 -inner select * from 客户 inner join 订购单 on 客户.客户号=订购单.客户号 -left select 客户.*,订单号,订购日期 from 客户 left join 订购单 on 客户.客户号=订购单.客户号 -right select 客户.*,订单号,订购日期 from 客户 right join 订购单 on 客户.客户号=订购单.客户号 -full select 客户.*,订

23、单号,订购日期 from 客户 full join 订购单 on 客户.客户号=订购单.客户号 数据库系统及应用 上机实验报告5 一、实验目的：理解视图的概念，掌握视图的使用方法。 二、实验内容：定义视图，并在视图上完成查询、插入、更新和删除操作。 三.、程序源代码： -1 -1)、基于单个表按投影操作定义视图 create view v_cus as select 客户号,客户名称 from 客户 -使用 select * from v_cus -2)、基于单个表按选择操作定义视图 create view v_order as select * 订购单from where 客户号=C001

24、-使用 select * from v_order -3)、基于单个表按选择和投影操作定义视图 create view v_cuss as select 客户名称,联系人,电话 from 客户 where 客户号=C003 -使用 select * from v_cuss -4)、基于多个表根据连接操作定义视图 create view v_join as select 客户.*,订单号,订购日期 from 客户 join 订购单 on 客户.客户号=订购单.客户号 -使用 select * from v_join -5)、基于多个表根据嵌套查询定义视图 create view v_item a

25、s select * from 订单名细 where 产品号 in( select 产品号 from 产品 where 产品名称=Macbook) -使用 select * from v_item -6)、查定义含有虚字段的视图 create view v_items(订单号,序号,产品号,数量,总金额) as select i.*,i.数量*g.单价 from 订单名细 i,产品 g where i.产品号=g.产品号 -使用 select * from v_items -2 -在视图上查询 select * from v_items -在视图上插入 insert into v_cus va

26、lues(C012,Google) -在视图上更新 update v_order set 订购日期=2011-11-29 -在视图上删除 delete from v_cus where 客户号=C012 7 上机实验报告 数据库系统及应用 一、实验目的：理解存储过程的概念，掌握存储过程的使用方法，加深对客户/服务器机制的理解。 二、实验内容：存储过程的建立和执行。 三.、程序源代码： -1、查询单价范围在X元到Y元内的产品信息 create proc sp_getgoods price1 money,price2 money as select * from 产品 where 单价price1

27、 and 单价dates 订购日期and if ERROR= 0 return 0 else return -1 -执行declare status1 int execute status1=sp_or_cus 2011-10-27 print status1 、将某产品的订购日期统一修改为一个指定日期-3create proc sp_upd_date gdname varchar(40),odate datetime as update 订购单=odate 订购日期set in( 订单号where 订单号select 订单名细from where 产品号 in( select 产品号 from 产品 where 产品名称=gdname) if ERROR= 0 return 0 else return -1 -执行 declare status2 int execute status2=sp_upd_date iphone,2011-10-23