MySQL多表查询

use abcc

CREATE TABLE Account(ID INT(11),name VARCHAR(255),class VARCHAR(255))
select * from Account;

INSERT INTO account VALUES
(1,“小汗”,“高三二班”)
,(2,“小涛”,“高三一班”)
,(3,“小浩”,“高三二班”)
,(4,“小彬”,“高三一班”)
,(5,“小鱼”,“高三一班”)
,(6,“小驴”,“高三二班”)
,(7,“小凯”,“高三一班”)

CREATE TABLE Exam(id INT,course VARCHAR(255),Score INT,Aid INT)
select * from Exam;
INSERT INTO Exam VALUES
(1,“英语”,87,1),
(2,“语文”,96,1),
(3,“数学”,58,1),
(4,“语文”,40,1),
(5,“英语”,98,1),
(6,“数学”,99,1),
(7,“英语”,78,1)

Select a.class,e.course,e.score from account a,Exam e where a.id=e.aid
SELECT a.class,e.course,AVG(e.score) from account a,Exam e where a.id=e.aid GROUP BY a.class,e.course