MySQL多表

内连接

内连接分为隐式内连接和显示内连接

特点：只显示交集数据

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

-- 隐式内连接实现
select tb_emp.name,tb_dept.name  from tb_emp ,tb_dept  where  dept_id=tb_emp.dept_id;

-- 起别名
-- 可以先写为
select * from tb_dept d, tb_emp e where d.id=e.dept_id;
-- 在把*进行修改
select d.name,e.name from tb_dept d, tb_emp e where d.id=e.dept_id;

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

[] 表示里面的内容可加可不加

-- 显示内连接实现
select e.name,d.name from tb_emp e inner join tb_dept d on e.dept_id=d.id;

外连接

外连接分为左外连接和右外连接

特点：一个表为主表，显示主表的所有数据信息

左外连接语法结构： select  字段列表   from   表1  left  [ outer ]  join 表2  on  连接条件 ... ;

左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据。

-- 左外连接实现
select e.name,d.name from tb_emp e left join tb_dept d on e.dept_id=d.id;
-- 以left join关键字左边的表为主表，查询主表中的所有数据，以及和主表匹配的右表中的数据
-- 如果右表没有匹配到数据，显示为null

右外连接语法结构: select  字段列表   from   表1  right  [ outer ]  join 表2  on  连接条件 ... ;

右外连接相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据。

-- 右外连接实现
select e.name,d.name from tb_emp e right join tb_dept d on e.dept_id=d.id;
-- 与左外连接 同理

子查询

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

子查询的查询结果不同：

1. 标量子查询（子查询结果为单个值[一行一列]）

2. 列子查询（子查询结果为一列，但可以是多行）

3. 行子查询（子查询结果为一行，但可以是多列）

4. 表子查询（子查询结果为多行多列[相当于子查询结果是一张表]） 

子查询可以书写的位置有：where之后、from之后、select之后

标量子查询

子查询返回的结果是单个值(数字、字符串、日期等)，最简单的形式，这种子查询称为标量子查询。

常用的操作符有：=、<>(!=)、>、>=、<、<= 

-- 标量子查询
-- tb_emp 为员工表 tb_dept为部门表
-- 1. 查询 "科研部" 的所有员工信息
select id from tb_dept where name='教研部'; -- 查询出id 为1
 -- 查询所有id 为1的员工
 select * from tb_emp where dept_id=1;
-- 可以使用嵌套
select * from tb_emp where dept_id=(select id from tb_dept where name='科研部');

-- 2. 查询在 "张三" 入职之后的员工信息
select name from tb_emp where name='张三';
-- 查询入职日期
select  entrydate from tb_emp where name='张三';

-- 查询指定张三入职日期之后入职的员工信息
select * from tb_emp where entrydate >'2011-11-11';
-- 可以使用嵌套
select * from tb_emp where entrydate >(select  entrydate from tb_emp where name='张三');

 列子查询

子查询返回的结果是一列(可以是多行)，这种子查询称为列子查询

常用的操作符：in  、not in等

-- 列子查询
-- 查询 "科研部" 和 "咨询部" 的所有员工信息
-- 查询 两个部的id 
select id from tb_dept where name='科研部'or name='咨询部'; -- 1,2
-- 查询这两个id 的员工
select * from tb_emp where dept_id in (1,2);
-- 进行嵌套
select * from tb_emp where dept_id in (select id from tb_dept where name='科研部'or name='咨询部');

行子查询

子查询返回的结果是一行(可以是多列)，这种子查询称为行子查询。

常用的操作符：=  、<>（!=） 、in 、not  in

-- 行子查询

--  查询与 "李四" 的入职日期 及 职位都相同的员工信息 ;
-- 查询李四的入职日期
select entrydate,job from tb_emp where name='李四';

-- 进行嵌套
select * from tb_emp where (entrydate,job) = (select entrydate,job from tb_emp where name='李四');

-- 如果不加李四

select * from tb_emp where (entrydate,job) = (select entrydate,job from tb_emp where name='李四') and name!= '李四';

表子查询

子查询返回的结果是多行多列，常作为临时表，这种子查询称为表子查询。

常用的操作符：in

-- 表子查询
-- 查询入职日期是 "2008-01-01" 之后的员工信息 , 及其部门信息
-- 查询2008-01-01 之后的员工
select * from tb_emp where entrydate>'2008-01-01';
-- 进行嵌套 给之前所查询的员工进行起别名
select e.name,e.entrydate,d.* from  (select * from tb_emp where entrydate>'2008-01-01') e ,tb_dept d where e.dept_id=d.id;

事务

事务是一组操作的集合，这组操作要么全部成功，要么全部失败

四大特性

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

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

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

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

MYSQL中有两种方式进行事务的操作

1.自动提交事务：即执行一条sql语句提交一次事务。（默认MySQL的事务是自动提交）

2.手动提交事务：先开启，再提交

事务操作有关的语句

start transaction; / begin ;   开启手动控制事务

commit;    提交事务

rollback;   回滚事务

-- 开启事务
start transaction ;

-- 删除科研部
delete from tb_dept where id = 1;

-- 删除科研部的员工
delete from tb_emp where dept_id = 1;

-- 提交事务 (成功时执行)
commit ;

-- 回滚事务 (出错时执行)
rollback ;

 手动提交事务使用步骤

第一种情况：开启事务->执行SQL语句->成功->提交事务

第二种情况：开启事务->执行SQL语句->失败->回滚事务

索引

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

MySQL数据库支持的索引结构有很多，如：Hash索引、B+Tree索引、Full-Text索引等。在没有指明时，默认都是B+Tree结构组织为索引。

B+Tree结构：

1.每一个节点，可以存储多个key（有n个key，就有n个指针）

2.节点分为：叶子节点、非叶子节点

        叶子节点，就是最后一层子节点，所有的数据都存储在叶子节点上

        非叶子节点，不是树结构最下面的节点，用于索引数据，存储的的是：key+指针

3.为了提高范围查询效率，叶子节点形成了一个双向链表，便于数据的排序及区间范围查询

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

查看索引：show  index  from  表名;

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

注意：

1.主键字段，在建表时，会自动创建主键索引

2.添加唯一约束时，数据库实际上会添加唯一索引