转帖杂和数据库------- SQL-server 数据库相关知识 事务、索引、视图

-- SQL-server 数据库相关知识 事务、索引、视图

2007年12月20日 星期四 18:34

/******************************************************************************************************************************************/

--       SQL-server 数据库相关知识   事务、索引、视图  

--( 一 问题阐述 举例:银行的转账业务就是典型的一个事务事例)

-- 一、二部分的代码建议分开执行，更容易理解

use master

go

--判断是否已经存在银行数据库Bank 如果存在了就删除那个数据库,

if exists(select *from sysdatabases where name='bank')

begin

drop database bank

end

--在E盘新建一个文件夹存放数据库文件

exec xp_cmdshell 'md e:\ChinaBank'

--建立一个简单的银行数据库

create database Bank

on primary

(

name='Bank_Date',

filename='e:\ChinaBank\bank_date.MDF',

SIZE=1MB,

MAXSIZE=100MB,

FILEGROWTH=1%

)

LOG ON

name='Bank_LOG',

filename='e:\ChinaBank\bank_log.LDF',

GO

use Bank

--建立银行用户信息表

create table userInfo

customerName char(10) not null,

customerBalance money not null

/*--添加约束:根据银行规定,账户余额不能低于1元,否则视于销户---*/

alter table userInfo

add constraint ck_userInfo_customerBalance check(customerBalance>=1)

/*---插入测试数据-----*/

insert userInfo(customerName,customerBalance)

select '张三',1000 union

select '李四',1

select *from userInfo

/*现在开始模拟转账,张三转账一千元到李四账户里,那么张三的账户里应该只剩下1元,李四账户应该有1001元*/

update userInfo set customerBalance=CustomerBalance-1000 where customerName='张三'

update userInfo set customerBalance=CustomerBalance+1000 where customerName='李四'

--问题出来了,看见没?张三的账户余额并没有减少,而李四却多出1000元??????????

--这里就体现了事务的概念,事务是单个的工作单元,如果某一事务成功,则在该事务中进行的所有数据更改均会提交,成为数据库

--中永久组成部份,如果事务中遇到了错误且必须取消和回滚,则所有数据更改均被清除,换句话说,你所作的操作均会被撤销,

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

--即这一组操作命令,要么都执行要么都不执行..

--上面的代码执行一次.看看表记录.你会发现我所说的.....

--下面的代码执行一次.证明事务是不可分割的工作逻辑单元

---( 二 )下面重新来操作刚才的转账行为

--恢复账户数据 张三1000元,李四1元

update userInfo set customerBalance=1000 where customerName='张三'

update userInfo set customerBalance=1 where customerName='李四'

select *from UserInfo

--开始事务(表示事务从此开始.面的T-SQL语句都是一个整体)

begin transaction

--定义一个变量,用于累积事务执行过程中的错误

declare @errorSum int

set @errorSum=0 --(计数器)初始化为0表示没有错误发生!

/*现在开始转账操作*/

--累积是否有错,(在张三的账户中减去1000元,看是否执行成功,如果失败,那么@@error就能捕捉到一个错误,@@error是SQL-Server系统中定义的全局变量

set @errorSum=@errorSum+@@error

/*根据事务过程中的错误,来确认事务是提交还是撤消*/

--如果计数器不为0就说明这项转账事务中有错误

if @errorSum<>0

   --交易失败!回滚事务..

   rollback transaction

else

   --交易成功,提交事务

   commit transaction

--再次查看表记录,发现数据依然是原来的.没有改变,再次证明了事务是一个不可分割的工作逻辑单元

--如果出现如下提示:是正常的.因为我们的表约束了账户余额必须大于1.否则认为是销户,

--张三的账户中转账1000到李四账户中.那么张三账户就只有0元了.所以就有如下出错提示,如果我们改为转账800,就不会出错了

--服务器: 消息 547，级别 16，状态 1，行 1

--UPDATE 语句与 COLUMN CHECK 约束 'ck_userInfo_customerBalance' 冲突。该冲突发生于数据库 'Bank'，表 'userInfo', column 'customerBalance'。

--语句已终止。

--        下面讲解索引

--索引是提供指针以指向存储在表中指定列的数据值，然后然后根据指定的排序次序排列这些指针，就好像一本书的目录一样，通过搜索索引找到特定的值，然后跟随指针

--到达包含该值的行。

--索引：是SQL-Server编排数据的内部方法，它为SQL-Server提供一种方法来编排查询数据的路由.

-- 在此借用上面建好的bank数据库做讲解

--新建一张表存储学号成绩

create table studentScore

name varchar(10) not null,

score float not null

--初始化数据

insert studentScore(name,score)

select '张小',90 union

select '张三',84 union

select '李三',29 union

select '五三',45 union

select '王三',90 union

select '赵三',82 union

select '李四',20 union

select '陈三',84 union

select '何五',30

select *from studentScore

--下面以 为成绩表 建立非聚集索引 为例 ,因为成绩有可能重复

/*先来检测一下是否已经存在该索引，如果存在就删除该索引，重新建一个*/

if exists(select *from sysindexes where name='ix_studentScore_score')

drop index studentScore.ix_studentScore_score

create nonclustered index ix_studentScore_score

on studentScore(score)

with fillfactor=10

--指定索引查询,按ix_studentScore_score ,查出成绩在40到90之间的学员姓名

select name from studentScore

(index=ix_studentScore_score)

where score between 40 and 90

--这里我们可以指定按那个索引进行查询，但是一般情况下不需要人工指定，SQL-server将会自动根据所创建的索引，优化查询

--另外SQL-Server中还包括唯一索引，主键索引，聚集索引等，上例中使用的是非聚集索引

--唯一索引：不允许两行具有相同的索引值，如果建有唯一约束，那么自动就创建了唯一索引。

--主键索引：在数据库关系图中为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型.

--聚集索引: 在聚集索引中,表中各行的物理顺序与键值的逻辑(索引)顺序相同,表中只能有一个聚集索引.但可以有多个非聚集索引,

--         设置某列为主键,该列默认为聚集索引.

--                                                  下面讲解 视图

--视图:使用视图的好处:1、出于安全考虑,用户不必看到整个数据库结构，而隐藏部分数据；2、符合用户日常业务逻辑，使他们对数据更容易理解

--视图是一种虚拟表，通常是作为来自一个或多个表的行或列的子集创建的，同时也可以包含全部列和行。

--我们依然采用T-SQL语句来创建视图

--

---检测是否存在该视图，如果存在就删除该视图

if exists (select *FROM SYSOBJECTS WHERE name='view_studentScore_score')

drop view view_studentScore_score

/**创建视图   这里用的内联接 涉及到了上面银行客户信息表*/

create view view_studentScore_score

as select 姓名=name,成绩=studentScore.score from studentScore

inner join userInfo

on studentScore.name=userInfo.customerName

--视图是一个虚拟表，可以像物理表一样打开，同样支持增删改查操作，但在实际应用中仅做查询使用....

select *from view_studentScore_score

select * from authors

--声明游标

declare @au_lname varchar(20)

declare mycursor cursor for select au_lname from authors

--打开

S2-ATM取款机SQL 2000脚本

2007年12月20日 星期四 18:33

--判断是否存在bankDB数据库，如果存在就删除它，另外建立一个

if exists (select *from sysdatabases where name='bankDB')

drop database ChinaBank

--在E盘建立一个bankDB文件夹

exec xp_cmdshell 'md e:\bankDB'

create database ChinaBank

filename='e:\bankDB\bank_date.MDF',

FILEGROWTH=15%

filename='e:\bankDB\bank_log.LDF',