1. Johngo学长首页
  2. 技术点详细复盘专题
  3. 数据库

mysql 常用的命令2

• 数据库 • 阅读 71
  1. 找出每个部门，不同工作岗位的最高薪资。
    mysql> select deptno,job,max(sal) from emp group by deptno,job;
    +——–+———–+———-+
    | deptno | job | max(sal) |
    +——–+———–+———-+
    | 10 | CLERK | 1300.00 |
    | 10 | MANAGER | 2450.00 |
    | 10 | PRESIDENT | 5000.00 |
    | 20 | ANALYST | 3000.00 |
    | 20 | CLERK | 1100.00 |
    | 20 | MANAGER | 2975.00 |
    | 30 | CLERK | 950.00 |
    | 30 | MANAGER | 2850.00 |
    | 30 | SALESMAN | 1600.00 |
    +——–+———–+———-+
    9 rows in set (0.01 sec)
  2. 每个部门最高薪资
    mysql> select deptno, max(sal) from emp group by deptno;
    +——–+———-+
    | deptno | max(sal) |
    +——–+———-+
    | 10 | 5000.00 |
    | 20 | 3000.00 |
    | 30 | 2850.00 |
    +——–+———-+
    3 rows in set (0.00 sec)
  3. 找出每个部门最高薪资，并且查出其中大于2900的
    mysql> select deptno,max(sal) from emp where sal > 2900 group by deptno;
    +——–+———-+
    | deptno | max(sal) | 这样写效率较高
    +——–+———-+
    | 10 | 5000.00 |
    | 20 | 3000.00 |
    +——–+———-+
    2 rows in set (0.00 sec) mysql> select deptno, max(sal) from emp group by deptno having max(sal) > 2900;
    +——–+———-+
    | deptno | max(sal) | 使用having效率较低
    +——–+———-+ having是对于分组后的过滤，只能联合使用，不能单独拿出来使用。
    | 10 | 5000.00 |
    | 20 | 3000.00 |
    +——–+———-+
    2 rows in set (0.00 sec)
  4. 找出每个部门的平均薪资 mysql> select deptno,avg(sal) from emp group by deptno;
    +——–+————-+
    | deptno | avg(sal) |
    +——–+————-+
    | 10 | 2916.666667 |
    | 20 | 2175.000000 |
    | 30 | 1566.666667 |
    +——–+————-+
    3 rows in set (0.00 sec)
  5. 先找出每个部门的平均薪资，要求显示其中大于2000的。
    mysql> select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;
    +——–+————-+
    | deptno | avg(sal) |
    +——–+————-+
    | 10 | 2916.666667 |
    | 20 | 2175.000000 |
    +——–+————-+
    2 rows in set (0.00 sec)
  6. distinct可以去除重复字段，出现在字段最前面，去除所有字段重复。
    mysql> select job from emp;
    +———–+
    | job |
    +———–+
    | CLERK |
    | SALESMAN |
    | SALESMAN |
    | MANAGER |
    | SALESMAN |
    | MANAGER |
    | MANAGER |
    | ANALYST |
    | PRESIDENT |
    | SALESMAN |
    | CLERK |
    | CLERK |
    | ANALYST |
    | CLERK |
    +———–+
    14 rows in set (0.00 sec) mysql> select distinct job from emp;
    +———–+
    | job |
    +———–+
    | CLERK |
    | SALESMAN |
    | MANAGER |
    | ANALYST |
    | PRESIDENT |
    +———–+
    5 rows in set (0.00 sec) mysql> select deptno,job from emp;
    +——–+———–+
    | deptno | job |
    +——–+———–+
    | 20 | CLERK |
    | 30 | SALESMAN |
    | 30 | SALESMAN |
    | 20 | MANAGER |
    | 30 | SALESMAN |
    | 30 | MANAGER |
    | 10 | MANAGER |
    | 20 | ANALYST |
    | 10 | PRESIDENT |
    | 30 | SALESMAN |
    | 20 | CLERK |
    | 30 | CLERK |
    | 20 | ANALYST |
    | 10 | CLERK |
    +——–+———–+
    14 rows in set (0.00 sec) mysql> select distinct deptno,job from emp;
    +——–+———–+
    | deptno | job |
    +——–+———–+
    | 20 | CLERK |
    | 30 | SALESMAN |
    | 20 | MANAGER |
    | 30 | MANAGER |
    | 10 | MANAGER |
    | 20 | ANALYST |
    | 10 | PRESIDENT |
    | 30 | CLERK |
    | 10 | CLERK |
    +——–+———–+
    9 rows in set (0.00 sec)
  7. 统计岗位的数量
    mysql> select count( distinct job) from emp;
    +———————-+
    | count( distinct job) |
    +———————-+
    | 5 |
    +———————-+
    1 row in set (0.00 sec)
  8. 查询员工名称和部门名称，使用内连接查询。
    mysql> select e.ename,d.dname from emp e inner join dept d on e.deptno = d.deptno;
    +——–+————+
    | ename | dname |
    +——–+————+
    | CLARK | ACCOUNTING |
    | KING | ACCOUNTING |
    | MILLER | ACCOUNTING |
    | SMITH | RESEARCH |
    | JONES | RESEARCH |
    | SCOTT | RESEARCH |
    | ADAMS | RESEARCH |
    | FORD | RESEARCH |
    | ALLEN | SALES |
    | WARD | SALES |
    | MARTIN | SALES |
    | BLAKE | SALES |
    | TURNER | SALES |
    | JAMES | SALES |
    +——–+————+
    14 rows in set (0.01 sec)
  9. 查询员工的薪资等级，显示员工名，薪资，薪资等级
    mysql> select e.ename,e.sal,s.grade from emp e inner join salgrade s on e.sal between s.losal and s.hisal;
    +——–+———+——-+
    | ename | sal | grade |
    +——–+———+——-+
    | SMITH | 800.00 | 1 |
    | ALLEN | 1600.00 | 3 |
    | WARD | 1250.00 | 2 |
    | JONES | 2975.00 | 4 |
    | MARTIN | 1250.00 | 2 |
    | BLAKE | 2850.00 | 4 |
    | CLARK | 2450.00 | 4 |
    | SCOTT | 3000.00 | 4 |
    | KING | 5000.00 | 5 |
    | TURNER | 1500.00 | 3 |
    | ADAMS | 1100.00 | 1 |
    | JAMES | 950.00 | 1 |
    | FORD | 3000.00 | 4 |
    | MILLER | 1300.00 | 2 |
    +——–+———+——-+
    14 rows in set (0.01 sec)
  10. 查询每个员工的上级，要求显示员工，上级的名字，
    这里使用自联接，一个表被视为两个表，一个雇员表和一个上级表。
    [En]

    Self-join is used here, and a table is treated as two tables, an employee table and a superior table.

mysql> select e1.ename,e2.ename as shangji from emp e1 join emp e2 on e1.mgr = e2.empno;
+——–+———+
| ename | shangji |
+——–+———+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+——–+———+
13 rows in set (0.01 sec)

内连接，A,B关系平等
外连接，A,B有主副之分，左连接（左边是主），右连接（右边是主表）。
  1. 与内连接不同，外连接必须把主表中每一个员工的上级查询出来
    例如king没有上级，赋值为null，这里主表是员工表，附表是上级表
    mysql> select e1.ename as ‘员工’ ,e2.ename as ‘上级’ from emp e1 left join emp e2 on e1.mgr = e2.empno;
    +——–+——-+
    | 员工 | 上级 |
    +——–+——-+
    | SMITH | FORD |
    | ALLEN | BLAKE | 左外连接
    | WARD | BLAKE |
    | JONES | KING |
    | MARTIN | BLAKE |
    | BLAKE | KING |
    | CLARK | KING |
    | SCOTT | JONES |
    | KING | NULL |
    | TURNER | BLAKE |
    | ADAMS | SCOTT |
    | JAMES | BLAKE |
    | FORD | JONES |
    | MILLER | CLARK |
    +——–+——-+
    14 rows in set (0.00 sec)

mysql> select e1.ename as ‘员工’ ,e2.ename as ‘上级’ from emp e2 right join emp e1 on e1.mgr = e2.empno;
+——–+——-+
| 员工 | 上级 |
+——–+——-+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE | 右外连接，注意两张表的位置与left和right对应，即使要正确确认主表和附表。
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| KING | NULL |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+——–+——-+
14 rows in set (0.00 sec)

12.查询那个部门没有员工。
mysql> select e.ename, d.* from emp e right join dept d on e.deptno = d.deptno;
+——–+——–+————+———-+
| ename | DEPTNO | DNAME | LOC |
+——–+——–+————+———-+
| CLARK | 10 | ACCOUNTING | NEW YORK |
| KING | 10 | ACCOUNTING | NEW YORK |
| MILLER | 10 | ACCOUNTING | NEW YORK |
| SMITH | 20 | RESEARCH | DALLAS |
| JONES | 20 | RESEARCH | DALLAS |
| SCOTT | 20 | RESEARCH | DALLAS |
| ADAMS | 20 | RESEARCH | DALLAS |
| FORD | 20 | RESEARCH | DALLAS |
| ALLEN | 30 | SALES | CHICAGO |
| WARD | 30 | SALES | CHICAGO |
| MARTIN | 30 | SALES | CHICAGO |
| BLAKE | 30 | SALES | CHICAGO |
| TURNER | 30 | SALES | CHICAGO |
| JAMES | 30 | SALES | CHICAGO |
| NULL | 40 | OPERATIONS | BOSTON |
+——–+——–+————+———-+
15 rows in set (0.00 sec)

mysql> select e.ename, d.* from emp e right join dept d on e.deptno = d.deptno where e.ename is null;
+——-+——–+————+——–+
| ename | DEPTNO | DNAME | LOC |
+——-+——–+————+——–+
| NULL | 40 | OPERATIONS | BOSTON |
+——-+——–+————+——–+
1 row in set (0.00 sec)

  1. 查询每个员工的薪资等级和部门名称
    mysql> select e.ename , d.dname , s.grade from emp e join dept d join salgrade s on e.deptno = d.deptno and e.sal between s.losal and s.hisal;
    +——–+————+——-+
    | ename | dname | grade |
    +——–+————+——-+
    | SMITH | RESEARCH | 1 |
    | ALLEN | SALES | 3 |
    | WARD | SALES | 2 |
    | JONES | RESEARCH | 4 |
    | MARTIN | SALES | 2 |
    | BLAKE | SALES | 4 |
    | CLARK | ACCOUNTING | 4 |
    | SCOTT | RESEARCH | 4 |
    | KING | ACCOUNTING | 5 |
    | TURNER | SALES | 3 |
    | ADAMS | RESEARCH | 1 |
    | JAMES | SALES | 1 |
    | FORD | RESEARCH | 4 |
    | MILLER | ACCOUNTING | 2 |
    +——–+————+——-+
    14 rows in set (0.00 sec) select
    e.ename,d.dname,s.grade
    from
    emp e 这里是三张表查询。先让emp 和 dept结合
    join 然后让emp 和 salgrade结合查询。
    dept d
    on
    e.deptno = d.deptno
    join
    salgrade s
    on
    e.sal between s.losal and hisal; +——–+————+——-+
    | ename | dname | grade |
    +——–+————+——-+
    | SMITH | RESEARCH | 1 |
    | ALLEN | SALES | 3 |
    | WARD | SALES | 2 |
    | JONES | RESEARCH | 4 |
    | MARTIN | SALES | 2 |
    | BLAKE | SALES | 4 |
    | CLARK | ACCOUNTING | 4 |
    | SCOTT | RESEARCH | 4 |
    | KING | ACCOUNTING | 5 |
    | TURNER | SALES | 3 |
    | ADAMS | RESEARCH | 1 |
    | JAMES | SALES | 1 |
    | FORD | RESEARCH | 4 |
    | MILLER | ACCOUNTING | 2 |
    +——–+————+——-+
    14 rows in set (0.00 sec)
  2. 查询每个员工的部门名称，工资等级和上级领导 select
    e1.ename as ‘员工’,d.dname,s.grade,e2.ename as ‘上级’
    from
    emp e1
    join
    dept d
    on
    e1.deptno = d.deptno
    join
    salgrade s
    on
    e1.sal between s.losal and s.hisal
    left join 这里注意的是left放在后面才能起作用。
    emp e2
    on
    e1.mgr = e2.empno;
+--------+------------+-------+-------+

| 员工 | dname | grade | 上级 |
+——–+————+——-+——-+
| SMITH | RESEARCH | 1 | FORD |
| ALLEN | SALES | 3 | BLAKE |
| WARD | SALES | 2 | BLAKE |
| JONES | RESEARCH | 4 | KING |
| MARTIN | SALES | 2 | BLAKE |
| BLAKE | SALES | 4 | KING |
| CLARK | ACCOUNTING | 4 | KING |
| SCOTT | RESEARCH | 4 | JONES |
| KING | ACCOUNTING | 5 | NULL |
| TURNER | SALES | 3 | BLAKE |
| ADAMS | RESEARCH | 1 | SCOTT |
| JAMES | SALES | 1 | BLAKE |
| FORD | RESEARCH | 4 | JONES |
| MILLER | ACCOUNTING | 2 | CLARK |
+——–+————+——-+——-+
14 rows in set (0.00 sec)

Original: https://www.cnblogs.com/journeyhch/p/15553194.html
Author: journeyhch
Title: mysql 常用的命令2

原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/508044/

转载文章受原作者版权保护。转载请注明原作者出处！

赞 (0)
0

【自取】最近整理的，有需要可以领取学习：



Linux核心资料大放送~

全栈面试题汇总（持续更新&可下载）

一个提高学习100%效率的工具！

【超详细】深度学习面试题目！

LeetCode Python刷题答案下载！

LeetCode Java版刷题答案下载！

LeetCode C++ 版本，抓紧保存！

LeetCode GO语言 刷题答案下载！

大家都在看

亲爱的 Coder【最近整理，可免费获取】👉 最新必读书单  | 👏 面试题下载  | 🌎 免费的AI知识星球