【MySQL基础】单表查询练习案例

  • Post author:
  • Post category:mysql




案例一



数据准备

CREATE TABLE exam(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	english INT,
	chinese INT,
	math	INT
);
INSERT INTO exam VALUES (NULL,'张三',85,74,91);
INSERT INTO exam VALUES (NULL,'李四',95,90,83);
INSERT INTO exam VALUES (NULL,'王五',85,84,59);
INSERT INTO exam VALUES (NULL,'赵六',75,79,76);
INSERT INTO exam VALUES (NULL,'田七',69,63,98);
INSERT INTO exam VALUES (NULL,'李老八',89,90,83);

#查询所有学生考试成绩信息 
SELECT *FROM exam;
#查询所有学生的姓名和英语成绩 
SELECT NAME,english FROM exam;
#查询英语成绩信息(不显示重复的值)
SELECT DISTINCT english FROM exam;	
#查看学生姓名和学生的总成绩 
SELECT NAME,SUM(english + chinese + math) sumGrade FROM exam GROUP BY NAME;
#查询学生的姓名和平均分,平均分用avg别名展示
SELECT NAME,(english+chinese+math)/3  AVG FROM exam GROUP BY NAME;	
#查询李四学生的成绩:
SELECT NAME,english,chinese,math FROM exam WHERE NAME ='李四';
#查询名称叫李四学生并且英文大于90分
SELECT NAME,english FROM exam WHERE english>90 AND NAME ='李四';
#查询姓李的学生的信息
SELECT *FROM exam WHERE	NAME LIKE '李%';
#查询英语成绩是69,75,89学生的信息
SELECT *FROM exam WHERE english IN (69,75,89);	
#查询数学成绩在80-90之间的学生信息
SELECT *FROM exam WHERE math BETWEEN 80 AND 90;	
#只要有一门不及格,就找出来
SELECT *FROM exam WHERE	english< 60 OR chinese < 60 OR math < 60;	
#查询学生信息,并且按照语文成绩进行排序:
SELECT *FROM exam ORDER BY chinese;
#查询学生信息,并且按照语文成绩倒序排序:
SELECT *FROM exam ORDER	BY chinese DESC;
#查询学生信息,先按照语文成绩进行倒序排序,如果成绩相同再按照英语成绩升序排序
SELECT *FROM exam ORDER	BY chinese DESC, english ASC;
#查询姓李的学生的信息,按照英语成绩降序排序
SELECT * FROM exam WHERE NAME LIKE '李%' ORDER BY english DESC;	
#查询学生信息,按照总成绩排序,只展示学生的姓名和总分(SUM)
SELECT NAME,SUM(english + chinese + math)  SUM FROM exam GROUP BY NAME ORDER BY SUM;
#获取所有学生的英语成绩的总和:
SELECT SUM(english) FROM exam;
#获取所有学生的英语成绩和数学成绩总和:
SELECT SUM(english+math) FROM exam;
#查询姓李的学生的英语成绩的总和
SELECT SUM(english) FROM exam WHERE NAME LIKE '李%' ;
#查询所有学生各科的总成绩:
SELECT SUM(english),SUM(chinese),SUM(math) FROM exam;	
#获得姓李的学生的个数
SELECT COUNT(*) FROM exam WHERE NAME LIKE '李%';		
#获得数学成绩的最高分:
SELECT MAX(math) FROM exam;
#获得语文成绩的最小值
SELECT MIN(chinese) FROM exam;
#获取语文成绩的平均值
SELECT AVG(chinese) FROM exam;



案列二



数据准备

CREATE TABLE orderitem(
	id INT PRIMARY KEY AUTO_INCREMENT,
	product VARCHAR(20),
	price DOUBLE
);
INSERT INTO orderitem VALUES (NULL,'电视机',2999);
INSERT INTO orderitem VALUES (NULL,'电视机',2999);
INSERT INTO orderitem VALUES (NULL,'洗衣机',1000);
INSERT INTO orderitem VALUES (NULL,'洗衣机',1000);
INSERT INTO orderitem VALUES (NULL,'洗衣机',1000);
INSERT INTO orderitem VALUES (NULL,'冰箱',3999);
INSERT INTO orderitem VALUES (NULL,'冰箱',3999);
INSERT INTO orderitem VALUES (NULL,'空调',1999);
#按商品名称统计,每类商品所购买的个数:
SELECT product,COUNT(*) FROM orderitem GROUP BY product;	
#按商品名称统计,每类商品所花费的总金额:
SELECT product,SUM(price) FROM orderitem GROUP BY product;	
#按商品名称统计,统计每类商品花费的总金额在5000元以上的商品
SELECT product,SUM(price) SUM FROM orderitem GROUP BY product HAVING SUM(price) > 5000 ;	
#按商品名称统计,统计每类商品花费的总金额在5000元以上的商品,并且按照总金额升序排序
SELECT product,SUM(price) SUM FROM orderitem GROUP BY product HAVING SUM(price) > 5000 ORDER BY SUM ASC;
#按商品名称统计,统计每类商品购买数量大于1的商品,并且按照购买数量降序排序,只展示商品和购买数量
SELECT product,COUNT(*) num FROM orderitem GROUP BY product HAVING num>1 ORDER BY num DESC;



版权声明:本文为weixin_43773867原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。