学习笔记☞ MySQL（四）

1、MySQL用户账户管理

    1、开启MySQL远程管理

        1、获取root权限
            sudo -i
        2、cd到配置文件所在路径
            cd /etc/mysql/mysql.conf.d
        3、vi mysqld.cnf
            # bind-address=127.0.0.1  #注释掉
        4、重启MySQL服务
            /etc/init.d/mysql restart

    2、添加授权用户

        1、使用root用户连接到数据库服务器
            mysql -uroot -p123456
        2、添加新的授权用户
            create user '用户名'@'ip地址' identified by '密码';
            例：create user 'tiger'@'%' identified by '******';
            IP地址的表示方式
                1、% 表示用户可以从任何地址连接到数据库服务器
                2、localhost 用户只能从本地连接
                3、指定一个IP 表示用户只能从此IP连接到服务器

    3、给用户授权

        grant 权限列表 on 库.表 to '用户名'@'IP地址' with grant option;
        grant all privileges on *.* to 'tiger'@'%' with grant option;   # 授权所有权限，等同于root权限
        权限列表：
            select，update，delete，insert，alter，drop，create，...
        库.表：*.* 表示所有库的所有表

    4、练习

        添加一个授权用户 monkey，所有人都可以连接，只对db1库有查询权限
            1、添加授权用户monkey

create user "monkey"@"%" identifited by "123456";

            2、给 monkey用户授权

grant select on db1.* to "monkey"@"%" with grant option;

            3、验证：mysql -hIP地址 -umonkey -p

    5、删除授权用户

        drop user '用户名'@'IP地址';

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

    1、命令格式

        mysqldump -uroot -p   源库名 > 路径/xxx.sql

    2、示例

        1、备份db2库

mysqldump -uroot -p db2> ~/db2.sql

    3、源库名的表示方式

--all-databases	备份所有库
库名	备份单个库
-B 库1 库2 ...	备份多个库
库名 表1 表2 ...	备份指定库的指定表

    4、练习

        1、备份所有库 all_mysql.sql，放到用户主目录下的mydata目录中

>>> mkdir mydata
>>> mysqldump -uroot -p --all-databases> ./mydata/all_mysql.sql

        2、备份db2库中的sheng，city，xian三张表

>>> mysqldump -uroot -p db2> ./mydata/db2scx.sql

        3、备份 MOSHOU 和 db2库

>>> mysqldump -uroot -p -B MOSHOU db2> ./mydata/MSdb2.sql

3、数据恢复

    1、命令格式

        mysql -u用户名 -p 目标库名 < 路径/xxx.sql    #目标库名需存在

    2、示例：

        1、备份库

>>> mysqldump -uroot -p db2 > db2.sql

        2、删除库

drop database db2;

        3、先创建空库

create database db2 default charset=utf8;

        4、恢复命令

>>> mysql -uroot -p db2 < db2.sql

    3、备份分为 完全备份 和 增量备份

        完全备份：mysqldump
        增量备份：binlog日志、xbackup工具

    4、从所有库的备份文件恢复某一个库(--one-database)

        myswl -u用户名 -p --one-database 目标库名 < all-mysql.sql
        示例：

>>> mysql -uroot -p --one-database db2 < all-mysql.sql

        注意：
            1、恢复库时库中新增的表不会删除
            2、恢复时必须先创建空库

4、事务和事务回滚

    1、定义

        一件事从开始发生到结束的整个过程

    2、作用

        确保数据的一致性

    3、事务和事务回滚的应用

        1、mysql中默认SQL语句会自动commit到数据库

show variables like 'autocommit';

        2、事务应用

            1、开启事务

start transaction;
...SQL命令
#此时autocommit被禁用，SQL命令不会对数据库中数据做出修改

            2、终止事务

commit;  #提交
或者
rollback;  #回滚

            3、注意

                1、事务回滚rollback只针对表记录的操作，增、删、改，对创建库和创建表操作无效

        3、案例

            1、背景
                你：建行卡
                你朋友：工商卡
                你在建行的自动取款机给你的朋友工商卡转账5000
            2、过程
                1、转账中
                    1、先到建行的数据库把你的余额 -5000
                    2、再到工行的数据库把你朋友的余额 +5000
                    3、commit; 转账成功
                       rollback;回滚
                2、过程                   

#表1、ccb
CREATE TABLE ccb (
  name  VARCHAR(20),
  money INT
);

INSERT INTO ccb VALUES ('zhuanqian', 10000);

#表2、ICBC

CREATE TABLE ICBC (
  name  VARCHAR(20),
  money INT
);
INSERT INTO ICBC VALUES ('shouqian', 4000);

#开始转账
START TRANSACTION;
UPDATE ccb
SET money = 5000
WHERE name = 'zhuanqian';
UPDATE ICBC
SET money = 断电;
ROLLBACK;

5、存储引擎

    1、定义

        是用来处理表的处理器

    2、存储引擎基本操作

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

show create table 表名; # engine= 引擎名

        2、创建表时指定存储引擎

create table 表名(...)engine=myisam;

        3、查看所有的存储引擎

show engines;

    3、工作中使用的存储引擎

        InnoDB  MyISAM

    4、常用存储引擎的特点

        1、InnoDB特点

            1、共享表空间

                表名.frm  存储表结构
                表名.ibd  存储表记录和索引信息

            2、支持行级锁

        2、MyISAM特点

            1、独享表空间

                表名.frm  存储表结构
                表名.myd  存储表记录
                表名.myi  存储索引信息

            2、支持表级锁

    5、锁

        1、加锁的目的

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

        2、锁的类型

            读锁(select)又叫共享锁

                加读锁后不能更改表中内容，但是可以查询

            写锁(insert,updata,delete)互斥锁、排他锁

        3、锁粒度

            表级锁
            行级锁

            -*-操作完成后会自动释放锁-*-

    6、如何决定使用哪种存储引擎

        1、执行查询多的表使用MyISAM存储引擎(使用InnoDB浪费资源)
        2、执行写操作比较多的表使用InnoDB存储引擎

    7、如何更改表的默认引擎

        1、sudo -i
        2、cd /etc/mysql/mysql.conf.d
        3、vi mysqld.cnf
            [mysqld]下
            defalut-storage-engine=myisam
        4、/etc/init.d/mysql restart

    8、MEMORY存储引擎

        memory：表记录存储在内存中
            表名.frm  表结构
            服务重启后表结构在，表记录都消失。
         create table 表名(...)engine=memory; #表中数据存储在内存中，重启、断电等数据将会丢失

6、MySQL调优

    1、选择合适的存储引擎

        1、经常用来读的表使用myisam存储引擎
        2、其余的表都用innodb存储引擎

    2、SQL语句调优(尽量避免全表扫描)

        1、在select ， where ， order by常涉及到的字段上建立索引
        2、where子句中尽量不要使用 !=，否则将放弃使用索引进行全表扫描
        3、尽量避免用NULL值判断，否则会全表扫描
            示例：
                select id from t1 where number is null;
                优化：
                在number字段设置默认值0
        4、尽量避免用 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;

        5、模糊查询中尽量避免前置 %，导致全表扫描

select id from t1 where name like 'a%'

        6、尽量避免in 和 not in 的使用，否则导致全表扫描

SELECT id FROM t1 WHERE id IN (1, 2, 3);
# 优化后
SELECT id FROM t1 WHERE id BETWEEN 1 AND 3;

        7、尽量避免使用select * ...，要用具体的字段列表代替*，不要反回用不到的任何字段

7、python数据库编程

    1、python数据库借口(python DB-API)

        1、为开发人员提供的数据库用于编程接口

        2、python支持的数据库服务软件

            mysql 、Oracle、SQL_Server、MongoDB...

        3、python提供的操作MySQL模块

            python3：pymysql
            Python2：MySQLdb

        4、pymysql模块使用流程

            1、创建数据库连接
            2、创建游标对象
            3、使用游标对象的方法操作数据库
            4、提交commit
            5、关闭游标对象
            6、关闭数据库连接

        5、建立数据库连接

            1、语法格式

                对象名=pymysql.connect('主机地址','用户名','密码','库名(可省略)',charset='utf8')

            2、connect连接对象支持的方法

cursor()	创建一个游标对象 对象名.cursor()
commit()	提交到数据库执行(表记录增删改)
rollback()	回滚
close()	关闭数据库系统

            3、游标对象支持的方法

execute('SQL命令')	执行SQL命令
fetchone()	取得结果集的第一条记录
fetchmany(n)	取得结果集的n条记录
fetchall()	取得结果集的所有记录
close()	关闭游标对象

8、MySQL workbench(图形化界面管理工具)

9、ER模型 & ER图

    1、定义

        ER模型既 实体 - 关系 模型
        ER图既 实体 - 关系 图

    2、三个概念

        1、实体

        2、属性

        3、关系

            1、定义：实体之间的关系

            2、分类
                一对一关系(1:1):班级和班长
                一对多关系(1:n):公司和职工、班级和学生
                多对多关系(m:n):学生和课程

        4、ER图的绘制

            1、矩形框代表实体，菱形框代表关系，椭圆形代表属性