各种各样的函数
函数的种类:
在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;
SELECT CURRENT_TIMESTAMP;
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;