1、完整的SQL語(yǔ)句create database S_Cuse S_Ccreate table Student( StuID char(8) not null primary key , StuName varchar(10)not null, Tel varchar(13), Sage smallint, Ssex char(2)default 男 check (Ssex in (男,女), Semail varchar(20), Sintday datetime, GradeID char(6) not null, Snation varchar(20), Sadd varchar(40),

2、)create table Grade( GradeID char(6) not null primary key, GName varchar(20) not null, GNum int not null, Gassist varchar(10), Gdept varchar(20), Gacademy varchar(20) )select *from Gradecreate table Teacher( TID char(4) not null primary key, TName varchar(10) not null, Temail varchar(20), Tsex char(

3、2), Tnation varchar(20), Tadd varchar(20), Ttel varchar(13)create table Course( CID char(6) not null primary key, CName varchar(20)not null, Cnum varchar(2), Ccapa varchar(3), Cdate datetime, Croom char(5), C_XKL varchar(6)create table Manager( MID char(6) not null primary key, MName varchar(10) not

4、 null, Msex char(2), Mtel varchar(13), Madd varchar(40), Memil varchar(20) create table SC( SCID char(5) not null primary key, StuID char(8) not null, TID char(4), CID char(6) not null , Credit smallint, check ( Credit between 0 and 100), foreign key (StuID) references Student(StuID), foreign key (T

5、ID) references Teacher(TID), foreign key (CID) references Course(CID) ) insert into Studentvalues(,樂(lè)樂(lè),20,女,,2010-09-20,漢,廣東河源)insert into Studentvalues(,陳紅,20,女,,2010-09-20,漢,廣東佛山)insert into Studentvalues(,大樹(shù),20,男,,2010-09-20,回,廣東汕頭)insert into Student(StuID,StuName,Tel,

6、Sage,Semail,Sintday,GradeID ,Snation,Sadd)values(,李剛,21,,2009-09-20,漢,山東煙臺(tái))insert into Student(StuID,StuName,Tel,Sage,Semail,Sintday,GradeID ,Snation,Sadd)values(,大鷹,20,,2008-10-10,漢,山西太原)insert into Student(StuID,StuName,Tel,Sage,Semail,Sintday,GradeID ,Snation,Sadd)value

7、s(,大山,22,,2008-10-10,漢,陜西西安)insert into Studentvalues(,佳佳,19,女,,2009-09-20,漢,山西太原)insert into Studentvalues(,格里,6,20,女,,2009-09-20,傣,江西南昌)insert into Studentvalues(,可可,6,18,女,,2011-09-10,藏,廣東河源)insert into Studentvalues(,小黑,20,男,,19

8、91-02-20,漢,廣東河源)insert into Studentvalues(,小樂(lè),20,女,,2010-09-20,漢,廣東梅州)insert into Studentvalues(,小美,20,女,,2010-09-20,漢,廣東汕頭)insert into Studentvalues(,張強(qiáng),20,男,,2010-09-20,漢,廣東梅州)insert into Gradevalues(,信管,60,Lucy,信息管理與信息系統(tǒng),商學(xué)院)insert into Gradevalues(,信管,55,黃強(qiáng),

9、信息管理與信息系統(tǒng),商學(xué)院)insert into Gradevalues(,信管,60,加樂(lè),信息管理與信息系統(tǒng),商學(xué)院)insert into Gradevalues(,信管,55,美華,信息管理與信息系統(tǒng),商學(xué)院)insert into Gradevalues(,信管,60,陳德,信息管理與信息系統(tǒng),商學(xué)院)insert into Gradevalues(,信管,65,劉華,信息管理與信息系統(tǒng),商學(xué)院)insert into Gradevalues(,國(guó)貿(mào),65,黃里歐,國(guó)際貿(mào)易,商學(xué)院)insert into Gradevalues(,國(guó)貿(mào),65,黃歐,國(guó)際貿(mào)易,商學(xué)院)insert

10、into Gradevalues(,國(guó)貿(mào),60,黃歐,國(guó)際貿(mào)易,商學(xué)院)insert into Gradevalues(,市營(yíng),65,黃里歐,市場(chǎng)營(yíng)銷,商學(xué)院)insert into Gradevalues(,英語(yǔ),65,黃美麗,英語(yǔ),外國(guó)語(yǔ)學(xué)院)insert into Gradevalues(,英語(yǔ),65,姜麗,英語(yǔ),外國(guó)語(yǔ)學(xué)院)insert into Gradevalues(,計(jì)算機(jī),60,小飛,計(jì)算機(jī),計(jì)算機(jī)學(xué)院)insert into Gradevalues(,信科,65,天力,信科,信息科技學(xué)院)insert into Teachervalues (T001,張莉莉,sfuij123

11、,女,漢,廣東湛江,)insert into Teachervalues (T002,牛莉,sfuij123,女,回,山西太原,7)insert into Teachervalues (T003,李麗莉,3257j123,女,漢,陜西西安,)insert into Teachervalues (T004,璐璐,s7689fuij123,女,漢,廣東河源,)insert into Teachervalues (T005,黎強(qiáng),s35468j123,男,漢,廣東梅州,)insert into Teachervalues (T006,張吉,s0897j123,男,漢,廣東汕頭,)insert int

12、o Teachervalues (T007,魯克,578j123,男,漢,廣東湛江,)insert into Teachervalues (T008,匡闊,4678j1353,男,漢,陜西西安,)insert into Teachervalues (T009,陸羽,sf579j123,男,漢,廣東潮州,)insert into Teachervalues (T010,張鐵花,sdghij123,女,漢,廣東河源,)insert into Teachervalues (T011,張?zhí)烊A,sfuij135,男,漢,江西南昌,)insert into Course values (j001,計(jì)算機(jī)基

13、礎(chǔ),32,120,2011-3-01,JD306,必修)insert into Course values (g001,管理學(xué),32,90,2011-3-01,JB306,必修)insert into Course values (g002,微觀經(jīng)濟(jì)學(xué),32,110,2011-3-01,JD305,必修)insert into Course values (w001,英語(yǔ),32,60,2011-9-01,JD306,必修)insert into Course values (x001,兩性心理,18,120,2011-9-01,JD206,通選)insert into Course value

14、s (j004,C語(yǔ)言,32,90,2011-3-01,HH306,必修)insert into Course values (j002,Javal,32,120,2010-9-01,HF306,必修)insert into Course values (w003,經(jīng)濟(jì)法,32,120,2011-3-01,HE306,必修)insert into Course values (w002,日語(yǔ),32,120,2011-9-01,JD301,必修)insert into Course values (g003,管理系統(tǒng),32,100,2011-3-01,JD301,限選)insert into C

15、ourse values (w003,日語(yǔ),32,120,2011-3-01,JD301,必修)insert into Course values (t002,自動(dòng)化,32,120,2011-9-01,JD301,必修)insert into Course values (g004,物流管理,32,120,2011-9-01,JD301,限選)insert into SCvalues (00001,T001,g003,99)insert into SCvalues (00002,T003,t002,90)insert into SCvalues (00003,T003,t002,58)inse

16、rt into SCvalues (00004,T003,t002,79)insert into SCvalues (00005,T008,x001,88)insert into SCvalues (00006,T008,x001,98)insert into SCvalues (00007,T008,x001,69)insert into SCvalues (00008,T003,w001,89)insert into SCvalues (00009,T003,w001,77)insert into SCvalues (00010,T002,w001,76)insert into SCval

17、ues (00011,T001,g003,93)insert into SCvalues (00012,T001,g003,89)insert into SCvalues (00013,T001,g003,88)insert into SCvalues (00014,T001,g003,70)insert into SCvalues (00015,T001,g003,61)select * from Studentselect * from Gradeselect * from Teacherselect * from Courseselect * from SCcreate view stu

18、dentview(學(xué)生學(xué)號(hào),學(xué)生姓名,性別,班級(jí),輔導(dǎo)員,專業(yè),學(xué)院)asselect StuID,StuName,Ssex,GName,Gassist,Gdept,Gacademyfrom Student,Gradewhere Student.GradeID=Grade.GradeIDselect * from studentviewcreate view sc_view(學(xué)生學(xué)號(hào),學(xué)生姓名,課程名稱,授課教師,課程性質(zhì),成績(jī))asselect SC.StuID,StuName,CName,TName,C_XKL,Creditfrom Student , SC,Teacher ,Course

19、where Student.StuID=SC.StuID and Course.CID=SC.CID and Teacher.TID=SC.TIDselect * from sc_viewcreate view cs_view(學(xué)生學(xué)號(hào),學(xué)生姓名,課程名稱,授課教師,上課地點(diǎn),時(shí)間 )asselect SC.StuID,StuName,CName,TName, Croom ,Cdatefrom Student , SC,Teacher ,Coursewhere Student.StuID=SC.StuID and Course.CID=SC.CID and Teacher.TID=SC.TID

20、select * from cs_viewcreate nonclustered index index_StuNameon Student(StuName)create unique index index_StuIDon Student(StuID)create unique index index_cidon Course (CID)create nonclustered index index_CNameon Course column(CName)create nonclustered index index_scon SC(StuID,CID,Credit)exec sp_help

21、index SCcreate procedure Pro_基本信息as select StuID,StuName,Ssex,GName,Gassistfrom Student,Gradewhere Student.GradeID=Grade.GradeIDexec Pro_基本信息create procedure Pro_班級(jí)信息GradeID char(6)as select StuID,StuName,Ssex,Grade. GradeID,GNamefrom Student,Gradewhere Student.GradeID=Grade.GradeID and Student.Grad

22、eID=GradeID exec Pro_班級(jí)信息 GradeID=exec Pro_班級(jí)信息 GradeID=create trigger trig_studenton Student after updateas begin update Grade set GNum = GNum +1 where GradeID in () update Grade set Gnum=Gnum-1 where GradeID in ()endselect *from Gradeupdate Studentset GradeID= where StuID=select *from Gradeselect

23、*from Grade where GradeID= create trigger trig_counton Student after updateas begin update Grade set GNum = GNum +1 where GradeID in (select GradeID from inserted) update Grade set GNum=GNum -1 where GradeID in (select GradeID from deleted)endselect *from Student where StuID=select *From Grade where

24、 GradeID=update Studentset GradeID=where StuID=select*from Grade where GradeID=select * from Gradecreate trigger Trig_Son Student after deleteasdelete from Student where StuID in (select StuID from deleted)Gocreate trigger Tri_scroon SCafter deleteasdelete from Student where StuID in (select StuID from deleted)Godelete from SC where StuID=select * from Student where StuID=select * from SC where StuID=create trigger trig_denyupdateon SCfor update as if update ( Credit)beginraiserror (該事務(wù)不能被處理，【Credit】列的值不能被改變,10,1)rollback transactionendupdate SC set Credit=100where SCID=00014declare s