記錄：簡單查詢語句：
有個學(xué)生表stu (name,xueke,chengji)
1、SELECT * FROM stu? #查詢所有數(shù)據(jù)
2拴测、SELECT * FROM stu? where NAME ='張1'??? #查詢name是張1的數(shù)據(jù)
#查詢name張1和chengji大于80的數(shù)據(jù)
3枉圃、SELECT *FROM stu? where NAME ='張1' andchengji >=80?
4痒给、SELECT *FROM stu? where NAME ='張1' ORDER BYchengji?? #按成績正序排序虐块，默認(rèn)是正序
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與聚合函數(shù)
[GROUP BY先從字面上來理解，GROUP表示分組橘霎，BY后面寫字段名蔫浆，表示根據(jù)哪個字段進(jìn)行分組，例如Excel姐叁，GROUP BY比較類似Excel里面的透視表瓦盛。
GROUP BY必須得配合聚合函數(shù)來用，分組之后你可以計(jì)數(shù)（COUNT）外潜，求和（SUM）原环，求平均數(shù)（AVG）等.]
舉例：按name進(jìn)行分組，取成績總和处窥，平均值嘱吗，最大值，最小值滔驾，
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
按學(xué)科分組摊阀，取平均值
查詢每科平均分
SELECT xueke,avg(chengji)? as avg_全班平均分from stu? GROUP BY xueke
#查詢所有學(xué)科成績大于等于70的學(xué)生
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條學(xué)科記錄的才行
SELECT name from stu? GROUPBY name HAVING MIN(chengji) >=70 and count(1)=3
9蚊惯、計(jì)算每個人的總成績并排名(要求顯示字段：姓名愿卸，總成績)
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截型、查詢每個學(xué)生成績大于80分各有幾門
select name,count(*) from stu? where chengji >=80? GROUP by name
12趴荸、列出至少有兩門課程大于等于90分的學(xué)生
select name,count(*) from stu? where chengji >=80? GROUP by name HAVING count(*) >2
13、列出平均成績大于等于80分的宦焦，并且語文課必須在90分以上的學(xué)生
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
