sql数据库入门基础语句（数据库进阶技能-SQL常用函数）

一、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)

​

,