数据库初级复习归纳

...wd......wd......wd...SQLSERVER2005操作题和选择题复习汇总松江分校王芳2012年6月视图类〔考题中的第2小题，必须得分〕新建视图SalesV，显示当前每个供给商的库存货物所占用资金的总和。要求视图显示的字段为“公司名〞、“库存资金〞，其中“库存资金〞为该供给商所供给的产品的“库存量〞*“单价〞的总和。在视图里新建，添加两个表Suppliers和Products，按题目意思设置好之后保存，对应的代码如下：CREATEVIEWdbo.SalesVASSELECTdbo.Suppliers.公司名,SUM(dbo.Products.单价*dbo.Products.库存量)AS库存资金FROMdbo.SuppliersINNERJOINdbo.ProductsONdbo.Suppliers.供货商ID=dbo.Products.供货商IDGROUPBYdbo.Suppliers.公司名【例2】新建视图ntv，显示1997年的分类销售情况汇总〔CategorySalesfor1997〕。要求视图显示的字段为“类名〞、“销售金额〞。注意：需要用到的基表为：“Orders〞、“OrderDetails〞、“Products〞和“Categories〞。在视图上右键新建视图，添加上面的四个表，做好后保存为ntv，名字不能错。其对应的代码为：CREATEVIEW[dbo].[ntv]ASSELECTdbo.Categories.类名,SUM((dbo.OrderDetails.单价*dbo.OrderDetails.数量)*(1-dbo.OrderDetails.折扣))AS销售金额FROMdbo.CategoriesINNERJOINdbo.ProductsONdbo.Categories.类ID=dbo.Products.类IDINNERJOINdbo.OrderDetailsONdbo.Products.产品ID=dbo.OrderDetails.产品IDINNERJOINdbo.OrdersONdbo.OrderDetails.订单ID=dbo.Orders.订单IDWHERE(dbo.Orders.运输日期BETWEENCONVERT(DATETIME,'1997-01-0100:00:00',102)ANDCONVERT(DATETIME,'1997-12-3100:00:00',102))GROUPBYdbo.Categories.类名GO【例3】新建视图SalesV，显示1997年8月要付给每个承运商的运费汇总。要求视图显示的字段为“承运商名〞、“运费汇总〞，其中“运费汇总〞为该承运商所承运定单的运费的总和。在视图里新建，添加两个表Shippers和Orders，按题目意思设置好之后保存，然后可以查看SalesV的属性，代码如下：CREATEVIEWdbo.SalesVASSELECTdbo.Shippers.承运商名,SUM(dbo.Orders.运价)AS运费汇总FROMdbo.OrdersINNERJOINdbo.ShippersONdbo.Orders.承运商ID=dbo.Shippers.承运商IDWHERE(dbo.Orders.运输日期BETWEENCONVERT(DATETIME,'1997-08-0100:00:00',102)ANDCONVERT(DATETIME,'1997-08-3100:00:00',102))GROUPBYdbo.Shippers.承运商名【例4】新建视图SalesV2，显示当前每个大类的库存货物所占用资金的总和。要求视图显示的字段为“类名〞、“库存资金〞，其中“库存资金〞为该大类的产品的“库存量〞*“单价〞的总和。在视图里新建，添加两个表Categories和Products，按题目意思设置好之后保存，然后可以查看SalesV2的属性，代码如下：CREATEVIEW[dbo].[SalesV2]ASSELECTdbo.Categories.类名,SUM(dbo.Products.单价*dbo.Products.库存量)AS库存资金FROMdbo.CategoriesINNERJOINdbo.ProductsONdbo.Categories.类ID=dbo.Products.类IDGROUPBYdbo.Categories.类名GO根基题〔简单，必得分题目〕【例1】在表“Products〞中增加一字段，字段名为“每件体积〞，数据类型为“nvarchar〞,20，不可以为空,其默认值为“0x0x0”做法：在题目中的表上右击“修改〞，在翻开的编辑界面下输入字段，并设类型及长度，在下面的属性栏里找到“常规〞的“默认值或绑定〞,输入题中的默认值，然后保存表。【例2】翻开表“Shippers〞，将“服务〞字段的内容由“〔503〕……〞修改为“〔8621〕……〞。做法：翻开题目中的表，即在表上右击“翻开表〞，找到对应字段下面的内容进展修改。或者在新建查询里输入如下脚本：usesalesgoUPDATEShippersSET服务='(8621)'+right(服务,8)whereleft(服务,5)='(503)'【例3】将素材目录下的Access数据库“FPNWIND.MDB〞中的表“运货商〞导入到数据库sales中。要求：将原表的“〞字段名改为“服务〞。做法：在sales数据库上右击“导入数据〞，然后按向导的方法完成各个步骤的操作，注意此题数据源在导入时的文件类型为“MicrosoftAccess〞〔如果数据源是Excel数据文件则要选择“MicrosoftExcel〞；如果数据源是文本文件则要选择“平面文件源〞〕，在倒数第四步里别忘了在“表和视图〞表格的“编辑〞按钮上点击进去后修改目标字段的名称〔有时候还要修改目标字段的类型〕。操作的局部图如下所示：点击“编辑〞后在窗口中将“〞字段名改为“服务〞如以下列图所示：【例4】为表“EmployeeTerritories〞建设一个参照完整性(既外键),要求表“EmployeeTerritories〞的所有记录的“雇员ID〞字段的值，在表“employees〞中已经存在，并以“k1〞为名保存。做法：要先检查主键表“Employees〞中的“雇员ID〞字段是否已经为主键，假设没有设好是不能设置外键的；之后进入表“EmployeeTerritories〞的设计窗口点击“关系〞，然后按要求添加新的关系。局部图如下所示：【例5】为表“orders〞建设一个参照约束〔check约束〕，要求表“orders〞的字段“运输日期〞必须比“要货日期〞早5天，且以“y1〞为名保存(不要检查已有的数据)。 做法：进入表“Orders〞的设计窗口点击“check约束〞，添加一个新的check约束，内容为：Datediff(day,运输日期,要货日期)>=5。并将“检查现有的数据〞前的勾去掉。【例5_2】为表“Employees〞建设一个参照约束〔check约束〕，要求表“Employees〞的字段“雇佣日期〞必须比“生日〞晚18年，且以“y1〞为名保存(不要检查已有的数据)。 做法：对Employees右击，选择修改；在设计窗口点击“check约束〞，添加一个新的check约束如以下列图所示：〔题五也是同样的图示〕点击表达式：输入如以下列图中内容，再点击最后一个选项，选择“否〞，最后修改名称并保存。【例6】建设一个角色OrderOP，要求此角色能够胜任根据客户的要求，输入新的订单信息，且对所输入的信息进展修改的工作所需要的相应权限,要求只赋予必要的最小权限〔相关表有：“Orders〞、“OrderDetails〞、“Products〞、“Customers〞和“Shippers〞〕。做法：在sales下的安全性文件夹上右击新建数据库角色，输入角色名称OrderOP，确定完成。然后分别进入Orders和OrderDetails表的属性界面，修改权限界面，添加角色及相应的insert、select、delete和update权利；再分别进入Products、Customers和Shippers表以同样的方法设置select权限。注意：此次此题应该会考用户设置，具体做法是按题目要求先在具体数据库外面的“安全性“文件夹里创立一个登录名，然后进入sales数据库的“安全性〞里的“用户〞上右击新建用户，填写用户名并选择已建的登录名，在该界面的左边选择“安全对象〞页面，点击“添加〞按钮选第二类，出现了全部表以后，在选相应的表及权限。设置完成即可。局部表如以下列图所示：【例7】对表“Customers〞建设一个全文索引sb1，然后在表“Customers〞中查找所有包含“SalesAgent〞的记录，并将查找结果以表syjg保存在数据库sales中。做法：先在Customers表上设置主键，然后在sales数据库上右键单击“属性〞，在文件页面上勾选“使用全文索引〞，最后在Customers表上右键单击“全文索引〞，按向导方法完成，注意在“创立新目录〞下面的目录名中输入sb1。完成全文索引之后在新建查询界面输入如下代码：usesalesgoselect*intosyjgfromdbo.Customerswherecontains(*,'"SalesAgent"')此处字体应该为黑色，可以打勾此处字体应该为黑色，可以打勾存储过程类〔必得分题目〕【例1】编写一个存储过程cc1，要求为所有英国商品〔即表“Suppliers〞的“国家〞为“UK〞的供给商提供的商品〕，在原价格〔指“单价〞〕的根基上提价5%。做法：在sales数据库下面的“可编程性〞文件夹的“存储过程“文件夹上右击新建存储过程，对应的代码如下：CREATEPROCEDUREcc1 ASBEGINupdatedbo.Productsset单价=单价*(1+0.05)FROMdbo.SuppliersINNERJOINdbo.ProductsONdbo.Suppliers.供货商ID=dbo.Products.供货商IDWHERE(dbo.Suppliers.国家=N'UK')END【例2】编写一个存储过程cc2，要求为“类名〞为“Seafood〞的所有Products在原价格〔指“单价〞〕的根基上提价10%。做法：在sales数据库下面的“可编程性〞文件夹的“存储过程“文件夹上右击新建存储过程，对应的代码如下：CREATEPROCEDUREcc2ASBEGINupdatedbo.Productsset单价=单价*(1+0.1)FROMdbo.CategoriesINNERJOINdbo.ProductsONdbo.Categories.类ID=dbo.Products.类IDWHERE(dbo.Categories.类名=N'Seafood')END触发器类【例1】建设删除触发器，要求当表“Employees〞的记录被删除后，表“EmployeeTerritories〞中相应的记录也能自动删除,并以“cfq1〞为名保存。做法：在表“Employees〞下面的触发器文件夹上右击新建触发器，修改其中的代码如下：CREATETRIGGERc1ONdbo.EmployeesAFTERDELETEASBEGINdeletefromdbo.EmployeeTerritorieswhere雇员IDin(select雇员IDfromdeleted)ENDGO【例2】建设修改触发器，要求表“OrderDetails〞中的记录修改后，相应产品在表“Products〞的字段“库存量〞和“预订量〞的数字也要做相应的修改〔“库存量〞要减去修改后的“数量〞并加上修改前的“数量〞，“预留量〞要加上修改后的“数量〞减去修改前的“数量〞〕，并以“cfq2”做法：在查询分析中输入或在表“OrderDetails〞的“触发器〞上鼠标右击“新建触发器〞，然后在窗口中输入：createtriggercfq2onOrderDetailsforUpdateasbeginupdateproductsset库存量=库存量+数量,预订量=预订量-数量fromdeletedINNERJOINProductsONdeleted.产品ID=Products.产品IDupdateproductsset库存量=库存量-数量,预订量=预订量+数量frominsertedINNERJOINProductsONinserted.产品ID=Products.产品IDend【例3】建设添加触发器，要求表“OrderDetails〞中的记录增加后，相应产品在表“Products〞的字段“库存量〞和“预订量〞的数字也要做相应的修改〔“库存量〞要减去新增记录的“数量〞，“预订量〞要加上新增记录的“数量〞〕，并以“cfq2”为名保存。做法：在查询分析中输入或在表“OrderDetails〞的“触发器〞上鼠标右击“新建触发器〞，然后在窗口中输入：Createtriggercfq2onorderdetailsforInsertasDeclare@numintSelect@num=(select数量frominserted)Updateproductsset库存量=库存量-@num,预订量=预订量+@numwhere产品ID=(select产品IDfrominserted)【例4】建设删除触发器，要求当表“Employees〞的记录被删除后，表“EmployeeTerritories〞中相应的记录也能自动删除,并以“cfq1”做法：在查询分析中输入或在表“Employees〞的“触发器〞上鼠标右击“新建触发器〞，然后在窗口中输入：createtriggercfq1onEmployeesfordeleteasbegindeletefromEmployeeTerritorieswhere雇员ID=(select雇员IDfromdeleted)end【例5】建设删除触发器，要求当表“Products〞的记录被删除后，要对表“OrderDetails〞中相应的记录进展检查，要求：如果被删除的产品在表“OrderDetails〞中已经有记录了，则该产品不能删除〔要恢复〕，要将表“Products〞中的“状态〞字段设为“0”；如果被删除的产品在表“OrderDetails〞中没有记录,则可以删除，并以“cfq1做法：在查询分析中输入或在表“Products〞的“触发器〞上鼠标右击“新建触发器〞，然后在窗口中输入：createtriggercfq1onproductsfordelete

as

ifexists(select*fromdeletedwhere产品IDin(select产品IDfromorderdetails))

begin

declare@IDint

select@ID=产品IDfromdeleted

rollbacktransaction

updateproductsset状态='0'where产品ID=@IDend游标类【例1】翻开查询分析器，首先声明一个游标syb,要求返回表“Products〞中“产品名〞为“Geitost〞的供给商，且该游标允许前后滚动和修改；然后翻开该游标；第三步对该游标所指定的记录进展修改，将其中“每件数量〞由“500g〞改为“454g〞；然后关闭该游标并删除该游标。该过程在查询分析器中调试成功后，将上述程序过程以“cc2〞为名，做为存储过程保存在数据库sales中。做法：在新建查询中输入以下代码并运行：UsesalesgocreateProcedurecc2asdeclaresybcursordynamicforselect*fromSuppliers,productswhereSuppliers.供货商ID=products.供货商IDandProducts.产品名='Geitost'andproducts.每件数量=’500g'opensybFETCHNEXTFROMsybwhile@@fetch_status=0beginUPDATEproductsSET每件数量='454g'WHERECURRENTOFsybfetchnextfromsybendclosesybdeallocatesybgo【例2】翻开查询分析器，首先声明一个游标syb,要求返回表“Products〞中“产品名〞为“Geitost〞的供给商，且该游标允许前后滚动和修改；然后翻开该游标；第三步对该游标所指定的记录进展修改，将其中“每件数量〞由“500g〞改为“454g〞；然后关闭该游标并删除该游标。该过程在查询分析器中调试成功后，将上述程序过程以“cc2”为名，做为存储过程保存在数据库sales中。做法：在新建查询中输入以下代码并运行：UsesalesgocreateProcedurecc2asdeclaresybcursordynamicforSELECT*FROMProductsWHERE产品名='Geitost'and每件数量='500g'OpensybFetchnextfromsybwhile@@fetch_status=0beginUpdateproductsset每件数量='454g'WHERECURRENTOFsybFetchnextfromsybendClosesybDeallocatesyb【例3】翻开查询分析器，首先声明一个游标syb,要求返回表“Suppliers〞中“联系人职务〞为“SalesManager〞的供给商，且该游标允许前后滚动和修改；然后翻开该游标；第三步对该游标所指定的记录进展修改，将其中国家为“Denmark〞记录的“〞由原来的“2800〞改为“DK2800〞；然后关闭该游标并删除该游标。该过程在查询分析器中调试成功后，将上述程序过程以“cc2〞为名，做为存储过程保存在数据库sales中。做法：在新建查询中输入以下代码并运行：UsesalesgocreateProcedurecc2asdeclaresybcursordynamicforSELECT*fromSuppliersWHERE国家='Denmark'AND联系人职务='SalesManager'AND='2800'opensybFETCHNEXTFROMsybwhile@@fetch_status=0beginUPDATESuppliersSET='DK2800'WHERECURRENTOFsybfetchnextfromsybendclosesybdeallocatesybgo用户函数类【例1】建设一个用户函数TotalSales，要求以员工的姓名和年份为实参，返回该员工该年的累计销售总金额〔注意以“运输日期〞为准〕。做法：在新建查询中输入以下代码并运行：CREATEFUNCTIONTotalSales(@xmvarchar(30),@yearint)RETURNSmoneyASBEGINdeclare@zjemoneyset@zje=(SELECTSUM((dbo.OrderDetails.单价*dbo.OrderDetails.数量)*(1-dbo.OrderDetails.折扣))AS销售金额FROMdbo.EmployeesINNERJOINdbo.OrdersONdbo.Employees.雇员ID=dbo.Orders.雇员IDINNERJOINdbo.OrderDetailsONdbo.Orders.订单ID=dbo.OrderDetails.订单IDWHEREyear(dbo.Orders.运输日期)=@yearAND(dbo.Employees.姓+dbo.Employees.名=@xm))RETURN@zjeENDGO【例2】建设一个用户函数TotalSales，要求以供给商名为实参，返回该供给商的库存产品总金额〔注意总金额为“库存量〞*“单价〞的总和〕。在sales数据库里的“可编程性〞文件夹下的“函数“文件夹上右击新建标量值函数，修改模板，或者直接在查询界面输入如下代码，执行成功即可：CREATEFUNCTIONTotalSales(@gsmvarchar(255))

RETURNSmoneyAS

BEGIN

declare@zjemoney

set@zje=(selectSUM(dbo.Products.单价*dbo.Products.库存量)AS库存产品总金额FROMdbo.SuppliersINNERJOINdbo.ProductsONdbo.Suppliers.供货商ID=dbo.Products.供货商IDGROUPBYdbo.Suppliers.公司名HAVING(dbo.Suppliers.公司名=@gsm)

return@zje

ENDSql2005概念题：(考试时应该是每题4个选项的单项选择题)数据库管理系统是管理数据库的系统，它按一定的数据模型组织数据，下面的〔I，J〕功能不是它提供的。数据定义功能数据操纵功能数据的完整性检查功能数据库的安全保护功能数据库系统的故障恢复功能在网络环境下访问数据库的功能数据库的并发控制功能方便、有效地存取数据库信息的接口和工具数据的防毒、杀毒功能*计算机系统的故障恢复功能*以下〔J、K、L〕不是主流的关系型数据库管理系统OracleSQLServerDB2SybaseInformixIngersMySQLAccessFoxProWindows*Excel*IIS*下面〔D、E〕不是数据库管理系统采用的数据模型层次模型网状模型关系模型幻灯片模型*多对多模型*数据库的逻辑构造设计包括：〔A、B〕表的设计*表字段的设计*触发器的设计存储过程的设计数据的存储构造的设计数据库物理文件、索引文件的组织设计文件存取路径的设计客户端应用程序或应用服务器向数据库服务器请求服务时，必须首先和数据库建设连接。下面〔F、G〕不是数据库接口：ODBCOLEDBADOADO.NETJDBCBtoB*BtoC*数据库连接池SQLServer2005企业版可以安装在〔D、E、F、G、I、J〕操作系统上。Windows2000ServerSP2Windows2000ServerSP3Windows2000ProfessionalEditionSP4Windows2000ServerSP4*Windows2003ServerSP1*Windows2003EnterpriseEditionSP1*Windows2003DatacenterEditionSP1*Windows2003WebEditionSP1Windows2000AdvancedServerSP4*Windows2000DatacenterServerSP4*WindowsXPHomeEditionSP2WindowsXPProfessionalEditionSP2WindowsXPMediaEditionSP2SQLServer2005开发版不可以安装在〔H〕操作系统上。Windows2000ServerSP2Windows2000ServerSP3Windows2000ProfessionalEditionSP4Windows2000ServerSP4Windows2003ServerSP1Windows2003EnterpriseEditionSP1Windows2003DatacenterEditionSP1Windows2003WebEditionSP1*Windows2000AdvancedServerSP4Windows2000DatacenterServerSP4WindowsXPHomeEditionSP2WindowsXPProfessionalEditionSP2WindowsXPMediaEditionSP2SQLServer2005标准版不可以安装在〔H、K〕操作系统上。Windows2000ServerSP2Windows2000ServerSP3Windows2000ProfessionalEditionSP4Windows2000ServerSP4Windows2003ServerSP1Windows2003EnterpriseEditionSP1Windows2003DatacenterEditionSP1Windows2003WebEditionSP1*Windows2000AdvancedServerSP4Windows2000DatacenterServerSP4WindowsXPHomeEditionSP2*WindowsXPProfessionalEditionSP2WindowsXPMediaEditionSP2下面〔F、G〕不属于SQLServer2005的服务器组件。数据库引擎〔DatabaseEngine〕报表服务〔ReportingServices〕分析服务〔AnalysisServices〕集成服务〔IntegrationServices〕通知服务〔NotificationServices〕网络服务〔WebServices〕*客户服务〔CustomServices〕*完全限定名包括〔A〕服务器名、数据库名、数据库架构名、对象名*服务器名、数据库名、所有者名、对象名服务器名、数据库名、数据库管理员名、对象名服务器名、数据库连接名、数据库架构名、对象名服务器连接名、数据库名、数据库架构名、对象名数据库对象不包括〔M、N、O〕表视图索引约束存储过程触发器默认值用户角色规则类型函数关系*架构*服务器*下面〔E、F〕不是系统数据库mastermodelmsdbtempdbNorthwind*Sales*对已存在的数据库不能改变的〔A、B〕数据文件名*日志文件名