1.多表查询
多表查询可以分为:
- 连接查询
- 内连接:查询两表的交集(A∩B)
- 外连接
- 左外连接:查询两表的交集,并保留左表全部数据(A + A∩B)
- 右外连接:查询两表的交集,并保留右表全部数据(B + A∩B)
- 子查询
1.1 内连接
内连接查询分为两类
- 隐式内连接
- 显式内连接
隐式内连接语法:
select 字段列表 from 表1 , 表2 where 条件 ... ;
显式内连接语法:
select 字段列表 from 表1 [ inner ] join 表2 on 连接条件 ... ;
注意事项:
一旦为表起了别名,就不能再使用表名来指定对应的字段了,此时只能够使用别名来指定字段。
1.2 外连接
外连接分为两种:
- 左外连接
- 右外连接。
左外连接语法结构:
select 字段列表 from 表1 left [ outer ] join 表2 on 连接条件 ... ;。
右外连接语法结构:
select 字段列表 from 表1 right [ outer ] join 表2 on 连接条件 ... ;
注意事项:
左外连接和右外连接是可以相互替换的,只需要调整连接查询时SQL语句中表的先后顺序就可以了。而我们在日常开发使用时,更偏向于左外连接。
1.3 子查询
SQL语句中嵌套select语句,称为嵌套查询,在查询语句中套娃。
SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 ... );
子查询外部的语句可以是insert / update / delete / select 的任何一个,最常见的是 select。
根据子查询结果的不同分为:
- 标量子查询(子查询结果为单个值[一行一列])
- 常用的操作符: = <> > >= < <=
- 列子查询(子查询结果为一列,但可以是多行)
- 常用的操作符:in 、 not in
- 行子查询(子查询结果为一行,但可以是多列)
- 常用的操作符:= 、<> 、IN 、NOT IN
- 表子查询(子查询结果为多行多列[相当于子查询结果是一张表])
- 查询结果为一个临时表,书写于from后,需指定别名使用
子查询可以书写的位置:
5. where之后
6. from之后
7. select之后
2. 事务
事务作用:保证在一个事务中多次操作数据库表中数据时,要么全都成功,要么全都失败。
2.1 操作
MYSQL中有两种方式进行事务的操作:
- 自动提交事务:即执行一条sql语句提交一次事务。(默认MySQL的事务是自动提交)
- 手动提交事务:先开启,再提交
事务操作有关的SQL语句:
SQL语句 | 描述 |
start transaction; 或 begin ; | 开启手动控制事务 |
commit; | 提交事务 |
rollback; | 回滚事务 |
2.2 四大特性
- 原子性(Atomicity) :原子性是指事务包装的一组sql是一个不可分割的工作单元,事务中的操作要么全部成功,要么全部失败。
- 一致性(Consistency):一个事务完成之后数据都必须处于一致性状态。
如果事务成功的完成,那么数据库的所有变化将生效。
如果事务执行出现错误,那么数据库的所有变化将会被回滚(撤销),返回到原始状态。 - 隔离性(Isolation):多个用户并发的访问数据库时,一个用户的事务不能被其他用户的事务干扰,多个并发的事务之间要相互隔离。
一个事务的成功或者失败对于其他的事务是没有影响。 - 持久性(Durability):一个事务一旦被提交或回滚,它对数据库的改变将是永久性的,哪怕数据库发生异常,重启之后数据亦然存在。
事务的四大特性简称为:ACID
3. 索引
3.1 介绍
索引(index):通过B+Tree数据结构,提高数据查询的效率
优点:
- 提高数据查询的效率,降低数据库的IO成本。
- 通过索引列对数据进行排序,降低数据排序的成本,降低CPU消耗。
缺点:
- 索引会占用存储空间。
- 索引大大提高了查询效率,同时却也降低了insert、update、delete的效率。
3.2 结构
MySQL数据库支持的索引结构有很多,如:Hash索引、B+Tree索引、Full-Text索引等。
我们平常所说的索引,如果没有特别指明,都是指默认的 B+Tree 结构组织的索引。
B+Tree结构:
- 每一个节点,可以存储多个key(有n个key,就有n个指针)
- 节点分为:叶子节点、非叶子节点
- 叶子节点,就是最后一层子节点,所有的数据都存储在叶子节点上
- 非叶子节点,不是树结构最下面的节点,用于索引数据,存储的的是:key+指针
- 为了提高范围查询效率,叶子节点形成了一个双向链表,便于数据的排序及区间范围查询
3.4 语法
创建索引
create [ unique ] index 索引名 on 表名 (字段名,... ) ;
案例:为tb_emp表的name字段建立一个索引
create index idx_emp_name on tb_emp(name);
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lYwlJShH-1676631992055)(assets/image-20221209105119159.png)]
查看索引
show index from 表名;
案例:查询 tb_emp 表的索引信息
show index from tb_emp;
删除索引
drop index 索引名 on 表名;
案例:删除 tb_emp 表中name字段的索引
drop index idx_emp_name on tb_emp;
注意事项:
- 主键字段,在建表时,会自动创建主键索引
- 添加唯一约束时,数据库实际上会添加唯一索引