2023年-汽车站数据库设计

汽车站数据库设计一、系统简要介绍主要实现的功能是汽车站驾驶人员和路线及汽车等基本信息进行管理，包括浏览驾驶员信息、路线信息，查询和统计一些驾驶员、汽车等数据，修改驾驶员行走的路线信息等。二、本系统有四张表组成，表结构如下:驾驶员表：（驾驶员编号,汽车编号，姓名，生日，性别，工作年限）汽车表：（汽车编号,型号，载客量，票价）路线表：（路线编号,目的地，行程时间，出发地）汽车路线表：（汽车编号，路线编号）用户表：（用户名，密码）三、表间关系说明一个驾驶员属于一辆车，一辆车可以有多个驾驶员，一辆车可以行走多条路线，一条路线也可有多辆车行走。四、E—R图如下：创建驾驶员表：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))；.汽车表car：字段含义字段名称字段类型汽车编号car_IDChar(20)primarykey类型typechar(20)载客量capacityChar(20)创建汽车表表：CREATETABLEcar(car_ID char(20)primarykey,type char(20),capacity char(20),);.路线表line字段含义字段名称字段类型路线编号LineJDChar(20)primarykey目的地destinationchar(20)notnull所需时间take_timechar(20)出发地start_addresschar(20)notnull票价ticket_priceChar(20)notnull创建路线表:line」line」Ddestinationtake_timechar(20)primarykey,char(20)notnull,char(20),start_addresschar(20)notnull,start_addresschar(20)notnull,ticket_pricechar(20)notnull）；4.汽车路线表carjine字段含义字段名称字段类型汽车编号carlDChar(20)路线编号linelDchar(20)创建汽车路线表：CREATETABLEcajlinecarlD char(20),linelDchar(20),

primarykey(carlDJinelD))；5.用户表user_ID.字段含义字段名称字段类型汽车编号usernameChar(20)路线编号passwordchar(20)创建用户表表:CREATETABLEuser_ID(username varchar(20),passwordvarchar(20),primarykey(username,password))；六.插入数据insert.汽车表数据insertintocarvalues('091201',飞鹤？45');insertintocarvaluesC091202:快鹿','40);insertintocarvalues('091203?鸵鸟','42');insertintocarvalues(691204;风火轮？45);insertintocar丫@111。5,0912。5;筋斗云？38);insertintocarvalues('091206;白龙马？42')；insertintocarvaluesC0912077毛驴？36);insertintocarvalues(6912087青牛？50);.驾驶员数据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张海风','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);.路线表数据insertintoline(line_ID,destination,take_time,start_address,ticket_price)values(201001;上海13小时(鬲京7123);insertintoline(line_ID,destination,take_time,start_address,ticket_price)values(201002?南通:4小时:鬲京?233);insertintoline(line_ID,destination,take_time,start_address,ticket_price)values(201003:苏州？3个半小时？南京？256);insertintoline(line_ID,destination,take_time,start_address,ticket_price)values(201004?盐城13小时20分。南京？330);insertintoline(line_ID,destination,take_time,start_address,ticket_price)values(201005;泰州？2个半小时？南京？450);insertintoline(line_lD,destination,take_time,start_address,ticket_price)values(201006;扬州？2小时,，鬲京？326);insertintoline(line_ID,destination,take_time,start_address,ticket_price)values(201007?上海？3小时？南京？527)；insertintoline(line_ID,destination,take_time,start_address,ticket_price)values('201008','苏州','3小时,，嗝京；430);.汽车路线表数据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(!.用户表数据insertintouser_IDvalues('丘B冬'JI23456');七.索引：建立索引遵循的规律：.建立在where子句经常引用的列上，.经常需要排序的列上，.连接属性列上等createindexcar_indexoncar(car_ID);汽车表在汽车编号字段上建立索引createindexdriver_indexondriver(driverlD);-驾驶员表在驾驶员编号字段上建立索引createindexline_indexonline(linelD);-路线表在路线编号字段上建立索引createindextake_time_indexonline(take_time);-路线表在路线诙需底间上建立索引，口备查询各路线所需时间八.视图,创建过程take_time_list,查询各路线所花时间：createviewtake_time」istselectline_ID,take_timefromline;select*fromtaketimelist;结果如下：select*fromtake_time_listline.IDtake_time12010013小时22010024小时32010033个半小时42010043小时20分52010052个半小时62010062小时72010073小时82010083小时.创建过程ticket_price,查询各路线所需票价createviewticket_pnceasselectdestination,start_address,ticket_pricefromline；select*fromticket_price；结果如下：select*fromticket_pricedestination startaddress ticketpricedestination startaddress ticketprice3.创建过程3.创建过程car_line_driver,查询各路线行驶的所有驾驶员及车辆createviewcar_line_driverselectline.destination,start_address,dnverID,car.car_IDfromcar,line,driverselect*fromcar_line_driver结果如下：091201091201091201091201091201091201091201091201091201091201091201091201091201091201091201091201091201091201194910011949100219491003194910041949100519491006194910071949100819491009194910101949101119491012南京南京南京南京南京南京南京南京南京南京南京南京12345678910|1112Inaiom1q/iqinmnaiom九.查询.查询有多少位驾驶员Selectcount(*)fromdriver执行结果：driverIDcar_IDnamebrithdaysexstanding1_19491001091201吴士198116219491002091204孙悟1974115319491003091203李娇197909419491004091202夏雨荷198306519491005091205刘备1977116619491006091208诸君元198514719491007091206陈娜1975017819491008091207张海风1978110919491009091201朱翠翠1980081019491010091204赵薇1988011119491011091208李鸣19781121219491012091203周杰磊198114.查询查询驾驶员的某些信息selectdriverlD,name,sexfromdriver执行结果：driverIDnamesex12345driverIDnamesex123456194910011949100219491003194910041949100519491006荷亮士悟娇雨备葛吴孙李夏刘诸7 19491007 陈娜8 19491008 张海风9 19491009 朱翠翠10 19491010 赵薇11 19491011 李鸣12 19491012 周杰磊110011010011.查询某型号汽车走那些路线selectline_ID,destinationfromlinewhereline_ID='201005'执行结果：select1ine_ID,destinationfromlinewhereline.ID='201005'line_ID1 201005line_ID1 201005destination4•查询驾驶员的年龄，并按年龄降序排列。selectname驾驶员名，2010-brithday年龄fromdriverinnerjoincarondriver.car_ID=car.car_IDorderby2desc执行结果：selectname驾驶员名，2010-brithday年龄fromdriverinnerjoincaron