Skr-Eric的Mysql课堂（六）——Mysql的锁、存储引擎、事务、数据的导入导出及备份恢复

锁

  1、目的 ：解决客户端并发访问的冲突问题

  2、锁分类

    1、锁类型

      1、读锁(共享锁)

        查询(select):加读锁之后,别人不能更改表记录,但可以进行查询

      2、写锁(互斥锁、排他锁)

        更新(update):加写锁之后别人不能查、不能改

    2、锁粒度

      1、行级锁 ：Innodb

        select ：加读锁,锁1行

         update ：加写锁,锁1行

      2、表级锁 ：MyISAM

        select ：加读锁,锁1张表

       update ：加写锁,锁1张表

 

存储引擎(engine ：处理表的处理器)

  1、基本操作命令

    1、查看所有存储引擎

      show engines;

    2、查看已有表的存储引擎

      show create table 表名;

    3、创建表指定存储引擎

      create table 表名(...)engine=myisam,charset=utf8;

    4、已有表

      alter table 表名 engine=myisam;

  2、常用存储引擎的特点

    1、InnoDB特点

      1、支持事务、外键、行级锁

      2、共享表空间

        表名.frm ：表结构和索引信息  

表名.ibd : 表记录

    2、MyISAM特点

      1、支持表级锁

      2、独享表空间

        表名.frm ：表结构

       表名.MYD ：表记录

       表名.MYI ：索引信息

    3、Memory存储引擎

      1、数据存储在内存中,速度快

      2、服务器重启、MySQL服务重启后表记录消失

  3、如何决定使用哪个存储引擎

    1、查询操作多的表使用MyISAM(使用InnoDB浪费资源)

    2、写操作多的表使用InnoDB

 

事务和事务回滚应用

    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> update ICBC set money=money+5000 where name="没钱人";

mysql> commit;

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

 

失败 ：

mysql> begin;

mysql> update CCB set money=money-5000 where name="有钱人";

mysql> update ICBC set 工行服务器故障... ;

mysql> rollback;

#####转账失败#####

 

数据导出

  1、把数据库表的记录到处到系统文件里

  2、语法格式

    select ... from 表名

    into outfile "文件名"

    fileds terminated by "分隔符"

    lines terminated by "\n";

  3、练习

    1、把MOSHOU库下的sanguo表中,英雄的姓名、攻击值和国家给导出来,sanguo.csv

      1、查看搜索路径

        show variables like "%secure%";

      2、执行数据导出语句

        select name,gongji,country from MOSHOU.sanguo

into outfile "/var/lib/mysql-files/sanguo.csv"

fields terminated by ","

lines terminated by "\n";

 

Error: ... secure_file_priv ...

    2、把 mysql 库下的user表中 user、host的值导出到系统文件 user.txt

      select user,host from mysql.user

      into outfile "/var/lib/mysql-files/user.txt"

      fields terminated by "   "

      lines terminated by "\n";

  4、查看、更改文件权限

    1、ls -l score.txt

      -  rw-  rw-  r--   tarena   tarena

      r(4) : 读           所有者   所属组

     w(2) : 写

     x(1) : 可执行

        rw- : 所有者权限

       rw- : 同组其他用户文件

        r-- : 其他组的用户权限

    2、chmod 777 score.txt

       chmod 740 score.txt

 

表的复制

  1、语法

    create table 表名 select ... from 表名 where 条件;

  2、示例

    1、复制MOSHOU.sanguo表,sanguo2

      create table MOSHOU.sanguo2 select * from MOSHOU.sanguo;

    2、复制MOSHOU.sanguo中的id、name、country的记录,sanguo3

      create table MOSHOU.sanguo3 select id,name,country from MOSHOU.sanguo;

    3、复制MOSHOU.sanguo中的name、country,每页显示2条记录,复制第3页的内容

      create table MOSHOU.sanguo4 select name,country from sanguo limit 4,2;

  3、复制表结构

    create table 表名 select ... from 表名 where false;

 

数据备份(mysqldump,在Linux终端操作)

  1、命令格式

    mysqldump -u用户名 -p 源库名 > ***.sql

  2、源库名的表示方式

    --all-databases    备份所有库

    库名               备份1个库

    -B 库1 库2 库3     备份多个库

    库名 表1 表2 表3   备份多张表

  3、练习

    1、备份所有库,放到mydata目录下 ：All.sql

      mysqldump -uroot -p --all-databases > All.sql

    2、备份db4库中的 sheng、city、xian三张表,db4scx.sql

      mysqldump -uroot -p db4 sheng city xian > db4scx.sql

    3、备份MOSHOU和db4两个库,md.sql

      mysqldump -uroot -p -B db4 MOSHOU > md.sql

 

数据恢复

  1、命令格式(Linux终端)

    mysql -u用户名 -p 目标库名 < ***.sql

  2、从所有库备份All.sql中恢复某一个库

    mysql -u用户名 -p --one-database 库名 < All.sql

    mysql -uroot -p --one-database db4 < All.sql

  3、示例

    1、在db4.sheng添加一条记录

      insert into sheng values(null,300000,"黑龙江");

    2、在db4库,新建一张表t888

      create table t888(id int);

    3、从 db4.sql 恢复db4库

      mysql -uroot -p db4 < db4.sql

    注意 ：

      1、恢复库时,如果恢复到原库会将表中数据覆盖,新增表不会删除

      2、恢复库时,如果库不存在,则必须先创建空库

 

 

想要看更多的课程请微信关注SkrEric的编程课堂