MySQL-小白日志:多表查询和事务

表连接查询

多表查询的分类：

两个表数据

CREATE TABLE dept(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
);
CREATE TABLE emp(
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(10),
	gender CHAR(1),
	salary DOUBLE,
	join_date DATE,
	dept_id INT,
	FOREIGN KEY (dept_id) REFERENCES dept(id)	
)
# 插入数据
insert into dept(name) values('开发部'),('市场部'),('财务部');
insert into emp(NAME,gender,salary,join_date,dept_id) values('孙悟空','男',7200,'2013-02-24',1);
insert into emp(NAME,gender,salary,join_date,dept_id) values('猪八戒','男',3600,'2010-12-02',2);
insert into emp(NAME,gender,salary,join_date,dept_id) values('唐僧','男',9000,'2008-08-08',2);
insert into emp(NAME,gender,salary,join_date,dept_id) values('白骨精','女',5000,'2015-10-07',3);
insert into emp(NAME,gender,salary,join_date,dept_id) values('蜘蛛精','女',4500,'2011-03-14',1);

内连接

隐式内连接

看不到关键字 JOIN，条件使用WHERE 指定

# 语法
SELECT 字段名 FROM 表1,表2 WHERE 条件
# 例如
SELECT * 
FROM emp,dept 
WHERE emp.dept_id = dept.id;

显示内连接

使用 INNER JOIN … ON语句 ，INNER可以省略

#语法
SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件
# 例如:查询孙悟空的信息，显示员工 id，姓名，性别，工资和所在的部门名称，
# 1 确定连接的表
SELECT * 
FROM emp INNER JOIN dept;
# 2 确定表的连接条件
SELECT * 
FROM emp INNER JOIN dept ON emp.dept_id = dept.id;
# 3 确定查询信息
SELECT emp.id,emp.name 姓名,gender 性别, salary 工资, dept.name 部门
FROM emp INNER JOIN dept ON emp.dept_id = dept.id 
WHERE emp.name = '孙悟空';

显示内连接查询步骤

1. 确定哪些表
2. 确定表的条件
3. 确定查询的条件和字段

左外连接

使用 LEFT OUTER JOIN … ON, OUTER可以省略

用左边表的记录去匹配右边表的记录，如果符合条件的则显示；否则，显示 NULL
可以理解为在内连接的基础上保证左表数据的全部显示

# 语法
SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件
# 示例
SELECT * 
FROM emp e LEFT JOIN dept d ON e.dept_id = d.id; 

右外连接

使用 RIGHT OUTER JOIN … ON ,OUTER 可以省略的

用右边表的记录去匹配左边表的记录,如果符合条件的则显示；否则，显示NULL
可以理解为在内连接的基础上保证左表数据的全部显示

# 语法
SELECT 字段名 FRON 左表 RIGHT [OUTER] JOIN 右表 ON 条件;
# 示例
SELECT * 
FROM emp e RIGHT JOIN dept d ON e.dept_id = d.id;

个人理解：可以使用其中一个外连接，改变左右表 ，同样也可以做到左外连接和右外连接.

子查询

子查询概念

1. 一个查询的结果作为另外一个查询的条件
2. 有查询的嵌套，内部的查询称为子查询

子查询的三种结果

1. 单行单列
2. 多行单列
3. 多行多列

子查询的结果作为一个值时

子查询的结果只要是单行单列，肯定在WHERE 后边作为条件,父查询可以用:比较运算符等

# 语法
SELECT 字段名 FROM 表 WHERE 字段 = (子查询);
# 例如：查询工资最高的员工是谁？
# 1 先查询最高工资
SELECT MAX(salary) 
FROM emp;
# 2 在根据最高工资查询对应的员工
SELECT emp.name 姓名,salary 工资 
FROM emp 
WHERE salary = (SELECT MAX(salary) FROM emp);

子查询结果为多行单列时

子查询结果为多行单列时, 类似于一个数组，父查询就要使用IN

# 语法
SELECT 字段名 FROM 表 WHERE 字段 IN (子查询);
# 示例：查询工资大于 5000 的员工，来自于哪些部门的名字
# 1 先查询工资大于 5000 的员工的部门di
SELECT emp.dept_id
FROM emp
WHERE salary > 5000;
# 2 根据子查询查找部门
SELECT dept.name
FROM dept
WHERE dept.id IN (SELECT emp.dept_id
				  FROM emp
				  WHERE salary > 5000);

子查询结果为多行多列时

子查询结果为多行多列时，肯定在FROM作为一个表

这时候一定要给取别名了

# 语法
SELECT 字段名 FROM (子查询) 表别名 WHERE 条件;
# 示例：查询出 2011 年以后入职的员工信息，包括部门名称
# 1 先查询2011年后入职的信息
SELECT * 
FROM emp
WHERE emp.join_date >='2011-1-1';
# 2 在结合两张表查询员工信息和部门信息
SELECT * 
FROM dept d,(SELECT * 
			 FROM emp
			 WHERE emp.join_date >='2011-1-1') AS e
WHERE d.id = e.dept_id;

事务

事务:在实际的开发过程中，一个业务操作如：转账，往往是要多次访问数据库才能完成的。转 账是一个用户扣钱，另一个用户加钱。如其中有一条 SQL 语句出现异常，这条 SQL 就可能执行失败。事务执行是一个整体，所有的 SQL 语句都必须执行成功。如果其中有 1 条 SQL 语句出现异常，则所有的 SQL 语句都要回滚，整个业务执行失败。

转账操作

# 创建数据表
CREATE TABLE account (
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(10),
	balance DOUBLE
);
INSERT INTO account (name, balance) VALUES ('张三', 1000), ('李四', 1000);
# 假设张三个李四转了500元
# 1 开启事务
START TRANSACTION;
# 2 转账
UPDATE account SET balance = balance - 500 WHERE name = '张三';
UPDATE account SET balance = balance + 500 WHERE name = '李四';
# 3 提交事务
COMMIT;

如果转账过程一方发生失败就应该回滚事务 rollback

# 假设转账失败
# 1 # 1 开启事务
START TRANSACTION;
# 2 转账
UPDATE account SET balance = balance - 500 WHERE name = '张三';
UPDATE account SET balance = balance + 500 WHERE name = '李四';
# 3 回滚事务
ROLLBACK;

MySQL 默认每一条 DML(增删改)语句都是一个单独的事务，每条语句都会自动开启一个事务，
语句执行完毕 自动提交事务，MySQL 默认开始自动提交事务。

# 查看MySQL事务是否开启
SELECT @@AUTOCOMMIT;

如果是1则为自动提交事务,0为取消自动

# 修改提交事务权限
SET @@AUTOCOMMIT = 0;

事务的原理

事务开启之后，所有操作都会临时保存在事务日志中，事务日志只有在得到commit的命令下才会同步到数据库中，其他任何情况都会清空事务日志(rollback ，断开连接)

事务的步骤

1. 客户端连接到数据库服务器，创建连接是创建此用户的临时日志
2. 开启事务后，所有的操作都会先写入到临时日志文件中
3. 所有的查询操作从表中查询，但会经过日志文件加工后才返回

回滚点

回滚点的操作语句

回滚点的操作语句	操作
设置回滚点	savepoint 名字
回到回滚点	rollback to 名字

设置回滚点可以让我们在失败的时候回到回滚点，而不是回到事务开启的时候

事务的隔离级别

事务的四大特性 ACID

事务特性	含义
原子性(Atomicity)	每个事务都是个整体，不可在拆分，事务中所有的SQL语句要么都执行成功，要么都失败
一致性 (Consistency)	事务在执行前数据库的状态与执行后数据库的状态保持一致,如：转账前2人的总金额是5000 那么转账后总金额也是5000
隔离性(Isolation)	事务与事务之间不应该相互影响，执行是保持隔离的状态
持久性(Durability)	一旦事务执行成功，对数据库的修改是持久的。就算关机，也是保存下来的

事务的隔离级别

事务在操作时的理想状态所有的事务之间保持隔离，互不影响。因为并发操作，多个用户同时访问同一个 数据。可能引发并发访问的问题

MySQL数据库中的四种隔离级别

从上往下级别一次升高

级别	名字	隔离级别	脏读	不可重读度	幻读	数据库默认隔离级别
1	读未提交	read uncommitted	是	是	是
2	读已提交	read committed	否	是	是	Oracle和 SQL Server
3	可重复读	repeatable read	否	否	是	MySQL
4	串行化	serializable	否	否	否

隔离级别越高，性能越差，安全性越高

MySQL 事务隔离级别相关的命令

# 查询隔离级别
SELECT @@tx_isolation;
# 设置隔离级别 需要退出MySQL
set global transaction isolation level 级别级字符串

DCL (Data Control Language)

创建用户

#语法
CREATE USER '用户名' @ '主机名' IDENTIFIED BY  '密码';

关键字说明

关键字	说明
用户名	创建的用户名
主机名	指定该用户在哪个主机上可以登录，如果是本地用户localhost 如果想让该用户任意远程主机登录，可以用通配符%
密码	密码可以为空，则该用户不需要密码就可以登录

授权

#语法
GRANT 权限1,权限2,...ON 数据库名.表名 TO '用户名' @ '主机名';

关键字说明

关键字	说明
GRANT … ON…TO	授权关键字
权限	权限：CREATE、ALTER、SELECT、INSERT、UPDATE等，如果要全部授权使用 ALL
据库名.表名	给该用户操作的有哪些数据库的那些数据表，如果全部授权可用 * 代替

撤销授权

#语法
REVOKE 权限1,权限2..ON 数据库.表名 FROM '用户名' @ '主机名';

关键字和上边授权一样

查看权限

#语法
SHOW GRANT FOR '用户名' @ '主机名';

usage 是指连接（登陆）权限，建立一个用户，就会自动授予其 usage 权限（默认授予）。

删除用户

#语法
DROP USER '用户名' @ '主机名';

修改管理员和用户密码

#语法 
#修改管理员密码
mysqladmin -uroot -p password 新密码
#修改用户密码
set passwor for '用户名' @ '主机名' = password('新密码');

管理原密码需要在未登录情况下修改
用户密码要在MySQL登陆的情况下修改