2020.4.24 Mysql day03

发布于 2020-04-24  43 次阅读


1.复习内容

2.灵感代办

  • from和on的使用总结

3.学习内容

  • mysql高级

    • 常用的三个存储引擎

      • innodb(事务,行级锁,外键)
      • MyISAM
      • MEMORY (内存存储)
    • 存储引擎

      • 定义:
        • 处理表的处理器
      • 1.查看所有引擎
        • show engines;
      • 2.查看已有表的存储引擎
        • show create table 表名;
      • 3.创建表时候指定
        • create table 表名(…) engine=MyISAM;
      • 4.已有表指定
        • alter table 表名 engine=InnoDB
    • 重点:常用存储引擎的特点

      • InnoDB

        • 支持行级锁(锁某一条记录)
        • 支持外键,事务相关操作(事务,事务回滚)
        • 表字段索引在同一个文件中
          • 1.表名.frm :表结构以及索引
          • 表名.ibd 表数据
      • MyISAM

        • 支持表级锁
        • 表字段和索引分开存储
          • 1.表名.frm 表结构
          • 2.表名.myi: 索引文件(my index)
          • 3.表名.myd:表记录(my data)
      • MEMORY

        • 表记录存储在内存中,效率高
        • 服务或主机重启,表记录清除
        • 内存的大小受限,不能存太多数据
      • 如何选择存储引擎

        • 1.查找多的使用 myISAM
        • 2.修改多的使用 InnoDB
        • 3.临时表 MEMORY
  • MySQL的用户账户管理

    • 开启远程Mysql远程连接

      • 改配置文件
    • 添加授权用户

      • 1.用root用户登录mysql

        • mysql -uroot -p
      • 2.授权

        • grant 权限列表 on 库.表 to “用户名”@”%” identified by “密码” with grant option;
        • with grant option; 是被授权用户可以继续执行grant
      • 3.刷新权限

        • flush privileges
      • 权限列表:

        • all privileges 、select 、insert ..
        • 库.表: . 代表所有库的所有表
      • mysql8.0创建权限已经改变

        • 创建用户和赋予权限需要分离
          • CREATE USER 'mike'@'%' IDENTIFIED BY '000000';
          • GRANT ALL ON . TO 'mike'@'%' WITH GRANT OPTION;
  • 事务和事务回滚

    • 事务的定义:

      • 一件事情从始至终都做完,这就成功,失败一件事,都全部失败撤回
    • 事务的作用

      • 例如银行转账,钱不能丢
      • 确保数据的一致性,准确,有效
    • 事务操作

      • 1.开启事务

        • 方法1:begin
        • 方法2:start transaction;
      • 2.开启事务中的命令

        • 增删改命令
      • 3.终止事务

        • commit 命令全部执行成功,提交数据库操作
        • rollback 回滚操作 有sql执行失败,回到之前的顺序
    • 事务的四大特性(ACID):

      • 原子性(atomicity)

        • 将多条sql命令看成是一条“命令”,其中一个环节失败,就视为这一条“命令”失败
      • 一致性()

        *

      • 隔离性

        • 事务只要没提交,里面执行的命令,只能在当前会话看到,别的会话看到还是之前的数据,当commit之后,数据才统一。
      • 持久性

        • 事务提交之后,修改的数据就被永久保存了
    • 事务注意点

      • 事务只针对表记录操作有效,库和表的操作无效
      • 事务一旦提交,对数据库的更改就是永久的
  • E-R模型(Entry -Relationship)

  • SQL优化

    • 存储引擎优化:
      • 1.读操作多
      • 2.写操作多
    • 索引优化:
      • 1.常查看的表建立索引
      • 2.常where的表建立索引
      • 3.常order by的建立索引
    • SQL语句优化:
      • 1.单挑查询最后添加limit 1,停止全表扫描
      • 2.where子句中不适用 != ,否则放弃索引,全表扫描
      • 3.尽量避免NULL值判断,否则放弃索引,全表扫描,优化方法就是设置default 为 0
      • 4.尽量避免or连接条件,否则放弃索引,全表扫描
      • 5.模糊查询尽量避免使用前置%,否则全表扫描(“%a%”)
      • 6.尽量避免使用in和not in,否和全表扫描
      • 7.尽量避免使用select *,*用指定字段替换

4.扩展延伸知识

  • auto_increment自增长

    • alter table t1 auto_increment=1000;
  • mysql8.0创建权限

  • mysql数据库执行命令就提交

    • show variables like ‘autocommit’
    • 事务开启之后 默认关闭autocommit
    • oracle默认没有开启
  • mysql union all

    • UNION 语句:用于将不同表中相同列中查询的数据展示出来;(不包括重复数据)
  • UNION ALL 语句:用于将不同表中相同列中查询的数据展示出来;(包括重复数据)


Ares个人进阶之路