You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
131 lines
4.0 KiB
131 lines
4.0 KiB
create table customers
|
|
(
|
|
T_id int auto_increment
|
|
primary key,
|
|
T_name varchar(10) not null,
|
|
T_rsex varchar(4) null,
|
|
T_phone varchar(20) null
|
|
)
|
|
charset = utf8mb3;
|
|
|
|
create table menu_items
|
|
(
|
|
primary key (M_id)
|
|
)
|
|
charset = utf8mb3;
|
|
|
|
create table discount_rules
|
|
(
|
|
primary key (M_id),
|
|
constraint discount_rules_menu_items_M_id_fk
|
|
foreign key (M_id) references menu_items (M_id)
|
|
on update cascade on delete cascade
|
|
)
|
|
charset = utf8mb3;
|
|
|
|
create table tables
|
|
(
|
|
Cz_id varchar(10) not null
|
|
primary key,
|
|
Cz_number int not null,
|
|
Cz_zt varchar(6) null
|
|
)
|
|
charset = utf8mb3;
|
|
|
|
create table workers
|
|
(
|
|
W_id varchar(10) not null
|
|
primary key,
|
|
W_name varchar(10) null,
|
|
W_sex varchar(4) null,
|
|
W_stat int null,
|
|
W_salary float null
|
|
)
|
|
charset = utf8mb3;
|
|
|
|
create table orders
|
|
(
|
|
O_id int auto_increment
|
|
primary key,
|
|
T_id int not null,
|
|
time datetime not null,
|
|
Cz_id varchar(10) not null,
|
|
W_id varchar(10) not null,
|
|
items text null,
|
|
constraint orders_customers_T_id_fk
|
|
foreign key (T_id) references customers (T_id),
|
|
constraint orders_tables_Cz_id_fk
|
|
foreign key (Cz_id) references tables (Cz_id),
|
|
constraint orders_workers_W_id_fk
|
|
foreign key (W_id) references workers (W_id)
|
|
)
|
|
charset = utf8mb3;
|
|
|
|
create table bills
|
|
(
|
|
B_id int auto_increment
|
|
primary key,
|
|
O_id int null,
|
|
S_price float null,
|
|
S_priceafter float null,
|
|
time datetime null,
|
|
constraint bills_orders_O_id_fk
|
|
foreign key (O_id) references orders (O_id)
|
|
on update cascade on delete cascade
|
|
)
|
|
charset = utf8mb3;
|
|
|
|
create index Cz_id
|
|
on orders (Cz_id);
|
|
|
|
create index T_id
|
|
on orders (T_id);
|
|
|
|
create index W_id
|
|
on orders (W_id);
|
|
|
|
create definer = root@localhost trigger UpdateTableStatusToFreeAfterDelete
|
|
after delete
|
|
on orders
|
|
for each row
|
|
BEGIN
|
|
UPDATE tables SET Cz_zt = '空闲' WHERE Cz_id = OLD.Cz_id;
|
|
END;
|
|
|
|
create definer = root@localhost view customer_orders as
|
|
select `c`.`T_id` AS `Customer_ID`,
|
|
`c`.`T_name` AS `Customer_Name`,
|
|
`o`.`O_id` AS `Order_ID`,
|
|
`o`.`time` AS `Order_Time`,
|
|
`t`.`Cz_id` AS `Table_ID`,
|
|
`t`.`Cz_number` AS `Table_Number`,
|
|
`w`.`W_id` AS `Waiter_ID`,
|
|
`w`.`W_name` AS `Waiter_Name`,
|
|
`o`.`items` AS `Ordered_Items`
|
|
from (((`mydata`.`orders` `o` join `mydata`.`customers` `c` on ((`o`.`T_id` = `c`.`T_id`))) join `mydata`.`tables` `t`
|
|
on ((`o`.`Cz_id` = `t`.`Cz_id`))) join `mydata`.`workers` `w` on ((`o`.`W_id` = `w`.`W_id`)));
|
|
|
|
create definer = root@localhost view customers_t as
|
|
select `mydata`.`customers`.`T_id` AS `T_id`,
|
|
`mydata`.`customers`.`T_name` AS `T_name`,
|
|
`mydata`.`customers`.`T_rsex` AS `T_rsex`,
|
|
`mydata`.`customers`.`T_phone` AS `T_phone`
|
|
from `mydata`.`customers`
|
|
where (`mydata`.`customers`.`T_rsex` = '女');
|
|
|
|
create definer = root@localhost view menu_item_sales as
|
|
select `m`.`M_id` AS `Menu_Item_ID`,
|
|
`m`.`M_name` AS `Menu_Item_Name`,
|
|
count(0) AS `Sales_Count`,
|
|
sum((`m`.`M_price` * (1 - coalesce(`d`.`discount`, 0)))) AS `Total_Sales_Amount`
|
|
from ((`mydata`.`menu_items` `m` left join `mydata`.`discount_rules` `d`
|
|
on ((`m`.`M_id` = `d`.`M_id`))) join `mydata`.`orders` `o` on ((find_in_set(`m`.`M_id`, `o`.`items`) > 0)))
|
|
group by `m`.`M_id`, `m`.`M_name`, `d`.`discount`;
|
|
|
|
create definer = root@localhost view table_status as
|
|
select `mydata`.`tables`.`Cz_id` AS `Table_ID`,
|
|
`mydata`.`tables`.`Cz_number` AS `Table_Number`,
|
|
`mydata`.`tables`.`Cz_zt` AS `Status`
|
|
from `mydata`.`tables`;
|
|
|