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

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

四、数据更新

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")
);
1
2
3
4
5
6
7
8
9
10

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');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

列清单的省略

-- 包含列清单
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');
1
2
3
4
5
6
7
8
9
10
11
12

插入 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');
1
2
3
4
5
6

插入默认值

-- 通过显式方法插入默认值 ---- 在 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');
1
2
3
4
5
6
7
8
9
10
11
12

从其他表中复制数据

-- 创建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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

各种各样的 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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

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

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

DROP TABLE 语句和 DELETE 语句

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

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

DELETE 语句的基本语法

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

-- 清空 Product 表
DELETE FROM Product;
1
2
3
4
5

DELETE 语句常见错误

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

DELETE FROM <表名> WHERE <条件>;

-- 删除销售单价(sale_price)大于等于4000日元的数据
DELETE FROM 
Product
WHERE sale_price >= 4000;
1
2
3
4
5
6

清空表–截断表

TRUNCATE
1

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

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

UPDATE 语句的基本语法

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

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

-- 更新部分数据行的搜索型 UPDATE
UPDATE <表名>
SET <列名> = <表达式>
WHERE <条件>;

-- 将商品种类为厨房用具的记录的销售单价更新为原来的 10 倍
UPDATE Product
SET sale_price = sale_price * 10
WHERE product_type = '厨房用具';
1
2
3
4
5
6
7
8
9

使用 NULL 进行更新

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

多列更新

-- 能够正确执行的繁琐的 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 = '厨房用具';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

4-4 事务

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

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

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

什么是事务

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

创建事务

-- 事务的语法
事务开始语句 ;
	DML 语句1 ; 
	DML 语句2 ; 
	DML 语句3 ;
		.
		.
		.
事务结束语句(COMMIT 或者 ROLLBACK);
-- 事务的结束语句只有 commit 和 rollback 这两种,所有的 DBMS 都是通用的

-- 事务的开始语句，而是由各个 DBMS 自己来定义的
● SQL Server、PostgreSQL
	BEGIN TRANSACTION
	
● MySQL
	START TRANSACTION
	
● Oracle、DB2
	无
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

事务举例

-- 更新商品信息的事务
-- 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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

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

结束事务的两种指令

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

1
2
3
4
5

ACID 特性

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

五、复杂查询

视图

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

视图和表

视图和表的区别就是是否保存了实际的数据

实际上视图保存的是 SELECT 语句。
 我们从视图中读取数据时，视图会在内部执行该 SELECT 语句并创建出 一张临时表。
1
2
3
4

视图的优点

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

创建视图的方法

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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

不建议使用多重视图,会降低 SQL 的性能

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

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

PostgerSql 支持 order by,但是还是建议不要使用
1
2
3
4
5

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

INSERT INTO ProductSum VALUES ('电器制品', 5); 
-- 该更新会失败,原因是视图归根结底还是从表派生出来的,因此,如果原表可以更新,那么视图中的数据也可以更新
-- 反之亦然,如果视图发生改变,而原表没有进行相应更新的话,就无法保证数据的一致性.
1
2
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等)，需要特别注意。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

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

删除视图

DROP VIEW 视图名称(<视图列名1>, <视图列名2>, ......)

-- 删除ProductSum
DROP VIEW ProductSum;
-- ERROR:  cannot drop view productsum because other objects depend on it

-- 解决方法
DROP VIEW ProductSum CASCADE;
1
2
3
4
5
6
7
8

子查询

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

子查询和视图

子查询的特点概括起来就是一张一次性视图。

视图并不是用来保存数据的，而是通过保存读取数据的 SELECT 语句的方法来为用户提供便利。

子查询就是将用来定义视图的 SELECT 语句直接用于 FROM 子句当中。
1
2
3
4
5

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

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;”。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

增加子查询的层数

-- 由于子查询的层数原则上没有限制，
-- 因此可以像“子查询的 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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

子查询的名称

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

标量子查询

什么是标量

标量就是单一的意思

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

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

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

标量子查询的书写位置

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

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

SELECT 
	product_id, product_name,sale_price,(
	SELECT AVG(sale_price) FROM Product
	) AS avg_price 
FROM Product;
1
2
3
4
5

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

SELECT 
	product_type, AVG(sale_price) 
FROM Product
GROUP BY 
	product_type
HAVING 
	AVG(sale_price)> (SELECT AVG(sale_price) FROM Product);
1
2
3
4
5
6
7

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

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

关联子查询

• 关联子查询会在细分的组内进行比较时使用。
• 关联子查询和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 );
1
2
3
4
5
6
7
8
9
10
11
12

关联子查询也是用来对集合进行切分的

结合条件一定要写在子查询中

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

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

6-1 各种各样的函数

函数的种类

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

算术函数

+ - * /
1

创建表并插入测试数据

-- 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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

ABS----绝对值

SELECT m,
ABS(m) AS abs_col
FROM SampleMath;
1
2
3

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 支持上面的%写法
1
2
3
4
5
6
7
8
9

SQL Server 不支持MOD函数

ROUND—四舍五入

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

字符串函数

-- 创建 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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

||函数—拼接

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

拼接三个字符串

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

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;
1
2
3
4
5
6
7
8

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;
1
2
3
4
5
6
7
8
9
10
11

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

-- 略
1

REPLACE — 字符串的替换

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

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

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

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

日期函数

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
2
3
4
5
6
7
8
9
10
11
12
13
14
15

2. 当前时间

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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

当前日期和时间

CURRENT_TIMESTAMP

-- SQL Server PostgreSQL MySQL
SELECT CURRENT_TIMESTAMP;

-- Oracle
SELECT CURRENT_TIMESTAMP
FROM dual;

-- DB2
SELECT CURRENT TIMESTAMP
FROM SYSIBM.SYSDUMMY1;
1
2
3
4
5
6
7
8
9
10
11
12

截取日期元素

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;
1
2
3
4
5
6
7
8
9

转换函数

1. 类型转换

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

2. 值的转换

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

SELECT COALESCE(str2, 'NULL'),str2 FROM SampleStr; 
1
2
3
4
5

6-2 谓词

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

什么是谓词

=、<、>、<> 等比较运算符，其正式的名称就是比较谓词

谓词和函数的最大区别: 
	对通常的函数来说，返回值有可能是数字、字符串或者日期等，
	但是谓词的返回值全都是真值(TRUE/ FALSE/UNKNOWN)。
1
2
3
4
5

要学习的谓词

• 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_%';
1
2
3
4
5
6
7
8
9
10
11
12
13

BETWEEN 谓词——范围查询

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

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

略
1

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);
1
2
3
4
5
6
7
8
9
10

需要注意的是，在使用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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

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);
1
2
3
4
5
6
7
8

EXIST 的参数

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

使用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);

1
2
3
4
5
6
7
8

6-3 CASE 表达式

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

什么是 CASE 表达式

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

CASE 表达式的语法

CASE 
	WHEN <求值表达式> THEN <表达式> 
	WHEN <求值表达式> THEN <表达式> 
	WHEN <求值表达式> THEN <表达式>
	.
	.
	.
	ELSE <表达式> 
END
1
2
3
4
5
6
7
8
9

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;
1
2
3
4
5
6
7
8

注意事项

CASE 表达式的书写位置

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

使用 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;
1
2
3
4
5
6
7
8
9
10
11

简单 CASE 表达式

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