《SQL基础教程》学习笔记Ch6

6函数、谓词、CASE表达式

6-1各种各样的函数

函数的种类

函数：输入某一值得到相应输出结果的功能，输入值称为参数(parameter)，输出值称为返回值

●算术函数(用来进行数值计算的函数)
● 字符串函数(用来进行字符串操作的函数)
● 日期函数(用来进行日期操作的函数)
● 转换函数(用来转换数据类型和值的函数)
● 聚合函数(用来进行数据聚合的函数)

算术函数

-- DDL:创建表
CREATE TABLE SampleMath (m NUMERIC (10,3),
n INTEGER, p INTEGER);

-- DML:插入数据
BEGIN TRANSACTION;
postgres=# INSERT INTO SampleMath(m, n, p) VALUES(500, 0, NULL);
INSERT 0 1
postgres=# INSERT INTO SampleMath(m, n, p) VALUES(-180, 0, NULL);
INSERT 0 1
postgres=# INSERT INTO SampleMath(m, n, p) VALUES(NULL, NULL, NULL);
INSERT 0 1
postgres=# INSERT INTO SampleMath(m, n, p) VALUES(NULL, 7, 3);
INSERT 0 1
postgres=# INSERT INTO SampleMath(m, n, p) VALUES(NULL, 5, 2);
INSERT 0 1
postgres=# INSERT INTO SampleMath(m, n, p) VALUES(NULL, 4, NULL);
INSERT 0 1
postgres=# INSERT INTO SampleMath(m, n, p) VALUES(8, NULL, 3);
INSERT 0 1
postgres=# INSERT INTO SampleMath(m, n, p) VALUES(2.27, 1, NULL);
INSERT 0 1
postgres=# INSERT INTO SampleMath(m, n, p) VALUES(5.555, 2, NULL);
INSERT 0 1
postgres=# INSERT INTO SampleMath(m, n, p) VALUES(NULL, 1, NULL);
INSERT 0 1
postgres=# INSERT INTO SampleMath(m, n, p) VALUES(8.76, NULL, NULL);
INSERT 0 1
postgres=# COMMIT;

查看一下该表：

6-1.png

1ABS--绝对值

SELECT m,
postgres-# ABS(m) AS abs_col
postgres-# FROM SampleMath;

6-2.png

2MOD--求余

6-3.png

注意：此处书上代码少了第一行最后的逗号。
取余只能针对两个整数值而言。

3ROUND--四舍五入

6-4.png

ROUND函数用法：ROUND(对象数值，保留小数的位数）

字符串函数

首先建立表SampleStr

BEGIN TRANSACTION;
BEGIN
postgres=# INSERT INTO SampleStr (str1, str2, str3) VALUES('opx','rx',NULL);
INSERT 0 1
postgres=# INSERT INTO SampleStr (str1, str2, str3) VALUES('abc','def',NULL);
INSERT 0 1
postgres=# INSERT INTO SampleStr (str1, str2, str3) VALUES('山田','太郎','是我');
INSERT 0 1
postgres=# INSERT INTO SampleStr (str1, str2, str3) VALUES('aaa',NULL,NULL);
INSERT 0 1
postgres=# INSERT INTO SampleStr (str1, str2, str3) VALUES(NULL,'xyz',NULL);
INSERT 0 1
postgres=# INSERT INTO SampleStr (str1, str2, str3) VALUES('@&*%$',NULL,NULL);
INSERT 0 1
postgres=# INSERT INTO SampleStr (str1, str2, str3) VALUES('ABC',NULL,NULL);
INSERT 0 1
postgres=# INSERT INTO SampleStr (str1, str2, str3) VALUES('aBC',NULL,NULL);
INSERT 0 1
postgres=# INSERT INTO SampleStr (str1, str2, str3) VALUES('abc太郎','abc','ABC');
INSERT 0 1
postgres=# INSERT INTO SampleStr (str1, str2, str3) VALUES('abcdefabc','abc','ABC');
INSERT 0 1
postgres=# INSERT INTO SampleStr (str1, str2, str3) VALUES('micmic','i','I');
INSERT 0 1
postgres=# COMMIT;

SELECT * FROM SampleStr;

观察表SampleStr:

6-5.png

1||--拼接

SELECT str1, str2,
        str1 || str2 AS str_concat
   FROM SampleStr;

6-6.png

也可以把三个字符串同时拼接：

6-7.png

2LENGTH--字符串长度

SELECT str1,
       length(str1) AS len_str
  FROM SampleStr;

3LOWER--小写转换

LOWER函数只能针对英文字母的情况

SELECT str1,
       LOWER(str1) AS low_str
  FROM SampleStr;

4REPLACE--字符串的替换

REPLACE(str1, str2, str3) AS rep_str：对象字符串str1，需要替换的字符串str2，替换后的字符串str3

6-8.png

5SUBSTRING--字符串的截取

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

6-9.png

6UPPER--大写转换

UPPER函数仍然只针对英文字母使用

6-10.png

日期函数

1CURRENT_DATE--当前日期

SELECT CURRENT_DATE;

2CURRENT_TIME--当前时间

SELECT CURRENT_TIME;

3EXTRACT--截取日期元素

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-11.png

转换函数

1CAST--类型转换

将字符串类型转换成数值类型：

6-12.png

将字符串类型转换成日期类型：

6-13.png

2COALESCE--将NULL转换成其他值

6-14.png

另一个例子，把空值替换为字符串‘NULL’

6-14.png

6-2谓词

谓词是函数的一种，但是它的特点是：返回值一定是真值（TRUE,FALSE,UNKNOWN)

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

首先我们先创建一个表

--创建SampleLike表
-- DDL:创建表
CREATE TABLE SampleLike
( strcol VARCHAR(6) NOT NULL,
PRIMARY KEY (strcol));
-- DML:插入数据
BEGIN TRANSACTION; 
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;

想要从该表中读取出包含字符串“ddd”的记录时，可能会得到前方一致、中间一致和后方一致等不同的结果。

前方一致 :选取出“dddabc”
中间一致 :选取出“abcddd”“dddabc”“abdddc”
后方一致 :选取出“abcddd”

使用LIKE进行前方一致查询：

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

使用LIKE进行中间一致查询：

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

使用LIKE进行后方一致查询：

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

我们还可以使用 _(下划线)来代替 %，与 % 不同的是，它代表了“任意 1 个字符”

使用LIKE和_（下划线）进行后方一致查询：

SELECT *
  FROM SampleLike
 WHERE strcol LIKE 'abc__';   --这里是两个下划线

6-15.png

上述代码只能取出后方有两个字符的结果，同样地，我们也可以查询出‘abc+三个字符'的结果：

SELECT *
FROM SampleLike
WHERE strcol LIKE 'abc___';  --这里是三个下划线

BETWEEN谓词--范围查询

6-16.png

BETWEEN 的特点就是结果中会包含 100 和 1000 这两个临界值。如果不想让结果中包含临界值，那就必须使用WHERE：

6-17.png

IS NULL,IS NOT NULL--判断是否为NULL

6-18.png

IN谓词--OR的简便用法

我们需查询单价为320，500和5000的商品，可以使用OR：

6-19.png

使用IN来简化：

6-20.png

NOT IN的用法：

6-21.png

但需要注意的是，IN和NOT IN都是无法取出NULL数据的，只能用 IS (NOT) NULL。

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

我们先建立下面这个表：

6-22.png

 CREATE TABLE ShopProduct 
(shop_id CHAR(4) NOT NULL, 
 shop_name VARCHAR(200) NOT NULL,
 product_id CHAR(4) NOT NULL, 
 quantity INTEGER NOT NULL,
 PRIMARY KEY (shop_id, product_id));
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES('000A','东京','0001',30);
INSERT 0 1
postgres=# INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES('000A','东京','0002',50);
INSERT 0 1
postgres=# INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES('000A','东京','0003',15);
INSERT 0 1
postgres=# INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES('000B','名古屋','0002',30);
INSERT 0 1
postgres=# INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES('000B','名古屋','0003',120);
INSERT 0 1
postgres=# INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES('000B','名古屋','0004',20);
INSERT 0 1
postgres=# INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES('000B','名古屋','0006',10);
INSERT 0 1
postgres=# INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES('000B','名古屋','0007',40);
INSERT 0 1
postgres=# INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES('000C','大阪','0003',20);
INSERT 0 1
postgres=# INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES('000C','大阪','0004',50);
INSERT 0 1
postgres=# INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES('000C','大阪','0006',90);
INSERT 0 1
postgres=# INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES('000C','大阪','0007',70);
INSERT 0 1
postgres=# INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES('000D','福冈','0001',100);
INSERT 0 1

我们希望读取的是大阪店在售商品的商品名称及销售单价：

6-23.png

NOT IN也可以用在子查询中：

6-23.png

EXIST谓词

谓词的作用就是“判断是否存在满足某种条件的记录”。如果存在这样的记录就返回真(TRUE)，如果不存在就返回假(FALSE)。 EXIST(存在)谓词的主语是“记录”。
使用 EXIST 选取出大阪店在售商品的销售单价：

6-24.png

由于 EXIST 只关心记录是否存在，因此返回哪些列都没有关系。即使写成下列代码那样，结果也不会发生改变。可以把在 EXIST 的子查询中书写 SELECT * 当作 SQL 的一种习惯。

SELECT product_name, sale_price 
  FROM Product AS P
 WHERE EXISTS (SELECT 1--此处为任意常数
                 FROM ShopProduct AS SP
                WHERE SP.shop_id = '000C'
                  AND SP.product_id = P.product_id);

同样可以使用NOT EXIST代替NOT IN，使用NOT EXIST读取出“东京店在售之外的商品的销售单价”：

6-25.png

6-3CASE表达式

什么是CASE表达式

CASE 表达式是在区分情况时使用的，这种情况的区分在编程中通常称为(条件)分支。

CASE表达式的使用方法

对于Product表，我们希望得到如下结果：

6-26.png

---使用搜索CASE表达式的写法--
SELECT product_name,
       CASE WHEN product_type = '衣服'
            THEN 'A:'||product_type
            WHEN product_type = '办公用品'
            THEN 'B:'||product_type
            WHEN product_type = '厨房用具'
            THEN 'C:'||product_type
            ELSE NULL
       END AS abc_product_type
FROM Product;

--使用简单CASE表达式的写法--
SELECT product_name,
       CASE product_type
            WHEN '衣服' THEN 'A:'||product_type
            WHEN '办公用品' THEN 'B:'||product_type 
            WHEN '厨房用具' THEN 'C:'||product_type
            ELSE NULL
       END AS abc_product_type
FROM Product;

6-27.png

ELSE子句也可以省略不写，这时会被默认为ELSE NULL。此外，CASE 表达式最后的“END”是不能省略的，请大家特别注意不要遗漏。
可以利用 CASE 表达式将下述 SELECT 语句结果中的行和列进行互换。

SELECT product_type,
       SUM(sale_price) AS sum_price,
  FROM Product
 GROUP BY product_type;

SELECT SUM(CASE WHEN product_type = '衣服'
                THEN sale_price ELSE 0 END) AS sum_price_clothes,
       SUM(CASE WHEN product_type = '厨房用具'
                THEN sale_price ELSE 0 END) AS sum_price_kitchen,
       SUM(CASE WHEN product_type = '办公用品'
                THEN sale_price ELSE 0 END) AS sum_price_office
FROM Product;

最后编辑于：2018.05.31 17:31:03

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 203,362评论 5赞 477
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 85,330评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 150,247评论 0赞 337
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,560评论 1赞 273
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,580评论 5赞 365
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,569评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,929评论 3赞 395
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,587评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,840评论 1赞 297
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,596评论 2赞 321
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,678评论 1赞 329
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,366评论 4赞 318
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,945评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,929评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,165评论 1赞 259
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 43,271评论 2赞 349
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,403评论 2赞 342