第6章 函数、谓词、CASE表达式

6-1 各种各样的函数

• 函数的种类

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

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

  • 算术函数（用来进行数值计算的函数）
  • 字符串函数（用来进行字符串操作的函数）
  • 日期函数（用来进行日期操作的函数）
  • 转换函数（用来转换数据类型和值的函数）
  • 聚合函数（用来进行数据聚合的函数）—— COUNT/SUM/AVG/MAX/MIN

• 算术函数

  • ABS——绝对值

    ABS是绝对值的函数。绝对值（absolute value）不考虑数值的符号，表示一个数到原点的距离

    ABS(数值)
    

    注意：ABS函数的参数为NULL 时，结果也是NULL。并非只有ABS函数如此，其实大多数函数对于NULL都返回NULL

  • MOD——求余

    MOD(被除数，除数)
    
    -- 注意：主流的DBMS都支持MOD函数，只有SQL Server不支持该函数，SQL Server使用特殊的运算符（函数）“%”来计算余数
    

  • ROUND——四舍五入

    ROUND(对象数值，保留小数的位数)
    

• 字符串函数

  • ||——拼接

    字符串1 || 字符串2
    

    进行字符串拼接时，如果其中包含NULL，那么得到的结果也是NULL

    

  • LENGTH——字符串长度

    LENGTH(字符串)
    

    

    

  • LOWER——小写转换

    LOWER(字符串)
    
    -- 与之对应的有大写转换函数（UPPER）
    

  • REPLACE——字符串的替换

    使用REPLACE函数可以将字符串的一部分替换为其他的字符串

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

  • SUBSTRING——字符串的截取

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

    
    
    

• 日期函数

  • CURRENT_DATE——当前日期

    CURRENT_DATE
    
    -- CURRENT_DATE函数能够返回SQL执行的日期，也就是该函数执行时的日期。由于没有参数，因此无需使用括号
    

  • CURRENT_TIME——当前时间

    CURRENT_TIME
    

  • CURRENT_TIMESTAMP——当前日期和时间

    CURRENT_TIMESTAMP
    

  • EXTRACT——截取日期元素

    使用EXTRACT函数可以截取出日期数据中的一部分，该函数的返回值并不是日期类型而是数值类型

    EXTRACT(日期元素 FROM 日期)
    

    
    
    

• 转换函数

  • CAST——类型转换

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

类型转换其实并不是为了方便用户使用而开发的功能，而是为了方便DBMS内部处理而开发的功能

• COALESCE——将NULL转换为其他值

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

6-2 谓词

• 什么是谓词（predicate）

  =、<、>、<>等比较运算符，其正式的名称就是【比较谓词】

  谓词其实是函数的一种，是需要满足特定条件的函数，该条件就是返回值是真值

  对通常的函数来说，返回值有可能是数字、字符串或者日期等，但是谓词的返回值全都是真值（TRUE/FALSE/UNKNOWN），这也是谓词和函数的最大区别

• LIKE谓词——字符串的部分一致查询

  当需要进行字符串的部分一致查询时需要使用LIKE谓词，部分一致大体可以分为前方一致、中间一致和后方一致三种类型

  

• %是代表“0字符以上的任意字符串”的特殊符号，_（下划线）则代表“任意1个字符”

• BETWEEN谓词——范围查询

BETWEEN的特点就是结果中会包含两个临界值

• IS NULL、IS NOT NULL——判断是否为NULL

  为了选取出某些值为NULL的列的数据，不能使用=，而只能使用特定的谓词IS NULL，与此相反，想要选取NULL以外的数据时，需要使用IS NOT NULL

• IN谓词——OR的简便用法

  注意：使用IN和NOT IN时是无法选取出NULL数据的，NULL终究还是需要使用IS NULL和IS NOT NULL来进行判断

  • 使用子查询作为IN谓词的参数

    • IN和子查询

      IN谓词（NOT IN谓词）具有其他谓词所没有的用法，那就是可以使用子查询作为其参数

    • NOT IN和子查询

      NOT IN的参数中包含NULL时结果通常会为空

• EXIST谓词

  • EXIST谓词的使用方法

    一言以蔽之，谓词的作用就是“判断是否存在满足某种条件的记录”。如果存在这样的记录就返回真（TRUE），如果不存在就返回假（FALSE）。EXIST（存在）谓词的主语是“记录”

    

  • EXIST的参数

    由上可知，EXIST是只有1个参数的谓词。EXIST只需要在右侧书写1个参数，该参数通常都会是一个关联子查询

  • 子查询中的(SELECT *)

    

  • 使用NOT EXIST替换NOT IN

  

6-3 CASE表达式

• 什么是CASE表达式

  CASE表达式是一种进行运算的功能，这就意味着CASE表达式也是函数的一种。CASE表达式是在区分情况时使用的，这种情况的区分在编程中通常称为 （条件）分支

• CASE表达式的语法

  CASE表达式的语法分为简单CASE表达式和搜索CASE表达式两种，其中搜索CASE表达式包含了简单CASE表达式的全部功能

  CASE WHEN 求值表达式 THEN 表达式
  	 WHEN 求值表达式 THEN 表达式
  	 WHEN 求值表达式 THEN 表达式
  	  .
  	  .
  	  .
  	 ELSE 表达式
  END
  

  

• CASE表达式的使用方法

  
  
  

  CASE表达式中的END不能省略，ELSE子句可以省略不写，这时会被默认为ELSE NULL，但是最好还是不要省略。

简单CASE表达式虽然看上去简化了书写，但是想要在WHEN子句中指定不同的列时，简单CASE表达式就无能为力了