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

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

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

语法：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'

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说教育文库《数据库原理与应用》实验指导书修改版(1)(17)在线全文阅读。