数据库 下载本文

例:A、在选课Score表中查询选修了001、005或003课程的同学学号、课程号和相应成绩。

SELECT StudentNo, CourseNo, Score FROM Score

WHERE CourseNo = ‘001’ OR CourseNo= ‘005’ OR CourseNo= ‘003’

B、在Student表中查询1991年出生且民族为“汉族”的同学学号、姓名、出生日期。 SELECT StudentNo, StudentName, birthday FROM Student

WHERE year(birthday)=1991 AND nation=’汉族’

C、在Student 表中查询籍贯既不是“南昌”也不是“上海”的同学姓名、课程号和所属班级编号。

SELECT StudentName, native, ClassNo FROM Student

WHERE native!=’南昌’ AND native!=’上海’

D、在选课Score表中查询成绩在80~90分之间的同学学号、课程号和相应成绩。 SELECT StudentNo, CourseNo, Score FROM Score

WHERE score>=80 AND score<=90

E、在选课Score表中查询成绩不在80~90分之间的同学学号、课程号和相应成绩。

SELECT StudentNo, CourseNo, Score FROM Score

WHERE score<80 OR score>90

12、排序运算:

例:A、在学生Student 表中查询籍贯既不是“南昌”也不是“上海”的同学姓名、籍贯和所属班级编号,并按籍贯的降序排序输出。 SELECT StudentName, Native, ClassNO FROM Student

WHERE native!=’南昌’ AND native!=’上海’ ORDER BY native DESC

B、在学生Student表中查询“女”学生的学号、姓名、所属班级编号和出生日期,并按班级编号的升序、出生日期的降序排序输出。 SELECT StudentNo, StudentName, classNo, birthday FROM Student WHERE SEX=’女’ ORDER BY classNo, month(birthday) DESC

其中,month()函数表示提取日期表达式的月份。

13、查询表:

例:查询1991年出生的“女”同学基本信息。

分析:可以先将学生表中的女生记录查询出来,然后在对查询表进行选择、投影操作。

SELECT StudentNo, studentName , birthday

FROM (SELECT* FROM Student WHERE sex=’女’) AS a WHERE year(birthday)=1991 AND sex=’女’ 该查询在FROM子句后是一个查询表,表示对该查询的查询结果——查询表进行查询,必须为查询表取一个名称(该名称称为元组变量),如使用AS a 取名为a,也可以写成

FROM(SELECT* FROM Student WHERE sex=’女’) a 该查询等价于:

SELECT studentNo, studentName, birthday FROM student

WHERE year(birthday)=1991 AND sex=’女’

五、连接查询:连接运算是关系数据库中使用最广泛的一种运算,包括等值连接、自然连接、非等值连接、自表连接和外连接等。

①等值与分等值连接运算:是在WHERE子句中加入连接多个关系的连接条件,其格式为:

WHERE[表1.]<属性名1><比较运算符>[表2.]<属性名2>

[<逻辑运算符>[表3.]<属性名3><比较运算符>[表4.]<属性名4>?]

比较运算符包括:>,>=,<,<=, =(或!=)<> 当比较运算符为=时,表示等值连接;其他运算为非等值连接。WHERE子句的连接谓词中的属性称为连接属性,连接属性之间必须具有可比性。

1、等值连接:

例:A、查找会计学员全体同学的学号、姓名、籍贯、班级编号和所在班级名称。 分析:a.该查询的结果为同学的学号、姓名、籍贯、班级编号和所在班级名称,在SELECT子句中必须包含这些属性。

b.由于班级名称和所属学院在班级表Class中,学号、姓名、籍贯、班级编号在学生表Student中,因此FROM子句必须包含Class表和Student表。

c.由于班级编号classNo既是班级表的主码,也是学生表的外码,这2个表的连接条件是classNo相等,因此在WHERE子句中必须包含连接条件Student.classNo=Class.classNo。

d.本查询要查询出会计学员的学生记录,因此在WHERE子句中还必须包括选择条件intitute=’会计学院’。

F.因此,本查询语句为:

SELECT studentNO, studentName, native, Student.classNo, className

FROM Student, Class

WHERE Student.classNo=Class.classNo AND institute=’会计学院’

在本连接操作中,如果涉及多个表的相同属性名,必须在相同的属性名前加上表名加以区分,如Student.classNo, Class.classNo。WHERE 子句中的Student.classNo=Class.classNo为连续条件,institute=’会计学院’为选择条件。

为了简化,可为参与连接的表取别名(称为元组变量),这样在相同的属性名前加上表的别名就可以了,本例可以改写为:

SELECT studentNo, studentName, native, b.classNo, className FROM Student AS a,Class AS b

WHERE a.classNo=b.classNo AND institute=’会计学院’ 或者:

SELECT studentNo, studentName, native, b.classNo, className FROM Student a, Class b

WHERE a.classNo=b.classNo AND institute=’会计学院’ 注:将Student 表取别名为a, Class表取别名为b, 班级编号分别用a.classNo 和b.classNo表示。对于多个表中不同属性名,可以不要在属性名前面加上前者。

例B:查找选修了课程名称为“计算机原理”的同学学号、姓名。 SELECT a.StudentNo, StudentName FROM Student a, Course b, Score c

WHERE b.courseNo=c.courseNo AND c.studentNo=a.studentNo AND b.courseName=’计算机原理’ 本例中元组变量的连接条件为:

b.courseNo=c.courseNo AND c.studentNo=a.studentNo

2、自然连接:SQL不直接支持自然连接,完成自然连接的方法是在等值连接的基础上消除重复列。

例:实现成绩表Score和课程表Course的自然连接。

SELECT studentNo, a,courseNo, score, courseName, creditHour, priorCourse FROM Score a, Course b

WHERE a.courseNo=b.courseNo

注:本例中课程编号在两个关系中同时出现,但在SELECT子句中仅需要出现1次,因此使用a.courseNo, 也可以使用b.courseNo。其他列名是唯一的,不需要加上元组变量。

②自表连接:若某个表与自己进行连接,称为自表连接。 例A:查找同时选修了编号为001和002课程的同学学号、姓名、课程号和相应成绩,并按学号排序输出。

分析:a.学生姓名在学生表中,因此FROM子句必须包含学生表(取别名为a) b.可以考虑两个成绩表,分别记为b和c,b表用于查询选修了编号为001课程的同学,c表用于查询选修了编号为002课程的同学,因此FROM的子句还必须包含两个成绩表b和c,且在WHERE子句中包含两个选择条件:

b.courseNo=’001’ AND c.courseNo=’002’ c.一方面,成绩表b与成绩表c在学号上做等值连接(自表连接),如果连接成功,表示学生同时选修了编号为001和002的课程;另一方面,学生表与成绩表b(或成绩表c)在学号上做等值连接。因此WHERE子句需要包含两个连接条件:

b.studentNo=c.studentNo AND a.studentNo=b.studentNo d.因此,本查询语句为:

SELECT a.studentNo, studentName, b.courseNo, b.score, c.courseNo, c.score FROM Student a, Score b, Score c

WHERE b.courseNo=’001’ AND c.courseNo=’002’

AND b.studentNo=c.studentNo AND a.studentNo=b.studentNo OBEY BY a.studentNo 若使用等值连接则写为:

SELECT a.studentNo, studentName, b.courseNo, b.core, c.courseNo, c.score FROM student a, Score b , (SELECT * FROM Score WHERE courseNo=’002’) WHERE b.courseNo=’001’ AND a.studentNo=b.studentNo AND a.studentNo=c.studentNo

OBEY BY a.studentNo

③外连接:在一般的连接中,只有满足连接条件的元组才被检索出来,对于没有满足连接条件的元组是不作为结果被检索出来的。

例:查询每个班级的班级名称、所属学院、学生学号、学生姓名,按班级名称排序输出。

SELECT className, institute, studentNo, studentName FROM Class a, Student b WHERE a.classNo=b.classNo OBEY BY className

④左外连接:左外连接的连接结果中包含左关系中的所有元组,对于左关系中没有连接上的元组,其右关系中的相应属性用空值替代。

例:使用左外连接查询每个班级的班级名称、所属学院、学生学号、学生姓名,按班级名称排序输出。

SELECT className, institute, studentNo, studentName

FROM Class aLEFT OUTER JOIN Student b ON a.classNo=b.classNo ORDER BY className

⑤右外连接:右外连接的连接结果中包含右关系的所有元组,对于右关系中没有连接上的元组,其左关系中的相应属性用空值替代。

例:使用右外连接查询每个班级的班级名称、所属学院、学生学号、学生姓名、