事务 索引 视图

1. 事务

为什么需要事务?比如银行转帐,一个帐户转出,一个帐户转入,需要只少两个SQL语句来执行转帐操作,保证不出错.就需要事务.

--转帐流程:1.转出 2.转入
 UPDATE st_money SET MONEY = MONEY-100 WHERE id = 1; --小明减100元
 UPDATE st_money SET MONEY = MONEY+100 WHERE id = 2; --小红加100元   

见上面代码,如果上面两个语句任一个执行错了,那么转帐就有问题,钱不知去哪了.只有使用事务的功能才能保证一致.

事务

分为显式事务/隐式事务/自动提交事务

1.显式事务

基本语法:

--事务开始(记录事务开始的位置)
 begin transaction; --简写成: begin tran;
--提交事务(从开始事务的地方开始提交给数据库)
 commit tran;
--回滚事务(回滚到开始事务的地方)
 rollback tran;

2.隐式事务

SET IMPLICIT_TRANSACTIONS ON;语句开启隐式事务.隐式事务一旦开始启,从你写的第一个语句起它就自动从后台偷偷开始事务了,等你操作完成后,一定要记得 提交回滚 操作.

3.自动事务

自动事务是SQL默认的事务行为,每成执行一句语句它都自动提交到数据库中.整个事务过程默认自动的.

自动事务 就是把SET IMPLICIT_TRANSACTIONS OFF;关掉.启用显示式事务,而又没有开始显式事务(begin tran;)

事务的特性

事务必须具备以下四个属性,简称ACID 属性:

  1. 原子性(Atomicity):事务是一个完整的操作。事务的各步操作是不可分的(原子的);要么都执行,要么都不执行
  2. 一致性(Consistency):当事务完成时,数据必须处于一致状态
  3. 隔离性(Isolation):对数据进行修改的所有并发事务是彼此隔离的,这表明事务必须是独立的,它不应以任何方式依赖于或影响其他事务
  4. 永久性(Durability):事务完成后,它对数据库的修改被永久保持,事务日志能够保持事务的永久性

事务的用法:实例1-显式事务

--1.显式事务(开始   结束(提交或回滚))
BEGIN TRAN;--开始显式事务
 UPDATE st_money SET MONEY = MONEY-100 WHERE id = 1; --刘帅减100元
 UPDATE st_money SET MONEY = MONEY+100 WHERE id = 2; --李豪加100元    
 COMMIT TRAN;--提交事务

实例:遇到错误回滚事务的方法

--申明一个记录错语代码累加的变量名,用来记录每个语句是否有错,最后判断回滚还是提交.
DECLARE @errors INT
SET @errors = 0;
BEGIN TRANSACTION; --事务开始 
                 
UPDATE st_money SET MONEY = MONEY-100 WHERE id = 1;
SET @errors =@errors + @@ERROR
UPDATE st_money SET MONEY = MONEY-200 WHERE id = 2;
SET @errors =@errors + @@ERROR
UPDATE st_money SET MONEY = MONEY-300 WHERE id = 3;
SET @errors =@errors + @@ERROR
UPDATE st_money SET MONEY = MONEY-400 WHERE id = 4;
SET @errors =@errors + @@ERROR
UPDATE st_money SET MONEY = MONEY-500 WHERE id = 5;
SET @errors =@errors + @@ERROR
UPDATE st_money SET MONEY = MONEY+100 WHERE id = 6;
SET @errors =@errors + @@ERROR
UPDATE st_money SET MONEY = MONEY+200 WHERE id = 7;
SET @errors =@errors + @@ERROR
UPDATE st_money SET MONEY = MONEY+300 WHERE id = 8;
SET @errors =@errors + @@ERROR
UPDATE st_money SET MONEY = MONEY+400 WHERE id = 9;
SET @errors =@errors + @@ERROR
UPDATE st_money SET MONEY = MONEY+500 WHERE id = 10;
SET @errors =@errors + @@ERROR
--只要上面代码有任何错误,则@errors不等于0,则回滚事务,
IF(@errors <> 0) 
BEGIN
    ROLLBACK TRANSACTION; --如果上面任一句有错,则回滚事务
    PRINT '执行了回滚操作';
END
else 
BEGIN
    COMMIT TRANSACTION;--没任何错误则提交事务
    PRINT '执行了提交操作';
END

实例2:设置SET XACT_ABORT ON;遇到错误时自动回滚事务

--第二种方法,打开遇错误自动回滚事务功能.下面批处理语句如没任何错误则自动提交,有错则自动回滚.
SET XACT_ABORT ON;--在批处理语句前加上这个语句
BEGIN TRAN
UPDATE st_money SET MONEY = MONEY-100 WHERE id = 1;
UPDATE st_money SET MONEY = MONEY-200 WHERE id = 2;
UPDATE st_money SET MONEY = MONEY-300 WHERE id = 3;
UPDATE st_money SET MONEY = MONEY-400 WHERE id = 4;
UPDATE st_money SET MONEY = MONEY-500 WHERE id = 5;
UPDATE st_money SET MONEY = MONEY+100 WHERE id = 6;
UPDATE st_money SET MONEY = MONEY+200 WHERE id = 7;
UPDATE st_money SET MONEY = MONEY+300 WHERE id = 8;
UPDATE st_money SET MONEY = MONEY+400 WHERE id = 9;
UPDATE st_money SET MONEY = MONEY+500 WHERE id = 10;
COMMIT TRAN

注意:如果想使用显式事务,但又开启了隐式事务.记得用 SET IMPLICIT_TRANSACTIONS OFF;把隐式事务关掉.

事务的用法:实例2- 隐式事务.

用 SET IMPLICIT_TRANSACTIONS ON;开启隐式事务.

SET IMPLICIT_TRANSACTIONS ON--开启隐式事务,自动开始事务,需提交
UPDATE st_money SET MONEY = MONEY-100 WHERE id = 1;
UPDATE st_money SET MONEY = MONEY-200 WHERE id = 2;
UPDATE st_money SET MONEY = MONEY-300 WHERE id = 3;
UPDATE st_money SET MONEY = MONEY-400 WHERE id = 4;
UPDATE st_money SET MONEY = MONEY-500 WHERE id = 5;
UPDATE st_money SET MONEY = MONEY+100 WHERE id = 6;
UPDATE st_money SET MONEY = MONEY+200 WHERE id = 7;
UPDATE st_money SET MONEY = MONEY+300 WHERE id = 8;
UPDATE st_money SET MONEY = MONEY+400 WHERE id = 9;
UPDATE st_money SET MONEY = MONEY+500 WHERE id = 10;
COMMIT TRAN; -- 提交事务在隐式事务中切记不要忘记

事务的用法:实例3 - 自动提交事务

自动提交事务是SQL默认的,不需要设置.每执行一个引响表的语句它会自动提交事务. 但要注意别启用了隐式事务.,如开启了用(SET IMPLICIT_TRANSACTIONS OFF)把隐式事务关掉.

了解内容.错误捕捉与处理.

最后讲个try....catch...语句,它在事务中常用到,为了解内容.try....catch...在T-SQL中进行错误捕捉与处理的语句,语法:

begin try
--正常语句,如果此语句块中内容无错,则正常执行完,而catch中的代码块不执行.
--但如果此语句块中内容有错,则捕捉错误后执行CATCH代码块中的内容.
end try
begin catch
--只有在try语句块中的内容有错时,此代码块才会被执行.
end catch

try...cathc...实例

--------try catch错误捕获与处理语句(错语级别的限制)-----------------------------
BEGIN TRANSACTION; --事务开始 
 BEGIN TRY
     exec('UPDATE st_money SET MONEY = MONEY-100 WHERE id = 1'); --刘帅减100元
     exec('UPDATE st_money SET MONEYa = MONEY+100 WHERE id = 2'); --李豪加100元(!表名有错)
     COMMIT TRAN;                                                     -- 
 END TRY
 BEGIN CATCH
    ROLLBACK TRAN;  --回滚事务
 END CATCH

2.索引

什么是索引

SQL Server中的数据也是按页( 4KB )存放索引:是SQL Server编排数据的内部方法。它为SQL Server提供一种方法来编排查询数据 。索引页:数据库中存储索引的数据页;索引页类似于汉语字(词)典中按拼音或笔画排序的目录页。索引的作用:通过使用索引,可以大大提高数据库的检索(查询)速度,改善数据库性能。

索引的类型

主键索引:为表定义一个主键将自动创建主键索引,主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的,并且不能为空唯一索引:唯一索引不允许两行具有相同的索引值聚集索引(Clustered):表中各行的物理顺序与键值的逻辑(索引)顺序相同,每个表只能有一个非聚集索引(Non-clustered):非聚集索引指定表的逻辑顺序。数据存储在一个位置,索引存储在另一个位置,索引中包含指向数据存储位置的指针。可以有多个,小于249个

注:

  1. 因为在创建主键的时候,也创建了主索引,也是聚集索引,而且一个表只能有一个聚集索引,那在创建主键的时候就建好了.
  2. 唯一索引,是为了表示列的唯一性,我们一般创建唯一约束,(基本上也用不上)
  3. 非聚集索引是我们的创建索引的重点部分.但由用create index默认就是非 聚集索引,

所以...我们学习语句只要学习简单创建索引就行了.CREATE INDEX 索引名 ON 表名(列名) WITH FILLFACTOR=X填充因子的值 X 取值0-100 但0与100无实际意义(都是100%填充).

思考: 什么时候创建索引???

3.视图

什么是视图

  • 视图是一张虚拟表,它表示一张表的部分数据或多张表的综合数据,其结构和数据是建立在对表的查询基础上
  • 视图中并不存放数据,而是存放在视图所引用的原始表(基表)中
  • 同一张原始表,根据不同用户的不同需求,可以创建不同的视图

总结:视图就是一个查询结果的SQL定义起的别名.基本语法

--创建视图
create view 视图名 as 查询语句
--删除视图
drop view 视图名
--修改视图(把创建时的语句中的create 改成 alter)
alter view 视图名 as 新的查询语句

视图练习

--1.创建一个视图名为:五班JAVA基础成绩,
--要求显示(学号,姓名,性别,JAVA基础成绩),并按分数从高到低排列

create view 五班JAVA基础成绩 as
select a.id,a.name,a.sex,b.score from st_info a 
join st_score b on a.id = b.uid 
where 
b.subid = (select id from subject where name = 'JAVA基础') AND a.classid = 5


--2.创建一个视图:五班学生成绩视图,
--要求显示(学号,姓名,小组名,科目名,成绩)五个列.
create view 五班学生成绩视图 AS
SELECT A.id 学号,A.name 姓名,B.name 小组,D.name 科目,C.score 成绩 FROM st_info A
JOIN st_group B ON A.groupid = B.id
JOIN st_score C ON A.id = C.uid
JOIN subject D ON C.subid = D.id
WHERE A.classid = 5 

--3.把学生库七个表写成中文视图名+中文列名的七个视图.
create view 学生信息表视图 AS
SELECT id 学号,name 姓名,sex 性别,qq QQ,tel 电话,classid 班级,groupid 小组,workid 职责,kaid 身份证号码 FROM st_info
 
 

--视图创建好了,怎么使用....(简化复杂的查询)
select 姓名,AVG(成绩) from 五班学生成绩视图 where 成绩 > 0 group by 姓名 order by AVG(成绩) 

--删除视图
 drop view 五班学生成绩视图
 
 --修改视图
alter view 五班学生成绩视图 as
SELECT A.id 学号,A.name 姓名,B.name 小组,D.name 科目,C.score 成绩 FROM st_info A
JOIN st_group B ON A.groupid = B.id
JOIN st_score C ON A.id = C.uid
JOIN subject D ON C.subid = D.id
WHERE A.classid = 5