开始时间：2021-03-13
存储引擎
建表的时候可以指定存储引擎,也可以指定字符集。
mysql默认使用的存储引擎是InnoDB方式。
默认采用的字符集是UTF-8
mysql支持很多存储引擎，每一个存储引擎都对应了一种不同的存储方式。
每一个存储引擎都有自己的优缺点，需要在合适的时机选择合适的存储引擎。
查看当前mysql支持的存储引擎
mysql> show engines\g
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
MyISAM
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------+--------------+------+------------+
| MyISAM | YES | MyISAM storage engine| NO | NO | NO |
MyISAM这种存储引擎不支持事务。
MyISAM是mysql最常用的存储引擎，但是这种引擎不是默认的。
MyIsAM采用三个文件组织一张表:
- xxx.frm(存储格式的文件)
- xxx.MYD(存储表中数据的文件)
- xxx.MYI （存储表中索引的文件)
优点:可被压缩，节省存储空间。并且可以转换为只读表，提高检索效率。
缺点:不支持事务。
innoDB
支持事务，能保证数据安全
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
存储在tablespace的表空间（逻辑概念），无法被压缩，无法转换成只读。
这种InnoDB存储引擎在Mysgz数据库崩溃之后提供自动恢复机制。
支持级联删除和级联更新，父表更新，子表对应字段也会更新
memory
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
不支持事务，数据索引放到内存中，容易丢失
优点就是查询速度最快
练习题1
求出每个部门最高薪资的人员名称
读题的时候我觉得很简单，直接向下面这样写就行
mysql> select ename,deptno,max(sal) from emp group by deptno;
+-------+--------+----------+
| ename | deptno | max(sal) |
+-------+--------+----------+
| CLARK | 10 | 5000.00 |
| SMITH | 20 | 3000.00 |
| ALLEN | 30 | 2850.00 |
+-------+--------+----------+
但实际上这样写不对，因为有些薪资可能一个值对应多个人员
mysql> select t.*,e.* from emp t join (select deptno,max(sal) as maxsal from emp group by deptno) e on e.deptno=t.deptno and e.maxsal=t.sal;
//第一步找出所有的部门以及薪资，第二步再去各部门匹配对应的部门和薪资，找出人员名单
+-------+-------+-----------+------+------------+---------+------+--------+--------+---------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | deptno | maxsal |
+-------+-------+-----------+------+------------+---------+------+--------+--------+---------+
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | 30 | 2850.00 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | 20 | 3000.00 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | 10 | 5000.00 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | 20 | 3000.00 |
+-------+-------+-----------+------+------------+---------+------+--------+--------+---------+
事务 transaction(TCL)
一个事务是一个完整的业务逻辑单元
比如:银行账户转账，从A账户向B账户转账10000.需要执行两条update语句:
update t_act set balance = balance - 10000 where actno = 'act-001';
update t _act set balance = balance + 10000 where actno = 'act-002 ';
以上两条DML语句必须同时成功，或者同时失败，不允许出现一条成功，一条失败。
要想保证以上的两条DML语句同时成功或者同时失败，那么就需要使用数据库的"事务机制"。
和事务相关的语句只有DML语句（insert delete update）
这三个都和数据相关，和缓存相关，事务的存在保证了数据的完整性和安全性
这里我嗅到了一丝线程安全的味道
开启事务->insert->update->delete->提交事务/回滚（类似于游戏存档）事务
中间三个操作是记录到缓存中，而不会真正修改数据
提交commit/回滚后rollback中间缓存就清空了
事务的四大特性ACID
- A(atomicity):原子性,或称不可分割性事务是最小的工作单元，不可再分。
- C:一致性(consistency):事务必须保证多条DM语句同时成功或者同时失败。
- I:隔离性(isolation):事务A与事务B之间具有隔离。（类似于线程要分开，保证线程安全）
- D:持久性(durability):持久性说的是最终数据必须持久化到硬盘文件中，事务才算成功的结束。（summit/rollback）
事务隔离级别
第一级别：读未提交(read uncommitted)
对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。读未提交存在脏读(Dirty Read)现象
这些数据都没有保存到硬盘中
Oracle一般是第二级别
第二级别:读已提交(read committed)
对方事务提交之后的数据我方可以读取到。这种隔离级别解决了:脏读现象没有了。
读己提交存在的问题是:不可重复读。比如说微信共享文档，A修改完，B读这个文档就读的A修改完后的结果，读不到A修改前的结果了。不可重复就是说A修改前后两次读取结果不一样，结果不相同。
MySQL一般是第三级别
第三级别:可重复读(repeatable read)
这种隔离级别解决了:不可重复读问题。这种隔离级别存在的问题是:读取到的数据是幻象。
因为别人已经修改了，但是读出来的还是没修改前的东西
第四级别:序列化读/串行化读
解决了所有问题。
效率低。需要事务排队。
演示
mysql> create table t_user(name varchar(255),id int primary key auto_increment);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t_user(name)values('zs');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t_user;
+------+----+
| name | id |
+------+----+
| zs | 1 |
+------+----+
1 row in set (0.01 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t_user;
+------+----+
| name | id |
+------+----+
| zs | 1 |
+------+----+
从上面的例子可以看出，一旦执行了DML语句，那么MySQL中的事务就自动提交了，因为回滚回不去，看到的还是提交后的数据。
演示使用 start transaction关闭自动提交机制
mysql> create table t_user( id int primary key auto_increment,username varchar(255));
mysql> insert into t_user(username)values('lisi');
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | lisi |
+----+----------+
// 开始事务，关闭自动提交机制
mysql> start transaction;
mysql> insert into t_user(username)values('ww');
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | lisi |
| 2 | ww |
+----+----------+
mysql> insert into t_user(username)values('zs');
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | lisi |
| 2 | ww |
| 3 | zs |
+----+----------+
//此时溯回是溯回到刚才的start之前，相当于读档
mysql> rollback;
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | lisi |
+----+----------+
1 row in set (0.00 sec)
但 commit之后就撤不回了
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | lisi |
+----+----------+
mysql> insert into t_user(username)values('zhangsan');
mysql> insert into t_user(username)values('zhangsa');
mysql> commit;
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | lisi |
| 4 | zhangsan |
| 5 | zhangsa |
+----+----------+
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | lisi |
| 4 | zhangsan |
| 5 | zhangsa |
+----+----------+
演示读未提交(read uncommitted)
mysql> set global transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)
//查看事务全局隔离级别
mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-UNCOMMITTED |
+-----------------------+
表1：
mysql> use mysql;
Database changed
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | lisi |
| 4 | zhangsan |
| 5 | zhangsa |
+----+----------+
表2：
mysql> start transaction;
mysql> insert into t_user(username)values('ww');
此时表2没有commit，但是我们在表1中查询
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | lisi |
| 4 | zhangsan |
| 5 | zhangsa |
| 6 | ww |
+----+----------+
已经有结果了
演示读已提交(read committed)
mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-COMMITTED |
+-----------------------+
表1：
C:\Users\Administrator>mysql -uroot -p333
mysql> use mysql;
Database changed
mysql> set global transaction isolation level read committed;
mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-COMMITTED |
+-----------------------+
mysql> exit
Bye
mysql> use mysql;
Database changed
//要开事务
mysql> start transaction;
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | lisi |
| 4 | zhangsan |
| 5 | zhangsa |
+----+----------+
//表2 commit之前
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | lisi |
| 4 | zhangsan |
| 5 | zhangsa |
+----+----------+
//表2 commit之后
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | lisi |
| 4 | zhangsan |
| 5 | zhangsa |
| 7 | ww |
+----+----------+
演示可重复读
表1：
无论表2怎么动，都动不了表1
C:\Users\Administrator>mysql -uroot -p333
mysql> use mysql;
Database changed
mysql> set global transaction isolation level repeatable read;
mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ |
+-----------------------+
mysql> use mysql;
Database changed
mysql> start transaction;
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | lisi |
| 4 | zhangsan |
| 5 | zhangsa |
| 7 | ww |
+----+----------+
4 rows in set (0.00 sec)
//表2insert后
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | lisi |
| 4 | zhangsan |
| 5 | zhangsa |
| 7 | ww |
+----+----------+
//表2commit后
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | lisi |
| 4 | zhangsan |
| 5 | zhangsa |
| 7 | ww |
+----+----------+
表2：
C:\Users\Administrator>mysql -uroot -p333
mysql> use mysql;
Database changed
mysql> start transaction;
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | lisi |
| 4 | zhangsan |
| 5 | zhangsa |
| 7 | ww |
+----+----------+
4 rows in set (0.00 sec)
mysql> insert into t_user(username)values('www');
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | lisi |
| 4 | zhangsan |
| 5 | zhangsa |
| 7 | ww |
| 8 | www |
+----+----------+
mysql> commit;
演示串行化读
表1：
C:\Users\Administrator>mysql -uroot -p333
mysql> select @@global.tx_isolation ;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| SERIALIZABLE |
+-----------------------+
1 row in set (0.00 sec)
mysql> use mysql;
Database changed
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | lisi |
| 4 | zhangsan |
| 5 | zhangsa |
| 7 | ww |
| 8 | www |
+----+----------+
5 rows in set (0.00 sec)
mysql> start transaction;
mysql> insert into t_user(username) values('ll');
Query OK, 1 row affected (0.00 sec)
//commit键入之前，表2一直处于等待状态，键入之后，表2随之自动更新，弹出结果
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
表2：
C:\Users\Administrator>mysql -uroot -p333
mysql> use mysql;
Database changed
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | lisi |
| 4 | zhangsan |
| 5 | zhangsa |
| 7 | ww |
| 8 | www |
+----+----------+
5 rows in set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
//表1commit键入之前，表2一直处于等待状态，键入之后，表2随之自动更新，弹出结果，所以弹出时间花了11秒
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | lisi |
| 4 | zhangsan |
| 5 | zhangsa |
| 7 | ww |
| 8 | www |
| 11 | ll |
+----+----------+
6 rows in set (11.29 sec)
结束时间：2021-03-18