全栈工程师-数据库管理-PostgreSQL_高级查询与子查询.docx

PAGE1

PAGE1

高级查询基础

1理解SELECT语句的高级用法

在PostgreSQL中，SELECT语句是进行数据查询的核心。除了基本的从表中选择数据，高级用法允许我们进行更复杂的数据操作和分析。以下是一些高级用法的示例：

1.1示例：嵌套查询

假设我们有一个employees表，其中包含员工的id、name和department_id。我们还有一张departments表，包含id和name。我们的目标是找出所有在“销售”部门工作的员工。

--创建示例数据

CREATETABLEdepartments(

idSERIALPRIMARYKEY,

nameVARCHAR(100)NOTNULL

);

CREATETABLEemployees(

idSERIALPRIMARYKEY,

nameVARCHAR(100)NOTNULL,

department_idINTEGERREFERENCESdepartments(id)

);

INSERTINTOdepartments(name)VALUES(销售),(研发);

INSERTINTOemployees(name,department_id)VALUES(张三,1),(李四,1),(王五,2);

--查询在“销售”部门工作的所有员工

SELECTe.name

FROMemployeese

WHEREe.department_idIN(

SELECTd.id

FROMdepartmentsd

WHEREd.name=销售

);

1.2示例：使用WITH子句

WITH子句可以用来定义一个临时的结果集，这个结果集可以在后续的查询中被引用，这有助于简化复杂的查询和提高可读性。

假设我们想要找出每个部门的员工平均年龄，然后找出平均年龄最高的部门。

WITHdepartment_agesAS(

SELECTd.nameASdepartment_name,AVG(e.age)ASaverage_age

FROMdepartmentsd

JOINemployeeseONd.id=e.department_id

GROUPBYd.name

)

SELECTdepartment_name,average_age

FROMdepartment_ages

WHEREaverage_age=(

SELECTMAX(average_age)

FROMdepartment_ages

);

2使用聚合函数和GROUPBY进行数据汇总

聚合函数如COUNT(),SUM(),AVG(),MIN(),MAX()等，可以对数据进行汇总分析。GROUPBY子句则允许我们按一个或多个列的值进行分组，然后对每个组应用聚合函数。

2.1示例：计算每个部门的员工数量

--查询每个部门的员工数量

SELECTd.name,COUNT(e.id)ASemployee_count

FROMdepartmentsd

JOINemployeeseONd.id=e.department_id

GROUPBYd.name;

2.2示例：找出每个部门的最高工资

--查询每个部门的最高工资

SELECTd.name,MAX(e.salary)ASmax_salary

FROMdepartmentsd

JOINemployeeseONd.id=e.department_id

GROUPBYd.name;

3掌握ORDERBY和LIMIT进行结果排序与限制

ORDERBY子句用于对结果集进行排序，而LIMIT子句则用于限制结果集的行数。

3.1示例：按工资降序排序并限制结果

--查询员工信息，按工资降序排序，只显示前10条记录

SELECTe.name,e.salary

FROMemployeese

ORDERBYe.salaryDESC

LIMIT10;

3.2示例：使用OFFSET跳过前几条记录

OFFSET子句可以与LIMIT一起使用，用于跳过结果集的前几条记录，这在分页查询中非常有用。

--查询员工信息，按工资降序排序，跳过前10条记录，显示接下来的10条

SELECTe.name,e.salary

FROMemployeese

ORDERBYe.salaryDESC

LIMIT10OFFSET10;

通过上述示例