记录：简单查询语句：
有个学生表stu (name,xueke,chengji)
1、SELECT * FROM stu #查询所有数据
2、SELECT * FROM stu where NAME ='张1' #查询name是张1的数据
#查询name张1和chengji大于80的数据
3、SELECT *FROM stu where NAME ='张1' andchengji >=80
4、SELECT *FROM stu where NAME ='张1' ORDER BYchengji #按成绩正序排序，默认是正序
5、SELECT *FROM stu where NAME ='张1' ORDER BYchengji ASC #ASC正序排序
6、SELECT * FROM stu where NAME ='张1' ORDER BY chengjiDESC #DESC倒序排序
7、Group by---Group By与聚合函数
[GROUP BY先从字面上来理解，GROUP表示分组，BY后面写字段名，表示根据哪个字段进行分组，例如Excel，GROUP BY比较类似Excel里面的透视表。
GROUP BY必须得配合聚合函数来用，分组之后你可以计数（COUNT），求和（SUM），求平均数（AVG）等.]
举例：按name进行分组，取成绩总和，平均值，最大值，最小值，
7.1、SELECTname,SUM(chengji) as sum_chengji from stu GROUP BY name
7.2、SELECTname,AVG(chengji) as avg_chengji from stu GROUP BY name
7.3、SELECTname,Max(chengji) as max_chengji from stu GROUP BY name
7.4、SELECTname,min(chengji) as min_chengji from stu GROUP BY name
7.5、SELECTname,count(chengji) as count_chengjifrom stu GROUP BY name
按学科分组，取平均值
查询每科平均分
SELECT xueke,avg(chengji) as avg_全班平均分from stu GROUP BY xueke
#查询所有学科成绩大于等于70的学生
8、SELECT name from stu GROUP BY name HAVING MIN(chengji) >=70
或者
SELECT DISTINCT A.name from stu A WHERE A.name not in (SELECT DISTINCT s.namefrom xuesheng s where s.chengji <70)
[思路：采用逆向思维---求三门成绩都大于80的人，可以先查出有成绩小于80的人，再除去这些人。办法：先查出有成绩小于80的人的name，然后再用not in 或not exists 方法。此方法采用逆向思维，能快速写出高效且简单的 SQL语句。]
特意，必须有3条学科记录的才行
SELECT name from stu GROUPBY name HAVING MIN(chengji) >=70 and count(1)=3
9、计算每个人的总成绩并排名(要求显示字段：姓名，总成绩)
select name,SUM(chengji) as sum_chengji fromstu GROUPBY name ORDER BY sum_chengji DESC
10、查询总分200分以上的倒序排名
SELECTname,sum(chengji) from stu GROUP BY namehaving sum(chengji) >200 order by sum(chengji) Desc
11、查询每个学生成绩大于80分各有几门
select name,count(*) from stu where chengji >=80 GROUP by name
12、列出至少有两门课程大于等于90分的学生
select name,count(*) from stu where chengji >=80 GROUP by name HAVING count(*) >2
13、列出平均成绩大于等于80分的，并且语文课必须在90分以上的学生
select namefrom stu where name in (SELECT name FROMstu where xueke='语文' and chengji>=90) GROUP by name HAVING avg(chengji)>=80
参考：
https://blog.csdn.net/sinat_21274091/article/details/51867943
