MySQL多表查询与事务操作(MySQL这三篇足以)

第三篇

  1. 多表查询的分类

    • 内连接
      • 隐式内连接
      • 显示内连接
    • 外连接
      • 左外连接
      • 右外连接
  2. 内连接

    用左边表的记录匹配右边表的记录,如果符合条件的则显示

    • 隐事内连接:看不到JOIN关键字,条件使用WHERE指定

      select 字段名 from 左表,右表 where 条件

    • 显式内连接:使用inner join …on 语句,可以省略 inner

      select 字段名 from 左表 [inner] join 右表 on 条件

  3. 总结内连接查询步骤

    • 确定查询那些表
    • 确定表连接的条件
    • 确定查询的条件
    • 确定查询的字段
  4. 左外连接:使用left outer join …on,outer可以省略

    select 字段名 from 左表 left [outer] join 右表 on 条件

    用左边表的记录去匹配右边表的记录,如果符合条件的则显示,否则显示null

    可以理解为:在内连接的基础上保证左表的数据全部显示

  5. 右外连接:使用right outer join …on,outer可以省略

    select 字段名 from 左表 right [outer] join 右表 on 条件

    用右边表的记录去匹配左边表的记录,如果符合条件的则显示,否则显示null

    可以理解为:在内连接的基础上保证左表的数据全部显示

  6. 子查询的概念

    • 一个查询的结果作为另一个查询的条件
    • 有查询的嵌套,内部查询称为子查询
    • 子查询要使用括号
  7. 子查询的结果

    • 子查询的结果是一个值

      子查询结果只要是单行单列,肯定在 WHERE 后面作为条件,父查询使用:比较运算符,如:> 、<、<>、 = 等

      select 查询字段 from 表 where 字段=(子查询);

    • 子查询的结果是多行单列时

      子查询的结果是多行单列,结果类似于一个数组,父查询用in运算符

      select 查询字段 from 表 where 字段 in (子查询)

    • 子查询的结果是多行多列

      子查询的结果只要是多列,肯定在from后面作为表

      select 查询字段 from (子查询) 表别名 where 条件;

      子查询作为表需要取别名,否则这张表没有名称无法访问表中的字段

  8. 事务

    事务执行是一个整体,所有的 SQL 语句都必须执行成功。如果其中有 1 条 SQL 语句出现异常,则所有的 SQL 语句都要回滚,整个业务执行失败

  9. 手动提交事务

    • 开启事务:start transaction
    • 提交事务:commit
    • 回滚事务:rollback
  10. 自动提交事务

    ​ MySQL 默认每一条 DML(增删改)语句都是一个单独的事务,每条语句都会自动开启一个事务,语句执行完毕 自动提交事务,MySQL 默认开始自动提交事务

    ​ 查看mysql是否开启自动提交事务:select @@autocommit;

    ​ 取消自动提交事务:set @@autocommit = 0;

  11. 事务的步骤

    • 客户端连接数据库服务器,创建连接时创建此用户临时日志文件
    • 开启事务以后,所有的操作都会先写入到临时日志文件中
    • 所有的查询操作从表中查询,但会经过日志文件加工后才返回
    • 如果事务提交则将日志文件中的数据写到表中,否则清空日志文件。
  12. 回归点

    设置回滚点:savepoint 名字

    回到回滚点:rollback to 名字

    设置回滚点可以让我们在失败的时候回到回滚点,而不是回到事务开启的时候

  13. 事务的隔离级别

    • 原子性(Atomicity):每个事务都是一个整体,不可再拆分,事务中所有的 SQL 语句要么都执行成功, 要么都失败。
    • 一致性(Consistency):事务在执行前数据库的状态与执行后数据库的状态保持一致
    • 隔离性(Isolation):事务与事务之间不应该相互影响,执行时保持隔离的状态
    • 持久性(Durability):一旦事务执行成功,对数据库的修改是持久的
  14. 事务的隔离级别

    事务在操作时的理想状态:所有事物之间保持隔离,互不影响,因为并发操作,多个用户同时访问同一个数据,可能引发并发访问的问题

    • 脏读:一个事务读取到了另一个事务中尚未提交的数据
    • 不可重复度:一个事务中两次读取的数据内容不一致,要求的是一个事务中多次读取时数据是一致的,这 是事务 update 时引发的问题
    • 幻读:一个事务中两次读取的数据的数量不一致,要求在一个事务多次读取的数据的数量是一致 的,这是 insert 或 delete 时引发的问题
  15. 数据库的默认隔离级别

    • Oracle 和 SQL Server :脏读
    • MySQL:不可重复读
  16. MySQL的四种隔离级别

    • 读未提交
    • 读已提交
    • 可重复读
    • 串行化
  17. MySQL事务隔离级别相关的命令

    查询隔离级别 select@@tx_isolation;

    设置事务隔离级别,需要退出mysql再登录才能看到隔离级别变化,设置隔离级别:select global transaction isolation level 级别字符串

  18. DCL(Data Control Language)

    mysqld 是 MySQL 的主程序,服务器端。mysql 是 MySQL 的命令行工具,客户端。

  19. 创建用户

    create user '用户名'@'主机名'identified by '密码';

    关键字说明:
    用户名:将创建的用户名

    主机名:指定该用户在哪个主机上可以登陆,如果是本地用户可用 localhost,如果想让该用户可以 从任意远程主机登陆,可以使用通配符%

    密码:该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器

  20. 给用户授权

    用户创建后没什么权限,需要给用户授权

    grant 权限1,权限2...on 数据库名.表名 to '用户名'@'主机名';

  21. 关键字说明

    grant...in...to:授权关键字
    权限:授予用户的权限,如 CREATE、ALTER、SELECT、INSERT、UPDATE 等。如果要授 予所有的权限则使用 ALL
    数据库名.表名:该用户可以操作哪个数据库的哪些表。如果要授予该用户对所有数据库和表的相应操作 权限则可用*表示,如*.* 
    '用户名'@'主机名':给哪个用户,注:有2对单引号
    
  22. 撤销授权

    REVOKE 权限 1, 权限 2... ON 数据库.表名 revoke all on test.* from 'user1'@'localhost'; '用户名'@'主机 名';

  23. 查看权限

    show grants for '用户名'@'主机名';

  24. 删除用户

    drop user '用户名'@'主机名'

  25. 修改管理员密码

    mysqladmin -uroot -p password 新密码

  26. 修改普通用户密码

    set password for '用户名'@'主机名' = password('新密码')

你可能感兴趣的