阳光餐厅--oracle---建表---danrong

select * from manager;
select * from dish;
select * from board;
select * from employee;
select * from orders;
select * from order_detail;


 
-- 菜品表

create table dish
(
  dishID number(10) primary key,
  dishName varchar2(10) not null unique,
  category varchar2(10) not null,
  price number(10) not null,
  picture varchar2(40) not null,
  status number(10) not null
)

create sequence dish_se start with 1 increment by 1
maxvalue 5000;

insert into dish (dishID,dishName,category,price,picture,status)
values(dish_se.nextval,'XX','XX','XX','XX','XX');


select * from dish;



--管理员表
create table manager 
(
mid  number(10) primary key,  
mname varchar2(10)  not null unique,
password  varchar2(10)  not null
)
create sequence manager_se start with 1 increment by 1
maxvalue 5000;

insert into manager (mid,mname,password)
values(manager_se.nextval,'1','1');
 
 select * from manager;
 
 
 
--餐桌表
create table board  
(
  boardid number(10) primary key,
  capacity number(10) not null
)

create sequence board_se start with 1 increment by 1
maxvalue 5000;

insert into board (boardid,capacity)
values(board_se.nextval,'4');
 
select * from board;

--职员表 

create table employee  
(
  eID number(10) primary key,
  ename varchar2(10) not null,
  sex varchar2(10) not null,   
  position varchar2(10) not null,   
  epassword varchar2(10) not null
)


create sequence emp_se start with 1 increment by 1
maxvalue 5000;

insert into employee(eID,ename,sex,position,epassword) 
values (emp_se.nextval,'1','男','服务员','1'); 

select * from employee;


--创建订单表
create table orders 
(
  orderID number(10) primary key,
  boardID number(10) not null, --外键
  pnumber number(10) not null,
  client varchar2(10), --允许为空
  mobile_number varchar2(20),--允许为空
  order_time varchar2(20) not null,
  arrive_time varchar2(20) not null,
  cash number(10),
  change number(10),
  status number(10) not null
)

--外键约束
alter table orders add 
constraint fk_order_boardID foreign key (boardID) references board (boardID);

create sequence order_se start with 1 increment by 1
maxvalue 5000;

insert into orders (orderID,boardID,pnumber,client,mobile_number,order_time,arrive_time,cash,change,status)
values(order_se.nextval,'1','4','yz',null,sysdate,'2017-1-9',100,1,1);



select * from orders;



-- 订单详情表


create table order_detail
(
  odID number(10) primary key,
  orderID number(10) not null, -- 外键
  eID number(10) not null,-- 外键
  dishID number(10) not null,-- 外键
  taste number(10) not null,
  price number(10) not null,
  count number(10) not null,
  status number(10) not null
)

--外键约束
alter table order_detail add
constraint fk_ordt_orderID foreign key (orderID) references orders (orderID);
alter table order_detail add
constraint fk_ordt_eID foreign key (eID) references employee (eID);
alter table order_detail add
constraint fk_ordt_dishID foreign key (dishID) references dish (dishID);

create sequence ordt_se start with 1 increment by 1
maxvalue 5000;



insert into order_detail (odID,orderID,eID,dishID,taste,price,count,status)
values(ordt_se.nextval,1,1,3,1,10,1,1);