初级java开发（MySQL基础）

原创声明

本文作者：泉幽

转载请务必在文章开头注明出处和作者。

1. SQL基础

• 概念：MySQL是关系型数据库，拥有表文件结构（由数据，索引构成的.frm文件存储在硬盘中）。

• 数据查询语言（DQL）

• select语句执行顺序

select......from......where......group by......having......（order by.....limit x,x）；

条件查询	where（不可以直接连接聚合函数）
分组查询	group by .....having
排序查询	order by xxx asc/desc（默认升序/降序）
分页/模糊查询	limit：skipCoint = (pageNo - 1) * pageSize like：%代表任意多个字符，_代表任意1个字符。会让索引失效
联合/去重查询	union（自动去重，并排序） union all（不去重，不排序，直接返回结果） distinct（去重）
连接/级联查询	内连接：平等关系 等值连接； = 非等值连接； between..... and.... 自连接；一张表看做两张表。自己连接自己。 外连接：主副关系，匹配不上默认null 左外连接（左为主） left join 右外连接（右为主） right join 级联查询：join on

• 数据操纵语言（DML）

• insert(插入)：insert into 表名 （...）values（..）；

• update(更新) ：update 表名 set xxx=xxx where 条件；

• delete(删除）：delete from 表名 where 条件;

注：复制数据也可以使用insert，insert into A表 select * from B表；

• 数据定义语言（DDL）

• create(创建数据库/表/索引）

• alter(修改数据库/表）

• drop(删除表/索引）

1、处理效率：drop>trustcate>delete。

2、drop删除整个表；trustcate删除全部记录，但不删除表；delete删除部分记录。

3、trustcate会将高水线复位；delete高水线保持原位置不动。

注：复制数据也可以使用create，create table 表名 as select语句；

• 数据控制语言（DCL）：grant(赋予用户权限） revoke(收回权限） deny(禁止权限)

• 事务控制语言（TCL）：savepoint (设置保存点）rollback (回滚) commit(提交)

2. 临时表

• 概述：执行sql语句创建的结果集表，连接关闭后空间被释放。

例如：内存临时表（MEMORY引擎）

磁盘临时表（MyISAM引擎）

• 使用场景：

• from子查询

• union联合查询

• distinct去重查询+order by

• order by与gruop by连用（内容不一样时）

3. MyISAM引擎

• 特点：三个文件组成表，可被压缩（数据/索引，分开存储），可转为只读。

外键	不支持
事务	不支持
锁	表锁
索引	全为非聚簇索引（检索效率快）

4. INnoDB引擎

• 特点：MySQL的默认引擎，无法被压缩（数据与索引一起储在tablespace的表空间中），可支持级联删除与更新，有自动恢复机制。

外键	支持
事务	支持
锁	表锁，行级锁
索引	聚簇索引（检索效率一般）

5. 索引

• 概述：索引通过B Tree存储到磁盘，能自动排序分区，并且关联表中的“物理地址”定位相关数据。

• 原理：本质是预排序+树形结构来加快检索效率的。

• 查询一张表有两种检索方式：

第一种方式：全表扫描。

第二种方式：根据索引检索（效率很高，有随机I/O自动排序，有表之间约束；但占有大量物理空间，创建/维护消耗大量时间）。

索引种类
主键索引	数据列不可重复，不能为Null，主键唯一；
唯一索引	数据列不可重复，可以为Null，索引列唯一；
全文索引	查询文本内容；	MyISAM/INnoDB引擎支持
普通索引	可以重复，可以为Null；
组合索引	多个列值组成索引；
索引数据结构
Btree索引	用于排序，分组，范围，模糊查询，比较稳定	INnoDB引擎默认索引
Hash索引	优点：通过hash算法快速定位数据，时间复杂o(1)，效率非 常高。 缺点：但不适合范围查询，因为需要每个key都进行一次hash，并且不稳定（哈希冲突）。

6. B树/B+树数据结构

• B树：

• 内部节点：键与值

• 叶子节点：键与值

• 用于随机检索

• B+树：

• 内部节点：只有键

• 叶子节点（连通的）：键与值

• 用于随机/顺序检索

• 优势：

• 内部节点只有键，空间利用率高，检索速度快。

• 叶子节点连通，利于范围查找，顺序查找。

• 稳定，每次从根节点查询到叶子节点。

7. 数据库事务

• 概念：一组sql语句的集合，同时成功或者同时失败。

• 作用：不同访问技术，有不同的处理机制。

jdbc使用事务：Connection提交与回滚。
mybatis使用事务：SqlSession提交与回滚。
hibernate使用事务：Session提交与回滚。

• 事务四大特征

A: 原子性	事务是最小的工作单元不可再分，要么都执行，要么都不执行。
C: 一致性	事务必须保证多条DML语句同时成功或者同时失败。
I：隔离性	事务是独立的，且拥有隔离等级： ①级：读未提交，无需锁（read uncommitted） 对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。存在（Dirty Read）脏读现象：表示读到了脏的数据。 ②级：读已提交，共享锁（read committed） 对方事务提交之后的数据我方可以读取到，不可重复读。 ③级：可重复读，共享锁（repeatable read） 读取到的数据是幻象。 ④级：序列化读/串行化，范围锁（serializable）
D：持久性	最终数据必须持久化到硬盘文件中，事务才算成功的结束。

8. 数据库设计

• 满足三范式：

• 第一范式（原子性）

• 第二范式（主键依赖）

• 第三范式（主键直接依赖）

• 关于1对1：

• 主键共享（primary key）

• 外键唯一（foreign key+unique）

9. 数据库优化

• 索引覆盖优化

• 索引使用场景

• 小型表：全表扫描效率高

• 中大型表：建立索引效率高

• 超大型表：建立索引（维护代价高），采用分区技术

• 多个字段经常被查询，建立组合索引

• 多个字段，且不重复，建立唯一索引

• 多个字段，且会重复，建立普通索引

• 经常使用CURD，不建立索引

• 不用Where条件，不建立索引

• 索引设计原则

• 索引使用在where之后，而不是select

• 索引列基数越大越好，而不是性别（基数只有男，女）

• 尽量使用短索引（长字符串指定短前缀，减少磁盘I/O，索引缓存中可以容纳更多的key）

• 不要过度索引（浪费物理空间，维护浪费时间）

• 索引最左匹配原则

从最左边为起点开始连续匹配，遇到范围查询（<、>、between、like）会停止匹配。

• SQL优化

• 1，选择合适的引擎

• 2，优化字段的数据类型

注：越小的列访问速度越快，可以使用比Int更小的，如smallint作为主键。

• 3，为搜索字段添加索引

• 4，避免使用select*

• 5，使用ENUM类型而不是VARCHAR

注：字符串底层实际为，TINYINT类型，用于如：性别，状态等有限固定长度。

• 6，尽量使用NOT NULL

注：Null需要额外的空间。

• 7，使用固定长度的表

注：更容易被缓存/重建，但不使用也会占空间。

• 8，采用redis缓存技术生成ID

• 防止SQL注入

sql注入的检测方法一般采取辅助软件或网站平台来检测，软件一般采用sql注入检测工具jsky，网站平台就有亿思网站安全平台检测工具。MDCSOFT SCAN等。采用MDCSOFT-IPS可以有效的防御SQL注入，XSS攻击等。

注意措施	采取措施
永远不要信任用户的输入	对用户的输入进行校验，可以通过正则表达式，或限制长度；对单引号和双"-"进行转换等。
永远不要使用动态拼装sql	可以使用参数化的sql或者直接使用存储过程进行数据查询存取。
永远不要使用管理员权限的数据库连接	可以为每个应用使用单独权限，有限的数据库连接。
永远不要把机密信息直接存放或加密	可以给出尽可能少的提示对于应用的异常信息，最好使用自定义的错误信息对原始错误信息进行包装

• 水平拆分

• 分表：将表按照一定规则分成多个表。

• 分库：将表按照一定规则，部署到不同数据库。

• 垂直拆分

• 分表：将表按照字段分成多个表。

• 分库：将表按照业务分类，部署到不同数据库。

10. 数据库存储过程

概述：传统的SQL语句执行时候，需要经历编译阶段；而MySQL的数据存储过程：是将SQL语句预先写完，通过一定的指令存储在服务器端的集合。需要的时候调用，相比下不仅效率高，灵活性强，可重复使用，还能减低网络负载。

该章节详情可以看：https://www.cnblogs.com/fengxia6/p/16939312.html

• 基础语法

• 创建/调用

delimiter $$
 create procedure proc01 ()    #创建存储过程，过程名为proc01，不带参数
 -> begin                      #过程体以关键字BEGIN开始
 -> create table student(id int,name char(10),age int);
 -> insert into student values(1,'zhangsan',18);
 -> insert into student values(2,'lisi',18);
 -> select * from student;
 -> end $$                     #过程体以关键字END结束
 ​
 delimiter ;                   #将语句的结束符号恢复为分号
 ​
 call proc01;                  #调用存储过程

• 查看

 show create procedure proc01\G       #查看存储过程的具体信息
 show procedure status like '%Proc01%'\G   #查看存储过程的状态

• 删除

drop procedure if exists proc01;
 #仅当存在时删除，不添加If EXISTS 时，如果指定的过程不存在，则产生一个错误。
 #存储过程内容的修改方法是通过删除原有存储过程，之后再以相同的名称创建新的存储过程。

• 控制语句

• 条件语句 if-then-else . .. . end if

delimiter $$  
 create procedure proc03(in innum int)  #创建存储过程proc03,参数名为innum，类型为int 
 -> begin 
 -> declare var int;         #定义变量var为int类型 
 -> set var=innum*2;         #变量var的值等于传入的参数值乘2
 -> if var>=10 then          #当var的值大于10时，id值会加1，否则减1
 -> update t set id=id+1;  
 -> else 
 -> update t set id=id-1;  
 -> end if;  
 -> end $$
  
 delimiter ;
 call proc03(8);    #调用存储过程，并传入参数8
 call proc03(3);    #调用存储过程，并传入参数3

• 循环语句 while ···· end while

 delimiter $$                   #修改默认结束符为$$
 create procedure proc04()      #创建存储过程proc04,无参数
 -> begin                       #过程体以关键字begin开始
 -> declare var int(10);        #定义变量var为int类型
 -> set var=0;                  #var的起始值为0
 -> while var<6 do              #使用while循环，当var值小于6时满足条件，则向表中插入var的值
 -> insert into t values(var);  
 -> set var=var+1;              #每次循环后var值自增1
 -> end while;                  #结束while循环
 -> end $$                      #创建存储过程结束
 ​
 delimiter ;                    #重新修改默认结束符为原始的;
 call proc04;                   #调用存储过程proc04

• 参数分类

• IN 输入参数： 表示调用者向过程传入值。（传入值可以是字面量或变量）

• OUT 输出参数： 表示过程向调用者传出值。（可以返回多个值)（传出值只能是变量）

• INOUT 输入输出参数： 既表示调用者向过程传入值，又表示过程向调用者传出值。（值只能是变量）

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
BEGIN
    存储过程体
END

11. 数据库集群搭建

• MySQL数据主从同步

• 通过复制的方式，实现同步。

• binlog线程：主服务器数据写入二进制日志（Binary log）

• I/O线程： 从服务器读取主服务器的二进制日志，写入服务器中继日志（Relay log）

• SQL线程： 读取中继日志，解析主服务器数据，并在从服务器重放

图11.1 MySQL数据库主从同步过程