SQL函数
内建SQL函数的语法:
select function(列) from 表
函数的类型:aggregate函数(合计函数),scalar函数
合计函数(aggregate functions)
aggregate函数的操作面向一系列的值,并返回一个单一的值
如果在select语句的项目列表中的众多其他表达式中使用select语句,则这个select必须使用group by语句
MS Access中的合计函数
函数 | 描述 |
---|---|
AVG(column) | 返回某列的平均值 |
COUNT(column) | 返回某列的行数(不包括 NULL 值) |
COUNT(*) | 返回被选行数 |
FIRST(column) | 返回在指定的域中第一个记录的值 |
LAST(column) | 返回在指定的域中最后一个记录的值 |
MAX(column) | 返回某列的最高值 |
MIN(column) | 返回某列的最低值 |
STDEV(column) | |
STDEVP(column) | |
SUM(column) | 返回某列的总和 |
VAR(column) | |
VARP(column) |
在SQL Server中的合计函数
函数 | 描述 |
---|---|
AVG(column) | 返回某列的平均值 |
BINARY_CHECKSUM | |
CHECKSUM | |
CHECKSUM_AGG | |
COUNT(column) | 返回某列的行数(不包括NULL值) |
COUNT(*) | 返回被选行数 |
COUNT(DISTINCT column) | 返回相异结果的数目 |
FIRST(column) | 返回在指定的域中第一个记录的值(SQLServer2000 不支持) |
LAST(column) | 返回在指定的域中最后一个记录的值(SQLServer2000 不支持) |
MAX(column) | 返回某列的最高值 |
MIN(column) | 返回某列的最低值 |
STDEV(column) | |
STDEVP(column) | |
SUM(column) | 返回某列的总和 |
VAR(column) | |
VARP(column) |
Scalar函数
scalar函数的操作面向某个单一的值,并返回基于输入值的一个单一的值
MS Access 中的 scalar函数
函数 | 描述 |
---|---|
UCASE(c) | 将某个域转换为大写 |
LCASE(c) | 将某个域转换为小写 |
MID(c,start[,end]) | 从某个文本域提取字符 |
LEN(c) | 返回某个文本域的长度 |
INSTR(c,char) | 返回在某个文本域中指定字符的数值位置 |
LEFT(c,number_of_char) | 返回某个被请求的文本域的左侧部分 |
RIGHT(c,number_of_char) | 返回某个被请求的文本域的右侧部分 |
ROUND(c,decimals) | 对某个数值域进行指定小数位数的四舍五入 |
MOD(x,y) | 返回除法操作的余数 |
NOW() | 返回当前的系统日期 |
FORMAT(c,format) | 改变某个域的显示方式 |
DATEDIFF(d,date1,date2) | 用于执行日期计算 |
SQL AVG函数
AVG函数返回数值列的平均值,NULL值不包括在计算中。
语法:select avg(column_name) from table_name
eg:
计算orderprice字段的平均值
select avg (orderprice) as orderaverage from orders
找到orderprice值高于orderprice平均值的客户
select customer from orders
where orderprice>(select avg(orderprice) from orders)
COUNT()函数
count(column_name)语法:返回匹配指定条件的行数(null不计入)
select count(column_name) from table_name
count(*)语法:返回表中的记录数
select count(*) from table name
count(distinct column_name)语法:返回指定列的不同值的数目
select count(distinct column_name) from table_name
count(distinct)适用于oracle和sql server,不能用于access
eg:
O_Id | OrderDate | OrderPrice | Customer |
---|---|---|---|
1 | 2008/12/29 | 1000 | Bush |
2 | 2008/11/23 | 1600 | Carter |
3 | 2008/10/05 | 700 | Bush |
4 | 2008/09/28 | 300 | Bush |
5 | 2008/08/06 | 2000 | Adams |
6 | 2008/07/21 | 100 | Carter |
计算客户carter的订单数:
select count (customer) as customernilsen from orders
where customer ='carter'
结果:
CustomerNilsen |
---|
2 |
COUNT(*)实例
如果我们省略where子句,比如这样
select count(*) as numberoforders from orders
结果:
NumberOfOrders |
---|
6 |
这是表中的总行数
计算“orders”表中不同客户的数目
select count(distinct customer) as numberofcustomers from orders
结果:
NumberOfOrders |
---|
3 |
这是orders表中不同客户bush,carter和adams的数目
FIRST()函数
返回指定的字段中第一个记录的值。可使用order by语句对记录进行排序。
语法:select first(column_name) from table_name
eg:查找orderprice列的第一个值
select first(orderprice) as firstorderprice from orders
结果:
FirstOrderPrice |
---|
1000 |
LAST()函数
返回指定的字段中最后一个记录的值,可使用order by语句对记录进行排序
语法:select last(column_name) from table_name
eg:
查找orderprice列的最后一个值
select last(orderprice) as lastorderprice from orders
结果:
LastOrderPrice |
---|
100 |
MAX函数
返回列中的最大值,null值不包括在计算中
语法:select max(column_name) from table_name
MIN函数
返回列中的最小值,null值不包括在计算中
语法:select min(column_name) from table_name
注:max和min也可用于文本列,以获得按字母顺序排列的最高或最低值
SUM函数
返回数值列的总数(总额)
语法:select sum(column_name) from table_name
GROUP BY语句
合计函数(比如sum)常常需要添加group by 语句,根据一个或多个列对结果集进行分组
语法:
select column_name, aggregate_function(column_name)
from table_name
where column_name operator value
group by column_name
eg:
希望查找每个客户的总金额(总订单),使用group by语句对客户进行组合
select customer,sum(orderprice) from orders
group by customer
结果:
Customer | SUM(OrderPrice) |
---|---|
Bush | 2000 |
Carter | 1700 |
Adams | 2000 |
若省略group by 则
Customer | SUM(OrderPrice) |
---|---|
Bush | 5700 |
Carter | 5700 |
Bush | 5700 |
Bush | 5700 |
Adams | 5700 |
Carter | 5700 |
对一个以上的列应用group by:
select customer,orderdate,sum(orderprice) from orders
group by customer, orderdate
HAVING子句
增加having子句的原因是,where关键字无法与合计函数一起使用
语法:
select column_name, aggregate_function(column_name)
from table_name
shere column_name opeator value
group by column_name
having aggregate_function(column_name) opeator value
eg:
查找订单总金额少于2000的客户
select customer, sum(orderprice) from orders
group by customer
having sum(orderprice)<2000
结果:
Customer | SUM(OrderPrice) |
---|---|
Carter | 1700 |
查找bush或adams拥有超过1500的订单总金
select customer, sum(orderprice) from orders
where customer='bush' or customer='adams'
group by customer
having sum(orderprice)>1500
结果:
Customer | SUM(OrderPrice) |
---|---|
Bush | 2000 |
Adams | 2000 |
UCASE()函数
把字段的值转换为大写
语法:
select ucase(column_name) from table_name
eg:
select ucase(lastname) as lastneme,firstname from persons
LCASE函数
把字段的值转换为小写
语法:
select ucase(column_name) from table_name
eg:
select Lcase(lastname) as lastneme,firstname from persons
MID函数
用于从文本字段提取字符
语法:
select mid(column_name,start,[length]) from table_name
参数 | 描述 |
---|---|
column_name | 必需。要提取字符的字段。 |
start | 必需。规定开始位置(起始值是 1)。 |
length | 可选。要返回的字符数。如果省略,则 MID() 函数返回剩余文本。 |
eg:
select mid(city,1,3) as smallcity from persons
SmallCity |
---|
Lon |
New |
Bei |
LEN()函数
返回文本字段中值的长度
语法:select len(column_name) from table_name
eg: select len(city) as lengthofcity from persons
LengthOfCity |
---|
6 |
8 |
7 |
ROUND()函数
用于把数值阻断舍入为指定的小数位数
语法:select round(column_name,decimals) from table_name
参数 | 描述 |
---|---|
column_name | 必需。要舍入的字段。 |
decimals | 必需。规定要返回的小数位数。 |
eg:
Prod_Id | ProductName | Unit | UnitPrice |
---|---|---|---|
1 | gold | 1000 g | 32.35 |
2 | silver | 1000 g | 11.56 |
3 | copper | 1000 g | 6.85 |
把名称和价格舍入最接近的整数
select productname, round(unitprice,0) as unitprice from products
ProductName | UnitPrice |
---|---|
gold | 32 |
silver | 12 |
copper | 7 |
NOW()函数
返回当前的日期和时间
如果是sql server数据库,用getdate()函数来获得当前日期时间
语法:select now() fromtable_name
Prod_Id | ProductName | Unit | UnitPrice |
---|---|---|---|
1 | gold | 1000 g | 32.35 |
2 | silver | 1000 g | 11.56 |
3 | copper | 1000 g | 6.85 |
select productname, unitprice, now() as perdate from products
ProductName | UnitPrice | PerDate |
---|---|---|
gold | 32.35 | 12/29/2008 11:36:05 AM |
silver | 11.56 | 12/29/2008 11:36:05 AM |
copper | 6.85 | 12/29/2008 11:36:05 AM |
FORMAT()函数
用于对字段的显示进行格式化
语法: select format(column_name,format) from table_name
参数 | 描述 |
---|---|
column_name | 必需。要格式化的字段。 |
format | 必需。规定格式。 |
eg:
Prod_Id | ProductName | Unit | UnitPrice |
---|---|---|---|
1 | gold | 1000 g | 32.35 |
2 | silver | 1000 g | 11.56 |
3 | copper | 1000 g | 6.85 |
希望显示每天日期所对应的名称和价格(日期显示格式为YYYY-MM-DD)
select productname , unitprice , format(nuw(),'YYYY-MM-DD') as perdate
from products
ProductName | UnitPrice | PerDate |
---|---|---|
gold | 32.35 | 12/29/2008 |
silver | 11.56 | 12/29/2008 |
copper | 6.85 | 12/29/2008 |
SQL快速参考
语句 | 语法 |
---|---|
AND / OR | SELECT column_name(s) FROM table_name WHERE condition AND|OR condition |
ALTER TABLE (add column) | ALTER TABLE table_name ADD column_name datatype |
ALTER TABLE (drop column) | ALTER TABLE table_name DROP COLUMN column_name |
AS (alias for column) | SELECT column_name AS column_alias FROM table_name |
AS (alias for table) | SELECT column_name FROM table_name AS table_alias |
BETWEEN | SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2 |
CREATE DATABASE | CREATE DATABASE database_name |
CREATE INDEX | CREATE INDEX index_name ON table_name (column_name) |
CREATE TABLE | CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, ....... ) |
CREATE UNIQUE INDEX | CREATE UNIQUE INDEX index_name ON table_name (column_name) |
CREATE VIEW | CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition |
DELETE FROM | DELETE FROM table_name (Note: Deletes the entire table!!) or DELETE FROM table_name |
DROP DATABASE | DROP DATABASE database_name |
DROP INDEX | DROP INDEX table_name.index_name |
DROP TABLE | DROP TABLE table_name |
GROUP BY | SELECT column_name1,SUM(column_name2) FROM table_name GROUP BY column_name1 |
HAVING | SELECT column_name1,SUM(column_name2) FROM table_name GROUP BY column_name1 HAVING SUM(column_name2) condition value |
IN | SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,..) |
INSERT INTO | INSERT INTO table_name VALUES (value1, value2,....) or INSERT INTO table_name |
LIKE | SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern |
ORDER BY | SELECT column_name(s) FROM table_name ORDER BY column_name [ASC|DESC] |
SELECT | SELECT column_name(s) FROM table_name |
SELECT * | SELECT * FROM table_name |
SELECT DISTINCT | SELECT DISTINCT column_name(s) FROM table_name |
SELECT INTO (used to create backup copies of tables) | SELECT * INTO new_table_name FROM original_table_name or SELECT column_name(s) |
TRUNCATE TABLE (deletes only the data inside the table) | TRUNCATE TABLE table_name |
UPDATE | UPDATE table_name SET column_name=new_value [, column_name=new_value] WHERE column_name=some_value |
WHERE | SELECT column_name(s) FROM table_name WHERE condition |