学习SQL【7】-函数

终于可以开原创标识和留言功能了,开心。我坚信努力总会有收获的。

不仅SQL, 对所有的编程语言来说,函数都起着至关重要的作用。函数就像是编程语言的“道具箱”,每种编程语言都准备了非常多的函数。譬如,C语言推崇的就是用函数来实现模块化编程。

根据用途不同,SQL中的函数大致可以分为算术函数、字符串函数、日期函数、转换函数和聚合函数。

一:函数的种类

1:函数的定义

所谓函数,就是输入某一值得到相应输出结果的功能,输入值称为参数,输出值称为返回值。

2:函数的分类

函数大致可以分为以下几种:

  • 算术函数(用来进行数值计算的函数)
  • 字符串函数(用来进行字符串操作的函数)
  • 日期函数(用来进行日期操作的函数)
  • 转换函数(用来转换数据类型和值的函数)
  • 聚合函数(用来进行数据聚合的函数) SQL中函数的种类有超过200多个,但是常用的只有30~50个。

二:算术函数

算术函数是最基本的函数,也就是我们常用的四则运算:

  • +(加法)
  • -(减法)
  • *(乘法)
  • /(除法)

为了学习算术函数,我们首先得创建一种示例用表:

--创建SampleMath表
 --DDL:创建表
 CREATE TABLE SampleMath (
 m  NUMERIC(10, 3),
 n  INTEGER,
 P  INTEGER);

如上所示,m列的数据类型为NUMERIC,NUMERIC是大多数DBMS都支持的一种数据类型,通过NUMERIC(全体位数, 小数位数)的形式来指定数值的大小。 然后再插入数据:

--插入数据
 BEGIN TRANSACTION;BEGIN
 INSERT INTO SampleMath VALUES (500, 0, NULL);INSERT 0 1
 INSERT INTO SampleMath VALUES (-180, 0, NULL);INSERT 0 1
 INSERT INTO SampleMath VALUES (NULL, NULL, NULL);INSERT 0 1
 INSERT INTO SampleMath VALUES (NULL, 7, 3);INSERT 0 1
 INSERT INTO SampleMath VALUES (NULL, 5, 2);INSERT 0 1
 INSERT INTO SampleMath VALUES (NULL, 4, NULL);INSERT 0 1
 INSERT INTO SampleMath VALUES (8, NULL, 3);INSERT 0 1
 INSERT INTO SampleMath VALUES (2.27, 1, NULL);INSERT 0 1
 INSERT INTO SampleMath VALUES (5.555, 2, NULL);INSERT 0 1
 INSERT INTO SampleMath VALUES (NULL, 1, NULL);INSERT 0 1
 INSERT INTO SampleMath VALUES (8.76, NULL, NULL);INSERT 0 1
 COMMIT;COMMIT

上面的代码是我在Windows的命令行窗口敲的,所以每插入一个数据回车就会弹出

确认一下创建的表的内容:

SELECT * FROM SampleMath;

执行结果:

    m     | n | p----------+---+---
  500.000 | 0 |
 -180.000 | 0 |
          |   |
          | 7 | 3
          | 5 | 2
          | 4 |
    8.000 |   | 3
    2.270 | 1 |
    5.555 | 2 |
          | 1 |
    8.760 |   |
(11 行记录)

ABS—-绝对值

ABS是计算绝对值的函数,使用方法直接看例子:

 --计算数值的绝对值
 SELECT m, ABS(m) AS abs_col   
   FROM SampleMath;

执行结果:

    m     | abs_col----------+---------
  500.000 | 500.000
 -180.000 | 180.000
          |
          |
          |
          |
    8.000 |   8.000
    2.270 |   2.270
    5.555 |   5.555
          |
    8.760 |   8.760
(11 行记录)

如上所示,-180的绝对值就是去掉符号的180。还有,当ABS函数的参数为NULL时,结果也为NULL。

MOD—-求余

MOD是计算除法余数的函数,使用方法如下:

--计算除法(n / p)的余数
 SELECT n, p, MOD(n, p) AS mod_col   
   FROM SampleMath;

执行结果:

n | p | mod_col---+---+---------
 0 |   |
 0 |   |
   |   |
 7 | 3 |       1
 5 | 2 |       1
 4 |   |
   | 3 |
 1 |   |
 2 |   |
 1 |   |
   |   |
(11 行记录)

注释:在SQL Server中使用特殊的运算符“%”来计算余数。

ROUND—-四舍五入

ROUND函数用来进行四舍五入操作。如果指定四舍五入的位数为1,那么就会对小数点第2位进行四舍五入。如果指定位数为2,那么就会对小数点第3位进行四舍五入操作。

 --对m列的数值进行n列位数的四舍五入处理
 SELECT m, n, ROUND(m, n) AS round_col   
   FROM SampleMath;

执行结果:

   m     | n | round_col----------+---+-----------
  500.000 | 0 |       500
 -180.000 | 0 |      -180
          |   |
          | 7 |
          | 5 |
          | 4 |
    8.000 |   |
    2.270 | 1 |       2.3
    5.555 | 2 |      5.56
          | 1 |
    8.760 |   |
(11 行记录)

字符串函数

为了学习字符串函数,我们再来创建一张表:

--创建SampleStr表
 --DDL:创建表
 CREATE TABLE SampleStr
 ( str1  VARCHAR(40),
   str2  VARCHAR(40),
   str3  VARCHAR(40));

插入数据:

BEGIN TRANSACTION;BEGIN
 INSERT INTO SampleStr VALUES ('opx', 'rt', NULL);INSERT 0 1
 INSERT INTO SampleStr VALUES ('abc', 'def', NULL);INSERT 0 1
 INSERT INTO SampleStr VALUES ('我', '是', '番茄酱');INSERT 0 1
 INSERT INTO SampleStr VALUES ('aaa', NULL, NULL);INSERT 0 1
 INSERT INTO SampleStr VALUES (NULL, 'xyz', NULL);INSERT 0 1
 INSERT INTO SampleStr VALUES ('@!#$%', NULL, NULL);INSERT 0 1
 INSERT INTO SampleStr VALUES ('ABC', NULL, NULL);INSERT 0 1
 INSERT INTO SampleStr VALUES ('aBC', NULL, NULL);INSERT 0 1
 INSERT INTO SampleStr VALUES ('abc番茄酱', 'abc', 'ABC');INSERT 0 1
 INSERT INTO SampleStr VALUES ('abcdefabc', 'abc', 'ABC');INSERT 0 1
 INSERT INTO SampleStr VALUES ('micmic', 'i', 'I');INSERT 0 1
 COMMIT;COMMIT

确认创建的表的内容:

SELECT * FROM SampleStr;

执行结果:

  str1    | str2 |  str3-----------+------+--------
 opx       | rt   |
 abc       | def  |
 我        | 是   | 番茄酱
 aaa       |      |
           | xyz  |
 @!#$%     |      |
 ABC       |      |
 aBC       |      |
 abc番茄酱 | abc  | ABC
 abcdefabc | abc  | ABC
 micmic    | i    | I
(11 行记录)

||—-拼接

在SQL中可使用两条并列的竖线“||”来实现字符串的拼接

 --拼接两个字符串
 SELECT str1, str2, str1 || str2 AS str_concat   
   FROM SampleStr;

执行结果:

  str1    | str2 |  str_concat-----------+------+--------------
 opx       | rt   | opxrt
 abc       | def  | abcdef
 我        | 是   | 我是
 aaa       |      |
           | xyz  |
 @!#$%     |      |
 ABC       |      |
 aBC       |      |
 abc番茄酱 | abc  | abc番茄酱abc
 abcdefabc | abc  | abcdefabcabc
 micmic    | i    | micmici
(11 行记录)

进行字符串拼接时,如果其中包含NULL,那么得到的结果也是NULL。当然,也可以进行三个字符串的拼接:

 --拼接三个字符串
 SELECT str1, str2, str3, str1 || str2 || str3 AS str_concat   
   FROM SampleStr  
  WHERE str1 = '我';

执行结果:

 str1 | str2 |  str3  | str_concat------+------+--------+------------
 我   | 是   | 番茄酱 | 我是番茄酱
(1 行记录)

LENGTH—-字符串长度

想要知道字符串包含多少个字符时,可以使用LENGTH函数

 --计算字符串长度
 SELECT str1, LENGTH(str1) AS len_str   
   FROM SampleStr;

执行结果:

 str1    | len_str-----------+---------
 opx       |       3
 abc       |       3
 我        |       1
 aaa       |       3
           |
 @!#$%     |       5
 ABC       |       3
 aBC       |       3
 abc番茄酱 |       6
 abcdefabc |       9
 micmic    |       6
(11 行记录)

LOWER—-小写转换

LOWER函数只能针对英文字母使用,它会将参数中的字符串全部都转换为小写

--大写转换为小写
 SELECT str1, LOWER(str1) AS low_str   
   FROM SampleStr   
  WHERE str1 IN ('ABC', 'aBC', 'abc', '番茄酱');

执行结果:

str1 | low_str------+---------
 abc  | abc
 ABC  | abc
 aBC  | abc
(3 行记录)

UPPER—-大写转换

LOWER函数只能针对英文字母使用,它会将参数中的字符串全部都转换为大写

 --将小写转换为大写
 SELECT str1, UPPER(str1) AS up_str   
   FROM SampleStr  
  WHERE str1 IN ('ABC', 'aBC', 'abc', '番茄酱');

执行结果:

 str1 | up_str------+--------
 abc  | ABC
 ABC  | ABC
 aBC  | ABC
(3 行记录)

REPLACE—-字符串的替换

REPLACE函数的语法:

REPLACE(对象字符串, 替换前的字符串, 替换后的字符串)

使用REPLACE函数,可以将字符串的一部分替换为其他的字符串,咱们用一个例子看一下:

--替换字符串的一部分
 SELECT str1, str2, str3, REPLACE(str1, str2, str3) AS rep_str   
   FROM SampleStr;

执行结果:

 str1    | str2 |  str3  |  rep_str-----------+------+--------+-----------
 opx       | rt   |        |
 abc       | def  |        |
 我        | 是   | 番茄酱 | 我
 aaa       |      |        |
           | xyz  |        |
 @!#$%     |      |        |
 ABC       |      |        |
 aBC       |      |        |
 abc番茄酱 | abc  | ABC    | ABC番茄酱
 abcdefabc | abc  | ABC    | ABCdefABC
 micmic    | i    | I      | mIcmIc
(11 行记录)

SUBSTRING—-字符串的截取

SUBSTRING函数的语法:

SUBSTRING(对象字符串 FROM 截取的起始位置 FOR 截取的字符数)

使用SUBSTRING函数可以截取字符串中的一部分字符串。截取的起始位置从字符串最左侧开始计算。

 --截取字符串中第3位和第4位的字符
 SELECT str1, SUBSTRING(str1 FROM 3 FOR 2) AS sub_str   
   FROM SampleStr;

执行结果:

  str1    | sub_str-----------+---------
 opx       | x
 abc       | c
 我        |
 aaa       | a
           |
 @!#$%     | #$
 ABC       | C
 aBC       | C
 abc番茄酱 | c番
 abcdefabc | cd
 micmic    | cm
(11 行记录)

日期函数

CURRENT_DATE—-当前日期

CURRENT_DATE函数能够返回SQL所执行时的日期,由于没有参数,所以无需使用括号:

--获得当前日期
 SELECT CURRENT_DATE;

执行结果:

   date------------
 2017-12-07
(1 行记录)

CURRENT_TIME—-当前时间

CURRENT_DATE函数能够返回SQL所执行时的时间,由于没有参数,所以也无需使用括号:

--取得当前时间
 SELECT CURRENT_TIME;

执行结果:

       timetz--------------------
 20:46:11.321481+08
(1 行记录)

CURRENT_TIMESTAMP—-当前时间和日期

CURRENT_TIMESTAMP函数具有CURRENT_DATE+CURRENT_TIME的功能。使用该函数可以同时获得当前的日期和时间:

--取得当前日期和时间
 SELECT CURRENT_TIMESTAMP;

执行结果:

  now-------------------------------
 2017-12-07 20:46:58.813816+08
(1 行记录)

EXTRACT—-截取日期元素

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;

执行结果:

    now              | year | month | day | hour | minute |  second-------------------------------+------+-------+-----+------+--------+----------
 2017-12-07 20:51:00.177839+08 | 2017 |    12 |   7 |   20 |     51 | 0.177839
(1 行记录)

转换函数

转换函数在SQL中主要有两种:一种是数据类型的转换,简称类型转换。另一种是值的转换。

CAST—-类型转换

CAST函数的语法:

CAST(转换前的值 AS 想要转换的数据类型)

进行数据类型转换可以避免一些由于表中数据类型不匹配或者数据类型不一致而引发的错误。

--将字符串类型转换为数值类型
 SELECT CAST('0001' AS INTEGER) AS int_col;

执行结果:

int_col---------
       1
(1 行记录)

将字符串类型转换为日期类型

SELECT CAST('2017-12-07' AS DATE) AS date_col;

执行结果:

 date_col------------
 2017-12-07
(1 行记录)

COALESCE—-将NULL转换为其他值

COALESCE函数的语法:

COALESCE(数据1, 数据2, 数据3,...)

COALESCE是SQL特有的函数。该函数会返回可变参数中左侧开始第一个不是NULL的值。参数个数是可变的,因此可以根据需要而无限增加。

--将NULL转换为其他值
 SELECT COALESCE(NULL, 1)  AS col_1,  
        COALESCE(NULL, 'test', 'NULL')  AS col_2,        
        COALESCE(NULL, NULL, '2017-12-07')  AS col_3;

执行结果:

col_1 | col_2 |   col_3-------+-------+------------
     1 | test  | 2017-12-07
(1 行记录)

使用SampleStr表中的列作为例子

SELECT COALESCE(str2, 'NULL')   FROM SampleStr;

执行结果:

 coalesce----------
 rt
 def
 是
 NULL
 xyz
 NULL
 NULL
 NULL
 abc
 abc
 i
(11 行记录)

如上所示,将str2列中NULL(空)转换为了字符串‘NULL’。

今天可以留言了,大家可以尽管吐槽啦

每天学习一点点,每天进步一点点。

原文发布于微信公众号 - 小白客(youcoding)

原文发表时间:2017-12-08

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Java成神之路

Oracle学习笔记_02_基本SQL

        SQL 语言大小写不敏感。         SQL 可以写在一行或者多行 关键字不能被缩写也不能分行         各子句一般要分行写。 ...

882
来自专栏王磊的博客

MongoDB Query 的几个方法

Query.All("name", "a", "b");//通过多个元素来匹配数组 Query.And(Query.EQ("name", "a"), Query...

2908
来自专栏数据和云

深入剖析:认识Oracle 中的 NULL 值

杨廷琨,网名 yangtingkun 云和恩墨技术总监,Oracle ACE Director,ACOUG 核心专家 经常看到很多人提出和NULL有关的问题。N...

2935
来自专栏互联网开发者交流社区

用于 SELECT 和 WHERE 子句的函数

1423
来自专栏与神兽党一起成长

[MySQL]查询学生选课的情况(二)

上一篇文章使用了自定义的函数查找出来选择Math,或者选择Chinese或选择Japanese课程的学生。

1500
来自专栏xcywt

学习SQLite之路(二)

  下面就是真正关于数据库的一些知识了: 20160614更新  参考: http://www.runoob.com/sqlite/sqlite-tutoria...

1907
来自专栏大大的微笑

设计模式之单例模式深究

为什么使用单例模式?         我认为是由于某些对象的创建比较耗时,对内存的消耗又比较大回收成本过高,这时候如果允许 不断的创建对象代价势必太高,如果...

2196
来自专栏Java后端生活

MySQL(三)基础查询

1489
来自专栏醉生梦死

Mysql中的运算符 原

    SELECT 10 % 3, 10 MOD 3, MOD(10,3);

1224
来自专栏吾爱乐享

软件测试之学习mysql的查询功能select及高级查询(重中之重)

1212

扫码关注云+社区

领取腾讯云代金券