数据库简单应用.ppt

第九章 SQL Server数据库简单应用,本章内容,数据库的创建与管理表对象的创建与管理索引的创建与管理Transact SQL编程基础存储过程触发器数据库完整性管理SQL Server数据库安全性管理,9.1 数据库的创建与管理,数据库是SQL Server存放数据和数据对象的容器，一般可通过两种方法对它进行创建、查看、修改、删除、备份、恢复：1、通过企业管理器所提供的可视化界面进行管理；2、使用Transact SQL语句，通过查询分析器进行管理3、备份和恢复是维护数据库安全性和完整性的管理方法，其语句为：BACKUP DATABASE 和 RESTORE DATABASE。,9.2 表对象的创建与管理,SQL Server可存储20亿个表，每个表可以有1024列NULL(空值)的概念表中一旦有了记录,字段类型有时不能修改，除非删除字段，再重新建立。若要增加一列，必须保证该列允许为null值，否则无法成功。利用企业管理器、查询分析器创建表查看与修改表的结构，查询与维护表数据，删除表创建视图,9.3 索引的创建与管理,索引好象一本书的目录，能够提高数据搜索速度。索引是在基本表的列上建立的一种数据库对象，它和基本表分开存储，它的建立和撤消对数据的内容没有影响。当索引建立后，它便记录了被索引列的每一个取值在表中的位置。当表中加入新的数据时，索引中也会增加相应的数据项。当对数据库中的基本表建立了索引，进行数据查询时，首先在相应的索引中查找，如果找到了则返回该数据库在基本表中的确切位置，再从基本表中取出全部记录值。,9.3 索引的创建与管理,索引一旦建立，完全由系统自动选择和维护，因此，创建索引时注意以下问题：1、基本表的记录数量越多,记录越长,越有必要创建索引，记录小时，创建索引意义不大2、索引是为了加快查询的速度,但同时会影响插入/删除/修改的速度索引通常可建立在WHERE、ORDER BY、GROUP BY子句中出现频率高的数据列上以下情况尽量不要创建索引P246,9.3 索引的类型,从列的使用角度可将索引分为1、单列索引：对单独一列建立的索引，一般对主关键字2、惟一索引：不允许在表中相应列上插入任何相同的取值3、复合索引：在两个以上列建立的索引从是否改变记录的物理位置分为1、聚簇索引：会改变基本表中记录的物理存储顺序，每个表只能建立一个，会降低插入、删除、修改数据的速度2、非聚簇索引：不改变基本表中记录的物理存储顺序，需要较多的磁盘空间,9.3 索引的创建和删除,使用企业管理器：1、在创建或设计表时2、所有任务-管理索引使用Transact SQL语句1、创建 Create unique clustered nonclustered index 索引名 on 表名 列名12、删除 Drop index 索引名,9.4 Transact SQL编程基础,程序注释语句1、方法1:：/*注释*/，多行2、方法2:：-注释，单行局部变量(其范围局限于批处理命令内)1、声明方法:DECLARE 变量名 数据类型2、参数说明：变量名必须以开头，必须符合标识符的命名规则；数据类型可以使用除了text/ntext/image以外的所有系统数据类型和用户自定义类型3、赋值方法a、select 变量名=表达式，select语句的查询功能和赋值功能不能同时使用，否则会出错b、set 变量名=表达式，在update语句中使用赋值语句时，必须严格匹配数据类型,9.4 Transact SQL编程基础,全局变量：全局变量用于记录服务器活动状态的一组数据，在7.0以后版本中，全局变量成了一种特殊形式的函数，通过在名称前保留两个符号（）区别于局部变量。connections 变量返回自上次启动以来连接或试图连接的次数select getdate()as 当前日期和时间 connections as 登录次数,9.4 程序结构,条件分支（IFELSE，CASE多重分支结构）循环（WHILE）顺序每个语句块的开始和结束标记为：BEGINENDRETURN返回语句理解例9-7至例9-11,9.4.4 游标,游标的作用:1、SQL命令是面向集合的2、如果需要针对记录进行“个性化”操作,则需要游标。3、游标提供了一种在服务器内部处理结果集的方法，可以识别一个数据集内指定的行，从而可以有选择地按行操作,9.4.4 游标,声明游标:DECLARE 游标名CURSORFOR select语句FOR打开游标OPEN 游标名关闭游标CLOSE 游标名,9.4.4 游标,释放游标:DEALLOCATE 游标名使用游标取数FETCHFROMINTO两个与游标操作有关和系统全局变量FETCH_STATUSCURSOR_ROWS,9.4.4 游标,利用游标修改数据:1、必须将游标声明为可更新的游标2、不能使用read only关键字3、要使用for update of 指明可更新的列4、更新操作update 表名 set 列名=表达式,where current of 游标名5、删除操作delete from 表名 where current of 游标名,9.4.5 事务,事务的作用：保持数据完整性。事务的处理控制语句开始一个事务（BEGIN TRAN）提交一个事务（COMMIT）回滚一个事务（ROLLBACK）在数据库应用程序设计中，事务控制语句的一般用法是第1步：BEGIN TRAN 事务名；第2步：对数据进行增、删、改等操作第3步：提交事务或回滚。基本逻辑是：如果没有发生异常情况，则通过COMMIT语句提交事务，确认第2步操作；否则执行ROLLBACK回滚命令撤消第2步操作,9.5 存储过程,存储过程：是一组预先编译好的Transact SQL代码，可以作为一个独立的数据库对象被用户使用，被应用程序直接调用。存储过程的好处有执行速度快模块化的程序设计减少网络流量保证系统的安全性,9.5 存储过程,创建存储过程的方法1、Transact SQL语法create proc 存储过程名as sql语句2、利用企业管理器,9.5 存储过程,执行存储过程1、如果对存储过程的调用是批处理的第一条语句，则可直接使用存储过程的名字调用该存储过程2、否则：EXECUTE/EXEC 存储过程名,9.5 存储过程,修改存储过程1、使用ALTER PROC语句2、通过“属性”窗口删除存储过程 1、使用DROP PROC语句2、通过右键菜单,示例,创建一个存储过程XXK2，显示出AAA库中每个学生的学号、姓名、专业等信息及选课门数,create procedure xxk2 as begin select x.学号,x.姓名,x.专业,count(*)as 门数 from students x,score y where x.学号=y.学号 group by x.学号,x.姓名,x.专业 end,9.6 触发器,触发器是一种特殊的存储过程,可以看做是表的一部分。它基于一个表而创建,但可以针对多个表进行操作。主要作用是:保证数据的完整性。主要针对UPDATE、DELETE、INSERT语句进行设置。,9.6 触发器,创建触发器1、Transact SQL语句create trigger 触发器名 on 表名for as sql语句2、利用企业管理器,9.6 触发器,删除、修改触发器1、Transact SQL语句DROP TRIGGER 触发器名ALTER TRIGGER 触发器名on 表名2、利用企业管理器,9.7 数据库完整性管理,数据完整性是指存储在数据库中的数据的正确性和相容性，以防止不符合语义的数据进入数据库。SQL Server实施完整性的途径主要有:约束(Constraint)标识列(Identity Column)默认值(Default)规则(Rule)触发器(Trigger)数据类型(Data Type)索引(Index)存储过程(Stored Procedure)考试重点表9-3(P271),9.7.1 约束(constraint),定义：在创建表时定义，与表共存亡用途：限制用户输入到表中的数据的值的范围分类：列级约束、表级约束查看：sp_helpconstraint 表名种类：Primary Key,Foreign Key,Unique,Check删除：alter table 表名 drop constraint 约束名,9.7.2 规则(rule),规则：单独存储的数据对象，对列的值进行规定和限制。当用户向绑定有规则的数据列中insert和update数据时，规则会检查修改值的完整性步骤：先创建规则，再将规则与某个列绑定，不再使用时删除它,9.7.2 规则(rule),使用Transact SQL创建和删除规则1、创建create rule 规则名 as 条件表达式2、删除drop 规则名使用Transact SQL绑定规则sp_bindrule 规则名,表名.列名使用Transact SQL解除绑定规则sp_unbindrule 表名.列名使用企业管理器管理规则,9.7.3 默认(default),使用Transact SQL创建默认create default 默认名 as 默认值使用Transact SQL绑定默认sp_bindefault 默认名,表名.列名使用Transact SQL解除绑定默认sp_unbindefault 表名.列名使用Transact SQL删除默认drop 默认名使用企业管理器管理默认,9.8 SQL Server数据库安全性管理,