mysql - 使用外键关系的 SQL 查询

Question

我正在学习 MySQL，我正在尝试在查询中进行简单的算术运算，我正在尝试确定平均工资最高的部门并获取部门名称。但我不知道如何使用外键访问 ProfessorsTable 表。任何人都可以帮助我吗？谢谢!

这是我试过的:

 SELECT Department FROM ProfessorsTable WHERE max(AVG(Salary));

下面是我的两个表:

 CREATE TABLE DepartmentsTable
 (
   Department VARCHAR(50) NOT NULL,
   Faculty VARCHAR(50) NOT NULL,
   Building VARCHAR(50) NOT NULL,
   CONSTRAINT PRIMARY KEY (Department)
 ) type = InnoDB;

 CREATE TABLE ProfessorsTable
 (
   Name VARCHAR(50) NOT NULL,
   Department VARCHAR(50) NOT NULL,
   Rank VARCHAR(50) NOT NULL,
   Salary INT UNSIGNED NOT NULL,
   CONSTRAINT FOREIGN KEY (Department) REFERENCES DepartmentsTable(Department), 
   CONSTRAINT PRIMARY KEY (Name, Department)
 ) type = innoDB;

Answer 1

获取所有部门的平均工资。 . .

SELECT Department, avg(Salary) AS avg_salary
FROM ProfessorsTable 
GROUP BY Department
ORDER BY avg_salary DESC;

只获取平均值最高的部门。 . .

SELECT Department, avg(Salary) AS avg_salary
FROM ProfessorsTable 
GROUP BY Department
ORDER BY avg_salary DESC LIMIT 1;