MYSQL数据库

MYSQL数据库

第四章 DML和事务处理

MySQL的存储引擎

存储引擎的类型

MyISAM、InnoDB 、Memory、CSV等9种

MyISAM与InnoDB类型主要区别

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

查看当前默认存储引擎

SHOW VARIABLES LIKE ‘storage_engine%’；

修改存储引擎

修改my.ini配置文件

default-storage-engine= InnoDB

设置表的存储引擎

CREATE TABLE 表名( #省略代码)ENGINE=存储引擎;

示例： CREATE TABLE myisam ( id INT(4))ENGINE=MyISAM;

数据表的存储位置

MyISAM类型表文件：*.frm：表结构定义文件 .MYD：数据文件. MYI：索引文件

InnoDB类型表文件：*.frm：表结构定义文件 ibdata1文件

存储位置因操作系统而异，可查my.ini

DML语句——插入单条数据记录

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

注意

字段名是可选的，如省略则依次插入所有字段

多个列表和多个值之间使用逗号分隔

值列表和字段名列表一一对应

如插入的是表中部分数据，字段名列表必填

示例： INSERT INTO student(loginPwd,studentName,gradeId,phone,bornDate)VALUES(‘123’,‘黄小平’,1,‘13956799999’,‘1996-5-8’);

DML语句——插入多条数据记录

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

示例： INSERT INTO subject(subjectName,classHour,gradeID)VALUES(‘Logic Java’,220,1),(‘HTML’,160,1),(‘Java OOP’,230,2);

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

DML语句——将查询结果插入新表

示例： CREATE TABLE phoneList( SELECT studentName,phone FROM student);

如新表已存在，将会报错！

将查询结果插入新表

CREATE TABLE 新表(SELECT 字段1，字段2…… FROM 原表)；

如新表已存在，不能重复创建

编写SQL语句实现从学生表提取姓名、手机号两列数据存储到通讯录表中

CREATE TABLE phoneList(SELECT studentName,phoneFROM student);

数据更新

更新数据记录

UPDATE 表名 SET 字段1=值1,字段2=值2,…,字段n=值n [WHERE 条件];

示例： UPDATE student SET sex = ‘女’;UPDATE student SET address = ‘北京女子职业技术学校家政班’WHERE address = ‘北京女子职业技术学校刺绣班’;

数据删除

删除数据记录

DELETE FROM 表名 [WHERE条件];

TRUNCATE TABLE 表名;

TRUNCATE语句删除后将重置自增列，表结构及其字段、约束、索引保持不变，执行速度比DELETE语句快

示例： DELETE FROM student WHERE studentName = ‘王宝宝’;TRUNCATE TABLE student;

事务

什么是事务

事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作

多个操作作为一个整体向系统提交，要么都执行、要么都不执行

事务是一个不可分割的工作逻辑单元

事务的特性

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

1.原子性（Atomicity）

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

2.一致性（Consistency）

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

3.隔离性（Isolation）

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

4.持久性（Durability）

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

如何创建事务

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

开始事务 BEGIN ;或START TRANSACTION;

提交事务 COMMIT ;

回滚（撤销）事务 ROLLBACK ;

自动关闭和开启事务

默认情况下，每条单独的SQL语句视为一个事务

关闭默认提交状态后，可手动开启、关闭事务

关闭/开启自动提交状态

SET autocommit = 0|1;

值为0：关闭自动提交

值为1：开启自动提交

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

演示示例4：关闭自动提交

SET autocommit=0; 关闭自动 提交，以下视为一个事务

/–转账:张三的账户减少500元,李四的账户增加500元–/

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; 开启自动 提交，恢复默认状态