PostgreSQL基础操作

数据库的连接：C:\PostgreSQL\9.6\bin\psql.exe -U postgres -d shop

SQL的语句编写要有段落感，不可连成一段！

第一章数据库和SQL

DDL：数据定义语言：

CREATE ：创建数据库和表等对象

DROP：删除数据库和表等对象

ALTER：修改数据库和表等对象的结构（一般用于删除结构）

DML：数据操控语言：

SELECT：查询表中的数据

INSERT：向表中插入新数据

UPDATE：更新表中的数据

DELECT：删除表中的数据（一般用于删除数据）

DCL：数据控制语言：

COMMIT：确认对数据库中的数据进行的变更

ROLLBACK：取消对数据库中的数据进行的变更

GRANT：赋予用户操作权限

REVOKE：取消用户的操作权限

数据库的创建：CREATE DATABASE <数据库名称> ;

表的创建：CREATE TABLE <表名>

(<列名1><数据类型><该列所需的约束>,

<列名2><数据类型><该列所需的约束>,

<列名3><数据类型><该列所需的约束>,

<列名4><数据类型><该列所需的约束>,

……

<该表的约束1>,<该表的约束2>,……);

表的删除：DROP TABLE <表名>;

添加新列：ALTER TABLE <表名> ADD COLUMN <列的定义>;

Oracle和SQL Server：ALTER TABLE <表名> ADD <列名>;

Oracle中添加多列：ALTER TABLE <表名> ADD (<列名>,<列名>,……);

删除列：ALTER TABLE <表名> DROP COLUMN <列名>;

Oracle：ALTER TABLE <表名> DROP <列名>;

Oracle删除多列：ALTER TABLE <表名> ADD (<列名>,<列名>,……);

插入数据：SQL Server、PostgreSQL：

-- DML:插入数据

BEGIN TRANSACTION;

INSERT INTO Product VALUES ('0001', 'T恤' ,'衣服', 1000, 500, '2009-09-20');

INSERT INTO Product VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');

INSERT INTO Product VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);

INSERT INTO Product VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');

INSERT INTO Product VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');

INSERT INTO Product VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');

INSERT INTO Product VALUES ('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');

INSERT INTO Product VALUES ('0008', '圆珠笔', '办公用品', 100, NULL, '2009-11-11');

COMMIT;

在MySQL中将BEGIN TRANSACTION;改成START TRANSACTION;

在Oracle和DB2中运行时，无需使用BEGIN TRANSACTION;

表的删除：Oracle、PostgreSQL：ALTER TABLE Poduct RENAME TO Product;

DB2：RENAME TABLE Poduct TO Product;

SQL Server：sp_rename ‘Poduct’, ’Product’;

MySQL：RENAME TABLE Poduct to Product;

第二章查询基础

列的查询：SELECT <列名>,…… FROM <表名>;

查询所有列：SELECT * FROM <表名>;

设置别名：SELECT product_id AS id,

product_name AS name,

purchase_date AS price

FROM Product;

设置中文别名：SELECT product_id AS "商品编号",

product_name AS "商品名称",

purchase_price AS "进货单价"

FROM Product; （中文别名要用双引号）

常数查询：SELECT '商品' AS string, 38 AS number, '2009-02-24' AS date,

product_id, product_name

FROM Product;

删除重复行：SELECT DISTINCT product_type FROM Product;（含有NULL的将被保留）

多列删除重复行：SELECT DISTINCT product_type, regist_date FROM Product;

使用WHERE选择纪录：SELECT <列名>,…… FROM <表名> WHERE <条件表达式>;

例：SELECT product_name, product_type

FROM Product

WHERE product_type = '衣服'; （product_type可以不在SELECT中选取）

单行注释：-- 本SELECT语句会从结果中删除重复行。

SELECT DISTINCT product_id, purchase_price

FROM Product;

多行注释：/* 本SELECT语句，

会从结果中删除重复行。*/

SELECT DISTINCT product_id, purchase_price

FROM Product;（注释可以插入SQL语句中）

算术运算符：SELECT product_name, sale_price,

sale_price * 2 AS “sale_price_x2”

FROM Product;（+ - * / 表示加减乘除四则运算）

（所有包含NULL的计算，结果都为NULL，NULL除以0仍然为NULL）

比较运算符：SELECT product_name, product_type

FROM Product

WHERE sale_price = 500;

（相等：“ = ” 不相等：<> 大于等于：>= 大于：> 小于等于：<= 小于：< ）

(WHERE中可以使用计算表达式，例：SELECT product_name, sale_price, purchase_price

FROM Product

WHERE sale_price - purchase_price >= 500;）

(字符串的比较按照字典顺序进行排序，例：‘2’>‘10’ ‘2’<‘222’)

(不能对NULLL使用比较运算符)

选取NULL/不选取NULL运算符：IS NULL/IS NOT NULL;

NOT 运算符：SELECT product_name, product_type, sale_price

FROM Product

WHERE NOT sale_price >= 1000;

AND运算符和OR运算符：SELECT product_name, purchase_price

FROM Product

WHERE product_type = '厨房用具'

AND sale_price >= 3000; （AND优先级高于OR）

使用括号强化运算：SELECT product_name, product_type, regist_date

FROM Product

WHERE product_type = '办公用品'

AND ( regist_date = '2009-09-11'

OR regist_date = '2009-09-20');

三值逻辑：真、假、不确定（AND一假即假，OR一真即真同样适用不确定）

（无法通过上述方法判断的包含不确定的逻辑运算都是不确定）

第三章聚合与排序

聚合函数： COUNT:计算纪录数（行数）

SUM:计算合计值

AVG:计算平均值

MAX:计算任意列最大值

MIN:计算任意列最小值

计算全部数据的行数：SELECT COUNT(*) FROM Product;（包括NULL行）

计算具体列的数据行数：SELECT COUNT(purchase_price) FROM Product;（不包括NULL行）

计算合计值：SELECT SUM(sale_price) FROM Product;（不包括NULL，NULL被排除）

计算平均值：SELECT AVG(sale_price) FROM Product;（不包括NULL，NULL不会算在分母和分子上）

（SUM/AVG函数只适用于数值类型的函数）

计算最大值/最小值：SELECT MAX(sale_price), MIN(purchase_price) FROM Product;

（MAX/MIN函数几乎适用所有数据类型，包括日期和字符串，任何可以排序的数据类型都可以使用）

去除含有重复数据的数据行数：SELECT COUNT(DISTINCT product_type) FROM Product;

（计算值的种类可以在COUNT函数的参数中使用DISTINCT，注意必须在括号内）

（在所有的聚合函数中都可以使用DISTINCT删除重复数据）

GROUP BY子句：SELECT <列名1>,<列名2>,<列名3>,……

FROM <表名>

GROUP BY <列名1>,<列名2>,<列名3>,……;

（GROUP BY子句中指定的列称为聚合键或者分组列）

（例:SELECT product_type, COUNT(*)

FROM Product

GROUP BY product_type;)

（子句书写顺序：SELECT -> FROM -> WHERE -> GROUP BY）

（聚合键中包含NULL时，结果会以空行的形式表现）

使用GROUP BY子句和WHERE子句进行汇总处理：SELECT <列名1>,<列名2>,<列名3>,……

FROM <表名>

WHERE

GROUP BY <列名1>,<列名2>,<列名3>,……;

（例：SELECT purchase_price, COUNT(*)

FROM Product

WHERE product_type = '衣服'

GROUP BY purchase_price;）

（DBMS内部执行顺序：FROM -> WHERE -> GROUP BY -> SELECT）

（使用GROUP BY子句时，SELECT子句中不能出现聚合键以外的列名）

（在WHERE和GROUP BY 子句中不能使用SELECT子句中定义的别名）

（只有SELECT、 HAVING和ORDER BY子句中可以使用聚合函数）

（和英语语法一样，一般来说，GROUP BY语句配合函数进行汇总，虽然有的时候其他操作，如：DISTINCT能和GROUP BY有着类似的功能与结果，但是请注意每一条语句最初的功能，我们应该按照功能进行使用，而不是为了得出结果对一条语句进行随意的使用）

对聚合结果指定条件（HAVING子句）：SELECT <列名1>,<列名2>,<列名3>, ……

FROM <表名>

GROUP BY <列名1>, <列名2>, <列名3>, ……

HAVING <分组结果对应的条件>;

(HAVING子句跟在GROUP BY子句之后)

(HAVING子句构成要素：常数、聚合函数、GROUP BY子句中指定的列名）

HAVING子句和WHERE子句的不同：WHERE子句 = 指定行所对应的条件

HAVING子句 = 指定组所对应的条件

（聚合键所对应的条件应该书写在WHERE子句中，而不是HAVING子句中）

对查询结果排序（ORDER BY子句）：SELECT <列名1>, <列名1>, <列名1>, ……

FROM <表名>

ORDER BY <排序基准列1>, <排序基准列1>, ……

（书写顺序：SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY）

指定升（降）序：ORDER BY默认升序，ASC升序，DESC降序

指定多个排序键：例：SELECT product_id, product_name, sale_price, purchase_price

FROM Product

ORDER BY sale_price, product_id;（价格相同时按照商品编号升序排列）

（排序键中包含NULL时，会在开头或末尾进行汇总）

在排序键中使用别名:例：

SELECT product_id AS id, product_name, sale_price AS sp, purchase_price

FROM Product

ORDER BY sp, id; （ORDER BY子句后可以使用别名）

（执行顺序：FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY）

（ORDER BY中可以使用SELECT子句中未包含的列和聚合函数）

（ORDER BY子句中不要使用列编号）

第四章数据更新

插入数据：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');

列清单 -> (product_id, product_name, product_type, sale_price, purchase_price, regist_date)

行清单 -> ('0001', 'T恤' ,'衣服', 1000, 500, '2009-09-20')

原则上，执行一次INSERT语句只插入一行数据）

多行插入：例：INSERT INTO ProductIns VALUES

('0002', '打孔器', '办公用品', 500, 320, '2009-09-11'),

('0003', '运动T恤', '衣服', 4000, 2800, NULL),

('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');

Oracle：INSERT ALL INTO ProductIns VALUES

('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');

('0003', '运动T恤', '衣服', 4000, 2800, NULL);

('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20')

SELECT * FROM DUAL;

（对全列INSERT时可以省略列清单）

（按照INSERT语句正常插入NULL数据，但是插入列不能设置NOT NULL约束）

（插入失败不会影响之前插入的数据）

默认值：DEFAULT <默认值> （DEFAULT=DEFAULT 0，默认值大小在创建表时已自行定义）

显性（推荐）：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');（省略sale_price和它的值）

（对未设定默认值但是无约束的列，省略后赋“NULL”；而对未设定默认值但是设置“NOT NULL”约束的列，省略后会报错，换句话说，对未设定默认值的列省略默认赋“NULL”，除了约束条件为“NOT NULL”）

（省略INSERT语句中的列名，会自动设定为该列的默认值；若没有默认值则设定为NULL）

复制数据：INSERT INTO <目标表> (<列名1>, <列名2>, <列名3>, ……)

SELECT <列名1>, <列名2>, <列名3>, ……

FROM <被复制表>;

（例：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;）

（INSERT语句中可以使用WHERE子句或者GROUP BY子句等任何SQL语法，但使用ORDER BY 子句不会产生任何效果）

数据的删除：DELECT FROM <表名>;

（DROP TABLE = 完全删除表 DELECT语句 = 留下表，删除表中全部数据，删除的是纪录）

搜索型删除：DELETE FROM <表名>

WHERE <条件>;

（DELETE语句中不能使用GROUP BY、HAVING和ORDER BY子句）

更新数据：UPDATE <表名>

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

搜索型更新：UPDATE <表名>

SET <列名> = <表达式>

WHERE <条件>;

（对未设置“NOT NULL”和主键约束的列可以用UPDATE语句清空成NULL）

多列更新：①使用逗号对列进行分隔排序(通用)

例：UPDATE Product

SET sale_price = sale_price * 10,

purchase_price = purchase_price / 2

WHERE product_type = '厨房用具';

②将列用（）括起来形成清单（仅在PostgreSQL和DB2中使用）

例：UPDATE Product

SET (sale_price, purchase_price) = (sale_price * 10, purchase_price / 2)

WHERE product_type = ‘厨房用具’;

创建事务：事务开始语句;

DML语句1；

DML语句2;

DML语句3;

……

事务结束语句(COMMIT或者ROLLBACK);

（事务是需要在同一个处理单元中执行的一系列更新处理的集合）

开始语句：SQL Server、PostgreSQL：BEGIN TRANSACTION

　　MySQL：START　TRANSACTION

　　　　　　　Oracle、DB2：无

提交处理：COMMIT（一旦提交，无法恢复）

取消处理：ROLLBACK（一旦回滚，恢复开始状态）

（SQL Server、PostgreSQL和MySQL默认自动提交事务；Oracle默认用户自己执行提交或者回滚指令）

ACID特性：原子性：事务结束时包含的更新处理要么都执行，要么都不执行

一致性（完整性）：事务中的处理要满足数据库提前设置的约束

隔离性：不同事务之间不受干扰，该事务结束前，对其他事务是不可见的

持久性（耐久性）：事务结束后，DBMS能够保证该时间点的数据状态被保存

（事务执行纪录保存到硬盘等存储介质，该执行纪录称为日志，可通过日志恢复到故障之前的状态）

第五章复杂查询

创建视图：CREATE VIEW 视图名称（<视图列名1>, <视图列名2>, ……）

<SELECT语句>;

（例：CREATE VIEW ProductSum (product_type, cnt_product)

SELECT product_type, COUNT(*)

FROM Product

GROUP BY product_type;）

使用视图：SELECT product_type, cnt_product

FROM ProductSum;（或者：SELECT * FROM ProductSum;）

（可以以一个视图为基础创建另一个视图组成多重视图，但应该尽量避免）

视图的限制：1.定义视图不可使用ORDER BY子句

2.视图更新的限制：①SELECT子句中未使用DISTINCT

②FROM子句中只有一张表

③未使用GROUP BY子句

④未使用HAVING子句

（通过汇总得到的视图无法更新）

可以更新的视图（例）：CREATE VIEW ProductJim (product_id, product_name, product_type, sale_price, purchase_price, regist_date)

SELECT *

FROM Product （既没有聚合又没有结合的SELECT语句）

WHERE product_type = '办公用品';

（注：PostgreSQL中视图初始设定为只读，在向视图中插入行时，需要执行允许对PostgreSQL视图进行更新的指令：CREATE OR REPLACE RULE insert_rule

AS ON INSERT

TO ProductJim DO INSERT

INSERT INNTO Product VALUES (

new.product_id,

new.product_name,

new.product_type,

new.sale_price,

new.purchase_price,

new.regist_date);

DB2和MySQL等其他DBMS不需要这样的指令）

INSERT INTO ProductSumJim VALUES ('0009','印章','办公用品',95,10,'2009-11-30');

删除视图：DROP VIEW 视图名称;

删除有关联的多重视图：DROP VIEW 视图名称 CASCADE;（关联的所有视图都将删除）

子查询（SQL Server、DB2、PostgreSQL、MySQL）：（子查询中最好区分大小写）

SELECT product_type, cnt_product （视图中的列名）

FROM (SELECT product_type, COUNT(*) AS cnt_product （直接从表定义视图的列）

FROM Product

GROUP BY product_type) AS ProductSum; （视图的名称）

Oracle：将“AS ProductSum”改成“ProductSum”

（执行顺序：先执行FROM中的SELECT子句（内层查询）再执行SELECT子句（外层的查询））

（子查询作为内层查询会首先执行）

子查询嵌套：SELECT product_type, cnt_product （视图2的列名）

FROM (SELECT * （视图1）

FROM (SELECT product_type, COUNT(*) AS cnt_product

FROM Product （视图1根据表的定义）

GROUP BY product_type) AS ProductSum

WHERE cnt_product = 4) AS ProductSum2;

Oracle:将“AS ProductSum”改为“ProductSum”；将“AS ProductSum2”改为“ProductSum2”

（尽量避免使用子查询多层嵌套，同时，子查询必须设定名称）

标量子查询：必须且只能返回1行1列（单一值）

SELECT product_id, product_name, sale_price

FROM Product

WHERE sale_price > (SELECT AVG(sale_price) （红字即为计算平均销售单价的标量子查询）

FROM Product);

（WHERE子句中无法使用聚合函数，但因为使用子查询的SQL会先从子查询开始执行，所以我们可以先通过标量子查询得出限定数据，再对数据进行筛选）

（能够使用常数或者列名的地方，无论是SELECT子句，GROUP BY子句、HAVING子句还是ORDER BY 子句，几乎所有地方都可以使用标量子查询）

关联子查询：在细分的组内进行比较

错误案例：SELECT product_id, product_name, sale_price

FROM Product

WHERE sale_price > (SELECT AVG(sale_price)

FROM Product

GROUP BY product_type);

（通过商品种类计算出的平均值有三个，标量子查询无法返回三个值，无法对细分组内的数据进行比较）

正确案例：SQL Server, DB2, PostgreSQL, MySQL：

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);（可以省略）

Oracle:将“AS”去掉

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

典型例题（习题5-4）：计算各商品种类的平均销售单价，并创建视图：

CREATE VIEW AvgPriceByType AS

SELECT product_id,

product_name,

product_type,

sale_price,

(SELECT AVG(sale_price)

FROM Product AS P2

WHERE P1.product_type = P2.product_type

GROUP BY P1.product_type) AS avg_sale_price

FROM Product AS P1;

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

函数种类：算数函数（数值计算）

字符串函数（字符串操作）

日期函数（日期操作）

转换函数（转换数据类型和值）

聚合函数（数据聚合）

算数函数：

ABS（绝对值）：ABS（数值）

例：SELECT m,ABS(m) AS abs_col

FROM SampleMath; 　　　　　　（返回绝对值）

（ABS函数参数为NULL时结果也是NULL，大多数函数对于NULL同样返回NULL）

MOD（求余）：MOD（被除数，除数）

例：SELECT n,p,MOD(n,p) AS mod_col

FROM SampleMath; 　　　　　　(返回余数）)

（只能对整数类型使用MOD函数）

SQL Server：SELECT n, p,n % p AS mod_col

FROM SampleMath;

ROUND（四舍五入）函数：ROUND（对象数值，保留小数的位数）

（ROUND函数只能使用NUMERIC（全体位数，小数位数）数据类型）

例：SELECT m, n, ROUND(m, n) AS round_col

FROM SampleMath;　　　　　　　　　（返回对象数值四舍五入后的值）

字符串函数：

||（拼接）：字符串1 || 字符串2

例：SELECT str1, str2, str1 || str2 as str_concat

FROM SampleStr;

SELECT str1, str2, str3, str1 || str2 || str3 as str_concat

FROM SampleStr; 　　　　　　　　　(返回拼接结果）

SQL Server：使用“+”运算符（函数）连接：SELECT str1, str2, str3, str1 + str2 + str3 AS str_concat

FROM SampleStr;

MySQL、SQL Server 2012 及以后：使用concat函数连接：

SELECT str1, str2, str3, CONCAT(str1, str2, str3) AS str_concat

FROM SampleStr;

LENGTH（字符串长度）：LENGTH（字符串）

例：SELECT str1, LENGTH(str1) AS len_str

FROM SampleStr; 　　（返回目标字符串长度）

SQL Server：使用LEN函数：SELECT str1, LEN(str1) AS len_str

FROM SampleStr;

（半角英文字母占1字节，汉字等全角字符占2字节以上（多字节字符），以字节为单位的函数进行计算时，返回结果可能比较奇怪，如：1个字符得到2字节以上的结果，不同DBMS的执行结果也不尽相同）

REPLACE（字符串替换）：REPLACE（对象字符串，替换前的字符串，替换后的字符串）

例：SELECT str1, str2, str3,

REPLACE(str1, str2, str3) AS rep_str

FROM SampleStr;

（替换前的字符串要在对象字符串内，如果不在对象字符串内，即使有替换后的字符串也不发生替换，返回对象字符串；只要发现对象字符串内有满足替换前字符串的将会被全部替换成替换后的字符串；对象字符串、替换前字符串、替换后字符串有一个是空则返回空）

SUBSTRING（字符串的截取） PostgreSQL/MySQL：

SUBSTRING（对象字符串 FROM 截取的初始位置 FOR 截取的字符数）

例：SELECT str1,

SUBSTRING(str1 FROM 3 FOR 2) AS sub_str

FROM SampleStr;　　　　　　（返回截取到的字符）

（与编程语言不同，第1个就是第1个，不是第0个，FOR 截取的字符数是从FROM字符开始算一共截取 FOR个字符，比如FROM 3 FOR 2就是从第3个字符开始，截取两个，也就是3和4字符，能截多少就返回多少，截不到就返回空）

　　SQL Server：SUBSTRING（对象字符串，截取的初始位置，截取的字符数）

Oracle、DB2：SUBSTR（对象字符串，截取的初始位置，截取的字符数）

UPPER（大写转换）：UPPER（字符串）

例：SELECT str1, UPPER(str1) AS up_str

FROM SampleStr

WHERE str1 IN ('ABC', 'aBC', 'abc', '山田'); （返回大写后的字符串）

LOWER（小写转换）：LOWER（字符串）

例：SELECT str1, LOWER(str1) AS low_str

FROM SampleStr

WHERE str1 IN ('ABC', 'aBC', 'abc', '山田');　　（返回小写后的字符串）

日期函数：

CURRENT_DATE（当前日期）：SELECT CURRENT_DATE; （返回该函数执行时的时间）

SQL Server：SELECT CAST（CURRENT_TIMESTAMP AS DATE） AS CUR_DATE;

Oracle：SELECT CURRENT_DATE FROM dual; （dual：临时表）

DB2：SELECT CURRENT DATE FROM SYSIBM.SYSDUMMY1;

CURRENT_TIME（当前时间）：SELECT CURRENT_TIME; （返回函数执行的当前时间）

SQL Server：SELECT CAST（CURRENT_TIMESTAMP AS TIME）AS CUR_TIME;

Oracle：SELECT CURRENT_TIMESTAMP FROM dual;

DB2：SELECT CURRENT TIME FROM SYSIBM.SYSDUMMY1;

CURRENT_TIMESTAMP（当前日期和函数）：SELECT CURRENT_TIMESTAMP;(返回日期和时间)

Oracle：SELECT CURRENT_TIMESTAMP FROM dual;

DB2：SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1;

EXTRACT （截取日期元素）：EXTRACT（日期元素 FROM 日期）

例：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 Server：将EXTRACT换成DATEPART，其他不变

Oracle：在最后加上FROM DUAL

DB2：将CURRENT_TIMESTAMP中的下划线全部去掉，在最后加上SYSIBM.SYSDUMMY1

转换函数：

CAST（类型转换）：CAST（转换前的值 AS 想要转换的值）

例：SELECT CAST(‘0001’ AS INTEGER) AS int_col;

MySQL：SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;

Oracle：SELECT CAST('0001' AS INTEGER) AS int_col FROM DUAL;

DB2：SELECT CAST('0001' AS INTEGER) AS int_col FROM SYSIBM.SYSDUMMY1;

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') AS col_3;

Oracle：在最后加上FROM DUAL

DB2：在最后加上 SYSIBM.SYSDUMMY1

谓词：返回值是真值的函数

LIKE（字符串的部分一致查询）：

前方一致：选取字符串中与查询对象字符串起始部分相同

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

中间一致：选取字符串中含有查询对象的字符串

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

(中间一致包含前方一致和后方一致)

后方一致：选取字符串中与查询对象字符串末尾部分相同

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

（%：“0字符以上的任意字符串”）

例：查询“abc+任意两个字符”：

SELECT * FROM SampleLike WHERE strcol LIKE 'abc__';

（_：“任意1个字符”）

BETWEEN（范围查询）：SELECT product_name,sale_price FROM Product

WHERE sale_price BETWEEN 100 AND 1000;

(BETWEEN默认包含临界值)

(若不包含临界值，则需要使用>和<：SELECT product_name,sale_price FROM Product

WHERE sale_price > 100 AND sale_price < 1000;)

IS NULL和IS NOT NULL：判断是否为NULL

例：SELECT product_name, purchase_price, FROM Product

WHERE purchase_price IS NOT NULL;

IN（OR的简便用法）：

例：（IN）SELECT product_name, purchase_price FROM Product

WHERE purchase_price IN (320, 500, 5000);

（NOT IN）SELECT product_name, purchase_price FROM Product

WHERE purchase_price NOT IN (320, 500, 5000);

（通过IN或者NOT IN来限定同类数据的不同数值，避免大量使用OR语句造成冗余）

（IN或者NOT IN无法选取NULL数据）

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

例：（IN）SELECT product_name, sale_price

FROM Product

WHERE product_id IN (SELECT product_id

FROM ShopProduct

WHERE shop_id = '000C');

（NOT IN）SELECT product_name, sale_price

FROM Product

WHERE product_id NOT IN (SELECT product_id

FROM ShopProduct

WHERE shop_id = '000C');

（使用子查询作为IN谓词参数时使当数据变更时程序容易维护）

EXISTS（难以理解的好工具）：判断是否存在满足某种条件的纪录

例：（EXISTS） 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);

（NOT EXISTS） 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);

（作为EXISTS参数的子查询中经常使用SELECT *，虽然对于EXISTS来说，只关心是否存在某纪录，并不关心返回哪些列；EXISTS只有一个参数，通常使用关联子查询）

CASE表达式：（条件）分支

搜索CASE：CASE WHEN <求值表达式> THEN <表达式>

WHEN <求值表达式> THEN <表达式>

……

ELSE <表达式>

END

例：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 （ELSE NULL可以省略）

END AS abc_product_type

FROM Product;

对按照商品种类得到的销售单价合计值进行行列转换：

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;

对比GROUP BY：

SELECT product_type, sum(sale_price) AS sum_price

FROM Product

GROUP BY product_type;

简单CASE：CASE <表达式>

WHEN <表达式> THEN <表达式>

……

ELSE<表达式>

END

例：SELECT product_name,

CASE product_type

WHEN '衣服' THEN 'A：' || product_type

WHEN '办公用品' THEN 'B：' || product_type

WHEN '厨房用具' THEN 'C：' || product_type

ELSE NULL （ELSE NULL可以省略）

END AS abc_product_type

FROM Product;

特定CASE：

Oracle：使用DECODE代替CASE

例：SELECT product_name,

DECODE(product_type, '衣服', 'A：' || product_type,

'办公用品', 'B：' || product_type,

'厨房用具', 'C：' || product_type,

NULL) AS abc_product_type

FROM Product;

MySQL：使用IF代替CASE

例：SELECT product_name,

IF( IF( IF(product_type = '衣服',

CONCAT('A：', product_type), NULL)

IS NULL AND product_type = '办公用品',

CONCAT('B：', product_type),

IF(product_type = '衣服',

CONCAT('A：', product_type), NULL))

IS NULL AND product_type = '厨房用具',

CONCAT('C：', product_type),

IF( IF(product_type = '衣服',

CONCAT('A：', product_type), NULL)

IS NULL AND product_type = '办公用品',

CONCAT('B：', product_type),

IF(product_type = '衣服',

CONCAT('A：', product_type),

NULL))) AS abc_product_type

FROM Product;

第七章集合运算

表的加法（并集）：UNION

例：SELECT product_id, product_name

FROM Product

UNION

SELECT product_id, product_name

FROM Product2;

（集合运算会除去重复的纪录）

注意事项：1.纪录列数必须相同

2.纪录中列的类型必须一致

3.可以使用任何SELECT语句，但是ORDER BY子句只能在最后使用一次

包含重复行：ALL

例：SELECT product_id, product_name

FROM Product

UNION ALL

SELECT product_id, product_name

FROM Product2;

选取公共部分（交集）：INTERSECT

例：SELECT product_id, product_name

FROM Product

INTERSECT

SELECT product_id, product_name

FROM Product2

ORDER BY product_id;

（MySQL不支持INTERSECT）

纪录的减法（差集）：EXCEPT

例：SELECT product_id, product_name

FROM Product

EXCEPT

SELECT product_id, product_name

FROM Product2

ORDER BY product_id;

（删除EXCEPT后的表）

Oracle：不使用EXCEPT而使用MINUS

内联结：INNER JOIN

例：SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price

FROM ShopProduct AS SP INNER JOIN Product AS P

ON SP.product_id = P.product_id;

（UNION、INTERSECT和EXCEPT添加纪录；联结添加列）

注意：1.联结时需要在FROM子句中使用多张表

2.内联结必须使用ON子句，且书写在FROM和WHERE之间

3.使用联结时SELECT子句中的列需要按照“<表的别名>.<列名>”的格式进行书写

内联结和WHERE子句的结合：

例：SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price

FROM ShopProduct AS SP INNER JOIN Product AS P

ON SP.product_id = P.product_id

WHERE SP.shop_id = '000A';

外联结：OUTER JOIN

例：SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price

FROM ShopProduct AS SP RIGHT OUTER JOIN Product AS P

ON SP.product_id = P.product_id;

注意：1.外联结选取表中全部信息

2.把哪张表作为主表，哪张表就是主表（选取该表中的全部数据），即关键字LEFT/RIGHT表示主表为左边/右边的表

三张表以上的联结：

例：SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price, IP.inventory_quantity

FROM ShopProduct AS SP INNER JOIN Product AS P

ON SP.product_id = P.product_id

INNER JOIN InventoryProduct AS IP

ON SP.product_id = IP.product_id

WHERE IP.inventory_id = 'S001';

交叉联结（笛卡尔积）：CROSS JOIN

例：SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name

FROM ShopProduct AS SP CROSS JOIN Product AS P;

联结的特定语法和过时语法：

例：SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price

FROM ShopProduct SP, Product P

WHERE SP.product_id = P.product_id

AND SP.shop_id = '000A'; （过时）

对比：SELECT SP.shop_id, SP.shop_name,SP.product_id,P.product_name, P.sale_price

FROM ShopProduct AS SP INNER JOIN Product AS P

ON SP.product_id = P.product_id

WHERE SP.shop_id = '000A'; （现在）

（上述两个代码结果一致，对于过时语法和特定语法，不建议使用，但要读懂）

关系除法：SQL中级水平

理解：由于乘法和除法是相辅相成的关系，除法运算的结果（商）乘以除数得到除法运算前的被除数，即通过商和除数相乘，也就是交叉联结，得到被除数的集合

第八章 SQL高级处理

窗口函数（OLAP函数）：<窗口函数> OVER (PARTITION BY <列清单>

ORDER BY <排序用列清单>)

能够作为窗口函数使用的函数：

1.聚合函数（SUM、AVG、COUNT、MAX、MIN）

2.RANK、DENSE_RANK、ROW_NUMBER等专用窗口函数

RANK函数：计算纪录排序

例：SELECT product_name, product_type, sale_price,

RANK () OVER (PARTITION BY product_type

ORDER BY sale_price) AS ranking

FROM Product;

PARTITION BY：排序的对象范围

ORDER BY：按照哪一列的何种顺序进行排序

例题理解即为：根据不同的product_type，按照sale_price进行升序排列

【窗口函数兼具分组（GROUP BY）和排序（ORDER BY）两种功能，但不具备汇总（GROUP BY）功能】

（PARTITION BY分组后的纪录集合合称为窗口，代表范围；GROUP BY分组后的纪录表示为组）

（不指定PARTITION BY将会对全部商品进行排序，而不是按照商品种类分组后的排序）

专用窗口函数的种类：

RANK函数：存在相同位次，会跳过之后的位次（三条纪录排在第一位：1，1，1，4，5）

DENSE_RANK函数：存在相同位次，不会跳过之后的位次（三条纪录排在第一位：1，1，1，2，3）

ROW_NUMBER函数：赋予唯一的连续位次（三条记录排在第一位：1，2，3，4，5）

三个对比：SELECT product_name, product_type, sale_price,

RANK () OVER (ORDER BY sale_price) AS ranking,

DENSE_RANK () OVER (ORDER BY sale_price) AS dense_ranking,

ROW_NUMBER () OVER (ORDER BY sale_price) AS row_num

FROM Product;

适用范围：只能在SELECT子句中，不能在WHERE子句或者GROUP BY子句中使用

作为窗口函数使用的聚合函数：

1.将SUM函数作为窗口函数使用

例：SELECT product_id, product_name, sale_price,

SUM (sale_price) OVER (ORDER BY product_id) AS current_sum

FROM Product;

按照ORDER BY子句指定的ｐｒｏｄｕｃｔ＿ｉｄ的升序进行排列，计算出商品编号对应商品的销售单价的合计值

２.将AVG函数作为窗口函数使用

例：SELECT product_id, product_name, sale_price,

AVG (sale_price) OVER (ORDER BY product_id) AS current_avg

　　FROM Product;

（其他聚合函数同理）

（以当前纪录作为基准进行统计，就是将聚合函数当作窗口函数使用的最大特征）

计算移动平均：在窗口中指定更加详细的汇总范围的备选功能，其中的汇总范围称为框架

1.将框架指定为当前纪录前/后n行

例：指定“最靠近的三行”为汇总对象

SELECT product_id, product_name, sale_price,

AVG (sale_price) OVER (ORDER BY product_id

ROWS 2 PRECEDING) AS moving_avg

FROM Product;

(1000/1)

(1000+500)/2

(1000+500+4000)/3

(500+4000+3000)/3

(4000+3000+6800)/3

ROW：行 PRECEDING：之前 FOLLOWING：之后

ROW 2 PRECEDING：截止到之前2行 ROW 2 FOLLOWING：截止到之后2行

2.将当前纪录的前后行作为汇总对象

例：SELECT product_id, product_name, sale_price,

AVG (sale_price) OVER (ORDER BY product_id

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg

FROM Product;

（注意：不管是一行还是多行，都是ROWS）

两个ORDER BY：在语句末尾使用ORDER BY子句对结果进行排序

例：SELECT product_name, product_type, sale_price,

RANK () OVER (ORDER BY sale_price) AS ranking

　　 FROM Product

　　ORDER BY ranking;

（两个ORDER BY的功能不同，前一个是将rank按照sale_price进行排序，后一个是按照rank的顺序进行排序）

（将聚合函数作为窗口函数使用时，会以当前纪录的基准来决定汇总对象的纪录）

同时得到合计行：SELECT '合计' AS product_type, SUM(sale_price)

FROM Product

UNION ALL

SELECT product_type, SUM(sale_price)

FROM Product

GROUP BY product_type;

ROLLUP（同时得出合计和小计）：

例:SELECT product_type, SUM(sale_price) AS sum_price

FROM Product

GROUP BY ROLLUP(product_type);

MySQL：将GROUP UP 子句改成GROUP BY product_type WITH ROLLUP

（一次计算出不同聚合键的组合结果，使用ROLLUP后得到合计行纪录称为超级分组纪录，默认NULL作为聚合键）

GROUPING函数：让NULL更加容易分辨由超级分组纪录产生的NULL返回1，其余返回0，便于分辨原始数据中的NULL和超级分组纪录中的NULL

第一个NULL是登记时就是NULL

第二个NULL是超级分组纪录的NULL

两个NULL不一样，不容易分辨

例：SELECT GROUPING(product_type) AS product_type,

GROUPING(regist_date) AS regist_date, SUM(sale_price) AS sum_price

FROM Product

GROUP BY ROLLUP(product_type, regist_date);

第二个是原始数据为NULL，返回0

第三个和第四个是超级分组记录产生的NULL，所以返回1

（具体业务中获取包含合计或者小计的汇总结果，利用ROLLUP和GROUPING）

（CAST（regist_date AS VARCHAR(16)）为了满足CASE表达式所有分支返回值一致，避免分别返回日期类型和字符串类型导致出错）

CUBE：将聚合键的所有可能的组合的汇总结果集中到一个结果中，组合结果为2的n次方（n是聚合键的个数）

SELECT CASE WHEN GROUPING(product_type) = 1

THEN '商品种类合计'

ELSE product_type END AS product_type,

CASE WHEN GROUPING(regist_date) = 1

THEN '登记日期合计'

ELSE CAST(regist_date AS VARCHAR(16)) END AS regist_date,

SUM(sale_price) AS sum_price

FROM Product

GROUP BY CUBE(product_type, regist_date);

本例中有product_type和regist_date两个聚合键，所以有四种组合，先按照商品种类分类，有三类，再对登记日期进行分类，有一类；前 9条纪录为办公用品，厨房用品，衣服以及其合计值，后面7条纪录为按照登记时间计算合计值；由于涉及regist_date数据的返回，为了避免分别返回日期类型和字符串类型导致出错，将不是超级分组纪录的regist_date数据通过CAST类型转换为VARCHAR

可以把CUBE理解为将使用聚合键进行切割的模块堆积成一个立方体（见书P280）

GROUPING SETS：取得ROLLUP或者CUBE结果中的部分纪录

例：SELECT CASE WHEN GROUPING(product_type) = 1

THEN '商品种类合计'

ELSE product_type END AS product_type,

CASE WHEN GROUPING(regist_date) = 1

THEN '登记日期合计'

ELSE CAST(regist_date AS VARCHAR(16)) END AS regist_date,

SUM(sale_price) AS sum_tanka

FROM Product

GROUP BY GROUPING SETS (product_type, regist_date);

目标是选取出将“商品种类”和“登记日期”各自作为聚合键；或者说是不选取“合计纪录同时使用两个聚合键的纪录”；由结果可知，只返回了以商品种类为聚合键时三个合计值，以日期为聚合键时5个不同日期和一个NULL的合计值，总合计值已经被排除在外

课后例题：使用Product表，计算出按照登记日期（regist_date）升序进行排序的各日期的销售单价（sale_price）的总额。排序需要将登记日期为NULL的“运动T恤”纪录排在第1位（也就是将其看作比其他日期都早）

ANSWER：-- ①regist_date为NULL时，将该记录放在最前显示。

SELECT regist_date, product_name, sale_price,

SUM (sale_price) OVER (ORDER BY regist_date NULLS FIRST) AS current_sum_price

FROM Product;

-- ②regist_date为NULL时，显示“1年1月1日”。

SELECT regist_date, product_name, sale_price,

SUM (sale_price) OVER (ORDER BY COALESCE(regist_date, CAST('0001-01-01' AS DATE))) AS current_sum_price

FROM Product;

第9章通过应用程序连接数据库

系统=应用+数据库

驱动：连接应用和数据库的特殊程序，两者插入驱动程序后，应用和数据库可以针对自身进行特别处理，以防编程语言或者DBMS发生变化，重新修改应用和SQL语句

驱动标准：ODBC（微软发布的DBMS连接标准）、JDBC（Java应用连接标准）

posted @ 2021-07-19 20:23 icui4cu 阅读(358) 评论(0) 编辑收藏举报

刷新页面返回顶部

icui4cu

PostgreSQL基础操作

公告