mysql 面试实战--sql语句查询

最近去面试，做了一道经典的mysql试题，分享一下。
将测试数据表导入数据库，其中name字段代表“姓名”，score字段代表“分数”。

1创建表结构和添加基础测试数据

create table `tb_score` (
    `id` double ,
    `name` varchar (300),
    `score` double 
);

insert into `tb_score` (`id`, `name`, `score`) values('1','n1','59');
insert into `tb_score` (`id`, `name`, `score`) values('2','n2','66');
insert into `tb_score` (`id`, `name`, `score`) values('3','n3','78');
insert into `tb_score` (`id`, `name`, `score`) values('4','n1','48');
insert into `tb_score` (`id`, `name`, `score`) values('5','n3','85');
insert into `tb_score` (`id`, `name`, `score`) values('6','n5','51');
insert into `tb_score` (`id`, `name`, `score`) values('7','n4','98');
insert into `tb_score` (`id`, `name`, `score`) values('8','n5','53');
insert into `tb_score` (`id`, `name`, `score`) values('9','n2','67');
insert into `tb_score` (`id`, `name`, `score`) values('10','n4','88');

1.1 单分数最高的人和单分数最低的人

SELECT NAME,MAX(score),MIN(score) FROM tb_score;

1.2 两门分数加起来的第2至5名

SELECT NAME,SUM(score) FROM tb_score GROUP BY NAME ORDER BY SUM(score) DESC LIMIT 1,4

1.3 两门总分数在150分以下的人。

SELECT NAME,SUM(score) FROM tb_score GROUP BY NAME HAVING SUM(score)<150

1.4 两门平均分数介于60和80的人。

SELECT NAME,AVG(score) FROM tb_score GROUP BY NAME HAVING AVG(score) BETWEEN 60 AND 80;

SELECT NAME,AVG(score) FROM tb_score GROUP BY NAME HAVING AVG(score)>60 AND AVG(score)<80;

1.5 总分大于150分，平均分小于90分的人数。

SELECT NAME,SUM(score),AVG(score) FROM tb_score GROUP BY NAME HAVING SUM(score)>150 AND AVG(score)<90

1.6 总分大于150分，平均分小于90分的人数有几个。

SELECT COUNT(DISTINCT NAME) FROM tb_score GROUP BY NAME HAVING SUM(score)>150 AND AVG(score)<90

2.创建表结构

create table `t_employ` (
	`id` int (11),
	`work_no` int (255),
	`name` varchar (165),
	`department` varchar (165),
	`type` varchar (60),
	`gz` double 
); 

INSERT INTO `t_employ` VALUES ('1', '1', '张三', '教学部', '老师', '1100');
INSERT INTO `t_employ` VALUES ('2', '2', '张二', '手工部', '钳工', '1000');
INSERT INTO `t_employ` VALUES ('3', '3', '张三', '教学部', '钳工', '1700');
INSERT INTO `t_employ` VALUES ('4', '4', '张四', '手工部', '钳工', '6000');
INSERT INTO `t_employ` VALUES ('5', '5', '张五', '体育部', '游泳', '1400');
INSERT INTO `t_employ` VALUES ('6', '6', '张六', '手工部', '木工', '1200');
INSERT INTO `t_employ` VALUES ('7', '7', '张七', '教学部', '老师', '1300');
INSERT INTO `t_employ` VALUES ('8', '8', '张八', '手工部', '木工', '2500');
INSERT INTO `t_employ` VALUES ('9', '9', '张九', '体育部', '棒球', '3000');
INSERT INTO `t_employ` VALUES ('10', '10', '十弟', '手工部', '钳工', '2500');

2.1 四种查询练习

-- 1请用一个SQL语句查询每个部门的总人数

SELECT department,COUNT(id) FROM t_employ GROUP BY department

-- 2请用一个SQL语句查询出不同部门的担任“钳工”的职工平均工资

SELECT department,AVG(gz) FROM t_employ WHERE TYPE='钳工' GROUP BY department

-- 3请用一个SQL语句查询出不同部门的担任“钳工”的职工平均工资高于2000的部门

SELECT department,AVG(gz) FROM t_employ WHERE TYPE='钳工' GROUP BY department HAVING AVG(gz)>2000

-- 4请用一个SQL语句查询每个部门低于平均工资的员工信息【内连接 on and 和 内连接 on where的使用】

SELECT e.department, e.gz ,e.name FROM t_employ e INNER JOIN 
(SELECT department ,AVG(gz) AS avgGz FROM t_employ GROUP BY department) t 
ON e.department =t.department WHERE e.gz < t.avgGz

第一个sql

第二个sql

第三个sql

第四个sql

SELECT department ,AVG(gz) AS avgGz FROM t_employ GROUP BY department

SELECT e.department, e.gz ,e.name FROM t_employ e INNER JOIN 
(SELECT department ,AVG(gz) AS avgGz FROM t_employ GROUP BY department) t 
ON e.department =t.department WHERE e.gz < t.avgGz