MYSQL多表联合查询 （4表）

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; 
   

SELECT
A.ID,
A.NUMBER,
A.PRICE,
A.ORDER_TIME,
B.USER_ID,
B.STARTIME,
B.STOPTIME,
C.CHANNEL_PAY,
D.COMPANY
FROM
D表 D
LEFT JOIN A表 A ON A.COMPANY_ID = D.UID
LEFT JOIN B表 B ON B.COMPANY_ID = D.UID
LEFT JOIN C表 C ON C.COMPANY_ID = D.UID

有条件的再加行：
WHERE ..........
排序(如A表的ID由大到小排):
ORDER BY A.ID DESC

select a.id, a.number, a.price, a.order_time, a.status, b.user_id, b.startime, b.stoptime, c.channel_pay, d.company from a, b, c, d where a.company_id = b.company_id and a.company_id = c.company_id and a.company_id= d.uid
你给的信息也太少了，表结构也不知道，除了company_id需不需要别的字段关联，否则怎么确定在各表中的唯一值？

select 要显示的若干列（格式是：表别名.列名，例如：a.ID）
from a a,
b b,
c c,
d d
where a.COMPANY_ID = b.COMPANY_ID
and a.COMPANY_ID = c.COMPANY_ID
and a.COMPANY_ID = d.UID;