【Mysql-多表关系、多表查询、级联操作、索引、数据库事务与隔离级别】

---

一、多表关系

1.一对一

	场景引入：一个人对应一个身份证，一个身份证从属某一个人

2.一对多

	场景引入：部门和员工表（一个部门有多个员工，一个员工从属与某一个部门）

3.多对多

	场景引入：选课表和学生表（一个学生可以选多种课程，一个课程也可以被多个学生选择）

二、多表查询

1.概念：基于两个或两个以上的表查询

2.多表查询的关键在于使用正确的过滤条件（否则会出现笛卡尔集）

		不使用过滤条件情况下，多表查询会出现笛卡尔集，行数是各个表行数的乘积，列数是各个表的列之和
		小细节：多表查询的条件不能少于表的个数-1，否则会出现笛卡尔集		

3.多表查询分类

		1）自连接：指在同一张表的连接查询【将一张表看作两张表】
				语法：select 显示的字段名 from 表名 表别名1，表名 表别名2 where 别名表1与别名表2之间的连接过滤条件
		2）内连接
			隐式内连接--看不到join关键字，使用where条件
				语法：select 显示的字段名称 from 表名1，表名2 where 连接过滤条件
			显示内连接--关键字inner join关键字，使用on条件
				语法：select 显示的字段名 from 表名1 inner join 表名2  on 连接过滤条件
			注意：实际开发中内连接优先使用where隐式内连接（SQL优化的一种）
		3）外连接
			左外连接---关键字left join ,使用on过滤(开发中常用）
				左外连接：将左边表数据以及满足条件的数据全部展示
				语法：select 显示字段名 from 表1 left join 表2 on 连接过滤条件
			右外连接---关键字right join ,使用on过滤			
				右外连接：将右边表数据以及满足条件的数据全部展示
				语法：select 显示字段名 from 表1 right join 表2 on 连接过滤条件			
		4）子查询（嵌套查询）：select语句嵌套select语句
				可以将子查询当作一个临时表（虚表），可以解决很多很多复杂的查询			
			单列单行子查询（依据一个字段查询，且只显示一行结果）
				场景引入：查询最高工资员工信息
				select * from 员工表 where 工资字段名 =（select max(工资字段名）from 员工表 )	

			单列多行子查询（依据一个字段查询返回多行结果,使用关键字in）
				场景引入：查询在市场部或财务部的员工信息
				select * from 员工表 where 员工表中关联部门号 in(select 部门号 from 部门表 where 部门名称=市场部 or 部门名称 =财务部
		
			多列子查询是指查询返回多个列数据（依据多个字段同时匹配查询）
				例：查询表中与column3中值1的column1,column2相同的其他值的数据
				语法：select column1, column2 
							from tablename 
									where (column1, column2) =(select column1,column2 from tableName where   column3 = 值1）and column3 != 值1
		5） 合并查询（关键字union,  union all)
				应用场景：合并多个select语句的结果，可以使用集合操作符号(可以是一张表的，也可以是多张表的）
				1）union all
					该操作符用于取得两个结果集的并集。使用该操作符，不会取消重复行
					语法：
					select columnName from tableName where condition1
					union all
					select columnName from tableName where condition2；
			2）union
				该操作符用于取得两个结果集的并集。使用该操作符，会取消重复行		
		6）mysql中Al l与Any的使用
				all代表所有
				any代表任意一个

三、 级联操作(关键字cascade)

	概念:当修改或者删除主表数据,从表数据会随之改变
	引入:两个表通过外键关联,外键所在表为从表,其关联的表为主表,此种情况下,如想修改或者删除主表的数据,需要先将从表中清除该关联数据,再做修改,步骤繁琐,灵活性不够,级联化解了这个不足.
	级联添加语法(创建表时添加):
			create table  从表名(
					主表名_关联字段名,
					CONSTRAINT  主表名_从表名_fk
					FOREIGN KEY  (主表名_关联字段名)
					REFERENCE 主表名(关联字段名)
					ON UPDATE CASCADE
					ON DELETE CASCADE
			);
	级联添加语法(给已创建表中的外键字段设置级联):
		alter table 从表名 add CONSTRAINT 主表名_从表名_fk FOREIGN KEY (主表名_关联字段) REFERENCE 主表名(关联字段名) ON UPDATE CASCADE ON DELETE CASCADE;
		//此处注意将外键设置字段的非法数据清除后，才能添加外键级联
	级联删除语法:
		alter table 从表名 drop FOREIGN KEY 主表名_从表名_fk;

	术语解释:
			ON UPDATE CASCADE   修改级联
			ON DELETE CASCADE   删除级联
			主表名_从表名_fk    外键名称

四、索引

	1.作用:用于提高数据库的性能，在不用加内存、不用改程序、不用调sql的基础上，使查询速度可能提高百倍千倍
	2.索引的原理
		1）没有索引为什么会慢？
			没有索引情况下，查询，会扫描查询字段的所有值，扫描完后，才返回结果
		2）使用索引为什么会快？
			增加索引后，索引字段会在内部形成一个数据结构（比如二叉树，通过二叉树比较n次，可以覆盖n^2范围）
		
	2.分类：
		主键索引（primary key)
		唯一索引(unique)
		普通索引（index)
		全文索引(fulltext) 【适用于MyISAM存储引擎】
			开发中考虑使用：全文搜索框架Solr和ElasticSearch(ES)
			
			mysql自带全文索引，效率不高，很少用
	3.索引相关语法
		1)查询表是否有索引
			show indexes from tableName
			show index from tableName
			show keys from tableName
			desc tableName(信息没有上面三种方式详细）
		2)添加普通索引或者唯一索引语法
			create [unique] index indexName on tableName (columnName[(length)] [asc | desc]...)
			alter table tableName add index  indexName (columnName...)
		3)添加主键索引
			alter table tableName add primary key(columnName)
		4)删除索引
			drop index indexName on tableName
			alter table tableName drop index indexName
		5)删除主键索引
			alter table tableName drop primary key;
		6)修改索引---先删除，再重新添加(语法如上)
	4.注意事项与细节：
		1>索引本身也会占用磁盘空间，而且对dml（update delete insert)语句的效率有影响--（需要对索引数据结构进行维护）
		2>创建索引后，只对创建了索引的列有效
	5.哪些列上适合使用索引
		a较频繁的作为查询条件字段应该创建索引
		b唯一性太差的字段不适合单独创建索引，即使频繁作查询条件（例：性别）
		c更新非常频繁的字段不适合创建索引
		d不会出现在where子句中的字段不该创建索引

##五、mysql事物
1.什么是事物？
事物用于保证数据的一致性，它由一组相关的dml语句组成(即业务整体)，该组的dml语句要么全部成功，要么全部失败(主要针对添加/修改/删除)。
事务概念2:当前整个业务操作同时执行多个sql或者多张表的sql时候,使用事务,将业务整体管理起来,要么全部成功,要么全部失败
例如：转账业务就要用事物来处理，用以保证数据的一致性
2.事物和锁
当执行事物操作时（dml语句），mysql会在表上加锁，防止其他用户改表的数据
3.mysql数据库控制台事务的几个重要操作
1)start transaction —开始一个事务
2)savepoint 保存点名—设置保存点
3)rollbcak to 保存点名—回退事务（从执行回退的位置，到保存点之间所有的事物与保存点都删除掉）
4)rollback —回退全部事务
5)commit—提交事务，所有的操作生效，不能回退（提交的同时会结束事物并删除保存点，其它会话（连接）将可以查看到事物变化后到新数据）
4.事务细节：
1）如果不开始事务，关系型数据库默认情况下，dml操作自动提交，不能回滚
2）如果开始一个事务，没有创建保存点，可以执行rollback，回退到事物开始状态
3）也可以在事务没有提交前，创建多个保存点，并选择回退到指定保存点
4）mysql的事务机制需要innodb的存储引擎才可以使用，myisam（不支持事务）
5）开始一个事务start transaction, set autocommit=off;
5.事务隔离级别（在事务中，依据不同的隔离级别，可以看到的数据不同）
1）引入：多个连接开启各自事务操作数据库中的数据时，数据库系统需要负责隔离操作，以保证各个连接在获取数据时的准确性
2）如不考虑隔离性，会导致的问题
a脏读（dirty read):当一个事务读取另一个事务尚未提交的修改时，产生脏读
b不可重复读（norepeatable read):同一查询在同一事务中多次进行，由于其他事务所做的修改或删除，每次返回不同的结果集，此时发生不可重复读
c幻读（phantom read):同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读
3)事务隔离级别
分别从小到大,安全性从低到高,执行效率从高到底
–read uncommitted;读未提交,会导致最严重的问题"脏读"(一个事务读到另一个没有提交的事务)
–read committed;读已提交, 可以有效防止脏读,但是会不可重复读的问题(在提交事务之前和提交事务后,数据不一致)
–repeatable read:可以重复读,并可以有效防止脏读以及不可重复读,会出现幻读
–serializable:串行话,级别最高,效率低
设置隔离级别语法:
set global transaction isolation level 级别名称;
隔离级别查询语法：
mysql5.1以上查询事务的隔离级别
select @@tx_isolation;—mysql默认隔离级别是repeatable read
mysql8.0查询事务的隔离级别
select @@transaction_isolation
6.)关系型数据库事务的特点ACID(重点)----传统型事务特点
原子性:使用事务管理的业务操作(执行多个sql,要么同时成功,要么同时失败)
一致性:在事务管理的业务操作过程中,执行之前与执行之后的数据总量不变（后期有分布式事务特点,一致性会被打破）
隔离性:业务和业务之间独立,需要被事务管理,事务和事务之间是独立的,相互之间不影响,分离开(一个事务管理一个业务)
持久性:针对增删改操作,只要提交事务,即使关机了,数据永久保存

---

与poppy一起学习

	预习jdbc，下期分享