SQL基础学习6

各种各样的函数

函数的种类：

在SQL中函数大致可分为以下几种：算术函数、字符串函数、日期函数、转换函数、聚合函数

算术函数：大多数函数对NULL（只有有一个参数为NULL）的结果都为NULL

+（加法）、-（减法）、*（乘法）、/（除法）

ABS（列名）--求绝对值：不考虑数值符号，表示一个数到原点距离的数值。

MOD（被除数列名，除数列名）--除法余数的函数：只能对整数类型的列使用，SQL Server不支持该函数

ROUND（对象数值，保留小数的位数）--四舍五入

字符串函数：如果函数参数中包含NULL，其结果都为NULL

（字符串1）||（字符串2）--字符串拼接：可以对两个或两个以上的字符串进行拼接

LENGTH（字符串）--字符串长度：求取字符串中包含多少个字符

LOWER（字符串）--小写转换：只能针对英文字母使用，将参数中的字符串全部转换为小写，并不影响原本就是小写的字符

UPPER（字符串）--大写转换：只能针对英文字母使用，将参数中的字符串全部转换为大写，并不影响原本就是大写的字符

REPLACE（对象字符串，替换前的字符串，替换后的字符串）--字符替换：将字符串中的一部分替换为其它的字符串

SUBSTRING（对象字符串 FROM 截取的起始位置 FOR 截取的字符数）--字符串截取：截取的位置从左侧开始计算

日期函数：

CURRENT_DATE--当前日期：能够返回SQL执行的日期，没有参数

SELECT CURRENT_DATE;

CURRENT_TIME--当前时间：能够取得SQL执行的时间，没有参数

SELECT CURRENT_TIME;

CURRENT_TIMESTAMP--当前日期和时间：同时得到当前的日期和时，没有参数

SELECT CURRENT_TIMESTAMP;

EXTRACT(日期元素 FROM 日期)--截取日期元素：可以截取出日期数据中的一部分

SELECT CURRENT_TIMESTAMP,
EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS year,
EXTRACT(MONTH FROM CURRENT_TIMESTAMP) AS month,
EXTRACT(DAY FROM CURRENT_TIMESTAMP) AS day,
EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS hour,
EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS minute,
EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS second;

转换函数：一是数据类型的转换，二是值的转换

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

SELECT CAST('0001' AS INTEGER) AS int_col;

COALESCE(数据1,数据2,数据3, ...)--将NULL转换为其它值：返回可变参数中左侧开始第1个不是NULL的值

谓词

何为谓词：满足返回值是真值的函数

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

SELECT *
FROM SampleLike
WHERE strcol LIKE 'dd%';

解释：从表SampleLike中选出开头为“dd”的字符串；其中“%”表示 0字符以上的任意字符串

SELECT *
FROM SampleLike
WHERE strcol LIKE '_dd';

解释：从表SampleLike中选出开头为任意一个字符且以“dd”结尾的字符串；其中“_”表示 任意1个字符

BETWEEN谓词——范围查询：需要使用三个参数

SELECT name,price
FROM Shop
WHERE price BETWEEN 100 AND 1000;

解释：从表Shop中选出price在100~1000之间的产品信息（结果中 包含临界值100和1000）；若 不想包含临界值必须使用<和>

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

SELECT name,price
FROM Shop
WHERE price IS NULL;

解释：从表Shop中选出price为NULL的产品信息

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

SELECT name,price
FROM Shop
WHERE price IN (100,200,300);

解释：从表Shop中选出price为100、200、300的产品信息；若要选出price 不是100、200、300的产品信息，可以 使用否定形式 NOT IN(NOT IN 代表了“以外”这样的否定含义)

P.S：使用IN和NOT IN 是无法选取出NULLL数据的

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

IN/NOT IN 和子查询：能够将表作为IN的参数，也可以认为“能够将视图作为IN的参数”

EXIST谓词：基本上可以使用IN（或者NOT IN）来代替

SELECT name,price
FROM Shop AS S
WHERE EXISTS (SELECT *
FROM Producer AS P
WHERE P.code='000C'
AND P.name=S.name);

解释：从表Producer中选出code为“000C”，且在表Producer和表Shop的name相同的产品信息

EXIST谓词的使用方法：判断是否存在满足某种条件的记录，若存在则返回真，若不存在则返回为假

EXIST的参数：左侧并没有任何参数，右侧通常是一个子查询（关联子查询）

子查询中的SELECT *：在EXIST的子查询中书写SELECT *为SQL的一种习惯

使用NOT EXIST替换NOT IN：当不存在满足子查询中指定条件的记录时返回真

CASE表达式

何为CASE表达式：一种表示（条件）分歧的函数

CASE表达式的语法：分为简单CASE表达式和搜索CASE表达式（搜索CASE包含了简单CASE的全部功能）

--搜索CASE表达式
CASE WHEN 判断表达式 THEN 表达式
     WHEN 判断表达式 THEN 表达式
     ...
     ELSE 表达式
END

解释：WHEN子句中的判断表达式就是类似“列=值”这样，返回值为真值的表达式；

CASE表达式从最初的WHEN子句中的判断表达式进行判断开始执行。若判断为真，则返回THEN子句的表达式，CASE表达式的执行到此为止；若判断为假，则跳转到下一条WHEN子句的判断之中。如果直到最后的WHEN子句为止返回结果都为假，则返回ELSE中的表达式，执行终止。

ELSE子句可以省略不写，自动默认为“ELSE NULL”

CASE表达式的书写位置：可以是任意位置

SELECT name
CASE 
WHEN class='衣服' THEN 'A:' || class
WHEN class='办公用品' THEN 'B:' || class
WHEN class='厨房用具' THEN 'C:' || class
ELSE NULL
END AS abc_class
FROM Shop;