SQL分組查詢(xún)語(yǔ)句和分頁(yè)查詢(xún)語(yǔ)句
1. 分組查詢(xún)
/*
分組函數(shù)
SELECT 字段列表 FROM 表名[WHERE 分組前條件限定]GROUP BY 分組字段名 [HAVING 分組后條件過(guò)濾];
.*/
select * from stu ;
-- 1.查詢(xún)男同學(xué)和女同學(xué)各自的數(shù)學(xué)平均分
select sex, avg(math) from stu group by sex;
select name, sex, avg(math) from stu group by sex;
-- 2.查詢(xún)男同學(xué)和女同學(xué)各自的數(shù)學(xué)平均分,以及及各自人數(shù)
select sex, avg(math),count(*) from stu group by sex;
-- 3。查詢(xún)男同學(xué)和女同學(xué)各自的數(shù)學(xué)平均分,以及及各自人數(shù),要求:分?jǐn)?shù)低于70分的不參與分組
select sex, avg(math),count(*) from stu where math > 70 group by sex;
-- 4.查詢(xún)男同學(xué)和女同學(xué)各自的數(shù)學(xué)平均分,以及及各自人數(shù),要求:分?jǐn)?shù)低于70分的不參與分組,分組之后人數(shù)大于2
select sex, avg(math),count(*) from stu where math > 70 group by sex having count(*) >2;
2. 分頁(yè)查詢(xún)
/*
分頁(yè)查詢(xún):
SELECT 字段列表 FROM 表名 LIMIT起始索引 ,查詢(xún)條目數(shù)
* 起始索引:從0開(kāi)始
*/
-- 1.從0開(kāi)始查詢(xún),查詢(xún)3條數(shù)據(jù)
select * from stu limit 0 , 3;
-- 2.每頁(yè)顯示3條數(shù)據(jù),查詢(xún)第1頁(yè)數(shù)據(jù)
select * from stu limit 0 , 3;
--3.每頁(yè)顯示3條數(shù)據(jù),查詢(xún)第2頁(yè)數(shù)據(jù)
select * from stu limit 3 ,3;
-- 4.每頁(yè)顯示3條數(shù)據(jù),查詢(xún)第3頁(yè)數(shù)據(jù)
select * from stu limit 6 ,3;
-- 起始索引 = (當(dāng)前頁(yè)碼-1)*每頁(yè)顯示的條數(shù)
本文來(lái)自博客園,作者:RHCHIK,轉(zhuǎn)載請(qǐng)注明原文鏈接:http://www.rzrgm.cn/suihung/p/16572951.html

浙公網(wǎng)安備 33010602011771號(hào)