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

如何用Practical SQL 2nd Edition掌握PostgreSQL：初学者必备的7个核心技能

news 2026/8/13 21:24:42

如何用Practical SQL 2nd Edition掌握PostgreSQL：初学者必备的7个核心技能

【免费下载链接】practical-sql-2Code and Data for the Second Edition of "Practical SQL" by Anthony DeBarros, published by No Starch Press.项目地址: https://gitcode.com/gh_mirrors/pr/practical-sql-2

Practical SQL 2nd Edition是Anthony DeBarros编写的PostgreSQL入门经典教程，通过真实数据集和场景化案例帮助新手快速掌握数据库操作。本文将提炼书中7个核心技能，助你从SQL小白成长为数据处理达人。

📊 1. 基础查询：用SELECT语句提取数据

SQL的核心能力在于数据提取，而SELECT是最基础也是最常用的命令。通过指定列名和过滤条件，你可以精准获取所需数据。

基础语法示例：

-- 从教师表中查询姓名和薪资 SELECT last_name, first_name, salary FROM teachers; -- 筛选特定学校的教师信息 SELECT last_name, school, hire_date FROM teachers WHERE school = 'Myers Middle School';

书中Chapter_03/Chapter_03.sql提供了完整的基础查询案例，包括条件过滤（WHERE）、排序（ORDER BY）和去重（DISTINCT）等实用技巧。

🧱 2. 表结构设计：创建合理的数据容器

良好的表结构是高效数据管理的基础。PostgreSQL支持多种数据类型，合理选择类型和约束能避免后期数据混乱。

创建表示例：

-- 创建包含主键和约束的表 CREATE TABLE natural_key_example ( license_id text CONSTRAINT license_key PRIMARY KEY, first_name text NOT NULL, last_name text NOT NULL );

关键约束类型：

主键（PRIMARY KEY）：唯一标识记录，如Chapter_08/Chapter_08.sql中的license_id
外键（FOREIGN KEY）：建立表间关系，确保数据一致性
非空（NOT NULL）：强制必填字段，避免无效数据

🔗 3. 多表关联：用JOIN整合分散数据

实际业务中数据常分散在多张表中，JOIN操作能将相关数据高效整合。Practical SQL详细讲解了5种常用关联方式：

JOIN类型对比：

-- 内连接：只返回匹配的记录 SELECT * FROM district_2020 JOIN district_2035 ON district_2020.id = district_2035.id; -- 左连接：保留左表所有记录，右表无匹配则为NULL SELECT * FROM district_2020 LEFT JOIN district_2035 ON district_2020.id = district_2035.id;

完整示例可参考Chapter_07/Chapter_07.sql，书中通过学区数据案例清晰展示了不同JOIN的使用场景和结果差异。

📈 4. 数据聚合：用GROUP BY分析统计信息

聚合函数（COUNT、SUM、AVG等）配合GROUP BY能快速生成统计报表，是数据分析的核心技能。

聚合查询示例：

-- 按州统计图书馆数量 SELECT stabr, count(*) AS library_count FROM pls_fy2018_libraries GROUP BY stabr ORDER BY library_count DESC;

进阶技巧：

HAVING：筛选聚合结果，如HAVING count(*) > 100
窗口函数：计算分组内的排名或占比，如Chapter_11/Chapter_11.sql中的百分位数计算

🧹 5. 数据清洗：处理脏数据的实用技巧

真实数据往往存在缺失、格式错误等问题。书中提供了系统的数据清洗方案：

常见清洗操作：

-- 处理缺失值 UPDATE meat_poultry_egg_establishments SET zip = '0' || zip WHERE st IN('PR','VI') AND length(zip) = 3; -- 标准化文本格式 SELECT upper(company), trim(company) FROM meat_poultry_egg_establishments;

Chapter_10/Chapter_10.sql通过肉类加工厂数据案例，展示了如何用UPDATE、TRIM和正则表达式处理复杂数据问题。

📅 6. 日期时间处理：掌握时间序列数据

PostgreSQL提供强大的日期时间函数，能轻松处理时间序列数据：

时间操作示例：

-- 提取年份和计算时间差 SELECT extract(year from trip_date) AS trip_year, trip_date - '2020-01-01'::date AS days_since_start FROM nyc_yellow_taxi_trips;

关键函数：

extract()：提取日期组件（年/月/日）
make_date()/make_time()：创建日期时间
AT TIME ZONE：处理时区转换

详细用法见Chapter_12/Chapter_12.sql的出租车数据案例。

📍 7. 空间数据：PostGIS扩展应用

PostgreSQL通过PostGIS扩展支持空间数据操作，开启地理信息分析能力：

空间查询示例：

-- 查找指定距离内的农贸市场 SELECT market_name, ST_Distance(geog_point, ST_MakePoint(-74.0060, 40.7128)::geography) / 1609 AS miles_from_nyc FROM farmers_markets WHERE ST_DWithin(geog_point, ST_MakePoint(-74.0060, 40.7128)::geography, 160934) -- 100英里范围 ORDER BY miles_from_nyc;

Chapter_15/Chapter_15.sql包含完整的空间数据处理案例，从点线面创建到距离计算和区域分析。