MySQL中DML语句和事务的概念「建议收藏」

大家好，又见面了，我是你们的朋友全栈君。

ML语句

知识要点 DML语句 插入行到表中 删除表中的行 更新表中的行 控制事务

DML语句 DML：DATA MANIPULATION LANGUAGE（数据操纵语言），由INSERT、UPDATE、DELETE等语句构成，用来修改表中的数据

INSERT语句 1.带VALUES子句的INSERT语句 INSERT [INTO] tbl_name[(col_name,…)] {VALUES | VALUE} (expr ,…),(…),… 用来把一个新行插入到表中 为和其它数据库保持一致，不要省略INTO关键字以及使用VALUES而不是value关键字 插入一行时，要求必须对该行所有的列赋值。但是赋值方式可以是显式赋值（直接给出值）和隐式赋值（由MySQL自动赋值）

2.在表名后面列出所有的列名 示例： 插入一个新的球队到teams表中 INSERT INTO teams(teamno,playerno,division) VALUES(3,6,‘third’); ##需要一一对应，顺序一致

3.在表名后面省略所有的列名 这种写法要求VALUES子句中的值必须按照列在表结构中的顺序来一一赋值 示例：INSERT INTO teams VALUES(4,104,‘third’);

在这里插入图片描述

4.在表名后面只列出部分的列名 所有没有明确赋值的列，将通过隐式赋值自动得到null值 示例： 添加一个新球员 INSERT INTO players(playerno,NAME,initials,sex,joined,street,town) VALUES(611,‘Jones’,‘GG’,‘M’,1997,‘Green Way’,‘Stratford’);

5.使用字面量NULL给列赋空值 示例： INSERT INTO teams VALUES(4,104,null); ##注意null值不要加引号

1. VALUES子句中除了字面量，还可以使用函数、计算、标量子查询等 示例： CREATE TABLE totals( numberplayers INTEGER NOT NULL, sumpenalties DECIMAL(9,2) NOT NULL); ##创建表 INSERT INTO totals(numberplayers,sumpenalties) VALUES((SELECT count(*) FROM players), ##子查询的值必须是一行一列 (SELECT sum(amount) FROM penalties)); 注意：子查询必须放在单独的小括号中

在这里插入图片描述

7.一条INSERT语句可以插入多个行 示例：添加4个新的球队 INSERT INTO teams(teamno,playerno,division) VALUES (6,7,‘third’), (7,27,‘fourth’), (8,39,‘fourth’), (9,112,‘sixth’); 注意：这种语法只要有一行出错，则插入全部取消

在这里插入图片描述

8.INSERT语句中可以使用IGNORE选项来当INSERT语句出错时，不显示错误消息。INSERT语句不会执行

在这里插入图片描述

主键列不允许数据重复

9.带子查询的insert语句 带子查询的INSERT语句 INSERT [INTO] tbl_name[(col_name,…)] SELECT … ##select可以非常复杂，添加where条件等 语法：如果在表名后面列出了列名，那么列的数量和数据类型必须和子查询的select列表相匹配 示例：insert into stu_bak select sid,sname,aphonum from stu;

在这里插入图片描述

语句释义：stu_bak和stu表的数据类型和列的数量完全一致 Duplicates表示主键冲突的列：（主键冲突是主键上有重复的数据） Records：表是插入多少行数据 示例2： INSERT INTO penalties SELECT paymentno + 100,playerno,payment_date,amount FROM penalties WHERE amount > (SELECT avg(amount) ##无关子查询（因为没有where条件，没有对外表访问） FROM penalties); 语句释义：把那些罚款额大于平均罚款额的所有罚款添加到penalties表中 也可以把本表中的行再次添加到本表中。注意主键值不要重复

UPDATE语句 1.可以修改表中的数据 语法： UPDATE [IGNORE] table_reference（表名） SET col_name1=expr1 [, col_name2=expr2,],… [WHERE where_condition] [ORDER BY …] [LIMIT row_count] 将满足WHERE条件的所有行的一个或多个列值改为新的值。没有WHERE子句则修改所有的行 2.在写update语句之前，可以先把select语句列出来需要更新的数据，对比着写出update语句 示例： 例1： 把95号球员的联盟会员号码改为2000 UPDATE players ##表名 SET leagueno = 2000 ##指定哪些列需要更新和更新的数据 WHERE playerno = 95; ##指定哪些行需要更新 共 1 行受到影响

例2： 把所有的罚款增加5% UPDATE penalties SET amount = amount*1.05; 共 8 行受到影响

例3： 把住在Stratford的球员的获胜局数设为0 UPDATE matches SET won = 0 WHERE playerno IN(SELECT playerno FROM players WHERE town=‘Stratford’); 共 4 行受到影响

在这里插入图片描述

释义：先写出他们的select语句，对比写出update语句

3.update的其他写法（画图法） 通过画图的方法写update语句更容易理解 案例分析 CREATE TABLE players_data( playerno INTEGER NOT NULL PRIMARY KEY, number_mat INTEGER, sum_penalties DECIMAL(7,2) ## 新建表，有三列 ); ##新建表的每一列来自不同表的列中的数据（或者数据操作）

INSERT INTO players_data(playerno) ##从PLAYERS表中取出数据插入到新建表中 SELECT playerno FROM players; ##新建表的第一列数据已经插入完毕

UPDATE players_data pd ##更新，将新建表的第一列数据分别访问matches 表 SET number_mat = ( SELECT count(*) FROM matches m WHERE m.playerno = pd.playerno), ##number_mat列更新的数据为多表连接后行数 sum_penalties = ( SELECT sum(amount) FROM penalties pen WHERE pen.playerno = pd.playerno); ##sum_penalties表更新的数据为多表连接后的总数 语句释义：创建表players_data保存每个球员的编号、所参加比赛的次数，和所引起的罚款总数

4.update的注意事项 注意，在SET子句的子查询中，不允许访问要更新的表 案例分析 在每笔罚款中减去平均罚款额。以下写法不允许 UPDATE penalties SET amount = amount – (SELECT avg(amount) FROM penalties); ##set后，不允许出现要更新的表 错误代码： 1064

面对这种情况，我们可以使用变量的方法进行更新 SET @avg_amount := (SELECT avg(amount) FROM penalties); ##设置变量

UPDATE penalties SET amount = amount – @avg_amount; ##使用变量

5.update语句中的order by 语句 UPDATE语句中可以使用ORDER BY子句，要求以排序的顺序来依次更新行。这在某些场景可能有用。例如，如果想要把所有罚款的罚款编号都加1，如果从罚款编号为1的行开始更新，要么就会发生主键值重复异常。如果从罚款编号最大的行开始更新，就没有问题 update语句是先找数据，在进行更新

示例： UPDATE penalties SET paymentno = paymentno + 1 ORDER BY paymentno DESC; ##降序排列后加1 语句释义：把所有罚款的编号增加1

6.update语句中的limit语句 UPDATE语句中可以使用LIMIT子句，指定一次更新的行数

示例： UPDATE penalties SET amount= amount *1.05 ORDER BY amount DESC, playerno ASC ##对penalties表的数据进行排序 LIMIT 4; ##前4个 语句释义：把4个最高的罚款额增加5%(罚款额相同则更新编号小的球员)

补充：IGNORE选项用于当UPDATE语句出错时，不显示错误消息

7.update更新多个表中的值 更新多个表中的值 MySQL允许我们使用1条UPDATE语句就更新两个或多个表中的行 语法： UPDATE [IGNORE] table_references SET col_name1=expr1 [, col_name2=expr2,],… [WHERE where_condition] 其中， table_references可以使用任何合法的连接语法。不能使用ORDER BY和LIMIT子句

示例： UPDATE matches m,teams t SET m.won = 0, t.playerno = 112 WHERE t.teamno = m.teamno AND t.division = ‘first’; 语句释义：把一个first分级球队的所有比赛的获胜局数设为0，并把first分级球队的队长编号改为112 可以先使用select查看我们需要更改的数据（将两个表共有的且符合条件的显示出来）

在这里插入图片描述

补充：MySQL首先执行一个二表连接查询，从两个表中找到满足连接条件 t.teamno = m.teamno 的所有行，然后对这些行分别进行更新 使用一条语句更新多个表的优点是：要么两个表都更新，要么两个表都不更新

REPLACE语句 1.语句定义及语法 作用：替代已有的行 REPLACE语句是INSERT语句的一个变种。当添加新行时，如果主键值重复，那么就覆盖表中已有的行。如果没有主键值重复，则插入该行 语法： REPLACE [INTO] tbl_name [(col_name,…)] VALUES (expr,…),(…),… 或者 REPLACE [INTO] tbl_name [(col_name,…)] SELECT …

示例： REPLACE INTO players(playerno,NAME,initials, sex,joined,street,town) VALUES(611,‘john’,‘GG’,‘M’,1977,‘Green Way’, ‘Startford’); 语句释义： 添加一个新的球员。如果主键值已经存在，则覆盖该行

DELETE语句 1.delete说明及语法 delete语句只能一行一行的删，只能删除整行，不能删除某一行的某些列 语法： DELETE [IGNORE] FROM tbl_name [WHERE where_condition] [ORDER BY …] [LIMIT row_count] 从表中删除满足WHERE条件的所有行。没有WHERE条件，则删除表中的所有行

示例： DELETE FROM penalties WHERE playerno=44; 语句释义：删除44号球员的罚款

2.带子查询 注：在WHERE子句的子查询中，不允许访问要删除行的表 案例分析： CREATE TABLE players_copy2 AS SELECT * FROM players; ##因为在WHERE子句的子查询中，不允许访问要删除行的表，所以我们可以创建一张和PLAYERS表一样的表

DELETE FROM players_copy1 WHERE joined > ( SELECT avg(joined) FROM players_copy2 ## players_copy2 表和PLAYERS表一样 WHERE town = ‘Stratford’); 语句释义: 删除球员，条件是他们加入俱乐部的年份晚于来自于Stratford的球员加入俱乐部的平均年份 补充：面对较为复杂的删除，我们可以先用select语句将我们要删除的球员列出来，再将select *替换为delete即可

3.带ORDER BY子句和LIMIT子句 用在DELETE语句中的ORDER BY子句和LIMIT子句的含义和用在UPDATE语句中是类似的 示例： DELETE FROM penalties ORDER BY amount DESC,playerno ASC LIMIT 4; 语句释义：删除4个最高的罚款

4.从多个表中删除行 语法： DELETE [IGNORE] tbl_name[.] [, tbl_name[.]] … FROM table_references [WHERE where_condition] 如果FROM中的表有别名，在DELETE子句中只能使用表别名 示例： DELETE teams, matches FROM teams, matches WHERE teams.teamno = matches.teamno AND teams.teamno=3; 语句释义：从teams和matches表中删除所有3号球队的行； 两个表中满足连接条件teams.teamno = matches.teamno和过滤条件teams.teamno=3的所有行被删除

TRUNCATE语句 清空一张（大）表更有效的方法是使用TRUNCATE语句，它比DELETE快得多 原理：将表行尾的指针直接指向0，这样mysql认为该表数据已经清空，真实数据未清空，mysql后台程序或自动清理代表的数据 语法： TRUNCATE [TABLE] tbl_name 示例： Truncate table committee_members; ##将committee_members表清空

事务 1.事务：transaction 一个数据库事务由一条或者多条sql语句构成，它们形成一个逻辑的工作单元。这些sql语句要么全部执行成功，要么全部执行失败 事务是保证数据的完整性和一致性的重要手段 事务类型 DML事务：由一条或者多条DML语句构成 DDL事务：总是由一条DDL语句构成 DCL事务：总是由一条DCL语句构成

2.在MySQL中，系统变量@@autocommit默认是打开的，这意味着任何1条SQL语句都会开始一个事务，语句执行完后事务自动结束。实际使用中，应该使用SET语句来关闭自动提交，否则一个事务不可能由多条SQL语句构成 SHOW VARIABLES LIKE ‘%autocommit%’; SET @@autocommit=0; SHOW VARIABLES LIKE ‘%autocommit%’;

3.对于DDL（create、alter、drop等开头的语句）和DCL（grant、revoke语句）事务，在执行每条语句之前和之后，MySQL会自动执行一条COMMIT语句，因此事务是自动开始和结束的。自动提交打开或者关闭对这些事务没有影响 对于DML事务，在自动提交关闭的情况下，事务的开始分为隐式开始和显式开始： 隐式开始：程序的第一条DML语句执行时或者在COMMIT或ROLLBACK语句之后执行第一条DML语句时，自动开始一个新的事务 显式开始：发出STRAT TRANSACTION语句。该语句会自动关闭自动提交，当事务结束后，autocommit变量恢复到原来的值

4.DML事务的结束 COMMIT语句：成功提交。事务所做的全部工作被永久地保存到磁盘上 ROLLBACK语句：失败回滚。事务所做的全部工作被撤销，表中的数据不受事务操作的影响 其它事务控制语句 SAVEPOINT identifier ：保存点命令，用来在事务中做一个标记，专门提供给rollback to语句使用 ROLLBACK TO [SAVEPOINT] identifier：回滚到保存点。专门用来撤销事务所做的部分工作：保存点之后所做的工作全部撤销。该语句并不结束事务

5.事务示例

在这里插入图片描述

ROLLBACK TO b; ##回滚到a保存点 ROLLBACK TO a; ##回滚到b保存点 ROLLBACK; ##回滚到事务开始之前

6.COMMIT 或 ROLLBACK 语句之前数据的状态 数据的修改都是在内存中进行的 通过查询表，当前用户（事务）能够查看DML操作的结果 其它用户（事务）不能查看当前用户（事务）所做的DML操作的结果。这叫做不允许脏读（dirty read）。脏读：一个事务读到了另一个事务未提交的数据。已修改但未提交的数据叫做赃数据 表中受影响的行被锁定，其它用户（事务）不能在受影响的行上修改数据

7.COMMIT或ROLLBACK语句之后数据的状态 COMMIT之后： 数据改变被写到数据库中 所有用户（事务）可以查看事务的结果 表中受影响行上的锁被释放，这些行现在可以被其它用户（事务）修改 事务中所有的保存点被删除 ROLLBACK之后： 数据改变被撤销 数据先前的状态被恢复 表中受影响行上的锁被释放

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/179183.html原文链接：https://javaforall.cn