思维导图模式 -- 深度理解及复习 数据库 知识

基本概念

mysql引擎

InnoDB：默认引擎，稳定，支持事务
MyIsam：不支持事务，快，不稳定

区分mysql的客户端和服务器端

mysql是服务器端
用java语言操作mysql是客户端

mysql连接命令

本地 ： mysql -u用户名 -p密码
服务器端 ： mysql -u用户名 -p密码 -h 服务器ip地址 -P 端口号3306

MySQL 语法使用

数据库操作

查询所有数据：show databases;
删除数据库：drop database db_name;
使用数据库：use db_name;
创建数据库：create database db_bame character set utf8mb4;

表操作

创建表：create table table_name(…);
删除表：drop table table_name;
查询所有表：show tables;
修改表结构：alter table table_name add clumom 字段名 类型

常用数据类型

数值类型：int，float，decimal（对应BigDecimal）
字符串类型：varchar(size)，text
日期类型：datetime

数据操作

新增数据

添加单条数据：insert into table_name(字段名) values(…)
添加多行数据：insert into table_name(字段名…) value(…),(…)
修改数据：update 表名 set 字段名=值 where…

清空数据

delete from 表名 where…

查询数据

全列查询：select * from 表名

指定列查询：select 列名 from 表名;（企业级开发正确使用姿势）

表达式查询：select math+10 from 表名;（企业级开发禁止使用）

别名查询：select uname as username from 表名;

聚合查询：count()、sum()、avg()、max()、min()
group by：select role,max(salary),min(salary),avg(salary) from emp group by role;
having：select role,max(salary),min(salary),avg(salary) from emp group by role having avg(salary)<1500;

去重：distinct (必须放到列最前面)
select distinct 列名 from 表名

排序：order by 成绩 desc / asc

where查询：
常见运算符：between and ，like “_喜欢%”，and，or，not

分页查询：limit 3, 3（查询 3 条数据，跳过前 3 条）

多表查询：
内联查询：inner join（企业规定：禁止 3 张表以上的连表查询）
select a.*, b.* from a inner join b on a.id=b.aid
外联查询：
左联查询：left join
右联查询：right join
子查询：select * from stu where classid in (select classid from student where username=“星星”)

表的约束
非空约束：not null
唯一约束：unique
主键约束：primary key
外键约束：foreign key
默认值：default

物理外键不用，用逻辑外键

数据库设计

表间关系

一对一
一对多
多对多

三范式

（防止数据冗余）
第一范式（1NF）:列不可再分【保证原子性】
第二范式（2NF）【消除了部分依赖】确保表中的每列都和主键相关
第三范式（3NF）【消除传递依赖】确保每列都和主键列直接相关,而不是间接相关

索引

（MySQL 高效最主要的手段）

查看一个表的所有索引
show index from 表名;

索引类型

普通索引
create index 索引名 on 表名(字段)

主键索引
无需显式创建

唯一索引
create unique index 索引名 on 表名(字段)

组合索引
create index 索引名 on 表名(字段A，字段B)

删除索引
drop index [索引名] on [表名]

索引注意事项

1.生产服务器，不要直接执行添加索引的操作（创建索引的过程非常耗时，且数据量越大，创建的时间越长）
2.对于多读（查询的）场景适合使用索引，而对于添加、删除比较多的场景，那么索引就不适用
3.避免使用查询的时候，不触发索引查询，比如在列上进行赋值运算

什么情况下索引不会触发？

1.当对一个列（已经设置索引的列），进行赋值查询的时候
2.避免使用 like 查询，当使用模糊查询的时候有可能不触发索引，比如当使用 like '%xxx%‘ 不会触发索引的，使用 like ‘xxx%’ 是可以触发索引的
3.当使用的是组合索引的时候，一定要遵顼最左匹配原则
4.尽量避免使用 or 查询，有可能导致索引不生效
5.不要使用 != <> 都会导致索引不生效
6.如果是字符串查询，一定要加上单引号，否则就会导致索引失效

如何优化 MySQL 的性能？

1.在查询比较多的关键列上加索引
2.开启慢查询日志，找到执行比较慢的 SQL，针对性的进行优化
3.能使用主键索引的时候，经常使用主键索引
4.分表（垂直分割）分库（水平分割）
5.提高数据库的硬件配置，更换一个读写性能更好的硬盘，更换一个更好更大的内存

主键索引 vs 普通索引

1.主键索引不需要显示从创建
2.主键索引不能删除，而普通索引可以
3.主键索引查询更快，而普通索引因为有回表查询，索引性能没有主键索引性能高

如何开启慢查询？

1.修改配置文件
修改配置文件 my.cnf，在 [mysqld] 下的下方加入：

[mysqld]
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow_query.log
long_query_time = 1

2.重启MySQL服务

service mysqld restart

事务

（MySQL 稳定的使用）

事务特性（ACID）

1.隔离性：并发事务执行时，隔离问题
2.原子性：要么全部成功，要么全部失败
3.持久性：事务执行完成之后，结果要一直保存下来
4.一致性：事务执行前后，数据要保证是正确的

并发事务存在的问题

脏读：事务 A 读取到了事务 B 未提交的数据，事务 B 回滚了，读取到了不存在的数据
不可重复读：事务 A 使用相同的查询条件，读取到了不一样的结果，因为这个过程中，事务 B 修改了数据
幻读：事务 A 将数据修改之后，事务 B 又添加了一条数据，导致事务 A 执行的结果和预期的不一致。

不可重复读 vs 幻读

不可重复读的侧重点描述的是修改操作，而幻读描述的添加或删除

事务的隔离级别

需要解决问题：脏度，不可重读读，幻读
1.读未提交：都不解决
2.读已提交：解决脏度
3.可重复读：解决脏度，不可重读读（默认隔离级别）
4.串行化：全部解决（缺点：性能不高）

幻读的解决方案

1.MVCC，也就是版本号的方法，处理时看版本号，如果版本号不一致，说明中途已经进行了修改，说明是幻读
2.gap 间隙锁，事务A在操作的时候加锁，B进不来，只能等A操作完成后B再操作

JDBC编程

1.创建一个 DataScource 对象（准备阶段）
2.创建一个 Connect 对象（连接到数据库服务器）
3.使用 PrepareStatement 对象拼接 SQL 语句
4.执行 SQL（相当于敲击回车）
5.查询结果
6.管理连接，释放资源（退出控制台）