MySQL多表&事务

1、多表关系

1.1关系分类

1.1.1一对多关系

1.部门和员工

2.分类和商品

3.班级和学生
4.用户和订单

1.1.2多对多关系

1. 学生和课程
2. 订单和商品
3. 老师和学生

1.1.3一对一关系

1. 身份证和人
2. 夫妻

1.2表关系创建

1.2.1一对多关系

在多表一方添加一个外键,指向主表的主键
注意: 外键的数据类型和长度 必须 和主表主键的类型和长度完全一样

1.2.2多对多关系

创建一个中间表,起码有两个字段分别作为外键,指向另外两个表的主键. 多对多关系,实际上拆分为两个一对多关系

1.2.3一对一关系

合二为一

2、多表查询★

2.1基本语法:

select 列列表 from 表列表 [where 条件]

2.2分类 ★

2.2.1笛卡尔积 : 两个表所有组合可能, 但是数据有效性极低 – 了解

2.2.2内连接查询

1. 隐式内连接查询
   select * from 表A,表B where 条件;
2. 显示内连接查询
   select * from 表A [ inner] join 表B on 条件;

2.2.3外连接查询

1. 左外连接查询
   select * from 表A left [outer] join 表B on 条件;

2. 右外连接查询
   select * from 表A right [outer ]join 表B on 条件;

   2.2.4查询方式的区别

   1.内链接查询: 查询两个表的交集部分

   2.左外连接查询: 主要查询左表的所有数据,若右表有数据则显示,没有显示为NULL

3. 右外连接查询: 主要查询右表的所有数据,若左表有数据则显示,没有显示为NULL

2.2.5子查询

​ 概念: 查询中嵌套查询，称嵌套查询为子查询(一个SQL作为另一个SQL的一部分)
​ 情况：

1.子查询的结果是单行单列的，子查询可以作为条件，使用运算符去判断。 运算符： > >= < <= =

2.子查询的结果是多行单列的，子查询可以作为条件，使用运算符in来判断

3.子查询的结果是多行多列的(虚拟表)，子查询可以作为一张虚拟表参与查询

3、事务

3.1概念:

逻辑上的一组操作,组成这组操作的各个单元,要么都成功,要么都失败

3.2MySQL事务操作

1. 开启事务： start transaction;
2. 执行一组操作
3. 回滚：rollback;
   失败
4. 提交：commit;
   成功

3.3事务四大特征

A 原子性: 一个事务不可分割的最小单元,要么都成功,要么都失败
C 一致性: 一个事务的前后,数据总量不变.
I 隔离性: 多个事务操作同一批数据,引发的问题
D 持久性: 一个事务一但被提交/回滚, 数据库会持久化的保存数据,这个过程是不可逆.

3.4事务并发问题

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

2.不可重复读(虚读)：在同一个事务中，两次读取到的数据不一样。

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

3.5隔离级别 --了解

3.5.1读未提交 read uncommitted

1.产生问题
脏读、不可重复读、幻读
2.解决问题
0个

3.5.2读已提交 read committed

1.产生问题
不可重复读、幻读
2.解决问题
赃读

3.5.3可重复读 repeatable read

1.产生问题
幻读
2.解决问题
赃读、不可重复读

3.5.4串行化 serializable

1.产生问题
0个
2.解决问题
脏读、不可重复读、幻读

4、DCL 数据控制语言

4.1管理用户

4.1.1添加用户

语法：CREATE USER ‘用户名’@‘主机名’ IDENTIFIED BY ‘密码’;

4.1.2删除用户

语法：DROP USER ‘用户名’@‘主机名’;

4.1.3修改用户密码

1. 修改普通用户
   语法: UPDATE USER SET PASSWORD = PASSWORD(‘新密码’) WHERE USER = ‘用户名’;
   例如: UPDATE USER SET PASSWORD = PASSWORD(‘abc’) WHERE USER = ‘lisi’;

2. root用户

   1. cmd – > net stop mysql 停止mysql服务
      注意: 需要管理员运行该cmd

   2. 使用无验证方式启动mysql服务： mysqld --skip-grant-tables

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

   4. use mysql;

   5. update user set password = password(‘你的新密码’) where user = ‘root’;

   6. 关闭两个窗口

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

   8. 启动mysql服务

   9. 使用新密码登录。

4.1.4查询用户

3. 切换到mysql数据库
   USE myql;

4. 查询user表
   SELECT * FROM USER;

4.2权限管理

4.2.1查询权限

语法: SHOW GRANTS FOR ‘用户名’@‘主机名’;
例如: SHOW GRANTS FOR ‘lisi’@’%’;

4.2.2授予权限

语法: grant 权限列表 on 数据库名.表名 to ‘用户名’@‘主机名’;
例如: GRANT ALL ON . TO ‘zhangsan’@‘localhost’;

4.2.3撤销权限

语法: revoke 权限列表 on 数据库名.表名 from ‘用户名’@‘主机名’;
例如: REVOKE UPDATE ON db3.account FROM ‘lisi’@’%’;