2021-07-26MySQL03笔记

最新推荐文章于 2024-07-19 03:51:13 发布

dasd11sda456

最新推荐文章于 2024-07-19 03:51:13 发布

阅读量91

点赞数 1

本文链接：https://blog.csdn.net/dasd11sda456/article/details/119116260

版权

MySQL-03

3、事务（Transaction）

3.1、什么是事务？

一个事务是一个完整的业务逻辑单元，不可再分。

比如：银行账户转账，从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语句同时成功或者同时失败，那么就需要使用数据库的“事务机制”。

3.2、和事务相关的语句只有：DML语句。（insert delete update）

为什么？因为它们这三个语句都是和数据库表当中的“数据”相关的。

事务的存在是为了保证数据的完整性，安全性。

3.3、假设所有的业务都能使用1条DML语句搞定，还需要事务机制吗？

不需要事务。

但实际情况不是这样的，通常一个“事儿（事务【业务】）”需要多条DML语句共同联合完成。

3.4、事务的特性？

事务包括四大特性：ACID

A：原子性：事务是最小的工作单元，不可再分。

C：移植性：事务必须保证多条DML语句同时成功或者同时失败。

I：隔离性：事务A与事务B之间具有隔离。

D：持久性：持久性说的是最终数据必须持久化到硬盘文件中，事务才算成功的事务。

3.5、关于事务之间的隔离性

事务隔离性存在隔离级别，理论上隔离级别包括4个：

第一级别：读未提交（read uncommitted）

对方事务还没有提交，我们当前的事务可以读取到对方未提交的数据。

读未提交存在脏读（Dirty Read）现象：表示读到了脏的数据。

第二级别：读已提交（read committed）

对方事务提交之后的数据我方可以读取到。

这种隔离级别解决了：脏读现象没有了。

读已提交存在的问题是：不可重复读。

第三级别：可重复读（repeatable read）

这种隔离级别解决了：不可重复问题。

这种隔离级别存在的问题是：读取到的数据都是幻象。

第四级别：序列化读/串行化读（serializable）

解决了所有问题。

效率低，需要事务排队。

oracle数据库默认的隔离级别是：读已提交。

mysql数据库默认的隔离级别是：可重复读。

3.6、演示事务

mysql事务默认情况下是自动提交的。

（什么是自动提交？只要执行任意一条DML语句则提交一次。）怎么关闭自动提交？start transaction;

准备表：

drop table if exists t_user;

create table t_user( id int primary key auto_increment, username varchar(255));
演示：mysql中的事务是支持自动提交的，只要执行一条DML，则提交一次。

insert into t_user (username) values (‘zs’);

mysql> select * from t_user;
±—±---------+
| id | username |
±—±---------+
| 1 | zs |
±—±---------+
1 row in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_user;
±—±---------+
| id | username |
±—±---------+
| 1 | zs |
±—±---------+
1 row in set (0.00 sec)

演示：使用start transaction;关闭自动提交机制。

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_user(username) values (‘ls’);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_user;
±—±---------+
| id | username |
±—±---------+
| 1 | zs |
| 2 | ls |
±—±---------+
2 rows in set (0.00 sec)

mysql> insert into t_user(username) values (‘ww’);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_user;
±—±---------+
| id | username |
±—±---------+
| 1 | zs |
| 2 | ls |
| 3 | ww |
±—±---------+
3 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t_user;
±—±---------+
| id | username |
±—±---------+
| 1 | zs |
±—±---------+
1 row in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_user(username) values (‘john’);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_user(username) values (‘jack’);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_user(username) values (‘rose’);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_user;
±—±---------+
| id | username |
±—±---------+
| 1 | zs |
| 4 | john |
| 5 | jack |
| 6 | rose |
±—±---------+
4 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t_user;
±—±---------+
| id | username |
±—±---------+
| 1 | zs |
| 4 | john |
| 5 | jack |
| 6 | rose |
±—±---------+
4 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_user;
±—±---------+
| id | username |
±—±---------+
| 1 | zs |
| 4 | john |
| 5 | jack |
| 6 | rose |
±—±---------+
4 rows in set (0.00 sec)

演示两个事务，假如隔离级别

演示第1级别：读未提交

set global transaction isolation level read uncommitted;

演示第2级别：读已提交

set global transaction isolation level read committed;

演示第3级别：可重复读

set global transaction isolation level repeatable read;
mysql远程登录：mysql -h192.168.151.18 -uroot -p444

4、索引

4.1、什么是索引？有什么用？

索引就相当于一本书的目录，通过目录可以快速的找到对应的资源。

在数据库方面，查询一张表的时候有两种检索方式：

第一种方式：全表扫描

第二种方式：根据索引检索（效率很高）

索引为什么可以提高检索效率呢？

其实最根本的原理是缩小了扫描的范围。

索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库当中的对象，也需要数据库不断的维护。是有维护成本的。比如，表中的数据经常被修改这样就不适合添加索引，因为数据一旦修改，索引需要重新排序，进行维护。

添加索引是给某一个字段，或者说某些字段添加索引。

select ename,sal from emp where ename = ‘SMITH’;

当ename字段上没有添加索引的时候，以上sql语句会进行全表扫描，扫描ename字段中所有的值。

当ename字段上添加索引的时候，以上sql语句会根据索引扫描，快速定位。

4.2、怎么创建索引对象？怎么删除索引对象？

创建索引对象：

create index 索引名称 on 表名(字段名);

删除索引对象：

drop index 索引名称 on 表名;

4.3、什么时候考虑给字段添加索引？（满足什么条件）

数据量庞大。（根据客户的需求，根据线上的环境）

该字段很少的DML操作。（因为字段进行修改操作，索引也需要维护）

该字段经常出现在where子句中。（经常根据哪个字段查询）

4.4、注意：主键和具有unique约束的字段自动会添加索引。

根据主键查询效率较高。尽量根据主键检索。

4.5、查看sql语句的执行计划：

explain select ename,sal from emp where sal = 5000;

给薪资sal字段添加索引：

create index emp_sal_index on emp(sal);

4.6、索引底层采用的数据结构是：B + Tree

4.7、索引的实现原理？

通过B Tree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的“物理地址”，最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的。

select ename from emp where ename = ‘SMITH’;

通过索引转换为：

select ename from emp where 物理地址 = 0x3;

4.8、索引的分类？

单一索引：给单个字段添加索引

复合索引: 给多个字段联合起来添加1个索引

主键索引：主键上会自动添加索引

唯一索引：有unique约束的字段上会自动添加索引

…

4.9、索引什么时候失效？

select ename from emp where ename like ‘%A%’;

模糊查询的时候，第一个通配符使用的是%，这个时候索引是失效的。

5、视图(view)

5.1、什么是视图？

站在不同的角度去看到数据。（同一张表的数据，通过不同的角度去看待）。

5.2、怎么创建视图？怎么删除视图？

create view myview as select empno,ename from emp;

drop view myview;

注意：只有DQL语句才能以视图对象的方式创建出来。

5.3、对视图进行增删改查，会影响到原表数据。（通过视图影响原表数据的，不是直接操作的原表）

可以对视图进行CRUD操作。(Create（增） Retrieve（检索） Update（修改） Delete（删除）)

5.4、面向视图操作？

mysql> select * from myview;
±------±-------+
| empno | ename |
±------±-------+
| 7369 | SMITH |
| 7499 | ALLEN |
| 7521 | WARD |
| 7566 | JONES |
| 7654 | MARTIN |
| 7698 | BLAKE |
| 7782 | CLARK |
| 7788 | SCOTT |
| 7839 | KING |
| 7844 | TURNER |
| 7876 | ADAMS |
| 7900 | JAMES |
| 7902 | FORD |
| 7934 | MILLER |
±------±-------+
create table emp_back as select * from emp;

create view myview1 as select empno,ename,sal from emp_back;

mysql> select * from myview1;
±------±-------±--------+
| empno | ename | sal |
±------±-------±--------+
| 7369 | SMITH | 800.00 |
| 7499 | ALLEN | 1600.00 |
| 7521 | WARD | 1250.00 |
| 7566 | JONES | 2975.00 |
| 7654 | MARTIN | 1250.00 |
| 7698 | BLAKE | 2850.00 |
| 7782 | CLARK | 2450.00 |
| 7788 | SCOTT | 3000.00 |
| 7839 | KING | 5000.00 |
| 7844 | TURNER | 1500.00 |
| 7876 | ADAMS | 1100.00 |
| 7900 | JAMES | 950.00 |
| 7902 | FORD | 3000.00 |
| 7934 | MILLER | 1300.00 |
±------±-------±--------+

update myview1 set ename = ‘hehe’,sal = 1 where empno = 7369;// 通过视图修改原表数据。

mysql> select * from myview1;
±------±-------±--------+
| empno | ename | sal |
±------±-------±--------+
| 7369 | hehe | 1.00 |
| 7499 | ALLEN | 1600.00 |
| 7521 | WARD | 1250.00 |
| 7566 | JONES | 2975.00 |
| 7654 | MARTIN | 1250.00 |
| 7698 | BLAKE | 2850.00 |
| 7782 | CLARK | 2450.00 |
| 7788 | SCOTT | 3000.00 |
| 7839 | KING | 5000.00 |
| 7844 | TURNER | 1500.00 |
| 7876 | ADAMS | 1100.00 |
| 7900 | JAMES | 950.00 |
| 7902 | FORD | 3000.00 |
| 7934 | MILLER | 1300.00 |
±------±-------±--------+

delete from myview1 where empno = 7369;// 通过视图删除原表数据。

mysql> select * from myview1;
±------±-------±--------+
| empno | ename | sal |
±------±-------±--------+
| 7499 | ALLEN | 1600.00 |
| 7521 | WARD | 1250.00 |
| 7566 | JONES | 2975.00 |
| 7654 | MARTIN | 1250.00 |
| 7698 | BLAKE | 2850.00 |
| 7782 | CLARK | 2450.00 |
| 7788 | SCOTT | 3000.00 |
| 7839 | KING | 5000.00 |
| 7844 | TURNER | 1500.00 |
| 7876 | ADAMS | 1100.00 |
| 7900 | JAMES | 950.00 |
| 7902 | FORD | 3000.00 |
| 7934 | MILLER | 1300.00 |
±------±-------±--------+