Python 操作 MySQL + 索引 + 约束 + 数据库设计 + 事务 + 视图超全学习笔记前言

import pymysql

# 1. 创建MySQL连接（必填参数：主机、端口、用户名、密码、操作的数据库）
conn = pymysql.connect(
    host='master',    # MySQL主机地址（IP/域名）
    port=3306,        # MySQL默认端口3306
    user='root',      # 用户名
    passwd='123456',  # 密码
    db='stu_test'     # 要操作的数据库名
)

# 2. 创建游标对象（用于执行SQL语句）
cur = conn.cursor()

# 3. 编写SQL语句（示例：复杂查询）
sql_str1 = '''
SELECT	t1.sid
        ,t1.sname
        ,t2.score
from (
    SELECT	sid
            ,sname
    from Student
    where sid in (
        select  t1.sid
        from (
            SELECT  sid
                    ,score
            from SC
            where cid = '01'
        ) t1 left join (
            SELECT  sid
                    ,score
            from SC
            where cid = '02'
        ) t2 on t1.sid = t2.sid
        where t1.score > ifnull(t2.score,0)
    )
) t1 left join SC t2 on t1.sid = t2.sid
'''

# 4. 执行SQL语句
cur.execute(sql_str1)

# 5. 获取查询结果
print(cur.fetchone())      # 获取1条数据
print('#' * 50)
print(cur.fetchall())      # 获取所有剩余数据
print('#' * 50)
print(cur.fetchmany(10))   # 获取指定条数数据

# 6. 关闭游标和连接（必做！释放资源）
cur.close()
conn.close()

关键说明

fetchone()：获取第一条结果，再次调用获取下一条
fetchall()：获取剩余所有结果
fetchmany(n)：获取指定n条结果
增删改操作需要执行conn.commit()提交事务，查询无需提交

二、MySQL 索引

1. 索引作用

大大提高 MySQL 的检索速度，优化查询效率。

2. 普通索引

最基础的索引，无任何限制。

-- 1. 直接创建索引
CREATE INDEX index_name ON table_name (column_name);

-- 2. 修改表添加索引
ALTER TABLE table_name ADD INDEX index_name(column_name);

-- 3. 创建表时指定索引
CREATE TABLE mytable(
    ID INT NOT NULL,
    username VARCHAR(16) NOT NULL,
    INDEX idx_username(username(16))
);

-- 4. 删除索引
DROP INDEX index_name ON mytable;

3. 唯一索引

索引列的值必须唯一，允许为空值。

-- 直接创建
CREATE UNIQUE INDEX index_name ON mytable(username(16));

-- 修改表添加
ALTER TABLE mytable ADD UNIQUE index_name(username(16));

-- 创建表时指定
CREATE TABLE mytable(
    ID INT NOT NULL,
    username VARCHAR(16) NOT NULL,
    UNIQUE idx_username(username(16))
);

4. ALTER 命令添加 / 删除各类索引

-- 添加主键索引（非空+唯一）
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list);

-- 添加唯一索引
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list);

-- 添加普通索引
ALTER TABLE tbl_name ADD INDEX index_name (column_list);

-- 添加全文索引
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list);

-- 删除普通索引
ALTER TABLE testalter_tbl DROP INDEX index_name;

-- 删除主键索引
ALTER TABLE testalter_tbl DROP PRIMARY KEY;

5. 查看索引

SHOW INDEX FROM table_name;
-- 格式化输出
SHOW INDEX FROM table_name\G;

三、MySQL 约束

约束：对表中数据进行限定，保证数据的正确性、有效性、完整性。分类：主键约束、非空约束、唯一约束、外键约束。

1. 非空约束（NOT NULL）

列的值不能为 NULL。

-- 1. 创建表时添加
CREATE TABLE stu(
    id INT,
    NAME VARCHAR(20) NOT NULL  -- name非空
);

-- 2. 创建表后添加
ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;

-- 3. 删除非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(25);

2. 唯一约束（UNIQUE）

列的值不能重复，允许多个 NULL。

-- 1. 创建表时添加
CREATE TABLE stu(
    id INT,
    phone_num VARCHAR(20) UNIQUE
);

-- 2. 创建表后添加
ALTER TABLE stu MODIFY phone_num VARCHAR(20) UNIQUE;

-- 3. 删除唯一约束（先删索引）
ALTER TABLE stu DROP INDEX phone_num;

3. 主键约束（PRIMARY KEY）

非空 + 唯一，表中记录的唯一标识，一张表只能有一个主键。

-- 1. 创建表时添加主键
CREATE TABLE stu (
    id INT PRIMARY KEY,
    NAME VARCHAR(20)
);

-- 2. 创建表后添加主键
ALTER TABLE stu MODIFY id INT PRIMARY KEY;

-- 3. 删除主键
ALTER TABLE stu DROP PRIMARY KEY;

-- 4. 主键自动增长（AUTO_INCREMENT）
CREATE TABLE stu(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
);

-- 添加/删除自动增长
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
ALTER TABLE stu MODIFY id INT;

4. 外键约束（FOREIGN KEY）

让多表之间产生关联，保证数据一致性。

-- 1. 创建表时添加外键
CREATE TABLE 表名(
    外键列 INT,
    CONSTRAINT 外键名称 FOREIGN KEY (外键列) REFERENCES 主表(主键列)
);

-- 2. 创建表后添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键列) REFERENCES 主表(主键列);

-- 3. 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

-- 4. 级联操作（更新/删除主表数据，从表同步修改）
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 
FOREIGN KEY(外键列) REFERENCES 主表(主键列) 
ON UPDATE CASCADE  -- 级联更新
ON DELETE CASCADE;  -- 级联删除

四、数据库设计

1. 多表之间的关系

一对多：部门和员工（在多的一方加外键）
多对多：学生和课程（创建中间表，包含两个外键）
一对一：人和身份证（任意一方加唯一外键）

2. 数据库设计三大范式

（1）第一范式（1NF）

每一列都是不可拆分的原子值。示例：地址拆分为省份、城市、详细地址，而非存储一个完整字符串。

（2）第二范式（2NF）

在 1NF 基础上，每一列都完全依赖主键，不能只依赖主键的一部分。示例：订单表不存储商品名称、价格，单独创建商品表，通过外键关联。

（3）第三范式（3NF）

在 2NF 基础上，每一列都直接依赖主键，不间接依赖。示例：订单表只存储客户编号，不存储客户姓名、公司，通过外键关联客户表。

五、MySQL 事务

1. 事务基本概念

一个业务操作包含多个步骤，要么全部成功，要么全部失败。核心操作：开启事务、提交事务、回滚事务。

2. 事务操作语法

sql

-- 1. 创建测试表
CREATE TABLE account(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(10),
    balance DOUBLE
);
INSERT INTO account(NAME,balance) VALUES ('zhangsan',1000),('lisi',1000);

-- 2. 转账业务（事务演示）
START TRANSACTION;  -- 开启事务
-- 张三减500
UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';
-- 李四加500
UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';

COMMIT;   -- 无异常，提交事务（数据永久生效）
ROLLBACK; -- 有异常，回滚事务（数据恢复原状）

3. 事务提交方式

自动提交：MySQL 默认，一条 DML 语句自动提交一次事务
手动提交：需手动开启 + 提交

sql

-- 查看提交方式（1=自动，0=手动）
SELECT @@autocommit;
-- 修改为手动提交
SET @@autocommit = 0;

4. 事务四大特征（ACID）

原子性：操作不可分割，全成功 / 全失败
一致性：事务前后数据总量不变
隔离性：多个事务互不干扰
持久性：提交后数据永久生效

5. 事务隔离级别

表格

级别	脏读	不可重复读	幻读
读未提交	✅	✅	✅
读已提交	❌	✅	✅
可重复读（默认）	❌	❌	✅
串行化	❌	❌	❌

-- 查询隔离级别
SELECT @@tx_isolation;
-- 设置隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL 级别名称;

六、MySQL 视图

1. 视图概念

虚拟表，基于 SQL 查询结果创建，不存储真实数据，简化查询、保障数据安全。

2. 视图操作语法

-- 1. 创建视图
CREATE VIEW 视图名 AS
SELECT 字段 FROM 表名 WHERE 条件;

-- 2. 修改视图
ALTER VIEW 视图名 AS
SELECT 新查询语句;

-- 3. 删除视图
DROP VIEW 视图名;

3. 视图优点

简单：屏蔽复杂表结构，直接使用查询结果
安全：控制用户访问的数据范围，隐藏敏感字段
数据独立：源表结构变化不影响视图使用

4. 注意事项

包含聚合函数、GROUP BY、UNION等的视图不可更新，一般仅用于查询。

总结

Python 操作 MySQL 核心：pymysql.connect()建立连接，游标执行 SQL
索引分普通、唯一、主键，核心作用是加速查询
约束保证数据规范，外键实现多表关联
事务保证业务安全，ACID 四大特性是核心
视图简化查询、提升安全性，适合封装常用查询逻辑

亚马逊云科技技术品牌专区

更多推荐

Docker容器安全加固指南

从2018年的Kubernetes漏洞到近年频发的容器逃逸事件，每一次安全事件都在提醒我们：容器安全不是可选项，而是必选项。真正的容器安全始于意识，固于技术，成于习惯。随着云原生技术的不断发展，安全威胁也在不断演变，唯有建立持续改进的安全文化，采用纵深防御策略，才能在这个动态变化的战场上保持主动。记住：最安全的容器不是无法攻破的容器，而是攻击者认为不值得花费精力攻击的容器。注：容器安全是一个快速发

亚马逊云科技技术品牌专区

ESP8266通过MQTT接入华为云物联网平台

本文介绍了基于华为云IoT平台的设备数据交互实现方案，分为两个阶段：1）使用MQTTX工具模拟设备，完成与华为云平台的连接测试，验证属性上报与命令下发机制；2）通过ESP8266 WiFi模块实现真实硬件接入，详细说明了AT指令配置流程，包括WiFi连接、MQTT参数设置、主题订阅和数据上报。重点解决了JSON数据格式处理、字符编码统一等常见问题，并强调需烧录支持MQTT的固件（8Mbit Fla