当前位置：首页 > news >正文

PGSQL学习笔记 -- 从入门到放弃

news 2024/11/28 20:27:16

pgsq学习笔记

一、基本语法
- （一）增删改查
- （二）PostgreSQL 语法详细介绍及示例
二、数据类型
- （一）基本数据类型
- （二）复合类型
- （三）JSON 和 XML 数据类型
- （四）地理数据类型
- （五）数组类型
- （六）自定义数据类型
- （七）表示方式和细节设置
- （八）使用示例
三、PostgreSQL 模式（SCHEMA）
- （一）介绍
- （二）PostgreSQL与MS SQL Server的区别：
- （三）创建和使用模式的示例。
四、PostgreSQL 运算符、函数和表达式
- （一）运算符
- （二）函数
- （三）表达式
五、PostgreSQL常用子句
- （一）Where 子句
- （二）LIMIT 子句
- （三）GROUP BY 语句
- （四）WITH 子句
六、PostgreSQL 约束
七、PostgreSQL 连接 (JOIN)
八、 PostgreSQL 时间/日期函数和操作符
⭐真·学习笔记&🥕拓展

一、基本语法

（一）增删改查

1、建表

CREATE TABLE IF NOT EXISTS public.testresult
(batch integer NOT NULL,descr text NOT NULL DEFAULT '',resultjsonb jsonb NOT NULL DEFAULT '',transdatetime timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP)
)

2、查询

SELECT batch, descr, resultjsonb, transdatetime
FROM public.testresult;

3、插入数据

INSERT INTO public.testresult(batch, descr, resultjsonb, transdatetime)
VALUES (?, ?, ?, ?);

4、更新

UPDATE public.testresult
SET batch=?, descr=?, resultjsonb=?, transdatetime=?
WHERE <condition>;

5、删除

DELETE FROM public.testresult WHERE <condition>;--清空表数据 delete
DELETE FROM public.testresult;
--清空表数据 truncate
TRUNCATE TABLE public.testresult;

Note:
TRUNCATE ：清空表但保留表结构和约束等，不会记录每行的删除，因此在处理大数据表时相比 DELETE 语句更为高效。但是，TRUNCATE 会永久删除所有行，无法恢复。

（二）PostgreSQL 语法详细介绍及示例

1. 数据库创建与管理

创建数据库
```
CREATE DATABASE database_name;
```
删除数据库
```
DROP DATABASE database_name;
```
连接数据库
```
\c database_name;
```

2. 表的创建与管理

创建表

CREATE TABLE table_name (column1 datatype,column2 datatype,column3 datatype
);

示例

CREATE TABLE users (id SERIAL PRIMARY KEY,name VARCHAR(100),email VARCHAR(100) UNIQUE
);

删除表
```
DROP TABLE table_name;
```

修改表结构

ALTER TABLE table_name ADD COLUMN column_name datatype;

3. 数据操作

插入数据

INSERT INTO table_name (column1, column2) VALUES (value1, value2);

示例

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

查询数据

SELECT column1, column2 FROM table_name WHERE condition;

示例

SELECT * FROM users WHERE id = 1;

更新数据

UPDATE table_name SET column1 = value1 WHERE condition;

示例

UPDATE users SET email = 'newemail@example.com' WHERE id = 1;

删除数据

DELETE FROM table_name WHERE condition;

示例

DELETE FROM users WHERE id = 1;

4. 事务管理

开始事务
```
BEGIN;
```
提交事务
```
COMMIT;
```
回滚事务
```
ROLLBACK;
```

5. 约束和索引

添加约束

ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column_name);

创建索引

CREATE INDEX index_name ON table_name (column_name);

6. 查询的数据排序与分组

排序

SELECT * FROM table_name ORDER BY column_name ASC|DESC;

分组

SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;

7. 函数和视图

创建视图

CREATE VIEW view_name AS SELECT columns FROM table_name WHERE condition;

创建函数

CREATE FUNCTION function_name(parameters) RETURNS return_type AS $$
BEGIN-- function body
END;
$$ LANGUAGE plpgsql;

8. 用户及权限管理

创建用户

CREATE USER username WITH PASSWORD 'password';

赋予权限

GRANT ALL PRIVILEGES ON DATABASE database_name TO username;

二、数据类型

（一）基本数据类型

整数类型：
- smallint：2 字节，范围 -32,768 到 32,767。
- integer（或 int）：4 字节，范围 -2,147,483,648 到 2,147,483,647。
- bigint：8 字节，范围 -9,223,372,036,854,775,808 到 9,223,372,036,854,775,807。
浮点数类型：
- real：4 字节，单精度浮点数。
- double precision：8 字节，双精度浮点数。
- numeric（或 decimal）：可变精度数值，精确到指定的小数位数。
字符类型：
- character varying(n)（或 varchar(n)）：可变长度字符串，最多 n 个字符。
- character(n)（或 char(n)）：固定长度字符串，不足的部分用空格填充。
- text：可变长度字符串，没有长度限制。
布尔类型：
- boolean：只包括 true、false 和 NULL。
日期和时间类型：
- date：日期值（年、月、日）。
- time：时间值（小时、分钟、秒），可以指定精度。
- timestamp：日期和时间的组合。
- interval：表示时间间隔或持续时间。

（二）复合类型

复合类型：
可以自定义复合数据类型，包含多个字段。其定义类似于表结构。

（三）JSON 和 XML 数据类型

JSON：
- json：存储 JSON 格式的数据。
- jsonb：存储二进制格式的 JSON 数据，操作更快且支持索引。
XML：
- xml：存储 XML 格式的数据。

（四）地理数据类型

PostGIS 扩展支持的地理数据类型：
- geometry：通用几何形状。
- geography：地理坐标（地球上的经纬度）。

（五）数组类型

PostgreSQL 允许字段存储数组，语法使用 {} 定义：

SELECT ARRAY[1, 2, 3]::integer[];  -- 整数数组

（六）自定义数据类型

PostgreSQL 允许用户创建自定义数据类型，可以用来满足特定需求。

（七）表示方式和细节设置

序列：用于生成唯一标识符。
枚举：定义一个自定义的枚举类型。
范围类型：定义一个范围（如 int4range, numrange）来表示具有下界和上界的范围。

（八）使用示例

CREATE TABLE example (id SERIAL PRIMARY KEY,name VARCHAR(100),created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,is_active BOOLEAN NOT NULL DEFAULT TRUE,score NUMERIC(5, 2),tags TEXT[],data JSONB
);

三、PostgreSQL 模式（SCHEMA）

（一）介绍

PostgreSQL中的模式（SCHEMA）是一个用于组织数据库对象（如表、视图、索引、函数等）的命名空间。每个数据库可以包含多个模式，允许用户有效地管理和区分不同的数据库对象。例如，在同一个数据库中，两个模式可以有相同名称的表，而不会产生冲突。

PostgreSQL模式的特点：

命名空间：模式提供了一个逻辑分离，使得不同的用户或应用程序可以在同一个数据库中而不互相干扰。
权限管理：可以为每个模式设置不同的权限，控制用户对特定模式中对象的访问。
默认模式：PostgreSQL有一个默认模式称为“public”，如果没有指定模式，数据库对象会被创建在此模式下。
易于管理：通过使用模式，可以将相关的数据库对象组织在一起，便于维护和管理。

（二）PostgreSQL与MS SQL Server的区别：

结构：
- PostgreSQL使用模式（SCHEMA）作为命名空间。
- MS SQL Server使用架构（SCHEMA），虽然概念相似，但在实现细节上有所不同。
默认架构：
- PostgreSQL有一个默认的public模式。
- MS SQL Server有一个默认的dbo架构（database owner），并且schema与数据库用户的概念更为紧密。
用户与权限管理：
- 在PostgreSQL中，模式权限与对象权限可以独立管理。
- 在MS SQL Server中，架构通常与用户权限直接相关，用户拥有的架构决定了他们可以创建的对象。
支持的对象类型：
- PostgreSQL中的模式可以包含几乎所有类型的数据库对象。
- MS SQL Server的架构也可以包含多种类型的对象，但在某些细节上（例如存储过程和函数的分类）可能有所不同。
查询方式：
- 在PostgreSQL中，可以通过schema_name.object_name的方式查询对象。
- 在MS SQL Server中，查询方式相似，但一般使用更加规范化的形式。

（三）创建和使用模式的示例。

1. 创建模式

在PostgreSQL中，使用CREATE SCHEMA语句来创建一个新的模式。基本语法如下：

CREATE SCHEMA schema_name;

示例：

CREATE SCHEMA sales;

上述命令在当前数据库中创建一个名为sales的模式。

2. 创建模式并指定所有者

可以在创建模式时指定它的所有者。所有者通常是一个用户或角色。语法如下：

CREATE SCHEMA schema_name AUTHORIZATION user_name;

示例：

CREATE SCHEMA marketing AUTHORIZATION johndoe;

这会创建一个名为marketing的模式，并将其所有权分配给名为johndoe的用户。

3. 创建表和其他对象在模式中

使用模式的主要目的之一是将表和其他对象组织在一起。可以在特定模式中创建表，语法如下：

CREATE TABLE schema_name.table_name (column_name data_type,...
);

示例：

CREATE TABLE sales.orders (order_id SERIAL PRIMARY KEY,order_date DATE NOT NULL,customer_id INT NOT NULL
);

这将在sales模式中创建一个名为orders的表。

4. 查询模式中的表

要查询特定模式中的表，可以使用模式名作为前缀。基本语法如下：

SELECT * FROM schema_name.table_name;

示例：

SELECT * FROM sales.orders;

5. 列出所有模式

使用以下SQL查询可以列出当前数据库中的所有模式：

SELECT schema_name FROM information_schema.schemata;

6. 修改模式

要修改模式的结构或名称，可以使用ALTER SCHEMA命令。例如，重命名模式的语法如下：

ALTER SCHEMA old_schema_name RENAME TO new_schema_name;

示例：

ALTER SCHEMA sales RENAME TO sales_data;

7. 删除模式

当不再需要某个模式时，可以使用DROP SCHEMA来删除它。注意，删除模式会删除该模式下的所有对象。

DROP SCHEMA schema_name CASCADE;

示例：

DROP SCHEMA sales_data CASCADE;

8. 权限管理

可以为模式设置权限，以控制用户对该模式及其对象的访问。使用GRANT和REVOKE命令进行权限管理。

示例：授权用户对模式的使用权限

GRANT USAGE ON SCHEMA sales TO johndoe;

示例：取消用户对模式的使用权限

REVOKE USAGE ON SCHEMA sales FROM johndoe;

四、PostgreSQL 运算符、函数和表达式

（一）运算符

1. 算术运算符
用于执行基本的数学运算。

+：加法
-：减法
*：乘法
/：除法
%：取模（求余）

示例：

SELECT 10 + 5; -- 结果：15
SELECT 10 - 5; -- 结果：5
SELECT 10 * 5; -- 结果：50
SELECT 10 / 5; -- 结果：2
SELECT 10 % 3; -- 结果：1

2. 比较运算符
用于比较两个值。

=：等于
<> 或 !=：不等于
>：大于
<：小于
>=：大于等于
<=：小于等于

示例：

SELECT 5 = 5; -- 结果：true
SELECT 5 <> 5; -- 结果：false
SELECT 5 > 3; -- 结果：true
SELECT 5 < 3; -- 结果：false
SELECT 5 >= 5; -- 结果：true
SELECT 5 <= 3; -- 结果：false

3. 逻辑运算符
用于组合多个条件。

AND：逻辑与
OR：逻辑或
NOT：逻辑非

示例：

SELECT 5 > 3 AND 4 < 6; -- 结果：true
SELECT 5 > 3 OR 4 > 6; -- 结果：true
SELECT NOT (5 > 3); -- 结果：false

4. 字符串运算符
用于字符串操作。

||：字符串连接

示例：

SELECT 'Hello' || ' ' || 'World'; -- 结果：Hello World

5. 集合运算符
用于集合操作。

UNION：合并两个查询结果集，并去除重复项
UNION ALL：合并两个查询结果集，保留重复项
INTERSECT：返回两个查询结果集的交集
EXCEPT：返回两个查询结果集的差集

示例：

-- 假设有两张表 A 和 B
SELECT * FROM A
UNION
SELECT * FROM B; -- 合并 A 和 B 的结果集，去除重复项SELECT * FROM A
UNION ALL
SELECT * FROM B; -- 合并 A 和 B 的结果集，保留重复项SELECT * FROM A
INTERSECT
SELECT * FROM B; -- 返回 A 和 B 的交集SELECT * FROM A
EXCEPT
SELECT * FROM B; -- 返回 A 和 B 的差集

6. 位运算符
用于位操作。

&：按位与
|：按位或
~：按位非
^：按位异或
<<：左移
>>：右移

示例：

SELECT 5 & 3; -- 结果：1
SELECT 5 | 3; -- 结果：7
SELECT ~5; -- 结果：-6
SELECT 5 ^ 3; -- 结果：6
SELECT 5 << 1; -- 结果：10
SELECT 5 >> 1; -- 结果：2

7. 其他运算符

IS：用于比较值是否为 NULL
IS NOT：用于比较值是否不为 NULL
BETWEEN：用于检查值是否在某个范围内
IN：用于检查值是否在给定的列表中
LIKE：用于模式匹配
ILIKE：不区分大小写的模式匹配

示例：

SELECT NULL IS NULL; -- 结果：true
SELECT 5 IS NOT NULL; -- 结果：true
SELECT 5 BETWEEN 1 AND 10; -- 结果：true
SELECT 5 IN (1, 3, 5, 7); -- 结果：true
SELECT 'abc' LIKE 'a%'; -- 结果：true
SELECT 'ABC' ILIKE 'a%'; -- 结果：true

（二）函数

PostgreSQL 提供了丰富的内置函数，用于处理各种数据类型，包括字符串、数值、日期时间等。以下是一些常用的 PostgreSQL 函数及其示例。

1. 字符串函数

1.1 LENGTH(string)
返回字符串的长度。

SELECT LENGTH('Hello, World!'); -- 结果: 13

1.2 LOWER(string)
将字符串转换为小写。

SELECT LOWER('Hello, World!'); -- 结果: hello, world!

1.3 UPPER(string)
将字符串转换为大写。

SELECT UPPER('Hello, World!'); -- 结果: HELLO, WORLD!

1.4 TRIM([BOTH | LEADING | TRAILING] [characters] FROM string)
去除字符串两端、开头或结尾的指定字符，默认为空格。

SELECT TRIM('  Hello, World!  '); -- 结果: Hello, World!
SELECT TRIM(BOTH 'x' FROM 'xxxHello, World!xxx'); -- 结果: Hello, World!

1.5 SUBSTRING(string FROM start FOR length)
从字符串中提取子字符串。

SELECT SUBSTRING('Hello, World!' FROM 8 FOR 5); -- 结果: World

1.6 CONCAT(string1, string2, ...)
连接多个字符串。

SELECT CONCAT('Hello, ', 'World!', '!'); -- 结果: Hello, World!

1.7 REPLACE(string, from_string, to_string)
将字符串中的指定部分替换为另一部分。

SELECT REPLACE('Hello, World!', 'World', 'PostgreSQL'); -- 结果: Hello, PostgreSQL!

⭐字符串函数拓展：

--String函数
--1、字符串连接
select 'Hello'||', '||'PGSQL! ';	--
--2、字符串长度
select LENGTH('PostgreSQL');
--3、子字符串
select SUBSTRING('This is test txt, Hello~ PostgreSQL! ' FROM 9 FOR 4);
--4、字符串位置
--返回子字符串在字符串中的起始位置
select POSITION('test' IN 'This is test txt, Hello~ PostgreSQL! ');
--与 POSITION 类似，但参数顺序不同
select STRPOS('This is test txt, Hello~ PostgreSQL! ', 'test');
--5、字符串替换 
--REPLACE(string, from_string, to_string)：将字符串中的所有 from_string 替换为 to_string
select REPLACE('abc123abc456aBc789', 'abc', '***');	-- ***123***456aBc789
--6、字符串修剪
--TRIM([LEADING|TRAILING|BOTH] [remstr FROM] string)：从字符串的开始、结束或两侧修剪指定的字符或空白。--7、字符串分割
--SPLIT_PART(string, delimiter, part_number)：根据分隔符将字符串分割成多个部分，并返回指定字段的部分。
--string 是要进行拆分的字符串;delimiter 是分隔符;part_number 是要返回的部分（1 为第一个部分，2 为第二个部分，依此类推）。
select SPLIT_PART('abc123;abc456;aBc789;', ';', 2);
select SPLIT_PART('abc123;abc456;aBc789;', 'ab', 2);
--8、字符串转换：
--TO_CHAR(timestamp, format)：将时间戳或数字转换为指定格式的字符串。
select TO_CHAR(now(), 'YYYY-MM-DD'),TO_CHAR(now(), 'YYYY/MM/DD'),TO_CHAR(now(), 'YYYYMMDD');
--'to_char'		'to_char-2'		'to_char-3'
--2024-11-28	2024/11/28		20241128--TO_DATE(text, text)：将字符串转换为日期类型，需要指定日期格式。
--TO_NUMBER(text, text)：将字符串转换为数字类型，需要指定数字格式。
--9、字符串重叠
--OVERLAY(string PLACING newstr FROM start FOR len)：用 newstr 替换 string 中从 start 位置开始的 len 个字符
select OVERLAY('abc-123-def-789' PLACING '*' FROM 5 FOR 3);	--abc-*-def-789
--10、字符串初始化
--INITCAP(string)：将字符串中每个单词的首字母转换为大写，其余字母转换为小写。
select INITCAP('tHIs is a STRing');	--This Is A String--11、字符串填充
--LPAD(string text, length integer, fill text) 和 RPAD(string text, length integer, fill text)：分别在字符串的左侧或右侧填充指定的字符，直到达到指定的长度。
select LPAD('abc-123', 10, '*');	--***abc-123
select RPAD('abc-123', 10, '*');	--abc-123***--12、字符串反转
--pgsql中没有内置的字符串反转函数，但是我们可以通过多种方式实现字符串的反转
--拓展：创建一个函数，实现字符串的反转

⭐⭐⭐字符串函数综合运用：创建一个函数，实现字符串反转功能
创建函数

CREATE OR REPLACE FUNCTION reverse_string(p_strinput TEXT)
RETURNS TEXT 
AS $$
DECLARE v_len int := length(p_strinput);v_stroutput text := '';
BEGINif p_strinput='' THENv_stroutput:='输入字符串不能为空！';RETURN v_stroutput;end if;while v_len>0 loopv_stroutput := v_stroutput || SUBSTRING(p_strinput FROM v_len FOR 1); --每次循环只取当前循环位置的一个字符v_len := v_len - 1;end loop;return v_stroutput;
END;
$$ LANGUAGE plpgsql;

测试：

select reverse_string('abc-123')

在这里插入图片描述

2. 数值函数
2.1 ABS(number)
返回数值的绝对值。

SELECT ABS(-10); -- 结果: 10

2.2 CEIL(number)
返回大于或等于给定数值的最小整数。

SELECT CEIL(3.14); -- 结果: 4

2.3 FLOOR(number)
返回小于或等于给定数值的最大整数。

SELECT FLOOR(3.14); -- 结果: 3

2.4 ROUND(number, decimals)
将数值四舍五入到指定的小数位数。

SELECT ROUND(3.14159, 2); -- 结果: 3.14

2.5 POWER(base, exponent)
返回基数的指数次幂。

SELECT POWER(2, 3); -- 结果: 8

2.6 SQRT(number)
返回数值的平方根。

SELECT SQRT(16); -- 结果: 4

3. 日期时间函数

3.1 CURRENT_DATE
返回当前日期。

SELECT CURRENT_DATE; -- 结果: 2023-10-01

3.2 CURRENT_TIME
返回当前时间。

SELECT CURRENT_TIME; -- 结果: 12:34:56.789

3.3 CURRENT_TIMESTAMP
返回当前日期和时间。

SELECT CURRENT_TIMESTAMP; -- 结果: 2023-10-01 12:34:56.789

3.4 EXTRACT(field FROM timestamp)
从时间戳中提取指定的部分，如年、月、日、小时等。

SELECT 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;

3.5 AGE(timestamp, timestamp)
计算两个日期之间的年龄（以时间间隔的形式返回）。

SELECT AGE('2023-10-01'::date, '2020-01-01'::date); -- 结果: 3 years 9 mons 0 days

3.6 TO_CHAR(timestamp, format)
将时间戳格式化为字符串。

SELECT TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH:MI:SS'); -- 结果: 2023-10-01 12:34:56

3.7 TO_DATE(text, format)
将字符串转换为日期。

SELECT TO_DATE('2023-10-01', 'YYYY-MM-DD'); -- 结果: 2023-10-01

3.8 TO_TIMESTAMP(text, format)
将字符串转换为时间戳。

SELECT TO_TIMESTAMP('2023-10-01 12:34:56', 'YYYY-MM-DD HH:MI:SS'); -- 结果: 2023-10-01 12:34:56

3.9 DATE_TRUNC(text, timestamp)
将时间戳截断到指定的精度。

SELECT DATE_TRUNC('day', CURRENT_TIMESTAMP); -- 结果: 2023-10-01 00:00:00

4. 聚合函数
4.1 COUNT(column)
返回指定列的行数。

SELECT COUNT(*) FROM employees; -- 统计表中的总行数

4.2 SUM(column)
返回指定列的总和。

SELECT SUM(salary) FROM employees; -- 统计所有员工的工资总和

4.3 AVG(column)
返回指定列的平均值。

SELECT AVG(salary) FROM employees; -- 计算所有员工的平均工资

4.4 MIN(column)
返回指定列的最小值。

SELECT MIN(salary) FROM employees; -- 找出最低工资

4.5 MAX(column)
返回指定列的最大值。

SELECT MAX(salary) FROM employees; -- 找出最高工资

5. 条件函数
5.1 COALESCE(value1, value2, ...)
返回第一个非 NULL 的值。

SELECT COALESCE(NULL, 'default', 'fallback'); -- 结果: default

5.2 CASE WHEN condition THEN result [WHEN condition THEN result ...] ELSE result END
根据条件返回不同的值。

SELECT name,CASE WHEN salary > 5000 THEN 'High'WHEN salary > 3000 THEN 'Medium'ELSE 'Low'END AS salary_level
FROM employees;

（三）表达式

1. 算术表达式
用于执行数学运算的表达式。

示例：

SELECT 2 + 3 AS sum, 10 - 4 AS difference, 5 * 6 AS product, 20 / 4 AS quotient;

2. 字符串表达式
用于操作字符串，通常使用连接运算符 ||。

示例：

SELECT 'Hello, ' || 'World!' AS greeting;

3. 布尔表达式
用于返回布尔值（真或假）的表达式。

示例：

SELECT (10 > 5) AS is_greater, (3 = 4) AS is_equal;

4. 比较表达式
用于比较两个值，并返回布尔值。

示例：

SELECT * FROM employees WHERE salary > 50000;

5. CASE 表达式
用于实现条件判断，可以返回不同的值。

示例：

SELECT name, CASE WHEN salary < 30000 THEN 'Low'WHEN salary BETWEEN 30000 AND 60000 THEN 'Medium'ELSE 'High'END AS salary_range
FROM employees;

6. 数组和 JSON 表达式
PostgreSQL 支持处理数组和 JSON 数据类型。

示例：
数组：

SELECT ARRAY[1, 2, 3, 4] AS my_array;

JSON：

SELECT '{"name": "John", "age": 30}'::json AS my_json;

7. 聚合表达式
用于对一组值进行计算并返回单个值的表达式，通常与 GROUP BY 一起使用。

示例：

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

8. 函数表达式
使用内置函数进行计算或操作。

示例：

SELECT NOW() AS current_time, LENGTH('PostgreSQL') AS string_length;

9. 子查询
在表达式中嵌套查询，通常用于复杂的查询逻辑。

示例：

SELECT name 
FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

五、PostgreSQL常用子句

（一）Where 子句

基本语法
WHERE 子句通常与 SELECT、UPDATE 和 DELETE 语句一起使用。基本语法如下：

SELECT column1, column2, ...
FROM table_name
WHERE condition;

主要运算符
在 WHERE 子句中，可以使用多种运算符来定义条件：

比较运算符：
- =：等于
- <> 或 !=：不等于
- >：大于
- <：小于
- >=：大于等于
- <=：小于等于
逻辑运算符：
- AND：与
- OR：或
- NOT：非
其他运算符：
- BETWEEN ... AND ...：在范围内
- LIKE：匹配模式
- IN (...)：在一组值中

示例

简单的条件筛选：
```
SELECT * FROM employees
WHERE department = 'Sales';
```
这个查询将返回所有在销售部门的员工。
使用 AND 运算符：
```
SELECT * FROM employees
WHERE department = 'Sales' AND salary > 50000;
```
这个查询将返回所有在销售部门且薪水高于 50000 的员工。

使用 OR 运算符：

SELECT * FROM employees
WHERE department = 'Sales' OR department = 'Marketing';

这个查询将返回所有在销售或市场部门的员工。

使用 NOT 运算符：
```
SELECT * FROM employees
WHERE NOT department = 'HR';
```
这个查询将返回所有不在 HR 部门的员工。
使用 BETWEEN 运算符：
```
SELECT * FROM employees
WHERE salary BETWEEN 30000 AND 70000;
```
这个查询将返回所有薪水在 30000 到 70000 之间的员工。
使用 LIKE 运算符：
```
SELECT * FROM employees
WHERE name LIKE 'A%';
```
这个查询将返回所有名字以 A 开头的员工。
使用 IN 运算符：
```
SELECT * FROM employees
WHERE department IN ('Sales', 'Marketing', 'IT');
```
这个查询将返回所有在销售、市场或 IT 部门的员工。

（二）LIMIT 子句

PostgreSQL 中的 LIMIT 子句用于限制查询结果的数量。它常与 SELECT 语句一起使用，可以控制返回的行数。通常在需要分页或者仅获取部分结果时使用。

基本语法

SELECT column1, column2, ...
FROM table_name
WHERE condition
LIMIT number;

number 是你希望返回的行数。

基本使用：

假设有一个名为 employees 的表，结构如下：

id name position
1 Alice Manager
2 Bob Developer
3 Charlie Designer
4 David Developer
5 Emma Manager
查询返回前 3 行数据：
```
SELECT * FROM employees
LIMIT 3;
```
返回结果：

id name position
1 Alice Manager
2 Bob Developer
3 Charlie Designer
与 OFFSET 一起使用：

LIMIT 可以与 OFFSET 结合使用，允许用户跳过前面的若干行。例如，假设你想获取第 2 页的数据，每页 2 行，可以这样写：
```
SELECT * FROM employees
ORDER BY id
LIMIT 2
OFFSET 2;
```
返回结果：

id name position
3 Charlie Designer
4 David Developer
在有序查询中使用：

在使用 ORDER BY 的查询中，使用 LIMIT 可确保获取到你需要的具体行。例如，获取所有员工中工资最高的 2 个员工（假设存在 salary 列）。
```
SELECT name, salary FROM employees
ORDER BY salary DESC
LIMIT 2;
```

id	name	position
1	Alice	Manager
2	Bob	Developer
3	Charlie	Designer
4	David	Developer
5	Emma	Manager

id	name	position
1	Alice	Manager
2	Bob	Developer
3	Charlie	Designer

id	name	position
3	Charlie	Designer
4	David	Developer

Note：

LIMIT 用于限制结果集的行数，但并不保证返回数据的顺序。如果想要有确定的返回顺序，务必使用 ORDER BY 子句。
如果使用了 OFFSET，那么会先跳过指定的行再应用 LIMIT 来限制返回结果。

（三）GROUP BY 语句

GROUP BY 语句在 PostgreSQL 中用于对结果集进行分组。它通常与聚合函数（如 COUNT、SUM、AVG 等）一起使用，以对每个分组进行计算。

基本语法

SELECT column1, aggregation_function(column2)
FROM table_name
WHERE condition
GROUP BY column1;

column1 是需要分组的字段。
aggregation_function 是用于聚合的函数，如 COUNT、SUM 等。

示例

假设我们有一个名为 sales 的表，结构如下：

id	product	quantity	price
1	A	10	100
2	B	20	150
3	A	5	100
4	B	10	150
5	C	15	200

计算每个产品的销售数量总和：

SELECT product, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product;

返回结果：

product	total_quantity
A	15
B	30
C	15

计算每个产品的销售总额：

可以将 quantity 和 price 相乘来获取每个产品的销售总额：
```
SELECT product, SUM(quantity * price) AS total_sales
FROM sales
GROUP BY product;
```
返回结果：

product total_sales
A 1500
B 3000
C 3000
使用 HAVING 子句过滤分组结果：

有时我们希望在分组后进行过滤，可以使用 HAVING 子句。例如，获取销售数量总和大于 20 的产品：
```
SELECT product, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product
HAVING SUM(quantity) > 20;
```
返回结果：

product total_quantity
B 30
组合多个字段进行分组：

如果有多个字段可以进行分组，比如销售日期和产品，假设我们的 sales 表增加了 sale_date 列，我们可以这样做：
```
SELECT sale_date, product, SUM(quantity) AS total_quantity
FROM sales
GROUP BY sale_date, product
ORDER BY sale_date, product;
```

product	total_sales
A	1500
B	3000
C	3000

product	total_quantity
B	30

Note：

在 SELECT 语句中，所有未包含在聚合函数中的字段必须出现在 GROUP BY 子句中。
HAVING 子句是在 GROUP BY 操作后的过滤，用于限制分组后的结果集，而 WHERE 子句是在分组之前限制行。

（四）WITH 子句

WITH 子句（也称为 Common Table Expressions，CTE）是一种在 SQL 查询中定义临时结果集的方法。这些临时结果集可以在同一个查询中被多次引用，使得复杂的查询更加清晰和易于管理。

优点

提高可读性：将复杂的查询分解成多个逻辑部分，每个部分都有一个明确的名称。
避免重复计算：可以在多个地方重用同一个 CTE，而不需要重复计算。
递归查询：支持递归查询，适用于层次结构数据的处理。

语法

WITH cte_name AS (-- 子查询
)
SELECT ...
FROM cte_name
JOIN other_table ON ...
WHERE ...

示例

示例 1：基本 CTE
假设有一个 employees 表，包含员工的 ID 和姓名，以及他们的经理 ID。

WITH manager_names AS (SELECT id, nameFROM employees
)
SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN manager_names m ON e.manager_id = m.id;

在这个例子中，manager_names CTE 从 employees 表中提取了所有经理的 ID 和姓名，然后在主查询中将其与 employees 表进行连接，以显示每个员工及其经理的姓名。

示例 2：递归 CTE
假设有一个 categories 表，表示产品分类，其中每个分类可以有父分类。

WITH RECURSIVE category_tree AS (-- 基础情况：选择顶级分类SELECT id, name, parent_idFROM categoriesWHERE parent_id IS NULLUNION ALL-- 递归情况：选择子分类SELECT c.id, c.name, c.parent_idFROM categories cJOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

在这个例子中，category_tree CTE 首先选择了所有顶级分类（即 parent_id 为 NULL 的分类），然后递归地选择了所有子分类，最终生成了一个包含所有分类及其层级关系的结果集。

示例 3：多个 CTE
假设有一个 orders 表和一个 order_items 表，分别存储订单和订单项信息。

WITH total_orders AS (SELECT order_id, SUM(quantity * price) AS total_priceFROM order_itemsGROUP BY order_id
),
high_value_orders AS (SELECT order_id, total_priceFROM total_ordersWHERE total_price > 1000
)
SELECT o.order_id, o.customer_id, h.total_price
FROM orders o
JOIN high_value_orders h ON o.order_id = h.order_id;

在这个例子中，total_orders CTE 计算了每个订单的总价，high_value_orders CTE 选择了总价超过 1000 的订单，最后的主查询将这些高价值订单与 orders 表进行连接，以显示订单 ID、客户 ID 和总价。

Note：
WITH 子句是 PostgreSQL 中非常强大的工具，可以帮助你编写更清晰、更高效的查询。通过将复杂的查询分解成多个逻辑部分，你可以更容易地管理和调试代码。

六、PostgreSQL 约束

PostgreSQL 约束详细介绍
在 PostgreSQL 中，约束（Constraints）用于确保数据库中的数据完整性。约束可以应用于表的列或整个表，以确保数据符合特定的规则和条件。常见的约束类型包括：

NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
EXCLUDE

1. NOT NULL 约束
NOT NULL 约束确保列中的值不能为空。

语法

CREATE TABLE table_name (column_name data_type NOT NULL,...
);

示例

CREATE TABLE employees (id SERIAL PRIMARY KEY,name VARCHAR(100) NOT NULL,department VARCHAR(50)
);

2. UNIQUE 约束
UNIQUE 约束确保列中的所有值都是唯一的，但允许空值。

语法

CREATE TABLE table_name (column_name data_type UNIQUE,...
);

示例

CREATE TABLE employees (id SERIAL PRIMARY KEY,email VARCHAR(100) UNIQUE,name VARCHAR(100)
);

3. PRIMARY KEY 约束
PRIMARY KEY 约束唯一标识表中的每一行，并且不允许空值。一个表只能有一个主键。

语法

CREATE TABLE table_name (column_name data_type PRIMARY KEY,...
);

示例

CREATE TABLE employees (id SERIAL PRIMARY KEY,name VARCHAR(100),department VARCHAR(50)
);

4. FOREIGN KEY 约束
FOREIGN KEY 约束用于建立两个表之间的关系，确保一个表中的数据引用另一个表中的有效数据。

语法

CREATE TABLE table_name (column_name data_type,FOREIGN KEY (column_name) REFERENCES referenced_table(referenced_column),...
);

示例

CREATE TABLE departments (id SERIAL PRIMARY KEY,name VARCHAR(50)
);CREATE TABLE employees (id SERIAL PRIMARY KEY,name VARCHAR(100),department_id INT,FOREIGN KEY (department_id) REFERENCES departments(id)
);

5. CHECK 约束
CHECK 约束确保列中的值满足特定的条件。

语法

CREATE TABLE table_name (column_name data_type CHECK (condition),...
);

示例

CREATE TABLE employees (id SERIAL PRIMARY KEY,age INT CHECK (age >= 18),name VARCHAR(100)
);

6. EXCLUDE 约束
EXCLUDE 约束用于确保表中的某些列组合不会冲突。通常用于处理多列唯一性约束。

语法

CREATE TABLE table_name (column1 data_type,column2 data_type,EXCLUDE USING gist (column1 WITH =, column2 WITH &&)
);

示例

CREATE TABLE reservations (room_id INT,during TSRange,EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

7、添加和删除约束

添加约束
可以使用 ALTER TABLE 语句添加约束。

示例

ALTER TABLE employees
ADD CONSTRAINT unique_email UNIQUE (email);

删除约束
可以使用 ALTER TABLE 语句删除约束。

示例

ALTER TABLE employees
DROP CONSTRAINT unique_email;

总结：
约束是 PostgreSQL 中确保数据完整性和一致性的强大工具。通过使用 NOT NULL、UNIQUE、PRIMARY KEY、FOREIGN KEY、CHECK 和 EXCLUDE 约束，可以有效地管理表中的数据，防止无效或重复的数据进入数据库。

七、PostgreSQL 连接 (JOIN)

PostgreSQL 连接 (JOIN) 详细介绍
在 PostgreSQL 中，连接（JOIN）用于将两个或多个表中的行组合在一起，基于某些相关的列。常见的连接类型包括：

INNER JOIN
LEFT JOIN（左连接）
RIGHT JOIN（右连接）
FULL OUTER JOIN（全外连接）
CROSS JOIN（交叉连接）
SELF JOIN（自连接）

1. INNER JOIN
INNER JOIN 返回两个表中匹配的行。

语法

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

示例
假设有两个表 employees 和 departments：

CREATE TABLE employees (id SERIAL PRIMARY KEY,name VARCHAR(100),department_id INT
);CREATE TABLE departments (id SERIAL PRIMARY KEY,name VARCHAR(50)
);INSERT INTO departments (name) VALUES ('HR'), ('IT'), ('Finance');
INSERT INTO employees (name, department_id) VALUES ('Alice', 1), ('Bob', 2), ('Charlie', 1), ('David', 3);

查询所有员工及其所属部门：

SELECT e.name AS employee, d.name AS department
FROM employees e
INNER JOIN departments d
ON e.department_id = d.id;

结果：

 employee | department
----------+------------Alice    | HRBob      | ITCharlie  | HRDavid    | Finance

2. LEFT JOIN
LEFT JOIN 返回左表中的所有行，以及右表中匹配的行。如果右表中没有匹配的行，则结果集中右表的列将包含 NULL。

语法

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

示例
查询所有员工及其所属部门，即使有些员工没有分配部门：

SELECT e.name AS employee, d.name AS department
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.id;

假设 employees 表中有一条记录 ('Eve', NULL)：

结果：

 employee | department
----------+------------Alice    | HRBob      | ITCharlie  | HRDavid    | FinanceEve      | NULL

3. RIGHT JOIN
RIGHT JOIN 返回右表中的所有行，以及左表中匹配的行。如果左表中没有匹配的行，则结果集中左表的列将包含 NULL。

语法

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

示例
查询所有部门及其员工，即使有些部门没有员工：

SELECT e.name AS employee, d.name AS department
FROM employees e
RIGHT JOIN departments d
ON e.department_id = d.id;

假设 departments 表中有一条记录 ('Marketing', 4)：

结果：

 employee | department
----------+------------Alice    | HRBob      | ITCharlie  | HRDavid    | FinanceNULL     | Marketing

4. FULL OUTER JOIN
FULL OUTER JOIN 返回两个表中的所有行。如果某个表中没有匹配的行，则结果集中该表的列将包含 NULL。

语法

SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;

示例
查询所有员工及其所属部门，即使有些员工没有分配部门，有些部门没有员工：

SELECT e.name AS employee, d.name AS department
FROM employees e
FULL OUTER JOIN departments d
ON e.department_id = d.id;

假设 employees 表中有一条记录 ('Eve', NULL)，departments 表中有一条记录 ('Marketing', 4)：

结果：

 employee | department
----------+------------Alice    | HRBob      | ITCharlie  | HRDavid    | FinanceEve      | NULLNULL     | Marketing

5. CROSS JOIN
CROSS JOIN 返回两个表的笛卡尔积，即第一个表的每一行与第二个表的每一行组合。

语法

SELECT columns
FROM table1
CROSS JOIN table2;

示例
查询所有员工和所有部门的组合：

SELECT e.name AS employee, d.name AS department
FROM employees e
CROSS JOIN departments d;

结果：

 employee | department
----------+------------Alice    | HRAlice    | ITAlice    | FinanceAlice    | MarketingBob      | HRBob      | ITBob      | FinanceBob      | MarketingCharlie  | HRCharlie  | ITCharlie  | FinanceCharlie  | MarketingDavid    | HRDavid    | ITDavid    | FinanceDavid    | MarketingEve      | HREve      | ITEve      | FinanceEve      | Marketing

6. SELF JOIN
SELF JOIN 是一个表与其自身的连接，通常用于处理层次结构数据。

语法

SELECT columns
FROM table1 t1
JOIN table1 t2
ON t1.column = t2.column;

示例
假设有一个 employees 表，包含员工的 ID、姓名和经理 ID：

CREATE TABLE employees (id SERIAL PRIMARY KEY,name VARCHAR(100),manager_id INT
);INSERT INTO employees (name, manager_id) VALUES
('Alice', NULL),
('Bob', 1),
('Charlie', 1),
('David', 2);

查询每个员工及其经理的姓名：

SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
LEFT JOIN employees e2
ON e1.manager_id = e2.id;

结果：

 employee | manager
----------+---------Alice    | NULLBob      | AliceCharlie  | AliceDavid    | Bob

总结：
连接（JOIN）是 PostgreSQL 中用于组合多个表数据的强大工具。通过使用 INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN、CROSS JOIN 和 SELF JOIN，可以灵活地查询和处理复杂的数据关系。选择合适的连接类型可以确保查询结果的准确性和效率。

八、 PostgreSQL 时间/日期函数和操作符

在 PostgreSQL 中，时间/日期函数和操作符用于处理日期和时间数据。这些函数和操作符可以帮助你进行日期计算、格式化、提取特定部分等操作。以下是一些常用的时间/日期函数和操作符的详细介绍和示例。

1. 常用时间/日期函数

1.1 CURRENT_DATE
返回当前日期。

1.2 CURRENT_TIME
返回当前时间。

1.3 CURRENT_TIMESTAMP
返回当前日期和时间。

1.4 NOW()
返回当前日期和时间，与 CURRENT_TIMESTAMP 相同。

1.5 EXTRACT(field FROM timestamp)
从时间戳中提取指定的部分，如年、月、日、小时等。

1.6 AGE(timestamp, timestamp)
计算两个日期之间的年龄（以时间间隔的形式返回）。

1.7 TO_CHAR(timestamp, format)
将时间戳格式化为字符串。

1.8 TO_DATE(text, format)
将字符串转换为日期。

1.9 TO_TIMESTAMP(text, format)
将字符串转换为时间戳。

1.10 DATE_TRUNC(text, timestamp)
将时间戳截断到指定的精度。

1.11 INTERVAL
表示时间间隔，常用于日期和时间的加减操作。

2. 示例

2.1 获取当前日期和时间

select current_date as date,current_time as time,current_timestamp as timestamp,now() as now

在这里插入图片描述

2.2 提取日期和时间的部分

SELECT 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;

在这里插入图片描述 2.3 计算两个日期之间的年龄

SELECT AGE('2024-11-01'::date, '2000-12-01'::date);

2.4 格式化时间戳

SELECT TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH:MI:SS');

在这里插入图片描述

2.5 将字符串转换为日期

SELECT TO_DATE('2024-11-28', 'YYYY-MM-DD');

在这里插入图片描述

2.6 将字符串转换为时间戳

SELECT TO_TIMESTAMP('2024-11-28 13:53:05', 'YYYY-MM-DD HH24:MI:SS');
SELECT TO_TIMESTAMP('2024-11-28 1:53:05', 'YYYY-MM-DD HH:MI:SS');

在这里插入图片描述

2.7 截断时间戳

SELECT DATE_TRUNC('year', CURRENT_TIMESTAMP),DATE_TRUNC('month', CURRENT_TIMESTAMP),DATE_TRUNC('day', CURRENT_TIMESTAMP),DATE_TRUNC('hour', CURRENT_TIMESTAMP);

在这里插入图片描述

2.8 使用时间间隔

-- 当前日期加上7天、减去7天
SELECT CURRENT_DATE + INTERVAL '7 days',CURRENT_DATE - INTERVAL '7 days';-- 当前时间前1h、后1h
SELECT CURRENT_TIME - INTERVAL '1 hour',CURRENT_TIME + INTERVAL '1 hour';-- 两个日期之间的差值，pg中参数可使用‘::[类型]’进行数据类型转换
SELECT '2024-11-28'::date - '2024-05-08'::date;

在这里插入图片描述

3. 其他常用操作

3.1 检查日期是否在某个范围内

SELECT '2024-11-28'::date BETWEEN '2024-01-01'::date AND '2024-12-31'::date
UNION all
SELECT '2023-11-28'::date BETWEEN '2024-01-01'::date AND '2024-12-31'::date;

3.2 获取第一天和最后一天

SELECT 'month' as tpye,DATE_TRUNC('month', CURRENT_DATE) AS firstday,(DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month - 1 day') AS lastday
UNION
SELECT 'quarter' as tpye,DATE_TRUNC('quarter', CURRENT_DATE) AS firstday,(DATE_TRUNC('quarter', CURRENT_DATE) + INTERVAL '3 months - 1 day') AS lastday
UNION
SELECT 'year' as tpye,DATE_TRUNC('year', CURRENT_DATE) AS firstday,(DATE_TRUNC('year', CURRENT_DATE) + INTERVAL '1 year - 1 day') AS lastday;

在这里插入图片描述

⭐真·学习笔记&🥕拓展

1、循环语句
1.1、for 循环

--升序循环
DO $$
BEGINFOR i IN 1..5 LOOPRAISE NOTICE ' i: %', i;END LOOP;
END;
$$;--降序循环
DO $$
BEGINFOR i IN REVERSE 5..1 LOOPRAISE NOTICE ' i: %', i;END LOOP;
END;
$$;

结果：
升序
NOTICE: i: 1
NOTICE: i: 2
NOTICE: i: 3
NOTICE: i: 4
NOTICE: i: 5
DO
降序
NOTICE: i: 5
NOTICE: i: 4
NOTICE: i: 3
NOTICE: i: 2
NOTICE: i: 1
DO

1.2、while循环

--降序循环
DO $$
DECLAREv_num int := 5;
BEGINwhile v_num>0 LOOPRAISE NOTICE ' v_num: %', v_num;v_num:=v_num-1;END LOOP;
END;
$$;--升序循环
DO $$
DECLAREv_num int := 1;
BEGINwhile v_num<6 LOOPRAISE NOTICE ' v_num: %', v_num;v_num:=v_num+1;END LOOP;
END;
$$;

结果：
升序
NOTICE: v_num: 1
NOTICE: v_num: 2
NOTICE: v_num: 3
NOTICE: v_num: 4
NOTICE: v_num: 5
DO
降序
NOTICE: v_num: 5
NOTICE: v_num: 4
NOTICE: v_num: 3
NOTICE: v_num: 2
NOTICE: v_num: 1
DO

查看全文

http://www.mrgr.cn/news/78402.html