数据库(二)2018-08-24

1. MySQL是一个关系型数据库, 关系型数据库描述的就是实体与实体之间的关系.聊到实体与实体之间的关系(表与表之间的关系), 就势必要聊到约束:

约束的作用: 
    保证数据的完整性.
单表约束:
    主键约束: primary key   //一般后边还会写上auto_increment
    唯一约束: unique
    非空约束: not null
    
多表约束:
    外键约束: foreign key
    

案例:
    创建一个部门表(dept) 和 员工信息表(employee), 
    正常的逻辑应该是, 先删除员工, 再清除部门, 
    而如果这两张表之间没有主外键约束的话, 就可以直接删除部门, 而没有先清除该部门的员工, 这样做是不符合实际生活中的逻辑的.

2. 可视化界面的使用
   SQLyog

3. 多表的分析和设计

在实际开发中, 经常会向数据库中创建多个表, 而且多个表之间都是有关系的.

关系分类:
    一对多的关系:
        一个部门可以有多个员工, 一个员工只能属于某一个部门. 
        一个分类下有多个商品, 一个商品只能属于某一个分类.
        一个用户产生多个订单, 一个订单只能属于某一个用户.
    
    多对多的关系:
        一个学生可以选择多门课程, 一个课程也可以被多个学生选择.
        一个订单包含多个商品, 一个商品也可以在多个订单中.
    
    一对一的关系: 
        一个公司只能对应一个注册地址, 一个注册地址只能对应一个公司.

4. 多表建表原则:

一对多的建表原则:
    在"多"的一方创建一个字段, 作为外键, 指向"一"的一方的主键.
    
    
多对多的建表原则:
    需要创建第三张表(中间表), 在中间表中至少需要有两个字段, 分别作为外键指向多对多双方的主键.
    
    
一对一的建表原则:
    唯一外键对应:
        示例:
            公司表
                cid int primary key auto_increment
                cname varchar(20)
                
            地址表:
                aid int primary key auto_increment
                aname varchar(20)
                
                //再新建一列: cno int unique 
    
    主键对应:
        两张表的主键保证一一对应.

5. 多表查询

    连接查询:
        交叉连接(了解即可):
            select * from A,B;      //最终结果获取到的是两个表的笛卡尔积, 一般不用这种做法.
            
        内连接:(inner join)
            显示内连接:
                select * from A inner join B on 条件;
            隐式内连接:
                select * from A,B where 条件;
                
        外连接:(outer join)
            左外连接:
                select * from A left outer join B on 条件;
            右外连接:
                select * from A right outer join B on 条件;
    
    
        内连接和外连接的区别:
            内连接:    查到的是 两个表的交集.
            左外连接:   左边表的全部 和 两个表的交集.
            右外连接:   右边表的全部 和 两个表的交集.
    
    子查询:
        概述:
            指的是一条语句的查询条件需要依赖另一条语句的查询结果.
            in:    只要满足其中一个就行.
            any:   只要比给定的所有值中的任意一个大(小)就行.
            all:   必须比给定的所有值都要大(小).
        需求:
            查询收入在5000元及其以上的员工的所在部门;
                SELECT * FROM dept WHERE did IN (SELECT dno FROM employee WHERE salary >= 5000);
            查询部门编号比"收入在5000元及其以上的员工所在部门编号任意一个" 大的 所有部门的名称及部门编号
                select * from dept where did > any(select dno from employee where salary >=5000);
                
            查询部门编号比"收入在5000元及其以上的员工所在部门编号任意一个" 都大的 所有部门的名称及部门编号
                select * from dept where did > all(select dno from employee where salary >=5000);

6. 事务

概述: 
    事务指的是逻辑上的一组操作, 组成这组操作的各个逻辑单元要么全部都成功, 要么全失败.
    
    //MySQL中默认情况下,一条语句是一个事务, 事务会自动提交.  
    //Oracle中默认事务是不提交的, 需要手动提交事务.

MySQL事务管理的命令:
    start transaction;      //开启事务
    SQL语句1;             //要执行的SQL语句
    SQL语句2;         
    commit                  //提交事务
    rollback;               //回滚事务
    
    show variables like '%commit%';
    
    
事务的特性:
    1. 原子性.
        强调事务的多个操作不可分割.  
    2. 一致性.
        强调事务执行的前后, 数据完整性保持一致.
    3. 隔离性
        强调事务执行过程中, 不应该受到其他事务的干扰.
    4. 持久性
        强调事务一旦结束, 数据就持久到数据库中.
        
如果不考虑事务的隔离性, 有可能会引发一些安全性的问题.
    读的方面:
        脏读:     指的是一个事务读到另一个事务未提交的数据.
        不可重复读: 指的是一个事务读到了另一个事务已经提交的 update的数据, 
                    导致多次查询结果不一致.
        虚读/幻读:  指的是一个事务读到了另一个事务已经提交的 insert的数据,
                    导致多次查询结果不一致.
    写的方面: (了解就行, 不用重点掌握)
        丢失更新.
    
    
    解决事务的读问题:
        设置事务的隔离级别:
            read uncommitted:   //未提交读
                脏读, 不可重复读, 虚读都有可能发生.
            read committed:     //已提交数据库
                避免脏读, 但是不可重复读和虚读是有可能发生的.
            repeatable read:    //可重复读
                避免脏读, 不可重复读. 但是虚读是有可能发生的.
            serializable: 串行化的
                避免脏读, 不可重复读, 虚读
                
        安全性:
            read uncommitted < read committed < repeatable read< Serializable
        效率:
            read uncommitted > read committed > repeatable read  > Serializable
            
    一般数据库设置: read committed 和 repeatable read
        MySQL默认:    repeatable read
        Oracle默认: read committed

7.演示:

    演示"脏读"的产生:
        1. 开启两个cmd窗口A, B.
        2. 查看当前的事务的隔离级别:
            select @@tx_isolation
            //默认为: repeatable read
        3. 将A窗口的隔离级别修改为: read uncommitted;
            set session transaction isolation level read uncommitted;
        4. 再次查看下两个窗口的的隔离级别:
            select @@tx_isolation;
        5. 在A, B两个窗口中分别开启事务:
        6. 在B窗口中完成转账.
            //注意: 先不要提交事务.
        7. 在A窗口中进行查询.
            //发现转账已经成功, 说明脏读已经发生了.
            //脏读: 一个事物读取到另一个事务未提交的数据.
        
    
    演示"不可重复读"的产生:
        1. 开启两个cmd窗口A, B.
        2. 查看当前的事务的隔离级别:
            select @@tx_isolation
            //默认为: repeatable read
        3. 将A窗口的隔离级别修改为: read committed;
            set session transaction isolation level read committed;
        4. 再次查看下两个窗口的的隔离级别:
            select @@tx_isolation;
        5. 在A, B两个窗口中分别开启事务:
        6. 在B窗口中完成转账.
            //注意: 先不要提交事务.
        7. 在A窗口中进行查询.
            //发现A窗口中的数据没有变化, 说明脏读没有发生.
            //脏读: 一个事物读取到另一个事务未提交的数据.
        8. 在B窗口中提交事务.
            //发现A窗口中的数据已经变化了, 说明"不可重复读"已经产生了,
            //不可重复读: 指的是一个事务读到了另一个事务已经提交的 update的数据, 
            //导致多次查询结果不一致.
        
    避免演示"不可重复读"的产生:
        1. 开启两个cmd窗口A, B.
        2. 查看当前的事务的隔离级别:
            select @@tx_isolation
            //默认为: repeatable read
        3. 将A窗口的隔离级别修改为: repeatable read;
            set session transaction isolation level repeatable read;
        4. 再次查看下两个窗口的的隔离级别:
            select @@tx_isolation;
        5. 在A, B两个窗口中分别开启事务:
        6. 在B窗口中完成转账.
            //主要: 先不要提交事务.
        7. 在A窗口中进行查询.
            //发现A窗口中的数据没有变化, 说明脏读没有发生.
            //脏读: 一个事物读取到另一个事务未提交的数据.
        8. 在B窗口中提交事务.
            //发现A窗口中的数据已经变化了, 说明"不可重复读"已经产生了,
            //不可重复读: 指的是一个事务读到了另一个事务已经提交的 update的数据, 
            //导致多次查询结果不一致.  
        
    演示: Serializable 串行化的

8. truncate table 和 delete from 区别?

//truncate(重置),  delete(删除表数据), drop(删除数据库, 数据表)
truncate table:
    可以清除数据, 而且会重置Id, 相当于是: 把表摧毁了, 然后创建一张和该表一模一样的表.
    属于DDL语句, 不可以结合 事务使用.

delete from:
    可以清除数据, 但是不会重置id.
    属于DML语句, 可以结合 事务 使用.