MySQL每日一练--餐馆管理系统数据库

 1.创建数据库（ReManager）

  2.创建菜品类别表 （FoodsType）,表结构如下：

Typeid    int          主键，自增，标识种子和标识增量都是1

TypeName  varchar(50)  唯一约束，不允许为空

创建菜品表 （Menu）,表结构如下：

Menuid      int          主键，自增，标识种子和标识增量都是1

MenuName    varchar(50)  唯一约束，不允许为空

Typeid      int          外键约束（与FoodsType表关联）

Price       decimal(6,2) 加check约束，取值只能在0-10000之间

创建销售表（Sales）

Salesid     int          主键，自增，标识种子和标识增量都是1

Menuid      int          外键约束（与Menu表相关联）

SalesDate   date         默认约束，默认值是系统时间

Amount      decimal(10,2)

3.为3张表分别添加数据(方法不限)：

菜品类别表 （FoodsType）数据如下：

1. 川菜
2. 湘菜
3. 粤菜
4. 浙菜
5. 苏菜

菜品表 （Menu）,数据如下：

1   麻婆豆腐   1   12

2   霸王别姬   2   18

3   鱼香肉丝   1   16

4   叫花鸡     5   25

5   脆皮乳猪   3   30

6   文昌鸡     3   28

7   西湖醋鱼   4   25

8   洞庭野鸭   2   50

销售表（Sales），数据如下：

1   2   2010-1-2    19

2   5   2011-2-3    25

3   8   2012-5-5    50

4   7   2012-8-1    25

5   1   2011-11-23  12

6   3   2009-1-1    16

7   4   2010-1-1    25

8   6   2014-3-6    28

9   1   2013-1-1    12

4.使用T-SQL代码实现如下题目：

1.把菜品表中文昌鸡的价格修改为 30

 2.由于浙菜销售情况不好，要求把菜品类别表中的 类别名为 浙菜的 行删掉。

 3.把菜品中，菜品类别为 川菜并且价格大于15的菜品的信息查询出来

 4.把菜品表中的菜品按照销售价格降序排列

 5.要求查询出每种菜品类别的 菜品数量，菜品平均价格。并且只显示菜品数量大于1的信息

 6.要求查询出 菜品名，菜品价格，菜品销售日期，并且按 菜品销售日期降序排列

7. 查询川菜和粤菜的销售情况，显示菜名，菜品价格，菜系名称，销售日期和销售数量

 代码如下(如果有不对的地方,欢迎指正):

-- 一，	创建数据库ReManager
-- 1，删除数据库
	DROP DATABASE IF EXISTS ReManager;
-- 2，创建数据库
	CREATE DATABASE ReManager;
-- 3，修改数据库编码方式和字符集排列顺序
	ALTER DATABASE ReManager CHARACTER SET utf8 COLLATE utf8_bin;
-- 4，使用数据库
	USE ReManager;
-- 二. 创建菜品类别表 （FoodsType）,表结构如下：
DROP TABLE IF EXISTS FoodsType;
CREATE TABLE FoodsType(
Typeid INT PRIMARY KEY AUTO_INCREMENT,                   -- 主键，自增，标识种子和标识增量都是1
 TypeName VARCHAR(50) NOT NULL UNIQUE                  -- 唯一约束，不允许为空
);
-- 创建菜品表 （Menu）,表结构如下：
DROP TABLE IF EXISTS Menu;
CREATE TABLE Menu(
Menuid INT PRIMARY KEY AUTO_INCREMENT,                        -- 主键，自增，标识种子和标识增量都是1
MenuName VARCHAR(50) NOT NULL UNIQUE,                         -- 唯一约束，不允许为空
Typeid INT ,                                          -- 外键约束（与FoodsType表关联）
Price DECIMAL(6,2) CHECK (Price >= 0 AND Price <= 10000),       -- 加check约束，取值只能在0-10000之间
FOREIGN KEY(Typeid) REFERENCES FoodsType(Typeid)
);
-- 创建销售表（Sales）
DROP TABLE IF EXISTS Sales;
CREATE TABLE Sales(
Salesid INT PRIMARY KEY AUTO_INCREMENT,-- 主键，自增，标识种子和标识增量都是1
Menuid INT ,                            -- 外键约束（与Menu表相关联）
SalesDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,      -- 默认约束，默认值是系统时间
Amount DECIMAL(10,2),
FOREIGN KEY(Menuid) REFERENCES Menu(Menuid)
);
-- 三.为3张表分别添加数据(方法不限)：
-- 1.添加菜品类别表FoodsType数据
INSERT INTO FoodsType VALUES(DEFAULT,'川菜');
INSERT INTO FoodsType VALUES(DEFAULT,'湘菜');
INSERT INTO FoodsType VALUES(DEFAULT,'粤菜');
INSERT INTO FoodsType VALUES(DEFAULT,'浙菜');
INSERT INTO FoodsType VALUES(DEFAULT,'苏菜');
SELECT * FROM FoodsType;
-- 2.添加菜品表Menu数据
INSERT INTO Menu VALUES(DEFAULT,'麻婆豆腐',1,12);
INSERT INTO Menu VALUES(DEFAULT,'霸王别姬',2,18);
INSERT INTO Menu VALUES(DEFAULT,'鱼香肉丝',1,16);
INSERT INTO Menu VALUES(DEFAULT,'叫花鸡',5,25);
INSERT INTO Menu VALUES(DEFAULT,'脆皮乳猪',3,30);
INSERT INTO Menu VALUES(DEFAULT,'文昌鸡',3,28);
INSERT INTO Menu VALUES(DEFAULT,'西湖醋鱼',4,25);
INSERT INTO Menu VALUES(DEFAULT,'洞庭野鸭',2,50);
SELECT * FROM Menu;
-- 3.添加销售表Sales数据
INSERT INTO Sales VALUES(DEFAULT,2,'2010-1-2',19);
INSERT INTO Sales VALUES(DEFAULT,5,'2011-2-3',25);
INSERT INTO Sales VALUES(DEFAULT,8,'2012-5-5',50);
INSERT INTO Sales VALUES(DEFAULT,7,'212-8-1',25);
INSERT INTO Sales VALUES(DEFAULT,1,'2011-11-23',12);
INSERT INTO Sales VALUES(DEFAULT,3,'2009-1-1',16);
INSERT INTO Sales VALUES(DEFAULT,4,'2010-1-1',25);
INSERT INTO Sales VALUES(DEFAULT,6,'2014-3-6',28);
INSERT INTO Sales VALUES(DEFAULT,1,'2013-1-1',12);
SELECT * FROM Sales;
-- 四.使用T-SQL代码实现如下题目：
-- 1.把菜品表中文昌鸡的价格修改为 30
UPDATE Menu SET Price = 30 WHERE MenuName = '文昌鸡';
-- 2.由于浙菜销售情况不好，要求把菜品类别表中的 类别名为 浙菜的 行删掉。
-- (1)
DELETE FROM Sales WHERE Menuid IN
(SELECT Menuid FROM Menu WHERE Typeid =
(SELECT Typeid FROM FoodsType WHERE TypeName='浙菜'));
-- (2)
DELETE FROM Menu WHERE Typeid = (SELECT Typeid FROM FoodsType WHERE TypeName='浙菜');
-- (3)
DELETE FROM FoodsType WHERE TypeName='浙菜';
-- 3.把菜品中，菜品类别为 川菜并且价格大于15的菜品的信息查询出来
 SELECT m.*
 FROM Menu m
 JOIN FoodsType t ON m.Typeid = t.Typeid
 WHERE t.TypeName = '川菜' AND m.Price > 15;
-- 4.把菜品表中的菜品按照销售价格降序排列
SELECT * FROM Menu ORDER BY Price DESC;
-- 5.要求查询出每种菜品类别的 菜品数量，菜品平均价格。并且只显示菜品数量大于1的信息
SELECT FoodsType.TypeName 菜品类别, COUNT(*)菜品数量, AVG(Menu.Price) 平均价格 
FROM Menu JOIN FoodsType ON Menu.Typeid = FoodsType.Typeid GROUP BY FoodsType.TypeName HAVING COUNT(*) > 1;
-- 6.要求查询出 菜品名，菜品价格，菜品销售日期，并且按 菜品销售日期降序排列
SELECT Menu.MenuName 菜品名,Menu.Price 菜品价格, Sales.SalesDate 菜品销售日期 
FROM Menu JOIN Sales ON Menu.Menuid = Sales.Menuid ORDER BY Sales.SalesDate DESC;
-- 7. 查询川菜和粤菜的销售情况，显示菜名，菜品价格，菜系名称，销售日期和销售数量
SELECT Menu.MenuName 菜名,Menu.Price 菜品价格,FoodsType.TypeName 菜系名称,Sales.SalesDate 销售日期,Sales.Amount 销售数量 
FROM Menu JOIN FoodsType ON Menu.Typeid = FoodsType.Typeid JOIN Sales ON Menu.Menuid = Sales.Menuid 
WHERE FoodsType.TypeName IN ('川菜','粤菜');