第四章 DML和事务处理

第四章 DML和事务处理 一,MySQL的存储引擎

存储引擎的类型
MyISAM、InnoDB 、Memory、CSV等9种

MyISAM与InnoDB类型主要区别

适用场合
使用MyISAM: 不需事务，空间小，以查询访问为主
使用InnoDB: 多删除、更新操作，安全性高，事务处理及并发控制

查看当前默认存储引擎

-- 查询存储引擎
SHOW VARIABLES LIKE 'default_storage_engine%';

临时修改存储引擎

-- 临时修改存储引擎
SET default_storage_engine ='MyISAM';

永久修改存储引擎

修改my.ini配置文件

# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

设置表的存储引擎

CREATE TABLE `myisam` (
   id  INT(4)
)ENGINE=MyISAM;

数据表的存储位置

二,DML语句 插入单条数据记录

INSERT INTO 表名 [(字段名列表)] VALUES (值列表);

-- INSERT INTO 表名 [(字段名列表)] VALUES (值列表);

-- 字段列表可以省略,但是要注意字段顺序,必须添加所有字段
INSERT INTO menus VALUES(12,'特国盾',2,'狗肉','好吃不贵',12,0);
-- 不省略字段列表,可以只添加部分字段
INSERT INTO menus(id,NAME,typeid,burden,brief)
 VALUES(13,'特国盾',2,'狗肉','好吃不贵');
-- 省略字段时,如果主键没有自增,必须添加主键

 -- 当主键自增时,可以使用null,或者default代替主键的值,也可以不添加主键
 INSERT INTO menus(id,NAME,typeid,burden,brief)
 VALUES(NULL,'特国盾',2,'狗肉','好吃不贵');
  INSERT INTO menus(id,NAME,typeid,burden,brief)
 VALUES(DEFAULT,'特国盾',2,'狗肉','好吃不贵');
  INSERT INTO menus(NAME,typeid,burden,brief)
 VALUES('特国盾',2,'狗肉','好吃不贵');

字段名是可选的，如省略则依次插入所有字段
多个列表和多个值之间使用逗号分隔
值列表和字段名列表一一对应
如插入的是表中部分数据，字段名列表必填

插入多条数据记录

INSERT INTO 新表（字段名列表）
 VALUES(值列表1),(值列表2),……,(值列表n);

-- 插入多条
 INSERT INTO TYPES(id,NAME) VALUES(NULL,'免费试吃'),
 (NULL,'八折优惠'),(DEFAULT,'五折优惠');

为避免表结构发生变化引发的错误，建议插入数据时写明具体字段名！

数据更新

update 表名 set 字段1=n,字段2=n2,.... where 条件

-- 修改 特国盾  为 铁锅炖土豆
# 尽量使用主键作为条件,或者有唯一约束的字段作为条件
UPDATE menus SET NAME='铁锅炖土豆' WHERe NAME='特国盾';
# 修改Id 为17 的name值
UPDATE menus SET NAME='铁锅炖王八' WHERe id=17;
# 修改 市场价格为12 会员价格为10的菜品的材料为  面粉
UPDATE menus SET burden='面粉' WHERe price=12 AND price1=10;

删除数据记录

DELETE FROM 表名 [WHERe条件];

# DELETe FROM 表名 [WHERe条件];
--  一定要加上where条件,否则会把所有数据都删除
DELETe FROM TYPES WHERe id=7;
-- 无法删除已经被当做外键使用的数据,除非先删除使用数据的子表的数据
DELETe FROM TYPES WHERe id=1;
#TRUNCATE TABLE 表名; 清空数据,同时也会情况自增,再次从头开始
TRUNCATE TABLE users;
#TRUNCATE语句删除后将重置自增列，表结构及其字段、约束、索引保持不变，执行速度比DELETE语句快

三,事务管理

什么是事务

事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作
多个操作作为一个整体向系统提交，要么都执行、要么都不执行
事务是一个不可分割的工作逻辑单元

转账过程就是一个整体
它需要两条UPDATE语句来完成，这两条语句是一个整体
如果其中任一条出现错误，则整个转账业务也应取消，两个账户中的余额应恢复到原来的数据，从而确保转账前和转账后的余额不变，即都是1001元

事务的特性

事务必须具备以下四个属性，简称ACID 属性

原子性（Atomicity）

事务是一个完整的操作，事务的各步操作是不可分的(原子的)，要么都执行，要么都不执行

一致性（Consistency）

当事务完成时，数据必须处于一致状态

隔离性（Isolation）

并发事务之间彼此隔离、独立，它不应以任何方式依赖于或影响其他事务

持久性（Durability）

事务完成后,它对数据库的修改被永久保持

如何创建事务

开始:
BEGIN ;
或
START TRANSACTION;

提交事务 :COMMIT ;
回滚事务:ROLLBACK ;

#MySQL中支持事务的存储引擎有InnoDB和BDB

-- 模拟转账 使用事务  没有异常
BEGIN;
#张三钱减少500
UPDATE bank SET currentMoney=currentMoney-500 WHERe customerName='张三';
#李四 +500
UPDATE bank SET currentMoney=currentMoney+500 WHERe customerName='李四';
COMMIT; #提交
END;
-- 模拟转账 使用事务  出现异常 余额不能小于0
BEGIN;
#张三钱减少500
UPDATE bank SET currentMoney=currentMoney-500 WHERe customerName='张三';
#李四 +500
UPDATE bank SET currentMoney=currentMoney+500 WHERe customerName='李四';
ROLLBACK;#回滚

自动关闭和开启事务

默认情况下，每条单独的SQL语句视为一个事务
关闭默认提交状态后，可手动开启、关闭事务

关闭/开启自动提交状态

SET autocommit = 0|1;

值为0：关闭自动提交
值为1：开启自动提交

关闭自动提交后，从下一条SQL语句开始则开启新事务，需使用COMMIT或ROLLBACK语句结束该事务

SET autocommit=0;

UPDATE `bank` SET `currentMoney`=`currentMoney`-500 
     WHERe `customerName`='张三';
UPDATE `bank` SET `currentMoney`=`currentMoney`+500 
     WHERe `customerName`='李四';
COMMIT;
UPDATE `bank` SET `currentMoney`=`currentMoney`-1000 WHERe `customerName`='张三';
ROLLBACK;
SET autocommit = 1;