數據庫課程設計民航售票系統方案(編輯修改稿)

　

【文章內容簡介】 102417Sell60000173EE1329418Sell130000181DFE453102419Sell140000192HY6783888表318 公告欄表Notice2NnoMnoNsayNtim11請您不要在公共場合吸煙……請去吸煙室。2010061922請您不要在公共場合吸煙……請去吸煙室。2010062033有位乘客在候機室里撿到一個錢包，請失主于管理處認領。2010062144請您不要在公共場合吸煙……請去吸煙室。2010062255請不要在機場門口乘坐黑車，以免上當受騙。2010062366有位乘客丟失了自己的護照，請拾到的朋友聯系：18986523698.2010062477請您不要在公共場合吸煙……請去吸煙室。2010062581請不要在機場門口乘坐黑車，以免上當受騙。2010062692請您不要在公共場合吸煙……請去吸煙室。20100627103請不要在機場門口乘坐黑車，以免上當受騙。20100628表319 后臺管理人員表People2PnoPnaPsexPageZnoZW1李一涵女231公告員2郭小葉女241公告員3劉楓男261公告員4謝欣然女231公告員5麥云潔女221公告員6蓯蓉女201公告員7葉漓江男282公告長8張妍女243勞資員9陳皓男314勞資科長表320 工資表Moneys2MnoMlevMnum1125002125003125004125005125006125007125008235009335501044850表321 工資發放表Gmoney2MnoPnoGtim112010060123201006013520100601442010060151020100601692010060172201006018820100601972010060110620100601/*建立數據庫*/use mastergocreate database minhangon(name=minhang_data,filename=39。d:\39。,size=5MB,maxsize=500MB,filegrowth=5MB)log on(name=minhang_log,filename=39。d:\39。,size=5MB,maxsize=50MB,filegrowth=10%)/*創建表*/use minhanggocreate table Users/*創建Users表*/(Uno char(4) primary key not null,Una1 varchar(20) not null,Upas varchar(16) not null,Una2 varchar(30) not null,Usex char(2) null,Uage int null,Utel varchar(20) null,Umes varchar(30) null)gocreate table Cabin/*創建Cabin表*/(Cno char(2) primary key not null,Ctype varchar(10) not null,Cnum int not null,Cpri1 int not null,Cpri2 int not null)gocreate table Flight/*創建Flight表*/(Fno char(5) primary key not null,Ftype varchar(6) not null,City1 varchar(10) not null,City2 varchar(10) not null,Time1 varchar(20) not null,Time2 varchar(20) not null,Ftim varchar(20) not null)gocreate table Airline/*創建Airline表*/(Ano char(4) primary key not null,Ana varchar(30) not null,Aman varchar(10) not null,Atel varchar(20) not null)gocreate table Ticket/*創建Ticket表*/(Tno char(6) primary key not null,Tna varchar(20) not null,Tpos char(18) not null,Ttel varchar(20) not null)gocreate table Twork/*創建Twork表*/(Wno char(4) primary key not null,Nature char(4) not null,Tno char(6) not null,Uno char(4) not null,Cno char(2) not null,Fno char(5) not null,Ano char(4) not null,Pri int not null)gocreate table Notice/*創建Notice表*/(Nno char(4) primary key not null,Mno char(4) not null,Nsay varchar(1000) not null,Ntim varchar(30) not null)gocreate table People/*創建People表*/(Pno char(4) primary key not null,Pna varchar(20) not null,Psex char(2) null,Page int null,Zno char(2) not null,ZW varchar(20) not null)gocreate table Moneys/*創建Moneys表*/(Mno char(4) primary key not null,Mlev char(2) not null,Mnum int not null)gocreate table Gmoney/*創建Gmoney表*/(Mno char(4) not null,Pno char(4) not null,Gtim varchar(20) not null,constraint PK_G primary key(Mno,Pno))goalter table Noticeadd constraint FK_N foreign key(Pno)references People(Pno)圖32 表創建效果/*插入記錄*/運用以下語句來插入記錄，這里不一一列出use database_namegoinsert into table_namevalues(column1,column2,column3……)go實際操作中，這里用到insert table1 select * from OPENROWSET(39。39。 ,39。Excel 。IMEX=1。HDR=YES。DATABASE=C:\39。 ,[Sheet1$])可以快速導入數據插入實例：use minhanggoinsert into Ticketvalues(20,39。劉林39。,13545091010,420202198807101314)goinsert into Usersvalues(15,39。liulin39。,00099922,39。劉林39。,39。男39。,21,13545091010,39。liulin@39。)goinsert into Tworkvalues(20,39。Sell39。,20,15,3,39。WER5639。,2,588)go修改實例：use minhanggoupdate Flightset Ftim=39。7H39。where Fno=39。ABX9839。goupdate Usersset Usex=nullwhere Una2=39。劉林39。goupdate Usersset Uage=nullwhere Uno=15go刪除實例：use minhanggodelete Userswhere Uno=15godelete Ticketwhere Tno=20godelete Tworkwhere Wno=20go查詢實例：/*查詢到廣州的航班（條件查詢）*/select*from Flightwhere City2=39。廣州39。圖33 查詢實例1/*查詢到從成都起飛的航班有哪些（條件查詢）*/select*from Flightwhere City1=39。成都39。圖34 查詢實例2/*查詢機型為A380的航班（條件查詢）*/select*from Flightwhere Ftype=39。A38039。圖35 查詢實例3/*查詢飛行時間大于小時的航班（條件查詢）*/select*from Fli