SQL系统学习笔记之函数

函数

算术函数（用来进行数值计算的函数）

• ABS——绝对值
• MOD——求余

  SQL Server 不支持该函数：使用% 取余

	  SELECT n, p,
 	  n % p AS mod_col
 	  FROM SampleMath;

• ROUND——四舍五入

  ROUND(对象数值，保留小数的位数)

字符串函数（用来进行字符串操作的函数）

• ||——拼接

  • 如果其中包含 NULL，那么得到的结果也是
    NULL。
  • 可以拼接多个字符串
  • || 函数在 SQL Server 和 MySQL 中无法使用。（SQL Server 使用“+”运算符、MySQL 使用 CONCAT 函数）

SELECT str1, str2,
 str1 | | str2 AS str_concat
 FROM SampleStr;

• LENGTH——字符串长度

  • MySQL 中是是以字节来计算长度的；不同 DBMS 计算标准不一样
  • 无法在 SQL Server 中使用
  • SQL Server 使用 LEN 函数
  • MySQL 中还存在计算字符串长度的自有函数 CHAR_LENGTH。

• LOWER——小写转换

• UPPER ——大写转换

• REPLACE——字符串的替换

  REPLACE(对象字符串，替换前的字符串，替换后的字符串)

SELECT str1, str2, str3,
 REPLACE(str1, str2, str3) AS rep_str  -- REPLACE(abc123,abd,ABC)=>ABC123
 FROM SampleStr;

• SUBSTRING——字符串的截取

  • SUBSTRING（对象字符串 FROM 截取的起始位置 FOR 截取的字符数）
  • 只有 PostgreSQL 和 MySQL 支持该语法
  • SUBSTRING(对象字符串，截取的起始位置，截取的字符数):SQL Server 专用语法
  • SUBSTR(对象字符串，截取的起始位置，截取的字符数):Oracle/DB2 专用语法

SELECT str1,
 SUBSTRING(str1 FROM 3 FOR 2) AS sub_str
 FROM SampleStr;

日期函数（用来进行日期操作的函数）

• CURRENT_DATE——当前日期

  • 无法在 SQL Server 中执行
  • SQL Server 使用 CURRENTTIMESTAMP 函数

-- 　获得当前日期
SELECT CURRENT_DATE; -- 2016-05-25

-- SQL Server使用 使用CAST（后述）函数将CURRENT_TIMESTAMP转换为日期类型
SELECT CAST(CURRENT_TIMESTAMP AS DATE) AS CUR_DATE;

• CURRENT_TIME——当前时间

SELECT CURRENT_TIME;  -- 17:26:50.995+09

无法在 SQL Server 中执行

• CURRENT_TIMESTAMP——当前日期和时间

  在 Oracle 和 DB2 中该函数的语法略有不同

 2016-04-25 18:31:03.704+09

• EXTRACT——截取日期元素

  • EXTRACT(日期元素 FROM 日期)
  • 的返回值并不是日期类型而是数值类型
  • SQL Server 也无法使用该函数:使用的是的 DATEPART 函数(DATEPART(YEAR , CURRENT_TIMESTAMP) AS year)

SELECT CURRENT_TIMESTAMP,
 EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS year,
 EXTRACT(MONTH FROM CURRENT_TIMESTAMP) AS month,
 EXTRACT(DAY FROM CURRENT_TIMESTAMP) AS day,
 EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS hour,
 EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS minute,
 EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS second;

 -- 执行结果
now | year | month | day | hour | minute | second
--------------------------+------+-------+-----+------+-------+-------
 2010-04-25 19:07:33.987+09 | 2010 | 4 | 25 | 19 | 7 | 33.987

转换函数（用来转换数据类型和值的函数）

• CAST——类型转换

  • CAST（转换前的值 AS 想要转换的数据类型）

  SELECT CAST('0001' AS INTEGER) AS int_col;-- 字符串转数值
  SELECT CAST('2009-12-14' AS DATE) AS date_col;-- 字符串转日期
  
  

• COALESCE——将 NULL 转换为其他值

  • COALESCE(数据 1，数据 2，数据 3……):该函数会返回可变参数中左侧开始第 1 个不是 NULL 的值
  • 运算或者函数中含有 NULL 时，结果全都会变为 NULL。能够避免这种结果的函数就是 COALESCE

SELECT COALESCE(NULL, 1) AS col_1,
 COALESCE(NULL, 'test', NULL) AS col_2,
 COALESCE(NULL, NULL, '2009-11-01') AS col_3;

 -- 结果
 col_1 | col_2 | col_3
-------+-------+-----------
 1 | test | 2009-11-01

聚合函数（用来进行数据聚合的函数）

见SQL系统学习笔记之数据库、表创建与基础查询

谓词

• 定义：返回值是真值,谓词的返回值全都是真值（TRUE/FALSE/UNKNOWN）。这也是谓词和函数的最大区别
• 见谓词：LIKE\ BETWEEN \IS NULL\IS NOT NULL\ IN \ EXISTS

like

• 进行字符串的部分一致查询
• (查找对象是 ddd)前方一致：选取出“dddabc”;中间一致：选取出“abcddd”“dddabc”“abdddc”;后方一致：选取出“abcddd”

-- 前方一致查询
SELECT *
 FROM SampleLike
 WHERE strcol LIKE 'ddd%'-- %代表“0 字符以上的任意字符串”

-- 中间一致查询
SELECT *
 FROM SampleLike
 WHERE strcol LIKE '%ddd%';


-- 后间一致查询
SELECT *
 FROM SampleLike
 WHERE strcol LIKE '%ddd';


-- 模糊查询还可以使用：_
-- _（下划线）来代替 %，与 % 不同的是，它代表了“任意 1 个字符”
-- 表示查询以abc开头的任意5位字符
SELECT *
 FROM SampleLike
 WHERE strcol LIKE 'abc_ _';

BETWEEN

左右包含

SELECT product_name, sale_price
 FROM Product
 WHERE sale_price BETWEEN 100 AND 1000;

EXIST

• “判断是否存在满足某种条件的记录”
• EXIST 通常都会使用关联子查询作为参数
• 作为 EXIST 参数的子查询中经常会使用 SELECT *。

-- 选取出“shoop_id=000c的在售商品的销售单价”
SELECT product_name, sale_price
 FROM Product AS P
 WHERE EXISTS (SELECT *
 FROM ShopProduct AS SP
 WHERE SP.shop_id = '000C'
 AND SP.product_id = P.product_id);

CASE 表达式

• CASE 表达式的语法分为简单 CASE 表达式和搜索 CASE 表达式两种;(因为搜索 case 表达式包含了简单 case 表达式的功能，所以一般使用搜索 case 表达式)

搜索 case 表达式

CASE WHEN <求值表达式> THEN <表达式>
 WHEN <求值表达式> THEN <表达式>
 WHEN <求值表达式> THEN <表达式>
 . . .
 ELSE <表达式>
END

SELECT product_name,
 CASE WHEN product_type = '衣服'
 THEN 'A ：' | | product_type
 WHEN product_type = '办公用品'
 THEN 'B ：' | | product_type
 WHEN product_type = '厨房用具'
 THEN 'C ：' | | product_type
 ELSE NULL
 END AS abc_product_type
 FROM Product;

-- 结果
product_name | abc_product_type
---------------+------------------
T恤衫 | A ：衣服
打孔器 | B ：办公用品
运动T恤 | A ：衣服
菜刀 | C ：厨房用具
高压锅 | C ：厨房用具
叉子 | C ：厨房用具
擦菜板 | C ：厨房用具
圆珠笔 | B ：办公用品

case 进行行列的转换

-- 对按照商品种类计算出的销售单价合计值进行行列转换
SELECT SUM(CASE WHEN product_type = '衣服'
 THEN sale_price ELSE 0 END) AS sum_price_clothes,
 SUM(CASE WHEN product_type = '厨房用具'
 THEN sale_price ELSE 0 END) AS sum_price_kitchen,
 SUM(CASE WHEN product_type = '办公用品'
 THEN sale_price ELSE 0 END) AS sum_price_office
 FROM Product;

简单 case 表达式

CASE <表达式>
 WHEN <表达式> THEN <表达式>
 WHEN <表达式> THEN <表达式>
 WHEN <表达式> THEN <表达式>
 . . .
 ELSE <表达式>
END

简单 case 表达式和搜索 case 表达式的区别

-- 使用搜索CASE表达式的情况
SELECT product_name,
 CASE WHEN product_type = '衣服'
 THEN 'A ：' | |product_type
WHEN product_type = '办公用品'
 THEN 'B ：' | |product_type
WHEN product_type = '厨房用具'
 THEN 'C ：' | |product_type
 ELSE NULL
 END AS abc_product_type
 FROM Product;
-- 使用简单CASE表达式的情况
SELECT product_name,
 CASE product_type --将类型写在case后,只能比较一列
 WHEN '衣服' THEN 'A ：' | | product_type
 WHEN '办公用品' THEN 'B ：' | | product_type
 WHEN '厨房用具' THEN 'C ：' | | product_type
 ELSE NULL
 END AS abc_product_type
 FROM Product;

Oracle 中的 DECODE、MySQL 中的 IF 实现 case 的分支功能

-- Oracle中使用DECODE代替CASE表达式
SELECT product_name,
 DECODE(product_type,
 '衣服', 'A ：' | | product_type,
 '办公用品', 'B ：' | | product_type,
 '厨房用具', 'C ：' | | product_type,
 NULL) AS abc_product_type
 FROM Product;

-- MySQL中使用IF代替CASE表达式
SELECT product_name,
 IF( IF( IF(product_type = '衣服',
 CONCAT('A ：', product_type), NULL)
 IS NULL AND product_type = '办公用品',
 CONCAT('B ：', product_type),
 IF(product_type = '衣服',
 CONCAT('A ：', product_type), NULL))
 IS NULL AND product_type = '厨房用具',
 CONCAT('C ：', product_type),
 IF( IF(product_type = '衣服',
 CONCAT('A：', product_type), NULL)
 IS NULL AND product_type = '办公用品',
 CONCAT('B ：', product_type),
 IF(product_type = '衣服',
 CONCAT('A ：', product_type),
 NULL))) AS abc_product_type
 FROM Product;