Kierke's Blog
MySQL 必知必会 Study Notes
Kierke
  2021-03-09   2024-07-20    8.1k Words  30 Mins  

极客时间专栏《MySQL 必知必会》（朱晓峰）的专栏学习笔记

课前准备 (2讲)

开篇词 | 在实战中学习，是解锁MySQL技能的最佳方法

  • 在工作中，最重要的绝对不是你的知识储备量，而是你解决实际问题的能力
  • 正确的学习方法，远比你投入的时间更重要
  • 实战，是最高效的方法
  • 学习技术点，能举一反三：
    • 项目的实际需求–>解决问题所需的知识点–>用好这些知识的实战经验
    • 解决问题需要用到：MySQL的数据存储功能，掌握设计数据表、定义字段等知识，确保数据的存储效率最高以及数据的唯一性，同时减少错误
    • MySQL是怎样存储数据的、如何才能高效查询、如何提供经营决策的依据、如何确保数据的可靠性和安全性……
  • 课程设计：
    • 实践篇：最基本的数据存储和操作，创建库、表，CURD，使用函数、表与表之间的关联操作等。快速掌握最基本的用法
    • 进阶篇：（用户管理水平的不断提升，对系统的要求也越来越多，越来越复杂）学会使用MySQL的复杂高级功能，把程序存储在服务器上、利用突发事件来调用程序、在不改变存储结构的前提下创建虚拟表以方便查询，等等
    • 优化篇：（项目投产，数据积累，性能优化的问题逐步凸显）学习数据库的设计规范，创建数据模型、理清设计思路。提升性能的具体方法
    • 案例篇：从0到1设计一个连锁超市的信息系统数据库，把讲到的知识点都融入到项目设计中，巩固复习，灵活使用
  • MySQL是一个非常优秀的数据库，里面包含了很多经典的设计思想。多多体会这些思想，提前建立起大局观，帮助我们从更高的层面去看待所遇见的实际问题


环境准备 | 带你安装MySQL和图形化管理工具Workbench

  • 略



实践篇 (13讲)

01 | 存储：一个完整的数据存储过程是怎样的？

  • 在MySQL中，一个完整的数据存储过程总共有4步，分别是创建数据库、确认字段、创建数据表、插入数据。

创建MySQL数据库

  • MySQL数据库系统从大到小依次是：数据库服务器、数据库、数据表、数据表的行与列
  • 创建数据库：CREATE DATABASE demo;
  • 查看数据库：SHOW DATABASES;
  • 为什么Workbench导航栏里面的数据库只有sys：
    • “information_schema”是MySQL系统自带的数据库：主要保存MySQL数据库服务器的系统信息，比如数据库的名称、数据表的名称、字段名称、存取权限、数据文件所在的文件夹和系统使用的文件夹，等等。
    • “performance_schema”是MySQL系统自带的数据库：可以用来监控MySQL的各类性能指标。
    • “sys”数据库是MySQL系统自带的数据库：MySQL数据库服务器的各类性能指标，帮助系统管理员和开发人员监控MySQL的技术性能。
    • “mysql”数据库：保存了MySQL数据库服务器运行时需要的系统信息，比如数据文件夹、当前使用的字符集、约束检查信息，等等。
    • 因为，Workbench是图形化的管理工具，主要面向开发人员。“sys”这个数据库已经够用；如果有特殊需求，比如，需要监控MySQL数据库各项性能指标、直接操作MySQL数据库系统文件等，可以由DBA通过SQL语句，查看其它的系统数据库。

确认字段

创建数据表

1
2
3
4
5
CREATE TABLE demo.test (
	barcode text,
    goodsname text,
    price int
);
  • 注意事项：
    • 创建表的时候，最好指明数据库
    • 不要在最后一个字段的后面加逗号“,”

查看表结构

1
DESCRIBE demo.text;
1
2
3
4
5
6
7
8
9
mysql> DESCRIBE demo.test;
+-----------+------+------+-----+---------+-------+
| Field     | Type | Null | Key | Default | Extra |
+-----------+------+------+-----+---------+-------+
| barcode   | text | YES  |     | NULL    |       |
| goodsname | text | YES  |     | NULL    |       |
| price     | int  | YES  |     | NULL    |       |
+-----------+------+------+-----+---------+-------+
3 rows in set (0.00 sec)
  • Field：字段名称
  • Type：字段类型
  • Null：表示这个字段是否允许是空值（NULL）注意：在MySQL里面，空值不等于空字符串。一个空字符串的长度是0，而一个空值的长度是空。而且，在MySQL里面，空值是占用空间的。
  • Key：键
  • Default：默认值
  • Extra：附加信息

查看数据库中的表

  • 选择数据库：USE demo;
  • 查询这个数据库中所有的表：SHOW TABLES;

设置主键

  • 主键可以确保数据的唯一性，而且能够减少数据错误
  • MySQL中数据表的主键，是表中的一个字段或者几个字段的组合
    • 必须唯一，不能重复
    • 不能是空
    • 必须可以唯一标识数据表中的记录
  • 一个MySQL数据表中只能有一个主键
  • 建议一定要给表定义主键，并且养成习惯。因为主键可以帮助你减少错误数据，并且提高查询的速度
  • 我们可以自己添加一个不会重复的字段来做主键
1
ALTER TABLE demo.test ADD COLUMN itemnumber int PRIMARY KEY AUTO_INCREMENT;

插入数据

1
INSERT INTO demo.test (barcode,goodsname,price) values ('0001','本',3);
  • 注意：
    • 要插入数据的字段名也可以不写，但是建议不要怕麻烦，一定要每次都写。这样做的好处是可读性好，不易出错，而且容易修改。
    • 由于字段itemnumber定义了AUTO_INCREMENT，所以我们插入一条记录的时候，不给它赋值，系统也会自动给它赋值。

小结

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 创建数据库
CREATE DATABASE demo；
-- 删除数据库
DROP DATABASE demo；
-- 查看数据库
SHOW DATABASES;
-- 创建数据表：
CREATE TABLE demo.test
(  
  barcode text,
  goodsname text,
  price int
); 
-- 查看表结构
DESCRIBE demo.test;
-- 查看所有表
DESCRIBE TABLES;
-- 添加主键
ALTER TABLE demo.test
ADD COLUMN itemnumber int PRIMARY KEY AUTO_INCREMENT;
-- 向表中添加数据
INSERT INTO demo.test
(barcode,goodsname,price)
VALUES ('0001','本',3);
  • MySQL中SQL语句的书写规范：写的每一条SQL语句的最后，都必须有一个分号，否则，MySQL会认为这条语句没有完成，提示语法错误


02 | 字段：这么多字段类型，该怎么定义？

整数类型

类型 有符号数取值范围 无符号数取值范围 占用字节数 适用场景
TINYINT -128~127 0-255 1 一般用于枚举数据
比如系统设定取值范围很小且固定的场景
SMALLINT -32768~32767 0~65535 2 可以用于较小范围的统计计数
比如统计工厂的固定资产库存数量等
MEDIUMINT -8388608~8388607 0~16777215 3 用于较大整数的计算
比如车站每日的客流量
INT(INTEGER) -2147483648~2147483 647 0~4294967295 4 取值范围足够大
一般情况下，不用考虑超限问题，用得最多
BIGINT -9,223,372,036,854,775,808~9 223 372 036 854 775 807 0~18446744073709551615 8 处理巨大的整数的时候才会用到
  • 在评估用哪种整数类型的时候，需要考虑存储空间和可靠性的平衡问题
  • 系统故障产生的成本远远超过增加几个字段存储空间所产生的成本
  • 首先确保数据不会超过取值范围，在这个前提之下，再去考虑如何节省存储空间

浮点数类型和定点数类型

类型 有符号数取值范围 无符号数取值范围 字节数 用途
FLOAT (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 4 单精度
浮点数值
DOUBLE (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 8 双精度
浮点数值
  • REAL默认就是DOUBLE。如果你把SQL模式设定为启用“REAL_AS_FLOAT”，那么，MySQL就认为REAL是FLOAT。如果要启用“REAL_AS_FLOAT”，就可以通过以下SQL语句实现：
1
SET sql_mode = “REAL_AS_FLOAT”;

  • 浮点数类型的无符号数取值范围，只相当于有符号数取值范围的一半，也就是只相当于有符号数取值范围大于等于零的部分

  • MySQL是按照这个格式存储浮点数的：符号（S）、尾数（M）和阶码（E）。因此，无论有没有符号，MySQL的浮点数都会存储表示符号的部分。因此，所谓的无符号数取值范围，其实就是有符号数取值范围大于等于零的部分

  • 浮点数类型有个缺陷，就是不精准

  • MySQL用4个字节存储FLOAT类型数据，用8个字节来存储DOUBLE类型数据。无论哪个，都是采用二进制的方式来进行存储的。

定点数类型：DECIMAL

  • DECIMAL的存储方式决定了它一定是精准的
  • DECIMAL是把十进制数的整数部分和小数部分拆开，分别转换成十六进制数，进行存储
  • MySQL用DECIMAL（M,D）的方式表示高精度小数。其中，M表示整数部分加小数部分，一共有多少位，M<=65。D表示小数部分位数，D<M。
  • 浮点类型取值范围大，但是不精准，适用于需要取值范围大，又可以容忍微小误差的科学计算场景（比如计算化学、分子建模、流体动力学等）；
  • 定点数类型取值范围相对小，但是精准，没有误差，适合于对精度要求极高的场景（比如涉及金额计算的场景）

文本类型

  • CHAR(M)：固定长度字符串。CHAR(M)类型必须预先定义字符串长度。如果太短，数据可能会超出范围；如果太长，又浪费存储空间。

  • VARCHAR(M)： 可变长度字符串。VARCHAR(M)也需要预先知道字符串的最大长度，不过只要不超过这个最大长度，具体存储的时候，是按照实际字符串长度存储的。

  • TEXT：字符串。系统自动按照实际长度存储，不需要预先定义长度。

  • ENUM： 枚举类型，取值必须是预先设定的一组字符串值范围之内的一个，必须要知道字符串所有可能的取值。

  • SET：是一个字符串对象，取值必须是在预先设定的字符串值范围之内的0个或多个，也必须知道字符串所有可能的取值。

  • TEXT类型也有4种，它们的区别就是最大长度不同

    • TINYTEXT：占用255字符。
    • TEXT： 占用65535字符。
    • MEDIUMTEXT：占用16777215字符。
    • LONGTEXT： 占用4294967295字符（相当于4GB）。

  • TEXT的问题：

    • 由于实际存储的长度不确定，MySQL不允许TEXT类型的字段做主键。遇到这种情况，你只能采用CHAR(M)，或者VARCHAR(M)

日期与时间类型

类型 日期格式 范围 占用字节数
YEAR YYYY 1901~2155 1
TIME HH:MM:SS ‘-838:59:59’/‘838:59:59’ 3
DATE YYYY-MM-DD ‘-838:59:59’/‘838:59:59’ 3
DATETIME YYYY-MM-DD HH:MM:SS ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’ 8
TIMESTAMP YYYY-MM-DD HH:MM:SS ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC 4
  • 用得最多的日期时间类型，就是DATETIME。虽然MySQL也支持YEAR（年）、TIME（时间）、DATE（日期），以及TIMESTAMP类型，但是我建议你，在实际项目中，尽量用DATETIME类型。因为这个数据类型包括了完整的日期和时间信息，使用起来比较方便。毕竟，如果日期时间信息分散在好几个字段，就会很不容易记，而且查询的时候，SQL语句也会更加复杂。
  • 建议：为了确保数据的完整性和系统的稳定性，优先考虑使用DATETIME类型。因为虽然DATETIME类型占用的存储空间最多，但是它表达的时间最为完整，取值范围也最大。
  • TIME的取值范围不是-23:59:59～23:59:59呢？
    • MySQL设计的TIME类型，不光表示一天之内的时间，而且可以用来表示一个时间间隔，这个时间间隔可以超过24小时。

小结

1
2
3
4
-- 修改字段类型语句
ALTER TABLE demo.goodsmaster MODIFY COLUMN price DOUBLE;
-- 计算字段合计函数：
SELECT SUM(price) FROM demo.goodsmaster;
  • 建议：在定义数据类型时，如果确定是整数，就用INT；如果是小数，一定用定点数类型DECIMAL；如果是字符串，只要不是主键，就用TEXT；如果是日期与时间，就用DATETIME。
  • TEXT虽然使用方便，但是效率不如CHAR(M)和VARCHAR(M)


03 | 表：怎么创建和修改数据表？

创建表

  • 不能使用MySQL的关键字

MySQL有哪些约束

默认约束

  • 插入数据的时候，如果不明确给字段赋值，那么系统会把设置的默认值自动赋值给字段

主键约束

  • 略

外键约束

  • 略

非空约束

  • 非空约束表示字段值不能为空，如果创建表的时候，指明某个字段非空，那么添加数据的时候，这个字段必须有值，否则系统就会提示错误。

唯一性约束

  • 唯一性约束表示这个字段的值不能重复，否则系统会提示错误。跟主键约束相比，唯一性约束要更加弱一些。
  • 在一个表中，我们可以指定多个字段满足唯一性约束，而主键约束则只能有一个
  • 满足主键约束的字段，自动满足非空约束，但是满足唯一性约束的字段，则可以是空值

自增约束

  • 自增约束可以让MySQL自动给字段赋值，且保证不会重复
  • 在数据表中，只有整数类型的字段（包括TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT），才可以定义自增约束。自增约束的字段，每增加一条数据，值自动增加1。
  • 第二，可以给自增约束的字段赋值，这个时候，MySQL会重置自增约束字段的自增基数，下次添加数据的时候，自动以自增约束字段的最大值加1为新的字段值。

如何修改表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
## 复制表结构
CREATE demo.importheadhist LIKE demo.importhead;

# 添加一个字段confirmer，类型INT
ALTER TABLE demo.importheadhist ADD confirmer INT;

# 添加一个字段confirmdate，类型是DATETIME
ALTER TABLE demo.importheadhist ADD confirmdate DATETIME; 

# 把字段名称“quantity”改成“importquantity” ，并且把字段类型改为DOUBLE
ALTER TABLE demo.importheadhist CHANGE quantity importquantity DOUBLE;

# 把字段“importquantity”类型改成DECIMAL(10,3)
ALTER TABLE demo.importheadhist MODIFY importquantity DECIMAL(10,3);

# 在字段supplierid之后，添加一个字段suppliername，数据类型是TEXT
ALTER TABLE demo.importheadhist ADD suppliername TEXT AFTER supplierid;

# 修改表的存储引擎
ALTER TABLE 表名 ENGINE=INNODB;
  • 可以通过指定关键字AUTO_EXTENDSIZE，来指定存储文件自增空间的大小，从而提高存储空间的利用率。
  • 在MySQL 8.0.23之后的版本中，你甚至还可以通过INVISIBLE关键字，使字段不可见，但却可以使用。


04 | 增删改查：如何操作表中的数据？

添加数据

1
INSERT INTO 表名 [(字段名 [,字段名] ...)] VALUES (值的列表);

插入数据记录

  • 我们在插入一条数据记录的时候，必须要考虑字段约束的3种情况
    • （1）如果字段允许为空，而我们没有给它赋值，那么MySQL会自动给它们赋予空值
    • （2）如果字段是主键，就不能为空，这个时候，MySQL会按照我们添加的约束进行处理
    • （3）如果有一个字段定义不能为空，又不是主键，当你插入一条数据记录的时候，就必须给这个记录赋值
  • 部分插入一条数据记录是可以的，但前提是，没有赋值的字段，一定要让MySQL知道如何处理，比如可以为空、有默认值，或者是自增约束字段，等等，否则，MySQL会提示错误的

插入查询结果

  • MySQL支持把查询的结果插入到数据表中，我们可以指定字段，甚至是数值，插入到数据表中
1
2
3
4
5
6
7
8
INSERT INTO 表名 （字段名）
SELECT 字段名或值
FROM 表名
WHERE 条件

INSERT INTO 历史流水表 （日结时间字段，其他字段）
SELECT 获取当前时间函数，其他字段
FROM 流水表

删除数据

1
2
DELETE FROM 表名
WHERE 条件

修改数据

1
2
3
UPDATE 表名
SET 字段名=值
WHERE 条件
  • 不要修改主键字段的值。因为主键是数据记录的唯一标识，如果修改了主键的值，就有可能会破坏数据的完整性
  • 如果你必须要修改主键的值，那有可能就是主键设置得不合理

查询数据

1
2
3
4
5
6
7
SELECT *|字段列表
FROM 数据源
WHERE 条件
GROUP BY 字段
HAVING 条件
ORDER BY 字段
LIMIT 起始点，行数

  • SELECT：是查询关键字，表示我们要做一个查询。“*”是一个通配符，表示我们要查询表中所有的字段。你也可以把要查询的字段罗列出来，这样，查询的结果可以只显示你想要查询的字段内容。

  • WHERE：表示查询条件。你可以把你要查询的数据所要满足的条件，放在WHERE关键字之后。

  • GROUP BY：作用是告诉MySQL，查询结果要如何分组，经常与MySQL的聚合函数一起使用。

  • HAVING：用于筛选查询结果，跟WHERE类似。

  • FROM关键字后面，还可以跟着更复杂的数据表联接。

  • ORDER BY的作用，是告诉MySQL，查询结果如何排序。ASC表示升序，DESC表示降序。

  • LIMIT的作用是告诉MySQL只显示部分查询的结果。这里的“LIMIT 1,2”中，“1”表示起始位置，MySQL中，起始位置的起点是0，1表示从第2条记录开始；“2”表示2条数据。

小结

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
INSERT INTO 表名 [(字段名 [,字段名] ...)] VALUES (值的列表);
 
INSERT INTO 表名 （字段名）
SELECT 字段名或值
FROM 表名
WHERE 条件
 
DELETE FROM 表名
WHERE 条件
 
UPDATE 表名
SET 字段名=值
WHERE 条件

SELECT *|字段列表
FROM 数据源
WHERE 条件
GROUP BY 字段
HAVING 条件
ORDER BY 字段
LIMIT 起始点，行数
  • 如果我们把查询的结果插入到表中时，导致主键约束或者唯一性约束被破坏了，就可以用“ON DUPLICATE”关键字进行处理。这个关键字的作用是，告诉MySQL，如果遇到重复的数据，该如何处理。
  • 假设我们要把门店B的商品数据，插入到门店A的商品表中去，如果有重复的商品编号，就用门店B的条码，替换门店A的条码，用门店B的商品名称，替换门店A的商品名称；如果没有重复的编号，就直接把门店B的商品数据插入到门店A的商品表中
1
2
3
4
INSERT INTO demo.goodsmaster 
SELECT *
FROM demo.goodsmaster1 as a
ON DUPLICATE KEY UPDATE barcode = a.barcode,goodsname=a.goodsname;


05 | 主键：如何正确设置主键？

业务字段做主键

  • 尽量不要用业务字段，也就是跟业务有关的字段做主键
  • 作为项目设计的技术人员，我们谁也无法预测在项目的整个生命周期中，哪个业务字段会因为项目的业务需求而有重复，或者重用之类的情况出现。
  • 身份证和电话号码也不能做主键
    • 身份证：涉及到隐私，客户不一定愿意给
    • 电话号码：手机号也存在被运营商收回，重新发给别人用的情况。

使用自增字段做主键

  • 自增字段做主键，对于单机系统来说是没问题的
  • 如果有多台服务器，各自都可以录入数据，那就不一定适用了。因为如果每台机器各自产生的数据需要合并，就可能会出现主键重复的问题。

手动赋值字段做主键

  • 即：分布式ID方案。我们可以采用手动赋值的办法，通过一定的逻辑，确保字段值在全系统的唯一性，这样就可以规避主键重复的问题了。

小结

1
2
ALTER TABLE demo.membermaster DROP PRIMARY KEY;
ALTER TABLE demo.membermaster ADD id INT PRIMARY KEY AUTO_INCREMENT;


06 | 外键和连接：如何做关联查询？

  • 把分散在多个不同的表里的数据查询出来的操作，就是多表查询
  • 在MySQL中，为了把2个表关联起来，会用到2个重要的功能：外键（FOREIGN KEY）和连接（JOIN）
  • 外键需要在创建表的阶段就定义；连接可以通过相同意义的字段把2个表连接起来，用在查询阶段

如何创建外键？

  • 外键就是从表中用来引用主表中数据的那个公共字段
  • 在MySQL中，外键是通过外键约束来定义的。它必须在从表中定义，包括指明哪个是外键字段，以及外键字段所引用的主表中的主键字段是什么
  • MySQL系统会根据外键约束的定义，监控对主表中数据的删除操作。如果发现要删除的主表记录，正在被从表中某条记录的外键字段所引用，MySQL就会提示错误，从而确保了关联数据不会缺失。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
[CONSTRAINT <外键约束名称>] FOREIGN KEY 字段名 REFERENCES <主表名> 字段名

CREATE TABLE 从表名
(
  字段名 类型,
  ...
-- 定义外键约束，指出外键字段和参照的主表字段
CONSTRAINT 外键约束名
FOREIGN KEY (字段名) REFERENCES 主表名 (字段名)
)

ALTER TABLE 从表名 ADD CONSTRAINT 约束名 FOREIGN KEY 字段名 REFERENCES 主表名 （字段名）;

CREATE TABLE demo.importdetails
(
	listnumber INT,
	itemnumber INT,
	quantity DECIMAL(10,3),
	importprice DECIMAL(10,2),
	importvalue DECIMAL(10,2),
	-- 定义外键约束，指出外键字段和参照的主表字段
	CONSTRAINT fk_importdetails_importhead
	FOREIGN KEY (listnumber) REFERENCES importhead (listnumber)
);

  • 通过SQL语句来查看外键约束的相关信息
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> SELECT
    ->     constraint_name, -- 表示外键约束名称
    ->     table_name, -- 表示外键约束所属数据表的名称
    ->     column_name, -- 表示外键约束的字段名称
    ->     referenced_table_name, -- 表示外键约束所参照的数据表名称
    ->     referenced_column_name -- 表示外键约束所参照的字段名称
    -> FROM
    ->     information_schema.KEY_COLUMN_USAGE
    -> WHERE
    ->     constraint_name = 'fk_importdetails_importhead';
+-----------------------------+---------------+-------------+-----------------------+------------------------+
| CONSTRAINT_NAME             | TABLE_NAME    | COLUMN_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+-----------------------------+---------------+-------------+-----------------------+------------------------+
| fk_importdetails_importhead | importdetails | listnumber  | importhead            | listnumber             |
+-----------------------------+---------------+-------------+-----------------------+------------------------+
1 row in set (0.05 sec)
  • 外键约束所在的表是“importdetails”，外键字段是“listnumber”，参照的主表是“importhead”，参照的主表字段是“listnumber”

连接

  • 内连接（INNER JOIN）：内连接表示查询结果只返回符合连接条件的记录，这种连接方式比较常用；
    • 在MySQL里面，关键字JOIN、INNER JOIN、CROSS JOIN的含义是一样的，都表示内连接
    • 关键字JOIN与关键字ON配对使用
  • 外连接（OUTER JOIN）：表示查询结果返回某一个表中的所有记录，以及另一个表中满足连接条件的记录。
    • 左连接，一般简写成LEFT JOIN，返回左边表中的所有记录，以及右表中符合连接条件的记录
    • 右连接，一般简写成RIGHT JOIN，返回右边表中的所有记录，以及左表中符合连接条件的记录。
  • 外键约束不是关联查询的必要条件

外键约束的利与弊

  • 外键约束可以防止数据缺失，避免出现误删的情况，从而提高系统整体的可靠性。
  • 外键约束是有成本的，需要消耗系统资源。
  • 对于大并发的SQL操作，可能会因为外键约束的系统开销而变得非常慢。所以，MySQL允许不使用系统自带的外键约束，在应用层面完成检查数据一致性的逻辑。
  • 即使你不用外键约束，也要想办法通过应用层面的附加逻辑，来实现外键约束的功能，确保数据的一致性。

小结

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- 定义外键约束：
CREATE TABLE 从表名
(
字段 字段类型
....
CONSTRAINT 外键约束名称
FOREIGN KEY (字段名) REFERENCES 主表名 (字段名称)
);
ALTER TABLE 从表名 ADD CONSTRAINT 约束名 FOREIGN KEY 字段名 REFERENCES 主表名 （字段名）;

-- 连接查询
SELECT 字段名
FROM 表名 AS a
JOIN 表名 AS b
ON (a.字段名称=b.字段名称);
 
SELECT 字段名
FROM 表名 AS a
LEFT JOIN 表名 AS b
ON (a.字段名称=b.字段名称);
 
SELECT 字段名
FROM 表名 AS a
RIGHT JOIN 表名 AS b
ON (a.字段名称=b.字段名称);
  • 刚开始学习MySQL的同学，很容易忽略在关联表中定义外键约束的重要性，从而导致数据缺失，影响系统的可靠性。我建议你尽量养成在关联表中定义外键约束的习惯。不过，如果你的业务场景因为高并发等原因，无法承担外键约束的成本，也可以不定义外键约束，但是一定要在应用层面实现外键约束的逻辑功能，这样才能确保系统的正确可靠。


07 | 条件语句：WHERE 与 HAVING有什么不同?

WHERE

  • 直接用表的字段对数据集进行筛选
  • 如果需要通过关联查询从其他的表获取需要的信息，那么执行的时候，也是先通过WHERE条件进行筛选，用筛选后的比较小的数据集进行连接。这样一来，连接过程中占用的资源比较少，执行效率也比较高。

HAVING

  • GROUP BY理解成对数据进行分组，方便我们对组内的数据进行统计计算
  • HAVING的查询过程：我们要把所有的信息都准备好，包括从关联表中获取需要的信息，对数据集进行分组，形成一个包含所有需要的信息的数据集合。接着，再通过HAVING条件的筛选，得到需要的数据。

怎么正确地使用WHERE和HAVING？

  • 如果需要通过连接从关联表中获取需要的数据，WHERE是先筛选后连接，而HAVING是先连接后筛选。
  • WHERE比HAVING更高效
    • 因为WHERE可以先筛选，用一个筛选后的较小数据集和关联表进行连接，这样占用的资源比较少，执行效率也就比较高。HAVING则需要先把结果集准备好，也就是用未被筛选的数据集进行关联，然后对这个大的数据集进行筛选，这样占用的资源就比较多，执行效率也较低。
  • WHERE可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件；HAVING必须要与GROUP BY配合使用，可以把分组计算的函数和分组字段作为筛选条件。
  • 在需要对数据进行分组统计的时候，HAVING可以完成WHERE不能完成的任务
    • 在查询语法结构中，WHERE在GROUP BY之前，所以无法对分组结果进行筛选。HAVING在GROUP BY之后，可以使用分组字段和分组中的计算函数，对分组的结果集进行筛选，这个功能是WHERE无法完成的。
  • WHERE和HAVING也不是互相排斥的，我们可以在一个查询里面同时使用WHERE和HAVING。


08 | 聚合函数：怎么高效地进行分组统计？

  • MySQL中有5种聚合函数较为常用，分别是求和函数SUM()、求平均函数AVG()、最大值函数MAX()、最小值函数MIN()和计数函数COUNT()

summation 求和

average 平均

minimum 最小

maximum 最大

count 计数

1
2
# 返回字符串 str 最左边的 n 个字符
LEFT(str，n)

求和函数SUM()

  • 求和函数获取的是分组中的合计数据，所以你要对分组的结果有准确的把握，否则就很容易搞错。
  • 你要知道是按什么字段进行分组的。如果是按多个字段分组，你要知道字段之间有什么样的层次关系；
  • 如果是按照以字段作为变量的某个函数进行分组的，你要知道这个函数的返回值是什么，返回值又是如何影响分组的等。

AVG（）

  • 通过计算分组内指定字段值的和，以及分组内的记录数，算出分组内指定字段的平均值

MAX()

  • 获取指定字段在分组中的最大值

MIN()

  • 获取指定字段在分组中的最小值

MAX（字段）这个函数返回分组集中最大的那个值。如果你要查询 MAX（字段1）和MAX（字段2），而它们是相互独立、分别计算的，你千万不要想当然地认为结果在同一条记录上。

COUNT（）

  • COUNT（*）：统计一共有多少条记录；
  • COUNT（字段）：统计有多少个不为空的字段值。
  • COUNT（字段）用来统计分组内这个字段的值出现了多少次。如果字段值是空，就不统计
  • 如果COUNT（*）与GROUP BY一起使用，就表示统计分组内有多少条数据。它也可以单独使用

小结

  • 这些函数，为我们对数据库中的数据进行统计和计算提供了方便。因为计算直接在数据库中执行，比在应用层面完成相同的工作，效率高很多。


09 | 时间函数：时间类数据，MySQL是怎么处理的？

获取日期时间数据中部分信息的函数

  • EXTRACT（type FROM date）表示从日期时间数据“date”中抽取“type”指定的部分
1
2
3
4
5
6
7
8
9
10
mysql> SELECT
    -> EXTRACT(HOUR FROM b.transdate) AS 时段,
    -> SUM(a.quantity) AS 数量,
    -> SUM(a.salesvalue) AS 金额
    -> FROM
    -> demo.transactiondetails a
    -> JOIN
    -> demo.transactionhead b ON (a.transactionid = b.transactionid)
    -> GROUP BY EXTRACT(HOUR FROM b.transdate)
    -> ORDER BY EXTRACT(HOUR FROM b.transdate);
  • HOUR（time）表示从日期时间“time”中，获取小时部分信息。
1
2
3
4
5
6
7
8
9
10
mysql> SELECT
-> HOUR(b.transdate) AS 时段, -- 改为使用HOUR函数
-> SUM(a.quantity) AS 数量,
-> SUM(a.salesvalue) AS 金额
-> FROM
-> demo.transactiondetails a
-> JOIN
-> demo.transactionhead b ON (a.transactionid = b.transactionid)
-> GROUP BY HOUR(b.transdate) -- 改写为HOUR函数
-> ORDER BY HOUR(b.transdate);-- 改写为HOUR函数
  • YEAR（date）：获取date中的年。
  • MONTH（date）：获取date中的月。
  • DAY（date）：获取date中的日。
  • HOUR（date）：获取date中的小时。
  • MINUTE（date）：获取date中的分。
  • SECOND（date）：获取date中的秒。

计算日期时间的函数

  • DATE_ADD（date, INTERVAL 表达式 type）：表示计算从时间点“date”开始，向前或者向后一段时间间隔的时间。“表达式”的值为时间间隔数，正数表示向后，负数表示向前，“type”表示时间间隔的单位（比如年、月、日等）。
  • LAST_DAY（date）：表示获取日期时间“date”所在月份的最后一天的日期。

我们可以用DATE_ADD()来计算从某个时间点开始，过去或者未来一个时间间隔的时间；通过LAST_DAY()函数，获得某个时间节点当月的最后一天的日期。借助它们，我们就可以获取从某个时间节点出发的指定月份的起始日期和截止日期

  • ADDDATE()：跟DATE_ADD()用法一致；
  • DATE_SUB()，SUBDATE()：与DATE_ADD()用法类似，方向相反，执行日期的减操作。

其他日期时间函数

  • CURDATE()、DAYOFWEEK()、DATE_FORMAT和DATEDIFF()。

  • CURDATE（）：获取当前的日期。日期格式为“YYYY-MM-DD”，也就是年月日的格式。

  • DAYOFWEEK（date）：获取日期“date”是周几。1表示周日，2表示周一，以此类推，直到7表示周六。

  • DATE_FORMAT()：它表示将日期时间“date”按照指定格式显示

  • DATEDIFF（date1,date2）：表示日期“date1”与日期“date2”之间差几天




标题二




Reference




Remark

  1. xxx需要再深入学习
1
2
3
4
5
<font color=red></font>
![]()
<img src="" title="图片名称" alt="图片无法正常加载展示！" width="100%" height="100%" >
<img src="" width="70%">
****


MySQL 必知必会 Study Notes
2021/MySQL-必知必会-Study-Notes/
Author
Kierke
Published
2021-03-09 19:23
License
  1. 课前准备 (2讲)
    1. 开篇词 | 在实战中学习，是解锁MySQL技能的最佳方法
    2. 环境准备 | 带你安装MySQL和图形化管理工具Workbench
  2. 实践篇 (13讲)
    1. 01 | 存储：一个完整的数据存储过程是怎样的？
      1. 创建MySQL数据库
      2. 确认字段
      3. 创建数据表
        1. 查看表结构
        2. 查看数据库中的表
        3. 设置主键
      4. 插入数据
      5. 小结
    2. 02 | 字段：这么多字段类型，该怎么定义？
      1. 整数类型
      2. 浮点数类型和定点数类型
        1. 定点数类型：DECIMAL
      3. 文本类型
      4. 日期与时间类型
      5. 小结
    3. 03 | 表：怎么创建和修改数据表？
      1. 创建表
      2. MySQL有哪些约束
        1. 默认约束
        2. 主键约束
        3. 外键约束
        4. 非空约束
        5. 唯一性约束
        6. 自增约束
      3. 如何修改表
    4. 04 | 增删改查：如何操作表中的数据？
      1. 添加数据
      2. 插入数据记录
      3. 插入查询结果
      4. 删除数据
      5. 修改数据
      6. 查询数据
      7. 小结
    5. 05 | 主键：如何正确设置主键？
      1. 业务字段做主键
      2. 使用自增字段做主键
      3. 手动赋值字段做主键
      4. 小结
    6. 06 | 外键和连接：如何做关联查询？
      1. 如何创建外键？
      2. 连接
      3. 外键约束的利与弊
      4. 小结
    7. 07 | 条件语句：WHERE 与 HAVING有什么不同?
      1. WHERE
      2. HAVING
      3. 怎么正确地使用WHERE和HAVING？
    8. 08 | 聚合函数：怎么高效地进行分组统计？
      1. 求和函数SUM()
      2. AVG（）
      3. MAX()
      4. MIN()
      5. COUNT（）
      6. 小结
    9. 09 | 时间函数：时间类数据，MySQL是怎么处理的？
      1. 获取日期时间数据中部分信息的函数
      2. 计算日期时间的函数
      3. 其他日期时间函数
  3. 标题二
  4. Reference
  5. Remark
  1. 课前准备 (2讲)
    1. 开篇词 | 在实战中学习，是解锁MySQL技能的最佳方法
    2. 环境准备 | 带你安装MySQL和图形化管理工具Workbench
  2. 实践篇 (13讲)
    1. 01 | 存储：一个完整的数据存储过程是怎样的？
      1. 创建MySQL数据库
      2. 确认字段
      3. 创建数据表
        1. 查看表结构
        2. 查看数据库中的表
        3. 设置主键
      4. 插入数据
      5. 小结
    2. 02 | 字段：这么多字段类型，该怎么定义？
      1. 整数类型
      2. 浮点数类型和定点数类型
        1. 定点数类型：DECIMAL
      3. 文本类型
      4. 日期与时间类型
      5. 小结
    3. 03 | 表：怎么创建和修改数据表？
      1. 创建表
      2. MySQL有哪些约束
        1. 默认约束
        2. 主键约束
        3. 外键约束
        4. 非空约束
        5. 唯一性约束
        6. 自增约束
      3. 如何修改表
    4. 04 | 增删改查：如何操作表中的数据？
      1. 添加数据
      2. 插入数据记录
      3. 插入查询结果
      4. 删除数据
      5. 修改数据
      6. 查询数据
      7. 小结
    5. 05 | 主键：如何正确设置主键？
      1. 业务字段做主键
      2. 使用自增字段做主键
      3. 手动赋值字段做主键
      4. 小结
    6. 06 | 外键和连接：如何做关联查询？
      1. 如何创建外键？
      2. 连接
      3. 外键约束的利与弊
      4. 小结
    7. 07 | 条件语句：WHERE 与 HAVING有什么不同?
      1. WHERE
      2. HAVING
      3. 怎么正确地使用WHERE和HAVING？
    8. 08 | 聚合函数：怎么高效地进行分组统计？
      1. 求和函数SUM()
      2. AVG（）
      3. MAX()
      4. MIN()
      5. COUNT（）
      6. 小结
    9. 09 | 时间函数：时间类数据，MySQL是怎么处理的？
      1. 获取日期时间数据中部分信息的函数
      2. 计算日期时间的函数
      3. 其他日期时间函数
  3. 标题二
  4. Reference
  5. Remark