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（'新密码'）

    ​

    ​