MySQLDay09(练习 常用存储引擎 事务基本概念）

列出所有员工的姓名、部门名称和工资
select 
    e.ename,d.dname,e.sal
from 
    emp e
join
    dept d
on 
    e.deptno=d.deptno;
列出所有部门的详细信息和人数
select
    e.*,d.*,d.loc,count(e.ename)
from 
    emp e
right join
    dept d
on
    e.deptn=d.deptno
group by
    d.deptno,d.dname,d.loc; 可以根据多个字段分组

列出各种工作的最低工资及从事此工作的雇员姓名
select 
    job,min(sal) as minsal
from
    emp
group by
    job;

select 
    e.ename,t.*
from 
    emp e
join 
    (select 
        job,min(sal) as minsal
    from
        emp
    group by
        job) t
on 
    e.job=t.job and e.sal=t.minsal; 
列出各个部门的MANAGR的最低薪金
select 
     deptno,min(sal)
from
    emp
where
    job='MANAGER'
groub by
    deptno;

列出所有员工的年工资，按年薪从低到高排序
select 
    ename,((sal+ifnull(comm,0))*12) as yearsal
from 
    emp
order by 
    yearsal asc;

员工领导的薪水超过3000的员工名称和领导名称
select 
    a.ename empname,b.ename leadername
from
    emp a
join
    emp b
on 
    a.mgr=b.empno
where
    b.sal>3000;

部门名称中带's'字符的部门员工的工资合计、部门人数
select 
    d.dname,ifnull(sum(e.sal),0) as sumsal,count(e.ename) as totalEmp
from 
    emp e
right join
    dept d
on
    e.deptno=d.deptno
where
    d.dname like '%s%'  
group by
    d.dname;                    

给任职日期超过30年的员工加薪10%
update emp_bak set sal=sal*1.1 where (to_days(now())-to_days(hiredate))/365>30;

存储引擎(MySQL特有_2016)
    数据库中的各表均被(在创建表时)指定的存储引擎来处理
    服务器可用的引擎依赖于一下因素
        MySQL的版本
        服务器在开发时如何配置
        启动选项
    为了解当前服务器的有什么存储引擎，可用show ngines
    mysql>SHOW ENGINES\G

常用的存储引擎
MyISAM存储引擎
    MySQL最常用的引擎 不支持事务
    管理的表具有以下特征
        使用三个文件表示每个表
            格式文件---存储表结构的定义(mytale.frm)
            数据文件---存储表行的内容(mytable.MYD)
            索引文件---存储表上索引(mytable.MYI)
        灵活的AUTO_INCREMENT字段处理
        可被转换为压缩、只读表来节省空间 只读提升效率

InnoDB存储引擎
    缺省引擎(默认引擎) 可以在my.ini改
    管理的表具有以下特征
        每个InnoDB表在数据库目录中以.frm格式文件表示
        InnoDB表空间tablespace被用于存储表的内容
        提供一组用来记录事务性活动的日志文件
        用COMMIT(提交)、SAVAPOINT及ROLLBACK(回滚)支持事务处理
        提供全ACID兼容
        在MySQL服务器崩溃后提供自动恢复
        多版本(MVCC)和行级锁定
        支持外键及引用的完整性，包括级联删除和更新

MEMORY存储引擎
    使用MEMORY存储引擎的表，其数据存储在内存中，且行的长度固定，这两个特点使得MEMORY存储引擎非常快
    MEMORY存储引擎管理的表具有以下特征
        在数据库目录内，每个表均以.frm格式的文件表示
        表数据及索引被存储在内存中
        表级锁机制
        不能包含TEXT或BLOB字段
    MEMORY存储引擎以前被称为HEAP引擎   

选择合适的存储引擎
    当创建表时，根据表的应用场景选择合适的存储引擎
    MyISAM表适合大量的数据读而少量数据更新的混合操作，MyISAM表的另一种适用情形是使用压缩的只读表

    如果查询中包含较多的数据更新操作，应使用InnoDB，其行级锁机制和多版本的支持为数据读取和更新的混合操作提供了良好的并发机制
    可使用MEMORY存储引擎来存储非永久需要的数据，或者是能够从基于磁盘的表中重新生成的数据       

事务
概述 和数据文件有关
    事务对应的英语单词Transaction
    事务：
        一个最小的不可再分的工作单元。
        通常一个事务对应了一个完整的业务
        而一个完整的业务需要批量的DML(insert,update,delete)语句共同联合完成
        事务只和DML语句有关系，或者说DML语句才有事务
        以上描述的“批量的DML语句”有多少条语句，和业务逻辑有关
            业务逻辑不同DML语句的个数不同

银行账户转账操作，用户转账是一个完整的业务，最小的单元，不可再分
也就是说用户账户转账是一个事务
执行转账操作
    update t_acc set balance=3000.0 where actno='acc-001';
    update t_acc set balance=2000.0 where actno='acc-002';
    以上的两条DML语句要求必须同时成功或者同时失败，最小单元，不可再分
    当第一条DML语句执行成功之后，并不能将底层数据库中第一个帐号的数据修改
    知识将操作记录了一下，这个记录是在内存中完成的，当第二条DML语句执行成功之后，
    和底层数据库文件中的数据完成同步。若第二条DML语句执行失败，清空所有的历史操作记录
    要完成以上的功能，必须借助事务

事务的四个特性
    原子性(A)
        事务是最小的工作单元，不可再分
    一致性(C)
        事务要求所有的DML语句操作的时候，必须保证同时成功或者同时失败
    隔离性(I)
        事务A和事务B之间具有隔离
    持久性(D)
        是事务的保证，事务终结的标志【内存中的数据持久到硬盘文件中】

一些术语【不是SQL语句】
    开启事务:Start Transaction
    事务结束:End Transaction
    提交事务Commit Transaction
    回滚事务Rollback Transaction

和事务有关的两条重要的SQL语句【TCL】：
    commit;     提交
    rollback;   回滚
    savepoint

事务开启的标志，事务结束的标志
    开启的标志：
        任何一条DML语句（insert update delete）执行，标志事务的开启。
    结束的标志：
        提交或者回滚
        提交：成功的结束，将所有的DML语句操作历史记录和底层硬盘文件中的数据来一次同步
        回滚：失败的结束，将所有的DML语句操作历史记录全部清空

重点
    在事务进行过程中，未结束之前，DML语句是不会更改底层数据库文件中的数据
    只是将历史操作记录一下，在内存中完成记录。只有在事务结束的时候，
    而且是成功的结束的时候才会修改底层硬盘文件中的数据