关联Python库：pymysql库详解

一、MySQL 基础认知

什么是 MySQL？

MySQL 是一款开源的关系型数据库管理系统（RDBMS），基于 SQL（结构化查询语言）操作数据，以高性能、易部署、跨平台（支持 Windows/Linux/macOS）著称，广泛用于 Web 开发（如 PHP+MySQL、Python+MySQL 组合）

• 核心特点：
  • 免费开源（社区版），企业版提供商业支持
  • 支持多线程、多用户并发访问
  • 支持事务、索引、视图、存储过程等核心数据库特性
  • 兼容多种存储引擎（如 InnoDB、MyISAM，默认 InnoDB）

核心概念

概念	解释
数据库（Database）	存储相关数据的容器，一个 MySQL 实例可包含多个数据库
表（Table）	数据库的基本存储单元，由行（记录）和列（字段）组成
字段（Column）	表的列，定义数据类型（如 INT、VARCHAR、DATE）
记录（Row）	表的行，是一条完整的数据
主键（Primary Key）	唯一标识表中记录的字段，非空且唯一
索引（Index）	加速查询的结构，类似书籍目录，避免全表扫描
事务（Transaction）	一组不可分割的操作，遵循 ACID 原则（原子性、一致性、隔离性、持久性）

二、MySQL 核心操作（实战）

以下是最常用的操作，基于 MySQL 8.0 版本（主流版本），所有命令结尾需加 ; 分隔

连接 / 退出 MySQL

# 连接MySQL（本地）
mysql -u root -p  # -u指定用户名，-p提示输入密码
# 连接远程MySQL
mysql -h 192.168.1.100 -u root -p -P 3306  # -h指定IP，-P指定端口（默认3306）

# 退出MySQL
exit;  # 或 quit;

数据库操作（CRUD）

-- 1. 创建数据库（指定字符集和排序规则，避免中文乱码）
CREATE DATABASE IF NOT EXISTS mydb DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 2. 查看所有数据库
SHOW DATABASES;

-- 3. 选择要操作的数据库
USE mydb;

-- 4. 删除数据库（谨慎！）
DROP DATABASE IF EXISTS mydb;

数据表操作

-- 1. 创建表（以用户表为例）
CREATE TABLE IF NOT EXISTS user (
  id INT PRIMARY KEY AUTO_INCREMENT,  -- 主键，自增
  name VARCHAR(50) NOT NULL,          -- 姓名，非空
  age INT DEFAULT 18,                 -- 年龄，默认18
  email VARCHAR(100) UNIQUE,          -- 邮箱，唯一
  create_time DATETIME DEFAULT NOW()  -- 创建时间，默认当前时间
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. 查看表结构
DESC user;  -- 或 DESCRIBE user;

-- 3. 修改表（添加字段）
ALTER TABLE user ADD COLUMN phone VARCHAR(20) AFTER age;

-- 4. 删除表
DROP TABLE IF EXISTS user;

4. 数据操作（增删改查）

-- 1. 插入数据（INSERT）
INSERT INTO user (name, age, email) VALUES 
('张三', 20, 'zhangsan@test.com'),
('李四', 22, 'lisi@test.com');

-- 2. 查询数据（SELECT，核心）
-- 基础查询
SELECT * FROM user;  -- 查询所有字段
SELECT name, age FROM user WHERE age > 20;  -- 条件查询
SELECT * FROM user ORDER BY age DESC LIMIT 2;  -- 排序+分页

-- 3. 修改数据（UPDATE）
UPDATE user SET age = 21 WHERE name = '张三';

-- 4. 删除数据（DELETE）
DELETE FROM user WHERE id = 2;

5. 索引操作（优化查询）

-- 1. 创建索引（给email字段加索引）
CREATE INDEX idx_user_email ON user(email);

-- 2. 查看索引
SHOW INDEX FROM user;

-- 3. 删除索引
DROP INDEX idx_user_email ON user;

三、MySQL 关键特性详解

存储引擎

MySQL 支持多种存储引擎，核心区别如下：

特性	InnoDB（默认）	MyISAM
事务支持	支持	不支持
行级锁	支持	表级锁
外键约束	支持	不支持
崩溃恢复	支持	不支持
性能	写操作优	读操作优

事务管理

-- 开启事务
START TRANSACTION;
-- 执行操作
UPDATE user SET age = 25 WHERE id = 1;
-- 提交事务（确认修改）
COMMIT;
-- 回滚事务（撤销修改）
-- ROLLBACK;

权限管理（安全）

-- 创建用户（仅允许本地访问）
CREATE USER 'test_user'@'localhost' IDENTIFIED BY '123456';

-- 授权（给test_user授予mydb所有表的查询/插入权限）
GRANT SELECT, INSERT ON mydb.* TO 'test_user'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;

-- 撤销权限
REVOKE INSERT ON mydb.* FROM 'test_user'@'localhost';

-- 删除用户
DROP USER 'test_user'@'localhost';

四、实用技巧与注意事项

1. ** 避免使用 SELECT ***：只查询需要的字段，减少数据传输和内存占用；
2. 合理加索引：索引加速查询，但会降低插入 / 更新 / 删除速度，仅给常用查询字段加索引；
3. 防止 SQL 注入：使用预处理语句（如 PREPARE）或 ORM 框架（如 Python 的 SQLAlchemy），避免直接拼接 SQL；
4. 定期备份：使用 mysqldump 工具备份数据库：

mysqldump -u root -p mydb > mydb_backup.sql  # 备份
mysql -u root -p mydb < mydb_backup.sql      # 恢复

5. 优化查询：使用 EXPLAIN 分析 SQL 执行计划：

EXPLAIN SELECT * FROM user WHERE age > 20;

总结

1. MySQL 是开源关系型数据库，核心基于 SQL 操作，默认存储引擎 InnoDB 支持事务和行级锁
2. 核心操作分为数据库、表、数据三层，SELECT 是查询核心，需结合条件、排序、分页使用
3. 优化 MySQL 性能的关键是合理设计表结构、加索引、避免全表扫描，同时做好权限和备份管理