SQL 的简单教程(Mac 下 PostgreSQL 的安装与使用)(2)

妖狐艹你老母 2021-05-14 11:34 444阅读 0赞

## 四、数据更新 ##

### 4-1 数据的插入(INSERT 语句的使用方法) ###

*  使用INSERT语句可以向表中插入数据(行)。原则上，INSERT语句每 次执行一行数据的插入。
 *  将列名和值用逗号隔开，分别括在()内，这种形式称为清单。
 *  对表中所有列进行INSERT操作时可以省略表名后的列清单。
 *  插入NULL时需要在VALUES子句的值清单中写入NULL。
 *  可以为表中的列设定默认值(初始值)，默认值可以通过在CREATE TABLE语句中为列设置 DEFAULT 约束来设定。
 *  插入默认值可以通过两种方式实现，即在INSERT语句的VALUES子句中指定 DEFAULT 关键字(显式方法)，或省略列清单(隐式方法)。
 *  使用INSERT…SELECT可以从其他表中复制数据。

什么是 INSERT

-- 创建ProductIns表的CREATE TABLE语句
    CREATE TABLE "public"."productins" (
      "product_id" char(4) COLLATE "pg_catalog"."default" NOT NULL,
      "product_name" varchar(100) COLLATE "pg_catalog"."default" NOT NULL,
      "product_type" varchar(32) COLLATE "pg_catalog"."default" NOT NULL,
      "sale_price" int4 DEFAULT 0,
      "purchase_price" int4,
      "regist_date" date,
      CONSTRAINT "productins_pkey" PRIMARY KEY ("product_id")
    );

INSERT 语句的基本语法

INSERT INTO <表名> (列1, 列2, 列3, ......) VALUES (值1, 值2, 值3, ......);
    -- 向表中插入一行数据
    INSERT INTO 
    ProductIns 
    (product_id,product_name,product_type,sale_price,purchase_price,regist_date) 
    VALUES 
    ('0001', 'T恤衫','衣服', 1000, 500, '2009-09-20');
    -- 多次执行 INSERT 语句
    INSERT INTO ProductIns VALUES ('0002', '打孔器','办公用品', 500, 320, '2009-09-11');
    INSERT INTO ProductIns VALUES ('0003', '运动T恤','衣服', 4000, 2800, NULL);
    INSERT INTO ProductIns VALUES ('0004', '菜刀','厨房用具', 3000, 2800, '2009-09-20');
    
    -- 多行INSERT(Oracle以外)
    INSERT INTO ProductIns VALUES 
    ('0002', '打孔器','办公用品', 500, 320, '2009-09-11'),
    ('0003', '运动T恤','衣服', 4000, 2800, NULL),
    ('0004', '菜刀','厨房用具', 3000, 2800, '2009-09-20');

列清单的省略

-- 包含列清单
    INSERT INTO 
    ProductIns
    (product_id, product_name, product_type, sale_price, purchase_price, regist_date) 
    VALUES
    ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
     	
    -- 省略列清单
    INSERT INTO 
    ProductIns
    VALUES
    ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');

插入 NULL

-- 向 purchase_price 列中插入 NULL
    INSERT INTO 
    ProductIns 
    (product_id,product_name,product_type,sale_price,purchase_price, regist_date) 
    VALUES 
    ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');

插入默认值

-- 通过显式方法插入默认值 ---- 在 VALUES 子句中指定 DEFAULT 关键字
    INSERT INTO ProductIns 
    (product_id, product_name, product_type, sale_price, purchase_price, regist_date) 
    VALUES 
    ('0007',   '擦菜板', '厨房用具', DEFAULT, 790, '2009-04-28');
    
    -- 通过隐式方法插入默认值
     INSERT INTO 
     ProductIns 
     (product_id, product_name, product_type,purchase_price, regist_date) 
     VALUES 
     ('0007', '擦菜板', '厨房用具',790, '2009-04-28');

从其他表中复制数据

-- 创建ProductCopy表的CREATE TABLE语句
    CREATE TABLE ProductCopy
    (
      "product_id" int4 NOT NULL,
      "product_name" varchar(100) COLLATE "pg_catalog"."default" NOT NULL DEFAULT ''::character varying,
      "product_type" varchar(32) COLLATE "pg_catalog"."default" NOT NULL DEFAULT ''::character varying,
      "sale_price" numeric(10,2) NOT NULL DEFAULT 0,
      "purchase_price" numeric(10,2) DEFAULT NULL::numeric,
      "regist_date" date,
      PRIMARY KEY ("product_id")
    );
     
    -- 从 Product 表中复制数据插入 ProductCopy 表中
    INSERT INTO ProductCopy 
    (product_id, product_name, product_type, sale_price, purchase_price, regist_date)
    SELECT 
    product_id, product_name, product_type, sale_price,  purchase_price, regist_date
    FROM Product;

各种各样的 select 语句

-- 创建ProductType表的CREATE TABLE语句
    CREATE TABLE "public"."producttype" (
      "product_type" varchar(32) COLLATE "pg_catalog"."default" NOT NULL,
      "sum_sale_price" int4,
      "sum_purchase_price" int4,
      CONSTRAINT "producttype_pkey" PRIMARY KEY ("product_type")
    );
    
    -- 插入其他表中数据合计值的INSERT ... SELECT语句
    INSERT INTO ProductType 
    (product_type,sum_sale_price,sum_purchase_price)
    SELECT 
    product_type,SUM(sale_price),SUM(purchase_price)
    FROM Product
    GROUP BY product_type;

### 4-2 数据的删除(DELETE 语句的使用方法) ###

*  如果想将整个表全部删除，可以使用 DROP TABLE 语句，如果只想删除 表中全部数据，需使用 DELETE 语句。
 *  如果想删除部分数据行，只需在WHERE子句中书写对象数据的条件即可。 通过 WHERE 子句指定删除对象的 DELETE 语句称为搜索型 DELETE 语句。

DROP TABLE 语句和 DELETE 语句

-- DROP TABLE 语句可以将表完全删除
    -- DELETE 语句会留下表(容器)，而删除表中的全部数据

***不管使用哪种方法，删除数据时都 要慎重，一旦误删，想要恢复数据就会变得十分困难***

DELETE 语句的基本语法

-- 保留数据表，仅删除全部数据行的 DELETE 语句
    DELETE FROM <表名>;
    
    -- 清空 Product 表
    DELETE FROM Product;

DELETE 语句常见错误  
![DELETE 语句常见错误][DELETE]

指定删除对象的 DELETE 语句(搜索型 DELETE)

DELETE FROM <表名> WHERE <条件>;
    
    -- 删除销售单价(sale_price)大于等于4000日元的数据
    DELETE FROM 
    Product
    WHERE sale_price >= 4000;

清空表–截断表

TRUNCATE

![TRUNCATE][]

### 4-3 数据的更新(UPDATE 语句的使用方法) ###

*  使用UPDATE语句可以更改(更新)表中的数据。
 *  更新部分数据行时可以使用WHERE来指定更新对象的条件。通过WHERE  
    子句指定更新对象的 UPDATE 语句称为搜索型 UPDATE 语句。
 *  UPDATE 语句可以将列的值更新为 NULL。
 *  同时更新多列时，可以在UPDATE语句的SET子句中，使用逗号分隔更  
    新对象的多个列。

UPDATE 语句的基本语法

UPDATE <表名> SET <列名> = <表达式>;

指定条件的 UPDATE 语句(搜索型 UPDATE)

-- 更新部分数据行的搜索型 UPDATE
    UPDATE <表名>
    SET <列名> = <表达式>
    WHERE <条件>;
    
    -- 将商品种类为厨房用具的记录的销售单价更新为原来的 10 倍
    UPDATE Product
    SET sale_price = sale_price * 10
    WHERE product_type = '厨房用具';

使用 NULL 进行更新

-- 使用 UPDATE 也可以将列更新为 NULL(该更新俗称为 NULL 清空)。
    -- 将商品编号为8的数据(圆珠笔)的登记日期更新为 NULL
    UPDATE Product
    SET regist_date = NULL
    WHERE product_id = '8';

多列更新

-- 能够正确执行的繁琐的 UPDATE 语句
    -- 一条UPDATE语句只更新一列 
    UPDATE Product
    SET sale_price = sale_price * 10 
    WHERE product_type = '厨房用具';
    
    UPDATE Product
    SET purchase_price = purchase_price / 2
    WHERE product_type = '厨房用具';
    
    
    
    -- 使用逗号对列进行分隔排列 
    UPDATE Product
    SET sale_price = sale_price * 10, purchase_price = purchase_price / 2
    WHERE product_type = '厨房用具';
    -- 将列用()括起来的清单形式 
    UPDATE Product
    SET (sale_price, purchase_price) = (sale_price * 10, purchase_price / 2)
    WHERE product_type = '厨房用具';

### 4-4 事务 ###

*  事务是需要在同一个处理单元中执行的一系列更新处理的集合。通过使用 事务，可以对数据库中的数据更新处理的提交和取消进行管理。
 *  事务处理的终止指令包括COMMIT(提交处理)和ROLLBACK(取消处 理)两种。
 *  DBMS的事务具有:

原子性(Atomicity)、一致性(Consistency)、隔离性 (Isolation)和持久性(Durability)四种特性。

*  通常将这四种特性的首字母结合起来，统称为 ACID 特性。

什么是事务

在 RDBMS 中，事务是对表中数据进行更新的单位。
    简单来讲，事务就是需要在同一个处理单元中执行的一系列更新处理的集合。

创建事务

-- 事务的语法
    事务开始语句 ;
    	DML 语句1 ; 
    	DML 语句2 ; 
    	DML 语句3 ;
    		.
    		.
    		.
    事务结束语句(COMMIT 或者 ROLLBACK);
    -- 事务的结束语句只有 commit 和 rollback 这两种,所有的 DBMS 都是通用的
    
    -- 事务的开始语句，而是由各个 DBMS 自己来定义的
    ● SQL Server、PostgreSQL
    	BEGIN TRANSACTION
    	
    ● MySQL
    	START TRANSACTION
    	
    ● Oracle、DB2
    	无

事务举例

-- 更新商品信息的事务
    -- SQL Server PostgreSQL
    BEGIN TRANSACTION;
    	-- 将运动T恤的销售单价降低1000日元 
    	UPDATE Product
    	SET sale_price = sale_price WHERE product_name = '运动T恤';
    	-- 将T恤衫的销售单价上浮1000日元 
    	UPDATE Product
    	SET sale_price = sale_price WHERE product_name = 'T恤衫';
    COMMIT;
    
    -- MySQL
    START TRANSACTION;
    	-- 将运动T恤的销售单价降低1000日元 
    	UPDATE Product
    	SET sale_price = sale_price WHERE product_name = '运动T恤';
    	-- 将T恤衫的销售单价上浮1000日元 
    	UPDATE Product
    	SET sale_price = sale_price WHERE product_name = 'T恤衫';
    COMMIT;
    
    -- Oracle DB2 没有事务开始语句
    	-- 将运动T恤的销售单价降低1000日元
    	UPDATE Product
    	SET sale_price = sale_price - 1000 WHERE product_name = '运动T恤';
    	-- 将T恤衫的销售单价上浮1000日元 
    	UPDATE Product
    	SET sale_price = sale_price + 1000 WHERE product_name = 'T恤衫';
    COMMIT;

***各个 DBMS 事务的开始语句都不尽相同，其中 Oracle 和 DB2 并没有定义特定的开始语句。可能大家觉得这样的设计很巧妙，其实是因为标准 SQL中规定了一种悄悄开始事务处理 A 的方法. 《标准SQL手册 修订第4版》中的记述 :希望大家注意事务默认 开 始 的 时 间 点 。 没有“ BEGINTRANSACTION”这样明确的开始标志。反之，事务的结束需要用户明确地给出指示***

结束事务的两种指令

commit: 提交处理
    	一旦提交无法恢复到事务开始前的状态了,因此,在提交之前一定要确认是否真的需要进行这些事务执行的修改
    ROLLBACK: 取消处理
    	一旦回滚，数据库就会恢复到事务开始之前的状态。通常回滚并不会像提交那样造成大规模的数据损失。

![COMMIT 的流程 = 直线进行][COMMIT _ _]![ROLLBACK 的流程 = 掉头回到起点][ROLLBACK _ _]

![事务开始时间][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1aml6aGlzaHVp_size_16_color_FFFFFF_t_70]

ACID 特性

原子性(Atomicity):
    	原子性是指在事务结束时，其中所包含的更新处理要么全部执行，要么完全不执行，也就是要么占有一切要么一无所有
    	
    一致性(Consistency):
    	一致性指的是事务中包含的处理要满足数据库提前设置的约束，如主键约束或者 NOT NULL 约束等。
    	
    隔离性(Isolation):
    	隔离性指的是保证不同事务之间互不干扰的特性
    	
    持久性(Durability):
    	指的是在事务(不论是提交还是回滚)结束后，DBMS 能够保证该时间点的数据状态会被保存的特性
    	保证持久性的方法根据实现的不同而不同，其中最常见的就是将事务的执行记录保存到硬盘等存储介质中(该执行记录称为日志)。当发生故障时,可以通过日志恢复到故障发生前的状态。

## 五、复杂查询 ##

### 视图 ###

*  从SQL的角度来看，视图和表是相同的，两者的区别在于表中保存的是实 际的数据，而视图中保存的是 SELECT 语句(视图本身并不存储数据)。
 *  使用视图，可以轻松完成跨多表查询数据等复杂操作。
 *  可以将常用的SELECT语句做成视图来使用。
 *  创建视图需要使用CREATE VIEW语句。
 *  视图包含“不能使用ORDER BY”和“可对其进行有限制的更新”两项限制。
 *  删除视图需要使用DROP VIEW语句。

视图和表

视图和表的区别就是是否保存了实际的数据
    
    实际上视图保存的是 SELECT 语句。
     我们从视图中读取数据时，视图会在内部执行该 SELECT 语句并创建出 一张临时表。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1aml6aGlzaHVp_size_16_color_FFFFFF_t_70 1]  
视图的优点

1. 无需保存数据,因此可以节省存储设备的容量;
    2. 可以将频繁使用的 SELECT 语句保存成视图，这样就不用每次都重新书写了

创建视图的方法

CREATE VIEW 
    视图名称(<视图列名1>, <视图列名2>, ......) 
    AS
    <SELECT 语句 >
    
    -- 创建 productsum 视图
    create view 
    productsum(product_type,cnt_product) 
    as 
    select product_type,count(*) from product GROUP BY product_type;
    
    -- 使用 productsum 视图 
    select product_type,cnt_product from productsum;
    
    -- 已视图为基础创建视图的多重视图
    CREATE VIEW ProductSumJim (product_type, cnt_product)
    AS
    SELECT product_type, cnt_product
      FROM ProductSum
     WHERE product_type = '厨房用具';
     
    SELECT product_type, cnt_product
      FROM ProductSumJim;

![可以在视图的基础上创建视图][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1aml6aGlzaHVp_size_16_color_FFFFFF_t_70 2]  
***不建议使用多重视图,会降低 SQL 的性能***

视图的限制1——定义视图时不能使用ORDER BY子句

因为视图和表一样，数据 行都是没有顺序的。
    实际上，有些 DBMS 在定义视图的语句中是可以使 用ORDER BY子句的A，但是这并不是通用的语法。
    因此，在定义视图 时请不要使用ORDER BY子句。
    
    PostgerSql 支持 order by,但是还是建议不要使用

视图的限制2 ——对视图进行更新

INSERT INTO ProductSum VALUES ('电器制品', 5); 
    -- 该更新会失败,原因是视图归根结底还是从表派生出来的,因此,如果原表可以更新,那么视图中的数据也可以更新
    -- 反之亦然,如果视图发生改变,而原表没有进行相应更新的话,就无法保证数据的一致性.

![通过汇总得到的视图无法更新][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1aml6aGlzaHVp_size_16_color_FFFFFF_t_70 3]  
能够更新视图的情况

CREATE VIEW 
    ProductJim(product_id,product_name,product_type,sale_price,purchase_price,regist_date)
    AS
    SELECT *
    FROM Product 
    WHERE product_type = '办公用品';
    
    -- 向视图中添加数据行
    INSERT INTO ProductJim VALUES ('0009','印章','办公用品',95,10,'2009-11-30');
    
    -- 注意:由于PostgreSQL中的视图会被初始设定为只读，所以执行上面添加数据的代码时，会发生错误。
    -- 这种情况下，在 INSERT 语句执行之前，需要使用下面的指令来允许更新操作。在 DB2 和 MySQL 等其他 DBMS 中，并不需要执行这样的指令。
    CREATE OR REPLACE RULE insert_rule 
    AS ON INSERT
    TO ProductJim DO INSTEAD
    INSERT INTO Product VALUES (
    new.product_id,
    new.product_name, 
    new.product_type, 
    new.sale_price, 
    new.purchase_price, 
    new.regist_date);
    -- UPDATE 语句和 DELETE 语句当然也可以像操作表时那样正常执行，但是对于原表来说却需要设置各种各样的约束(主键和NOT NULL等)，需要特别注意。

***不知道是不是版本的原因.我的测试数据库可以直接更新***

删除视图

DROP VIEW 视图名称(<视图列名1>, <视图列名2>, ......)
    
    -- 删除ProductSum
    DROP VIEW ProductSum;
    -- ERROR: cannot drop view productsum because other objects depend on it
    
    -- 解决方法
    DROP VIEW ProductSum CASCADE;

### 子查询 ###

*  一言以蔽之，子查询就是一次性视图(SELECT语句)。
 *  与视图不同，子查 询在 SELECT 语句执行完毕之后就会消失。
 *  由于子查询需要命名，因此需要根据处理内容来指定恰当的名称。
 *  标量子查询就是只能返回一行一列的子查询。

子查询和视图

子查询的特点概括起来就是一张一次性视图。
    
    视图并不是用来保存数据的，而是通过保存读取数据的 SELECT 语句的方法来为用户提供便利。
    
    子查询就是将用来定义视图的 SELECT 语句直接用于 FROM 子句当中。

视图和子查询进行一番比较

CREATE VIEW ProductSum (product_type, cnt_product) AS
    SELECT product_type, COUNT(*)
    FROM Product
    GROUP BY product_type;
    
    -- 确认创建好的视图
    SELECT product_type, cnt_product
    FROM ProductSum;
    
    -- 子查询
    SELECT product_type, cnt_product
    FROM(
    	SELECT product_type, COUNT(*) AS cnt_product
    	FROM Product
    	GROUP BY product_type
    ) AS ProductSum;
    
    -- 在 Oracle 的 FROM 子句中，不能使用 AS(会发生错误)，因此，在 Oracle 中执行上述代码时，需要将1中的“) AS ProductSum;”变为“) ProductSum;”。

![SELECT 语句的执行顺序][SELECT]  
增加子查询的层数

-- 由于子查询的层数原则上没有限制，
    -- 因此可以像“子查询的 FROM 子句中还可以继续使用子查询，
    -- 该子查询的 FROM 子句中还可以再使用子查 询......”
    -- 这样无限嵌套下去
    SELECT product_type, cnt_product
    from(
    	SELECT product_type, cnt_product
    	FROM(
    		SELECT product_type, COUNT(*) AS cnt_product
    		FROM Product
    		GROUP BY product_type
    	) AS ProductSum
    	where cnt_product = 4
    ) as productsum2;
    
    -- 但是，随着子查询嵌套层数的增加，SQL 语句会变得越来越难读懂，性能也会越来越差。
    -- 因此，请大家尽量避免使用多层嵌套的子查询。

子查询的名称

-- 原则上子查询必须设定名称，因此请大家尽量从处理内容的角度出发为子查询设定恰当的名称

标量子查询

什么是标量

标量就是单一的意思
    
    而标量子查询则有一个特殊的限制，那就是必须而且只能返回 1 行 1列的结果，也就是返回表中某一行的某一列的值，例如“10”或者“北京” 这样的值

在 WHERE 子句中使用标量子查询

-- 查询出销售单价高于平均销售单价的商品。
    SELECT product_id, product_name, sale_price
    FROM Product
    WHERE sale_price > (SELECT AVG(sale_price) FROM Product);

![SELECT 语句的执行顺序(标量子查询)][SELECT 1]

标量子查询的书写位置

标量子查询的书写位置并不仅仅局限于 WHERE 子句中，
    通常任何可 以使用单一值的位置都可以使用。
    也就是说，能够使用常数或者列名的地方，
    无论是 SELECT 子句、GROUP BY 子句、HAVING 子句，还是 ORDER BY子句，
    几乎所有的地方都可以使用

在 SELECT 子句中使用标量子查询

SELECT 
    	product_id, product_name,sale_price,(
    	SELECT AVG(sale_price) FROM Product
    	) AS avg_price 
    FROM Product;

在 HAVING 子句中使用标量子查询

SELECT 
    	product_type, AVG(sale_price) 
    FROM Product
    GROUP BY 
    	product_type
    HAVING 
    	AVG(sale_price)> (SELECT AVG(sale_price) FROM Product);

使用标量子查询时的注意事项

该子查询绝对不能返回多行结果

### 关联子查询 ###

*  关联子查询会在细分的组内进行比较时使用。
 *  关联子查询和GROUP BY子句一样，也可以对表中的数据进行切分。
 *  关联子查询的结合条件如果未出现在子查询之中就会发生错误

普通的子查询和关联子查询的区别

*  选取出各 商品种类中高于该商品种类的平均销售单价的商品

-- 按照商品种类计算平均价格
    SELECT AVG(sale_price) 
    FROM Product
    GROUP BY product_type;
    
    -- 通过关联子查询按照商品种类对平均销售单价进行比较
    SELECT product_type, product_name, sale_price
    FROM Product AS P1 
    WHERE sale_price > 
    (SELECT AVG(sale_price) FROM Product AS P2
    WHERE P1.product_type = P2.product_type
    GROUP BY product_type );

关联子查询也是用来对集合进行切分的  
![根据关联子查询进行切分的图示][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1aml6aGlzaHVp_size_16_color_FFFFFF_t_70 4]  
结合条件一定要写在子查询中

关联名称的作用域.
    子查询内部设定的关联名称，只能在该子查询内部使用。换句话说，就是“内部可以看到外部，而外部看不到内部”

![子查询内的关联名称的有效范围][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1aml6aGlzaHVp_size_16_color_FFFFFF_t_70 5]

## 六、函数、谓词、CASE 表达式 ##

### 6-1 各种各样的函数 ###

函数的种类

1. 算术函数(用来进行数值计算的函数)
    2. 字符串函数(用来进行字符串操作的函数) 
    3. 日期函数(用来进行日期操作的函数)
    4. 转换函数(用来转换数据类型和值的函数) 
    5. 聚合函数(用来进行数据聚合的函数): COUNT、SUM、AVG、MAX、MIN

算术函数

+ - * /

创建表并插入测试数据

-- DDL:创建表
    CREATE TABLE SampleMath (m NUMERIC (10,3),
    n INTEGER, p INTEGER);
    
    -- DML:插入数据
    BEGIN TRANSACTION; 1
    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;

ABS----绝对值

SELECT m,
    ABS(m) AS abs_col
    FROM SampleMath;

MOD—求余

-- MOD(被除数，除数)
    SELECT n, p,
    MOD(n, p) AS mod_col
    FROM SampleMath;
    
    -- SQL Server
    SELECT n, p,
    n % p AS mod_col FROM SampleMath;
    -- MySQL PostgerSql 支持上面的%写法

***SQL Server 不支持MOD函数***

ROUND—四舍五入

-- 对 m 列的数值进行 n 列位数的四舍五入处理
    SELECT m, n,
    ROUND(m, n) AS round_col
    FROM SampleMath;

![执行说明以及结果][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1aml6aGlzaHVp_size_16_color_FFFFFF_t_70 6]

字符串函数

-- 创建 SampleStr 表
    --DDL：创建表
    CREATE TABLE SampleStr
    (str1  VARCHAR(40),
     str2  VARCHAR(40),
     str3  VARCHAR(40));
    
    --DML：插入数据
    START 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',NULL,NULL);
    INSERT INTO SampleStr (str1, str2, str3) VALUES ('abc太郎','abc','ABC');
    INSERT INTO SampleStr (str1, str2, str3) VALUES ('abcdefabc','abc','ABC');
    INSERT INTO SampleStr (str1, str2, str3) VALUES ('micmic','i','I');
    
    COMMIT;
    
    
    --确认表中的内容
    SELECT * FROM SampleStr;

||函数—拼接

SELECT 
    str1, str2,str1 || str2 AS str_concat
    FROM SampleStr;
    -- 如果有 null 那么拼接的结果也是 null

![执行结果][20190618222140361.png]  
拼接三个字符串

SELECT str1, str2, str3, str1 || str2 || str3
    FROM SampleStr 
    WHERE str1 = '山田';

***SQL Server 和 MySQL 中无法使用***  
SQL Server 使用 “+” MySQL 使用 CONCAT拼接字符串

-- SQL Server
    SELECT str1, str2, str3,
    str1 + str2 + str3 AS str_concat
    FROM SampleStr;
    
    -- MySQL SQL Server 2012 及之后 
    SELECT str1, str2, str3,CONCAT(str1, str2, str3) AS str_concat 
    FROM SampleStr;

LENGTH—计算字符串长度

-- Oracle DB2 PostgreSQL MySQL
    SELECT str1,
    LENGTH(str1) AS len_str
    FROM SampleStr;
    
    -- MySQL中还存在计算字符串长度的自有函数 CHAR_LENGTH
    
    -- SQL Server LEN函数
    SELECT str1,
    LEN(str1) AS len_str
    FROM SampleStr;

LOWER — 转化为小写  
UPPER — 大写转化

-- 略

REPLACE — 字符串的替换

-- REPLACE( 对象字符串，替换前的字符串，替换后的字符串 )
    SELECT str1, str2, str3,
    REPLACE(str1, str2, str3) AS rep_str
    FROM SampleStr;

***替换字符或者被替换字符有 NULL 的被替换之后的字符也是 NULL***

SUBSTRING — 字符串的截取 (PostgreSQL/MySQL专用语法)

-- SUBSTRING(对象字符串 FROM 截取的起始位置 FOR 截取的字符数)
    SELECT str1,
    SUBSTRING(str1 FROM 3 FOR 2) AS sub_str
    FROM SampleStr;

![特定的 SQL][SQL]

日期函数

1.  当前日期

CURRENT_DATE
    -- PostgreSQL MySQL
    SELECT CURRENT_DATE;
    
    -- SQL Server 
    -- 使用CAST(后述)函数将CURRENT_TIMESTAMP转换为日期类型
    SELECT CAST(CURRENT_TIMESTAMP AS DATE) AS CUR_DATE;
    
    -- Oracle
    SELECT CURRENT_DATE
    FROM dual;
    
    -- DB2
    SELECT CURRENT DATE
    FROM SYSIBM.SYSDUMMY1;

1.  当前时间

CURRENT_TIME
    
    -- PostgreSQL MySQL
    SELECT CURRENT_TIME;
    
    -- SQL Server 
    -- 使用CAST函数(后述)将CURRENT_TIMESTAMP转换为时间类型 
    SELECT CAST(CURRENT_TIMESTAMP AS TIME) AS CUR_TIME;
    
    -- Oracle
    SELECT CURRENT_TIME
    FROM dual;
    
    -- DB2
    SELECT CURRENT TIME
    FROM SYSIBM.SYSDUMMY1;

当前日期和时间

CURRENT_TIMESTAMP
    
    -- SQL Server PostgreSQL MySQL
    SELECT CURRENT_TIMESTAMP;
    
    -- Oracle
    SELECT CURRENT_TIMESTAMP
    FROM dual;
    
    -- DB2
    SELECT CURRENT TIMESTAMP
    FROM SYSIBM.SYSDUMMY1;

截取日期元素

EXTRACT
    -- PostgreSQL MySQL
    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;

![特定的 SQL][SQL 1]  
转换函数

1.  类型转换

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

![将字符串类型转换为数值类型][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1aml6aGlzaHVp_size_16_color_FFFFFF_t_70 7]  
![将字符串类型转换为日期类型][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1aml6aGlzaHVp_size_16_color_FFFFFF_t_70 8]  
2. 值的转换

-- COALESCE 函数
    -- COALESCE(数据1,数据2,数据3... ...)
    -- 该函数会返回可变参数A中左侧开始第1个不是NULL的值。
    
    SELECT COALESCE(str2, 'NULL'),str2 FROM SampleStr;

![在这里插入图片描述][20190618233659902.png]  
![各种 DBMS 执行 COALESCE 的区别][DBMS _ COALESCE]

### 6-2 谓词 ###

*  谓词就是返回值为真值的函数。
 *  掌握LIKE的三种使用方法(前方一致、中间一致、后方一致)。
 *  需要注意BETWEEN包含三个参数。
 *  想要取得NULL数据时必须使用IS NULL。
 *  可以将子查询作为IN和EXISTS的参数。

什么是谓词

=、<、>、<> 等比较运算符，其正式的名称就是比较谓词
    
    谓词和函数的最大区别: 
    	对通常的函数来说，返回值有可能是数字、字符串或者日期等，
    	但是谓词的返回值全都是真值(TRUE/ FALSE/UNKNOWN)。

要学习的谓词

*  LIKE
 *  BETWEEN
 *  IS NULL、IS NOT NULL
 *  IN
 *  EXISTS

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

前方一致:WHERE strcol LIKE 'ddd%';
    
    中间一致:WHERE strcol LIKE '%ddd%';
    
    后方一致: WHERE strcol LIKE 'abc%'; 
    
    -- 一下语句在 PostgerSql 中都是有效的
    select * from SampleLike where strcol like 'ab_ddd';
    select * from SampleLike where strcol like 'ab__dd';
    select * from SampleLike where strcol like '_d____';
    select * from SampleLike where strcol like '_d%';
    select * from SampleLike where strcol like '%d___';
    select * from SampleLike where strcol like '_d_%';

BETWEEN 谓词——范围查询

-- WHERE sale_price BETWEEN 100 AND 1000;
    -- BETWEEN 的特点就是结果中会包含 100 和 1000 这两个临界值

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

略

IN 谓词——OR 的简便用法

-- WHERE purchase_price = 320 
    -- OR purchase_price = 500 
    -- OR purchase_price = 5000;
    
    -- 虽然上述方法没有问题，但还是存在一点不足之处，那就是随着希望选取的对象越来越多，SQL 语句也会越来越长，阅读起来也会越来越困难。这时，我们就可以使用 IN 谓词“IN( 值,......)”来 替换上述 SQL 语句。
    
    -- WHERE purchase_price IN (320, 500, 5000);
    
    -- 使用NOT IN进行查询时指定多个排除的进货单价进行查询
    -- WHERE purchase_price NOT IN (320, 500, 5000);

***需要注意的是，在使用IN和NOT IN时是无法选取出NULL数据的。 实际结果也是如此，上述两组结果中都不包含进货单价为 NULL 的叉子和圆 珠笔。NULL终究还是需要使用IS NULL和IS NOT NULL来进行判断***

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

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));
    
    BEGIN TRANSACTION;
    INSERT INTO ShopProduct(shop_id,shop_name,product_id,quantity) VALUES ('000A','北京','0001',30);
    INSERT INTO ShopProduct(shop_id,shop_name,product_id,quantity) VALUES ('000A','北京','0002',50);
    INSERT INTO ShopProduct(shop_id,shop_name,product_id,quantity) VALUES ('000A','北京','0003',15);
    INSERT INTO ShopProduct(shop_id,shop_name,product_id,quantity) VALUES ('000B','上海','0002',30);
    INSERT INTO ShopProduct(shop_id,shop_name,product_id,quantity) VALUES ('000B','上海','0003',120);
    INSERT INTO ShopProduct(shop_id,shop_name,product_id,quantity) VALUES ('000B','上海','0004',20);
    INSERT INTO ShopProduct(shop_id,shop_name,product_id,quantity) VALUES ('000B','上海','0006',10);
    INSERT INTO ShopProduct(shop_id,shop_name,product_id,quantity) VALUES ('000B','上海','0007',40);
    INSERT INTO ShopProduct(shop_id,shop_name,product_id,quantity) VALUES ('000C','广州','0003',20);
    INSERT INTO ShopProduct(shop_id,shop_name,product_id,quantity) VALUES ('000C','广州','0004',50);
    INSERT INTO ShopProduct(shop_id,shop_name,product_id,quantity) VALUES ('000C','广州','0006',90);
    INSERT INTO ShopProduct(shop_id,shop_name,product_id,quantity) VALUES ('000C','广州','0007',70);
    INSERT INTO ShopProduct(shop_id,shop_name,product_id,quantity) VALUES ('000D','南京','0001',100);
    COMMIT;

EXIST 谓词

-- 用EXIST选取出“上海店在售商品的销售单价”
    
    SELECT product_name, sale_price
    FROM Product AS P
    WHERE EXISTS (SELECT *
    FROM ShopProduct AS SP
    WHERE SP.shop_id = '000C'
    AND SP.product_id = P.product_id);

EXIST 的参数

之前我们学过的谓词，基本上都是像“列 LIKE 字符串”或者“列BETWEEN 值 1 AND 值 2”,
    这样需要指定 2 个以上的参数，而 EXIST 的 左侧并没有任何参数。
    这是因为 EXIST 是只有 1 个参数的谓词。
    EXIST 只需要在右侧书写 1 个参数，该参数通常都会是一个子查询

![子查询中的 SELECT \*][SELECT 2]  
使用NOT EXIST替换NOT IN

-- 使用NOT EXIST读取出“北京店在售之外的商品的销售单价”
    SELECT product_name, sale_price
    FROM Product AS P 
    WHERE NOT EXISTS (SELECT *
    FROM ShopProduct AS SP 
    WHERE SP.shop_id = '000A'
    AND SP.product_id = P.product_id);

### 6-3 CASE 表达式 ###

*  CASE表达式分为简单CASE表达式和搜索CASE表达式两种。搜索 CASE 表达式包含简单 CASE 表达式的全部功能。
 *  虽然CASE表达式中的ELSE子句可以省略，但为了让SQL语句更加容易 理解，还是希望大家不要省略。
 *  CASE 表达式中的 END 不能省略。
 *  使用CASE表达式能够将SELECT语句的结果进行组合。
 *  虽然有些DBMS提供了各自特有的CASE表达式的简化函数，例如Oracle中的 DECODE 和 MySQL 中的 IF，等等，但由于它们并非通用的函数，功能上也有些限制，因此有些场合无法使用。

什么是 CASE 表达式

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

CASE 表达式的语法

CASE 
    	WHEN <求值表达式> THEN <表达式> 
    	WHEN <求值表达式> THEN <表达式> 
    	WHEN <求值表达式> THEN <表达式>
    	.
    	.
    	.
    	ELSE <表达式> 
    END

CASE 表达式的使用方法

-- 通过 CASE 表达式将 A ~ C 的字符串加入到商品种类当中
    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;

![执行结果][20190619160819120.png]  
注意事项  
![case 使用时的注意事项][case]  
CASE 表达式的书写位置

CASE 表达式的便利之处就在于它是一个表达式。
    之所以这么说，是因为表达式可以书写在任意位置

使用 CASE 表达式将行列转换

-- 按照类型分组
    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;

简单 CASE 表达式  
![简单 CASE 表达式][CASE]

[DELETE]: /images/20210508/c3c359406d7e44828566a647530a2b00.png
[TRUNCATE]: /images/20210508/1f731a6a90aa4771aaa0a5c0aefe472f.png
[COMMIT _ _]: /images/20210514/1620992018859.png
[ROLLBACK _ _]: /images/20210508/13d1db3a2ec64dbbba182d035b740a1c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1aml6aGlzaHVp_size_16_color_FFFFFF_t_70]: /images/20210508/caf4ffdb0df54af1a0bb18b65f72e9cf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1aml6aGlzaHVp_size_16_color_FFFFFF_t_70 1]: /images/20210508/398b3730187945f19555379a261a4e3b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1aml6aGlzaHVp_size_16_color_FFFFFF_t_70 2]: /images/20210508/cbc5b0a247b8467f92761dee1b035fe8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1aml6aGlzaHVp_size_16_color_FFFFFF_t_70 3]: /images/20210508/e9412dba893449c88b30fcc1266c1dbb.png
[SELECT]: /images/20210508/9ed9d97b89f240f491558c7c30b2386f.png
[SELECT 1]: /images/20210508/9bd71e83a1eb4b618f654c139a2d401c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1aml6aGlzaHVp_size_16_color_FFFFFF_t_70 4]: /images/20210508/e75c467cc247412083344f35bff1fc2e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1aml6aGlzaHVp_size_16_color_FFFFFF_t_70 5]: /images/20210508/1d7a7920ac5144558f481cdc614a287b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1aml6aGlzaHVp_size_16_color_FFFFFF_t_70 6]: /images/20210508/fa645c3fb0e24bce8def817a9ce0d274.png
[20190618222140361.png]: /images/20210508/6e2e91e1c9d340fbb5e41aad9a59acda.png
[SQL]: /images/20210508/b0f202e5a74546cc8f477c08536f222e.png
[SQL 1]: /images/20210508/7731c91a719a497db5a8fdebbdd97e5a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1aml6aGlzaHVp_size_16_color_FFFFFF_t_70 7]: /images/20210508/ffdc73388f174f3b814ef8a25e05019d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1aml6aGlzaHVp_size_16_color_FFFFFF_t_70 8]: /images/20210508/56138b29e3794616a3cca88085996b6e.png
[20190618233659902.png]: /images/20210508/6c8b1f16eebb4ca6b9c614210d495c40.png
[DBMS _ COALESCE]: /images/20210508/70866ad4c4394e8287550114360380f0.png
[SELECT 2]: /images/20210508/a61e8cef383b485c9b385d16755a0f85.png
[20190619160819120.png]: /images/20210508/74ff0209b461468fa7529ae672ca71c5.png
[case]: /images/20210508/8fcc60727d5f48f1ab137a2ba61ae457.png
[CASE]: /images/20210508/b16c437f569641eebb91e1c48fc8796a.png