龙空技术网

数据库进阶技能-SQL常用函数

IT小白痴 368

前言:

如今咱们对“sql可变长字符型”大约比较珍视,我们都想要剖析一些“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_colSFROM 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(';, '.', 2);+------------------------------------------+| SUBSTRING_INDEX(';, '.', 2) |+------------------------------------------+|                                 |+------------------------------------------+1 row in set (0.00 sec)SELECT SUBSTRING_INDEX(';, '.', -2);+-------------------------------------------+| SUBSTRING_INDEX(';, '.', -2) |+-------------------------------------------+| mysql.com                                 |+-------------------------------------------+1 row in set (0.00 sec)

获取第1个元素比较容易,获取第2个元素/第n个元素可以采用二次拆分的方法。

SELECT SUBSTRING_INDEX(';, '.', 1);+------------------------------------------+| SUBSTRING_INDEX(';, '.', 1) |+------------------------------------------+| www                                      |+------------------------------------------+1 row in set (0.00 sec)SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(';, '.', 2), '.', -1);+--------------------------------------------------------------------+| SUBSTRING_INDEX(SUBSTRING_INDEX(';, '.', 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)

标签: #sql可变长字符型 #sql 分位数函数 #mysql除法运算保留两位小数 #sqlserver四舍五入两位小数 #sql保留两位小数函数