MySQL(DQL,多表)
SQL简介:
DQL:
DQL(基本查询)：
语法：
- 查询多个字段：select 字段1,字段2,字段3 from 表名；
- 查询所有字段(通配符)：select * from 表名；
- 设置别名：select 字段1 [as 别名1],字段2 [as 别名2] from 表名；
- 去除重复记录：select distinct 字段列表 from 表名；
注意事项：
- *号代表查询所有字段，在实际开发中尽量少用（不直观、影响效率）；
DQL(条件查询)：
语法：
- 条件查询：select 字段列表 from 表名 where 条件列表；
比较运算符：
| 比较运算符 | 功能 |
|---|---|
| > | 大于 |
| >= | 大于等于 |
| < | 小于 |
| <= | 小于等于 |
| = | 等于 |
| <> 或 != | 不等于 |
| between ... and ... | 在某个范围之内(含最小,最大值) |
| in (...) | 在in之后的列表中的值,多选一 |
| like 占位符 | 模糊匹配(_匹配单个字符, %匹配任意个字符) |
| is null | 是null |
逻辑运算符:
| 逻辑运算符 | 功能 |
|---|---|
| and 或 && | 并且(多个条件同时成立) |
| or 或 || | 或者(多个条件任意一个成立) |
| not 或 ! | 非, 不是 |
DQL(分组查询):
聚合函数:
- 介绍：将一列数据作为一个整体，进行纵向计算；
- 语法：select 聚合函数(字段列表) from 表名；
函数：
| 函数 | 功能 |
|---|---|
| count | 统计数量 |
| max | 最大值 |
| min | 最小值 |
| avg | 平均值 |
| sum | 求和 |
注意事项：
- null值不参与所有聚合函数运算；
- 统计数量可以使用：count(*)，count(字段)，count(常量)，推荐使用count(*)；
语法：
- 分组查询：select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件]；
where 与 having区别：
- 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤；
- 判断条件不同：where不能对聚合函数进行判断，而having可以；
注意事项：
- 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义；
- 执行顺序：where > 聚合函数 > having；
DQL(排序查询)：
语法：
- 条件查询：select 字段列表 from 表名 [where 条件列表] [group by 分组字段] order by 字段1 排序方式1,字段2 排序方式2...；
排序方式：
- ASC：升序（默认值）
- DESC：降序
注意事项：
- 如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序；
DQL(分页查询)：
语法：
- 分页查询：select 字段列表 from 表名 limit 起始索引,查询记录数；
注意事项：
- 起始索引从0开始，起始索引 = (查询页码 - 1) * 每页显示记录数；
- 分页查询时数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT；
- 如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10；
流程控制函数：
- if(表达式,tvalue,fvalue)：当表达式为true时，取值tvalue；当表达式为false时，取值fvalue
- case expr when value1 then result1 [when value2 then value2 ...] [else result] end
多表：
多表设计：
-
一对多(多对一)；
- 在多的一方添加外键，关联另外一方的主键；
-
一对一；
- 任意一方，添加外键，关联另外一方的主键；
-
多对多；
- 通过中间表来维护，中间表的两个外键，分别关联另外两张表的主键；
外键约束：
物理外键：
- 概念：使用foreign key 定义外键关联另外一张表；
- 定义外键的为子表；
- 缺点：
- 影响增、删、改的效率（需要检查外键关系）；
- 仅用于单节点数据库，不适用与分布式、集群场景；
- 容易引发数据库的死锁问题，消耗性能；
逻辑外键：
- 概念：在业务层逻辑中，解决外键关联；
- 通过逻辑外键，就可以很方便的解决上述问题；
多表查询：
介绍：
- 多表查询：指从多张表中查询数据；
- 笛卡尔积：笛卡尔积是指在数学中，两个集合(A集合 和 B集合)的所有组合情况；
分类：
- 连接查询：
- 内连接：相当于查询A、B交集部分数据；
- 外连接：
- 左外连接：查询左表所有数据(包括两张表交集部分数据)；
- 右外连接：查询右表所有数据(包括两种表交集部分数据)；
- 子查询；
内连接：
语法：
- 隐式内连接：select 字段列表 from 表1,表2 where 条件...；
- 显式内连接：select 字段列表 from 表1 inner join 表2 on 连接条件...；
- 显式内连接中的inner可以省略；
外连接：
语法：
- 左外连接：select 字段列表 from 表1 left outer join 表2 on 连接条件...；
- 右外连接：select 字段列表 from 表1 right outer join 表2 on 连接条件...；、
- 外连接中的outer可以省略；
子查询：
概述：
- 介绍：SQL语句中嵌套select语句，称为嵌套查询，又称子查询；
- 为括号内的查询为子查询
- 形式：select * from t1 where column1 = (select column1 from t2...)；
- 子查询外部的语句可以是insert / update / delete / select 的任何一个，最常见的是select；
标量子查询：
- 子查询返回的结果是单个值(数字、字符串、日期等)，最简单的形式；
- 常用的操作符：= <> > >= < <=
列子查询：
- 子查询返回的结果是一列(可以是多行)；
- 常用的操作符：in、not in等；
行子查询:
- 子查询返回的结果是一行(可以是多列);
- 常用的操作符：=、<>、in、not in
表子查询：
- 子查询返回的结果是多行多列，常作为临时表；
- 常用的操作符：in
- 例子：列表连接，一张临时表，一张正常表；
select * from (select * from tb_emp where entrydate > '2006-01-01') e,tb_dept d where e.dept_id = d.id
事务:
介绍：
概念:
事务是一组操作的集合,它是一个不可分割的工作单位；事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，既这些操作要么同时成功，要么同时失败；
注意事项：
- 默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务；
操作：
事务控制：
- 开启事务：start transaction；/ begin；
- 提交事务：commit；
- 操作同时成功提交事务；
- 回滚事务：rollback；
- 操作有一个失败回滚事务；
四大特性(ACID):
- 原子性(Atomicity):
- 事务是不可分割的最小单元，要么全部成功，要么全部失败；
- 一致性(Consistency)：
- 事务完成时，必须使所有的数据都保持一致状态；
- 隔离性(Isolation)：
- 数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行；
- 持久性(Durability)：
- 事务一旦提交或回滚，它对数据库中的数据的改变就是永久的；
索引：
介绍：
概念：
- 索引（index）是帮助数据库高效获取数据的数据结构；
优缺点：
优点
- 提高数据查询的效率，降低数据库的IO成本；
- 通过索引列对数据进行排序，降低数据排序的成本，降低CPU消耗；
缺点：
- 索引会占用存储空间；
- 索引大大提高了查询效率，同时却也降低了insert、update、delete的效率；
结构:
- B+Tree(多路平衡搜索树);
特点：
- 每一个节点，可以存储多个key（有n个key，就有n个指针）；
- 所有的数据都存储在叶子节点，非叶子节点仅用于索引数据；
- 叶子节点形成了一颗双向链表，便于数据的排序及区间范围查询；
语法：
- 创建索引：
create [unique]/index 索引名 on 表名(字段名...);
- 查看索引：
show index from 表名;
- 删除索引：
drop index 索引名 on 表名;
注意事项:
- 主键字段，在建表时，会自动创建主键索引；
- 添加唯一约束时，数据库实际上会添加唯一索引；