实践篇：会从项目最基本的数据存储和操作开始讲起，包括创建数据库、数据表、对表中的数据进行增删改查操作、使用函数、表与表之间的关联操作等，帮你快速掌握最基本的用法。
目录
01 一个完整的存储过程是怎样的
我们学习了数据存储的完整过程，包括创建数据库、创建数据表、确认字段和插入数据。建议你跟着文字和视频实际操作一下，熟练掌握存储数据的方法。在进行具体操作的时候，我们会用到 8 种 SQL 语句，我再给你汇总下。
-- 创建数据库
CREATE DATABASE demo；
-- 删除数据库
DROP DATABASE demo；
-- 查看数据库
SHOW DATABASES;
-- 创建数据表：
CREATE TABLE demo.test
(
barcode text,
goodsname text,
price int
);
-- 查看表结构
DESCRIBE demo.test;
-- 查看所有表
SHOW 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 语句的书写规范。MySQL 以分号来识别一条 SQL 语句结束，所以，你写的每一条 SQL 语句的最后，都必须有一个分号，否则，MySQL 会认为这条语句没有完成，提示语法错误。
思考题
我想请你思考一下，假设用户现在要销售商品，你能不能帮它设计一个销售表，把销售信息（商品名称、价格、数量、金额等）都保存起来？
CREATE TABLE demo.sales
(
goodsname text,
salesprice decimal(10,2),
quantity decimal(10,3),
salesvalue decimal(10,2)
);02 这么多字段类型，该怎么定义？
今天，我给你介绍了几个常用的字段数据类型，包括整数类型、浮点数类型、定点数类型、文本类型和日期时间类型。同时，我们还清楚了为什么整数类型用得少，浮点数为什么不精准，以及常用的日期时间类型。
浮点数类型是把十进制数转换成二进制数存储，DECIMAL 则不同，它是把十进制数的整数部分和小数部分拆开，分别转换成十六进制数，进行存储。这样，所有的数值，就都可以精准表达了，不会存在因为无法表达而损失精度的问题。MySQL 用 DECIMAL（M,D）的方式表示高精度小数。其中，M 表示整数部分加小数部分，一共有多少位，M<=65。D 表示小数部分位数，D<M。
另外，我们还学习了几个新的 SQL 语句。尤其是第 2 条，我们在项目中会经常用到，你一定要重点牢记。
-- 修改字段类型语句
ALTER TABLE demo.goodsmaster
MODIFY COLUMN price DOUBLE;
-- 计算字段合计函数：
SELECT SUM(price)
FROM demo.goodsmaster;最后，我还想再给你分享 1 个小技巧。在定义数据类型时，如果确定是整数，就用 INT；如果是小数，一定用定点数类型 DECIMAL；如果是字符串，只要不是主键，就用 TEXT；如果是日期与时间，就用 DATETIME。
- 整数：INT。
- 小数：DECIMAL。
- 字符串：TEXT。
- 日期与时间：DATETIME。
这样做的好处是，首先确保你的系统不会因为数据类型定义出错。不过，凡事都是有两面的，可靠性好，并不意味着高效。比如，TEXT 虽然使用方便，但是效率不如 CHAR(M) 和 VARCHAR(M)
思考题
假设用户需要一个表来记录会员信息，会员信息包括会员卡编号、会员名称、会员电话、积分值。如果要你为这些字段定义数据类型，你会如何选择呢？为什么？
CREATE TABLE demo.membermaster
(
memberid INT PRIMARY KEY,
membername TEXT,
memberphone TEXT,
memberpoints INT
);03 表怎么创建和修改数据表？
这节课，我们学习了创建和修改数据表的具体方法。在讲创建表时，我讲到了一个重要的概念，就是约束，包括默认约束、非空约束、唯一性约束和自增约束等。
- 默认值约束：就是给字段设置一个默认值。
- 非空约束：就是声明字段不能为空值。
- 唯一性约束：就是声明字段不能重复。
- 自增约束：就是声明字段值能够自动加 1，且不会重复。
在修改表时，我们可以通过修改已经存在的表创建新表，也可以通过添加字段、修改字段的方式来修改数据表。最后，我给你汇总了一些常用的创建表的 SQL 语句，你一定要牢记。
CREATE TABLE
(
字段名 字段类型 PRIMARY KEY
);
CREATE TABLE
(
字段名 字段类型 NOT NULL
);
CREATE TABLE
(
字段名 字段类型 UNIQUE
);
CREATE TABLE
(
字段名 字段类型 DEFAULT 值
);
-- 这里要注意自增类型的条件，字段类型必须是整数类型。
CREATE TABLE
(
字段名 字段类型 AUTO_INCREMENT
);
-- 在一个已经存在的表基础上，创建一个新表
CREATE TABLE demo.importheadhist LIKE demo.importhead;
-- 修改表的相关语句
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 数据类型;
ALTER TABLE 表名 ADD COLUMN 字段名 字段类型 FIRST|AFTER 字段名;
ALTER TABLE 表名 MODIFY 字段名 字段类型 FIRST|AFTER 字段名;思考题
请你写一个 SQL 语句，将表 demo.goodsmaster 中的字段“salesprice”改成不能重复，并且不能为空。
ALTER TABLE demo.goodsmaster
CHANGE COLUMN salesprice salesprice DECIMAL(10,2) NOT NULL UNIQUE;
04 增删改查：如何操作表中的数据
今天，我们学习了添加、删除、修改和查询数据的方法，这些都是我们经常遇到的操作，你一定要好好学习。下面的这些 SQL 语句，是我总结的数据操作语法，你一定要重点掌握。
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，如果遇到重复的数据，该如何处理。
思考题
我想请你思考一个问题：商品表 demo.goodsmaster 中，字段“itemnumber”是主键，而且满足自增约束，如果我删除了一条记录，再次插入数据的时候，就会出现字段“itemnumber”的值不连续的情况。请你想一想，如何插入数据，才能防止这种情况的发生呢？
添加商品表中记录的时候，可以判断一下，如果发现itemnumber不连续，
可以通过显式指定itemnumber值的办法插入数据，而不是省略itemnumber让它自增。05 如何设置主键
今天，我给你介绍了设置数据表主键的三种方式：数据表的业务字段做主键、添加自增字段做主键，以及添加手动赋值字段做主键。
- 用业务字段做主键，看起来很简单，但是我们应该尽量避免这样做。因为我们无法预测未来会不会因为业务需要，而出现业务字段重复或者重用的情况。
- 自增字段做主键，对于单机系统来说是没问题的。但是，如果有多台服务器，各自都可以录入数据，那就不一定适用了。因为如果每台机器各自产生的数据需要合并，就可能会出现主键重复的问题。
- 我们可以采用手动赋值的办法，通过一定的逻辑，确保字段值在全系统的唯一性，这样就可以规避主键重复的问题了。
开始使用 MySQL 时，很多人都很容易犯的错误是喜欢用业务字段做主键，想当然地认为了解业务需求，但实际情况往往出乎意料，而更改主键设置的成本非常高。所以，如果你的系统比较复杂，尽量给表加一个字段做主键，采用手动赋值的办法，虽然系统开发的时候麻烦一点，却可以避免后面出大问题。
思考题
在刚刚的例子中，如果我想把销售流水表 demo.trans 中，所有单位是“包”的商品的价格改成原来价格的 80%，该怎么实现呢？
UPDATE demo.trans AS a,
demo.goodsmaster AS b
SET
price = price * 0.8
WHERE
a.itemnumber = b.itemnumber
AND b.unit = '包'06 外键和链接
这节课，我给你介绍了如何进行多表查询，我们重点学习了外键和连接。
外键约束，可以帮助我们确定从表中的外键字段与主表中的主键字段之间的引用关系，还可以确保从表中数据所引用的主表数据不会被删除，从而保证了 2 个表中数据的一致性。连接可以帮助我们对 2 个相关的表进行连接查询，从 2 个表中获取需要的信息。左连接表示连接以左边的表为主，结果集中要包括左边表中的所有记录；右连接表示连接以右边的表为主，结果集中要包括右边表中的所有记录。
我汇总了常用的 SQL 语句，你一定要重点掌握。
-- 定义外键约束：
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 条件语句
它们的 2 个典型区别：
第一个区别是，如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接后筛选。
这一点，就决定了在关联查询中，WHERE 比 HAVING 更高效。因为 WHERE 可以先筛选，用一个筛选后的较小数据集和关联表进行连接，这样占用的资源比较少，执行效率也就比较高。HAVING 则需要先把结果集准备好，也就是用未被筛选的数据集进行关联，然后对这个大的数据集进行筛选，这样占用的资源就比较多，执行效率也较低。
第二个区别是，WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件；HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件。
这决定了，在需要对数据进行分组统计的时候，HAVING 可以完成 WHERE 不能完成的任务。这是因为，在查询语法结构中，WHERE 在 GROUP BY 之前，所以无法对分组结果进行筛选。HAVING 在 GROUP BY 之后，可以使用分组字段和分组中的计算函数，对分组的结果集进行筛选，这个功能是 WHERE 无法完成的。
思考题
有这样一种说法：HAVING 后面的条件，必须是包含分组中的计算函数的条件，你觉得对吗？为什么？
HAVING后面的条件，必须是包含分组中计算函数的条件。这种说法是有道理的，主要是考虑到查询的效率。因为如果不是分组中的计算函数的条件，那么这个条件应该可以用WHERE而不是用HAVING，查询的效率就不高了。
09 时间函数：时间类数据
最后，我还想多说一句，MySQL 中获取的时间，其实就是 MySQL 服务器计算机的系统时间。如果你的系统有一定规模，需要在多台计算机上运行，就要注意时间校准的问题。比如我们的信息系统受门店经营环境和操作人员的素质所限，有时会遇到误操作、停电等故障而导致的计算机系统时间失准问题。这对整个信息系统的可靠性影响非常大。针对这个问题，有 2 种解决办法。第一种方法是，可以利用 Windows 系统自带的网络同步的方式，来校准系统时间。另一种办法就是，门店统一从总部 MySQL 服务器获取时间。由于总部的服务器的配置和运维状况一般要好于门店，所以系统时间出现误差的可能性也较小。如果采用云服务器，系统时间的可靠性会更高。
思考题
假如用户想查一下今天是星期几（不能用数值，要用英文显示），你可以写一个简单的查询语句吗？
SELECT DATE_FORMAT(CURRENT_DATE(), '%W');
11 索引：怎么提高查询的速度
MySQL 中的索引，就相当于图书馆的检索目录，它是帮助 MySQL 系统快速检索数据的一种存储结构。我们可以在索引中按照查询条件，检索索引字段的值，然后快速定位数据记录的位置，这样就不需要遍历整个数据表了。而且，数据表中的字段越多，表中数据记录越多，速度提升越是明显。
单字段索引
直接给数据表创建索引的语法如下
CREATE INDEX 索引名 ON TABLE 表名 (字段);创建表的同时创建索引的语法如下所示：
CREATE TABLE 表名
(
字段 数据类型,
….
{ INDEX | KEY } 索引名(字段)
)修改表时创建索引的语法如下所示：
ALTER TABLE 表名 ADD { INDEX | KEY } 索引名 (字段);给表设定主键约束或者唯一性约束的时候，MySQL 会自动创建主键索引或唯一性索引。这也是我建议你在创建表的时候，一定要定义主键的原因之一。
在选择索引字段的时候，要选择那些经常被用做筛选条件的字段。这样才能发挥索引的作用，提升检索的效率。
如果有多个索引，而这些索引的字段同时作为筛选字段出现在查询中的时候，MySQL 会选择使用最优的索引来执行查询操作。
组合索引
直接给数据表创建索引的语法如下：
CREATE INDEX 索引名 ON TABLE 表名 (字段1，字段2，...);创建表的同时创建索引
CREATE TABLE 表名
(
字段 数据类型,
….
{ INDEX | KEY } 索引名(字段1，字段2，...)
)修改表时创建索引：
ALTER TABLE 表名 ADD { INDEX | KEY } 索引名 (字段1，字段2，...);组合索引的多个字段是有序的，遵循左对齐的原则。比如我们创建的组合索引，排序的方式是 branchnumber、cashiernumber 和 itemnumber。因此，筛选的条件也要遵循从左向右的原则，如果中断，那么，断点后面的条件就没有办法利用索引了。
类似的，如果筛选的是一个范围，如果没有办法无法精确定位，也相当于中断。比如“branchnumber > 10 AND cashiernumber = 1 AND itemnumber = 100”这个条件，只能用到组合索引中 branchnumber>10 的部分，后面的索引就都用不上了。
最后，我来跟你说说索引的成本。索引能够提升查询的效率，但是建索引也是有成本的，主要有 2 个方面，一个存储空间的开销，还有一个是数据操作上的开销。存储空间的开销，是指索引需要单独占用存储空间。数据操作上的开销，是指一旦数据表有变动，无论是插入一条新数据，还是删除一条旧的数据，甚至是修改数据，如果涉及索引字段，都需要对索引本身进行修改，以确保索引能够指向正确的记录。因此，索引也不是越多越好，创建索引有存储开销和操作开销，需要综合考虑。
思考题
假如我有一个单品销售统计表，包括门店编号、销售日期（年月日）、商品编号、销售数量、销售金额、成本、毛利，而用户经常需要对销售情况进行查询，你会对这个表建什么样的索引呢？为什么？
我会用门店编号、销售日期、商品编号、销售金额和毛利这些字段，分别创建索引，理由是这些字段经常会被用作筛选条件来进行查询。
12 事务：怎么确保关联操作正确执行
事务可以确保事务中的一系列操作全部被执行，不会被打断；或者全部不被执行，等待再次执行。事务中的操作，具有原子性、一致性、永久性和隔离性的特征。但是这并不意味着，被事务包裹起来的一系列 DML 数据操作就一定会全部成功，或者全部失败。你需要对操作是否成功的结果进行判断，并通知 MySQL 针对不同情况，分别完成事务提交或者回滚操作，才能最终确保事务中的操作全部成功或全部失败。
MySQL 支持 4 种不同的事务隔离等级，等级越高，消耗的系统资源也越多，你要根据实际情况进行设定。
在 MySQL 中，并不是所有的操作都可以回滚。比如创建数据库、创建数据表、删除数据库、删除数据表等，这些操作是不可以回滚的，所以，你在操作的时候要特别小心，特别是在删除数据库、数据表时，最好先做备份，防止误操作。
思考题
学完了这节课以后，如果现在有人对你说，事务就是确保事务中的数据操作，要么全部正确执行，要么全部失败，你觉得这句话对吗？为什么？
这种说法是不对的，事务会确保事务处理中的操作要么全部执行，要么全部不执行，执行中遇到错误，是继续还是回滚，则需要程序员来处理
13 临时表
临时表是一种特殊的表，用来存储查询的中间结果，并且会随着当前连接的结束而自动删除。MySQL 中有 2 种临时表，分别是内部临时表和外部临时表：内部临时表主要用于性能优化，由系统自动产生，我们无法看到；外部临时表通过 SQL 语句创建，我们可以使用。
跟普通表相比，临时表有 3 个不同的特征：
- 临时表的创建语法需要用到关键字 TEMPORARY；
- 临时表创建完成之后，只有当前连接可见，其他连接是看不到的，具有连接隔离性；
- 临时表在当前连接结束之后，会被自动删除。
临时表可以存储在磁盘中，也可以通过指定引擎的办法存储在内存中，以加快存取速度。
当然，临时表也有不足，比如会挤占空间。我建议你，在使用临时表的时候，要从简化查询和挤占资源两个方面综合考虑，既不能过度加重系统的负担，同时又能够通过存储中间结果，最大限度地简化查询。
1483
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
