SQL基础教程（第2版）第6章 函数、谓词、CASE表达式：6-1 函数

6-1　各种各样的函数

● 根据用途，函数可以大致分为算术函数、字符串函数、日期函数、转换函数和聚合函数。
● 函数的种类很多，无需全都记住，只需要记住具有代表性的函数就可以了，其他的可以在使用时再进行查询。

---

 

■ 函数的种类

所谓函数，就是输入某一值得到相应输出结果的功能，输入值称为参数（parameter），输出值称为返回值。
虽然数量众多，但常用函数只有 30 ~ 50 个。参考文档是 DBMS 手册的一部分。

■ 算术函数

算术函数是最基本的函数，其实之前我们已经学习过了。没错，就是 2-2 节介绍的加减乘除四则运算。

为了学习算术函数，我们首先根据代码清单 6-1 创建一张示例用表（SampleMath）。
由于 PostgreSQL 中的 ROUND函数只能使用 NUMERIC 类型的数据，因此我们在示例中也使用了该数据类型。

List6_1_MySQL

--Mysql
--DDL：创建表
CREATE TABLE SampleMath
(m  NUMERIC (10,3),
 n  INTEGER,
 p  INTEGER);

--DML：插入数据
START TRANSACTION;

INSERT INTO SampleMath(m, n, p) VALUES (500,  0,    NULL);
INSERT INTO SampleMath(m, n, p) VALUES (-180, 0,    NULL);
INSERT INTO SampleMath(m, n, p) VALUES (NULL, NULL, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (NULL, 7,    3);
INSERT INTO SampleMath(m, n, p) VALUES (NULL, 5,    2);
INSERT INTO SampleMath(m, n, p) VALUES (NULL, 4,    NULL);
INSERT INTO SampleMath(m, n, p) VALUES (8,    NULL, 3);
INSERT INTO SampleMath(m, n, p) VALUES (2.27, 1,    NULL);
INSERT INTO SampleMath(m, n, p) VALUES (5.555,2,    NULL);
INSERT INTO SampleMath(m, n, p) VALUES (NULL, 1,    NULL);
INSERT INTO SampleMath(m, n, p) VALUES (8.76, NULL, NULL);

COMMIT;


--确认表中的内容
SELECT * FROM SampleMath;

■ ABS——绝对值

ABS 是计算绝对值的函数。绝对值（absolute value）不考虑数值的符号，表示一个数到原点的距离。 

■ MOD——求余

这里有一点需要大家注意：主流的 DBMS 都支持 MOD 函数， 只有SQL Server 不支持该函数。
SQL Server使用特殊的运算符（函数）“%”来计算余数。

■ ROUND——四舍五入

---

■ 字符串函数

在日常生活中，我们经常会像使用数字那样，对字符串进行替换、截取、简化等操作，因此 SQL 也为我们提供了很多操作字符串的功能。 
为了学习字符串函数，我们再来创建一张表（SampleStr），参见代码清单 6-5。

--MySQL
--DDL：创建表
CREATE TABLE SampleStr
(str1  VARCHAR(40),
 str2  VARCHAR(40),
 str3  VARCHAR(40));

--DML：插入数据
START TRANSACTION;

INSERT INTO SampleStr (str1, str2, str3) VALUES ('opx',            'rt'    ,    NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('abc'    ,    'def'    ,    NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('山田'    ,    '太郎'  ,    '是我');
INSERT INTO SampleStr (str1, str2, str3) VALUES ('aaa'    ,    NULL    ,    NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES (NULL    ,    'xyz',            NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('@!#$%',    NULL    ,    NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('ABC'    ,    NULL    ,    NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('aBC'    ,    NULL    ,    NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('abc太郎',    'abc'    ,    'ABC');
INSERT INTO SampleStr (str1, str2, str3) VALUES ('abcdefabc',   'abc'    ,    'ABC');
INSERT INTO SampleStr (str1, str2, str3) VALUES ('micmic',          'i',        'I');

COMMIT;


--确认表中的内容
SELECT * FROM SampleStr;

■ ||——拼接

 

|| 函数在 SQL Server 和 MySQL 中无法使用。 

■ LENGTH——字符串长度

■ LOWER()——小写转换
■ UPPER()——大写转换

■ REPLACE——字符串的替换

■ SUBSTRING——字符串的截取

首字符的序号为1

---

■日期函数

虽然 SQL 中有很多日期函数，但是其中大部分都依存于各自的 DBMS，
因此无法统一说明 。本节将会介绍那些被标准 SQL 承认的可以应用于
绝大多数 DBMS 的函数。
■ CURRENT_DATE——当前日期

CURRENT_DATE 函数能够返回 SQL 执行的日期，也就是该函数执行时的日期。由于没有参数，因此无需使用括号。 

■ CURRENT_TIME——当前时间

■ CURRENT_TIMESTAMP——当前日期和时间

■ EXTRACT——截取日期元素

--PostgreSQL, MySQL
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;

--Oracle
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
FROM DUAL;

--SQL Server
SELECT CURRENT_TIMESTAMP,
       DATEPART(YEAR   , CURRENT_TIMESTAMP) AS year,
       DATEPART(MONTH  , CURRENT_TIMESTAMP) AS month,
       DATEPART(DAY    , CURRENT_TIMESTAMP) AS day,
       DATEPART(HOUR   , CURRENT_TIMESTAMP) AS hour,
       DATEPART(MINUTE , CURRENT_TIMESTAMP) AS minute,
       DATEPART(SECOND , CURRENT_TIMESTAMP) AS second;

--DB2
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
  FROM SYSIBM.SYSDUMMY1;

■ 转换函数

■ CAST——类型转换

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

COALESCE 是 SQL 特有的函数。该函数会返回可变参数中左侧开始第 1个不是 NULL 的值。

此外，多数 DBMS 中都提供了特有的 COALESCE 的简化版函数（如Oracle 中的 NVL 等）。推荐大家使用通用的 COALESCE 函数。