sql查询语句练习

最新推荐文章于 2024-07-18 15:32:08 发布

nadoudou123

最新推荐文章于 2024-07-18 15:32:08 发布

阅读量116

点赞数

分类专栏：个人练习文章标签： sql mysql

本文链接：https://blog.csdn.net/nadoudou123/article/details/119579021

版权

个人练习专栏收录该内容

7 篇文章 0 订阅

订阅专栏

mysql> select * from emp;
+-------+--------+-----------+------+------------+------+------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal  | comm | deptno |
+-------+--------+-----------+------+------------+------+------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800 | NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600 |  300 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250 |  500 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975 | NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250 | 1400 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850 | NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450 | NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-07-03 | 3000 | NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500 |    0 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-07-13 | 1100 | NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950 | NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000 | NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1981-01-23 | 1300 | NULL |     10 |
+-------+--------+-----------+------+------------+------+------+--------+

mysql> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+


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

连接查询（三张表连接）

找出每个员工的部门名称以及工资等级，要求显示员工名、部门名、薪资、薪资等级

select e.ename,d.dname,e.sal,s.grade 
    FROM emp e 
JOIN 
    dept d 
on 
    e.deptno = d.deptno 
join 
    salgrade s 
on 
    e.sal BETWEEN	s.losal and s.hisal

找出每个员工的部门名称以及工资等级还有上级领导，要求显示员工名、领导名、部门名、薪资、薪资等级
SELECT e.ename,e.sal,d.dname,s.grade,p.ename as leadername from emp e JOIN dept d	on e.deptno = d.deptno JOIN  salgrade s on sal BETWEEN s.losal and s.hisal LEFT JOIN emp p on e.mgr = p.empno