数据库深入(学习笔记)

多表查询

概述：

• 多表查询：指从多张表中查询数据
• 笛卡尔积：笛卡尔积是指在数学中，两个集合(A集合，B集合)的所有组合情况。

分类：

• 连接查询

  • 内连接：相当于查询A、B交集部分数据

  隐式内连接:select 字段列表 from 表1，表2 where 条件...;
  显式内连接:select 字段列表 from 表1[inner] join 表2 on 连接条件...；
  

  # 多表查询
  select * from tb_dept,tb_emp;
  select * from tb_emp,tb_dept where tb_dept.id = tb_emp.dept_id;
  
  # 内连接
  # 查询员工姓名，及所属部门的名称(隐式内连接)
  select tb_emp.name,tb_dept.name  from tb_emp,tb_dept where tb_emp.dept_id = tb_dept.id;
  
  # 查询员工姓名，及所属部门的名称(显式内连接实现)
  select tb_dept.name,tb_emp.name  from tb_emp inner join tb_dept on tb_dept.id = tb_epm.dept_id;
  

  • 外连接
    • 左外连接：查询左表所有数据(包括两张表交集部分数据)
    • 右外连接：查询右表所有数据(包括两张表交集部分数据)

  左外连接: select 字段列表 from 表1 left [outer] join 表2 on 连接条件;
  右外连接: select 字段列表 from 表1 right[outer] join 表2 on 连接条件;
  

  # 外连接
  # 查询所有员工姓名，和对应的部门名称(左外连接)
  select tb_emp.name, tb_dept.name
  from tb_emp
           left join tb_dept on tb_dept.id = tb_emp.dept_id;
  # 查询部门表 所有 部门名称，和对应的员工名称(右外连接)
  select tb_emp.name, tb_dept.name
  from tb_emp
           right join tb_dept on tb_emp.dept_id = tb_dept.id;
  

• 子查询

  概述：SQL语句中嵌套select语句，称为嵌套查询，又称子查询

  select * from t1 where column1 =(select column 1 from t2....)
  # 子查询外部的语句可以是insetr / update / delete / select 的任何一个，最常见的是select
  

  标量子查询：

  • 子查询返回的结果是单个值(数字、字符串、日期等)，最简单的形式
  • 常用的操作符： = <> > >= < <=

  # 子查询
  # 查询"教研部"的所有员工信息
      # 查询教研部 的部门ID
  select id from tb_dept where name = '教研部';
      # 再查询该部门ID 下的员工信息
  select * from tb_emp where dept_id = 2;
  select * from tb_emp where dept_id = (select id from tb_dept where name = '教研部');
  # 查询方东白 入职之后的员工信息
  #     查询该员工的入职时间
  select tb_emp.entrydate from tb_emp where name = '方东白';
  #     再查询该员工入职之后的员工信息
  select * from tb_emp where entrydate > '2012-11-01';
  select * from tb_emp where entrydate > (select tb_emp.entrydate from tb_emp where name = '方东白');
  

  列子查询

  • 子查询返回的结果是一列(可以是多行)
  • 常用的操作符: in、not in等

  # 列子查询
  # A.查询教研部和咨询部的所以有员工信息
  # a.查询教研部和咨询部的部门ID
  select tb_dept.id from tb_dept where name = '教研部' or name = '咨询部';
  # b.根据部门ID 查询该部门下的员工信息
  select * from tb_emp where dept_id in(3,2);
  select * from tb_emp where dept_id in(select tb_dept.id from tb_dept where name = '教研部' or name = '咨询部');
  

  行子查询

  • 子查询返回的结果是一行(可以是多列)
  • 常用操作符: = 、 <>、in、not in

  # 行子查询
  # A.查询 韦一笑 的入职信息 及职位信息都相同的员工信息
  # a.查询 韦一笑的入职日期和职位
  select tb_emp.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 = '韦一笑');
  

  表子查询

  • 子查询返回的结果是多行多列，常作为临时表
  • 常用的操作符:in

事务

介绍

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

注意事项：默认MySQL的事物是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务。

事务控制:

• 开启事务: start transaction/begin
• 提交事务:commint;
• 回滚事务:rollback;

事务的四大特性

• 原子性:事务是不可分割的最小单元，要么全部成功，要么全部失败
• 一致性：事务完成时，必须使所有的数据都保持一致状态
• 隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
• 持久性：事务一旦提交或回滚，它对数据库中的数据的该表就是永久的。

索引

介绍

索引（index）是帮助数据库高效获取数据的数据结构

优点：

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

缺点:

• 索引会占用存储空间
• 索引大大提高了查询效率，同时却也降低了insert、update、delete的效率

结构

MySQL数据库支持的索引结构有很多，如Hash索引、B+Tree索引(多路平衡搜索树)、Full-Text索引等。如果没有特别指明，都是默认的B+Tree结构组织的索引。

语法