项目四：MySQL数据表的检索总结

数据库管理系统的一个最重要的功能就是数据查询，数据查询不仅是简单查询数据库中存储的数据，还更要对数据进行筛选，以及确定数据以怎样的格式显示。MySQL提供了功能强大、灵活的语句来实现这样操作，本项目将介绍如何使用SELECT语句查询数据表中的一列或多列数据、连接查询，子查询，以及使用Navicat生成查询等。

一、基本查询语句

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>]

]

SELECT[字段1，字段2...，字段n]

FROM[表或视图]

WHERE[查询条件];

以上语句含义：

1、{* | <字段列表>}：包含星号通配符选择字段列表，表示查询的字段，其中字段列至少包含一个字段名称，如果要查询多个字段，多个字段之间用逗号隔开，最后一个字段不用加逗号。

2、FROM<表1>,<表2>...：表1和表2查询数据的来源，可以是单个或多个。

3、WHERE<表达式>：可选项，如果选择该项，将限定查询行必须满足的查询条件。

4、[GROUP BY<字段>]：该子句告诉MySQL如何显示查询出来的数据，并按照指定的字段分组。

5、[ORDER BY字段>]：该子句告诉MySQL该怎样的顺序显示查询出来的数据，可以进行的排序有升序（ASC）、降序（DESC）。

6、[LIMIT[<offset>,]<row count>]：该子句告诉MySQL每次显示查询出来的数据条数数。

1、检索所有列

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

SELECT * FROM 表名;

（2）在select语句中指定所有字段。根据前面select语句的格式，select关键字后面的字段名为将要查找的数据，因此可以将表中所有字段的名称跟在select子句后面。有时候，由于表中的字段比较多，不一定记得所有字段的名称，因此该方法会很不方便，不建议使用，其语法格式为：

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

2、检索指定列

（1）检索单个字段。查询表中的某一个字段，语法格式为：

SELECT 字段名 FROM 表名;

（2）检索多个字段。要想从数据表中检索多个字段的数据，仍然使用相同的select语句，只需在关键字select后面指定要查找的多个字段的名称，不同字段名称之间用逗号（，）隔开，最后一个字段后面不需要加逗号，语法格式为：

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

有些情况，显示的字段名会很长，不能直观，可以指定字段别名替换字段或表达式，为字段定义别名的基本语法格式为：

字段名 [AS] 字段别名

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

注意，MySQL中的SQL语句时不区分大小写的，因此SELECT和select的作用是相同。

二、查询指定记录

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

SELECT 字段名1，字段名2，...，字符名n FROM 表名 WHERE 条件;

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

操作符	说明
=	等于
<> , ! =	不等于
<	小于
<=	小于或等于
>	大于
>=	大于或等于
BETWEEN	在指定的两个值之间

1、带IN关键字的查询

IN操作符用来指定条件范围，范围每一个条件都可以进行匹配。IN取一组由逗号分隔、括在圆括号的合法值，语法格式为：

SELECT 字段名1，字段名2，...，字段名n FROM 表名 WHERE 字段名 IN（值1，值2，...）

2、带BETWEEN AND的范围查询

BETWEEN AND用来查询某个范围内的值，该操作符需要两个参数，即范围的开始值和结束值，若字段值满足指定的范围查询条件，则这些记录被返回，BETWEEN匹配范围中的所有值，包括开始值和结束值，语法格式为： 

SELECT字段名1,字段名2,···.字段名 n FROM 表名 WHERE字段名 BETWEEN值1AND值2

BETWEEN AND操作符前可以加关键字NOT,表示指定范围之外的值，若字段值不满足指定范围内的值，则这些记录被返回。

3、带LIKE的字符匹配查询

前面介绍的所有操作符都是针对已知值进行过滤。不管是匹配一个值还是多个值，检验大于还是小于已知值，或者检查某个范围的值，其共同点是过滤使用的值都是已知的，如果要查找所有的姓“刘”的员工信息，该如何查找呢？简单的比较操作在这里已经行不通了，需要使用通配符进行匹配查找，通过创建查找模式对表中的数据进行比较。执行这个任务的关键字是LIKE,语法格式为：

SELECT字段名1,字段名2,···,字段名n FROM表名 WHERE字段名 LIKE条件；

通配符是一种在SQL的WHERE条件子句中拥有特殊意思的字符，SQL语句中支持多种通配符，可以和LIKE一起使用的通配符有“％”和“＿”。

1、百分号通配符（％）。通配符“％”匹配任意长度的字符，甚至包括零字符，可以在搜索模式中的任意位置使用，并且可以使用多个通配符。

2、下划线通配符（＿）。通配符“＿”的用法与“％”相同，区别是“％”可以匹配多个字符，而“＿”只能匹配任意单个字符。若需要匹配多个字符，则使用相同个数的“＿”。

4、查询空值

数据表创建时，设计者可以指定某列中是否可以包含空值（NULL).空值不同于0,也不同于空字符串。空值一般表示数据未知、不适用或在以后添加数据。在SELECT语句中使用IS NULL子句，可以查询某字段内容为空的记录，语法格式为：

SELECT字段名1,字段名2,···,字段名n FROM表名 WHERE字段名 IS NULL

5、带AND的多条件查询

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

SELECT字段名1,字段名2,字段名n FROM表名WHERE条件1AND条件2···:

6、带OR的多条件查询

与AND相反，在WHERE声明中使用OR操作符，表示只需要满足其中一个条件的记录即可返回。OR也可以连接两个甚至多个查询条件，多个条件表达式之间用OR分开，语法格式为：

SELECT字段名1,字段名2,···,字段名n FROM表名 WHERE条件1OR条件2...

OR可以和AND一起使用，但在使用时要注意两者的优先级，由于AND的优先级高于OR,因此先对AND两边的操作数进行操作，再与OR中的操作数结合。

7、查询不同的值

SELECT语句返回所有匹配的行，如果不希望每个值每次都出现，该怎么办呢？例如，如果想检索“员工表”所有部门的名称，按以往的方法，部门字段中所有行的数据都会显示出来，有些部门名称会有雷同。因此，可以使用DISTINCT关键字，返回数据的不同值，语法格式为：

SELECT DISTINCT字段名1,字段名2,···,字段名n FROM表名

注意，DISTINCT关键字作用于所有的列，不仅仅是跟在其后的那一列。

8、显示前N行

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

SELECT DISTINCT 字段名1,字段名2,···,字段名n FROM表名 LIMIT[位置偏移量，］行数

注意，第一个被检索的行是第0行，而不是第1行。

三、查询结果排序

从表中查询出来的数据是无序的，或者其排列顺序不是用户所期望的。为了使查询结果满足用户的要求，可以使用ORDER BY语句对查询结果进行排序，其语法格式为：

SELECT字段名1,字段名2,···,字段名n FROM表名 ORDER BY字段名1[ASC|DESC],字段名2[ASC|DESC]···

在上面的语法格式中，指定的字段名1、字段名2等是对查询结果排序的依据。参数ASC表示按升序进行排序，DESC表示按降序进行排序。默认情况下，按照ASC方式进行排序。

四、查询分组与汇总

1、聚集函数

（1）AVG()函数。AVG() 函数通过计算返回的行数和每一行数据的和，求得指定列数据的平均值。AVG()函数可以用来返回所有列的平均值，也可以用来返回特定列或行的平均值。

注意，AVG()函数只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出。为了获得多个列的平均值，必须使用多个AVG()函数。AVG()函数忽略列值为NULL的行。

（2)COUNT()函数。COUNT()函数统计数据表中包含的记录行的总数，或者根据查询结果返回列中包含的数据行数，其使用方法有以下两种。

①COUNT(*):计算表中总的行数，不管某列是否有数值或为空值。

②COUNT(字段名）：计算指定列下总的行数，计算时将忽略空值的行。注意，指定列的值为空的行被COUNT()函数忽略，但是如果不指定列，而在COUNT()函数中使用星号（＊），则所有记录都不忽略。

（3)MAX()函数。MAX()函数返回指定列中的最大值，而且要求指定列名。注意，MAX()函数除了用来找出最大的列值或日期值之外，还可以返回任意列中的最大值，包括返回字符类型的最大值。在对字符类型数据进行比较时，按照字符的ASCII码值大小进行比较，从a到z,a的最小，z的最大。在比较时，先比较第一个字符，如果相等，继续比较下一个字符，一直到两个字符不相等或字符结束为止。例如，“bef”与“bcg”比较时，“bef”为最大值。

（4)MIN()函数。MIN()函数返回指定列中的最小值，且要求指定列名。MIN()函数与MAX()函数类似，不仅适用于查找数值类型，也应用于字符类型。

（5)SUM()函数。SUM()函数用于求总和，返回指定列值的总和（总计）。注意，SUM()函数在计算时，忽略列值为NULL的行。

2、分组查询

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

［GROUP BY 字段名］［HAVING<条件表达式＞］

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

（1)创建分组。分组是使用SELECT语句的 GROUP BY子句建立的。GROUPBY关键字通常和聚集函数一起使用，如MAX()、MINO、COUNT()、SUM()、AVG().例如，要返回员工表中每个部门的员工人数，这时就要在分组过程中用到COUNT()函数，把数据分为多个逻辑组，并对每个组进行集合计算。

在使用GROUP BY子句时，需要知道以下重要的规定。

①GROUP BY子句可以包含任意数目的列，因而可以对分组进行嵌套，更细致地进行数据分组

②如果在GROUP BY子句中嵌套了分组，数据将在最后指定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（但不能从个别的列取回数据）。

③GROUP BY子句中列出的每一列都必须是检索列或有效的表达式（不能是聚集函数）。如果在SELECT 中使用表达式，就必须在GROUP子句中指定相同的表达式，不能使用别名。
④大多数SQL实现不允许GROUP BY列带有长度可变的数据类型（如文本或备注型字段）。

⑤除聚集函数外，SELECT语句中的每一列都必须在GROUP BY子句中给出。

⑥如果分组列中包含具有NULL值的行，那么NULL将作为一个分组返回，如果列中有多行NULL值，它们将分为一组。

⑦GROUP BY子句必须出现在WHERE子句之后，ORDERBY子句之前。

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

HAVING关键字与WHERE关键字都是用来过滤数据的，HAVING支持所有WHERE操作符。两者的区别在于，HAVING在数据分组之后进行过滤来选择分组，而WHERE在分组之前选择记录。另外，WHERE排除的记录不包括在分组中。（3)分组中使用WITH ROLLUP.使用WITH ROLLUP关键字之后，在所有查询出的分组记录后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。

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

（5)GROUP BY和ORDER BY一起使用。某些情况下需要对分组进行排序，在前面的介绍中，ORDERBY用来对查询的记录进行排序，如果和GROUP BY一起使用就可以完成对分组的排序。

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

五、创建多表连接查询

1、交叉连接查询

交叉连接查询返回的结果是被连接的两个表中所有数据行的笛卡儿积，也就是返回第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。例如，员工表中有13个员工，销售表中有11条销售记录，那么交叉连接的结果就有13x11=143条数据，其语法格式为：

SELECT *FROM 表名1 CROSS JOIN表名2

用户也可以使用WHERE子句来实现多表连接查询，其语法格式为：

SELECT查询字段FROM表名1,表名2,···WHERE表名1.关系字段＝表名2.关系字段［AND连接条件···];

2、内连接查询

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

SELECT査询字段FROM表名1[INNER]JOIN 表名2ON表名1.关系字段＝表名2.关系字段；
 

3、外连接查询

（1）LEFT JOIN(左连接）。LEFT JOIN(左连接）返回包括左表中的所有记录和右表中连接字段相等的记录。其语法格式为：

SELECT查询字段 FROM表名 1 LEFT OUTER JOIN 表名2ON表名1.关系字段＝表名2.关系字段；

（2）RIGHT JOIN(右连接）。RIGHT JOIN(右连接）返回包括右表中的所有记录和左表中连接字段相等的记录。其语法格式为：

SELECT查询字段 FROM表名1 RIGHT OUTER JOIN 表名2 ON表名1.关系字段＝表名2.关系字段；

4、复合条件连接查询

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

六、创建子查询

1、带IN关键字的子查询

使用IN关键字进行子查询时，内层查询语句仅仅返回一个数据列，这个数据列中的值将提供给外层查询语句进行比较操作。SELECT语句中可以使用NOTIN关键字，其作用与IN相反，语法格式为：

SELECT 查询字段 FROM 表名 WHERE 字段名［NOT]IN(SELECT语句）

2、带EXISTS关键字的子查询

EXISTS关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行其语法格式为：

SELECT 查询字段 FROM 表名 WHERE[NOT]EXISTS(SELECT语句）

3、带ANY、SOME关键字的子查询

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

SELECT查询字段 FROM表名 WHERE 字段名 比较运算符 ANY|SOME(SELECT语句）

4、带ALL关键字的子查询

ALL关键字与ANY和SOME不同，使用ALL时需要同时满足所有内层查询的条件。ALL关键字必须接在一个比较操作符后面，表示与子查询返回的所有值比较都为TRUE，则返回TRUE。其语法格式为：

SELECT 查询字段 FROM 表名 WHERE 字段名 比较运算符 ALL （SELECT 语句）

七、创建多表联合查询

1、使用UNION

使用UNION很简单，所要做的只是给出每条SELECT语句，在各条语句之间放上关键字UNION,其语法格式为：

SELECT查询字段FROM表名

UNION [ALL]

SELECT 查询字段FROM表名；

2、UNION 规则

UNION非常容易使用，但在应用过程中需要注意以下几条规则。

①UNION必须由两条或两条以上的SELECT 语句组成，语句之间用关键字UNION分隔（因此，如果组合4条SELECT语句，将要使用3个UNION关键字）。

②UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过，各个列不需要以相同的次序列出）。

③列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐式转换的类型（如不同的数值类型或不同的日期类型）。如果遵守了这些基本规则或限制，那么可以将UNION用于任何数据检索操作。

3、包含或取消重复的行UNION

从查询结果集中自动去除重复的行，换句话说，它的行为与一条SELECT语句中使用多个WHERE子句条件一样。如果想返回所有的匹配行，要使用UNIONALL而不是UNION.

4、对联合查询结果排序

SELECT语句可以使用ORDER BY子句排序。在使用UNION联合查询时，只能使用后条 ORDER BY子句，它必须位于最后一条SELECT语句之后。对于结果集，不存在用一种方式排序一部分，而又用另一种方式排序另一部分的情况，因此不允许使用多条 ORDER BY子句。