数据定义及更新语句练习

实验一数据定义及更新语句练习实验内容用SQL语句建立MYSPJ数据库，包括S，P，J，和SPJ四个基本表参见实验指导书，要求实现关系的三类完整性分别使用插入、删除、修改语句更新基本表中的数据实验方法和步骤建立数据库MYSPJ打开“开始程序MICROSOFTSQLSERVER企业管理器”在企业管理器左边的树标签中依次打开“MICROSOFTSQLSERVERSQLSERVER组LOCALWINDOWSNT数据库”，LOCALWINDOWSNT前的红色标记转化为绿色标记表明SQLSERVER服务已启动从企业管理器的“工具”菜单中选择“SQL查询分析器”，在其窗口书写创建数据库MYSPJ的语句，点击执行按钮（或F5键）执行该SQL语句在企业管理器左边的树标签中查看数据库是否建立成功注意可以直接在企业管理器中创建数据库在MYSPJ中建立S,P,J和SPJ四个基本表在查询分析器中使用CREATETABLE语句建立各表，注意设置主键外键约束注意主键外键约束的建立有两种方法可以通过SQL语句建立也可以在企业管理器中设置比较两种用法更新语句的使用使用INSERT语句将习题中给出的示例记录插入各表使用UPDATE语句更新表中的记录将P表中的所有红色零件的重量增加5将SPJ表中所有天津供应商的QTY属性值减少10使用DELETE语句删除P表中的所有红色零件的记录SQL语句执行后返回基本表查看更新后的结果，若与期望不符，分析原因并记录在实验报告中重点难点重点CREATETABLE语句以及INSERT、UPDATE、DELETE语句的使用表中各字段数据类型和长度的选择三类完整性的设置用SQL语句如何实现难点关于外键及如何保证参照完整性的理解违反各类完整性时系统如何处理级联更新和级联删除的使用注意事项注意在企业管理器中可以直接创建数据库、表，插入、删除、修改记录以及设置主键外键约束本实验的目的是掌握数据定义及更新语句，故要求在查询分析器中通过SQL语句实现实验内容实验二简单查询和连接查询实验内容完成下面的简单查询查询所有“天津”的供应商明细；查询所有“红色”的14公斤以上的零件。查询工程名称中含有“厂”字的工程明细。完成下面的连接查询等值连接求S表和J表的相同城市的等值连接。自然连接查询所有的供应明细，要求显示供应商、零件和工程的名称，并按照供应商、工程、零件排序。笛卡尔积求S和P表的笛卡尔积左连接求J表和SPJ表的左连接。右连接求SPJ表和J表的右连接。实验方法和步骤在MICROSOFTSQLSERVER中打开实验一建立的MYSPJ数据库在MICROSOFTSQLSERVER查询分析器中设计并执行SQL语句记录执行结果，若有错误，分析原因并改正重点难点重点简单SELECT语句使用自然连接的使用难点自然连接的实现过程左外连接和右外连接的区别与联系注意事项实验过程中对有疑问的语句要多实践尝试多种方法来解决同一问题，巩固所学理论知识例如查询所有“天津”的供应商明细方法1SELECTFROMSWHERECITY天津方法2SELECTSNO,SNAME,CITY,STATUSFROMSWHERECITY天津部分实验结果等值连接求S表和J表的相同城市的等值连接自然连接查询所有的供应明细，要求显示供应商、零件和工程的名称，并按照供应商、工程、零件排序。实验三分组查询和嵌套查询实验内容分组查询求各种颜色零件的平均重量。求北京供应商和天津供应商的总个数。求各供应商供应的零件总数。求各供应商供应给各工程的零件总数。求使用了100个以上P1零件的工程名称。求各工程使用的各城市供应的零件总数。嵌套查询IN连接谓词查询查询没有使用天津供应商供应的红色零件的工程名称查询供应了1000个以上零件的供应商名称HAVING比较运算符查询求重量大于所有零件平均重量的零件名称EXISTS连接谓词查询供应J1的所有的零件都是红色的供应商名称至少用了供应商S1所供应的全部零件的工程号JNO实验方法和步骤在MICROSOFTSQLSERVER中打开实验一建立的MYSPJ数据库在MICROSOFTSQLSERVER查询分析器中设计并执行SQL语句记录执行结果，若有错误，分析原因并改正重点难点重点分组查询及集函数的使用嵌套查询难点GROUPBY子句的使用，HAVING短语的使用EXISTS量词的使用关于分组GROUPBY子句将查询结果按某一列或多列分组，值相等的为一组。对查询结果分组的目的是为了细化集函数的作用对象，分组后每个组都有一个函数值如果分组后还要求按一定的条件对这些组进行筛选，最终只输出满足指定条件的组，则可以是使用HAVING短语指定筛选条件回顾分组实例例求各个课程号及相应的选课人数SELECTCNO,COUNTSNOFROMSCGROUPBYCNO例查询选修了3门以上课程的学生学号SELECTSNOFROMSCGROUPBYSNOHAVINGCOUNT3关于集函数SELECTCOUNTFROMSPJ得到表SPJ的记录数SELECTCOUNTSNOFROMSPJ得到表SPJ中SNO字段非空记录数SELECTCOUNTDISTINCTSNOFROMSPJ得到表SPJ中SNO字段值非相同记录数部分实验结果查询没有使用天津供应商供应的红色零件的工程名称查询供应了1000个以上零件的供应商名称求重量大于所有零件平均重量的零件名称查询供应J1的所有的零件都是红色的供应商名称至少用了供应商S1所供应的全部零件的工程号JNO实验四视图操作和数据控制实验内容定义如下视图查询北京的供应商的编号、名称和城市查询S1供应商的所有供应明细查询各工程名称使用的各种颜色零件的个数查询上面定义的视图数据控制（GRANT和REVOKE语句）使用GRANT把对S表查询的权利授予WANGLI。使用GRANT把对P表查询、插入、修改、删除的权利授予LIMING。使用REVOKE把LIMING对P表插入、删除的权利回收。实现习题四P148中第8，9题实验方法和步骤在MICROSOFTSQLSERVER中打开实验一建立的MYSPJ数据库在MICROSOFTSQLSERVER查询分析器中设计并执行SQL语句注意视图创建后只能看到视图的定义，语句并不执行，视图定义后可以像基本表一样使用注意数据控制语句使用前必须先创建用户授权之后执行相应的SQL语句查看验证授权语句是否正确执行记录执行结果，若有错误，分析原因并改正重点难点视图的创建和查询理解视图的执行过程验证SQLSERVER中哪些视图可以更新授权后对授权语句的验证以及收回授权后的验证视图部分实验结果查询北京的供应商的编号、名称和城市查询S1供应商的所有供应明细查询各工程名称使用的各种颜色零件的个数实验五触发器和存储过程实验内容触发器和存储过程有一个小型的图书管理数据库，包含的表为BOOKSTORE（BOOKID，BOOKNAME，BOOKAUTHOR，PURCHASEDATE，STATE）；图书库存表BORROWCARD（CARDID，OWNERNAME）；借书证表BORROWLOG（CARDID，BOOKID，BORROWDATE，RETURNDATE）；借书记录表写一个存储过程，实现借书操作，要求有事务处理。（1）读者借书，要先设置书籍不在库标志STATE（借出），然后增加借书记录，在同一事务中完成。（2）要求在事务执行过程中引入错误触发事件，以此体会事务的错误保护机制和事务编程的作用。（3）要求用触发器实现表的完整性控制。实验方法和步骤触发器在MICROSOFTSQLSERVER中打开实验一建立的MYSPJ数据库在查询分析器中编写触发器，参考CREATETRIGGER语句存储过程创建图书借阅数据库，注意保证三类完整性在查询