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

MySQL中DML语句和事务的概念「建议收藏」ML语句知识要点DML语句插入行到表中删除表中的行更新表中的行控制事务DML语句DML:DATAMANIPULATIONLANGUAGE(数据操纵语言),由INSERT、UPDATE、DELETE等语句构成,用来修改表中的数据INSERT语句1.带VALUES子句的INSERT语句INSERT[INTO]tbl_name[(col_name,…)]{VA…

大家好,又见面了,我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。

Jetbrains全系列IDE使用 1年只要46元 售后保障 童叟无欺

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之后:
数据改变被撤销
数据先前的状态被恢复
表中受影响行上的锁被释放

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/179183.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号