优化Myschool数据库设计第六章 使用事务、视图、索引

USE MySchool
GO
set nocount on --不显示受影响行数
--判断表是否存在
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE name = 'bank')
DROP TABLE bank
GO
--创建表
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)

--示例1：使用事务进行银行转账
PRINT '查看转账事务前的余额'
SELECT * FROM bank
GO

BEGIN TRANSACTION --开始事务
DECLARE @errorSum INT --定义变量,用于累计事务执行过程中的错误
SET @errorSum=0 --初始化为0,即无错误

--转账:张三的账户少1000元,李四的账户多1000元
UPDATE bank SET currentMoney=currentMoney-1000
WHERE customerName='张三'
SET @errorSum=@errorSum+@@ERROR --累计是否有错误
UPDATE bank SET currentMoney=currentMoney+1000
WHERE customerName='李四'
SET @errorSum=@errorSum+@@ERROR --累计是否有错误

IF @errorSum<>0 --如果有错误
BEGIN
PRINT '交易失败,回滚事务'
ROLLBACK TRANSACTION
END
ELSE
BEGIN
PRINT '交易成功,提交事务'
COMMIT TRANSACTION
END
GO

PRINT '查看转账事务后的余额'
SELECT * FROM bank
GO

--练习1：批量插入学生成绩
BEGIN TRANSACTION
DECLARE @errorSum INT
SET @errorSum=0
/*--插入数据--*/
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
VALUES(10000,1,GETDATE(),55)
SET @errorSum=@errorSum+@@error
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
VALUES(10000,2,GETDATE(),182)--分数违反约束
SET @errorSum=@errorSum+@@error
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
VALUES(10000,3,GETDATE(),90)
SET @errorSum=@errorSum+@@error
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
VALUES(10000,4,GETDATE(),56)
SET @errorSum=@errorSum+@@error

/*--根据是否有错误，确定事务是提交还是撤销--*/
IF(@errorSum<>0) --如果有错误
BEGIN
PRINT '插入失败，回滚事务'
ROLLBACK TRANSACTION
END
ELSE
BEGIN
PRINT '插入成功，提交事务'
COMMIT TRANSACTION
END
GO

--示例2：创建视图，查询java logic最近一次考试信息
IF EXISTS (SELECT * FROM sysobjects WHERE name='vw_student_result ')
DROP VIEW vw_student_result
GO
CREATE VIEW vw_student_result
AS
SELECT 姓名=StudentName,学号=Student.StudentNo,成绩=StudentResult,
课程名称 = SubjectName,考试日期=ExamDate
FROM Student
INNER JOIN Result ON Student.StudentNo = Result.StudentNo
INNER JOIN Subject ON Result.SubjectNo = Subject.SubjectNo
WHERE Subject.SubjectName = 'java logic'
AND ExamDate =
(SELECT MAX(ExamDate) FROM Result WHERE SubjectNo =
(SELECT SubjectNo FROM Subject WHERE SubjectName = 'java logic'))
GO

SELECT * FROM vw_student_result

--使用视图的注意事项
--1.一个视图可以嵌套另一个视图
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE name='vw_Student')
DROP VIEW vw_Student
GO

CREATE VIEW vw_Student
AS
SELECT * FROM Student
GO

IF EXISTS(SELECT * FROM SYSOBJECTS WHERE name='vw_Student_S1')
DROP VIEW vw_Student_S1
GO

CREATE VIEW vw_Student_S1
AS
SELECT * FROM vw_Student
WHERE GradeId=1
GO

SELECT * FROM vw_Student_S1

--2.视图定义中的 SELECT 语句不能包括ORDER BY 子句，
--除非在 SELECT 语句的选择列表中也有一个 TOP 子句
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE name='vw_Top5_Student')
DROP VIEW vw_Top5_Student
GO

CREATE VIEW vw_Top5_Student
AS
SELECT top 5 * FROM Student
ORDER BY StudentNo desc
GO
SELECT * FROM vw_Top5_Student
go

--3.视图定义中的 SELECT 语句不能包括insert into语句
--IF EXISTS(SELECT * FROM SYSOBJECTS WHERE name='vw_Student')
-- DROP view vw_Student
--GO
--CREATE VIEW vw_Student
--AS
-- SELECT *
-- INTO newTable
-- FROM Student
--GO
--SELECT vw_Student

--练习2：创建视图，查看学生总成绩
--创建视图,实现查询学生各学期参加考试的总成绩
if exists(select * from sysobjects where name = 'vw_student_result_info')
drop view vw_student_result_info
go
CREATE VIEW vw_student_result_info
AS
SELECT StudentName AS 姓名,Student.StudentNo AS 学号
,Phone AS 联系电话,GradeName AS 学期
,SUM(StudentResult) AS 总成绩
FROM Student
INNER JOIN Grade ON Student.GradeId = Grade.GradeId
INNER JOIN Result ON Student.StudentNo = Result.StudentNo
Group By Student.StudentNo,StudentName,Phone,GradeName
GO

SELECT * FROM vw_student_result_info --查看视图结果

--示例3：在Student表的StudentName列创建非聚集索引
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'IX_Student_StudentName')
DROP INDEX Student.IX_Student_StudentName --删除索引
GO

/*--学生姓名列创建非聚集索引：填充因子为30％--*/
CREATE NONCLUSTERED INDEX IX_Student_StudentName
ON Student(StudentName)
WITH FILLFACTOR = 30
GO

/*----指定按索引：IX_Student_StudentName查询----*/
SELECT * FROM Student
WITH (INDEX=IX_Student_StudentName)
WHERE StudentName LIKE '李%'

--示例4：查看索引
EXEC sp_helpindex Result --查看Result表上创建的索引

--查看MySchool数据库中创建的所有索引
USE MySchool
SELECT * FROM sys.indexes

 

BEGIN TRANSACTION DECLARE @errorSum INT
SET @errorSum=0 /*--查询Result表中所有Y2学生的考试成绩，保存到新表HistoreResult*/ SELECT Result.* INTO HistoreResult FROM Result INNER JOIN Student ON Result.StudentNo=Student.StudentNo INNER JOIN Grade ON Grade.GradeId=Student.GradeId WHERE GradeName='Y2' SET @errorSum=@errorSum +@@ERROR /*--删除Result表中所有Y2学生的考试成绩*/ DELETE Result FROM Result JOIN Student ON Result.StudentNo=Student.StudentNo INNER JOIN Grade ON Grade.GradeId=Student.GradeId WHERE GradeName='Y2' SET @errorSum=@errorSum+@@ERROR if(@errorSum > 0) BEGIN
PRINT '插入失败，回滚事务'
ROLLBACK TRANSACTION
END
ELSE
BEGIN
PRINT '插入成功，提交事务'
COMMIT TRANSACTION
END
GO