看看MYsql试题：
sql面试题（学生表_课程表_成绩表_教师表）
https://www.cnblogs.com/a-dou/articles/5489772.html
表架构
Student(S,Sname,Sage,Ssex) 学生表
Course(C,Cname,T) 课程表
SC(S,C,score) 成绩表
Teacher(T,Tname) 教师表
drop table if exists course;
create table course(
c int(4) default null primary key auto_increment,
cname varchar(30) not null,
t int(4) default null
)engine=innodb default charset=utf8;
INSERT INTO course(c,cname,t)VALUES ('1','语文','1');
INSERT INTO course(c,cname,t)VALUES ('2','数学','2');
INSERT INTO course(c,cname,t)VALUES ('3','英语','3');
INSERT INTO course(c,cname,t)VALUES ('4','物理','4');
drop table if exists sc;
create table sc(
s int(4) ,
c int(4) default null,
score int(4) default null
)engine=innodb default charset=utf8;
insert into `sc` values ('1', '1', '56');
insert into `sc` values ('1', '2', '78');
insert into `sc` values ('1', '3', '67');
insert into `sc` values ('1', '4', '58');
insert into `sc` values ('2', '1', '79');
insert into `sc` values ('2', '2', '81');
insert into `sc` values ('2', '3', '92');
insert into `sc` values ('2', '4', '68');
insert into `sc` values ('3', '1', '91');
insert into `sc` values ('3', '2', '47');
insert into `sc` values ('3', '3', '88');
insert into `sc` values ('3', '4', '56');
insert into `sc` values ('4', '2', '88');
insert into `sc` values ('4', '3', '90');
insert into `sc` values ('4', '4', '93');
insert into `sc` values ('5', '1', '46');
insert into `sc` values ('5', '3', '78');
insert into `sc` values ('5', '4', '53');
insert into `sc` values ('6', '1', '35');
insert into `sc` values ('6', '2', '68');
insert into `sc` values ('6', '4', '71');
drop table if exists student;
create table student(
s int(4),
sname varchar(32) default null,
sage int(4) default null,
ssex varchar(8) default null
)engine=innodb default charset=utf8;
INSERT INTO `student` VALUES ('1', '刘一', '18', '男');
INSERT INTO `student` VALUES ('2', '钱二', '19', '女');
INSERT INTO `student` VALUES ('3', '张三', '17', '男');
INSERT INTO `student` VALUES ('4', '李四', '18', '女');
INSERT INTO `student` VALUES ('5', '王五', '17', '男');
INSERT INTO `student` VALUES ('6', '赵六', '19', '女');
drop table if exists `teacher`;
create table `teacher` (
`t` int(11) default null,
`tname` varchar(16) default null
) engine=myisam default charset=utf8;
-- ----------------------------
-- records of teacher
-- ----------------------------
insert into `teacher` values ('1', '叶平');
insert into `teacher` values ('2', '贺高');
insert into `teacher` values ('3', '杨艳');
insert into `teacher` values ('4', '周磊');员工表：
+---------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+------------------+------+-----+---------+----------------+ | id | int(11) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(255) | YES | | NULL | | | salary | int(10) unsigned | YES | | NULL | | | department_id | int(11) | YES | | NULL | | +---------------+------------------+------+-----+---------+----------------+ +----+-------+--------+---------------+ | id | name | salary | department_id | +----+-------+--------+---------------+ | 1 | joe | 70000 | 1 | | 2 | henry | 80000 | 2 | | 3 | sam | 60000 | 2 | | 4 | max | 90000 | 1 | +----+-------+--------+---------------+
部门表：
+-------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(255) | YES | | NULL | | +-------+------------------+------+-----+---------+----------------+ +----+-------+ | id | name | +----+-------+ | 1 | It | | 2 | Sales | +----+-------+
用sql查询语句找出每个部门工资最高的员工： 就是这个sql语句 使用了group by
select d.name,e.name,max(e.salary) from employee as e left join department as d on d.id=e.department_id GROUP BY d.id; +-------+-------+---------------+ | name | name | max(e.salary) | +-------+-------+---------------+ | It | joe | 90000 | | Sales | henry | 80000 | +-------+-------+---------------+