如何利用Cursor实现复杂查询

在数据库编程中，Cursor 是一个非常重要的概念，它允许你逐行地处理查询结果集。使用 Cursor 可以实现复杂的查询操作，包括多表连接、条件筛选、排序、分组等。以下是一些常见的复杂查询操作及其实现方法：

1. 多表连接查询

假设你有两个表 employees 和 departments，你想查询每个部门的员工数量。

SELECT d.department_name, COUNT(e.employee_id) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

使用 Cursor 实现：

import sqlite3

# 连接到数据库
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# 执行查询
query = """
SELECT d.department_name, COUNT(e.employee_id) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;
"""
cursor.execute(query)

# 获取结果
rows = cursor.fetchall()

# 处理结果
for row in rows:
    print(f"Department: {row[0]}, Employee Count: {row[1]}")

# 关闭连接
cursor.close()
conn.close()

2. 条件筛选查询

假设你想查询年龄大于30岁的员工，并按工资降序排列。

SELECT employee_id, name, age, salary
FROM employees
WHERE age > 30
ORDER BY salary DESC;

使用 Cursor 实现：

import sqlite3

# 连接到数据库
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# 执行查询
query = """
SELECT employee_id, name, age, salary
FROM employees
WHERE age > 30
ORDER BY salary DESC;
"""
cursor.execute(query)

# 获取结果
rows = cursor.fetchall()

# 处理结果
for row in rows:
    print(f"Employee ID: {row[0]}, Name: {row[1]}, Age: {row[2]}, Salary: {row[3]}")

# 关闭连接
cursor.close()
conn.close()

3. 分组查询

假设你想查询每个部门的平均工资和最高工资。

SELECT department_id, AVG(salary) AS avg_salary, MAX(salary) AS max_salary
FROM employees
GROUP BY department_id;

使用 Cursor 实现：

import sqlite3

# 连接到数据库
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# 执行查询
query = """
SELECT department_id, AVG(salary) AS avg_salary, MAX(salary) AS max_salary
FROM employees
GROUP BY department_id;
"""
cursor.execute(query)

# 获取结果
rows = cursor.fetchall()

# 处理结果
for row in rows:
    print(f"Department ID: {row[0]}, Average Salary: {row[1]}, Maximum Salary: {row[2]}")

# 关闭连接
cursor.close()
conn.close()

4. 子查询

假设你想查询工资高于部门平均工资的员工。

SELECT e.employee_id, e.name, e.salary, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

使用 Cursor 实现：

import sqlite3

# 连接到数据库
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# 执行查询
query = """
SELECT e.employee_id, e.name, e.salary, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);
"""
cursor.execute(query)

# 获取结果
rows = cursor.fetchall()

# 处理结果
for row in rows:
    print(f"Employee ID: {row[0]}, Name: {row[1]}, Salary: {row[2]}, Department: {row[3]}")

# 关闭连接
cursor.close()
conn.close()

通过这些示例，你可以看到如何使用 Cursor 实现复杂的查询操作。根据你的具体需求，可以组合不同的 SQL 语句和条件来实现更复杂的查询。