SQL操作指南五（函数）

函数

  • 函数的种类
    函数大致分为以下几类：
    ①算术函数（用以进行数值计算）
    ②字符串函数（用以进行字符串操作）
    ③日期函数（用以进行日期操作）
    ④转换函数（用以转换数据类型和值）
    ⑤聚合函数（用以进行数据聚合）

  • 算术函数
    算术函数是最基本的函数主要是加减乘除四则运算。
    为了方便演示算术函数，我们先创建一张名称为SampleMath的表，如下（m NUMERIC(10,3)n INTEGER,p INTEGER)：

+-------+------+------+
| m     | n    | p    |
+-------+------+------+
| 500   |    0 | NULL |
| -180  |    0 | NULL |
| NULL  | NULL | NULL |
| NULL  |    7 |    3 |
| NULL  |    5 |    2 |
| NULL  |    4 | NULL |
| 8     | NULL |    3 |
| 2.27  |    1 | NULL |
| 5.555 |    2 | NULL |
| NULL  |    1 | NULL |
| 8.76  | NULL | NULL |
+-------+------+------+

ABS—绝对值
语法：ABS（数值）
示例：

SELECT m,ABS(m) AS abs_exp
FROM SampleMath;
+-------+-------+
| m     | abs_m |
+-------+-------+
| 500   | 500   |
| -180  | 180   |
| NULL  | NULL  |
| NULL  | NULL  |
| NULL  | NULL  |
| NULL  | NULL  |
| 8     | 8     |
| 2.27  | 2.27  |
| 5.555 | 5.555 |
| NULL  | NULL  |
| 8.76  | 8.76  |
+-------+-------+

MOD—求余
语法：MOD(被除数，除数)
示例：

SELECT n,p,MOD(n,p) AS mod_exp
FROM SampleMath;
+------+------+--------+
| n    | p    | mod_np |
+------+------+--------+
|    0 | NULL | NULL   |
|    0 | NULL | NULL   |
| NULL | NULL | NULL   |
|    7 |    3 |      1 |
|    5 |    2 |      1 |
|    4 | NULL | NULL   |
| NULL |    3 | NULL   |
|    1 | NULL | NULL   |
|    2 | NULL | NULL   |
|    1 | NULL | NULL   |
| NULL | NULL | NULL   |
+------+------+--------+

ROUND—四舍五入
语法：ROUND(对象数值，保留小数的位数）
示例：

SELECT m,n,ROUND(m,n) AS round_exp
FROM SampleMath;
+-------+------+-----------+
| m     | n    | round_exp |
+-------+------+-----------+
| 500   |    0 | 500.000   |
| -180  |    0 | -180.000  |
| NULL  | NULL | NULL      |
| NULL  |    7 | NULL      |
| NULL  |    5 | NULL      |
| NULL  |    4 | NULL      |
| 8     | NULL | NULL      |
| 2.27  |    1 | 2.300     |
| 5.555 |    2 | 5.560     |
| NULL  |    1 | NULL      |
| 8.76  | NULL | NULL      |
+-------+------+-----------+
  • 字符串函数
    为了方便演示字符串函数，我们创建一张名为SampleStr的表，如下（str1 VARCHAR(40),str2 VARCHAR(40),str3 VARCHAR(40)):
+-----------+--------+------+
| 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    |
+-----------+--------+------+

||—拼接
语法：字符串1||字符串2或者concat(字符串1，字符串2）
示例：

 SELECT str1,str2,concat(str1,str2) AS str_concat
 FROM SampleStr;
+-----------+--------+--------------+
| str1      | str2   | str_concat   |
+-----------+--------+--------------+
| opx       | rt     | opxrt        |
| abc       | def    | abcdef       |
| 雨伞      | 手电筒 | 雨伞手电筒   |
| aaa       | NULL   | NULL         |
| NULL      | xyz    | NULL         |
| #$@*!     | NULL   | NULL         |
| ABC       | NULL   | NULL         |
| aBC       | NULL   | NULL         |
| abc散记   | abc    | abc散记abc   |
| abcdefabc | abc    | abcdefabcabc |
| micmic    | i      | micmici      |
+-----------+--------+--------------+

需要注意的是：||的方法在SQL Server和MySQL中无法使用。

LENGTH—字符串长度
语法：LENGTH(字符串)
示例：

SELECT str1,LENGTH(str1) AS str_length
FROM SampleStr;
+-----------+------------+
| str1      | str_length |
+-----------+------------+
| opx       |          3 |
| abc       |          3 |
| 雨伞      |          6 |
| aaa       |          3 |
| NULL      | NULL       |
| #$@*!     |          5 |
| ABC       |          3 |
| aBC       |          3 |
| abc散记   |          9 |
| abcdefabc |          9 |
| micmic    |          6 |
+-----------+------------+

该函数无法在SQL Server中使用。

LOWER—小写转换
语法：LOWER(字符串)
示例：

SELECT str1,LOWER(str1)
FROM SampleStr;
+-----------+-------------+
| str1      | LOWER(str1) |
+-----------+-------------+
| opx       | opx         |
| abc       | abc         |
| 雨伞      | 雨伞        |
| aaa       | aaa         |
| NULL      | NULL        |
| #$@*!     | #$@*!       |
| ABC       | abc         |
| aBC       | abc         |
| abc散记   | abc散记     |
| abcdefabc | abcdefabc   |
| micmic    | micmic      |
+-----------+-------------+

UPPER—大写转换
语法：UPPER(字符串)
示例：

 SELECT str1,UPPER(str1) AS str_up
 FROM SampleStr;
+-----------+-----------+
| str1      | str_up    |
+-----------+-----------+
| opx       | OPX       |
| abc       | ABC       |
| 雨伞      | 雨伞      |
| aaa       | AAA       |
| NULL      | NULL      |
| #$@*!     | #$@*!     |
| ABC       | ABC       |
| aBC       | ABC       |
| abc散记   | ABC散记   |
| abcdefabc | ABCDEFABC |
| micmic    | MICMIC    |
+-----------+-----------+

REPLACE—字符串替换
语法：REPLACE(对象字符串，替换前的字符串，替换后的字符串)
使用REPLACE函数可以将对象字符串中拥有替换前字符串的片段替换成替换后的字符串的功能。
示例：

SELECT str1,str2,str3,REPLACE(str1,str2,str3) AS str_rep
FROM SampleStr;
+-----------+--------+------+-----------+
| str1      | str2   | str3 | str_rep   |
+-----------+--------+------+-----------+
| opx       | rt     | NULL | NULL      |
| abc       | def    | NULL | NULL      |
| 雨伞      | 手电筒 | 工具 | 雨伞      |
| aaa       | NULL   | NULL | NULL      |
| NULL      | xyz    | NULL | NULL      |
| #$@*!     | NULL   | NULL | NULL      |
| ABC       | NULL   | NULL | NULL      |
| aBC       | NULL   | NULL | NULL      |
| abc散记   | abc    | ABC  | ABC散记   |
| abcdefabc | abc    | ABC  | ABCdefABC |
| micmic    | i      | I    | mIcmIc    |
+-----------+--------+------+-----------+

SUBSTRING—字符串的截取
语法：SUBSTRING(对象字符串 FROM 截取的其实位置 FOR 截取的字符数)
使用SUBSTRING函数可以截取字符中的一部分字符串。截取的起始位置从字符串最左侧开始计算。
示例：

SELECT str1,SUBSTRING(str1 FROM 3 FOR 2) AS str_sub
FROM SampleStr;
+-----------+---------+
| str1      | str_sub |
+-----------+---------+
| opx       | x       |
| abc       | c       |
| 雨伞      |         |
| aaa       | a       |
| NULL      | NULL    |
| #$@*!     | @*      |
| ABC       | C       |
| aBC       | C       |
| abc散记   | c散     |
| abcdefabc | cd      |
| micmic    | cm      |
+-----------+---------+

只有PostgreSQL和MySQL支持以上用法。

  • 日期函数

CURRENT_DATE函数—当前日期
语法：CURRENT_DATE
CURRENT_DATE函数能够返回SQL执行的日期，也就是该函数执行的日期。由于没有参数，所以无序使用括号。
示例：

SELECT CURRENT_DATE;
+--------------+
| CURRENT_DATE |
+--------------+
| 2018-11-10   |
+--------------+

该函数无法在SQL Server中执行。此外，Oracle和DB2中也略有不同。

CURRENT_TIME—当前时间
语法：CURRENT_TIME
CURRENT_TIME函数能够返回SQL执行的时间，也就是该函数执行的时间。由于没有参数，所以无序使用括号。
示例：

SELECT CURRENT_TIME;
+--------------+
| CURRENT_TIME |
+--------------+
| 16:00:56     |
+--------------+

该函数同样无法在SQL Server中执行。此外，Oracle和DB2中也略有不同。

CURRENT_TIMESTAMP—当前日期和时间
语法：CURRENT_TIMESTAMP
CURRENT_TIMESTAMP同时具有CURRENT_DATE和CURRENT_TIME的功能，使用该函数可以同时得到当前的日期和时间，当然也可以从结果中截取日期和时间。
示例：

SELECT CURRENT_TIMESTAMP;
+---------------------+
| CURRENT_TIMESTAMP   |
+---------------------+
| 2018-11-10 16:05:19 |
+---------------------+

EXTRACT——截取日期元素
语法：EXTRACT(日期元素 FROM 日期)
使用EXTRACT函数可以截取日期数据中的一部分。该函数的返回值并不是日期类型而是数据类型。
示例：

SELECT CURRENT_TIMESTAMP,
    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;
+---------------------+------+-------+-----+------+--------+--------+
| CURRENT_TIMESTAMP   | year | month | day | hour | minute | second |
+---------------------+------+-------+-----+------+--------+--------+
| 2018-11-10 16:12:54 | 2018 |    11 |  10 |   16 |     12 |     54 |
+---------------------+------+-------+-----+------+--------+--------+

SQL Server无法使用该函数。

  • 转换函数
    转换函数在SQL中有两层意思，一是数据类型的转换，简称为”类型转换“，在英语中称为cast。另一层意思是值的转换。

CAST—类型转换
语法：CAST(转换前的值 AS 想要转换的数据类型)
示例（此处用MySQL举例）：

--将字符串转换成数值类型
SELECT CAST('0001' AS SIGNED INTEGER) AS int_exp;
+---------+
| int_exp |
+---------+
|       1 |
+---------+

SELECT CAST('2018-9-1' AS DATE) AS date_exp;
+------------+
| date_exp   |
+------------+
| 2018-09-01 |
+------------+

COALESCE—将NULL转换为其他值
语法：COALESCE(数据1，数据2，数据3······)
COALESCE是SQL特有的函数。该函数会返回可变参数中左侧开始第1个不是NULL的值。参数的个数是可变的，因此可以根据需求无限增加。
示例：

SELECT COALESCE(NULL,1) AS exp_1,
               COALESCE(NULL,'teat',NULL) AS exp_2,
               COALESCE(NULL,NULL,'2018-11-10') AS exp_3;
+-------+-------+------------+
| exp_1 | exp_2 | exp_3      |
+-------+-------+------------+
|     1 | teat  | 2018-11-10 |
+-------+-------+------------+

使用SampleStr表中的数据示例：

SELECT COALESCE(str2,'NULL')
FROM SampleStr;
+-----------------------+
| COALESCE(str2,'NULL') |
+-----------------------+
| rt                    |
| def                   |
| 手电筒                |
| NULL                  |
| xyz                   |
| NULL                  |
| NULL                  |
| NULL                  |
| abc                   |
| abc                   |
| i                     |
+-----------------------+
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 212,454评论 6赞 493
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 90,553评论 3赞 385
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 157,921评论 0赞 348
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 56,648评论 1赞 284
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 65,770评论 6赞 386
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 49,950评论 1赞 291
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,090评论 3赞 410
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 37,817评论 0赞 268
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 44,275评论 1赞 303
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,592评论 2赞 327
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,724评论 1赞 341
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 34,409评论 4赞 333
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 40,052评论 3赞 316
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,815评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,043评论 1赞 266
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 46,503评论 2赞 361
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 43,627评论 2赞 350

推荐阅读更多精彩内容

  • 关于Mongodb的全面总结
    关于Mongodb的全面总结 MongoDB的内部构造《MongoDB The Definitive Guide》...
    中v中阅读 31,913评论 2赞 89
  • 一张照片的故事
    那是一张我们在机场的照片，我与我的好哥们高飞的合照，也是我们最后一次见面的留念。 高飞与我是高中同学，他体质很弱，...
    aa1473a33dd5阅读 185评论 0赞 0
  • 变形计之目标管理19
    这两天工作压力很大，全力以赴的冲刺年底的《变形计》大课。 平时工作压力大，一般很少有脑袋可以思考问题，可这次工作压...
    虫儿蛹阅读 227评论 0赞 0
  • 思念，一个省略号
    金秋十月，秋风揽叶入怀 思念，一个省略号 苦中，带甜 整整一个月，似漫长的一世纪 无处安放的思念 时常被流放于深夜...
    吴伟锋阅读 1,132评论 0赞 1