MySQL入门第五天（单表操作）

目录

5.1数据操作

（1）复制表结构和数据

1.复制表结构

2.复制表数据

临时表的使用

（2）解决主键冲突

（3）清空数据

（4）去除重复记录

5.2排序与限量

（1）排序

1.单字段排序

2.多字段排序

（2）限量

5.3分组与聚合函数

（1）分组

1.分组统计

2.分组排序

3.多分组统计

4.回溯统计

5.统计筛选

（2）聚合函数

5.4运算符

运算符优先级

---

5.1数据操作

（1）复制表结构和数据

1.复制表结构

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 表名
{ LIKE 旧表名| (LIKE旧表名 ) };

从“旧表名”中 复制一份相同的表结构 ，但不会复制表中保存的数据。

       “{}”表示语法在使用时可以任选其中一种，“|”表示或的意思。

2.复制表数据

INSERT [INTO] 数据表名1 [(字段列表)] SELECT [(字段列表)] FROM 数据表名2;

数据表名1和数据表名2通常使用的是同一个表

可在短期内快速增加表的数据量，测试表的压力以及效率等。

二次复制旧表数据到新表时，会造成主键冲突

临时表的使用

临时表：是一种仅在当前会话中可见，并在当前会话关闭时自动删除的数据表。

作用：主要用于临时存储数据。

语法：只需在CREATE与TABLE关键中间添加TEMPORARY即可。

建临时表时指定的数据库可以是 MySQL 服务器中存在的数据库也可以是不存在的数据库。

若数据库不存在，操作临时表时必须使用“数据库 . 临时表名”指定临时表所在的数据库。

临时表中数据的操作与普通表相同，都可以进行SELECT 、 INSERT 、 UPDATE 和 DELETE 操作。

SHOW TABLES不能查看指定数据库下有哪些临时表 .

临时表的表名必须使用ALTER TABLE 修改，而不能使用 RENAME TABLE…TO 修改。

（2）解决主键冲突

1.主键冲突更新

INSERT [INTO] 数据表名 [(字段列表)] {VALUES | VALUE} (字段列表)
ON DUPLICATE KEY UPDATE 字段名1 = 新值1[,字段名2 = 新值2] …;

2.主键冲突替换

REPLACE [INTO] 数据表名 [(字段列表)]
{VALUES | VALUE} (值列表) [, (值列表)] …;

（3）清空数据

TRUNCATE [TABLE] 表名

TRUNCATE操作与DELETE语句在本质上的区别

1.实现方式不同： TRUNCATE 本质上先执行删除（ DROP ）数据表的操作，然后再根据有效的表结构文件（ . frm ）重新创建数据表的方式来实现数据清空操作。而 DELETE 语句则是逐条的删除数据表中保存的记录。

2.执行效率不同：在针对大型数据表（如千万级的数据记录）时， TRUNCATE 清空数据的实现方式，决定了它比 DELETE 语句删除数据的方式执行效率更高。

3.对 AUTO_INCREMENT 的字段影响不同： TRUNCATE 清空数据后，再次向表中添加数据，自动增长字段会从默认的初始值重新开始，而使用 DELETE 语句删除表中的记录时，则不影响自动增长值。

       4.删除数据的范围不同：TRUNCATE语句只能用于清空表中的所有记录，而DELETE语句可通过WHERE指定删除满足条件的部分记录

       5.返回值含义不同：TRUNCATE操作的返回值一般是无意义的，而DELETE语句则会返回符合条件被删除的记录数。

6.所属 SQL 语言的不同组成部分： DELETE 语句属于 DML 数据操作语句，而 TRUNCATE 通常被认为是 DDL 数据定义语句。

• 当删除的数据量很小时， DELETE 的执行效率要比 TRUNCATE 高；

• 只有删除的数据量很大时，才能看出 TRUNCATE 的执行效率比 DELETE 高。

（4）去除重复记录

SELECT select选项 字段列表 FROM 数据表;

select 选项：默认值为 All ，表示保存所有查询到的记录。

当设置为 DISTINCT 时，表示去除重复记录，只保留一条。

当查询记录的字段有多个时，必须所有字段的值完全相同才被认为是重复记录。

5.2排序与限量

（1）排序

1.单字段排序

指的是查询时仅按照一个指定字段进行升序或降序排序。

SELECT  *|{字段列表} FROM 数据表名
ORDER BY 字段名 [ASC | DESC];

ASC( ascend ) 表示升序， DESC( descend ) 表示降序。

ORDER BY 默认值 为 ASC 。

2.多字段排序

指的是查询时按照指定的多个字段进行升序或降序排序。

SELECT  *|{字段列表} FROM 数据表名
ORDER BY 字段名1 [ASC | DESC] [, 字段名2 [ASC | DESC]]…;

多字段排序首先按照字段名1进行排序，当字段1的值相同时，再按照字段2进行排序，依次类推。

• 数据表的字符集是 utf8 ，当排序的字段为中文时，默认不会按照中文拼音的顺序排序。

• 在不改变数据表结构的情况下，可以使用“ CONVERT( 字段名 USING gbk )” 函数强制让指定的字段按中文排序。

• 在按照指定字段进行升序排列时，如果某条记录的字段值为 NULL ，则系统会将 NULL 看作是最小的值，从而将其显示在查询结果中的第一条记录的位置。

（2）限量

对于一次性查询出的大量记录，不仅不便于阅读查看，还会浪费系统效率。

MySQL中提供了一个关键字LIMIT，可以限定记录的数量，也可以指定查询从哪一条记录开始（通常用于分页）。

SELECT [select 选项] 字段列表 FROM 数据表名 
[WHERE 条件表达式] [ORDER BY 字段 ASC|DESC]
LIMIT [OFFSET,] 记录数;

记录数 ：表示限定获取的最大记录数量。仅含此参数时，表示从第 1 条记录开始获取。

OFFSET ：表示偏移量，用于设置从哪条记录开始，默认第 1 条记录的偏移量值为 0 ，第 2 条记录的偏移量值为 1 ，依次类推。

在MySQL中除了对查询记录进行排序和限量外，对数据表中记录的更新与删除操作也可以进行排序和限量。

# 数据更新的排序与限量
UPDATE 数据表名 SET 字段=新值, … [WHERE 条件表达式]
ORDER BY 字段 ASC|DESC LIMIT 记录数;
# 数据删除的排序与限量
DELETE FROM 数据表名 [WHERE 条件表达式]
ORDER BY 字段 ASC|DESC LIMIT 记录数;

5.3分组与聚合函数

（1）分组

分组操作的目的：就是为了统计。

分组的关键字：GROUP BY。

分组筛选：用HAVING进行条件筛选。

常见的分组：分组统计、分组排序、多分组统计、回溯统计、统计筛选。

1.分组统计

查询时在WHERE条件后添加GROUP BY即可进行分组统计。

SELECT [select 选项] 字段列表 FROM 数据表名
[WHERE 条件表达式] GROUP BY 字段名;

在 MySQL5.7 中分组后， SELECT 获取的字段列表只能是 GROUP BY 分组的字段 ，或 使用了聚合函数的非分组字段 。

若在获取非分组字段时没有使用聚合函数，MySQL会报错误提示。

2.分组排序

默认分组操作的字段提供按升序排序，因此在分组时可为指定的字段进行升序或降序排序。

SELECT [select 选项] 字段列表 FROM 数据表名
[WHERE 条件表达式] GROUP BY 字段名 ORDER BY 字段名[ASC | DESC];

GROUP BY 分组排序的实现需要使用 ORDER BY ， 而且需要 在分组字段后添加 ASC （ 升序，默认值可省略） 或 DESC （降序）。

3.多分组统计

按某个字段进行分组后，对已经分组的数据进行再次分组的操作。

  SELECT [select 选项] 字段列表 FROM 数据表名 
  [WHERE 条件表达式]
  GROUP BY 字段名1,字段名2 … ORDER BY 字段名1 [ASC | DESC], [, 字段名2 [ASC | DESC]]…;

查询出的数据首先按照字段1进行分组排序，再将字段1相同的结果按照字段2进行分组排序，依次类推。

4.回溯统计

在根据指定字段分组后，系统又自动对分组的字段向上进行了一次新的统计并产生一个新的统计数据，且该数据对应的分组字段值为NULL。

SELECT [select 选项] 字段列表 FROM 数据表名
[WHERE 条件表达式]
GROUP BY 字段名1,字段名2 … ORDER BY 字段名1 [ASC | DESC], [, 字段名2 [ASC | DESC]]… WITH ROLLUP;

虽然回溯统计对数据的分析很有帮助，但是MySQL的同一个查询语句中回溯统计（WITH ROLLUP）与排序（ORDER BY）仅能出现一个。

5.统计筛选

对查询的数据分组时，可以利用HAVING根据条件进行数据筛选，它与前面学习过的WHERE功能相同，但是在实际运用时两者有一定的区别。

WHERE 操作是从数据表中获取数据，将数据从磁盘存储到内存中，而 HAVING 是对已存放到内存中的数据进行操作。

HAVING 位于 GROUP BY 子句后，而 WHERE 位于 GROUP BY 子句之前。

HAVING 关键字后可以使用聚合函数，而 WHERE 则不可以。通常情况下， HAVING 关键字与 GROUP BY 一起使用，对分组后的结果进行过滤。

SELECT [select 选项] 字段列表 FROM 数据表名
[WHERE 条件表达式]
GROUP BY 字段名 ORDER BY 字段名 [ASC | DESC], …[WITH ROLLUP]
HAVING 条件表达式;

WHERE 条件之后的所有语句都是对 内存 中的数据进行操作。

  HAVING 则根据条件表达式 对分组后的内容 进行过滤。

在查询中使用别名

# 字段设置别名
SELECT 字段1 [AS] 别名1, 字段2 [AS] 别名2 [, …] FROM 表名
# 表设置别名
SELECT 表别名.字段 [, …] FROM 表名 [AS] 表别名

AS 用于为其前面的字段、表达式、函数等设置别名，也可省略 AS 使用空格代替。

例如，为 category_id 设置别名 cid ，可以使用“ category_id AS cid ”或“ category_id cid ” 。

（2）聚合函数

函数名	描述
COUNT()	返回参数字段的数量，不统计为NULL的记录
SUM()	返回参数字段之和
AVG()	返回参数字段的平均值
MAX()	返回参数字段的最大值
MIN()	返回参数字段的最小值
GROUP_CONCAT()	返回符合条件的参数字段值的连接字符串
JSON_ARRAYAGG()	将符合条件的参数字段值做为单个JSON数组返回，MySQL5.7.22新增
JSON_OBJECTAGG()	将符合条件的参数字段值做为单个JSON对象返回，MySQL5.7.22新增

5.4运算符

运算符优先级

运算符

=(比较运算符)、<=>、>=、>、<=、<、<>、!=、IS、LIKE、REGEXP、IN

BETWEEN、CASE、WHEN、THEN、ELSE

NOT

AND、&&

XOR

OR、||

=(赋值运算符)、:=

同行的运算符具有相同的优先级，除赋值运算符从右到左运算外，其余相同级别的运算符，在同一个表达式中出现时，运算的顺序为从左到右依次进行。

若要提升运算符的优先级别，可以使用圆括号“ () ” ，当表达式中同时出现多个圆括号时，最内层的圆括号中的表达式优先级最高。