百度360必应搜狗淘宝本站头条
mysql distinctjenkins官网mysql 子查询vba splitpython list sort
当前位置：网站首页 > IT技术 > 正文

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

wptr33 2025-03-01 15:43 6 浏览

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


?

相关推荐

数据库基础:mysql主从集群搭建

文章首发于微信公众号：java架构师进阶之路前言：Mysql数据库没有增量备份的机制，当数据量太大的时候备份是一个很大的问题。还好mysql数据库提供了一种主从备份的机制，其实就是把主数据库的所有的...

Mysql-cluster搭建

前期准备准备五台虚拟机：ip地址分别为：192.168.1.211管理节点192.168.1.64SQL节点192.168.1.65SQL节点192.168.1.70数据节点192.168.1...

mysql 主从数据库搭建

一、创建目录在dev/htb下面创建文件夹master01htb]#mkdirmysql/master01-p2)进入master01...

从零搭建高可用的 MySQL 主从复制架构(基于 Linux 实战指南)

背景在生产环境中，单点MySQL数据库容易成为性能瓶颈或单点故障源。搭建MySQL主从复制架构，可以实现读写分离、高可用，提升系统的整体稳定性与扩展性。...

「MySQL 8」MySQL 5.7都即将停只维护了，是时候学习一波MySQL 8了

MySQL8新特性选择MySQL8的背景：MySQL5.6已经停止版本更新了，对于MySQL5.7版本，其将于2023年10月31日停止支持。后续官方将不再进行后续的代码维护。另外，...

Mysql启动选项和配置文件

Mysql启动选项和配置文件Mysql启动方式下面的启动命令都需要依赖在Linux环境下配置的Mysql环境变量...

centos安装mysql操作手册

1.下载Mysql首先去Mysql官网下载安装包，网址https://dev.mysql.com/downloads/mysql/推荐大家下载Linux通用版本的，便于管理安装位置，也方便一台服务器...

MySQL安装

MySQL的安装过程因操作系统的不同而有所差异。以下是在几种常见操作系统上安装MySQL的基本步骤：Windows下载MySQL：访问MySQL官方网站下载页面：MySQLDownloads...

MySQL数据库安装教程

前言今天就带各位小伙伴学习数据库技术。数据库技术是Java开发中必不可少的一部分知识内容。也是非常重要的技术。...

MySQL学到什么程度?才有可以在简历上写精通

前言如今互联网行业用的最多就是MySQL，然而对于高级Web面试者，尤其对于寻找30k下工作的求职者，很多MySQL相关知识点基本都会涉及，如果面试中，你的相关知识答的模糊和不切要点，基...

一起免费考 MySQL OCP 认证啦

前言：在1995年，首个MySQL版本发布，为庆祝MySQL诞辰30周年，OracleUniversity在限定期间内推出了多个MySQL的免费培训课程与认证，其中也包括My...

教程2 | 制作用户管理系统

一、项目简介用户管理系统是一个基于C/S模式的小型管理系统，使用了GUI技术来实现管理系统的页面效果，该管理系统可以对用户的信息，比如姓名、年龄、密码和地址等进行增删改查操作。用户管理系统通过JDBC...

红帽Linux中安装mysql8详细步骤

注意：我写的解压路径和截图路径不一致，仅供参考先前往官网下载mysql8下载地址：https://dev.mysql.com/downloads/选择指定版本和系统下载命令...

MySQL主从配置

主从原理MySQL主从又叫做Replication、AB复制。简单讲就是A和B两台机器做主从后，在A上写数据，另外一台B也会跟着写数据，两者数据实时同步的。...

mysql的主从搭建以及实现主从切换方法

主从搭建的方法：a.准备两台服务器...

一周热门
最近发表
标签列表
Copyright Your WebSite.Some Rights Reserved.