6、函数、谓词、CASE表达式
6-1：各种各样的函数，根据用途函数可能分为算术函数、字符串函数、日期函数、转换函数和聚合函数。
6-1-1：函数和种类
1、算术函数：用来进行数值计算的函数
2、字符串函数：用来进行字符串操作的函数
3、日期函数：用来进行日期操作的函数
4、转换函数：用来转换数据类型和值的函数
5、聚合函数：用来进行数据聚合的函数·
6-1-2：算术函数
1、四则运算：+（加法）、-（减法）、*（乘法）、/(除法)
-- 创建SampleMath表 CREATE TABLE SampleMath( m NUMERIC(10,3), -- NUMERIC:全体位数据类型，小数位数 n INTEGER, p INTEGER );
-- 插入数据 BEGIN TRANSACTION; INSERT INTO SampleMath(m,n,p) VALUES(500,0,NULL); INSERT INTO SampleMath(m,n,p) VALUES(-180,0,NULL); INSERT INTO SampleMath(m,n,p) VALUES(NULL,NULL,NULL); INSERT INTO SampleMath(m,n,p) VALUES(NULL,7,3); INSERT INTO SampleMath(m,n,p) VALUES(NULL,5,2); INSERT INTO SampleMath(m,n,p) VALUES(NULL,4,NULL); INSERT INTO SampleMath(m,n,p) VALUES(8,NULL,3); INSERT INTO SampleMath(m,n,p) VALUES(2.27,1,NULL); INSERT INTO SampleMath(m,n,p) VALUES(5.555,2,NULL); INSERT INTO SampleMath(m,n,p) VALUES(NULL,1,NULL); INSERT INTO SampleMath(m,n,p) VALUES(8.76,NULL,NULL); COMMIT;
2、ABS绝对值函数
语法：ABS(数据)
SELECT m,ABS(m) AS abs_col FROM SampleMath;
ABS(NULL)的值也为NULL。
3、MOD求余函数
语法：MOD(被除数，除数)
SELECT n,p,MOD(n,p) AS mod_col FROM SampleMath;
4、ROUND四舍五入函数
语法：ROUND(对象数值，保留小数的位数)
SELECT m,n,ROUND(m,n) AS round_col FROM SampleMath;
5、字符串函数
-- 创建SampleStr表 CREATE TABLE SampleStr( str1 VARCHAR(40), str2 VARCHAR(40), str3 VARCHAR(40) );
-- 插入数据 BEGIN TRANSACTION; INSERT INTO SampleStr(str1,str2,str3) VALUES('opx','rt',NULL); INSERT INTO SampleStr(str1,str2,str3) VALUES('abc','def',NULL); INSERT INTO SampleStr(str1,str2,str3) VALUES('山田','太郎','是你'); INSERT INTO SampleStr(str1,str2,str3) VALUES('aaa',NULL,NULL); INSERT INTO SampleStr(str1,str2,str3) VALUES(NULL,'xyz',NULL); INSERT INTO SampleStr(str1,str2,str3) VALUES('@!#$%',NULL,NULL); INSERT INTO SampleStr(str1,str2,str3) VALUES('aBC',NULL,NULL); INSERT INTO SampleStr(str1,str2,str3) VALUES('abc太郎','abc','ABC'); INSERT INTO SampleStr(str1,str2,str3) VALUES('abdcefabc','abc','ABC'); INSERT INTO SampleStr(str1,str2,str3) VALUES('micmic','i','I'); COMMIT;
1、||拼接函数
语法：字符串1||字符串2(NULL与任务拼接都为NULL),MySQL使用CONCAT函数
SELECT str1,str2,str1||str2 AS str_concat --CONCAT(str1,str2) FROM SampleStr;
2、LENGTH函数
语法：LENGTH(字符串)
SELECT str1,LENGTH(str1) as len_str FROM SampleStr;
3、LOWER小写转换函数与UPPER大写转换函数
语法：LOWER（字符串） UPPER(字符串)
SELECT str1,LOWER(str1) AS low_str FROM SampleStr WHERE str1 IN ('ABC','aBC','abc','山田');
SELECT str1,UPPER(str1) AS up_str FROM SampleStr WHERE str1 IN ('ABC','aBC','abc','山田');
4、REPLACE字符串的替换
语法：REPLACE(对象字符串，替换前的字符串，替换后的字符串)
SELECT str1,str2,str3,REPLACE(str1,str2,str3) AS rep_str FROM SampleStr;
5、SUBSTRING字符串截取
语法：SUBSTRING(对象字符串， FROM 截取的起始位置 FOR 截取的字符数)
SELECT str1,SUBSTRING(str1 FROM 3 FOR 2) AS sub_str FROM SampleStr;
6、日期函数
1)CURRENT_DATE当前日期函数
语法：CURRENT_DATE
SELECT CURRENT_DATE;--Mysql\Postgresql
2)CURRENT_TIME当前时间函数
语法：CURRENT_TIME
SELECT CURRENT_TIME;--Mysql\Postgresql
3)CURRENT_TIMESTAMP当前日期和时间函数
语法：CURRENT_TIMESTAMP
SELECT CURRENT_TIMESTAMP;--Mysql\Postgresql
4)EXTRACT截取日期元素（返回为数值型）
语法：EXTRACT(日期元素 FROM 日期)
-- mysql\postgresql 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;
6、转换函数
1）CAST-类型转换
语法：CAST(转换前的值 AS 想要转换的数据类型)
--字符串转为数值类型
-- sql server\postgresql SELECT CAST('0001' AS INTEGER) AS int_col; -- mysql SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;
-- 字符串转为日期类型 -- SQL Server\Mysql SELECT CAST('2009-12-14' AS DATE) AS date_col;
2）COALESCE-把NULL转换为其他值
语法：COALESCE(数据1，数据2，数据3...)
SELECT COALESCE(NULL,1) AS col_1, COALESCE(NULL,'test',NULL) AS col_2, COALESCE(NULL,NULL,'2009-11-01');
SELECT COALESCE(str2,'NULL') FROM SampleStr;
6-2：谓词（返回值是真值）
1、谓词就是返回值为真值的函数
2、LIKE的三种方法（前方一致、中间一致、后方一致）
3、需要注意BETWEEN包含三个参数
4、想要取得NULL数据时间必须使用IS NULL
5、可以把子查询作为IN和EXISTS的参数
6-2-1：LIKE谓词-字符串的部分一致查询
-- 创建SampleLike表 CREATE TABLE SampleLike ( strcol VARCHAR(6) NOT NULL, PRIMARY KEY(strcol) ); -- 插入数据
BEGIN TRANSACTION;-- START TRACSACTION
INSERT INTO SampleLike(strcol) VALUES('abcddd'); INSERT INTO SampleLike(strcol) VALUES('dddabc'); INSERT INTO SampleLike(strcol) VALUES('abdddc'); INSERT INTO SampleLike(strcol) VALUES('abcdd'); INSERT INTO SampleLike(strcol) VALUES('ddabc'); INSERT INTO SampleLike(strcol) VALUES('abddc'); COMMIT;
1、前方一致查询(%:代表0字符以上的任意字符串， _(下划线)代表1个字符)
SELECT * FROM SampleLike WHERE strcol LIKE 'ddd%';
2、中间一致查询
SELECT * FROM SampleLike WHERE strcol LIKE '%ddd%';
2、后方一致查询
SELECT * FROM SampleLike WHERE strcol LIKE '%ddd';
-- abc后任意2个字符 SELECT * FROM SampleLike WHERE strcol LIKE 'abc__';
6-2-2：BETWEEN谓词-范围查询（3个参数）
-- 从shohin表中读出销售单价（hanbai_tanka）在100和1000之间的商品(包含100和1000两个值，如果不包含用<>) SELECT shohin_mei,hanbai_tanka FROM Shohin WHERE hanbai_tanka BETWEEN 100 AND 1000;
6-2-3：IS NULL\IS NOT NULL-判断是否为NULL
-- 选取出进货单价（shiire_tanka）为NULL的商品 SELECT shohin_mei,shiire_tanka FROM Shohin WHERE shiire_tanka IS NULL;
6-2-4：IN谓词-OR的简便用法(IN和NOT IN无法选取出NULL数据)
SELECT shohin_mei,shiire_tanka FROM Shohin -- WHERE shiire_tanka = 320 -- OR shiire_tanka = 500 -- OR shiire_tanka = 5000; WHERE shiire_tanka IN(320,500,5000);
6-2-5：使用子查询作为IN谓词的参数
6-2-6：EXIST谓词
6-2：CASE表达式
1、CASE表达式分为简碟CASE表达式和搜索CASE表达式两种。搜索CASE包含简碟CASE表达式全部功能
2、CASE表达式的ELSE子句可以省略，但最好不要
3、CASE表达式的END不参省略
4、使用CASE表达式能把SELECT语据的结果进行组合
6-2-1：搜索CASE表达式
语法：CASE WHEN <判断表达式> THEN<表达式>
WHEN <判断表达式> THEN<表达式>
.....
ELSE<表达式>
END
SELECT shohin_mei, CASE WHEN shohin_bunrui = '衣服' THEN 'A:'||shohin_bunrui WHEN shohin_bunrui = '办公用品' THEN 'B:'||shohin_bunrui WHEN shohin_bunrui = '厨房用具' THEN 'C:'||shohin_bunrui ELSE NULL END AS abc_shohin_bunrui FROM Shohin;
CASE表达式实现行列转换
SELECT shohin_bunrui,SUM(hanbai_tanka) AS sum_tanka FROM Shohin GROUP BY shohin_bunrui;
-- 对照商品种类计算出的销售单价合计值行行列换行 SELECT SUM(CASE WHEN shohin_bunrui = '衣服' THEN hanbai_tanka ELSE 0 END) AS sum_tanka_ihuku, SUM(CASE WHEN shohin_bunrui = '厨房用具' THEN hanbai_tanka ELSE 0 END) AS sum_tanka_kitchen, SUM(CASE WHEN shohin_bunrui = '办公用品' THEN hanbai_tanka ELSE 0 END) AS sum_tanka_jimu FROM Shohin;
