本文共 5820 字，大约阅读时间需要 19 分钟。

数据库联合查询详解

本文将详细介绍数据库中的联合查询方法，包括笛卡尔积、内联、外联、全联以及自联等多种常见方式，并结合实例说明如何使用这些方法进行多表查询。

1. 联合查询概述

联合查询是数据库中处理多表数据查询的重要操作方式，常见的有以下几种方法：

1.1 笛卡尔积

笛卡尔积是一种最基本的表连接方式，它会将两个或多个表的所有行组合起来，生成一个新的结果集。由于这种方式会产生所有可能的组合，结果集的规模可能会非常大。

示例：

select * from class_A, course

1.2 内联

内联是将两个表的记录进行交叉连接，但仅显示两个表中字段值相等的记录。这种方式通常用于表之间存在一对一关系的情况。

内联方法一：

select * from class_A, course where class_A.course = course.cname

内联方法二：

select * from class_A inner join course on class_A.course = course.cname

1.3 外联

外联又分为左联和右联两种方式。左联以左表为主，右表为辅，左表的记录较多时，右表缺少的字段会显示为空值（null）；若左表的记录较少，多余的字段则不会显示。

左联示例：

select * from class_A left join course on class_A.course = course.cname

右联示例：

select * from class_A right join course on class_A.course = course.cname

1.4 全联

全联会显示两个表中所有记录，不存在关联关系的字段则显示为空值。

全联示例：

select * from class_A full join course on class_A.course = course.cname

1.5 自联

自联是将两个表以自身方式连接，通常用于需要两个表之间存在一对一关系的情况。

自联示例：

select * from student_info, old_student_info where student_info.student_id = old_student_info.student_id

2. 其他SQL操作示例

2.1 多表查询

查询多个表的所有记录：

select * from class_info, course_info, result_info, student_info

2.2 等值连接

通过等值连接两个表：

select class_info.*, student_info.* from class_info, student_info where class_info.class_no = student_info.class_no

2.3 自然连接

通过自然连接两个表：

select student_info.class_no, class_info.class_name, director, profession,        student_info.student_id, student_info.student_name, student_info.student_sex,        born_date, tele_number, ru_date, address, comment from class_info, student_info where student_info.class_no = class_info.class_no

2.4 自身连接

查询不同课程成绩相同的学生信息：

select a.student_id, a.course_no, a.result from result_info a, result_info b where a.result = b.result and a.course_no != b.course_no

2.5 子查询

查询小强所在的班级名称：

select class_info.class_name from student_info, class_info where student_info.class_no = class_info.class_no and student_name like '%小强%'

查询小强的班级名称：

select class_name from class_info where class_no = (    select class_no from student_info     where student_name like '%小强%')

2.6 使用IN、NOT IN子查询

查询特定班级的学生信息：

select student_id, student_name, student_sex from student_info where class_no in (2000001, 2000002)

查询不是2000001或2000002班的学生信息：

select student_id, student_name, student_sex from student_info where class_no not in (2000001, 2000002)

2.7 排序与分组

按年龄排序学生信息：

select student_name, student_sex, born_date from student_info where (year(getdate()) - year(born_date)) != 2order by (year(getdate()) - year(born_date)) desc, student_name

统计班级人数：

select class_no, count(student_id) as 班级人数 from student_info group by class_no

统计男女生人数及平均年龄：

select count(student_id) as 总人数,        avg(year(getdate()) - year(born_date)) as 平均年龄 from student_info group by student_sex

筛选人数大于2的班级：

select class_no, count(student_id) as 班级人数 from student_info group by class_no having count(student_id) > 2

2.8 使用HAVING子句

筛选人数大于2的班级：

select class_no, count(student_id) as 班级人数 from student_info group by class_no having count(student_id) > 2

与WHERE子句的区别：

• WHERE子句用于筛选记录。
• HAVING子句用于筛选组的结果。

2.9 基于多表的联合查询

查询班级信息、课程信息、成绩信息和学生信息：

select * from class_info, course_info, result_info, student_info

通过等值连接查询班级信息和学生信息：

select class_info.*, student_info.* from class_info, student_info where class_info.class_no = student_info.class_no

2.10 子查询与连接的区别

• 连接可以同时查询多个表。
• 子查询的结果只能来自一个表。

2.11 使用IN、NOT IN的子查询

查询2000001和2000002班的学生信息：

select student_id, student_name, student_sex from student_info where class_no in (2000001, 2000002)

查询不是2000001或2000002班的学生信息：

select student_id, student_name, student_sex from student_info where class_no not in (2000001, 2000002)

2.12 使用EXISTS与NOT EXISTS子查询

判断学生是否存在：

select * from student_info where exists(    select * from result_info     where student_info.student_id = result_info.student_id)

判断学生是否不存在：

select * from student_info where not exists(    select * from result_info     where student_info.student_id = result_info.student_id)

2.13 使用UNION运算符

组合多个表的结果：

select * from table1 union select * from table2

注意： 需要注意UNION运算符的使用，确保结果集具有唯一性。

3. 其他SQL示例

3.1 列出2014年以后入学的学生信息：

select * from student_info where year(ru_date) > '2015'

3.2 列出广州的男同学信息：

select student_name, tele_number, address from student_info where student_sex = '男' and address like '广州%'

3.3 列出电话开头是136的学生信息：

select * from student_info where tele_number like '136%'

3.4 列出所有不是2岁的学生信息：

select student_name, student_sex, born_date from student_info where (year(getdate()) - year(born_date)) != 2

3.5 列出2-3岁之间的学生信息：

select student_name, student_sex, born_date from student_info where (year(getdate()) - year(born_date)) between 2 and 3

3.6 判断表达式是否为空值：

select * from student_info where comment is null

3.7 判断表达式不为空值：

select * from student_info where comment is not null

3.8 使用IN子查询：

查询特定班级的学生信息：

select student_id, student_name, student_sex from student_info where class_no in (2000001, 2000002)

3.9 使用NOT IN子查询：

查询不是2000001和2000002班的学生信息：

select student_id, student_name, student_sex from student_info where class_no not in (2000001, 2000002)

3.10 基于多表的查询：

查询学生的基本信息和家庭住址：

select student_name, '家庭住址', address from student_info

3.11 使用GROUP BY与HAVING：

统计各个班级的学生人数：

select class_no, count(student_id) as 班级人数 from student_info group by class_no

统计男女生人数及平均年龄：

select count(student_id) as 总人数,        avg(year(getdate()) - year(born_date)) as 平均年龄 from student_info group by student_sex

3.12 排序查询：

按年龄排序学生信息：

select student_name, student_sex, born_date from student_info where (year(getdate()) - year(born_date)) != 2order by (year(getdate()) - year(born_date)) desc, student_name

根据入学时间倒序排列：

select * from student_info where year(ru_date) > '2015'order by ru_date desc, student_id

3.13 使用DISTINCT去重：

去重复记录：

select distinct class_no from student_info

3.14 列出女性学生的姓名及家庭住址：

select student_name, '家庭住址' as 描述, address from student_info where student_sex = '女'

4. 最后总结

通过以上方法，我们可以在数据库中灵活地查询多个表的数据，通过合理运用笛卡尔积、内联、外联、全联等方式，可以高效地完成多表查询任务。掌握这些技能对于数据库管理和数据分析工作具有重要意义。

转载地址：http://zzffk.baihongyu.com/