MySQL分組查詢

1.建立表

create table score( id int, player varchar(20), position varchar(20), score float ); insert into score values(1,'哈登','guard',30.0),(2,'杜蘭特','vanguard',29.1),(3,'詹姆斯','vanguard',28.3),(4,'安東尼-戴維斯','vanguard',27.8),(5,'利拉德','guard',27.2);

2.分組

SELECT 字段名1,........ FROM 表名 GROUP BY 字段名1......[HAVING 條件表達式]

（2）對球員位置歸類，顯示每一位置的總分

select position,sum(score) from score group by position;

(3) 查詢得分大于29的球員

select player,sum(score) from score group by player having sum(score)>29.0;

分組之後不能用where過濾要用having，having要用函數

(4)查詢球員得分大于27小于30

select player,sum(score) from score where score>27 group by player having sum(score)< 30;

2.使用LIMIT 限制查詢結果的數量

SELECT 字段名1....... FROM 表名 LIMIT [OFFSET] 記錄數

select from score limit 4;

select from score limit 3,2;

3.為表取别名

SELECT * FROM 表名 [AS] 别名;

4.函數清單