SQL 中常用Function总结二
一、SQL GROUP BY 语句
合计函数 (比如 SUM) 常常需要添加 GROUP BY 语句。
定义:GROUP BY 语句用于结合合计函数,根据一个或多个列对结果集进行分组。
GROUP BY 语法:
SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name
举例说明:
例子 1:查找每个客户的总金额(总订单)。使用 GROUP BY 语句对客户进行组合。
SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer;
结果:
注:在MSSQL中 执行SELECT Customer,SUM(OrderPrice) FROM Orders语句会报错,这是因为:在MSSQL中,当SELECT 后 既有 表结构本身的字段,又有需要使用聚合函数(COUNT(),SUM(),MAX(),MIN(),AVG()等)的字段,就要用到group by分组,查询的限定条件里有需要用聚合函数计算的字段时也需要用分组。但是在MySQL中执行SELECT Customer,SUM(OrderPrice) FROM Orders时,并不会报错,只是显示的结果如下:
这里可以看出,通过SUM(OrderPrice)计算后返回的结果是一条记录,故而最后只有一条记录。但是结果并不是我们想要的,所以切记要加上GROUP BY。另外顺便介绍
下,WHERE和HAVING的区别,WHERE作用于全表,而HAVING只作用于分组的组内。
二、SQL中的HAVING 子句
定义:在 SQL 中增加 HAVING 子句原因是,WHERE 关键字无法与合计函数一起使用。
HAVING 语法:
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
举例说明:
例子 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;
结果:
例子 3:现在查找订单数量大于1的用户。
SELECT Customer FROM Orders GROUP BY Customer HAVING COUNT(O_id)>1;
结果:
三、SQL中的UCASE函数
定义:UCASE 函数把字段的值转换为大写。
UCASE语法:
SELECT UCASE(column_name) FROM table_name
注:MSSQL不支持UCASE,取代的函数名为UPPER,用法一样。
举例说明:
例子 1:现在查询订单表的所有客户并将其名字大写化。
SELECT distinct UCASE(Customer) FROM Orders;
结果:
四、SQL中的LCASE函数
定义:LCASE 函数把字段的值转换为小写。
LCASE语法:
SELECT LCASE(column_name) FROM table_name
注:MSSQL不支持LCASE,取代的函数名为LOWER,用法一样
举例说明:
例子 1:现在查询订单表的所有客户并将其名字小写化。
SELECT distinct LCASE(Customer) FROM Orders;
结果:
五、SQL中的MID函数
定义:MID 函数用于从文本字段中提取字符。
MID语法:
SELECT MID(column_name,start[,length]) FROM table_name
注:MSSQL不支持MID,取代的函数名为Substring,用法一样
参数说明:
column_name | 必需。要提取字符的字段。 |
start | 必需。规定开始位置(起始值是 1)。 |
length | 可选。要返回的字符数。如果省略,则 MID() 函数返回剩余文本。 |
举例说明:
例子 1:从 "Customer" 列中提取前 3 个字符。
SELECT distinct MID(Customer,1,3) FROM Orders;
结果:
六、SQL中的LEN函数
定义:LEN 函数返回文本字段中值的长度。
LEN语法:
SELECT LEN(column_name) FROM table_name
注:MySql不支持LEN,取代的函数名为LENGTH,用法一样
例子 1:取得 "Customer" 列中值的长度。
SELECT distinct LENGTH(Customer) FROM Orders;
结果:
七、SQL中的ROUND函数
定义:ROUND 函数用于把数值字段舍入为指定的小数位数。
ROUND语法:
SELECT ROUND(column_name,decimals) FROM table_name
参数说明:
参数 | 描述 |
---|---|
column_name | 必需。要舍入的字段。 |
decimals | 必需。规定要返回的小数位数。 |
举例说明:
例子 1:把名称和价格舍入为最接近的整数。
SELECT ProductName, ROUND(UnitPrice,0) as UnitPrice FROM Product;
结果:
八、SQL中的Now函数
定义:NOW 函数返回当前的日期和时间。
NOW语法:
SELECT NOW() FROM table_name
注:使用 MSServer 数据库,要使用 getdate() 函数来获得当前的日期时间。
例子 1:显示当天的日期所对应的和价格。
SELECT ProductName, UnitPrice, Now() as PerDate FROM Products
结果:
九、SQL中的FORMAT函数
定义:FORMAT 函数用于对字段的显示进行格式化。
FORMAT语法:
SELECT FORMAT(column_name,format) FROM table_name
注:MSSQL中没有format函数,取代的是convert函数
参数说明:
参数 | 描述 |
---|---|
column_name | 必需。要格式化的字段。 |
format | 必需。规定格式。 |
举例说明:
例子 1:显示每天日期所对应的名称和价格(日期的显示格式是 "YYYY-MM-DD")。
SELECT ProductName, UnitPrice, DATE_FORMAT(Now(),'%Y-%M-%D') as PerDate FROM Product;
注:在Mysql中使用format来格式化日期类型有问题,需要使用另一个函数来格式化:date_format(), 同时格式化的格式也有区别,之后会专门写一个准对MySQL格式化日期的文章。
结果:
将'%Y-%M-%D'换成'%y-%m-%d'后的效果
SELECT ProductName, UnitPrice, DATE_FORMAT(Now(),'%y-%m-%d') as PerDate FROM Product;
例子2:使用format来格式化UnitPrice,结果保留一位小数。
下面是例子中涉及到表的建表语句和数据:
create table Orders(
O_Id int,
OrderDate date,
OrderPrice float,
Customer varchar(10)
);
insert into Orders values(1,'2008/12/29',1000,'Bush');
insert into Orders values(2,'2008/11/23',1600,'Carter');
insert into Orders values(3,'2008/10/05',700,'Bush');
insert into Orders values(4,'2008/09/28',300,'Bush');
insert into Orders values(5,'2008/08/06',2000,'Adams');
insert into Orders values(6,'2008/07/21',100,'Carter');
create table Product(
Prod_Id int,
ProductName varchar(20),
Unit varchar(20),
UnitPrice float
);
insert into Product values(1,'gold','1000 g',32.35);
insert into Product values(2,'silver','1000 g',11.56);
insert into Product values(3,'copper','1000 g',6.85);