DDL（数据定义语言）
数据库操作
查询
SHOW DATABASES;查询当前选择的数据库
SELECT DATABASE();创建数据库
CREATE DATABASE [IF NOT EXISTS] database_name [字符集] []删除数据库
DROP DATABASE IF EXISTS database_name;选中当前数据库
USE database_name;
表操作
查询当前数据库的所有表
SHOW TABLES;创建表
CREATE TABLE 表名( 字段1 字段1类型 [COMMENT 字段1的注释]， 字段1 字段1类型 [COMMENT 字段1的注释]， 。。。 )[COMMENT 表注释];
DML（数据操作语言）
INSERT
给指定字段添加数据
INSERT INTO 表名 (字段1，字段2。。) VALUES(值1， 值2);给全部字段添加数据
INSERT INTO 表名 VALUES(值1，值2。。);批量添加数据
INSERT INTO 表名 (字段1，字段2。。) VALUES(值1， 值2),(值1， 值2),(值1， 值2),(值1， 值2)...;INSERT INTO 表名 VALUES(值1，值2。。), 值1，值2。。),值1，值2。。)..;
UPDATE
修改数据
UPDATE 表名 SET 字段1=值1， 字段2=值2 .。。 [WHERE 条件]；DELETE
删除数据
DELETE FROM 表名 [WHERE 条件];
DQL（数据查询语言）
SELECT
语法：
SELECT 字段列表 FROM 表名列表 WHERE 条件列表 GROUP BY 分组字段列表 HAVING 分组后条件列表 ORDER BY 排序字段列表 LIMIT 分页参数
基本查询
SELECT [DISTINCT(这是去重的)] 字段1 [AS 别名], 字段2。。。 FROM 表名; -- 查询全部 SELECT * FROM 表名；- 注意：
DISTINCT：这是对所有字段相同的记录去重，只有有一个不同，就不会去掉
- 注意：
条件查询
条件：
比较运算符 功能 > 大于 >= 大于等于 < 小于 <= 小于等于 = 等于 <> 或 != 不等于 BETWEEN .. AND .. 在某个范围之内（含两个端点） IN(...) 在in之后的列表中是否存在对应的值 LIKE 匹配的字符，可使用正则 模糊匹配(_匹配单个字符，%匹配任意个字符) IS NULL 是NULL么，是为TRUE 在两个普通的比较运算符之间通过以下运算符连接,NOT除外
逻辑运算符 功能 AND 或 && 并且，两边要都为TRUE OR 或 || 两边有其一为TRUE就行 NOT || ! 非，不是，即后面的条件不成立才是TRUE
聚合查询
函数 功能 count 统计数量 max 最大数 min 最小数 avg 平均值 sum 求和 - 注意：一般情况下，需要使用
group by才行，但如果字段有且只有函数时，可以不需要group by，还有null不统计
- 注意：一般情况下，需要使用
分组查询
语法：
select 字段列表 FROM 表名 [where 条件] group by 分组字段 [having 分组后过滤字段];注意：where与having的区别
- 执行时机不同：where是分组前进行过滤，having是分组后进行过滤
- 判断条件不同：where不能对聚合函数进行判断，而having而可以
排序查询
select 字段列表 from 表名 order by 字段1 排序方式1， 字段2 排序方式2，。。。;- 排序方式
- ASC：升序（默认）
- DESC：降序
- 排序方式
分页查询
语法：
select 字段列表 from 表名 limit 起始索引, 查询记录;- 注意：
- 起始索引从0开始，起始索引=（查询页码-1）*每页显示记录数
- 注意：
DQL的执行顺序
SELECT 字段列表 (6) FROM 表名列表 (1) WHERE 条件列表 (2) GROUP BY 分组字段列表 (3) HAVING 分组后条件列表 (4) ORDER BY 排序字段列表 (5) LIMIT 分页参数 (7)流程函数
- if(value, t, f)：如果value为true则返回t，否则返回f
- ifnull(v1, v2)：如果v1不为空，返回v1，否则返回v2
- case when v1 then r1 when v2 then r2 else r3 end：这个跟java的switch差不多，匹配其中一个，如果都没有就走else
多表查询
内连接
隐式内连接
select 字段列表 from 表1，表2。。 条件。。;显式内连接（效率更好一点）
select 字段列表 from 表1 [inner] join 表2 on 连接条件;
外连接
左外连接：会包含左表的所有数据，即使和右表没有对应的条件相等的情况，也会显示，只是置为null
select 字段列表 from 表1 left [outer] join 表2 on 条件;右外连接：同理
select 字段列表 from 表1 right [outer] join 表2 on 条件;外连接必须要有条件
联合查询：union [all]
select 字段列表 from 表1 union all -- 有all会去重 select 字段列表 from 表2; -- 相当于把两个查询结果拼接 -- 查询结果的字段类型要一样，且列数要相等子查询（又称嵌套查询）
事务
用法
开启事务
命令开始
begin # 开启事务start transaction# 开启事务
修改当前会话提交方式
set @@autocommit = 0
查看当前会话的事务提交方式
select @@autocommit- 1：自动提交
- 0：手动提交
提交事务：当执行完语句后,没报错后提交
commit;回滚事务：当执行出错后，回滚事务，会把数据恢复
rollback;
事务的四大特性：ACID
- 原子性（Atomicity）：事务时不可分割的最小操作单元，要么全部成功，要么全部失败
- 一致性（Consistency）： 事务完成时，必须使所有的数据都保持一致
- 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作的独自环境下运行
- 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的修改是永久的
并发事务问题
脏读：一个事务读到另一个事务还没有提交的数据
- 当一个事务的隔离级别为Read uncommitted时候，会出现这个，也就是另一个事务还没提交的数据，它能查到变化
不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同
- 当有一个并发事务提交了更新的话，前后两次会查出不同数据
幻读：一个事务按照条件查询数据时，没有对应的数据行，但是插入数据的时候，又发现这行数据已经存在
- √：表示会出现
存储引擎
innodb的三大特性：外键、行级锁、事务
索引
索引分类
主键索引：针对于表中主键创建的索引，默认自动创建，只能有一个，关键字：primary
唯一索引：针对同一个表中某一数据列中的值重复，可以有多个，关键字：unique
常规索引：快速定位特定数据，可以有多个
全文索引：全文索引查找的是文本中的关键词，而不是比较索引中的值，可以有多个，关键字：fulltext
在innodb存储引擎中，根据索引的存储形式有：
- 聚集索引：将数据存储与索引放到了一起，索引结构的叶子结点保存了行数据，必须有，而且只有一个
- 如果存在主键，那主键索引就是聚集索引
- 如果不存在主键，将使用第一个唯一索引作为聚集索引
- 如果都没有或没有合适的唯一索引，则innodb会自动生成一个rowid（row_id是这个的合并）作为隐藏的聚集索引
- 二级索引：将数据和索引分开存储，索引结构的叶子节点关联的是对应的主键，可以存在多个
- 聚集索引：将数据存储与索引放到了一起，索引结构的叶子结点保存了行数据，必须有，而且只有一个
索引性能分析
explain：在执行的select语句前面加上它就行
字段解释
id：select查询的序号，表示查询中执行的select子句或操作表的顺序（id相同，从上到下，id越大，先执行）
select_type：表示查询的类型，常见的有simple（简单表查询，没有使用表连接，或者子查询）、primary（主查询，即外层的查询）、union（union中的第二个或者后面的查询语句）、subquery（包含了子查询）等
type：表示连接类型，性能又好到差为null，system，const，eq_ref，ref，range，index，all
prossible_key：显示可能应用在这张表的索引
key：实际使用的索引
key_len：表示索引使用的字节数，在不失精确性的前提下，越短越好
rows：可能必要的查询行数，在innodb的引擎，不总是准确的
filtered：表示返回结果的行数占读取行数的百分比，越大越好
索引的使用
最左前缀法则：如果索引了多咧（联合索引），要遵守最左前缀法则即查询从最左列开始，并且不跳过索引中的列，如果跳过了某列，后面的字段索引将失效，最左列的字段一定要存在条件中，否则不走索引
范围查询：联合索引中，出现了范围查询(>,<)，范围右侧的索引失效
索引列运算：不要在所索引列上进行运算操作，索引将失效
字符串不加引号：不加单引号索引会失效
模糊查询：头部模糊查询，索引失效，尾部模糊不失效
or连接的条件：用or分隔开的条件，如果or前的条件的列有索引，而后面的列没有索引，那么涉及的索引不会被用到
数据评估：当mysql认为不用索引更快时不会使用
sql提示：当有多个索引的使用，可以使用语句提示mysql执行
- use index（索引名）：推荐使用这个索引，不一定被mysql接受
- Ignore index（索引名）：忽略索引
- force index（索引）：强制使用该索引
覆盖索引：尽量使用覆盖的索引（也就是返回的列在索引能找到），减少select *可能导致的回表查询
前缀索引：当字段为字符串的时候，有时候需要索引很长的字符串，这时候可以只将字符串的一部分前缀
create index idx_xx on table_name(column(n))
SQL优化
insert优化
- 批量查询：
insert into tb_test values(1, 2), (1, 2) ..... - 手动提交事务：也就是开始事务去插入，插入完了，再提交事务一起插入
- 主键顺序查询：这个就是优化索引的构建啥的，会更快一点
- 大量数据使用load
order by 优化
- using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后再排序缓冲区中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫file sort
- using index：通过有序索引顺序扫描直接返回有序的数据
- 创建的索引一般是按照升序，如果排序按降序就会触发全表扫描
- 所以尽可能的按升序，或对降序进行索引
group by 优化
- 也需要满足最左前缀法则
limit优化
- 分页查询时，通过创建索引 ，通过索引查询像主键作为字表，联合全表，再去获取数据
- 主要就是通过索引去优化
count优化
- 几种用法
- count（主键）
- count（字段），服务层还要判断是否为null
- count（*）：InnoDB引擎，不会全部字段取出来，服务层直接按行进行累加
- count（1）：相当于每行都为结果为1，不为null，直接加1
- 按照效率的话：count（字段）< count（主键）< count（1）≈ count(*)
update优化
- 避免由行锁变为表锁
- 当有索引的时候，是行数，当没有的时候是表锁
锁
加锁需要开启事务？
分类：
全局锁：锁住全部的表
- 场景：备份数据库的时候，开启全局锁，确保获得一致性视图
表级锁：锁住单个表
读锁（
lock tables tb_name read）：别的客户端只能读，不能写写锁（
lock tables tb_name write）：同时堵塞其他客户端的读和写释放锁（
unlock tables）元数据锁：也就是锁住表的结构，当有事务的时候，自动开启
意向锁：为了避免DML在执行时，加的行级锁和表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查
分类
意向共享锁（IS）：有 语句select ... lock in share mode 添加
与表锁共享锁（read）兼容，与表锁排它锁（write）互斥
意向排它锁（IX）：由insert，update，select ... for update，delete添加
与表锁（read）或（write）都互斥，意向锁之间不会互斥
行级锁：锁住单个行
- 两种类型的行锁
- 共享锁（S）：允许一个事物去读一行，阻止其他事务获得相同数据集的排它锁
- 排它锁（X）：运行获取排它锁的事物更新数据，阻止其他事务获得相同数据集的共享锁和排它锁
- 分类
- 行锁（record lock）：锁定单个行的记录的锁，防止其他事务对此队形update和delete。在RC和RR隔离级别下都支持
- insert、update、delete自动加排它锁，普通的select，不加任何锁，但是select可以手动加上面的意向锁
- 这个行锁是基于索引的，如果没有索引或失效就会升级为表锁
- 间隙锁（gap lock）：这个就是加载两个数据之间的间隙，如假设有id为3和8，这是加入对select ... where id = 4 gap lock，这时就会锁住3和8的间隙，这时就不能插入数据到3和8之间，如插入一条id为5的，
- 总的来说：这个只是锁一个间隙
- 临键锁（next-key lock）：行锁和间隙锁组合，锁着前面/或后面的多个间隙，就是确保它和它前面/后面的记录不变，在RR隔离级别下支持
- 间隙和临键：
- 索引上的等值查询（唯一索引）：给不存在的记录加锁，优化为间隙锁
- 索引上的等值查询（普通索引）：向右遍历时最后一个值不满足查询要求时，next-key lock退化为间隙锁
- 例如有普通字段为这样的：2<--->3<--->4<--->4<--->5
- 使用select 选择条件为4同时加上共享锁时，这时不止记录会上锁，同时会将第一个前面的间隙锁住和最后一个4的后面的间隙锁住
- 索引上的范围查询（唯一索引），会访问到不满足条件的第一个值为止，对其他满足该条件的都上锁包括间隙
- 行锁（record lock）：锁定单个行的记录的锁，防止其他事务对此队形update和delete。在RC和RR隔离级别下都支持
- 两种类型的行锁