如何在SQL-server使用索引和视图

1、索引
建立快速查询的引用机制，将频繁进行查询的数据建立索引，放入内存，并排序，通过排序特征，进行快速查询。

2、索引分类
唯一索引：当字段建立了唯一性约束时，创建的索引。
主键索引：当字段建立了主键约束时，会自动建立主键索引。
聚集索引：一个普通字段，建立索引后默认为聚集索引，按物理存储顺序排序。
非聚集索引：物理存储顺序与排序顺序不同。
复合索引：将多个列组合成的索引
全文索引：对于字符串较多的字段，建立一种特殊的标记功能。

3、创建索引
create index 索引名 on 表名(字段名)
create index inname on dang_an(name)
查看索引：
exec sp_help 表名
exec sp_help dang_an

创建索引（聚集索引）
create index in_name on dang_an(name)

删除索引：
drop index 索引名 on 表名；
drop index inname on dang_an

4、view视图
视图是一张虚拟表，没有表结构，它的数据和表结构来源于查询语句。
作用：
1）.简化命令代码应用。
2）.提高代码安全性。
3）.提供了更多的查询规则。

创建视图
create view 视图名
as
查询语句

例：创建一个视图，查看dang_an表中的一部分字段
create view vi_dang_an
as
select id,name,sex,age
from dang_an

查看所有视图名称
exec sp_tables

查看某个视图数据
select * from vi_dang_an

例：再创建一个视图，将多个表的内容显示出来
create view vi_gongzi
as
select dang_an.id,dang_an.name,zhiwei.zw,gongzi.nian,gongzi.yue,gongzi.gongzi
from dang_an,gongzi,zhiwei
where dang_an.id=gongzi.id and
dang_an.zw_id=zhiwei.zw_id

select * from vi_gongzi

统计所有人七月份工资
select * from vi_gongzi where yue=7

例：查询每个人的平均工资
select name,avg(gongzi) from vi_gongzi
group by name

也可以和别的表一起用
想在vi_gongzi后面追一个考核（kaohe.kaohe）
select vi_gongzi.*,kaohe.kaohe from vi_gongzi,kaohe
where vi_gongzi.id=kaohe.id and
vi_gongzi.nian=kaohe.nian and
vi_gongzi.yue=kaohe.yue

能不能把上面的语句再做成一个视图，可以。
create view vi_aaa
as
select vi_gongzi.*,kaohe.kaohe from vi_gongzi,kaohe
where vi_gongzi.id=kaohe.id and
vi_gongzi.nian=kaohe.nian and
vi_gongzi.yue=kaohe.yue

select * from vi_aaa

5、修改视图中的数据
1）.视图中的数据依赖于基本表。视图只引用了一张基本表，且基本表中的非空字段都引用到视图中时，可以对视图修改数据。
2）、当视图引用多张表时，无法添加和修改，删除数据。
3）.当视图中使用了聚合函数，并得到数据时，无法进行添加，修改，删除数据。

修改视图格式：
alter view 视图名
as
查询语句

例：创建视图，并查找显示性别为男的所有数据
create view vi_da1
as
select id,name,sex,age,jg_id,zw_id
from dang_an
where sex=‘男’

分析：现在有了条件语句where。能不能添加数据？
可以。
添加数据：
insert into vi_da1 values(12,‘张柏芝’,‘女’,18,1,1) →可以添加，只是不显示
select * from vi_da1

删除视图（不建议删。被引用了之后可能会影响其他视图。sql server会提示你）
drop view 视图名

修改视图名（和删除一样，不建议修改）
exec sp_rename 旧视图名,新视图名

exec sp_rename vi_da1,vi_bbb

6、存储过程
是一种特殊的数据库命令，能够快捷，简便的实现某些功能。
7、分类
系统存储过程：由数据库管理器创建的存储过程，实现对数据库管理器，数据库对象的操作。
自定义存储过程：用户自行编辑的，对于指定的表或库进行快速操作的存储过程
8、作用
简化开发的代码量
代码传送更安全
能够节省更多的流量
使用变量构建存储过程，使用时更灵活

格式：
create procedure 存储过程名
as
T-SQL语句（增删改查）

例：创建一个存储过程，查询dang_an表中的信息。
create procedure proc_dang_an
as
select * from dang_an

查看存储过程（名）
exec sp_stored_procedures

执行存储过程（显示proc_dang_an中的内容）
exec proc_dang_an

例：查询年龄大于、等于30岁的员工，做成存储过程
create procedure proc_dang_an1
as
select * from dang_an where age>=30

exec proc_dang_an1

例：查询职位为销售，7月的工资
create procedure pro_xhgongzi
as
select dang_an.name,gongzi.gongzi,zhiwei.zw,gongzi.nian,gongzi.yue
from dang_an,zhiwei,gongzi
where dang_an.id=gongzi.id and
dang_an.zw_id=zhiwei.zw_id and
zhiwei.zw=‘销售’ and
gongzi.yue=7

exec pro_xhgongzi

9、变量
使用一段字符串，替代未知或不确定的数值。
格式
create procedure 存储过程名
@变量名1 数据类型，
@变量名1 数据类型 output
as
T-SQL语句

例：查询指定职位，指定月份的员工工资信息
create procedure pro_gz
@zw1 char(20),
@yue1 int
as
select dang_an.name,gongzi.gongzi,zhiwei.zw,gongzi.nian,gongzi.yue
from dang_an,zhiwei,gongzi
where dang_an.id=gongzi.id and
dang_an.zw_id=zhiwei.zw_id and
zhiwei.zw=@zw1 and
gongzi.yue=@yue1

exec pro_gz ‘销售’,7
exec pro_gz ‘运维’,6

查看存储过程的后台代码
exec sp_helptext pro_gz

例：创建一个gongzi表添加数据的存储过程
create procedure proc_instgz
@id1 int,
@nian1 int,
@yue1 int,
@gongzi1 int
as
insert into gongzi values(@id1,@nian1,@yue1,@gongzi1)

插入数据
exec proc_instgz 1,2017,8,8600
查看数据（查看的是物理表，因为语句定义时是插入gongzi数据）
select * from gongzi where id=1

例：修改年龄的存储过程
create procedure pro_upage
@age1 int,
@name1 char(10)
as
update dang_an set age=@age1 where name=@name1

exec pro_upage 28,‘李晨’

删除数据的存储过程
create procedure pro_del
@id1 int
as
delete from dang_an where id=@id1

exec pro_del 1

修改存储过程
alter procedure 存储过程名
as
T-SQL语句

删除存储过程
drop procedure 存储过程名

10、触发器
是一种特殊的存储过程，对指定的表或库进行更详细的控制。对指定的动作进行触发任务

11、分类
1）、DML触发器
insert触发器：当给数据表中添加数据时触发
update触发器：当给数据表中修改数据时触发
dalete触发器：当删除表中数据时触发

格式：
create trigger 触发器名
on 表明 //只能对一张表创建触发器
for 动作 //intsert，update，delete
as
控制语句 //指定触发后的操作，可以对其他表操作

2）、DDL触发器：
alter触发器：修改库时修改
drop触发器：删除库时修改

格式：
create trigger 触发器名
on database //对数据库操作（当前数据库）
for create,alter,drop
as
rollback

临时表：
inserted表：存放新添加的数据或修改后的数据
deleted：存放新删除的数据或修改前的数据

例：查看inserted表和deleted表
create trigger tri_1
on dang_an
for insert,update,delete
as
print ‘inserted表’
select * from inserted
print ‘deleted表’
select * from deleted

查看触发器的信息：exec sp_help tri_1
查看触发器语句：exec sp_helptext tri_1

测试插入数据，查看inserted和deleted的变化
insert into dang_an values(14,‘张三’,‘男’,65,‘1990/02/25’,6,6,6)

例：给dang_an表添加数据时，同时在dang_an_b中添加相同数据
create trigger ti_2
on dang_an
for insert
as
insert into dang_an_b select * from inserted

例：删除dang_an表数据时，同时在dang_an_b中删除相同数据
create trigger ti_3
on dang_an
for delete
as
insert into dang_an_b select * from deleted

例：修改dang_an表中的数据时，将被修改的数据添加到dang_an_b中
create trigger ti_4
on dang_an
for update
as
insert into dang_an_b select * from inserted

例：设置禁止删除gongzi表中的数据

create trigger ti_5
on gongzi
for update,delete
as
begin
raiserror(‘禁止动作’,15,4)
rollback
end

修改触发器
alter trigger 触发器名
for 动作
as
执行语句

删除触发器
drop trigger 触发器名

12、事务
指业务上的一个逻辑单元，能够保证数据所有的操作那么全部成功，那么全部失败。

13、那些情况会执行事务
1、显示提交（commit）：遇到commit指令时，将结束事务病保存所有更改的数据
2、显示回滚（rollback）：取消更改
3、DML语句：自动/隐式提交
4、正常结束的程序：自动提交
5、非正常结束的程序：隐式回滚

14、事务的特点：
1）、原子性：
整个事务所有的步骤是不可分割的，原子性的原则规定一个事务的各个步骤都必须完成，否则整个事务都不会完成。
2）、一致性：
无论是事务前，事务中，事务后，数据始终处于一致的状态。
3）、隔离性：
未完成的事务必须不可视。在某个事务进行期间，只有执行该事务的会话能看见所做的变化（而不是更改后的新值）。
持久性：
如果事务提交成，则数据修改永远生效。如果回滚，则数据完全没有修改。

语句：
1）开始事务
语法如下：
begin transaction
这个语句显式地标记一个事务的起始点。

2）提交事务
语法如下：
commit transaction
这个语句标志一个事务成功结束。自事务开始至提交语句之间执行的所有数据更新将永久保在数据库数据文件中，并释放连接时占用的资源。

3）回滚（撤销）事务
语法如下：
rollback transaction
清除自事务起点至该语句所做的所有数据更新操作，将数据状态回滚到事务开始时的起点，并释放由事务控制的资源。

15、锁：
作用：在多用户环境下保证数据库完整性和一致性。
类型：
共享锁S：都不能改，至能读。针对远程
排他锁X：授权后可以修改。未授权的用户等待授权用户的排他锁释放后，并授权才能使用。
更新锁U：可以更新但不可以删除

查看锁的类型：
exec sp_lock

16、死锁形成的条件：
1)、互斥条件
2)、请求和等待条件
3)、不剥夺条件
4)、环路等待条件
13、预防死锁
1)、破坏互斥条件
2)、破坏请求和等待条件
3)、破坏不剥夺条件