• 1. 数据库系统原理与应用 第八讲
  • 2. 由于SELECT语句返回所有满足WHERE子句中条件的行,而应用程序并不总能将整个结果集作为一个单元来处理,因此引入游标机制,以便每次处理一行或一部分行。 游标包括游标结果集(Cursor Result Set)和游标位置(Cursor Position)两个部分,10.4 使用游标
  • 3. 一、游标的基本操作 1.声明游标 DECLARE 游标名 [INSENSITIVE] [SCROLL] [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] CURSOR FOR select_statement [FOR {READ ONLY | UPDATE [OF 列名[,…n] ] } ] 其中:INSENSITIVE创建将由该游标使用的数据的临时副本,该游标不允许修改。 SCROLL指定所有的提取选项,分FIRST、LAST、PRIOR、NEXT、RELATIVE和ABSOLUTE几种。10.4 使用游标
  • 4. DECLARE 游标名 [INSENSITIVE] [SCROLL] [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] CURSOR FOR select_statement [FOR {READ ONLY | UPDATE [OF 列名[,…n] ] } ] SQL Server2005支持四种游标: STATIC静态游标 按照打开时的原样显示结果集 DYNAMIC动态游标 反映结果集中所作的所有更改 FAST_FORWARD只进游标 只能从头到尾顺序提取 KEYSET键集驱动游标 打开时成员和行顺序是固定的 10.4 使用游标
  • 5. DECLARE 游标名 [INSENSITIVE] [SCROLL] [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] CURSOR FOR select_statement [FOR {READ ONLY | UPDATE [OF 列名[,…n] ] } ] select_statement是标准的SELECT语句 READ ONLY把游标定义为只读 UPDATE定义游标内可更新的列,如没有指定列表,则可以更新所有的列10.4 使用游标
  • 6. 2.打开游标 OPEN 游标名 打开游标时服务器将执行声明时使用的SELECT语句。游标打开后,其位置位于第一行。 3.关闭游标 CLOSE 游标名 关闭游标后可以再次打开。 4.释放游标 DEALLOCATE 游标名 释放游标将释放所有分配给此游标的资源。10.4 使用游标
  • 7. 5.提取行 FETCH [ [NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | RELATIVE n ] FROM ] 游标名 [INTO @variable_name[,…n] ] INTO @variable_name[,…n]允许将提取的列数据放到局部变量中。 可以通过@@FETCH_STATUS()函数报告上一个FETCH的状态,0为成功,-1为失败,-2为行不存在。10.4 使用游标返回当前行之后的结果行,且当前行递增为结果行。如是对游标第一次提取,则返回第一行。返回当前行之前的结果行,且当前行递减为结果行。如是对游标第一次提取,则无行返回。返回游标的第一行,并将其作为当前行。返回游标的最后一行,并将其作为当前行。如n>0则返回从游标头开始的第n行,如n<0则返回游标尾之前的第n行,并将其作为当前行。如n=0则没有行返回。如n>0则返回当前行之后的第n行,如n<0则返回当前行之前的第n行,并将其作为当前行。如n=0则返回当前行。
  • 8. 二、使用游标 1.使用游标的过程10.4 使用游标使用DECLARE CURSOR声明游标使用OPEN打开游标使用FETCH INTO提取数据空否使用CLOSE关闭游标使用DEALLOCATE 释放游标不空空
  • 9. 例:利用游标打印每个学生的信息及平均分10.4 使用游标
  • 10. 2.使用游标修改和删除数据 只要在游标声明时没带READ ONLY,那么这个游标就是可更新的。 修改:UPDATE 表名 SET 列名=表达式 […] WHERE CURRENT OF 游标名 删除:DELETE表名 WHERE CURRENT OF 游标名 10.4 使用游标
  • 11. 例:在score表中增加一个level列,存放每个成绩对应的成绩等级。10.4 使用游标
  • 12. 第十一章 索引 索引和书的目录类型,通过索引可以快速地访问数据库表中的特定数据,它是对数据库表中一个或多个列的值进行排序的结构。 … …    索引表  主键  地址指针 200401003 o 200402001 o 200402002 o 200401002 o 200401001 o 200402001 杨秋红 … … … … 数据区(学生关系S) 200401002 李建平 … 200402002 吴志伟 … 200401003 王丽丽 … 200401001 张华 …
  • 13. 第十一章 索引 当经常查询索引列中的数据时,就需要在表上创建索引。不过索引会降低添加、删除和修改行的速度,但对数据的检索速度将大大提升。
  • 14. 第十一章 索引11.1 索引类型 11.2 创建和删除索引 11.3 查看和修改索引属性
  • 15. 11.1 索引类型SQL Server 2005提供3种类型的索引: 聚集索引 非聚集索引 唯一索引
  • 16. 11.1 索引类型1.聚集索引 在这种索引中,数据表中记录的物理顺序与索引顺序相同,因此最适合范围查找。 每个表只能有一个聚集索引。 如果一个表中没有创建其他聚集索引,则在表的主键上自动创建聚集索引。
  • 17. 11.1 索引类型聚集索引主要用于下面几种情况: 包含大量非重复值的列 经常使用between、>、>=、<和<=运算符返回一个范围值的查询 被连续访问的列 返回大型结果集的查询 经常被使用连接或GROUP BY子句访问的列 联机事务处理类型的应用程序
  • 18. 11.1 索引类型2.非聚集索引 这种索引中,数据存储在一个地方,索引存储在另一个地方,索引带有指针指向数据的存储位置,索引中的项目按索引键值的顺序存储。
  • 19. 11.1 索引类型非聚集索引主要用于下面几种情况: 包含大量非重复值的列 不返回大型结果集的查询 返回精确匹配的查询的搜索条件中常用的列 经常需要连接和分组的列 在特定的查询中覆盖一个表中的所有列
  • 20. 11.1 索引类型3.唯一索引 这种索引表示表中任何两个记录的索引值都不相同,可以确保索引列不包含重复的值。 在创建主键和唯一性约束的列上会自动创建唯一性索引。
  • 21. 11.2 创建和删除索引一、创建索引 SQL Server2005提供了3种方法来创建索引: 使用SQL Server控制管理器 使用CREATE INDEX语句 使用CREATE TABLE语句
  • 22. 11.2 创建和删除索引创建索引时,需要指定索引的特征: 聚集还是非聚集索引 唯一还是不唯一索引 单列还是多列索引 索引中的列顺序为升序还是降序 覆盖还是非覆盖索引 覆盖索引是指那些索引项中包含查寻所需要的全部信息的非聚簇索引,这种索引之所以比较快也正是因为索引页中包含了查寻所必须的数据,不需去访问数据页
  • 23. 11.2 创建和删除索引1.使用SQL Server控制管理器创建索引 例:对school数据库的 student表的sclass列创 建一个升序的非聚集索 引IQ_sclass。
  • 24. 11.2 创建和删除索引
  • 25. 11.2 创建和删除索引可删除已有的同名索引可重新生成索引在唯一性索引时,如选中则忽略重复键,反之则报错当未选中时,索引不使用行级锁定。清除此选项可提高索引维护的速度,但可能阻塞其他用户。 当未选中时,索引不使用页级锁定。此选项将降低暂时阻塞其他用户的可能性,但也会降低索引维护操作的速度。 对各索引页的叶级填充的程度。该值大时,插入或修改记录后重新索引的工作会很大,IO操作增加,性能下降,但其占用空间小。不选可提高索引维护的速度,但很可能阻塞其他用户。限制执行并行计划时所使用的处理器数
  • 26. 11.2 创建和删除索引2.使用CREATE INDEX语句CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX 索引名称 ON {表名 | 视图名} (列名 [ASC | DESC] [,…n]) [WITH index_option [,…n] ] [ON [filegroup | default] ]UNIQUE:创建唯一索引 CLUSTERED:创建聚集索引,该索引将对磁盘上的数据进行物理排序 NOCLUSTERED:创建非聚集索引,每个表最多可有249个非聚集索引
  • 27. 11.2 创建和删除索引ASC | DESC:确定升序还是降序 Index_option:索引选项,定义为 { PAD_INDEX={ON | OFF} | FILLFACTOR=fillfactor | IGNORE_DUP_KEY={ON | OFF} | DROP_EXISTING={ON | OFF} | STATISTICS_NORECOMPUTE={ON | OFF} | SORT_IN_TEMPDB ={ON | OFF} }索引的填充1-100间的填充度,默认0ON时发出警告信息;OFF时发出错误信息且回滚。ON时删除并重新生成索引ON时不自动重新计算过时的统计信息ON时在tempdb中存储临时排序结果
  • 28. 11.2 创建和删除索引例:为score表的sno(降序)和cno列创建聚集索引IDX_SC,且强制唯一。例:为score表的sno(降序)和cno列创建索引IDX_SC1,使其填充度为80%。
  • 29. 11.2 创建和删除索引3.使用CREATE TABLE语句 在创建表时,通过指定PRIMARY KEY约束或UNIQUE约束,SQL Server会自动为这些约束创建索引。
  • 30. 11.2 创建和删除索引二、删除索引 SQL Server2005提供了2种方法来创建索引: 使用SQL Server控制管理器 使用DROP INDEX语句 DROP INDEX 表名.索引名
  • 31. 11.3 查看和修改索引属性使用SQL Server控制管理器
  • 32. 11.3 查看和修改索引属性使用T-SQL语句查看和修改索引属性 1.查看索引信息 EXEC sp_helpindex 对象名 2.修改索引属性 ALTER INDEX {索引名 | ALL } ON 表或视图名 REBUILD [WITH (rebuild_index_option) ]
  • 33. 第十一章视图 视图在数据库中是作为一个独立的对象进行存储的。 在关系数据库系统中,为了简化用户的查询操作,而又不增加数据的存储空间,采用视图方式
  • 34. 第十二章 视图12.1 创建和删除视图 12.2 使用视图 12.3 视图定义的修改和信息查看
  • 35. 12.1 创建视图和删除视图 视图在数据库中是作为一个独立的对象进行存储的。视图和查询很相似,但也有区别: 存储方式:视图存储为数据库设计的一部分,而查询则不是 更新结果:对视图和查询的结果集更新限制是不同的 排序结果:可以排序任何查询结果,而视图只有包括TOP子句时才能排序 参数设置:可以为查询创建参数,但不能为视图创建参数 加密:可以加密视图,但不能加密查询。
  • 36. 12.1 创建视图和删除视图一、创建视图 SQL Server2005提供了2种方法来创建视图: 使用SQL Server控制管理器 使用CREATE VIEW语句
  • 37. 12.1 创建视图和删除视图CREATE VIEW view_name [ ( column [, …n] ) ] [ WITH view_attribute [,…n] ] AS select_statement [WITH CHECK OPTION] 其中WITH CHECK OPTION是强制视图上执行的所有数据修改都必须符合select_statement设置的准则。 view_attribute:ENCRYPTION,防止将视图作为SQL Server复制的一部分发布;SCHEMABINDING,把视图绑定到架构上;VIEW_METADATA,指定为引用视图的查询请求浏览模式的元数据时,返回有关视图的元数据信息,而不是基表或表。
  • 38. 12.1 创建视图和删除视图书上例12.2创建名为st1_degree的视图,包括所有学生的姓名、课程和成绩。
  • 39. 12.1 创建视图和删除视图二、删除视图 SQL Server2005提供了2种方法来删除视图: 使用SQL Server控制管理器 使用DROP VIEW语句 DROP VIEW {view_name} [,…n] 可以一次删除一个或多个视图
  • 40. 12.2 使用视图1.使用视图进行数据查询 SELECT * FROM st1_degree 2.通过视图向基表中插入数据 一般只针对简单的视图。要符合下面条件: 需要有插入数据的权限 对于视图中未引用的字段,数据表应该允许为空或有默认值 视图不能包含多个字段值的组合以及统计函数 视图不能包含DISTINCT或GROUP BY 不能在一个语句中对多个基表使用数据修改语句
  • 41. 12.2 使用视图书上例12.5
  • 42. 12.2 使用视图3.通过视图修改基表中的数据
  • 43. 12.2 使用视图4.通过视图删除基表中的数据
  • 44. 12.3 视图定义的修改和信息查看一、视图定义的修改 SQL Server2005提供了2种方法来修改视图: 使用SQL Server控制管理器 使用ALTER VIEW语句ALTER VIEW view_name [ ( column [, …n] ) ] [ WITH view_attribute [,…n] ] AS select_statement [WITH CHECK OPTION]
  • 45. 12.3 视图定义的修改和信息查看二、视图重命名 SQL Server2005提供了2种方法来重命名视图: 使用SQL Server控制管理器 使用存储过程sp_renamesp_rename [@objname=] ‘object_name’, [@newname=] ‘new_name’ [, [@objtype=] ‘object_type’] 其中:object_name为视图的当前名称 new_name为视图的新名称 object_type为重命名对象的类型,默认值为NULL
  • 46. 12.3 视图定义的修改和信息查看三、查看视图的信息 SQL Server2005提供了2种方法来查看视图信息: 使用SQL Server控制管理器 使用存储过程sp_helptextsp_helptext [@objname=] ‘object_name’,
  • 47. 第十三章 数据库完整性数据库完整性是确保数据库中的数据的一致性和正确性 13.1 约束 13.2 默认值 13.3 规则
  • 48. 13.1 约束SQL Server2005提供了多种强制数据完整性的机制 PRIMARY KEY约束 FOREIGN KEY约束 UNIQUE约束 CHECK约束 NOT NULL约束
  • 49. 13.1 约束1.PRIMARY KEY约束 可以约束标识列或列集,这些列或列集的值唯一地标识表中的行。CREATE TABLE department /*部门表*/ ( dno int PRIMARY KEY, /*部门号为主键*/ dname char (20), /*部门名*/ )
  • 50. 13.1 约束 如在创建表时指定了主键,SQL Server会自动创建一个“PK_”且后跟表名的主键索引,这个索引默认是聚集索引,同时也是一个唯一索引。
  • 51. 13.1 约束2.FOREIGN KEY约束 可以约束表之间的关系,为表的一列或多列数据提供参照完整性。CREATE TABLE worker /*职工表*/ ( no int PRIMARY KEY, /*编号为主键*/ name char (8), /*姓名*/ sex char (2), /*性别*/ dno int /*部门号*/ FOREIGN KEY REFERENCES department (dno) ON DELETE NO ACTION, address char (30), /*地址*/ )ON ACTION:指定删除因错误而失败。另一个选项CASCADE:指定删除已删除行的外键的所有行。
  • 52. 13.1 约束3.UNIQUE约束 可以列集内强制执行值的唯一性。如果列有UNIQUE约束,SQL Serevr将确保数据除NULL外的所有值均唯一。CREATE TABLE table5 ( c1 int UNIQUE, c2 int )
  • 53. 13.1 约束4.CHECK约束 通过限制用户输入的值来加强域完整性。指定应用于列中输入的所有值的布尔条件,拒绝所有不为TRUE的值。CREATE TABLE table6 (f1 int , f2 int NOT NULL CHECK(f2>=0 and f2<=100) )限定f2只能为0到100
  • 54. 13.2 默认值 SQL Server2005提供了两种使用默认值的方法 创建表时指定默认值 使用CREATE DEFAULT创建默认对象,然后使用存储过程sp_bindefault来绑定到指定列上
  • 55. 13.2 默认值1.创建表时指定默认列 1)使用SQL Server管理控制器性别默认为男
  • 56. 13.2 默认值2)使用T-SQL语言默认为10默认为当前日期也可以通过ALTER TABLE来给列加上默认值: ALTER TABLE table8 ADD CONSTRAINT con1 DEFAULT getdate() FOR C3
  • 57. 13.2 默认值2.使用默认对象 默认对象是单独存储的,删除表时DEFAULT约束会自动删除,但默认对象不会删除。
  • 58. 13.3 规则 规则限制了可以存储在表中或者用户定义数据类型的值。 规则与CHECK约束功能基本相同,但一个列只能应用一个规则,却可以应用多个CHECK约束。CHECK可作为CREATE TABLE语句的一部分进行指定,而规则以单独的对象创建,然后绑定到列上。
  • 59. 13.3 规则
  • 60. ●上机练习3 创建People表CREATE TABLE People( 员工编号 char(6) NOT NULL PRIMARY KEY , 员工姓名 varchar(10) NOT NULL, 所属部门 varchar(30) , 口令 varchar(20) , 电子邮箱 varchar(30) , 出生年月 datetime , 家庭成员数量 int, 月工资 money, 裸眼视力 decimal (3,2), [近视/远视度数] int)
  • 61. ●上机练习3 在People表中插入记录INSERT INTO People VALUES ('100001', '李四', '计算机', '1234567', 'lisi@hotmail.com', '1980-01-07', 5, 2532.78, 3.25, -100)INSERT INTO People (员工编号,员工姓名,所属部门,电子邮箱,出生年月,家庭成员数量,裸眼视力) VALUES ('100001', '李四', '计算机', 'lisi@hotmail.com', '1980-01-07', 5, 3.25)
  • 62. ●上机练习3 在People表中添加字段及修改字段ALTER TABLE People ADD 性别 char(2), 结婚日期 datetime, 岗位名称 varchar(30), 上岗年月 char(4)ALTER TABLE People ALTER COLUMN 岗位名称 varchar(100) ALTER TABLE People ALTER COLUMN 上岗年月 char(6)
  • 63. ●上机练习3 修改该表岗位名称字段长度为100,修改上岗年月字段类型为数字型6位use wee alter table people alter column [岗位名称] varchar(100) alter table people alter column [上岗年月] decimal(6,0)
  • 64. ●上机练习3 删除该表中结婚年月的字段use wee alter table people drop column 结婚年月
  • 65. ●上机练习3 按课程名升序、百分制成绩降序显示。use 成绩管理 select c.学号,c.姓名,a.课程编号,b.课程名,a.百分制 from 成绩表a,课程表b,学生表c where a.课程编号=b.课程编号and a.学号=c.学号 order by 课程名,百分制desc
  • 66. ●上机练习3 输出学号为“97101000”学生的上述字段内容。use 成绩管理 select a.学号,c.姓名,a.课程编号,b.课程名,a.百分制 from 成绩表a,课程表b,学生表c where a.课程编号=b.课程编号and a.学号='97101000' and a.学号=c.学号 order by 课程名,百分制desc
  • 67. ●上机练习3 输出该生一年级所有成绩。use 成绩管理 select a.学号, c.姓名,a.课程编号,b.课程名,a.百分制,a.学年 from 成绩表a,课程表b,学生表c where a.课程编号=b.课程编号and a.学号='97101000' and a.学年='1' and a.学号=c.学号 order by 课程名,百分制desc
  • 68. ●上机练习3 输出该生一年级第二、三学期的成绩。use 成绩管理 select a.学号, c.姓名,a.课程编号,b.课程名, a.百分制,a.学期,a.学年 from 成绩表a,课程表b,学生表c where a.课程编号=b.课程编号and a.学号='97101000' and a.学年='1' and (a.学期='2' or a.学期='3') and a.学号=c.学号 order by 课程名,百分制desc
  • 69. ●上机练习3 输出“英语国四”成绩在70~80分之间的同学的记录。 use 成绩管理 select 学生表.姓名,学生表.学号,课程表.课程名,成绩表.百分制,课程表.课程编号 from 学生表,课程表,成绩表 where 课程表.课程编号=成绩表.课程编号and 学生表.学号=成绩表.学号 and 成绩表.课程编号='00000000' and 成绩表.百分制 between 70 and 80 课程名='英语国四'
  • 70. ●上机练习3 显示通过英语国四级的同学人数。use 成绩管理 select b.课程名,count(*) as 通过人数 from 成绩表a,课程表b,学生表c where a.课程编号=b.课程编号and a.学号=c.学号and b.课程名='英语国四' and a.百分制>=60 group by b.课程名
  • 71. ●上机练习3 只显示英语国四成绩前10名同学的记录。use 成绩管理 select top 10 a.学号, c.姓名,a.课程编号,b.课程名, a.百分制,a.学期,a.学年 from 成绩表a,课程表b,学生表c where a.课程编号=b.课程编号and b.课程名='英语国四' and a.学号=c.学号 order by 百分制desc
  • 72. ●上机练习3 查学号“97101000”的同学选课数。use 成绩管理 select a.学号,c.姓名,count(*) as 选课数 from 成绩表a,课程表b,学生表c where a.课程编号=b.课程编号 and a.学号=c.学号and a.学号='97101000' group by a.学号,c.姓名
  • 73. ●上机练习3 查每个人的选课数,并按选课数降序排序。use 成绩管理 select a.学号,c.姓名,count(*) as 选课数 from 成绩表a,课程表b,学生表c where a.课程编号=b.课程编号 and a.学号=c.学号 group by a.学号,c.姓名 order by 选课数desc
  • 74. ●上机练习3 只显示选课数<=40的人记录use 成绩管理 select a.学号,c.姓名,count(*) as 选课数 from 成绩表a,课程表b,学生表c where a.课程编号=b.课程编号and a.学号=c.学号 group by a.学号,c.姓名 HAVING count(*)<=40
  • 75. ●上机练习3 查共有几位同学选了课程号为“01010001”的课程。 SELECT COUNT(*) FROM 成绩表,课程表,学生表 WHERE 成绩表.课程编号=课程表.课程编号 AND 学生表.学号=成绩表.学号 AND 成绩表.课程编号=01010001SELECT COUNT(*) FROM 成绩表 WHERE 成绩表.课程编号=‘01010001’
  • 76. ●上机练习3 查每门课程的选修人数,平均成绩。use 成绩管理 select b.课程编号,b.课程名,count(*) as 选修人数,avg(a.百分制) as 平均分 from 成绩表a,课程表b,学生表c where a.课程编号=b.课程编号and a.学号=c.学号 group by b.课程编号,b.课程名
  • 77. ●上机练习3 统计各门课程的不及格人数,并使不及格人数最多的那门课程排在第一条,但没有不及格人数的课程不显示。 use 成绩管理 select b.课程编号,b.课程名,count(*) as 不及格人数 from 成绩表 a,课程表 b where a.课程编号=b.课程编号 and (a.百分制<60 and 等级制<>'P') group by b.课程编号,b.课程名 having count(*)>0 order by 不及格人数 desc
  • 78. ●上机练习3 显示数学类课程(课程编号在01010001到01010014的课程)中选课人数超过150人的课程。use 成绩管理 select b.课程编号,b.课程名,count(*) as 选课人数 from 成绩表a,课程表b,学生表c where a.课程编号=b.课程编号and a.学号=c.学号 and a.课程编号between 01010001 and 01010014 group by b.课程编号,b.课程名 having count(*)>150
  • 79. ●上机练习3 显示学生表中电话字段、地址字段都为空的记录use 成绩管理 select* from 学生表 where 电话is null and 地址is null
  • 80. ●上机练习3 显示不符合规则的成绩记录:当百分制<60时,绩点不能>0,当百分制>=60时,绩点不能<=0use 成绩管理 select* from 成绩表 where ((百分制<60 and 绩点>0) or (百分制>=60 and 绩点<=0)) and 等级制!='p'
  • 81. ●上机练习3 把成绩表中课程编号为”00000000”的改为”60000000”,然后再改回来。use 成绩管理 update 成绩表set 课程编号='60000000' where 课程编号='00000000' select* from 成绩表where 课程编号='60000000' update 成绩表set 课程编号='00000000' where 课程编号='60000000' select* from 成绩表where 课程编号='00000000'
  • 82. ●上机练习3 按下列规则输出各人的姓名、平均绩点,并按平均绩点降序显示。 use 成绩管理 select a.学号,c.姓名,sum(a.绩点*a.学分)/sum(a.学分) as 平均绩点 from 成绩表 a,学生表 c where a.学号=c.学号 and a.等级制<>’P’ group by a.学号,c.姓名 order by 平均绩点 desc
  • 83. 自学要求与上课安排 ●上机内容: 完成上机实习:做完上机练习6 ,做完课本的上机实验题9。 ●要求: 在上机时,显示你的第六部分上机结果。
  • 84. (本页无文本内容)