JavaWeb学习第七天-MySQL对表查询、事务和索引

---

前言

MySQL数据库的剩余部分，仅供个人学习使用，如果也对你有所帮助，小子深感荣幸。

---

一、多表查询

所谓多表查询其实就是需要从多张表中获取所需数据

select * from  tb_emp , tb_dept;
//实际就是对两张表中的所有数据做一个笛卡尔积

分类：

• 内连接
• 外连接：左外连接、右外连接
• 子查询

1.内连接

用于查询多表中的交集部分的数据

//隐式内连接语法：
select  字段列表   from   表1 , 表2   where  条件 ... ;

//显示内连接语法
select  字段列表   from   表1  [ inner ]  join 表2  on  连接条件 ... ;

在多表查询时可以给表起别名（一旦起了别名只能使用别名来指明表中字段,包括select语句在内）

• tableA as 别名1 , tableB as 别名2 ;

• tableA 别名1 , tableB 别名2 ;

select emp.name , dept.name		//select语句使用表的名称改为别名
from tb_emp as emp 	//tb_emp 起别名 emp
inner join tb_dept as dept 
on emp.dept_id = dept.id;

2.外连接

外连接分为左外连接（left outer join）和右外连接（right outer join），位于外连接关键字“左”（或者“右”）侧的表称为主表，外连接会自动查询主表中的所有数据以及和主表匹配的另一侧表（从表）中的数据。

//左外连接
select  字段列表   from   表1  left  [ outer ]  join 表2  on  连接条件 ... ;	//左外连接，这个时候表1是主表，表2是从表，会查询表1中的所有数据

//右外连接
select  字段列表   from   表1  right  [ outer ]  join 表2  on  连接条件 ... ;	//右外连接，这个时候表2是主表，表1是从表，会查询表2中的所有数据

如果在从表中没有搜索到和主表匹配的数据，则会返回一个null。（这个null的返回和该字段的数据类型没有关系，null表示的是空，而并非是返回一个类似于默认值的东西）

3.子查询

又叫嵌套查询，其实就是在某一个查询返回结果中继续进行查询操作。

• 标量子查询（查询返回结果是单个数值，一行一列）
• 列子查询（返回一列，可以多行）
• 行子查询（返回一行，可以多列）
• 表子查询（返回一个表）

子查询可以书写在where，from，select语句之后

3.1 标量子查询

//查询研发部所有员工信息

//不适用子查询：
-- 1.查询"研发部"部门ID
select id from tb_dept where name = '研发部';    #查询结果：1
-- 2.根据"研发部"部门ID, 查询员工信息
select * from tb_emp where dept_id = 1;

//使用子查询：
-- 合并出上两条SQL语句
select * from tb_emp where dept_id = (select id from tb_dept where name = '研发部');

3.3 行子查询

在进行行子查询时可能会使用到多个字段的查询：

//查询和逃夭夭入职日期和职位都相同的员工信息
select * from tb_emp where (entrydate,job) = (select entrydate , job from tb_emp where name = '逃夭夭');

//使用（字段1，字段2...）查询多个字段

3.4 表子查询

直接给个例子

// 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
select e.*, d.* 
from (select * from emp where entrydate > '2006-01-01') e 	//给暂时表起个表名方便调用
left join dept d 
on e.dept_id = d.id ;

二、事务

事务是一组操作的集合，是一个不可分割的工作单位。事务会把所有的操作作为一个整体一起向系统提交或者撤销操作请求，即这些操作要么同时成功，要么同时失败。

事务控制：

• 开启事务：start transaction; / begin;
• 提交事务：commit;
• 回滚事务：rollback;

在图形化操作窗口中，每一个窗口也可以看作是一个事务。假如我们在命令窗口A中对表格B中的数据进行了修改但是尚未提交；此时如果在A窗口中调用命令查看表格B的数据，会发现数据已经被修改了。但是如果在一个新的窗口打开B表格，在新窗口中是看不到A中对表格的修改的。类似于这种，每一个窗口在其他事务提交前无法看到其他事务对数据的修改，也是事务隔离性的体现。

手动提交事务的操作顺序：

• 第1种情况：开启事务 => 执行SQL语句 => 成功 => 提交事务
• 第2种情况：开启事务 => 执行SQL语句 => 失败 => 回滚事务

四大特性： ACID

• 原子性（Atomicity） ：原子性是指事务包装的一组sql是一个不可分割的工作单元，事务中的操作要么全部成功，要么全部失败。

• 一致性（Consistency）：一个事务完成之后数据都必须处于一致性状态。如果事务成功的完成，那么数据库的所有变化将生效。如果事务执行出现错误，那么数据库的所有变化将会被回滚(撤销)，返回到原始状态。

• 隔离性（Isolation）：多个用户并发的访问数据库时，一个用户的事务不能被其他用户的事务干扰，多个并发的事务之间要相互隔离。一个事务的成功或者失败对于其他的事务是没有影响。

• 持久性（Durability）：一个事务一旦被提交或回滚，它对数据库的改变将是永久性的，哪怕数据库发生异常，重启之后数据亦然存在。

三、索引

索引是帮助数据库高效获取数据的数据结构。

优点：

1. 提高数据查询的效率，降低数据库的IO成本。
2. 通过索引列对数据进行排序，降低数据排序的成本，降低CPU消耗。

缺点：

1. 索引会占用存储空间。
2. 索引大大提高了查询效率，同时却也降低了insert、update、delete的效率（因为对索引字段的修改可能会导致索引数据结构的调整）。

1.语法

创建索引：

create  [ unique ]  index 索引名 on  表名 (字段名,... ) ;	//给某表的某些字段添加索引

查看索引：

show  index  from  表名;

删除索引：

drop  index  索引名  on  表名;

2.结构

一般情况下都是指使用B+树结构组织的索引。

真正的数据都是存储在叶子节点上的，而且所有的叶子结点本身也构成一个有序双向链表，在进行区间范围查询或者数据排序时具有很大的优势。

---

总结

主要用于个人学习使用，如果存在错误还请大佬指正，小子拜谢。共勉。