18-MySQL多表查询与事务的操作（数据库）

感谢你的路过，希望学生的笔记能给你一点微不足道的参考
Java基础思维导图，完整Java体系的链接

一，多表查询

1.1多表查询的分类：

1.1.1内连接查询：
	1. 隐式内连接：使用where条件消除无用数据
		例子：
			-- 查询所有员工信息和对应的部门信息：SELECT * FROM em,dept WHERE em.dept_id = dept.id;
			-- 查询员工表的名称，性别。部门表的名称：SELECT em.name,em.gender,dept.name FROM em,dept WHERE em.`dept_id` = dept.`id`;
				第二种写法：
					SELECT 
					t1.name, -- 员工表的姓名
					t1.gender,-- 员工表的性别
					t2.name -- 部门表的名称
					FROM
					em t1,
					dept t2
					WHERE 
					t1.`dept_id` = t2.`id`;
	2. 显式内连接：
		* 语法： select 字段列表 from 表名1 [inner] join 表名2 on 条件
		* 例如：
			SELECT * FROM em INNER JOIN dept ON em.`dept_id` = dept.`id`;	
			SELECT * FROM em JOIN dept ON em.`dept_id` = dept.`id`;
	3.非等值连接
	4.自连接	
	5. 内连接查询：
		1. 从哪些表中查询数据
		2. 条件是什么
	3. 查询哪些字段
1.1.2 外连接查询：
	1. 左外连接：左外连接（左连接）：表示左边的这张表是主表。
		* 语法：select 字段列表 from 表1 left [outer] join 表2 on 条件；
		* 查询的是左表所有数据以及其交集部分。
		* 例子：-- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称
				SELECT t1.*,t2.`name` FROM em t1 LEFT JOIN dept t2 ON t1.`dept_id` = t2.`id`;
	2. 右外连接：右外连接（右连接）：表示右边的这张表是主表。
		* 语法：select 字段列表 from 表1 right [outer] join 表2 on 条件；
		* 查询的是右表所有数据以及其交集部分。
		* 例子：SELECT * FROM dept t2 RIGHT JOIN em t1 ON t1.`dept_id` = t2.`id`;
	3.多表连接

1.2子查询：

概念：查询中嵌套查询，称嵌套查询为子查询
	-- 查询工资最高的员工信息
		-- 1 查询最高的工资是多少 9000 ：SELECT MAX(salary) FROM em;
		-- 2 查询员工信息，并且工资等于9000的：SELECT * FROM em WHERE em.`salary` = 9000;
		-- 一条sql就完成这个操作。子查询：SELECT * FROM em WHERE em.`salary` = (SELECT MAX(salary) FROM em);
1. 子查询的结果是单行单列的：
	子查询可以作为条件，使用运算符去判断。 运算符： > >= < <= =
	-- 查询员工工资小于平均工资的人：SELECT * FROM em WHERE em.salary < (SELECT AVG(salary) FROM em);
2. 子查询的结果是多行单列的：
	子查询可以作为条件，使用运算符in来判断
	-- 查询'财务部'和'市场部'所有的员工信息
		SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部';
		SELECT * FROM em WHERE dept_id = 3 OR dept_id = 2;
		-- 子查询：SELECT * FROM em WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');
3. 子查询的结果是多行多列的：
	* 子查询可以作为一张虚拟表参与查询
	-- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
	-- 子查询
		SELECT * FROM dept t1 ,(SELECT * FROM em WHERE em.`join_date` > '2011-11-11') t2 WHERE t1.id = t2.dept_id;
	-- 普通内连接
		SELECT * FROM em t1,dept t2 WHERE t1.`dept_id` = t2.`id` AND t1.`join_date` >  '2011-11-11'
4.union （可以将查询结果集相加）
案例：找出工作岗位是SALESMAN和MANAGER的员工？
	select ename,job from emp where job = 'MANAGER'
	union
	select ename,job from emp where job = 'SALESMAN';
	两张不相干的表中的数据拼接在一起显示？
	select ename from emp
	union
	select dname from dept;
5.多行新增 
	insert into 表名(列名) values (列值),(列值),(列值)；
6.多表更新
	(1)update 表1,表2 set 列名=列值 where 表1.列名=表2.列名 and 其他限定条件
	(2)update 表1 inner join 表2 on 表1.列名=表2.列名 set 列名=列值 where 限定条件 
	示例：update employee e,salary s set title='助工',salary=1000 where e.empid=s.empid and name='李四'
7.多表删除
	语法： delete 被删除数据的表 from 删除操作中使用的表 where 限定条件 注:多张表之间使用逗号间隔 
	示例： //删除人事部的信息
	 delete d,e,s from department d,employee e,salary s where d.depid=e.depid and s.empid=e.empid and depname='人事部
8.日期运算函数
	now() 获得当前系统时间 
	year(日期值) 获得日期值中的年份 
	date_add(日期,interval 计算值 计算的字段); 
	注：计算值大于0表示往后推日期，小于0表示往前推日期 
	示例： date_add(now(),interval -40 year);//40年前的日期

1.3补充问题：

在表的连接查询方面有一种现象被称为：笛卡尔积现象
   笛卡尔积现象：当两张表进行连接查询的时候，没有任何条件进行限制，最终的查询结果条数是两张表记录条数的乘积。
关于表的别名：
   select e.ename,d.dname from emp e,dept d;
   表的别名有什么好处？
      第一：执行效率高。
      第二：可读性好。
怎么避免笛卡尔积现象？当然是加条件进行过滤。
   思考：避免了笛卡尔积现象，会减少记录的匹配次数吗？
      不会，次数还是那些次。只不过显示的是有效记录。
什么是外连接，和内连接有什么区别？
   内连接：
      假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接。
      AB两张表没有主副之分，两张表是平等的。
   外连接：
      假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张表是副表，主要查询主表中的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。

二，事务

2.1事务的基本介绍

2.1.1概念：
*  如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。
2.1.2操作：
	1. 开启事务： start transaction;
	2. 回滚：rollback;
	3. 提交：commit;
	
	-- 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;

 2.1.3MySQL数据库中事务默认自动提交
	* 事务提交的两种方式：
		* 自动提交：
			* mysql就是自动提交的
			* 一条DML(增删改)语句会自动提交一次事务。
		* 手动提交：
			* Oracle 数据库默认是手动提交事务
			* 需要先开启事务，再提交
	* 修改事务的默认提交方式：
		* 查看事务的默认提交方式：SELECT @@autocommit; -- 1 代表自动提交  0 代表手动提交
		* 修改默认提交方式： set @@autocommit = 0;

2.2事务的四大特征：

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

2.3.事务的隔离级别（了解）

   * 概念：多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。
   * 存在问题：
      1. 脏读：一个事务，读取到另一个事务中没有提交的数据

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

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

   * 隔离级别：
      1. read uncommitted：读未提交
          产生的问题：脏读、不可重复读、幻读

      2. read committed：读已提交 （Oracle）

          产生的问题：不可重复读、幻读
      3. repeatable read：可重复读 （MySQL默认）

         * 产生的问题：幻读
      5. serializable：串行化
          可以解决所有的问题
         不同的隔离级别的锁的情况(了解)
            1. 读未提交（RU）: 有行级的锁，没有间隙锁。它与RC的区别是能够查询到未提交的数据。
            2. 读已提交（RC）：有行级的锁，没有间隙锁，读不到没有提交的数据。
            3. 可重复读（RR）：有行级的锁，也有间隙锁，每次读取的数据都是一样的，并且没有幻读的情况。
            4. 序列化（S）：有行级锁，也有间隙锁，读表的时候，就已经上锁了。
   隐式提交(了解)
      DQL:查询语句句 DML:写操作(添加,删除,修改) DDL:定义语句句(建库,建表,修改表,索引操作,存储过程,视图) DCL: 控制语⾔言(给⽤用户授权,或删除授权) DDL（Data Define Language）：都是隐式提交。 隐式提交：执⾏行行这 种语句句相当于执⾏行行commit; DDL 。
          注意：隔离级别从小到大安全性越来越高，但是效率越来越低
         * 数据库查询隔离级别（8.0之后）：select @@transaction_isolation;
         * 数据库设置隔离级别：
            设置全局中的事务隔离级别set global transaction isolation level 级别字符串;
            设置当前会话中的事务隔离级别 mysql> set session transaction isolation level read uncommitted;

2.4补充：

SQL优化
1.对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引 
2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，
	如： select id from t where num is null 最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库. 备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用NULL。 
3.应尽量避免在 where 子句中使用 != 或 <> 操作符，否则引擎将放弃使用索引而进行全表扫描。 
4.应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致 引擎放弃使用索引而进行全表扫描，
	如：select id from t where num=10 or Name = 'admin'可以这样查询： 
	select id from t where num = 10 union all select id from t where Name = 'admin'
5.in 和 not in 也要慎用，否则会导致全表扫描，
	如： select id from t where num in(1,2,3) 对于连续的数值，能用 between 就不要用 in 了： select id from t where num between 1 and 3 很多时候用 exists 代替 in 是一个好的选择

三，DCL语句（了解）

   DBA：数据库管理员

3.1DCL：管理用户，授权

1. 管理用户
	1. 添加用户：
		语法：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
	2. 删除用户：
		语法：DROP USER '用户名'@'主机名';
	3. 修改用户密码：
		SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');
			SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');
		UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
			UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi';
		 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;
			* 通配符： % 表示可以在任意主机使用用户登录数据库

3.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'@'%';

四，索引与视图

4.1索引

4.1.1什么是索引？有什么用？
	索引就相当于一本书的目录，通过目录可以快速的找到对应的资源。
	在数据库方面，查询一张表的时候有两种检索方式：
		第一种方式：全表扫描
		第二种方式：根据索引检索（效率很高）
	索引为什么可以提高检索效率呢？
		其实最根本的原理是缩小了扫描的范围。
	索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库当中的对象，也需要数据库不断的维护。是有维护成本的。比如，表中的数据经常被修改这样就不适合添加索引，因为数据一旦修改，索引需要重新排序，进行维护。
	添加索引是给某一个字段，或者说某些字段添加索引。
	select ename,sal from emp where ename = 'SMITH';
	当ename字段上没有添加索引的时候，以上sql语句会进行全表扫描，扫描ename字段中所有的值。
	当ename字段上添加索引的时候，以上sql语句会根据索引扫描，快速定位。
4.1.2怎么创建索引对象？怎么删除索引对象？
	创建索引对象：create index 索引名称 on 表名(字段名);
	删除索引对象：drop index 索引名称 on 表名;
4.1.2什么时候考虑给字段添加索引？（满足什么条件）
	* 数据量庞大。（根据客户的需求，根据线上的环境）
	* 该字段很少的DML操作。（因为字段进行修改操作，索引也需要维护）
	* 该字段经常出现在where子句中。（经常根据哪个字段查询）
4.1.4注意：主键和具有unique约束的字段自动会添加索引。
	根据主键查询效率较高。尽量根据主键检索。
4.1.5
	查看sql语句的执行计划：
		mysql> explain select ename,sal from emp where sal = 5000;
	给薪资sal字段添加索引：
		create index emp_sal_index on emp(sal);
4.1.6索引底层采用的数据结构是：B + Tree
4.1.7索引的实现原理？
	通过B Tree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的“物理地址”，
	最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率
	是最高的。
		select ename from emp where ename = 'SMITH';
		通过索引转换为：
		select ename from emp where 物理地址 = 0x3;
4.1.8索引的分类？
	单一索引：给单个字段添加索引
	复合索引:  给多个字段联合起来添加1个索引
	主键索引：主键上会自动添加索引
	唯一索引：有unique约束的字段上会自动添加索引
	....
4.1.9索引什么时候失效？
	select ename from emp where ename like '%A%';
	模糊查询的时候，第一个通配符使用的是%，这个时候索引是失效的。

4.2视图

4.2.1什么是视图？
	站在不同的角度去看到数据。（同一张表的数据，通过不同的角度去看待）。
4.2.2怎么创建视图？怎么删除视图？
	create view myview as select empno,ename from emp;
	drop view myview;
	注意：只有DQL语句才能以视图对象的方式创建出来。
4.2.3对视图进行增删改查，会影响到原表数据。（通过视图影响原表数据的，不是直接操作的原表）
	可以对视图进行CRUD操作。
4.2.4面向视图操作：见
4.2.5视图的作用？
	视图可以隐藏表的实现细节。保密级别较高的系统，数据库只对外提供相关的视图，java程序员
	只对视图对象进行CRUD。

4.3补充：

   1，什么是事务，什么是锁？
      事务就是并发控制的单位，满足ACID属性
      锁可以分为乐观锁和悲观锁：
         悲观锁：认为在修改数据库数据的这段时间里存在着也想修改此数据的事务；
         乐观锁：认为在短暂的时间里不会有事务来修改此数据库的数据；
         我们一般意义上讲的锁其实是指悲观锁，在数据处理过程中，将数据置于锁定状态（由数据库实现）。
   2，什么是存储过程，有什么优点？
      存储过程是 SQL 语句和可选控制流语句的预编译集合，以一个名称存储并作为一个单元处理。
      优点：
         可以在单个存储过程中执行一系列 SQL 语句。
         可以从存储过程内引用其他存储过程，这可以简化一系列复杂语句。
         存储过程于创建时在服务器上进行编译，所以执行起来比单个 SQL 语句快。

练习

   这里培训没有练习任务。不过我为了提升自己的SQL语句编写能力，自己再网上找了题目来练习。虽然写博客的时候已经忘的差不多了。
需求：自己没事找事
建表语句：

DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS DEPT;
DROP TABLE IF EXISTS SALGRADE;

CREATE TABLE DEPT
       (DEPTNO int(2) not null ,
	DNAME VARCHAR(14) ,
	LOC VARCHAR(13),
	primary key (DEPTNO)
	);
CREATE TABLE EMP
       (EMPNO int(4)  not null ,
	ENAME VARCHAR(10),
	JOB VARCHAR(9),
	MGR INT(4),
	HIREDATE DATE  DEFAULT NULL