SQL Server基础（四）

天堂1111

　　1、索引：是一个独立的，物理的数据库结构，一般是与表或视图中的列相关联，数据库用户可以利用索引快速地访问数据库表中的数据信息
　　通常情况下，只有需要经常查询索引列中的数据时才在表上创建索引。因为索引的使用将占用磁盘空间，并且降低添加，删除和更新行的速度
　　2、使用索引的场合：
　　（1）创建索引的原因及注意事项
　　加快有联接表、排序或分组操作的查询速度
　　创建惟一索引，可强制值的惟一性
　　索引最好创建在具有高度选择性的列上
　　（2）不创建索引的原因及注意事项
　　在创建了索引的列上修改数据，将自动更新相关联的索引
　　维护索引需要时间和资源
　　（3）下列情况适合建立索引
　　经常被查询搜索的列，如where子句中的列
　　在Order  By子句中使用的列
　　外键或主键列
　　值惟一的列
　　常以排序形式访问的列
　　（4）下列情况不适合建立索引
　　在查询中很少被引用的列
　　包含很多重复值的列
　　数据类型为bit、text、image的列
　　3、创建唯一索引：惟一索引确保索引列上的数据都是惟一的，不包含重复值
　　语法：在 CREATE INDEX 语句中使用 UNIQUE 选项
　　但定义了 PRIMARY KEY 约束和 UNIQUE 约束的列会自动创建惟一索引
　　唯一索引：关键字为unique,唯一索引表示列中不包含重复的值，每个值都是唯 一的。当向具有唯 一索引的表插入数时，数据库会检查新添加的行在该 列中是否具有重复值。如果有重复值，数据拒绝插入
　　Create unique index 索引名称
　　On 表名(字段名)
　　Create unique index 索引名称
　　On 表名(字段1，字段2)        //组合起来的唯一性
　　4、聚集索引：关键字为clustered,聚集索引表示将数据行的键值在表内排序并存储对应的数据记录，使得表的物理顺序与索引顺序一至。
　　Create unique clustered  index 索引名称
　　On 表名(字段名)
　　Create unique clustered  index 索引名称
　　On 表名(字段名 DESC)
　　5、两种非聚集索引：建于堆上和建于聚集索引上
　　使用非聚集索引
　　若未指定索引类型，则默认为非聚集索引
　　叶节点页的次序和表的物理存储次序不同
　　每个表最多可以有249个非聚集索引
　　非聚集索引最好在具有高选择性的列上创建
　　在非聚集索引创建之前创建聚集索引
　　非聚集索引：关键字为nonclustered,非聚集索引表示数据存储在一个位置，索引存储在另一个位置，索引通过指针指向数据的存储位置。
　　Create unique nonclustered  index 索引名称
　　On 表名(字段名)
　　6、查询索引：exec sp_helpindex 表名
　　删除索引：drop index 表名.索引名
　　7、视图：是一种逻辑对象，它是从一个或几个基本表中导出来的表，是一种虚拟表。
　　视图的内容可以是：
　　（1）基表中列的子集或行的子集
　　（2）两个或多个基表的联合
　　（3）基表的统计汇总
　　（4）其他视图的子集
　　（5）视图和基表的混合
　　视图的优点：
　　对用户只显示特定数据，限制对敏感数据的访问
　　屏蔽数据库复杂性，简化复杂的查询
　　简化用户权限管理
　　改进性能
　　组织数据以便导出到其他应用程序
　　8、创建视图
　　（1）视图定义的限制
　　CREATE VIEW 语句不能包括 COMPUTE 或 COMPUTE BY 子句，也不能包括 INTO 关键字
　　仅当使用 TOP 关键字的时候，CREATE VIEW 语句才能包括 ORDER BY 子句
　　视图不能引用临时表
　　视图不能引用超过1024列
　　在单一批处理中，CREATE VIEW 语句不能和其他 Transact-SQL 语句组合使用
　　（2）创建视图
　　使用 SELECT 语句指定视图的内容
　　创建视图时，将检查视图定义语句中所有引用的数据库对象是否都存在
　　命名遵守标识符的命名规则，拥有者是可选的
　　建议使用一个一致的命名约定来区别表和视图
　　执行 CREATE VIEW 语句的用户必须是 sysadmin、db_owner 或 db_ddladmin 角色的成员，或具有 CREATE VIEW 权限
　　在视图中引用的所有表和视图上，也要拥有 SELECT 权限
　　例：use pubs
　　create view name_vie
　　as
　　select fname,lname from employee
　　select * from name_vie
　　9、修改视图包括插入，修改，删除三个方面的操作
　　更新数据时必须满足下列条件：
　　使用insert语句进行插入操作的视图必须能够在基表中插入数据，否则插入操作会失败。
　　如果视图上没有包括基表中所有属性为not null的行，那么插入操作会失败
　　如果在视图中包含使用统计函数的结果，或者是包含多个列值的组合，则插入操作不成功。
　　不能在使用了distinct,group by 或having的语句的视图中插入数据
　　如果创建视图的create view 语句中使用了with check option，那么所有对视图进行修改的语句必段符合with check option中限定的条件
　　对由多个基表连接而成的视来说，一个插入操作只能作用在一个基表上。
　　10、更改视图：更改其定义，但不影响相关的存储过程或触发器，也不更改权限，和 CREATE VIEW 的限制相同，保留分配的权限，使用新的 SELECT 语句和选项代替原来的定义

　　>　　select语句 [WITH CHECK OPTION]
　　11、删除视图：从数据库中删除视图的定义和所有分配给它的权限
　　DROP VIEW {视图名} [,...n]
　　12、索引视图：结果集保留在数据库中并建立了索引以供快速访问的视图
　　创建索引视图
　　在视图上实现一个惟一聚集索引
　　创建惟一聚集索引之后，可以在视图上创建其他索引
　　索引视图自动反映在基表上进行的数据修改
　　数据改变的时候，惟一聚集索引自动更新
　　创建索引视图的指导方针
　　查询优化器自动确定给定查询是否能从使用索引视图中受益
　　使用索引优化向导确定在何处使用索引视图
　　索引视图使用场合
　　对改进检索结果速度的性能提升的要求超过维护代价的增长
　　底层的数据很少更新
　　查询进行了大量的联接和聚合操作，需要处理很多数据行或被很多用户频繁调用
　　创建索引视图的限制
　　视图上创建的第一个索引必须是惟一聚集索引
　　创建视图时必须使用 SCHEMABINDING 选项
　　视图可以引用基表，但不能引用其他视图
　　引用表和用户定义函数的时候，必须使用两部分名称
　　随后使用索引视图的连接必须具有相同的选项设置
　　例：Use pubs
　　create view dbo.MM
　　WITH SCHEMABINDING
　　as
　　select emp_id,fname,lname from dbo.employee
　　CREATE UNIQUE CLUSTERED  INDEX  JJJ
　　on dbo.MM(emp_id)
　　13、事务：是指一系列的操作，是单个的工作单元，如果某一事务成功，则在该事务中进行的所有数据修改均会提交，成为数据库中的永久组成部分，如果事务遇到错误且必须取消或回滚，则所有数据修改均被清除
　　显示事务：BEGIN TRANSACTION ：标记显式连接事务的起始点
　　COMMIT TRANSACTION 或 COMMIT WORK ：如果没有遇到错误，可使用该语句成功地结束事务，该事务中的所有数据修改在数据库中都将永久有效，事务占用的资源将被释放
　　ROLLBACK TRANSACTION 或 ROLLBACK WORK ：用来清除遇到错误的事务，该事务修改的所有数据都返回到事务开始时的状态，事务占用的资源将被释放
　　例：begin transaction
　　begin try
　　insert into stu values('Jack1',20,1)
　　insert into stu values('Jack2',2,1)
　　commit transaction
　　end try
　　begin catch
　　print '错误'
　　rollback transaction
　　end catch
　　select * from stu
　　14、存储过程：是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。存储过程可包含程序流程、逻辑和对数据库的查询。他们可以接受参数、输出参数、返回单个或多个结果集以及返回值。
　　存储过程的使用：
　　存储在服务器上的 Transact-SQL 语句的命名集合
　　是封装重复性任务的方法
　　支持用户声明变量、条件执行以及其他编程特性
　　接受输入参数
　　向调用过程或批处理返回状态值
　　以输出参数的形式将多个值返回至调用过程或批处理
　　创建：
　　先分析该过程中的语句以检查语法的准确性
　　将存储过程的名字存入当前数据库的 sysobjects 系统表
　　将存储过程的文本存入当前数据库的 syscomments 系统表
　　延迟名称解析
　　执行（初次或重新编译时）：
　　执行，查询处理器读入存储过程并处理
　　检测变化并自动重新编译
　　15、流程控制语句：
　　（1）IF……ELSE……
　　例：use pubs
　　declare @pic float

　　select @pic=sum(price) from >　　if @pic>500
　　begin
　　print 'more 500'
　　end
　　else
　　begin
　　print 'less 500'
　　end
　　（2）Case 条件
　　when   then
　　……
　　else
　　end
　　例：use northwind
　　go
　　select top 10 orderid,orderid %10 as 'Last Digit',Position=
　　Case orderid %10
　　when 1 then 'First'
　　when 2 then 'Second'
　　when 3 then 'Third'
　　when 4 then 'Fourth'
　　Else 'Something Else'
　　end
　　from orders
　　（3）while循环
　　例：declare @i int
　　set @i=1
　　while @i　　as
　　select au_lname,au_fname from authors
　　--删除存储过程
　　drop proc Mypro