数据库原理实验报告

《数据库原理实验报告》由会员分享，可在线阅读，更多相关《数据库原理实验报告（18页珍藏版）》请在装配图网上搜索。

1、 数据库原理实验报告 学院：电信学院 系别：计算机系 班级： 计科112班 姓名：翟舒青 学号：111404060227 实验一：数据定义及更新语句练习一实验目的： 熟练掌握用SQL语句实现数据库和基本表的创建、数据的更新。二实验内容： （一）用SQL语句建立如下mySPJ数据库，包括S，P，J，和SPJ四个基本表(教材第二章习题5中的四个表)，要求实现关系的三类完整性。S(SNO,SNAME,STATUS,CITY);P(PNO,PNAME,COLOR,WEIGHT);J(JNO,JNAME,CITY);SPJ(SNO,PNO,JNO,QTY)供应商表S由供应商代码（SNO）、供应商姓名（S

2、NAME）、供应商状态（STATUS）、供应商所在城市（CITY）组成。零件表P由零件代码（PNO）、零件名（PNAME）、颜色（COLOR）、重量（WEIGHT）组成。工程项目表J由工程项目代码（JNO）、工程项目名（JNAME）、工程项目所在城市（CITY）组成。供应情况表SPJ由供应商代码（SNO）、零件代码（PNO）、工程项目代码（JNO）、供应数量（QTY）组成，表示某供应商 供应某种零件 给某工程项目的数量为QTY。 （二）分别使用插入、删除、修改的方式更新基本表中的数据。三完成情况：1.利用Insert 语句将习题中给出的示例记录插入各表。 insert into S(SNO,S

3、NAME,STATUS,CITY) values(S5,为民,30,上海);2.利用Update更新表中的记录：将p表中的所有红色零件的重量增加5。 update P set WEIGHT=WEIGHT+5 where COLOR=红;将spj表中所有天津供应商的QTY属性值减少10。用子查询。 update SPJ set QTY=QTY-10 where 天津= (select CITY from S where SPJ.SNO=S.SNO); 3.利用Delete语句删除p表中的所有红色零件的记录。 delete from P where COLOR=红; 四.实验结果1.利用Inser

4、t 语句将习题中给出的示例记录插入各表。S5 为民 30上海 S1 P1 J1 190S1 P1 J3 90S1 P1 J4 690S1 P2 J2 90S2 P3 J1 400S2 P3 J2 200S2 P3 J4 500S2 P3 J5 400S2 P5 J1 400S2 P5 J2 100S3 P1 J1 200S3 P3 J1 200S4 P5 J1 90S4 P6 J3 290S4 P6 J4 190S5 P2 J4 100S5 P3 J1 200S5 P6 J2 200S5 P6 J4 500NULLNULLNULLNULL2.利用Update更新表中的记录： 将p表中的所有红色

5、零件的重量增加5。P1 螺母 红 17P2 螺栓 绿 17P3 螺丝刀 蓝 14P4 螺丝刀 红 19P5 凸轮 蓝 40P6 齿轮 红 35NULLNULLNULLNULL将spj表中所有天津供应商的QTY属性值减少10。用子查询。 3.利用Delete语句删除p表中的所有红色零件的记录。P2 螺栓 绿 17P3 螺丝刀 蓝 14P5 凸轮 蓝 40NULLNULLNULLNULL 5 问题及解决 试验一较简单，未出现错误。六思考题 1.在为各表设定关键字时弹出的页面中有关键字和索引选项，试通过给基本表设定这两个项目并说明二者的区别。 答：关键字受主键影响，而索引不受主键影响。索引可以加快表

6、的查询速度，通常我们将经常用来查询的一个或者几个字段设置为索引，但不宜过多，3个内最好； 关键字是一个表里能够唯一区分开每条数据的字段，其主要作用是用来和其他表进行关联的；虽然一个表可能存在多个能够区分开每条数据的字段，但我们通常选择可以唯一标识的，易于关联其他表的那个字段作为关键字。 2. 在基本表中输入数据时，注意数据与字段的数据类型和长度以及能否为空的属性是否一致，保存时如有错误分析错误原因，及时改正并将错误报告写在实验报告中。七实验总结： 答：从这次实验中我明白一个道理：细节决定成败，尤其是要注意sql语言与c语言的差别。 实验二：简单查询和连接查询一实验目的： 熟练掌握用SQL语句实

7、现的简单查询和多个数据表连接查询。二实验内容：(一) 完成下面的简单查询：查询所有“天津”的供应商明细；查询所有“红色”的14公斤以上的零件。查询工程名称中含有“厂”字的工程明细。(二) 完成下面的连接查询：等值连接：求s表和j表的相同城市的等值连接。自然连接：查询所有的供应明细，要求显示供应商、零件和工程的名称，并按照供应、工程、零件排序。笛卡尔积：求s和p表的笛卡尔积左连接：求j表和spj表的左连接。右连接：求spj表和j表的右连接。三完成情况： 1.查询所有“天津”的供应商明细； select * from S where CITY=天津; 2.查询所有“红色”的14公斤以上的零件。 s

8、elect * from P where COLOR=红 AND WEIGHT14; 3.查询工程名称中含有“厂”字的工程明细。 select JNO,JNAME,CITY from J where JNAME LIKE %厂%; 4.等值连接：求s表和j表的相同城市的等值连接。 select S.*,J.* from S,J where S.CITY=J.CITY; 5.自然连接：查询所有的供应明细，要求显示供应商、零件和工程的名称，并按照供应、工程、零件排序。 select SNAME,PNAME,JNAME from S,P,J,SPJ where S.SNO=SPJ.SNO AND P

9、.PNO=SPJ.PNO AND J.JNO=SPJ.JNO ORDER BY S.SNO,J.JNO,P.PNO; 6.笛卡尔积：求s和p表的笛卡尔积。 select S.*,P.* from S,P 7.左连接：求j表和spj表的左连接。 select J.JNO,JNAME,CITY,SNO,PNO,QTY from J left outer join SPJ on(J.JNO=SPJ.JNO); 8.右连接：求spj表和j表的右连接。 select SPJ.JNO,SNO,PNO,QTY,JNAME,CITY from SPJ right outer join J on(SPJ.JNO

10、=J.JNO);四实验结果： 1.查询所有“天津”的供应商明细； 2.查询所有“红色”的14公斤以上的零件。 3.查询工程名称中含有“厂”字的工程明细。 4.等值连接：求s表和j表的相同城市的等值连接。 5.自然连接：查询所有的供应明细，要求显示供应商、零件和工程的名称，并按照供应、工程、零件排序。 6.笛卡尔积：求s和p表的笛卡尔积。 7.左连接：求j表和spj表的左连接。 8.右连接：求spj表和j表的右连接。 五问题及解决：1.查询工程名称中含有“厂”字的工程明细出现了问题，经过反复实验发现 应该写成%厂&才可以查找出来所需结果。2.笛卡尔积：求s和p表的笛卡尔积。这个由于书上没有范例，

11、所以通过上网查资料，翻阅书籍解决了问题。 3.左右链接由于书上写的是out导致了以下错误 消息155，级别15，状态1，第2 行 out 不是可以识别的join 选项。把out去掉是正确的。通过看答案知道是outer。六思考题： 查看实验内容2中左连接和右连接的执行结果是否一致，为什么？ 答：不一样，左连接是输出第一个表的全部，二右连接是输出第二个表的全部。七实验总结： 遇到不会的问题，不要放弃，应该通过查阅资料或者请教同学和老师，弄明白原理，增加自己的阅读量。 实验三：分组查询和嵌套查询一实验目的：熟练掌握用SQL语句实现多个数据表的分组查询和嵌套查询。二实验内容：(一) 分组查询：1. 求

12、各种颜色零件的平均重量。2. 求北京供应商和天津供应商的总个数。3. 求各供应商供应的零件总数。4. 求各供应商供应给各工程的零件总数。5. 求使用了100个以上P1零件的工程名称。6. 求各工程使用的各城市供应的零件总数。（二） 嵌套查询：1. in连接谓词查询： 查询没有使用天津供应商供应的红色零件的工程名称。 查询供应了1000个以上零件的供应商名称。(having)2. 比较运算符：求重量大于所有零件平均重量的零件名称。3. Exists连接谓词： 查询供应J1的所有的零件都是红色的供应商名称。 至少用了供应商S1所供应的全部零件的工程号JNO。3 完成情况： 1. 求各种颜色零件的平

13、均重量。 select COLOR,AVG(WEIGHT) from P group by COLOR; 2.求北京供应商和天津供应商的总个数。 select city,COUNT(city) as number from S where city=北京 or city=天津 group by CITY; 3.求各供应商供应的零件总数。 select S.SNO,SNAME,SUM(QTY) from S,SPJ where S.SNO=SPJ.SNO group by S.SNO,SNAME； 4.求各供应商供应给各工程的零件总数。 select SNAME,JNAME,SUM(QTY) a

14、s 零件总数 from S,J,SPJ where S.SNO=SPJ.SNO and J.JNO=SPJ.JNO group by SNAME,JNAME; 5.求使用了100个以上P1零件的工程名称。 select JNAME,SUM(QTY) from J,SPJ where J.JNO=SPJ.JNO and PNO=p1 group by JNAME having SUM(QTY)100; 6.求各工程使用的各城市供应的零件总数。 select JNAME,S.CITY,SUM(QTY) from J,S,SPJ where S.SNO=SPJ.SNO and J.JNO=SPJ.J

15、NO group by JNAME,S.CITY; 7.查询没有使用天津供应商供应的红色零件的工程名称。 select JNO,JNAME from J where not exists (select * from SPJ where SPJ.JNO=J.JNO and SNO in (select SNO from S where CITY=天津) and PNO in (select PNO from P where COLOR=红); 8.查询供应了1000个以上零件的供应商名称。(having) select SNAME,SUM(QTY) from S,SPJ where S.SNO

16、=SPJ.SNO group by SNAME having SUM(QTY)1000； 9.比较运算符：求重量大于所有零件平均重量的零件名称。 select PNAME from P where WEIGHT (select AVG(WEIGHT) from P); 10. 查询供应J1的所有的零件都是红色的供应商名称。 select SNO,SNAME from S where SNO in (select distinct SNO from SPJ x where not exists (select * from SPJ y where y.SNO=x.SNO and y.JNO=J1

17、 and not exists (select * from P where P.PNO=y.PNO and COLOR=红) 11.至少用了供应商S1所供应的全部零件的工程号JNO。 select distinct JNO from SPJ x where not exists (select * from SPJ y where SNO=S1 and not exists (select * from SPJ z where z.PNO=y.PNO and z.JNO=x.JNO);四实验结果：1. 求各种颜色零件的平均重量。 2. 求北京供应商和天津供应商的总个数。 3. 求各供应商供应

18、的零件总数。 4. 求各供应商供应给各工程的零件总数。 5. 求使用了100个以上P1零件的工程名称。 6. 求各工程使用的各城市供应的零件总数。 7.查询没有使用天津供应商供应的红色零件的工程名称。 8. 查询供应了1000个以上零件的供应商名称。(having) 9. 比较运算符：求重量大于所有零件平均重量的零件名称。 10. 查询供应J1的所有的零件都是红色的供应商名称。 11.至少用了供应商S1所供应的全部零件的工程号JNO。 五问题及解决：由于嵌套查询学的不太好，总是出错。所以又重新复习了一遍课本，反复练习，问题已解决。 例如：求使用了1000个以上P1零件的工程名称。不用在pno=

19、p1时，再对qty求和，系统会自动求和。6 思考题：1. 嵌套查询中的in连接谓词查询，关键字in可以直接用any代替么？什么情况下in和any可以互相代替？ 答：关键字in不可以直接用any代替。=any和in可以互相替换。2. 嵌套查询中的内查询为外查询返回的内容是什么？是表达式？视图？还是物理数据集合？ 答：在嵌套查询中，子查询的结果往往是一个集合（带in、any的嵌套查询），但是带有Exists谓词的子查询不返回任何数据，只产生逻辑真值“true”或者逻辑真值“false”。7 实验总结：带exists和in的语句不太好理解，总是比较混乱，达不到题目要求。需要大量的练习及理解。 实验四

20、：视图操作和数据控制 一实验目的： 熟练掌握用SQL语句实现视图操作和数据控制。二实验内容: (一）定义如下视图：查询北京的供应商的编号、名称和城市。查询S1供应商的所有供应明细。查询各工程名称使用的各种颜色零件的个数。查询上面定义的视图。 （二）数据控制： 使用GRANT把对S表查询的权利授予WangLi。 使用GRANT把对P表查询、插入、修改、删除的权利授予LiMing。 使用REVOKE把LiMing对P表插入、删除的权利回收。三完成情况： 1.查询北京的供应商的编号、名称和城市。 create view S_bj(SNO,SNAME,CITY) as (select SNO,SNAM

21、E,CITY from S where CITY=北京); 2.查询S1供应商的所有供应明细。 create view s1_SPJ as (select SNO,PNO,JNO,QTY from SPJ where SNO=S1); 3.查询各工程名称使用的各种颜色零件的个数。 create view JNAME_COLOR_QTY(JNAME,COLOR,QTY) as select JNAME,COLOR,SUM(QTY) as QTY from J,P,SPJ where J.JNO=SPJ.JNO and P.PNO=SPJ.PNO group by JNAME,COLOR； cre

22、ate view jnamecolorqty(JNAME,COLOR,QTY) as select JNAME,COLOR,QTY from JNAME_COLOR_QTY 4.查询上面定义的视图 select * from S_bj select * from S1_SPJ select * from JNAME_COLOR_QTY 5.使用GRANT把对S表查询的权利授予WangLi。 grant select on S to WangLi; 6.使用GRANT把对P表查询、插入、修改、删除的权利授予LiMing。 grant select,insert,update,delete on

23、P to LiMing; 7.使用REVOKE把LiMing对P表插入、删除的权利回收。 revoke insert,delete on P from LiMing;四实验结果： 1.查询北京的供应商的编号、名称和城市。 2.查询S1供应商的所有供应明细。 3.查询各工程名称使用的各种颜色零件的个数。 五问题及解决： 使用GRANT把对S表查询的权利授予WangLi应该是： grant select on S to WangLi; 而不是：grant select on table S to WangLi; 出现错误： 消息156，级别15，状态1，第3 行 关键字TO 附近有语法错误。 消息15151，级别16，状态1，第1 行 无法对用户WangLi 执行查找，因为它不存在，或者您没有所需的权限。六思考题：实验内容中的哪些视图可以用来更新记录？ 答:一般的，行列子集视图是可更新的。 7 实验总结： 只看书不练习是不会真正学会sql语句的，但是在练习时有迷惑的地方还应该仔细看看书，回归课本。