一、SQL函数详解
sql自带了各种各样的函数,极大提高了sql语言的便利性。
所谓函数,类似一个黑盒子,你给它一个输入值,它便按照预设的程序定义给出返回值,输入值称为参数
。
函数大致分为如下几类:
- 算术函数 (用来进行数值计算的函数)
- 字符串函数 (用来进行字符串操作的函数)
- 日期函数 (用来进行日期操作的函数)
- 转换函数 (用来转换数据类型和值的函数)
- 聚合函数 (用来进行数据聚合的函数)
函数总个数超过200个,不需要完全记住,常用函数有 30~50 个,其他不常用的函数使用时查阅文档即可
1.1 算数函数
+ - * /
四则运算
为了演示其他的几个算数函数,在此构造samplemath
表
-- DDL :创建表
USE shop;
DROP TABLE IF EXISTS samplemath;
CREATE TABLE samplemath
(m float(10,3),
n INT,
p INT);
-- 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;
+----------+------+------+
| m | n | p |
+----------+------+------+
| 500.000 | 0 | NULL |
| -180.000 | 0 | NULL |
| NULL | NULL | NULL |
| NULL | 7 | 3 |
| NULL | 5 | 2 |
| NULL | 4 | NULL |
| 8.000 | NULL | 3 |
| 2.270 | 1 | NULL |
| 5.555 | 2 | NULL |
| NULL | 1 | NULL |
| 8.760 | NULL | NULL |
+----------+------+------+
11 rows in set (0.00 sec)
- ABS – 绝对值
语法:ABS( 数值 )
ABS 函数用于计算一个数字的绝对值,表示一个数到原点的距离。
当 ABS 函数的参数为NULL
时,返回值也是NULL
。
- MOD – 求余数
语法:MOD( 被除数,除数 )
MOD 是计算除法余数(求余)的函数,是 modulo 的缩写。小数没有余数的概念,只能对整数列求余数。
注意:主流的 DBMS 都支持 MOD 函数,只有SQL Server 不支持该函数,其使用%
符号来计算余数。
- ROUND – 四舍五入
语法:ROUND( 对象数值,保留小数的位数 )
ROUND 函数用来进行四舍五入操作。
注意:当参数 保留小数的位数 为变量时,可能会遇到错误,请谨慎使用变量。
SELECT m,
ABS(m)ASabs_col ,
n, p,
MOD(n, p) AS mod_col,
ROUND(m,1)ASround_colS
FROM samplemath;
+----------+---------+------+------+---------+-----------+
| m | abs_col | n | p | mod_col | round_col |
+----------+---------+------+------+---------+-----------+
| 500.000 | 500.000 | 0 | NULL | NULL | 500.0 |
| -180.000 | 180.000 | 0 | NULL | NULL | -180.0 |
| NULL | NULL | NULL | NULL | NULL | NULL |
| NULL | NULL | 7 | 3 | 1 | NULL |
| NULL | NULL | 5 | 2 | 1 | NULL |
| NULL | NULL | 4 | NULL | NULL | NULL |
| 8.000 | 8.000 | NULL | 3 | NULL | 8.0 |
| 2.270 | 2.270 | 1 | NULL | NULL | 2.3 |
| 5.555 | 5.555 | 2 | NULL | NULL | 5.6 |
| NULL | NULL | 1 | NULL | NULL | NULL |
| 8.760 | 8.760 | NULL | NULL | NULL | 8.8 |
+----------+---------+------+------+---------+-----------+
11 rows in set (0.08 sec)
1.2 字符串函数
字符串函数也经常被使用,为了学习字符串函数,在此我们构造samplestr
表。
-- DDL :创建表
USE shop;
DROP TABLE IF EXISTS samplestr;
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;
+-----------+------+------+
| str1 | str2 | str3 |
+-----------+------+------+
| opx | rt | NULL |
| abc | def | NULL |
| 太阳 | 月亮 | 火星 |
| aaa | NULL | NULL |
| NULL | xyz | NULL |
| @!#$% | NULL | NULL |
| ABC | NULL | NULL |
| aBC | NULL | NULL |
| abc哈哈 | abc | ABC |
| abcdefabc | abc | ABC |
| micmic | i | I |
+-----------+------+------+
11 rows in set (0.00 sec)
- CONCAT – 拼接
语法:CONCAT(str1, str2, str3)
MySQL中使用 CONCAT 函数进行拼接。
- LENGTH – 字符串长度
语法:LENGTH( 字符串 )
- LOWER – 小写转换
LOWER 函数只能针对英文字母使用,它会将参数中的字符串全都转换为小写。该函数不适用于英文字母以外的场合,不影响原本就是小写的字符。
类似的, UPPER 函数用于大写转换。
- REPLACE – 字符串的替换
语法:REPLACE( 对象字符串,替换前的字符串,替换后的字符串 )
- SUBSTRING – 字符串的截取
语法:SUBSTRING (对象字符串 FROM 截取的起始位置 FOR 截取的字符数)
使用 SUBSTRING 函数 可以截取出字符串中的一部分字符串。截取的起始位置从字符串最左侧开始计算,索引值起始为1。
- (扩展内容)SUBSTRING_INDEX – 字符串按索引截取
语法:SUBSTRING_INDEX (原始字符串, 分隔符,n)
该函数用来获取原始字符串按照分隔符分割后,第 n 个分隔符之前(或之后)的子字符串,支持正向和反向索引,索引起始值分别为 1 和 -1.
SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
+------------------------------------------+
| SUBSTRING_INDEX('www.mysql.com', '.', 2) |
+------------------------------------------+
| www.mysql |
+------------------------------------------+
1 row in set (0.00 sec)
SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
+-------------------------------------------+
| SUBSTRING_INDEX('www.mysql.com', '.', -2) |
+-------------------------------------------+
| mysql.com |
+-------------------------------------------+
1 row in set (0.00 sec)
获取第1个元素比较容易,获取第2个元素/第n个元素可以采用二次拆分的写法。
SELECT SUBSTRING_INDEX('www.mysql.com', '.', 1);
+------------------------------------------+
| SUBSTRING_INDEX('www.mysql.com', '.', 1) |
+------------------------------------------+
| www |
+------------------------------------------+
1 row in set (0.00 sec)
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('www.mysql.com', '.', 2), '.', -1);
+--------------------------------------------------------------------+
| SUBSTRING_INDEX(SUBSTRING_INDEX('www.mysql.com', '.', 2), '.', -1) |
+--------------------------------------------------------------------+
| mysql |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)
1.3 日期函数
不同DBMS的日期函数语法各有不同,本课程介绍一些被标准 SQL 承认的可以应用于绝大多数 DBMS 的函数。特定DBMS的日期函数查阅文档即可。
- CURRENT_DATE – 获取当前日期
SELECT CURRENT_DATE;
+--------------+
| CURRENT_DATE |
+--------------+
| 2020-08-08 |
+--------------+
1 row in set (0.00 sec)
- CURRENT_TIME – 当前时间
SELECT CURRENT_TIMESTAMP;
+---------------------+
| CURRENT_TIMESTAMP |
+---------------------+
| 2020-08-08 17:27:07 |
+---------------------+
1 row in set (0.00 sec)
- EXTRACT – 截取日期元素
语法:EXTRACT(日期元素 FROM 日期)
使用 EXTRACT 函数可以截取出日期数据中的一部分,例如“年”
“月”,或者“小时”“秒”等。该函数的返回值并不是日期类型而是数值类型。
SELECT CURRENT_TIMESTAMP as now,
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 |
+---------------------+------+-------+------+------+--------+--------+
| 2020-08-08 17:34:38 | 2020 | 8 | 8 | 17 | 34 | 38 |
+---------------------+------+-------+------+------+--------+--------+
1 row in set (0.00 sec)
1.4 转换函数
“转换”这个词的含义非常广泛,在 SQL 中主要有两层意思:一是数据类型的转换,简称为类型转换,在英语中称为cast
;另一层意思是值的转换。
- CAST – 类型转换
语法:CAST(转换前的值 AS 想要转换的数据类型)
-- 将字符串类型转换为数值类型
SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;
+---------+
| int_col |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)
-- 将字符串类型转换为日期类型
SELECT CAST('2009-12-14' AS DATE) AS date_col;
+------------+
| date_col |
+------------+
| 2009-12-14 |
+------------+
1 row in set (0.00 sec)
- COALESCE – 将NULL转换为其他值
语法:COALESCE(数据1,数据2,数据3……)
COALESCE 是 SQL 特有的函数。该函数会返回可变参数 A 中左侧开始第 1个不是NULL的值。参数个数是可变的,因此可以根据需要无限增加。
在 SQL 语句中将 NULL 转换为其他值时就会用到转换函数。
SELECT COALESCE(NULL, 11) AS col_1,
COALESCE(NULL, 'hello world', NULL) AS col_2,
COALESCE(NULL, NULL, '2020-11-01') AS col_3;
+-------+-------------+------------+
| col_1 | col_2 | col_3 |
+-------+-------------+------------+
| 11 | hello world | 2020-11-01 |
+-------+-------------+------------+
1 row in set (0.00 sec)