【PostgreSQL 17】3 查询

简单查询

别名

SELECT
	first_name AS "名字", 
	last_name AS "姓氏"
FROM
	employees
;

去重

SELECT
	DISTINCT department_id, job_id
FROM
	employees
;

对组合进行去重，而不只是department_id

无FROM快速查询

SELECT
	1+1 AS "结果",
	version() AS "版本"
;

利用查询条件过滤数据

BETWEEN

SELECT
	*
FROM
	employees
WHERE
	hire_date <= '2005-01-01'
AND
	salary BETWEEN 10000 AND 12000
;

IN

SELECT
	*
FROM
	employees
WHERE
	salary IN (10000, 11000)
;

字符串的模糊匹配 LIKE

ILIKE 不区分大小写

% 任意多个字符

SELECT
	first_name
FROM
	employees
WHERE
	first_name LIKE 'S%'
OR
	first_name LIKE '%s'
;

_ 任意一个字符

SELECT
	's ' LIKE 's_'
;

\转义字符

SELECT
	*
FROM
	employees
WHERE
	first_name LIKE 'S\%'
	-- first_name LIKE 'S#%' ESCAPE '#'
;

空值判断

IS NULL可以简写为ISNULL，
IS NOT NULL 可以简写为NOTNULL

SELECT
	*
FROM
	employees
WHERE
	manager_id IS NULL
;

IS [NOT] DISTINCT FROM 运算符支持空值比较

SELECT
	1 IS DISTINCT FROM null
;

复杂查询

AND

SELECT
	*
FROM 
	employees
WHERE
	first_name = 'Steven'
AND
	last_name = 'King'
;

OR

SELECT
	*
FROM 
	employees
WHERE
	first_name = 'Steven'
OR
	last_name = 'King'
;

注意点

OR 短路运算
1/0没有运算，所以不会报错。

SELECT
	1=1
OR
	1/0=1
;

AND 优先级更高

SELECT 
	*
FROM
	employees
WHERE
	salary = 10000
OR
	salary = 12000
AND
	department_id = 80
;

排序显示

默认为升序 ASC
降序 DESC
null在排序时视为最大值

SELECT 
	employee_id, first_name, last_name, hire_date, salary
FROM
	employees
ORDER BY
	first_name DESC
;

也可以用查询字段中的字段序号来排序

SELECT 
	employee_id, first_name, last_name, hire_date, salary
FROM
	employees
ORDER BY
	2 DESC
;

使用NULLS FIRST将NULL排到最前面

SELECT 
	employee_id, manager_id
FROM
	employees
ORDER BY
	manager_id NULLS FIRST
;

限制返回结果的数量

SELECT 
	employee_id, manager_id
FROM
	employees
FETCH FIRST 10 ROWS ONLY
;

SELECT 
	employee_id, manager_id
FROM
	employees
LIMIT 10
;

SELECT 
	employee_id, manager_id
FROM
	employees
FETCH FIRST 10 ROWS WITH TIES   -- 和最后值相同的行都会返回
;

分页查询 OFFSET

查询从第11个开始的10个

SELECT 
	employee_id, manager_id
FROM
	employees
OFFSET 10 ROWS
FETCH FIRST 10 ROWS ONLY
;

SELECT 
	employee_id, manager_id
FROM
	employees
LIMIT 10
OFFSET 10
;

参考资料

[1] 不剪发的Tony老师 PostgreSQL开发指南 第05节~第08节
[2] 不剪发的Tony老师 PostgreSQL开发指南 第09节~第11节