Mysql数据库之——多表查询、事务、DCL

发表于 更新于 分类于 阅读次数： 本文字数： 2.6k 阅读时长 ≈ 2 分钟

多表查询

  • 语法
1
2
3
4
5
6
select 
	列名列表
from
	列名列表
where
	···

ps：多表查询就是from后面写n个表的名称，用逗号隔开

例子：查询dept表和emp表

1
2
select * from dept, emp；
-- 查询后有很多无用的数据

查询后的结果条数是两个表的记录条数积，也叫做——笛卡尔积。

笛卡尔积

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

多表查询分类

其实就是为了去除查询到的那些无用的数据

  • 内连接查询
  • 外连接查询
  • 子查询

内连接查询

查询条件成立的部分数据（交集）

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

例子：

1
2
3
4
5
6
7
SELECT
	t1.name,
	t2.name
FROM
	dept t1, emp t2
WHERE
	t1.did = t2.eid;
  • 显示内连接

语法：

1
2
3
4
5
6
7
8
9
SELECT
	字段列表
FROM
	表名1
[INNER]
JOIN
	表名2
ON
	条件

  • 内连接查询需要知道的

    • 条件是什么
    • 从哪些表中查询数据
    • 查询哪些字段

  • 注意事项：

    • 需要能连接成功才能查询出来，如果连接不成功，则认为那条数据非法，所以不显示
    • 例如：刚进入学的同学还没有分班级，所以使用内连接查询两张表时，就不会显示这个刚入学同学的信息，如果想查询出来，就需要使用外连接了

外连接查询

  • 左外连接

    查询的是左表所有数据以及其交集部分（交集部分就是判断条件中条件成立的数据）
    语法

    1
    select 字段列表from 表1 left [outer] join 表2 on 条件;

  • 右外连接

    查询的是右表所有数据以及其交集部分，其实就是右表所有记录，和条件成立的部分

语法

1
select 字段列表from 表1 right [outer] join 表2 on 条件;

子查询

概念：查询中嵌套查询

例子：查询工资最高人员

1
SELCET * FROM emp WHERE emp.salary = (SELECT MAX(salary) FROM emp);

解析：首先查询出最高工资是多少，然后查询拥有最高工资的人是谁

  • 子查询的不同情况
    • 子查询的结果是单行单列的

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

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

      可以使用 in 来用作集合判断

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

      子查询可以作为一张虚拟的表来用作查询

事务

事务的基本介绍

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

例子：张三给李四转账500元
1、查询张三账户余额是否大于500
2、张三账户 金额 - 500
3、李四账户 金额 + 500

在这个业务操作时，我们就需要使用到事务管理，只要一个不成功，系统就会自动回滚，让所有事务中的业务代码都不生效

事务的操作步骤

  • 开启事务：strat transaction;
  • 回滚：rollback;
  • 提交：commit;
1
2
3
4
5
strat transaction;
业务逻辑sql语句

rollback;
commit;
  • Mysql数据库中事务会默认提交，Oracle数据库默认是手动的。

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

    • 事务提交的两种方式

      自动提交：MySql就是自动提交的，一条DML语句就会自动提交一次

      手动提交：需要先开启事务，然后再commit提交

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

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

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

事务的四大特征ACID（面试题）

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

事务的隔离级别（面试）

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

存在问题

  • 脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

  • 不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。

  • 幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

隔离界别（面试）

  • read uncommitted：读未提交

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

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

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

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

    产生问题：幻读

  • serializable：串行化（类似于java多线程加上锁，一个事务没结束另一个事务暂时不能操作当前表）

    可以解决所有问题

注意事项：隔离级别从小越来越高，但是效率越来越低，所以我们就需要设置一个合理的隔离级别

查询与设置隔离级别

  • 查询：

    select @@tx_isolation;

  • 设置：

    set global transaction isolation leven 级别字符串;

DCL用户管理

  • 查询用户

    1、切换到mysql数据库
    2、查询user表

  • 创建用户

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

  • 删除用户

    drop user “用户名”@“主机名”;

  • 修改用户密码

    uodate user set password = password(“新密码”) where user = “用户名”;
    或者：set password for “用户名”@“主机名” = password(“新密码”);

  • 权限管理

    • 查询权限：show grants for “用户名”@“主机名”;
    • 授予权限：grant 权限列表 on 数据库.表名 to “用户名”@“主机名”;

  • 撤销权限：revoke 权限列表 on 数据库名.表名 from “用户名”@“主机名”;

  • 通配符

    权限通配符：all
    数据库与表的通配符：*

如果mysql中忘记root用户密码怎么办？

1、cmd执行：net stop mysql
2、启动无验证方式启动mysql服务：mysql –skip-grant-tables
3、手动结束服务进程
4、启动mysql服务：net start mysql

通配符%：表示在任意主机可以登录