数据处理函数

• 函数的可移植性没有SQL强：能够运行在多个系统上的代码称为可移植的（portable）；SQL的可移植性比较好，在SQL实现之间有差异时，比较好处理；然而函数的可移植性却很差，差异很大。如果决定使用函数，一定要做好注释，以便以后可以确切地知道SQL代码的含义。
• 大多数SQL实现支持以下类型的函数：
  • 用于处理文本串的文本函数（如删除或填充值，转换值为大写或小写等）
  • 用于在数值数据上进行算术操作的数值函数（如返回绝对值，进行代数运算等）
  • 用于处理日期和时间值并从这些值中提取特定成分的日期和时间函数（如返回两个日期之差，检查日期有效性等）
  • 返回DBMS正使用的特殊信息的系统函数（如返回用户登录信息，检查版本细节等）

1. 文本处理函数

• 常用的文本处理函数

  函数	说明
  Left()	返回串左边的字符
  Length()	返回串的长度
  Locate()	找出串的一个字串
  Lower()	将串转换为小写
  LTrim()	去掉串左边的空格
  Right()	返回串右边的字符
  RTrim()	去掉串右边的空格
  Soundex()	返回串的SOUNDEX值
  SubString()/Substr()	返回子串的字符
  Upper()	将串转换为大写

  • Left()/Right()函数说明：

    • 语法：LEFT(str,length)
    • LEFT()函数接收两个参数：str是要提取子串的字符串，length是一个整数（如果非正，将返回一个空串，如果大于str的长度，将会返回整个str字符串，指定将从左/右返回的字符数

  • Locate()函数说明：

    • 语法：LOCATE(substr,str)或LOCATE(substr,str,pos)
    • 第一个语法返回substr在str中第一次出现的位置，第二个语法返回substr在str中从pos处开始第一次出现的位置；若substr不在str中，则返回值为0

  • Soundex()函数说明：

    • SOUNDEX是一个将任何文本转换为描述其语音表示的字母数字模式的算法，它考虑了类似的发音字符和音节，使得其可以对串进行发音比较而不是字母比较。

    • 例:

    • # 表中人名为Y.Lee，但实际应该是Y.Lie
      SELECT cust_name,cust_contact
      FROM customers
      WHERE SOUNDEX(cust_contact) = SOUNDEX('Y LIE');
      +-------------+--------------+
      | cust_name   | cust_contact |
      +-------------+--------------+
      | Coyote Inc. | Y Lee        |
      +-------------+--------------+
      

  • Substring()函数说明：

    • 语法：SUBSTRING(str,pos)或SUBSTRING(str FROM pos)
    • SUBSTRING()函数也有两个参数：str参数是要提取子串的字串，pos是一个整数，用于指定子串的起始字符，可正可负
      • 如果pos为正，则函数从字符串的开始处提取子串
      • 如果pos为0，则返回一个空串
      • 注意：该方法索引从1开始！！！
    • SUBSTR()与SUBSTRING()同义，可以互换使用

2. 日期和时间处理函数

• MySQL日期格式：首选/默认为yyyy-mm-dd，它排除了多义性（如，04/05/06是2006年5月4日或2006年4月5日或2004年5月6日或……）
  • 年份应该总是使用4位数字:尽管MySQL支持2位数字的年份，但使用完整的4位数字年份更为可靠，因为这样MySQL不必做出任何假定

2.1 基本日期比较

• 思考：如果直接使用WHERE order_date = '2005-09-01'，这样有什么问题?
  • 数据类型为datetime，这种数据类型存储日期以及时间值；如果仅存储日期，那么表中均具有时间值00:00:00，这时候查询是没有问题的，但实际情况是，如果同时存储了日期和时间，例如order_date值为’2005-09-01 11:30:03’，那么上述语句就会匹配失败
  • 解决办法是指示MySQL仅将给出的日期与列中日期部分进行比较，而非与整个列值比较，因此需要使用Date()函数：WHERE Date(order_date) = '2005-09-01'
  • 如果需要的日期，尽量使用Date()函数是一个好习惯，Time()亦是如此

2.2 匹配月份

• 需求：匹配2005年9月份所有的订单

• 方法一：将时间定义为要匹配的日期范围

  • SELECT cust_id, order_num
    FROM orders
    WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';
    

• 方法二：不需要记住每个月有多少天&&不需要操心闰年2月

  • SELECT cust_id, order_num
    FROM orders
    WHERE Year(order_date) = 2005 AND Month(order_date) = 9;
    

3. 数值处理函数

• 数值处理函数仅处理数值数据，一般用于代数、三角或集合运算

  • 函数	说明
    Abs()	返回一个数的绝对值
    Cos()	返回一个角度的余弦
    Exp()	返回一个数的指数值
    Mod()	返回除操作的余数
    Pi()	返回圆周率
    Rand()	返回一个随机数
    Sin()	返回一个角度的正弦
    Sqrt()	返回一个数的平方根
    Tan()	返回一个角度的正切