MySQL从0到1基础语法笔记（下）

多表问题分析：

        部门数据可以直接删除，然后还有部分员工归属于该部门下，此时就出现了数据的不完整、不一致问题。

 目前上述的两张表，在数据库层面，并未建立关联，所以是无法保证数据的一致性和完整性的。

 通过引入外键约束可以解决这个问题，此时这个外键称为 物理外键   --不推荐使用

物理外键：

 物理外键：使用 foreign key 定义外键关联另外一张表。

 缺点：

    影响增删改的效率（需要检查外键关系）

    仅用于单节点数据库，不适用于分布式、集群场景。

    容易引发数据库的死锁问题，消耗性能。

逻辑外键：

 逻辑外键： 在业务层逻辑中，解决外键关联。 -- 推荐

 多表设计：

 一对多：在多的一方添加外键，关联另外一方的主键

 一对一：任意一方，添加外键，关联另外一方的主键

 多对多：通过中间表来维护，中间表的两个外键，分别关联另外两张表的主键

多表查询：

select * from tb_emp,tb_dept where tb_emp.dept_id = tb_dept.id;

-- 笛卡尔积  A集合与B集合的所有组合情况，总数量 = sum(A) * sum(B)

内连接：

-- A. 查询员工的姓名，及所属的部门名称（隐式内连接实现）

-- select 字段列表 from 表1,表2 where 条件.....;

select tb_emp.name , tb_dept.name from tb_dept,tb_emp where tb_emp.dept_id = tb_dept.id;

-- 起别名

select e.name,d.name from tb_dept d,tb_emp e where e.dept_id=d.id;

-- B. 查询员工的姓名，及所属的部门名称（显示内连接实现）

-- select 字段列表 from 表1 [inner] join 表2 on 连接条件.....;

select tb_emp.name,tb_dept.name from tb_emp inner  join tb_dept on  tb_emp.dept_id = tb_dept.id;

外连接：

-- 左外连接：select 字段列表 from 表1 left [outer] join 表2 on 连接条件...

-- 右外连接：select 字段列表 from 表1 right [outer] join 表2 on 连接条件...

-- 左外连接全部包含左边的数据和两张表交集部分的数据 左右可替换 但左外更常用

-- A. 查询员工表 所有 员工的姓名，和对应的部门名称（左外连接）

select e.name,d.name from tb_emp e left join tb_dept d on e.dept_id=d.id;

-- A. 查询员工表 所有 员工的姓名，和对应的部门名称（右外连接）

select e.name,d.name from tb_emp e right join tb_dept d on e.dept_id=d.id;

子查询：

-- 标量子查询

-- A. 查询 ”教研部“ 的所有员工信息

-- a. 查询 教研部 的部门id - tb_dept

select id from tb_dept where name='教研部';

-- b. 再查询该部门ID下的员工信息 - tb_emp

select * from tb_emp where dept_id = 2;

select * from tb_emp where dept_id = (select id from tb_dept where name='教研部');

-- B. 查询在 “方东白” 入职之后的员工信息

-- a. 查询方东白 的入职时间

select tb_emp.entrydate from tb_emp where username='fangdongbai';

-- b.再查询 方东白 之后入职的员工信息

select * from tb_emp where entrydate > (select tb_emp.entrydate from tb_emp where username='fangdongbai');

-- 列子查询 ： 不做演示了 就是比标量子查询多几个条件，比如id不止一个

-- 行子查询 返回结果是一行（可以是多列）

-- A.査询与"韦一笑”的入职日期 及 职位都相同的员工信息;

-- a.查询"韦一笑”的入职日期及职位

select entrydate,job from tb_emp where name='韦一笑';

-- b.查询与其入职日期 及 职位都相同的员工信息;

select *from tb_emp where entrydate ='2007-01-01'and job=2;

select *from tb_emp where (entrydate,job) = (select entrydate,job from tb_emp where name='韦一笑');

select *from tb_emp where entrydate = (select entrydate from tb_emp where name='韦一笑') and job=(select job from tb_emp where name='韦一笑');

事务：

格式：    

start transaction ;

-- 删除部门

delete from tb_dept where id=1;

-- 删除员工

delete from tb_emp where dept_id=1;

commit ;

rollback ;     -- 一旦出现异常可以通过rollback恢复回来。

/*

概念：

 事务：是一组操作的集合，它是一个不可分割的工作单位。事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作

    要么同时成功，要么同时失败。 这样可以有效避免在逻辑外键中由于单个语句报错而导致数据不一致的情况。

阶段：

 开启事务：start transaction; / begin;

 提交事务：commit;

 回滚事务：rollback;

四大特性：（ACID）

重要：

 原子性：事务是不可分割的最小单元，要么全部成功，要么全部失败。

 一致性：事务完成时，必须使所有数据都保持一致状态。

 隔离性：数据库中提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。

 （比如在执行了start但不执行commit时，看到的表的数据没有变化，但是查询出来的表已经被操作过了），隔离性越高，事务越安全，效率越低。

 持久性：事务一旦提交或者回滚，对数据库中的数据的改变就是永久的。

 */

数据库优化：索引

创建索引：

-- create index idx_sku_sn on tb_sku(sn);  一次性创建索引

-- select * from tb_sku where sn = '1000000000354'; 不使用索引可能要十几秒  使用索引就几毫秒

索引概念：

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

  没有索引时 查找数据就是 全表扫描

  有索引时，就是建立了一个搜索树 结点就是上面说的 sn，结点与数据相关联，可以直接拿结点取数据。

  优点：提高查询和数据排序效率

  缺点：占用内存，降低增、删、改的效率，因为当对数据进行增删改操作时，数据结构要进行一个维护操作（一般不用担心缺点）

 结构：

  MySQL数据库支持的索引结构有很多，Hash索引、B+Tree索引、Full-Text索引等。平常没有特别指明的话默认指B+树结构。

  二叉搜索树和红黑树在大数据量的情况下，层级深，检索速度慢，因为每个父亲节点只有2个子节点。

  B+Tree:

1.  一个节点可以有多个key值，每个key值都带有一个指针，指针指向下一个节点（磁盘块/页）

 2. 页是数据库进行磁盘管理的最小单位，一个页大小是16kb

 3. 非叶子节点：仅仅起到索引数据，查找数据的作用，并不管保存数据

 4.叶子节点：所有的数据都是在叶子节点保存的，所有的key都会出现在叶子节点。叶子结点会保持所有key的数据。

        叶子节点都是按照元素的key从小到大的顺序进行排序的，在叶子节点中间形成了一个双向链表（便于数据的排序及区间范围查询），可以通过上一个元素找到下一个元素，也可以通过下一个元素找到上一个元素。

  5.找到key为x的数据，会进入x>=n的那个指针，一直这样找下去。

语法：

  创建索引：create [unique] index 索引名 on 表名（字段名,...）;

  查看索引：show index from 表名;

  删除索引：drop index 索引名 on 表名;

-- 创建：为tb_emp表的name字段建立一个索引

create index idx_emp_name on tb_emp(name);

-- 查询：查询 tb_emp 表的索引信息

show index from tb_emp;

注：

-- 一旦设定了某个字段的 唯一约束 ，就会自动生成一个 唯一索引

-- 一旦对某张表指定了主键，就会生成主键索引，主键索引是索引内性能最高的

-- 删除：删除tb_emp 表中name字段的索引

drop index idx_emp_name on tb_emp;

==========================结束============================

在Java中操作数据库，用的是MyBatis:一款优秀的 持久层 框架，用于简化JDBC的开发。

“明月清风晓星尘，凌霜傲雪宋子琛。”——《魔道祖师》