1.AVG()函數(shù),返回所有列的平均值忠寻,也可返回特定列或行的平均值做裙。
返回所有列的平均值:
select avg(book_id) as avg_book_id from chapter;/*返回某列的平均值 */?
返回特定列或行的平均值:
?select avg(words) as avg_word from chapter where book_id = 2;??
select avg(book_id) as avg_book_id, avg(words) as avg_word from chapter where book_id = 3;?
?select avg(book_id), avg(words) as avg_word from chapter where book_id = 3;
2.COUNT()函數(shù)鞍泉,返回表中行的數(shù)目疑苫,也可返回符合特定條件的行的數(shù)目。
count(*) 對表中行的數(shù)目進行計數(shù)授舟,不管列表中包含的是空值(null)救恨,還是非空值。
count(列名)對特定列中具有值的行進行計數(shù)释树,忽略null值肠槽。
select count(*) as num_name from chapter;?/*對表中行的數(shù)目進行計數(shù),不管表列中包含的是空值(null)還是非空值奢啥。*/?
select count(name) as num_name from chapter;/* 對特定列中具有值的行進行計數(shù)秸仙,不包含值為null的行。*/?
3.MAX()函數(shù)桩盲,返回指定列中的最大值寂纪,忽略列值為null的行,必須指定列名max(列名)赌结。
select max(words) as max_word from chapter;/*返回指定列中的最大值捞蛋,忽略列值為null的行;用于文本數(shù)據(jù)時柬姚,max()返回按該列排序后的最后一列*/?
4.MIN()函數(shù)拟杉,返回指定列中的最小值,忽略列值為null的行量承,必須指定列名min(列名)搬设。
select min(book_id) as min_word from chapter;/*與max()功能正好相反,返回指定列的最小值*/
?5.sum()函數(shù)撕捍,返回指定列值的和(總計)拿穴,忽略列值為null的行,必須指定列名min(列名)忧风。
?select sum(words) as sum_words from chapter where book_id = 2; /* 忽略列值為null的行 */?
?select sum(words) as sum_words from chapter;
6.聚集不同的值
1)對所有行執(zhí)行集數(shù)默色,指定ALL參數(shù)或不指定參數(shù)(因為ALL是默認行為);
2)只包含不同的值狮腿,指定DISTINCT參數(shù)腿宰。
?select avg(distinct words) as avg_word from chapter where book_id = 2;?
7.組合聚集函數(shù)(select 語句可根據(jù)需要包含多個聚集函數(shù))
select count(*) as count_chapter,
?min(book_id) as min_book,?
max(words) as max_word,
?avg(words) as avg_word?
?from chapter;
數(shù)據(jù)源來自:SQL必知必會書籍? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??今日學習至此? ? ?2019.04.28