MySQL 处理数据

1、前言

MySQL目前在国内的使用率是比较高的，学习好sql语言是许多公司需要的能力。我们来学习一下MySQL在查找上的运用。

2、自动售货机数据处理

DDL语句创建数据库

CREATE DATABASE IF NOT EXISTS test;

导入数据

 

 导入成功

开始查询

1. 统计每台售货机各自全年的销售额

SELECT order_device_no AS 设备编号,  SUM(order_item_price) AS 全年销售额,  COUNT(order_time) AS 购买次数 FROM tb_orders GROUP BY order_device_no;

                        

2. 统计“饮料”和“非饮料”各自全年的销售额

SELECT item_class1_category AS 饮料与非饮料, SUM(order_item_price) AS 全年销售额 FROM tb_categories LEFT JOIN tb_orders ON tb_orders.order_item_name=tb_categories.item_name GROUP BY item_class1_category;

                        

3. 统计销量前十商品的名称、销量和销售额

SELECT order_item_name AS 商品名称, COUNT(order_device_no) AS 销量, SUM(order_item_price) AS 商品销售额 FROM tb_orders GROUP BY order_item_name ORDER BY COUNT(order_device_no) DESC LIMIT 10;

                        

4. 统计每月销售额（注：可使用 MONTH()函数）

SELECT MONTH(order_time) AS 月份, SUM(order_item_price) AS 每月销售额 FROM tb_orders GROUP BY MONTH(order_time) ORDER BY MONTH(order_time);

                        

5. 统计哪种“可乐”全年的销量更高

SELECT order_item_name AS 商品, COUNT(order_device_no) AS 销量 FROM tb_orders WHERE order_item_name LIKE '%可乐%' GROUP BY order_item_name ORDER BY COUNT(order_device_no) DESC LIMIT 1;

                        

请的结合你所学数据库知识改造你的数据库（新建或修改表结构），使之能够存储售货机安放 地点的信息。 首先创建一个新的字段用于存储地点信息 order_address ALTER TABLE tb_orders ADD order_address VARCHAR(50); 再向 order_address 中更新信息 UPDATE tb_orders SET order_address='学校正门' WHERE order_device_no='E43A6E078A04172'; UPDATE tb_orders SET order_address='学校后门' WHERE order_device_no='E43A6E078A07631'; UPDATE tb_orders SET order_address='运动场' WHERE order_device_no='E43A6E078A04134';UPDATE tb_orders SET order_address='食堂区' WHERE order_device_no='E43A6E078A04228'; UPDATE tb_orders SET order_address='宿舍区' WHERE order_device_no='E43A6E078A06874';