数据库sql常用命令：数据库进阶技能-SQL常用函数
数据库sql常用命令：数据库进阶技能-SQL常用函数语法：MOD( 被除数，除数 )当 ABS 函数的参数为NULL时，返回值也是NULL。为了演示其他的几个算数函数，在此构造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 NU
一、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。
图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)
