mysql 数据库 day_06

事务
============================================
    *) 事务是数据操作的最小单元


    *) 多个数据增删改操作，完成的一项业务处理
    *) 如果事务事务成功，其中每一项操作都生效
        如果事务事务失败，其中每一项操作都失败
    *) 数据库数据操作，以事务为一个最小操作单元，
        不应该以一个sql语句为一个操作单元；
        要么整个事务成功，要么整个事务失败
    *) 事务的原子性
    *) 在当前事务中对数据的修改，只对当前连接可见


    *) ACID
        ------------
            A - 原子性 Atomic
            C - 一致性 Consistency
                    转账前 a+b = 100
                    转帐后 a+b = 100
            I - 隔离性 Isolation
                    一个事物进行中时，
                    另一事物不能操作数据
            D - 持久性 Durancy
                    提交事务之后，
                    数据持久生效


    *)事务操作
        
        *)开始事务
            
            start transaction;
            
            set autocommit=0;
        
        *)提交事务
            
            commit;
        
        *)回滚事务
        
            rollback;
     
    *) mysql 默认不开始事务，需要手动启动事务
        
        *)每一步数据修改都直接生效
        
        *)查看事务是否开启
            
            show variables like 'autocommit';
            
               自动提交开启，没有事务
               自动提交关闭，开始事务


        *)innodb 提供行级锁
            
            一个事务修改一行数据未提交时，
            该行数据会被锁定，
            不允许其他事务修改


    
    *)事务隔离级别
          set tx_isolation='READ-UNCOMMITTED';
          set tx_isolation='read-committed';
          set tx_isolation='repeatable-read';
          set tx_isolation='serializable';
        
        1) 脏读  READ-UNCOMMITTED 读未提交数据
              
              一个事务修改一半的数据
              被其他事务读取
        
        2) 不允许脏读 read-committed 读提交数据
              
              只能读取其他事务已提交的数据
              
              虚读 update
              幻读 insert,delete
              
        
        3) 可重复读 (mysql 默认) repeatable-read
              
              事务过程中，
              前后读取的数据一致，
              
              即使过程中数据已被其他事务修改，
              也读取旧的数据
        
        4) 串行化 serializable
              
              所有事务排队依次执行，
              不能同时执行


        
        *) 查看隔离级别
              
             show variables like 'tx%';






视图 view
============================================
  * 将一个查询的查询结果当做表
  * 可以从这个虚拟的表查询
  
    drop view if exists v1;
    
    create [or replace] view v1
    as
    selet ...;
  
  * 视图不是表，不保存数据，
    从视图查询时，
    本质是从查询结果再查询(两层查询)
  
  * 查看视图
      show tables;
      desc v1;
      show create view v1;
  
  * 为什么使用视图
      *)视图在大的项目中非常常用
      
      *)简化查询
          select 
          from 
              a join b .. join c...join d...join e...
          
          select .. from v1;
      
      *)安全
          创建视图给用户访问，
          隐藏真实数据表
      
      *)一般只从视图查询，不对视图做增删改操作








索引
==============================================
  * 数据存储位置目录
        *)哈希索引
        *)红黑树索引
  
  * 提高查询效率，首先考虑创建索引
        
  * where name='abc'
      
      name数据创建索引
  
  * where name='abc' and birth='1993-5-3'
      
      name和birth两个字段创建索引
        对 where name='abc' 单字段过滤也有效
        但对 where birth='1993-5-3' 单字段过滤无效
  
  * 创建索引
      
      create index index_name on tb1(name);
      
      create index first_name_index
      on employees(first_name);      
      
      create index job_dept_index
      on employees(job_id, department_id);








数据备份和恢复
================================================
  * 导出库表及数据
        
        mysqldump  -uroot -p 
        --default-character-set=utf8
        库名>/home/soft01/hr.sql
  
        
        mysqldump  -uroot -p --default-character-set=utf8  stu>/home/soft01/stu.sql




        cat ~/stu.sql  |more
  
  * 将备份数据重新导入数据库
        
        mysql -uroot -p --default-character-set=utf8
        库名<文件路径
                
        mysql -uroot -p --default-character-set=utf8
        stu2</home/soft01/stu.sql        
        
        drop database if exists jtds;
        create database jtds charset=utf8;
        将 jtds.sql 导入 jtds 库






limit
=================================================
  * limit 5
        前5条
  
  * limit 0,10
        从第一条开始的10条
  
  * limit 10,10
        从第十一条开始的10条
  
  * limit 20,10
        从第二十一条开始的10条
  
  * 查询第 page 页，每页10条
        
        第一个参数：  (page-1)*10
        第二个参数：  10
        


































练习
==========================================
  1. 测试事务操作
        
        start transaction;
        
        insert into regions(region_name) values('aaa');
        insert into regions(region_name) values('bbb');
        insert into regions(region_name) values('ccc');
        
        select * from regions;
        
        -- 新开终端，查询 regions 表
        
        -- 第一个客户端
        commit;
        
        -- 第二个客户端再查询
        
        
        -- 第一个客户端
        start transaction;
        insert into regions(region_name) values('aaa');
        insert into regions(region_name) values('bbb');
        insert into regions(region_name) values('ccc');
        select * from regions;
        rollback;
        select * from regions;
        
        -- 第一个客户端
        start transaction;
        
        update regions set region_name='南极' 
        where region_id=5;
        
        select * from regions;
        
        -- 第二个客户端
        start transaction;
        
        update regions set region_name='北极' where region_id=5;


  2. 测试隔离级别
        
        1) 脏读
           
           两个客户端中都设置
           set tx_isolation='READ-UNCOMMITTED'; -- 脏数据
           
           -- 第一个客户端
           start transaction;
           
           insert into regions(region_name) values('eee');
           
           update regions set region_name='月球' 
           where region_id=5;
           
           -- 第二个客户端
           select * from regions;
           
           -- 第一个客户端
           rollback;
           
      
      
      
      2) 幻读
        
           两个客户端中都设置
           set tx_isolation='read-committed'; -- 读提交数据、幻读
           
           -- 第一个客户端
           rollback;
           start transaction;
           insert into regions(region_name) values('5555555');
           
           update regions set region_name='水星' 
           where region_id=5;
           
           -- 第二个客户端
           rollback;
           start transaction;
           select * from regions;
           
           -- 第一个客户端
           commit;
           
           -- 第二个客户端
           select * from regions; -- 看到第一个客户端提交的数据
           
      
      
      3) 可重复读
      
           两个客户端中都设置
           set tx_isolation='repeatable-read'; -- 读提交数据、幻读
           
           -- 第一个客户端
           rollback;
           start transaction;
           insert into regions(region_name) values('888888');
           
           update regions set region_name='土星' 
           where region_id=5;
           
           -- 第二个客户端
           rollback;
           start transaction;
           select * from regions;
           
           -- 第一个客户端
           commit;
           
           -- 第二个客户端
           select * from regions; -- 看到第一个客户端提交的数据
 
  3. 视图测试
      
      drop view if exists v1;
      
      create or replace view v1
      as
      select 
       employee_id,first_name,salary,department_id
   from employees
   where (department_id,salary) in(
       select
           department_id, max(salary)
       from employees
       where department_id is not null
       group by department_id
   );
   
   show tables;
   select * from v1;


  4. 商品表 tb_item
        
        select * from tb_item;
  
  5. 有什么品牌
        
        select distinct brand from tb_item;
  
  6. 最贵商品的品牌、名称、价格
        
        select brand,title,price
        from tb_item
        order by price desc
        limit 1;
  
  7. 分页查看商品
        
        select id,brand,title,price
        from tb_item
        order by price desc
        -- limit 0,10;
        -- limit 10,10;
        -- limit 20,10;
        limit 30,10;
  
  8. 商品名和商品描述
        
        select
            title,item_desc
        from
            tb_item i join tb_item_desc d
            on i.id=d.item_id
        limit 1;


  9. 商品分类表 tb_item_cat
        
        select * from tb_item_cat;
        
        select * from tb_item_cat
        where name like '%电脑%' or
              name like '%笔记本%' or
              name like '%手机%';
        
  10. 查询所有的顶层分类
        
        select * from tb_item_cat
        where is_parent=1 and parent_id is null;
  
  11. 查询 161 下的二层分类
        
        select * from tb_item_cat
        where parent_id=161 order by sort_order;
  
  12. 查询 162 下的三层分类
        select * from tb_item_cat
        where parent_id=162 order by sort_order;
  
  13. 用户表
        select * from tb_user;


  14. 订单表
        select * from tb_order;
        
        用户 id 是14的订单
        select * from tb_order where user_id=14;
  
  15. 订单编号是 20161001490698615071
      查询这个订单的所有商品
      
        select * from tb_order_item
        where order_id=20161001490698615071;






作业
===========================================================
    查询没中类别的商品数量
    查询 类别 163 的商品
    查询商品价格不大于100的商品名称列表
    查询品牌是联想,且价格在40000以上的商品名称和价格
    查询品牌是三木,或价格在10以上的商品名称和价格
    查询品牌是三木、广博、齐心的商品名称和价格
    查询品牌不是联想、戴尔的商品名称和价格
    查找品牌是联想且价格大于10000的电脑名称
    查询联想或戴尔的电脑名称列表
    查询联想、戴尔、三木的商品名称列表
    查询不是戴尔的电脑名称列表
    查询所有是记事本的商品品牌、名称和价格
    查询品牌是末尾字符是'力'的商品的品牌、名称和价格
    名称中有联想字样的商品名称
    查询卖点含有'爆款'电脑名称
    查询开头字母是A的电脑名称
    将地址表中的城市、地区、详细地址组合到一起,
    获取订单编号为 20161001490698615071 中的每种商品的标题、单价、数量、总价
    统计商品表中各个品牌的商品数量
    统计商品表中各个品牌的商品数量,并且按照数量从少到多排序
    统计商品表中各个品牌的商品数量,去除品牌为NULL的行,并且按照数量从少到多排序
    查询不同品牌中最贵的商品价格
    将不同品牌中最贵的商品按照价格降序排序
    找出不同品牌中最贵的商品的前三名
    
    查询订购了10000028商品的客户姓名和联系方式(三层子查询)
         首先查询订单表,根据10000028这个商品编号找到有该商品的订单编号
         然后查询订单表,根据上面得到的订单编号找到订购此订单的用户编号
         最后查询用户表,根据第二部中查到的用户编号,找到对应的用户名和联系方式






    查询没中类别的商品数量
    select cid,count(*) from tb_item group by cid
    
    查询 类别 163 的商品
    select id,title,price from tb_item where cid=163;
    查询商品价格不大于100的商品名称列表
    select id,title,price from tb_item where price<100;
    查询品牌是联想,且价格在40000以上的商品名称和价格
    select id,title,price from tb_item where brand='联想' and price>40000;
    查询品牌是三木,或价格在50以下的商品名称和价格
    select id,brand,title,price from tb_item where brand='三木' or price<50;
    查询品牌是三木、广博、齐心的商品名称和价格
    select id,brand,title,price from tb_item where brand in('三木','广博','齐心');
    查询品牌不是联想、戴尔的商品名称和价格
    select id,brand,title,price from tb_item where brand not in('联想','戴尔');
    查找品牌是联想且价格大于10000的电脑名称
    select id,brand,title,price from tb_item where brand='联想' and price>10000;
    查询联想或戴尔的电脑名称列表
    select id,brand,title,price from tb_item where brand='联想' or brand='戴尔';
    查询联想、戴尔、三木的商品名称列表
    select id,brand,title,price from tb_item where brand in('联想','戴尔','三木');
    查询不是戴尔的电脑名称列表
    select id,brand,title,price from tb_item where brand<>'戴尔';
    查询所有是记事本的商品品牌、名称和价格
    select id,brand,title,price from tb_item where title like '%记事本%';
    查询品牌是末尾字符是'力'的商品的品牌、名称和价格
    select id,brand,title,price from tb_item where brand like '%力';
    名称中有联想字样的商品名称
    select id,brand,title,price from tb_item where title like '%联想%';
    查询卖点含有'爆款'电脑名称
    select id,brand,title,price from tb_item where cell_point like '%爆款%';
    查询开头字母是A的电脑名称
    select id,brand,title,price from tb_item where title like 'A%';
    将地址表中的城市、地区、详细地址组合到一起,
    select concat(receiver_city, receiver_district,receiver_address) addr from tb_address;
    获取订单编号为 20161001490698615071 中的每种商品的标题、单价、数量、总价
    select title,price,num,total_fee from tb_order_item where order_id='20161001490698615071';
    统计商品表中各个品牌的商品数量
    select brand,count(*) from tb_item group by brand;
    统计商品表中各个品牌的商品数量,并且按照数量从少到多排序
    select brand,count(*) c from tb_item group by brand order by c;
    统计商品表中各个品牌的商品数量,去除品牌为NULL的行,并且按照数量从少到多排序
    select brand,count(*) c from tb_item where brand is not null group by brand order by c;
    查询不同品牌中最贵的商品价格
    select id,title,brand,price from tb_item where (brand,price) in(
      select brand,max(price) m from tb_item where brand is not null group by brand);
    将不同品牌中最贵的商品按照价格降序排序
    select id,title,brand,price from tb_item where (brand,price) in(
      select brand,max(price) m from tb_item where brand is not null group by brand)
    order by price desc;
    
    找出不同品牌中最贵的商品的前三名
    select id,title,brand,price from tb_item where (brand,price) in(
      select brand,max(price) m from tb_item where brand is not null group by brand)
    order by price desc
    limit 3;
    
    查询订购了10000028商品的客户姓名和联系方式
     首先查询订单表,根据10000028这个商品编号找到有该商品的订单编号
     然后查询订单表,根据上面得到的订单编号找到订购此订单的用户编号
     最后查询用户表,根据第二部中查到的用户编号,找到对应的用户名和联系方式
    
    select id, username, phone, email from tb_user where id in
      (select user_id from tb_order where order_id in
        (select order_id from tb_order_item where item_id=10000028))
    
    
    select 
        distinct u.id, u.username, u.phone, u.email
    from 
        tb_user u join tb_order o on u.id=o.user_id
                  join tb_order_item i on o.order_id=i.order_id
    where 
        i.item_id=10000028