第六章:事务,视图和索引
来源:互联网 发布:淘宝卖家钱多久到账 编辑:程序博客网 时间:2024/06/05 11:01
- --上机1
- use MySchool
- go
- begin transaction
- declare @errornum int
- set @errornum=0
- insert into Result values('23219',1,90,'2013-09-12')
- set @errornum+=@@ERROR
- insert into Result values('23219',1,90,'2013-09-13')
- set @errornum+=@@ERROR
- insert into Result values('23219',1,90,'2014-09-12')
- set @errornum+=@@ERROR
- insert into Result values('23219',1,90,'2013-09-11')
- set @errornum+=@@ERROR
- insert into Result values('23219',1,90,'2003-09-12')
- set @errornum+=@@ERROR
- if @errornum<>0
- begin
- print '操作失败,回滚事务'
- rollback transaction
- end
- else
- begin
- print '操作成功,保存事务'
- commit transaction
- end
- --上机2
- go
- begin transaction
- declare @errornum int
- set @errornum=0
- select * into historyresult from Result
- where StudentNo in(select StudentNo from Student
- where GradeId=(select GradeId from Grade where GradeName='Y2'))
- set @errornum+=@@ERROR
- delete from Result
- where StudentNo in(select StudentNo from Student
- where GradeId=(select GradeId from Grade where GradeName='Y2'))
- set @errornum+=@@ERROR
- select * into historystudent from Student
- where GradeId=(select GradeId from Grade where GradeName='Y2')
- set @errornum+=@@ERROR
- delete from Student
- where GradeId=(select GradeId from Grade where GradeName='Y2')
- set @errornum+=@@ERROR
- if @errornum<>0
- begin
- print '操作失败,回滚事务'
- rollback transaction
- end
- else
- begin
- print '操作成功,保存事务'
- commit transaction
- end
- --上机3
- go
- 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,SubjectId,MAX(ExamDate) ExamDate
- FROM Result
- GROUP BY StudentNo,Subjectid) tmp
- ON r.ExamDate=tmp.ExamDate
- AND r.Subjectid = tmp.Subjectid AND r.StudentNo = tmp.StudentNo
- INNER JOIN Subject sub ON sub.Subjectid = r.Subjectid
- 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
- go
- create nonclustered index index_result
- on result(studentresult)
- select studentname,Subjectname,ExamDate,StudentResult from Result
- with(index=index_result)
- inner join Student on Student.StudentNo=Result.StudentNo
- inner join Subject on Subject.SubjectId=Result.SubjectId
- where StudentResult between 80 and 90
0 0
- 第六章:事务,视图和索引
- 优化myshool第六章 事务 视图 索引 上机 简答
- 事务、视图和索引
- 事务、视图和索引
- 06事务、视图和索引
- sql事务、视图和索引
- 事务、视图、索引和备份
- Mysql系列课程--第六章 索引和视图
- 黑马程序员--事务、视图和索引
- sqlite 视图、触发器、索引和事务总结
- sqlite 视图、触发器、索引和事务总结
- 第六章 事物、视图、索引整理
- 事务、索引、视图
- 事务、触发器、视图、索引
- 事务,视图,索引
- 视图 索引 事务
- 事务,视图,索引
- 事务 索引 视图
- Android4.1及以下RadioButton向右偏移不居中
- spring配置文件详解
- IDEA 专门发布了一个golang的IDE叫Goland
- 第七章:存储过程
- 关于指针的15个经典面试题
- 第六章:事务,视图和索引
- 第五章:项目:我的租房网
- 知识库--Using Akka Refs and Transactions(124)
- 学习笔记1--协方差矩阵
- 第四章:高级查询
- WEB入门之十六 操作DOM节点
- 第三章:SQL 编程
- docker简介及使用国内镜像源安装docker
- 第二章:数据库的实现