资源描述:
《广工数据库实验代码》由会员上传分享,免费在线阅读,更多相关内容在工程资料-天天文库。
1、第一次实验代码:1、创建数据库:CREATEDATABASESCON(NAME=SCDAT,FILENAME二'E:5944SC.mdf',SIZE=30MB,FTLEGROWTH20%)LOGON(NAMESCLOG,FILENAME二'E:5944SC.ldf,SIZE-3MB,FILEGROWTH二1MB)2、创建基本表:1、DEPARTMENT表USESCGOCREATETABLEDEPARTMENT(dnochar(6),deptnamechar(20)notnull,headerchar(8),PRIMARYKEY(dno))2、TEACHER表
2、USESCGOCREATETABLETEACHER(tnochar(8),t_namechar(8)notnull,t_sexchar(2),t_birthdaysmalldatetime,dnochar(6),teachtitlechar(10),PRIMARYKEY(t_no),FOREIGNKEY(dno)REFERENCESDEPARTMENT(dno))3、SPECIALITY表USESCGOCREATETABLESPECIALITY(spnochar(8),dnochar(6)notnull,spnamechar(20)notnull,PRIMARYKE
3、Y(spno),FOREIGNKEY(dno)REFERENCESDEPARTMENT(dno))4.STUDENT表USESCGOCREATETABLESTUDENT(s_nochar(8),snamechar(8)notnull,sexchar(2),sbirthdaySmalldatetime,dnochar(6),spnochar(8),classnochar(4),PRIMARYKEY(s_no),FOREIGNKEY(dno)REFERENCESDEPARTMENT(dno),FOREIGNKEY(spno)REFERENCESSPECIALITY(sp
4、no))5、COURSE表USESCGOCREATETABLECOURSE(enochar(10),cnamechar(20)notnull,spnochar(8),ctnotinyint,lecturetinyint,experimenttinyint,semestertinyint,credittinyint,PRIMARYKEY(eno),FOREIGNKEY(spno)REFERENCESSPECIALITY(spno))6、STLDENT_COURSE表USESCGOCREATETABLESTUDENT_COURSE(s_nochar(8),tcidsma
5、llint,scoretinyint,PRIMARYKEY(s_no,tcid),FOREIGNKEY(sno)REFERENCESSTUDENT(sno))7、CLASS表USESCGOCREATETABLECLASS(spnochar(8),classnochar(4),headerchar(8),PRIMARYKEY(spno,classno),FOREIGNKEY(spno)REFERENCESSPECIALITY(spno))8、TEACIIER_COURSE表USESCGOCREATETABLETEACHERCOURSEtcidsmallint,tnoc
6、har(8),spnochar(8),classnochar(4),enochar(10)notnull,semesterchar(6),schoolyearchar(10),PRIMARYKEY(tcid),FOREIGNKEY(t^no)REFERENCESTEACHER(tno),FOREIGNKEY(spno)REFERENCESSPECIALITY(spno),FOREIGNKEY(eno)REFERENCESCOURSE(eno))用UPDATE语句更改student表中数据:UPDATESTUDENTSETsname=,贾兰',sex二,男,where
7、s_no二'5941';用DELETE语句删除student表中数据:deletefromSTUDENTcoursewheres_no二'5942';deletefromSTUDENTwheresno二'5942';第二次实验代码:查询全部学生的学号、姓名、性别和出身年月日:SELECTs_no,sname,sex,sbirthdayFROMstudent查询全体学生的学号、姓名和年龄:SELECTsno,sname,year(getdate())-Year(sbirthday)FROMstudent查询院系编号为‘510000,(计算机学院)的全部学生的学号、