pymysql的使用方法以及增删改查

以下是关于 pymysql 使用方法以及增删改查常见操作（包含各种常用条件和功能）的详细介绍及对应简单的 SQL 语句示例：

一、pymysql 使用方法概述

1. 连接数据库
   • 参数说明
     • 首先，使用pymysql.connect()函数来建立与MySQL数据库的连接。这个函数需要一些参数来指定数据库的位置、登录凭据和其他相关信息。
     • host：数据库服务器的主机地址。如果是本地数据库，通常可以使用localhost或127.0.0.1。例如，host = 'localhost'。
     • user：用于登录数据库的用户名。假设用户名是root，则user = 'root'。
     • password：与用户名对应的密码。例如password = 'your_password'，这里需要替换为实际的数据库密码。
     • database：要连接的数据库名称。比如database = 'your_database'，其中your_database是实际的数据库名字。
     • port（可选）：MySQL服务器常用3306端口，若数据库服务器使用了其他端口，可以通过参数指定，如port = 3307。
     • charset（可选）：指定字符集，常用的是utf8mb4，它能很好地支持中文等多种字符编码，如charset = 'utf8mb4'。
   • 示例代码

     • import pymysql
       # 建立数据库连接
       connection = pymysql.connect(
           host='localhost',				# 数据库服务器地址，本地一般用'localhost'
           user='root',					# 登录用户名
           password='your_password',		# 用户密码
           database='your_database',		# 要连接的数据库名称
           charset='utf8mb4'				# 推荐的字符集，支持更多字符类型
       )
       

2. 创建游标（用于执行SQL语句）
   • 连接建立好后，通过连接对象的cursor()方法创建游标。游标就像是一个在数据库中执行SQL语句的工具。
   • 示例：

     • cursor = connection.cursor()
       

3. 执行SQL语句
   • 执行简单SQL语句（无参数）
     • 对于简单的SQL语句，如查询所有记录的SELECT语句，可以直接使用游标对象的execute()方法。
     • 例如，查询名为students表中的所有记录：

       • sql = "SELECT * FROM students"
         cursor.execute(sql)
         

   • 执行带参数的SQL语句（防止SQL注入）
     • 在插入、更新等操作中，SQL语句经常需要动态地传入参数。可以使用占位符%s来表示参数，然后将参数作为元组传递给execute()方法。
     • 例如，向students表中插入一条记录，包含name和age两个字段：

       • sql = "INSERT INTO students (name, age) VALUES (%s, %s)"
         values = ('John', 20)
         cursor.execute(sql, values)
         

4. 获取查询结果（仅针对查询操作）
   • 获取单条记录
     • 如果查询操作预计只返回一条记录，可以使用游标对象的fetchone()方法来获取结果。
     • 例如，查询students表中年龄最小的学生记录（假设按年龄升序排序后第一条记录是年龄最小的）：

       • sql = "SELECT * FROM students ORDER BY age ASC LIMIT 1"
         cursor.execute(sql)
         result = cursor.fetchone()
         print(result)
         

   • 获取多条记录
     • 当查询可能返回多条记录时，使用fetchall()方法来获取所有结果。返回的结果是一个包含元组的列表，每个元组代表表中的一行数据。
     • 例如，查询students表中所有年龄大于20岁的学生记录：

       • sql = "SELECT * FROM students WHERE age > 20"
         cursor.execute(sql)
         results = cursor.fetchall()
         for row in results:
             print(row)
         

5. 提交或回滚事务（针对数据修改操作）
   • 提交事务
     • 在执行插入、更新或删除操作后，需要使用连接对象的commit()方法来提交事务，这样才能使数据库中的数据真正被修改。
     • 例如，在插入一条学生记录后提交事务：

       • sql = "INSERT INTO students (name, age) VALUES (%s, %s)"
         values = ('Alice', 22)
         cursor.execute(sql, values)
         connection.commit()
         

   • 回滚事务
     • 如果在执行事务过程中出现错误（例如SQL语法错误、违反数据库约束等），可以使用连接对象的rollback()方法回滚事务，撤销已经执行的操作。
     • 例如，在尝试更新学生记录时可能出错并回滚：

       • try:
             sql = "UPDATE students SET age = age - 1 WHERE name = 'Non - existent'"
             cursor.execute(sql)
             connection.commit()
         except:
             connection.rollback()
         

6. 关闭游标和连接
   • 在完成数据库操作后，为了释放资源，应该先使用cursor.close()方法关闭游标，然后使用connection.close()方法关闭数据库连接。
   • 示例：

     • cursor.close()
       connection.close()
       

二、查询操作（SELECT）

1. 查询所有记录

SELECT * FROM your_table;

此语句用于获取指定表（这里假设表名为 your_table）中的所有数据记录。

2. 查询指定列记录

SELECT column1, column2 FROM your_table;

例如，若只想获取 your_table 表中的 column1 和 column2 这两列的数据，将对应列名写在 SELECT 后面即可。

3. 条件查询记录

SELECT * FROM your_table WHERE condition;

比如，查询年龄（假设表中有 age 列）大于20岁的记录：

SELECT * FROM your_table WHERE age > 20;

将具体的筛选条件按照语法规则写在 WHERE 后面就行。

4. 模糊查询记录（使用 LIKE 关键字和通配符）

-- 查询以 '张' 开头的姓名记录（假设表中有 `name` 列）
SELECT * FROM your_table WHERE name LIKE '张%';

% 表示任意多个字符，_ 可表示单个字符，可按需灵活运用来实现不同的模糊匹配需求。

5. 范围查询记录
   • 使用 BETWEEN... AND...

-- 查询年龄在18到20岁之间（包含18和20岁）的记录（假设表中有 `age` 列）
SELECT * FROM your_table WHERE age BETWEEN 18 AND 20;

- **使用 `IN` 操作符**

-- 查询班级（假设表中有 `class` 列）为 'Class A'、'Class B'、'Class C' 的记录
SELECT * FROM your_table WHERE class IN ('Class A', 'Class B', 'Class C');

6. 排序查询记录（使用 ORDER BY 关键字）

-- 按照年龄（假设表中有 `age` 列）升序排序查询记录
SELECT * FROM your_table ORDER BY age ASC;
-- 按照成绩（假设表中有 `score` 列）降序排序查询记录
SELECT * FROM your_table ORDER BY score DESC;

ASC 表示升序（默认可以省略），DESC 表示降序，指定排序的列名跟在 ORDER BY 后面即可。

7. 分组查询记录（使用 GROUP BY 结合聚合函数，可搭配 HAVING 进一步筛选分组结果）

-- 统计每个班级（假设表中有 `class` 列）的学生人数（使用 `COUNT` 聚合函数）
SELECT class, COUNT(*) FROM your_table GROUP BY class;
-- 统计每个班级的平均成绩（假设表中有 `class` 列和 `score` 列），且只显示平均成绩大于80分的班级
SELECT class, AVG(score) FROM your_table GROUP BY class HAVING AVG(score) > 80;

8. 多表关联查询记录（使用不同类型的 JOIN 操作）
   • 内连接（JOIN）示例：假设有 students 表（含 student_id、name 等列）和 courses 表（含 course_id、course_name、student_id 等列），通过 student_id 关联查询学生姓名和所选课程名称。

SELECT students.name, courses.course_name 
FROM students 
JOIN courses ON students.student_id = courses.student_id;

- **左连接（`LEFT JOIN`）示例**：查询所有学生以及他们所选的课程（如果有的话），以 `students` 表为主表展示。

SELECT students.name, courses.course_name 
FROM students 
LEFT JOIN courses ON students.student_id = courses.student_id;

9. 子查询记录（将一个查询语句嵌套在另一个查询语句中）

-- 查询成绩高于平均成绩的学生记录（假设表中有 `score` 列）
-- 先通过子查询获取平均成绩
SELECT AVG(score) FROM your_table;
-- 再在主查询中使用该结果作为条件
SELECT * FROM your_table WHERE score > (SELECT AVG(score) FROM your_table);

10. 去重查询记录（使用 DISTINCT 关键字）

-- 查询表中不同班级（假设表中有 `class` 列）的名称
SELECT DISTINCT class FROM your_table;

11. 分页查询记录（使用 LIMIT 关键字）

-- 每页显示10条记录，查询第2页的数据（从第11条记录开始取10条）
SELECT * FROM your_table LIMIT 10, 10;

语法为 LIMIT offset, limit，offset 表示偏移量（从第几条记录开始，起始为0），limit 表示每页显示的记录数。

三、插入操作（INSERT）

1. 插入单条记录

INSERT INTO your_table (column1, column2, column3) VALUES (value1, value2, value3);

例如，向 students 表插入一条学生记录（假设有 name、age、class 列）：

INSERT INTO students (name, age, class) VALUES ('John', 20, 'Class A');

确保列名和对应的值按顺序、数量匹配准确。

2. 插入多条记录（使用一条语句批量插入）

INSERT INTO your_table (column1, column2, column3) VALUES (value1_1, value2_1, value3_1), (value1_2, value2_2, value3_2), (value1_3, value2_3, value3_3);

比如，向 students 表批量插入多条学生记录：

INSERT INTO students (name, age, class) VALUES ('Alice', 21, 'Class B'), ('Bob', 19, 'Class A'), ('Cathy', 22, 'Class C');

四、更新操作（UPDATE）

1. 更新单条记录

UPDATE your_table SET column1 = new_value1 WHERE condition;

例如，将 students 表中名为 John 的学生年龄更新为21岁（假设表中有 name、age 列）：

UPDATE students SET age = 21 WHERE name = 'John';

2. 更新多条记录（基于条件批量更新）

UPDATE your_table SET column1 = new_value1 WHERE condition;

比如，将 students 表中班级（假设表中有 class 列）为 Class A 的所有学生年龄加1：

UPDATE students SET age = age + 1 WHERE class = 'Class A';

五、删除操作（DELETE）

1. 删除单条记录

DELETE FROM your_table WHERE condition;

例如，删除 students 表中名为 Bob 的学生记录（假设表中有 name 列）：

DELETE FROM students WHERE name = 'Bob';

2. 删除多条记录（基于条件批量删除）

DELETE FROM your_table WHERE condition;

比如，删除 students 表中年龄小于18岁的所有学生记录（假设表中有 age 列）：

DELETE FROM students WHERE age < 18;

以上就是 pymysql 进行增删改查等常见数据库操作的使用方法及对应的简单 SQL 语句示例，实际应用中需根据具体的数据库表结构、业务需求等对语句中的表名、列名、条件和值等内容进行准确调整。