数据库事务

数据库事务是保证数据操作完整性的基础

客户端向数据表中写数据的时候，需要先向事务日志中写入这些操作
只有当事务日志中的写入操作都成功的时候，才会一次性commit到数据表中
一旦事务日志中有一个写入操作发生错误，那么之前的事务日志也会被清除和回滚

也就是说，向数据表中写入数据的时候，要么都成功，要么什么都不写入。
从而保证了数据的完整性。

存储引擎之MyISAM

mysql5.5之前的默认存储引擎

1，默认MyISAM的表会在磁盘中产生3个文件

.frm：表结构文件
.MYD：数据文件
.MYI：索引文件

2，可以在创建表的时候指定数据文件和索引文件的位置，只有MyISAM表支持
DATA DIRECTORY [=] 数据保存的绝对路径
DATA DIRECTORY [=] 索引文件的保存位置

3，MyISAM单表最大支持的数据量是2的64次方条记录

4，每个表最多可以建立64个索引

5，如果是复合索引，每个复合索引最多包含16列，索引值的最大长度是1000B

6，MyISAM引擎的存储格式

定长（FIXED 静态）：是指字段中不包含VARCHAR/TEXT/BLOB
动态（DYNAMIC）：只要字段中包含了VARCHAR/TEXT/BLOB
压缩（COMPRESSED）：myisampack创建

--测试MyISAM存储引擎
--静态以空间换时间（注重时间）
--动态以时间换空间（注重空间）
--静态存储(fixed)
CREATE TABLE test_myisam(
  a INT UNSIGNED,
  b CHAR(30)
)ENGINE=MyISAM;
--动态存储(dynamic)
CREATE TABLE test_myisam2(
  a INT UNSIGNED,
  b VARCHAR(30)
)ENGINE=MyISAM;
--使用ROW_FORMAT命令以静态方式存储数据
CREATE TABLE test_myisam3(
  a INT UNSIGNED,
  b VARCHAR(30)
)ENGINE=MyISAM ROW_FORMAT=FIXED;
--测试INNODB存储引擎
--默认
CREATE TABLE test_innodb(
  id INT UNSIGNED AUTO_INCREMENT KEY,
  username VARCHAR(20) NOT NULL
);

存储引擎之INNODB

mysql5.5之后的默认存储引擎

1，设计遵循ACID模型，支持事务，具有从服务器崩溃中恢复的能力，能够最大限度保护用户的数据

原子性（Atomiocity）
一致性（Consistency）
隔离性（Isolation）
持久性（Durability）

2，支持行级锁，可以提升多用户并发时的读写能力

3，支持外键，保证数据的一致性和完整性

4，InnoDB拥有自己独立的缓存池，常用的数据和索引都在缓存中

5，对于INSERT，UPDATE，DELETE操作，InnoDB会使用一种change buffer的机制来优化，缓存变更的数据，减少自盘I/O，提高性能

6，创建InnoDB表之后会产生2个文件
.frm：表结构文件
.idb：数据和索引存储表空间中

7，所有的表都需要创建主键，最后是配合AUTO_INCREMENT，也可以设置经常查询的列作为主键

添加记录

INSERT [INTO] tbl_name[(col_name, …)] {VALUE|VALUES}(VALUES…);

--测试添加记录
CREATE DATABASE IF NOT EXISTS song DEFAULT CHARACTER SET 'UTF8';
USE song;
CREATE TABLE IF NOT EXISTS user(
  id INT UNSIGNED AUTO_INCREMENT KEY COMMENT '编号',
  username VARCHAR(20) NOT NULL UNIQUE COMMENT '用户名',
  age TINYINT UNSIGNED DEFAULT 18 COMMENT '年龄',
  email VARCHAR(50) NOT NULL DEFAULT 'imooc@qq.com' COMMENT '邮箱'
)ENGINE=INNODB CHARSET=UTF8;


--不指定字段名称
--VALUE 和 VALUES是一样的
INSERT user VALUE(1,'song',30,'1234@qq.com');
INSERT user VALUE(NULL,'queen',25,'queen@qq.com');        // 对于主键，可以插入NULL
INSERT user VALUES(DEFAULT,'lili',27,'lili@qq.com');    // 对于主键，可以插入DEFAULT

--指定字段名称
INSERT user(username,email) VALUES('rose','rose@qq.com');
INSERT user(email,username) VALUES('nian@qq.com','nian');

--INSERT ... SET
INSERT user SET username = 'd',age=45,email='d@qq.com';

--一次插入多条记录
INSERT user
VALUES(NULL,'a',DEFAULT,DEFAULT),(NULL,'b',56,'b@qq.com'),(NULL,'C',14,'c@qq.com');

--INSERT ... SELECT
CREATE TABLE test(
  a VARCHAR(20)
);
INSERT test VALUES('aa'),('BB'),('CC');
INSERT user(username) SELECT a FROM test;--插入三条username记录

删除记录

DELETE FROM tbl_name [WHERE 条件]
#如果不添加条件，表中所有数据都会被删除
#DELETE清空数据表的时候不会重置AUTO_INCREMENT的值，可以通过ALTER语句将其重置为1

--测试删除语句
--删除用户名为song
DELETE FROM user WHERE username='song';

--删除表中所有元素
DELETE FROM user;

--如果不重置AUTO_INCREMENT的值，添加记录后id从删除之前的id开始，而不是从0开始
INSERT user VALUE(NULL,'queen',25,'queen@qq.com');
INSERT user VALUES(DEFAULT,'lili',27,'lili@qq.com');

--重置AUTO_INCREMENT
ALTER TABLE user AUTO_INCREMENT=1;
--这时候再插入的话就是从1开始了
INSERT user VALUE(NULL,'queen',25,'queen@qq.com');
INSERT user VALUES(DEFAULT,'lili',27,'lili@qq.com');

--彻底清空表
TRUNCATE user;

修改记录

update tbl_name SET 字段名称=值，字段名称=值 [WHERE 条件]

--测试更新语句

--修改第一个用户的信息 id=1
UPDATE user SET age=29 WHERE id=1;

--修改id=3的用户信息
UPDATE user SET username='lucy',age=10,email='lucy@qq.com' WHERE id=3;

--所有用户年龄加10
UPDATE user SET age=age + 10;

--将id<=5的用户年龄减20，将邮箱改为默认值
UPDATE user SET age=age-20,email=DEFAULT WHERE id<=5;

复制记录

insert into db1.table1 select * from db2.table2 (完全复制)
insert into db1.table1 select distinct * from db2.table2(不复制重复纪录）
insert into tdb1.able1 select top 5 * from db2.table2 (前五条纪录)

【后端】mysql

记录操作