MySQL基础

MySQL基础

1、数据库

1.1、概念

软件，安装在操作系统之上，本质还是文件的存储

关系型数据库：SQL

MySQL，Oracle，Sql Server，DB2，SQLlite，

通过表和表之间，行和列之间进行存储

可以把每张表看作一个类，每行为一个对象

非关系型数据库：NoSQL （not Only）

Redis，MongDB，

存对象，通过对象自身属性决定

1.2、 DBMS

数据库管理系统，维护和获取数据

应用->数据库管理系统（管理和操作）->数据库（存储）

1.3、MySQL

关系型数据库管理系统

属于Oracle下产品

适用于中小型网站，大型可做集群

安装建议：

1. 尽量不使用.exe

2. 尽量使用压缩包安装

启动：

退出：

1.4、Workbench

1.5、数据库语言

DDL 定义

DML 操作

DQL 查询

DCL 控制

1.6、数据库引擎

InnoDB

安全性高，支持事务处理，多表多用户操作，

在数据库表中只有*.frm文件，及上级目录下的ibdata1文件

MYISAM

节约空间，速度较快，

文件：*.frm 表结构的定义文件，

​ *.MYD 数据文件（data)

​ *.MYI 索引文件（index)

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

1.7、数据库的字符集编码

CHARSET=utf8 --不设置的话，mysql不支持中文

2、MySQL数据管理

2.1、外键

1. 在创建表时添加约束
2. alert 表添加约束

物理外键，不建议使用

最好数据库作单纯的表，程序实现外键

2.2、DML

删除

delete 和truncate

• 相同点：都不会删除表结构
• 不同：truncate 重新设置自增列

2.3、分页

limit 起始下标，页面大小（（n-1）*pageSize,pageSize)

limit 0,5

2.4 Having

group by 后过滤

2.5 MD5

不可逆，MD5破解网站的密码为常见值的字典查找，不是真的破解

MD5()

3、事务

3.1、事务原则

ACID原则 原子性Atomicity，一致性Consistency，隔离性Isolation，持久性Durability

A：一组操作要么都成功要么都失败

C：符合逻辑运算，数据完整性保持一致

I：针对多个用户同时操作，排除其他事务对本次事务的影响

  -  脏读：一个事务读取了另一个事务还未提交的数据
  -  不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同
  -  幻读：一个事务内读取到了别的事务插入的数据，导致前后读取不一致

设置隔离级别：

set transaction isolation level 设置事务隔离级别
select @@tx_isolation 查询当前事务隔离级别

设置	描述
Serializable	可避免脏读、不可重复读、虚读情况的发生。（串行化）
Repeatable read	可避免脏读、不可重复读情况的发生。（可重复读）
Read committed	可避免脏读情况发生（读已提交）。
Read uncommitted	最低级别，以上情况均无法保证。(读未提交)

D：没有提交恢复到原状，提交后不可逆，被持久化到数据库中

SET AUTOCOMMIT =0 --关闭自动提交

START TRANSACTION --标记事务开始

COMMIT --提交

ROLLBACK --回滚

SET AUTOCOMMIT =1 --开启自动提交

--保存点
SAVEPOINT [NAME] --保存点
ROLLBACK TO SAVEPOINT [NAME] --回滚到保存点
RELEASE SAVEPOINT [NAME]  --释放保存点

4、索引

索引index是帮助SQL高效获取数据的数据结构

4.1、索引分类

• 主键索引（primary key）
• 唯一索引（unique key）
• 常规索引（index）
• 全文索引（FullText） 快速定位数据

4.2、分析索引

EXPLAIN +SQL语句

4.3、索引原则

• 不要对经常变动的数据加索引
• 小数据量表不需要加索引
• 索引一般加在常用来查询的字段上
• 索引不是越多越好

MySQL索引的数据结构和算法原理

5、权限管理和备份

5.1、用户管理

-- 查找用户
select * from mysql.user;
-- 创建用户
create user lucas identified by'123456';
set password for lucas='111111';
-- 重命名
rename user lucas to lucas2;
-- 用户授权
grant all privileges on *.* to lucas;
-- 查看权限
show grants for lucas;

5.2、备份

• 保证重要的数据不丢失
• 数据转移

备份方式：

• 直接拷贝物理文件
• 命令行导出 mysqldump

mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql
# mysqldump -h主机 -u用户名 -p密码 数据库 表1 表2>物理磁盘位置/文件名

# 导入 登录的情况下，切换到指定数据库
source D:/a.sql

6、规范数据库设计

6.1、为什么需要设计

糟糕：

• 数据冗余，浪费空间
• 插入和删除都麻烦（不要使用物理外键）
• 程序的性能差

良好：

• 节省内存空间
• 保证数据库完整性
• 方便开发

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

• 分析需求：分析业务
• 概要设计：设计关系图E-R图

设计步骤（个人博客）：

• 收集信息
  • 用户表（用户登录注销，用户个人信息，写博客，创建分类）
  • 分类表（文章分类，谁创建的）
  • 文章表（文章的信息）
  • 友链表（友情链接信息）
  • 自定义表（系统信息，某个关键的字，或者一些主字段） key:value
• 标识实体（把需求落实到每个字段）
• 标识实体之间的关系

6.2、三大范式

三大范式：

• 数据库表的每一列都是不可分割的原子数据项，每一列不可再分
• 每张表只描述一件事情
• 确保数据库表中的每一列数据都和主键直接相关，不能间接相关

规范性和性能的问题：

• 关联查询的表不能超过三张表
• 性能更重要
• 故意增加计算列（从大数据量降低为小数据量的查询：索引）
• 故意增加冗余字段（从多表查询变为单表查询）

7、JDBC

7.1、数据库驱动

应用程序->数据库驱动（MySql驱动，Oracle驱动）->数据库

7.2、JDBC

为了简化开发人员对数据库统一的操作，提供了一个（Java操作数据库）的规范，即JDBC

应用程序->JDBC->数据库驱动（MySql驱动，Oracle驱动）->数据库

• java.sql

• javax.sql

• 数据库驱动

7.3、JDBC程序

1. 加载驱动
2. 连接数据库DriverManager
3. 获得执行sql的对象Statement
4. 获得返回的结果集ResultSet
5. 释放连接

7.4、PreparedStatement对象

防止SQL注入 or 1=1, 把传递进来的参数当作字符

使用？占位符代替参数

8、IDEA

9、事务

1. 开启事务conn.setAutoCommit(false)
2. 一组业务执行完毕，提交事务
3. 可以在catch语句中显式地定义回滚语句，但默认失败就会回滚

10、数据库连接池

数据库连接->执行->释放

连接->释放 过程十分消耗资源

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

• 常用连接数
• 最小连接数 和常用连接数一般设置相同
• 最大连接数
• 等待超时

编写连接池，实现接口DataSource

数据库连接池

DBCP：

C3P0：

Druid：

无论使用什么数据源，实现的datasource接口不变，方法不变

properties文件通过InputStream 数据流 加载到对象Properties中

mit(false)==
2. 一组业务执行完毕，提交事务
3. 可以在catch语句中显式地定义回滚语句，但默认失败就会回滚

10、数据库连接池

数据库连接->执行->释放

连接->释放 过程十分消耗资源

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

• 常用连接数
• 最小连接数 和常用连接数一般设置相同
• 最大连接数
• 等待超时

编写连接池，实现接口DataSource

数据库连接池

DBCP：

C3P0：

Druid：

无论使用什么数据源，实现的datasource接口不变，方法不变

properties文件通过InputStream 数据流 加载到对象Properties中