distinct 去重
关于查询结果集的去重
distinct
mysql> SELECT distinct job from EMP;
+-----------+
| job |
+-----------+
| CLERK |
| SALESMAN |
| MANAGER |
| ANALYST |
| PRESIDENT |
+-----------+
5 rows in set (0.00 sec)
统计岗位的数量
mysql> select count(distinct job) from EMP;
+---------------------+
| count(distinct job) |
+---------------------+
| 5 |
+---------------------+
1 row in set (0.00 sec)
连接查询
什么是连接查询
在实际开发中,大部分的情况都不是从一张表中查询数据,一般都是多张表联合查询去出最终的结果
找出每一个员工的部门名称,要求显示员工名和部门名
会用到 别名
- 别名有什么好处?
- 第一:执行效率高
- 第二:可读性好
mysql> select e.ename,e.deptno from EMP e ,DEPT d where e.deptno = d.deptno;
+--------+--------+
| ename | deptno |
+--------+--------+
| CLARK | 10 |
| KING | 10 |
| MILLER | 10 |
| SMITH | 20 |
| JONES | 20 |
| SCOTT | 20 |
| ADAMS | 20 |
| FORD | 20 |
| ALLEN | 30 |
| WARD | 30 |
| MARTIN | 30 |
| BLAKE | 30 |
| TURNER | 30 |
| JAMES | 30 |
+--------+--------+
14 rows in set (0.00 sec)
内连接之等值连接
最大特点是:条件是等量关系
查询每个员工的部门名称,要求显示员工名和部门名
1、找出每一个员工的部门名称,要求显示员工名和部门名
表A join 表B ... on(连接条件)
好处:连接条件后面可以加where判断
mysql> select e.ename,d.deptno from EMP e join DEPT d on e.deptno = d.deptno;
+--------+--------+
| ename | deptno |
+--------+--------+
| CLARK | 10 |
| KING | 10 |
| MILLER | 10 |
| SMITH | 20 |
| JONES | 20 |
| SCOTT | 20 |
| ADAMS | 20 |
| FORD | 20 |
| ALLEN | 30 |
| WARD | 30 |
| MARTIN | 30 |
| BLAKE | 30 |
| TURNER | 30 |
| JAMES | 30 |
+--------+--------+
14 rows in set (0.00 sec)
---------------------------------加上where---------------------------
mysql> select e.ename,d.deptno from EMP e join DEPT d on e.deptno = d.deptno where d.deptno=10;
+--------+--------+
| ename | deptno |
+--------+--------+
| CLARK | 10 |
| KING | 10 |
| MILLER | 10 |
+--------+--------+
3 rows in set (0.00 sec)
2、找出每个员工的工资等级,要求显示员工名,工资,工资等级
mysql> select e.ename,e.sal,s.grade from EMP e join SALGRADE s on e.sal between s.losal and s.hisal;
+--------+------+-------+
| ename | sal | grade |
+--------+------+-------+
| SMITH | 800 | 1 |
| ALLEN | 1600 | 3 |
| WARD | 1250 | 2 |
| JONES | 2975 | 4 |
| MARTIN | 1250 | 2 |
| BLAKE | 2850 | 4 |
| CLARK | 2450 | 4 |
| SCOTT | 3000 | 4 |
| KING | 5000 | 5 |
| TURNER | 1500 | 3 |
| ADAMS | 1100 | 1 |
| JAMES | 950 | 1 |
| FORD | 3000 | 4 |
| MILLER | 1300 | 2 |
+--------+------+-------+
14 rows in set (0.00 sec)
自连接
特点:一张表显示两张表,自己连接自己
1、找出每个员工的上级领导，要求显示员工名和对应的领导名
mysql> select a.ename "员工名",b.ename "领导名" from EMP a inner join EMP b on a.empno = b.MGR;
+-----------+-----------+
| 员工名 | 领导名 |
+-----------+-----------+
| FORD | SMITH |
| BLAKE | ALLEN |
| BLAKE | WARD |
| KING | JONES |
| BLAKE | MARTIN |
| KING | BLAKE |
| KING | CLARK |
| JONES | SCOTT |
| BLAKE | TURNER |
| SCOTT | ADAMS |
| BLAKE | JAMES |
| JONES | FORD |
| CLARK | MILLER |
+-----------+-----------+
13 rows in set (0.00 sec)
外连接(左连接/右连接)
什么是外连接,和内连接有什么区别?
内连接:
假设A和B表进行连接,使用内连接的话,凡是A表和B表能够匹配上的记录查询出来,这就是内连接
AB两张表没有主副之分,两张表示平等的
外连接:
假设A和B表进行连接,使用外连接的话,AB两张表中有一张是主表,一张是副表,主要查询主表中的数据
,顺便查询副表中的数据没有喝主表的数据匹配上,副表自动模拟出null与之匹配
1、找出每个员工的上级领导，要求显示员工名和对应的领导名
mysql> select a.ename "员工编号",b.ename "大佬" from EMP a left join EMP b on a.empno= b.mgr;
+--------------+--------+
| 员工编号 | 大佬 |
+--------------+--------+
| FORD | SMITH |
| BLAKE | ALLEN |
| BLAKE | WARD |
| KING | JONES |
| BLAKE | MARTIN |
| KING | BLAKE |
| KING | CLARK |
| JONES | SCOTT |
| BLAKE | TURNER |
| SCOTT | ADAMS |
| BLAKE | JAMES |
| JONES | FORD |
| CLARK | MILLER |
| SMITH | NULL |
| ALLEN | NULL |
| WARD | NULL |
| MARTIN | NULL |
| TURNER | NULL |
| ADAMS | NULL |
| JAMES | NULL |
| MILLER | NULL |
+--------------+--------+
21 rows in set (0.00 sec)
mysql> select a.ename "员工编号",b.ename "大佬" from EMP a right join EMP b on a.empno= b.mgr;
+--------------+--------+
| 员工编号 | 大佬 |
+--------------+--------+
| FORD | SMITH |
| BLAKE | ALLEN |
| BLAKE | WARD |
| KING | JONES |
| BLAKE | MARTIN |
| KING | BLAKE |
| KING | CLARK |
| JONES | SCOTT |
| NULL | KING |
| BLAKE | TURNER |
| SCOTT | ADAMS |
| BLAKE | JAMES |
| JONES | FORD |
| CLARK | MILLER |
+--------------+--------+
14 rows in set (0.00 sec)
2、找出哪个部门没有员工
mysql> select d.* from EMP e right join DEPT d on e.deptno=d.deptno where e.ename is null;
+--------+------------+--------+
| DEPTNO | DNAME | LOC |
+--------+------------+--------+
| 40 | OPERATIONS | BOSTON |
+--------+------------+--------+
1 row in set (0.00 sec)
3、找出每一个员工的部门名称以及工资等级
涉及以下三张表
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-13 | 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 | 1982-01-23 | 1300 | NULL | 10 |
+-------+--------+-----------+------+------------+------+------+--------+
14 rows in set (0.00 sec)
mysql> select * from DEPT;
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.00 sec)
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)
mysql> select e.ename,d.dname,s.grade from EMP e join DEPT d on e.deptno=d.deptno join SALGRADE s on e.sal between losal andd 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)
4、找出每一个员工的部门名称,工资等级,以及上司领导
mysql> select e.ename "员工",d.dname,s.grade,e1.ename "领导" from EMP e join DEPT d on e.deptno=d.deptno join SALGRADE s on e.sal between losal and hisal left join EMP e1 on e1.empno = e.mgr;
+--------+------------+-------+--------+
| 员工 | 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)
5、找出高于平均薪资的员工信息（用where字句中使用子查询）
第一步 找出平均薪资
mysql> select avg(sal) from EMP;
+-------------------+
| avg(sal) |
+-------------------+
| 2073.214285714286 |
+-------------------+
1 row in set (0.00 sec)
第二步 where过滤
mysql> select ename,sal from EMP where sal > 2073.214285714286;
+-------+------+
| ename | sal |
+-------+------+
| JONES | 2975 |
| BLAKE | 2850 |
| CLARK | 2450 |
| SCOTT | 3000 |
| KING | 5000 |
| FORD | 3000 |
+-------+------+
6 rows in set (0.00 sec)
合并
mysql> select ename,sal from EMP where sal > (select avg(sal) from EMP);
+-------+------+
| ename | sal |
+-------+------+
| JONES | 2975 |
| BLAKE | 2850 |
| CLARK | 2450 |
| SCOTT | 3000 |
| KING | 5000 |
| FORD | 3000 |
+-------+------+
6 rows in set (0.00 sec)
6、找出每个部门平均薪资的薪资等级（from后面嵌套子查询）
mysql> select t.*,s.grade
-> from (select deptno,avg(sal) avgsal from EMP group by deptno) t
-> join SALGRADE s on t.avgsal between s.losal and s.hisal;
+--------+--------------------+-------+
| deptno | avgsal | grade |
+--------+--------------------+-------+
| 10 | 2916.6666666666665 | 4 |
| 20 | 2175 | 4 |
| 30 | 1566.6666666666667 | 3 |
+--------+--------------------+-------+
3 rows in set (0.00 sec)