奶茶店管理系统设计（mysql大作业）

奶茶店管理系统设计

一. 建库语句：

创建数据库，数据库名称为“store”，基字符集设置为utf8。
CREATE DATABASE

store

CHARACTER SET utf8 COLLATE utf8_general_ci;
USE

store

;

二. 建表语句：

创建5张数据表（分店表branch_store，员工表employee，特权顾客表privilege，订单表orders，订单清单表menu）并插入一些数据，完成后各表的结构如图所示。

1. 建立分店表，设置b_id为主键。

CREATETABLE`branch_store`(`b_id`varchar(20)NOTNULL,`b_name`varchar(40)NOTNULL,PRIMARYKEY(`b_id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;

1. 建立员工表，s_id为主键，b_id为外键。

CREATETABLE`employee`(`s_id`varchar(10)NOTNULL,`s_name`varchar(30)NOTNULL,`s_sex`varchar(30)NOTNULL,`s_phone`varchar(30)NOTNULL,`s_date`dateNOTNULL,`s_age`int(10)NOTNULL,`b_id`varchar(20)NOTNULL,PRIMARYKEY(`s_id`),KEY`b_id`(`b_id`),CONSTRAINT`staff_ibfk_1`FOREIGNKEY(`b_id`)REFERENCES`branch`(`b_id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;

1. 建立特权顾客表，v_id为主键，s_id为外键，v_create默认为系统当前时间。

CREATETABLE`privilege`(`v_id`varchar(10)NOTNULL,`v_name`varchar(30)NOTNULL,`v_sex`varchar(10)NOTNULL,`v_age`int(10)NOTNULL,`v_create`timestampNOTNULLDEFAULTCURRENT_TIMESTAMP,`s_id`varchar(10)NOTNULL,PRIMARYKEY(`v_id`),KEY`s_id`(`s_id`),CONSTRAINT`vip_ibfk_1`FOREIGNKEY(`s_id`)REFERENCES`staff`(`s_id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;

1. 建立订单简表，添加外键v_id , b_id , s_id。

CREATETABLE`orders`(`o_id`int(10)NOTNULLAUTO_INCREMENT,`b_id`varchar(10)DEFAULTNULL,`s_id`varchar(10)DEFAULTNULL,`v_id`varchar(10)DEFAULTNULL,PRIMARYKEY(`o_id`),KEY`v_id`(`v_id`),KEY`b_id`(`b_id`),KEY`s_id`(`s_id`),CONSTRAINT`orders_ibfk_1`FOREIGNKEY(`v_id`)REFERENCES`vip`(`v_id`),CONSTRAINT`orders_ibfk_2`FOREIGNKEY(`b_id`)REFERENCES`branch`(`b_id`),CONSTRAINT`orders_ibfk_3`FOREIGNKEY(`s_id`)REFERENCES`staff`(`s_id`))ENGINE=InnoDBAUTO_INCREMENT=9DEFAULTCHARSET=utf8;

1. 建立订单清单表，o_id为外键。

CREATETABLE`menu`(`o_id`int(10)NOTNULL,`o_list`text,PRIMARYKEY(`o_id`),CONSTRAINT`orderlist_ibfk_1`FOREIGNKEY(`o_id`)REFERENCES`orders`(`o_id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;

三. 查询语句

查询会员表中的所有数据
select*from`menu`
  

查询员工表中的名字和入会日期两列数据
SELECT`s_name`,`s_date`FROM`employee` 

查询员工表中性别为男的员工的所有数据
SELECT*FROM`employee`WHERE`s_sex=男
 

查询员工表中s_id为S10的员工的名字，性别与年龄
SELECT `s_name`,`s_sex`,`s_age` FROM `employee` WHERE `s_id`='S10'
 

查询员工表中进店日期在2021年2月2日之后且年龄大于30的员工的信息
SELECT * FROM `employee` WHERE `s_date`>2021-02-02 AND `s_age`>30
 

查询员工表中进店日期在2022年1月1日之后或年龄大于20的员工的名字与年龄
SELECT `s_name`,`s_age` FROM `employee` WHERE `s_date`>2022-01-01 OR `s_age`>20 

查询店员表中b_id为B02和B03的店员的所有数据
SELECT * FROM `employee` WHERE `b_id` IN('B02','B03')
 

查询特权顾客表中入会日期在2021年1月1日和2021年12月31日之间的特权顾客的所有数据
SELECT * FROM `privilege` WHERE `v_create` BETWEEN '2021-02-01' AND '2021-12-31'
 
查询特权顾客表中名字中含有“左”字的会员的所有数据
SELECT * FROM `privilege` WHERE `v_name` LIKE '%左%'

 

查询员工表中名字以”刘”开头的店员的所有数据
SELECT * FROM `employee` WHERE `s_name` LIKE '刘%'
 

查询员工表中名字以“飞”结尾的店员的所有数据
SELECT * FROM `employee` WHERE `s_name` LIKE '%飞'
 

查询员工表中的总记录数
SELECT COUNT(*) FROM `employee`
 

查询员工表中的所有数据，按照年龄升序排序
SELECT * FROM `employee` ORDER BY s_age ASC
 

查询特权顾客表中的所有数据，按照年龄升序排序，若年龄相同则按v_id降序排序
SELECT * FROM `privilege` ORDER BY v_age ASC,v_id DESC
 

查询会员表中年龄大于25的人数，查询结果的列名指定为svip。
SELECT COUNT(*) AS svip FROM `privilege` WHERE `v_age`>30
 
查询会员表中年龄大于25的会员姓名，查询结果的列名指定为svip。
SELECT `v_name` AS svip FROM `privilege` WHERE `v_age`>25
 

查询员工表中员工的年龄平均值
SELECT AVG(`s_age`) FROM `employee`
 

查询员工表中年龄最大值
SELECT MAX(`s_age`) FROM `employee`
 

查询员工表中第1到10个员工的所有数据
SELECT * FROM `employee` LIMIT 0,10
 

查询由第S05号员工之后员工受理的特权顾客的数据，查询结果展示会员名字，性别，年龄，以及受理员工的编号和名字
SELECT v.`v_name`,v.`v_sex`,v.`v_age`,s.s_id,s.`s_name` FROM `privilege` v
INNER JOIN employee s ON v.s_id = s.`s_id`
WHERE s.`s_id`>'S05'
 

查询订单清单对应的订单编号、受理分店编号、受理店员编号、下单会员编号以及订单清单内容
SELECT o.`o_id`,o.`b_id`,o.`s_id`,o.`v_id`,ol.`o_list` FROM `orders` o
RIGHT JOIN `menu`ol ON o.`o_id` = ol.`o_id`

四. 删除语句

将会员表中编号为V20的会员数据删除
DELETE FROM

vip

WHERE

v_id

=‘V20’

五. 更新语句

将员工表中名字为周周的员工年龄修改为39
UPDATE

employee

SET

s_age

=‘39’ WHERE

s_name

=‘周周’

将员工表中编号为S11的员工的名字修改为凯文，性别修改为男
UPDATE

employee

SET

s_name

=‘九月’,

s_sex

=‘男’ WHERE

s_id

=‘S11’

六. 创建存储过程

1. 创建存储过程

创建名为find_employee的存储过程，其功能是根据员工编号查询员工数据

创建find_menu查找某个数据库存在的存储过程

创建循环语句

创建条件语句

1. 查看存储过程 2.1. 查看存储过程的状态 语法如下：

SHOW PROCEDURE STATUS LIKE ‘find_menu’;

2.2. 查看存储过程的定义
– 查看存储过程find_menu的定义
SHOW CREATE PROCEDURE find_menu;

3.修改存储过程
– 修改存储过程find_menu，使它可以写数据(modifies sql data)
ALTER PROCEDURE find_menu MODIFIES SQL DATA;

1. 删除存储过程 drop procedure DROP PROCEDURE IF EXISTS find_menu

创建游标
创建游标来计算森林玫果的订单的数量；

DELIMITER $$
DROP PROCEDURE IF EXISTS StatisticStore;
CREATE PROCEDURE StatisticStore()
BEGIN
#–创建接收游标数据的变量
DECLARE c INT;
DECLARE n VARCHAR(20);
#–创建总数变量
DECLARE total INT DEFAULT 0;
#–创建结束标志变量
DECLARE done INT DEFAULT FALSE;
#–创建游标
DECLARE cur CURSOR FOR SELECT NAME,COUNT FROM menu WHERE o_list= ‘森林玫果’;
#–指定游标循环结束时的返回值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
#–设置初始值
SET total = 0;
#–打开游标
OPEN cur;
#–开始循环游标里的数据
read_loop:LOOP
#–根据游标当前指向的一条数据
FETCH cur INTO n,c;
#–判断游标的循环是否结束
IF done THEN
LEAVE read_loop; #–跳出游标循环
END IF;
#–获取一条数据时，将count值进行累加操作，这里可以做任意你想做的操作，
SET total = total + c;
#–结束游标循环
END LOOP;
#–关闭游标
CLOSE cur;

#--输出结果  
SELECT total;  

END;
#–调用存储过程
CALL StatisticStore();

七. 创建触发器

创建名为test的触发器并创建同名表，功能是在员工表插入数据时向同名表插入数据记录此次操作

查看触发器
语法：show triggers;

查看触发器的创建语句

SHOW CREATE TRIGGER test;
触发器不能修改，只能删除
语法：drop trigger + 触发器名字