MySQL窗口函数与PyMySQL以及SQL注入

MySQL窗口函数与PyMySQL实战指南：从基础到安全编程

引言

在数据处理和分析领域，MySQL作为最流行的关系型数据库之一，其窗口函数功能为数据分析提供了强大的支持。同时，Python作为数据分析的主要语言，通过PyMySQL库与MySQL数据库进行交互，实现了高效的数据处理流程。本文将深入探讨MySQL窗口函数的使用方法以及PyMySQL的安全编程实践。

一、MySQL窗口函数详解

1.1 窗口函数概述

窗口函数是MySQL 8.0版本引入的重要功能，它允许在查询结果集的每一行上执行计算，同时保持原始行的完整性。窗口函数的主要优势包括：

• 简洁性：相比复杂的子查询，窗口函数语法更加简洁
• 高效性：处理大量数据时性能更优
• 多功能性：支持排名、累计值、差值计算等多种功能

1.2 窗口函数基本语法

SELECT 
    查询字段,
    窗口函数(字段名) OVER([PARTITION BY 分组字段] [ORDER BY 排序字段]) AS 别名
FROM 表名;

1.3 实际应用案例

案例1：计算学生分数与平均分的差值

需求：计算每个学生的分数与所有学生平均分的差值

传统子查询方式：

SELECT
    *,
    (SELECT AVG(Score) FROM students) AS avg_score,
    Score - (SELECT AVG(Score) FROM students) AS diff_score
FROM students;

窗口函数方式：

SELECT
    *,
    AVG(Score) OVER() AS avg_score,
    Score - AVG(Score) OVER() AS diff_score
FROM students;

案例2：按性别分组计算差值

需求：计算每个学生的分数与同性别学生平均分的差值

窗口函数实现：

SELECT
    *,
    AVG(Score) OVER(PARTITION BY Gender) AS avg_score,
    Score - AVG(Score) OVER(PARTITION BY Gender) AS diff_score
FROM students;

案例3：排名函数应用

MySQL提供了三种排名函数：

• RANK()：有并列时序号重复但不连续
• DENSE_RANK()：有并列时序号重复且连续
• ROW_NUMBER()：返回连续唯一的行号

SELECT
    *,
    RANK() OVER(ORDER BY Score DESC) AS rank_num,
    DENSE_RANK() OVER(ORDER BY Score DESC) AS dense_rank_num,
    ROW_NUMBER() OVER(ORDER BY Score DESC) AS row_num
FROM students;

1.4 获取分组内最高分学生

使用窗口函数结合子查询：

WITH ranked_students AS (
    SELECT
        *,
        ROW_NUMBER() OVER(PARTITION BY Gender ORDER BY Score DESC) AS rn
    FROM students
)
SELECT * FROM ranked_students WHERE rn = 1;

二、MySQL事务机制

2.1 事务的四大特性

1. 原子性（Atomicity）：事务中的所有操作要么全部完成，要么全部不完成
2. 一致性（Consistency）：事务执行前后数据库的完整性不被破坏
3. 隔离性（Isolation）：多个事务并发执行时相互隔离
4. 持久性（Durability）：事务提交后对数据的修改是永久的

2.2 事务操作示例

-- 创建账户表
CREATE TABLE account (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    balance DECIMAL(10, 2) NOT NULL DEFAULT 0.00
) ENGINE=InnoDB;

-- 插入测试数据
INSERT INTO account (username, balance) VALUES
('Alice', 1000.00),
('Bob', 500.00);

-- 事务操作示例
BEGIN;
-- Alice账户扣减200元
UPDATE account SET balance = balance - 200 WHERE username = 'Alice';
-- Bob账户增加200元
UPDATE account SET balance = balance + 200 WHERE username = 'Bob';
COMMIT;

2.3 事务提交方式

MySQL支持两种事务提交方式：

• 自动提交：SET AUTOCOMMIT = 1
• 手动提交：SET AUTOCOMMIT = 0

三、PyMySQL基础操作

3.1 PyMySQL安装与连接

# 安装PyMySQL
# pip install pymysql

import pymysql

# 建立数据库连接
connection = pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='123456',
    database='jing_dong',
    charset='utf8'
)

3.2 查询操作

# 创建游标对象
cursor = connection.cursor()

# 执行查询SQL
sql = "SELECT * FROM goods"
cursor.execute(sql)

# 获取查询结果
data = cursor.fetchall()  # 获取所有结果
# data = cursor.fetchone()  # 获取一条结果

for item in data:
    print(item)

# 关闭游标和连接
cursor.close()
connection.close()

3.3 增删改操作

cursor = connection.cursor()

try:
    sql = "INSERT INTO account(username, balance) VALUES('zhangsan', 200)"
    row = cursor.execute(sql)
    print(f'影响的行数：{row}')
except Exception as e:
    print('报错了', e)
    connection.rollback()  # 回滚操作

connection.commit()  # 提交事务
cursor.close()
connection.close()

四、SQL注入问题与解决方案

4.1 SQL注入问题演示

问题代码：

username = input('请输入用户名:')
password = input('请输入密码:')

sql = f"SELECT * FROM user WHERE user = '{username}' AND pwd = '{password}'"
cursor.execute(sql)

攻击示例：

• 用户名输入：111
• 密码输入：' OR 1=1 OR '

生成的SQL语句：

SELECT * FROM user WHERE user = '111' AND pwd = '' OR 1=1 OR ''

这将导致条件始终为真，绕过身份验证。

4.2 参数化查询解决方案

安全代码：

username = input('请输入用户名:')
password = input('请输入密码:')

sql = "SELECT * FROM user WHERE user = %s AND pwd = %s"
params = [username, password]
cursor.execute(sql, params)

优势：

• 防止SQL注入攻击
• 提高代码可读性
• 自动处理特殊字符转义

五、实战项目：京东商品管理系统

5.1 数据库设计

-- 创建京东数据库
CREATE DATABASE IF NOT EXISTS jing_dong CHARSET=utf8;
USE jing_dong;

-- 创建商品表
CREATE TABLE goods(
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
    name VARCHAR(150) NOT NULL,
    cate_name VARCHAR(40) NOT NULL,
    brand_name VARCHAR(40) NOT NULL,
    price DECIMAL(10,3) NOT NULL DEFAULT 0,
    is_show BIT NOT NULL DEFAULT 1,
    is_saleoff BIT NOT NULL DEFAULT 0
);

-- 创建用户表
CREATE TABLE user(
    id INT PRIMARY KEY AUTO_INCREMENT,
    user VARCHAR(30),
    pwd VARCHAR(30)
);

5.2 Python操作示例

import pymysql

class JDManager:
    def __init__(self):
        self.connection = pymysql.connect(
            host='localhost',
            port=3306,
            user='root',
            password='123456',
            database='jing_dong',
            charset='utf8'
        )
        self.cursor = self.connection.cursor()
    
    def query_goods(self):
        """查询所有商品"""
        sql = "SELECT * FROM goods"
        self.cursor.execute(sql)
        return self.cursor.fetchall()
    
    def add_goods(self, name, cate_name, brand_name, price):
        """添加商品"""
        sql = "INSERT INTO goods(name, cate_name, brand_name, price) VALUES(%s, %s, %s, %s)"
        params = [name, cate_name, brand_name, price]
        try:
            self.cursor.execute(sql, params)
            self.connection.commit()
            return True
        except Exception as e:
            self.connection.rollback()
            print(f"添加失败：{e}")
            return False
    
    def close(self):
        """关闭连接"""
        self.cursor.close()
        self.connection.close()

# 使用示例
manager = JDManager()
goods_list = manager.query_goods()
for goods in goods_list:
    print(goods)
manager.close()

六、最佳实践总结

6.1 窗口函数使用建议

1. 选择合适的排名函数：根据业务需求选择RANK、DENSE_RANK或ROW_NUMBER
2. 合理使用PARTITION BY：避免过度分组导致性能问题
3. 注意MySQL版本兼容性：窗口函数需要MySQL 8.0+

6.2 PyMySQL安全编程

1. 始终使用参数化查询：避免字符串拼接SQL语句
2. 正确处理事务：确保数据一致性
3. 及时关闭连接：避免连接泄漏
4. 异常处理：捕获并处理数据库操作异常

6.3 性能优化建议

1. 合理使用索引：提高查询性能
2. 批量操作：减少数据库交互次数
3. 连接池：在高并发场景下使用连接池
4. 查询优化：避免SELECT *，只查询需要的字段

结语

MySQL窗口函数和PyMySQL的结合为数据处理提供了强大的工具。通过掌握窗口函数的使用方法，可以简化复杂的数据分析查询；通过PyMySQL的安全编程实践，可以构建稳定可靠的数据库应用程序。在实际项目中，合理运用这些技术，能够显著提高开发效率和系统安全性。

关键词：MySQL、窗口函数、PyMySQL、SQL注入、事务处理、数据分析