• 数据库设计（MySQL）
  • 1、数据库创建
  • 2 、数据表的创建（可同时在创建时定义相关的约束）
  • 3 、视图的创建

 

数据库设计（MySQL）

 本笔记为笔者自己学习MySQL时整理所得。笔记中的案列为航空系统数据库的创建，若有疏漏或不当之处，请在评论区指出。谢谢。

1、数据库创建

create database 数据库名;

create database BookTicket;  

 

2 、数据表的创建（可同时在创建时定义相关的约束）

create table 表名(
列名+数据类型+约束,
);
定义表的约束
primary key：不允许为空，不允许重复（每个表必须有一个主键列）
unique:定义唯一约束
not null：定义非空约束
constraint husband_id_FK foreign key(husband_id) references husband(id)：定义外键约束

航空公司表
create table Company(
company_name varchar(20)  primary key,
phone varchar(15) not null);
飞机表
create table Plane (
plane_number varchar(10)  primary key,
plane_type varchar(20) not null,
quota_f int not null,
quota_s int not null,
fk_cp varchar(20),
constraint fk_cp_FK foreign key(fk_cp) references Company(company_name)
);
航线表
create table Line (
line_number varchar(10)  primary key,
airport_s varchar(20) not null,
airport_e varchar(20) not null,
distance int not null,
fk_cl varchar(20),
constraint fk_cl_FK foreign key(fk_cl) references Company(company_name)
);
航班表
create table Flight (
flight_number varchar(10) primary key,
constraint flight_number_FK foreign key(flight_number) references Plane(plane_number),
fk_lf varchar(10),
constraint fk_lf_FK foreign key(fk_lf ) references Line(line_number) ,
price_f int not null,
price_s int not null,
flydate varchar(20) not null);
乘客表
create table Passenger (
passenger_name varchar(10) primary key,
distances int not null,
E_mail varchar(20) not null);
机票表
create table Ticket (
ticket_number int primary key,
fk_ft varchar(10),
constraint fk_ft_FK foreign key(fk_ft) references Flight(flight_number),
fk_pt varchar(10),
constraint fk_pt_FK foreign key(fk_pt) references Passenger(passenger_name) ,
cabin varchar(10) constraint ch_cbn check
 (cabin = ' 头等舱 ' or cabin = ' 普通舱 ') not null,
pay int constraint ch_pay check
 (pay>0) not null);
侯票表
create table Wait (
fk_pw varchar(10),
constraint fk_pw_FK foreign key(fk_pw) references Passenger(passenger_name) ,
fk_fw varchar(10),
constraint fk_fw_FK foreign key(fk_fw) references Flight(flight_number) ,
cabin_w varchar(10) not null,
E_mail varchar(20) not null);

 

3 、视图的创建

create view view_name AS
select column_name(s)
from table_name
where condition

create view flight_cf (flight_number, count_f)
as
select flight_number, count(ticket_number)
from Flight
Join Ticket
where cabin = ' 头等舱 ' group by flight_number；

create view flight_cs (flight_number, count_s)
as
select flight_number, count(ticket_number)
From Flight
Join  Ticket
where cabin = ' 普通舱 ' group by flight_number;

create view flight_wf (flight_number, wait_f)
as
select flight_number, count(passenger_name)
from passenger
Join Flight
Join Wait
 where cabin_w = ' 头等舱 ' group by flight_number;

create view flight_ws (flight_number, wait_s)
as
select flight_number, count(passenger_name)
from passenger
Join Flight
Join Wait
 Wait where cabin_w = ' 普通舱 ' group by flight_number;

create view flight_q (flight_number, quota_f, quota_s)
as
select flight_number, quota_f, quota_s
from Flight left join Plane on plane_number = Plane.plane_number ;
create view book_info (flight_number, quota_f, count_f, wait_f,quota_s, count_s, wait_s)
as
select flight_q.flight_number, quota_f, count_f, wait_f,quota_s, count_s, wait_s
from flight_q left join flight_cf on flight_q.flight_number = flight_cf.flight_number
left join flight_cs on flight_q.flight_number = flight_cs.flight_number
left join flight_wf on flight_q.flight_number = flight_wf.flight_number
left join flight_ws on flight_q.flight_number = flight_ws.flight_number;