mysql练习

#创建0630mysql练习题数据库
CREATE DATABASE IF NOT EXISTS 0630_practice;
USE 0630_practice;
#创建学生信息表
CREATE TABLE IF NOT EXISTS stuInfo(
	CLASSNO VARCHAR(50),
	STUDENTNO INT(11),
	`NAME` VARCHAR(50),
	SEX VARCHAR(50),
	COURSE VARCHAR(50),
	GRADE INT(11) 
);
#向学生信息表插入数据
INSERT INTO stuInfo VALUES
('Class1',1001,'Jennifer','F','English',98),
('Class1',1002,'Tommy','M','English',89),
('Class1',1003,'Alex','M','English',91),
('Class2',2001,'Emar','F','English',91),
('Class2',2002,'Racher','F','English',80),
('Class2',2003,'Chandler','M','English',81),
('Class3',3001,'Roben','M','English',65),
('Class3',3002,'Hugo','M','English',66),
('Class3',3003,'Ellen','F','English',67),
('Class1',1004,'Jonny','F','French',88),
('Class1',1005,'Tedesco','M','French',88),
('Class1',1006,'Scofield','M','French',89),
('Class2',2001,'Emar','F','French',80),
('Class2',2002,'Racher','F','French',78),
('Class2',2003,'Chandler','M','French',84),
('Class3',3001,'Roben','M','French',67),
('Class3',3002,'Hugo','M','French',70),
('Class3',3003,'Ellen','F','French',71);
#1)查询Class1班级英语平均分与Class2中英语平均分的差值
SELECT
ABS(
(
	SELECT AVG(s.`GRADE`)
	FROM stuInfo s
	WHERE s.`COURSE`='English' AND s.`CLASSNO`='Class1') -
(
	SELECT AVG(s.`GRADE`)
	FROM stuInfo s
	WHERE s.`COURSE`='English' AND s.`CLASSNO`='Class2')) 1班和2班英语平均分差值;
#2)查询各班级法语成绩中低于班级法语平均分人员数
SELECT SUM(CASE
	   WHEN s.`GRADE`<a.avgg THEN 1
	   ELSE 0
	   END) 人数,s.`CLASSNO` 班级
FROM stuInfo s ,
(SELECT AVG(s.`GRADE`) avgg,s.`CLASSNO` CLASS
FROM stuInfo s
WHERE s.`COURSE`='French'
GROUP BY s.`CLASSNO`) a
WHERE s.`COURSE`='French' AND  s.`CLASSNO`=a.CLASS
GROUP BY s.`CLASSNO`;
#3)查询每个班级英语成绩最好的同学输出:班级,姓名
SELECT a.n 姓名,a.c 班级
FROM 
( 
	SELECT MAX(s.`GRADE`),s.`NAME` n,s.`CLASSNO` c
	FROM stuInfo s
	WHERE  s.`COURSE`='English'
	GROUP BY s.`CLASSNO`) a;
#4)对所有班级同学英语成绩从高往低排序,结果输出:排名 班级 姓名 分数
SELECT @b:=@b+1 排名,s.`CLASSNO` 班级,s.`NAME` 姓名,s.`GRADE` 分数
FROM stuInfo s ,(SELECT @b:=0) a
WHERE s.`COURSE`='English'
ORDER BY s.`GRADE` DESC;	
#创建考试表
CREATE TABLE IF NOT EXISTS exam(
examNo INT(10) PRIMARY KEY, 
`Name` VARCHAR(10),
exam1 INT(10),
exam2 INT(10)
);
#向考试表插入数据
INSERT INTO exam VALUES
(1,'A',35,43),
(2,'B',78,65),
(3,'C',43,48),
(4,'A',59,67),
(5,'A',84,81),
(6,'C',58,59),
(7,'B',89,60),
(8,'D',59,94),
(9,'D',65,75),
(10,'D',84,63);
/*
5)
考试通过的定义:exam1成绩>=60exam2>=60
每个人有多次考试记录,有一次通过则通过,若全部未通过,则为未通过
无论哪种情况都输出模块1成绩+模块2成绩和成绩最高的记录
查询所有成员最终的考核结果,结果如模板所示写出查询语句
*/
#通过的人需要找最好成绩
SELECT d.examNo 考试编号,d.name 姓名,d.exam1 模块1考试成绩,d.exam2 模块2考试成绩, CASE WHEN TRUE THEN '考试通过' END 考试结果
FROM 
(
SELECT *
FROM 
(
SELECT DISTINCT a.*,a.exam1+a.exam2 su
FROM 
(SELECT e.*,(CASE 
	   WHEN e.exam1>=60 AND e.exam2>=60 THEN '通过'
	   ELSE '未通过'
	   END) 考试结果
	FROM exam e) a
WHERE a.考试结果='通过') b) d,
(
SELECT MAX(c.su) su,c.name
FROM
(
SELECT *
FROM 
(
SELECT DISTINCT a.*,a.exam1+a.exam2 su
FROM 
(SELECT e.*,(CASE 
	   WHEN e.exam1>=60 AND e.exam2>=60 THEN '通过'
	   ELSE '未通过'
	   END) 考试结果
	FROM exam e) a
WHERE a.考试结果='通过') b) c
GROUP BY c.name) e
WHERE d.name=e.name AND e.su=d.su
ORDER BY d.examNo;
#没通过的人需要找最好成绩
SELECT d.examNo 考试编号,d.name 姓名,d.exam1 模块1考试成绩,d.exam2 模块2考试成绩, CASE WHEN TRUE THEN '考试未通过' END 考试结果
FROM
(
SELECT *,a.exam1+a.exam2
FROM 
(SELECT e.*,(CASE 
	   WHEN e.exam1>=60 AND e.exam2>=60 THEN '通过'
	   ELSE '未通过'
	   END) 考试结果
	FROM exam e) a
WHERE a.考试结果='未通过' AND a.name NOT IN 
(SELECT DISTINCT a.name
FROM 
(SELECT e.*,(CASE 
	   WHEN e.exam1>=60 AND e.exam2>=60 THEN '通过'
	   ELSE '未通过'
	   END) 考试结果
	FROM exam e) a
WHERE a.考试结果='通过' )
GROUP BY a.name
HAVING MAX(a.exam1+a.exam2)) d;
#联合表
SELECT *
FROM
(SELECT d.examNo 考试编号,d.name 姓名,d.exam1 模块1考试成绩,d.exam2 模块2考试成绩, CASE WHEN TRUE THEN '考试通过' END 考试结果
FROM 
(
SELECT *
FROM 
(
SELECT DISTINCT a.*,a.exam1+a.exam2 su
FROM 
(SELECT e.*,(CASE 
	   WHEN e.exam1>=60 AND e.exam2>=60 THEN '通过'
	   ELSE '未通过'
	   END) 考试结果
	FROM exam e) a
WHERE a.考试结果='通过') b) d,
(
SELECT MAX(c.su) su,c.name
FROM
(
SELECT *
FROM 
(
SELECT DISTINCT a.*,a.exam1+a.exam2 su
FROM 
(SELECT e.*,(CASE 
	   WHEN e.exam1>=60 AND e.exam2>=60 THEN '通过'
	   ELSE '未通过'
	   END) 考试结果
	FROM exam e) a
WHERE a.考试结果='通过') b) c
GROUP BY c.name) e
WHERE d.name=e.name AND e.su=d.su
ORDER BY 考试编号 ASC) a1

UNION
SELECT *
FROM
(
SELECT d.examNo 考试编号,d.name 姓名,d.exam1 模块1考试成绩,d.exam2 模块2考试成绩, CASE WHEN TRUE THEN '考试未通过' END 考试结果
FROM
(
SELECT *,a.exam1+a.exam2
FROM 
(SELECT e.*,(CASE 
	   WHEN e.exam1>=60 AND e.exam2>=60 THEN '通过'
	   ELSE '未通过'
	   END) 考试结果
	FROM exam e) a
WHERE a.考试结果='未通过' AND a.name NOT IN 
(SELECT DISTINCT a.name
FROM 
(SELECT e.*,(CASE 
	   WHEN e.exam1>=60 AND e.exam2>=60 THEN '通过'
	   ELSE '未通过'
	   END) 考试结果
	FROM exam e) a
WHERE a.考试结果='通过' )
GROUP BY a.name
HAVING MAX(a.exam1+a.exam2)) d ) a2;
#创建订单表
CREATE TABLE IF NOT EXISTS orderList(
orderId INT(10),
orderDate DATETIME,
orderPerson VARCHAR(11),
orderAddress VARCHAR(11)
);
#向订单表插入数据
INSERT INTO orderList VALUES
(1001,'2020/4/1 16:58:00','A','中国'),
(1002,'2020/5/14 16:58:00','A','美国'),
(1003,'2020/5/20 16:58:00','B','英国'),
(1004,'2020/6/10 16:58:00','C','中国'),
(1005,'2020/4/23 16:58:00','D','法国'),
(1006,'2020/6/9 16:58:00','C',NULL);
#创建订单明细表
CREATE TABLE IF NOT EXISTS orderProduct(
orderId INT(10),
productId INT(10),
productNum INT(10),
productPrice DOUBLE(8,2)
);
#插入数据
INSERT INTO orderProduct VALUES
(1001,001,5,6),
(1001,002,8,23),
(1002,003,12,17),
(1003,001,3,6),
(1003,004,4,36),
(1004,005,9,15),
(1004,002,14,23),
(1005,003,23,17),
(1006,003,14,17);
#6)查询订单申请时间在2020年4月和5月,各申请人的合计订单总额结果以订单总额降序排序
SELECT c.orderperson 姓名,SUM(c.s) 45俩月总金额
FROM
	(SELECT o.`orderPerson`,o.`orderId`,b.s
	FROM 
		(SELECT SUM(a.产品总价) s, a.orderid
		FROM (SELECT *,p.`productNum`*p.`productPrice` 产品总价
		      FROM orderProduct p
		      WHERE p.`orderId` IN
				(SELECT o.`orderId`
				FROM orderList o
				WHERE CONVERT(DATE_FORMAT(CONVERT(o.`orderDate`,DATE),'%m'),UNSIGNED INTEGER) IN (4,5))) a
	GROUP BY a.orderid ) b JOIN orderList o
	ON o.`orderId`=b.orderid) c
GROUP BY c.orderperson
ORDER BY 45俩月总金额 DESC;
#7)去除英国法国的订单,查询各年月的订单申请总额
SELECT a.时间,a.orderid,SUM(a.productNum*a.productPrice) 订单申请总金额
FROM (
SELECT o.*,p.`productNum`,p.`productPrice`,CONCAT(DATE_FORMAT(o.`orderDate`,'%Y'),'年',DATE_FORMAT(o.`orderDate`,'%m'),'月') 时间
FROM orderList o JOIN orderProduct p
ON p.`orderId`=o.`orderId`
WHERE o.`orderAddress` NOT IN ('英国','法国') OR o.`orderAddress` IS NULL) a
GROUP BY a.时间,a.orderid;

#创建销售表
CREATE TABLE IF NOT EXISTS sale(
proId VARCHAR(10),
saleNum INT(10),
saleTime DATETIME
);
#插入数据
INSERT INTO sale VALUES
('C1001',15,'2020/6/1 0:00'),
('C1002',26,'2020/5/2 0:00'),
('C1003',21,'2020/4/3 0:00'),
('C1003',23,'2020/4/4 0:00'),
('C1003',0,'2020/3/5 0:00'),
('C1001',16,'2020/2/6 0:00'),
('C1002',32,'2020/1/7 0:00'),
('C1001',16,'2019/12/8 0:00'),
('C1001',32,'2019/6/9 0:00'),
('C1002',17,'2019/5/9 0:00');

#创建产品明细表
CREATE TABLE IF NOT EXISTS product(
productId VARCHAR(10) PRIMARY KEY NOT NULL,
productName VARCHAR(10),
productPrice DOUBLE(8,2)
);
#插入数据
INSERT INTO product VALUES
('C1001','产品A',45),
('C1002','产品B',52),
('C1003','产品C',39);
#8)查询2020年,每月的销售额,以及同比环比,并按照年月进行升序排序-->同比环比啥玩意?先写个总销售额
#9)查询2020年起,每月的累计销售额-->第八题一样的啊
SELECT 2020 年,a.月份 月,a.总销售数*p.productprice 总销售额
FROM
(
SELECT proid,SUM(salenum) 总销售数,2020 年,CONVERT(DATE_FORMAT(s.`saleTime`,'%m'),UNSIGNED INTEGER)月份
FROM sale s
WHERE DATE_FORMAT(s.`saleTime`,'%Y')=2020
GROUP BY 月份,proid
ORDER BY 月份 ) a JOIN product p
ON p.`productId`=a.proid
ORDER BY 月;