/*
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;