MySQL多表与事务

2021-09-03 11:23:39

关键词：多表查询；事务； DCL

select * from emp, dept;

笛卡尔集：

由两个集合A，B组成的所有可能情况。

多表查询的分类

1. 内连接查询

隐式内连接
用where语句来消除
SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件
显式内连接：用inner

SELECT * FROM emp e INNER JOIN dept d ON e.dept_id = d.id;

内连接查询注意：
1. 从哪些表查询数据
2. 条件是什么
3. 查询哪些字段

2. 外连接查询

左外连接（常用）
- 语法：
- 查询的左表所有数据以及其交集部分。
右外连接

3. 子查询（很重要）

概念：对查询的结果进行查询。有点类似数学的等价代换。

子查询的不同情况：单行单列、单行多列、多行多列

     ● 查询单行单列的结果；
      子查询作为条件，使用运算符去判断。
      SELECT 查询字段 FROM 表 WHERE 字段=（子查询）;
      eg:员工工资小于平均工资
    
     ● 查询多行单列的结果。
       子查询结果是单例多行，结果集类似于一个数组，父查询使用 IN 运算符。
       就类似于excel的高级筛选功能。
       
     ● 查询多行多列的结果。
       子查询是一张虚拟表。可以很容易地转换为内连接。
       子查询作为表需要取别名，否则这张表没有名称则无法访问表中的字段。

多表查询综合练习（复习时练习）
作业文件夹里面的练习。
有两个视频没看。多表关系可以借助架构关系来看外键跟谁的主键联系的。

事务

1. 事务的基本介绍

概念

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

操作

背景：张三给李四转账的案例。开两个查询窗口，一个执行事务，一个执行普通DQL查询操作。被事务管理了之后，只有提交了之后，真实数据才会改变。
- 开启事务：start transaction;
- 回滚：rollback;
- 提交：commit;
  例子：还是用上面的例子。只不过事先打开两个窗口，一个cmd窗口的mysql，一个是sqlyog窗口。在cmd窗口中连接好数据库，然后开启事务，然后做一下操作，这个时候去sqlyog查看结果；随后在cmd窗口中点击提交，去sqlyog查看结果。
MySQL数据库中，事务会默认自动提交
- 事务提交的两种方式：自动和手动

      MySQL是自动提交的，一条DML语句会自动提交一次事务。
      可以将其修改为手动提交模式，然后输入语句必须commit过后另一个窗口的查询才会更新。方法如下：
      SELECT @@autocommit;
      set @@autocommit = 0;

2. 事务的四大特征

原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。
持久性：当事务提交或回滚后，数据库会持久化地保存数据。
隔离性：多个事务之间，相互独立。
一致性：事务操作前后，数据总量不变

3. 事务的隔离级别（了解）

多个事物之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题。
需要开多个cmd窗口，来看效果
存在的问题：

脏读：
不可重复读（虚读）
幻读

隔离级别（留了一些尾巴，有需要再看）

    1. read uncommitted：读未提交
    	* 产生的问题：脏读、不可重复读、幻读
    2. read committed：读已提交 （Oracle）
    	* 产生的问题：不可重复读、幻读
    3. repeatable read：可重复读 （MySQL默认）
    	* 产生的问题：幻读
    4. serializable：串行化
    	* 可以解决所有的问题

注意：隔离级别从小到大安全性越来越高，但是效率越来越低
数据库查询隔离级别：
- SELECT @@transaction_isolation;(8.0之后的版本要使用这条)
数据库设置隔离级别：
- set global transaction isolation level 级别字符串;
演示：
set global transaction isolation level read uncommitted;
start transaction;
-- 转账操作
update account set balance = balance - 500 where id = 1;
update account set balance = balance + 500 where id = 2;

DCL: 数据控制语言

-- 创建 user1 用户，只能在 localhost 这个服务器登录 mysql 服务器，密码为 123
create user ‘user1‘@‘localhost‘ identified by ‘123‘;
-- 创建 user2 用户可以在任何电脑上登录
create user ‘user2‘@‘%‘ identified by ‘123‘;

-- 要进入mysql数据库才有用
use mysql;
show TABLES;
select * FROM user;

-- 修改密码目前仅至两个办法有效果
ALTER USER ‘root‘@‘localhost‘ IDENTIFIED BY ‘12345678‘;
SET PASSWORD = ‘123‘;

-- 查询权限
show grants for ‘user1‘@‘localhost‘;
show grants for ‘user2‘@‘%‘;

-- 授予权限
GRANT ALL ON *.* TO ‘user1‘@‘localhost‘;

-- 撤销权限 (存在问题)
REVOKE SELECT ON db4.account FROM ‘user1‘@‘localhost‘;