查询工资高于500或者岗位为MANAGER的雇员,同时还要满足他们的姓名首字母为大写的J
mysql> select *from emp;
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 |
| 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 |
| 007521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 |
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 |
| 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 |
| 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 |
| 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 |
| 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 |
| 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 |
| 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 |
| 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
| 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 |
| 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.00 sec)
mysql> select *from emp where sal>500 or job='MANAGER';
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 |
| 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 |
| 007521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 |
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 |
| 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 |
| 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 |
| 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 |
| 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 |
| 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 |
| 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 |
| 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
| 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 |
| 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.00 sec)
mysql> select *from emp where sal>500 and job='MANAGER';
+--------+-------+---------+------+---------------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+-------+---------+------+---------------------+---------+------+--------+
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 |
| 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 |
+--------+-------+---------+------+---------------------+---------+------+--------+
3 rows in set (0.00 sec)
mysql> select *from emp where (sal>500 or job='MANAGER') and ename like 'J%';
+--------+-------+---------+------+---------------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+-------+---------+------+---------------------+---------+------+--------+
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
+--------+-------+---------+------+---------------------+---------+------+--------+
2 rows in set (0.00 sec)
mysql> select *from emp where (sal>500 or job='MANAGER') and substring(ename,1,1)='J';
+--------+-------+---------+------+---------------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+-------+---------+------+---------------------+---------+------+--------+
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
+--------+-------+---------+------+---------------------+---------+------+--------+
2 rows in set (0.00 sec)
mysql> select *from emp where (sal>500 or job='MANAGER') and substring(ename,1,2)='JA';
+--------+-------+-------+------+---------------------+--------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+-------+-------+------+---------------------+--------+------+--------+
| 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
+--------+-------+-------+------+---------------------+--------+------+--------+
1 row in set (0.00 sec)
mysql>
按照部门号升序而雇员的工资降序排列
mysql> select *from emp order by deptno asc ,sal desc;
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 |
| 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 |
| 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 |
| 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 |
| 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 |
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 |
| 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 |
| 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 |
| 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 |
| 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 |
| 007521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 |
| 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 |
| 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.00 sec)
mysql> select ename, sal, deptno from emp order by deptno asc ,sal desc;
+--------+---------+--------+
| ename | sal | deptno |
+--------+---------+--------+
| KING | 5000.00 | 10 |
| CLARK | 2450.00 | 10 |
| MILLER | 1300.00 | 10 |
| SCOTT | 3000.00 | 20 |
| FORD | 3000.00 | 20 |
| JONES | 2975.00 | 20 |
| ADAMS | 1100.00 | 20 |
| SMITH | 800.00 | 20 |
| BLAKE | 2850.00 | 30 |
| ALLEN | 1600.00 | 30 |
| TURNER | 1500.00 | 30 |
| WARD | 1250.00 | 30 |
| MARTIN | 1250.00 | 30 |
| JAMES | 950.00 | 30 |
+--------+---------+--------+
14 rows in set (0.00 sec)
mysql>
按照年薪降序排列
mysql> select *from emp where ename='SMITH';
+--------+-------+-------+------+---------------------+--------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+-------+-------+------+---------------------+--------+------+--------+
| 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 |
+--------+-------+-------+------+---------------------+--------+------+--------+
1 row in set (0.00 sec)
mysql> select sal*12 年薪 from emp where name='SMITH';
ERROR 1054 (42S22): Unknown column 'name' in 'where clause'
mysql> select sal*12 年薪 from emp where ename='SMITH';
+---------+
| 年薪 |
+---------+
| 9600.00 |
+---------+
1 row in set (0.01 sec)
mysql> select sal*12+comm 年薪 from emp where ename='SMITH';
+--------+
| 年薪 |
+--------+
| NULL |
+--------+
1 row in set (0.00 sec)
mysql> select sal*12+ifnull(comm,0) 年薪 from emp where ename='SMITH';
+---------+
| 年薪 |
+---------+
| 9600.00 |
+---------+
1 row in set (0.01 sec)
mysql> select sal*12+ifnull(comm,0) 年薪 from emp ;
+----------+
| 年薪 |
+----------+
| 9600.00 |
| 19500.00 |
| 15500.00 |
| 35700.00 |
| 16400.00 |
| 34200.00 |
| 29400.00 |
| 36000.00 |
| 60000.00 |
| 18000.00 |
| 13200.00 |
| 11400.00 |
| 36000.00 |
| 15600.00 |
+----------+
14 rows in set (0.00 sec)
mysql> select ename,sal,comm,sal*12+ifnull(comm,0) 年薪 from emp ;
+--------+---------+---------+----------+
| ename | sal | comm | 年薪 |
+--------+---------+---------+----------+
| SMITH | 800.00 | NULL | 9600.00 |
| ALLEN | 1600.00 | 300.00 | 19500.00 |
| WARD | 1250.00 | 500.00 | 15500.00 |
| JONES | 2975.00 | NULL | 35700.00 |
| MARTIN | 1250.00 | 1400.00 | 16400.00 |
| BLAKE | 2850.00 | NULL | 34200.00 |
| CLARK | 2450.00 | NULL | 29400.00 |
| SCOTT | 3000.00 | NULL | 36000.00 |
| KING | 5000.00 | NULL | 60000.00 |
| TURNER | 1500.00 | 0.00 | 18000.00 |
| ADAMS | 1100.00 | NULL | 13200.00 |
| JAMES | 950.00 | NULL | 11400.00 |
| FORD | 3000.00 | NULL | 36000.00 |
| MILLER | 1300.00 | NULL | 15600.00 |
+--------+---------+---------+----------+
14 rows in set (0.00 sec)
mysql> select ename,sal,comm,sal*12+ifnull(comm,0) 年薪 from emp order by 年薪;
+--------+---------+---------+----------+
| ename | sal | comm | 年薪 |
+--------+---------+---------+----------+
| SMITH | 800.00 | NULL | 9600.00 |
| JAMES | 950.00 | NULL | 11400.00 |
| ADAMS | 1100.00 | NULL | 13200.00 |
| WARD | 1250.00 | 500.00 | 15500.00 |
| MILLER | 1300.00 | NULL | 15600.00 |
| MARTIN | 1250.00 | 1400.00 | 16400.00 |
| TURNER | 1500.00 | 0.00 | 18000.00 |
| ALLEN | 1600.00 | 300.00 | 19500.00 |
| CLARK | 2450.00 | NULL | 29400.00 |
| BLAKE | 2850.00 | NULL | 34200.00 |
| JONES | 2975.00 | NULL | 35700.00 |
| SCOTT | 3000.00 | NULL | 36000.00 |
| FORD | 3000.00 | NULL | 36000.00 |
| KING | 5000.00 | NULL | 60000.00 |
+--------+---------+---------+----------+
14 rows in set (0.00 sec)
mysql> select ename,sal,comm,sal*12+ifnull(comm,0) 年薪 from emp order by 年薪 desc;
+--------+---------+---------+----------+
| ename | sal | comm | 年薪 |
+--------+---------+---------+----------+
| KING | 5000.00 | NULL | 60000.00 |
| SCOTT | 3000.00 | NULL | 36000.00 |
| FORD | 3000.00 | NULL | 36000.00 |
| JONES | 2975.00 | NULL | 35700.00 |
| BLAKE | 2850.00 | NULL | 34200.00 |
| CLARK | 2450.00 | NULL | 29400.00 |
| ALLEN | 1600.00 | 300.00 | 19500.00 |
| TURNER | 1500.00 | 0.00 | 18000.00 |
| MARTIN | 1250.00 | 1400.00 | 16400.00 |
| MILLER | 1300.00 | NULL | 15600.00 |
| WARD | 1250.00 | 500.00 | 15500.00 |
| ADAMS | 1100.00 | NULL | 13200.00 |
| JAMES | 950.00 | NULL | 11400.00 |
| SMITH | 800.00 | NULL | 9600.00 |
+--------+---------+---------+----------+
14 rows in set (0.00 sec)
mysql>
显示工资最高的员工的姓名和工作岗位
mysql> select max(sal) from emp;
+----------+
| max(sal) |
+----------+
| 5000.00 |
+----------+
1 row in set (0.00 sec)
mysql> select *from emp where sal=5000;
+--------+-------+-----------+------+---------------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+-------+-----------+------+---------------------+---------+------+--------+
| 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 |
+--------+-------+-----------+------+---------------------+---------+------+--------+
1 row in set (0.01 sec)
mysql> select *from emp where sal=(select max(sal) from emp);
+--------+-------+-----------+------+---------------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+-------+-----------+------+---------------------+---------+------+--------+
| 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 |
+--------+-------+-----------+------+---------------------+---------+------+--------+
1 row in set (0.02 sec)
mysql> select ename, job from emp where sal=(select max(sal) from emp);
+-------+-----------+
| ename | job |
+-------+-----------+
| KING | PRESIDENT |
+-------+-----------+
1 row in set (0.00 sec)
mysql>
显示工资高于平均工资的员工信息
mysql> select avg(sal) from emp;
+-------------+
| avg(sal) |
+-------------+
| 2073.214286 |
+-------------+
1 row in set (0.00 sec)
mysql> select *from emp where sal>(select avg(sal) from emp);
+--------+-------+-----------+------+---------------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+-------+-----------+------+---------------------+---------+------+--------+
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 |
| 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 |
| 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 |
| 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 |
| 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 |
+--------+-------+-----------+------+---------------------+---------+------+--------+
6 rows in set (0.00 sec)
mysql>
显示每个部门的平均工资和最高工资
mysql> select max(sal),avg(sal) from emp;
+----------+-------------+
| max(sal) | avg(sal) |
+----------+-------------+
| 5000.00 | 2073.214286 |
+----------+-------------+
1 row in set (0.00 sec)
mysql> select max(sal),avg(sal) from emp group by deptno;
+----------+-------------+
| max(sal) | avg(sal) |
+----------+-------------+
| 5000.00 | 2916.666667 |
| 3000.00 | 2175.000000 |
| 2850.00 | 1566.666667 |
+----------+-------------+
3 rows in set (0.00 sec)
mysql> select max(sal) 最高,avg(sal) 平均 from emp group by deptno;
+---------+-------------+
| 最高 | 平均 |
+---------+-------------+
| 5000.00 | 2916.666667 |
| 3000.00 | 2175.000000 |
| 2850.00 | 1566.666667 |
+---------+-------------+
3 rows in set (0.00 sec)
mysql> select max(sal) 最高,format(avg(sal),2) 平均 from emp group by deptno;
+---------+----------+
| 最高 | 平均 |
+---------+----------+
| 5000.00 | 2,916.67 |
| 3000.00 | 2,175.00 |
| 2850.00 | 1,566.67 |
+---------+----------+
3 rows in set (0.00 sec)
mysql> select format(max(sal),2) 最高,format(avg(sal),2) 平均 from emp group by deptno;
+----------+----------+
| 最高 | 平均 |
+----------+----------+
| 5,000.00 | 2,916.67 |
| 3,000.00 | 2,175.00 |
| 2,850.00 | 1,566.67 |
+----------+----------+
3 rows in set (0.00 sec)
mysql>
显示平均工资低于200的部门号和他的平均工资
mysql> select deptno, avg(sal) 平均工资 from emp group by deptno having 平均工资<2000;
+--------+--------------+
| deptno | 平均工资 |
+--------+--------------+
| 30 | 1566.666667 |
+--------+--------------+
1 row in set (0.00 sec)
mysql> select deptno 部门号, avg(sal) 平均工资 from emp group by deptno having 平均工资<2000;
+-----------+--------------+
| 部门号 | 平均工资 |
+-----------+--------------+
| 30 | 1566.666667 |
+-----------+--------------+
1 row in set (0.00 sec)
mysql>
显示每种岗位的雇员总数,平均工资
mysql> select count(*) from emp;
+----------+
| count(*) |
+----------+
| 14 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from emp group by job;
+----------+
| count(*) |
+----------+
| 2 |
| 4 |
| 3 |
| 1 |
| 4 |
+----------+
5 rows in set (0.00 sec)
mysql> select job,count(*) from emp group by job;
+-----------+----------+
| job | count(*) |
+-----------+----------+
| ANALYST | 2 |
| CLERK | 4 |
| MANAGER | 3 |
| PRESIDENT | 1 |
| SALESMAN | 4 |
+-----------+----------+
5 rows in set (0.00 sec)
mysql> select job,count(*) 人数,avg(sal) 平均工资 from emp group by job;
+-----------+--------+--------------+
| job | 人数 | 平均工资 |
+-----------+--------+--------------+
| ANALYST | 2 | 3000.000000 |
| CLERK | 4 | 1037.500000 |
| MANAGER | 3 | 2758.333333 |
| PRESIDENT | 1 | 5000.000000 |
| SALESMAN | 4 | 1400.000000 |
+-----------+--------+--------------+
5 rows in set (0.00 sec)
mysql> select job,count(*) 人数,format(avg(sal),2) 平均工资 from emp group by job;
+-----------+--------+--------------+
| job | 人数 | 平均工资 |
+-----------+--------+--------------+
| ANALYST | 2 | 3,000.00 |
| CLERK | 4 | 1,037.50 |
| MANAGER | 3 | 2,758.33 |
| PRESIDENT | 1 | 5,000.00 |
| SALESMAN | 4 | 1,400.00 |
+-----------+--------+--------------+
5 rows in set (0.00 sec)
mysql>
结尾
最后,感谢您阅读我的文章,希望这些内容能够对您有所启发和帮助。如果您有任何问题或想要分享您的观点,请随时在评论区留言。
同时,不要忘记订阅我的博客以获取更多有趣的内容。在未来的文章中,我将继续探讨这个话题的不同方面,为您呈现更多深度和见解。
谢谢您的支持,期待与您在下一篇文章中再次相遇!