数据库初级 复习汇总

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

2、) 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”和“Categories”。在视图上右键新建视图，添加上面的四个表，做好后保存为ntv ，名字不能错。其对应的代

3、码为：CREATE VIEW dbo.ntvASSELECT dbo.Categories.类名, SUM(dbo.OrderDetails.单价* dbo.OrderDetails.数量) * (1 - dbo.OrderDetails.折扣) AS 销售金额FROM dbo.Categories INNER JOIN dbo.Products ON dbo.Categories.类ID = dbo.Products.类ID INNER JOIN dbo.OrderDetails ON dbo.Products.产品ID = dbo.OrderDetails.产品ID INNER JOIN

4、dbo.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的属性，代码如下：CREATE VIEW dbo.SalesVASSELECT 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

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

7、s.类名, SUM(dbo.Products.单价 * dbo.Products.库存量) AS 库存资金 FROM dbo.Categories INNER JOIN dbo.Products ON dbo.Categories.类ID = dbo.Products.类IDGROUP BY dbo.Categories.类名GO二、 基础题（简单，必得分题目）【例1】在表“Products”中增加一字段，字段名为“每件体积”，数据类型为“nvarchar”,20，不可以为空,其默认值为“0x0x0”。做法：在题目中的表上右击“修改”，在打开的编辑界面下输入字段，并设类型及长度，在下面的属性栏

8、里找到“常规”的“默认值或绑定”,输入题中的默认值，然后保存表。【例2】打开表“Shippers”，将“服务电话”字段的内容由“（503）”修改为“（8621）”。做法：打开题目中的表，即在表上右击“打开表”，找到对应字段下面的内容进行修改。或者在新建查询里输入如下脚本：use salesgoUPDATE Shippers SET 服务电话='(8621)'+right(服务电话,8) where left(服务电话,5)='(503)'【例3】将素材目录下的Access数据库“FPNWIND.MDB”中的表“运货商”导入到数据库sales中。要求：将原表的“电

9、话”字段名改为“服务电话”。做法：在sales数据库上右击“导入数据”，然后按向导的方法完成各个步骤的操作，注意本题数据源在导入时的文件类型为“Microsoft Access”（如果数据源是Excel数据文件则要选择“Microsoft Excel”；如果数据源是文本文件则要选择“平面文件源”），在倒数第四步里别忘了在“表和视图”表格的“编辑”按钮上点击进去后修改目标字段的名称（有时候还要修改目标字段的类型）。操作的部分图如下所示：点击“编辑”后在窗口中将“电话”字段名改为“服务电话”如下图所示：【例4】为表“EmployeeTerritories”建立一个参照完整性(既外键),要求表“Em

10、ployeeTerritories”的所有记录的“雇员ID”字段的值，在表“employees”中已经存在，并以“k1”为名保存。做法：要先检查主键表“Employees”中的“雇员ID”字段是否已经为主键，若没有设好是不能设置外键的；之后进入表“EmployeeTerritories”的设计窗口点击“关系”，然后按要求添加新的关系。部分图如下所示： 【例5】为表“orders”建立一个参照约束（check约束），要求表“orders”的字段“运输日期”必须比“要货日期”早5天，且以“y1”为名保存(不要检查已有的数据)。做法：进入表“Orders”的设计窗口点击“check约束”，添加一个新

11、的check约束，内容为：Datediff (day, 运输日期, 要货日期)>=5 。并将“检查现有的数据”前的勾去掉。【例5_2】为表“Employees”建立一个参照约束（check约束），要求表“Employees”的字段“雇佣日期”必须比“生日”晚18年，且以“y1”为名保存(不要检查已有的数据)。做法：对Employees右击，选择修改；在设计窗口点击“check约束”，添加一个新的check约束如下图所示：（题五也是同样的图示）点击表达式：输入如下图中内容， 再点击最后一个选项，选择“否”，最后修改名称并保存。【例6】建立一个角色OrderOP，要求此角色能够胜任根据客户的

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

13、设置，具体做法是按题目要求先在具体数据库外面的“安全性“文件夹里创建一个登录名，然后进入sales数据库的“安全性”里的“用户”上右击新建用户，填写用户名并选择已建的登录名，在该界面的左边选择“安全对象”页面，点击“添加”按钮选第二类，出现了全部表以后，在选相应的表及权限。设置完成即可。部分表如下图所示：【例7】对表“Customers”建立一个全文索引sb1，然后在表“Customers”中查找所有包含“Sales Agent”的记录，并将查找结果以表syjg保存在数据库sales中。做法：先在Customers表上设置主键，然后在sales数据库上右键单击“属性”，在文件页面上勾选“使用全

14、文索引”，最后在Customers表上右键单击“全文索引”，按向导方法完成，注意在“创建新目录”下面的目录名中输入sb1。完成全文索引之后在新建查询界面输入如下代码：use salesgoselect * into syjg from dbo.Customers where contains(*,'"Sales Agent"')此处字体应该为黑色，可以打勾三、 存储过程类（必得分题目）【例1】编写一个存储过程cc1，要求为所有英国商品（即表“Suppliers”的“国家”为“UK”的供应商提供的商品），在原价格（指“单价”）的基础上提价5%。做法：在sale

15、s数据库下面的“可编程性”文件夹的“存储过程“文件夹上右击新建存储过程，对应的代码如下：CREATE PROCEDURE cc1ASBEGINupdate dbo.Products set 单价=单价*(1+0.05) FROM dbo.Suppliers INNER JOIN dbo.Products ON dbo.Suppliers.供货商ID = dbo.Products.供货商IDWHERE (dbo.Suppliers.国家= N'UK')END【例2】编写一个存储过程cc2，要求为“类名”为“Seafood”的所有Products在原价格（指“单价”）的基础上提价1

16、0%。做法：在sales数据库下面的“可编程性”文件夹的“存储过程“文件夹上右击新建存储过程，对应的代码如下：CREATE PROCEDURE cc2ASBEGINupdate dbo.Products set 单价=单价*(1+0.1) FROM dbo.Categories INNER JOIN dbo.Products ON dbo.Categories.类ID = dbo.Products.类IDWHERE (dbo.Categories.类名 = N'Seafood')END四、 触发器类【例1】建立删除触发器，要求当表“Employees”的记录被删除后，表“Emp

17、loyeeTerritories”中相应的记录也能自动删除,并以“cfq1”为名保存。做法：在表“Employees”下面的触发器文件夹上右击新建触发器，修改其中的代码如下：CREATE TRIGGER c1 ON dbo.Employees AFTER DELETE AS BEGINdelete from dbo.EmployeeTerritories where 雇员ID in(select 雇员ID from deleted)ENDGO【例2】建立修改触发器，要求表“OrderDetails”中的记录修改后，相应产品在表“Products”的字段“库存量”和“预订量”的数字也要做相应的修

18、改（“库存量”要减去修改后的“数量”并加上修改前的“数量”，“预留量”要加上修改后的“数量”减去修改前的“数量”），并以“cfq2”为名保存。做法：在查询分析中输入或在表“OrderDetails”的“触发器”上鼠标右击“新建触发器”，然后在窗口中输入：create trigger cfq2 on OrderDetails for Updateasbeginupdate products set 库存量=库存量+数量,预订量=预订量-数量 from deleted INNER JOIN Products ON deleted.产品ID = Products.产品IDupdate product

19、s set 库存量=库存量-数量,预订量=预订量+数量 from inserted INNER JOIN Products ON inserted.产品ID= Products.产品IDend【例3】建立添加触发器，要求表“OrderDetails”中的记录增加后，相应产品在表“Products”的字段“库存量”和“预订量”的数字也要做相应的修改（“库存量”要减去新增记录的“数量”，“预订量”要加上新增记录的“数量”），并以“cfq2”为名保存。做法：在查询分析中输入或在表“OrderDetails”的“触发器”上鼠标右击“新建触发器”，然后在窗口中输入：Create trigger cfq2

20、 on orderdetails for InsertasDeclare num intSelect num=(select数量 from inserted)Update products set库存量=库存量-num,预订量=预订量+numwhere 产品ID=(select产品ID from inserted)【例4】建立删除触发器，要求当表“Employees”的记录被删除后，表“EmployeeTerritories”中相应的记录也能自动删除,并以“cfq1”为名保存。做法：在查询分析中输入或在表“Employees”的“触发器”上鼠标右击“新建触发器”，然后在窗口中输入：create

21、 trigger cfq1 on Employeesfor deleteasbegindelete from EmployeeTerritorieswhere 雇员ID =(select 雇员ID from deleted)end 【例5】建立删除触发器，要求当表“Products”的记录被删除后，要对表“OrderDetails”中相应的记录进行检查，要求：如果被删除的产品在表“OrderDetails”中已经有记录了，则该产品不能删除（要恢复），要将表“Products”中的“状态”字段设为“0”；如果被删除的产品在表“OrderDetails”中没有记录,则可以删除，并以“cfq1”为名

22、保存。做法：在查询分析中输入或在表“Products”的“触发器”上鼠标右击“新建触发器”，然后在窗口中输入：create trigger cfq1 on products for deleteasif exists(select * from deleted where 产品ID in (select 产品ID from orderdetails)begindeclare ID intselect ID = 产品ID from deletedrollback transactionupdate products set 状态='0' where 产品ID = ID end 五

23、、 游标类【例1】打开查询分析器，首先声明一个游标syb,要求返回表“Products”中“产品名”为“Geitost”的供应商，且该游标允许前后滚动和修改；然后打开该游标；第三步对该游标所指定的记录进行修改，将其中“每件数量”由“500g”改为“454g”；然后关闭该游标并删除该游标。该过程在查询分析器中调试成功后，将上述程序过程以“cc2”为名，做为存储过程保存在数据库sales中。做法：在新建查询中输入以下代码并运行：Use salesgocreate Procedure cc2 asdeclare syb cursor dynamic for select * from Supplie

24、rs,products where Suppliers.供货商ID=products. 供货商ID and Products.产品名='Geitost' and products. 每件数量=500 g'open sybFETCH NEXT FROM sybwhile fetch_status=0 begin UPDATE products SET 每件数量='454g' WHERE CURRENT OF syb fetch next from syb endclose sybdeallocate sybgo【例2】打开查询分析器，首先声明一个游标syb

25、,要求返回表“Products”中“产品名”为“Geitost”的供应商，且该游标允许前后滚动和修改；然后打开该游标；第三步对该游标所指定的记录进行修改，将其中“每件数量”由“500g”改为“454g”；然后关闭该游标并删除该游标。该过程在查询分析器中调试成功后，将上述程序过程以“cc2”为名，做为存储过程保存在数据库sales中。做法：在新建查询中输入以下代码并运行：Use salesgocreate Procedure cc2 asdeclare syb cursor dynamic for SELECT * FROM Products WHERE 产品名='Geitost'

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

27、家为“Denmark”记录的“邮编”由原来的“2800”改为“DK2800”；然后关闭该游标并删除该游标。该过程在查询分析器中调试成功后，将上述程序过程以“cc2”为名，做为存储过程保存在数据库sales中。做法：在新建查询中输入以下代码并运行：Use salesgocreate Procedure cc2 asdeclare syb cursor dynamic for SELECT * from Suppliers WHERE 国家 = 'Denmark' AND 联系人职务 = 'Sales Manager' AND 邮编 = '2800'

28、open sybFETCH NEXT FROM sybwhile fetch_status=0 begin UPDATE Suppliers SET 邮编 = 'DK2800' WHERE CURRENT OF syb fetch next from syb endclose sybdeallocate sybgo六、 用户函数类【例1】建立一个用户函数TotalSales，要求以员工的姓名和年份为实参，返回该员工该年的累计销售总金额（注意以“运输日期”为准）。做法：在新建查询中输入以下代码并运行：CREATE FUNCTION TotalSales (xm varchar(3

29、0),year int) RETURNS money ASBEGINdeclare 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.订单ID

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

31、eclare zje moneyset zje=(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 zje ENDSql2005概念题：(考试时应该是每题4个选项的单选题)1. 数据库管理系统是管理数据库的系统，它按一定的数据模型组

32、织数据，下面的（ I，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 *

33、L） IIS *3. 下面（ D、E ）不是数据库管理系统采用的数据模型A） 层次模型B） 网状模型C） 关系模型D） 幻灯片模型 *E） 多对多模型 *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

34、C *H） 数据库连接池6. SQL Server 2005企业版可以安装在（ 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 20

35、03 Web Edition SP1I） Windows 2000 Advanced Server SP4 *J） Windows 2000 Datacenter Server SP4 *K） Windows XP Home Edition SP2L） Windows XP Professional Edition SP2M） Windows XP Media Edition SP27. SQL Server 2005开发版不可以安装在（ H ）操作系统上。A） Windows 2000 Server SP2B） Windows 2000 Server SP3C） Windows 2000 P

36、rofessional Edition SP4D） Windows 2000 Server SP4E） Windows 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） Windows XP Home Edition SP2L） Windows XP

37、Professional Edition SP2M） Windows XP Media Edition SP28. SQL Server 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 Datacen

38、ter Edition SP1H） Windows 2003 Web Edition SP1 *I） Windows 2000 Advanced Server SP4J） Windows 2000 Datacenter Server SP4K） Windows XP Home Edition SP2 *L） Windows XP Professional Edition SP2M） Windows XP Media Edition SP29. 下面（ F、G ）不属于SQL Server 2005的服务器组件。A） 数据库引擎（Database Engine）B） 报表服务（Reporting

39、 Services）C） 分析服务（Analysis Services）D） 集成服务（Integration Services）E） 通知服务（Notification Services）F） 网络服务（Web Services） *G） 客户服务（Custom Services） *10. 完全限定名包括（ A ）A） 服务器名、数据库名、数据库架构名、对象名 *B） 服务器名、数据库名、所有者名、对象名C） 服务器名、数据库名、数据库管理员名、对象名D） 服务器名、数据库连接名、数据库架构名、对象名E） 服务器连接名、数据库名、数据库架构名、对象名11. 数据库对象不包括（ M、N、O ）A） 表B） 视图C） 索引D） 约束E） 存储过程F） 触发器G） 默认值H） 用户I） 角色J） 规则K） 类型L） 函数M） 关系