云南农业职业技术学院 - 互联网技术学院 - 美和易思《MYSQL 高级查询与编程》综合机试试卷

傷城~ 2023-01-07 07:09 123阅读 0赞

> 数据库及试题文档下载：[https://download.csdn.net/download/weixin\_44893902/14503097][https_download.csdn.net_download_weixin_44893902_14503097]

**目录**

题目：电商平台 mysql 数据库系统管理

一、 语言和环境

二、 题目（100 分）

1、需求场景：

2、功能需求：

3、实现功能

（1）写更新语句，将 t\_goods 表里的每个商品的单价增加 2 元；(注：执行你所编写 sql 语句，检查结果变化,sql 保存命名为 answer01，将更新前的数据截图保存命名为 answer01(old)，更新后的数据截图命名为 answer02(new));

（2）从表 t\_customer 中，统计男性顾客数和女性顾客数，显示信息：性别 sex,人数 count；(注：将结果保存为视图，并将结果截图，视图和图片命名为 answer02);

（3）统计没有卖出过的商品的信息，按库存降序，单价升序排列，显示信息为：商品名 goods\_name，单价 price，库存量 stock；(注：将结果保存为视图，并将结果截图，视图和图片命名为 answer03);

（4）统计下过单的顾客的信息，显示信息为：顾客姓名 cus\_name，电话 phone，性别 sex；(注：将结果保存为视图，并将结果截图，视图和图片命名为 answer04);

（5）写更新语句，更新 t\_order 所有订单的 money 字段为订单的真实金额，订单状态改为已支付；(注：执行你所编写 sql 语句，检查结果变化,sql 保存命名为 answer05，将更新前的数据截图保存命名为 answer05(old)，更新后的数据截图命名为 answer05(new));

（6）从表 t\_customer 和 t\_order，统计每个顾客（包括下过单和没下过的顾客）的下单总金额和下单总数，显示信息为：顾客姓名，电话，下单总金额 allMoney，下单总数 order\_num；(注：将结果保存为视图，并将结果截图，视图和图片命名为 answer06);

（7）查询每个顾客的购物信息，显示信息：顾客姓名 cus\_name，商品名 goods\_name，购买次数 buy\_count；(注：将结果保存为视图，并将结果截图，视图和图片命名为 answer07);

（8）为了保证删除订单时候，数据能保持一致性，对 t\_order 表加触发器，在删除订单数据之后，触发删除订单明细表里的对应数据；(注：执行你所编写 sql 语句，检查结果变化，sql 保存命名为 answer08);

（9）写存储过程实现查询某个顾客的登录账号，存储过程需要传入顾客姓名，并且将查到的登录账号传出，并查询展示出来；(注：执行你所编写 sql 语句，检查结果，sql 保存命名为 answer09);

三、评分标准:

数据库完整导入代码：

--------------------

# **题目：电商平台 mysql 数据库系统管理** #

## **一、 语言和环境**  ##

1. 实现语言：sql。

2. 开发环境：mySql,navicat。

## **二、 题目（100 分）**  ##

### 1、需求场景：  ###

今年来，由于国家各种扶持政策的推行，出现大批大学毕业生返乡创业的热潮。小王是云南某大学互联网学院计算机软件专业的在读大二学生，为了让自己所学能学以致用，他打算从现在就开始着手开发一个电商平台，考虑着毕业后能回到自己的家乡怒江地区，就地取材，将家乡各种特产销售到全国各地去。以下就是小王开发这个平台，设计的几个核心数据库表。

### 2、功能需求：  ###

**a、创建数据库，名称为 mall；**

**b、创建数据表 t\_customer（顾客）、t\_order（订单）、t\_orderdetail（订单明细）， t\_goods（商品）表结构如下：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg_size_16_color_FFFFFF_t_70][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg_size_16_color_FFFFFF_t_70 1][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg_size_16_color_FFFFFF_t_70 2][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg_size_16_color_FFFFFF_t_70 3][]

**c、录入以下数据：**

t\_customer 表：

![20210115210100727.png][]

t\_order 表：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg_size_16_color_FFFFFF_t_70 4][]

t\_goods 表：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg_size_16_color_FFFFFF_t_70 5][]

t\_orderdetail 表：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg_size_16_color_FFFFFF_t_70 6][]

## 3、实现功能  ##

### （1）写更新语句，将 t\_goods 表里的每个商品的单价增加 2 元；(注：执行你所编写 sql 语句，检查结果变化,sql 保存命名为 answer01，将更新前的数据截图保存命名为 answer01(old)，更新后的数据截图命名为 answer02(new));  ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg_size_16_color_FFFFFF_t_70 7][]![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg_size_16_color_FFFFFF_t_70 8][]

UPDATE t_goods SET price=price+2;

查询更新前后的数据：

SELECT * FROM t_goods;

恢复：

UPDATE t_goods SET price=price-2;

### （2）从表 t\_customer 中，统计男性顾客数和女性顾客数，显示信息：性别 sex,人数 count；(注：将结果保存为视图，并将结果截图，视图和图片命名为 answer02);  ###

![2021011521234775.png][]

CREATE VIEW answer02 AS
    SELECT sex 性别,COUNT(*) 人数 FROM t_customer GROUP BY sex;

### （3）统计没有卖出过的商品的信息，按库存降序，单价升序排列，显示信息为：商品名 goods\_name，单价 price，库存量 stock；(注：将结果保存为视图，并将结果截图，视图和图片命名为 answer03);  ###

![20210115212358218.png][]

CREATE VIEW answer03 AS
    SELECT goods_name 商品名,price 单价,stock 库存量
    FROM t_goods WHERE goods_id NOT IN (SELECT goods_id FROM t_orderdtrail) ORDER BY price ASC;

### （4）统计下过单的顾客的信息，显示信息为：顾客姓名 cus\_name，电话 phone，性别 sex；(注：将结果保存为视图，并将结果截图，视图和图片命名为 answer04);  ###

![2021011521240711.png][]

CREATE VIEW answer04 AS
    SELECT cus_name 顾客姓名,phone 电话,sex 性别 FROM t_customer 
    WHERE cus_id IN (SELECT cus_id FROM t_order);

### （5）写更新语句，更新 t\_order 所有订单的 money 字段为订单的真实金额，订单状态改为已支付；(注：执行你所编写 sql 语句，检查结果变化,sql 保存命名为 answer05，将更新前的数据截图保存命名为 answer05(old)，更新后的数据截图命名为 answer05(new));  ###

查询更新前后数据：

SELECT * FROM t_order;

**更新前：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg_size_16_color_FFFFFF_t_70 9][]

**更新后：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg_size_16_color_FFFFFF_t_70 10][]

UPDATE t_order t1,(SELECT order_id,(g.price*goods_count) trueMoney FROM t_orderdtrail od,t_goods g
    WHERE od.goods_id=g.goods_id) t2 SET t1.money=t2.trueMoney,t1.`status`=1 WHERE t1.order_id=t2.order_id;

恢复：

UPDATE t_order SET money=0,`status`=0

### （6）从表 t\_customer 和 t\_order，统计每个顾客（包括下过单和没下过的顾客）的下单总金额和下单总数，显示信息为：顾客姓名，电话，下单总金额 allMoney，下单总数 order\_num；(注：将结果保存为视图，并将结果截图，视图和图片命名为 answer06);  ###

![20210115212653987.png][]

CREATE VIEW answer06 AS
    SELECT cus_name 顾客姓名,phone 电话,SUM(price) 下单总金额,COUNT(o.order_id) 下单总数
    FROM t_orderdtrail od,t_order o,t_customer c,t_goods g
    WHERE o.order_id=od.order_id AND o.cus_id=c.cus_id AND od.goods_id=g.goods_id
    GROUP BY cus_name;

### （7）查询每个顾客的购物信息，显示信息：顾客姓名 cus\_name，商品名 goods\_name，购买次数 buy\_count；(注：将结果保存为视图，并将结果截图，视图和图片命名为 answer07); ###

![20210115212724340.png][]

CREATE VIEW answer07 AS
    SELECT cus_name 顾客姓名,goods_name 商品名称, COUNT(o.order_id) 购买次数
    FROM t_orderdtrail od,t_order o,t_customer c,t_goods g
    WHERE o.order_id=od.order_id AND o.cus_id=c.cus_id AND od.goods_id=g.goods_id 
    GROUP BY goods_name;

### （8）为了保证删除订单时候，数据能保持一致性，对 t\_order 表加触发器，在删除订单数据之后，触发删除订单明细表里的对应数据；(注：执行你所编写 sql 语句，检查结果变化，sql 保存命名为 answer08);  ###

DROP TRIGGER IF EXISTS `answer08`;
    delimiter $$
    CREATE TRIGGER answer08
    AFTER DELETE ON t_order
    FOR EACH ROW
    BEGIN
    	DELETE FROM t_orderdtrail WHERE t_orderdtrail.order_id=t_order.order_id;
    END $$
    delimiter ;

### （9）写存储过程实现查询某个顾客的登录账号，存储过程需要传入顾客姓名，并且将查到的登录账号传出，并查询展示出来；(注：执行你所编写 sql 语句，检查结果，sql 保存命名为 answer09); ###

创建存储过程：

![20210115212818629.png][]

delimiter $$
    CREATE PROCEDURE answer09(
    	IN name VARCHAR(20)
    )
    BEGIN
    	SELECT cus_name 顾客姓名,account 账号 FROM t_customer WHERE cus_name=name;
    END
    $$
    delimiter ;

实现存储过程：

![20210115212801844.png][]

SET @name='王二';
    CALL answer09(@name);

## **三、评分标准:**  ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg_size_16_color_FFFFFF_t_70 11][]

## 数据库完整导入代码： ##

/*
    Navicat MySQL Data Transfer
    
    Source Server         : test
    Source Server Version : 50646
    Source Host           : localhost:3306
    Source Database       : mall
    
    Target Server Type    : MYSQL
    Target Server Version : 50646
    File Encoding         : 65001
    
    Date: 2021-01-15 21:37:04
    */
    
    SET FOREIGN_KEY_CHECKS=0;
    
    -- ----------------------------
    -- Table structure for t_customer
    -- ----------------------------
    DROP TABLE IF EXISTS `t_customer`;
    CREATE TABLE `t_customer` (
      `cus_id` int(11) NOT NULL AUTO_INCREMENT,
      `cus_name` varchar(20) CHARACTER SET utf8 NOT NULL,
      `phone` varchar(15) NOT NULL,
      `sex` char(1) CHARACTER SET utf8 DEFAULT NULL,
      `account` varchar(20) NOT NULL,
      `pwd` char(8) DEFAULT NULL,
      PRIMARY KEY (`cus_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
    
    -- ----------------------------
    -- Records of t_customer
    -- ----------------------------
    INSERT INTO `t_customer` VALUES ('1', '张芮', '15908719999', '女', 'zhangrui', '12345678');
    INSERT INTO `t_customer` VALUES ('2', '李四', '15908712222', '男', 'lisi', '12345678');
    INSERT INTO `t_customer` VALUES ('3', '王二', '15908713333', '男', 'wanger', '12345678');
    INSERT INTO `t_customer` VALUES ('4', '赵五', '15908714444', '男', 'zhaowu', '12345678');
    INSERT INTO `t_customer` VALUES ('5', '李晓', '15908715555', '女', 'lixiao', '12345678');
    
    -- ----------------------------
    -- Table structure for t_goods
    -- ----------------------------
    DROP TABLE IF EXISTS `t_goods`;
    CREATE TABLE `t_goods` (
      `goods_id` int(11) NOT NULL AUTO_INCREMENT,
      `goods_name` varchar(50) CHARACTER SET utf8 NOT NULL,
      `price` decimal(8,2) DEFAULT NULL,
      `unit` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
      `stock` int(11) DEFAULT '100',
      PRIMARY KEY (`goods_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
    
    -- ----------------------------
    -- Records of t_goods
    -- ----------------------------
    INSERT INTO `t_goods` VALUES ('1', '土蜂蜜', '122.00', '公斤', '300');
    INSERT INTO `t_goods` VALUES ('2', '野生干菌子', '152.00', '公斤', '50');
    INSERT INTO `t_goods` VALUES ('3', '百香果', '14.50', '公斤', '100');
    INSERT INTO `t_goods` VALUES ('4', '火腿肉', '99.00', '公斤', '60');
    INSERT INTO `t_goods` VALUES ('5', '本地腐乳', '12.50', '瓶', '300');
    INSERT INTO `t_goods` VALUES ('6', '腊肠', '147.00', '公斤', '300');
    
    -- ----------------------------
    -- Table structure for t_order
    -- ----------------------------
    DROP TABLE IF EXISTS `t_order`;
    CREATE TABLE `t_order` (
      `order_id` int(11) NOT NULL,
      `cus_id` int(11) NOT NULL,
      `number` char(6) NOT NULL,
      `money` decimal(8,2) DEFAULT NULL,
      `order_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
      `status` char(1) CHARACTER SET utf8 DEFAULT NULL,
      `remark` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
      PRIMARY KEY (`order_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    
    -- ----------------------------
    -- Records of t_order
    -- ----------------------------
    INSERT INTO `t_order` VALUES ('1', '1', '000001', '0.00', '2021-01-15 21:36:56', '0', '');
    INSERT INTO `t_order` VALUES ('2', '2', '000002', '0.00', '2021-01-15 21:36:56', '0', '');
    INSERT INTO `t_order` VALUES ('3', '1', '000003', '0.00', '2021-01-15 21:36:56', '0', '');
    INSERT INTO `t_order` VALUES ('4', '2', '000004', '0.00', '2021-01-15 21:36:56', '0', '');
    INSERT INTO `t_order` VALUES ('5', '3', '000005', '0.00', '2021-01-15 21:36:56', '0', '');
    INSERT INTO `t_order` VALUES ('6', '1', '000006', '0.00', '2021-01-15 21:36:56', '0', '请分装成两份');
    INSERT INTO `t_order` VALUES ('7', '4', '000007', '0.00', '2021-01-15 21:36:56', '0', '请包装多加一层防护');
    
    -- ----------------------------
    -- Table structure for t_orderdtrail
    -- ----------------------------
    DROP TABLE IF EXISTS `t_orderdtrail`;
    CREATE TABLE `t_orderdtrail` (
      `order_id` int(11) NOT NULL,
      `goods_id` int(11) NOT NULL,
      `goods_count` int(11) DEFAULT '1',
      `remark` varchar(100) CHARACTER SET utf8 DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    
    -- ----------------------------
    -- Records of t_orderdtrail
    -- ----------------------------
    INSERT INTO `t_orderdtrail` VALUES ('1', '2', '2', '一公斤一袋分装');
    INSERT INTO `t_orderdtrail` VALUES ('2', '1', '1', '');
    INSERT INTO `t_orderdtrail` VALUES ('3', '2', '2', '一公斤一袋分装');
    INSERT INTO `t_orderdtrail` VALUES ('4', '5', '1', '');
    INSERT INTO `t_orderdtrail` VALUES ('5', '6', '2', '一公斤一袋分装');
    INSERT INTO `t_orderdtrail` VALUES ('6', '2', '3', '');
    INSERT INTO `t_orderdtrail` VALUES ('7', '4', '1', '');

[https_download.csdn.net_download_weixin_44893902_14503097]: https://download.csdn.net/download/weixin_44893902/14503097
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg_size_16_color_FFFFFF_t_70]: /images/20221119/60f8c1efdf2f4f3bb540586ff5f5feae.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg_size_16_color_FFFFFF_t_70 1]: /images/20221119/30ddb8f8db2f44deb96195d74ef47c04.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg_size_16_color_FFFFFF_t_70 2]: /images/20221119/d7be2ef00cff4ef580de02ce28461f63.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg_size_16_color_FFFFFF_t_70 3]: /images/20221119/7732a41a9f1c4939b3c186275dfce548.png
[20210115210100727.png]: /images/20221119/3a4f6f1f53754996ae3cddc5a9a9ffdf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg_size_16_color_FFFFFF_t_70 4]: /images/20221119/b5b532ae81c44ac9bd5b5494d82d7573.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20210115210444478.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20210115210455367.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20210115212250900.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/20210115212310258.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg==,size_16,color_FFFFFF,t_70
[2021011521234775.png]: https://img-blog.csdnimg.cn/2021011521234775.png
[20210115212358218.png]: https://img-blog.csdnimg.cn/20210115212358218.png
[2021011521240711.png]: https://img-blog.csdnimg.cn/2021011521240711.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg_size_16_color_FFFFFF_t_70 9]: https://img-blog.csdnimg.cn/20210115212515217.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg_size_16_color_FFFFFF_t_70 10]: https://img-blog.csdnimg.cn/20210115212600565.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg==,size_16,color_FFFFFF,t_70
[20210115212653987.png]: https://img-blog.csdnimg.cn/20210115212653987.png
[20210115212724340.png]: https://img-blog.csdnimg.cn/20210115212724340.png
[20210115212818629.png]: https://img-blog.csdnimg.cn/20210115212818629.png
[20210115212801844.png]: https://img-blog.csdnimg.cn/20210115212801844.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg_size_16_color_FFFFFF_t_70 11]: https://img-blog.csdnimg.cn/20210115212030276.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg==,size_16,color_FFFFFF,t_70