MySQL

MySQL 学习笔记

1. windows 系统中启动和关闭服务

启动：net start 服务名
关闭：net stop 服务名

2. SQL 语句的分类

DQL：数据查询语言（带有 select 关键字）
DML：数据操作语言（对表的数据进行增删改）
DDL：数据定义语言（带有 create、drop、alter，都是针对表的结构进行修改）
TCL：事务控制语言，包括事务的提交和回滚
DCL：数据控制语言，例如授权 grant，撤销授权 revoke。

3. 导入 MySQL 配置文件

进入具体的数据库
source sql 文件路径

4. 查询表的结构

desc 表名

5. 列起别名

select 列名 (as) 新列名1 from 表名
该语句只是显示时将列名起了个别名，而且 as 只能针对一个列。
as 可有可无。
新列名可用单双引号隔开，建议用单引号，双引号在 Oracle 中无法使用。

6. 字段参与数学计算

字段名 * 数字 ，查询出来的结果就是计算后的，但同时列名也会修改，可以使用 as 来改。

7. 条件查询

语法格式

select
	……
from
	……
where
	……

% 匹配任意个字符
_ 匹配一个字符
用 \ 转义上述通配符
like 为模糊查询，支持上面的通配符
判空为 is null ，而不是用等号来进行衡量。因为数据库中的 null 代表空，不代表一个值。
in 关键字，类似多个 or 。
例如 select 列名 from 表名 where 列名1 in(值1, 值2);
in() 括号内是集合，不是区间。

8. 数据排序

用 order by 排序，默认升序。
后接 desc 来实现降序，升序为 asc
查询多个列排序：
order by 列名1 asc, 列名2 asc; 有优先级。
根据字段的位置排序
select 列名1,列名2,…… from 表名 order by 数字;
其中数字表示第几列

9. 单行处理函数

substr(被取的字符串, 起始下标, 截取长度)
str_to_date() 将字符串转换成时间的格式
date_format() 格式化日期
format() 数字格式化
round(数字, 保留的位数) 四舍五入
select 后直接跟着值或者字符串，则直接返回结果，如果 from 了一个表，那会根据表的结构返回结果。
保留位数可以是负数，此时保留到整数位，个位，十位……
rand() 生成 0 - 1 之间的随机数
ifnull(数据/列名, 被当作的值) 将 null 转换成一个具体值
只要有 null 参与的数学计算，其结果一定是 null
case 列名 when ...（列名为啥时） then ...（做啥事） when ... then ... else ... end。类似 if 语句。then 的结果当作列名。

10. 分组函数（多行处理函数）

特点：
输入多行，最终输出一行。
必须先进行分组，然后才能用。没分组整张表当作一个分组。
count()，sum()，avg()，max() 和 min()
注意点：
1. 分组函数自动忽略 null
2. count(*) 是计算项的个数（只要有一行没有 null ）。
3. 这些函数不能直接使用在 where 子句中。
  即这种语句是错误的：
  select 列名 from 表名 where 列名 > min(列名);
  因为分组函数在使用时，必须先分组，而 where 执行时未分组，即未执行 group by ，看下面的执行顺序。

11. 分组查询

先对数据进行分组，然后对每一组的数据进行操作。
语法格式：
1
2
3
4
5
6
select ... from ... group by ...

关键字顺序

select
	...
from
	...
where
	...
group by
	...
order by
	...
# 执行顺序
	1. from
	2. where
	3. group by
	4. select
	5. order by

group by 列名 根据列名分组。
select 查询的列，只能跟参加分组的，以及分组函数。对于其他的列，在 MySQL 中能查询出，但无意义，在 Oracle 中就会报错。
可以根据多个列名进行分组，和 order by 一样，具有优先级。
由于 where 内不能使用分组函数，所以如果对分组查询后的数据进行进一步过滤的话，需要使用 having 关键字。
但是 having 关键字效率较低，因此可以在前面考虑使用 where ，先把过滤要求提前，然后再进行分组也行，此时效率高点。优先使用 where ，除非 where 无法实现的。
即
select max(列1) from 表1 group by 列2 having max(列1) > xxx
改成
select max(列1) from 表1 where 列1 > xxx group by 列2

12. 去除查询结果的重复数据

使用 distinct 修饰查询的列名。
注意其只能出现在所有列名的最前方，否则多个列查询时，结果不匹配，发生语法错误。

13. 连接查询

适用于多表查询，即跨表查询。
分类：内外连接查询，全连接查询（少）
1. 内连接：等值连接、非等值连接、自连接
2. 外连接：左连接、右连接
3. 全连接
无任何限制的连接查询，会发生笛卡尔积现象。
效率贴士：
1. 连接查询时，指定具体表的列名就不用查别的表的列名。
2. 表名可以起别名。
3. 表连接的越少越好，因为多表连接是在笛卡尔积现象的基础之上，进行筛选。
等值连接：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# SQL92 语法： select a.列1, b.列2 from 表1 a, 表2 b where a.列1 = b.列1 # SQL99 语法： select a.列1, b.列2 from 表1 a (inner)join 表2 b on a.列1 = b.列1
92 和 99 的区别：

92 的缺点在于，where 是表连接的条件，如果后面再进行数据筛选，那么就会在 where 语句的后面加 and ，此时筛选条件和过滤条件杂糅在一起，结构不清晰。所以 99 后面过滤条件时，可以再用 where ，这样结构比较清晰。
非等值连接

就是 on 的条件不是相等。
自连接

就是 join 还是相同的表，注意自连接起别名的时候要不相同，当成两个表来看。
外连接
就是将内连接中，未匹配的内容也给全部显示出来。
常用的就是当左表或者右表中为 null 的内容也给显示出来。
左右连接实际上就是定表的主次关系，优先保证主表的完整性。
左右外连接就是left join 和 right join
和等值连接一样，outer join，这个 outer 可以省略。
全连接——两张表全是主表
多表查询：
1
2
3
4
5
6
7
8
9
10
11
12
select ... from ... join ... on ... join ... on ...
可以一直嵌套，然后内外连接也可以混合使用。

14. 子查询

from 后的子查询，可以将子查询的查询结果当作一张临时表。如果后面还需要用，还可以再起别名。
select 后面的子查询结果不能有重复（多于一条）
例如：select 列1, 列2, （select 列3 from 表2) from 表1;
此时一条记录中，列1 和列2 各对应一条，但是子查询多余一条，由笛卡尔积现象可知，报错。

15. 合并查询结果 `Union`

union 对于表连接来说，效率高，因为表连接是笛卡尔积成倍增加，而 union 是两个结果集的拼接，是加法级。
union 在进行结果集相并时，要求两边列数相同。
同时，在进行合并时，列的数据类型相同，否则在 Oracle 中会报错。

16. 表的创建

命名建议以 t_ 和 tbl_ 开始，可读性强。

17. 数据类型

clob 字符大对象，最多可以存储 4G 的字符串，用于存储文章，存储简介，存储说明等。（因为 varchar() 最长 255 位）。
blob 二进制大对象（Binary Large Object），用于存储图片，声音、视频。此时需要使用 IO 流。

18. 删除表

加 if exist ，这样删除不存在的表时不会报错。

19. 插入日期

str_to_date：将字符串 varchar 类型转换成 date 类型。
date_format ：将 date 类型转换成具有一定格式的 varchar 字符串类型。
补充：数据库默认使用小骆驼峰命名法。
str_to_date('字符串日期', '日期格式')
日期格式：%Y %m %d %h %i %s，使用 - 连接，注意字符串日期和日期格式要一一对应。
如果给的字符串的格式就是 %Y-%m-%d，那么就不需要用函数转换，系统会自动类型转换。
想让日期以特定格式输出字符串：date_format(列名, '日期格式')
默认查询取出时，系统自动调用 date_format(列名, '%Y-%m-%d')。
date 是短日期，datetime 是长日期。
长日期的默认格式：%Y-%m-%d %h:%i:%s。
MySQL 中使用 now() 来获取当前时间，带有时分秒信息。

20. 快速创建表、插入表、快速删除（快速复制）

create table 表名 as select ... 把查询的结果直接新建成一个表。
insert into 表名 select ...（很少用，因为结果要符合表结构）。
delete 删除比较慢，且不会释放空间，优点在于可以回滚。
物理删除（阶段）：truncate ，其不能删除单条数据。

21. 约束

目的：保证数据完整性有效性。
类型：
1. 非空约束：not null。
2. 唯一性约束（列级约束）：unique 。可以有多个 null 。
3. 主键约束：primary key(pk)。pk = unique + not null（MySQL 中可以，Oracle 不是）。
4. 外键约束：foreign key(fk)。
5. 检查约束：check （MySQL 不支持，但是 Oracle 支持）
联合唯一（表级约束）：unique(列1, 列2)
因此想要使用联合约束时，就要使用表级约束。
没有主键，表无效！
可以有联合主键，但是在实际开发中，不建议使用复合主键。主键一般是数字且定长。
自然主键：主键是一个自然数，和业务没关系。
业务主键：主键值和业务相关，例如银行卡卡号。
尽量使用自然主键。然后使用 auto_increment 来让其自动维护。
外键区分父子表。被引用的为父表。
格式：foreign key(列名) references 父表(被引用列名)
用外键就是为了规范子表的一列，防止出现错误数据。
被引用的字段未必是主键，但至少要有 unique 约束。

22. MySQL 独有的存储引擎

存储引擎就是表存储/组织数据的方式。

在建表的时候可以指定存储引擎、编码方式：

1
2
3

create table t_xxx(
	...
)ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8

查看 MySQL 支持哪些存储引擎：
show engines \G
MyISAM 存储引擎：
1. 使用三个文件表示每个表：
  格式文件——存储表结构的定义（mytable.frm）
  数据文件——存储表行的内容（mytable.MYD）
  索引文件——存储表上索引（mytable.MYI）：索引可以缩小扫描范围，提高检索效率。
  特点/存储优势：可被转换为压缩、只读表来节省空间
2. 对于一张表来说，只要是主键，或者加有 unique 约束的字段都会自动创建索引。
InnoDB 存储引擎：
1. MySQL 默认存储引擎，重量级存储引擎。
2. 支持事务和崩溃后自动恢复机制以保证安全性；提供一组用来记录事务性活动的日志文件。
3. 用表空间 tablespace （逻辑名称，表空间存储 + 索引）来存储表的内容，不是文件。
4. 支持外键。
5. 但效率低，不能压缩。
6. 每个 InnoDB 表在数据库目录中以 .frm 格式文件表示。
MEMORY 存储引擎：
1. 数据存储在内存中，行长度固定，因此其非常快。
2. 每个表在数据库目录中以 .frm 格式文件表示。
3. 表数据和索引都在内存中，就是快！
4. 表级锁机制。
5. 不能包含 TEXT 和 BLOB 字段。
6. 以前称作 HEAP 引擎。

23. 事务

业务的最小单元，不可再分。本质上就是多条 DML 语句同时成功，或者同时失败。
事务就是通过：提供一组用来记录事务性活动的日志文件。来保证同时成功和同时失败，这个过程可以提交事务（事务全部成功结束并清空日志文件）和回滚事务（事务全部失败结束并全部撤销）。
MySQL 默认自动提交：每执行一句话，自动提交。

使用方法：

1
2
3

start transaction; # 关闭自动提交
...
commit/rollback;

特点：
1. （A）原子性：不可再分
2. （C）一致性：同时成功或者失败
3. （I）隔离性：多线程并发的问题
4. （D）持久性：事务结束保障，提交后将数据保存到硬盘上。
隔离性：隔离级别，4 个级别：
1. 读未提交：read uncommitted：事务 A 可以读取到事务 B 未提交的数据。
  会出现“脏读”现象。这种隔离级别都是理论上的。大部分数据隔离级别都是二档起步。
2. 读已提交（Oracle 默认）：read committed：事务 A 只能读取到事务 B 提交之后的数据。
  但存在“不可重复读取数据”问题。即事务 A 多次读取同一数据，但事务 B 在事务 A 多次读取的过程中，对数据作了更新并提交，导致事务 A 多次读取同一数据时，结果不一致。
3. 可重复读（MySQL 默认）：repeatable read：事务 A 开启后，不管多久，每一次在事务 A 中读取到的数据都是一致的。即使事务 B 将数据已经提交了，事务 A 读取到的数据还是没有改变。存在“幻读”问题。
4. 序列化：serializable（最高的隔离级别），解决所有问题，但是表示事务排队，不能并发！
查看隔离级别
select @@tx_isolation
设置隔离级别
set global transaction isolation level xxx

24. 索引

在数据库表的字段上添加的，为了提高查询效率存在的一种机制。
MySQL 查询两种方式：全表扫描和根据索引全表扫描。
MySQL 中索引也需要排序，和树的数据结构相同，使用 B+ 树。
在任何数据库当中主键上都会自动添加索引字段，且在 MySQL 中，一个字段如果有 unique 约束，也会自动创建索引对象。任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号。
在 MySQL 中，索引是一个独立的对象，不同的存储引擎以不同的形式存在，在 MYISAM 中，索引存储在 .MYI 文件中。在 InnoDB 存储引擎中索引存储在一个逻辑名称为 tablespace 中。在 MEMORY 引擎中索引被存储在内存当中。不管哪个，最终都是以 B+ 树的形式存储。
一般在庞大的数据下；某字段经常出现在 where 后面；某字段很少的 DML 操作（即很少被操作，这样索引很少重新排序）。这三个条件下建议建立索引。
一般不要随意添加索引，因为索引也需要维护，太多反而降低系统性能。建议通过主键和 unique 约束字段查询，这样效率要高一点。
索引的创建和删除
1. 创建：
  create index 表名索引名 on 表名(字段名)
2. 删除：
  drop index 索引名 on 表名
3. 查看一个 SQL 语句是否使用了索引进行检索
  explain ...
索引失效的时候
1. 当使用模糊查询且开头用的是通配符时，此时索引失效。
2. 使用 or 时，如果两端都有索引，才会使用索引，否则只要有不走索引的，那就不会走索引。此时采用 union 较为合适。
3. 使用复合索引的时候，没有使用左侧的列查找，索引失效。
  复合索引就是多个字段联合起来构成索引：表名(字段1，字段2)
  此时就是用了字段 2 没用字段 1 时就会失效，但只用字段 1 不会。
4. 在 where 中字段参与了运算，索引失效。
5. 在 where 当中，索引列使用了函数。
索引是优化中优先考虑的因素，分为单一/复合索引，主键索引，唯一性索引…
注意，唯一性比较弱的字段上添加索引用处不大。

25. 视图

视图（View），站在不同的角度去看待同一份数据。
创建和删除视图对象：
create view 视图名 as select ...
drop 视图名;
注意，只有 DQL 语句才能以 view 的形式创建。
视图作用：可以面向视图对象进行增删改查，对视图对象的增删改查，会导致原表也会被操作。在实际开发中，用于简化 SQL 语句；相当于将一个长的，需要多次使用的 SQL 语句以视图对象的形式建立出来，这样有利于后期的维护。（类似代码的封装重构）
视图就类似表，但是是存储在硬盘上的，不会消失。

26. 数据库设计范式

第一范式：任何一张表都有主键，每个字段不可再分
第二范式：建立在第一范式基础上，所有非主键字段完全依赖主键，不产生部分依赖
第三范式：建立第二范式的基础上，要求所有非主键字段直接依赖主键，不要产生传递依赖。
目的是为了避免数据冗余。

Databases > MySQL

#Databases

MySQL

https://endlessshw.top/MySQL/MySQL 笔记/

作者

EndlessShw

发布于

2025年1月1日

许可协议

MySQL

MySQL 学习笔记

1. windows 系统中启动和关闭服务

2. SQL 语句的分类

3. 导入 MySQL 配置文件

4. 查询表的结构

5. 列起别名

6. 字段参与数学计算

7. 条件查询

8. 数据排序

9. 单行处理函数

10. 分组函数（多行处理函数）

11. 分组查询

12. 去除查询结果的重复数据

13. 连接查询

14. 子查询

15. 合并查询结果 Union

16. 表的创建

17. 数据类型

18. 删除表

19. 插入日期

20. 快速创建表、插入表、快速删除（快速复制）

21. 约束

22. MySQL 独有的存储引擎

23. 事务

24. 索引

25. 视图

26. 数据库设计范式

15. 合并查询结果 `Union`