《汽车站数据库设计32684.docx》由会员分享，可在线阅读，更多相关《汽车站数据库设计32684.docx（9页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、 汽车站数据库设计一、 系统简要介绍主要实现的功能是汽车站驾驶人员和路线及汽车等基本信息进行管理，包括浏览驾驶员信息、路线信息，查询和统计一些驾驶员、汽车等数据，修改驾驶员行走的路线信息等。二、 本系统有四张表组成，表结构如下：驾驶员表：（驾驶员编号，汽车编号，姓名，生日，性别，工作年限）汽车表：（汽车编号，型号，载客量，票价）路线表：（路线编号，目的地，行程时间，出发地）汽车路线表：（汽车编号，路线编号）用户表：（用户名，密码）三、 表间关系说明一个驾驶员属于一辆车，一辆车可以有多个驾驶员，一辆车可以行走多条路线，一条路线也可有多辆车行走。四、ER图如下： 驾驶员表属于汽车表路线表汽车路线表
2、五.各表的简单介绍和创建代码：1.驾驶员表driver字段含义字段名称字段类型驾驶员编号driverIDChar(20) primary key汽车编号car_IDChar(20) not null姓名namechar(20) unique生日birthdayChar(20)性别sexChar(20)工作年限standingChar(20)创建驾驶员表：CREATE TABLE driver(driverID char(20) primary key,car_ID char(20) NOT NULL ,name char(20)unique,brithday char(20),sex char
3、(2),standing char(20), foreign key (car_ID) references car(car_ID );2.汽车表car：字段含义字段名称字段类型汽车编号car_IDChar(20) primary key类型typechar(20)载客量capacityChar(20)创建汽车表表：CREATE TABLE car(car_ID char(20)primary key,type char(20) ,capacity char(20) ,);3.路线表line字段含义字段名称字段类型路线编号Line_IDChar(20) primary key目的地destin
4、ationchar(20) not null所需时间take_timechar(20)出发地start_addresschar(20) not null票价ticket_priceChar(20) not null创建路线表：CREATE TABLE line(line_ID char(20)primary key,destination char(20) not null,take_time char(20) ,start_address char(20) not null，ticket_price char(20) not null);4.汽车路线表car_line字段含义字段名称字段类型
5、汽车编号carIDChar(20)路线编号lineIDchar(20)创建汽车路线表：CREATE TABLE car_line( carID char(20), lineID char(20), primary key(carID,lineID);5.用户表user_ID.字段含义字段名称字段类型汽车编号usernameChar(20)路线编号passwordchar(20)创建用户表表：CREATE TABLE user_ID( username varchar(20), password varchar(20), primary key(username,password);六.插入数据
6、insert1.汽车表数据insert into car values(091201,飞鹤,45);insert into car values(091202,快鹿,40);insert into car values(091203,鸵鸟,42);insert into car values(091204,风火轮,45);insert into car values(091205,筋斗云,38);insert into car values(091206,白龙马,42);insert into car values(091207,毛驴,36);insert into car values(09
7、1208,青牛,50);2.驾驶员数据insert into driver (driverID,car_ID,name,brithday,sex,standing)values (19491001,091201,吴士,1981,1,6);insert into driver (driverID,car_ID,name,brithday,sex,standing)values (19491002,091204,孙悟,1974,1,15);insert into driver (driverID,car_ID,name,brithday,sex,standing)values (19491003,
8、091203,李娇,1979,0,9);insert into driver (driverID,car_ID,name,brithday,sex,standing)values (19491004,091202,夏雨荷,1983,0,6);insert into driver (driverID,car_ID,name,brithday,sex,standing)values (19491005,091205,刘备,1977,1,16);insert into driver (driverID,car_ID,name,brithday,sex,standing)values (1949100
9、6,091208,诸葛亮,1985,1,4);insert into driver (driverID,car_ID,name,brithday,sex,standing)values (19491007,091206,陈娜,1975,0,17);insert into driver (driverID,car_ID,name,brithday,sex,standing)values (19491008,091207,张海风,1978,1,10);insert into driver (driverID,car_ID,name,brithday,sex,standing)values (194
10、91009,091201,朱翠翠,1980,0,8);insert into driver (driverID,car_ID,name,brithday,sex,standing)values (19491010,091204,赵薇,1988,0,1);insert into driver (driverID,car_ID,name,brithday,sex,standing)values (19491011,091208,李鸣,1978,1,12);insert into driver (driverID,car_ID,name,brithday,sex,standing)values (1
11、9491012,091203,周杰磊,1981,1,4);3.路线表数据insert into line (line_ID,destination,take_time,start_address,ticket_price) values (201001,上海,3小时,南京,123);insert into line (line_ID,destination,take_time,start_address,ticket_price) values (201002,南通,4小时,南京,233);insert into line (line_ID,destination,take_time,star
12、t_address,ticket_price) values (201003,苏州,3个半小时,南京,256);insert into line (line_ID,destination,take_time,start_address,ticket_price) values (201004,盐城,3小时20分,南京,330);insert into line (line_ID,destination,take_time,start_address,ticket_price) values (201005,泰州,2个半小时,南京,450);insert into line (line_ID,d
13、estination,take_time,start_address,ticket_price) values (201006,扬州,2小时,南京,326);insert into line (line_ID,destination,take_time,start_address,ticket_price) values (201007,上海,3小时,南京,521);insert into line (line_ID,destination,take_time,start_address,ticket_price) values (201008,苏州,3小时,南京,430);4.汽车路线表数据
14、insert into car_line (carID,lineID) values (91202,201001);insert into car_line(carID,lineID) values (91207,201002);insert into car_line(carID,lineID) values (91208,201007);insert into car_line(carID,lineID) values (91206,201003);insert into car_line (carID,lineID) values (91205,201008);insert into c
15、ar_line (carID,lineID) values (91204,201005);insert into car_line (carID,lineID) values (91201,201004);insert into car_line (carID,lineID) values (91203,201006);insert into car_line (carID,lineID) values (91202,201003);insert into car_line (carID,lineID) values (91206,201006);5.用户表数据insert into user
16、_ID values(邱冬,123456);七.索引：建立索引遵循的规律：1.建立在where子句经常引用的列上，2.经常需要排序的列上，3.连接属性列上等create index car_index on car(car_ID);-汽车表在汽车编号字段上建立索引create index driver_index on driver(driverID);-驾驶员表在驾驶员编号字段上建立索引create index line_index on line(lineID);-路线表在路线编号字段上建立索引create index take_time_index on line(take_time);
17、-路线表在路线所需时间上建立索引，以备查询各路线所需时间八视图1.创建过程take_time_list，查询各路线所花时间：create view take_time_listasselect line_ID,take_timefrom line;select * from take_time_list;结果如下:2.创建过程ticket_price，查询各路线所需票价create view ticket_priceasselect destination,start_address,ticket_pricefrom line；select * from ticket_price；结果如下:3
18、.创建过程car_line_driver，查询各路线行驶的所有驾驶员及车辆create view car_line_driverasselect line.destination,start_address,driverID,car.car_IDfrom car,line,driverselect * from car_line_driver结果如下:九查询1查询有多少位驾驶员Select count (*) from driver执行结果：2.查询查询驾驶员的某些信息select driverID, name,sex from driver执行结果：3.查询某型号汽车走那些路线select
19、line_ID,destination from line where line_ID =201005执行结果：4.查询驾驶员的年龄，并按年龄降序排列。select name 驾驶员名, 2010-brithday 年龄from driver inner join caron driver.car_ID=car.car_ID order by 2 desc执行结果：5.查询工作年限超过8年的驾驶员的名字Select name 驾驶员名 ,standing 工龄 from driver where standing8执行结果：6.查询汽车站的线路段select * from line;7. 查询汽车表9