数据库表操作练习

/*
1、创建成绩表，字段包括：学生姓名，语文成绩，数学成绩，英语成绩

     向表中插入多条数据；
     查询：

           （1） 查询所有学生的数学成绩和总成绩
           （2） 查询所有学生的语文和数学成绩和，按从高到低排序
           （3） 查询班级总成绩最高的学生姓名
           （4） 查询班里所有姓李学生的总成绩最高的姓名
 */
-- 创建表
 create table examres(
   id int primary key auto_increment,
   name VARCHAR(20),
   Chinese FLOAT(5,2),
   Math FLOAT(5,2),
   English FLOAT(5,2)
 );
-- 插入数据
INSERT INTO examres (name, Chinese, Math, English) VALUES ('egon',61.2,75.3,88),
  ('alex',75,86,83),
  ('yuanhao',98,96,50),
  ('wupeiqi',86,90,87),
  ('buer',100,100,100);

-- （1） 查询所有学生的数学成绩和总成绩
SELECT Math,sum(Chinese+Math+English) as toal_score FROM examres GROUP BY Math;

-- （2） 查询所有学生的语文和数学成绩和，按从高到低排序                      -- 不能添加name 或 *
SELECT sum(Chinese+Math) FROM examres GROUP BY Chinese+Math ORDER BY Chinese+Math DESC;

-- （3） 查询班级总成绩最高的学生姓名
SELECT name,max(Chinese+Math+English) FROM examres GROUP BY name ORDER BY max(Chinese+Math+English) DESC  LIMIT 1;
SELECT name,sum(Chinese+Math+English) as toal_score FROM examres GROUP BY name ORDER BY sum(Chinese+Math+English) DESC  LIMIT 1;
SELECT name,Chinese+Math+English as toal_score FROM examres where Chinese+Math+English=(SELECT max(Chinese+Math+English) from examres);

-- （4） 查询班里所有姓李学生的总成绩最高的姓名
ALTER TABLE examres CHARACTER SET utf8;
SELECT name,Chinese+Math+English as toal_score FROM examres where name LIKE 'e%' ORDER BY Chinese+Math+English DESC LIMIT 1;




/*
2、创建一张某超市的购物表，字段包括：商品名，购物价格，商品生茶日期，商品分类；

     向该表中插入多条数据；


     查询：（1）每一类商品花的总价格

                （2）统计每类商品各有多少件

                （3）统计水果花了多少钱（两种方式实现）

                （4）统计购买的2017-01-12日生产的商品中价格最贵的商品（插入的数据中包括2017-01-12生产的商品）

                （5）统一购买商品的总价格

 */

-- 创建表
CREATE TABLE goods_list(
      id INT PRIMARY KEY AUTO_INCREMENT,
      name VARCHAR(20),
      price FLOAT(6,2),
      pro_date DATE,
      class VARCHAR(20)
) CHARACTER SET utf8;

-- 插入值
INSERT INTO goods_list (name, price, pro_date, class) VALUES ('苹果',20,20170612,'水果'),
                                             ('香蕉',800,20170602,'水果'),
                                             ('水壶',120,20170612,'电器'),
                                             ('被罩',70,20170612,'床上用品'),
                                             ('音响',420,20170612,'电器'),
                                             ('床单',55,20170612,'床上用品'),
                                             ('草莓',34,20170612,'水果');

-- （1）每一类商品花的总价格
SELECT class,sum(price) FROM goods_list GROUP BY class;

-- （2）统计每类商品各有多少件
SELECT class,count(price) FROM goods_list GROUP BY class;

-- （3）统计水果花了多少钱（两种方式实现）
SELECT class,sum(price) FROM goods_list WHERE class='水果' GROUP BY class;

SELECT sum(price) FROM goods_list WHERE price in (SELECT price FROM goods_list WHERE class='水果');

-- （4）统计购买的2017-06-12日生产的商品中价格最贵的商品（插入的数据中包括2017-01-12生产的商品）
SELECT id,name,price,pro_date FROM goods_list WHERE pro_date=20170612 ORDER BY price DESC LIMIT 1;


-- （5）统一购买商品的总价格
SELECT sum(price) as toal_price FROM goods_list;