学习目标: 

.掌握表结构和数据的复制,能够复制现有数据表的表结构和数据。
.掌握主键冲突的解决方法,能够解决主键冲突问题。
.掌握清空数据操作,能够利用TRUNCATE 语句清空数据。
.掌握如何去除查询结果中的重复记录,能够利用DISTINCT 实现去重查询。
.掌握排序查询操作,能够利用ORDERBY 对返回的查询结果进行排序。
.掌握限量查询操作,能够利用LIMIT 对返回的数据进行限量。
.掌握分组查询操作,能够利用GROUPBY 对返回的查询结果进行分组。
.掌握聚合函数的使用,能够根据不同场景对查询数据进行统计。
.熟悉算术运算符的用法,能够说明每个算术运算符的含义。
.熟悉比较运算符的用法,能够说明每个比较运算符的含义。
.熟悉逻辑运算符的用法,能够说明每个逻辑运算符的含义。
.熟悉赋值运算符的用法,能够说明每个赋值运算符的含义。
.熟悉位运算符的用法,能够说明每个位运算符的含义。
.熟悉运算符的优先级,能够说明常用运算符的优先级。
在前面的章节中已经学习了数据表和数据的基本操作。但是,实际的需求可能会更加
复杂,仅仅通过前面学习的知识并不能完全满足开发的需要。例如,对查询到的数据进行排
序、限量和分组,以及连接多张表进行查询等。因此,需要学习更多关于数据操作的知识,这
些知识主要分为单表操作和多表操作,将在第5章和第6章中进行详细讲解。本章讲解数
据库中的单表操作。

5.数据进阶操作
1 

在实际开发中,除了需要对数据进行添加、修改、查询和删除外,有时还需要进行一些进
阶操作,例如复制表结构和数据、解决主键冲突、清空数据和去除查询结果中的重复记录。
本节对数据进阶操作进行详细讲解。

5.1 
复制表结构和数据
1.
MySQL 中提供了专门的SQL 语句,用于创建表并复制已有数据表的表结构和数据。


第5章 单表操作1 15 
下面分别进行讲解。
1.复制已有的表结构
在开发时,若需要创建一个与已有数据表相同结构的数据表,基本语法格式如下。 
CREATE [TEMPORARY]TABLE [IF NOT EXISTS]新数据表名称{LIKE 源表| (LIKE 源表)} 
上述语法格式中,在复制已有的数据表结构时,使用“LIKE 源表”与使用“(LIKE 源
表)”语法效果相同,任选其一即可。通过这种方式复制的数据表为一个空表,该表包括源表
中定义的任何字段属性和索引,但不会复制源表中保存的数据。
为了帮助读者更好地理解如何复制已有的表结构,接下来将以4.4节动手实践中设计
的电子商务网站数据库为例进行演示。读者可通过本书配套源代码获取该数据库的SQL 
文件,导入MySQL中,将数据库命名为shop。下面演示如何复制shop数据库中sh_goods 
数据表的表结构,将复制出来的数据表命名为my_goods,并存放于mydb数据库中,具体
SQL语句及执行结果如下。 
mysql>USE shop; 
Database changed 
mysql>CREATE TABLE mydb.my_goods (LIKE sh_goods); 
Query OK,0 rows affected (0.05 sec) 
按以上步骤创建完成后,通过SHOWCREATETABLE语句查看my_goods数据表的
结构,具体SQL语句及执行结果如下。 
mysql>SHOW CREATE TABLE mydb.my_goods; 
+----------+---------------------------------------------------+ 
| Table | Create Table | 
+----------+---------------------------------------------------+ 
| my_goods | CREATE TABLE `my_goods` ( | 
| | `id` int unsigned NOT NULL AUTO_INCREMENT | 
| | COMMENT '商品id', | 
| | `category_id` int unsigned NOT NULL DEFAULT '0' | 
| | COMMENT '分类id', | 
| | `spu_id` int unsigned NOT NULL DEFAULT '0' | 
| | COMMENT 'SPU id ', | 
| | ……(此处省略部分字段) | 
| | PRIMARY KEY (`id`) | 
| | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | 
| | COLLATE=utf8mb4_0900_ai_ci COMMENT='商品表' | 
+----------+---------------------------------------------------+ 
1 row in set (0.00 sec) 
从上述执行结果可以看出,当前已经成功依据已有的数据表创建出与其结构相同的数
据表。
2.复制已有的表数据
复制已有的表数据是新增数据的一种方式,它是从已有的数据中获取数据,并且将获取

MySQL数据库原理、设计与应用(第1 16 2版) 
的数据添加到对应的数据表中。需要注意的是,此种方式获取数据与添加数据的表结构要
相同,否则可能会遇到添加不成功的情况,基本语法格式如下。 
INSERT [INTO]新数据表名称[(字段名[, …])] 
SELECT *| {字段名[, …]} FROM 源表; 
在上述语法格式中,如果将新数据表名称和源表设为同一个数据表,可在短期内快速增
加该数据表的数据量。
下面演示如何将sh_goods表中的数据复制到数据表my_goods中,具体SQL语句及
执行结果如下。 
mysql>INSERT INTO mydb.my_goods SELECT * FROM sh_goods; 
Query OK,10 rows affected (0.01 sec) 
Records: 10 Duplicates: 0 Warnings: 0 
执行完上述SQL语句后,使用SELECT语句查看商品数据的添加情况,会看到已将数
据表sh_goods中的数据完全复制到了数据表my_goods中。由于查询结果很长,比较占用
篇幅,这里不再演示。
需要注意的是,在向一张数据表中复制数据时,如果该数据表中含有主键,可能会遇到
主键重复的问题。例如,再次将sh_goods表中的数据复制到my_goods表中,系统会报主
键重复的错误,具体SQL语句如下。 
mysql>INSERT INTO mydb.my_goods SELECT * FROM sh_goods; 
ERROR 1062 (23000): Duplicate entry '1' for key 'my_goods.PRIMARY' 
对于上述问题,可以通过指定主键id字段以外的字段来完成数据复制,具体SQL语句如下。 
mysql> INSERT INTO mydb.my_goods (category_id,name,keyword,content, 
-> price,stock,score,comment_count) 
-> SELECT category_id,name,keyword,content,price,stock, 
-> score,comment_count FROM sh_goods; 
Query OK,10 rows affected (0.01 sec) 
Records: 10 Duplicates: 0 Warnings: 0 
上述SQL语句中,在添加数据时,指定除主键id字段之外的字段,避免了主键重复。
多学一招:临时表
临时表是一种在当前会话中可见,并在当前会话关闭时自动删除的数据表,主要用于临
时存储数据。若要创建临时表,只需要在CREATE 与TABLE 关键字中间添加
TEMPORARY即可,示例如下。 
#方式1: 创建临时表
CREATE TEMPORARY TABLE mydb.tmp_table1 (id int); 
Query OK,0 rows affected (0.00 sec) 
#方式2: 创建临时表并复制数据
CREATE TEMPORARY TABLE mydb.tmp_table2 SELECT id,name FROM shop.sh_goods; 
Query OK,10 rows affected (0.00 sec) 
Records: 10 Duplicates: 0 Warnings: 0

第5章 单表操作1 17 
上述示例中,方式1表示在mydb数据库下创建一个tmp_table1临时表;方式2表示将
shop数据库下的sh_goods数据表中的数据复制到mydb数据库下的tmp_table2临时表
中。临时表中数据的操作与普通表相同,都可以进行SELECT、INSERT、UPDATE 和
DELETE操作,这里不再演示。
需要注意的是,使用SHOW TABLES语句不能查看当前数据库下有哪些临时表。
若要修改临时表的表名必须使用ALTER TABLE,而不能使用RENAMETABLE。
下面演示如何将tmp_table2的表名改为tmp_table3,具体SQL语句及示例结果如下。 
mysql>ALTER TABLE mydb.tmp_table2 RENAME TO mydb.tmp_table3; 
Query OK,10 rows affected (0.00 sec) 
Records: 10 Duplicates: 0 Warnings: 0 
使用DESC语句查看临时表的结构,具体SQL语句及执行结果如下。 
mysql>DESC mydb.tmp_table3; 
+-------+--------------+------+-----+---------+-------+ 
| Field | Type | Null | Key | Default | Extra | 
+-------+--------------+------+-----+---------+-------+ 
| id | int unsigned | NO | | 0 | NULL | 
| name | varchar(120) | NO | | | NULL | 
+-------+--------------+------+-----+---------+-------+ 
2 rows in set (0.00 sec) 
上述SQL语句中,使用DESC语句可以查看tmp_table3临时表的表结构。
5.1.2 解决主键冲突
在向数据表中添加一条记录时,如果添加的记录的主键值在现有的数据中已经存在,会
产生主键冲突的情况。
下面演示主键冲突的情况。例如,当my_goods表经过数据复制以后,再添加一条会引
起主键冲突的数据,具体SQL语句及执行结果如下。 
mysql>INSERT INTO mydb.my_goods (id,name,content,keyword) 
-> VALUES (20,'橡皮','修正书写错误','文具'); 
ERROR 1062 (23000): Duplicate entry '2' for key 'my_goods.PRIMARY' 
从上述执行结果可以看出,系统提示添加数据的主键发生冲突。若要解决这类问题, 
MySQL中提供了两种方式,分别为主键冲突更新和主键冲突替换,下面分别进行讲解。
1.主键冲突更新
主键冲突更新是指在添加数据的过程中若发生主键冲突,则添加数据操作利用更新的
方式实现,语法格式如下。 
INSERT [INTO]数据表名称[(字段名[,…])] 
{VALUES | VALUE} (值[,…]) 
ON DUPLICATE KEY UPDATE 字段名1=新值1[,字段名2=新值2,…];

MySQL数据库原理、设计与应用(第1 18 2版) 
上述语法格式中,在INSERT 语句后添加ONDUPLICATEKEY 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.01 sec) 
上述SQL语句中,添加数据时使用ON DUPLICATE KEY UPDATE 更新name字
段、content字段和keyword字段的值。由执行结果可知,当添加的记录与数据表中已存在
的记录主键冲突时,返回的结果为2rowsaffected,表示影响了两条记录。
修改完成后,查看数据是否添加成功,具体SQL语句及执行结果如下。 
mysql>SELECT name,content,keyword FROM mydb.my_goods WHERE id=20; 
+------+--------------+---------+ 
| name | content | keyword | 
+------+--------------+---------+ 
| 橡皮| 修正书写错误| 文具| 
+------+--------------+---------+ 
1 row in set (0.00 sec) 
从上述执行结果可以看出,成功查询出id字段值为20的记录,说明数据添加成功。
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) 
从上述执行结果可以看出,返回的结果为2rowsaffected,表示影响了两条记录。
修改完成后,查看数据是否添加成功,具体SQL语句及执行结果如下。 
mysql>SELECT name,content,keyword FROM mydb.my_goods WHERE id=20;

第5章 单表操作1 19 
+------+--------------+---------+ 
| name | content | keyword | 
+------+--------------+---------+ 
| 橡皮| 修正书写错误| 文具| 
+------+--------------+---------+ 
1 row in set (0.00 sec) 
从上述执行结果可以看出,成功查询出id字段值为20的记录,说明数据添加成功。
综上所述,主键冲突更新和主键冲突替换这两种方式都可以解决添加数据时主键冲突
的问题。主键冲突替换遇到主键重复会先删除、后新增,适用于添加数据字段特别多的
情况。
5.1.3 清空数据
在MySQL中,除了可以使用DELETE语句删除数据表中的部分数据或全部数据外, 
还可以通过TRUNCATE语句删除数据表中的全部数据,其基本语法如下。 
TRUNCATE [TABLE]数据表名称; 
上述语法中,“数据表名称”用于指定要删除的数据表的名称。
使用TRUNCATE语句与使用DELETE语句删除数据的操作类似,但是两者存在本
质的区别,具体如下。
(1)实现方式不同。TRUNCATE语句相当于先执行删除数据表(DROPTABLE)的
操作,再根据有效的表结构文件(.frm)重新创建数据表,实现数据清空操作;而DELETE语
句则是逐条地删除数据表中保存的数据。
(2)执行效率不同。在针对大型数据表(如千万级的数据记录)时,从实现方式角度考
虑,TRUNCATE语句比DELETE语句删除数据的方式执行效率更好。而当删除的数据
量很小时,DELETE语句的执行效率高于TRUNCATE语句。
(3)对设置了AUTO_INCREMENT 字段的影响不同。使用TRUNCATE语句删除
数据后,如果字段值设置了AUTO_INCREMENT,那么再次添加数据时,该字段的值会从
默认的初始值重新开始;而使用DELETE 语句删除数据时,字段值会保持原有的自动增
长值。
(4)删除数据的范围不同。TRUNCATE语句只能用于清空数据表中的全部数据;而
DELETE语句可以通过WHERE子句指定删除满足条件的部分数据。
(5)返回值含义不同。TRUNCATE语句的返回值一般是无意义的;而DELETE语句
则会返回符合条件被删除的数据数量。
(6)所属SQL语言的组成部分不同。TRUNATE语句通常被认为是数据定义语言; 
而DELETE语句属于数据操作语言。
下面通过实际操作演示TRUNCATE 语句和DELETE 语句的区别。使用
TRUNCATE语句删除全部数据并重新添加一条数据,具体SQL语句及执行结果如下。 
#删除全部数据
mysql>TRUNCATE TABLE mydb.my_goods;

MySQL数据库原理、设计与应用(第1 20 2版) 
Query OK,0 rows affected (0.06 sec) 
#添加数据
mysql>INSERT INTO mydb.my_goods (name,content,keyword) 
->VALUES ('香蕉','一种富含钾元素的水果','水果'); 
Query OK,1 row affected (0.01 sec) 
#查看数据
mysql>SELECT id,name,content,keyword FROM mydb.my_goods; 
+----+------+----------------------+---------+ 
| id | name | content | keyword | 
+----+------+----------------------+---------+ 
| 1 | 香蕉| 一种富含钾元素的水果| 水果| 
+----+------+----------------------+---------+ 
1 row in set (0.00 sec) 
从上述执行结果可以看出,执行TRUNCATE语句后,返回值为0rowsaffected,明显
无实际意义。删除数据后,再次新增一条数据,查询到的商品id值为1。
使用DELETE语句删除全部数据并重新添加一条数据,查询添加后的结果,具体SQL 
语句及执行结果如下。 
#删除全部数据
mysql>DELETE FROM mydb.my_goods; 
Query OK,1 row affected (0.01 sec) 
#添加数据
mysql>INSERT INTO mydb.my_goods (name,content,keyword) 
->VALUES ('苹果','一种很有营养的水果','水果'); 
Query OK,1 row affected (0.01 sec) 
#查看数据
mysql>SELECT id,name,content,keyword FROM mydb.my_goods; 
+----+------+--------------------+---------+ 
| id | name | content | keyword | 
+----+------+--------------------+---------+ 
| 2 | 苹果| 一种很有营养的水果| 水果| 
+----+------+--------------------+---------+ 
1 row in set (0.00 sec) 
从上述执行结果可以看出,执行DELETE语句后,返回值为1rowaffected,表示有一
条记录受影响。删除数据后,再次新增一条数据,查询到的商品id值为2。
5.1.4 去除查询结果中的重复记录
数据表的字段如果没有设置唯一约束或主键约束,那么该字段就有可能存储了重复的
值。在实际应用中,有时需要从查询记录中去除重复数据,这时可以使用SELECT 语句的
查询选项DISTINCT实现去重查询。带有查询选项的SELECT语句的语法格式如下。 
SELECT [查询选项]字段名[,…]FROM 数据表名称; 
上述语法中,查询选项为可选项,取值为ALL或DISTINCT,其中ALL为默认值,表
示保存所有查询到的记录;当设置为DISTINCT 时,表示去除重复记录,只保留一条记录。

第5章 单表操作1 21 
需要注意的是,当查询的字段有多个时,只有所有字段的值完全相同,才会被认为是重复
数据。下
面通过具体操作演示查询选项DISTINCT 的使用。先查看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.01 sec) 
从上述执行结果可以看出,查询结果中仅包含3条记录,分别为办公、电子产品和服装, 
不再包含重复的记录。
5.2 排序和限量
随着电子商务网站的迅速发展,商品的数量越来越多,商品的种类越来越丰富,人们在
查看商品列表时,常常会对其进行排序,以便将符合要求的数据显示在前面,方便进一步操
作。同时,为了提高执行效率,经常需要对操作的数据进行限量。例如,在查看商品时,只显
示10条符合要求的记录。本节详细讲解MySQL中的排序和限量操作。

MySQL数据库原理、设计与应用(第1 22 2版) 
5.2.1 排序
在查询数据表中的数据时,如果需要进行排序,可以通过ORDERBY 来实现。排序可
以使数据更有组织性、更容易查找,经过排序整理后的数据便于观察,易于从中发现规律。
同样,在我们的学习和工作中,也需要做到有组织、有计划,以便更高效地完成任务。
ORDERBY排序查询的基本语法格式如下。 
SELECT *| {字段名[,…]} FROM 数据表名称
ORDER BY 字段名1 [ASC | DESC][,字段名2 [ASC | DESC]]…; 
上述语法格式中,使用ORDERBY 进行排序时,如果不指定排序方式,默认按照ASC 
(ascending,升序)方式进行排序。排序意味着数据与数据发生比较,需要遵循一定的比较
规则,具体规则取决于当前使用的校对集。默认情况下,数字和日期的顺序为从小到大;英
文字母的顺序按ASCII码的次序,即从A 到Z。如果想要降序排序,将ASC 改为DESC 
(descending,降序)即可。
ORDERBY可以对多个字段的值进行排序,首先按照字段名1进行排序,当字段名1 
的值相同时,再按照字段名2进行排序,以此类推。ORDERBY后面也可以跟表达式。
需要说明的是,按照指定字段进行排序时,如果指定字段中包含NULL,NULL会被当
作最小值进行排序。
下面演示查询sh_goods表中的数据,让数据在显示时首先按商品分类(category_id字
段)升序排序,然后再按商品价格(price字段)降序排序,具体SQL语句及执行结果如下。 
mysql>SELECT category_id,keyword,name,price FROM sh_goods 
->ORDER BY category_id,price DESC; 
+-------------+----------+-----------------------+---------+ 
| category_id | keyword | name | price | 
+-------------+----------+-----------------------+---------+ 
| 3 | 办公| 钢笔T1616 | 15.00 | 
| 3 | 办公| 碳素笔GP1008 | 1.00 | 
| 3 | 办公| 2H 铅笔S30804 | 0.50 | 
| 6 | 电子产品| 华为P50 智能手机| 1999.00 | 
| 8 | 电子产品| 桌面音箱BMS10 | 69.00 | 
| 9 | 电子产品| 头戴耳机Star Y360 | 109.00 | 
| 11 | 电子产品| 办公计算机天逸510Pro | 2000.00 | 
| 12 | 电子产品| 超薄笔记本Pro12 | 5999.00 | 
| 15 | 服装| 收腰风衣中长款| 299.00 | 
| 16 | 服装| 薄毛衣联名款| 48.00 | 
+-------------+----------+-----------------------+---------+ 
10 rows in set (0.00 sec) 
从上述执行结果可以看出,查询的所有数据先按category_id字段升序排序,相同
category_id值的记录再按照price字段降序排序。此外,由于sh_goods数据表的字符集是
utf8mb4,当排序的字段为中文时,默认不会按照中文拼音的顺序排序。在不改变数据表结
构的情况下,若要强制字段按中文首字母排序,可以使用“CONVERT(字段名USING 
gbk)”函数将字段的字符集指定为gbk。
下面演示如何查询sh_goods表中的数据,按照keyword关键词字段进行降序排序,具

第5章 单表操作1 23 
体SQL语句及执行结果如下。 
mysql>SELECT category_id,keyword,name,price FROM sh_goods 
->ORDER BY CONVERT(keyword USING gbk) DESC; 
+-------------+----------+-----------------------+---------+ 
| category_id | keyword | name | price | 
+-------------+----------+-----------------------+---------+ 
| 15 | 服装| 收腰风衣中长款| 299.00 | 
| 16 | 服装| 薄毛衣联名款| 48.00 | 
| 12 | 电子产品| 超薄笔记本Pro12 | 5999.00 | 
| 6 | 电子产品| 华为P50 智能手机| 1999.00 | 
| 8 | 电子产品| 桌面音箱BMS10 | 69.00 | 
| 9 | 电子产品| 头戴耳机Star Y360 | 109.00 | 
| 11 | 电子产品| 办公计算机天逸510Pro | 2000.00 | 
| 3 | 办公| 2H 铅笔S30804 | 0.50 | 
| 3 | 办公| 钢笔T1616 | 15.00 | 
| 3 | 办公| 碳素笔GP1008 | 1.00 | 
+-------------+----------+-----------------------+---------+ 
10 rows in set (0.00 sec) 
上述SELECT语句中,按照keyword字段值的中文首字母进行降序排序。
多学一招:按指定顺序排序
前面使用ORDERBY实现了对字段的升序和降序排序,如果想要对sh_goods表中
keyword字段的查询结果集进行指定顺序排序,则可以借助FIELD()函数来实现。使用
FIELD()函数查询排序结果的语法格式如下。 
SELECT * | {字段名[, …]} FROM 数据表名称
ORDER BY FIELD(value, str1, str2, str3, …); 
上述语法格式表示将获取到的value字段,按照“str1,str2,str3”的顺序进行排序,其中
str1、str2、str3属于查询字段value的结果集中的内容。value参数后面的参数可自定义,不
限制参数个数。
下面演示如何查询sh_goods表中的数据,将关键词keyword字段按照“办公,服装,电
子产品”排序,具体SQL语句及执行结果如下。 
mysql>SELECT category_id,keyword,name,price FROM sh_goods 
->ORDER BY FIELD(keyword,'办公','服装','电子产品'); 
+-------------+----------+-----------------------+---------+ 
| category_id | keyword | name | price | 
+-------------+----------+-----------------------+---------+ 
| 3 | 办公| 2H 铅笔S30804 | 0.50 | 
| 3 | 办公| 钢笔T1616 | 15.00 | 
| 3 | 办公| 碳素笔GP1008 | 1.00 | 
| 15 | 服装| 收腰风衣中长款| 299.00 | 
| 16 | 服装| 薄毛衣联名款| 48.00 | 
| 12 | 电子产品| 超薄笔记本Pro12 | 5999.00 | 
| 6 | 电子产品| 华为P50 智能手机| 1999.00 | 
| 8 | 电子产品| 桌面音箱BMS10 | 69.00 | 
| 9 | 电子产品| 头戴耳机Star Y360 | 109.00 | 
| 11 | 电子产品| 办公计算机天逸510Pro | 2000.00 |