MSSQL事务与视图

落日映苍穹つ 2023-10-16 23:53 36阅读 0赞

use MySchool  
go  
select \* from Student; --63  
select \* from Result; --103  
  
  
  
  
\--没有参加考试的学员信息  
\--公共：32  
select \* from Student where exists  
(select \* from Result where StudentNo=Student.StudentNo)  
  
  
\--in 替换  
select \*from Student where StudentNo in (select StudentNo from result)  
  
  
\--  
select distinct Student.StudentNo,StudentName from Student inner join Result  
on Student.StudentNo = Result.StudentNo  
  
  
\--不同：31  
select \*from Student where StudentNo not in (select StudentNo from result)  
  
  
\--表连接不能替换子查询 63?(其实学生表)  
select distinct Student.StudentNo,StudentName from Student inner join Result  
on Student.StudentNo <> Result.StudentNo  
  
  
  
  
if exists (select \* from sysobjects where name='bank')  
drop table bank  
CREATE TABLE bank  
(  
customerName CHAR(10), --顾客姓名  
currentMoney MONEY --当前余额  
)  
GO  
ALTER TABLE bank  
ADD CONSTRAINT CK\_currentMoney  
CHECK(currentMoney >= 1)  
GO  
INSERT INTO bank(customerName, currentMoney)  
VALUES('张三', 1000)  
INSERT INTO bank(customerName, currentMoney)  
VALUES('李四', 1)  
  
  
select \* from bank;  
\--假定张三的账户直接转账1000元到李四的账户  
update bank set currentMoney = currentMoney-1000 where customerName='张三 ';  
update bank set currentMoney = currentMoney+1000 where customerName='李四 ';  
  
  
\--（一）事务？  
\--事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作  
\--多个操作作为一个整体向系统提交，要么都执行、要么都不执行  
\--事务是一个不可分割的工作逻辑单元  
  
  
\--（二）事务必须具备以下四个属性，简称ACID 属性：  
\--原子性（Atomicity）  
\--一致性（Consistency）  
\--隔离性（Isolation）  
\--永久性（Durability）  
  
  
\--（三）使用SQL语句管理事务  
\--1）开始事务  
\--begin transaction  
  
\--2)提交事务  
\--commit transaction  
  
  
\--3)回滚事务  
\--rollback transaction  
  
  
一旦事务提交或回滚，则事务结束  
判断某条语句执行是否出错：  
使用全局变量@@ERROR  
@@ERROR只判断当前一条T-SQL语句执行是否有错  
为了判断事务中所有T-SQL语句是否有错，可以对错误进行累计  
\--事务可以嵌套  
  
  
  
  
\--(四）事务分类  
a)显式事务  
用BEGIN TRANSACTION明确指定事务的开始(最常用的事务类型)  
  
  
B)隐性事务  
通过设置SET IMPLICIT\_TRANSACTIONS ON 语句，将隐性事务模式设置为打开  
其后的T-SQL语句自动启动一个新事务  
提交或回滚一个事务后，下一个 T-SQL 语句又将启动一个新事务  
  
  
  
  
C)自动提交事务  
SQL Server 的默认模式  
每条单独的 T-SQL 语句视为一个事务  
  
SELECT \* FROM bank;  
  
  
  
begin transaction --开始事务  
\--声明变量累计错误号  
declare @err int=0  
update bank set currentMoney = currentMoney-1000 where customerName='张三 ';  
\--累计错误号  
set @err =@err+@@ERROR  
update bank set currentMoney = currentMoney+1000 where customerName='李四 ';  
set @err =@err+@@ERROR --累计错误号  
  
IF @err<>0 --如果SQL语句执行出错  
BEGIN  
PRINT '交易失败，回滚事务'  
ROLLBACK TRANSACTION  
END  
ELSE  
BEGIN  
PRINT '交易成功，提交事务，写入硬盘，永久的保存'  
COMMIT TRANSACTION  
END  
GO  
PRINT '查看转账事务后的余额'  
SELECT \* FROM bank  
GO  
  
  
  
\--800  
begin transaction --开始事务  
\--声明变量累计错误号  
declare @err int=0  
update bank set currentMoney = currentMoney-800 where customerName='张三 ';  
\--累计错误号  
set @err =@err+@@ERROR  
update bank set currentMoney = currentMoney+800 where customerName='李四 ';  
set @err =@err+@@ERROR --累计错误号  
  
IF @err<>0 --如果SQL语句执行出错  
BEGIN  
PRINT '交易失败，回滚事务'  
ROLLBACK TRANSACTION  
END  
ELSE  
BEGIN  
PRINT '交易成功，提交事务，写入硬盘，永久的保存'  
COMMIT TRANSACTION  
END  
GO  
PRINT '查看转账事务后的余额'  
SELECT \* FROM bank  
GO  
  
  
\--二、什么是视图  
\--(1)视图是一张虚拟表  
\--表示一张表的部分数据或多张表的综合数据  
\--其结构和数据是建立在对表的查询基础上  
\--(2)视图中不存放数据  
\--数据存放在视图所引用的原始表中  
\--(3)一个原始表，根据不同用户的不同需求，可以创建不同的视图  
  
  
  
  
\--视图的用途  
\-- 筛选表中的行  
\-- 防止未经许可的用户访问敏感数据  
\-- 降低数据库的复杂程度  
\-- 将多个物理数据库抽象为一个逻辑数据库  
  
  
  
  
\--使用T-SQL语句创建视图  
\--CREATE VIEW view\_name  
\-- AS  
\-- <SELECT 语句>  
  
  
\--使用T-SQL语句删除视图  
IF EXISTS (SELECT \* FROM sysobjects WHERE name = 'view\_name')  
DROP VIEW view\_name  
  
  
\--使用T-SQL语句查看视图  
SELECT \* FROM view\_name  
  
  
\-参加考试的学员信息  
select \* from Result  
  
  
IF EXISTS (SELECT \* FROM sysobjects WHERE name = 'vw\_student')  
DROP VIEW vw\_student  
go  
create view vw\_student  
as  
select st.StudentNo,st.StudentName,r.subjectid,r.studentResult,r.examdate  
from Student st,Result r  
where st.StudentNo=r.StudentNo  
  
  
select \* from vw\_student;  
  
  
使用视图注意事项:  
\--(1)视图中可以使用多个表  
IF EXISTS (SELECT \* FROM sysobjects WHERE name = 'vw\_student')  
DROP VIEW vw\_student  
go  
create view vw\_student  
as  
select distinct st.StudentNo 学号,st.StudentName 姓名,st.Sex 性别,g.GradeName 年级名称  
,s.SubjectName 课程,r.StudentResult 成绩,r.ExamDate 考试日期  
from Student st,Grade g,Subject s,Result r  
where st.StudentNo=r.StudentNo  
and g.GradeId = st.GradeId  
and s.SubjectId = r.SubjectId  
go  
select \* from vw\_student;  
  
  
  
  
  
  
\--一个视图可以嵌套另一个视图  
IF EXISTS (SELECT \* FROM sysobjects WHERE name = 'vw\_student1')  
DROP VIEW vw\_student1  
go  
create view vw\_student1  
as  
select \* from vw\_student where 年级名称='s2'  
go  
select \*from vw\_student1  
  
  
  
  
\--视图定义中的 SELECT 语句不能包括下列内容：  
\--(1)ORDER BY 子句，除非在 SELECT 语句的选择列表中也有一个 TOP 子句  
IF EXISTS (SELECT \* FROM sysobjects WHERE name = 'vw\_student2')  
DROP VIEW vw\_student2  
go  
create view vw\_student2  
as  
\--发生异常  
\--select \* from vw\_student where 成绩 is not null order by 成绩 desc;  
select top 5 \* from vw\_student where 成绩 is not null order by 成绩 desc;  
go  
select \*from vw\_student2  
  
  
  
  
\--(2)INTO 关键字  
IF EXISTS (SELECT \* FROM sysobjects WHERE name = 'vw\_student2')  
DROP VIEW vw\_student2  
go  
create view vw\_student2  
as  
\--发生异常  
\--select \* into mystudent from vw\_student where 成绩 is not null  
go  
select \*from vw\_student2  
  
  
\--select \*from mystudent;  
  
  
  
  
\--(3)引用临时表或表变量  
IF EXISTS (SELECT \* FROM sysobjects WHERE name = 'vw\_student2')  
DROP VIEW vw\_student2  
go  
create view vw\_student2  
as  
\--发生异常表变量  
\--declare @sex varchar(2)='女'  
\--select \* from vw\_student where 性别=@sex  
  
\--临时表不可以使用  
select \* from \#my  
go  
select \*from vw\_student2  
  
  
\--创建临时表  
\--select \* into \#my from vw\_student  
  
select \*from vw\_student  
  
\--在视图中能否删除数据?(多张表数据)  
delete from vw\_student where 姓名='洛飞'  
  
update vw\_student set 年级名称='IT' where 姓名='洛飞'  
  
  
  
  
  
  
IF EXISTS (SELECT \* FROM sysobjects WHERE name = 'vw\_student2')  
DROP VIEW vw\_student2  
go  
create view vw\_student2  
as  
select \* from Grade;  
go  
select \* from vw\_student2  
  
  
\--在视图中能否删除数据?(1张表数据)==>基表数据（grade）  
delete from vw\_student2 where gradeid=10