初识 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 驱动 -> 数据库