MySQL数据库操作学习(6)事务与pymysql

一只特立独行猪

于 2024-03-22 18:58:43 发布

阅读量1k

点赞数 12

分类专栏：数据库mysql 文章标签：数据库 mysql 学习

本文链接：https://blog.csdn.net/Appppiiiiggg/article/details/136949573

版权

数据库mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

文章目录

1、视图

视图（View）是一种虚拟的表，它是基于一个或多个实际表（或其他视图）的查询结果集。视图本身并不包含实际的数据，而是根据定义视图时指定的查询语句动态生成结果。通过视图，用户可以以类似于表的方式访问和操作数据，而无需直接访问底层表。

以下是关于视图的一些重要信息和优点：

简化数据访问：视图可以隐藏底层表的复杂性，为用户提供一个简单易用的界面来访问数据。
数据安全性：视图可以限制用户只能访问他们需要的数据，通过在视图上设置权限来控制数据的访问。
数据一致性：视图可以确保数据的一致性，通过在视图中定义复杂的查询来确保数据的准确性。
重用性：视图可以重用查询逻辑，避免重复编写相同的查询语句。
性能优化：视图可以提高查询性能，通过预先计算和存储视图结果，减少重复计算的开销。

在创建视图时，需要定义视图的结构和查询语句。一旦视图创建完成，用户可以像操作表一样使用视图，包括查询、插入、更新和删除数据。视图可以根据需要进行更新，但有一些限制条件，例如视图必须包含主键或唯一键等。

总的来说，视图是数据库中一个非常有用的工具，可以提高数据访问的灵活性和安全性，同时简化复杂查询的操作。

# 创建视图表
create view 视图名称 as select 已查询语句;

create view stu_2301 as select * from st1 where class = 2301;
select * from stu_2301;

# 删除视图
drop view 视图名称;

# 查询当前数据库所有的视图
show full tables in 数据库名称 where table_type like 'view';

show full tables in demo1 where table_type like 'view';

2、索引

索引：就是目录
一般如果你的表查询操作很少，数据很少（少于1w）不建议索引

目的：就是优化我们的数据库工作效率，但是也会占用资源

主键索引
普通索引
唯一索引

# 在创建表的时候创建索引
# 格式
create table 表名(
	字段名 数据类型,
	字段名 数据类型,
    # 普通索引
    index 索引名称(字段名),
    # 唯一索引
	unique 索引名称(字段名)
    
);

# 能创建表后插入索引

create index 索引名称 on 表名(字段名);
create unique 索引名称 on 表名(字段名);

# 查询索引
show keys from 表名;


select * from t21 WHERE id = 11;
select * from t22 WHERE id = 11;

3、存储引擎

在关系型数据库中，存储引擎（Storage Engine）是负责管理数据存储和检索的核心组件，它定义了数据的存储格式、索引类型、查询处理方式等。不同的存储引擎具有不同的特性和适用场景，常见的存储引擎包括但不限于以下几种：

InnoDB：
- InnoDB 存储引擎是 MySQL 中最常用的存储引擎之一，具有事务支持、行级锁、外键约束等特性。
- InnoDB 存储引擎适合于需要事务支持和高并发读写操作的场景，如在线交易处理系统。
MyISAM：
- MyISAM 存储引擎是 MySQL 中的另一个常见存储引擎，具有全文索引、压缩表等特性。
- MyISAM 存储引擎适合于读密集型的应用，如数据仓库、报表系统等。
Memory：
- Memory 存储引擎将数据存储在内存中，提供了快速的数据访问速度，但数据不会持久化到磁盘。
- Memory 存储引擎适合于临时数据存储、缓存等场景。
NDB Cluster：
- NDB Cluster 存储引擎是 MySQL Cluster 中的存储引擎，支持分布式存储和高可用性。
- NDB Cluster 存储引擎适合于需要水平扩展和高可用性的应用。
PostgreSQL 中的存储引擎：
- PostgreSQL 中没有明确的存储引擎概念，而是使用表空间（Tablespaces）来管理数据存储。不同的表空间可以使用不同的存储引擎或文件系统。

选择合适的存储引擎取决于应用的需求，如对事务支持、并发性能、数据完整性、存储空间利用率等方面的要求。在设计数据库时，需要根据具体场景和需求选择最适合的存储引擎，以达到最佳的性能和可靠性。

# 查询储存引擎：
show engine;

4、事务(重点)

①介绍

事务（Transaction）是数据库管理系统中用于确保数据操作的一致性和完整性的一种机制。事务是一组数据库操作（如插入、更新、删除数据）的逻辑单元，要么全部操作成功，要么全部操作失败，保证了数据的完整性和一致性。事务具有以下四个特性，通常简称为 ACID 特性：

原子性（Atomicity）：
- 原子性指事务中的所有操作要么全部成功提交，要么全部失败回滚。即事务是不可分割的最小工作单位，要么全部执行，要么全部撤销。
一致性（Consistency）：
- 一致性指事务在执行前后，数据库从一个一致的状态转换到另一个一致的状态。即事务执行前后，数据库的完整性约束没有被破坏。
隔离性（Isolation）：
- 隔离性指多个事务并发执行时，每个事务的操作应该与其他事务隔离，互不干扰。事务之间应该是相互独立的，不应该相互影响。
持久性（Durability）：
- 持久性指一旦事务提交，其所做的修改将会永久保存在数据库中，即使系统发生故障，数据也不会丢失。

事务通常遵循以下四个基本操作步骤，通常称为 ACID 原则：

开始事务（Begin Transaction）：标志着事务的开始。
执行事务操作（Perform Transaction Operations）：包括插入、更新、删除等数据库操作。
提交事务（Commit Transaction）：将事务中的操作永久保存到数据库中。
回滚事务（Rollback Transaction）：撤销事务中的操作，回到事务开始前的状态。

通过事务的使用，可以确保数据库操作的一致性和完整性，防止数据丢失或不一致的情况发生。在开发应用程序时，合理地使用事务可以保证数据的可靠性，尤其在涉及多个数据库操作时更为重要。

事务处理：insert, update, delete语句

②事务隔离级别

事务隔离级别是数据库管理系统中用来控制事务之间相互影响程度的设置。不同的隔离级别决定了事务在并发执行时对数据的可见性和影响范围，从而影响了数据库系统的并发控制和性能。常见的事务隔离级别包括以下四种：

读未提交（Read Uncommitted）：
- 最低的隔离级别，允许一个事务读取另一个事务未提交的数据修改。
- 可能导致脏读（Dirty Read）和不可重复读（Non-Repeatable Read）问题
1. 脏读（Dirty Read）：
  - 脏读指一个事务读取了另一个事务未提交的数据修改。换句话说，一个事务读取到了另一个事务正在修改但尚未提交的数据。
  - 如果这个事务最终回滚/撤销，那么读到的数据就是无效的，这就是“脏读”。
2. 不可重复读（Non-Repeatable Read）：
  - 不可重复读指在同一个事务内，多次读取同一数据时，由于其他事务的更新操作，每次读取的数据可能不同。
  - 换句话说，一个事务在读取同一数据时，由于其他事务的更新操作，导致每次读取的数据都不一致，这就是“不可重复读”。
读已提交（Read Committed）：
- 允许一个事务只能读取已经提交的数据，避免了脏读问题。
- 但可能导致不可重复读和幻读（Phantom Read）问题。
幻读（Phantom Read）是数据库中另一个并发问题，与不可重复读类似，也是指在同一个事务内多次执行相同的查询，但由于其他事务的插入或删除操作，每次查询的结果集可能不同的现象。

举个例子来说明幻读：假设事务A首先执行一个查询，查询某个范围内的数据行，然后事务B在这个范围内插入了一行新数据并提交事务，接着事务A再次执行相同的查询，这时会发现结果集中多出了一行数据，就好像出现了幻影般的新数据，这就是幻读。
可重复读（Repeatable Read）：
- 不可重复读通常涉及到同一行数据的更新或删除操作，在可重复读级别下，事务在第一次读取数据后，即使其他事务对该数据进行了修改或删除，事务再次读取该数据时仍然能够看到第一次读取时的，事务在第一次读取数据后会对相关数据行进行加锁，防止其他事务修改或删除这些数据，。
- 而幻读通常涉及到数据集合的变化，例如在一个范围内新增或删除数据行。在可重复读级别下，虽然事务会对读取的数据行进行加锁，但并不会锁定整个范围，因此其他事务仍有可能在该范围内插入新的数据行，导致事务多次读取同一范围数据时发生幻读。可重复读级别下的锁定机制无法完全阻止其他事务对数据集合的影响，因此无法完全避免幻读问题。
串行化（Serializable）：
- 最高的隔离级别，确保事务之间完全隔离，避免了脏读、不可重复读和幻读问题。
- 在串行化级别下，数据库会以串行化的方式执行事务，即每个事务都好像是在系统中独立运行一样，不会受到其他事务的影响。具体来说，串行化级别会对事务执行的数据进行严格的加锁，确保事务在读取和修改数据时不会受到其他事务的干扰，从而保证了数据的一致性和完整性。
  
  虽然串行化级别能够避免绝大部分的并发问题，但由于其严格的加锁机制可能导致系统性能下降，因此在实际应用中并不常用。一般情况下，开发人员会根据实际需求和应用场景选择合适的事务隔离级别，权衡数据一致性和性能之间的关系。

选择合适的事务隔禽级别取决于应用的需求和对数据一致性的要求。通常情况下，读已提交和可重复读是较为常用的隔离级别，可以根据具体场景来选择。在开发应用程序时，需要根据业务需求和性能考量来合理设置事务隔离级别，以确保数据的一致性和并发性能。

/*
事务还可以分两个大类：隐性事务和显性事务

隐性事务很简单： 就是inset，delete, update 都是事务

显性事务带有明显的开始和结束的标志，下面就开始创建显性事务

*/
# 没有开始事务的时候, 这个是不能回滚，因为是自动提交事务
insert into account(name, money) values ("余恒镕", 50);
insert into account(name, money) values ("丁国峰", 20);




# 回滚事务
# begin 开始事务
begin;
insert into account(name, money) values ("陈婷", 5000);
insert into account(name, money) values ("小茗同学", 20000);

select * from account;


ROLLBACK;
select * from account;



# 提交事务
# begin 开始事务
begin;
insert into account(name, money) values ("陈婷", 5000);
insert into account(name, money) values ("小茗同学", 20000);

select * from account;


commit;
select * from account;

# 回滚必须在提交之前执行

5、pymysql(重点)

使用python执行mysql的sql语句进行增删改查

①导入模块

pip install pymysql

②链接数据库对象

import pymysql

# 开始建立数据库链接对象 db

db = pymysql.connect(

    # host 主机地址，因为我们的数据都是本机的127.0.0.1 或者本机域名localhost

    host="localhost",

    # user 链接数据库的用户名

    user="root",

    # password 用户名的密码

    password="root",

    # 数据库字符编码

    charset="utf8",

    # 你要使用的数据库名称

    database="demo1"

)

③创建游标对象（指针）

# 创建游标对象（理解为指针）就是代表该链接的数据库

# cursor专门创建游标

cur = db.cursor()

③隐式事务

# 这里不需要分号， sql语句不区分

sql = "DeSc st1"

# 如何提交svaql语句和查看sql语句

# execute 提交sql语句

cur.execute(sql)
#execute是一个关键字或方法，用于执行SQL语句或命令。在Python的数据库操作中，通常使用execute方法来执行SQL语句，比如查询、插入、更新或删除数据。

# 查看结果fetchall(), fetchone()

# 返回嵌套元组

print(cur.fetchall())

在数据库编程中，fetchall()和fetchone()是用于从数据库中获取查询结果的方法。这两个方法通常与execute()方法一起使用，用于检索执行SQL查询后返回的数据。

fetchall(): 该方法用于从最后一次执行的查询中获取所有的结果行。它会返回一个包含所有结果行的列表，每一行都表示为一个元组。如果查询没有返回任何结果，fetchall()将返回一个空列表。

fetchone(): 该方法用于从最后一次执行的查询中获取下一行结果。它会返回一个包含一行结果的元组。每次调用fetchone()时，游标会向下移动到下一行数据。如果没有更多的行可用，fetchone()将返回None。

这两个方法在处理查询结果时非常有用，可以帮助您从数据库中检索所需的数据并进行进一步处理。如果您有任何疑问或需要更多解释，请随时告诉我。

④显性事务

# 如何设置显性事务

# 插入到多个

sql = "insert into st1(name, class, garden) values (%s, %s, %s)"


values = [

    ("xxx6", '2301', "女"),
    ("xxx7", '2302', "男"),
    ("xxx8", '2302', "女")



]

# 使用多条数据结合方法executemany(sql语句，数据)

cur.executemany(sql, values)
db.commit()