学习SQL【7】-函数
学习.png
不仅SQL， 对所有的编程语言来说，函数都起着至关重要的作用。函数就像是编程语言的“道具箱”，每种编程语言都准备了非常多的函数。譬如，C语言推崇的就是用函数来实现模块化编程。
根据用途不同，SQL中的函数大致可以分为算术函数、字符串函数、日期函数、转换函数和聚合函数。
一：函数的种类
1：函数的定义
所谓函数，就是输入某一值得到相应输出结果的功能，输入值称为参数，输出值称为返回值。
2：函数的分类
函数大致可以分为以下几种：
-
算术函数（用来进行数值计算的函数）
-
字符串函数（用来进行字符串操作的函数）
-
日期函数（用来进行日期操作的函数）
-
转换函数（用来转换数据类型和值的函数）
-
聚合函数（用来进行数据聚合的函数）
SQL中函数的种类有超过200多个，但是常用的只有30~50个。
二：算术函数
算术函数是最基本的函数，也就是我们常用的四则运算：
-
+（加法）
-
-（减法）
-
*（乘法）
-
/（除法）
为了学习算术函数，我们首先得创建一种示例用表：
--创建SampleMath表
--DDL：创建表
CREATE TABLE SampleMath (
m NUMERIC(10, 3),
n INTEGER,
P INTEGER);
如上所示，m列的数据类型为NUMERIC，NUMERIC是大多数DBMS都支持的一种数据类型，通过NUMERIC（全体位数, 小数位数）的形式来指定数值的大小。
然后再插入数据：
--插入数据
BEGIN TRANSACTION;BEGIN
INSERT INTO SampleMath VALUES (500, 0, NULL);INSERT 0 1
INSERT INTO SampleMath VALUES (-180, 0, NULL);INSERT 0 1
INSERT INTO SampleMath VALUES (NULL, NULL, NULL);INSERT 0 1
INSERT INTO SampleMath VALUES (NULL, 7, 3);INSERT 0 1
INSERT INTO SampleMath VALUES (NULL, 5, 2);INSERT 0 1
INSERT INTO SampleMath VALUES (NULL, 4, NULL);INSERT 0 1
INSERT INTO SampleMath VALUES (8, NULL, 3);INSERT 0 1
INSERT INTO SampleMath VALUES (2.27, 1, NULL);INSERT 0 1
INSERT INTO SampleMath VALUES (5.555, 2, NULL);INSERT 0 1
INSERT INTO SampleMath VALUES (NULL, 1, NULL);INSERT 0 1
INSERT INTO SampleMath VALUES (8.76, NULL, NULL);INSERT 0 1
COMMIT;COMMIT
上面的代码是我在Windows的命令行窗口敲的，所以每插入一个数据回车就会弹出
确认一下创建的表的内容：
SELECT * FROM SampleMath;
执行结果：
m | n | p----------+---+---
500.000 | 0 |
-180.000 | 0 |
| |
| 7 | 3
| 5 | 2
| 4 |
8.000 | | 3
2.270 | 1 |
5.555 | 2 |
| 1 |
8.760 | |
(11 行记录)
ABS—-绝对值
ABS是计算绝对值的函数，使用方法直接看例子：
--计算数值的绝对值
SELECT m, ABS(m) AS abs_col
FROM SampleMath;
执行结果：
m | abs_col----------+---------
500.000 | 500.000
-180.000 | 180.000
|
|
|
|
8.000 | 8.000
2.270 | 2.270
5.555 | 5.555
|
8.760 | 8.760
(11 行记录)
如上所示，-180的绝对值就是去掉符号的180。还有，当ABS函数的参数为NULL时，结果也为NULL。
MOD—-求余
MOD是计算除法余数的函数，使用方法如下：
--计算除法（n / p）的余数
SELECT n, p, MOD(n, p) AS mod_col
FROM SampleMath;
执行结果：
n | p | mod_col---+---+---------
0 | |
0 | |
| |
7 | 3 | 1
5 | 2 | 1
4 | |
| 3 |
1 | |
2 | |
1 | |
| |
(11 行记录)
注释：
在SQL Server中使用特殊的运算符“%”来计算余数。
ROUND—-四舍五入
ROUND函数用来进行四舍五入操作。如果指定四舍五入的位数为1，那么就会对小数点第2位进行四舍五入。如果指定位数为2，那么就会对小数点第3位进行四舍五入操作。
--对m列的数值进行n列位数的四舍五入处理
SELECT m, n, ROUND(m, n) AS round_col
FROM SampleMath;
执行结果：
m | n | round_col----------+---+-----------
500.000 | 0 | 500
-180.000 | 0 | -180
| |
| 7 |
| 5 |
| 4 |
8.000 | |
2.270 | 1 | 2.3
5.555 | 2 | 5.56
| 1 |
8.760 | |
(11 行记录)
字符串函数
为了学习字符串函数，我们再来创建一张表：
--创建SampleStr表
--DDL:创建表
CREATE TABLE SampleStr
( str1 VARCHAR(40),
str2 VARCHAR(40),
str3 VARCHAR(40));
插入数据：
BEGIN TRANSACTION;BEGIN
INSERT INTO SampleStr VALUES ('opx', 'rt', NULL);INSERT 0 1
INSERT INTO SampleStr VALUES ('abc', 'def', NULL);INSERT 0 1
INSERT INTO SampleStr VALUES ('我', '是', '番茄酱');INSERT 0 1
INSERT INTO SampleStr VALUES ('aaa', NULL, NULL);INSERT 0 1
INSERT INTO SampleStr VALUES (NULL, 'xyz', NULL);INSERT 0 1
INSERT INTO SampleStr VALUES ('@!#$%', NULL, NULL);INSERT 0 1
INSERT INTO SampleStr VALUES ('ABC', NULL, NULL);INSERT 0 1
INSERT INTO SampleStr VALUES ('aBC', NULL, NULL);INSERT 0 1
INSERT INTO SampleStr VALUES ('abc番茄酱', 'abc', 'ABC');INSERT 0 1
INSERT INTO SampleStr VALUES ('abcdefabc', 'abc', 'ABC');INSERT 0 1
INSERT INTO SampleStr VALUES ('micmic', 'i', 'I');INSERT 0 1
COMMIT;COMMIT
确认创建的表的内容：
SELECT * FROM SampleStr;
执行结果：
str1 | str2 | str3-----------+------+--------
opx | rt |
abc | def |
我 | 是 | 番茄酱
aaa | |
| xyz |
@!#$% | |
ABC | |
aBC | |
abc番茄酱 | abc | ABC
abcdefabc | abc | ABC
micmic | i | I
(11 行记录)
||—-拼接
在SQL中可使用两条并列的竖线“||”来实现字符串的拼接
--拼接两个字符串
SELECT str1, str2, str1 || str2 AS str_concat
FROM SampleStr;
执行结果：
str1 | str2 | str_concat-----------+------+--------------
opx | rt | opxrt
abc | def | abcdef
我 | 是 | 我是
aaa | |
| xyz |
@!#$% | |
ABC | |
aBC | |
abc番茄酱 | abc | abc番茄酱abc
abcdefabc | abc | abcdefabcabc
micmic | i | micmici
(11 行记录)
进行字符串拼接时，如果其中包含NULL，那么得到的结果也是NULL。当然，也可以进行三个字符串的拼接：
--拼接三个字符串
SELECT str1, str2, str3, str1 || str2 || str3 AS str_concat
FROM SampleStr
WHERE str1 = '我';
执行结果：
str1 | str2 | str3 | str_concat------+------+--------+------------
我 | 是 | 番茄酱 | 我是番茄酱
(1 行记录)
LENGTH—-字符串长度
想要知道字符串包含多少个字符时，可以使用LENGTH函数
--计算字符串长度
SELECT str1, LENGTH(str1) AS len_str
FROM SampleStr;
执行结果：
str1 | len_str-----------+---------
opx | 3
abc | 3
我 | 1
aaa | 3
|
@!#$% | 5
ABC | 3
aBC | 3
abc番茄酱 | 6
abcdefabc | 9
micmic | 6
(11 行记录)
LOWER—-小写转换
LOWER函数只能针对英文字母使用，它会将参数中的字符串全部都转换为小写
--大写转换为小写
SELECT str1, LOWER(str1) AS low_str
FROM SampleStr
WHERE str1 IN ('ABC', 'aBC', 'abc', '番茄酱');
执行结果：
str1 | low_str------+---------
abc | abc
ABC | abc
aBC | abc
(3 行记录)
UPPER—-大写转换
LOWER函数只能针对英文字母使用，它会将参数中的字符串全部都转换为大写
--将小写转换为大写
SELECT str1, UPPER(str1) AS up_str
FROM SampleStr
WHERE str1 IN ('ABC', 'aBC', 'abc', '番茄酱');
执行结果：
str1 | up_str------+--------
abc | ABC
ABC | ABC
aBC | ABC
(3 行记录)
REPLACE—-字符串的替换
REPLACE函数的语法：
REPLACE(对象字符串, 替换前的字符串, 替换后的字符串)
使用REPLACE函数，可以将字符串的一部分替换为其他的字符串，咱们用一个例子看一下：
--替换字符串的一部分
SELECT str1, str2, str3, REPLACE(str1, str2, str3) AS rep_str
FROM SampleStr;
执行结果：
str1 | str2 | str3 | rep_str-----------+------+--------+-----------
opx | rt | |
abc | def | |
我 | 是 | 番茄酱 | 我
aaa | | |
| xyz | |
@!#$% | | |
ABC | | |
aBC | | |
abc番茄酱 | abc | ABC | ABC番茄酱
abcdefabc | abc | ABC | ABCdefABC
micmic | i | I | mIcmIc
(11 行记录)
SUBSTRING—-字符串的截取
SUBSTRING函数的语法：
SUBSTRING(对象字符串 FROM 截取的起始位置 FOR 截取的字符数)
使用SUBSTRING函数可以截取字符串中的一部分字符串。截取的起始位置从字符串最左侧开始计算。
--截取字符串中第3位和第4位的字符
SELECT str1, SUBSTRING(str1 FROM 3 FOR 2) AS sub_str
FROM SampleStr;
执行结果：
str1 | sub_str-----------+---------
opx | x
abc | c
我 |
aaa | a
|
@!#$% | #$
ABC | C
aBC | C
abc番茄酱 | c番
abcdefabc | cd
micmic | cm
(11 行记录)
日期函数
CURRENT_DATE—-当前日期
CURRENT_DATE函数能够返回SQL所执行时的日期，由于没有参数，所以无需使用括号：
--获得当前日期
SELECT CURRENT_DATE;
执行结果：
date------------
2017-12-07
(1 行记录)
CURRENT_TIME—-当前时间
CURRENT_DATE函数能够返回SQL所执行时的时间，由于没有参数，所以也无需使用括号：
--取得当前时间
SELECT CURRENT_TIME;
执行结果：
timetz--------------------
20:46:11.321481+08
(1 行记录)
CURRENT_TIMESTAMP—-当前时间和日期
CURRENT_TIMESTAMP函数具有CURRENT_DATE+CURRENT_TIME的功能。使用该函数可以同时获得当前的日期和时间：
--取得当前日期和时间
SELECT CURRENT_TIMESTAMP;
执行结果：
now-------------------------------
2017-12-07 20:46:58.813816+08
(1 行记录)
EXTRACT—-截取日期元素
EXTRACT函数的语法：
EXTRACT(日期元素 FROM 日期)
使用EXTRACT函数可以截取日期数据中的一部分。该函数的返回值并不是日期类型而是数值类型：
--截取日期元素
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-------------------------------+------+-------+-----+------+--------+----------
2017-12-07 20:51:00.177839+08 | 2017 | 12 | 7 | 20 | 51 | 0.177839
(1 行记录)
转换函数
转换函数在SQL中主要有两种：一种是数据类型的转换，简称类型转换。另一种是值的转换。
CAST—-类型转换
CAST函数的语法：
CAST(转换前的值 AS 想要转换的数据类型)
进行数据类型转换可以避免一些由于表中数据类型不匹配或者数据类型不一致而引发的错误。
--将字符串类型转换为数值类型
SELECT CAST('0001' AS INTEGER) AS int_col;
执行结果：
int_col---------
1
(1 行记录)
将字符串类型转换为日期类型
SELECT CAST('2017-12-07' AS DATE) AS date_col;
执行结果：
date_col------------
2017-12-07
(1 行记录)
COALESCE—-将NULL转换为其他值
COALESCE函数的语法：
COALESCE(数据1, 数据2, 数据3,...)
COALESCE是SQL特有的函数。该函数会返回可变参数中左侧开始第一个不是NULL的值。参数个数是可变的，因此可以根据需要而无限增加。
--将NULL转换为其他值
SELECT COALESCE(NULL, 1) AS col_1,
COALESCE(NULL, 'test', 'NULL') AS col_2,
COALESCE(NULL, NULL, '2017-12-07') AS col_3;
执行结果：
col_1 | col_2 | col_3-------+-------+------------
1 | test | 2017-12-07
(1 行记录)
使用SampleStr表中的列作为例子
SELECT COALESCE(str2, 'NULL') FROM SampleStr;
执行结果：
coalesce----------
rt
def
是
NULL
xyz
NULL
NULL
NULL
abc
abc
i
(11 行记录)
如上所示，将str2列中NULL（空）转换为了字符串‘NULL’。
每天学习一点点，每天进步一点点。
