MySQL单表查询、多表查询、分组查询、等级查询综合练习

发布时间：2024年01月20日

一、建表并插入数据

1.detp表

CREATE DATABASE dept_emp;

USE dept_emp;    

CREATE TABLE dept (
	deptno INT(2) NOT NULL COMMENT '部门编号',
	dname VARCHAR (15) COMMENT '部门名称',
	loc VARCHAR (20) COMMENT '地理位置' 
);

-- 添加主键
ALTER TABLE dept ADD PRIMARY KEY (deptno);

-- 添加数据
INSERT INTO dept (deptno,dname,loc)VALUES (10,'财务部','高新四路');
INSERT INTO dept (deptno,dname,loc)VALUES (20,'人事部','科技二路');
INSERT INTO dept (deptno,dname,loc)VALUES (30,'销售部','长安区');
INSERT INTO dept (deptno,dname,loc)VALUES (40,'运输部','雁塔区');

mysql> select * from dept;
+--------+-----------+--------------+
| deptno | dname     | loc          |
+--------+-----------+--------------+
|     10 | 财务部    | 高新四路     |
|     20 | 人事部    | 科技二路     |
|     30 | 销售部    | 长安区       |
|     40 | 运输部    | 雁塔区       |
+--------+-----------+--------------+
4 rows in set (0.02 sec)

2.emp表

CREATE TABLE emp(
	empno INT(4) NOT NULL COMMENT '员工编号',
	ename VARCHAR(10) COMMENT '员工名字',
	job VARCHAR(10) COMMENT '职位',
	mgr INT(4) COMMENT '上司',
	hiredate DATE COMMENT '入职时间',
	sal INT(7) COMMENT '基本工资',
	comm INT(7) COMMENT '补贴',
	deptno INT(2) COMMENT '所属部门编号'
);

-- 添加主键
ALTER TABLE emp ADD PRIMARY KEY (empno);

-- 添加外键约束
ALTER TABLE emp ADD CONSTRAINT f_ed_key
FOREIGN KEY (deptno) 
REFERENCES dept(deptno);

INSERT INTO `emp` VALUES('7369','张倩','办事员','7902','2002-12-17','820',NULL,'20');
INSERT INTO `emp` VALUES('7499','刘博','售货员','7698','1992-02-20','1900','300','30');
INSERT INTO `emp` VALUES('7521','李兴','售货员','7698','1995-07-22','1250','500','30');
INSERT INTO `emp` VALUES('7566','李雷','人事部长','7839','1991-04-02','975',NULL,'20');
INSERT INTO `emp` VALUES('7654','刘浩','售货员','7698','1991-09-28','1250','1400','30');
INSERT INTO `emp` VALUES('7698','刘涛','销售部长','7839','1997-05-01','2850',NULL,'30');
INSERT INTO `emp` VALUES('7782','华仔','人事部长','7839','1995-06-09','2450',NULL,'10');
INSERT INTO `emp` VALUES('7788','张飞','人事专员','7566','1998-04-19','3000',NULL,'20');
INSERT INTO `emp` VALUES('7839','马晓云','董事长',NULL,'1991-11-17','5000',NULL,'10');
INSERT INTO `emp` VALUES('7844','马琪','售货员','7698','1996-09-08','1500','0','30');
INSERT INTO `emp` VALUES('7876','李涵','办事员','7788','1997-05-23','1100',NULL,'20');
INSERT INTO `emp` VALUES('7900','李小涵','销售员','7698','1993-2-13','950',NULL,'30');
INSERT INTO `emp` VALUES('7902','张三','人事组长','7566','1992-10-08','3000',NULL,'20');
INSERT INTO `emp` VALUES('7934','张三丰','人事长','7782','1997-06-23','1300',NULL,'10');

mysql> select * from emp;
+-------+-----------+--------------+------+------------+------+------+--------+
| empno | ename     | job          | mgr  | hiredate   | sal  | comm | deptno |
+-------+-----------+--------------+------+------------+------+------+--------+
|  7369 | 张倩      | 办事员       | 7902 | 2002-12-17 |  820 | NULL |     20 |
|  7499 | 刘博      | 售货员       | 7698 | 1992-02-20 | 1900 |  300 |     30 |
|  7521 | 李兴      | 售货员       | 7698 | 1995-07-22 | 1250 |  500 |     30 |
|  7566 | 李雷      | 人事部长     | 7839 | 1991-04-02 |  975 | NULL |     20 |
|  7654 | 刘浩      | 售货员       | 7698 | 1991-09-28 | 1250 | 1400 |     30 |
|  7698 | 刘涛      | 销售部长     | 7839 | 1997-05-01 | 2850 | NULL |     30 |
|  7782 | 华仔      | 人事部长     | 7839 | 1995-06-09 | 2450 | NULL |     10 |
|  7788 | 张飞      | 人事专员     | 7566 | 1998-04-19 | 3000 | NULL |     20 |
|  7839 | 马晓云    | 董事长       | NULL | 1991-11-17 | 5000 | NULL |     10 |
|  7844 | 马琪      | 售货员       | 7698 | 1996-09-08 | 1500 |    0 |     30 |
|  7876 | 李涵      | 办事员       | 7788 | 1997-05-23 | 1100 | NULL |     20 |
|  7900 | 李小涵    | 销售员       | 7698 | 1993-02-13 |  950 | NULL |     30 |
|  7902 | 张三      | 人事组长     | 7566 | 1992-10-08 | 3000 | NULL |     20 |
|  7934 | 张三丰    | 人事长       | 7782 | 1997-06-23 | 1300 | NULL |     10 |
+-------+-----------+--------------+------+------------+------+------+--------+
14 rows in set (0.00 sec)

3.salgrade表

CREATE TABLE salgrade(
grade INT (10) COMMENT '工资等级',
losal INT (10) COMMENT '最低限额',
hisal INT (10) COMMENT '最高限额'
);

INSERT INTO salgrade (grade, losal, hisal)VALUES (1, 700, 1200);
INSERT INTO salgrade (grade, losal, hisal)VALUES (2, 1201, 1400);
INSERT INTO salgrade (grade, losal, hisal)VALUES (3, 1401, 2000);
INSERT INTO salgrade (grade, losal, hisal)VALUES (4, 2001, 3000);
INSERT INTO salgrade (grade, losal, hisal)VALUES (5, 3001, 9999);

mysql> select * from salgrade;
+-------+-------+-------+
| grade | losal | hisal |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)

二、单表查询

1.选择部门30中的所有员工

mysql> select empno 编号,ename 姓名 from emp where deptno = 30;
+--------+-----------+
| 编号   | 姓名      |
+--------+-----------+
|   7499 | 刘博      |
|   7521 | 李兴      |
|   7654 | 刘浩      |
|   7698 | 刘涛      |
|   7844 | 马琪      |
|   7900 | 李小涵    |
+--------+-----------+
6 rows in set (0.00 sec)

2.列出所有办事员的姓名，编号和部门编号

mysql> select empno 编号,ename 姓名,deptno 部门号 from emp where job = '办事员';
+--------+--------+-----------+
| 编号   | 姓名   | 部门号    |
+--------+--------+-----------+
|   7369 | 张倩   |        20 |
|   7876 | 李涵   |        20 |
+--------+--------+-----------+
2 rows in set (0.01 sec)

3.找出佣金高于薪金的员工

mysql> select empno 编号,ename 姓名 from emp where comm > sal;
+--------+--------+
| 编号   | 姓名   |
+--------+--------+
|   7654 | 刘浩   |
+--------+--------+
1 row in set (0.00 sec)

4.找出没有佣金的员工

mysql> select empno 编号,ename 姓名,comm 佣金 from emp where comm is null;
+--------+-----------+--------+
| 编号   | 姓名       | 佣金    |
+--------+-----------+--------+
|   7369 | 张倩      |   NULL |
|   7566 | 李雷      |   NULL |
|   7698 | 刘涛      |   NULL |
|   7782 | 华仔      |   NULL |
|   7788 | 张飞      |   NULL |
|   7839 | 马晓云    |   NULL |
|   7876 | 李涵      |   NULL |
|   7900 | 李小涵    |   NULL |
|   7902 | 张三      |   NULL |
|   7934 | 张三丰    |   NULL |
+--------+-----------+--------+
10 rows in set (0.00 sec)

5.找出佣金高于薪金的60%的员工

mysql> select empno 编号,ename 姓名 from emp where comm > sal * 0.6;
+--------+--------+
| 编号   | 姓名   |
+--------+--------+
|   7654 | 刘浩   |
+--------+--------+
1 row in set (0.01 sec)

6.找出部门10中所有人事部长和部门20中所有办事员的详细资料

mysql> select * from emp where ((deptno = 10 and job = '人事部长') or (deptno = 20 AND job = '办事员'));
+-------+--------+--------------+------+------------+------+------+--------+
| empno | ename  | job          | mgr  | hiredate   | sal  | comm | deptno |
+-------+--------+--------------+------+------------+------+------+--------+
|  7782 | 华仔   | 人事部长     | 7839 | 1995-06-09 | 2450 | NULL |     10 |
|  7369 | 张倩   | 办事员       | 7902 | 2002-12-17 |  820 | NULL |     20 |
|  7876 | 李涵   | 办事员       | 7788 | 1997-05-23 | 1100 | NULL |     20 |
+-------+--------+--------------+------+------------+------+------+--------+
3 rows in set (0.00 sec)

7.找出收取佣金的员工

mysql> select empno 编号,ename 姓名,comm 佣金 from emp where comm is not null;
+--------+--------+--------+
| 编号   | 姓名   | 佣金   |
+--------+--------+--------+
|   7499 | 刘博   |    300 |
|   7521 | 李兴   |    500 |
|   7654 | 刘浩   |   1400 |
|   7844 | 马琪   |      0 |
+--------+--------+--------+
4 rows in set (0.00 sec)

8.找出不收取佣金或收取的佣金低于100的员工

mysql> select empno 编号,ename 姓名,comm 佣金 from emp where comm is null or comm < 100;
+--------+-----------+--------+
| 编号   | 姓名       | 佣金    |
+--------+-----------+--------+
|   7369 | 张倩      |   NULL |
|   7566 | 李雷      |   NULL |
|   7698 | 刘涛      |   NULL |
|   7782 | 华仔      |   NULL |
|   7788 | 张飞      |   NULL |
|   7839 | 马晓云    |   NULL |
|   7844 | 马琪      |      0 |
|   7876 | 李涵      |   NULL |
|   7900 | 李小涵    |   NULL |
|   7902 | 张三      |   NULL |
|   7934 | 张三丰    |   NULL |
+--------+-----------+--------+
11 rows in set (0.00 sec)

9.找出姓张的员工的信息

mysql> select * from emp where ename regexp '^张';
+-------+-----------+--------------+------+------------+------+------+--------+
| empno | ename     | job          | mgr  | hiredate   | sal  | comm | deptno |
+-------+-----------+--------------+------+------------+------+------+--------+
|  7369 | 张倩      | 办事员       | 7902 | 2002-12-17 |  820 | NULL |     20 |
|  7788 | 张飞      | 人事专员     | 7566 | 1998-04-19 | 3000 | NULL |     20 |
|  7902 | 张三      | 人事组长     | 7566 | 1992-10-08 | 3000 | NULL |     20 |
|  7934 | 张三丰    | 人事长       | 7782 | 1997-06-23 | 1300 | NULL |     10 |
+-------+-----------+--------------+------+------------+------+------+--------+
4 rows in set (0.04 sec)

10.显示员工的姓名和受雇日期,新的员工排在最前面

mysql> select ename 姓名,hiredate 受雇日期 from emp order by hiredate desc;
+-----------+--------------+
| 姓名      | 受雇日期       |
+-----------+--------------+
| 张倩      | 2002-12-17   |
| 张飞      | 1998-04-19   |
| 张三丰    | 1997-06-23   |
| 李涵      | 1997-05-23   |
| 刘涛      | 1997-05-01   |
| 马琪      | 1996-09-08   |
| 李兴      | 1995-07-22   |
| 华仔      | 1995-06-09   |
| 李小涵    | 1993-02-13   |
| 张三      | 1992-10-08   |
| 刘博      | 1992-02-20   |
| 马晓云    | 1991-11-17   |
| 刘浩      | 1991-09-28   |
| 李雷      | 1991-04-02   |
+-----------+--------------+
14 rows in set (0.00 sec)

11.按工作的降序排序,若工作相同则按薪金排序

mysql> select empno 编号,ename 姓名,job 工作,sal 薪资 from emp order by job desc,sal;
+--------+-----------+--------------+--------+
| 编号   | 姓名      | 工作         | 薪资     |
+--------+-----------+--------------+--------+
|   7698 | 刘涛      | 销售部长     |   2850 |
|   7900 | 李小涵    | 销售员       |    950 |
|   7839 | 马晓云    | 董事长       |   5000 |
|   7521 | 李兴      | 售货员       |   1250 |
|   7654 | 刘浩      | 售货员       |   1250 |
|   7844 | 马琪      | 售货员       |   1500 |
|   7499 | 刘博      | 售货员       |   1900 |
|   7369 | 张倩      | 办事员       |    820 |
|   7876 | 李涵      | 办事员       |   1100 |
|   7934 | 张三丰    | 人事长       |   1300 |
|   7566 | 李雷      | 人事部长     |    975 |
|   7782 | 华仔      | 人事部长     |   2450 |
|   7902 | 张三      | 人事组长     |   3000 |
|   7788 | 张飞      | 人事专员     |   3000 |
+--------+-----------+--------------+--------+
14 rows in set (0.00 sec)

12.查出不姓刘的员工的信息

mysql> select * from emp where ename regexp '^[^刘]';
+-------+-----------+--------------+------+------------+------+------+--------+
| empno | ename     | job          | mgr  | hiredate   | sal  | comm | deptno |
+-------+-----------+--------------+------+------------+------+------+--------+
|  7369 | 张倩      | 办事员       | 7902 | 2002-12-17 |  820 | NULL |     20 |
|  7521 | 李兴      | 售货员       | 7698 | 1995-07-22 | 1250 |  500 |     30 |
|  7566 | 李雷      | 人事部长     | 7839 | 1991-04-02 |  975 | NULL |     20 |
|  7782 | 华仔      | 人事部长     | 7839 | 1995-06-09 | 2450 | NULL |     10 |
|  7788 | 张飞      | 人事专员     | 7566 | 1998-04-19 | 3000 | NULL |     20 |
|  7839 | 马晓云    | 董事长       | NULL | 1991-11-17 | 5000 | NULL |     10 |
|  7844 | 马琪      | 售货员       | 7698 | 1996-09-08 | 1500 |    0 |     30 |
|  7876 | 李涵      | 办事员       | 7788 | 1997-05-23 | 1100 | NULL |     20 |
|  7900 | 李小涵    | 销售员       | 7698 | 1993-02-13 |  950 | NULL |     30 |
|  7902 | 张三      | 人事组长     | 7566 | 1992-10-08 | 3000 | NULL |     20 |
|  7934 | 张三丰    | 人事长       | 7782 | 1997-06-23 | 1300 | NULL |     10 |
+-------+-----------+--------------+------+------------+------+------+--------+
11 rows in set (0.00 sec)

13.取出姓李的员工

mysql> select empno 编号,ename 姓名 from emp where ename regexp '^李';
+--------+-----------+
| 编号   | 姓名      |
+--------+-----------+
|   7521 | 李兴      |
|   7566 | 李雷      |
|   7876 | 李涵      |
|   7900 | 李小涵    |
+--------+-----------+
4 rows in set (0.00 sec)

三、多表查询

1. 查询出每一位雇员的编号、姓名、职位、部门名称、位置

mysql> select empno 编号,ename 姓名,job 职位,dname 部门名称,loc 位置 from emp e inner join dept d on e.deptno = d.deptno;
+--------+-----------+--------------+--------------+--------------+
| 编号   | 姓名      | 职位         | 部门名称     | 位置            |
+--------+-----------+--------------+--------------+--------------+
|   7782 | 华仔      | 人事部长     | 财务部       | 高新四路     |
|   7839 | 马晓云    | 董事长       | 财务部       | 高新四路     |
|   7934 | 张三丰    | 人事长       | 财务部       | 高新四路     |
|   7369 | 张倩      | 办事员       | 人事部       | 科技二路     |
|   7566 | 李雷      | 人事部长     | 人事部       | 科技二路     |
|   7788 | 张飞      | 人事专员     | 人事部       | 科技二路     |
|   7876 | 李涵      | 办事员       | 人事部       | 科技二路     |
|   7902 | 张三      | 人事组长     | 人事部       | 科技二路     |
|   7499 | 刘博      | 售货员       | 销售部       | 长安区       |
|   7521 | 李兴      | 售货员       | 销售部       | 长安区       |
|   7654 | 刘浩      | 售货员       | 销售部       | 长安区       |
|   7698 | 刘涛      | 销售部长     | 销售部       | 长安区       |
|   7844 | 马琪      | 售货员       | 销售部       | 长安区       |
|   7900 | 李小涵    | 销售员       | 销售部       | 长安区       |
+--------+-----------+--------------+--------------+--------------+
14 rows in set (0.00 sec)

2.要求查询出每一位雇员的姓名、职位、领导的姓名

mysql> select a.ename 员工,a.job 职位,b.ename 领导 from emp a left join emp b on a.mgr = b.empno;
+-----------+--------------+-----------+
| 员工      | 职位         | 领导      |
+-----------+--------------+-----------+
| 张倩      | 办事员       | 张三      |
| 刘博      | 售货员       | 刘涛      |
| 李兴      | 售货员       | 刘涛      |
| 李雷      | 人事部长     | 马晓云    |
| 刘浩      | 售货员       | 刘涛      |
| 刘涛      | 销售部长     | 马晓云    |
| 华仔      | 人事部长     | 马晓云    |
| 张飞      | 人事专员     | 李雷      |
| 马晓云    | 董事长       | NULL      |
| 马琪      | 售货员       | 刘涛      |
| 李涵      | 办事员       | 张飞      |
| 李小涵    | 销售员       | 刘涛      |
| 张三      | 人事组长     | 李雷      |
| 张三丰    | 人事长       | 华仔      |
+-----------+--------------+-----------+
14 rows in set (0.00 sec)

3.查询出每个雇员的编号、姓名、基本工资、职位、领导的姓名、部门名称及位置。

mysql> select a.empno 编号,a.ename 姓名,a.sal 基本工资,a.job 职位,b.ename 领导,d.dname 部门名,d.loc 位置 from emp a 
    -> left join emp b on a.mgr = b.empno  
    -> inner join dept d on a.deptno = d.deptno;
+--------+-----------+--------------+--------------+-----------+-----------+--------------+
| 编号   | 姓名      | 基本工资     | 职位         | 领导      | 部门名    | 位置         |
+--------+-----------+--------------+--------------+-----------+-----------+--------------+
|   7782 | 华仔      |         2450 | 人事部长     | 马晓云    | 财务部    | 高新四路     |
|   7839 | 马晓云    |         5000 | 董事长       | NULL      | 财务部    | 高新四路     |
|   7934 | 张三丰    |         1300 | 人事长       | 华仔      | 财务部    | 高新四路     |
|   7369 | 张倩      |          820 | 办事员       | 张三      | 人事部    | 科技二路     |
|   7566 | 李雷      |          975 | 人事部长     | 马晓云    | 人事部    | 科技二路     |
|   7788 | 张飞      |         3000 | 人事专员     | 李雷      | 人事部    | 科技二路     |
|   7876 | 李涵      |         1100 | 办事员       | 张飞      | 人事部    | 科技二路     |
|   7902 | 张三      |         3000 | 人事组长     | 李雷      | 人事部    | 科技二路     |
|   7499 | 刘博      |         1900 | 售货员       | 刘涛      | 销售部    | 长安区       |
|   7521 | 李兴      |         1250 | 售货员       | 刘涛      | 销售部    | 长安区       |
|   7654 | 刘浩      |         1250 | 售货员       | 刘涛      | 销售部    | 长安区       |
|   7698 | 刘涛      |         2850 | 销售部长     | 马晓云    | 销售部    | 长安区       |
|   7844 | 马琪      |         1500 | 售货员       | 刘涛      | 销售部    | 长安区       |
|   7900 | 李小涵    |          950 | 销售员       | 刘涛      | 销售部    | 长安区       |
+--------+-----------+--------------+--------------+-----------+-----------+--------------+
14 rows in set (0.00 sec)

4.列出在部门"销售部"工作的员工的姓名，假定不知道销售部的部门编号。

mysql> select ename 姓名 from emp e inner join dept d on e.deptno = d.deptno where dname = '销售部';
+-----------+
| 姓名      |
+-----------+
| 刘博      |
| 李兴      |
| 刘浩      |
| 刘涛      |
| 马琪      |
| 李小涵    |
+-----------+
6 rows in set (0.00 sec)

5.列出与"李兴"从事相同工作的所有员工。

mysql> select ename 姓名 from emp where job = (select job from emp where ename = '李兴');
+--------+
| 姓名   |
+--------+
| 刘博   |
| 李兴   |
| 刘浩   |
| 马琪   |
+--------+
4 rows in set (0.05 sec)

6.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。

mysql> select ename 姓名,sal 工资 from emp where sal > (select max(sal) from emp where deptno = 30);
+-----------+--------+
| 姓名      | 工资   |
+-----------+--------+
| 张飞      |   3000 |
| 马晓云    |   5000 |
| 张三      |   3000 |
+-----------+--------+
3 rows in set (0.00 sec)

7.列出受雇日期早于其直接上级的所有员工。

mysql> select a.ename 姓名,a.hiredate 受雇日期 from emp a inner join emp b on a.mgr = b.empno where a.hiredate < b.hiredate;
+-----------+--------------+
| 姓名      | 受雇日期     |
+-----------+--------------+
| 刘博      | 1992-02-20   |
| 李兴      | 1995-07-22   |
| 李雷      | 1991-04-02   |
| 刘浩      | 1991-09-28   |
| 马琪      | 1996-09-08   |
| 李涵      | 1997-05-23   |
| 李小涵    | 1993-02-13   |
+-----------+--------------+
7 rows in set (0.00 sec)

四、分组函数查询

1.按照部门编号分组，求出每个部的人数，平均工资

mysql> select deptno 部门号,count(*) 人数,avg(sal) 平均工资 from emp group by deptno;
+-----------+--------+--------------+
| 部门号    | 人数   | 平均工资     |
+-----------+--------+--------------+
|        10 |      3 |    2916.6667 |
|        20 |      5 |    1779.0000 |
|        30 |      6 |    1616.6667 |
+-----------+--------+--------------+
3 rows in set (0.00 sec)

2.按照职位分组，求出每个职位的最高和最低工资

mysql> select job 职位,max(sal) 最高工资,min(sal) 最低工资 from emp group by job;
+--------------+--------------+--------------+
| 职位         | 最高工资     | 最低工资     |
+--------------+--------------+--------------+
| 办事员       |         1100 |          820 |
| 售货员       |         1900 |         1250 |
| 人事部长     |         2450 |          975 |
| 销售部长     |         2850 |         2850 |
| 人事专员     |         3000 |         3000 |
| 董事长       |         5000 |         5000 |
| 销售员       |          950 |          950 |
| 人事组长     |         3000 |         3000 |
| 人事长       |         1300 |         1300 |
+--------------+--------------+--------------+
9 rows in set (0.00 sec)

3.计算出每个各职位的平均工资

mysql> select job 职位,avg(sal) 平均工资 from emp group by job;
+--------------+--------------+
| 职位         | 平均工资     |
+--------------+--------------+
| 办事员       |     960.0000 |
| 售货员       |    1475.0000 |
| 人事部长     |    1712.5000 |
| 销售部长     |    2850.0000 |
| 人事专员     |    3000.0000 |
| 董事长       |    5000.0000 |
| 销售员       |     950.0000 |
| 人事组长     |    3000.0000 |
| 人事长       |    1300.0000 |
+--------------+--------------+
9 rows in set (0.00 sec)

4.查询出每个部门的名称、部门的人数、平均工资

mysql> select d.dname 部门,count(e.empno) 总人数,avg(sal) 平均工资 from dept d  left  join emp e on e.deptno = d.deptno group by d.dname;
+-----------+-----------+--------------+
| 部门      | 总人数    | 平均工资     |
+-----------+-----------+--------------+
| 财务部    |         3 |    2916.6667 |
| 人事部    |         5 |    1779.0000 |
| 销售部    |         6 |    1616.6667 |
| 运输部    |         0 |         NULL |
+-----------+-----------+--------------+
4 rows in set (0.00 sec)

5.要求统计出每个部门的详细信息，并且要求这些部门的平均工资高于2000；

mysql> select d.deptno 部门号,d.dname 部门,d.loc 位置,count(*) 人数,avg(sal) 平均工资 from emp e 
    -> inner join dept d on e.deptno = d.deptno group by d.deptno, d.dname, d.loc
    -> having avg(sal) > 2000;
+-----------+-----------+--------------+--------+--------------+
| 部门号    | 部门      | 位置         | 人数   | 平均工资     |
+-----------+-----------+--------------+--------+--------------+
|        10 | 财务部    | 高新四路     |      3 |    2916.6667 |
+-----------+-----------+--------------+--------+--------------+
1 row in set (0.00 sec)

6.要求查询出工资比华仔还要高的全部雇员信息

mysql> select * from emp where sal > (select sal from emp where ename = '华仔');
+-------+-----------+--------------+------+------------+------+------+--------+
| empno | ename     | job          | mgr  | hiredate   | sal  | comm | deptno |
+-------+-----------+--------------+------+------------+------+------+--------+
|  7698 | 刘涛      | 销售部长     | 7839 | 1997-05-01 | 2850 | NULL |     30 |
|  7788 | 张飞      | 人事专员     | 7566 | 1998-04-19 | 3000 | NULL |     20 |
|  7839 | 马晓云    | 董事长       | NULL | 1991-11-17 | 5000 | NULL |     10 |
|  7902 | 张三      | 人事组长     | 7566 | 1992-10-08 | 3000 | NULL |     20 |
+-------+-----------+--------------+------+------------+------+------+--------+
4 rows in set (0.00 sec)

7.要求查询出高于公司平均工资的全部雇员信息

mysql> select * from emp where sal > (select avg(sal) from emp);
+-------+-----------+--------------+------+------------+------+------+--------+
| empno | ename     | job          | mgr  | hiredate   | sal  | comm | deptno |
+-------+-----------+--------------+------+------------+------+------+--------+
|  7698 | 刘涛      | 销售部长     | 7839 | 1997-05-01 | 2850 | NULL |     30 |
|  7782 | 华仔      | 人事部长     | 7839 | 1995-06-09 | 2450 | NULL |     10 |
|  7788 | 张飞      | 人事专员     | 7566 | 1998-04-19 | 3000 | NULL |     20 |
|  7839 | 马晓云    | 董事长       | NULL | 1991-11-17 | 5000 | NULL |     10 |
|  7902 | 张三      | 人事组长     | 7566 | 1992-10-08 | 3000 | NULL |     20 |
+-------+-----------+--------------+------+------------+------+------+--------+
5 rows in set (0.00 sec)

8.查询出每个部门的编号、名称、位置、部门人数、平均工资

mysql> select d.deptno 部门号,dname 部门,loc 位置,count(*) 人数,avg(sal) 平均工资 from emp e 
	-> inner join dept d on e.deptno = d.deptno group by d.deptno,d.dname,d.loc;
+-----------+-----------+--------------+--------+--------------+
| 部门号    | 部门      | 位置         | 人数   | 平均工资     |
+-----------+-----------+--------------+--------+--------------+
|        20 | 人事部    | 科技二路     |      5 |    1779.0000 |
|        30 | 销售部    | 长安区       |      6 |    1616.6667 |
|        10 | 财务部    | 高新四路     |      3 |    2916.6667 |
+-----------+-----------+--------------+--------+--------------+
3 rows in set (0.00 sec)

9.列出至少有一个员工的所有部门。

mysql> select deptno 部门号, dname 部门, loc 位置 from dept where deptno in 
	-> (select deptno from emp group by deptno having count(*) > 0);
+-----------+-----------+--------------+
| 部门号    | 部门      | 位置         |
+-----------+-----------+--------------+
|        10 | 财务部    | 高新四路     |
|        20 | 人事部    | 科技二路     |
|        30 | 销售部    | 长安区       |
+-----------+-----------+--------------+
3 rows in set (0.00 sec)

五、等级查询

1.查询每个工资等级各有多少员工

mysql> select grade 工资等级,count(*) 人数 from salgrade s inner join emp e on s.losal <= e.sal and e.sal <= s.hisal  group by grade;
+--------------+--------+
| 工资等级     | 人数   |
+--------------+--------+
|            1 |      4 |
|            3 |      2 |
|            2 |      3 |
|            4 |      4 |
|            5 |      1 |
+--------------+--------+
5 rows in set (0.00 sec)

2.查询部门中（所有人）的平均工资等级

mysql> select deptno 部门号,avg(grade) 平均工资等级 from emp inner join salgrade on emp.sal between salgrade.losal and salgrade.hisal
    -> group by deptno;
+-----------+--------------------+
| 部门号    | 平均工资等级       |
+-----------+--------------------+
|        20 |             2.2000 |
|        30 |             2.5000 |
|        10 |             3.6667 |
+-----------+--------------------+
3 rows in set (0.00 sec)

文章来源:https://blog.csdn.net/weixin_72583321/article/details/135714921
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：chenni525@qq.com进行投诉反馈，一经查实，立即删除！