《数据库原理与应用》实验指导书修改版(1) - 图文(5)

《数据库原理与应用》实验指导

CNO CHAR(8) NOT NULL, GRADE FLOAT,

CHECK (GRADE BETWEEN 0 AND 100),

PRIMARY KEY (SNO,CNO),

FOREIGN KEY(SNO)REFERENCES student, FOREIGN KEY(CNO)REFERENCES course) GO

单击快捷工具栏的执行图标“

”执行上述语句，四张表便创建完成。

14

《数据库原理与应用》实验指导

实验五 连接查询和嵌套查询

连接查询 ①内连接：从两张表中提取满足连接条件的数据

语法：FROM 表1 [INNER] JOIN 表2 ON <条件表达式>

1．找出学生的姓名、出生年份、所在班级名称： SELECT Stu_name,year(birthday),Class_name

FROM Student INNER JOIN Class On Student.Class_id=Class.Class id 等价于：

SELECT Stu.sex,year(birthday),Class_name FROM Student.Class

WHERE Student.Class_id=Class.Class.id

2．找出各班级所属系的名称和电话 SELECT Class name,Depar_name,Phone

FROM Class INNER JOIN Deparment On Class.Depar_id=Deparment.Depar_id 等价于：

SELECT Class_name,Depar name,Phone FROM Class,Deparment

WHERE Class.Depar_id=Deparment.Depar_id

3．找出与“汤俊逸”老师在同一年出生的男学生的姓名、电话： SELECT Stu_name,Phone

FROM Student INNER JOIN Teacher On year(Student.Birthday)=year(Teacher.Birthday) WHERE Teacher.Teac_name='汤俊逸?AND Student.Stu_sex='男'

4．找出住在“广州市昌岗中路”的现管99的学生：

SELECT studen.*

FROM Student INNER JOIN Class On Student.Class_id=Class.Class_id WHERE Class.Class_name='现管99' AND address='广州市昌岗中路'

自连接：

5．找出同年同月同日出生的学生的姓名、出生年月 SELECT a.stu_name,b.stu_name,a.binhday

FROM student as a INNER JOIN student as b

ON a.birthday=b.birthday AND a.stu_id<>b.stu_id

6．找出“0203”班级中具有相同生日的学生的姓名、出生年月 SELECT a.stu_name,b.stu_name,a.birthday

FROM student as a INNER JOIN student as b ON month(a.birthday)=month(b.birthday) AND day(a.birthday)=day(b.birthday) AND a.stu_id<>b.stu_id

WHERE a.class_id='0203?AND b.Class_id='0203'

15

《数据库原理与应用》实验指导

②外连接：

FROM 表l LEFT | RIGHT | FULL[OUTER] JOIN 表2 ON<条件表达式> 特点：

除了返回内部连接的记录外。还显示主表中不符合连接条件的记录。

7．列出班级编号为“0102”的学生的学号、姓名、及奖惩情况： SELECT Student.Stu_id,Stu_name,award,punish

FROM Student LEFT JOIN Studeat_info On Student.Stu_id=Student_info.Stu_id WHERE Class_id='0102' 或者：

SELECT student.Stu_id,Stu_name,award,punish

FROM Student info RIGHT JOIN Student On Student.Stu_id=Student_info.Stu id WHERE Class id='0102' ③交叉连接：结果集中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。

语法：FROM 表l CROSS JOIN 表2 (注意：此处不能指定ON连接条件)

子查询 （嵌套查询）

注意：

①作为子查询的SELECT语句必须放在括号之内.

②子查询不能包含ORDER BY字句，但当子查询中使用TOP选项时，可以使用ORDER BY字句。

三种形式的子查询： (1)、由[NOT] IN 引出子查询

(2)、由[NOT] EXISTS引出子查询 (3)、由比较运算符引出子查询

(注意：在比较子查询中，如果没有使用，ALL或ANY修饰，则必须保证子查询所返回的结果集合中只有单行数据。否则将引起查询错误，如果比较操作与ALL或ANY修饰一起使用。这时则允许子查询返回多个数据行。)

比较运算符 >、>=、!< ＝ <> <、<=、!>

16

修饰符 ALL ANY ANY ANY ALL ANY 取值 最大值 最小值 取所有值 取所有值 最小值 最大值 《数据库原理与应用》实验指导

1．找出现管98所有学生的姓名、电话、地址： SELECT stu_name,phone,address

FROM student WHERE class_id IN (SELECT Class_id

FROM Class

WHERE Class_name='现管98')

当子查询返回一条记录时，“in”可以用“=”代替：

SELECT stu_name,phone,address FROM student WHERE class_id= (SELECT Class_id

FROM Class

WHERE Class_name='现管98')

或者：

SELECT stu_name,phone,address

FROM student WHERE EXISTS (SELECT *

FROM Class

WHERE Class_name='现管98' AND Class.Class_id=Student Class_id)

2、找出现管98所有任课老师的编号： SELECT Teac_id FROM CourseTeacher WHERE Class_id IN

(SELECT Class_id FROM Class

WHERE Class_name='现管98')

3、找出所有任教“c语言”老师的编号。 SELECT DISTINCT Teac_id FROM CoursgTeacher WHERE Course_id IN

(SELECT Course_id

17

《数据库原理与应用》实验指导

FROM course

WHERE Course_name='c语言')

4、找出计算机系所有文学生的姓名、屯话： SELECT stu_name,phone FROM student

WHERE Stu_seX='女' AND class_id in (SELECT Class_id FROM Class WHERE Depar_id IN (SELECT Depar_id

FROM Deparment

WHERE Depar_name='计算机系')

5、找出“钟开滨”老师所教的班级的名称： SELECT Class_name FROM Class

WHERE Class_id IN

(SELECT Class_id FROM CourseTeacher WHERE Teac_id IN (SELECT Teac_id

FROM Teacher

WHERE Teac_name='钟开滨'))

或者不采用子查询： SELECT Class_name

FROM Class,ComseTeacher,Teacher

WHERE Class.Class_id=CourseTeacher.Class_id AND CourseTeacher.Teac_id=Teacher.Teac_id AND Teac_name='钟开滨'

6．找出现管98的c语言的老师的姓名： SELECT Teac_name FROM teacher WHERE Teac_id IN (SELECT Teac_id

18