【SQL Srever】视图事务索引存储过程

空城za

已于 2022-05-10 01:52:01 修改

阅读量212

点赞数

分类专栏： SQL Server 文章标签：视图事务索引数据一致性查询优化

于 2022-05-10 01:50:48 首次发布

本文链接：https://blog.csdn.net/m0_60370702/article/details/123745927

版权

SQL Server 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

视图

为什么需要视图

例：求出平均工资最高的部门的编号和编号

-- 非视图写法
select * 
from 
	(
		select deptno, avg(sal) "avg_sal"
		from emp
		group by deptno
	) "T"
where "T"."avg_sal" = (
	select max(avg_sal)
	from(
		select deptno, avg(sal) "avg_sal"
			from emp
			group by deptno
		)"E"
)

-- 视图写法
-- 创建视图
create view v$_emp_1
as
	select deptno, avg(sal) "avg_sal"
			from emp
			group by deptno

-- 把视图当作表来查询
select * from V$_emp_1
where avg_sal = (select max(avg_sal) from v$_emp_1)

使用视图可以简化查询，避免代码冗余

什么是视图

视图从代码上看是个select语句
视图从逻辑上看被当作一虚拟张表

视图的格式

格式

create view 视图的名字 
as  
	-- select 的前面不能加begin
	select 语句
	-- select 后面不能添加end

视图的优点

简化查询

增加数据的保密性

create view v$_emp2
as 
	select empno,ename,job,mgr,comm,deptno from emp -- 非保密内容做成视图


select * from v$_mp2 -- 非保密内容给别人看，原emp表不给

视图的缺点

增加了数据库维护的成本
视图只是简化了查询（代码），并不能加快查询速度

注意的问题

创建视图的select语句必须为所有的计算列指定别名

create view v$_a
as 
	select avg(sal) as "avg_sal" from emp

视图不是物理表，是虚拟表
不建议根据视图更新视图所依赖的原始表的信息

事务

为什么需要事务

事务主要用来保证数据的合理性和并发处理的能力
通俗的说：
1. 事务可以保证避免数据处于一种不合理的中间状态
  - 例子：
    银行中的转账操作，账户A把一定数量的款项转到账户B上，这个操作包括两个步骤，一是从账户A上把存款减去一定数量，二是在账户B上把存款加上相同的数量。这两个步骤显然要么都完成，要么都失败，否则银行会受到损失。显然，这个转账操作中的两个步骤就构成一个事务
2. 利用事务可以实现多个用户对共享资源的同时访问
  - 例子：假设A和B用户都希望查询修改M表数据，A用户不应该刚把M表的数据改成5，查询时显示的数据却是8（B用户修改M表数据为8），事务必须保证多个用户对共享资源同时访问时，数据库给用户的反应是合理的

事务和线程的关系

事务也是通过锁来解决很多问题的
线程同步就说通过锁来解决的

事务和第三方插件的关系

直接使用事务库技术难度很大，一般借助第三方插件来实现

如何创建事务

T-SQL使用下列语句来管理事务

-- 开始事务：
begin transaction
-- 提交事务
commit transaction
-- 回滚（撤销）事务：
rollback transaction

索引

索引定义

SQL Server的索引是对数据库表中的一个或多个列的值进行排序的结构。

索引的作用

加快数据的检索
保证数据的一致性
实现表与表之间的参照完整性
在使用group by，order by 子句进行查询时，利用索引可以减少排序和分组的时间。

索引是否必须？

创建索引需要占用数据空间并花费一定的时间
建立索引会减慢数据修改的速度

索引的创建

- 定义有主键和外键的列
- 在指定范围中快速或频繁查询的列
- 连接中频繁使用的列
- 需要按照排序顺序快速或频繁检索的列
- 若创建了主键约束或唯一约束时，系统将自动的为建有这些约束的列创建聚集索引
- 当删除主键约束或唯一约束时，这些列上创建的聚集索引也会被自动删除
- 若不指定索引类型，sql server 将使用非聚集索引作为默认的索引类型
当在同一张表中建立和删除聚集索引和非聚集索引时，聚集索引优先进行操作

索引分类

聚集索引
索引的顺序决定了表中行的存储顺序，因此每个表中只能有一个聚集索引。
非聚集索引
索引仅仅记录指向表中行的位置的指针，这些指针本身是有序的，通过这些指针可以在表中快速地定位数据。

设计索引

一个表如果有大量索引，会影响insert，update和delete语句的性能
避免对经常更新的表进行过多的索引，并且索引应保持较窄
使用多个索引可以提高更新少而数据量大的查询的性能
对小表进行索引可能不会产生优化效果

create [unique] -- 唯一键
[clustered] [nonclustered] index index_name -- 聚集，非聚集 
on 表名(column [])

存储过程

定义

存储过程：一种为了完成特定完成功能的一个或一组SQL语句集合。经编译后存储在服务器端的数据库中，可以利用存储过程来加速SQL语句的执行。调用名称，传入参数，执行未完成特定功能。

优点

提高应用程序的通用性和可移植性。多次调用，不必重新编写，可以随时修改
更有效的管理数据库的权限
提高执行SQL的速度
减轻服务器负担

缺点

占用空间

创建存储过程

-- 创建一个无参数的存储过程
create proc SearchEmpInfo -- 修改 alter
as begin
	select ename, deptno from emp
	select * from emp
end
go
-- 调用执行
exec SearchEmpInfo
-- 删除存储过程
drop proc SearchEmpInfo