数据库学习记录（六）

1.事务(Transaction)

Q:什么是事务？

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

比如：银行账户，从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)

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

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

 

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

不需要事务机制


Q: 事务的特性？

A:    

事务包括四大特性：ACID

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

B:一致性：事务必须保证多条DML语句同时成功或者同时失败。

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

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

 

关于事务之间的隔离性？

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

 第一级别：读未提交(read uncommitted)

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

 读未提交存在脏读(Dirty Read) 现象：表示读到了脏数据。

 

 第二级别：读已提交(read committed)

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

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

 

 第三级别：可重复读(repeatable read)

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

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

 

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

 解决了所有问题。

 效率低，需要事务排队。

 

Oracle数据库默认的隔离级别是：第二级别，读已提交。

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

 

演示事务：

* 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语句，则提交一次。

mysql> insert into t_user(username) values('zs');

Querk OK, 1 row affected (0.03 sec)

mysql> select * from t_user;

+----+----------+

| id | username |

+----+----------+

|  1| zs    |

+----+----------+

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

  mysql> select * from t_user;

+----+----------+

| id | username |

+----+----------+

|  1 | zs    |

+----+----------+

 

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

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("lisi");

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from t_user;

+----+----------+

| id | username |

+----+---------- +

|  1| zs     |

|  2 | lisi   |

+----+---------- +

2 rows in set (0.00 sec)

 

mysql> insert into t_user(username) values("wangwu");

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from t_user;

+----+----------+

| id | username |

+----+----------+

|  1 | zs    |

|  2 | lisi   |

|  3 | wangwu  |

+----+----------+

3 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    |

+----+----------+

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("wangwu");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_user(username) values("object");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_user(username) values("joke");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_user(username) values("xiaozhaozhao");

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from t_user;

+----+--------------+

| id | username   |

+----+--------------+

|  1 | zs      |

|  4 | wangwu    |

|  5 | object    |

|  6 | joke     |

|  7 | xiaozhaozhao |

+----+--------------+

5 rows in set (0.00 sec)

 

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

 

        mysql> select * from t_user;

        +----+--------------+

        | id | username   |

        +----+--------------+

        |  1 | zs        |

        |  4 | wangwu    |

        |  5 | object    |

        |  6 | joke     |

        |  7 | xiaozhaozhao |

        +----+--------------+

        5 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 | wangwu    |

        |  5 | object     |

        |  6 | joke      |

        |  7 | xiaozhaozhao |

        +----+--------------+

        5 rows in set (0.00 sec)

 

rollback : 回滚。

commit ： 提交。

start transaction : 关闭自动提交机制。

      

-----------------------------------------------------------------

 

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

       演示第1级别：读未提交

           set global transaction isolation level read uncommitted;

       演示第二级别；读已提交

           set global transaction isolation level read committed;

       演示第三级别:可重复读

           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语句的执行计划：

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

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra   |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

|  1 | SIMPLE   | emp  | ALL | NULL       | NULL | NULL  | NULL |   14 | 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 | type | possible_keys | key       | key_len | ref  | rows | Extra    |

+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+

|  1 | SIMPLE   | emp  | ref | emp_sal_index | emp_sal_index  | 9    | const |  1  | Using where |

+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+

                                                                                  rows检索次数减少了

 

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

 

   4.7、索引的实现原理？

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

       通过物理索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的。

           select ename from emp where ename = 'SMITH';

       通过索引转换为：

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

 

   4.8、索引的分类？

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

       复合索引：给多个字段联合起来添加一个索引

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

       唯一索引：有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操作。

 

    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_bak as select * from emp;

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

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

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

 

   5.5、试图的作用？

       试图可以隐藏表的实现细节。保密级别较高的系统，数据库只对外提供相关的视图，java程序员只对视图对象进行CRUD。

 

6、DBA命令

 

    6.1、在数据库当中的数据导出

        在windows的DOS命令窗口中执行： (导出整个库)

        mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p999

    在windows的dos命令窗口中执行：(导出数据库中指定的表)

        mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot -p999

 

    6.2、导入数据

        create database bjpowernode;

    use bjpowernode;

    source D:\bjpowernode.sql

 

7、数据库设计三范式(重点内容，面试经常会问)

 

    7.1、什么是设计范式？

        设计表的依据。按照这三个范式设计的表不会出现数据冗余。

 

    7.2、三范式都是哪些？

        第一范式：任何一张表都应该有主键，并且每一个字段原子性不可再分。

 

    第二范式：建立在第一范式的基础上，所有非主键字段完全依赖主键，不能产生部份依赖。

        多对多？三张表，关系表两个外键。

        t_student学生表

        sno(pk)       sname

        ---------------------

         1          张三

         2          李四

         3          王五

 

         t_teacher 讲师表

         tno(pk)      tname

         ----------------------

          1         王老师

          2         张老师

          3         李老师

 

          t_student_teacher_relation 学生讲师关系表

          id(pk)        sno(fk)      tno(fk)

          -------------------------------------------

           1          1           3

           2          1         1

           3          2         2

           4          2         3

           5          3         1

           6          3         3

  

    第三范式：建立在第二范式的基础上，所有非主键字段直接依赖主键，不能产生传递依赖。

        一对多？两张表，多的表加外键。

        班级t_class

        cno(pk)          cname

        --------------------------

          1              班级1

          2              班级2

 

        学生t_student

        sno(pk)         sname         classno(fk)

        --------------------------------------------

         101         张1       1

         102           张2       2

         103         张3       2

         104         张4       1

         105         张5       2

 

      提醒：在实际的开发中，以满足客户需求为主，有的时候会拿冗余换执行速度。

 

一对一怎么设计？

 

  一对一设计有两种方案：主键共享

   t_user_login 用户登陆表

    id(pk)       username        password

    ----------------------------------------

     1             zs                123

     2             ls                456

      

     t_user_detail 用户详细信息表

      id(pk+fk)         realname          tel          ...

     ----------------------------------------------------

    1                     张三            11111111112234

    2                     李四            12112523432412

 

 

  一对一设计有两种方案:外键唯一。

   t_user_login 用户登陆表

    id(pk)       username        password

    ----------------------------------------

     1             zs                123

     2             ls                456

      

     t_user_detail 用户详细信息表

      id(pk)         realname          tel            userid(fk+unique)     

     ----------------------------------------------------

       1               张三         111111114          2

       2            李四         121432412          1

 

来源：https://www.bilibili.com/video/BV1fx411X7BD?p=70&spm_id_from=pageDriver