SQL 中常用Function总结二

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() 函数返回剩余文本。

meta

举例说明：

例子 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,结果保留一位小数。

  SELECT ProductName, format(UnitPrice,1), DATE_FORMAT(Now(),'%y-%m-%d') as PerDate FROM Product;

下面是例子中涉及到表的建表语句和数据：
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);