- 1sql中的合计函数:
1.1 avg(column) 返回某列的平均值
用法: select avg(column_name) from table_name
例子:选出OrderPrice 值高于 OrderPrice 平均值的客户: select OrderPrice from table where OrderPrice>(select avg(OrderPrice ) from table)
1.2. count(column)返回匹配指定条件的行数
用法:select count(column_name) from table_name
例子:计算客户Carter的订单数: select count(Customer) as CustomerNilesen from table where Customer ='Carter'
1.3. count(*)返回被选的行数
用法: select count(*) as NumberofTable from table
例子:计算表Orders的总行数:SELECT COUNT(*) AS NumberOfOrders FROM Orders
1.4. count(distinct column)返回与结果相异的数目
例子:计算 "Orders" 表中不同客户的数目。select count(dictinct Customer) as NumberOfCustomer from Orders
1.5.first(column)返回在指定域中第一个记录的值
用法: select first(column_name) from table_name
例子:查找 "OrderPrice" 列的第一个值 select first(OrderPrice) as FirstOrderPrice from Orders
1.6. last(column)返回在指定域中最后一个记录的值
例子:找 "OrderPrice" 列的最后一个值select last(OrderPrice) as LastOrderPrice from Orders
1.7. max(column)返回某列的最高值
用法:SELECT MAX(column_name) FROM table_name
1.8.min(column)返回某列的最低值
1.9.sum(column)返回某列的总和
select sum(column_name) from table_name;
- 2.MS Access 中的scaler函数,其中有语法和例子的在sql中也适用
2.1 UCASE(c) 将某个域转换为大写
语法:select ucase(column_name) from table_name
例子:选取 "LastName" 和 "FirstName" 列的内容,然后把 "LastName" 列转换为大写
select ucase(LastName) as LastName, FirstName from Persons
2.2 LCASE(c)将某个域转换为小写
语法: select lcase(column_name) from table_name
例子:选取 "LastName" 和 "FirstName" 列的内容,然后把 "LastName" 列转换为小写。
select lcase(LastName) as LastName, FirstName from Persons
2.3 MID(c, start[,end])从某个文本域中提取字符
语法: select mid(column_name, start[, length]) from table_name
例子: "City" 列中提取前 3 个字符。
select mid(City,1, 3) as SmallCity from Persons
2.4 len(c)返回某个字符域的长度
语法:select len(column_name) from table_name;
例子:取得 "City" 列中值的长度 select len(City) as LengthofCity from Persons
instr(c, char)返回在某个文本域中指定字符的数值位置
left(c, number_of_char)返回某个被请求的文本域的左侧部分
right(c, number_of_char)返回某个被请求的文本域的右侧部分
2.5 round(c, decimals)对某个数值域进行指定小数位数的四舍五入
语法: select round(column_name, decimals) from table_name
例子:把名称和价格舍入为最接近的整数 select ProductName, round(UnitPrice, 0) as UnitPrice from Products
mod(x, y)返回除法操作的余数
format(c, format)改变某个域的显示方式
datediff(d, date1, date2)用于计算执行日期
- Group By语句:用于结合合计函数,根据一个或多个列对结果集进行分组。
用法:select column_name, aggregate_funtion(column_name) from table_name where column_name operator value GROUP BY column_name
例子:查找每个客户的总金额(总订单)并对客户进行分组:
select Customer, sum(OrderPrice) from Orders group by Customer
- HAVING子句:使用having 子句是因为where 关键字无法和合计函数一起使用
用法: select column_name, aggregate_function(column_name) from table_name where column_namr operator value group by column_name having aggregate_function(column_name) operator value
例子1:查找订单总金额少于 2000 的客户
select Customer, sum(OrderPrice) from Orders group by Customer having sum(OrderPrice) < 2000
例子2:查找客户 "Bush" 或 "Adams" 拥有超过 1500 的订单总金额。
select Customer, sum(OrderPrice) from Orders where Customer='Bush' or Customer='Adams' group by customer having sum(OrderPrice) > 1500
- Now()函数:返回当期的日期和时间
语法: select now() from table_name
例子:显示当天的日期所对应的名称和价格:select ProductName, UnitPrice, now() as perDate from Products
- format()函数:用于对字段的显示进行格式化
select format(column_name, format) from table_name
例子:显示每天日期所对应的名称和价格(日期的显示格式是 "YYYY-MM-DD")
select ProductName, UnitPrice, format(now(), 'YYYY-MM-DD') as PerDate from Products