MySQL下_多表操作

数据库的关系
1. MySQL是一个"关系型数据库", 所谓的关系型数据库描述的是: 实体与实体之间的关系. 而我们知道,
   所谓的实体对应的是: 数据表, 所以研究实体与实体之间的关系, 其实研究的就是: 数据表与数据表之间的关系.
   但是, 在研究它们之间的关系之前, 我们先来解决下昨天遗留的一个问题: 约束.
     约束:
        作用: 用来保证数据的完整性和安全性.

        分类:
            单表约束:
                主键约束: primary key       //auto_increment
                唯一约束: unique
                非空约束: not null
            多表约束:
                外键约束: foreign key
                    格式:
                        alter table 表A add foreign key(表A的列名) references 表B(表B的列名);
                                //外表,从表         //外键列,从键列             //主表(主键列).
                                //记忆: 外表的外键列, 不能出现 主表的主键列没有的内容.

                                //   员工表                                        //部门表

                                //能不能有员工, 但是没有部门?   不能.
                                //能不能有部门, 但是没员工?        能.


                案例: 部门表 和  员工表
                    // 裁部门的需求: 现实中的逻辑应该是: 先裁掉该部门下所有的员工, 然后再裁部门.
                    create table category(
                        cid int primary key auto_increment,
                        cname varchar(20)
                    );
                    insert into category values(null,'人事部');
                    insert into category values(null,'财务部');
                    insert into category values(null,'研发部');
                    insert into category values(null,'销售部');

                    create table employee(
                        eid int primary key auto_increment,
                        ename varchar(20),
                        gender varchar(10),
                        salary double, -- 薪水
                        eno int     #注释的文字
                    );

                    insert into employee values(null,'凤姐','女',20000.6,1),(null,'芙蓉姐姐','女',30000.3,1);
                    insert into employee values(null,'大可乐','男',2000.3,2);
                    insert into employee values(null,'佳乐','男',100.3,3);

                    alter table employee add foreign key(eno) references category(cid);


2. 多表设计分析:
    一对多的关系:
        一个部门可以有多名员工, 但是一个员工只能属于一个部门.
            班级        学生
            分类表       商品

    多对多的关系:
        一门课程可以被多名学生选择, 一个学生也可以选择多门课程.           //例如: 大学时候的选修课.
            订单        商品


    一对一的关系:
        一个人应该只能有一个身份证号.
        一个公司只能有一个注册地址, 一个注册地址也只能对应一家公司.


3. 多表设计原则:
    一对多的关系, 如何建表:
        在"多"的一方新建一列"外键列", 去关联"一"的一方的主键列.

    多对多的关系, 如何建表:
        新建第三张表, 该表至少有三列, 一列是该表的id列, 剩下两列分别去关联"多"的两方的"主键列".

    一对一的关系, 如何建表:
        唯一外键约束:

        主键对应: 



4. 多表建表分析实战
    购物系统的表关系分析:
        用户表:
        订单表:
        商品表:
        分类表:
    三个角度研究表与表之间的关系:
        1. 用户表和订单表的关系.
        2. 商品表和分类表的关系.
        3. 订单表和商品表的关系.



5. 多表查询
    交叉查询:       //理解, 没有什么特殊意义.
        格式:
            select * from 表A,表B;            //查询结果是两张表的: 笛卡尔积.
                                            //笛卡尔积指的是两张表(总条数)的乘积:  表A的总条数 * 表B的总条数


    连接查询:
        内连接查询:  inner join：显式和隐式两种方式结果相同
            显示内连接:
                select * from 表A inner join 表B on 条件;
                //select * from 表A join 表B on 条件;   (inner可以省略)

            隐式内连接: 
                select * from 表A,表B where 条件;

        外连接查询: outer join
            左外连接查询: 交集 + 左表的全集
                select * from 表A left outer join 表B on 条件;
                //select * from 表A left join 表B on 条件;  (outer可以省略)

            右外连接查询: 交集 + 右表的全集
                select * from 表A right outer join 表B on 条件;
                //select * from 表A right join 表B on 条件;

    子查询:
        概述:
            一个SQL语句的条件需要依赖另一个SQL语句的查询结果.
            in:    只要满足其中一个就行.                      =
            any:   只要比给定的所有值中的任意一个大(小)就行.   or
            all:   必须比给定的所有值都要大(小).             and


        案例:
            已知: 
                部门表category:
                    cid, cname
                员工表employee:
                    eid, ename, salary, sex, dno
            需求:
                1. 查询员工姓名, 员工编号, 员工工资, 员工所在的部门.

                2. 查询所有有员工的部门名字.

                3.查询收入在5000元及其以上的员工的所在部门;

                4.查询部门编号比"收入在5000元及其以上的员工所在部门编号任意一个" 大的 所有部门的名称及部门编号

                5.查询部门编号比"收入在5000元及其以上的员工所在部门编号任意一个" 都大的 所有部门的名称及部门编号


6. 事务
    概述: 
        事务指的是逻辑上的一组操作, 组成该逻辑操作的逻辑单元, 要么全部成功, 要么全部失败.

        /*
            记忆:
                MySQL默认开启了事务的自动提交功能, 每一个SQL语句都是一个事务.
                Oracle默认没有开启事务的自动提交功能, 需要手动提交.

                事务可以和 DML(用来执行更新语句的, 增删改)语句相结合使用.


                MySQL数据库的默认隔离级别是:   repeatable read
                Oracle数据库的默认隔离级别是:  read committed
        */

    事务演示:   // 模拟转账的案例
        //佳乐 给  凤姐 转 1000块钱.
        开启事务;
            start transaction;
        SQL语句1;
            //update account set money = money - 1000 where aid = 1;
        SQL语句2;
            //update account set money = money + 1000 where aid = 2;
        提交事务;   
            commit;
        事务回滚;   // 事务回滚会把数据还原该该事务执行之前的状态.
            rollback;



        show variables like '%commit%';   //查看MySQL是否开启了"事务的自动提交"功能.
        select @@tx_isolation;            //查看当前事务的隔离级别.
        set session transaction isolation level 隔离级别;   //设置当前事务的隔离级别.


    事务的特点:
        1. 原子性.
            强调事务的多个操作不可分割.  
        2. 一致性.
            强调事务执行的前后, 数据完整性保持一致.
        3. 隔离性  //重点.
            强调事务执行过程中, 不应该受到其他事务的干扰.
        4. 持久性
            强调事务一旦结束, 数据就持久到数据库中.

    如果不考虑隔离性, 有可能出现以下问题:
        关于读的问题:
            脏读:
                指的是: 一个事务读取到了另一个事务还没有提交的数据.

            不可重复读:
                指的是: 一个事务读取到了另一个事务提交过的: 修改的数据.
                导致多次查询结果不一致.


            虚读: //有些书中可能会把它写成"幻读", 这个了解就行.
                指的是: 一个事务读取到了另一个事务提交过的: 添加的数据.
                导致多次查询结果不一致.


        关于写的问题: //暂时了解.
            丢失更新.

    其实所谓的"考虑隔离性", 其实就是设置"隔离级别":
        隔离级别权限从小到大分别是:
            read uncommitted  < read committed < repeatable read < serializable

        安全性从小到大分别是:
            read uncommitted  < read committed < repeatable read < serializable

        效率从高到低分别是:
            read uncommitted  > read committed > repeatable read > serializable

        read uncommitted: 有可能引发脏读, 不可重复读, 虚读.
        read commit:      能规避脏读, 但是有可能发生不可重复读, 虚读.
        repeatable read:  能规避脏读, 不可重复读, 但是有可能发生虚读.
        serializable:     串行化的, 能规避这三种. 


    演示"脏读":
        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的数据, 
            //导致多次查询结果不一致.