数据库（mysql）查询语句练习（初级）

观前提示

此查询练习的数据库来源于网上，可以自行百度！！！！

数据库

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `DEPTNO` int(11) NOT NULL COMMENT '部门编号',
  `DNAME` varchar(14) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '部门名称',
  `LOC` varchar(13) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '部门地址',
  PRIMARY KEY (`DEPTNO`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES ('10', 'ACCOUNTING', 'NEW YORK');
INSERT INTO `dept` VALUES ('20', 'RESEARCH', 'DALLAS');
INSERT INTO `dept` VALUES ('30', 'SALES', 'CHICAGO');
INSERT INTO `dept` VALUES ('40', 'OPERATIONS', 'BOSTON');

-- ----------------------------
-- Table structure for emp
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `EMPNO` int(11) NOT NULL COMMENT '编号',
  `ENAME` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '姓名',
  `JOB` varchar(9) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '工种',
  `MGR` double DEFAULT NULL COMMENT '上级编号',
  `HIREDATE` date DEFAULT NULL COMMENT '生日',
  `SAL` double DEFAULT NULL COMMENT '薪水',
  `COMM` double DEFAULT NULL COMMENT '奖金',
  `DEPTNO` int(11) DEFAULT NULL COMMENT '部门编号',
  PRIMARY KEY (`EMPNO`) USING BTREE,
  KEY `DEPTNO` (`DEPTNO`) USING BTREE,
  CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`DEPTNO`) REFERENCES `dept` (`DEPTNO`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES ('2222', 'Dfds', 'dsfcds', '56454', '1988-02-21', '546464', '888', '40');
INSERT INTO `emp` VALUES ('7369', 'SMITH', 'CLERK', '7902', '1980-12-17', '800', null, '20');
INSERT INTO `emp` VALUES ('7499', 'ALLEN', 'SALESMAN', '7698', '1981-02-20', '1600', '300', '30');
INSERT INTO `emp` VALUES ('7521', 'WARD', 'SALESMAN', '7698', '1981-02-22', '1250', '500', '30');
INSERT INTO `emp` VALUES ('7566', 'JONES', 'MANAGER', '7839', '1981-04-02', '2975', null, '20');
INSERT INTO `emp` VALUES ('7654', 'MARTIN', 'SALESMAN', '7698', '1981-09-28', '1250', '1400', '30');
INSERT INTO `emp` VALUES ('7698', 'BLAKE', 'MANAGER', '7839', '1981-05-01', '2850', null, '30');
INSERT INTO `emp` VALUES ('7782', 'CLARK', 'MANAGER', '7839', '1981-06-09', '2450', null, '10');
INSERT INTO `emp` VALUES ('7788', 'SCOTT', 'ANALYST', '7566', '1987-07-13', '3000', null, '20');
INSERT INTO `emp` VALUES ('7839', 'KING', 'PRESIDENT', null, '1981-11-17', '5000', null, '10');
INSERT INTO `emp` VALUES ('7844', 'TURNER', 'SALESMAN', '7698', '1981-09-08', '1500', '0', '30');
INSERT INTO `emp` VALUES ('7876', 'ADAMS', 'CLERK', '7788', '1987-07-13', '1100', null, '20');
INSERT INTO `emp` VALUES ('7900', 'JAMES', 'CLERK', '7698', '1981-12-03', '950', null, '30');
INSERT INTO `emp` VALUES ('7902', 'FORD', 'ANALYST', '7566', '1981-12-03', '3000', null, '20');
INSERT INTO `emp` VALUES ('7934', 'MILLER', 'CLERK', '7782', '1982-01-23', '1300', null, '10');

-- ----------------------------
-- Table structure for salgrade
-- ----------------------------
DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
  `GRADE` int(11) DEFAULT NULL COMMENT '等级编号',
  `LOSAL` double DEFAULT NULL COMMENT '最低薪水',
  `HISAL` double DEFAULT NULL COMMENT '最高薪水'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

-- ----------------------------
-- Records of salgrade
-- ----------------------------
INSERT INTO `salgrade` VALUES ('1', '700', '1200');
INSERT INTO `salgrade` VALUES ('2', '1201', '1400');
INSERT INTO `salgrade` VALUES ('3', '1401', '2000');
INSERT INTO `salgrade` VALUES ('4', '2001', '3000');
INSERT INTO `salgrade` VALUES ('5', '3001', '9999');

题目

– 1、 查询部门在NEW YORK的所有员工姓名,员工号和部门名称。

– 2、 查找所在部门平均薪水高于2000的部门名称和平均薪水。（薪水低于1000的员工不列入统计范围，结果按照平均薪水降序排序）

– 3、 从人员表表中查询出管理者是BLAKE的员工姓名ename、工资sal、所在部门的编号、所在部门的名称、薪水级别。

– 4、 查询每个部门中工资在2000元以上的员工姓名,工资及其所在部门名称,并以工资升序排序,如果工资相等则按员工姓名降序排序

– 5、 查询员工中和主管在同一年入职且薪水比主管薪水低的员工编号、员工姓名、入职时间、部门编号及其主管编号、主管姓名、主管入职时间、主管部门编号。

– 6、 查询每个部门的部门名称和各部门员工中工资最低的工资。

– 7、 查询各部门工资大于1800的所有员工的姓名，工资和所在部门的名称，并按照员工姓名升序排列。

– 8、 查询入职时间跟BLAKE同年同月入职的员工的信息及其所在部门信息及其薪水级别信息

– 9、 查询各部门员工的入职日期在’1981-5-1’ 和’1985-5-1’之间的所有员工的姓名、入职时间和部门名称(包括无部门员工),并以员工姓名升序排序。

– 10、统计各部门的员工总工资数，需显示部门名称和总工资数（包括无部门员工)。

– 11、统计现一共有多少个部门已开始投入运行，显示部门编号、名称、所在地、部门的人数(即有员工的部门)

– 12、查询各部门人数大于3人的部门名称和人数。

– 13、查询部门所在地不在’NEW YORK’的部门名称和平均工资。

– 14、查询员工中有1981-12-03入职的部门的所有员工信息

– 15、查询各部门中从事各个工作岗位的人数有多少，显示其部门名称、职位名称(不含无员工部门)和从事该职位的人数,并以部门名称、职位名称、从事该职位的人数升序排序。

– 16、查询各部门中雇佣时间最长的职员的所有信息。

– 17、查询各部门中平均薪水最多的部门名称(无部门的除外)。

– 18、列出薪水高于在部门30工作的所有员工的薪金的员工姓名和薪金，部门名称。

– 19、显示部门人数最多的前两个部门名称和员工人数。

– 20、列出薪水高于所在部门平均薪水的员工姓名和部门编号。

解答

– 1、 查询部门在NEW YORK的所有员工姓名,员工号和部门名称。
select e.ename,e.empno,d.loc from emp e join dept d on e.DEPTNO=d.DEPTNO where d.LOC=‘NEW YORK’;

– 2、 查找所在部门平均薪水高于2000的部门名称和平均薪水。（薪水低于1000的员工不列入统计范围，结果按照平均薪水降序排序）
select dept.dname,avg(sal) from emp join dept on emp.deptno=dept.deptno where empno in
(select empno from emp where sal >1000)
group by emp.DEPTNO having avg(sal) >2000 order by avg(sal) desc;

– 3、 从人员表表中查询出管理者是BLAKE的员工姓名ename、工资sal、所在部门的编号、所在部门的名称、薪水级别。
select DISTINCT e.ename,e.sal,e.deptno,d.dname,s.GRADE from dept d,emp e,salgrade s where d.deptno=e.deptno and e.mgr=(select empno from emp where ename=‘BLAKE’)
AND e.sal BETWEEN s.LOSAL and s.HISAL;

– 4、 查询每个部门中工资在2000元以上的员工姓名,工资及其所在部门名称,并以工资升序排序,如果工资相等则按员工姓名降序排序
select e.ename,e.sal,d.dname from emp e join dept d on e.DEPTNO=d.DEPTNO where sal>2000 order by sal asc,e.ename desc;

– 5、 查询员工中和主管在同一年入职且薪水比主管薪水低的员工编号、员工姓名、入职时间、部门编号及其主管编号、主管姓名、主管入职时间、主管部门编号。
select e.empno,e.ename,e.hiredate,e.deptno,e1.empno,e1.ename,e1.hiredate,e1.deptno from emp e join emp e1 on e.mgr = e1.empno
where YEAR(e.hiredate) = YEAR(e1.hiredate) and e.sal < e1.sal

– 6、 查询每个部门的部门名称和各部门员工中工资最低的工资。
select d.dname,min(sal) from emp e join dept d on e.deptno=d.deptno group by e.deptno;

– 7、 查询各部门工资大于1800的所有员工的姓名，工资和所在部门的名称，并按照员工姓名升序排列。
select e.ename,e.sal,d.dname from emp e join dept d on e.deptno=d.deptno where sal>1800 order by e.ename;

– 8、 查询入职时间跟BLAKE同年同月入职的员工的信息及其所在部门信息及其薪水级别信息
select emp.*,dept.dname,salgrade.GRADE from emp,dept,salgrade where emp.deptno=dept.deptno and YEAR(hiredate)=(select YEAR(hiredate) from emp where ename=‘BLAKE’)
and month(hiredate)=(select month(hiredate) from emp where ename=‘BLAKE’)
and emp.sal BETWEEN salgrade.LOSAL and salgrade.HISAL;

– 9、 查询各部门员工的入职日期在’1981-5-1’ 和’1985-5-1’之间的所有员工的姓名、入职时间和部门名称(包括无部门员工),并以员工姓名升序排序。
select e.ename,e.hiredate,d.dname from emp e join dept d on e.deptno=d.deptno where e.hiredate between ‘1981-5-1’ and ‘1985-5-1’ order by e.ename asc;

– 10、统计各部门的员工总工资数，需显示部门名称和总工资数（包括无部门员工)。
select d.dname,sum(sal) from emp e join dept d on e.deptno=d.deptno group by e.deptno;

– 11、统计现一共有多少个部门已开始投入运行，显示部门编号、名称、所在地、部门的人数(即有员工的部门)
select dept.*,count(emp.empno) from emp,dept where dept.deptno=emp.deptno GROUP BY emp.deptno

– 12、查询各部门人数大于3人的部门名称和人数。
select d.dname,count(e.empno) ‘人数’ from emp e join dept d on e.deptno=d.deptno group by e.deptno having 人数>3;

– 13、查询部门所在地不在’NEW YORK’的部门名称和平均工资。
select d.dname,avg(sal) from emp e join dept d on e.deptno=d.deptno group by e.deptno having e.DEPTNO not in(select deptno from dept where loc=‘NEW YORK’);

– 14、查询员工中有1981-12-03入职的部门的所有员工信息
select * from emp where deptno in (select deptno from emp where hiredate=‘1981-12-03’);

– 15、查询各部门中从事各个工作岗位的人数有多少，显示其部门名称、职位名称(不含无员工部门)和从事该职位的人数,并以部门名称、职位名称、从事该职位的人数升序排序。
select d.dname,e.job,count(e.job) from emp e join dept d on e.deptno=d.deptno group by d.dname,e.job order by d.dname,e.job,count(e.job);

– 16、查询各部门中雇佣时间最长的职员的所有信息。
select *from emp e join dept d on e.deptno=d.deptno group by e.deptno having max(datediff(now(),hiredate));

– 17、查询各部门中平均薪水最多的部门名称(无部门的除外)。
select avg(e.sal),d.dname from emp e join dept d on e.deptno=d.deptno group by e.deptno order by avg(e.sal) desc limit 1,1;

– 18、列出薪水高于在部门30工作的所有员工的薪金的员工姓名和薪金，部门名称。
select e.ename,e.sal,d.dname from emp e join dept d on e.deptno=d.deptno
where e.sal>all (select sal from emp where deptno=30);

– 19、显示部门人数最多的前两个部门名称和员工人数。
select d.dname,count(e.deptno)‘员工人数’ from emp e join dept d on e.deptno=d.deptno group by e.deptno order by 员工人数 desc limit 0,2;

– 20、列出薪水高于所在部门平均薪水的员工姓名和部门编号。
select e.ename,e.deptno from emp e join (select deptno,avg(sal) avgsal from emp group by deptno) tab on e.deptno = tab.deptno where e.sal > tab.avgsal