MySQL之数据备份和恢复， 语句优化和事务回滚

1.数据备份(mysqldump,在Linux终端操作)
    1.命令格式:
        mysqldump -u用户名 -p 源库名 > ****.sql
    2.源库名的表示方式
        --all-databases     备份所有的库
        库名                 备份一个库
        -B 库1 库2 库3       备份多个库
        库名 表1 表2 表3      备份多张表
    3.练习
        1.备份所有的库放到mydata目录下:All.sql
            mysqldump -uroot -p --all-databases > All.sql

        2.备份db4库中的sheng,city,xian三张表,db4sex.sql
            mysqldump -uroot -p db4 sheng city xian > db4sex.sql

        3.备份MOSHOU和db4两个库,md.sql
            mysqldump -uroot -p -B MOSHOU db4 > md.sql

2.数据恢复
    1.命令格式(Linux终端)
        mysql -u用户名 -p 目标库名 < xxx.sql
        mysql -uroot -p db4 < db4.sql
    2.从所有的库备份All.sql中恢复某一个库
        mysql -u用户名 -p --one-database 库名 < All.sql
    3.示例
        1.在db4.sheng中添加一条记录
        insert into sheng values(
        null,300000,'青海省');
        2.在db4库,新建一张表t888
        create table t8888(id int); 
        3.从db4.sql恢复db4库
        mysql -uroot -p db4 < db4.sql
        注意:
            1.恢复库时,如果恢复到原库会将表中数据覆盖,新增表不会删除
            2.恢复库时,如果库不存在,则必须先创建空库

3.mysql调优
    1.创建索引
        在select,where,order by常涉及到的字段建立索引

    2.选择合适搜索引擎
        1.读操作多:MyISAM
        2.写操作多:InnoDB

    3.sql语句优化(避免全表扫描)
        1.where子句 尽量不使用 != ,否则放弃索引全表扫描
        2.尽量避免null空值判断,否则全表扫描
            优化前:
                select number from t1 where number is null;
            优化后:
                在number字段设置默认值0,确保number字段无NULL
                select number from t1 where number=0;
        3.尽量避免用or连接条件,否则全表扫描
            优化前:
                select id from t1 where id=10 or id=20;
            优化后:
                select id from t1 where id=10
                union all
                select id from t1 where id=20;
        4.模糊查询尽量避免使用前置的%,否则全表扫描
            select variable from t1 where name='%secure%';
        5.尽量避免使用in和not in 否则全表扫描
            优化前:
                select id from t1 where id in(1,2,3,4);
            优化后:
                select id from t1 where id between 1 and 4;
        6.不能使用select * ...
            用具体的字段代替*,不要返回用不到的任何字段

4.事务和事务回滚 
    1.定义:一件事从开始发生到结束的整个过程
    2.作用:确保数据的一致性
    3.事务和事务回滚应用
        1.SQL命令会 autocommit 到数据库执行
        2.事务操作
            1.开启事务
                mysql> begin; | start transactions;
                mysql> SQL命令 ...
                    ##此时autocommit被禁用 ##
            2.终止事务
                mysql> commit; | rollback;
        3.案例:
            1.背景
                你 : 建行卡
                朋友: 工行卡
                你在建行自动取款机 给你朋友 转5000 块钱
            2,建表
                表1 CCB
                create table CCB(
                name varchar(20),
                money decimal(20,2)
                );
                insert into CCB values('有钱人',100000);
                表2 ICBC
                create table ICBC(
                name varchar(20),
                money decimal(20,2)
                );
                insert into ICBC values('没钱人',0);
            3.开始转账
                mysql>begin;
                mysql>update CCB set money=money-5000 where name='有钱人';
                mysql>uodate ICBC set money=money+5000 where name='没钱人';
                mysql> commit; 

                ####转账成功#####