2022年数据库课后答案更新到十二章知识 .pdf

数据库技术与应用刘卫国熊拥军主编课后答案第一章数据库系统概论一、选择题ABDAD二、填空题载体、意义；数据集合；数据库管理系统；数据库系统；层次、网状、关系、关系模型第二章关系数据库基本原理一、选择题D DACBD二、填空题1. 实体完整性、参照完整性、用户自定义完整性。2. （A,B） R1(A,D) 和 R2(A,B,C).3. 元子特性4. 外键名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 25 页 - - - - - - - - - 5. F1 （AB，AC，AD）6. 选择第三章 SQL SERVER2005 系统基础一、选择题ACBCB二、填空题1. 企业版，标准版，开发版，工作组版，精简版2. WINDOWs 身份验证模式和混合模式3. SQL Server管理平台、 SQL Server 配置管理器4. 已注册服务器窗口、对象资源管理器，文档窗口5. 数据查询语言，数据定义语言，数据操纵语言和数据控制语言名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 25 页 - - - - - - - - - 第四章数据库的管理一、选择题ABDAC二、填空题1、数据文件和事务日志文件2、master , tempdb , model ,msdb和 mssqlsystemresource3、数据文件，事务日志文件和文件组4、55、create database, alter database, drop database四、应用题1create database saleson primary(name=saldat01,filename=c:dbsaldat01.mdf),(name=saldat02,filename=c:dbsaldat02.ndf),filegroup filegrp1(name=saldat11,filename=d:dbsaldat11.ndf),(name=saldat12,filename=d:dbsaldat12.ndf),名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 25 页 - - - - - - - - - (name=saldat13,filename=d:dbsaldat13.ndf)2alter database salesadd log file(name=sallog2,filename=c:dbsallog2.ldf)3alter database salesadd file(name=saldat03,filename=c:dbsaldat03.ndf,size=5,filegrowth=20%)扩展alter database salesadd file(name=saldat14,filename=d:dbsaldat14.ndf,size=5,filegrowth=20%) to filegroup filegrp14alter database sales set single_user5名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 25 页 - - - - - - - - - drop database sales 第五章表的管理一、选择题CACCC二、填空题1、-2 21-221-1， 0-255.2、输入存储字段小于100时按原字段存，大于100时截取 100位。8个字节。3、日期时间数据类型，数字数据类型4、表名和字段名称5、insert，update ， truncate或 delete 。四、应用题- 创建数据库create database sales-4.1名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 25 页 - - - - - - - - - - 标识符列（自动增长的列）-identity(种子，增量 )create table sell_order(order_id1 int identity(1,2),goods_id char(6) not null,employee_id char(4) not null,custmer_id char(4) not null,transporter_id char(4) not null,order_num float,discount float,order_date datetime,send_date datetime,arrival_date datetime,cost money)- 删除标识符列alter table sell_order名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 25 页 - - - - - - - - - drop column order_id1- 添加标识符列alter table sell_orderadd order_id1 int identity(1,2)-4.2alter table sell_order-drop column send_dateadd 发货日期 datetime-4.3- 标识列自动增长insert sell_order (goods_id,employee_id,custmer_id,order_num,discount,order_date)values(135,16,99,30,9.5,2009-2-26)insert sell_order values(135,16,99,30,9.5,2009-2-26,)- 为空和 null不同insert sell_order values(135,16,99,null,30,9.5,2009-2-26,null,null,null)- 允许手动增长（显示声明）名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 25 页 - - - - - - - - - set identity_insert sales.dbo.sell_order oninsert sell_order (order_id1,goods_id,employee_id,custmer_id,order_num,discount,order_date) values(8,135,16,99,30,9.5,2009-2-26)set identity_insert sales.dbo.sell_order off-4.4insert insert sell_order values(26,02,6,10,200,8,2008-10-10,2008-12-12,200000,2008-12-1)-4.5sell_order values(26,29,100,10,200,8,2009-1-1,2008-12-12,null,2008-12-1)update sell_order set employee_id=16 where (employee_id=29 and cost is null)-4.6update sell_order set discount=discount*0.9 where(custmer_id=100 and cost is null)-4.7名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 8 页，共 25 页 - - - - - - - - - delete from sell_order where order_date=2009-1-1 第六章 数据查询一、选择题CABBC 二、填空题1、TOP/PERCENT 2、UNION ，查询数据 / 结果集3、嵌套查询 / 子查询4、内链接，外连接5、等值连接，自然连接6、into 四、应用题-128 页应用题第一题create database student2 create table student ( s_no char(8), s_name char(10), s_sex char(2), birthday datetime, polity char(8) 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 9 页，共 25 页 - - - - - - - - - ) insert into student values(s003,江鱼,女,2003-01-01,国民党 ) create table sco ( s_no char(8), c_no char(8), score float ) insert into sco values(s003,数据结构 ,85) -1-1 select * from student order by s_no -1-2 select * from student where s_sex=女 order by s_sex compute count(s_sex) -1-3 select s_name,birthday,year(getdate()-year(birthday) as 年龄 from student where s_sex= 男 select s_name,convert(varchar,birthday,23),year(getdate()-year(birthday) as 年龄 from student where s_sex=男 -select convert(varchar,getdate(),23) 只截取系统当前日期-select convert(varchar,getdate(),8) 只截取系统当前时间-1-4 select s_name,birthday,year(getdate()-year(birthday) as 年龄,c_no,score from student,sco where student.s_no=sco.s_no select s_name,birthday,year(getdate()-year(birthday) as 年龄,c_no,score 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 10 页，共 25 页 - - - - - - - - - from student inner join sco on student.s_no=sco.s_no -1-5 select score from sco where s_no=(select s_no from student where s_name=江鱼) select sco.score,student.s-name from sco inner join student on sco.s_no=student.s_no where student.s_name=江于 -1-6 select s_name from student where s_no in(select s_no from sco where score35 -2-5 select 部门名 ,count(职工号 ) as 职工人数 from 职工, 部门 where 部门. 部门号 =职工 . 部门号 group by 部门名 having count(*)=2 order by count(*) desc select 部门名 ,count(*) as 职工人数 from 职工 inner join 部门 on 部门 . 部门号 =职工. 部门号名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 13 页，共 25 页 - - - - - - - - - group by 部门 . 部门名 having count(*)=1 order by count(*) desc 第七章 索引与视图一、选择题CCADB 二、填空题1、alter table, drop index 2、primary key, unique 3、视图4、定义5、计算列6、修改，删除第八章 数据完整性一、选择题BCCAD 二、填空题1、行2、create rule和 create default 3、唯一性名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 14 页，共 25 页 - - - - - - - - - 4、primary key ，多5、检查，唯一性6、constraint，约束名四、应用题- 第八章应用题-1 alter table sell_order add order_id1 int identity(1,2) alter table sell_order drop column order_id1 -2 alter table sell_order add primary key(order_id1) -3 use sales alter table customer add primary key(customer_id) alter table sell_order add foreign key(custom_id) references customer(customer_id) on delete cascade on update cascade -4 alter table customer 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 15 页，共 25 页 - - - - - - - - - add constraint kk unique(customer_name) -5 alter table sell_order add constraint k3 default (0)for discount -6 alter table sell_order add constraint k4 check (order_datesend_date),check (send_datearriver_date) -7 Create rule send_date_rule As send_date Exec sp_bindrule send_date_rule,Sell_Order.send_date-8 Create default order_date_def As getdate() Exec sp_binddefault order_date_def,Sell_Order.order_date第九章 T-SQL 程序设计一、选择题BCCDD 二、填空题1、局部2、44,82,126 3、gf 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 16 页，共 25 页 - - - - - - - - - 4、/* */ 5、单条， begin-end 6、break, continue 7、declare cursor, select 四、应用题-4.1.1 SELECT goods_name as 商品名称 , CASE WHEN classification_id=P001 THEN 笔记本计算机 WHEN classification_id=P002 THEN 激光打印机 WHEN classification_id=P003 THEN 喷墨打印机 WHEN classification_id=P004 THEN 交换机 END AS 商品类别 , unit_price AS 单价,stock_quantity as 库存FROM goods -4.1.2 declare value real set value=-1 while value15000 open employee_cursor FETCH NEXT FROM employee_cursor WHILE fetch_status = 0 BEGIN FETCH NEXT FROM employee_cursor END -4-2 select employee_name, case (datediff(yy,birth_date,getdate()-1)/10 when 2 then 20到30岁 when 3 then 30到40岁 when 4 then 40到50岁 else 50岁以上 end as 年龄段 ,department_name as 所属部门from department,employee where department.department_id=employee.department_id order by 所属部门 asc 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 18 页，共 25 页 - - - - - - - - - select nld as 年龄段 ,count(*) as 人数 from (select case (datediff(yy,birth_date,getdate()-1)/10 when 2 then 20到30岁 when 3 then 30到40岁 when 4 then 40到50岁 else 50岁以上 end as nld,birth_date from employee) a GROUP BY nld -4-3 create function fact2(x int) returns int as begin if x=1 return 1 return x*dbo.fact2(x-1) end select dbo.fact2(6) -4-4 declare i int,j int,sum int select i = 1, j = 0,sum=0 while(i100) 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 19 页，共 25 页 - - - - - - - - - begin if(i%7=0) begin set j=j+1 set sum=sum+i end set i=i+1 end select j,sum -4-5 declare cur_employee cursor for select sex,count(*) from employee group by sex declare sex char(2),sex_num int open cur_employee fetch next from cur_employee into sex,sex_num while fetch_status=0 begin select sex as 性别 ,sex_num as 人数fetch next from cur_employee into sex,sex_num end close cur_employee 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 20 页，共 25 页 - - - - - - - - - deallocate cur_employee 第十章 存储过程和触发器一、选择题CAADA 二、填空题1、sp_ 2、存储过程3、commit transaction, rollback transaction 4、数据封锁机制四、应用题-1 CREATE PROC prStoreOrderID Order_id1 char(6), goods_id char(6), employee_id char(4), Custom_ID char(5), Transporter_ID char(4), order_date datetime, order_id2 char(6) OUTPUT AS BEGIN INSERT INTO sell_order(Order_ID1,goods_id,employee_id,Custom_ID,Transporter_ID,order_date) 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 21 页，共 25 页 - - - - - - - - - VALUES(Order_id1,goods_id,employee_id,Custom_ID,Transporter_ID,order_date) SELECT order_id2=Order_ID1 FROM sell_order WHERE Order_ID1=Order_id1 END DECLARE order_id2 char(6) EXEC prStoreOrderID S00009,G00005,e010,C0009,T010,2013.01.01,order_id2 OUTPUT SELECT order_id2 -2 create proc chapter10_4_2 As begin ；Create table #temp；(id int)；declare n int；Select n=1 ；while(n=100) ；begin ；Insert #temp values(n)；Select n=n+1 ；end；Select * from #temp；end；Exec chapter10_4_2；-3 create proc prupdatename g_id char(6),g_name varchar(50) as begin update goods set goods_name=g_name where goods_id=g_id 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 22 页，共 25 页 - - - - - - - - - end exec prupdatename G0008,IBM T51 -4 CREATE PROC prTest AS SELECT * FROM goods IF EXISTS(SELECT * FROM sysobjects WHERE name=prTest) print 存在此存储过程 ELSE print 不存在此存储过程 -5 EXEC sp_helptext prTest -6 EXEC sp_depends prTest -7 EXEC sp_rename prTest,npr_Test -8 DROP PROC npr_Test 第十一章数据库的安全管理一、选择题CDBC 二、填空题1、登录账号，角色名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 23 页，共 25 页 - - - - - - - - - 2、window 身份验证模式和混合模式3、对象、语句、隐含4、grant, revoke 5、创建新的数据库角色、分配权限给创建的角色、将这个角色授予某个用户四、应用题sp_addlogin kwh,123456,sales use sales sp_grantdbaccess kwh,kwh2 sp_addrolemember db_datareader,kwh2 sp_revokedbaccess kwh2 sp_droplogin kwh 第十二章数据库的备份与还原一、选择题ABAA 二、填空题1、磁盘备份设备、磁带设备、命名通道设备2、backup database restore database 3、完全、事务日志、差异、文件和文件组4、简单恢复模型、完全恢复模型、大容量日志记录恢复模型四、应用题-1. 以文件形式 (临时备份设备 ) 进行备份与还原数据库backmysales1.bak restore database sales from disk=d:backmysales1.bak 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 24 页，共 25 页 - - - - - - - - - -2. 逻辑备份设备exec sp_addumpdevice disk,my2,d:backmysales2.bak backup database sales to my2 restore database sales from my2 - 覆盖现有的数据库restore database sales from my2 with replace -3. 差异备份exec sp_addumpdevice disk,my3,d:backmysales3.bak backup database sales to my3 exec sp_addumpdevice disk,my4,d:backmysales4.bak backup database sales to my4 with differential restore database sales from my3 with norecovery restore database sales from my4 with recovery -4. 日志备份与还原exec sp_addumpdevice disk,my5,d:backmysales5.bak exec sp_addumpdevice disk,my6,d:backmysales6.bak backup database sales to my5 backup log sales to my6 restore database sales from my5 with norecovery restore log sales from my6 with recovery 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 25 页，共 25 页 - - - - - - - - -