mysql多表查询实验,MySQL数据库实验：任务四 数据库的多表查询设计

任务四 数据库的多表查询设计

文章目录任务四 数据库的多表查询设计【实训目的与要求】【实训原理】【实训步骤】一、连接查询1．自然连接查询2．内连接查询3．自身连接查询4．外连接查询二、嵌套查询1．带有IN谓词的子查询2．带有比较运算符的子查询3．带有ANY或ALL谓词的子查询三、基于派生表的查询【实训总结】

【实训目的与要求】

1、 掌握外键的概念，使用SELECT语句实现多表查询；

2、 掌握子查询的设计；

3、 掌握连接查询的设计等。

【实训原理】

MySQL多表查询。

【实训步骤】

参考任务二中提供的表数据内容，利用SELECT命令查询数据库表中的数据。参考教材《数据库系统概论》P89-115的例题，以及《MySQL数据库入门》第5章内容。

一、连接查询

1．自然连接查询

(1)功能：查询每个学生及其选修课程的情况

命令：

SELECT student.SNo,SName,SSex,SBir,Scredits,CNo,Score

FROM student,sc

WHERE student.SNo=sc.SNo;

截图：

(2)功能：查询选修00005号课程且成绩在70分以上的所有学生的学号和姓名

命令：

SELECT student.SNo,SName

FROM student,sc

WHERE student.SNo=sc.SNo AND sc.CNo=00005 AND Score>70;

截图：

2．内连接查询

(1)功能：查询每个同学的所修课程

命令：

SELECT student.SName,sc.CNo

FROM student

JOIN sc

ON student.SNo=sc.SNo;

截图：

(2)功能：查询所选修课程的学生学号

命令：

SELECT course.CName,sc.SNo

FROM sc

JOIN course

ON sc.CNo=course.CNo;

截图：

3．自身连接查询

(1)功能：查询每一门课的间接先修课

命令：

SELECT FIRST.CNo,SECOND.CPno

FROM course FIRST,course SECOND

WHERE FIRST.CPno=SECOND.CNo;

截图：

4．外连接查询

(1)功能：查询每个学生及其选修课程情况(左外连接)

命令：

SELECT student.SNo,SName,SSex,SBir,Scredits,CNo,Score

FROM student LEFT OUTER JOIN sc USING(SNo);

截图：

(2)功能：查询每个学生及其选修课程情况(右外连接)

命令：

SELECT student.SNo,SName,SSex,SBir,Scredits,CNo,Score

FROM student RIGHT OUTER JOIN sc USING(SNo);

截图：

二、嵌套查询

1．带有IN谓词的子查询

(1)功能：查询与“艾舒安”在同一个系学习的学生学号，姓名及学分

命令：

SELECT SNo,SName,Scredits,Sdept

FROM student

WHERE Sdept IN

(SELECT Sdept

FROM student

WHERE SName="艾舒安");

截图：

(2)功能：查询选修了课程名为“C语言”的学生学号和姓名

命令：

SELECT SNo,SName

FROM student

WHERE SNo IN

(SELECT SNo

FROM sc

WHERE CNo IN

(SELECT CNo

FROM course

WHERE CName="C语言"

)

);

截图：

2．带有比较运算符的子查询

(1)功能：查询与“艾舒安”在同一个系学习的学生学号，姓名及学分

命令：

SELECT SNo,SName,Scredits,Sdept

FROM student

WHERE Sdept =

(SELECT Sdept

FROM student

WHERE SName="艾舒安");

截图：

(2)功能：找出每个学生超过他自己选修课程平均成绩的课程号

命令：

SELECT SNo,CNo

FROM sc x

WHERE Score>=(SELECT AVG(Score)

FROM sc y

WHERE y.SNO=x.SNo);

截图：

3．带有ANY或ALL谓词的子查询

(1)功能：查询非计算机科学系中比计算机科学系任意一个学生年龄大的学生姓名和出生日期

命令：

SELECT SName,SBir

FROM student

WHERE SBir

FROM student

WHERE Sdept="CS")

AND Sdept<>'CS';

截图：

(2)功能：查询非计算机科学系中比计算机科学系所有学生年龄都大的学生姓名和出生日期

命令：

SELECT SName,SBir

FROM student

WHERE SBir>ALL(SELECT SBir

FROM student

WHERE Sdept="CS")

AND Sdept<>'CS';

截图：

三、基于派生表的查询

(1)功能：查询成绩大于80的学生姓名和学号

命令：

select sname,student.sno

from student,(select sno

from sc

where score>80)

as a

where student.SNo=a.sno;

截图：

(2)功能：查询选修了00005号课程的所有学生姓名

命令：

SELECT SName

FROM student,(SELECT SNo FROM sc WHERE CNo=‘00005’)AS sc1

WHERE student.SNo=sc1.SNo;

截图：

【实训总结】

在sc表中插入学生的选课信息及成绩，便于操作。