SQL基础总结(四)：操作符和函数

本系列blog源自前年写的SQL学习笔记，汇总一下发上来。(1月份发了前三篇笔记，原以为后面的笔记误操作删了，今天在硬盘里又找到了，一起发上来)

--------------------------------

派生列是一个计算结果。派生列不会成为表中的永久列，他们用于显示或者报表目的。

 

包含空值的任何算数运算的结果是空值。

 

在算术表达式中使用多种数字数据类型。DBMS将把所有数字转换为表达式中最复杂操作数的数据类型，并以这种类型返回结果，这个转换过程称为提升。如一个整数和一个浮点数相加，DBMS将整数转换为浮点数，然后进行数字加法，以浮点数形式返回结果。

 

有时DBMS强制数学封闭，因此一个整数除以一个整数时一定要仔细，结果可能是整数（根据DBMS而定）。

 

使用操作符||组合（连接）串。

连接串与非串时，DBMS如不能隐式转换类型，必须将非串转换为串。

包含空值的连接运算结果为空值，如’a’||null||’b’等于null。但Oracle例外（Oracle将空值当作空串处理）。

 

可以连接十六进制和二进制串：B’0100’||B’1011’等于B’01001011’。

可以使用TRIM()函数从连接的串中删除不需要的空格。

CONCAT()接受任何参数并在必要时将非串转换为串（CAST则不是必须的）。Oracle和DB2也支持CONCAT()函数。

 

使用函数SUBSTRING()提取串的一部分。

SUBSTRING(string FROM start [FOR length])

空串的子串是空串。

例：SUBSTRING(‘abc01’ FROM 1 FOR 3) -- ‘abc’

SUBSTRING(‘abc01’ FROM 4) -- ‘01’  省略FOR时一直到串末尾

可以从十六进制或二进制中提取子串：

SUBSTRING(B’01001011’ FROM 5 FOR 4)  -- B’1011’

Oracle和DB2中子串函数是SUBSTR(string,start [,length]).

Oracle将空值作为空串处理：SUBSTR(NULL,1,2)返回’’.

 

使用函数UPPER()将返回小写字母转换为大写字母。使用函数LOWER()返回将大写字母转换为小写字母的串。大小写的更改只对字母起作用，数字、标点、和空格不会更改。

 

使用TRIM()删除串两端不需要的字符。可以修整前导字符、尾随字符、或者都修整。默认情况下修整空格。定义LEADING删除前导空格，定义TRAILING删除尾随空格。定义BOTH同时删除前导和尾随空格。

例：TRIM(LEADING FROM ‘   ABC   ’)  --  ‘ABC   ’;

TRIM(TRAILING FROM ‘   ABC   ’)  --  ‘   ABC’;

TRIM(BOTH FROM ‘   ABC   ’)  -- ‘ABC’;

如果不指定，默认为BOTH。

在DB2中，用于修整前导空格的修整函数是LTRIM(string),修整尾随空格的是RTRIM(string)。函数CHARACTER_LENGTH()返回串中字符的个数。

 

BIT_LENGTH(expr)：返回表达式中位的个数，BIT_LENGTH(B’01001011’)返回8。

OCTET_LENGTH(expr)：返回表达式中的字节个数，OCTET_LENGTH(B’10001101’)返回1。OCTET_LENGTH(‘ABC’)。

位统计和字节统计函数因DBMS而异。

 

函数POSITION()在给定串中定位一个特定子串。返回在串中子串第一次出现的起始位置。

串中不含有子串，POSITION将返回0。串比较是否区分大小写依赖于DBMS。

POSITION(‘ef’ IN ‘abcdef’)  -- 返回5。

 

SQL标准定义了函数OVERLAY以替换子串。

OVERLAY(‘AxxxEFG’ PLACING ‘bcd’ FROM 2 FOR 3) – 返回’AbcdEFG’。

 

函数EXTACT()将日期或时间间隔隔为单一的字段，并以数字形式返回。

EXTACT(field FROM date_or_time)

Field是YEAR、MONTH、DAY、HOUR、MINUTE、SECOND、TIMEZONE_HOUR、TIMEZONE_MINUTE。

EXTACT(YEAR FROM DATE ‘2011-01-12’) 返回数字2011。

 

CURRENT_DATE、CURRENT_TIME、CURRENT_TIMESTAMP从计算机系统时钟上获得当前日期和时间。

 

CURRENT_TIME、CURRENT_TIMESTAMP都可以接受精度参数，CURRENT_TIME(6)返回SECOND字段中有6位数字精度的当前时间。

 

使用函数CURRENT_USER标识数据库服务器中的活动用户。

 

函数CAST()将一种数据类型的表达式转换为另一种数据类型。

CAST(expr AS data_type)

如果浮点数超出DBMS允许的SMALLINT值的范围，将FLOAT转换为SMALLINT将会失败。从DATE到TIMESTAMP转换中，结果中的时间部分可能是00:00:00。

 

使用CASE计算条件值：

CASE有两种格式：简单格式和搜索格式。

简单格式：

SELECT price,

type,

CASE type   --以type为条件

  WHEN ‘A’ THEN price*1.1  --type值为A时，查询price*1.1

                WHEN ‘B’THEN price*0.1  --type值为B时，查询price*0.1

  ELSE price   --没有匹配则返回默认值price

END   --注意CASE以END结尾

FROM titles

如果没有ELSE子句，默认ELSE NULL。

 

搜索格式：

SELECT price,

type,

CASE

  WHEN type= ‘A’ THEN price*1.1  --type值为A时，查询price*1.1

                WHEN type= ‘B’THEN price*0.1  --type值为B时，查询price*0.1

  WHEN type> ‘C’THEN price*1.2  --type值大于C时，查询price*1.2

                ELSE price  --没有匹配则返回默认值price

END  --注意CASE以END结尾

FROM titles

 

当返回结果后，CASE可能计算剩余WHEN子句中的表达式，也可能不计算，这依赖于DBMS。

可以在SELECT、WHERE、ORDER BY子句中或任何允许使用表达式的位置，使用CASE。

 

使用COALESCE()检查空值：

通常被用于在结果中用特定值替代空值的显示，是搜索CASE的一般形式缩写。

COALESCE(expr1,expr2)

所有表达式需要是一样的类型，或者隐式转换为一样的类型。如果表达式1和表达式2都为空值，则COALESCE()返回空值。

例：COALESCE(state,’N/A’) 如果state为空，则返回’N/A’。

 

可以从不可以为空列中得到空值：

SELECT max(ID)  --ID列不能为空

FROM titles

WHERE au_name=’no this line’

 

使用NULLIF()比较表达式：

函数NULLIF()比较两个表达式，如果相等则返回空值，如果不等则返回第一个表达式：

NULL(expr1,expr2)

用于转换用户定义的不适用的值为空值。

例：SELECT males,females, males/NULLIF(females,0) AS ratio

FROM school_club

当females为0时，因为0不能做除数，所以转换为NULL。任何数除以NULL都得NULL。