PostgreSQL 常用关键字速查

1. 数据查询类 (SELECT相关)

SELECT - 查询数据

-- 基本查询
SELECT name, population FROM world;

-- 查询所有列
SELECT * FROM world;

-- 去重
SELECT DISTINCT continent FROM world;

WHERE - 条件筛选

-- 单条件
SELECT name FROM world WHERE population > 200000000;

-- 多条件
SELECT name FROM world 
WHERE population > 100000000 AND gdp > 1000000000;

-- 范围查询
SELECT name FROM world WHERE population BETWEEN 10000000 AND 50000000;

-- 模糊匹配
SELECT name FROM world WHERE name LIKE 'Ch%';  -- Ch开头
SELECT name FROM world WHERE name LIKE '%land'; -- land结尾
SELECT name FROM world WHERE name LIKE '%an%';  -- 包含an

ORDER BY - 排序

-- 升序（默认）
SELECT name, population FROM world ORDER BY population;

-- 降序
SELECT name, population FROM world ORDER BY population DESC;

-- 多字段排序
SELECT name, continent, population FROM world 
ORDER BY continent ASC, population DESC;

LIMIT & OFFSET - 限制结果数量

-- 取前10条
SELECT name FROM world LIMIT 10;

-- 分页：跳过前20条，取10条
SELECT name FROM world LIMIT 10 OFFSET 20;

---

2. 聚合函数类

GROUP BY - 分组

-- 按大洲分组统计国家数量
SELECT continent, COUNT(*) as country_count 
FROM world 
GROUP BY continent;

-- 统计各大洲总人口
SELECT continent, SUM(population) as total_pop 
FROM world 
GROUP BY continent;

HAVING - 分组后筛选

-- 筛选人口总和超过10亿的大洲
SELECT continent, SUM(population) as total_pop 
FROM world 
GROUP BY continent 
HAVING SUM(population) > 1000000000;

聚合函数

-- COUNT 计数
SELECT COUNT(*) FROM world;
SELECT COUNT(DISTINCT continent) FROM world;

-- SUM 求和
SELECT SUM(population) FROM world;

-- AVG 平均值
SELECT AVG(gdp) FROM world;

-- MAX/MIN 最大最小值
SELECT MAX(population), MIN(population) FROM world;

---

3. 连接类 (JOIN)

INNER JOIN - 内连接

SELECT world.name, city.name 
FROM world 
INNER JOIN city ON world.name = city.country 
WHERE world.population > 100000000;

LEFT JOIN - 左连接

SELECT world.name, city.name 
FROM world 
LEFT JOIN city ON world.name = city.country;

RIGHT JOIN - 右连接

SELECT world.name, city.name 
FROM world 
RIGHT JOIN city ON world.name = city.country;

FULL OUTER JOIN - 全外连接

SELECT world.name, city.name 
FROM world 
FULL OUTER JOIN city ON world.name = city.country;

---

4. 数据操作类

INSERT - 插入数据

-- 插入单条
INSERT INTO world (name, continent, population) 
VALUES ('NewCountry', 'Asia', 50000000);

-- 插入多条
INSERT INTO world (name, continent, population) 
VALUES 
  ('Country1', 'Europe', 10000000),
  ('Country2', 'Africa', 20000000);

UPDATE - 更新数据

-- 更新单个字段
UPDATE world SET population = 1400000000 WHERE name = 'China';

-- 更新多个字段
UPDATE world 
SET population = 1400000000, gdp = 14000000000 
WHERE name = 'China';

DELETE - 删除数据

-- 删除特定记录
DELETE FROM world WHERE population < 100000;

-- 删除所有记录（谨慎使用）
DELETE FROM world;

---

5. 表结构操作类

CREATE - 创建

-- 创建表
CREATE TABLE country (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  population INTEGER,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建索引
CREATE INDEX idx_population ON world(population);

ALTER - 修改表结构

-- 添加列
ALTER TABLE world ADD COLUMN capital VARCHAR(100);

-- 删除列
ALTER TABLE world DROP COLUMN capital;

-- 修改列类型
ALTER TABLE world ALTER COLUMN population TYPE BIGINT;

DROP - 删除

-- 删除表
DROP TABLE world;

-- 删除索引
DROP INDEX idx_population;

---

6. 条件逻辑类

AND / OR / NOT

SELECT name FROM world 
WHERE population > 100000000 AND continent = 'Asia';

SELECT name FROM world 
WHERE continent = 'Europe' OR continent = 'Asia';

SELECT name FROM world 
WHERE NOT continent = 'Africa';

IN / NOT IN

SELECT name FROM world 
WHERE continent IN ('Europe', 'Asia', 'Africa');

SELECT name FROM world 
WHERE name NOT IN ('China', 'India', 'USA');

EXISTS

SELECT name FROM world w
WHERE EXISTS (
  SELECT 1 FROM city c WHERE c.country = w.name
);

CASE - 条件表达式

SELECT name, population,
  CASE 
    WHEN population > 1000000000 THEN '超大国'
    WHEN population > 100000000 THEN '大国'
    WHEN population > 10000000 THEN '中等国家'
    ELSE '小国'
  END as country_size
FROM world;

---

7. 子查询相关

子查询示例

-- WHERE子查询
SELECT name FROM world 
WHERE population > (SELECT AVG(population) FROM world);

-- FROM子查询
SELECT continent, avg_pop FROM (
  SELECT continent, AVG(population) as avg_pop 
  FROM world 
  GROUP BY continent
) as subquery
WHERE avg_pop > 50000000;

---

8. 其他常用关键字

UNION - 联合查询

SELECT name FROM world WHERE continent = 'Asia'
UNION
SELECT name FROM world WHERE population > 200000000;

AS - 别名

SELECT name AS country_name, population AS pop FROM world;
SELECT w.name FROM world AS w;

NULL处理

-- IS NULL / IS NOT NULL
SELECT name FROM world WHERE gdp IS NULL;
SELECT name FROM world WHERE gdp IS NOT NULL;

-- COALESCE (返回第一个非空值)
SELECT name, COALESCE(gdp, 0) as gdp FROM world;