查询优化: 顾名思义就是创建索引, 视图等方式使数据库快速查找到需要的东西
索引分为:
唯一索引, 主键索引, 聚集索引, 非聚集索引, 复合索引, 全文索引.
在 SQL Server 中, 一个表只能创建一个聚集索引, 但可以创建多个非聚集索引.
设置某列为主键, 该列默认就为聚集索引.
按照下列标准选择建立索引的列:
频繁搜索的列;
经常用于查询选择的列;
经常排序, 分组的列;
经常用于连接的列 (主键 / 外键)
不要使用下面列创建索引:
仅包含几个不同值的列;
表中包含几行.
在 SQL 语句中, 特别是在 SELECT 语句中正确使用索引可以大大提高查询速度, 保证应用程序的运行性能.
视图是一种虚拟表, 通常是作为来自一个或多个表的行或列的子集创建的.
视图通常用来进行以下三种操作:
筛选表中的行;
防止未经许可的用户访问敏感的信息;
将多个物理数据表抽象为一个逻辑数据表.
使用视图可以给用户和开发人员带来很多好处:
对最终用户的好处:
结果更容易理解;
获得数据更容易.
对开发人员的好处:
限制数据检索更容易;
维护应用程序更方便.
创建视图语法
create view aa
as
SELECT dbo. 基本信息表. 学号, dbo. 成绩表. 学号 AS Expr1, dbo. 基本信息表. 姓名, dbo. 成绩表. 成绩
FROM dbo. 基本信息表 INNER JOIN
dbo. 成绩表 ON dbo. 基本信息表. 学号 = dbo. 成绩表. 学号
查看视图
select * from aa
存储过程的优点:
模块化程序设计; 执行速度快, 效率高; 减少网络流量; 具有良好的安全性.
常用的系统存储过程
比如:
- exec sp_databases
- # 列出当前系统中的数据库
- exec sp_helptext aa
- # 查看视图的语句文本
若 xp_cmdshell 作为服务器安全配置的一部分而被关闭, 请使用如下语句启用:
- exec sp_configure 'show advanced options',1
- # 显示高级配置信息
- go
- reconfigure
- # 重新配置
- go
- exec sp_configure 'xp_cmdshell',1
- # 打开 xp_cmdshell 选项
- go
- reconfigure
- # 重新配置
- go
使用这些语句开启扩展存储过程.
比如使用这些语句在系统中创建某些文件:
- exec xp_cmdshell 'md c:\bank',no_output
- # 创建文件夹 c:\bank
- exec xp_cmdshell 'dir c:\'
- # 列出 c 盘下的文件等内容
- create proc oo
- as
select 姓名, SUM(成绩) as 总成绩
from 基本信息表 left join 成绩表 on 基本信息表. 学号 = 成绩表. 学号
group by 姓名
- # 创建存储过程 qq
- exec qq
- # 查看存储过程 qq
- create proc ww
- @shuo varchar(10)
- as
select 姓名, SUM(成绩) as 总成绩
from 基本信息表 left join 成绩表 on 基本信息表. 学号 = 成绩表. 学号
group by 姓名
having 姓名 =@shuo
# 创建针对每个同学查看的记录
exec ww 张三
# 查看 ww 存储过程但是只查看张三
触发器分为以下几种
INSERT 触发器: 当向表中插入数据时触发, 自动执行触发器定义的 SQL 语句;
UPDATE 触发器: 当更新表中某列, 多列时触发, 自动执行触发器所定义的 SQL 语句;
DELETE 触发器: 当删除表中记录时触发, 自动执行触发器定义的 SQL 语句.
两个特殊的表由系统管理:
创建触发器的语句:
第一种
create trigger 删除
on 科目表
- for delete
- as
- begin
delete from 成绩表
- end
- # 删除之后不会同步从表中的数据
第二种
create trigger 自动同步成绩
on 科目表
- after delete
- as
- begin
delete from 成绩表 where 科目 id=(select 科目 id from deleted)
- end
- # 删除之后自动同步成绩
第三种
create trigger 禁止删除
on 基本信息表
- for delete
- as
- print '禁止删除'
- rollback transaction
- # 禁止删除数据, 如果删除数据则执行回滚, 撤回操作
事务: 保证数据库的原子性, 一致性, 隔离性, 持久性, 简称 ACID.
一个小实例
- begin transaction
- declare @errorsum int
- set @errorsum=0
- # 定义 内部变量, 用来保存前一条的执行结果, 执行成功为 0, 执行不成功为非 0.
- /*-- 转帐: 张三的帐户少 1000, 李四的帐户多 1000 元 --*/
- update bank set currentmoney=currentmoney-1000
- where name='zhangsan'
- set @errorsum=@errorsum+@@ERROR
- update bank set currentmoney=currentmoney+1000
- where name='lisi'
- set @errorsum=@errorsum+@@error
- print '查看转账事务中的余额'
- select * from bank
- if @errorsum<>0
- begin
- print '交易失败, 回滚事务'
- rollback transaction
- end
- else
- begin
- print '交易成功, 提交事务, 写入硬盘, 永久地保存'
- commit transaction
- end
- go
- print '查看转账事务后的余额'
- select * from bank
- go
锁的模式:
共享锁 (S 锁): 用于读取资源;
排他锁 (X 锁): 用于修改数据;
更新锁 (U 锁):U 锁是 S 锁和 X 锁的结合.
查看锁:
1, 使用 sys.dm_tran_locks 动态管理视图;
2, 使用 Profiler 来捕捉锁信息.
来源: http://blog.51cto.com/14157628/2404470