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);