MySQL多表、事务

多表查询

• 笛卡尔积：

  • 有两个集合A、B取这两个集合的所有组成情况

  • 要完成多表查询，需要消除无用的数据

• 多表查询分类：

  • 内连接查询：

    • 隐式内连接：使用where条件消除无用数据

    • 显式内连接：

      • 语法：select 字段列表 from 表名1 [inner] join 表名2 on 条件；

    • 注意事项：

      • 从哪些表中查询数据

      • 条件是什么

      • 查询哪些字段

  • 外连接查询：

    • 左外连接：

      • 语法：select 字段列表 from 表1 left [outer] join 表2 on 条件；

      • 查询的是左表所有数据及其交集部分

    • 右外连接

      • 语法：select 字段列表 from 表1 right [outer] join 表2 on 条件；

      • 查询的是右表所有数据及其交集部分

  • 子查询：

    • 概念：查询中嵌套查询，称嵌套查询为子查询

    • 子查询的结果是单行单列的：

      • 子查询可以作为条件，使用运算符去判断。 运算符：>、>=、<、<=、=

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

      • 子查询可以作为条件，使用运算符in来判断

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

      • 子查询可以作为一张虚拟表。

事务

• 事务的基本介绍

  • 概念：

    • 如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败

  • 操作：

    • 开启事务：start transaction;

    • 回滚：rollback;

    • 提交：commit;

  • MySQL数据库中事务默认自动提交

    • 事务提交的两种方式：

      • 自动提交：

        • mysql就是自动提交的

        • 一条DML（增删改）语句会自动提交一次事务

      • 手动提交：

        • Oracle数据库默认是手动提交事务

        • 需要先开启事务，在提交

    • 修改事务的默认提交方式：

      • 查看事务的默认提交方式：SELECT @@autocommit; ---1代表自动提交 ---0代表手动提交

      • 修改默认提交方式：set @@autocommit = 0；

• 事务的四大特征

  • 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败

  • 持久性：当事务提交或回滚后，数据库会持久化的保存数据

  • 隔离性：多个事务之间，相互独立

  • 一致性：事务操作前后，数据总量不变

• 事务的隔离级别（了解）

  • 概念：多个事务之间是隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题

  • 存在问题：

    • 脏读：一个事务，读取到另一个事务中没有提交的数据

    • 不可重复读（虚读）：在同一个事务中，两次读取的数据不一样。

    • 幻读：一个事务操作（DML）数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改

  • 隔离级别：

    • read uncommitted：读未提交

      • 产生的问题：脏读、不可重复读、幻读

    • read committed：读已提交（Oracle默认）

      • 产生的问题：不可重复读、幻读

    • repeatable read：可重复读（MySQL默认）

      • 产生的问题：幻读

    • serializable：串行化

      • 可以解决所有问题

    • 注意：隔离级别从小到大安全性越来越高，但是效率越来越低

    • 数据库查询隔离级别：

      • select @@tx_isolation;

    • 数据库设置隔离级别：

      • set global transaction isolation level 级别字符串；

DCL

• SQL分类：

  • DDL：操作数据库和表

  • DML：增删改表中数据

  • DQL：查询表中数据

  • DCL：管理用户，授权

• DBA：数据库管理员

• DCL：管理用户，授权

  • 管理用户

    • 添加用户：

      • 语法：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'；

    • 删除用户：

      • 语法：DROP USER '用户名'@'主机名';

    • 修改用户密码：

      • 语法：

        • UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名'；

        • SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码')；

      • mysql中忘记了root用户密码，怎么办？

        • cmd --> net stop mysql 停止MySQL服务

          • 需要管理员运行该cmd

        • 使用无验证方式启动mysql服务：mysqld --skid-grant-tables

        • 打开新的cmd窗口，直接输入mysql命令，敲回车。就可以登录成功

        • use mysql；

        • update user set password = password('新密码') where user = 'root';

        • 关闭两个窗口

        • 打开任务管理器，手动结束mysqld.exe的进程

        • 启动mysql服务

        • 使用新密码登录

    • 查询用户：

      • 切换到mysql数据库

        • use mysql；

      • 查询user表

        • select * from user；

      • 通配符：% 表示可以在任意主机使用用户登录数据库

  • 权限管理：

    • 查询权限：

      • 语法：

        • SHOW GRANTS FOR '用户名'@'主机名';

    • 授予权限：

      • 语法：

        • grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

    • 撤销权限：

      • 语法：

        • revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';