数据库简单应用ppt课件

第9章 SQL Server数据库简单应用 本章主要介绍了SQL Server数据库以及表、视图 、存储过程、索引等数据库对象的创建和管理方法。通过 学习，要求达到： 1、能够运用SQL Server企业管理器和查询分析器两种工具 管理数据库； 2、能够在前五章学习的基础上，熟练掌握Transact SQL的 具体应用方法； 3、能够在SQL Server数据库管理系统的支持下编写出简单 的批处理应用程序。 一、数据库的创建与管理 数据库是SQL Server存放数据和数据对象的容器，一般可通过两种方法对 它进行管理：一是通过企业管理器所提供的可视化界面进行管理；二是使用 Transact SQL语句，通过查询 分析器进行管理。本节将以“教学管理”数据库 的创建和管理为例，介绍SQL Server数据库的创建、删除、更改以及日志文 件的使用等知识。 （一）创建数据库 1、使用企业管理器的方法 鼠标右击“数据库”子项“新建数据库”“数据库属性” 依次完成各选项 卡的设置单击 “确定” 2、使用Transact SQL 语句的方法 启动“查询 分析器”，然后输入CREATE DATABASE 教学管理 命令 （二）查看数据库信息 1、使用企业管理器的方法 鼠标右击“教学管理”“属性” 2、使用Transact SQL语句的方法 (1) sp_helpdb的功能及语法 主要用来查看服务器上的数据库信息，语法结构如下： sp_helpdb (2) sp_databases的功能及语法 主要用来查看当前服务器上所有可以使用的数据库，语法结构为： sp_databases (3) sp_helpfile的功能及语法 用来查看当前数据库中文件（数据文件和日志文件）的信息，若不 指定文件名，则返回所有文件的信息。语法结构如下： sp_helpfile 文件名 (4) sp_helpfilegroup 用来查看当前数据库的文件组相关信息，用法与sp_helpfile相同。语 法结构如下： sp_helpfilegroup 文件组名 (三) 修改数据库 1、使用企业管理器的方法 鼠标右击要修改的数据库名“属性”依次打开各选项 卡进 行修改 2、使用Transact SQL语句的方法 语法格式： alter database 教学管理 modify file (name=教学管理_data , maxsize=50, filegrowth=5 ) 3、数据库改名 语法格式： sp_renamedb 旧名，新名 （四）删除数据库 1、使用企业管理器 鼠标右键单击 要删除的数据库“删除” 2、使用Transact SQL语句 drop database 库名 （五）备份和恢复数据库 1、备份数据库 用鼠标右键单击 需备份的数据库名“所有任务”“备份 数据库”通过对话 框的操作完成备份工作 2、恢复数据库 同鼠标右键单击 需恢复的数据库名“所有任务”“还原 数据库”通过对话 框的操作可完成恢复工作 二、表对象的创建与管理 要想使数据库发挥 作用，还必须在其中建立各种各样的数据库对 象，如表、视图 、索引和存储过 程等，其中最重要的就是表。 （一）创建表 SQL Server每个数据库最多可存储20亿个表，每个表可以有1024列 。 1、使用企业管理器的方法 展开要创建表的数据库鼠标右击“表”“新建表” 在窗口中设计 修改击“保存”按钮输入表名击“确定” 2、使用Transact SQL语句的方法 第一步 打开库 方法是在下拉框中选择 “教学管理”数据库名，或者通过USE命 令打开 第二步 输入create table建表命令，并运行它，即可完成建立表的操 作。（二）查看与修改表结构 1、查看数据库中所包含的表 启动企业管理器，展开“教学管理”数据库后，点击“表”项，在右侧窗口中， 显示了所有表的信息。 注意：如果表格是在查询 分析其中通过create table命令创建的，并且企业管 理起已启动，“教学管理”数据库已处于展开状态，此时需用鼠标右建单击 “表” 项，在弹出菜单中选择 “刷新”命令，才可看到新建的表格。 2、查看表结构 双击“成绩表”可查看该表的结构信息。 3、修改表结构 用鼠标右建单击 “成绩表”“设计 表”在其中完成字段项的增、删 、改操作。 另外，使用Alter table命令也可完成表格修改工作，但在实际 工作中，建议 使用企业管理器来完成对表格结构的修改工作。 （三）查询 与维护 表数据 创建空表后，需要向其中添加记录 ，并对他们进 行增删改等维护 工作了。 对 于一个实际 的数据库应 用系统开发项 目而言，通常采用企业管理器完成初始、 、批量数据的增加和删改等维护 工作，而Transact SQL则主要用于客户端应用程 序与SQL Server数据库的交互工作，因此，这两种方法均需要掌握。 1、企业管理其主要通过记录维护 窗口完成数据的查询维护 工作，以“学生 表”为例，主要方法是：鼠标右击“学生表”“打开表”返回所有行 在其中可显示表中的记录 并完成对记录 的增加、删除、修改工作。 2、Transact SQL主要用过select、insert、delete、update命令完成记录 的查询 、添加、删除和修改操作。这四个命令均符合SQL标准语法规则 。 （四）删除表 1、通过企业管理器删除表格的方法 鼠标右击要删除的表格“删除” 或者直接在选中的表格上按下“Delete”键盘键 ，此时会弹出“表格删除”对 话框，点击“全部除去”按钮即可 2、通过Transact SQL的DROP TABLE命令也可以完成删除表格操作。 例如： use 教学管理 drop table 成绩表 注意：千万不要随意删除表格，特别是系统表。 （五）创建视图 视图 是SQL Server的重要对象之一，可以将它理解为一组存储在SQL Server数据库中，经过预 先编译 的Select语句。 1、在企业管理器中对视图进 行管理，与表的操作方法基本相同，均可通过 弹出菜单完成视图 的创建、打开、修改、删除等工作。 2、在查询 分析其中，主要使用create view、alter view、drop view和select语 句创建、修改、删除和查询视图 。 三、索引的创建与管理 SQL Server中引入索引(index)主要是为了提高查询 的效率，它能够加速 order by 和group by 字据的操作，保证数据的唯一型，并加快表连接的速度。 1、索引的概念和创建原则 索引就是一个指向表中数据的指针，是在基本表的列上建立的一种数 据库对 象，它和基本表分开存储，它的建立或撤销对 数据的内容毫无影响。 索引一经创 建，就完全由系统自动选择 和维护 ，不需要用户指定使用索引 ，也不需要用户执 行打开索引或进行重新索引等操作 在创建索引时，务必注意下列问题 ：P245 1-3 归纳 起来，在几种情况下不要创建索引：P246 1-7 （二）索引的类型 从两个方面分类：1、从列的使用角度可将索引分为单 列索引、唯一索引、 复合索引三类。2、 从是否改变基本表记录 的物理位置角度可分为聚集索引 和非聚集索引两类。 1、单列索引 是对基本表的某一单独的列进行索引，通常应对 每个基本表的主关键字 建立单列索引。 2、唯一索引 一旦在一个或多个列上建立了唯一索引，则不允许在表中相应的列上插 入任何相同的取值。 3、复合索引 是针对 基本表中两个或两个以上列建立的索引。 4、聚集索引 采用聚集索引会改变基本表中记录 的物理存储顺 序。 5、非聚集索引 采用非聚集索引，表中记录 的物理顺序与索引顺序不同，即表中的记 录仍按实际 插入的先后顺序排列，不按索引列排序。 （三） 聚集和非聚集索引的对比 1、存取速度 从建立了聚集所引得表中取出数据要比建立了非聚集所引得表快，但相 对而言，聚集索引会降低向表中插入、删除和修改数据的速度。 2、索引的数据 对聚索引的主要限制是每个表只能建立一个聚集索引，但是一个表可以 有不止一个非聚集索引。 3、所需空间 非聚集索引需要较多的硬盘空间和内存。 （四）创建和删除索引 1、使用企业管理器方法 （1）通过点击“钥匙”图标创 建，这种方式将在所选定的一个或多个列 上创建一个聚集索引，是表的主关键字索引。 （2）即使用索引管理菜单创 建、修改、撤销索引。 鼠标右击“所有任务”“管理索引” 2、使用Transact SQL创建索引 Transact SQL使用create index 命令创建索引，常用的语法结构如下： create unique clustered|nonclustered index on ( 次序 ，次序) 说明： unique:为表或视图创 建唯一索引（不允许存在索引值相同的两行）。 clustered:聚集索引。 nonclustered:非聚集索引，默认值 索引名：在实际 使用时，用户并不需要知道索引名，但在创建阶段 ，索引名应符合SQL Server的命名规则 ，并且在整个数据库中，索引名不能 重复。 例如：create unique clustered index myindex1 on 成绩表（学号，课程号） 3、使用Transact SQL删除索引 语法结构：drop index ,n 四、Transact SQL编程基础 使用Transact SQL编写的程序可以通过SQL Server提供的查询 分析器 运行，也可以嵌入到Visual Basic、Delphi、PowerBuilder、C语言中。 （一）程序注释语 句 注释：就是用一般人所熟悉的汉语 、英语等自然语言，对程序语言 进行说明。它不是可执行语句，不参与程序的编译 ，只是一些说明性的 文字，主要对程序代码的功能及实现 方式进行简要的解释和说明，以帮 助程序人员理解Transact SQL程序段。 SQL Server提供了两种形式的程序注释语 句，一种是使用“/*”和“*/” 括起来的可以连续书 写多行的注释语 句，一种是使用“-”表示的单行书 写注释语 句。 例如：P249-250 （二）变量 变量是程序设计 必不可少的部分。SQL Server支持两种形式的变量， 即局部变量和全局变量 1、局部变量 局部变量是作用域局限在一定范围内的Transact SQL对象。可以理 解为一个局部变量的使用范围局限于批处理内，即两个GO之间的部 分。 （1）声明局部变量 语法：declare 变量名 数据类型 ,n 主要参数的含义是： 变量名：必须以打头，必须符合SQL Server表是符的命名规则 。 数据类型：可以使用除了text、ntext和image类型以外的所有的系统 数据类型和用户定义数据类型。 例如：declare myint int, mychar char(8) （2）为局部变量赋值 为局部变量赋值 有两种方式，一种是使用select语句，一种是使用set语句 。 语法如下： 1）使用select赋值 select 变量名=表达式 若表达式中需要用到表中字段的值，那么select赋值语 法也可以写成： select 变量名=表达式 from 表名, where 条件表达式 注意：select的查询 功能和赋值 功能不可以同时使用。 例 P251 9-4 2）使用SET赋值 一条SET语句只能给一个变量赋值 ，SET命令可以在UPDATE语句中使用 。 例 P252 9-5、9-6 2、全局变量 全局变量通过在名称前保留两个符号()区别于局部变量。 （三）程序结构 1、ifelse条件结构 ifelse常见的语法结构 if 逻辑 表达式 单条语句或者 BEGINEND语句组 else 单条语句或者 BEGINEND语句组 一种常用的ifelse语句逻辑 判断结构是： if NOT EXISTS (SELECT语句) Transact SQL 语句组 else Transact SQL 语句组 例 9-7 根据学号在学生表中查找该学生的信息，如果该学生属于软件专业 ，则 显示他的成绩，否则显 示“学号为 的学生不属于软件专业 ”。 use 教学管理 go declare myno char(8) set myno=“20030001” if (select 专业 from 学生表 where 学号=myno)=“软件” begin select avg(成绩) as 平均成绩 from 学生表 where 学号=myno end else print 学号为+myno+的学生不存在或不属于软件专业 go 2、case 多重分支结构 结构一： case when 逻辑 表达式1 then 返回结果1 when 逻辑 表达式2 then 返回结果2 else 返回结果n end 结构二： case 表达式 when 表达式的值1 then 返回结果1 when 表达式的值2 then 返回结果2 else 返回结果n end 3、while 循环结 构 while 逻辑 表达式 begin Transact SQL语句组 break continue end 例 9-11 4、return 返回语句 return 整数表达式 在批处理、语句块或存储过 程中，使用return语句可以立即从当前程序结构 中退出，return后面的语句将不会被执行。 （四）游标 1、游标的概念及用途 当用户需要对数据集合中的每一行进行“个性化”的操作时，就需要用到游 标了。游标提供了一种在服务器内部处理结果集的方法，具有类似于C语言指 针一样的结构，可以识别 一个数据集合内指定的行，从而可以有选择 地按行操 作。 P258 9-12 2、声明游标 declare 游标名 insensitivescroll cursor for select 语句 for read only|update of 列名1，列名2,n Insensitive: 使用这个参数定义游标会把提取出来的记录 集放在一个在Tempdb数 据库里创建的临时 表里。任何通过这 个游标进 行的操作，都在这个临时 表里进 行。 Scroll: 使用该参数定义的游标，可以包括以下所有的取数功能： first 、last、prior、next、relative、absolute Read only: 声明只读游标，不允许通过只读游标进 行数据的更新操作。 Update: 定义游标内可更新的列 例 P260 9-13、9-14 3、打开游标 在使用游标之前，必须打开它。 open 游标名 填充结果集 4、关闭游标 close 游标名 释放与游标关联的当前结果集 5、释放游标 deallocate 游标名 释放游标所使用的资源。 6、使用游标取数 fetch next|prior|first|last|absoluten|nvar|relativen|nvar from 游标名 into 局部变量1，局部变量2,n Into : 允许将提取的列数据放到局部变量中。 N或nvar : 表示游标相对于作为基准记录 的偏离位置。 7、两种与游标有关的系统全局变量 fetch_status: 返回被fetch语句执行的最后游标的状态，返回值的意义是 ：0表示fetch语句成功；-1表示fetch语句失败或次行不再结果集中；-2标示被提 取的行不存在。 8、利用游标修改数据 在声明时要使用update关键字。同时使用“for update of”关键字指明可更新 的列。常用的更新语法有： （1）更新操作 update 表名 set 列名=表达式,n where current of 游标名 （2）删除操作 delete from 表名 where current of 游标名 例：P260 9-13、9-14、P262 9-15 （五）事务 1、事务的作用 事务是并发控制的基本单位，它反映了现实 世界中需要一个完整的单位 提交的一项工作。将逻辑 相关的一组操作捆绑在一起，以便服务器保持数据 的完整性。 例 9-16、9-17 2、事务处 理控制语句 SQL Server中可通过以下三个语句完成事务控制： （1）开始一个事务：begin transaction 事务名 （2）提交一个事务：commit transaction 事务名 （3）回滚一个事务：rollback transaction 事务名 在数据库应 用程序设计 中，事务控制语句的一般用法是： 第一步：Begin tran 事务名； 第二步：对数据库进 行增、删、改等操作 第三步：提交事务或回滚。 例：P264 9-18 （五）存储过 程 1、存储过 程的作用 存储过 程是一组预 先编译 好的Transact SQL代码，可以作为一个独立的数 据库对 象被用户使用，被应用程序直接调用。 2、创建存储过 程 (1) create procedure 存储过 程名 with recompile|encryption|recompile,encryption as sql语句 n 参数说明： recompile：表明SQL Server不会缓存该过 程的计划，该过 程将在运行时 重新编译 。 encryption：存储过 程作为数据库对 象将在syscomments表中留下完整的 代码等信息。使用了该参数后，将对访问这 些数据的入口进行加密。 sql语句：利用Transact SQL编写的程序。 (2)使用企业管理器创建 例如：9-19 在教学管理数据库中创建一个名为“user-proc1”的存储过 程，它只 包含一个简单 的select语句。 use 教学管理 go create proc user_proc1 as select * from 学生表 go 3、执行存储过 程 如果对存储过 程的调用是批处理的第一条语句，则可直接使用存储过 程 的名字调用该存储过 程，比如： use 教学管理 go user_proc1 go 否则，使用execute或exec关键字完成调用。 Exec user_proc1 4、修改存储过 程 alter procedure 存储过 程名 with recompile|encryption|recompile,encryption as sql语句 5、删除存储过 程 (1) drop procedure 存储过 程名1，存储过 程名2 (2)使用企业管理器删除 (六) 触发器 触发器是一种特殊的存储过 程，它基于一个表的创建，但可以针对 多个表进 行操作，与表紧密相连，可以看作是表定义的一部分，主要用来保证数据的完 整性。 在SQL Server中一张表可以有多个触发器，用户可以针对 update 、delete、 insert语句分别设 置触发器，那么当用户进 行update、insert、delete等数据维护 操 作执行后，这些事先定义好的触发器对象就会被“触发”，并按事先定义好的规 则自动执 行。 1、创建触发器 (1) create trigger 触发器名 on 表名 with encryption for delete,insert,updatenot for replication as sql 语句 return 参数说明： with encryption：使用该参数对访问 syscomments表的入口进行加密。 not for replication：表示当赋值进 程更改触发器所涉及的表时，不应执 行该触发器。 (2) 使用企业管理器 展开“教学管理”右击“学生表”选“所有任务”“管理 触发器”输入语句击“检查语 法”“确定” 2、删除和修改触发器 Transact SQL使用drop trigger和alter trigger 语句来删除、修改触发器。 drop|alter trigger 触发器名 例如： use 教学管理 go create trigger 学生更新表 on 学生表 for update as declate msg varchar(20) select msg=str(rowcount)+个记录 被更新 print msg return 七、数据库完整性管理 数据的完整性是指存储在数据库中的数据的正确性和相容性。设计 数据库 完整性的目的是为了防止数据库存在不符合语义 的数据，防止错误 信息的输 入和输出。SQL Server提供的用来实施数据完整性的途径主要是约束 (Constraint)、标识 列(Identity Column)、默认(Default)、规则 (Rule)、触发器 (Trigger)、数据类型(Data Type)、索引(Index)和存储过 程(Stored Procedure)等 1、使用约束实施数据的完整性 约束的用途是限制用户输 入到表中的数据的值的范围，一般分为列级约 束和表级约 束两种。要向浏览 某张表格上所有约束的信息，可以使用存储过 程： sp_helpconstraint 表名 (1) primary key 约束 特征： a、创建primary key 约束时，SQL Server 会自动创 建一个唯一的聚集索 引； b、定义了primary key 约束的字段的取值不能重复，并且不能取null值； c、每个表只能定义一个primary key 约束； d、如果表中已经有了聚集索引，那么在创建primary key约束之前，要么 指定所创建的是非聚集索引，要么删除已有的聚集索引。 例 9-21 创建表级primary key 约束。 create table 学生表 (学号 char(8) not null, 姓名 char(8) null, constraint pk_学生表 primary key (学号) 例 9-22 创建列级 primary key 约束 create table 学生表 (学号 char(8) not null, constraint pk_学号 primary key nonclustered, 姓名 char(8) null) (2) Foreign Key约束 特征： a、一旦Foreign Key约束定义了某个字段，则该 字段的取值必须参照 (Reference)同一表或另一表中的Primary Key 约束或Unique约束。 b、Foreign Key 约束不