mysql-multiquery

mysql - 多表查询

1. 等值连接
   查询每个学生及其选修课程的情况

   select student.sname,course.cname from student,sc,course where student.sno=sc.sno and course.cno=sc.cno ORDER BY student.sno
   

2. 自身连接
   查询每一门课的间接先修课（即先修课的先修课）

   select f.cno,s.cpno from course f,course s where f.cpno=s.cno
   

3. 外连接
   保留舍弃的元组，在对应被连接表的分量部分用空值(NULL)代替,这种连接被叫做外连接

   查找每个学生的基本情况及选课情况

   select student.*,sc.grade,sc.cno from student LEFT JOIN sc ON student.sno=sc.sno
   

4. 复合条件连接
   查询选修2号课程且成绩在90分以上的所有学生

   SELECT student.sname from student,sc where student.sno=sc.sno and sc.grade>90 and sc.cno LIKE 'c002'
   

   查询每个学生的学号、姓名、选修的课程名及成绩

   SELECT student.sno,student.sname,course.cname,sc.grade from student,course,sc where student.sno=sc.sno and course.cno=sc.cno
   

5. 嵌套查询,in子查询

   SELECT sname from student where sno in (SELECT sno from sc where cno='c002')
   

   查询与“刘晨”在同一个系学习的学生

   SELECT sname from student where sdept in (select sdept from student where sname like '刘晨')
   

   查询选修了课程名为“信息系统”的学生学号和姓名

   SELECT sno,sname from student where sno in (select sno from sc where cno in (select cno from course where cname='信息系统'))
   

   找出每个学生超过他选修课程平均成绩的课程号

   SELECT sno,cno from sc x where grade >=(SELECT AVG(grade) from sc y where y.sno=x.sno)
   

   查询其他系中比计算机科学某一学生年龄小的学生姓名和年龄

   SELECT sname,sage from student where sage<ANY(SELECT sage from student where sdept='cs') and sdept<>'cs'