本文已参与「新人创作礼」活动，一起开启掘金创作之路
实验目的
（1） 掌握等值连接、不等值连接的形式和用途
（2） 掌握自连接的形式和应用场合
实验预习与准备
（1） 了解数据检索时使用多表连接的原因和目的，以及连接条件如何确定
（2） 掌握等值连接和不等值连接的区别
（3） 了解自连接的用法
实验内容及步骤
查询上过“大学英语”且期末成绩在80到90分之间的学生学号、姓名和学分
select st.sno,st.sname,c.credit from score as s,course as c,student as st where s.cno=c.cno and st.sno = s.sno and c.cname="大学英语" and s.score>=80 and s.score<=90;
查询计算机学院的女生姓名，及其所选的课程名以及该课程的平时成绩，期末成绩
select st.sname,c.cname,s.score,s.usual from score as s,course as c,student as st, class as cl where s.cno=c.cno and st.sno = s.sno and st.clno = cl.clno and cl.department="计算机学院" and st.sex="女";
查询李晨老师教过的学生的学号，姓名，电话号码
mysql> select st.sno,st.sname,st.tel from teacher as t,course_class as cc ,class as c,student as st
-> where cc.tno = t.tno and cc.clno = st.clno
-> and t.tname = "李晨";
查询和“张丹丹”老师同一个院系的学生的姓名和院系名称
mysql> select st.sname,t.department from student as st,teacher as t, class as c
-> where t.department=c.department
-> and t.tname="张丹丹" and st.clno=c.clno;
查询邮箱不为空并且 “数据库原理”课程期末成绩在80分以上的学生学号、姓名和院系，并按学生院系升序排列，同一院系的学生按出生日期降序排列
mysql> select st.sno,st.sname,c.department from student as st, class as c,score as s
-> where s.cno = "数据库原理" and s.score>80
-> and -isnull(st.email)
-> and s.sno=st.sno
-> order by c.department,st.birth desc;
查询与“贺明明”一个院系的教师编号和名字。
mysql> select t.tno,t.tname from teacher as t,teacher as tt
-> where t.department=tt.department and tt.tname="贺明明"
-> and t.tname<>"贺明明";
查询所有出生日期晚于“朱凡”并且性别与之相同的学生姓名、班级名称和院系
mysql> select st.sname,c.clname,c.department from student as st, class
as c,student
-> where student.sname = "朱凡" and student.birth < st.birth and student.sex=st.sex and st.clno=c.clno;
查询同时教授“010002”和“010003”号课程的教师信息。
mysql> select t.* from teacher as t,course_class as cc, course_class as ccc
-> where cc.con="010003" and ccc.con="010002" and t.tno=cc.tno and
t.tno=ccc.tno;
查询在同一门课程中期末成绩相同的学生的学号、课程名称和期末成绩
mysql> select s.sno,c.cname,s.score from score as s,score as ss,course as c
-> where (s.score =ss.score and s.sno!=ss.sno) and s.cno=c.cno and
s.cno=ss.cno;
