实验六 多表查询（V2.0）

                                    实验六  多表查询
【实验目的】

1. 掌握等值连接和非等值连接

2. 掌握自身连接(同一个表之间连接)

3. 掌握外连接

4. 掌握复合条件连接

 

【实验内容】

1. 基本命令：

select <目标列>

from <表1，表2>[,<表3>[,<...>]]

where <条件>

功能：从表1和表2中查询满足条件的目标列。

 

2. 实验内容

【例1】查询选修了3号课程的学生学号和姓名

 

select student.sno,snamefrom student,scwhere student.sno = sc.sno  and cno = '3'

 备注：表1和表2笛卡尔积，是连接的第一步，是第一个表的每一行与第二个表的每一行组成一次连接（元组相连）。表1和表2的笛卡尔积，有M（M=表1行数*表2行数）行，N（N=表1列数+表2列数）列。

笛卡尔积的每一行不一定有意义。例如学生表student和选课表sc的笛卡尔积20行中只有5行有意义。需要我们做筛选条件，就是表1的某一列=表二的某一列。例如student.sno = sc.sno

 

【例2】查询所有课程的先修课课程号，课程名和学分

解释：题目需要查询课程信息，附加先修课信息。

两个表的笛卡尔积，如果是表1与表1做笛卡尔积，结果是笛卡尔积的特例情况。需要对表做两个不同的别名，目标列才能够进行区分。

 

--查询每个课程的先修课学分（课程号，课程名，课程学分，先修课名和先修课学分）select 课程.cno,课程.cname,课程.ccredit,课程.cpno,先修课程.Cname,先修课程.ccreditfrom Course 课程,Course 先修课程where 课程.Cpno = 先修课程.Cno

【例3】查询全体学生的选修课程情况

解释：外连接能够实现表1与表2具有联系的元组显示的同时，可以显示某个表全部元组（左连接全部显示左边表，右连接全部显示右边表，左边表是from结构中第一个表，右边表是from结构中最后一个表）。

比较：select student.*,cno,grade

           from student,sc

          where student.sno = sc.sno

本命令只能够查询具有选修课程的学生，没有显示全部学生。（即没有选修课的学生，在结果中没有显示出来）

 

比较命令：

-- 比较如下查询,查询学生选修情况（只列出具有选课的学生）select student.*,cno,gradefrom student,scwhere student.sno = sc.sno--查询全部学生的选修课程情况，没有选修课程的也要显示出来。select student.*,cno,gradefrom student left join sc   on student.sno = sc.sno

查询效果：

 

 【例4】查询男生同学中选修了数据库课程的学生学号，姓名，课程名和成绩。（复合条件查询）

思考：
        查询表如果有2个，连接条件有几个？    1个
        如果查询表有3个，则连接条件有几个？  2个

        如果查询中有n个表，n>=2,则连接条件为 n-1个。（还可能有其他筛选条件）

例4参考答案

Select student.sno,sname,cname,gradefrom student,sc,coursewhere student.sno = sc.sno and sc.cno = course.cno and ssex = ‘男’ and cname = ‘数据库’

【练习】

1.查询姓名为刘晨的学生选修的课号和成绩。

2.查询年龄大于19的学生的学号、选修的课号和成绩。

3.查询年龄大于19（含）的学生的学号和所选修的课程数量。

提示：SC分组统计。（student中可以限定年龄）

select __________,count(cno)

from sc,student

where ________ and ________

group by ____________

 

查询年龄大于19（含）学生选修课程数多于1门课程的学生学号和课程数量。

 

 

 

4.查询3学分以上（含3分）课程被选修情况，结果有学号，课程号，学分，成绩（结果按照学号升序、成绩降序排序）。

selectf______________________

from sc ,course

where sc.cno=course.cno and ________________

order by _____________________________

 

5.查询所有学生的（所有学生包括没有选课的学生）选课情况（学号、姓名、性别、课程号、成绩）。

 

6.查询所有课程的（含有没有被选修的课程）被选修情况（课程号、课程名、学分、学号、成绩）。

select course.cno,cname,ccredit,sno,grade

from course________ sc

on ______________________

 

方法二：

select _____________________________

from sc ______________course

on ______________________________

 

7.   查询所有课程的（含有没有被选修的课程）被选修情况（学号、课程号、课程名、学分、成绩）。

 

 

【实验总结】

查询的目标列或查询条件决定需要从多个表内进行查询时，多表查询。