1.SQL AVG 函數(shù) ===> 返回?cái)?shù)值列的平均值恋沃。NULL 值不包括在計(jì)算中
語法:
select avg(column_name) from table_name
實(shí)例:
select avg(orderPrice) as orderAvergae from orders
// 計(jì)算訂單中的平均價(jià)格 orderAvergae ===> 950
select customer from orders
where orderPrice > (select avg(orderPrice) from orders)
// 篩選出訂單價(jià)格大于平均價(jià)格的顧客
2.SQL COUNT 函數(shù) ===> 返回匹配指定條件的行數(shù)
語法:
select count(column_name) from table_name
// count(column_name) 函數(shù)返回指定列的值的數(shù)目(null不計(jì)入)
select count(*) from table_name
// count(*) 函數(shù)返回表中的記錄數(shù)
select count(distinic column) from table_name
// count(distinic column_name) 函數(shù)返回指定列的不同值的數(shù)目
實(shí)例:
select count(customer) as customerNilsen from orders
where customerName = 'Carter'
// 希望計(jì)算客戶 "Carter" 的訂單數(shù)
select count(distinc customer) as numberOfCustomer from orders
// 計(jì)算 "Orders" 表中不同客戶的數(shù)目
3.SQL FIRST() 函數(shù) ===> 返回指定的字段中第一個(gè)記錄的值
語法:
select first(column_name) from table_name
4.SQL LAST() 函數(shù) ===> 返回指定的字段中最后一個(gè)一個(gè)記錄的值
語法:
select last(column_name) from table_name
5.SQL MAX() 函數(shù) ===> 返回一列中的最大值唐断。NULL 值不包括在計(jì)算中
語法:
select max(column_name) from table_name
6.SQL MIN() 函數(shù) ===> 返回一列中的最小值镊叁。NULL 值不包括在計(jì)算中
語法:
select min(column_name) from table_name
7.SQL SUM() 函數(shù) ===> 返回?cái)?shù)值列的總數(shù)(總額)
語法:
select sum(column_name) from table_name
8.SQL GROUP BY函數(shù) ===> 用于結(jié)合合計(jì)函數(shù)雹嗦,根據(jù)一個(gè)或多個(gè)列對結(jié)果集進(jìn)行分組
語法:
select column_name, aggregate_function(column_name)
from table_name
where column_name operator value
group by column_name