MySQl心得4--4--数据库查询4--子查询

1.子查询

在查询条件中，可以使用另一个查询的结果作为条件的一部分，例如，判定列值是否与某个查询的结果集中的值相等，作为查询条件一部分的查询称为子查询。SQL标准允许SELECT多层嵌套使用，用来表示复杂的查询。子查询除了可以用在SELECT语句中，还可以用在INSERT、UPDATE及DELETE语句中。子查询通常与IN、EXIST谓词及比较运算符结合使用。

2.IN子查询

IN子查询用于进行一个给定值是否在子查询结果集中的判断，格式为： expression [ NOT ] IN  (subquery )

其中，subquery是子查询。当表达式expression与子查询subquery的结果表中的某个值相等时，IN谓词返回TRUE，否则返回FALSE；若使用了NOT，则返回的值刚好相反。

例:查找在XSCJ数据库中选修了课程号为206的课程的学生的姓名、学号。

SELECT 姓名,学号 FROM XS WHERE 学号 IN

        (SELECT 学号  FROM XS_KC 

        WHERE课程号 = '206' );

说明：在执行包含子查询的SELECT语句时，系统先执行子查询，产生一个结果表，再执行查询。本例中，先执行子查询：

SELECT 学号 FROM  XS_KC WHERE 课程号='206';

得到一个只含有学号列的表，XS_KC中的每个课程名列值为206的行在结果表中都有一行。再执行外查询，若XS表中某行的学号列值等于子查询结果表中的任一个值，则该行就被选择。

注意：IN子查询只能返回一列数据。对于较复杂的查询，可以使用嵌套的子查询。

例:查找未选修离散数学的学生的姓名、学号、专业名。

SELECT 姓名,学号,专业名 FROM XS

      WHERE 学号 NOT IN(

        SELECT学号 FROM XS_KC

          WHERE课程号 IN

             (SELECT 课程号 FROM KC

               WHERE  课程名 ='离散数学'));

3.比较子查询

这种子查询可以认为是IN子查询的扩展，它使表达式的值与子查询的结果进行比较运算，格式为：  expression { < | <= |= | > | >= | != | <> } { ALL | SOME | ANY } ( subquery )

其中，expression为要进行比较的表达式，subquery是子查询。ALL、SOME和ANY说明对比较运算的限制。

如果子查询的结果集只返回一行数据时，可以通过比较运算符直接比较。

ALL指定表达式要与子查询结果集中的每个值都进行比较，当表达式与每个值都满足比较的关系时，才返回TRUE，否则返回FALSE；

SOME或ANY是同义词，表示表达式只要与子查询结果集中的某个值满足比较的关系时，就返回TRUE，否则返回FALSE。

例: 查找XS表中比所有计算机系的学生年龄都大的学生学号、姓名、专业名、出生日期。

SELECT 学号, 姓名, 专业名, 出生日期  FROM XS

     WHERE  出生日期 <ALL (

        SELECT出生日期  FROMXS

          WHERE专业名 ='计算机' );

4. EXISTS子查询

EXISTS谓词用于测试子查询的结果是否为空表，若子查询的结果集不为空，则EXISTS返回TRUE，否则返回FALSE。EXISTS还可与NOT结合使用，即NOTEXISTS，其返回值与EXIST刚好相反。格式为：

[ NOT ] EXISTS ( subquery )

例: 查找选修206号课程的学生姓名。

SELECT 姓名 FROM XS  WHERE EXISTS (

          SELECT * FROM XS_KC

             WHERE 学号 = XS.学号 AND 课程号 = '206' );

本例与前面的子查询例子不同点是:前面的例子中，内层查询只处理一次，得到一个结果集，再依次处理外层查询；而本例的内层查询要处理多次，因为内层查询与XS.学号有关，外层查询中XS表的不同行有不同的学号值。这类子查询称为相关子查询，因为子查询的条件依赖于外层查询中的某些值。其处理过程是：首先查找外层查询中XS表的第一行，根据该行的学号列值处理内层查询，若结果不为空，则WHERE条件就为真，就把该行的姓名值取出作为结果集的一行；然后再找XS表的第2、3、…行，重复上述处理过程直到XS表的所有行都查找完为止。

说明：由于没有人选了全部课程，所以结果为空。

5.  MySQL区分了4种类型的子查询：返回一个表的子查询是表子查询；返回带有一个或多个值的一行的子查询是行子查询；返回一行或多行，但每行上只有一个值的是列子查询；只返回一个值的是标量子查询。从定义上讲，每个标量子查询都是一个列子查询和行子查询。上面介绍的子查询都属于列子查询。另外，子查询还可以用在SELECT语句的其他子句中。表子查询可以用在FROM子句中，但必须为子查询产生的中间表定义一个别名。

6. SELECT关键字后面也可以定义子查询。

例:从XS表中查找所有女学生的姓名、学号，以及与081101号学生的年龄差距。

SELECT 学号, 姓名, YEAR(出生日期)-YEAR(

         ( SELECT 出生日期  FROM XS

               WHERE 学号='081101'  )  )  AS 年龄差距

                FROM XS  WHERE 性别='0';

说明：本例中子查询返回值中只有一个值，所以这是一个标量子查询。YEAR函数用于取出DATE类型数据的年份。

在WHERE子句中还可以将一行数据与行子查询中的结果通过比较运算符进行比较。

例: 查找与081101号学生性别相同、总学分相同的学生学号和姓名。

SELECT 学号,姓名 FROM XS

   WHERE  (性别,总学分)=( SELECT 性别,总学分

        FROM XS  WHERE 学号='081101' );

7.使用union可以把来自许多SELECT语句的结果组合到一个结果集合中。语法格式如下：

SELECT ...

UNION [ALL | DISTINCT]

SELECT ...

[UNION [ALL | DISTINCT]

SELECT ...]

说明：

SELECT语句为常规的选择语句，但是还必须遵守以下规则：

●   列于每个SELECT语句的对应位置的被选择的列应具有相同的数目和类型。例如，被第一个语句选择的第一列应和被其他语句选择的第一列具有相同的类型。

●   只有最后一个SELECT语句可以使用INTO OUTFILE。

●   HIGH_PRIORITY不能与作为UNION一部分的SELECT语句同时使用。

●  ORDER BY和LIMIT子句只能在整个语句最后指定，同时还应对单个的SELECT语句加圆括号。排序和限制行数对整个最终结果起作用。

8.  使用UNION的时候，在第一个SELECT语句中被使用的列名称被用于结果的列名称。MySQL自动从最终结果中去除重复行，所以附加的DISTINCT是多余的，但根据SQL标准，在语法上允许采用。要得到所有匹配的行，则可以指定关键字ALL。

例:查找学号为081101和学号为081210的两位同学的信息。

SELECT 学号, 姓名, 专业名, 性别, 出生日期, 总学分

    FROM XS WHERE 学号= '081101'   UNION

    SELECT 学号, 姓名, 专业名, 性别, 出生日期, 总学分

    FROM XS  WHERE学号= '081210';

9.handler语句

select语句通常用来返回行的一个集合；MySQL还支持另外一个查询数据库的语句：handler语句，该语句能够一行一行的浏览表中的数据，他并属于SQL标准，这是MySQL的专用语句，该语句只适用于MyISAM和InnoDB表。

使用该语句要先使用handler open语句打开一个表，再使用handler read语句浏览打开表的行，浏览完后使用handler close语句关闭已打开的表

1). 打开一个表

可以使用handler open语句打开一个表。

语法格式为： HANDLER 表名 OPEN [ AS 新表名 ]

说明：若打开表时使用别名，则在其他进一步访问表的语句也都要使用别名。

2). 浏览表中的行

handler read语句用于浏览一个已经打开的表的数据行。

语法格式为： HANDLER表名READ { FIRST |NEXT }

               [ WHERE where_condition ] [LIMIT ... ]

说明：

●   FIRST | NEXT：这两个关键字是HANDLER语句的读取声明，FIRST表示读取第一行，NEXT表示读取下一行。

●  WHERE子句：如果想返回符合特定条件的行，可以加一条WHERE子句，这里的WHERE子句和SELECT语句中的WHERE子句具有相同的功能，但是这里的WHERE子句中不能包含子查询、系统内置函数、BETWEEN、LIKE和IN运算符。

●   LIMIT子句：若不使用LIMIT子句，HANDLER语句只取表中的一行数据。若要读取多行数据，则要添加LIMIT子句。这里的LIMIT子句和SELECT语句中的LIMIT子句不同。SLECT语句中的LIMIT子句用来限制结果中的行的总数，而这里的LIMIT子句用来指定HANDLER语句所能获得的行数。

10. 由于没有其他的声明，在读取一行数据的时候行的顺序是由MySQL决定的。如果要按某个顺序来显示，可以通过在HANDLER READ语句中指定索引来实现。

语法格式为：

（1）HANDLER 表名 READ 索引名 { = | >= | <= | < } (value1,value2,...)  [ WHERE 条件 ] [LIMIT... ]

（2）HANDLER表名READ索引名{ FIRST | NEXT | PREV | LAST }

[ WHERE条件  ] [LIMIT ... ]

说明：

第一种方式是使用比较运算符为索引指定一个值，并从符合该条件的一行数据开始读取表。如果是多列索引，则值为多个值的组合，中间用逗号隔开。value1、value2是为索引指定的值。

第二种方式是使用关键字读取行，FIRST表示第一行，NEXT表示下一行，prev表示上一行，last表示最后一行。

11. 关闭打开的表

行读取完后必须使用handler close语句来关闭表。

语法格式为： HANDLER 表名close

例： 一行一行地浏览KC表中的满足要求的内容，要求第一行为学分大于4的第一行数据。

首先打开表：        USEXSCJ；(指定库，打开xskj库)

          handler KC open;（打开kc表）

读取满足条件的第一行：handler KC readFIRST  WHERE 学分>4;

读取下一行：          handler KC read NEXT;

关闭该表：            handler KC close;