基础语法

数据库操作

创建数据库

CREATE DATABASE mydb;
CREATE DATABASE mydb WITH
    ENCODING 'UTF8'
    LC_COLLATE 'zh_CN.UTF-8'
    LC_CTYPE 'zh_CN.UTF-8'
    TEMPLATE template0;

查看数据库

\l
SELECT datname FROM pg_database;

删除数据库

DROP DATABASE mydb;
DROP DATABASE IF EXISTS mydb;

修改数据库

ALTER DATABASE mydb RENAME TO newdb;
ALTER DATABASE mydb OWNER TO newuser;

模式操作

创建模式

CREATE SCHEMA app;
CREATE SCHEMA IF NOT EXISTS app;
CREATE SCHEMA app AUTHORIZATION user1;

查看模式

\dn
SELECT schema_name FROM information_schema.schemata;

删除模式

DROP SCHEMA app;
DROP SCHEMA app CASCADE;

搜索路径

SHOW search_path;
SET search_path TO app, public;
SET search_path TO app;

表操作

创建表

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INTEGER DEFAULT 0,
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    amount DECIMAL(10, 2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending'
);

查看表结构

\d users
\d+ users
SELECT * FROM information_schema.columns WHERE table_name = 'users';

修改表

ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users ALTER COLUMN name TYPE VARCHAR(100);
ALTER TABLE users RENAME COLUMN name TO username;
ALTER TABLE users RENAME TO user_info;
ALTER TABLE users ADD CONSTRAINT email_unique UNIQUE (email);

删除表

DROP TABLE users;
DROP TABLE IF EXISTS users;
DROP TABLE users CASCADE;

临时表

CREATE TEMP TABLE temp_users AS
SELECT * FROM users WHERE status = 'active';

数据操作

INSERT

INSERT INTO users (name, email, age) VALUES ('张三', 'zhangsan@example.com', 25);

INSERT INTO users (name, email) VALUES
    ('李四', 'lisi@example.com'),
    ('王五', 'wangwu@example.com');

INSERT INTO users_copy SELECT * FROM users;

INSERT INTO users (name, email)
VALUES ('赵六', 'zhaoliu@example.com')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

UPDATE

UPDATE users SET age = 26 WHERE name = '张三';
UPDATE users SET age = age + 1, status = 'verified' WHERE id = 1;
UPDATE users SET status = 'inactive' WHERE age > 60;

DELETE

DELETE FROM users WHERE id = 1;
DELETE FROM users WHERE age < 18;
DELETE FROM users;
TRUNCATE TABLE users;

UPSERT

INSERT INTO users (id, name, email)
VALUES (1, '张三', 'zhangsan@example.com')
ON CONFLICT (id) DO UPDATE SET
    name = EXCLUDED.name,
    email = EXCLUDED.email;

查询语句

基本查询

SELECT * FROM users;
SELECT name, email FROM users;
SELECT DISTINCT status FROM users;

WHERE 条件

SELECT * FROM users WHERE age = 25;
SELECT * FROM users WHERE age > 20 AND age < 30;
SELECT * FROM users WHERE age IN (20, 25, 30);
SELECT * FROM users WHERE name LIKE '张%';
SELECT * FROM users WHERE email IS NOT NULL;
SELECT * FROM users WHERE age BETWEEN 20 AND 30;

ORDER BY 排序

SELECT * FROM users ORDER BY age;
SELECT * FROM users ORDER BY age DESC;
SELECT * FROM users ORDER BY age DESC, name ASC;
SELECT * FROM users ORDER BY age DESC NULLS LAST;

LIMIT 和 OFFSET

SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 10;
SELECT * FROM users FETCH FIRST 10 ROWS ONLY;

聚合函数

SELECT COUNT(*) FROM users;
SELECT AVG(age) FROM users;
SELECT MAX(age), MIN(age) FROM users;
SELECT SUM(amount) FROM orders;
SELECT COUNT(DISTINCT status) FROM users;

GROUP BY 分组

SELECT status, COUNT(*) FROM users GROUP BY status;
SELECT status, AVG(age) as avg_age FROM users GROUP BY status HAVING AVG(age) > 25;

连接查询

INNER JOIN

SELECT u.name, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

LEFT JOIN

SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

RIGHT JOIN

SELECT u.name, o.amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

FULL OUTER JOIN

SELECT u.name, o.amount
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

CROSS JOIN

SELECT * FROM users CROSS JOIN departments;

自连接

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

子查询

标量子查询

SELECT * FROM users
WHERE age > (SELECT AVG(age) FROM users);

IN 子查询

SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders);

EXISTS 子查询

SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

FROM 子查询

SELECT * FROM (
    SELECT name, age FROM users
) AS temp
WHERE age > 25;

UNION 操作

SELECT name FROM users
UNION
SELECT name FROM customers;

SELECT name FROM users
UNION ALL
SELECT name FROM customers;

SELECT name FROM users
INTERSECT
SELECT name FROM customers;

SELECT name FROM users
EXCEPT
SELECT name FROM customers;

CTE 公用表表达式

基本 CTE

WITH active_users AS (
    SELECT * FROM users WHERE status = 'active'
)
SELECT * FROM active_users WHERE age > 25;

多个 CTE

WITH
user_stats AS (
    SELECT user_id, COUNT(*) as order_count
    FROM orders
    GROUP BY user_id
),
top_users AS (
    SELECT * FROM user_stats WHERE order_count > 10
)
SELECT u.name, t.order_count
FROM users u
JOIN top_users t ON u.id = t.user_id;

递归 CTE

WITH RECURSIVE employee_tree AS (
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.id, e.name, e.manager_id, et.level + 1
    FROM employees e
    JOIN employee_tree et ON e.manager_id = et.id
)
SELECT * FROM employee_tree;

下一步学习

• 高级特性 - 掌握高级功能
• 索引优化 - 学习索引优化