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

2018-04-18  本文已影响0人  los_pollos

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;
上一篇 下一篇

猜你喜欢

热点阅读