MySQL第三篇

悠悠 2022-03-07 11:24 305阅读 0赞

#### MySQL第三篇 ####

**1、锁(MySQL自动加锁和释放锁)**

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

**2、锁分类**

1、锁类型

1、读锁(共享锁)

select ：加读锁之后别人可以查询,但是不能更改(update)

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

update ：加写锁之后,别人不能查、不能改

2、锁粒度

1、行级锁 ：可加读锁、写锁

2、表级锁 ：可加读锁、写锁

**3、存储引擎(处理表的处理器)**

1、查看所有存储引擎 show engines;

2、查看已有表存储引擎 show create table 表名;

3、创建表时指定存储引擎 create table 表名(…)engine=MyISAM;

4、已有表时修改存储引擎 alter table 表名 engine=InnoDB;

**4、常用存储引擎特点**

1、InnoDB特点

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

2、共享表空间

3、表名.frm ：表结构和索引信息  
表名.ibd ：表记录

2、MyISAM特点

1、支持表级锁

2、独享表空间

3、表名.frm ：表结构  
表名.MYD ：表记录  
表名.MYI ：索引信息

3、MEMORY特点

1、表结构存储在硬盘,表记录存储在内存  
2、服务/主机重启后,表记录消失

**5、如何选择使用哪个存储引擎**

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

2、执行写操作多的表用InnoDB

**6、数据备份(mysqldump,在Linux终端中操作)**

1、mysqldump -u用户名 -p 源库名 > XXX.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库,db4.sql
          mysqldump -uroot -p db4 > db4.sql
            
        3、备份MOSHOU库和db4库,md.sql
          mysqldump -uroot -p -B MOSHOU db4 > md.sql 
        
        4、备份db3库中sheng、city、xian三张表,scx.sql
          mysqldump -uroot -p db3 sheng city xian > scx.sql

**7、数据恢复(Linux终端中操作)**

1、mysql -u用户名 -p 目标库名 < XXX.sql
        
      2、示例
      1、先创建db3库
          mysql> create database db3 character set utf8;
          mysql> exit();
          终端 ：mysql -uroot -p --one-database db3 < db3.sql
      3、注意
          1、恢复库时,会将表中数据覆盖,新增表不会删除
          2、恢复时,如果要恢复的库不存在,则先创建空库

**8、数据导入**

1、作用 ：把文件系统的内容导入到数据库中
      2、语法格式
        load data infile "文件名"
        into table 表名
        fields terminated by "分隔符"
        lines terminated by "\n";
        
        e.g.  
        1、在数据库创建对应的表
            create table score(
                id int,
                name varchar(20),
                score float(5,2),
                phnumber char(11),
                class char(7)
            )charset=utf8;
            
        2、把文件拷贝到数据库的搜索路径中
              1、查看搜索路径
                show variables like "secure_file_priv";  # /var/lib/mysql-files
              2、拷贝文件
            	sudo cp scoreTable.csv /var/lib/mysql-files/
                
        3、执行数据导入语句
            load data infile "/var/lib/mysql-files/scoreTable.csv"  #绝对路径
            into table score
            fields terminated by ","
            lines terminated by "\n";

**9、数据导出**

1、把数据库表的记录到处到系统文件里
      2、语法格式
        select ... from 表名
        into outfile "文件名"
        fileds terminated by "分隔符"
        lines terminated by "\n";
        
      e.g.
    	select name,age,score from stu.math
    	into outfile "/var/lib/mysql-files/math.csv"
    	fields terminated by ","
    	lines terminated by "\n";

**10、表的复制**

1、语法  
create table 表名 select … from 表名 where 条件;

2.复制表结构

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

**11、MySQL用户管理**

1、开启远程连接
        1、sudo -i
        2、cd /etc/mysql/mysql.conf.d/
        3、subl mysqld.cnf
        44行 ：bind-address = 127.0.0.1
        4、/etc/init.d/mysql restart
        
     2、添加授权用户
        1、查看用户
        select Host,User from mysql.user;
         
        2、创建用户
          create user "user_name"@"host" identified by "password";
          et:
          create user "tiger"@"localhost" identified by "123456"; 只能本机登录
          create user "dog"@"%" identified by "123456"; 允许远程连接
    
        3、授权
          grant 权限列表 on 库.表 to "用户名"@"%" 
          identified by "密码"
          with grant option;
          权限列表 ：all privileges 、select 、drop、update
          库.表    ：库.*  、 *.*(所有库所有表)
         
         4.删除用户
         drop user "user_name"@"host"
    
         5.设置与更改用户密码
         set password for "user_name"@"host" = password("新密码");
    
         6.撤销用户权限
         revoke privileges on 库.表 form "user_name"@"host"

**12、事务和事务回滚**

1、定义 ：一件事从开始发生到结束的过程
        
      2、作用 ：确保数据的一致性
    
      3、事务和事务回滚应用
        SQL命令默认自动提交到数据库执行
        show variables like "autocommit"
        1、开启事务
          mysql>begin;
        2、终止事务
          mysql>commit; | rollback;
            
       4、注意:
            事务只针对于表记录操作(增删改),对库和表的操作无效

**13、pymysql**

1、创建库db5,utf8
    	  create database db5 charset utf8;
    	2、创建表t1
    	  use db5;
    	  create table t1(
    	  id int primary key auto_increment,
    	  name varchar(20),
    	  score float(5,2)
    	  )charset=utf8;
    	3、插入3条记录
    	  insert into t1 values
    	  (1,"李白",60),
    	  (2,"杜甫",70),
    	  (3,"白居易",80);   
    
      4、pymysql使用流程
        1、数据库连接对象 ：db = pymysql.connect(host="localhost",user="root",
    			        password="123456",database="db5",charset="utf8")
        2、游标对象       ：cur = db.cursor()
        3、执行命令       ：cur.execute('sql命令')
        4、提交           ：db.commit()
        5、关闭游标       ：cur.close()
        6、关闭数据库连接 ：db.close()
    
      5、数据库连接对象(db)方法
        1、db.commit()   ：提交到数据库执行
        2、db.rollback() ：回滚
        3、db.close()    ：断开与数据库连接
        4、db.cursor()   ：创建游标对象
    
      6、游标对象(cur)方法
        1、cur.execute(sql命令) ：执行sql命令
        2、cur.close()          ：关闭游标对象
        3、cur.fetchone()       ：取第1条(查询)
        4、cur.fetchmany(n)     ：取n条(查询)
        5、cur.fetchall()       ：取所有记录(查询)
    
      7、connect()连接对象的参数
        1、host ：主机地址
        2、user ：用户名
        3、password ：密码
        4、database ：库名
        5、charset  ：字符集(推荐utf8)
        6、port     ：端口号(3306)