2023年-汽车站数据库设计

汽车站数据库设计

一、系统简要介绍主要实现的功能是汽车站驾驶人员和路线及汽车等基本信息进行管理，包括浏览驾驶员信息、路线信息，查询和统计一些驾驶员、汽车等数据，修改驾驶员行走的路线信息等

二、本系统有四张表组成，表结构如下:驾驶员表（驾驶员编号,汽车编号，姓名，生日，性别，工作年限）汽车表（汽车编号,型号，载客量，票价）路线表（路线编号,目的地，行程时间，出发地）汽车路线表（汽车编号，路线编号）用户表（用户名，密码）

三、表间关系说明一个驾驶员属于一辆车，一辆车可以有多个驾驶员，一辆车可以行走多条路线，一条路线也可有多辆车行走

四、图如下E—R创建驾驶员表CREATE TABLEdriverdriverlD char20primary key,carJD char20NOT NULL,name char20unique,brithday char20,sex char2,standing char20,；foreign keycar_ID referencescarcar_ID

2.汽车表car字段含义字段名称字段类型汽车编号car_ID Char20primary key类型type char20载客量capacity Char20创建汽车表表CREATE TABLEcarcar_ID char20primary key,type char20,capacity char20,;

3.路线表line字段含义字段名称字段类型路线编号LineJD Char20primary key目的地destination char20not null所需时间take_time char20出发地start_address char20not null票价ticket_price Char20not null创建路线表:CREATE TABLEline」line Ddsteasrtti_naadtidorness char20not null,char20primary key,char20not null,char20,take_timeticket_price char20not null）；.汽车路线表carjine4字段含义字段名称字段类型汽车编号carlD Char20路线编号linelD char20创建汽车路线表CREATE TABLEcajlinecarlD char20,linelD char20,primary keycarlDJinelD；

5.用户表user_ID.字段含义字段名称字段类型汽车编号username Char20路线编号password char20创建用户表表:CREATE TABLEuser_IDusername varchar20,password varchar20,；primary keyusername,password六.插入数据insert.汽车表数据1飞鹤？insert intocar values091201,45;快鹿insert intocar valuesC091202:,40;鸵鸟insert intocar values091203,42;风火轮？insert intocar values691204;45;丫@筋斗云？insert intocar1115,09125;38;白龙马？；insert intocar values091206;42毛驴？insert intocar valuesC091207736;青牛？insert intocar values691208750;.驾驶员数据2吴±;insert intodriver driverID,car_ID,name,brithday,sex,standing values119491001,0912011981孙悟？；；insert intodriver driverID,car_ID,name,brithday,sex,standing valuesCl9491002;0912041974,r15;李娇？insert intodriver driverID,car_ID,name,brithday,sex,standing valuesCl9491003,091203;1979,O,9;夏雨荷？insert intodriver driverID,car_ID,name,brithday,sex,standing values fl9491004;,0912021,1983,6;刘备？；insert intodriver driverID,car_ID,name,brithday,sex,standing valuesCl9491005,091205;197716;诸葛亮？「,1985:4;；陈娜insert intodriver driverlD,car_ID,name,brithday,sex,standing valuesCl949100709120619750:17,;张海风insert intodriver driverID,car_ID,name,brithday,sex,standing values19491008,0912077,1978,TJO;朱翠翠？；insert intodriver driverID,car_ID,name,brithday,sex,standing values1980708;赵薇？insert intodriver driverID,car_ID,name,brithday,sex,standing valuesC1949101O,091204;1988,0:1;；李鸣insert intodriver driverID,car_ID,name,brithday,sex,standing valuesC19491011091208,1978,1712;；周杰磊？insert intodriver driverID,car_ID,name,brithday,sex,standing values194910120912031981:1,4;.路线表数据3上海小时鬲京insert intoline line_ID,destination,take_time,start_address,ticket_price values201001;137123;南通:小时:鬲insert intoline line_ID,destination,take_time,start_address,ticket_price values2010024京233;苏州？个半小时？insert intoline line_ID,destination,take_time,start_address,ticket_price values201003:3南京？256;盐城小时分insert intoline line_ID,destination,take_time,start_address,ticket_price values2010041320南京？330;泰州？个半小时？insert intoline line_ID,destination,take_time,start_address,ticket_price values201005;2南京？450;扬州？小时,，鬲insert intoline line_lD,destination,take_time,start_address,ticket_price values201006;2京？326;上海？小时？南insert intoline line_ID,destination,take_time,start_address,ticket_price values2010073京？；527苏州小时,，嗝京；insert intoline line_ID,destination,take_time,start_address,ticket_price values201008,,3430;.汽车路线表数据4insert intocar_linecarIDJineID values191208\,201007,;insert intocar_linecarIDJineID values91206,201003;insert intocarjine carTDJinelDvaluesf「,insert intocarjine carlDJinelDvalues9120201004;insert intoCaroline carlDJinelDvalues191203\,201006,;insert intocar_line carlDJinelDvalues11;insertinto car_line carTDJinelDvalues!.用户表数据5丘冬insert intouser_ID valuesB JI23456;七.索引建立索引遵循的规律建立在子句经常引用的列上，

1.where.经常需要排序的列上，

2.连接属性列上等3create indexcar_index oncarcar_ID;-汽车表在汽车编号字段上建立索引create indexdriver_index ondriverdriverlD;--驾驶员表在驾驶员编号字段上建立索引create indexline_index onlinelinelD;--路线表在路线编号字段上建立索引create indextake_time_index onlinetake_time;--路线表在路线诙需底间上建立索引，口备查询各路线所需时间八.视图,创建过程查询各路线所花时间1take_time_list,」create viewtake_time istselect line_ID,take_time from line;select*from taketime list;结果如下select*from take_time_listline.ID take_time12010013小时22010024小时32010033个半小时42010043小时20分52010052个半小时62010062小时72010073小时82010083小时.创建过程查询各路线所需票价2ticket_price,create viewticket_pnceasselect destination,start_address,ticket_price；from line；select*from ticket_price结果如下select*from ticket_price.创建过程查询各路线行驶的所有驾驶员及车辆3car_line_driver,destination start address ticket pricecreate viewcar_line_driverselect line.destination,start_address,dnverID,car.car_ID fromcar,line,driverselect*from car_line_driver结果如下12345678910|1112I1Q/IQI nmnai om九.查询查询有多少位驾驶员执行结果

1.Select count*from driverdriverIDcar_ID namebrithday sexstanding吴士1_19491001091201198116219491002091204孙悟1974151319491003091203李娇197909419491004091202夏雨荷198306519491005091205刘备1977116619491006091208诸君元198514719491007091206陈娜1975017819491008091207张海风1978110919491009091201朱翠翠1980081019491010091204赵薇1988011119491011091208李鸣19781121219491012091203周杰磊

198114.查询查询驾驶员的某些信息2select driverlD,name,sex from driverdriverID namesex119491001葛荷1亮219491002吴1319491003孙士李悟0419491004夏娇0519491005刘雨1619491006诸备陈娜1719491007张海风8194910080朱翠翠9194910091赵薇10194910100李鸣11194910110周杰磊12194910121执行结果.查询某型号汽车走那些路线3selectline_ID,destination fromlinewhere line_ID=201005执行结果select1ine_ID,destination fromlineline_ID dest ination1201005where line.ID=201005•查询驾驶员的年龄，并按年龄降序排列4驾驶员名，年龄select name2010-brithdayfrom driverinner joincaron driver.car_ID=car.car_IDorder by2desc执行结果select name驾驶员名，2010-brithday年龄from driverinner joincar ondriver.car_ID=car.car_IDorder by2descv£L「驶员名1孙悟362陈娜刘备353334张海风325326311朱翠翠308周杰磊2992910夏雨荷2711诸葛元

2512225.查询工作年限超过年的驾驶员的名字8驾驶员名工龄执行结果Select name,standing fromdriver where standing8Select name驾驶员名,standing工龄fromdriverwherestanding驾驶员名工龄1孙悟152李娇93刘备164陈娜175张海风106李鸣

126.查询汽车站的线路段select*fromline;select*from car_line_driverline IDdestination taketime startaddressticketprice上海12010013小时南京123南通京2201002苏州4小时233南京3201003盐城3个半小时南京2564201004泰州3小时20分2个半小时南京330扬州5201005上海2小时南4506201006苏州3小时南京32672010073小时南京52181201008南京

4307.查询汽车表select*from carline drivercarID typecapacity轮云马1091201鹤鹿鸟2091202火斗无3091203驴牛飞快鸵风4091204筋白毛5091205青6091206709120780912080628520553434444。