⑤MySQL之约束二

1，事务（Transaction）

1.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语句同时成功或者同时失败，那么就需要使用数据库的“事务机制”。
1.2，和事务相关的语句只有DML语句。（insert delete update）
这三个语句都是和数据库表当中的“数据”相关的，事务的存在是为了保住数据的完整性，安全性。
1.3，假设所有的业务都能使用1条DML语句搞定，还需要事务机制吗？
不需要，但实际情况不是这样的，通常一个“事儿（事务【业务】）”需要多条DML语句共同联合完成。
1.4，事务包括四大特征：ACID
A：原子性：事务是最小的工作单元，不可再分。
C：一致性：事务必须保证多条DML语句同时成功或失败。
I：隔离性：事务A和B之间具有隔离。
D：持久性：说的是最终数据必须持久化到硬盘文件中，事务才算成功的结束。
1.5，事务之间的隔离性
事务隔离性存在隔离级别，理论上隔离级别包括4个：
    第一级别：读未提交（read uncommitted）
        对方事务还未提交，我们当前事务可以读取到对方未提交的数据。
        读未提交存在脏读（Dirty Read）现象：表示读到了脏的数据。
    第二级别：读已提交（read committed）
        对方事务提交之后的数据我方可以读取到。
        读已提交存在的问题：不可重复读。
    第三级别：可重复读（repeatable read）
        这种隔离级别解决了：不可重复读问题。
        可重复读存在问题：读取到的数据是幻象的
    第四级别：序列化读/串行化读(serializable)
        解决了所有问题，
        存在问题：效率低，事务需要排队。

    oracle数据库默认的隔离级别是：读已提交。
    mysql数据库默认的隔离级别是：可重复读。
1.6，演示事务
mysql事务是默认情况下自动提交的。（只要执行任意一条DML语句则提交一次。）
关闭自动提交：start transaction;

准备一个表
    drop table if exists t_user;
    create table t_user(
        id int primary key auto_increment,
        username varchar(255) unique
    );
---------------------------------------------------------------------------------------------
演示：(插入一条记录，rollback回滚，再次查询还是有一条记录，说明：要执行DML语句就会提交一次。)
    mysql>
        insert into t_user(username) values('e');
        select * from t_user;

        +----+----------+
        | id | username |
        +----+----------+
        |  1 | e        |
        +----+----------+

    mysql>
        rollback;
        select * from t_user;
        +----+----------+
        | id | username |
        +----+----------+
        |  1 | e        |
        +----+----------+
---------------------------------------------------------------------------------------------
演示：使用start transaction;关闭自动提交机制。
    mysql>
        start transaction;
        insert into t_user(username) values('es'),('ls');
        select * from t_user;

        +----+----------+
        | id | username |
        +----+----------+
        |  1 | e        |
        |  2 | es       |
        |  3 | ls       |
        +----+----------+

    mysql>
        rollback;
        select * from t_user;

        +----+----------+
        | id | username |
        +----+----------+
        |  1 | e        |
        +----+----------+
---------------------------------------------------------------------------------------------
演示提交：commit;
    mysql>
        start transaction;
        insert into t_user(username) values('es'),('ls'),('lss');
        select * from t_user;

        +----+----------+
        | id | username |
        +----+----------+
        |  1 | e        |
        |  4 | es       |
        |  5 | ls       |
        |  6 | lss      |
        +----+----------+

    mysql>
        commit;
        rollback;
        select * from t_user;

        +----+----------+
        | id | username |
        +----+----------+
        |  1 | e        |
        |  4 | es       |
        |  5 | ls       |
        |  6 | lss      |
        +----+----------+
1.7，使用事务的隔离级别
设置事务的全局隔离级别：
    set global transaction isolation level 级别;
    set global transaction isolation level read uncommitted;
    set global transaction isolation level read committed;
    set global transaction isolation level repeatable read;
    set global transaction isolation level serializable;
查看事务的全局隔离级别：
    MySQL8.0以前：select @@global.tx_isolation;
    MySQL8.0及以以上版本：select @@global.transaction_isolation

2，索引（是给字段添加索引）

2.1，什么是索引，有什么用？
索引相当于一本书的目录，通过目录可以快速的找到对应的资源
在数据库方面，查询一张表的时候有两种检索方式：
    第一种方式：全表扫描
    第二种方式：根据索引检索（效率高）

索引为什么可以提高检索效率
    根本原因是缩小了扫描范围。

索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引在数据库中的对象，也需要
不断的维护，是有维护成本的。比如：表中的数据经常被修改，这样就不适合添加索引，因为数据
一旦修改，索引需要重新排序，进行维护。
2.2，怎么创建索引对象，怎么删除索引对象？
创建索引对象：create index 索引名称 on 表名(字段名);
删除索引对象：drop index 索引名称 on 表名;
2.3，什么时候给字段添加索引？（满足什么条件）
数据量庞大
该字段很少的DML操作
该字段经常出现在where字句中。
2.4，主键和具有unique约束的字段会自动添加索引。
2.5，查看sql语句的执行计划（执行详情）：explain select语句;
mysql> explain select ename,sal from emp where sal = 5000;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

给薪资sal字段添加索引
    create index emp_sal_index on emp(sal);

mysql> explain select ename,sal from emp where sal = 5000;
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | ref  | emp_sal_index | emp_sal_index | 9       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+

删除sal字段的索引
    drop index emp_sal_index on emp;
2.6，索引底层采用的数据结构：B + Tree
2.7，索引的实现原理
通过B Tree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的“物理地址”，最终通过索引检索到
数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的。
2.8，索引的分类：
单一索引：给单个字段添加索引
复合索引：给多个字段联合起来添加一个索引
主键索引：主键上会自动添加索引
唯一索引：有unique约束的字段会自动添加索引。
...
2.9，索引什么时候失效？
模糊查询的时候，第一个通配符使用的是%，这个时候索引是失效的。

3，视图（view）

3.1，什么是视图
站在不同的角度去看数据。
3.2，怎么创建视图,怎么删除视图
复制一张表
create table emp_bak as select * from emp;

创建视图
create view emp_bak_view as select empno,ename,sal from emp_bak;
删除视图
drop view emp_bak_view;

注意：只要DQL语句才能以视图对象的方式创建出来。
3.3，对视图进行增删改查，会影响原表数据。（通过视图影响原表数据，不是直接操作的原表）
可以对视图进行CRUD操作
3.4，面向视图操作
mysql> select * from emp_bak_view;
    +-------+--------+---------+
    | 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 |
    +-------+--------+---------+

通过视图修改原表数据
mysql>
    update emp_bak_view set ename = 'hehe',sal = 1 where empno = 7369;
    select * from emp_bak;
    +-------+--------+-----------+------+------------+---------+---------+--------+
    | EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
    +-------+--------+-----------+------+------------+---------+---------+--------+
    |  7369 | hehe   | CLERK     | 7902 | 1980-12-17 |    1.00 |    NULL |     20 |
    |  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
    |  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
    |  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
    |  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
    |  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
    |  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
    |  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
    |  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
    |  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
    |  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
    |  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
    |  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
    |  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
    +-------+--------+-----------+------+------------+---------+---------+--------+

通过视图删除原表数据
mysql>
    delete from emp_bak_view where empno = 7369;
    select * from emp_bak;
    +-------+--------+-----------+------+------------+---------+---------+--------+
    | EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
    +-------+--------+-----------+------+------------+---------+---------+--------+
    |  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
    |  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
    |  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
    |  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
    |  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
    |  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
    |  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
    |  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
    |  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
    |  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
    |  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
    |  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
    |  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
    +-------+--------+-----------+------+------------+---------+---------+--------+
3.5，视图的作用
视图可以隐藏表的实现细节，保密级别较高的系统，数据库只对外提供相关的视图，java程序员
只对视图对象进行CRUD。

4，DBA命令

4.1，数据导出
导出整个数据库：mysqldump 数据库名称>路径数据库名.sql -uroot -p
导出某一张表：mysqldump 数据库名称 表名>路径数据库名.sql -uroot -p
4.2，数据导入
create database 数据库名称;
use 数据库名称;
source 路径数据库名.sql

5，数据库设计三范式

5.1，什么是设计范式
设计表的依据，按照这三个范式设计的表不会出现数据冗余。
5.2，三范式
第一范式：任何一张表都应该有主键，并且每一个字段原子性不可再分。
第二范式：建立在第一范式的基础之上，所有的非主键字段完全依赖主键，不能产生部分依赖。
（多对多，三张表，关系表两个外键）
第三范式：建立在第二范式的基础之上，所有的非主键字段直接依赖主键，不能产生传递依赖。
（一对多，两张表，多的表加外键）

6，悲观锁乐观锁

悲观锁：事务必须排队执行，数据锁住了，不允许并发。（行级锁：select语句后面加：for update）
乐观锁：支持并发，事务不需要排队，只不过需要一个版本号。

上篇：④MySQL之约束一

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 203,362评论 5赞 477
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 85,330评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 150,247评论 0赞 337
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,560评论 1赞 273
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,580评论 5赞 365
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,569评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,929评论 3赞 395
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,587评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,840评论 1赞 297
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,596评论 2赞 321
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,678评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,366评论 4赞 318
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,945评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,929评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,165评论 1赞 259
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 43,271评论 2赞 349
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,403评论 2赞 342

推荐阅读更多精彩内容