MySQL学习——数据操作与事务控制

一.数据操作语言

1.主要用来实现对数据库表中的数据进行操作。
2.数据操作语言主要包括如下几种：
增加行数据：使用INSERT语句实现
修改行数据：使用UPDATE语句实现
删除行数据：使用DELETE语句实现

进入数据库：

• use 数据库名称；

  
  示例

1.插入数据

• 使用INSERT语句向表中插入数据
  INSERT INTO table [(column [, column...])]
  VALUES (value [, value...]);

1.采用这种语法一次只能追加一条记录；
2.column部分叫做列名列表，value部分叫做值列表，列名列表和值列表必须在个数及数据类型上保持一致；
3.列名列表部分可以省略，如果省略，默认包括该表的所有列，列的顺序为使用 desc 表名 命令所查看的顺序；
4.列名列表部分也可以指定部分非空的列，注意值列表必须和列名列表对应；
5.字符和日期型数据必须要用单引号括起来。
例子：

示例

• 插入空值NULL
  1.隐含法: 在列名列表中忽略该列

  
  示例
  

  2.显示法: 指定 NULL关键字或者''

  
  示例

• 插入日期值
  SYSDATE() 函数记录当前日期和时间

  
  示例
  示例1
• 批量插入数据

使用insert语句可以一次性地向表批量插入多条记录

INSERT INTO 表名[(字段列表)] VALUES
(值列表1),
(值列表2),
…
(值列表n);
例子：
INSERT INTO EMP(EMPNO, ENAME, JOB, SAL) VALUES
('8881', '张三', '部门经理', 6000),
('8882', '李四', '职员', 3000),
('8883', '王五', '职员', 3500),
('8884' ,'赵六', '部门经理', 6500),
('8885', '高七', '职员', 2500),
('8886', '马八', '职员', 3100),
('8887', '钱九', '部门经理', 5000),
('8888', '孙十', '职员', 2800);

• 通过子查询插入多行数据
  INSERT INTO 表名[(列名1[,列名2，…，列名n])] 子查询 ；

ps:不必书写values子句
INSERT子句和数据类型必须和子查询中列的数量和类型相匹配中列的数量

• 例子：创建manager表

  
  创建表
  

  向manager表中插入职位为MANAGER的记录

  
  插入

3.修改数据

修改数据主要用来按照指定条件修改表中某些行的列数据。
修改数据使用UPDATE子句完成：
UPDATE table
SET column = value [, column = value]
[WHERE condition];

1.WHERE子句用来限定修改哪些行。
2.SET子句用来限定修改哪些列。
3.WHERE子句中的更新条件是一个逻辑表达式，通常需要使用到关系运算符和逻辑运算符，返回True或者False。

功能
关系运算符

• 使用 WHERE 子句指定要修改的记录
  把员工编号为7782的部门编号修改为20

  
  修改

• 如果要修改所有记录，WHERE子句可以忽略
  把所有员工的部门编号修改为20

  
  修改

• 一次修改多列
  把部门编号为10的员工，部门编号调整为20，工资增加100

  
  修改多列

4.删除数据

删除数据主要用来按照指定条件从表中删除某些行

• 使用 DELETE 语句删除表中满足条件的行记录
  DELETE FROM table
  [WHERE condition];
• 删除选中记录
  删除职位是CLERK的员工记录
  DELETE FROM emp
  WHERE job = 'CLERK';
• 删除全部记录
  删除所有员工记录
  DELETE FROM emp;
• 截断表
  截断表语法：
  TRUNCATE TABLE table;
  TRUNCATE TABLE emp;
• TRUNCATE和DELETE区别
  TRUNCATE是DDL，只能删除表中所有记录，释放存储空间，使用ROLLBACK不可以回滚。
  DELETE是DML，可以删除指定记录，不释放存储空间，使用ROLLBACK可以回滚。

二.事务处理

• 事务(Transaction)概念
• 事务：也称工作单元，是由一个或多个SQL语句所组成的操作序列，这些SQL语句作为一个完整的工作单元，要么全部执行成功，要么全部执行失败。在数据库中，通过事务来保证数据的一致性。
• 事务处理语言：Transaction Process Language ,简称TPL，主要用来对组成事务的DML语句的操作结果进行确认或取消。确认也就是使DML操作生效，使用提交(COMMIT)命令实现；取消也就是使DML操作失效，使用回滚(ROLLBACK)命令实现。
• 通过事务的使用，能防止数据库中出现数据不一致现象。如两个银行账户进行转账，涉及到两条更新操作，这两条更新操作只允许全部成功或失败，否则数据会出现不一致的现象。
• 事务组成
  在数据库中，事务由一组相关的DML或SELECT语句，加上一个TPL语句（COMMIT、ROLLBACK）或一个DDL语句(CREATE、ALTER、DROP、TRUNCATE等)或一个DCL（GRANT、REVOKE）语句。
  例：如下语句组成两个事务。
  INSERT….
  UPDATE….
  DELETE….
  SELECT….
  INSERT…
  COMMIT;-- 前6条语句，组成第1个事务
  UPDATE…
  DELETE….
  CREATE… ;--后3条语句，组成第2个事务

1.事务特性

事务特征可用四个字母的缩写表示：即ACID

• 原子性（Atomicity）
  事务就像“原子”一样，不可被分割，组成事务的DML操作语句要么全成功，要么全失败，不可能出现部分成功部分失败的情况。
• 一致性（Consistency）
  一旦事务完成，不管是成功的，还是失败的，整个系统处于数据一致的状态。
• 隔离性（Isolation）
  一个事务的执行不会被另一个事务所干扰。比如两个人同时从一个账户从取钱，通过事务的隔离性确保账户余额的正确性。
• 持久性（Durability）
  也称为永久性，指事务一旦提交，对数据的改变就是永久的，不可以再被回滚。

2.处理事务的方法

MySQL的事务处理主要有两种方法
1.用begin,rollback,commit来实现
begin开始一个事务
rollback事务回滚
commit 事务提交
2.直接用set来改变MySQL的自动提交模式 MySQL默认是自动提交的，也就是你提交一个sql，就直接执行！
可以通过
set autocommit = 0 禁止自动提交
set autocommit = 1 开启自动提交
来实现事务的处理。
但要注意当用set autocommit = 0 的时候，以后所有的sql都将作为事务处理，直到用commit确认或 rollback结束，注意当结束这个事务的同时也开启了新的事务！按第一种方法只将当前的做为一个事务!

3.隐式结束

• 隐式提交：当下列任意一种情况发生时，会发生隐式提交
  1.执行一个DDL(CREATE、ALTER、DROP、TRUNCATE、RENAME）语句；
  2.执行一个DCL(GRANT、REVOKE)语句；

• 隐式回滚：当下列任意一种情况发生时，会发生隐式回滚
  1.客户端强行退出
  2.客户端连接到服务器端异常中断
  3.系统崩溃

• 设置保存点：
  如果在一个事务内，想要回滚到指定位置，不是回滚到事务的起始点，可以通过保存点(SAVEPOINT)来实现。
  SAVEPOINT savepointname；--定义一个保存点语句；
  ROLLBACK TO savepointname;--回滚到指定保存点
  注意：如上两条语句不结束事务的执行。

• 开始事务操作：
  BEGIN；

• 撤销DELETE操作：
  DELETE FROM test;
  ROLLBACK;

• 定义insert_a保存点：
  INSERT INTO TEST VALUES('A');
  SAVEPOINT INSERT_a;

• 定义insert_b保存点：
  INSERT INTO TEST VALUES('B');
  SAVEPOINT INSERT_b;

• 撤销操作到insert_b保存点：
  INSERT INTO TEST VALUES('C');
  ROLLBACK TO INSERT_b;
  DELETE FROM test WHERE test_str='A';

• 将所有修改写入数据库
  COMMIT;

• 所有操作已经COMMIT提交，不能回滚
  ROLLBACK;
  （此文章仅作为个人学习笔记使用，如有错误欢迎指正~）