面试准备之SQL 5 —— 事务、索引和视图

5/3/2010来源:SQL教程人气:8309

1. 什么是事务?

事务是一种机制、一种操作序列,它包含了一组数据库操作命令,并且所有的命令作为一个整体一起向系统提交

或撤销操作请求,即这一组数据库要么都执行,要么都不执行。特别适用于多用户同时操作的数据库系统。

 

事务是作为单个逻辑工作单元执行的一系列操作。

一个逻辑工作单位必须有4个属性:

原子性:事务是一个完整的操作,事务的各元素不可再分。所有元素必须作为一个整体提交或回滚。

一致性:当事务完成时,数据必须处于一致状态。

隔离性:对数据进行修改时所有并发事务是彼此隔离的。

持久性:事务完成后,对系统影响是永久性的。

 

2.创建事务

开始事务:begin transaction

提交事务:commit transaction

回滚(撤销)事务:rollback transaction

 

代码
use studb
go

if exists(select * from sysobjects where name = 'bank')
drop table bank
create table bank
(
    customerName char(10), --顾客姓名
    currentMoney money --余额
)
go
--增加检查约束 账户余额不能小于1
alter table bank
add constraint CK_currentMoney check(currentMoney >= 1)
go
insert into bank values('张三',1000)
insert into bank values('李四',1)

select * from bank

----------------------------------------------------------
--------------- * * * * 事 * * 务 * * * * ----------------
----------------------------------------------------------
use studb
go
set nocount on --不显示受影响的行数信息
PRint '事务之前的数据:'
select * from bank
go

begin transaction
declare @errorSum int
set @errorSum=0

update bank set currentMoney = currentMoney-1000 where customername='张三'
update bank set currentMoney = currentMoney+1000 where customername='李四'

set @errorSum = @errorSum + 1
 
print '事务中的数据:'
select * from bank

if @errorSum <> 0
    begin
        print '交易失败,回滚事务'
        rollback transaction
    end
else
    begin
        print '交易成功,提交事务,写入硬盘,永久的保存'
        commit transaction
    end
go
print '事务后的数据:'
select * from bank

 

----------------------------------------------------------
--------------- * * * * 索 * * 引 * * * * ----------------
----------------------------------------------------------
--索引:它是SQL Server编排数据的内部方法
--索引可以分为以下三种;
--唯一索引:不允许两行具有相同的索引值
--主键索引:为表定义主键时自动创建 唯一索引的特殊类型 主键索引要求主键中的每一个值都是唯一的
--聚集索引:表中各行的物理顺序与键值的逻辑(索引)顺序相同,表中只能包含一个聚集索引(可以理解为字典的拼音)。
--非聚集索引:数据和索引包含指向数据存储的相应位置 表中的各行的物理顺序与键值的逻辑顺序不匹配。(可以理解为MAP)
--聚集索引比非聚集索引速度更快
--在一个表中只能有一个聚集索引,但是可以有多个非聚集索引,设置某列为主键该列就默认为聚集索引了。
--表是可以没有索引的,主键索引不一定就是聚集索引。

--索引运用到哪里?
--该列频繁被搜索,该列用于对数据排序
--劣种就几个不同的值,表中就几行数据就没必要使用索引

 

--语法
--create [unique][clustered|nonclustered] index index_name on table_name (column_name[,column_name]...)
--[
-- with fillfactor = x --填充因子 x 为0~100之间的值
--]

 

代码
use studb
go

if exists (select [name] from sysindexes where [name]='IX_stuMarks_writtenExam')
drop index stuMarks.IX_stuMarks_writtenExam --查询是否已经存在该索引 如果存在就删除
create nonclustered index IX_stuMarks_writtenExam on stuMarks(writtenExam)
with fillfactor=30 --填充因子 预留空间
go
--查询
select * from stumarks (index=IX_stuMarks_writtenExam)
--会报错 :'index' 附近有语法错误。如果它要作为表提示的一部分,则必须有 WITH 关键字和圆括号,如:
select * from stumarks with(index=IX_stuMarks_writtenExam)
select * from stumarks with(index=IX_stuMarks_writtenExam)  where writtenExam between 60 and 90
 

----------------------------------------------------------
--------------- * * * * 视 * * 图 * * * * ----------------
----------------------------------------------------------

视图:是一种虚拟表,基于一个表或多个表的数据的查询方法。

一般作用:筛选表中的行、防止未经许可的用户访问敏感数据、将多个物理数据表抽象为一个逻辑数据表

--语法:
--create view view_name
--as
--<select 语句>

代码

use studb
go

if exists(select * from sysobjects where name='view_stuinfo_stumarks')
drop view view_stuinfo_stumarks
go
create view view_stuinfo_stumarks
as
select 姓名=stuname,学号=stuinfo.stuno,笔试成绩=writtenexam,机试成绩=labexam,
平均分=(writtenexam+labexam)/2 from stuinfo left join stumarks on stuinfo.stuno = stumarks.stuno
go