汽車(chē)站數(shù)據(jù)庫(kù)設(shè)計(jì)一、系統(tǒng)簡(jiǎn)要介紹主要實(shí)現(xiàn)的功能是汽車(chē)站駕駛?cè)藛T和路線及汽車(chē)等基本信息進(jìn)行管理，包括瀏覽駕駛員信息、路線信息，查詢(xún)和統(tǒng)計(jì)一些駕駛員、汽車(chē)等數(shù)據(jù)，修改駕駛員行走的路線信息等。二、本系統(tǒng)有四張表組成，表結(jié)構(gòu)如下:駕駛員表：（駕駛員編號(hào),汽車(chē)編號(hào)，姓名，生日，性別，工作年限）汽車(chē)表：（汽車(chē)編號(hào),型號(hào)，載客量，票價(jià)）路線表：（路線編號(hào),目的地，行程時(shí)間，出發(fā)地）汽車(chē)路線表：（汽車(chē)編號(hào)，路線編號(hào)）用戶(hù)表：（用戶(hù)名，密碼）三、表間關(guān)系說(shuō)明一個(gè)駕駛員屬于一輛車(chē)，一輛車(chē)可以有多個(gè)駕駛員，一輛車(chē)可以行走多條路線，一條路線也可有多輛車(chē)行走。四、E—R圖如下：創(chuàng)建駕駛員表：CREATETABLEdriverdriverlD char(20)primarykey,carJD char(20)NOTNULL,name char(20)unique,brithday char(20),sex char(2),standing char(20),foreignkey(car_ID)referencescar(car_ID))；.汽車(chē)表car：字段含義字段名稱(chēng)字段類(lèi)型汽車(chē)編號(hào)car_IDChar(20)primarykey類(lèi)型typechar(20)載客量capacityChar(20)創(chuàng)建汽車(chē)表表：CREATETABLEcar(car_ID char(20)primarykey,type char(20),capacity char(20),);.路線表line字段含義字段名稱(chēng)字段類(lèi)型路線編號(hào)LineJDChar(20)primarykey目的地destinationchar(20)notnull所需時(shí)間take_timechar(20)出發(fā)地start_addresschar(20)notnull票價(jià)ticket_priceChar(20)notnull創(chuàng)建路線表:line」line」Ddestinationtake_timechar(20)primarykey,char(20)notnull,char(20),start_addresschar(20)notnull,start_addresschar(20)notnull,ticket_pricechar(20)notnull）；4.汽車(chē)路線表carjine字段含義字段名稱(chēng)字段類(lèi)型汽車(chē)編號(hào)carlDChar(20)路線編號(hào)linelDchar(20)創(chuàng)建汽車(chē)路線表：CREATETABLEcajlinecarlD char(20),linelDchar(20),

primarykey(carlDJinelD))；5.用戶(hù)表user_ID.字段含義字段名稱(chēng)字段類(lèi)型汽車(chē)編號(hào)usernameChar(20)路線編號(hào)passwordchar(20)創(chuàng)建用戶(hù)表表:CREATETABLEuser_ID(username varchar(20),passwordvarchar(20),primarykey(username,password))；六.插入數(shù)據(jù)insert.汽車(chē)表數(shù)據(jù)insertintocarvalues('091201',飛鶴？45');insertintocarvaluesC091202:快鹿','40);insertintocarvalues('091203?鴕鳥(niǎo)','42');insertintocarvalues(691204;風(fēng)火輪？45);insertintocar丫@111。5,0912。5;筋斗云？38);insertintocarvalues('091206;白龍馬？42')；insertintocarvaluesC0912077毛驢？36);insertintocarvalues(6912087青牛？50);.駕駛員數(shù)據(jù)insertintodriver(driverID,car_ID,name,brithday,sex,standing)values(119491001','091201?吳±;1981insertintodriver(driverID,car_ID,name,brithday,sex,standing)valuesCl9491002;'091204?孫悟？1974,；r；15');insertintodriver(driverID,car_ID,name,brithday,sex,standing)valuesCl9491003','091203;李嬌？1979','O',9);insertintodriver(driverID,car_ID,name,brithday,sex,standing)valuesfl9491004;,0912021夏雨荷？1983',。,6);insertintodriver(driverID,car_ID,name,brithday,sex,standing)valuesCl9491005','091205;劉備？1977"；16');諸葛亮？1985:「,4);insertintodriver(driverlD,car_ID,name,brithday,sex,standing)valuesCl9491007'；091206?陳娜?1975?0:17,);insertintodriver(driverID,car_ID,name,brithday,sex,standing)values('19491008','0912077張海風(fēng)','1978',TJO');insertintodriver(driverID,car_ID,name,brithday,sex,standing)values朱翠翠？198070；8);insertintodriver(driverID,car_ID,name,brithday,sex,standing)valuesC1949101O','091204;趙薇？1988','0:1');insertintodriver(driverID,car_ID,name,brithday,sex,standing)valuesC19491011'；091208','李鳴1978','1712');insertintodriver(driverID,car_ID,name,brithday,sex,standing)values('19491012'；091203?周杰磊？1981:1,4);.路線表數(shù)據(jù)insertintoline(line_ID,destination,take_time,start_address,ticket_price)values(201001;上海13小時(shí)(鬲京7123);insertintoline(line_ID,destination,take_time,start_address,ticket_price)values(201002?南通:4小時(shí):鬲京?233);insertintoline(line_ID,destination,take_time,start_address,ticket_price)values(201003:蘇州？3個(gè)半小時(shí)？南京？256);insertintoline(line_ID,destination,take_time,start_address,ticket_price)values(201004?鹽城13小時(shí)20分。南京？330);insertintoline(line_ID,destination,take_time,start_address,ticket_price)values(201005;泰州？2個(gè)半小時(shí)？南京？450);insertintoline(line_lD,destination,take_time,start_address,ticket_price)values(201006;揚(yáng)州？2小時(shí),，鬲京？326);insertintoline(line_ID,destination,take_time,start_address,ticket_price)values(201007?上海？3小時(shí)？南京？527)；insertintoline(line_ID,destination,take_time,start_address,ticket_price)values('201008','蘇州','3小時(shí),，嗝京；430);.汽車(chē)路線表數(shù)據(jù)insertintocar_line(carIDJineID)values(191208\,201007,);insertintocar_line(carIDJineID)values('91206','201003');insertintocarjine(carTDJinelD)values(finsertintocarjine(carlDJinelD)values('9120「,'201004');insertintoCaroline(carlDJinelD)values(191203\,201006,);insertintocar_line(carlDJinelD)values(11);insertintocar_line(carTDJinelD)values(!.用戶(hù)表數(shù)據(jù)insertintouser_IDvalues('丘B(yǎng)冬'JI23456');七.索引：建立索引遵循的規(guī)律：.建立在where子句經(jīng)常引用的列上，.經(jīng)常需要排序的列上，.連接屬性列上等createindexcar_indexoncar(car_ID);汽車(chē)表在汽車(chē)編號(hào)字段上建立索引createindexdriver_indexondriver(driverlD);-駕駛員表在駕駛員編號(hào)字段上建立索引createindexline_indexonline(linelD);-路線表在路線編號(hào)字段上建立索引createindextake_time_indexonline(take_time);-路線表在路線詼需底間上建立索引，口備查詢(xún)各路線所需時(shí)間八.視圖,創(chuàng)建過(guò)程take_time_list,查詢(xún)各路線所花時(shí)間：createviewtake_time」istselectline_ID,take_timefromline;select*fromtaketimelist;結(jié)果如下：select*fromtake_time_listline.IDtake_time12010013小時(shí)22010024小時(shí)32010033個(gè)半小時(shí)42010043小時(shí)20分52010052個(gè)半小時(shí)62010062小時(shí)72010073小時(shí)82010083小時(shí).創(chuàng)建過(guò)程ticket_price,查詢(xún)各路線所需票價(jià)createviewticket_pnceasselectdestination,start_address,ticket_pricefromline；select*fromticket_price；結(jié)果如下：select*fromticket_pricedestination startaddress ticketpricedestination startaddress ticketprice3.創(chuàng)建過(guò)程3.創(chuàng)建過(guò)程car_line_driver,查詢(xún)各路線行駛的所有駕駛員及車(chē)輛createviewcar_line_driverselectline.destination,start_address,dnverID,car.car_IDfromcar,line,driverselect*fromcar_line_driver結(jié)果如下：091201091201091201091201091201091201091201091201091201091201091201091201091201091201091201091201091201091201194910011949100219491003194910041949100519491006194910071949100819491009194910101949101119491012南京南京南京南京南京南京南京南京南京南京南京南京12345678910|1112Inaiom1q/iqinmnaiom九.查詢(xún).查詢(xún)有多少位駕駛員Selectcount(*)fromdriver執(zhí)行結(jié)果：driverIDcar_IDnamebrithdaysexstanding1_19491001091201吳士198116219491002091204孫悟1974115319491003091203李嬌197909419491004091202夏雨荷198306519491005091205劉備1977116619491006091208諸君元198514719491007091206陳娜1975017819491008091207張海風(fēng)1978110919491009091201朱翠翠1980081019491010091204趙薇1988011119491011091208李鳴19781121219491012091203周杰磊198114.查詢(xún)查詢(xún)駕駛員的某些信息selectdriverlD,name,sexfromdriver執(zhí)行結(jié)果：driverIDnamesex12345driverIDnamesex123456194910011949100219491003194910041949100519491006荷亮士悟嬌雨備葛吳孫李夏劉諸7 19491007 陳娜8 19491008 張海風(fēng)9 19491009 朱翠翠10 19491010 趙薇11 19491011 李鳴12 19491012 周杰磊110011010011.查詢(xún)某型號(hào)汽車(chē)走那些路線selectline_ID,destinationfromlinewhereline_ID='201005'執(zhí)行結(jié)果：select1ine_ID,destinationfromlinewhereline.ID='201005'line_ID1 201005line_ID1 201005destination4?查詢(xún)駕駛員的年齡，并按年齡降序排列。selectname駕駛員名，2010-brithday年齡fromdriverinnerjoincarondriver.car_ID=car.car_IDorderby2desc執(zhí)行結(jié)果：selectname駕駛員名，2010-brithday年齡fromdriverinnerjoincaron