MySQL数据库原理、设计与应用

《MySQL数据库原理、设计与应用》是MySQL编程的经典教材，其*版发行近10万册。历久弥新，精心修订。
本书是针对没有数据库基础的人群，帮助读者掌握MySQL数据库的基础知识和应用。全书共分为12章，内容包括数据库的基本概念、数据库与数据表的操作、数据的操作、用户与权限管理、视图、事务、触发器、存储过程、数据库优化与部署等内容。全书提供11个综合案例，通过“知识讲解 动手实践”的方式，帮助读者在构建知识体系基础上，将所学知识应用起来，不仅加深了对知识的理解，还可以掌握实用的应用技术。
配套的数字教学资源丰富，包括精美教学PPT、900道测试题、20小时教学视频。

 

本书是面向MySQL数据库初学者的入门教材，以通俗易懂的语言、丰富实用的案例，详细讲解MySQL的开发和管理技术。
全书共12章。第1章讲解数据库基本概念和MySQL的安装步骤；第2、3章讲解数据库的基本操作；第4章讲解数据库设计的理论与实践；第5、6章讲解单表和多表的查询操作；第7~11章讲解用户与权限、视图、事务、存储过程、索引等，适合需要提高自身技术的读者；第12章讲解Linux环境下MySQL的配置和部署方案。
本书附有配套资源，包括教学视频、习题、教学课件等，而且为了帮助读者更好地学习本书中的内容，还提供了在线答疑，希望得到更多读者的关注。
本书既可作为高等院校计算机相关专业的数据库基础课程的教材，也可作为广大IT技术人员和编程爱好者的读物。

目录
第1章数据库入门1
1.1数据库基础知识1
1.1.1数据库概述1
1.1.2数据库技术的发展2
1.1.3三级模式和二级映像3
1.1.4数据模型6
1.1.5关系运算10
1.1.6SQL语言12
1.1.7常见的数据库产品14
1.2MySQL安装与配置15
1.2.1获取MySQL15
1.2.2安装MySQL17
1.2.3配置MySQL19
1.2.4管理MySQL服务19
1.2.5用户登录与设置密码21
1.2.6MySQL客户端的相关命令22
1.3常用图形化工具24
1.3.1SQLyog24
1.3.2Navicat26
1.4本章小结27
1.5课后练习27
第2章数据库基本操作29
2.1数据库操作29
2.1.1创建数据库29
2.1.2查看数据库30
2.1.3选择数据库31
2.1.4删除数据库32
2.2数据表操作33
2.2.1创建数据表34目录MySQL数据库原理、设计与应用
2.2.2查看数据表35
2.2.3修改数据表37
2.2.4查看表结构38
2.2.5修改表结构40
2.2.6删除数据表43
2.3数据操作44
2.3.1添加数据44
2.3.2查询数据46
2.3.3修改数据48
2.3.4删除数据48
2.4动手实践: 电子杂志订阅表的操作49
2.5本章小结51
2.6课后练习52
第3章数据类型与约束54
3.1数据类型54
3.1.1数字类型54
3.1.2时间和日期类型60
3.1.3字符串类型63
3.2表的约束68
3.2.1默认约束69
3.2.2非空约束70
3.2.3约束71
3.2.4主键约束74
3.3自动增长76
3.4字符集与校对集78
3.4.1字符集与校对集概述78
3.4.2字符集与校对集的设置80
3.5动手实践: 设计用户表82
3.6本章小结84
3.7课后练习84
第4章数据库设计86
4.1数据库设计概述86
4.2数据库设计范式87
4.3数据建模工具91
4.4数据库设计——电子商务网站93
4.4.1需求分析93
4.4.2准备工作95
4.4.3商品分类表96
4.4.4商品表97
4.4.5商品规格表100
4.4.6商品属性表103
4.4.7用户表106
4.4.8评论表108
4.5动手实践: 商品购物流程设计109
4.6本章小结114
4.7课后练习114
第5章单表操作116
5.1数据操作116
5.1.1复制表结构和数据116
5.1.2解决主键冲突118
5.1.3清空数据119
5.1.4去除重复记录121
5.2排序与限量122
5.2.1排序122
5.2.2限量124
5.3分组与聚合函数126
5.3.1分组126
5.3.2聚合函数132
5.4运算符133
5.4.1算术运算符133
5.4.2比较运算符139
5.4.3逻辑运算符144
5.4.4赋值运算符146
5.4.5位运算符147
5.4.6运算符优先级149
5.5动手实践: 商品评论表的操作150
5.6本章小结153
5.7课后练习153
第6章多表操作155
6.1多表查询155
6.1.1联合查询155
6.1.2连接查询157
6.2子查询162
6.2.1什么是子查询162
6.2.2子查询分类162
6.2.3子查询关键字165
6.3外键约束168
6.3.1添加外键约束168
6.3.2关联表操作170
6.3.3删除外键约束172
6.4动手实践: 多表查询练习174
6.5本章小结176
6.6课后练习176
第7章用户与权限178
7.1用户与权限概述178
7.2用户管理182
7.2.1创建用户182
7.2.2设置密码186
7.2.3修改用户188
7.2.4删除用户190
7.3权限管理190
7.3.1授予权限190
7.3.2回收权限195
7.3.3刷新权限196
7.4动手实践: 用户与权限练习196
7.5本章小结198
7.6课后练习198
第8章视图200
8.1初识视图200
8.1.1视图的概念和使用200
8.1.2创建视图的语法格式202
8.2视图管理203
8.2.1创建视图203
8.2.2查看视图205
8.2.3修改视图206
8.2.4删除视图207
8.3视图数据操作208
8.3.1添加数据208
8.3.2修改数据209
8.3.3删除数据209
8.3.4视图检查条件209
8.4动手实践: 视图的应用210
8.5本章小结212
8.6课后练习212
第9章事务214
9.1事务处理214
9.1.1事务的概念214
9.1.2事务的基本操作215
9.1.3事务的保存点217
9.2事务隔离级别219
9.2.1查看隔离级别219
9.2.2修改隔离级别220
9.2.3MySQL的4种隔离级别221
9.3动手实践: 事务的应用227
9.4本章小结228
9.5课后练习228
第10章数据库编程230
10.1函数230
10.1.1内置函数230
10.1.2自定义函数247
10.2存储过程250
10.2.1存储过程的概念250
10.2.2存储过程的创建与执行250
10.2.3存储过程的修改与删除252
10.2.4存储过程的错误处理254
10.3变量256
10.3.1系统变量256
10.3.2会话变量258
10.3.3局部变量259
10.4流程控制260
10.4.1判断语句260
10.4.2循环语句264
10.4.3跳转语句267
10.5游标268
10.5.1游标的作用268
10.5.2游标的操作流程268
10.5.3使用游标检索数据269
10.6触发器271
10.6.1触发器的概述271
10.6.2触发器的基本操作271
10.7事件275
10.7.1事件的概述275
10.7.2事件的基本操作275
10.8预处理SQL语句279
10.9动手实践: 数据库编程实战281
10.10本章小结283
10.11课后练习283
第11章数据库优化285
11.1存储引擎285
11.1.1什么是存储引擎285
11.1.2存储引擎的选择285
11.1.3InnoDB存储引擎287
11.1.4MyISAM存储引擎289
11.2索引290
11.2.1索引概述290
11.2.2索引的基本操作291
11.2.3索引的使用原则297
11.3锁机制299
11.3.1认识锁机制299
11.3.2表级锁300
11.3.3行级锁303
11.4分表技术307
11.5分区技术308
11.5.1分区概述308
11.5.2分区管理309
11.6数据碎片与维护313
11.7动手实践: 数据库优化实战315
11.8本章小结321
11.9课后练习322
第12章数据库配置与部署324
12.1Linux环境安装MySQL324
12.1.1Linux环境搭建324
12.1.2用yum安装MySQL327
12.1.3编译安装MySQL331
12.2MySQL配置文件336
12.2.1配置区段336
12.2.2基本配置336
12.2.3内存和优化配置337
12.2.4日志配置338
12.3数据备份与还原339
12.3.1数据备份339
12.3.2数据还原341
12.3.3二进制日志342
12.4多实例部署345
12.5主从复制347
12.6动手实践: 组复制350
12.7本章小结355
12.8课后练习355

传智播客和“黑马程序员”
江苏传智播客教育科技股份有限公司(简称传智播客)是一家专门致力于高素质软件开发人才的科技公司。“黑马程序员”是传智播客旗下的高端IT教育品牌。
“黑马程序员”的学员多为大学毕业后想从事IT行业，但各方面条件还不成熟的年轻人。“黑马程序员”的学员筛选制度非常严格，包括严格的技术测试、自学能力测试，还包括性格测试、压力测试、品德测试等，百里挑一的残酷筛选制度确保学员质量，并降低企业的用人风险。
自“黑马程序员”成立以来，教学研发团队一直致力于打造精品课程资源，不断在产、学、研3个层面创新自己的执教理念与教学方针，并集中“黑马程序员”的优势力量，有针对性地出版了计算机系列教材60多种，制作了教学视频数十套，发表各类技术文章数百篇。
“黑马程序员”不仅斥资研发IT系列教材，还为高校师生提供以下配套学习资源与服务。
为大学生提供的配套服务:
1. 专注辅学的 “高校学习平台”(http://yx.boxuegu.com)，专业老师在线为您答疑解惑。
2. 针对高校学生在学习过程中存在的压力等问题，面向大学生量身打造了“播妞”。同学可以添加“播妞”微信: 208695827/ QQ: 3231342131，获取学习资源。
微信二维码
QQ二维码

3. 高校学生也可扫描上方右侧二维码，加入播妞粉丝团，获取学习资源，与播妞一起快乐学习。
为IT教师提供的配套服务:
针对高校教学，“黑马程序员”为IT系列教材精心设计了“教案 授课资源 考试系统 题库 教学辅助案例”等系列教学资源。高校老师请登录“高校教辅平台”(http://yx.boxuegu.com)平台或关注码大牛老师微信/QQ: 2011168841，获取教材配套资源，也可以扫描下方二维码，加入专为IT教师打造的师资服务平台——“教学好助手”，获取“黑马程序员”教师教学辅助资源的相关动态。
MySQL数据库原理、设计与应用
MySQL是一种关系数据库管理系统，它是目前世界上流行的数据库之一，具有开源、稳定、可靠、管理方便以及支持众多系统平台等特点。MySQL广泛应用于互联网行业的数据存储，如电商、社交等网站数据的存储往往都是MySQL。
目前，从各大招聘网站的信息来看，各类计算机人才的技能要求中基本都要掌握至少一种数据库的操作和使用。其中，MySQL数据库是常见的一种。因此，MySQL数据库一般会作为计算机相关专业需要了解或掌握的技能之一。
为什么要学习本书
本书针对想要从事与计算机相关的工作，但是没有数据库基础或基础比较薄弱的人群。从了解数据库的特点、概念、原理起步，再探讨MySQL数据库的特点和使用，尽可能地确保读者可以学以致用，具备解决实际问题的能力。
本书根据知识的难易程度，采用先易后难的方式部署教材章节顺序。在知识讲解时以环环相扣的推进方式阐述出每个名词概念的作用以及相互之间的联系；在实际操作时，从指令的语法、注意事项、案例演示等多个角度详细讲解，帮助读者提高对MySQL数据库的整体认识。
如何使用本书
本书主要讲解的内容包括数据库的理论知识，MySQL数据库安装与配置，数据库、数据表的管理，数据的增、删、改、查操作，以及用户权限、视图、存储引擎、事务、索引等，还增加了电子商务网站的数据库设计以及Linux环境的配置与部署。
本书共分为12章，各章内容简要介绍如下。
 第1章主要从数据库系统的组成、数据库技术的发展阶段、数据库的三级模式结构、数据模型与关系运算、SQL等方面介绍与数据库相关的理论。还讲解了MySQL安装与配置、常用图形化工具的使用。通过本章的学习，要求初学者对数据库在理论体系上有一个整体的认识与了解，熟练掌握MySQL数据库的安装、配置与管理。
 第2、3章主要讲解数据库的基本操作，包括数据库、数据表，数据的增、删、改、查，创建数据表时数据类型及表约束的选取，以及字符集与校对集在MySQL中的作用。此部分是所有想要使用MySQL的初学者都必须掌握的内容。
 第4章从数据库实际运用的角度，讲解项目开发中如何设计一个合理、规范和高效的数据库。主要包括数据库设计的6个阶段、涉及的相关人员、数据库设计的三范式以及数据库建模工具的使用，并以电子商务网站的数据库设计为例，演示如何根据实际需求，设计出合理的数据库。
 第5、6章主要从数据操作的角度，讲解如何快速复制表结构、添加表数据，如何对数据进行判断、分组、排序与限量，如何连接多个数据表查询数据，如何建立外键约束等。此部分是所有想要从事与数据库开发相关工作的人员必须掌握的操作内容。
 第7章从数据库安全的角度，讲解为操作数据库的用户分配权限的重要性。提供创建用户、分配密码、授予以及回收权限等具体的SQL操作。通过本章的学习，要求读者能够熟练操作数据库的同时保证数据的安全。
 第8～11章从多角度讲解数据库优化的方式，包括存储引擎的选取，视图、事务、索引、锁和预处理SQL语句的应用，函数、变量、存储过程、游标、触发器和事件等多语句的编程，分表分区技术、数据碎片整理、慢查询日志、查询缓存等常见的优化解决方案。此部分内容有助于读者循序渐进地掌握如何提升和改进MySQL的性能。
 第12章介绍在Linux系统中MySQL的安装与配置，MySQL的多实例部署、数据备份与还原，以及如何利用主从复制或组复制来提高数据库的可用性和负载能力。
在上面所列举的12章中，第1~6章讲解MySQL的基础理论与SQL指令，主要帮助初学者奠定扎实的基本功；第7~12章从安全与优化角度深层次挖掘MySQL，提升读者的MySQL运用技能，积累开发经验。
在学习过程中，读者一定要亲自动手实践本书中的案例，如果不能完全理解书中所讲知识，读者可以登录“博学谷”平台，通过平台中的教学视频进行深入学习。学习完一个知识点后，要及时在“博学谷”平台进行测试，以巩固学习内容。
另外，如果读者在理解知识点的过程中遇到困难，建议不要纠结于某个地方，可以先往后学习。通常来讲，通过逐渐地学习，前面不懂和疑惑的知识也就能够理解了。在学习的过程中，一定要多动手实践，如果在实践的过程中遇到问题，建议多思考，厘清思路，认真分析问题发生的原因，并在问题解决后总结经验。
致谢
本书的编写和整理工作由传智播客教育科技股份有限公司完成，主要参与人员有吕春林、韩冬、乔治铭、陈欢、李东超、韩振国、王金涛、孙书华等，全体人员在这近一年的编写过程中付出了很多辛勤的汗水，在此一并表示衷心的感谢。
意见反馈
尽管我们付出了的努力，但教材中难免会有欠妥之处，欢迎各界专家和读者朋友提出宝贵意见，我们将不胜感激。您在阅读本书时，如发现任何问题或有不认同之处可以通过电子邮件与我们取得联系。
请发送电子邮箱至[email protected]。

黑马程序员
2019年2月于北京 前言MySQL数据库原理、设计与应用

第5章单表操作学习目标
 掌握复制表结构与数据的操作
 掌握数据的排序、限量与分组
 掌握常用聚合函数与比较函数
 掌握MySQL常用运算符的使用在前面的章节中已经学习了数据表的创建、数据类型、约束、字符集的设置，以及数据的基本增、删、改、查操作。但实际需求会更加复杂，前面学习过的内容不能够完全满足开发需求，所以需要深入学习更多的数据操作。例如，为数据表插入大量的测试数据，对查询的数据进行筛选、分组、排序或限量。本章将围绕数据库中的单表操作进行详细讲解。
5.1数据操作〖*4/5〗5.1.1复制表结构和数据〖*3/4〗1. 复制已有的表结构在开发时，若需要创建一个与已有数据表相同结构的数据表时，可以通过以下的语法完成表结构的复制。基本语法格式如下。CREATE [TEMPORARY] TABLE [IF NOT EXISTS]表名
{ LIKE 旧表名| (LIKE旧表名) }
在上述语法中，仅能从“旧表名”中复制一份相同的表结构，但不会复制表中保存的数据。其中，“{}”表示语法在使用时可以任选其中一种，“|”表示或的意思。因此，在复制已有的表结构时，可以使用“LIKE 旧表名”或“(LIKE旧表名)”中的任意一种语法格式。
下面从第4章创建的shop数据库中，复制一份与sh_goods数据表相同结构的my_goods表到mydb数据库中，具体SQL语句如下。mysql> USE shop;
Database changed
mysql> CREATETABLEmydb.my_goods LIKE sh_goods;
Query OK, 0 rows affected (0.07 sec)
按以上步骤创建完成后，可以利用SHOW CREATE TABLE查看my_goods表的结构，具体SQL语句如下。第5章单表操作MySQL数据库原理、设计与应用mysql> SHOW CREATE TABLE mydb.my_goods\G
 1. row 
Table: my_goods
Create Table: CREATE TABLE `my_goods` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘商品id’,
`category_id` int(10) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘分类id’,
此处省略部分字段
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
从上述结果可知，只需一行操作就可以依据已有的表创建出与其相同结构的表。
2. 复制已有的表数据
数据复制也可称为蠕虫复制，是新增数据的一种方式，它是从已有的数据中获取数据，并且将获取到的数据插入到对应的数据表中，实现成倍的增加。需要注意的是，此种方式获取数据与插入数据的表结构要相同，否则可能会遇到插入不成功的情况。基本语法格式如下。INSERT [INTO] 数据表名1 [(字段列表)] SELECT [(字段列表)] FROM 数据表名2;
在上述语法中，数据表名1和数据表名2通常使用的是同一个表(如my_goods表)，从而可在短期内快速增加表的数据量，测试表的压力以及效率等，相关内容会在本书数据库优化部分讲解，此处读者了解即可。
下面利用以上语法从sh_goods表中复制数据到my_goods表中，具体SQL语句如下。mysql> INSERT INTO mydb.my_goods SELECT  FROM sh_goods;
Query OK, 10 rows affected (0.01 sec)
Records: 10Duplicates: 0Warnings: 0
执行完上述SQL语句后，使用SELECT查看商品数据的添加情况，会看到数据已从sh_goods表完全复制到了my_goods表中，这里不再演示。
需要注意的是，若数据表中含有主键，而主键又具有性，所以在数据复制时还要考虑主键冲突的问题。例如，通过以下方式再向my_goods表中添加数据，系统会报“主键重复”的错误。具体SQL语句如下。mysql> INSERT INTO mydb.my_goods SELECT  FROM sh_goods;
ERROR 1062 (23000): Duplicate entry ‘1’ for key ‘PRIMARY’
对于以上主键冲突的问题，数据复制时可以指定除id主键外的任意字段完成。具体SQL语句如下。mysql> INSERT INTO mydb.my_goods (category_id, name, keyword, price,
-> content) SELECT category_id, name, keyword, price, content
-> FROM sh_goods;
Query OK, 10 rows affected (0.00 sec)
Records: 10Duplicates: 0Warnings: 0
多学一招: 临时表的使用
临时表指的是一种仅在当前会话中可见，并在当前会话关闭时自动删除的数据表。它主要用于临时存储数据。临时表的语法很简单，只需在CREATE与TABLE关键字中间添加TEMPORARY即可。示例如下。# 方式1: 创建临时表
CREATE TEMPORARY TABLE mydb.tmp_table1 (id int);
# 方式2: 创建临时表
CREATE TEMPORARY TABLE mydb.tmp_table2 SELECT id, name FROM shop.sh_goods;
在上述语句中，创建临时表时指定的数据库可以是MySQL服务器中存在的数据库，也可以是不存在的数据库。若数据库不存在，操作临时表时必须使用“数据库.临时表名”指定临时表所在的数据库。
除此之外，临时表中数据的操作与普通表相同，都可以进行SELECT、INSERT、UPDATE和DELETE操作。这里不再演示。
需要注意的是，SHOW TABLES不能查看指定数据库下有哪些临时表，并且临时表的表名必须使用ALTER TABLE修改，而不能使用RENAME TABLE…TO修改。
5.1.2解决主键冲突
在对数据表插入数据时，若表中的主键含有实际的业务意义，因此在插入数据时若不能确定对应的主键是否存在，往往会出现主键冲突的情况。例如，mydb.my_goods表经过数据复制以后，再插入编号为20的商品信息(橡皮，文具类，用于修正书写错误)，具体SQL语句及执行结果如下。mysql> INSERT INTO mydb.my_goods(id, name, content, keyword)
-> VALUES (20, ‘橡皮’, ‘修正书写错误’, ‘文具’);
ERROR 1062 (23000): Duplicate entry ’20’ for key ‘PRIMARY’
从上述的执行结果可知，系统提示插入数据的主键发生冲突。若要解决这类问题，MySQL中提供了两种方式，分别为主键冲突更新和主键冲突替换。
1. 主键冲突更新
主键冲突更新操作指的是，当插入数据的过程中若发生主键冲突，则插入数据操作利用更新的方式实现。基于语法格式如下。INSERT [INTO] 数据表名 [(字段列表)] {VALUES | VALUE} (值列表)
ON DUPLICATE KEY UPDATE 字段名1 = 新值1[,字段名2 = 新值2] …;
从上述语法可知，在INSERT语句后添加ON DUPLICATE KEY UPDATE可在发生主键冲突时，更新此条记录中通过“字段名1 = 新值1[,字段名2 = 新值2] …”设置的字段名对应的新值。
例如，修改以上发生主键冲突的插入语句，具体SQL语句及执行结果如下。mysql> INSERT INTO mydb.my_goods (id, name, content, keyword)
-> VALUES (20, ‘橡皮’, ‘修正书写错误’, ‘文具’)
-> ON DUPLICATE KEY UPDATE name = ‘橡皮’, content = ‘修正书写错误’,
-> keyword = ‘文具’;
Query OK, 2 rows affected (0.00 sec)
mysql> SELECT name, content, keyword FROM mydb.my_goods WHERE id = 20;
—— ———— ———
| name | content| keyword |
—— ———— ———
|橡皮  | 修正书写错误 | 文具  |
—— ———— ———
1 row in set (0.00 sec)
以上执行结果中，当插入的记录与数据表中已存在的记录主键冲突时，返回的结果为“2 rows affected”。
2. 主键冲突替换
主键冲突替换操作指的是，当插入数据的过程中若发生主键冲突，则删除此条记录，并重新插入。基于语法格式如下。REPLACE [INTO] 数据表名 [(字段列表)]
{VALUES | VALUE} (值列表) [, (值列表)] …;
从上述语法可知，REPLACE语句与INSERT语句的使用类似，区别在于前者每执行一次就会发生两个操作(删除记录和插入记录)。例如，修改以上发生主键冲突的插入语句，具体SQL语句及执行结果如下。mysql> REPLACE INTO mydb.my_goods (id, name, content, keyword)
-> VALUES (20, ‘橡皮’, ‘修正书写错误’, ‘文具’);
Query OK, 2 rows affected (0.00 sec)
mysql> SELECT name, content, keyword FROM mydb.my_goods WHERE id = 20;
—— ———— ———
| name | content| keyword |
—— ———— ———
|橡皮  | 修正书写错误 | 文具  |
—— ———— ———
1 row in set (0.00 sec)
从以上的执行结果可知，REPLACE替换与ON DUPLICATE KEY UPDATE更新都能解决插入数据时主键冲突的问题，但REPLACE更适合插入数据字段特别多的情况。
5.1.3清空数据
除了第2章讲解的DELETE语句可以删除数据外，在MySQL中还可以利用TRUNCATE清空指定数据表中的全部数据。其基本语法格式如下。TRUNCATE [TABLE] 表名
下面演示清空mydb数据库下的my_goods表，具体SQL语句及执行结果如下。mysql> TRUNCATE TABLE mydb.my_goods;
Query OK, 0 rows affected (0.08 sec)
需要注意的是，TRUNCATE操作虽然与DELETE语句的使用非常相似，但是两者在本质上有一定的区别，具体如下。
 实现方式不同: TRUNCATE本质上先执行删除(DROP)数据表的操作，然后再根据有效的表结构文件(.frm)重新创建数据表的方式来实现数据清空操作。而DELETE语句则是逐条地删除数据表中保存的记录。
 执行效率不同: 在针对大型数据表(如千万级的数据记录)时，TRUNCATE清空数据的实现方式决定了它比DELETE语句删除数据的方式执行效率更高。
 对AUTO_INCREMENT的字段影响不同: TRUNCATE清空数据后，再次向表中添加数据，自动增长字段会从默认的初始值重新开始，而使用DELETE语句删除表中的记录时，则不影响自动增长值。
 删除数据的范围不同: TRUNCATE语句只能用于清空表中的所有记录，而DELETE语句可通过WHERE指定删除满足条件的部分记录。
 返回值含义不同: TRUNCATE操作的返回值一般是无意义的，而DELETE语句则会返回符合条件被删除的记录数。
 所属SQL语言的不同组成部分: DELETE语句属于DML数据操作语句，而TRUNCATE通常被认为是DDL数据定义语句。
为了读者更好地理解，重新使用5.1.1小节中数据复制的方式完成my_goods表的数据添加，对比TRUNCATE与 DELETE数据表my_goods后，重新插入一条记录的效果。
(1) 为my_goods插入10条记录后，使用TRUNCATE清空并重新插入数据。mysql> TRUNCATE TABLE mydb.my_goods;
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO mydb.my_goods (name, content, keyword)
-> VALUES (‘苹果’, ‘一种很有营养的水果’, ‘水果’);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT id, name, content, keyword FROM mydb.my_goods;
—- —— ——————– ————
| id | name | content| keyword|
—- —— ——————– ————
| 1 | 苹果 | 一种很有营养的水果 | 水果   |
—- —— ——————– ————
1 row in set (0.00 sec)
(2) 为my_goods插入10条记录后，使用DELETE删除并重新插入数据。mysql> DELETE FROM mydb.my_goods;
Query OK, 10 rows affected (0.00 sec)
mysql> INSERT INTO mydb.my_goods (name, content, keyword)
-> VALUES (‘苹果’, ‘一种很有营养的水果’, ‘水果’);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT id, name, content, keyword FROM mydb.my_goods;
—- —— ——————– ————
| id | name | content| keyword|
—- —— ——————– ————
| 11 | 苹果| 一种很有营养的水果 | 水果 |
—- —— ——————– ————
1 row in set (0.00 sec)
从上述的操作中不难看出TRUNCATE操作和DELETE语句在删除数据的区别，具体如表51所示。表51TRUNCATE与DELETE对比操作返回值id(插入字段增长值)执 行 效 率TRUNCATE0 rows affected10.01sDELETE10 rows affected110.00s在表51中，DELETE的返回值表示有10条记录受影响，而TRUNCATE的返回值为0，明显无实际意义；删除数据后，再次新增一条记录后，查询到的商品id值明显不同，TRUNCATE后id字段从默认值1开始增长，而DELETE后id值则继续从10开始增长，因此后结果为11。
需要注意的是，当删除的数据量很小时，DELETE的执行效率要比TRUNCATE高；只有删除的数据量很大时，才能看出TRUNCATE的执行效率比DELETE高。例如，my_goods表含有20480条记录时，使用TRUNCATE清空数据的执行时间仍然为0.01秒，而DELETE语句的执行时间会增加到0.17秒左右。因此，在实际开发时具体使用哪种方式执行删除操作，需要根据实际需求进行合理的选择。
5.1.4去除重复记录
实际应用中，出于对数据的分析需求，有时需要去除查询记录中重复的数据。例如，查看商品表中共有几种分类。此时可以使用SELECT语句的选项，其基本语法格式如下。SELECT select选项 字段列表 FROM 数据表
在上述语法中，“select选项”默认值为All，表示保存所有查询到的记录；当设置为DISTINCT时，表示去除重复记录，只保留一条。需要注意的是，当查询记录的字段有多个时，必须所有字段的值完全相同才被认为是重复记录。
为了方便案例的演示，本章以下所有小节均使用shop数据库下的sh_goods表进行操作。下面查看sh_goods表中所有的keyword字段的值，具体SQL语句如下。mysql> SELECT keyword FROM sh_goods;
———-
| keyword|
———-
| 文具   |
| 文具 |
| 文具   |
| 电子产品   |
| 电子产品   |
| 电子产品   |
| 电子产品   |
| 电子产品   |
| 服装   |
| 服装   |
———-
10 rows in set (0.00 sec)
从上述执行结果可知，查询出的keyword字段值有3条为“文具”，5条为“电子产品”，2条为“服装”，即使有重复的数据，默认情况下依然保存了所有查询到的记录。
接下来，查看sh_goods表中去除重复记录的keyword字段值，具体SQL语句如下。mysql> SELECT DISTINCT keyword FROM sh_goods;
————
| keyword|
————
| 文具 |
| 电子产品 |
| 服装 |
————
3 rows in set (0.00 sec)
从上述执行结果可知，此次的查询结果仅有3条记录，分别为文具、电子产品和服装，不再包含重复的记录。
5.2排序与限量
在电子商务网站迅速发展的今天，商品的种类与数量数以万计，甚至更多。因此，人们在查看某种商品时经常需要对其进行排序，让满足要求的数据显示到前面，方便进一步操作。同时，为了提高执行的效率，经常需要对操作的数据进行限量。例如，仅查看符合要求的10条记录。本节将针对MySQL中排序和限量操作进行详细讲解。
5.2.1排序
在项目开发时，为了使查询的数据结果满足用户的要求，通常会对查询出的数据进行上升或下降的排序。MySQL针对不同的开发需求提供两种排序的方式，分别为单字段排序和多字段排序，接下来将对这两种排序方式的语法及使用进行详细讲解。
1. 单字段排序
单字段排序指的是查询时仅按照一个指定字段进行升序或降序排序。其基本语法格式如下。SELECT|{字段列表} FROM 数据表名
ORDER BY 字段名 [ASC | DESC];
在上述语法中，ASC表示升序，DESC表示降序。而ORDER BY默认值为ASC。
下面按照商品价格从高到低依次显示sh_goods表中的所有商品。具体SQL语句及执行结果如下。mysql> SELECT id, name, price FROM sh_goods ORDER BY price DESC;
—- ———– ———
| id | name| price |
—- ———– ———
|4 | 超薄笔记本  | 5999.00 |
|8 | 办公电脑  | 2000.00 |
|5 | 智能手机  | 1999.00 |
|9 | 收腰风衣  |299.00 |
|7 | 头戴耳机  |109.00 |
|6 | 桌面音箱  |69.00 |
| 10 | 薄毛衣  |48.00 |
|2 | 钢笔  |15.00 |
|3 | 碳素笔  |1.00 |
|1 | 2B铅笔 |0.50 |
—- ———– ———
10 rows in set (0.00 sec)
从以上的执行结果可知，查询到的商品显示顺序以价格为标准，从高到低依次显示。
2. 多字段排序
当在开发中需要根据多个条件对查询的数据进行排序时，可以采用多字段排序。其基本语法格式如下。SELECT|{字段列表} FROM 数据表名
ORDER BY字段名1 [ASC | DESC] [, 字段名2 [ASC | DESC]]…;
在上述语法中，多字段排序首先按照字段名1进行排序，当字段1的值相同时，再按照字段名2进行排序，依此类推。
下面查询sh_goods表中的数据，让数据在显示时首先按商品分类category_id升序排序，然后再按商品价格price从高到低依次排序。具体SQL语句及执行结果如下。mysql> SELECT category_id, id, name, price FROM sh_goods
-> ORDER BY category_id, price DESC;
————- —- ———— ———
| category_id | id | name | price |
————- —- ———— ———
| 3 |2 | 钢笔   | 15.00 |
| 3 |3 | 碳素笔   |1.00 |
| 3 |1 | 2B铅笔  |0.50 |
| 6 |5 | 智能手机   | 1999.00 |
| 8 |6 | 桌面音箱   | 69.00 |
| 9 |7 | 头戴耳机   |109.00 |
|10 |8 | 办公电脑   | 2000.00 |
|12 |4 | 超薄笔记本   | 5999.00 |
|15 |9 | 收腰风衣   |299.00 |
|16 | 10 | 薄毛衣   | 48.00 |
————- —- ———— ———
10 rows in set (0.00 sec)
在上述执行结果中，查询的所有数据首先按category_id升序排序，相同category_id值的记录按照price字段降序排序。
此外由于数据表的字符集是utf8，当排序的字段为中文时，默认不会按照中文拼音的顺序排序。那么在不改变数据表结构的情况下，可以使用“CONVERT(字段名 USING gbk)”函数强制让指定的字段按中文排序。
值得一提的是，在按照指定字段进行升序排列时，如果某条记录的字段值为NULL，则系统会将NULL看作是小的值，从而将其显示在查询结果中的条记录的位置。
5.2.2限量
对于一次性查询出的大量记录，不仅不便于阅读查看，还会浪费系统效率。为此，MySQL中提供了一个关键字LIMIT，可以限定记录的数量，也可以指定查询从哪一条记录开始。其基本语法格式如下。SELECT [select选项] 字段列表 FROM 数据表名
[WHERE条件表达式] [ORDER BY 字段 ASC|DESC]
LIMIT [OFFSET,] 记录数;
在上述语法中，“记录数”表示限定获取的记录数量，也就是说，在“记录数”大于数据表符合要求的实际记录数量时，以实际记录数为准；当LIMIT后仅含有此参数时，表示从第1条记录开始获取；可选项OFFSET表示偏移量，用于设置从哪条记录开始，MySQL中默认第1条记录的偏移量值为0，第2条记录的偏移量值为1，依此类推。
下面以sh_goods表为例，演示限量查询记录与如何获取指定区间的记录。
(1) 限制记录数。查询sh_goods表中价格贵的商品，具体SQL语句及执行结果如下。mysql> SELECT id, name, price FROM sh_goods
-> ORDER BY price DESC LIMIT 1;
—- ———— ———
| id | name | price |
—- ———— ———
|4 |超薄笔记本| 5999.00 |
—- ———— ———
1 row in set (0.00 sec)
在上述SQL语句中，利用商品价格从高到低依次排序，然后再利用LIMIT限制查询出的记录数为1条，即可获取查询记录中的第1条记录，也就是价格贵的商品。
(2) 获取指定区间的记录。获取指定区间的记录通常在项目开发中用于实现数据的分页展示，从而缓解网络和服务器的压力。例如，从第1条记录开始，获取5条商品记录，商品记录中包含id、name和price，具体SQL语句及执行结果如下。mysql> SELECT id, name, price FROM sh_goods LIMIT 0, 5;
—- ———— ———
| id | name | price |
—- ———— ———
|1 | 2B铅笔   |0.50 |
|2 | 钢笔  | 15.00 |
|3 | 碳素笔  |1.00 |
|4 | 超薄笔记本   | 5999.00 |
|5 | 智能手机  | 1999.00 |
—- ———— ———
5 rows in set (0.00 sec)
在上述SQL语句中，LIMIT关键字后的“0”表示第1条记录的偏移量，“5”表示从第1(偏移量 1)条记录开始多获取5条记录。
多学一招: 排序后限量更新或删除数据
在MySQL中除了对查询记录进行排序和限量外，对数据表中记录的更新与删除操作也可以进行排序和限量。其基本语法格式如下。#数据更新的排序与限量
UPDATE数据表名 SET 字段=新值, … [WHERE 条件表达式]
ORDER BY字段 ASC|DESC LIMIT 记录数;
#数据删除的排序与限量
DELETE FROM数据表名 [WHERE 条件表达式]
ORDER BY字段 ASC|DESC LIMIT 记录数;
在上述语法中,UPDATE和DELETE操作中添加ORDER BY表示根据指定的字段,按顺序更新或删除符合条件的记录。如果UPDATE和DELETE操作没有添加WHERE条件,则可以使用LIMIT来限制更新和删除的数量。
例如,为sh_goods表中价格便宜的两种商品库存设置为500,具体SQL语句及查询结果如下。mysql> UPDATE sh_goods SET stock = 500
-> ORDER BY price ASC
-> LIMIT 2;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2Changed: 2Warnings: 0
mysql> SELECT id, name, price, stock FROM sh_goods ORDER BY price;
—- ———— ——— ——-
| id | name | price | stock |