深入学习和回顾MySQL

MySQL学习巩固拓展，主要针对那些学习过MySQL数据库的人，再次回顾和深入学习

1、数据库分类

关系型数据库：

• MySQL, Oracle, Sql Server, DB2, SQLlite...

• 通过表和表之间，行和列之间的关系进行数据的存储，学员信息表， 考勤表,

非关系型数据库：

• Redis，MongDB...

• 非关系型数据库，对象存储，通过对象的自身的属性来决定。

MySQL简介

MySQL是-一个关系型数据库管理系统 前世：瑞典MySQL AB公司 今生：属于Oracle旗下产品 MySQL是最好的RDBMS (Relational Database Management System)，关系数据库管理系统)应用软件之一。 开源的数据库软件

数据库xx语言

DDL 定义

DML 操作

DQL 查询

DCL 控制

2、数据库

列类型

数值

类型	描述	大小
tinyint	十分小的数据	1个字节
smallint	较小的数据	2个字节
mediumint	中等大小的数据	3个字节
int	标准的整数	4个字节
bigint	较大的数据	8个字节
float	浮点数	4个字节
double	浮点数	8个字节(存在精度问题! )
decimal	字符串形式的浮点数	金融计算的时候，一般是使用decimal

字符串

类型	描述	大小
char	字符串固定大小的	0~255
varchar	可变字符串	0~65535
tinytext	微型文本	2^8- 1
text	文本串，保存大文本	2^16 -1

时间和日期

类型	格式	描述
date	YYYY-MM-DD	日期格式
time	HH：mm：ss	时间格式
datetime	YYYY-MM-DD HH：mm：ss	最常用的时间格式
timestamp	1970.1.1到现在的毫秒数	时间戳
year		年份表示

null

• 没有值，未知

• 注意，不要使用NULL进行运算，结果为NULL

阿里巴巴规范里的，公司开发的数据库表基本都要有的几个字段

id：主键

version：乐观锁

is_delete：伪删除

gmt_create：创建时间

gmt_update：修改时间

数据库引擎

INNODB：默认使用

MYISAM：早些年使用

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大，约为两倍

常规使用操作:

• MYISAM：节约空间，速度较快

• INNODB：安全性高，事务的处理，多表多用户操作

外键

其实这个话题是老生常谈，很多人在工作中确实也不会使用外键。包括在阿里的JAVA规范中也有下面 这一条 【强制】不得使用外键与级联，一切外键概念必须在应用层（就是在代码中）解决。 但是呢，询问他们原因,大多是这么回答的 每次做DELETE或者UPDATE都必须考虑外键约束，会导致开发的时候很痛苦，测试数据极为不方便

去重

作用：去除SELECT查询出来的结果中重复的数据，重复的数据只显示一条

distinct

删除

delete命令

--删除数据（避免这样写，会全部删除）
 DELETE FROM student
 ​
 --删除指定数据
 DELETE FROM student WHERE id = 1

TRUNCATE命令

作用：完全清空一个数据库，表的结构和索引约束不会改变

 --清空student表
 TRUNCATE student

delete和truncate的区别

• 相同点:都能删除数据，都不会删除表结构

• 不同:

  • TRUNCATE 重新设置自增列计数器会归零

  • TRUNCATE 不会影响事务

 --不会影响自增

DELETE FROM student

 --自增归零

TRUNCATE TABLE student

了解即可： DELETE删除的问题，重启数据库，现象

• InnoDB 自增列会从1开始 (存在内存当中的，断电即失)

• MyISAM继续从 上一个自增量开始(存在文件中的，不会丢失)

函数：concat（'a','b','c'）：拼接函数，参数个数可变

SELECT CONCAT('姓名：',studentName) AS 新名字 FROM student

查询结果如下：

新名字
姓名：张三
姓名：李四
姓名：朱小明

有的时候，列名字不是那么的见名知意。我们起别名 AS 字段名 as 别名 表名 as 别名

3、连表查询

操作	描述
inner join	如果表中至少有一个匹配， 就返回行
left join	会从左表中返回所有的值，即使右表中没有匹配
right join	会从右表中返回所有的值，即使左表中没有匹配

4、排序

GROUP BY：分组

HAVING：过滤分组必须满足的条件

ORDER BY：排序，（升序：ASC），（降序：DESC）

5、MySQL函数

常用函数

数学函数

ABS（）：绝对值、CEILING（）：向上取整、FLOOR（）：向下取整、RAND（）：返回一个0~1之间的随机数、SIGN（）：判断一个数的符号

LOWER（）：转小写字母、UPPER（）：转大写字母

 --替换出现的指定字符，在'坚持就能成功'中把'坚持'替换成'努力' 
SELECT REPLACE('坚持就能成功','坚持','努力')

SUBSTR（'热爱学习',1,3）：返回指定的子字符串(源字符串，截取的位置，截取的长度)

REVERSE（）：反转字符串

时间和日期函数：

函数	描述
CURRENT_DATE（）	获取当前日期（年月日）
CURDATE（）	获取当前日期（同上，年月日）
NOW（）	获取当前时间（年月日时分秒）
LOCALTIME（）	本地时间
SYSDATE（）	系统时间

系统函数：

SYSTEM_USER（）：系统登录用户

VERSION（）：获得当前mysql版本

聚合函数（常用）

函数	描述
COUNT()	计数
SUM()	求和
MAX()	最大值
MIN()	最小值
AVG()	平均值

count()语法：

（1）count(*)---包括所有列，返回表中的记录数，相当于统计表的行数，在统计结果的时候，不会忽略列值为NULL的记录。

（2）count(1)---忽略所有列，1表示一个固定值，也可以用count(2)、count(3)代替，在统计结果的时候，不会忽略列值为NULL的记录。

（3）count(列名)---只包括列名指定列，返回指定列的记录数，在统计结果的时候，会忽略列值为NULL的记录（不包括空字符串和0），即列值为NULL的记录不统计在内。

（4）count(distinct 列名)---只包括列名指定列，返回指定列的不同值的记录数，在统计结果的时候，在统计结果的时候，会忽略列值为NULL的记录（不包括空字符串和0），即列值为NULL的记录不统计在内。

count(*)、count(1)、count(列名)执行效率比较：

（1）如果列为主键，count(列名)效率优于count(1)

（2）如果列不为主键，count(1)效率优于count(列名)

（3）如果表中存在主键，count(主键列名)效率最优

（4）如果表中只有一列，则count(*)效率最优

（5）如果表有多列，且不存在主键，则count(1)效率优于count(*)

数据库级别MD5加密

什么是MD5

MD5信息摘要算法（英语：MD5 Message-Digest Algorithm），一种被广泛使用的密码散列函数，可以产生出一个128位（16字节）的散列值（hash value），用于确保信息传输完整一致。MD5由美国密码学家罗纳德·李维斯特（Ronald Linn Rivest）设计，于1992年公开，用以取代MD4算法。这套算法的程序在 RFC 1321 标准中被加以规范。1996年后该算法被证实存在弱点，可以被加以破解，对于需要高度安全性的数据，专家一般建议改用其他算法，如SHA-2。2004年，证实MD5算法无法防止碰撞（collision），因此不适用于安全性认证，如SSL公开密钥认证或是数字签名等用途。

6、事务

事务的四个特性

原子性(Atomicity)

要么都成功，要么都失败

一致性(Consistency)

事务前后的数据完整性要保证一致

持久性(Durability)

事务一旦提交则不可逆，被持久化到数据库中!

隔离性(Isolation)

事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务, 不能被其他事务的操作数据所干扰，事务之间要相互隔离。

隔离导致的问题

脏读：

指一个事务读取了另外一个事务未提交的数据。

不可重复读：

在一个事务内读取表中的某一行数据，多次读取结果不同。(这个不一定是错误，只是某些场合不对)

幻读：

是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

事务的隔离级别

事务隔离级别	脏读	不可重复读	幻读
读未提交(read-uncommitted)	是	是	是
不可重复读(read-committed)	否	是	是
可重复读(repeatable-read)	否	否	是
串行化(serializable)	否	否	否

读未提交，顾名思义，就是一个事务可以读取另一个未提交事务的数据。

读提交，顾名思义，就是一个事务要等另一个事务提交后才能读取数据。

重复读，就是在开始读取数据（事务开启）时，不再允许修改操作

串行化，是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

7、索引

MySQL官方对索引的定义为：索引(Index) 是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

索引分类

在一个表中，主键索引只能有一个，唯一索引可以有多个

• 主键索引 （PRIMARY KEY）

  • 唯一标识，不可重复

• 唯一索引 （UNIQUE KEY）

  • 避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引

• 常规索引 （KEY/INDEX）

  • 默认的，可以用index、key关键字设置

• 全文索引 （FullText）

  • 在特定的数据库引擎下才有，MyISAM

  • 快速定位数据

索引原则

• 索引不是越多越好

• 不要对进程变动数据加索引

• 小数据量的表不需要加索引

• 索引一般加在常用来查询的字段上！

索引的数据结构：Hash类型的索引

Btree：InnoDB的默认数据结构

8、数据库备份

• 直接拷贝物理文件

• 在可视化根据中手动导出（转储SQL文件）

• 命令行 mysqldump（mysqldump -hlocalhost -uroot -p123456 哪张表 > 位置）

#  mysq1dump -h主机 -u用户名 -p密码 数据库 > 物理磁盘位置
 mysqldump -hlocalhost -uroot -p123456 mybatis > D:/mybatis.sql   --备份一个数据库
 ​
 #  mysq1dump -h主机 -u用户名 -p密码 数据库 表名 > 物理磁盘位置（表名是多个，中间用空号隔开）
 mysqldump -hlocalhost -uroot -p123456 mybatis student  > D:/mybatis.sql   --备份一个数据库中的表
 ​
 #  导入数据库
 source 备份数据库的文件名
 source D:/a.sql

9、规范数据库设计

为什么需要设计数据库

当数据库比较复杂的时候，我们就需要设计了 糟糕的数据库设计:

• 数据冗余，浪费空间

• 数据库插入和删除都会麻烦、异常

• 程序性能差

良好的数据库设计:

• 节省内存空间

• 保证数据库的完整性

• 方便我们开发系统

软件开发中，关于数据库的设计

• 分析需求：分析业务和需要处理的数据库的需求

• 概要设计：设计关系图E-R图

三大范式

为什么需要数据规范化?

• 信息重复

• 更新异常

• 插入异常

  • 无法正常显示信息

• 删除异常

  • 丢失有效的信息

三大范式

第一范式(1NF)

原子性：保证每一列不可再分

第二范式(2NF)

前提：满足第一范式

每张表只描述一件事情

第三范式(3NF)

前提：满足第一范式和第二范式

第三范式需要确保数据表中的每一列数据都和主键直接相关， 而不能间接相关。

10、JDBC

比较重要的一个对象（statement）

Jdbc中的statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查,只需要通过这个对象向数据库发送增删改查语句即可。 Statement对象的executeUpdate方法，用于向数据库发送增、删、改的sq|语句，executeUpdate执行完后, 将会返回一个整数(即增删改语句导致了数据库几行数据发生了变化)。 Statement.executeQuery方法用于向数据库发送查询语句，executeQuery方法返回代表查询结果的ResultSet对象。

SQL注入

本质就是拼接字符串

SQL注入是比较常见的网络攻击方式之一，它不是利用操作系统的BUG来实现攻击，而是针对程序员编写时的疏忽，通过SQL语句，实现无账号登录，甚至篡改数据库。

SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息。

Statement对象不安全（SQL注入），PreparedStatement对象可以防止SQL注入

conn = JdbcUtils.getConnection(); // 获收数据库连接
 st = conn. createStatement(); //获SQL的执行对象
 String sql = "INSERT INTO users(id, NAME , PASSWORD , email' , birthday')"+"VALUES(4, 'kuangshen' , ' 123456 , ' 24736743@qq.com , ' 2020-01-01')";
 int i = st.executeUpdate( sql);//SQL写完，直接执行

conn = JdbcUtils.getConnection();
String sql = "insert into users(id," NAME', PASSWORD , email , birthday') values(?,?,?,?,?)";
st = conn.prepareStatement(sq1); //预编SQL，先写sql，然后不执行
//手动给参数赋位
//st.setInt( (parameterlIndex:), (param:) ); //id
st.setInt(1, 4); //id
st.setString(2,"qinjiang");
st.setString(3,"1232112");
st.setString(4,"24734673@qq. com");
st.setDate(5,(new Date().getTime());
//执行
int i = st.executeUpdate();
             
//PreparedStatement防止SQL注入的本质，把传递进来的参数当做字符
//假设其中存在转义字符，比如说'会被直接转义

数据库连接池

数据库连接---执行完毕---释放

连接---释放 这个过程是十分浪费资源的

池化技术：准备一些预先的资源，过来就连接预先准备好的

编写连接池，实现一个接口DataSource

开放数据源实现

DBCP、C3P0、Druid

使用了这些数据库连接池之后，我们在项目开发中就不需要编写连接数据库的代码了!

SpringBoot默认数据源：HikariDataSource

CodingLabs - MySQL索引背后的数据结构及算法原理