PostgreSQL SQL基础

SQL（Structured Query Language）是用于管理关系数据库的标准语言。PostgreSQL完全支持SQL标准，并提供了许多扩展功能。本文将介绍PostgreSQL SQL的基础知识。

SQL概述

SQL类型

SQL可以分为以下几类：

数据查询语言（DQL）：用于查询数据，主要是SELECT语句
数据操作语言（DML）：用于修改数据，包括INSERT、UPDATE、DELETE语句
数据定义语言（DDL）：用于定义数据库对象，包括CREATE、ALTER、DROP语句
数据控制语言（DCL）：用于控制数据访问权限，包括GRANT、REVOKE语句
事务控制语言（TCL）：用于控制事务，包括BEGIN、COMMIT、ROLLBACK语句

PostgreSQL SQL扩展

PostgreSQL提供了许多SQL标准之外的扩展功能：

数组类型支持
JSON/JSONB支持
范围类型支持
高级索引类型
窗口函数
递归查询
通用表表达式（CTE）

基本查询（SELECT）

简单查询

基本SELECT语句用于从表中检索数据：

sql

-- 检索表中的所有列
SELECT * FROM users;

-- 检索特定列
SELECT username, email FROM users;

-- 检索并别名列
SELECT username AS user_name, email FROM users;

WHERE子句

WHERE子句用于过滤数据：

sql

-- 等值条件
SELECT * FROM users WHERE id = 1;

-- 比较条件
SELECT * FROM products WHERE price > 100;
SELECT * FROM products WHERE price BETWEEN 50 AND 100;
SELECT * FROM products WHERE price IN (50, 100, 150);

-- 字符串匹配
SELECT * FROM users WHERE username LIKE 'a%'; -- 以a开头的用户名
SELECT * FROM users WHERE username LIKE '%a%'; -- 包含a的用户名
SELECT * FROM users WHERE username LIKE 'a__'; -- 以a开头，长度为3的用户名

-- 空值检查
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

-- 逻辑运算符
SELECT * FROM products WHERE price > 100 AND category_id = 1;
SELECT * FROM products WHERE price < 50 OR category_id = 2;
SELECT * FROM products WHERE NOT (price > 100);

ORDER BY子句

ORDER BY子句用于排序结果：

sql

-- 升序排序（默认）
SELECT * FROM products ORDER BY price;

-- 降序排序
SELECT * FROM products ORDER BY price DESC;

-- 多列排序
SELECT * FROM products ORDER BY category_id, price DESC;

-- 使用列别名排序
SELECT username AS user_name FROM users ORDER BY user_name;

LIMIT和OFFSET子句

LIMIT和OFFSET子句用于分页查询：

sql

-- 限制结果数量
SELECT * FROM products ORDER BY price LIMIT 10;

-- 分页查询（每页10条，第2页）
SELECT * FROM products ORDER BY price LIMIT 10 OFFSET 10;

-- PostgreSQL扩展：FETCH子句
SELECT * FROM products ORDER BY price FETCH FIRST 10 ROWS ONLY;
SELECT * FROM products ORDER BY price OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

DISTINCT子句

DISTINCT子句用于去除重复行：

sql

-- 去除重复值
SELECT DISTINCT category_id FROM products;

-- 多列去重
SELECT DISTINCT category_id, price FROM products;

数据操作（DML）

插入数据（INSERT）

INSERT语句用于向表中插入数据：

sql

-- 插入单行数据
INSERT INTO users (username, email, password_hash) VALUES ('john', 'john@example.com', 'hashed_password');

-- 插入多行数据
INSERT INTO users (username, email, password_hash) VALUES
('jane', 'jane@example.com', 'hashed_password_1'),
('bob', 'bob@example.com', 'hashed_password_2');

-- 插入查询结果
INSERT INTO users_archive SELECT * FROM users WHERE created_at < '2023-01-01';

-- 使用DEFAULT关键字
INSERT INTO users (username, email, created_at) VALUES ('alice', 'alice@example.com', DEFAULT);

更新数据（UPDATE）

UPDATE语句用于更新表中的数据：

sql

-- 更新单行数据
UPDATE users SET email = 'new_email@example.com' WHERE id = 1;

-- 更新多行数据
UPDATE products SET price = price * 1.1 WHERE category_id = 1;

-- 更新多列数据
UPDATE users SET username = 'new_username', email = 'new_email@example.com' WHERE id = 1;

-- 使用RETURNING子句返回更新的数据
UPDATE products SET price = price * 1.1 WHERE category_id = 1 RETURNING id, price;

删除数据（DELETE）

DELETE语句用于删除表中的数据：

sql

-- 删除单行数据
DELETE FROM users WHERE id = 1;

-- 删除多行数据
DELETE FROM users WHERE created_at < '2023-01-01';

-- 删除所有数据（谨慎使用）
DELETE FROM users;

-- 使用RETURNING子句返回删除的数据
DELETE FROM products WHERE category_id = 5 RETURNING id, name;

数据定义（DDL）

创建表（CREATE TABLE）

CREATE TABLE语句用于创建新表：

sql

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    category_id INTEGER REFERENCES categories(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

修改表（ALTER TABLE）

ALTER TABLE语句用于修改现有表的结构：

sql

-- 添加列
ALTER TABLE products ADD COLUMN active BOOLEAN DEFAULT TRUE;

-- 修改列
ALTER TABLE products ALTER COLUMN price TYPE DECIMAL(12, 2);
ALTER TABLE products ALTER COLUMN description SET NOT NULL;

-- 删除列
ALTER TABLE products DROP COLUMN active;

-- 添加约束
ALTER TABLE products ADD CONSTRAINT products_name_unique UNIQUE (name);

-- 删除约束
ALTER TABLE products DROP CONSTRAINT products_name_unique;

删除表（DROP TABLE）

DROP TABLE语句用于删除表：

sql

-- 删除表
DROP TABLE IF EXISTS products;

-- 级联删除（删除依赖对象）
DROP TABLE products CASCADE;

表连接（JOIN）

表连接用于从多个表中检索数据：

INNER JOIN

返回两个表中匹配的行：

sql

SELECT users.username, orders.order_number, orders.total_amount
FROM users
INNER JOIN orders ON users.id = orders.user_id;

LEFT JOIN

返回左表中的所有行，以及右表中匹配的行：

sql

SELECT users.username, orders.order_number
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

RIGHT JOIN

返回右表中的所有行，以及左表中匹配的行：

sql

SELECT users.username, orders.order_number
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

FULL JOIN

返回两个表中的所有行，匹配的行合并，不匹配的行用NULL填充：

sql

SELECT users.username, orders.order_number
FROM users
FULL JOIN orders ON users.id = orders.user_id;

CROSS JOIN

返回两个表的笛卡尔积：

sql

SELECT users.username, products.name
FROM users
CROSS JOIN products;

自连接

表与自身连接：

sql

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

聚合函数

聚合函数用于对数据进行汇总计算：

常用聚合函数

sql

-- COUNT：计算行数
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT category_id) FROM products;

-- SUM：计算总和
SELECT SUM(price) FROM products;
SELECT SUM(price) FROM products WHERE category_id = 1;

-- AVG：计算平均值
SELECT AVG(price) FROM products;

-- MAX：计算最大值
SELECT MAX(price) FROM products;

-- MIN：计算最小值
SELECT MIN(price) FROM products;

-- GROUP_CONCAT：连接字符串（PostgreSQL中使用STRING_AGG）
SELECT category_id, STRING_AGG(name, ', ') FROM products GROUP BY category_id;

GROUP BY子句

GROUP BY子句用于分组数据：

sql

SELECT category_id, COUNT(*) AS product_count, AVG(price) AS avg_price
FROM products
GROUP BY category_id;

SELECT category_id, active, COUNT(*) AS product_count
FROM products
GROUP BY category_id, active;

HAVING子句

HAVING子句用于过滤分组后的结果：

sql

SELECT category_id, COUNT(*) AS product_count
FROM products
GROUP BY category_id
HAVING COUNT(*) > 10;

SELECT category_id, AVG(price) AS avg_price
FROM products
GROUP BY category_id
HAVING AVG(price) > 100;

子查询

子查询是嵌套在其他SQL语句中的查询：

WHERE子句中的子查询

sql

-- 单行子查询
SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products);

-- 多行子查询
SELECT * FROM products WHERE category_id IN (SELECT id FROM categories WHERE name IN ('Electronics', 'Clothing'));

--  EXISTS子查询
SELECT * FROM users WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id);

FROM子句中的子查询

sql

SELECT p.category_id, p.avg_price
FROM (
    SELECT category_id, AVG(price) AS avg_price
    FROM products
    GROUP BY category_id
) AS p
WHERE p.avg_price > 100;

SELECT子句中的子查询

sql

SELECT 
    id, 
    name, 
    (SELECT COUNT(*) FROM orders WHERE orders.product_id = products.id) AS order_count
FROM products;

事务控制

事务是一组原子性的SQL操作，要么全部执行成功，要么全部失败：

sql

-- 开始事务
BEGIN;

-- 执行SQL语句
INSERT INTO orders (user_id, total_amount) VALUES (1, 100);
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 1;

-- 提交事务
COMMIT;

-- 或者回滚事务
-- ROLLBACK;

常用PostgreSQL扩展

LIMIT和OFFSET

sql

SELECT * FROM products ORDER BY price LIMIT 10 OFFSET 5;

RETURNING子句

sql

INSERT INTO users (username, email) VALUES ('new_user', 'new_user@example.com') RETURNING id, created_at;

UPDATE products SET price = price * 1.1 WHERE id = 1 RETURNING id, price;

DELETE FROM users WHERE id = 1 RETURNING *;

字符串函数

sql

-- 字符串连接
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
SELECT first_name || ' ' || last_name AS full_name FROM users;

-- 字符串长度
SELECT LENGTH(username) FROM users;

-- 大小写转换
SELECT UPPER(username) FROM users;
SELECT LOWER(username) FROM users;

-- 字符串截取
SELECT SUBSTRING(email FROM 1 FOR 5) FROM users;
SELECT LEFT(email, 5) FROM users;
SELECT RIGHT(email, 10) FROM users;

日期时间函数

sql

-- 当前日期时间
SELECT NOW();
SELECT CURRENT_TIMESTAMP;
SELECT CURRENT_DATE;
SELECT CURRENT_TIME;

-- 日期时间计算
SELECT NOW() + INTERVAL '1 day' AS tomorrow;
SELECT NOW() - INTERVAL '1 hour' AS one_hour_ago;

-- 日期时间提取
SELECT EXTRACT(YEAR FROM created_at) AS year FROM users;
SELECT EXTRACT(MONTH FROM created_at) AS month FROM users;
SELECT EXTRACT(DAY FROM created_at) AS day FROM users;

-- 日期格式化
SELECT TO_CHAR(created_at, 'YYYY-MM-DD HH24:MI:SS') AS formatted_date FROM users;

最佳实践

使用参数化查询

防止SQL注入

限制SELECT *的使用

只选择需要的列，减少网络传输和CPU开销

使用合适的数据类型

提高存储效率和查询性能

为频繁查询的列创建索引

提高查询速度

使用事务处理

确保数据完整性，遵循ACID特性

编写清晰的SQL语句

使用适当的缩进和注释，提高可维护性

避免在WHERE子句中使用函数

影响索引使用，降低查询性能

使用EXPLAIN分析查询计划

优化查询性能，识别瓶颈

常见问题（FAQ）

如何防止SQL注入？

使用参数化查询
避免直接拼接SQL语句
对用户输入进行验证和转义

SELECT *和SELECT column1, column2有什么区别？

SELECT *返回所有列，可能包含不需要的数据
SELECT column1, column2只返回指定列，减少网络传输和CPU开销
推荐使用SELECT column1, column2

什么是事务？

事务是一组原子性的SQL操作，要么全部执行成功，要么全部失败
事务具有ACID特性：原子性、一致性、隔离性、持久性

INNER JOIN和LEFT JOIN有什么区别？

INNER JOIN只返回两个表中匹配的行
LEFT JOIN返回左表中的所有行，以及右表中匹配的行

什么时候使用GROUP BY？

当需要对数据进行分组并计算聚合函数时
GROUP BY后面的列必须出现在SELECT列表中（除非使用聚合函数）

总结

本文介绍了PostgreSQL SQL的基础知识，包括基本查询、数据操作、数据定义、表连接、聚合函数、子查询和事务控制等内容。掌握这些基础知识是使用PostgreSQL进行数据库开发的基础。

要深入学习PostgreSQL SQL，建议继续学习高级SQL特性，如窗口函数、递归查询、通用表表达式等。

PostgreSQL SQL基础 ​

SQL概述 ​

SQL类型 ​

PostgreSQL SQL扩展 ​

基本查询（SELECT） ​

简单查询 ​

WHERE子句 ​

ORDER BY子句 ​

LIMIT和OFFSET子句 ​

DISTINCT子句 ​

数据操作（DML） ​

插入数据（INSERT） ​

更新数据（UPDATE） ​

删除数据（DELETE） ​

数据定义（DDL） ​

创建表（CREATE TABLE） ​

修改表（ALTER TABLE） ​

删除表（DROP TABLE） ​

表连接（JOIN） ​

INNER JOIN ​

LEFT JOIN ​

RIGHT JOIN ​

FULL JOIN ​

CROSS JOIN ​

自连接 ​

聚合函数 ​

常用聚合函数 ​

GROUP BY子句 ​

HAVING子句 ​

子查询 ​

WHERE子句中的子查询 ​

FROM子句中的子查询 ​

SELECT子句中的子查询 ​

事务控制 ​

常用PostgreSQL扩展 ​

LIMIT和OFFSET ​

RETURNING子句 ​

字符串函数 ​

日期时间函数 ​

最佳实践 ​

使用参数化查询 ​

限制SELECT *的使用 ​

使用合适的数据类型 ​

为频繁查询的列创建索引 ​

使用事务处理 ​

编写清晰的SQL语句 ​

避免在WHERE子句中使用函数 ​

使用EXPLAIN分析查询计划 ​

常见问题（FAQ） ​

如何防止SQL注入？ ​

SELECT *和SELECT column1, column2有什么区别？ ​

什么是事务？ ​

INNER JOIN和LEFT JOIN有什么区别？ ​

什么时候使用GROUP BY？ ​

总结 ​