MySQL第二天作业
先创建数据库：
1、创建成绩表，字段包括：学生姓名，语文成绩，数学成绩，英语成绩
答：
mysql> CREATE TABLE cross_table(name VARCHAR(20),
-> Chinese float(4,1),
-> Math float(4,1), /*创建成绩表字段*/
-> English float(4,1)
-> )charset=utf8;
Query OK, 0 rows affected (0.06 sec)
mysql> INSERT INTO cross_table(name,Chinese,Math,English) VALUES
-> ("黄未",100,100,100),
-> ("贾永旭",0,0,0),
-> ("Alex",20,30,0),
-> ("egon",70,60,40),
-> ("苑昊",80,92,83.5), /*往表中插入多条数据*/
-> ("武沛齐",62.5,77.5,30),
-> ("景丽洋",99,90,97.5),
-> ("李文周",60,60,59.5);
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0
（1）查询所有学生的数学成绩和总成绩
mysql> SELECT name 姓名, Math 数学,Chinese+Math+English,总成绩 FROM cross_table
+-----------+--------+-----------+
| 姓名 | 数学 | 总成绩 |
+-----------+--------+-----------+
| 黄未 | 100.0 | 300.0 |
| 贾永旭 | 0.0 | 0.0 |
| Alex | 30.0 | 50.0 |
| egon | 60.0 | 170.0 |
| 苑昊 | 92.0 | 255.5 |
| 武沛齐 | 77.5 | 170.0 |
| 景丽洋 | 90.0 | 286.5 |
| 李文周 | 60.0 | 179.5 |
+-----------+--------+-----------+
8 rows in set (0.00 sec)
(2)查询所有学生的语文和数学成绩的总和，按从高到低排序
mysql> SELECT name 姓名,Chinese+Math 总成绩 FROM cross_table ORDER BY Chinese+Math DESC;
+-----------+-----------+
| 姓名 | 总成绩 |
+-----------+-----------+
| 黄未 | 200.0 |
| 景丽洋 | 189.0 |
| 苑昊 | 172.0 |
| 武沛齐 | 140.0 |
| egon | 130.0 |
| 李文周 | 120.0 |
| Alex | 50.0 |
| 贾永旭 | 0.0 |
+-----------+-----------+
8 rows in set (0.00 sec)
(3)查询班级总成绩最高的学生姓名
方法一：
mysql> SELECT name 姓名, Chinese+Math+English 总成绩 FROM cross_table ORDER BY Chinese+Math+English DESC LIMIT 1;
+--------+-----------+
| 姓名 | 总成绩 |
+--------+-----------+
| 黄未 | 300.0 |
+--------+-----------+
1 row in set (0.00 sec)
方法二：
mysql> SELECT name 姓名, Chinese+Math+English 总成绩 FROM cross_table WHERE Chinese+Math+English=(SELECT MAX(Chinese+Math+English) FROM cross_table);
+--------+-----------+
| 姓名 | 总成绩 |
+--------+-----------+
| 黄未 | 300.0 |
+--------+-----------+
1 row in set (0.00 sec)
(4)查看班里所有姓景学生的总成绩最高的姓名
由于表里学生只有一位姓景的同学，所以再 新添加两位姓景的同学：
mysql> INSERT INTO cross_table(name,Chinese,Math,English) VALUES
-> ("景甜",70,77.5,88),
-> ("景天",99,55,0);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT name 姓名, Chinese 语文,Math 数学,English 英语 FROM cross_table;
+-----------+--------+--------+--------+
| 姓名 | 语文 | 数学 | 英语 |
+-----------+--------+--------+--------+
| 黄未 | 100.0 | 100.0 | 100.0 |
| 贾永旭 | 0.0 | 0.0 | 0.0 |
| Alex | 20.0 | 30.0 | 0.0 |
| egon | 70.0 | 60.0 | 40.0 |
| 苑昊 | 80.0 | 92.0 | 83.5 |
| 武沛齐 | 62.5 | 77.5 | 30.0 |
| 景丽洋 | 99.0 | 90.0 | 97.5 |
| 李文周 | 60.0 | 60.0 | 59.5 |
| 景甜 | 70.0 | 77.5 | 88.0 |
| 景天 | 99.0 | 55.0 | 0.0 |
+-----------+--------+--------+--------+
10 rows in set (0.00 sec)
mysql> SELECT name 姓名,Chinese+Math+English 总成绩 FROM cross_table WHERE name LIKE '景%' ORDER BY Chinese+Math+English DESC LIMIT 1;
+-----------+-----------+
| 姓名 | 总成绩 |
+-----------+-----------+
| 景丽洋 | 286.5 |
+-----------+-----------+
1 row in set (0.00 sec)
2、创建一张某超市的购物表，字段包括：商品名，购物价格，商品生产日期，商品分类
答：
mysql> CREATE TABLE shopping_list(product_name VARCHAR(20),
-> product_price float(7,2),
-> product_date DATE,
-> product_class VARCHAR(20)
-> )CHARSET=UTF8;
Query OK, 0 rows affected (0.06 sec)
mysql> DESC shopping_list;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| product_name | varchar(20) | YES | | NULL | |
| product_price | float(7,2) | YES | | NULL | |
| product_date | date | YES | | NULL | |
| product_class | varchar(20) | YES | | NULL | |
+---------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
/*向购物表中插入数据*/
mysql> INSERT INTO shopping_list(product_name,product_price,product_date,product_class) VALUES
-> ("吹风机",99,20160106,"电器"),
-> ("香皂",7.9,20170307,"生活用品"),
-> ("西瓜",0.99,20170720,"水果"),
-> ("空调",3999,20160912,"电器"),
-> ("牙膏",12,20170309,"生活用品"),
-> ("苹果",7.98,20170719,"水果"),
-> ("电饭煲",399,20160702,"电器");
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0
（1）查询每一类商品花的总价格
mysql> SELECT product_class 商品种类,SUM(product_price) 总价格 from shopping_list GROUP BY product_class;
+--------------+-----------+
| 商品种类 | 总价格 |
+--------------+-----------+
| 水果 | 8.97 |
| 生活用品 | 19.90 |
| 电器 | 4497.00 |
+--------------+-----------+
3 rows in set (0.00 sec)
（2）统计每类商品各有多少件
mysql> SELECT product_class 商品种类,count(product_name) 数量 FROM shopping_list GROUP BY product_class;
+--------------+--------+
| 商品种类 | 数量 |
+--------------+--------+
| 水果 | 2 |
| 生活用品 | 2 |
| 电器 | 3 |
+--------------+--------+
3 rows in set (0.00 sec)
（3）统计水果花了多少钱
方法一：
mysql> SELECT product_class 商品种类, sum(product_price) 总价格 FROM shopping_list WHERE product_class='水果';
+--------------+-----------+
| 商品种类 | 总价格 |
+--------------+-----------+
| 水果 | 8.97 |
+--------------+-----------+
1 row in set (0.00 sec)
方法二：
mysql> SELECT product_class 商品种类,sum(product_price) 总价格 FROM shopping_list GROUP BY product_class HAVING product_class='水果';
+--------------+-----------+
| 商品种类 | 总价格 |
+--------------+-----------+
| 水果 | 8.97 |
+--------------+-----------+
1 row in set (0.00 sec)
（4）统计购买时间的2017年7月20日生产的商品中最贵的商品（插入的数据中包括2017年7月20日产生的商品）
答：
先查看一下表里是否有2017年7月20日生产的商品：
mysql> SELECT product_name 商品名称, product_price 商品价格,product_date 生产日期,product_class 商品种类 FROM shopping_list;
+--------------+--------------+--------------+--------------+
| 商品名称 | 商品价格 | 生产日期 | 商品种类 |
+--------------+--------------+--------------+--------------+
| 吹风机 | 99.00 | 2016-01-06 | 电器 |
| 香皂 | 7.90 | 2017-03-07 | 生活用品 |
| 西瓜 | 0.99 | 2017-07-20 | 水果 |
| 空调 | 3999.00 | 2016-09-12 | 电器 |
| 牙膏 | 12.00 | 2017-03-09 | 生活用品 |
| 苹果 | 7.98 | 2017-07-19 | 水果 |
| 电饭煲 | 399.00 | 2016-07-02 | 电器 |
+--------------+--------------+--------------+--------------+
7 rows in set (0.00 sec)
经查看，只有一件商品符合要求，为了让实验结果更加清晰，向表格内增加2种2017年7月20日生产的商品：
mysql> INSERT INTO shopping_list(product_name,product_price,product_date,product_class) VALUES
-> ("荔枝",15,20170720,"水果"),
-> ("索尼平板电视",13999,20170720,"电器");
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT product_name 商品名称, product_price 商品价格,product_date 生产日期,product_class 商品种类 FROM shopping_list;
+--------------------+--------------+--------------+--------------+
| 商品名称 | 商品价格 | 生产日期 | 商品种类 |
+--------------------+--------------+--------------+--------------+
| 吹风机 | 99.00 | 2016-01-06 | 电器 |
| 香皂 | 7.90 | 2017-03-07 | 生活用品 |
| 西瓜 | 0.99 | 2017-07-20 | 水果 |
| 空调 | 3999.00 | 2016-09-12 | 电器 |
| 牙膏 | 12.00 | 2017-03-09 | 生活用品 |
| 苹果 | 7.98 | 2017-07-19 | 水果 |
| 电饭煲 | 399.00 | 2016-07-02 | 电器 |
| 荔枝 | 15.00 | 2017-07-20 | 水果 |
| 索尼平板电视 | 13999.00 | 2017-07-20 | 电器 |
+--------------------+--------------+--------------+--------------+
9 rows in set (0.00 sec)
mysql> SELECT product_name 商品名称,product_price 商品价格 FROM shopping_list WHERE product_date='2017-07-20' ORDER BY product_price DESC LIMIT 1;
+--------------------+--------------+
| 商品名称 | 商品价格 |
+--------------------+--------------+
| 索尼平板电视 | 13999.00 |
+--------------------+--------------+
1 row in set (0.00 sec)
（5）统一购买商品的总价格
mysql> SELECT SUM(product_price) 商品总价 FROM shopping_list;
+--------------+
| 商品总价 |
+--------------+
| 18539.87 |
+--------------+
1 row in set (0.00 sec)