-:事物4个特性
1.原子性
2.一致性
3,永久性
4.隔离性
二:事物分类
1.原子性
2.一致性
3,永久性
4.隔离性
二:事物分类
显式事务,隐式事物,自动提交事务
三:视图操作
create view vw—StuInfo
AS
select * from Student
三:视图操作
create view vw—StuInfo
AS
select * from Student
1,视图不能进行增删改查擦
视图中的select后不能跟
视图可以嵌套orderby,除非Top关键字
不能有into
引用零时表和变量
四:
索引(index)加快表中的检索记录,同时降低增删改数度
索引会降低增删改效率
1:
BEGIN TRANSACTION
DECLARE @errorSum INT
SET @errorSum=0
/*--插入数据--*/
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
VALUES(1,1,GETDATE(),90)
SET @errorSum=@errorSum+@@error
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
VALUES(1,1,GETDATE(),70)
SET @errorSum=@errorSum+@@error
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
VALUES(2,1,GETDATE(),67)
SET @errorSum=@errorSum+@@error
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
VALUES(2,1,GETDATE(),55)
SET @errorSum=@errorSum+@@error
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
VALUES(3,1,GETDATE(),102)--分数违反约束
SET @errorSum=@errorSum+@@error
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
VALUES(3,4,GETDATE(),90)
SET @errorSum=@errorSum+@@error
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
VALUES(4,4,GETDATE(),56)
SET @errorSum=@errorSum+@@error
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
VALUES(4,4,GETDATE(),88)
SET @errorSum=@errorSum+@@error
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
VALUES(5,4,GETDATE(),40)
SET @errorSum=@errorSum+@@error
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
VALUES(5,4,GETDATE(),65)
SET @errorSum=@errorSum+@@error
/*--根据是否有错误,确定事务是提交还是撤销--*/
IF(@errorSum<>0) --如果有错误
BEGIN
PRINT '插入失败,回滚事务'
ROLLBACK TRANSACTION
END
ELSE
BEGIN
PRINT '插入成功,提交事务'
COMMIT TRANSACTION
END
GO
2:
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
/*--将Student表中所有Y2的学员记录,保存到新表HistoryStudent*/
SELECT Student.* INTO HistoryStudent
FROM Student INNER JOIN Grade ON Grade.GradeId=Student.GradeId
WHERE GradeName='Y2'
SET @errorSum=@errorSum+@@error
/*--删除Studet表中所有Y2学员记录*/
DELETE Student FROM Student
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
3:
CREATE VIEW vw_student_result_info
AS
SELECT 姓名=StudentName,学号=Student.StudentNo,
联系电话=Phone,学期=GradeName,成绩=Total
FROM Student
LEFT OUTER JOIN (
SELECT r.StudentNo,GradeName,SUM(StudentResult) Total
FROM Result r
INNER JOIN (
SELECT StudentNo,SubjectNo,MAX(ExamDate) ExamDate
FROM Result
GROUP BY StudentNo,SubjectNo) tmp
ON r.ExamDate=tmp.ExamDate
AND r.SubjectNo = tmp.SubjectNo AND r.StudentNo = tmp.StudentNo
INNER JOIN Subject sub ON sub.SubjectNo = r.SubjectNo
INNER JOIN Grade g ON g.GradeId = sub.GradeId
GROUP By r.StudentNo,GradeName ) TmpResult2
ON Student.StudentNo = TmpResult2.StudentNo
GROUP BY StudentName,Student.StudentNo,Phone,GradeName,Total
GO
SELECT * FROM vw_student_result_info
4:
USE MySchool
GO
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'IX_Result_StudentResult')
DROP INDEX Result.IX_Result_StudentResult --删除索引
GO
--考试成绩列上创建可重复索引:填充因子为70%--
CREATE INDEX IX_Result_StudentResult
ON Result(StudentResult)
WITH FILLFACTOR = 70
GO
--查询输出学生姓名、课程名称、考试日期和考试成绩
SELECT StudentName '学生姓名',SubjectName '课程名称',ExamDate '考试日期', StudentResult '考试成绩' FROM Subject,Student,Result
WITH (INDEX = IX_Result_StudentResult)
WHERE StudentResult BETWEEN 80 AND 90 AND Student.StudentNo = Result.StudentNo AND Subject.SubjectId = Result.SubjectId