MySQL 基础知识点

初识 MySQL

MySQL 是一种 关系型数据库管理系统（RDBMS）
开源
默认端口：3306

数据库语言

DDL：Data Definition Language 数据定义语言
DML：Data Manipulation Language 数据管理语言
DQL：Data Query Language 数据查询语言
DCL：Data Control Language 数据控制语言

MySQL 下载与安装

https://www.cnblogs.com/blackBlog/p/12844709.html (详见笔者博客)

MySQL 管理

官方文档：https://dev.mysql.com/doc/refman/5.7/en/

MySQL 命令

1.查看数据库版本：

SELECT VERSION();

2.查看MySQL提供的所有存储引擎：

SHOW ENGINES;

默认使用 InnoDB

3.查看库表的存储引擎

SHOW TABLE STATUS LIKE 'tb_matrix';

数据库的列类型

数值

1.tinyint

2.smallint

3.int 常用

4.mediumint

5.bigint

6.float

7.double

8.decimal
字符串

1.char

2.varchar
时间

1.date

2.time

3.datetime

4.timestamp

数据库字段属性（重点）

自增

通常用于设置唯一的主键，必须是整数类型
非空

NULL

NOT NULL：不赋值就会报错
默认

设置默认值

操作数据库

数据库

-- 创建数据库
CREATE DATABASE `数据库名` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

-- utf8: 字符集编码
-- utf8_general_ci: 不区分大小写

-- 使用数据库
USE [数据库名];

创建数据表

-- 创建数据库表
CREATE TABLE [IF NOT EXISTS] `表名`(
    `字段名` 列类型 [属性] [索引] [注释],
    `字段名` 列类型 [属性] [索引] [注释],
	....
	`字段名` 列类型 [属性] [索引] [注释]
)[表类型][字符集设置][注释]

-- 字符集设置
CHARACTER SET utf8;

修改数据表

-- 修改表名
ALTER TABLE [数据库表名] RENAME AS [新表名];
-- 增加表字段
ALTER TABLE [数据库表名] ADD [字段名] [列属性];
-- 修改表字段
ALTER TABLE [数据库表名] MODIFY [字段名] [列属性]; --修改约束
ALTER TABLE [数据库表名] CHANGE [旧字段名] [新字段名]; --重命名
-- 删除表字段
ALTER TABLE [数据库表名] DROP [字段名];

删除数据表

DROP TABLE IF EXISTS [数据库表名];

数据库表类型（引擎）

-- 引擎
INNODB: 默认使用
MyISAM: 早期使用

InnoDB 和 MyISAM 区别

	MyISAM	InnoDB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键索引	不支持	不支持
全文索引	支持	不支持
表空间大小	较小	约为MyISAM的2倍

MySQL 数据管理

外键

为什么不用外键？

阿里巴巴规范：不得使用外键与级联，一切外键概念必须在应用层解决。

DML

数据库管理语言

新增

-- 语法
insert into [表名] ([字段1], [字段2]...) values ([值1], [值2]...), ([值1], [值2]...);

修改

-- 语法
update [表名] set [字段名] = [值], [字段名] = [值]... where [条件];

删除

-- 语法
delete from [表名] where [条件];

清空

-- 语法
TRUNCATE [表名];

-- 计数器会归零，不会影响事务

DQL

数据库查询语言

-- 查询全部信息
SELECT * FROM [表名];
-- 查询部分信息
SELECT [字段名], ... FROM [表名];
-- AS 给字段名和表名启 别名
SELECT [字段名] AS [别名], ... FROM [表名] AS [别名];

-- 整体语法格式
SELECT * 
FROM [表名]
WHERE ...
GROUP BY ... 【指定结果按照哪个字段来分组】
HAVING ... 【过滤分组的记录必须满足的次要条件】
ORDER BY ...
LIMIT ...

去重

DISTINCT
-- 作用：去除SELECT查询结果中重复的数据

WHERE 条件子句（等值查询）

作用：检索符合条件的语句

1.LIKE （模糊查询）
-- ... WHERE [cloumn_name] like '%[value]%'
说明：
'%' 代表任意字符
'_' 代表1个字符

2.IN
具体的1个或者多个值

3.NULL 和 NOT NULL

4.BETWEEN ... AND ...

JOIN ON （连接查询）

1.LEFT JOIN
左连接
2.INNER JOIN
内连接
3.RIGHT JOIN
右连接

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

分页

...
LIMIT (N-1)*pageSize, pageSize;

N: 代表当前页
pageSize: 页面大小
数据总数/页面大小: 总页数

-- 说明：缓解数据库压力;

排序

ORDER BY ... DESC / ASC
DESC : 倒序排序
ASC : 正序排序

子查询（由里及外）

...
WHERE
(SELECT * FROM ...)

分组和过滤

MySQL 函数

常用函数

-- 系统
SELECT VERSION();
SELECT USER();
SELECT SYSTEM_USER();

--

聚合函数

-- 统计：COUNT(*) / COUNT(1) / COUNT(column_name)
SELECT COUNT(*) FROM TABLE_NAME;
区别：
1.COUNT(column_name)忽略所有的 NULL 值; COUNT(*) / COUNT(1) 不忽略 NULL 值
2.COUNT(*) / COUNT(1) 本质计算行数

-- 求合
SELECT SUM(column_name) FROM TABLE_NAME;

-- 平均值
SELECT AVG(column_name) FROM TABLE_NAME;

-- 最大值
SELECT MAX(column_name) FROM TABLE_NAME;

-- 最小值
SELECT MIN(column_name) FROM TABLE_NAME;

数据库级别的MD5加密

MD5

MD5：信息摘要算法，不可逆性
MD5 破解网站的原理，背后有一个字典，MD5加密后的值，加密前的值

事务

事务是逻辑上的一组操作，要么执行，要么都不执行。

事务原则

事务原则：ACID 原则；原子性，一致性，隔离性，持久性

原子性 Atomicity
事务要么都成功，要么都失败
一致性 Consistency
事务执行前后的数据完整性要保证一致.
隔离性 Isolation
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启事务，不能被其他事务的操作数据干扰
持久性 Durability
事务一旦提交则不可逆，被持久化到数据库中

并发事务会带来哪些问题？

1.脏读：指一个事务读取到另外一个事务未提交的数据
2.幻读：在一个事务内读取表中的某一行数据，多次读取结果不同
3.不可重复读：指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致
4.数据丢失：两个事务都读取了同一数据，事务A修改提交了，事务B也修改提交了。这样对于第一个事务来说就是数据丢失

事务处理

MySQL 默认开启事务自动提交

SET AUTOCOMMIT = 0 /* 关闭 */

SET AUTOCOMMIT = 1 /* 开启 */

START TRANSACTION /* 开启事务 */

-- 提交
COMMIT
-- 回滚
ROLLBACK

事务的隔离级别

InnoDB 存储引擎默认支持隔离级别的 REPEATABLE-READ （可重复读）已经可以完全保证事务的隔离性要求，即达到了 SQL标准 SERIALIZABLE(可串行化)隔离级别。

读取未提交：可能会有脏读、幻读、可重复读

READ-UNCOMMITTED
读取已提交：有效避免了脏读

READ-COMMITTED
可重复读：可以阻止脏读和不可重复读

REPEATABLE-READ
可串行化：有些的避免了脏读、幻读、可重复读

SERIALIZABLE

索引

索引是帮助 MySQL 高效获取数据的数据结构。

提取句子主干，就可以得到索引的本质：索引是数据结构

索引的分类

主键索引（PRIMARY KEY ）
- 唯一的标识，主键不可重复，只能有一列作为主键
唯一索引（UNIQUE KEY）
- 避免重复的列，唯一索引可重复，多个列都可以标识为唯一索引
普通索引（INDEX）
- 默认的
全文索引（Fulltext）
- 在特定的数据库引擎下才有，快速定位数据
组合索引

索引的使用

索引的使用

-- 显示所有的索引
SHOW INDEX FROM [表名];

-- 增加索引
ALTER TABLE `TABLE_NAME` ADD INDEX INDEX_NAME(column_name) COMMENT '索引备注';
ALTER TABLE `TABLE_NAME` ADD UNIQUE INDEX_NAME(column_name) COMMENT '索引备注';

使用函数生成测试数据

-- 函数生成百万数据

/* 创建函数 */
DELIMITER //
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN 
   DECLARE num INT DEFAULT 1000000;
   DECLARE i INT DEFAULT 0;
   WHILE i<num DO
     -- 插入语句
     INSERT INTO `TB_MATRIX`(`MATRIX_NAME`, `MATRIX_CODE`, `IS_DELETED`) VALUES (CONCAT('矩阵',i),          CONCAT('MATRIX-',i), 0);
     SET i = i+1;
   END WHILE;
	 RETURN i;
END;

/* 执行函数 */
SELECT mock_data();

索引原则

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

EXPLAIN

EXPLIAN 输出

列	含义
id	SELECT 标识符
select_type	SELECT 类型
table	表名
partitions	匹配的分区
type	联接类型
possible_keys	可能使用的索引
key	实际选择的索引
key_len	所选键的长度
ref	与索引比较的列
rows	估计要检索的行
filtered	按条件过滤的行百分比
extra	附加信息输出解析

EXPLIAN 解析

id

SELECT 标识符。SELECT 查询中的序号。

select_type

SELECT 类型，可以是下表中显示的任何类型。

SELECT_TYPE	JSON NAME	MEAN
SIMPLE		简单查询（不使用 UNION 或子查询）
PRIMARY		最外层查询
UNION		SELECT 陈述中的第二个或之后的陈述 UNION
DEPENDENT UNION
UNION RESULT
SUBQUERY		首先SELECT 在子查询
DEPENDENT SUBQUERY		首先SELECT在子查询，取决于外部查询
DERIVED		派生吧
MATERIALIZED		物化子查询
UNCACHEABLE SUBQUERY		子查询，其结果无法缓存，必须针对外部查询的每一行重新进行评估
UNCACHEABLE UNION

table

table_name: 表名

partitions

查询将匹配记录的分区。

type

联接类型。

-------------------------------------------------------------------------
最佳类型->最差类型
1.system
该表只有一行

2.const
该表最多具有一个匹配行，该行在查询开始时读取。因为只有一行，所以优化器的其他部分可以将这一行中列的值视为常量。const非常块，因为它只能读取一次。

3.eq_ref

4.ref
从表中读取具有匹配索引值的所有行

5.fulltext
全文索引

6.ref_or_null
类似于 ref, 但除了 MySQL 会额外搜索包含 NULL 值的行。此连接类型优化最常用于解析 子查询。

7.index_merge
此连接类型指示使用索引合并优化。

8.unique_subquery

9.index_subquery

10.range
使用索引选择行，仅检索给定范围内的行

11.index
该index连接类型是一样的ALL，只是索引树被扫描

12.ALL
全表扫描

possible_keys

MySQL可以选择的查询表中的各行的索引

key

KEY 指示MySQL实际决定使用的索引。

key_len

MySQL 实际决定使用的索引的长度

ref

ref显示将哪些列或常量与该 key 列中命名的索引进行比较。

rows

rows 列指示 MySQL 执行查询必须检查的行数。
对于 Innodb, 此数字是估计值，可能并不总是准确的。

filtered

filtered 列知识被表条件过滤的行的估计百分比，最大值 100；这表示未过滤行。值从100减小表示过滤量增加。

extra

包含有关 MySQL 如何解析查询的其他信息

权限管理和数据库备份

用户管理

SQL 命令操作

-- 用户表 mysql.user

-- 创建用户
CREATE USER 用户名 IDENTIFIED BY '密码';

-- 修改密码
SET PASSWORD = PASSWORD('密码'); -- 修改当前登录用户密码
SET PASSWORD FOR 用户名 = PASSWORD('密码'); -- 修改指定用户密码

-- 重命名
RENAME USER 旧用户名 TO 新用户名;

-- 用户授权
GRANT ALL PRIVILEGES ON *.* TO 用户名；

-- 查询用户权限
SHOW GRANT FOR 用户名;

-- 撤销用户权限
REMOVE ALL PRIVILEGES ON *.* FROM 用户名；

MySQL 备份

为什么需要备份？

1.防止数据丢失
2.数据转移

数据库备份方式

1.直接拷贝物理文件
2.可视化工具中手动导出
3.命令行导出（mysqldump）

-- mysqldump -hlocalhost -uroot -proot 数据库名 >磁盘位置
mysqldump -hlocalhost -uroot -proot matrixdb >D:/theonedb.sql

数据库的规约，三大范式

三大范式

为什么需要数据规范化

1.信息重复
2.更新异常
3.插入异常：无法正确显示信息
4.删除异常：丢失有效信息

三大范式

第一范式

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

第二范式

前提：满足第一范式
每张表只描述一件事情，第二范式要求每一列都与主键相关

第三范式

前提：满足第一范式和第二范式
每一列必须和主键相关，不可以间接相关

规范性和性能问题

阿里规范：关联查询的表不得超过三张表

1.考虑商业化的需求和目标，数据库的性能更加重要
2.在规范性能的问题的时候，需要适当的考虑一下 规范性
3.故意给某些表增加一些多余字段
4.增加一些计算列（从大数据量降低为小数据量的查询；增加索引）

JDBC(重点)

数据库驱动

MySQL Driver：mysql-connector-java

应用程序 -> JDBC -> MySQL 驱动 -> 数据库

MySQL 基础知识点

初识 MySQL

数据库语言

MySQL 下载与安装

MySQL 管理

MySQL 命令

数据库的列类型

数据库字段属性（重点）

操作数据库

数据库表类型（引擎）

MySQL 数据管理

外键

DML

新增

修改

删除

清空

DQL

去重

WHERE 条件子句 （等值查询）

JOIN ON （连接查询）

分页

排序

子查询（由里及外）

分组和过滤

MySQL 函数

常用函数

聚合函数

数据库级别的MD5加密

MD5

事务

事务原则

事务处理

事务的隔离级别

索引

索引的分类

索引的使用

索引原则

EXPLAIN

EXPLIAN 输出

EXPLIAN 解析

权限管理和数据库备份

用户管理

MySQL 备份

数据库的规约，三大范式

三大范式

JDBC(重点)

数据库驱动

WHERE 条件子句（等值查询）