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