数据库初级复习汇总

1、SQL SERVER 2005 操作题和选择题复习汇总松江分校 王芳 2012 年 6 月一、视图类（考题中的第 2 小题，必须得分）【例1 】 新建视图 SalesV ，显示当前每个供应商的库存货物所占用资金的总和。要求视图显示 的字段为 “公司名 ”、“库存资金 ”，其中 “库存资金 ”为该供应商所供应的产品的“库存量”* “单价”的总和。在视图里新建， 添加两个表 Suppliers 和 Products ，按题目意思设置好之后保存，对应的代 码如下：CREATE VIEW dbo.SalesVASSELECT dbo.Suppliers. 公司名 , SUM（dbo.Products.

2、 单价 * dbo.Products. 库存量 ） AS 库存资金 FROM dbo.Suppliers INNER JOIN dbo.Products ON dbo.Suppliers.供货商 ID =dbo.Products. 供货商 IDGROUP BY dbo.Suppliers.公司名【例 2 】 新建视图 ntv ，显示 1997 年的分类销售情况汇总（ Category Sales for 1997）。要求视图显示的字段为 “类名” 、“销售金额” 。注意：需要用到的基表为： “ Orders ”、“ OrderDetails ”、 “ Products ”和“ Categori

3、es ”。在视图上右键新建视图，添加上面的四个表，做好后保存为 ntv ，名字不能错。其对应的代 码为：CREATE VIEW dbo.ntvASSELECT dbo.Categories. 类名,SUM(dbo.OrderDetails. 单价 * dbo.OrderDetails. 数量)* (1 - dbo.OrderDetails. 折扣)AS 销售金额FROM dbo.Categories INNER JOINdbo.Products ON dbo.Categories.类 ID =dbo.Products.类ID INNER JOINdbo.OrderDetails ON dbo.

4、Products.产品 ID =dbo.OrderDetails. 产品 ID INNER JOINdbo.Orders ON dbo.OrderDetails.订单 ID =dbo.Orders. 订单 ID WHERE (dbo.Orders.运输日期 BETWEEN CONVERT(DATETIME, '1997-01-01 00:00:00', 102) AND CONVERT(DATETIME, '1997-12-31 00:00:00', 102)GROUP BY dbo.Categories. 类名GO【例3】新建视图SalesV，显示1997年8

5、月要付给每个承运商的运费汇总。要求视图显示的字段为 承运商名”、运费汇总"，其中 运费汇总"为该承运商所承运定单的运费的总和。在视图里新建，添加两个表 Shippers和Orders，按题目意思设置好之后保存，然后可以查看SalesV的属性，代码如下:ISlkl WCF3F母:运商也7毎:运商名 服落电话r?=舉曲-dbo. ¥i 癇要列别名1表1输出乍类型1排序J砖另组依底上J承运商名ShippersF7Grotip By运夷汇总:Orders匝5um运辎曰朝Orders| LWherewJJJJSELECT cdb口.Shippers.f 5UM(db口.Or

6、fieri；云价)AS FROMdbo.Shippers INNEP. JOINdbo. Orders Gfrl dbo. Shippers,承话Ttt ID = dbo. Orders,_WHERE (dbdsOrdersBETWEEN 匚ONVERTfDATETIME, fclW7-OB-Dl OOsOOsO 102) ANEi CONVERT(DATEjTIME., 4 ,承运商名运费汇总VFederal Shipping;3426.50005peedy Expr曰ft?509.9400United P曰匚kge1SS5.0200CREATE VIEW dbo.SalesVASSELEC

7、T dbo.Shippers. 承运商名 , SUM(dbo.Orders. 运价 ) AS 运费汇总 FROM dbo.Orders INNER JOIN dbo.Shippers ON dbo.Orders.承运商 ID = dbo.Shippers. 承运商ID WHERE (dbo.Orders. 运输日期 BETWEEN CONVERT(DATETIME, '1997-08-01 00:00:00', 102) AND CONVERT(DATETIME, '1997-08-31 00:00:00', 102)GROUP BY dbo.Shippers.

8、 承运商名【例 4 】新建视图 SalesV2 ，显示当前每个大类的库存货物所占用资金的总和。要求视图显示的字段为“类名”、“库存资金 ”，其中“库存资金 ”为该大类的产品的“库存量” *“单价”的总和。 在视图里新建，添加两个表 Categories 和 Products ，按题目意思设置好之后保存，然后可以查 看 SalesV2 的属性，代码如下：CREATE VIEW dbo.SalesV2ASSELECT dbo.Categories. 类名 , SUM(dbo.Products. 单价 * dbo.Products. 库存量 ) AS 库存 资金 FROM dbo.Categorie

9、s INNER JOINdbo.Products ON dbo.Categories.类 ID =dbo.Products. 类 IDGROUP BY dbo.Categories. 类名GO二、基础题(简单，必得分题目)【例 1 】在表 “Products ”中增加一字段， 字段名为 “每件体积 ”，数据类型为 “nvarchar ”,20 ，不可 以为空 ,其默认值为 “0x0x0 ”。做法：在题目中的表上右击 “修改” ，在打开的编辑界面下输入字段， 并设类型及长度， 在下面的属性栏里找到“常规”的“默认值或绑定” ,输入题中的默认值，然后保存表。FJ皺据类型int.-JR允许仝nv-a

10、rchar(-4O)int类I。单罚RrtcbrcirirLcvel DlscontlnuBd 毎件悴积int nvarchar(ZO) mrioney s-rThallint srrullirbt 临 nrrliinfr bit nvarchar(ZO)列属性I图ii". "IEl帕(SW)母件侍税七浪30黔iA值或如疋癱据英型rwrckr【例2】打开表Shippers ”，将“服务电话”字段的内容由“(503 )”修改为“(8621 )”。做法：打开题目中的表，即在表上右击“打开表”，找到对应字段下面的内容进行修改。或者在新建查询里输入如下脚本：use salesgoU

11、PDATE Shippers SET服务电话='(8621)'+right(服务电话,8)where left(服务电话,5)='(503)'【例3】将素材目录下的 Access数据库“FPNWIND.MDB ”中的表“运货商”导入到数据库sales 中。要求：将原表的“电话”字段名改为“服务电话”。做法：在sales数据库上右击“导入数据”，然后按向导的方法完成各个步骤的操作，注意本题数据源在导入时的文件类型为"Microsoft Access"(如果数据源是Excel数据文件则要选择"Microsoft Excel ”；如果数据

12、源是文本文件则要选择"平面文件源”)， 在倒数第四步里别忘了在“表和视图”表格的“编辑”按钮上点击进去后修改目标字段的名称(有时候还要修改目标字段的类型)。操作的部分图如下所示：IJ亦注级斑库CH5 .亲斤洼走询匸4長宥救雄暉爾丰为0、住茅<r>-t-二走G合血?| fiflj 除 a主倉卿主（X.导入缈埒（x»导出斂罐CXA合:诺U裘兌：1S崖 CCJ . SQL SerTflr导人和导出宙导选择源表和源视图选择一牛或窖牛要复带蝴表和视图表和视EKD；ft W拊目标运扯甬=41<=. dbo.运货商疔创逢目艇（帥厂址:除井玄朋创理目标盂Ur出险自标盂中的

13、行（町目标裘型可为空值犬小HR话货府IP运釜庖IDiatr螢司弟称赴司君称n?ar clhajp40电话服务电话”nvtr chtrp24r向=1折走中追加行:匕r启用抹识爺扎0）映射:【例4】为表 EmployeeTerritories"建立一个参照完整性 （既外键），要求表EmployeeTerritories"的所有记录的 雇员ID"字段的值，在表 employees "中已经存在，并以k1 ”为名保存。做法：要先检查主键表Employees ”中的雇员id ”字段是否已经为主键，若没有设好是不能设置外键的；之后进入表EmployeeTerrito

14、ries”的设计窗口点击"关系”，然后按要求添加新的关系。部分图如下所示:1【-鄭即顺'；霸©Z_【例5】为表Orders "建立一个参照约束（check约束），要求表 Orders "的字段 运输日期"必 须比 要货日期”早5天，且以y1 ”为名保存（不要检查已有的数据）。做法：进入表 Orders "的设计窗口点击“ check约束"，添加一个新的 check约束，内容为：Datediff （day, 运输日期，要货日期）=5。并将“检查现有的数据”前的勾去 掉。【例5_2】为表Employees "建

15、立一个参照约束（check约束），要求表 Employees "的字段 雇 佣日期”必须比 生日”晚18年，且以V1”为名保存（不要检查已有的数据）。做法：对Employees右击，选择修改；在设计窗口点击“ check约束"，添加一个新的check约束如下图所示：（题五也是同样的图示）racr砸同 M)-日却慎黑UIKERl *1 1TC<A虽*!用干燮幅4賈 HilEJ |点击表达式：输入如下图中内容,再点击最后一个选项，选择“否”，最后修改名称并保存。【例6】建立一个角色OrderOP，要求此角色能够胜任根据客户的要求，输入新的订单信息，且对所输入的信息进行修改

16、的工作所需要的相应权限，要求只赋予必要的最小权限（相关表有：“ Orders "、“ OrderDetails "、“ Products "、“ Customers ”和“ Shippers ”）。做法：在sales下的安全性文件夹上右击新建数据库角色，输入角色名称OrderOP ，确定完成。然后分别进入 Orders和OrderDetails表的属性界面，修改权限界面，添加角色及相应的 insert、select、delete 和 update 权利；再分别进入 Products 、Customers 和 Shippers表以同样的方法设置 select权限。注

17、意：此次本题应该会考用户设置，具体做法是按题目要求先在具体数据库外面的“安全性“文件夹里创建一个登录名，然后进入sales数据库的“安全性”里的“用户”上右击新建用户，填写用户名并选择已建的登录名，在该界面的左边选择“安全对象”页面，点击“添加”按钮选第二类，出现了全部表以后，在选相应的表及权限。设置完成即可。部分表如下图所示:2辺汕3ft丄数番库关系團 国N表+ u可编程性* 存储-u安全性(±二用户 日立角色+ _jlZ 国匸应用 studentI i卄口 x士 sa 上？妥全如Or-ia-rllBt &idboCrimdbnFr-a ihiclidboShi H>

18、*rx百bo藏脊库:fl巴容称3dba Cir drDitB&lx 的式帜限 t£）：松盟.磁若按于眞石按于或題拒址但驾器环回1LC3Jl-T>OLJMiyiTistrtdbo回|.區按»EdbaEClllTADLIMIM'.Adrijai g.Lrt.Lac*Stltctdbo回ri LUpd“dbc;库加.U隣 ij：i瓢顾咱【例7】对表“Customers ”建立一个全文索引sb1，然后在表 “ Customers ”中查找所有 包含“ Sales Age nt ”的记录，并将查找结果以表 syjg保存在数据库sales中。做法：先在Custom

19、ers表上设置主键，然后在 sales数据库上右键单击"属性"，在文件页 面上勾选"使用全文索引”，最后在 Customers表上右键单击"全文索引”，按向导方法 完成，注意在"创建新目录”下面的目录名中输入sbl。完成全文索引之后在新建查询界面输入如下代码：use sales goselect * i nto syjg from dbo.Customers where con tai ns(*,"'Sales Agen t"')三、存储过程类（必得分题目）【例1】编写一个存储过程cc1，要求为所有英国商品（

20、即表"Suppliers ”的"国家”为“UK”的供应商提供的商品），在原价格（指“单价”）的基础上提价5%。做法：在sales数据库下面的“可编程性”文件夹的“存储过程“文件夹上右击新建存储过程，对应的代码如下：CREATE PROCEDURE cc1 ASBEGINupdate dbo.Products set 单价=单价 *（1+0.05） FROM dbo.Suppliers INNER JOINdbo.Products ON dbo.Suppliers.供货商 ID = dbo.Products.供货商 IDWHERE (dbo.Suppliers. 国家 = N

21、'UK')END【例2 】编写一个存储过程 cc2 ，要求为“类名”为“ Seafood ”的所有 Products 在原价格(指“单价”)的基础上提价 10% 。做法：在 sales 数据库下面的“可编程性”文件夹的“存储过程“文件夹上右击新建存储过 程，对应的代码如下：CREATE PROCEDURE cc2 ASBEGINupdate dbo.Products set单价 =单价 *(1+0.1) FROMdbo.Categories INNER JOINdbo.Products ON dbo.Categories.类 ID = dbo.Products.类IDWHERE

22、 (dbo.Categories. 类名= N'Seafood')END四、触发器类例1 】建立删除触发器，要求当表Employees ”的记录被删除后，表 “EmployeeTerritories”中相应的记录也能自动删除,并以cfq1 ”为名保存。做法：在表 “Employees”下面的触发器文件夹上右击新建触发器，修改其中的代码如下：CREATE TRIGGER c1 ONdbo.EmployeesAFTER DELETE ASBEGINdelete from dbo.EmployeeTerritories where雇员 ID in(select雇员 ID from d

23、eleted)ENDGO【例 2 】建立修改触发器，要求表 “OrderDetails ”中的记录修改后，相应产品在表 “Products ”的 字段 “库存量 ”和“预订量 ”的数字也要做相应的修改（ “库存量 ”要减去修改后的 “数量”并加上修改 前的“数量”， “预留量”要加上修改后的 “数量”减去修改前的“数量”），并以 “cfq2 ”为名保存。 做法：在查询分析中输入或在表 “OrderDetails ”的“触发器” 上鼠标右击“新建触发器”， 然后在窗口中输入：create trigger cfq2 on OrderDetails for Updateasbeginupdate p

24、roducts set 库存量 = 库存量 + 数量 ,预订量 = 预订量 - 数量 from deletedINNER JOIN Products ON deleted. 产品 ID = Products. 产品 IDupdate products set库存量 =库存量 -数量,预订量=预订量 +数量 from inserted INNERJOIN Products ON inserted.产品 ID= Products. 产品 IDend【例 3 】建立添加触发器，要求表 “OrderDetails ”中的记录增加后，相应产品在表“Products ”的字段“库存量 ”和“预订量 ”的数

25、字也要做相应的修改 （“库存量 ”要减去新增记录的 “数量”，“预订量 ” 要加上新增记录的 “数量 ”），并以 “cfq2 ”为名保存。做法：在查询分析中输入或在表 “OrderDetails ”的“触发器” 上鼠标右击“新建触发器”， 然后在窗口中输入：Create trigger cfq2on orderdetails for InsertasDeclare num intSelect num=(select 数量 from inserted)Update products set 库存量 = 库存量 -num, 预订量 = 预订量 +numwhere 产品 ID=(select 产品

26、ID from inserted)【例 4 】建立删除触发器，要求当表 “Employees ”的记录被删除后，表 “EmployeeTerritories 中相应的记录也能自动删除 ,并以 “cfq1 ”为名保存。做法：在查询分析中输入或在表“ Employees ”的“触发器”上鼠标右击“新建触发器”， 然后在窗口中输入：create trigger cfq1 on Employeesfor deleteasbegindelete from EmployeeTerritorieswhere 雇员 ID =(select 雇员 ID from deleted)end【例 5】建立删除触发器，

27、要求当表 “Products ”的记录被删除后，要对表 “OrderDetails ”中相应 的记录进行检查， 要求： 如果被删除的产品在表 “OrderDetails ”中已经有记录了，则该产品不能 删除(要恢复)，要将表 “Products ”中的“状态”字段设为“ 0 ”；如果被删除的产品在表 “OrderDetails ”中没有记录 , 则可以删除，并以 “cfq1 ”为名保存。做法： 在查询分析中输入或在表“ Products ”的“触发器”上鼠标右击“新建触发器”，然 后在窗口中输入：create trigger cfq1 on products for delete as产品 I

28、D in (select 产品 ID from orderdetails)if exists(select * from deleted wherebegindeclare ID intselect ID = 产品 ID from deletedrollback transaction update products set 状态 ='0' where 产品 ID = ID end五、游标类【例 1 】打开查询分析器，首先声明一个游标syb, 要求返回表 “Products ”中 “产品名 ”为“Geitost ”的供应商，且该游标允许前后滚动和修改；然后打开该游标；第三步对该

29、游标所 指定的记录进行修改，将其中“每件数量”由“ 500g ”改为“ 454g ”；然后关闭该游标并 删除该游标。该过程在查询分析器中调试成功后，将上述程序过程以“cc2 ”为名，做为存储过程保存在数据库 sales 中。做法：在新建查询中输入以下代码并运行：Use salesgocreate Procedure cc2 asdeclare syb cursor dynamic for select * from Suppliers,products where Suppliers. 供货商 ID=products. 供货商 ID andProducts. 产品名 ='Geitost

30、' and products.每件数量 = ' 500 g'open sybFETCH NEXT FROM sybwhile fetch_status=0beginUPDATE products SET 每件数量 ='454g' WHERE CURRENT OF sybfetch next from sybendclose sybdeallocate sybgo【例2】打开查询分析器，首先声明一个游标syb,要求返回表 Products "中 产品名"为“Geitost ”的供应商，且该游标允许前后滚动和修改；然后打开该游标；第三步对

31、该游标所 指定的记录进行修改，将其中“每件数量”由“ 500g ”改为“ 454g ”；然后关闭该游标并 删除该游标。该过程在查询分析器中调试成功后，将上述程序过程以“cc2 ”为名，做为存储过程保存在数据库 sales 中。做法：在新建查询中输入以下代码并运行：Use salesgocreate Procedure cc2 asdeclare syb cursor dynamicforSELECT * FROM Products WHERE 产品名 ='Geitost' and每件数量 ='500 g'Open sybFetch next from sybwh

32、ile fetch_status=0beginUpdate products set 每件数量 ='454 g' WHERE CURRENT OF sybFetch next from sybendClose sybDeallocate syb【例 3 】打开查询分析器，首先声明一个游标syb, 要求返回表 “Suppliers ”中“联系人职务 ”为“Sales Manager ”的供应商，且该游标允许前后滚动和修改；然后打开该游标；第三步对该游标 所指定的记录进行修改，将其中国家为“ Denmark ”记录的 “邮编 ”由原来的 “2800 ”改为 “DK2800 ”；然后

33、关闭该游标并删除该游标。该过程在查询分析器中调试成功后，将上述程序过 程以 “cc2 ”为名，做为存储过程保存在数据库sales 中。做法：在新建查询中输入以下代码并运行：Use salesgocreate Procedure cc2 asdeclare syb cursor dynamicforSELECT * from Suppliers WHERE国家 = 'Denmark' AND 联系人职务 = 'SalesManager' AND 邮编 = '2800'open sybFETCH NEXT FROM sybwhile fetch_st

34、atus=0beginUPDATE Suppliers SET 邮编 = 'DK2800' WHERE CURRENT OF sybfetch next from sybendclose sybdeallocate sybgo六、用户函数类【例 1 】建立一个用户函数 TotalSales ，要求以员工的姓名和年份为实参， 返回该员工该年的累 计销售总金额(注意以“运输日期”为准)。做法：在新建查询中输入以下代码并运行：CREATE FUNCTION TotalSales (xm varchar(30),year int)RETURNS money ASBEGINdeclare

35、 zje moneyset zje=(SELECT SUM(dbo.OrderDetails. 单价 * dbo.OrderDetails. 数量 ) * (1 - dbo.OrderDetails. 折扣 ) AS 销售金额 FROM dbo.Employees INNER JOIN dbo.Orders ON dbo.Employees. 雇员 ID = dbo.Orders. 雇员 ID INNER JOIN dbo.OrderDetails ON dbo.Orders. 订单 ID = dbo.OrderDetails. 订单 IDWHERE year(dbo.Orders. 运输日期

36、 ) = year AND (dbo.Employees.姓 + dbo.Employees.名 = xm)RETURN zjeENDGO【例 2 】建立一个用户函数 TotalSales ，要求以供应商名为实参，返回该供应商的库存产品总金 额(注意总金额为“库存量”*“单价”的总和)。在sales数据库里的“可编程性”文件夹下的“函数“文件夹上右击新建标量值函数，修改模板，或者直接在查询界面输入如下代码，执行成功即可：CREATE FUNCTION TotalSales (gsm varchar(255)RETURNS money ASBEGINdeclare zje moneyset zj

37、e=(select SUM(dbo.Products. 单价 * dbo.Products. 库存量 ) AS 库存产品总金额FROM dbo.Suppliers INNER JOIN dbo.Products ON dbo.Suppliers.供货商 ID =dbo.Products. 供货商 IDGROUP BY dbo.Suppliers. 公司名HAVING (dbo.Suppliers. 公司名 =gsm )return zjeENDSql2005 概念题： (考试时应该是每题 4 个选项的单选题 )1. 数据库管理系统是管理数据库的系统， 它按一定的数据模型组织数据， 下面的( I

38、，J ) 功能不是它提供的。A ) 数据定义功能B) 数据操纵功能C) 数据的完整性检查功能D ) 数据库的安全保护功能E) 数据库系统的故障恢复功能F) 在网络环境下访问数据库的功能G) 数据库的并发控制功能H ) 方便、有效地存取数据库信息的接口和工具I )数据的防毒、杀毒功能*J)计算机系统的故障恢复功能*2. 以下( J、K、 L )不是主流的关系型数据库管理系统A ) OracleB ) SQL ServerC)DB2D)SybaseE)InformixF)IngersG)MySQLH)AccessI)FoxProJ)Windows *K)Excel *L)IIS *下面(D、 E

39、)不是数据库管理系统采用的数据模型A)层次模型B)网状模型C)关系模型D)幻灯片模型 *E)多对多模型 *3.4. 数据库的逻辑结构设计包括： (A 、 B )A)表的设计*B)表字段的设计*C)触发器的设计D)存储过程的设计E)数据的存储结构的设计F)数据库物理文件、索引文件的组织设计G) 文件存取路径的设计5. 客户端应用程序或应用服务器向数据库服务器请求服务时，必须首先和数据库建立连接。下面( F、 G )不是数据库接口：A)ODBCB)OLE DBC)ADOD)ADO.NETE)JDBCF)B to B*G)B to C*H)数据库连接池6. SQL Server 2005 企业版可以

40、安装在(D 、 E、F、G、 I、 J )操作系统上。A)Windows 2000 Server SP2B)Windows 2000 Server SP3C)Windows 2000 Professional Edition SP4D)Windows 2000 Server SP4*E)Windows 2003 Server SP1*F)Windows 2003 Enterprise Edition SP1*G)Windows 2003 Datacenter Edition SP1*H)Windows 2003 Web Edition SP1I)Windows 2000 Advanced Se

41、rver SP4*J)Windows 2000 Datacenter Server SP4*K)L)Windows XP Home Edition SP2Windows XP Professional Edition SP2M)Windows XP Media Edition SP27. SQL Server 2005 开发版不可以安装在( H )操作系统上。A)Windows 2000 Server SP2B)Windows 2000 Server SP3C)Windows 2000 Professional Edition SP4D)Windows 2000 Server SP4E)Win

42、dows 2003 Server SP1F)Windows 2003 Enterprise Edition SP1G)Windows 2003 Datacenter Edition SP1H)Windows 2003 Web Edition SP1 *I)Windows 2000 Advanced Server SP4J)Windows 2000 Datacenter Server SP4K)L)Windows XP Home Edition SP2Windows XP Professional Edition SP2M)Windows XP Media Edition SP28. SQL S

43、erver 2005 标准版不可以安装在( H 、K )操作系统上。A)Windows 2000 Server SP2B)Windows 2000 Server SP3C)Windows 2000 Professional Edition SP4D )Windows 2000 Server SP4E)Windows 2003 Server SP1F)Windows 2003 Enterprise Edition SP1G )Windows 2003 Datacenter Edition SP1H) Windows 2003 Web Edition SP1I )Windows 2000 Adva

44、nced Server SP4J)Windows 2000 Datacenter Server SP4K)Windows XP Home Edition SP2L) Windows XP Professional Edition SP2M ) Windows XP Media Edition SP29. 下面( F、G )不属于 SQL Server 2005 的服务器组件。A)数据库引擎( Database Engine )B)报表服务(Reporting Services )C)分析服务(Analysis Services )D)集成服务(Integration Services )E)通知服务(Notification Services )F)网络服务(Web Services )*G)客户服务(Custom Services )*10. 完全限定名包括( A )A ) 服务器名、数据库名、数据库架构名、对象名B) 服务