高级查询、事务、权限管理

1. 多表查询

	1. 查询语法	
		    select 列名列表
    		from 表名列表
			where...

sql文件：

				-- 创建部门表 
				create table dept(   
					id int primary key auto_increment,   
					name varchar(20) 
				) 
				 
				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,   
					foreign key (dept_id) references dept(id) -- 外键，关联部门表(部门表的主键) 
				)   
				 
				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);

	2. 笛卡尔积
		* 有两个集合A、B，取这两个集合的所有组成情况
		* 要完成多表查询，需要消除无用的数据
	
	3. 多表查询分类
		1. 内连接查询：查询交集部分
			1. 隐式内连接查询：
				使用where条件限定
				SELECT * 
				FROM dept,emp
				WHERE dept.id = emp.dept_id;
			2. 显示内连接查询：
				select 字段列表 from 表名1 [inner] join 表名2 on 条件
				SELECT *
				FROM emp
				INNER JOIN dept
				ON emp.dept_id = dept.id
			3. 注意：
				1. 从哪些表查询
				2. 条件是什么
				3. 查询字段
			
		2. 外连接查询
			1. 左外连接：查询的是左边表的所有以及相交的部分
				语法：select 字段列表 from 表1 left [outer] join 表2 on 条件 

			2. 右外连接：查询的是右边表的所有以及相交的部分
				语法：select 字段列表 from 表1 right [outer] join 表2 on 条件
		3. 子查询 
			1. 概念：查询嵌套查询

			2. 不同情况
				1. 子查询的结果是单行单列的：
					子查询结果可以作为结果使用，可以使用运算符判断。单值运算符
					
					示例：
					-- 查询工资最高的员工的信息
	
					-- 查询最高工资
					SELECT MAX(salary) FROM emp;
					-- 查询工资最高的员工的信息
					SELECT * FROM emp WHERE salary = 9000;
					-- 合成一个sql，即子查询
					SELECT * FROM emp WHERE salary = (SELECT MAX(salary) FROM emp);

				2. 子查询的结果是多行单列的：即可以作为集合
					子查询结果可以作为条件，使用运算符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 = '市场部');
					
				3. 子查询的结果是多行多列的：
					子查询结果可以作为一张表参与查询
					-- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
					-- 子查询
					SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2
					WHERE t1.id = t2.dept_id;
					-- 普通内连接
					SELECT * FROM emp t1,dept t2 WHERE t1.`dept_id` = t2.`id` AND t1.`join_date` >  '2011-11-11'

2. 事务

	1. 事务的基本介绍
		1. 概念：如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。
		
		2. 操作：
			1. 开启事务： start transaction;
			2. 回滚：rollback;
			3. 提交：commit;	
		3. 例子：

			CREATE TABLE account (
				id INT PRIMARY KEY AUTO_INCREMENT,
				NAME VARCHAR(10),
				balance DOUBLE
			);
			-- 添加数据
			INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000);
						
						
			SELECT * FROM account;
			UPDATE account SET balance = 1000;
			-- 张三给李四转账 500 元
						
			-- 0. 开启事务
			START TRANSACTION;
			-- 1. 张三账户 -500
						
			UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';
			-- 2. 李四账户 +500
			-- 出错了...
			UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';
						
			-- 发现执行没有问题，提交事务
			COMMIT;
						
			-- 发现出问题了，回滚事务
			ROLLBACK;

		4. MySQL数据库中事务默认自动提交
		
			* 事务提交的两种方式：
				* 自动提交：
					* mysql就是自动提交的
					* 一条DML(增删改)语句会自动提交一次事务。
				* 手动提交：
					* Oracle 数据库默认是手动提交事务
					* 需要先开启事务，再提交
			* 修改事务的默认提交方式：
				* 查看事务的默认提交方式：SELECT @@autocommit; -- 1 代表自动提交  0 代表手动提交
				* 修改默认提交方式： set @@autocommit = 0;	
	
	2. 事务的四大特征
		1. 原子性：不可分割的最小操作单位，要么同时成功，要么同时失败
		2. 持久性：当事务提交或回滚后，数据库会持久化的保存数据
		3. 隔离性：多个事务之间，相互独立
		4. 一致性：事务操作前后，数据总量不变

	3. 事物的隔离级别（了解）
		1. 概念：多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。
		2. 存在问题：
			1. 脏读：一个事务，读取到另一个事务中没有提交的数据
			2. 不可重复读(虚读)：在同一个事务中，两次读取到的数据不一样。
			3. 幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。
		3. 隔离级别：
			1. read uncommitted：读未提交
				产生的问题：脏读、不可重复读、幻读
			2. read committed：读已提交 （Oracle）
				产生的问题：不可重复读、幻读
			3. repeatable read：可重复读 （MySQL默认）
				产生的问题：幻读
			4. serializable：串行化
				可以解决所有的问题

			注意：隔离级别从小到大安全性越来越高，但是效率越来越低
			数据库查询隔离级别：
				select @@tx_isolation;
			数据库设置隔离级别：
				set global transaction isolation level  级别字符串; 

3. DCL

管理用户，授权
1. 管理用户
		1. 添加用户：
			* 语法：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
		2. 删除用户：
			* 语法：DROP USER '用户名'@'主机名';
		3. 修改用户密码：
			
			UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
			UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi';
			
			SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');
			SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');

			* 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. 使用新密码登录。
		4. 查询用户：
			-- 1. 切换到mysql数据库
			USE myql;
			-- 2. 查询user表
			SELECT * FROM USER;
			
			* 通配符： % 表示可以在任意主机使用用户登录数据库

	2. 权限管理：
		1. 查询权限：
			-- 查询权限
			SHOW GRANTS FOR '用户名'@'主机名';
			SHOW GRANTS FOR 'lisi'@'%';

		2. 授予权限：
			-- 授予权限
			grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
			-- 给张三用户授予所有权限，在任意数据库任意表上
			
			GRANT ALL ON *.* TO 'zhangsan'@'localhost';
		3. 撤销权限：
			-- 撤销权限：
			revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
			REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';