ACCP7.0優(yōu)化myschool數(shù)據(jù)庫設(shè)計(jì)
ACCP課程體系通過結(jié)合先進(jìn)的`多模式教學(xué)法，使學(xué)習(xí)者在掌握理論知識(shí)與工具的同時(shí)，具備良好的自我學(xué)習(xí)能力和個(gè)人素質(zhì)，成為符合21世紀(jì)企業(yè)要求的IT人才。下面是關(guān)于ACCP7.0優(yōu)化myschool數(shù)據(jù)庫設(shè)計(jì)，歡迎大家參考!
上機(jī)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 '操作失敗,回滾事務(wù)'
rollback transaction
end
else
begin
print '操作成功,保存事務(wù)'
commit transaction
end
--上機(jī)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 '操作失敗,回滾事務(wù)'
rollback transaction
end
else
begin
print '操作成功,保存事務(wù)'
commit transaction
end
--上機(jī)3
go
CREATE VIEW vw_student_result_info
AS
SELECT 姓名=StudentName,學(xué)號(hào)=Student.StudentNo,
聯(lián)系電話=Phone,學(xué)期=GradeName,成績(jī)=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
--上機(jī)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
【ACCP7.0優(yōu)化myschool數(shù)據(jù)庫設(shè)計(jì)】相關(guān)文章：
1.OracleDBA數(shù)據(jù)庫性能進(jìn)行優(yōu)化
2.Linux數(shù)據(jù)庫的MySQL性能優(yōu)化技巧
3.ACCP7.0課程設(shè)計(jì)理念簡(jiǎn)介
4.Linux數(shù)據(jù)庫：關(guān)鍵的MySQL性能優(yōu)化技巧
5.2016最數(shù)據(jù)庫設(shè)計(jì)技巧