基于JXDB的《数据库原理》实验(2015秋)

1、实验报告 实验报告个人成绩单学号： 姓名： 实验成绩：序号实验名应做题数实做题数自评分教师评分实验1数据库的建立修改与删除8实验2 Transact_SQL基本程序设计10实验3学生课程数据库ST设计与实施6实验4数据查询与更新35实验5索引与视图14实验6存储过程10实验7自定义函数10实验8规则与触发器11实验9数据库安全性实验10游标与事务实验11数据库的恢复与并发控制实验12基于C#的数据库应用系统开发 平均分几点说明：实验成绩=所有实验的平均分；要求对实验内容中的每1道题编写源代码并给出运行结果。对非编程题写出操作步骤。实验1 数据库的建立修改与删除一、实验环境操作系统：Window

2、s XP或以上版本。数据库管理系统：MS SQL Server 2008。二、实验目的 1熟悉SQL Server 2008上机环境，学会SQL Server Management Studio的启动与退出。2熟练掌握建立、查看、修改与删除数据库的两种方法。三、实验内容 【1】在用户方便的存储空间上建立存放用户数据库的文件夹。本实验在F盘根目录下建立文件夹：F:SQL2008DB，再建立存放T_SQL源代码的文件夹：F:SQL2008DBT_SQL【2】利用界面方法创建学生课程数据库ST，参数如表1.1所示。表1.1 ST数据库属性设置文件组文件类型逻辑文件名物理文件名初始大小自动增量最大容量

3、主文件组PRIMARY主数据文件ST_DataF:SQL2008DBST_Data.mdf3M1M不限制增长日志文件ST_logF:SQL2008DBST_log.ldf2M10%10M【3】利用界面方法查看ST数据库的属性。【4】使用T-SQL语句建立学生-课程数据库ST，参数如表1.1所示。USE masterIF DB_ID(N'ST') IS NOT NULLDROP DATABASE STCREATE DATABASE STON(NAME=ST_Date,FILENAME='G:SQLT_SQLST_Date.mdf',SIZE=3MB,MAXSIZE

4、=UNLIMITED)LOG ON(NAME=ST_log,FILENAME='G:SQLT_SQLST_log.ldf',SIZE=1MB,MAXSIZE=UNLIMITED,FILEGROWTH=10%)【5】使用T-SQL语句建立数据库JXDB，参数如表1.2所示。表1.2 数据库JXDB属性设置文件组文件类型逻辑文件名物理文件名初始大小自动增量最大容量主文件组PRIMARY主数据文件JXDB_DataF:SQL2008DBJXDB_Data.mdf3M10%不限制增长次数据文件JXDB_Data1F:SQL2008DBJXDB_Data1.ndf2M10%30M自定义文

5、件组JX_Group1次数据文件JXDB_Data2F:SQL2008DBJXDB_Data2.ndf1M1M20M次数据文件JXDB_Data3F:SQL2008DBJXDB_Data3.ndf1M1M10M日志文件JXDB_logF:SQL2008DBJXDB_log.ldf2M10%不限制增长USE masterGOIF DB_ID(N'JXDB') IS NOT NULLDROP DATABASE JXDBGOCREATE DATABASE JXDBONPRIMARY(NAME=JXDB_Date,FILENAME='G:SQLT_SQLJXDB_Date.md

6、f',SIZE=3MB,MAXSIZE=UNLIMITED,FILEGROWTH=10%),(NAME=KXDB_Date1,FILENAME='G:SQLT_SQLJXDB_Date1.mdf',SIZE=1MB,MAXSIZE=30MB,FILEGROWTH=10%),FILEGROUP JX_Group1(NAME=KXDB_Date2,FILENAME='G:SQLT_SQLJXDB_Date2.mdf',SIZE=1MB,MAXSIZE=20MB,FILEGROWTH=1MB),(NAME=KXDB_Date3,FILENAME='G:

7、SQLT_SQLJXDB_Date3.mdf',SIZE=1MB,MAXSIZE=10MB,FILEGROWTH=1MB)LOG ON(NAME=JXDB_log,FILENAME='G:SQLT_SQLJXDB_log.ldf',SIZE=2MB,MAXSIZE=20MB,FILEGROWTH=10%)【6】使用T-SQL语句为数据库JXDB添加文件组JX_Group2，并为此文件组添加1个数据文件 JXDB_Data4；添加1个日志文件JXDB_log1。参数由用户自己设定。ALTER DATABASE JXDBADD FILEGROUP JX_Group2GOAL

8、TER DATABASE JXDBADD FILE(NAME=JXDB_Date4,FILENAME='G:SQLT_SQLJXDB_Date4.mdf',SIZE=1MB,MAXSIZE=20MB,FILEGROWTH=1MB)TO FILEGROUP JX_Group2GOALTER DATABASE JXDBADD FILE(NAME=JXDB_log1,FILENAME='G:SQLT_SQLJXDB_log1.ldf',SIZE=2MB,MAXSIZE=20MB,FILEGROWTH=10%)GO【7】使用T-SQL语句修改数据库JXDB中次数据文件J

9、XDB_Data4的属性：文件初始大小改为5M，增长方式改为每次按20%增长。删除日志文件JXDB_log1。ALTER DATABASE JXDBMODIFY FILE(NAME=JXDB_Date4,SIZE=5MB)GOALTER DATABASE JXDBMODIFY FILE(NAME=JXDB_Date4,FILEGROWTH=20%)GOALTER DATABASE JXDBREMOVE FILE JXDB_log1【8】使用T-SQL语句删除数据库JXDB。DROP DATABASE JXDB;四、实验过程中出现的问题及解决方法实验2 Transact SQL语言基本程序设计一

10、、实验环境操作系统：Windows XP或以上版本。数据库管理系统：MS SQL Server 2008。二、实验目的 1熟练掌握Transact SQL语言中顺序、分支、循环三种控制结构语句的语法、语义与语用。2熟练掌握Transact SQL语言中系统函数的使用方法。3学会使用Transact SQL语句编写基本程序。三、实验内容 【2_1】求方程ax2+bx+c=0的根。(x+2x+1=0）declare a float,b float ,c float,d float,e float,f floatset a=1.0set b=2.0set c=1.0set f=power(b,2)-

11、4*a*cif f <0 print '没有实数根'else if power(b,2)-4*a*c=0beginprint '有一个实数根'endelse if f>0select d=(-1*b-sqrt(f)/(2*a)select e=(-1*b+sqrt(f)/(2*a)print dprint e【2_2】求n以内所有偶数的和。declare n int,x int,sum intset n=30set sum=0set x=0while n>x beginset sum=sum+xset x=x+2 endselect n as

12、'n',sum as '偶数和'【2_3】求语句“I am student.”中各字母的ASC码。DECLARE sen nvarchar(50),tem nvarchar(1)SET sen = 'I am student.'SET tem = LEFT(sen,1)WHILE sen != ''BEGINIF tem = nullBEGINBREAKENDIF (ASCII(tem) - ASCII('A') < 26 AND ASCII(tem) - ASCII('A') >= 0

13、) OR (ASCII(tem) - ASCII('a') < 26 AND ASCII(tem) - ASCII('a') >= 0) BEGINPRINT tem+' '+ CONVERT(nvarchar(3),ASCII(tem)SET sen = SUBSTRING(sen,2,LEN(sen)-1)SET tem = LEFT(sen,1)ENDELSEBEGINSET sen = SUBSTRING(sen,2,LEN(sen)-1)SET tem = LEFT(sen,1)ENDEND【2_4】求语句“I am stu

14、dent.”中各字母出现的频率及其ASC码，重复字母只求1次ASC码。DECLARE i int,a char(20),b char(1),j int,y int,c char(1)select a='I am student',i=0,j=len(a),y=0aa: WHILE (i<=j)BEGIN select b=substring(a,i,1)select i=i+1if(b!='')beginselect y=0while(y<i-1)beginselect c=substring(a,y,1)select y=y+1if(b=c)got

15、o aaendselect 'b'=b,ascii(b)endEND【2_5】在20秒钟内，每隔10秒显示系统时间。declare start datetimeset start=GETDATE()while DATEDIFF(SS,start,GETDATE()<20beginwaitfor delay '0:0:10'select GETDATE() as '当前时间'endGO【2_6】求任意5个数的平方根，若捕捉到负数转异常处理，输出“负数不能开平方”这一出错信息。DECLARE i int,b int,c floatselect

16、i=0while(i<5)beginselect b=rand()*100select c=sqrt(b)select b,cif(c<0) select '负数不可以开平方'select i=i+1end【2_7】对选修表按成绩的分数段显示A(90100)、B(8089)、C(7079)、D(6069)、E(059)五个等级。USE JXDBGO select 成绩=CASEWHEN 成绩 <60 THEN 'E'WHEN 成绩 between 60 and 70 THEN 'D'WHEN 成绩 between 70 and

17、80 THEN 'C'WHEN 成绩 between 80 and 90 THEN 'B'ELSE 'A'ENDFROM 选修【2_8】将某待查学生姓名赋给变量s，查询该学生记录，若不存在则输出“查无此人！”。DECLARE s char(20)select s='zhuliang'select * from 学生where 姓名=s【2_9】对选修2号课分数作如下处理：若2号课平均分低于70分则普加5分，如此反复循环，当最高分为100分时退出循环。USE JXDBGO Declare gradeavg decimal(5,1),

18、a decimal(5,1)While (select AVG(成绩) from 选修 where 课号='2')<70BeginBEGIN TRYUpdate 选修 set 成绩=成绩+5 where 课号='2'END TRYBEGIN CATCH set a=100-(select max(成绩)from 选修 where 课号='2')Update 选修 set 成绩=成绩+a where 课号='2'breakEND CATCHendselect gradeavg=AVG(成绩) from 选修 where 课号=

19、'2'select '2号课平均成绩'= gradeavgSELECT * from 选修 where 课号='2'实验3学生课程数据库ST设计与实施一、实验环境操作系统：Windows XP或以上版本。数据库管理系统：MS SQL Server 2008。二、实验目的 1深刻理解数据库设计的6个步骤，运用理论知识能按步骤自己独立设计一个数据库。2熟练掌握建立、修改与删除表结构的两种方法。3深刻理解数据库的实体完整性、参照完整性及自定义完整性规则。熟练掌握定义完整性约束条件的方法。4掌握利用界面方法和使用T-SQL语句向表中插入记录的方法。三、实

20、验内容 1 学生课程数据库需求分析某高校有若干个学生，每个学生可选修多门课程，每门课程可供多个学生选修，1门课程可以是多门课程的先修课，每门课最多有1门先修课。学生、课程等实体其属性分别如下：学生：学号，姓名，性别，年龄，所在系课程：课号，课名，学分2 学生课程数据库概念结构设计【1】为该校学生课程数据库设计一个E-R模型，对于键属性用下划线“_”标明。图3.1 学生课程数据库E-R模型3 学生课程关系数据库逻辑结构设计【2】将学生课程数据库E-R图转换为等价的关系模型，并标明主键和外键。主键之下用下划线“_”表示，外键之下用双下划线“ ”表示。以上3个关系模式构成学生课程关系数据库模式。学生

21、（学号，姓名，年龄，性别，院系）课程（课号，课名，学分，选修课号）选修（学号，课号，成绩）【3】通过调查分析ST数据库中的数据应满足如表3-1所示的完整性约束条件。表3-1 学生课程数据库ST完整性约束条件表名完整性约束条件约束类型学生学号取值唯一且不能为空值主键性别只能为“男”或“女”Check院系要么为空，要么为院系表中的某院系外键课程课号取值唯一且不能为空值主键选修课号只能是已开设的课号外键学分固定Check选修学号、课号取值唯一且不能为空值主键成绩采用百分制Check学号只能是学生表中已有的学号外键课号只能是课程表中已有的课号外键4 教学关系数据库ST物理结构设计【4】ST数据库中的数

22、据文件和日志文件均存储在文件夹F:SQL2008DB之中。数据库属性设置如表3-2所示。表3-2 ST数据库属性设置表文件类型逻辑文件名物理文件名初始大小自动增量最大容量主数据文件JXDB_DataF:SQL2008DBJXDB_Data.mdf3MB10%不限制增长日志文件JXDB_logF:SQL_logJXDB_log.ldf2MB10%不限制增长【5】对学生课程数据库ST中各个表结构进行详细设计。表3-3 “student”表结构列名数据类型允许Null值约束条件sno Char(6)Primary keysnameChar(8)ssexChar(2)允许Check(性别 in(男,女

23、)sage Date允许sdeptChar(8)References(院系)表3-4 “course”表结构列名数据类型允许Null值约束条件cnoChar(3)Primary keycnameVarchar(16)ccreditAs 课时/8允许cpnoChar(3)允许references课程(课号)表3-5 “sc”表结构列名数据类型允许Null值约束条件snoChar(6)references学生(学号)cnoChar(3)references课程(课号)scoreDecimal(5,1)允许Check(成绩>=0&&成绩<=100)表级约束:primary

24、 key(学号,课号) 级联插入触发器5 数据库实施-建立ST数据库表结构插入数据源代码【6】建立ST数据库表结构插入数据源代码-建立学生-课程数据库表结构插入数据ST.sql-例1 建立学生表结构-方法1: 建立由系统自动生成约束名的学生表结构(年龄)-方法2: 建立自定义约束名的学生表结构(年龄)create table 学生(sex char(5) check(sex in("男","女");-例2 建立课程表结构-例3 建立选修表结构-向学生表插入数据 实验4 数据查询与更新一、实验环境操作系统：Windows XP或以上版本。数据库管理系统：M

25、S SQL Server 2008。二、实验目的 1深刻理解并熟练掌握查询(SELECT)、插入(INSERT)、修改(UPDATE)和删除(DELETE)语句的语法、语义及语用。2熟练掌握单表查询、集合查询、多表查询程序设计方法。3熟练掌握相关与不相关的嵌套查询。三、实验内容 【4_1】查询课时数最高的前3门课程的课号、课名、课时，对于与最后并列的课程应返回这些额外行。 【4-2】 查询选修了课程的学生学号。 【4.3】 查询全体教工的职工号、姓、名、性别、参加工作年月、学历与职务、职称等级、工资、纳税。即将姓与名拆开为两列，学历、职务两列合并为一列，职称等级为：助教为初级；讲师为中级；副教

26、授以上为高级；其他为空串，工资在5000元以上的按超过部分的5%纳税。SELECT 职工号,姓=case when len(姓名)>3 then left(姓名,2) else left(姓名,1)end,名=case when len(姓名)>3 then right(姓名,2) else substring(姓名,2,3)end,性别,参加工作年月=str(year(工作日期)+'.'+ltrim(str(month(工作日期),学历与职务=学历+职务,职称等级=case 职称when '助教' then '初级'when 

27、9;讲师' then '中级'when '副教授' then '高级'when '教授' then '高级'else '' end,工资,纳税=case when 工资>5000 then str(工资-5000)*0.05) else 0 endFROM 教工【4.4】 查询距今15年零6个月之前参加工作的教工。select *from 教工where DATEDIFF(MM, 教工.工作日期, GETDATE() > 15*12+6【4.5】 查询1980年至2000年内参加

28、工作的具有硕士及以上学历且姓名中不含“平”字的教工。【4.6】 查询学号为偶数身高在165以上所有女生的学号、姓名、性别、身高。 【4.7】查询所有姓陈的且有配偶的教工的职工号、姓名、性别、配偶号。 【4.8】 查询办公电话中顺数第2个字符为"2",倒数第1个字符为2,6,7,8的所有行政部门的记录。select *from 部门where 办公电话 like '_2%,'and 部门名 not like '%学院'【4.9】 查询全体教工的部门号,职工号,姓名,性别,生日。查询结果依次按部门号升序排列，按性别降序排列，按生日降序排列。 【4

29、.10】 查询学位课总门数、总学分、平均课时、最高课时、最课时低。select COUNT(*) 学位课总门数,SUM(学分)总学分,AVG(课时)平均课时,MAX(课时)最高课时, MIN(课时)最低课时from 课程WHERE 学位课否=1【4.11】 查询有教学任务的教工人数。select COUNT(DISTINCT 教工号)有教学任务的人数from 教课【4.12】 统计各部门教工人数及工资总额，并按部门号升序排列。select 部门号,COUNT(部门号) 人数,SUM(工资) 总工资from 教工group by 部门号【4.13】 统计各部门男女教工人数，各部门人数及教工总人数

30、。select 部门号,性别,COUNT(部门号) 人数from 教工group by 部门号,性别with cube【4.14】查询有2个(含2个)以上学生成绩不及格的课号及不及格人数。select 课号,COUNT(*) 不及格人数from 选修where 成绩<60group by 课号having COUNT(*)>=2【4.15】查询每个学生的平均成绩，最后统计选修了课程的学生人数。select 学号,AVG(成绩)平均成绩,count(DISTINCT 学号) 选修了课程的人数from 选修group by 学号with rollup【4.16】 生成按部门号升序排列的

31、各部门男女教工人数统计表。select 部门号,性别,COUNT(部门号) 人数from 教工group by 部门号,性别order by 部门号【4.17】 查询教学考核平均成绩低于80分及工龄不足30 年的教工号，并按教工号升序排列。SELECT 教工号,AVG(考核分)平均考核成绩,职工号,DATEDIFF(YY,工作日期,GETDATE() 工龄FROM 教课 JOIN 教工 ON 教课.教工号=教工.职工号WHERE DATEDIFF(YY,工作日期,GETDATE()<30GROUP BY 教工号,职工号,工作日期HAVING AVG(考核分)<80【4.18】 查询

32、学生人数在3人以上的本科专业号。SELECT 专业号,SUM(人数)总人数FROM 班级GROUP BY 专业号HAVING SUM(人数)>=3【4.19】 查询140102班中未选修课程的学生学号。SELECT 学生.学号FROM 学生 WHERE 班号='140102'EXCEPT SELECT DISTINCT 学生.学号FROM 学生,选修WHERE 学生.学号=选修.学号 【4.20】 查询每个班的班号、班名、人数、专业名、部门名。SELECT 班号,班名,人数,专业名,部门名FROM 班级,专业,部门WHERE 班级.专业号=专业.专业号 AND SUBST

33、RING(班级.班号,3,2)= 部门.部门号【4.21】 查询计算机科学与技术学院每个教工所教课程的教工号、姓名、班号、人数、课号、课名、课表课时、折算课时，按教工号升序排列。其中课表课时=课时，折算课时计算方法为：当所教班级学生人数不超过50人时，折算课时是课表课时的1.0倍，以后每增加10人以内，折算课时增加0.1倍，折算课时封顶为2.0倍。select 职工号,教工.姓名,班级.班号,人数,课程.课号,课名, 课时 as 课表课时,折算课时=case when COUNT(*)<=50 then 课时*1.0when COUNT(*)>=150 then 课时*2.0els

34、e 课时*(COUNT(*)-40)/10)*0.1 endfrom 班级, 教工, 部门, 学生, 课程, 选修where 班级.班主任 = 教工.职工号 and 教工.部门号=部门.部门号 and 课程.课号=选修.课号 and 班级.班号=学生.班号 and 部门.部门名='计算机科学与技术学院'group by 职工号,教工.姓名,班级.班号,人数,课程.课号,课名, 课时order by 职工号【4.22】在上题查询结果的基础上对每个 教工的课表课时、折算课时给出小计行。select 职工号,教工.姓名,班级.班号,人数,课程.课号,课名, 课时 as 课表课时,折算

35、课时=case when COUNT(*)<=50 then 课时*1.0when COUNT(*)>=150 then 课时*2.0else 课时*(COUNT(*)-40)/10)*0.1 end from 教工, 班级, 部门, 学生, 课程, 选修where 班级.班主任 = 教工.职工号 and 教工.部门号=部门.部门号 and 课程.课号=选修.课号 and 班级.班号=学生.班号 and 部门.部门名='计算机科学与技术学院' union all select 职工号,教工.姓名, 班级.班号, 人数,课程.课号,课名,课时,sum(课时),sum(

36、课时) from 班级, 教工, 部门, 学生, 课程, 选修 group by 职工号,教工.姓名,班级.班号,人数,课程.课号,课名, 课时order by 职工号;拓展思考：可编一存储过程，将每个教工的“授课课时”、“折算课时”作为小计行插入每个教师之后。【4.23】查询25岁之前参加工作且担任过教学任务的教工记录。SELECT 教工.*FROM 教工 JOIN 教课 ON 教工.职工号=教课.教工号WHERE DATEDIFF(YY,生日,工作日期)<25【4.24】查询应届毕业生的学号、姓名、性别、生日、部门名、专业名、学制、班号，依次按部门号、专业号、班号升序排列。USE J

37、XDBselect 学号,姓名,性别,生日,部门名,专业名,学制,班级.班号from 学生,班级,专业,部门where 专业.院系=部门.部门号 and 班级.班号=学生.班号 and 班级.专业号=专业.专业号 and LEFT(专业.学制,1)+LEFT(学生.学号,2)=15 order by 部门号,班级.专业号,班级.班号【4.25】用两种不同方法查询所有在同一天出生的学生。将结果先按生日降序再按学号升序排列。select *from 学生 a, 学生 b where a.生日=b.生日order by a.生日 DESC, a.学号select *from 学生 a, 学生 b w

38、here DATEDIFF(dd,a.生日,b.生日)=0 order by a.生日 DESC, a.学号【4.26】查询各个部门的部门号、部门名、专业个数、职工人数、学生人数select 部门.部门号,部门名,count(distinct 专业.专业号)专业个数, count(distinct 教工.职工号)职工人数,count(distinct 班级.人数)学生人数from 部门,专业,班级,教工where 部门.部门号 = 专业.院系 and 教工.职工号 = 班级.班主任and 班级.专业号 = 专业.专业号group by 部门.部门号,部门名【4.27】查询每个学生的学号、姓名、

39、平均成绩。SELECT 学生.学号,姓名,AVG(成绩) 平均成绩FROM 学生,选修GROUP BY 学生.学号,姓名【4.28】查询每个学生(含未选修课程的学生)的学号、姓名、总学分。注意：不及格课程无学分。SELECT 学生.学号,姓名,SUM(课程.学分) 总学分,生日FROM 学生,课程WHERE DAY(学生.生日) IN (SELECT DAY(学生.生日)FROM 学生)GROUP BY 学生.学号,姓名,生日ORDER BY 生日 DESC,学号【4.29】查询计算机科学与技术学院共有多少班级。SELECT COUNT(班号) 班级个数FROM 班级,专业,部门WHERE 班

40、级.专业号=专业.专业号 AND 专业.院系=部门.部门号 AND 部门名='计算机科学与技术学院'【4.29】查询在计算机科学与技术学院所有班级均担任过课程的教工的职工号与姓名。查询选修了全部课程的学生学号和姓名。SELECT COUNT(班号)班级数FROM 部门,专业,班级WHERE 部门.部门号=专业.院系 AND专业.专业号=班级.专业号 AND部门.部门名='计算机科学与技术学院'【4.30】学士学位授予条件为：拿到所有必修课的学分，非必修课修满8个以上学分，学位课平均成绩在75分以上。查询计算机科学与技术学院应届毕业生可授予学士学位学生的学号、姓名

41、、性别、班级、总学分、学位课平均成绩。按班级升序，再按学位课平均成绩降序排列。【4.31】使用SELECT语句建立与“学生”表结构完全相同的空表：计科院学生。IF EXISTS (SELECT NAME FROM SYS.objects WHERE NAME='计科院学生' and type='U')DROP TABLE 计科院学生USE JXDBSELECT *INTO 计科院学生FROM 学生WHERE 1<>1【4.32】将计算机科学与技术学院的学生插入到“计科院学生”表中。USE JXDBINSERT INTO 计科院学生SELECT 学生.

42、*FROM 学生,班级,专业,部门WHERE 部门名='计算机科学与技术学院' AND 专业.院系 = 部门.部门号 AND 班级.专业号 = 专业.专业号 AND 学生.班号 = 班级.班号【4.33】职工'19900301'与职工'20100101'结为夫妻。UPDATE 教工SET 配偶号='19900301'WHERE 职工号='20100101' UPDATE 教工SET 配偶号='20100101'WHERE 职工号='19900301' 查询结果【4.34】按对教师进行

43、教学考核的平均分数给教工增加或减少工资，平均分数在90以上的加400元，80至89的加200元，60以下的减少500元。select 姓名,工资+400 from 教工 where 职工号 in(select 教工号 from 教课 where 考核分>90);select 姓名,工资+200 from 教工 where 职工号 in(select 教工号 from 教课 where 80<=考核分 and 考核分<90);select 姓名,工资-500 from 教工 where 职工号 in(select 教工号 from 教课 where 考核分<60);这为了

44、不修改数据使用了select 修改表中数据就用update【4.35】删除无人选修及平均成绩不及格的课程。select * from 课程 where 课号 in(select 课号 from 选修 where 成绩<60) or 课号 not in(select 课号 from 选修 ); 这为了不修改数据使用了select 修改表中数据就用drop实验5 索引与视图一、实验环境操作系统：Windows XP或以上版本。数据库管理系统：MS SQL Server 2008。二、实验目的 1理解索引的目的、维护与使用，唯一与非唯一索引、聚簇与非聚簇索引、单索引与复合索引的区别。2掌握建立

45、与删除索引的方法。3掌握建立与删除视图的方法。4掌握对视图表进行查询与更新的操作方法。三、实验内容 1 建立与删除索引 表5.1 专业表索引规划表名索引项类型创建方式专业专业号(升序)聚集索引隐式专业名(升序)非聚集唯一索引显式学制(降序)、层次(升序)非聚集组合索引显式院系(升序)非聚集单索引显式-【5.1】对专业表按“专业名”升序排列建立唯一索引。IF EXISTS(SELECT name FROM sys.indexes WHERE name='I专业名')DROP INDEX I专业名 ON 专业CREATE UNIQUE INDEX I专业名 ON 专业 (专业名 A

46、SC)-【5.2】对专业表按“学制”降序、“层次”升序建立索引。IF EXISTS(SELECT name FROM sys.indexes WHERE name='I专业')DROP INDEX I专业 ON 专业CREATE INDEX I专业 ON 专业 (学制 DESC, 层次 ASC)-【5.3】对专业表按“院系”升序排列建立索引。IF EXISTS(SELECT name FROM sys.indexes WHERE name='I专业')DROP INDEX I专业 ON 专业CREATE INDEX I专业 ON 专业 (院系 ASC)2 建立与

47、删除视图【5.4】举例说明建立视图时带与不带WITH CHECK OPTION子句的区别。例：建立140101001学生选课视图。并要求透过该视图进行的数据更新操作只涉及该学生。不带WITH CHECK OPTION子句时USE JXDBIF OBJECT_ID ('dbo.V140101001学生成绩','V') IS NOT NULLDROP VIEW V140101001学生成绩GOCREATE VIEW V140101001学生成绩AS SELECT *FROM 选修WHERE 学号='140101001'GOSELECT * FROM

48、V140101001学生成绩带WITH CHECK OPTION子句时USE JXDBIF OBJECT_ID ('dbo.V140101001学生成绩','V') IS NOT NULLDROP VIEW V140101001学生成绩GOCREATE VIEW V140101001学生成绩AS SELECT *FROM 选修WHERE 学号='140101001'WITH CHECK OPTIONGO INSERT INTO V140101001学生成绩 VALUES('140101002','2',90)消息5

49、50，级别16，状态1，第1 行试图进行的插入或更新已失败，原因是目标视图或者目标视图所跨越的某一视图指定了WITH CHECK OPTION，而该操作的一个或多个结果行又不符合CHECK OPTION 约束。语句已终止。GO UPDATE V140101001学生成绩SET 成绩=88WHERE 课号='2'带有WITH CHECK OPTION的子句，每次插入时会自动检查“学号”属性值是否为'140101001'，如果不是则拒绝插入。在查询时会自动加入学号='140101001'条件。【5.5】举例说明建立视图时带与不带WITH SCHEMA

50、BINDING(模式绑定)子句的区别。建立不带SCHEMABINDING子句的男生视图“V男生”；USE JXDBIF EXISTS(SELECT name FROM sys.objects WHERE name='V男生') DROP VIEW V男生GOCREATE VIEW V男生AS SELECT *FROM 学生WHERE 性别='男'WITH CHECK OPTION删除学生表中的“像片”列；GOALTER TABLE 学生 DROP COLUMN 像片查询视图“V男生”。GOSELECT * FROM V男生此例告诉我们，因为建立视图“V男生”时未

51、进行模式绑定，当底层对象学生表被更改后，导致视图“V男生”无法使用，成为“孤立”视图。【5.6】建立计算机科学与技术学院的教工视图。USE JXDBIF EXISTS(SELECT name FROM sys.objects WHERE name='V计科院教工') DROP VIEW V计科院教工GOCREATE VIEW V计科院教工AS SELECT 教工.*FROM 教工 JOIN 部门 ON 部门.部门号=教工.部门号WHERE 部门名='计算机科学与技术学院'WITH CHECK OPTIONGOSELECT * FROM V计科院教工【5.7】建立

52、计算机科学与技术学院的选修视图，对该视图的任何操作只能在工作时间(星期一至星期五9:00至17:00)进行。【5.8】建立计算机科学与技术学院的教课视图。USE JXDBIF EXISTS (SELECT name FROM sys.objects WHERE name='V教课') DROP VIEW V教课GOCREATE VIEW V教课ASSELECT 教课.*FROM 教课 JOIN V计科院教工 ON 教课.教工号=V计科院教工.职工号WITH CHECK OPTIONGOSELECT * FROM V计科院教课【5.9】建立“140101001”学生选修视图。US

53、E JXDBIF EXISTS (SELECT name FROM sys.objects WHERE name='V140101001选修') DROP VIEW V140101001选修GOCREATE VIEW V140101001选修ASSELECT 选修.*FROM 选修 WHERE 选修.学号='140101001'WITH CHECK OPTIONGOSELECT * FROM V140101001选修【5.10】建立教工视图：将教工表中的“生日”换为“年龄”，工作日期换为“工龄”，老干处职工的工龄为退休（男年满60、女年满55退休）前的工龄。US

54、E JXDBIF EXISTS (SELECT name FROM sys.objects WHERE name='V教工') DROP VIEW V教工GOCREATE VIEW V教工ASSELECT 职工号,姓名,性别,datediff(yy,生日,GETDATE() AS 年龄, 工龄=CASEWHEN 2015-year(生日)>60 AND 性别='男' THEN DATEADD(yyyy,60,工作日期)WHEN 2015-year(生日)>55 AND 性别='女' THEN dateadd(yyyy,50,工作日期)ELSE 工作日期END,学历,职务,职称,工资,配偶号FROM 教工GOSELECT *FROM V教工【