SQL函数使用

SQL函数使用,大多数SQL实现支持以下类型的函数。

  • 用于处理文本字符串(如删除或填充值,转换值为大写或小写)的文本函数。
  • 用于在数值数据上进行算术操作(如返回绝对值,进行代数运算)的数值函数。
  • 用于处理日期和时间值并从这些值中提取特定成分(如返回两个日期之差,检查日期有效性)的日期和时间函数。
  • 用于生成美观好懂的输出内容的格式化函数(如用语言形式表达出日期,用货币符号和千分位表示金额)。
  • 返回DBMS正使用的特殊信息(如返回用户登录信息)的系统函数。

我们在SQL函数看到函数用于SELECT后面的列名,但函数的作用不仅于此。它还可以作为SELECT语句的其他成分,如在WHERE子句中使用,在其他SQL语句中使用等。

文本处理函数

前面我们已经看过一个文本处理函数的例子,其中使用RTRIM()函数来去除列值右边的空格。下面是另一个例子,这次使用的是UPPER()函数:

    SELECT vend_name, UPPER(vend_name) AS vend_name_upcase
    FROM Vendors
    ORDER BY vend_name;

输出结果:

    vend_name                            vend_name_upcase
    ---------------------------      ----------------------------
    Bear Emporium                       BEAR EMPORIUM
    Bears R Us                           BEARS R US
    Doll House Inc.                     DOLL HOUSE INC.
    Fun and Games                       FUN AND GAMES
    Furball Inc.                        FURBALL INC.
    Jouets et ours                      JOUETS ET OURS

可以看到,UPPER()将文本转换为大写,因此本例子中每个供应商都列出两次,第一次为Vendors表中存储的值,第二次作为列vend_name_upcase转换为大写。

提示:大写,小写,大小写混合
此时你应该已经知道SQL函数不区分大小写,因此upper(), UPPER(), Upper()都可以,substr(), SUBSTR(), SubStr()也都行。随你的喜好,不过注意保持风格一致,不要变来变去,否则你写的程序代码就不好读了。

下表列出了一些常用的文本处理函数。
常用的文本处理函数
SQL函数使用

表中的SOUNDEX需要做进一步的解释。SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX考虑了类似的发音字符和音节,使得能对字符串进行发音比较而不是字母比较。虽然SOUNDEX不是SQL概念,但多数DBMS都提供对SOUNDEX的支持。

说明:SOUNDEX支持
PostgreSQL不支持SOUNDEX(),因此以下的例子不适用于这个DBMS

另外,如果在创建SQLite时使用了SQLITE_SOUNDEX编译时选项,那么SOUNDEX()在SQLite中就可用。因为SQLITE_SOUNDEX不是默认的编译时选项,所以多数SQLite实现不支持SOUNDEX()。

下面给出一个使用SOUNDEX()函数的例子。Customers表中有一个顾客Kids Place,其联系名为Michelle Green。但如果这是错误的输入,此联系名实际上应该是Michael Green,该怎么办呢?显然,按正确的联系名搜索不会返回数据,如下所示:

    SELECT cust_name, cust_contact
    FROM Customers
    WHERE cust_contact = 'Michael Green';

输出结果:

    cust_name                           cust_contact
    --------------------------      ----------------------------

现在试一下使用SOUNDEX()函数进行搜索,它匹配所有发音类似于Michael Green的联系名:

    SELECT cust_name, cust_contact
    FROM Customers
    WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green');

输出结果:

    cust_name                           cust_contact
    --------------------------      ----------------------------
    Kids Place                         Michelle Green

在这个例子中,WHERE子句使用SOUNDEX()函数把cust_contact列值和搜索字符串转换为它们的SOUNDEX值。因为Michael Green和Michelle Green发音相似,所以它们的SOUNDEX值匹配,因此WHERE子句正确地过滤出了所需的数据。

日期和时间处理函数

日期和时间采用相应的数据类型存储在表中,每种DBMS都有自己的特殊形式。日期和时间值以特殊的格式存储,以便能快速和有效地排序或过滤,并且节省物理存储空间。
应用程序一般不使用日期和时间的存储格式,因此日期和时间函数总是用来读取、统计和处理这些值。由于这个原因,日期和时间函数在SQL中具有重要的作用。遗憾的是,它们很不一致,可移植性最差。

我们举个简单的例子,来说明日期处理函数的用法。Orders表中包含的订单都带有订单日期。要检索出某年的所有订单,需要按订单日期去找,但不需要完整日期,只要年份即可。

为在SQL Server中检索2020年的所有订单,可如下进行:

SELECT order_num
    FROM Orders
    WHERE DATEPART(yy, order_date) = 2020;

输出结果:

    order_num
    -----------
    20005
    20006
    20007
    20008
    20009

这个例子使用了DATEPART()函数,顾名思义,此函数返回日期的某一部分。DATEPART()函数有两个参数,它们分别是返回的成分和从中返回成分的日期。在此例子中,DATEPART()只从order_date列中返回年份。通过与2020比较,WHERE子句只过滤出此年份的订单。

下面是使用名为DATE_PART()的类似函数的PostgreSQL版本:

    SELECT order_num
    FROM Orders
    WHERE DATE_PART('year', order_date) = 2020;

Oracle没有DATEPART()函数,不过有几个可用来完成相同检索的日期处理函数。例如:

    SELECT order_num
    FROM Orders
    WHERE EXTRACT(year FROM order_date) = 2020;

在这个例子中,EXTRACT()函数用来提取日期的成分,year表示提取哪个部分,返回值再与2020进行比较。

提示:PostgreSQL支持Extract()
除了前面用到的DatePart(),PostgreSQL也支持Extract()函数,因而也能这么用。

完成相同工作的另一方法是使用BETWEEN操作符:

    SELECT order_num
    FROM Orders
    WHERE order_date BETWEEN to_date('2020-01-01', 'yyyy-mm-dd')
      AND to_date('2020-12-31', 'yyyy-mm-dd');

在此例子中,Oracle的to_date()函数用来将两个字符串转换为日期。一个包含2020年1月1日,另一个包含2020年12月31日。BETWEEN操作符用来找出两个日期之间的所有订单。值得注意的是,相同的代码在SQL Server中不起作用,因为它不支持to_date()函数。但是,如果用DATEPART()替换to_date(),当然可以使用这种类型的语句。

DB2,MySQL和MariaDB具有各种日期处理函数,但没有DATEPART()。DB2,MySQL和MariaDB用户可使用名为YEAR()的函数从日期中提取年份:

    SELECT order_num
    FROM Orders
    WHERE YEAR(order_date) = 2020;

在SQLite中有个小技巧:

    SELECT order_num
    FROM Orders
    WHERE strftime('%Y', order_date) = '2020';

这里给出的例子提取和使用日期的成分(年)。按月份过滤,可以进行相同的处理,使用AND操作符可以进行年份和月份的比较。

DBMS提供的功能远不止简单的日期成分提取。大多数DBMS具有比较日期、执行日期的运算、选择日期格式等的函数。但是,可以看到,不同DBMS的日期-时间处理函数可能不同。

数值处理函数

数值处理函数仅处理数值数据。这些函数一般主要用于代数、三角或几何运算,因此不像字符串或日期-时间处理函数使用那么频繁。
具有讽刺意味的是,在主要DBMS的函数中,数值函数是最一致、最统一的函数。下表列出一些常用的数值处理函数。
常用数值处理函数
SQL函数使用

酷客网相关文章:

赞(1)

评论 抢沙发

评论前必须登录!

 

SQL教程