學(xué)生基本信息數(shù)據(jù)庫(kù)代碼

學(xué)生基本信息數(shù)據(jù)庫(kù)代碼createdatabasestudjcusestudjccreatetableadminUsers(adminIDintIDentity(1,1)primarykey,adminNamevarchar(20),adminPwdvarchar(20))insertintoadminUsersvalues('wyx','1234')insertintoadminUsersvalues('sql','abcd')createtablezhuanye(zhuanyeIDintidentity(1,1)primarykey,zhuanyeNamevarchar(20))insertintozhuanyevalues('計(jì)算機(jī)應(yīng)用技術(shù)')insertintozhuanyevalues('軟件技術(shù)')insertintozhuanyevalues('計(jì)算機(jī)網(wǎng)絡(luò)技術(shù)')createtableclass(classIDintidentity(1,1)primarykey,zhuanyeIDintforeignkeyreferenceszhuanye(zhuanyeID),classNamevarchar(20))insertintoclassvalues(1,'計(jì)A081')insertintoclassvalues(1,'計(jì)B081')insertintoclassvalues(2,'軟件A081')insertintoclassvalues(2,'軟件A091')insertintoclassvalues(3,'網(wǎng)絡(luò)A081')insertintoclassvalues(2,'網(wǎng)絡(luò)A091')createtablezhengzhimm(mmIDintprimarykey,mmNamechar(4))insertintozhengzhimmvalues(1,'黨員')insertintozhengzhimmvalues(2,'團(tuán)員')insertintozhengzhimmvalues(3,'群眾')createtablestudUsers(studNumchar(10)primarykey,studNamevarchar(20),studSexchar(2),zhuanyeIDintforeignkeyreferenceszhuanye(zhuanyeID),classIDintforeignkeyreferencesclass(classID),mmIDintforeignkeyreferenceszhengzhimm(mmID))insertintostudUsersvalues('2008010101','張鳳','女','1','1',2)insertintostudUsersvalues('2008010102','李立芬','女','1','2',2)insertintostudUsersvalues('2008010201','王一','男','2','3',1)altertablestudUsersaddcheck(studSex='男'orstudSex='女')createtablejangli(jlIDintidentity(1,1)primarykey,jlNamevarchar(50),jlDanweivarchar(50),jlGradechar(10))altertablejangliaddunique(jlName,jlDanwei)insertintojanglivalues('三好學(xué)生','水利學(xué)院','院級(jí)')insertintojanglivalues('優(yōu)秀團(tuán)員','水利學(xué)院','院級(jí)')insertintojanglivalues('一等獎(jiǎng)學(xué)金','水利學(xué)院','院級(jí)')createtablechufa(cfIDintidentity(1,1)primarykey,cfNamevarchar(50),cfDanweivarchar(50),cfGradechar(10))insertintochufavalues('警告','水利學(xué)院','院級(jí)')insertintochufavalues('留校察看','水利學(xué)院','院級(jí)')createtablestudjl(studjlIDintidentity(1,1)primarykey,studNumchar(10)foreignkeyreferencesstudUsers(studNum),jlIDintforeignkeyreferencesjangli(jlID),jlXueqichar(1)notnull,jlTimedateTime,jlCailiaovarchar(50)null,jlYouxiaochar(1)default'y',jlChexiaoTimedatetimenull)altertablestudjladdunique(studNum,jlID,jlXueqi)insertintostudjl(studNum,jlID,jlXueqi,jlTime)values('2008010101',1,'1','2009-11-2')insertintostudjl(studNum,jlID,jlXueqi,jlTime)values('2008010102',2,'2','2009-11-2')insertintostudjl(studNum,jlID,jlXueqi,jlTime)values('2008010201',1,'2','2009-11-2')createtablestudcf(studcfIDintidentity(1,1)primarykey,studNumchar(10)foreignkeyreferencesstudUsers(studNum),cfIDintforeignkeyreferenceschufa(cfID),cfXueqichar(1)notnull,cfTimedateTime,cfCailiaochar(50)null,cfYouxiaochar(1)default'y',cfChexiaoTimedatetimenull)altertablestudcfaddunique(studNum,cfID,cfXueqi)insertintostudcf(studNum,cfID,cfXueqi,cfTime)values('2008010201',1,'1','2009-11-2')insertintostudcf(studNum,cfID,cfXueqi,cfTime)values('2008010101',2,'2','2009-11-12')createtablenews(newsIDintidentity(1,1)primarykey,titlevarchar(50),textvarchar(400))insertintonewsvalues('操作說(shuō)明','1.學(xué)生使用系統(tǒng)說(shuō)明;<br>2.管理員使用系統(tǒng)說(shuō)明.')創(chuàng)建視圖studxx，方便查詢學(xué)生的信息createviewstudxxasSELECTdbo.studUsers.studNum,dbo.studUsers.studName,dbo.studUsers.studSex,dbo.zhuanye.zhuanyeName,dbo.class.className,dbo.zhengzhimm.mmNameFROMdbo.studUsersINNERJOINdbo.classONdbo.studUsers.classID=dbo.class.classIDINNERJOINdbo.zhengzhimmONdbo.studUsers.mmID=dbo.zhengzhimm.mmIDINNERJOINdbo.zhuanyeONdbo.studUsers.zhuanyeID=dbo.zhuanye.zhuanyeIDANDdbo.class.zhuanyeID=dbo.zhuanye.zhuanyeID創(chuàng)建視圖studjlxx，方便查詢學(xué)生的獲獎(jiǎng)信息createviewstudjlxxasSELECTdbo.studUsers.studNumAS學(xué)號(hào),dbo.studUsers.studNameAS姓名,dbo.jangli.jlNameAS獎(jiǎng)勵(lì)名稱,dbo.studjl.jlXueqiAS獎(jiǎng)勵(lì)學(xué)期,dbo.studjl.jlYouxiaoAS有效性FROMdbo.studUsersINNERJOINdbo.studjlONdbo.studUsers.studNum=dbo.studjl.studNumINNERJOINdbo.jangliONdbo.studjl.jlID=dbo.jangli.jlID創(chuàng)建視圖studcfxx，方便查詢學(xué)生的處罰信息createviewstudcfxxasSELECTdbo.studUsers.studNumAS學(xué)號(hào),dbo.studUsers.studNameAS姓名,dbo.chufa.cfNameAS處罰名稱,dbo.studcf.cfXueqiAS處罰學(xué)期,dbo.studcf.cfYouxiaoAS有效性FROMdbo.studUsersINNERJOINdbo.studcfONdbo.studUsers.studNum=dbo.studcf.studNumINNERJOINdbo.chufaONdbo.studcf.cfID=dbo.chufa.cfID創(chuàng)建視圖jangli_view，方便查詢獎(jiǎng)勵(lì)的基本信息create