SQL 中常用Function总结二

时间:2022-12-09 21:12:06

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;

结果:

SQL 中常用Function总结二

注:在MSSQL中 执行SELECT Customer,SUM(OrderPrice) FROM Orders语句会报错,这是因为:在MSSQL中,当SELECT 后 既有 表结构本身的字段,又有需要使用聚合函数(COUNT(),SUM(),MAX(),MIN(),AVG()等)的字段,就要用到group by分组,查询的限定条件里有需要用聚合函数计算的字段时也需要用分组。但是在MySQL中执行SELECT Customer,SUM(OrderPrice) FROM Orders时,并不会报错,只是显示的结果如下:

SQL 中常用Function总结二

这里可以看出,通过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;

结果:

SQL 中常用Function总结二

例子 2:现在查找客户 "Bush" 或 "Adams" 拥有超过 1500 的订单总金额。

  SELECT Customer,SUM(OrderPrice) FROM Orders WHERE Customer='Bush' OR Customer='Adams' GROUP BY Customer HAVING SUM(OrderPrice)>1500;

结果:

SQL 中常用Function总结二

例子 3:现在查找订单数量大于1的用户。

  SELECT Customer FROM Orders GROUP BY Customer HAVING COUNT(O_id)>1;

结果:

SQL 中常用Function总结二

三、SQL中的UCASE函数

定义:UCASE 函数把字段的值转换为大写。

UCASE语法:

  SELECT UCASE(column_name) FROM table_name

注:MSSQL不支持UCASE,取代的函数名为UPPER,用法一样。

举例说明:

例子 1:现在查询订单表的所有客户并将其名字大写化。

  SELECT distinct UCASE(Customer) FROM Orders;

结果:

SQL 中常用Function总结二

四、SQL中的LCASE函数

定义:LCASE 函数把字段的值转换为小写。

LCASE语法:

  SELECT LCASE(column_name) FROM table_name

注:MSSQL不支持LCASE,取代的函数名为LOWER,用法一样

举例说明:

例子 1:现在查询订单表的所有客户并将其名字小写化。

  SELECT distinct LCASE(Customer) FROM Orders;

结果:

SQL 中常用Function总结二

五、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 中常用Function总结二

六、SQL中的LEN函数

定义:LEN 函数返回文本字段中值的长度。

LEN语法:

  SELECT LEN(column_name) FROM table_name

注:MySql不支持LEN,取代的函数名为LENGTH,用法一样

举例说明:

例子 1:取得 "Customer" 列中值的长度。

  SELECT distinct LENGTH(Customer) FROM Orders;

结果:

SQL 中常用Function总结二

七、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 中常用Function总结二

八、SQL中的Now函数

定义:NOW 函数返回当前的日期和时间。

NOW语法:

  SELECT NOW() FROM table_name

注:使用 MSServer 数据库,要使用 getdate() 函数来获得当前的日期时间。

举例说明:

例子 1:显示当天的日期所对应的和价格。

  SELECT ProductName, UnitPrice, Now() as PerDate FROM Products

结果:

SQL 中常用Function总结二

九、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格式化日期的文章。

结果:

SQL 中常用Function总结二

将'%Y-%M-%D'换成'%y-%m-%d'后的效果

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

结果:

SQL 中常用Function总结二

例子2:使用format来格式化UnitPrice,结果保留一位小数。

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

SQL 中常用Function总结二

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