数据库系统概论 连接数据库

目录

1. 等值与非等值连接查询

1.1 等值连接

 1.2 自然连接

2. 自身连接

3. 外连接

4. 多表连接

5. 实验案例

5.1 连接查询案例

5.2 自身连接案例

5.3 外连接案例

5.4 聚集函数案例

5.5 分组统计查询

---

连接查询：同时涉及两个以上表的查询

连接条件（连接谓词）：用来连接两个表的条件

一般格式：[<表名>.]<列名1><比较运算符> [<表名2>.]<列名2>

也就是说对两个表进行笛卡儿积，把满足条件的元组选出来

连接谓词中的列名称称为连接字段，各连接字段类型必须是可比的，名字不必相同

   

1. 等值与非等值连接查询

1.1 等值连接

连接运算符为“=”

--查询每个学生及其选修课程的情况
--方法一：
SELECT Student.*,SC.*
FROM Student,SC
WHERE Student.Sno=SC.Sno;

--方法二:
SELECT Student.*,SC.*
FROM Student INNER JOIN SC ON  Student.Sno=SC.Sno;

 1.2 自然连接

 把重复的属性列去掉

两表中都有同名的列，目标列要表达清楚用哪一列（表名.列名）

--查询每个学生及其选修课程的情况
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student,SC
WHERE Student.Sno=SC.Sno;

--查询选修2号课程且成绩在90分以上的所有学生的学号和姓名
SELECT Student.Sno,Sname
FROM Student,SC
WHERE Student.Sno=SC.Sno AND SC.Cno='2' AND SC.Grade>90;

2. 自身连接

一个表与其自己进行连接

• 需要给表起别名以示区别
• 由于所有属性名都是同名属性，因此必须使用别名前缀 

--查询每一门课的间接先修课（即先修课的先修课）
SELECT FIRST.Cpno,SECOND.Cno
FROM COURSE FIRST,COURSE SECOND
WHERE FIRST.Cpno=SECOND.Cno;

3. 外连接

将主体表中不满足连接条件的元组一并输出（左外连接，右外连接）

--查询每个学生及其选修课程的情况
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student LEFT OUTER JOIN SC ON (Student.Sno=SC.Sno);
--左外连接，以Student表为准

左外连接： LEFT OUTER

右外连接：RIGHT OUTER

全外连接：FULL OUTER

4. 多表连接

两个以上的表进行连接

--查询每个学生的学号、姓名、选修的课程名及成绩
SELECT Student.Sno,Sname,Cname,Grade 
FROMStudent,SC,Course 
WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno;

--先分析涉及几张表

5. 实验案例

5.1 连接查询案例

• 查询所有选课学生的学号、姓名、选课名称及成绩。

SELECT SC.Sno,Student.Sname,Course.Cname,SC.Grade
FROM SC,Student,Course
WHERE SC.Sno=Student.Sno AND SC.Cno=Course.Cno

• 查询每门课程的课程号、任课教师姓名及选课人数，按课程号升序显示结 

SELECT TEACHING.Cno,TEACHER.Tname, TEACHING.Snum
FROM TEACHER,TEACHING
WHERE TEACHING.Tno=TEACHER.Tno
ORDER BY TEACHING.Cno;

• 查询籍贯为山西或河北，成绩为90分以上的学生的姓名、籍贯、课程号和成绩。

SELECT Student.Sname,Student.Bplace,SC.Cno,SC.Grade
FROM Student,SC
WHERE Student.Sno=SC.Sno AND Bplace IN('山西','河北') AND Grade>=90;

5.2 自身连接案例

• 查询所有比“王志伟”年龄大的教师的姓名、年龄，以及王志伟的年龄。

SELECT T2.Tname,T2.Age AS 教师的年龄,T1.Age AS 王志伟的年龄
FROM TEACHER T1,TEACHER T2
WHERE T1.Tname='王志伟' 
,AND T2.Tname<>'王志伟' 
,AND T2.Age>T1.Age

5.3 外连接案例

• 查询所有学生的学号、姓名、选课名称及成绩（没有选课的学生的选课信息显示为空）

SELECT Student.Sno,Student.Sname,COURSE.CNAME,SC.Grade
FROM Student LEFT OUTER JOIN 
(SC INNER JOIN COURSE ON SC.CNO=COURSE.CNO)
ON Student.Sno=SC.Sno;

5.4 聚集函数案例

• 查询男生人数

SELECT COUNT(*) AS 男生人数
FROM STUDENT
WHERE SEX='男'

• 查询选修了“数据库”课程的学生人数、平均成绩和最高成绩

查询选修了“数据库”课程的学生人数、平均成绩和最高成绩
SELECT COURSE.CNO,COURSE.CNAME
,COUNT(*) AS 学生人数
,AVG(SC.GRADE) AS 平均成绩
,MAX(SC.GRADE) AS 最高成绩
FROM COURSE,SC
WHERE COURSE.CNAME='数据库' AND SC.CNO=COURSE.CNO
GROUP BY COURSE.CNO,COURSE.CNAME

 

SELECT SC.*,GRADE*0.7+平时成绩 AS 最终成绩
FROM SC

5.5 分组统计查询

查询选修了两门以上课程的男学生的姓名、选课门数、平均成绩

SELECT STUDENT.SNO
,STUDENT.SNAME
,COUNT(*) AS 选课门数
,AVG(SC.GRADE) AS 平均成绩
FROM STUDENT,SC
WHERE STUDENT.SNO=SC.SNO AND STUDENT.SEX='男'
GROUP BY STUDENT.SNO,STUDENT.SNAME
HAVING COUNT(*)>=2