个人博客
https://blog.csdn.net/cPen_web
Python里连接MySQL的库
mysql-connector
records
pymysql
Python3 MySQL 数据库连接 - PyMySQL
详细步骤 https://www.runoob.com/python3/python3-mysql.html
步骤:安装PyMySQL
[root@cPen ~]# pip3 install pymysql
步骤:实例链接 Mysql 的 jd 数据库
[root@cPen ~]# python3
>>> import pymysql # 打开数据库连接 ↓
>>> db = pymysql.connect(host="192.168.1.11",user="jd",password="Sanchuang123#",database="jd")
>>> cursor = db.cursor() #注:创建一个游标
#注:游标:是mysql里的缓存空间 --> 类似 大使馆
>>> cursor.execute("select version()") # 使用 execute() 方法执行 SQL 查询
1
>>> data = cursor.fetchone() # 使用 fetchone() 方法获取单条数据.
>>> print(data)
('5.7.32',)
>>> db.close() # 关闭数据库连接
root@(none) mysql>create user 'stone'@'127.0.0.1' identified by '123456';
root@(none) mysql>create user 'stone'@'192.168.1.11' identified by '123456';
以上创建的2个用户,使用下面的方式登录不了
[root@cPen lianxi]# mysql -ustone -p'123456';
ERROR 1045 (28000): Access denied for user 'stone'@'localhost' (using password: YES)
#注:其实是使用文件socket的方式连接到mysqld进程,进行数据交互
stone@localhost
root@(none) mysql>select host,user from mysql.user;
+--------------+---------------+
| host | user |
+--------------+---------------+
| 127.0.0.1 | stone |
| 192.168.1.11 | stone |
| localhost | root |
+--------------+---------------+
root@(none) mysql>create user 'stone'@'localhost' identified by '123456';
root@(none) mysql>^DBye
[root@cPen lianxi]# mysql -ustone -p'123456'
stone@(none) mysql> #注:登录成功
mysql和mariadb的差异
1 .
mysql登录进去 mysql>
mariadb登录进去 MariaDB [(none)]> 还可以看到在哪个数据库
2 .
mysql和mariadb 用户表里的字段 mysql.user
mariadb存放密码的字段就叫 Password
mysql存放密码的字段叫 authentication_string
GRANT
with grant option子句
root@localhost
这个用户可以使用grant语句给其他的用户授权
with grant option子句
通过在grant语句的最后使用该子句,就允许被授权的用户把得到的权限继续授给其它用户
root@(none) mysql>create user 'heyachen'@'%' identified by '123456';
root@(none) mysql>grant all on *.* to 'heyachen'@'%';
#注:用户有create的权利,但没有grant权利
root@(none) mysql>create user 'huangtao'@'%' identified by '123456';
root@(none) mysql>grant all on *.* to 'huangtao'@'%' with grant option;
root@(none) mysql>select * from mysql.user where user='huangtao' \G;
……
Grant_priv: Y
#注:用户有grant的权利,可以给别人授权
如何查看授予给某个用户的权限?
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();
root@(none) mysql>show grants; #注:默认查看当前用户
root@(none) mysql>show grants for 'huangtao';
+-----------------------------------------------------------------+
| Grants for huangtao@% |
+-----------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'huangtao'@'%' WITH GRANT OPTION |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)
--> 背后查看的是 information_schema.USER_PRIVILEGES 表
root@(none) mysql>show engines;
--> 本质上 查看information_schema.ENGINES 表
示例:知道当前是哪个用户 select user();
root@(none) mysql>select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
或者看前面的提示符
示例:查看mysql版本 select version();
root@(none) mysql>select version();
REVOKE
废除权限 revoke
root@(none) mysql>revoke all on *.* from 'heyachen'@'%'; #注:回收权限
root@(none) mysql>show grants for 'heyachen';
+--------------------------------------+
| Grants for heyachen@% |
+--------------------------------------+
| GRANT USAGE ON *.* TO 'heyachen'@'%' |
+--------------------------------------+
1 row in set (0.00 sec)
create user 'yangsiting'@'192.168.0.%' identified by '123456';
--> 允许整个192.168.0.0/24网段可以访问
主机名使用localhost还是127.0.0.1?
使用localhost,是通过socket来连接(文件socket);使用127.0.0.1,是通过tcp/ip来连接(网络socket)
用户具有usage权限意味着“没有权限”,它只表示该用户可以连接到数据库。无法废除该权限
DML
数据操纵语言
select insert delete
DATA MANIPULATION LANGUAGE(数据操纵语言),由INSERT、UPDATE、DELETE等语句构成,用来修改表中的数据
INSERT
# 关键字 for print --> 预用的 --> 预先保留的变量名 keyword
>>> import sys #注:操纵Python本身
>>> import os #注:操纵Linux操作系统
>>> import keyword #注:导入关键字模块
>>> print(keyword.kwlist) #注:查询所有关键字
>>> print(len(keyword.kwlist)) #注:查询关键字一共多少个
>>> keyword.kwlist
['False', 'None', 'True', 'and', 'as', 'assert', 'break', 'class', 'continue', 'def', 'del', 'elif', 'else', 'except', 'finally', 'for', 'from', 'global', 'if', 'import', 'in', 'is', 'lambda', 'nonlocal', 'not', 'or', 'pass', 'raise', 'return', 'try', 'while', 'with', 'yield']
insert into TEAMS(TEAMNO,playerno,division) values(3,10,"third")
#注:表名区分大小写,字段名不区分大小写
INSERT TEAMS VALUES(4,8,"fourth")
insert TEAMS values(5,"fifth",9) #注:会出错
#注:SQLyog 右击 改变表 --> 查看表结构
-----------------------------------------------------------------
2、在表名后面省略所有的列名
这种写法要求VALUES子句中的值必须按照列在表结构中的顺序来一一赋值
INSERT INTO teams
VALUES(4,104,'third');
-----------------------------------------------------------------
3、在表名后面只列出部分的列名
所有没有明确赋值的列,将通过隐式赋值自动得到null值
例2: 添加一个新球员
INSERT INTO players(playerno,NAME,initials,sex,joined,street,town)
VALUES(611,'Jones','GG','M',1997,'Green Way','Stratford');
#注:没指定的列 它的值都为null
SQL语句之间接;分号,单独执行1条可以不接
-----------------------------------------------------------------
count(*) --> count() 是一个统计函数,统计某列有多少行数据
* 代表 列名 --> 字段名
root@TENNIS mysql>select name,town,joined,playerno from PLAYERS;
root@TENNIS mysql>select name,town,joined,playerno from PLAYERS where joined=1980;
#示例:统计行数 (所有显示的字段有多少行) --> 输出出来行的数量
root@TENNIS mysql>select name,town,joined,playerno,count(*) from PLAYERS where joined=1980;
+-----------+-----------+--------+----------+----------+
| name | town | joined | playerno | count(*) |
+-----------+-----------+--------+----------+----------+
| Newcastle | Inglewood | 1980 | 8 | 3 |
+-----------+-----------+--------+----------+----------+
1 row in set (0.00 sec)
sum() --> 求和函数
sum(amount) --> 里面放的列名,将所有的值相加
root@TENNIS mysql>select sum(amount) from PENALTIES;
+-------------+
| sum(amount) |
+-------------+
| 480.00 |
+-------------+
1 row in set (0.00 sec)
#注:SQL语句操纵的是列
root@TENNIS mysql>select sum(amount) from PENALTIES where amount >50;
VALUES子句中除了字面量(#注:即固定的值),还可以使用函数、计算、标量子查询等
INSERT INTO totals(numberplayers,sumpenalties)
VALUES((SELECT count(*) FROM players),
(SELECT sum(amount) FROM penalties));
注意:子查询必须放在单独的小括号中
#注:子查询 --> 先执行子查询,再执行外层的
#示例:统计有多少用户 (小圆括号 相当于子查询)
[root@cPen ~]# usernum=$(cat /etc/passwd |wc -l)
[root@cPen ~]# echo $usernum
21
[root@cPen ~]# echo $(cat /etc/passwd |wc -l)
21
[root@cPen ~]# echo `cat /etc/passwd |wc -l`
21
#注:反引号不能嵌套,$()更好
一条INSERT语句可以插入多个行,用,逗号隔开
INSERT INTO teams(teamno,playerno,division)
VALUES (6,7,'third'),
(7,27,'fourth'),
(8,39,'fourth'),
(9,112,'sixth');
带子查询的INSERT语句
将联盟会员号为空的球员插入到recr_players表中
INSERT INTO recr_players
(SELECT playerno, NAME, town, phoneno
FROM players
WHERE leagueno IS NULL);
共 4 行受到影响
#注:将select查询的结果插入到表里去。先子查询select,然后再把值传给insert语句
把那些罚款额大于平均罚款额的所有罚款添加到penalties表中
INSERT INTO penalties
SELECT paymentno + 100,playerno,payment_date,amount
FROM penalties
WHERE amount > (SELECT avg(amount)
FROM penalties);
共 4 行受到影响
#示例:查询联盟编号是null值的
select * from PLAYERS where LEAGUENO IS NULL;
select * from PLAYERS where LEAGUENO IS not NULL;
例8:把那些罚款额大于平均罚款额的所有罚款添加到penalties表中
INSERT INTO PENALTIES
SELECT paymentno + 100,playerno,payment_date,amount
FROM PENALTIES
WHERE amount > (SELECT avg(amount)
FROM PENALTIES);
#注:avg() 通过计算的,必须要用select语句得到
paymentno + 100 是为了避免主键值一样 (主键约束)
需求分析:
1 .确定 表 --> 罚款表
2 .确定 字段 --> 球员号和罚款额
3 .确定 条件 --> 罚款额大于平均罚款额
avg(amount)
UPDATE
#示例:插入2行数据
root@TENNIS mysql>insert into totals values(20,1000),(30,2000);
#示例:不接where条件语句,会修改所有行
root@TENNIS mysql>update totals set sumpenalties=10000;
#注:工作中误操作怎么恢复:用以前的备份恢复,通过备份回滚,二进制日志 通过日志回滚
#示例:接where条件语句
root@TENNIS mysql>update totals set sumpenalties=480 where numberplayers=15;
例1: 把95号球员的联盟会员号码改为2000
表:球员
字段:联盟会员号码
条件:95号球员
UPDATE PLAYERS SET LEAGUENO='2000' WHERE PLAYERNO=95;
例2: 把所有的罚款增加5%
表:罚款表
字段:AMOUNT
条件:所有的行
UPDATE PENALTIES SET AMOUNT=AMOUNT*1.05;
例3: 把住在Stratford的球员的获胜局数设为0 --> 牵扯到 主键、外键、子查询
表:MATCHES PLAYERS
字段:获胜局数
条件:住在Stratford的球员
子查询:SELECT PLAYERNO,TOWN FROM PLAYERS WHERE TOWN="Stratford";
update MATCHES SET won=0 where MATCHES.PLAYERNO IN (SELECT PLAYERNO FROM PLAYERS WHERE TOWN="Stratford");
某个字段的值为NULL 和 空值 有什么区别? 当某个字段类型为字符串的时候
1 .占用空间区别
空值('')的长度是0,是不占用空间的;而的NULL长度是NULL,其实它是占用空间的
2 .插入/查询方式区别
mysql> INSERT tb_test VALUES (NULL,NULL);
mysql> INSERT tb_test VALUES ('','');
如果要单纯查NULL值列,则使用 is NULL去查,单纯去查空值('')列,则使用 =''
3 .COUNT 和 IFNULL函数
总结:使用 COUNT(字段) 统计会过滤掉 NULL 值,但是不会过滤掉空值
说明:IFNULL有两个参数。 如果第一个参数字段不是NULL,则返回第一个字段的值。 否则,IFNULL函数返回第二个参数的值(默认值)
4 .索引字段说明
MySql中如果某一列中含有NULL,那么包含该列的索引就无效了
1 .存储空间上说明
NULL值 其实数据库需要在另外地方记录下,这个字段的值为NULL --> NULL值也是值 --> 空气
空值 是不消耗存储空间的 --> 真空状态
2 .在count函数统计的时候,NULL值所在的列不计入
root@TENNIS mysql>select name,char_length(name),length(name) from t1;
+------+-------------------+--------------+
| name | char_length(name) | length(name) |
+------+-------------------+--------------+
| | 0 | 0 |
| NULL | NULL | NULL |
+------+-------------------+--------------+
ORDER BY 排序
排序是根据字段的内容来排序
升序:从小到大 --> 默认 ASC
降序:从大到小 --> DESC
#示例:根据amount字段来排序,默认升序
root@TENNIS mysql>select * from PENALTIES order by amount; #注:升序
#示例:排序 --> 降序
root@TENNIS mysql>select * from PENALTIES order by amount DESC; #注:降序
UPDATE语句中可以使用ORDER BY子句,要求以排序的顺序来依次更新行。这在某些场景可能有用。例如,如果想要把所有罚款的罚款编号都加1,如果从罚款编号为1的行开始更新,要么就会发生主键值重复异常。如果从罚款编号最大的行开始更新,就没有问题(#注:即降序)。
例6:把所有罚款的编号增加1
UPDATE penalties
SET paymentno = paymentno + 1
ORDER BY paymentno DESC;
LIMIT --> 限量
罚款表 --> 罚款额最高的前3名的球员编号和总罚款金额
表:罚款表
字段:sum(amount)
条件:前3名
分组:group by 接列名
limit
select playerno,sum(amount) from PENALTIES group by playerno;
select playerno,sum(amount) from PENALTIES group by playerno order by sum(amount) DESC;
select playerno,sum(amount) from PENALTIES group by playerno order by sum(amount) DESC limit 3;
聚合函数:sum() count() avg() --> 聚集合成一个值
取第4行、第5行
limit 3,2
ORDER BY amount DESC, playerno ASC #注:罚款金额一样,再根据第2个字段排序
#示例:显示第3行 --> 偏移量offset 2,前面2条不要
root@TENNIS mysql>select * from PENALTIES ORDER BY amount DESC limit 2,1;
root@TENNIS mysql>help select
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
offset 偏移量
row_count 行数
更新多个表中的值
#注:可以改多个表,但是一定要有关联条件(主外键),进行连接查询。多表连接,只有外键能确定唯一性
MySQL允许我们使用1条UPDATE语句就更新两个或多个表中的行
例8:把一个first分级球队的所有比赛的获胜局数设为0,并把first分级球队的队长编号改为112
UPDATE MATCHES m,TEAMS t
SET m.won = 0,
t.playerno = 112
WHERE t.teamno = m.teamno
AND t.division = 'first';
共 6 行受到影响
MySQL首先执行一个二表连接查询,从两个表中找到满足连接条件 t.teamno = m.teamno 的所有行,然后对这些行分别进行更新
使用一条语句更新多个表的优点是:要么两个表都更新,要么两个表都不更新
例8:把一个first分级球队的所有比赛的获胜局数设为0,并把first分级球队的队长编号改为112
UPDATE
表:TEAMS MATCHES
字段:获胜局数 --> MATCHES 队长编号 --> TEAMS
条件:TEAMNO first分级球队
update TEAMS t,MATCHES m set m.won=0,t.playerno=112 where t.division="first" and t.teamno=m.teamno;
REPLACE语句
替代已有的行
REPLACE语句是INSERT语句的一个变种。当添加新行时,如果主键值重复,那么就覆盖表中已有的行。如果没有主键值重复,则插入该行
#注:replace和insert的区别:如果主键值重复,那么就覆盖表中已有的行
root@TENNIS mysql>insert into t2(id,name) values(1,'cali');
root@TENNIS mysql>insert into t2(id,name) values(1,'cali');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' #注:Duplicate entry 重复的条目
entry条目 record记录
一行记录就是一个条目
#示例:REPLACE
root@TENNIS mysql>replace into t2(id,name) values(1,'califeng');
Query OK, 2 rows affected (0.00 sec)
root@TENNIS mysql>update t2 set name="fengdeyong" where id=1;
Query OK, 1 row affected (0.00 sec)
DELETE
从表中删除满足WHERE条件的所有行。没有WHERE条件,则删除表中的所有行
#示例:DELETE语句
root@TENNIS mysql>delete from t2; #注:没有WHERE条件,则删除表中的所有行
root@TENNIS mysql>delete from t2 where id=1; #注:删除为id=1的行
#示例:带子查询
delete操作的时候,不允许根据本表做查询,又删除本表的数据
带子查询
例2: 删除球员,条件是他们加入俱乐部的年份晚于来自于Stratford的球员加入俱乐部的平均年份
注意,在WHERE子句的子查询中,不允许访问要删除行的表
root@TENNIS mysql>DELETE FROM PLAYERS WHERE joined > (SELECT avg(joined) FROM PLAYERS WHERE town = 'Stratford');
ERROR 1093 (HY000): You can't specify target table 'PLAYERS' for update in FROM clause
clause 子句
操作如下
#第一步,复制父表 --> 子表
CREATE TABLE players_copy2
AS SELECT * FROM PLAYERS;
#第一步,删数据
DELETE FROM PLAYERS
WHERE joined > (
SELECT avg(joined)
FROM players_copy2
WHERE town = 'Stratford');
例2: 删除球员,条件是他们加入俱乐部的年份晚于来自于Stratford的球员加入俱乐部的平均年份
delete
表:球员
字段:加入俱乐部的年份
条件:加入俱乐部的年份 > 来自于Stratford的球员加入俱乐部的平均年份
子查询:平均年份 来自于Stratford
select avg(joined) from players_copy2 where town='Stratford';
例3: 删除4个最高的罚款
DELETE FROM penalties
ORDER BY amount DESC,playerno ASC
LIMIT 4;
从多个表中删除行
语法:
DELETE [IGNORE] tbl_name[.*] [, tbl_name[.*]] ...
FROM table_references
[WHERE where_condition]
如果FROM中的表有别名,在DELETE子句中只能使用表别名
例4: 从teams和matches表中删除所有3号球队的行
DELETE teams, matches
FROM teams, matches
WHERE teams.teamno = matches.teamno
AND teams.teamno=3;
两个表中满足连接条件teams.teamno = matches.teamno和过滤条件teams.teamno=3的所有行被删除
TRUNCATE
DELETE和TRUNCATE操作的区别
清空一张(大)表更有效的方法是使用TRUNCATE语句,它比DELETE快得多
锁的角度:delete删除 是一行行删,对每行加锁;truncate操作 是对表加锁,直接把表删了
日志的角度:delete操作产生日志,而truncate操作不产生日志
MySQL里有二进制日志
二进制日志:记录我们的SQL操作
通过备份恢复
InnoDB支持行锁,MyISAM支持表锁
feature 属性、特点、特性
MVCC:多版本并发控制 --> 大并发操作的时候,提高读写的性能,能保持数据的一致性
--> 事务 (背后用的是事务) --> 行锁
Innodb存储引擎支持MVCC功能,MyISM不支持
Multi-Version Concurrency Control 多版本并发控制
MVCC是一种多版本并发控制机制
多版本控制: 指的是一种提高并发的技术。最早的数据库系统,只有读读之间可以并发,读写,写读,写写都要阻塞。引入多版本之后,只有写写之间相互阻塞,其他三种操作都可以并行,这样大幅度提高了InnoDB的并发度。
Multi-Version Concurrency Control 多版本并发控制,MVCC 是一种并发控制的方法,一般在数据库管理系统中,实现对数据库的并发访问;在编程语言中实现事务内存
#注:要想使用MVCC 必须要支持事物。Innodb支持事物(transaction)
Transactions 事物
mariadb为什么只有表结构文件(.frm),没有表数据文件(.ibd)?(MySQL和mariadb的区别)
mariadb 里采用了一种方法,当表里的数据特别少的时候,先使用公共的数据文件,存放。默认情况下不给每个表都独立创建一个数据文件,而是先共用。
mysql 采用一个表独立创建一个数据文件
mariadb每个表创建一个数据文件,如何实现?
#注:改这个参数 innodb_file_per_table
1 .修改配置文件
[root@cPen ~]# vim /etc/my.cnf
[mysqld]
innodb_file_per_table=1 #注:1表示开,0表示关
[root@cPen ~]# service mysqld restart
2 .在mysql数据库里临时设置变量
root@(none) mysql>set global innodb_file_per_table=1;
root@(none) mysql>show variables like "innodb_file_per_table";
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.01 sec)
#示例:truncate语句 删除表
root@TENNIS mysql>truncate table t2;
root@TENNIS mysql>select * from t2;
Empty set (0.00 sec)
事务
事务:transaction
一个数据库事务由一条或者多条sql语句构成,它们形成一个逻辑的工作单元。这些sql语句要么全部执行成功,要么全部执行失败
事务是保证数据的完整性和一致性的重要手段
事务类型
DML事务:由一条或者多条DML语句构成
DDL事务:总是由一条DDL语句构成
DCL事务:总是由一条DCL语句构成
ACID
原子性、一致性、隔离性、持久性
#注:原子性有2个操作:commit(提交)、rollback(回滚)
原子性:
COMMIT 提交 --> 让缓存里的数据写到磁盘
ROLLBACK 回滚 --> 撤销操作 --> 在缓存里删除数据
#注:一致性 --> 数据一样,不能有多个版本,只有一种可能
#注:持久性 --> 数据要落盘,写在磁盘里
root@TENNIS mysql>show variables like "innodb_file_per_table"; #注:一个文件对应一个表空间
+-----------------------+-------+ #注:innodb存储引擎给每一个表建立文件 (参数)
| Variable_name | Value | #注:innodb独立的表空间
+-----------------------+-------+ #注:每一个表创建一个表文件
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.01 sec)
#示例:改这个参数
# 方法1 修改配置文件
[root@cPen ~]# vim /etc/my.cnf
innodb_file_per_table=1 #注:1表示开,0表示关
# 方法2 立马生效
#或 root@(none) mysql>set global innodb_file_per_table=1;
[root@cPen ~]# service mysqld restart
root@(none) mysql>show variables like "innodb_file_per_table";
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.01 sec)
innodb的架构图
root@(none) mysql>show variables like 'innodb_file_per_table';
| innodb_file_per_table | ON |
root@(none) mysql>set global innodb_file_per_table=1;
表空间 --> 其实就是表对应的文件 --> 表占用的磁盘空间 --> 表空间(tablespaces)
innodb存储引擎 1个表(即文件)最大可以有多大? --> 存储引擎的限制
Storage limits 64TB
MyISAM存储引擎 1个表(即文件)最大可以有多大? --> 存储引擎的限制
Storage limits 256TB
事务类型
DML事务:由一条或者多条DML语句构成
DDL事务:总是由一条DDL语句构成 --> 主要 建表、建库、改表、改库
DCL事务:总是由一条DCL语句构成 --> grant revoke (数据控制语句)
MySQL里 @@接变量名代表是系统变量 @接变量名是自定义变量
root@(none) mysql>set @sg="zhangheng"; #注:定义变量,接@
root@(none) mysql>select @sg; #注:使用变量 --> 相当于print、echo
+-----------+
| @sg |
+-----------+
| zhangheng |
+-----------+
1 row in set (0.00 sec)
root@(none) mysql>select @mv,@sg;
#注:不接@符号,表示系统变量
#示例:把变量 插入到表中
root@test mysql>insert into t1 values(1,@mv),(2,@sg); #注:引用变量要接@符号
平常每输入一条SQL语句,其实背后就是开启一个事务,然后MySQL会自动提交 autocommit自动是开启的
在MySQL中,系统变量@@autocommit默认是打开的,这意味着任何1条SQL语句都会开始一个事务,语句执行完后事务自动结束。实际使用中,应该使用SET语句来关闭自动提交,否则一个事务不可能由多条SQL语句构成
root@test mysql>show variables like "autocommit"; #注:敲完一个SQL语句 自动帮提交
| Variable_name | Value |
| autocommit | ON |
SET @@autocommit=0;
root@test mysql>SET @@autocommit=0; #注:关闭
#效果:每敲完一个SQL语句,不会自动帮提交 --> 事务 --> 没有落盘 (事务没有做commit操作)
缓存调用存储引擎落盘的,因为innodb存储引擎autocommit自动提交是关闭的,只有敲commit才能落盘
#示例:手动提交 commit,缓存里的数据 落盘
root@test mysql>commit;
因为事务的隔离性,事务是隔离开的,不同的事务 不能读其他事务缓存里面的东西
buffer pool 缓存池 --> 最大可以使用物理内存的80%的空间
缓存 使用的是内存的空间
rollback操作:把缓存里的东西 删了,不落盘了。撤销之前做的操作
#注:commit; 提交完了以后 就不能撤销了
#示例
root@test mysql>rollback;
root@test mysql>commit;
对于DDL(create、alter、drop等开头的语句)和DCL(grant、revoke语句)事务,在执行每条语句之前和之后,MySQL会自动执行一条COMMIT语句,因此事务是自动开始和结束的。自动提交打开或者关闭对这些事务没有影响
对于DML事务(insert create等操作),在自动提交关闭的情况下,事务的开始分为隐式开始和显式开始:
隐式开始:程序的第一条DML语句执行时或者在COMMIT或ROLLBACK语句之后执行第一条DML语句时,自动开始一个新的事务 --> autocommit开启的情况 on
显式开始:发出STRAT TRANSACTION语句。该语句会自动关闭自动提交,当事务结束后,autocommit变量恢复到原来的值 --> STRAT TRANSACTION输入后,临时关一下;commit/rollback后恢复on
DML事务的结束
COMMIT语句:成功提交。事务所做的全部工作被永久地保存到磁盘上
ROLLBACK语句:失败回滚。事务所做的全部工作被撤销,表中的数据不受事务操作的影响
其它事务控制语句
SAVEPOINT identifier :保存点命令,用来在事务中做一个标记,专门提供给rollback to语句使用
ROLLBACK TO [SAVEPOINT] identifier:回滚到保存点。专门用来撤销事务所做的部分工作:保存点之后所做的工作全部撤销。该语句并不结束事务
示例:
START TRANSACTION;
INSERT INTO trans_demo(id,NAME) VALUES(1,'zs');
……
SAVEPOINT a;
UPDATE trans_demo SET NAME='ww' WHERE id=1;
SAVEPOINT b;
DELETE FROM trans_demo WHERE id=2;
-- 下面决定如何结束事务
ROLLBACK TO b;
ROLLBACK TO a;
ROLLBACK;