MySQL基础（三）

多表查询
多表查询的概述
多表查询即为从两张或者多张表中查询出相关数据.
准备SQL

#创建公司-部门表
CREATE TABLE DEPT(
  ID INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20) NOT NULL
);

#部门表中插入数据
INSERT INTO DEPT (NAME) VALUES ("开发部"),("市场部"),("财务部");

#创建公司-员工表
CREATE TABLE EMP(
  ID INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(10),
  GENDER CHAR(1),
  SALARY DOUBLE,
  JOIN_DATE DATE,
  DEPT_ID INT,
  CONSTRAINT EMP_DEPT_FK FOREIGN KEY (DEPT_ID) REFERENCES DEPT(ID)
);

#向员工表中插入数据
INSERT INTO EMP VALUES (NULL,"孙悟空","男",7200,"2013-02-24",1);
INSERT INTO EMP VALUES (NULL,"猪八戒","男",3600,"2010-12-02",2);
INSERT INTO EMP VALUES (NULL,"唐僧","男",9000,"2008-08-08",2);
INSERT INTO EMP VALUES (NULL,"白骨精","女",5000,"2015-10-07",3);
INSERT INTO EMP VALUES (NULL,"蜘蛛精","女",4500,"2011-03-14",1);

笛卡尔积
笛卡尔积就是当直接查询两张表中的数据时没有条件限制,每一条A表的数据都会和B表的数据生成一条新的数据,产生了大量的重复无意义多余数据,即为笛卡尔积.

#查询EMP表与DEPT表中的所有数据(笛卡尔积)
SELECT * FROM DEPT,EMP;

多表查询的分类
内连接查询
隐式内连接：隐式内连接使用WHERE条件消除无用的数据.

#内连接-隐氏内连接(查询所有的员工信息和部门信息)
SELECT * FROM EMP,DEPT WHERE EMP.DEPT_ID = DEPT.ID;

#内连接-隐式内连接(查询两张表的对应字段,使用表名.字段名区分)
SELECT EMP.NAME,EMP.GENDER,DEPT.NAME FROM DEPT,EMP WHERE DEPT.ID = EMP.DEPT_ID;

#内连接-隐式内连接(查询两张表的对应字段,使用表名的别名.字段名区分)
SELECT E.NAME,E.GENDER,D.NAME FROM EMP E,DEPT D WHERE E.DEPT_ID = D.ID;

显示外链接
语法格式：SELECT 字段列表 FROM 表名1 INNER JOIN 表名2 ON 条件;

#显示内连接(查询所有的员工信息和对应部门信息)
SELECT * FROM EMP E INNER JOIN DEPT D on E.DEPT_ID = D.ID;


#显示内连接(查询员工的姓名,性别,部门信息)不使用INNER
SELECT E.NAME,E.GENDER,D.NAME FROM EMP E JOIN DEPT D on E.DEPT_ID = D.ID;```

使用内连接查询的步骤：
从那些表查询数据？
两张/多张表查询数据，那么表与表之间关联的条件是什么？
查询两张/多张表的那些字段？


**左外链接**
语法格式：SELECT 字段列表 FROM 表1 LEFT JOIN 表2 ON 条件;

```sql
#查询所有员工的姓名,性别以及部门信息,如果员工有部门则显示部门,如果员工没有部门则不显示.
SELECT E.NAME,E.GENDER,D.NAME FROM EMP E LEFT JOIN DEPT D on E.DEPT_ID = D.ID;

左外链接查询的是左表中的所有数据以及交集的部分。

右外链接
语法格式：SELECT 字段列表 FROM 表1 RIGHT JOIN 表2 ON 条件;

#查询所有部门的信息以及部门对应的员工,如果员工没有部门,则不显示.
SELECT D.NAME,E.NAME,E.GENDER FROM EMP E RIGHT JOIN DEPT D on E.DEPT_ID = D.ID;

右外链接查询的是右表中的所有数据以及交集部分。

子查询
概述：查询中嵌套查询即为子查询（嵌套的查询为子查询）

/*
查询SALARY最高的员工信息.
1.查询最高的工资数据MAX(SALARY)
2.查询SALARY与上一条结果的相等的员工信息.
 */
SELECT MAX(SALARY) FROM EMP; #9000
SELECT * FROM EMP WHERE SALARY = 9000;

#子查询-查询工资最高的员工信息
SELECT * FROM EMP WHERE SALARY = (SELECT MAX(SALARY) FROM EMP);

子查询的不同情况

子查询的结果是单行单列，可以使用运算符去判断

#子查询-查询SALARY低于平均SALARY的员工信息.
SELECT * FROM EMP WHERE SALARY < (SELECT AVG(SALARY) FROM EMP);

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

#子查询-查询财务部和市场部的所有员工信息.
SELECT * FROM EMP WHERE DEPT_ID IN (SELECT DEPT.ID FROM DEPT WHERE DEPT.NAME = "财务部" OR DEPT.NAME = "市场部");

子查询的结果是多行多列，子查询可以作为一张虚拟表来进行查询。

#子查询-查询员工入职日期是2011-11-11日之后的员工信息和部门信息.
SELECT * FROM DEPT D,(SELECT * FROM EMP WHERE JOIN_DATE > "2011-11-11") E WHERE D.ID = E.DEPT_ID;

虚拟表要起一个别名，否则无法在之后的WHERE条件中使用。

事务

事务概述：如果一个包含了多个步骤的业务操作被事务管理，那么这些操作要么同时成功，要么同时失败。
事务的操作：
开启事务：START TRANSACTION
回滚事务：ROLLBACK
提交事务：COMMIT
事务提交的两种方式
手动提交：需要事先开启事务再进行提交。
自动体检：MYSQL默认的就是自动体检，一条DML语句会自动提交一次事务。

#查看事务的默认提交方式
SELECT @@AUTOCOMMIT;  #结果为1代表自动提交,结果为0代表手动提交.


#修改事务的默认提交方式
SET @@AUTOCOMMIT = 0;```

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

事务的隔离级别
多个事务之间是隔离的，相互独立的，但是如果多个事务操作统一数据，则会引发一些问题，通过设置不同的隔离级别就可以解决这些问题。
事务的隔离级别存在的问题：
脏读：一个事务读取到另一个事务中没有提交的数据。
不可重复读：在同一事务中两次读取到的数据不一样。
幻读：一个事务操作数据表中的所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。

MYSQL中的隔离级别
READ UNCOMMITED：读未提交
产生的问题：脏读/不可重复读/幻读
READ COMMITED：读已提交
产生的问题：不可重复读/幻读
REPEATABLE READ：可重复读
产生的问题：幻读
SERIALIABLE：串行化
解决所有问题
隔离级别从小到大安全性越来越高，效率越来越低

```sql
#查看MYSQL的默认隔离级别
SELECT @@tx_isolation;

#设置当前MYSQL的隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL 级别字符串;