SQL 使用数据处理函数

SQL 使用数据处理函数

一、函数：

• 函数一般是在数据上执行的，为数据的转换和处理提供了方便。

1.1：函数带来的问题：

• 事实上，只有少数几个函数被所有主要的DBMS等同地支持。虽然所有类型的函数一般都可以在每个DBMS中使用，但各个函数的名称和语法可能及其不同。

• DBMS函数的差异：

  函数	语法
  提取字符串的组成部分	Access使用MID()；DB2、Oracle、Post-greSQL和SQLite使用SUBSTR()；MySQL和SQL Server使用SUBSTRING()
  数据类型转换	Access和Oracle使用多个函数，每种类型的转换有一个函数；DB2和PostgreSQL使用CAST()；MariaDB、MySQL和SQL Server使用CONVERT()
  取当前日期	Access使用NOW()；DB2和PostgreSQL使用CURRENT_DATE；MariaDB和MySQL使用CURDATE()；Oracle使用SYSDATE；SQLServer使用GETDATE()；SQLite使用DATE()

  • 可以看到，SQL语句不一样，SQL函数不是可移植的。这表示为特定的SQL实现编写的代码在其他实现中可能不正常。

• 可移植：

  • 所编写的代码可以在多个系统上运行。
  • 为了代码的可移植，许多SQL程序员不赞成使用特定于实现的功能。虽然这样做可以很有好处，但有的时候并不利于应用程序的性能。

• 是否应该使用函数？

  • `如果你使用函数，应该保证做好代码的注释，以便维护。

二、使用函数：

• 大多数SQL实现支持以下类型的函数。

  1.用于处理文本字符串(如删除或填充值，转换值为大写或小写)的文本函数。

  2.用于在数值数据上进行算术操作(返回绝对值，进行代数运算)的数值函数。

  3.用于处理日期和时间值并从这些值中提取特定成分(如返回两个日期之差，检查日期有效性)的日期和时间函数。

  4.返回DBMS正使用的特殊信息(如返回用户登录信息)的系统函数。

2.1：文本处理函数：

• 使用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   |
    +-----------------+------------------+
    6 rows in set (0.00 sec)
    

  • 可以看到，UPPER()将文本转换为了大写，可以从输出结果看，第一为vendors表中存储的值，第二次作为vend_name_upcase转换为大写。

• 常用的文本处理函数

  函数	说明
  LEFT() ( 或使用子字符串函数 )	返回字符串左边的字符
  LENGTH() ( 也使用DATALENGTH() 或 LEN() )	返回字符串的长度
  LOWER() ( Access使用 LCASE() )	将字符转换为小写
  LTRIM()	去掉字符串左边的空格
  RIGHT() (或使用子字符串函数)	返回字符串后边的字符
  RTRIM()	去掉字符串后边的空格
  SOUNDEX()	返回字符串的SOUNDEX值
  UPPER()	将字符串转换为大写

2.2：日期和时间处理函数：

• 日期和时间采用相应的数据类型存储在表中，每种DBMS都有自己的特殊方式。

• 日期和时间值以特殊的格式存储，以便能快速和有效地排序或过滤，并且节省物理存储空间。

• 应用程序一般不使用日期和时间的存储格式，因此日期和时间函数总是用来读取、统计和处理这些值。

  SELECT order_num FROM orders WHERE YEAR(order_date)=2012;
  

  • 输出结果如下：

    +-----------+
    | order_num |
    +-----------+
    |     20005 |
    |     20006 |
    |     20007 |
    |     20008 |
    |     20009 |
    +-----------+
    

• 注意：举得栗子提取和使用日期的成分(年)。按月份过滤，可以进行相同的处理，指定AND操作符以及年和月份的比较。

2.3：数值处理函数：

• 数值处理函数仅处理数值数据。

  • 这些函数一般主要用于代数、三角或几何运算。

• 常用数值处理函数：

  函数	说明
  ABS（）	返回一个数的绝对值
  COS（）	返回一个角度的余弦
  EXP（）	返回一个数的指数值
  PI（）	返回圆周率
  SIN（）	返回一个角度的正弦
  SQRT（）	返回一个数的平方根
  TAN（）	返回一个角度的正切