Mistj's Digital Garden

Appearance

PostgreSQL 12.2 手册

在 Windows 上安装 PostgreSQL 14

pg手册小结 ​

Where ​

不等运算符 (<>)运算符,等同于!= 。 如果数据库字段不为空，也不能查询到。数据库字段非空很有必要。

sql
-- 不会查询到name为空的数据
select * from document_directories where  name <> '中国';
-- 如果字段不是非空的，需要额外考虑到
select * from document_directories where  name <> '中国' or name is null;
-- 不会查询到name为空的数据
select * from document_directories where  name <> '中国';
-- 如果字段不是非空的，需要额外考虑到
select * from document_directories where  name <> '中国' or name is null;

BETWEEN 运算符用于检查一个值是否位于一个值区间之内(包含边界)，如果这个值介于指定的区间，BETWEEN 运算符返回真，否则返回假。

sql
select *  
from document_directories  
where  length(name) between 3 and 4
select *  
from document_directories  
where  length(name) between 3 and 4

Order By ​

  • 可使用 NULLS FIRST 或者 NULLS LAST 改变对 NULL 值的处理规则。
    • NULLS FIRST: null 值在非 null 值之前。
    • NULLS LAST: null值在非null值之后。
  • 默认情况下，PostgreSQL 采用升序排序时采用 NULLS LAST，降序排序时采用 NULLS FIRST 也就是说， PostgreSQL 默认 null 值比非 null 值大。

Order By自定义排序:

sql
select *  
from document_directories  
ORDER BY CASE name  
             WHEN '中国' THEN 1  
             WHEN '北京' THEN 2  
             WHEN '南京' THEN 3  
             END;
select *  
from document_directories  
ORDER BY CASE name  
             WHEN '中国' THEN 1  
             WHEN '北京' THEN 2  
             WHEN '南京' THEN 3  
             END;

fetch和limit的区别 ​

OFFSET子句必须在FETCH子句之前。但是，在PostgreSQL中，OFFSET和FETCH子句可以以任何顺序出现。

FETCH子句在功能上等同于LIMIT子句。如果您计划使您的应用程序与其他数据库系统兼容，您应该使用FETCH子句，因为它遵循标准SQL。

sql
select * from document_directories FETCH FIRST 5  
ROWS ONLY;
select * from document_directories FETCH FIRST 5  
ROWS ONLY;

DISTINCT ​

  • SELECT DISTINCT 返回一个没有重复行的结果集。
  • DISTINCT 后面可以指定一个列或者多个列，也可以用 *。
  • DISTINCT 将所有的 NULL 视为相等的，并只保留一个。
  • DISTINCT ON 子句用来返回每组重复值的第一个行。
  • 可以一同使用 DISTINCT 和 COUNT 关键词，来计算非重复结果的数目。
    • select count(distinct name) from document_directories

IN ​

带有子查询的 PostgreSQL IN 的表达式都可以使用 EXISTS 运算符 改写，并且 EXISTS 表达式具有更好的效率。上面的语句等同于以下使用 EXISTS 的语句：

sql
SELECT COUNT(*) 
FROM film 
WHERE film_id IN ( SELECT film_id FROM inventory );

SELECT COUNT(*)
FROM film f
WHERE EXISTS (
    SELECT 1
    FROM inventory i
    WHERE i.film_id = f.film_id
  );
SELECT COUNT(*) 
FROM film 
WHERE film_id IN ( SELECT film_id FROM inventory );

SELECT COUNT(*)
FROM film f
WHERE EXISTS (
    SELECT 1
    FROM inventory i
    WHERE i.film_id = f.film_id
  );

LIKE ​

ilike 忽略大小写

sql
select * from document_directories where  pinyin ilike 'eee'
select * from document_directories where  pinyin ilike 'eee'
  • % 匹配零或多个任意字符。
  • _ 匹配单个任意字符。
    • 举例 a_ 匹配以字符 a 开头长度为 2 字符串
    • 举例 _a 匹配以字符 a 结尾长度为 2 字符串。
  • 如果需要匹配通配符，则需要使用 \ 转义字符，如 \% 和 \_。

EXISTS ​

  • EXISTS 一般用在 WHERE 子句中。
  • EXISTS 是一个单目操作符，它需要一个子查询 subquery 作为参数。
  • 如果子查询 subquery 返回了至少一行（不论行中的值是否为 NULL），则 EXISTS 的计算结果为 TRUE，否则计算结果为 FALSE。
  • EXISTS 运算时，一旦子查询找到一个匹配的行，EXISTS 运算就会返回。这对提高查询新能很有帮助。
  • EXISTS 不关心子查询中的列的数量或者名称，它只在乎子查询是否返回行。所以在 EXISTS 的子查询中，无论你是使用 SELECT 1 还是 SELECT *，亦或是 SELECT column_list，都不影响 EXISTS 运算的结果。
  • NOT EXISTS 则是 EXISTS 的否定操作。

ALL ​

判断元素是否都满足，需要根据运算符进行比较。

sql
WHERE expr1 > ALL (subquery)

SELECT 2 = ALL(ARRAY[1, 2, 3]);  -- false
SELECT 2 > ALL(ARRAY[1, 2, 3]); -- false  
SELECT 0 < ALL(ARRAY[1, 2, 3]); -- true
SELECT 0 <> ALL(ARRAY[1, 2, 3]); -- 判断都不等于
WHERE expr1 > ALL (subquery)

SELECT 2 = ALL(ARRAY[1, 2, 3]);  -- false
SELECT 2 > ALL(ARRAY[1, 2, 3]); -- false  
SELECT 0 < ALL(ARRAY[1, 2, 3]); -- true
SELECT 0 <> ALL(ARRAY[1, 2, 3]); -- 判断都不等于

如果子查询不返回任何行，则 ALL 运算符的计算结果始终为真。

ANY ​

要检查一个数组中是否至少存在一个满足条件的值

HAVING ​

  • group by 之后，不能用where过滤列，因此有了having

HAVING 子句用于为带有 GROUP BY 子句的分组查询指定过滤条件。
HAVING 看起来与 WHERE 相似，虽然他们都是指定过滤条件，但是他们的区别是： WHERE 子句指定的条件用于过滤表中的行，而 HAVING 子句指定的条件用于过滤分组。

sql
SELECT name, count(*) t  
FROM document_directories  
GROUP BY name  
HAVING count(*) > 1
SELECT name, count(*) t  
FROM document_directories  
GROUP BY name  
HAVING count(*) > 1

GROUPING SETS/ ROLLUP ​

可以看做对多个group by进行了union all的操作。

sql
SELECT rating, rental_rate, count(*) FROM film 
	GROUP BY GROUPING SETS ((rating), (rental_rate), ()) 
	ORDER BY rating, rental_rate;
SELECT rating, rental_rate, count(*) FROM film 
	GROUP BY GROUPING SETS ((rating), (rental_rate), ()) 
	ORDER BY rating, rental_rate;

ROLLUP 子句都可以使用 GROUPING SETS 子句实现，比如：

  • ROLLUP(a, b) 等效于 GROUPING SETS((a,b), (a), ())。
  • ROLLUP(a, b, c) 等效于 GROUPING SETS((a,b,c), (a,b), (a), ())。

PostgreSQL ROLLUP 用法与实例