Question

如何使MYSQL多表联合查询?

Answer 1

1. select * from 表1 inner join 表2 on 关联条件

2. select * from 表1 left outer join 表2 on 关联条件

3. select * from 表1 right outer join 表2 on 关联条件 

4. select * from 表1 cross join 表2 on 关联条件

MYSQL查询

1. 查询平均成绩大于70分的同学的学号和平均成绩
   SELECT s.id,AVG(sc.score) FROM student s,studentcourse sc WHERE s.id=sc.student_id GROUP BY s.id HAVING  AVG(sc.score)>70;

2. 查询所有同学的学号、姓名、选课数、总成绩
   SELECT id,NAME
   FROM student
   WHERE id NOT IN (SELECT student_id
   FROM studentcourse
   WHERE course_id IN (SELECT course.id
   FROM teacher,course
   WHERE teacher.id=course.teacher_id
   AND teacher.name=’关羽’));

   

3. 查询学生信息和平均成绩
   SELECT s.id,s.name,s.city,s.age, c.name,sc.score,t.name
   FROM student s,studentcourse sc,course c,teacher t
   WHERE s.id=sc.student_id AND c.id=sc.course_id AND c.teacher_id=t.id GROUP BY s.id;