[大数据]深入学习和回顾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、数据库

列类型

数值

字符串

时间和日期

null

• 没有值，未知

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

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

id：主键

version：乐观锁

is_delete：伪删除

gmt_create：创建时间

gmt_update：修改时间

数据库引擎

INNODB：默认使用

MYISAM：早些年使用

常规使用操作:

• 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、连表查询

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（）：反转字符串

时间和日期函数：

系统函数：

SYSTEM_USER（）：系统登录用户

VERSION（）：获得当前mysql版本

聚合函数（常用）

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)

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

隔离导致的问题

脏读：

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

不可重复读：

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

幻读：

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

事务的隔离级别

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

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

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

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

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索引背后的数据结构及算法原理