MySql多表查询、事务、DCL

一、多表查询
概述：
　　一次查询多张数据库表。
分类：内连接查询，外链接查询，子查询
　　实际应用中尽量避免使用多表查询，数据量大的情况下影响效率。

1.1、内连接查询
概述：
　　内连接是从结果表中删除与其他被连接表中没有匹配行的所有行，所以内连接可能会丢失信息。
　　分类：隐式内连接、显示外连接
笛卡尔积：

select * from emp,dept  ==》没有条件限定，打印所有的组成情况。 ==》查询多表没有限定条件。

　　1、有两个集合AB，取两个集合的所有组成情况。
　　2、要完成多表查询，需要消除无用的数据。

1.1.1、隐式内连接
概述：
　　使用where条件消除无用数据
怎么解决笛卡尔积？
　　隐式外连接where通过外键与另一张表建立联系，使用where条件消除无用数据。 ---> 条件设置为量两表的关联数据(如外键和主键)。

1.1.2、显式内连接
语法：
　　select 字段列表 from 表名1 [inner] join 表名2 on 条件；
--> 跟where的作用差不多，但是显式内连接的时候要写on

SELECT emp.`NAME`, emp.gender, dept.`NAME` FROM emp INNER JOIN dept ON emp.dept_id = dept.id;

1.2、外连接查询
概述：
　　外连接分为：左外连接，右外连接
　　在左外连接和右外连接时都会以一张表为基表，该表的内容会全部显示，然后加上两张表匹配的内容。如果基表的数据在另一张表没有记录。那么在相关联的结果集行中列显示为空值。

1.2.1、左外链接：

SELECT * FROM emp LEFT OUTER JOIN dept ON emp.dept_id = dept.id;

　　则以emp为主，dept为辅，为辅展示和主表的交集部分。==》为辅的表的交集行的所有列信息都会显式，不一定显示所有行。
　　如果左表某行在右表没有对应行，则在相关联的结果集行中，有表中的所有选择列表列均为空值。
语法：

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

1.2.2、右外连接
语法：

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

1.3、子查询
概念：
　　查询中嵌套查询，称嵌套查询为子查询。(子查询的结果存在不同的情况）

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

查询员工信息，并且工资等于9000的
SELECT * FROM emp WHERE emp.`salary` = 9000;
SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp);

WHERE后不能使用聚合函数  不能写  WHERE xx < (AVG(salary) FROM emp); 
应该写  WHERE emp.`salary` = (SELECT AVG(salary) FROM emp);

1.3.2、子查询的结果是多行多列的：
　　子查询可以作为一张虚拟表参与查询 

1.3.3、子查询的结果是多行单列的：
子查询可以作为条件，使用运算符in来判断  ---> 结果可以放在集合中。

查询'财务部'和'市场部'所有的员工信息
SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部';
SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;
子查询
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');

　　sql中，一个SELECT-FROM-WHERE语句称为一个查询块。
　　当获得一个查询的答案需要多个步骤的操作，首先必须创建一个查询来确定用户不知道但包含在数据库中的值，将一个查询块嵌套在另一个查询块的WHERE字句或HAVING短语的条件中查询块称为子查询或内层查询。

附注：
　　WHERE和GROUP BY位置随意前后。
　　ON不能使用WHERE来替换。
　　子查询是多行多列，可以想象成在多张表的基础上，提取出想要的数据，然后组成一张新的表。

二、事务
2.1、事务的基本介绍
2.1.1、概述
　　含有多条sql语句的一个原子性操作。
　　如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。
　　持久化的层，展示到网站上东西。事务存在于持久层。
2.1.2、操作
　　1、开启事务：start transaction；
　　2、回滚：rollback  --> 程序中存在sql语句执行失败，需执行回滚。
　　3、提交：commit

2.1.3、MySql中事务默认自动提交
事务提交的两种方式：
　　1、自动提交
　　2、手动提交
　　Oracle数据库默认是手动提交事务，需要先开启事务，再提交。

查看事务的默认提交方式：

SELECT @@autocommit; -- 1 代表自动提交  0 代表手动提交

修改默认提交方式：

set @@autocommit = 0;

使用控制台命令：
　　开启事务：start transaction | begin
　　提交事务：commit
　　回滚事务：rollback

2.2、事务的四大特性
　　1、原子性Atomicity：是不可分割的最小操作单位，要么同时成功，要么同时失败。
　　2、一致性Consistency：事务操作前后，数据总量不变。==》一个状态转变成另一个状态，数据一致。
　　3、隔离性Isolation：多个事务之间。相互独立。==》并发事务之间要相互隔离。多线程访问数据库的安全问题。
　　4、持久性Durability：当事务提交或回滚后，数据库会持久化的保存数据。

2.3、事务的隔离级别
概念：
　　多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。
存在的问题：
1、脏读：一个事务，读取到另一个事务中没有提交的数据。
　  两个事务，对方的事务尚未提交，我方查询的时候可以了看到对方增删改的结果。　 
　  这时如果对方rollback回滚，而不是提交，就会出现问题。
2、不可重复读(虚读)【不可重复读(到不一样的数据)】：A在同一个事务中，两次读取到的数据不一样。---> 即对方UPDATE的语句。脏读会引起不可重复读。
　  一些场景下虚读(不可重复读(到不一样的数据))是合理的。但是
　  有些业务需要在一次事务中读取的数据一样。当该事务结束了，才要求看到其他事务对表的改变。
　  报表：==>做好了--又改变了==>一次事务中查了两次，第一次对方未提交交时查，后一次对方提交后查，两次查询的结果不一样。
3、幻读：一个事务操作(DML)数据库中所有记录，另一个事务添加了一个数据，则另一个事务查询不到自己的修改。---> 即对方的INSERT语句()。
　  我本次的事务尚未提交，【可能】(因为数据库对隔离级别的处理有所差别)读取到对方INSERT语句的数据。

select @@tx_isolation;

数据库设置隔离级别：

set global transaction isolation level 级别字符串；

附注：
　　serialzable：一次只处理一个事务，但是效率很低。只有对方事务提交或者回滚，我方才能操作。
　　如果读一些数据只是读，不修改，可以降低隔离级别。这样来提高效率。

三、DCL数据库控制语言
DCL：用户管理，授权，权限管理。
　　本质是修改mysql系统中的表
DBA：Database Administrator数据库管理员。
DCL：管理用户，授权 --> 可以进行细粒度权限的划分。

3.1、管理用户
3.1.1、添加用户：

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

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

3.1.2、删除用户

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

3.1.3、修改用户密码

语法：UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';

mysql中忘记了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>使用新密码登录。

3.1.4、查询用户：
　　1、切换到mysql数据库　　USE myql;
　　2、查询user表　　SELECT * FROM USER;

3.2、权限管理
3.2.1、查询权限

SHOW GRANTS FOR '用户名'@'主机名'；

3.2.2、授予权限
grant 权限列表 on 数据库.表名 to '用户名'@'主机名'；

GRANT ALL ON *.* to 'zhangsan'@'localhost'；

3.2.3、撤销权限
revoke 权限列表 on 数据库.表名 from '用户名'@'主机名'；

REVOKE UPDATE ON *.* FROM 'zhangsan'@'主机名'；