MySQL知识点汇总

一、MySQL基础

①和数据库相关的操作

1. 查询数据库：show databases；
2. 新建数据库：create database if not exists db_name character set utf8mb4;
3. 使⽤数据库：use db_name;
4. 删除数据库：drop database db_name;

②类型相关

1. 时间：datetime
2. 整数：int
3. 布尔：boolean
4. 字符串：varchar
5. ⽂本：text
6. 超⼤⽂本：longtext
7. ⾦额： decimal

③表相关的操作

1. 查看所有表：show tables；
2. 创建表：create table table_name(id int primary key,…);
3. 删除表：drop table table_name;
4. 查看表结构：desc table_name;
5. 插⼊数据：insert into table_name values(…);
6. 插⼊多条数据：insert into table_name(字段…) values(…),(…);
7. 修改：update tabel_name set 字段=value where 条件。

a)表查询

1. 全列查询：*
2. 指定列查询：字段名
3. 表达式查询
4. 查询时使⽤别名
5. 去重：distinct 【可以使⽤多个条件去重、distinct 必须放在查找字段最前⾯，否则会报错】
6. 排序 order by XXX asc/desc
7. 区间查询：where 字段 between 起始值 and 结束值
8. 模糊查询：like 【%和_前者匹配多个，后者匹配⼀个】
9. 分⻚ limit x,y
10. 聚合查询：sum、count、avg、max、min
11. 分组查询：group by、having【根据科室计算平均⼯资】
12. 联合查询：
    a. 内连接：inner join XXX on 联查条件；
    b. 外连接（左连接和右连接）：left join XXX on 联查条件；
    c. ⼦查询：【查询某个同学的同班同学】

b)表约束

1. ⾮空约束：not null；
2. 唯⼀约束：unique；
3. 主键约束：primary key；
4. 外键约束：foreign key；【物理外键性能低，⼀般不使⽤；推荐使⽤逻辑主键】
5. 默认值：default

c)表设计

1. ⼀对⼀
2. ⼀对多
3. 多对多（三张表、需要添加中间关系表）

二、索引

① 索引分类

a) 普通索引
CREATE INDEX index_name ON table(column(length))

b) 主键索引
创建表的时候设置 primary key 会⾃动创建主键索引。

c) 唯⼀索引
CREATE UNIQUE INDEX indexName ON table(column(length))

d) 组合索引
ALTER TABLE 表名 ADD INDEX 索引名 (name,city,age);

② 索引查询和删除

1. 查询索引：show index from 表名；
2. 删除索引：drop index [索引名] on [表名]。

③注意事项

1. 索引的创建需要时间，如果数据量⽐较⼤创建索引的时间会⽐较⻓，所以不建议直接在⽣产环境来创建索引；
2. 不要在列上做计算，这样会导致不执⾏索引；
3. 尽量不要使⽤ like 查询，可能会导致索引不执⾏，⽐如 like ‘%XXX%’ 就不会执⾏索引，⽽ like’XXX%’ 会执⾏索引。
4. 查询⽐较多使⽤创建索引、反之如果添加和删除⽐较多则不建议创建索引。

④索引不⽣效的⼏种场景

1. 最左匹配原则（组合索引）；
2. 尽量避免 or 查询可能会导致索引失效；
3. 不要在列上进⾏任何操作；
4. 字符串索引必须加引号；
5. 不要使⽤ != 或者 <>；
6. is not in 不会⾛索引；
7. 尽量避免 like 查询，可能会导致索引实现，⽐如 like ‘%XXX%’ 就不会执⾏索引，⽽ like 'XXX%'会执⾏索引。

⑤MySQL 性能调优

1. 创建索引；
2. 禁⽌使⽤导致索引失效的 SQL；
3. 去掉 3 张表以上的联合查询；
4. 开启慢查询⽇志，找到具体的 SQL 进⾏优化；
5. 分库（⽔平分隔，读写分离）、分表（垂直分隔，实现难度⼤）；

⑥常⻅⾯试题

1. 主键索引和唯⼀索引的区别？
   a. 【主键索引不能被删除；主键索引不需要回表查询，唯⼀索引的⾮叶⼦节点存储的只有 id，需要根据 id 查询这条数据】
2. 索引失效的场景有哪些？
3. 如果优化 MySQL？
4. 使⽤索引需要注意哪些事项？
5. 索引的底层是如何实现的？

三、事务

①事务基本要素

a)原子性
⼀个事务中的所有操作，要么全部执⾏成功，要么全部执⾏失败。
原⼦性是事务最重要的特性，全部执⾏失败并不是不执⾏，⽽是通过逆操作 rollback（回滚）数据。
逆操作解释：⽐如 A 账户 -500 元的逆操作就是 A 账户 +500 元。
b)一致性
⼀个事务在执⾏前后数据必须保持⼀种合法的状态。
例如转账操作，不能在执⾏完成之后将账户减成负数，这个负数就是不合法的，也就是不满⾜⼀致性的。
c)持久性
事务执⾏完成之后，它所做的所有修改都是永久的（不会丢失）。
d）隔离性
是⽤来解决并发事务执⾏的问题的。

②事务的使用

start transaction;
update salay set money=money-500 where name='A';
update salay set money=money+500 where name='B';
commit/rollback;

③事务存在的问题

a)脏读
事务 A 读取到了事务 B 更新的数据，但之后事务 B 进⾏了回滚，导致事务 A 读到不存在的数据就是脏数据。
b)不可重复读
事务 A 多次读取同⼀个数据结果不⼀致的问题，在事务 A 多次读取的过程中，事务 B 进⾏了更新并提交，导致事务A 读取到的同⼀数据结果不⼀致的问题。
c)幻读
事务 A 将数据修改之后，事务 B ⼜新增（或删除）了⼀条数据，导致结果和事务 A 预期的不⼀致。
不可重复读 VS 幻读
不可重复度和幻读强调的侧重点不同，不可重复度的侧重点是修改，⽽幻读的侧重点是新增和删除。

④事务隔离级别

四、JDBC 编程

引⼊ jar 包之后执⾏以下流程：

1. 创建⼀个 DataSource 对象（准备阶段）
2. 创建⼀个 Connect 对象（连接到数据服务器）
3. 使⽤ PrepareStatement 对象拼接 SQL 语句
4. 执⾏ SQL（相当于敲下回⻋）
5. 查询结果
6. 管理连接、释放资源（退出控制台）