狂神说_MySQL教程
本文最后更新于：2025年6月25日 晚上
安装MySQL
官方下载地址:MySQL :: Download MySQL Community Server (Archived Versions)
下载并解压相应版本的压缩包(推荐压缩包,不然有注册表相关的东西)
配置环境变量(此电脑/属性/高级系统设置/环境变量/系统变量/新建)
变量名：MYSQL_HOME
变量值：D:\DEV\ENV\MySQL-8.0.12-winx64 MySQL解压的位置，bin目录的上一层
配置环境变量(此电脑/属性/高级系统设置/环境变量/系统变量)，找到
Path,最后添加：;%MYSQL_HOME%\bin在解压目录下创建
my.ini文件(UTF-8编码)[mysqld] #设置3306端口号 port=3306 #设置MySQL的安装目录 basedir=D:\\DEV\\ENV\\MySQL-8.0.12-winx64 #设置MySQL数据库的数据存放目录 datadir=D:\\DEV\\ENV\\MySQL-8.0.12-winx64\\data #运行最大连接数 max_connections=200 #运行连接失败的次数。这也是为了防止有人从该主机试图攻击数据库系统 max_connect_errors=10 #服务端使用的字符集默认为utf-8 character-set-server=utf8 [mysql] #客户端使用的字符集默认为utf8 default-character-set=utf8 [client] #客户端默认端口号为3306 port=3306cmd(管理员模式)输入：
mysqld --initialize --console（记住临时密码，最后一行，一会要登录自定义密码）windows下右键左下角windows徽标，选择 命令提示符(管理员)
输入
mysqld install安装服务输入
net start MySQL启动MySQL服务输入
mysql -u root -p回车，输入刚才最后一行显示的临时密码输入
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';更改root用户登录密码
安装Navicat学习版
123云盘：Navicat12下载
提取码:eMu4
安装(navicat121_premium_cs_x64)
将(Navicat_Keygen_Patch_v4.9_By_DFoX)放在navicat的安装目录
- 点（1）中右边的Pathch
- （2）选择Premium，再Languages
- 在（4）中点击右边的Generate，会有注册码出现，复制，打开Navicat，粘贴激活码，若出错，点手动激活
- 将Navicat中上半部分的信息粘贴在破解器的Request Code处，点击左下角的Generate
- 复制生成的码，粘贴在Navicat下半部分 激活！
数据库基本属性
常用的列类型
数值
| 类型 | 占用大小(字节) | 备注 |
|---|---|---|
| int | 4 | 整数型（常用） |
| float | 4 | 浮点数 |
| double | 8 | 会存在精度问题 |
| decimal | 一般表示钱，字符串形式的浮点数 |
字符串
| 类型 | 可表示大小 | 备注 |
|---|---|---|
| char | 0-255 | 固定字符串 |
| varchar | 0-65535 | 可变字符串（常用） |
| text | 2^16-1 | 保存大文本 |
时间日期 java.util.Date
| 类型 | 格式 |
|---|---|
| date | yyyy-mm-dd |
| time | HH:mm:ss |
| datetime | yyyy-mm-dd HH:mm:ssMySQL基本语句 |
| timestamp | 时间戳，从1970.1.1到现在的毫秒数(常用) |
null 空值
null 代表 空值，未知既不是 0 也不是 空串’’
注意：不要使用null值进行试算，结果仍然为null
数据库的字段属性
unsigned-无符号
无符号整数，不能声明为负数
fillzero-填充零
填充0，不足的位数使用0填充，如列属性为 int(3)，值为
5，实际为005auto_increment-自增
自动在上一条记录+1，通常用来设计唯一主键(整数型)，可以自定义起始值和步长
not null-非空
指定非空的列不填充值会报错
关于数据库引擎
| MYISAM | INNODB | |
|---|---|---|
| 事务支持 | × | √ |
| 数据行锁定 | × | √ |
| 外键约束 | × | √ |
| 全文索引 | √ | × |
| 表空间大小 | 较小 | 较大，约为2倍 |
| 物理文件上 | *.frm文件(表结构文件)，*.MYD文件(data,数据文件)，*.MYI文件(index,索引文件) | 只有一个*.frm文件(表结构文件)，及上级目录下的ibdata1文件 |
所有的数据库数据都存放在安装目录下的data目录下，就是my.ini里面的datadir所在的位置，一个文件夹对应一个数据库，其本质上还是文件的形式存在。
字符集编码
CHARSET=utf8 设置字符集编码
不设置MySQL默认编码集是
Latin1，不支持中文，可以在my.ini中设置character-set=utf8来设置默认编码集，
MySQL基本语句
DDL语句——数据定义语言
创建数据库
CREATE DATABASE [IF NOT EXISTS] `数据库名`;删除数据库
DROP DATABASE IF EXISTS `数据库名`;使用某个数据库，如果表名或字段名是特殊字符，需要加`符号
USE `数据库名`;查看所有数据库
SHOW DATABASES;创建表
CREATE TABLE [IF NOT EXISTS] xxx ( `字段名` 列类型(大小) [属性] [索引] [注释]， `字段名` 列类型(大小) [属性] [索引] [注释]， `字段名` 列类型(大小) [属性] [索引] [注释] ) [表类型] [字符集] [表注释] ;修改表名
ALTER TABLE `原表名` RENAME AS `新表名`;增加表字段
ALTER TABLE `表名` ADD `字段名` 列类型(大小);修改表字段
ALTER TABLE `表名` MODIFY `字段名` 列类型(大小); ALTER TABLE `表名` CHANGE `原字段名` `新字段名` 列类型(大小);删除表字段
ALTER TABLE `表名` DROP `字段名`;删除表
DROP TABLE [IF EXISTS] `表名`;
DML语句——数据操纵语言
插入语句
-- 插入全部字段 INSERT INTO `表名` VALUES(值1,值2,值......); -- 只插入部分字段 INSERT INTO `表名`(字段值1,字段值2,字段值......) VALUES(值1,值2,值......);修改语句
-- 不写where条件限制会更改表中所有的记录！！ UPDATE `表名` SET `字段1`='xx',`字段2`='xx' WHERE `字段x`='xxx';删除语句
-- 不写where条件限制会更改表中所有的记录！！ DELETE FROM `表名` WHERE `字段1`='xxx';清空表数据
TRUNCATE TABLE `表名`; DELETE FROM `表名`;DELETE TRUNCATE 命令类型 DML语句 DDL语句 锁定 行级锁定，删除会锁定行 表级锁定，删除会锁定整张表 执行速度 慢，需要维护较多日志，可恢复 快，删除重建表结构，不可恢复，相对大表速度尤为明显 使用对象 TABLE，VIEW TABLE 自增 继续自增 初始化
DQL语句——数据查询语言
| 运算符 | 语法 | 描述 |
|---|---|---|
| AND | a and b | 逻辑与，两个都满足 |
| OR | a or b | 逻辑或，满足一个 |
| NOT | not a | 逻辑非，表不是 |
| IS NULL | a is null | 空 |
| IS NOT NULL | a is not null | 非空 |
| BETWEEN .. AND | a betweent xx and xxx | 在 xx 和 xxx 之间，包含边界值,[xx,xxx] |
| LIKE | like “%a%” | 包含a,%表示模糊匹配，还可以用’_’表示模糊匹配一个字符，如 like “刘_“，刘某 |
| IN | a in (xxxx) | a包含xxxx,在xxxx里面 |
-- 查看MySQL版本
SELECT @@VSERSION;
-- 查询全部的数据
SELECT * FROM `表名`;
-- 查询指定字段
SELECT `字段1`，`字段2` FROM `表名`;
-- 别名，给结果列一个名字，也可以给表起别名
SELECT `字段1` [AS] `字段别名` FROM `表名`;CONCAT 字符串拼接
SELECT CONCAT("需拼接的信息",`字段名`) FROM `表名`;DISTINC 去重
SELECT DISTINCT 字段名 FROM `表名`;实战
-- 创建`school`数据库
create DATABASE IF NOT EXISTS `school`;
-- 删除 `student`表格
DROP TABLE IF EXISTS `student`;
-- 创建 `student` 表格
CREATE TABLE IF NOT EXISTS `student`(
`id` int(4) not null auto_increment COMMENT '学号',
`name` varchar(30) DEFAULT '匿名' COMMENT '姓名',
`password` varchar(20) DEFAULT '123456' COMMENT '登陆密码',
`sex` varchar(2) DEFAULT '女' COMMENT '性别',
`birth` datetime COMMENT '出生日期',
`address` varchar(100) COMMENT '家庭住址',
`email` varchar(30) COMMENT '电子邮箱',
PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT '学生信息表'
-- 显示 `student` 表结构
DESC `student`;……后续省略
事务(重要)
事务原则：ACID原则。原子性、一致性、隔离性、持久性
事务原则
原子性(Atomicity)
原子性是指一个事务是一个不可分割的工作单位，其中的操作要么都做，要么都不做。
数据库中：如果事务中一个sql语句执行失败，则已执行的语句也必须回滚，数据库退回到事务前的状态。
持久性（Durability）
持久性是指事务一旦提交，它对数据库的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。
简单理解：就是数据的持久化
隔离性（Isolation）
隔离性是指，事务内部的操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
通常使用锁机制来保证事务的隔离性。通常使用锁机制来保证事务的隔离性。
MyIsam存储引擎只支持表锁，InnoDB存储引擎同时支持表锁和行锁，出于性能考虑，绝大多数情况下使用的都是行锁。
若不保证事务的隔离性，则有可能会出现数据的【脏读、不可重复读和幻读】
脏读：当前事务(A)中可以读到其他事务(B)未提交的数据（脏数据），这种现象是脏读举例如下(以账户余额表为例)
时间 事务A 事务B T1 开始 开始 T2 修改zs余额，由100->300 T3 查询zs余额，为300(脏读) T4 提交事务 不可重复读：在事务A中先后两次读取同一个数据，两次读取的结果不一样，这种现象称为不可重复读
时间 事务A 事务B T1 开始 开始 T2 查询zs余额，为100 T3 修改zs余额，由100->300 T4 提交事务 T5 查询zs余额，为300(不可重复读) 幻读：在事务A中按照某个条件先后两次查询数据库，两次查询结果的条数不同，这种现象称为幻读。不可重复读与幻读的区别可以通俗的理解为：前者是数据变了，后者是数据的行数变了。举例如下：
时间 事务A 事务B T1 开始 开始 T2 查询0<id<5的所有用户余额(1_zs:100) T3 余额表插入新用户，2_ls:200 T4 提交事务 T5 查询0<id<5的所有用户余额(1_zs:100，2_li：200[幻读])
事务隔离级别
SQL标准中定义了四种隔离级别，并规定了每种隔离级别下上述几个问题是否存在。
一般来说，隔离级别越低，系统开销越低，可支持的并发越高，但隔离性也越差。隔离级别与读问题的关系如下：
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| Read Uncommitted - 读未提交 | √ | √ | √ |
| Read committed - 读已提交 | × | √ | √ |
| Repeatable Read - 可重复读 | × | × | √ |
| Serializable - 可串行化 | × | × | × |
在实际应用中，读未提交在并发时会导致很多问题，而性能相对于其他隔离级别提高却很有限，因此使用较少。
可串行化强制事务串行，并发效率很低，只有当对数据一致性要求极高且可以接受没有并发时使用，因此使用也较少。
因此在大多数数据库系统中，默认的隔离级别是读已提交(如Oracle)或可重复读
另外，InnoDB默认的隔离级别是【可重复读】，但是InnoDB通过MVCC（MVCC全称Multi-Version Concurrency Control，即多版本的并发控制协议）避免了幻读问题
一致性（Consistency）
一致性是指事务执行结束后，数据库的完整性约束没有被破坏，事务执行的前后都是合法的数据状态。
简单理解：就是事务执行前后，数据的改变都是合法，且各方数据都是一致的
索引(重要)
MySQL官方对索的定义为: 索引(index)是帮助MSQL高效获取数据的数据结构提取句子主干，就可以得到索引的本质：索引是数据结构。
索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。
索引分类
按字段特性分类
MySQL索引按字段特性分类可分为： 主键索引(PRIMARY KEY)、唯一索引(UNIQUE)、普通索引(INDEX)、全文索引(FULLTEXT) 。
主键索引(PRIMARY KEY)
建立在主键上的索引被称为主键索引，一张数据表只能有一个主键索引，索引列值不允许有空值，通常在创建表时一起创建。
唯一索引(UNIQUE KEY)
建立在UNIQUE字段上的索引被称为唯一索引，一张表可以有多个唯一索引，索引列值允许为空，列值中出现多个空值不会发生重复冲突
常规索引(INDEX)
建立在普通字段上的索引被称为普通索引。
全文索引(FULLTEXT)
MyISAM 存储引擎支持Full-text索引，用于查找文本中的关键词，而不是直接比较是否相等。Full-text索引一般使用倒排索引实现，它记录着关键
词到其所在文档的映射。
InnoDB 存储引擎在 MySQL5.6.4版本中也开始支持Full-text索引。
按字段个数分类
MySQL索引按字段个数分类可分为：单列索引、联合索引（也叫复合索引、组合索引）。
单列索引
建立在单个列上的索引被称为单列索引。
联合索引
建立在多个列上的索引被称为联合索引，又叫复合索引、组合索引。在MySQL中使用联合索引时要遵循最左前缀匹配原则。所以我们需要注意如下几个方面：
- 实际业务场景中创建联合索引时，我们应该把识别度比较高的字段放在前面，提高索引的命中率，充分的利用索引。
- 创建联合索引后，该索引的任何最左前缀都可以用于查询。比如当你有一个联合索引(col1, col2, col3)，该索引的所有最左前缀为(col1)、(col1, col2)、(col1, col2, col3)，包含这些列的所有查询都会使用该索引进行查询。
- 虽然联合索引可以避免回表查询，提高查询速度，但同时也会降低表数据更新的速度。因为联合索引列更新时，MySQL不仅要保存数据，还要维护一下索引文件。所以不要盲目使用，应根据业务需求来创建。
MySQL的执行计划
在select语句之前增加
explain关键字，执行后MySQL就会返回执行计划的信息，而不是执行sql。但如果from中包含子查询，MySQL仍会执行该子查询，并把子查询的结果放入临时表中。
Explain中的列
id
id列的编号是select的序列号，有几个select就有几个id，并且id是按照select出现的顺序增长的，id列的值越大优先级越高，id相同则是按
照执行计划列从上往下执行，id为空则是最后执行。
select_type
表示对应行是简单查询还是复杂查询。
- simple：不包含子查询和union的简单查询
- primary：复杂查询中最外层的select
- subquery：包含在select中的子查询（不在from的子句中）
- derived：包含在from子句中的子查询。mysql会将查询结果放入一个临时表中，此临时表也叫衍生表
- union：在union中的第二个和随后的select，UNION RESULT为合并的结果
table
表示当前行访问的是哪张表。当from中有子查询时，table列的格式为<derivedN>，表示当前查询依赖id=N行的查询，所以先执行id=N行的查询。
partitions
查询将匹配记录的分区。对于非分区表，该值为NULL。
type
此列表示关联类型或访问类型。也就是MySQL决定如何查找表中的行。依次从最优到最差分别为：system>const>eq_ref>ref>range>index>all。
NULL：MySQL能在优化阶段分解查询语句，在执行阶段不用再去访问表或者索引。
system、const：MySQL对查询的某部分进行优化并把其转化成一个常量（可以通过show warnings命令查看结果）。system是const的一
个特例，表示表里只有一条元组匹配时为system。
eq_ref：主键或唯一键索引被连接使用，最多只会返回一条符合条件的记录。简单的select查询不会出现这种type。
ref：相比eq_ref，不使用唯一索引，而是使用普通索引或者唯一索引的部分前缀，索引和某个值比较，会找到多个符合条件的行。
range：通常出现在范围查询中，比如in、between、大于、小于等。使用索引来检索给定范围的行。
index：扫描全索引拿到结果，一般是扫描某个二级索引，二级索引一般比较少，所以通常比ALL快一点。
ALL：全表扫描，扫描聚簇索引的所有叶子节点。
possible_keys
此列显示在查询中可能用到的索引。如果该列为NULL，则表示没有相关索引，可以通过检查where子句看是否可以添加一个适当的索引来提高性
能。
key
此列显示MySQL在查询时实际用到的索引。在执行计划中可能出现possible_keys列有值，而key列为null，这种情况可能是表中数据不多，
MySQL认为索引对当前查询帮助不大而选择了全表查询。如果想强制MySQL使用或忽视possible_keys列中的索引，在查询时可使用force
index、ignore index。
key_len
此列显示MySQL在索引里使用的字节数，通过此列可以算出具体使用了索引中的那些列。索引最大长度为768字节，当长度过大时，MySQL会做一个
类似最左前缀处理，将前半部分字符提取出做索引。当字段可以为null时，还需要1个字节去记录。
ref
此列显示key列记录的索引中，表查找值时使用到的列或常量。常见的有const、字段名
rows
此列是MySQL在查询中估计要读取的行数。注意这里不是结果集的行数。
Extra
此列是一些额外信息。常见的重要值如下：
Using index：使用覆盖索引（如果select后面查询的字段都可以从这个索引的树中获取，不需要通过辅助索引树找到主键，再通过主键去
主键索引树里获取其它字段值，这种情况一般可以说是用到了覆盖索引）。
Using where：使用 where 语句来处理结果，并且查询的列未被索引覆盖。
Using index condition：查询的列不完全被索引覆盖，where条件中是一个查询的范围。
Using temporary：MySQL需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的。
Using filesort：将使用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。
Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段时。
索引设计原则
选择唯一性索引
唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低
查询速度。
为经常需要排序、分组和联合操作的字段建立索引
经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。
为常作为查询条件的字段建立索引
如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。
限制索引的数目
索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会
使更新表变得很浪费时间。
尽量使用数据量少的索引
如果索引的值很长，那么查询的速度会受到影响。
例如，对一个CHAR（100）类型的字段进行全文检索需要的时间肯定要比对CHAR（10）类型的字段需要的时间要多。
尽量使用前缀来索引
如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字
符，这样可以提高检索速度。
删除不再使用或者很少使用的索引
表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索
引对更新操作的影响。
小表不应建立索引；包含大量的列并且不需要搜索非空值的时候可以考虑不建索引S
实战
创建表格
CREATE TABLE `app_user`( id BIGINT (20) UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR (50) DEFAULT '' COMMENT '用户昵称', email VARCHAR (50) NOT NULL COMMENT '用户邮箱', phone VARCHAR (20) DEFAULT '' COMMENT '手机号', gender TINYINT (4) UNSIGNED DEFAULT '0' COMMENT '性别 (0:男;1: 女)', password VARCHAR (100) NOT NULL COMMENT '密码', age TINYINT(4) DEFAULT 0 COMMENT '年龄', create_time DATETIME DEFAULT CURRENT_TIMESTAMP, update_time TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id) )ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表';编写函数
CREATE FUNCTION mock_data() RETURNS INT BEGIN DECLARE b INT DEFAULT 0; DECLARE e INT DEFAULT 1000000; WHILE b < e DO INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`) VALUES(CONCAT('用户',b), '124785@qq.com', CONCAT('15',FLOOR(RAND()*((999999999-100000000)+100000000))), FLOOR(RAND()*2), UUID(),FLOOR(RAND()*100)); SET b = b+1; END WHILE; RETURN b; END运行函数
select mock_data();
MySQL8.0以上版本如果运行报错：
This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)
show variables like 'log_bin_trust_function_creators'; -- Variable_name Value -- log_bin_trust_function_creators ON set global log_bin_trust_function_creators = 1; -- Variable_name Value -- log_bin_trust_function_creators OFF
-- 查询name为 '用户9999'和'用户100000'的所有数据
SELECT * FROM `app_user` WHERE `name` in ('用户9999','用户100000') ; -- 0.480s
-- 查看执行计划，发现扫描了992269行
EXPLAIN SELECT * FROM `app_user` WHERE `name` in ('用户9999','用户100000') ;
-- id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-- 1 SIMPLE app_user NULL ALL 992269 20.00 Using where
-- 给app_user的name列创建一个普通索引
CREATE INDEX id_appuser_name_index ON `app_user`(`name`); -- 2.722s
-- 再来查询name为 '用户9999'和'用户100000'的所有数据，发现查询时间大大减少
SELECT * FROM `app_user` WHERE `name` in ('用户9999','用户100000') ; -- 0.21s
-- id select_type table type possible_keys key key_len rows filtered Extra
-- 1 SIMPLE app_user range id_appuser_name_index id_appuser_name_index 203 100.00 Using index condition