MySQL 查询数据（六）

查询数据

基本查询语句

• MySQL 从数据表中查询数据的基本语句为 SELECT 语句。SELECT 语句的基本格式是：

  SELECT
  	{* | <字段列表>}
  	[
          FROM <表1>，<表2>
          [
              WHERE <表达式>
              [ GROUP BY <group by definition> ]
              [ HAVING <expression> [{<operator> <expression>}...] ]
              [ ORDER BY <order by definition> ]
              [ LIMIT [offset,] <row count> 
          ]
      ];
  

  其中，各条子句的含义如下：

  • {*|< 字段列表 >} 包含星号通配符选字段列表，表示查询的字段，其中字段列至少包含一个字段名称，如果要查询多个字段，多个字段之间用逗号隔开，最后一个字段后不用加逗号。
  • FROM < 表 1>,< 表 2>...，表 1 和表 2 表示查询数据的来源，可以是单个或者多个。
  • WHERE 子句是可选项，如果选择该项，就将限定查询行必须满足的查询条件。
  • GROUP BY < 字段 >，该子句告诉 MySQL 如何显示查询出来的数据，并按照指定的字段分组。
  • [ORDER BY < 字段 >]，该子句告诉 MySQL 按什么样的顺序显示查询出来的数据，可以进行的排序有升序（ASC）和降序（DESC）。
  • [LIMIT [ < offset > ,] < row count >]，该子句告诉 MySQL 每次显示查询出来的数据条数。

单表查询

• 单表查询是指从一张表数据中查询所需的数据。主要有查询所有字段、查询指定字段、查询指定记录、查询空值、多条件的查询、对查询结果进行排序等。

查询所有字段

1. 在 SELECT 语句中使用星号(*)通配符查询所有字段

   SELECT 查询记录最简单的形式是从一个表中检索所有记录，实现的方法是使用星号（*）通配符指定查找所有列的名称。语法格式如下：

   SELECT * FROM 表名;
   

2. 在 SELECT 语句中指定所有字段

   SELECT 关键字后面的字段名为将要查找的数据，因此可以将表中所有字段的名称跟在 SELECT 子句后面，如果忘记了字段名称，可以使用 DESC 命令查看表的结构。

查询指定字段

1. 查询单个字段

   查询表中的某一个字段，语法格式为：

   SELECT 列名 FROM 表名;
   

2. 查询多个字段

   使用 SELECT 声明，可以获取多个字段下的数据，只需要在关键字 SELECT 后面指定要查找的字段的名称，不同字段名称之间用逗号（,）分隔开，最后一个字段后面不需要加逗号，语法格式如下：

   SELECT 字段名1,字段名2,...,字段名n FROM 表名;
   

查询指定记录

• 数据库中包含大量的数据，根据特殊要求，可能只需要查询表中的指定数据，即对数据进行过滤。在 SELECT 语句中，通过 WHERE 子句可以对数据进行过滤，语法格式为：

  SELECT 字段名1,字段名2,...,字段名n
  FROM 表名
  WHERE 查询条件
  

  在 WHERE 子句中，MySQL 提供了一系列的条件判断符，如下表所示。

  操作符	说明
  =	相等
  <>，!=	不相等
  <	小于
  <=	小于或者等于
  >	大于
  >=	大于或者等于
  BETWEEN AND	位于两值之间

带 IN 关键字的查询

• IN 操作符用来查询满足指定范围内的条件的记录，使用 IN 操作符，将所有检索条件用括号括起来，检索条件之间用逗号分隔开，只要满足条件范围内的一个值即为匹配项。相反，可以使用关键字 NOT 来检索不在条件范围内的记录。

带 BETWEEN AND 的范围查询

• BETWEEN AND 用来查询某个范围内的值，该操作符需要两个参数，即范围的开始值和结束值，如果字段值满足指定的范围查询条件，则这些记录被返回。BETWEEN AND 操作符前可以加关键字 NOT，表示指定范围之外的值。

带 LIKE 的字符匹配查询

• 通配符是一种在 SQL 的 WHERE 条件子句中拥有特殊意思的字符，SQL 语句中支持多种通配符，可以和 LIKE 一起使用的通配符有 ‘%’ 和 ‘_’ 。

1. 百分号通配符 ‘%’ ，匹配任意长度的字符，甚至包括零字符

   在搜索匹配时，通配符 ‘%’ 可以放在不同位置，例如 ’b%y‘ 可以查询以 b 开头、以 y 结尾的数据。

2. 下划线通配符 ‘_’ ，一次只能匹配任意一个字符

   下划线通配符 ’ _ ‘ 的用法和 ’%‘ 相同，区别是 ’%‘ 可以匹配多个字符，而 ’ _ ‘ 只能匹配任意单个字符，如果要匹配多个字符，则需要使用相同个数的 ‘ _ ’ 。

查询空值

• 数据表创建的时候，设计者可以指定某列中是否可以包含空值（NULL）。空值不同于 0，也不同于空字符串。空值一般表示数据未知、不适用或将在以后添加数据。在 SELECT 语句中使用 IS NULL 子句，可以查询某字段内容为空记录。与 IS NULL 相反的是 IS NOT NULL，该关键字查找字段不为空的记录。

带 AND 的多条件查询

• 使用 SELECT 查询时，可以增加查询的限制条件，这样可以使查询的结果更加精确。MySQL 在 WHERE 子句中使用 AND 操作符限定只有满足所有查询条件的记录才会被返回。可以使用 AND 连接两个甚至多个查询条件，多个表达式之间用 AND 分开。

带 OR 的多条件查询

• 与 AND 相反，在 WHERE 声明中使用 OR 操作符，表达只需要满足其中一个条件的记录即可返回。OR 也可以连接两个甚至多个查询条件，多个条件表达式之间用 OR 分开。
• 提示：OR 可以和 AND 一起使用，但是在使用时要注意两者的优先级，由于 AND 的优先级高于 OR，因此先对 AND 两边的操作数进行操作，再与 OR 中的操作数结合。

查询结果不重复

• 在 SELECT 语句中，可以使用 DISTINCT 关键字指示 MySQL 消除重复的记录值。语法格式为：

  SELECT DISTINCT 字段名 FROM 表名;
  

对查询结果排序

• MySQL 可以通过在 SELECT 语句中使用 ORDER BY 子句，对查询的结果进行排序。

1. 单列排序

   通过 ORDER BY 子句，MySQL 可以对单一列的数据按照字母表的顺序进行排序。语法格式如下：

   SELECT 字段名1 [,字段名2,...字段名n] FROM 表名 ORDER BY 其中一个字段名;
   

2. 多列查询

   对多列数据进行排序，必须将需要排序的列之间用逗号隔开。语法格式如下：

   SELECT 字段名1 [,字段名2,...字段名n] FROM 表名 ORDER BY 字段名a,字段名b,...字段名n;
   

   提示：在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第一列数据中所有值都是唯一的，将不再对第二列进行排序。

3. 指定排序方向

   默认情况下，查询数据按字母升序进行排序（A ~ Z），但数据的排序并不仅限于此，还可以使用 ORDER BY 对查询结果进行降序排序（Z ~ A），这可以通过关键字 DESC 实现。语法格式如下：

   SELECT 字段名1 [,字段名2,...字段名n] FROM 表名 ORDER BY 字段名a DESC(ASC) [,字段名b,...字段名n];
   

   提示：与 DESC 相反的是 ASC（升序排序），将字段列中的数据按字母表顺序升序排序，实际上，在排序的时候 ASC 是默认的排序方式，所以加不加都可以。也可以对多列进行不同的顺序排序。如果要对多列都进行降序排序，必须要在每一列的列名后面加 DESC 关键字。

分组查询

• 分组查询是对数据按照某个或多个字段进行分组，MySQL 中使用 GROUP BY 关键字对数据进行分组，基本语法格式为：

  [GROUP BY 字段] [HAVING <条件表达式>]
  

  字段值为进行分组时所依据的列名称；“HAVING < 条件表达式 >” 指定满足表达式限定条件的结果将被显示。

1. 创建分组

   GROUP BY 关键字通常和集合函数一起使用，比如 MAX()、MIN()、COUNT()、SUM()、AVG()，可以在分组过程中使用 COUNT(*) 函数，对每个组进行集合运算。可以使用 GROUP_CONCAT() 函数，将每个分组中各个字段的值显示出来。

2. 使用 HAVING 过滤分组

   GROUP BY 可以 HAVING 一起限定显示记录所需满足的条件，只有满足条件的分组才会被显示。

   提示：HAVING 关键字与 WHERE 关键字都是用来过滤数据的，两者有什么区别呢？其中最重要的一点是，HAVING 在数据分组之后进行过滤来选择分组，而 WHERE 在分组之前用来选择记录。另外，WHERE 排除的记录不再包括在分组中。

3. 在 GROUP BY 子句中使用 WITH ROLLUP

   使用 WITH ROLLUP 关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。

4. 多字段分组

   使用 GROUP BY 可以对多个字段进行分组，GROUP BY 关键字后面跟需要分组的字段，MySQL 根据多字段的值来进行层次分组，分组层次从左到右，即先按第 1 个字段分组，然后在第 1 个字段值相同的记录中，再根据第 2 个字段的值进行分组。

5. GROUP BY 和 ORDER BY 一起使用

   某些情况下需要对分组进行排序，ORDER BY 用来对查询的记录排序，如果和 GROUP BY 一起使用可以完成对分组进行排序。

   提示：当使用 ROLLUP 时，不能同时使用 ORDER BY 子句进行结果排序，即 ROLLUP 和 ORDER BY 是互相排斥的。

使用 LIMIT 限制查询结果的数量

• SELECT 返回所有匹配的行，有可能是表中所有的行，如仅仅需要返回第一行或者前几行，使用 LIMIT 关键字，基本语法格式如下：

  LIMIT [位置偏移量,] 行数
  

  第一个 “位置偏移量” 参数指示 MySQL 从哪一行开始显示，是一个可选参数，如果不指定 “位置偏移量” ，将会从表中的第一条记录开始（第一条记录的位置偏移量是 0，第二天记录的位置偏移量是 1，依次类推）；第二个参数 “行数” 指示返回的记录条数。

提示：MySQL 5.7 中可以使用 “LIMIT 4 OFFSET 3” ，意思是获取从第 5 条记录开始后面的 3 条记录，和 “LIMIT 4,3” 返回的结果相同。

使用集合函数查询

• 有时候并不需要返回实际表中的数据，而只是对数据进行总结，MySQL 提供了一些查询功能，可以对获取的数据进行分析和报告。这些函数的功能有计算数据表中记录行数的总数。这些集合函数的名称和作用如下表所示。

  函数	作用
  AVG()	返回某列的平均值
  COUNT()	返回某列的行数
  MAX()	返回某列的最大值
  MIN()	返回某列的最小值
  SUM()	返回某列值的和

COUNT()函数

• COUNT() 函数统计数据表中包含的记录行的总数，或者根据查询结果返回列中包含的数据行数。其使用方法有两种：
  • COUNT(*) 计算表中总的函数，不管某列有数值或者为空值。
  • COUNT(字段名) 计算指定列下总的行数，计算时将忽略空值的行。

SUM()函数

• SUM() 是一个求总和的函数，返回指定列值的总和。
• 提示：SUM() 函数在计算时，忽略列值为 NULL 的行。

AVG()函数

• AVG() 函数通过计算返回的行数和每一行数据的和，求得指定列数据的平均值。
• 提示：AVG 函数使用时，其参数为要计算的列名称，如果要得到多个列的多个平均值，则需要在每一列上使用 AVG() 函数。

MAX()函数

• MAX() 返回指定列中的最大值。
• MAX() 函数不仅适用于查找数值类型，也可应用于字符类型（根据 ASCII 码值大小进行比较）。

MIN()函数

• MIN() 返回查询列中的最小值。

连接查询

• 连接是关系数据库模型的主要特点。连接查询是关系数据库中最主要的查询，主要包括内连接、外连接等。通过连接运算符可以实现多个表查询。

内连接查询

• 内连接（INNER JOIN）使用比较运算符进行表间某（某些）列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组合成新纪录，也就是说，在内连接查询中，只有满足条件的记录才能出现在结果关系中。

  1. 用 WHERE 子句进行内连接查询，语法格式如下：

     SELECT 字段名1 [,字段名2,...字段名n]
     FROM 表名1,表名2
     WHERE 连接条件; --(表名1.var1 = 表名2.var1)
     

  2. 用 INNER JOIN 语法进行内连接查询，语法格式如下：

     SELECT 字段名1 [,字段名2,...字段名n]
     FROM 表名1 INNER JOIN 表名2
     ON 连接条件;
     

     在这里的查询语句中，两个表之间的关系通过 INNER JOIN 指定。使用这种语法时，连接的条件使用 ON 子句给出而不是 WHERE，ON 和 WHERE 后面指定的条件相同。

• 提示：使用 WHERE 子句定义连接条件比较简单明了，而 INNER JOIN 语法是 ANSI SQL 的标准规范，使用 INNER JOIN 连接语法能够确保不会忘记连接条件，而且，WHERE 子句在某些时候会影响查询的性能。

• 如果在一个连接查询中涉及的两个表都是同一个表，那么这种查询称为自连接查询。自连接是一种特殊的内连接，是指相互连接的表在物理上为同一张表，但可以在逻辑上分为两张表。语法格式如下：

  SELECT 字段名1 [,字段名2,...字段名n]
  FROM 表名1 AS b1,表名1 AS b2
  WHERE 连接条件;
  

外连接

• 外连接查询将查询多个表中相关联的行，内连接时，返回查询结果集合中的仅是扶额查询条件和连接条件的行。但有时候需要包含没有关联的行中数据，即返回查询结果集合中的不仅包含符合连接条件的行，而且还包括左表（左外连接或左连接）、右表（右外连接或右连接）或两个边接表（全外连接）中所有数据行。外连接分为左外连接或左连接和右外连接或右连接。
  • LEFT JOIN（左连接）：返回包括左表中的所有记录和右表中连接字段相等的记录。
  • RIGHT JOIN（右连接）：返回包括右表中的所有记录和左表中连接字段相等的记录。

1. LEFT JOIN 左连接

   左连接的结果包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是连接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果行中，右表的所有选择列表列均为空值。语法格式如下：

   SELECT 字段名1 [,字段名2,...字段名n]
   FROM 表名1 LEFT OUTER JOIN 表名2
   ON 连接条件;
   

2. RIGHT JOIN 右连接

   右连接是左连接的反向连接，将返回右表的所有行，如果右表的某行在左表中没有匹配行，左表将返回空值。

   SELECT 字段名1 [,字段名2,...字段名n]
   FROM 表名1 RIGHT OUTER JOIN 表名2
   ON 连接条件;
   

复合条件连接查询

• 复合条件连接查询是在连接查询的过程中，通过添加过滤条件，限制查询的结果，使查询的结果更加准确。

子查询

• 子查询是指一个查询语句嵌套在另一个查询语句内部的查询。在 SELECT 子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。子查询中常用的操作符有 ANY（SOME）、ALL、IN、EXISTS。子查询可以添加到 SELECT、UPDATE 和 DELETE 语句中，而且可以进行多层嵌套。子查询中也可以使用比较运算符，如 **“<” “<=” “>” “>=”** 和 **“!=”** 等。

带 ANY、SOME 关键字的子查询

• ANY 和 SOME 关键字是同义词，表示满足其中任一条件，它们允许创建一个表达式对子查询的返回值列表进行比较，只要满足内层子查询中的任何一个比较条件，就返回一个结果作为外层查询的条件。

• ANY 关键字接在一个比较操作符的后面，表示若与子查询返回的任何值比较为 true，则返回 true。

  SELECT 字段名1 FROM 表名1 WHERE 字段名1 > ANY (SELECT 字段名2 FROM 表名2);
  

带 ALL 关键字的子查询

• ALL 关键字与 ANY 和 SOME 不同，使用 ALL 时需要同时满足所有内层查询的条件。
• ALL 关键字接在一个比较操作符的后面，表示与子查询返回的所有值比较为 true，则返回 true。

带 EXISTS 关键字的子查询

• EXISTS 关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行，如果至少返回一行，那么 EXISTS 的结果为 true，此时外层查询语句将进行查询；如果子查询没有返回任何行，那么 EXISTS 返回的结果是 false，此时外层语句将不进行查询。

  SELECT 字段名1 FROM 表名1 WHERE EXISTS (SELECT 字段名2 FROM 表名2 WHERE 查询条件);
  

• NOT EXISTS 与 EXISTS 使用方法相同，返回的结果相反。子查询如果至少返回一行，那么 NOT EXISTS 的结果为 false，此时外层查询语句将不进行查询；如果子查询没有返回任何行，那么 NOT EXISTS 返回的结果为 true，此时外层语句将进行查询。

• 提示：EXISTS 和 NOT EXISTS 的结果只取决于是否会返回行，而不取决于这些行的内容，所以这个子查询输入列表通常是无关紧要的。

带 IN 关键字的子查询

• IN 关键字进行子查询时，内层查询语句仅仅返回一个数据列，这个数据列里的值将提供给外层查询语句进行比较操作。通常用于一个表的查询需要另一个表的字段去匹配查询条件。

  SELECT 字段名1 FROM 表名1 WHERE 字段名2 IN (SELECT 字段名2 FROM 表名2 WHERE 查询条件);
  

• SELECT 语句中可以使用 NOT IN 关键字，其作用与 IN 正好相反。

带比较运算符的子查询

• 子查询时还可以使用其他的比较运算符，如 **“<” “<=” “>” “>=”** 和 **“!=”** 等。

  SELECT 字段名1 FROM 表名1 WHERE 字段名1 = (SELECT 字段名2 FROM 表名2 WHERE 查询条件);
  

合并查询结果

• 利用 UNION 关键字，可以给出多条 SELECT 语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同。各个 SELECT 语句之间使用 UNION 或 UNION ALL 关键字分隔。UNION 不使用关键字 ALL，执行的时候删除重复的记录，所有返回的行都是唯一的；使用关键字 ALL 的作用是不删除重复行也不对结果进行自动排序。基本语法格式如下：

  SELECT column,... FROM table1
  UNION [ALL]
  SELECT column,... FROM table2;
  

• UNION ALL 关键字执行时所需要的资源少，所以尽可能地使用它，确定查询结果中不会有重复数据或者不需要去掉重复数据的时候，应当使用 UNION ALL 以提高查询效率。

为表和字段取别名

为表取别名

• 当表名字很长或者执行一些特殊查询时，为了方便操作或者需要多次使用相同的表时，可以为表指定别名，用这个别名替代表原来的名称。为表取别名的基本语法格式为：

  表名 [AS] 表别名
  

  “表名” 为数据库中存储的数据表的名称，“表别名” 为查询指定的表的新名称，AS 关键字为可选参数。

• MySQL 可以同时为多个表取别名，而且表别名可以放在不同的位置，如 WHERE 子句、SELECT 列表、ON 子句以及 ORDER BY 子句等。

• 提示：在为表取别名时，要保证不能与数据库中的其他表的名称冲突。

为字段取别名

• 在使用 SELECT 语句显示查询结果时，MySQL 会显示每个 SELECT 后面指定的输出列，在有些情况下，显示的列的名称会很长或者名称不够直观，MySQL 可以指定列别名、替换字段或表达式。为字段取别名的基本语法格式为：

  列名 [AS] 列别名
  

  “列名” 为表中字段定义的名称，“列别名” 为字段新的名称，AS 关键字为可选参数。

• 也可以为 SELECT 子句中的计算字段取别名，例如，对使用 COUNT 聚合函数或者 CONCAT 等系统函数执行的结果字段取别名。

• 表别名只在执行查询的时候使用，并不在返回结果中显示，而列别名定义之后，将返回给客户端显示，显示的结果字段为字段列的别名。

使用正则表达式查询

• 正则表达式通常用来检索或替换那些符合某个模式的文本内容，根据指定的匹配模式匹配文本中符合要求的特殊字符串。MySQL 中使用 REGEXP 关键字指定正则表达式的字符匹配模式。下面列出了 REGEXP 操作符中常用字符匹配列表。

  选项	说明	例子	匹配值示例
  ^	匹配文本的开始字符	‘^b’ 匹配以字母 b 开头的字符串	book，big，banana，bike
  $	匹配文本的结束字符	‘st$’ 匹配以 st 结尾的字符串	test，resist，persist
  .	匹配任何单个字符	‘b.t’ 匹配任何 b 和 t 之间有一个字符的字符串	bit，bat，but，bite
  *	匹配零个或多个在它前面的字符	‘f*n’ 匹配字符 n 前面有任意个字符 f	fn，fan，faan，fabcn
  +	匹配前面的字符 1 次或多次	‘ba+’ 匹配以 b 开头后面紧跟至少有一个 a	ba，bay，bare，battle
  < 字符串 >	匹配包含指定的字符串的文本	‘fa’	fan，afa，faad
  [字符集合]	匹配字符集合中的任何一个字符	‘[xz]’ 匹配 x 或者 z	dizzy，zebra，x-ray，extra
  [^]	匹配不在括号内的任何字符	‘[ ^abc]’ 匹配任何不包含 a、b 或 c 的字符串	desk，fox，f8ke
  字符串{n,}	匹配前面的字符串至少 n 次	b{2} 匹配 2 个或更多的 b	bbb，bbbb，bbbbb
  字符串{n,m}	匹配前面的字符串至少 n 次、至多 m 次。如果 n 为 0，此参数为可选参数	b{2,4} 匹配最少 2 个、最多 4 个 b	bb，bbb，bbbb

查询以特定字符或字符串开头的记录

• 字符 ‘^’ 匹配以特定字符或者字符串开头的文本。

查询以特定字符或字符串结尾的记录

• 字符 ‘$’ 匹配以特定字符或者字符串结尾的文本。

用符号 “.” 来替代字符串中的任意一个字符

• 字符 ‘.’ 匹配任意一个字符。例如 ‘a.g’ 匹配包含 ‘a’ 与 ‘g’ 且两个字母之间只有一个字母的记录。

使用 “*” 和 “+” 来匹配多个字符

• 星号 ‘*‘ 匹配前面的字符任意多次，包括 0 次。加号 ’+’ 匹配前面的字符至少一次。

匹配指定字符串

• 正则表达式可以匹配指定字符串，只要这个字符串在查询文本中即可，如果要匹配多个字符串，那么多个字符串之间要使用分隔符 ‘|‘ 隔开。例如 ’on|ap' 查询包含 on 或者 ap 的字符串。

匹配指定字符串中的任意一个

• 方括号 ”[]" 指定一个字符集合，只匹配其中任何一个字符，即为所查找的文本。另外，方括号 ”[]“ 还可以指定数值集合。匹配集合 ”[456]“ 也可以写成 ”[4-6]“ 即指定集合区间。例如，”[a-z]“ 表示集合区间为 a~z 的字母， ”[0-9]“ 表示集合区间为所有数字。

匹配指定字符以外的字符

• ”[ ^ 字符集合]“ 匹配不在指定集合中的任何字符。例如 '[ ^a-e1-2]' 匹配包含字母 a ~ e 和数字 1 ~ 2 以外的字符的记录。只要包含一个不在范围里的字符即满足条件。

使用或者来指导字符串连续出现的次数

”字符串{n,}“ 表示至少匹配 n 次前面的字符；”字符串{n,m}“ 表示匹配前面的字符串不少于 n 次，不多于 m 次。