Mysql DQL2

最新推荐文章于 2024-09-05 17:08:26 发布

m0_46911493

最新推荐文章于 2024-09-05 17:08:26 发布

阅读量108

点赞数

本文链接：https://blog.csdn.net/m0_46911493/article/details/106963311

版权

外连接：

主表的数据全部无条件的查询出来

左连接有右连接的写法右连接也有对应的左连接的写法

左外连接（左连接） 表示左边的是这张表的主表

select	字段
FROM 	主表
LEFT (OUTER) JOIN 副表
ON 条件

找出所有员工的上级领导
//left 左边是主表如果副表没有匹配的内容返回null

 select 
    	e.ename, e1.ename
    from
    	emp e
    left join
    	emp e1
    on
    e.mgr=e1.empno;

+--------+-------+
| ename  | ename |
+--------+-------+
| 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 |
+--------+-------+







右外连接（右外连接） 表示右边的是这张表的主表

select	字段
FROM 	主表
RIGHT (OUTER) JOIN 副表
ON 条件

内外连接的区别：

内连接：

	假设A表和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接，A B 两张表没有主副之分 两张表是平等的，

外连接：

	假设A表和B表进行连接，使用外连接的话，A B两张表中有一张表是主表，一张表是辅表，主要查询主表中的数据，捎带查询副表，当副表中的数据没和主表中的数据匹配上，附表自动模拟出null与之匹配。

区别：

连接查询原理与笛卡尔积现象

笛卡尔积现象：如果两张表查询没有限制最终结果为两张表的乘积

避免笛卡尔积现象的方法：加条件进行过滤但是不会减少匹配次数，只不过显示的是有效的记录；

别名的好处：执行效率高可读性好！！！

三张表的连接查询：

案例 找出每个员工的部门名称以及工作等级
部门表：（dept d）
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

工资等级：（salgrade s）
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
员工表：（emp e）
-------+--------+---------+--------+
| empno | ename  | sal     | deptno |
+-------+--------+---------+--------+
|  7369 | SMITH  |  800.00 |     20 |
|  7499 | ALLEN  | 1600.00 |     30 |
|  7521 | WARD   | 1250.00 |     30 |
|  7566 | JONES  | 2975.00 |     20 |
|  7654 | MARTIN | 1250.00 |     30 |
|  7698 | BLAKE  | 2850.00 |     30 |
|  7782 | CLARK  | 2450.00 |     10 |
|  7788 | SCOTT  | 3000.00 |     20 |
|  7839 | KING   | 5000.00 |     10 |
|  7844 | TURNER | 1500.00 |     30 |
|  7876 | ADAMS  | 1100.00 |     20 |
|  7900 | JAMES  |  950.00 |     30 |
|  7902 | FORD   | 3000.00 |     20 |
|  7934 | MILLER | 1300.00 |     10 |
+-------+--------+---------+--------+

....
	A
join
	B
join
	C
on
......

表示A先和B连接再和C连接
//先找A B 两个表有关联的部分 然后再和C表进行匹配
select 
	e.ename, d.deptno, 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;




结果：
+--------+--------+-------+
| ename  | deptno | grade |
+--------+--------+-------+
| SMITH  |     20 |     1 |
| ALLEN  |     30 |     3 |
| WARD   |     30 |     2 |
| JONES  |     20 |     4 |
| MARTIN |     30 |     2 |
| BLAKE  |     30 |     4 |
| CLARK  |     10 |     4 |
| SCOTT  |     20 |     4 |
| KING   |     10 |     5 |
| TURNER |     30 |     3 |
| ADAMS  |     20 |     1 |
| JAMES  |     30 |     1 |
| FORD   |     20 |     4 |
| MILLER |     10 |     2 |
+--------+--------+-------+


找出每个员工的部门名称以及工作等级 上级领导
此时只能用外连接 因为king没有上级 但是结果不能舍弃king
select 
	e.ename, d.deptno, s.grade,e1.ename'领导'
from
    emp e
join
	dept d
on
	e.deptno=d.deptno
join
	salgrade s
on 
	e.sal between s.losal and s.hisal
left join
	emp e1
on
	e.mgr=e1.empno; //员工表里的领导编号等于领导表里的员工编号
+--------+--------+-------+-------+
| ename  | deptno | grade | 领导    |
+--------+--------+-------+-------+
| SMITH  |     20 |     1 | FORD  |
| ALLEN  |     30 |     3 | BLAKE |
| WARD   |     30 |     2 | BLAKE |
| JONES  |     20 |     4 | KING  |
| MARTIN |     30 |     2 | BLAKE |
| BLAKE  |     30 |     4 | KING  |
| CLARK  |     10 |     4 | KING  |
| SCOTT  |     20 |     4 | JONES |
| KING   |     10 |     5 | NULL  |
| TURNER |     30 |     3 | BLAKE |
| ADAMS  |     20 |     1 | SCOTT |
| JAMES  |     30 |     1 | BLAKE |
| FORD   |     20 |     4 | JONES |
| MILLER |     10 |     2 | CLARK |
+--------+--------+-------+-------+
包括没有领导的都在表里

子查询

什么是子查询？子查询出现在哪里？

select 语句当中可以嵌套select语句，被嵌套的select就是子查询

子查询出现在 select。。。。。（select）from（select） where（select）

where语句中使用子查询：

找出薪资高于平均薪资的员工



select
	ename, sal
from
	emp
where
	sal>(select 
    		avg(sal)
      from
    		emp);
    		
+-------+---------+
| ename | sal     |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+

from后面嵌套子查询

找出每个部门的平均薪资等级
薪资等级
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
部门的平均薪资：
select avg(sal) from emp  group by deptno;
+-------------+
| avg(sal)    |
+-------------+
| 2916.666667 |
| 2175.000000 |
| 1566.666667 |
+-------------+



第一步：找出每个部门的平均水平按照部门分类          select deptno '部门', avg(sal) '平均工资'  from emp group by deptno;
+------+-------------+
| 部门     | 平均工资           |
+------+-------------+
|   10 | 2916.666667 |
|   20 | 2175.000000 |
|   30 | 1566.666667 |
+------+-------------+
第二步：将以上的查询结果当作一个临时表 t 让表t 和salgrade s连接， 条件是： t.avg(sal) between s.losal and s.hisal


写法：
select
	t.*, s.grade
from
	(select deptno , avg(sal) as avgsal   from emp group by deptno) as t
join
	salgrade s
on
	t.avgsal between s.losal and s.hisal;
	
	
	
+--------+-------------+-------+
| deptno | avgsal      | grade |
+--------+-------------+-------+
|     30 | 1566.666667 |     3 |
|     10 | 2916.666667 |     4 |
|     20 | 2175.000000 |     4 |
+--------+-------------+-------+


找出每个部门薪资等级的平均值：


第一步：找出每个员工的薪水等级
select
	e.ename, e.sal, e.deptno, s.grade
from 
	emp e 
join 
	salgrade s 
on 	
    e.sal between s.losal and s.hisal;

+--------+---------+--------+-------+
| ename  | sal     | deptno | grade |
+--------+---------+--------+-------+
| SMITH  |  800.00 |     20 |     1 |
| ALLEN  | 1600.00 |     30 |     3 |
| WARD   | 1250.00 |     30 |     2 |
| JONES  | 2975.00 |     20 |     4 |
| MARTIN | 1250.00 |     30 |     2 |
| BLAKE  | 2850.00 |     30 |     4 |
| CLARK  | 2450.00 |     10 |     4 |
| SCOTT  | 3000.00 |     20 |     4 |
| KING   | 5000.00 |     10 |     5 |
| TURNER | 1500.00 |     30 |     3 |
| ADAMS  | 1100.00 |     20 |     1 |
| JAMES  |  950.00 |     30 |     1 |
| FORD   | 3000.00 |     20 |     4 |
| MILLER | 1300.00 |     10 |     2 |
+--------+---------+--------+-------+

基于以上结果继续按照deptno分组 求grade值
直接group by 分组即可
select
	e.ename, e.sal,  e.deptno, avg(s.grade)
from 
	emp e 
join 
	salgrade s 
on 	
    e.sal between s.losal and s.hisal
group by
	 e.deptno;
+-------+---------+--------+--------------+
| ename | sal     | deptno | avg(s.grade) |
+-------+---------+--------+--------------+
| CLARK | 2450.00 |     10 |       3.6667 |
| SMITH |  800.00 |     20 |       2.8000 |
| ALLEN | 1600.00 |     30 |       2.5000 |
+-------+---------+--------+--------------+

select 后面嵌套子查询

找出每一个员工所在的部门名称，要求显示员工名和部门名
 select 
 e.ename,
 (select d.dname from dept d where e.deptno=d.deptno) 
 as 
 dname 
 from
 emp e;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+

union（可以将查询结果集相加）；

可以将两张不相干的数据拼接在一起显示只能拼接字段数量相同的查询

找出工作岗位是salesman和manager的员工
第一种：
select 
	e.ename, e.job 
from 
	emp e
where
	e.job='SALESMAN'
or
	e.job='MANAGER';

第二种：
select 
	e.ename, e.job 
from 
	emp e 
where
	e.job 
in 
	('MANAGER','SALESMAN') ;
	
	
	
第三种：
select ename, job from emp where job='MANAGER'
   union
select ename, job from emp where 					job='SALESMAN';

+--------+----------+
| ename  | job      |
+--------+----------+
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| JONES  | MANAGER  |
| MARTIN | SALESMAN |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
| TURNER | SALESMAN |
+--------+----------+

limit：

mysql特有的，其他数据库中没有不通用

语法机制：

limit startIndex, length

startIndex表示起始位置，从零开始

length表示取几个

limit是sql语句中最后一个执行的环节

通用的标准分页sql：

每页显示pageSize条记录：

第pageNo页：（pageNo-1）*pageSize,pageSize;

pageSize:每页显示多少条记录；

pageNo：显示第几页；

m0_46911493

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql DQL2

外连接：主表的数据全部无条件的查询出来左连接有右连接的写法右连接也有对应的左连接的写法左外连接（左连接）表示左边的是这张表的主表select 字段FROM 主表LEFT (OUTER) JOIN 副表ON 条件找出所有员工的上级领导//left 左边是主表如果副表没有匹配的内容返回null select e.ename, e1.ename from emp e left join emp e1 on e.mgr=
复制链接

扫一扫