1、河南科技大学数据库实验报告实验一：数据定义及更新语句练习一实验目的： 熟练掌握用 SQL语句实现数据库和基本表的创建、数据的更新。二实验内容：（一）用 SQL语句建立如下 mySPJ数据库，包括 S，P，J，和 SPJ四个基本表 （ 教材第二章 习题 5 中的四个表 ） ，要求实现关系的三类完整性。S（SNO,SNAME,STA TUS,CITY）;P（PNO,PNAME,COLOR,WEIGHT）;J（JNO,JNAME,CITY）;SPJ（SNO,PNO,JNO,QTY）供应商表 S 由供应商代码（ SNO）、供应商姓名（ SNAM）E、供应商状态（ STATUS）、供应 商所在城市（ C
2、ITY）组成。零件表 P 由零件代码（ PNO）、零件名（ PNAM）E、颜色（ COLOR）、重量（ WEIGHT）组成。 工程项目表 J 由工程项目代码 （ JNO）、工程项目名 （JNAME）、工程项目所在城市 （CITY） 组成。供应情况表 SPJ 由供应商代码（ SNO）、零件代码（ PNO）、工程项目代码（ JNO）、供应数 量（ QTY）组成，表示某供应商 供应某种零件 给某工程项目的数量为 QTY。（二）在数据库 mySPJ 中建立 S，P，J，和 SPJ四个基本表：按照实验内容给出的基本表结构在查询分析器窗口中书写 SQL 语句分别建立各表，并 设置主键和外键约束；三完成情况
3、USE masterGOCREATE DATABASE myspjON（ NAME = TEXTS_dat ,FILENAME = 数据库实验 myspjdat.mdf ,SIZE = 10 ,MAXSIZE = 50 ,FILEGROWTH = 5 ）LOG ON（ NAME = TEXTS_log ,FILENAME = 数据库实验 myspjlog.ldf ,SIZE = 5 MB,MAXSIZE = 25 MB,FILEGROWTH = 5 MB )bGO create table s(sno char ( 9) primary key , sname char ( 20), stat
4、u int ,city char ( 20 ),);create table p(pno char ( 9) primary key , pname char ( 20), color char ( 20), weigh int ,);create table j(jno char ( 9) primary key , jname char ( 20), city char ( 20 ),);create table spj sno char ( 9), pno char ( 9), jno char ( 9), qty int ,primary key ( sno , pno , jno )
5、,foreign key ( sno ) references s( sno ),foreign key ( pno ) references p( pno ),foreign key ( jno ) references j ( jno ),);1.利用 Insert 语句将习题中给出的示例记录插入各表。 insert into svalues ( s1 , 精益 , 20 , 天津 );insertinto svalues ( s2 , 盛锡 , 10 , 北京 );insert into svalues( s3,东方红 , 30 , 北京 );insertintosvalues( s4,
6、丰泰盛 , 20 , 天津 );insertintosvalues( s5,为民,30 , 上海 );insertintopvalues( p1,螺母 ,红 , 12 );insertintopvalues( p2,螺栓 ,绿 , 17 );insertintopvalues( p3,螺丝刀 , 蓝 ,14 );insertintopvalues( p4,螺丝刀 , 红 ,14 );insertintopvalues( p5,凸轮 ,蓝 , 40 );insertintopvalues( p6,齿轮 ,红 , 30 );insertintojvalues( j1,三建 ,北京 );insert
7、intojvalues( j2,一汽 ,长春 );insertintojvalues( j3,弹簧厂 , 天津 );insertintojvalues( j4,造船厂 , 天津 );insertintojvalues( j5,机车厂 , 唐山 );insertintojvalues( j6, 无线电厂, 常州 );insertintojvalues( j7, 半导体厂insertintospjvalues( s1, p1, j1insertintospjvalues( s1, p1, j3insertintospjvalues( s1, p1, j4insertintospjvalues( s
8、1, p2, j2insertintospjvalues( s2, p31, j1insertintospjvalues( s2, p3, j2insertintospjvalues( s2, p3, j4insertintospjvalues( s2, p3, j5insertintospjvalues( s2, p5, j1insertintospjvalues( s2, p5, j2insertintospjvalues( s3, p1, j1insertintospjvalues( s3, p3, j1insertintospjvalues( s4, p5, j1insertintos
9、pjvalues( s4, p6, j3南京 );, 200 );, 100 );, 700 );, 100 );, 400 );, 200 );, 500 );, 400 );, 400 );, 100 );, 200 );, 200 );, 100 );, 300 );insert into spj values ( s4 insert into spj values ( s5 insert into spj values ( s5 insert into spj values ( s5, p6 , j4, p2 , j4, p3 , j1, p6 , j2, 200 );, 100 );
10、, 200 );, 200 );2.利用 Update 更新表中的记录：1将 p表中的所有红色零件的重量增加 5。update pset weigh =weigh +5 where color = 红 ;2 将 spj 表中所有天津供应商的 QTY属性值减少 10 。用子查询。 并记录在实验报告中。update spjset qty =qty - 10where sno in(select snofrom swhere city = 天津 );3.利用 Delete 语句删除 p 表中的所有红色零件的记录。deletefrom pwhere color = 红 ;思考题（一） 在为各表设定关键
11、字时弹出的页面中有关键字和索引选项， 试通过给基本表设定 这两个项目并说明二者的区别。答: 关键字受主键影响，而索引不受主键影响二） 在基本表中输入数据时， 注意数据与字段的数据类型和长度以及能否为空的属性 是否一致，保存时如有错误分析错误原因，及时改正并将错误报告写在实验报告中。 七实验总结：心得体会，建议等。答：从这次实验中我明白一个道理： 细节决定成败， 尤其是要注意 sql 语言与 c 语言的差别。实验二：简单查询和连接查询一实验目的： 熟练掌握用 SQL语句实现的简单查询和多个数据表连接查询。二实验内容：（一）完成下面的简单查询：1查询所有“天津”的供应商明细； 查询所有“红色”的
12、14 公斤以上的零件。 查询工程名称中含有“厂”字的工程明细。（二）完成下面的连接查询：1等值连接：求 s 表和 j 表的相同城市的等值连接。2自然连接：查询所有的供应明细，要求显示供应商、零件和工程的名称， 并按照供应、工程、零件排序。3笛卡尔积：求 s 和 p 表的笛卡尔积4左连接：求 j 表和 spj 表的左连接。5右连接：求 spj 表和 j 表的右连接。三完成情况（三）完成下面的简单查询：1询所有“天津”的供应商明细；select *from swhere city = 天津 ;2查询所有“红色”的 14 公斤以上的零件。select pnofrom p where color =
13、红 and weigh 14 ;3查询工程名称中含有“厂”字的工程明细。select *from jwhere jname like % 厂 ;(4)完成下面的连接查询：1值连接：求 s 表和 j 表的相同城市的等值连接。select s.*, j .*from s, jwhere s. city =j . city ;2自然连接：查询所有的供应明细，要求显示供应商、零件和工程的名称， 并按照供应、工程、零件排序。select s. sname , p. pname , j . jnamefrom s, p, j , spjwhere s. sno =spj . sno and p. pno
14、=spj . pno and j . jno =spj . jno3笛卡尔积：求 s 和 p 表的笛卡尔select *from s, p4左连接：求 j 表和 spj 表的左连接。select j .*, sno , pno , qtyfrom j left outer join spj on ( j . jno =spj . jno );5右连接：求 spj 表和 j 表的右连接。select j .*, sno , pno , qty from spj right outer join j on ( j . jno =spj . jno );回答思考题提出的问题，查看实验内容 2 中左连
15、接和右连接的执行结果是否一致，为什么？ 答：不一样，左连接是输出第一个表的全部，二右连接是输出第二个表的全部。七实验总结：心得体会，建议等。答：从这次实验中我明白一个道理： 细节决定成败， 尤其是要注意 sql 语言与 c 语言的差别。实验三：分组查询和嵌套查询、 实验目的： 熟练掌握用 SQL语句实现多个数据表的分组查询和嵌套查询。、实验内容： 完成下面的查询：(一) 完成下面的查询：1、分组查询： 求各种颜色零件的平均重量。 selectcolor,AVG(weigh)weight from p group by color;2求北京供应商和天津供应商的总个数。 select COUNT
16、(city) 个数 from swhere city =北京 or city = 天津 ;3求各供应商供应的零件总数。select sno,sum(qty )数目from spjgroup by snoorder by 数目 asc;4求各供应商供应给各工程的零件总数。select sno,jno ,sum(qty )数目from spjgroup by sno,jno;5求使用了 100 个以上 P1 零件的工程名称。 select jname from j where jno in(select jnofrom spjwhere pno=p1 and qty100);6求各工程使用的各城市
17、供应的零件总数。 select jname from jwhere jno in(select jnofrom spjwhere pno=p1 and qty100);二) 嵌套查询：1. in 连接谓词查询： 查询没有使用天津供应商供应的红色零件的工程名称。select jnamefrom jwhere jno not in(select jno from s,p,spj where spj.sno=s.snoand spj.pno=p.pno and p.color = 红and s.city=天津 ) 查询供应了 1000 个以上零件的供应商名称。 (having) select sna
18、mefrom swhere snoin(select snofrom spjgroup bysnohaving SUM (qty)1000)2.比较运算符：求重量大于所有零件平均重量的零件名称。 select pname from p where weigh ( select AVG ( weigh ) from p3.)Exists 连接谓词： 查询供应 J1 的所有的零件都是红色的供应商名称。select sname from s where exists( select * from p,spjwhere p.pno=spj.pno andspj .sno=s.sno and jno =
19、j1 and color=红); 至少用了供应商 S1 所供应的全部零件的工程号 JNO。实验五：触发器和存储过程一、 实验报告要求：（一 ） 实验目的： 理解触发器和存储过程的含义， 掌握用 SQL语句实现触发器和存储过 程的编写，并初步掌握什么情况下使用事务。（二 ） 实验内容：有一个小型的图书管理数据库，包含的表为：bookstore （ bookid ， bookname， bookauthor ， purchasedate ， state ）；- 图书库 存表borrowcard （ cardid ， ownername）； - 借书证表borrowlog （ cardid ， bo
20、okid ， borrowdate ， returndate ）； - 借书记录表 写一个存储过程，实现借书操作，要求有事务处理。 （ 1）读者借书，要先设置书籍不在库标志 state（借出），然后增加借书记录，在同一事务中完成。 （ 2）要求在事务执行过程中引入错误触发事件，以此体会事务的错误保护机制和事务编程的作用。 （3）要求用触发器实现表的完整性控制。（三）完成情况（附上设计的 SQL语句）。建立数据库：create database bookstoruse bookstorcreate table bookstore（bookid int,bookname char(20), boo
21、kauthor char(20), purchasedate char(20), stat char( 6), primary key (bookid ), Check(statin(在库,不在库 ), );create table borrowcard(cardid int primary key, ownername char(20),);create table borrowlog (cardid int, bookid int, borrowdate char(20), returndate char(20), primary key(cardid,bookid ), foreign
22、key (cardid)references borrowcard (cardid ), foreign key(bookid )references bookstore(bookid ),);存储过程：char(20),returndateand stat= 在 库 andcreate procedure dbo .jieshu (cardid int ,bookid int ,borrowdate char(20) asbegin transactioninsertinto borrowlog values(cardid ,bookid ,borrowdate ,returndate )
23、if exists(select * from bookstore,borrowcard where bookid =bookid cardid = cardid )begin update bookstore set stat=不在库 where bookid =bookid commit transaction end else beginif not exists(select* from bookstore where bookid =bookid ) print不存在该书 if not exists(select* from borrowcard where cardid= card
24、id ) print没有此用户 rollback transactionEnd查询语句： exec jieshu 1002062,1,20121103,2012124结果：触发器 :create trigger storon borrowlogafter insertasif （new.borrowdate new.returndate） print失败，还书时间不应早于结束时间 beginrollback transactionend然后分析错误原（四） 问题及解决： 首先写出执行语句不成功的时候系统报告的错误信息。 因，并给出解决办法。（五）回答思考题提出的问题，思考题：如何通过系统的设置实现类似的功能，而不需触发器？答：建立数据库时定义完整性约束条件。
