MySQL数据库实用教程（5）

 多表查询

一、连接查询

1、交叉连接                   

        交叉连接(CROSS JOIN)又称笛卡尔积，由第一个表的每一行与第二个表的每一行连接起来后形成的表。                                                       

SELECT * FROM 表1 CROSS JOIN 表2;
SELECT * FROM 表1, 表2;

-- 例
-- 对专业表speciality和学生表student进行交叉连接，查询专业和学生所有可能的组合。
SELECT specialityname, sname 
FROM speciality CROSS JOIN student;
/*or
SELECT specialityname, sname
FROM speciality, student;
*/

2、内连接

        内连接使用比较运算符进行表间某些字段值的比较操作，并将与连接条件相匹配的数据行组成新记录，以消除交叉连接中没有意义的数据行。

-- 使用INNER JOIN定义连接条件的显式语法结构
SELECT 目标列表达式1, 目标列表达式2,..., 目标列表达式n,
FROM table1 [INNER] JOIN table2 ON 连接条件
[WHERE 过滤条件]

-- 使用WHERE子句定义连接条件的隐式语法结构
SELECT 目标列表达式1, 目标列表达式2, …, 目标列表达式n
FROM table1, table2 
WHERE 连接条件 [AND 过滤条件]

        ① 目标列表达式：需要检索的列的名称或别名。

        ② table1、table2：进行内连接的表名。

        ③ 连接条件：连接查询中用来连接两个表的条件，其格式如下。 [<表名1.>] <列名1> <比较运算符> [<表名2.>] <列名2> 其中，比较运算符有：<、<=、=、>、>=、!=、<>。

        ④ 在使用INNER JOIN的内连接中，连接条件放在FROM子句的ON子句中，过滤条件放在WHERE子句中。

        ⑤ 在使用WHERE子句定义连接条件的内连接中，连接条件和过滤条件都放在WHERE子句中。

（1）等值连接与非等值连接（表间通过比较运算符连接）

-- 例
SELECT speciality.*, student.*    -- 对专业表speciality和学生表student进行等值连接
FROM speciality, student
WHERE speciality.specialityno = student.specialityno;

/*  or
SELECT speciality.*, student.*
FROM speciality INNER JOIN student ON speciality.specialityno = student.specialityno;
*/

（2）自然连接

自然连接在FROM子句中使用关键字NATURAL JOIN，在目标列中去除相同的字段名。

-- 例
SELECT *
FROM speciality NATURAL JOIN student;

内连接 vs 自然连接 区别：

内连接：需要明确指定连接条件。

自然连接：自动使用所有相同名称的列作为连接条件。且重复列只显示一次

（3）自连接

        将某个表与自身进行连接，称为自表连接或自身连接，简称自连接，使用自连接需要为表指定多个别名，且对所有查询字段的引用必须使用表别名限定。

-- 例  （查询选修了1201课程的成绩高于“学号为193002的学生的成绩”的学生姓名）
SELECT a.courseno, a.studentno, a.grade
FROM score a, score b   -- 这里只有一个表，但是有两个别名
WHERE a.grade>b.grade AND a.courseno='1201' AND b.courseno='1201' AND b.studentno='193002'
ORDER BY a.grade DESC;

3、外连接

        左外连接（LEFT OUTER JOIN）：结果表中除了包括满足连接条件的行外，还包括左表的所有行，当左表有记录而在右表中没有匹配记录时，右表对应列被设置为空值NULL。

-- 例 （对两表进行左外连接）
SELECT tname, courseno
FROM teacher LEFT JOIN lecture ON (teacher.teacherno=lecture.teacherno);

/*   左表（tname）都显示出来，不会因为右表没有而不显示
+----------+-----------+
| tname    | courseno  |
+----------+-----------+
| 郭逸超   | 1004       |
| 任敏     | NULL       |
| 杨静     | 1201       |
| 周章群   | 4008       |
| 黄玉杰   | 8001       |
+----------+-----------+   */

        右外连接（RIGHT OUTER JOIN）：结果表中除了包括满足连接条件的行外，还包括右表的所有行，当右表有记录而在左表中没有匹配记录时，左表对应列被设置为空值NULL。

-- 例  （对讲课表和课程表进行右外连接）
SELECT teacherno, cname
FROM lecture RIGHT JOIN course ON (course.courseno=lecture.courseno);

/*   右表（cname）都显示出来，不会因为左表没有而不显示
+--------------+------------------+
| teacherno    | cname            |
+--------------+------------------+
| 100004       | 数据库系统        |
| 120037       | 英语             |
| 400012       | 通信原理         |
| 800023       | 高等数学         |
| NULL         | 软件工程         |
+--------------+------------------+  */

 

二、子查询

1、IN子查询

        在IN子查询中，首先执行括号内的子查询，再执行父查询，子查询的结果作为父查询的查询条件。

<表达式> [ NOT ] IN (<子查询>)

-- 例  （查询选修了课程号为1004的课程的学生情况）
SELECT *
FROM student
WHERE studentno IN
(
    SELECT studentno 
    FROM score 
    WHERE courseno='1004'
);

2、比较子查询

        比较子查询是指父查询与子查询之间用比较运算符进行关联。

<表达式> { < | <= | = | > | >= | != | <> } { ALL | SOME | ANY } (<子查询>)
-- ALL指定表达式要与子查询结果集中每个值都进行比较，SOME和ANY指定表达式只需某个值满足即可

-- 例  （查询比通信工程专业任一学生年龄都大的学生）
SELECT *
FROM student
WHERE sbirthday < ALL
    (SELECT sbirthday 
     FROM student a, speciality b  
     WHERE a.specialityno=b.specialityno AND specialityname='通信工程’
    );

3、EXISTS子查询

        在EXISTS子查询中，父查询的SELECT语句返回的每一行数据都要由子查询来评价，如果EXISTS谓词指定条件为TRUE，查询结果就包含该行，否则该行被丢弃。

[ NOT ] EXISTS ( <子查询> )

-- 例  （查询选修4008课程的学生姓名）
SELECT sname AS 姓名
FROM student
WHERE EXISTS 
    (SELECT *
     FROM score
     WHERE score.studentno=student.studentno AND courseno='4008’
    );

 

三、联合查询

        联合查询将两个或多个SQL语句的查询结果集合并起来，利用联合进行查询处理以完成特定的任务，使用UNION关键字，将两个或多个SQL查询语句结合成一个单独SQL查询语句。

<SELECT查询语句1>
{UNION | UNION ALL }
<SELECT查询语句2>

        UNION语句将第一个查询中的所有行与第二个查询的所有行相加。不使用关键字ALL,消除重复行，所有返回行都是唯一的。使用关键字ALL，不去掉重复记录，也不对结果自动排序。

        在联合查询中，需要遵循的规则如下。

        1、在构成联合查询的各个单独的查询中，列数和列的顺序必须匹配，数据类型必须兼容。

        2、在ORDER BY子句和LIMIT子句，必须置于最后一条SELECT语句之后。

-- 例  （查询性别为女且专业为网络工程的学生）
SELECT studentno, sname, ssex
FROM student
WHERE ssex='女'
UNION
SELECT a.studentno, a.sname, a.ssex 
FROM student a, speciality b
WHERE a.specialityno=b.specialityno AND specialityname='网络工程';