1.函数的语法
内建SQL函数的语法
SELECT function (列)FROM 表
函数的类型
在SQL中,基本的函数类型和种类有若干重,函数的基本类型有 Aggregate 函数 和Scalar函数
合计函数(Aggregate 函数)
aggregate函数的操作面向同一系列的值,并返回单一的值,
注释 如果在SELECT语句的项目列表中的众多其它表达式中使用SELECT语句,则这个SELECT语句必须使用 group by语句
以下复制于jb51.net
"Persons" table (在大部分的例子中使用过)
Name | Age |
---|---|
Adams, John | 38 |
Bush, George | 33 |
Carter, Thomas | 28 |
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函数
语法
SELECT AVG(column_name)From table_name
SQL AVG()实例
下面这个Order表
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 |
现在我们希望计算OrderPrice字段中得平均数
使用下面的语句
SELECT AVG(OrderPrice) As OrderAverage FROM Orders
结果集为
OrderAverage
950
例2
现在,我们希望找到OrderPrice值高于OrderPrice平均值的客户
我们 使用如下SQL语句
SELECT customer FROM Orders where OrderPrice >(SELECT FROM AVG(OrderPrice)FROM Orders)
结果集为
Customer
Bush
Carter
Adams
3.SQL COUNT()函数
语法
Count(column_name)函数返回指定列的值的数值(NULL值不计入)
count(column_name)FROM table_name
SQL count(*)语法
count(*)返回表中的记录数
SELECT count(*)FROM table_name
SQL count(distinct column_name)语法
count(distinct column_name)函数返回指定列的不同值的数目
SELECT COUNT(distinct column_name) FROM table_name
注意 count(distinct)适用于Oracle 和Microsoft SQL Server,但不适用于Microsoft Access
SQL count(column_name)实例
我们拥有下列 "Orders" 表:
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’
结果为2 因为 Carter工有两个订单
CustomerNilsen
2
SQL COUNT(*)实例
如果我们省略where子句
SELECT COUNT(*) As NumberOfOrders FROM Orders
结果为
NumberOfOrders
6
这为表中的总行数
SQL COUNT(distinct column_name实例)
现在我们希望计算Orders表中不同客户的数目
SELECT COUNT(Distinct customer) As NumberOfCustomers FROM Orders
结果为NumberOfCustomers
3
这是Orders表中不同客户的数目
4.SQL first()函数
返回指定的字段中第一个记录的值
提示 可使用 Order by对记录进行排序
语法
SELECT first(column_name) FROM table_name
实例
我们拥有下面这个 "Orders" 表:
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 |
我们希望查找OrderPrice列的第一个值
SELECT first(OrderPrice) As FirstOrderPrice FROM Orders
结果为
FirstOrderPrice
1000
5.SQL
返回指定的字段中最后一个记录的值
提示 可使用 Order by对记录进行排序
语法
SELECT Last(column_name) FROM table_name
实例
我们拥有下面这个 "Orders" 表:
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 |
我们希望查找OrderPrice列的最后一个值
SELECT Last(OrderPrice) As LastOrderPrice FROM Orders
结果为
FirstOrderPrice
100
6.SQL MAX()函数
返回一列中得最大的值,NULL值不包括在计算中
语法
SELECT MAX(column_name)FROM table_name
注意 MIN 和 MAX 也用于文本列,以获得按字母顺序排列的最高或者最低值
SQL MAX()实例
我们拥有下面这个 "Orders" 表:
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 |
SELECT MAX (OrderPrice)As LargestOrderPrice FROM Orders
结果类似为
LargestOrderPrice
2000
7.SQL MIN()函数
返回一列中得最小的值,NULL值不包括在计算中
语法
SELECT MIN(column_name)FROM table_name
注意 MIN 和 MAX 也用于文本列,以获得按字母顺序排列的最高或者最低值
SQL MIN()实例
我们拥有下面这个 "Orders" 表:
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 |
SELECT MIN (OrderPrice)As smallestOrderPrice FROM Orders
结果类似为
smallestOrderPrice
300
我们拥有下面这个 "Orders" 表:
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 |
用于结合合计函数,根据一个或者多个列对结果集进行分组
语法
SELECT column_name,aggreate_function(column_name) From table_name where column_name operator value ground by column_name
实例
我们拥有下面这个 "Orders" 表:
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 |
我们希望查找每个客户的总金额
则 SELECT customer,SUM(OrderPrice)From orders group by customer
结果集为
customer SUM(OrderPrice)
Bush 2000
Carter 1700
Adams 2000
如果省略 group by 得到的结果
SELECT customer,SUM(OrderPrice)From orders
Customer | SUM(OrderPrice) |
---|---|
Bush | 5700 |
Carter | 5700 |
Bush | 5700 |
Bush | 5700 |
Adams | 5700 |
Carter | 5700 |
那么为什么不能使用上面这条 SELECT 语句呢?解释如下:上面的 SELECT 语句指定了两列(Customer 和 SUM(OrderPrice))。"SUM(OrderPrice)" 返回一个单独的值("OrderPrice" 列的总计),而 "Customer" 返回 6 个值(每个值对应 "Orders" 表中的每一行)。因此,我们得不到正确的结果。不过,您已经看到了,GROUP BY 语句解决了这个问题。
Group by一个以上实例
SELECT Customer,OrderDate,SUM(OrderPrice)From Orders group by customer,OrderDate;
10.SQL HAVING 语句
在SQL中增加该语句的原因是,where关键字无法与合计函数一块使用
语法
SELECT column_name,aggregate_function(column_name)From table_name where column_name operator value group by column_name HAVING aggregate_function(column_name) operator value
实例
我们拥有下面这个 "Orders" 表:
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 |
现在我们希望查找订单总金额少于2000的客户
则 SELECT Customer ,SUM(OrderPrice)From Orders Group BY Customer HAVING SUM(OrderPrice)<2000
结果集类似为
Customer SUM(OrderPrice)
Carter 1700
我们希望查找客户 “Bush”和“Adams”拥有超过1500的订单总金额
我们在SQL语句中增加了一个普通的where字句
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
11. SQL UCASE()函数
把字段的值转换为大写
语法
SELECT UCASE(column_name)FROM table_name
实例
我们拥有下面这个 "Persons" 表:
Id | LastName | FirstName | Address | City |
---|---|---|---|---|
1 | Adams | John | Oxford Street | London |
2 | Bush | George | Fifth Avenue | New York |
3 | Carter | Thomas | Changan Street | Beijing |
现在我们希望选取LastName和firstName列的内容,然后把lastName列转换为大写
SELECT UCASE(LastName) as LastName,firstName FROM Persons
结果为
LastName FirstName
ADAMS john
BUSH George
CARTER Thomas
12. SQL LCASE()函数
把字段的值转换为大写
语法
SELECT LCASE(column_name)FROM table_name
实例
我们拥有下面这个 "Persons" 表:
Id | LastName | FirstName | Address | City |
---|---|---|---|---|
1 | Adams | John | Oxford Street | London |
2 | Bush | George | Fifth Avenue | New York |
3 | Carter | Thomas | Changan Street | Beijing |
现在我们希望选取LastName和firstName列的内容,然后把lastName列转换为大写
SELECT LCASE(LastName) as LastName,firstName FROM Persons
结果为
LastName FirstName
adams john
bush George
carter Thomas
我们拥有下面这个 "Persons" 表:
Id | LastName | FirstName | Address | City |
---|---|---|---|---|
1 | Adams | John | Oxford Street | London |
2 | Bush | George | Fifth Avenue | New York |
3 | Carter | Thomas | Changan Street | Beijing |
我们拥有下面这个 "Persons" 表:
Id | LastName | FirstName | Address | City |
---|---|---|---|---|
1 | Adams | John | Oxford Street | London |
2 | Bush | George | Fifth Avenue | New York |
3 | Carter | Thomas | Changan Street | Beijing |
我们拥有下面这个 "Products" 表:
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我们拥有下面这个 "Products" 表:
Prod_Id | ProductName | Unit | UnitPrice |
---|---|---|---|
1 | gold | 1000 g | 32.35 |
2 | silver | 1000 g | 11.56 |
3 | copper | 1000 g | 6.85 |
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 |
我们拥有下面这个 "Products" 表:
Prod_Id | ProductName | Unit | UnitPrice |
---|---|---|---|
1 | gold | 1000 g | 32.35 |
2 | silver | 1000 g | 11.56 |
3 | copper | 1000 g | 6.85 |
ProductName | UnitPrice | PerDate |
---|---|---|
gold | 32.35 | 12/29/2008 |
silver | 11.56 | 12/29/2008 |
copper | 6.85 | 12/29/2008 |
语句 | 语法 |
---|---|
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 |