mysql中all用法和any的用法和内连接和外连接,全外连接,联合查询,自连接

all的用法

与子查询配合使用

在all的用法中,有三种

第一种: <>all类似于not in 等效于not in

语法:select 列名 from 表名 where 列名 <> all(select 列名 from 表名 where 条件表达式);

例如1:显示表中与CLERK部门的员工工资都不同的员工姓名和工资.

select ename ,sal from emp
where sal<>all(select sal from emp where job='CLERK');

例如2:查看表中与SALESMAN岗位员工不同工资的员工姓名和工资.
select ename ,sal from emp
where sal<>all(select sal from emp where job='SALESMAN');

例如:3.查看表中与SCOTT不同部门的员工的员工姓名和工资。
select ename,sal from emp
where deptno <> all(select deptno from emp where ename='scott');

第二种: >all比子查询中的最大值还要大

例如:查看表中比30号部门最高工资的员工还高的员工姓名和工资

select ename,sal from emp
where sal > all(select sal from emp where deptno=30);

第三种: <all比子查询中的最小值还要小

例如:查看表中比10号部门最低工资的员工还要低的员工姓名和工资

select ename ,sal from emp
where sal < all (select sal from emp where deptno=10);

注意:= all 是不存在的 any的用法

与子查询配合使用

在any的用法中,有三种

语法:select 列名 from 表名 where 列名 = any (select 列名 from 表名 where 条件表达式);

第一种: = any 与任何一个相等

例如:查看表中与20号部门员工工资相同的员工姓名和工资

select ename,sal from emp where sal =any (select sal from emp where deptno = 20);

第二种: > any 比最低的高

例如:查看表中比20号部门工资最低的员工的工资高的员工姓名和工资

select ename,sal from emp where sal >any (select sal from emp where deptno = 20);

第三种:< any 比最高的低

例如:查看表中比10号部门工资最低的员工的工资高的员工姓名和工资

select ename,sal from emp where sal < any (select sal from emp where deptno = 10);

注意:除了= any 会包含自己,其余的<any和>any都不会包含自己. 内连接

说明:在表进行跨表查询时,查询结果只是返回符合连接条件的数据

关键字:inner join

语法:select 别名1.列名1,别名2.列名2 from 表名 1 [as] 别名1 inner join 表名2 [as] 别名2 on 别名1.列名3=别名2.列名3 where 列名4 =值;

lnner join 代替了原本的多表查询的逗号
第一个on替代了之前的多表查询用来建立联系的where

例:查询7934 工号的员工所在的部门名称,结果显示员工编号和部门名称

等值连接:

select empno,dame from emp e,dept d where e.deptno=d.deptno

and empno=7934;

内连接:

select empno ,dname from emp e inner join dept d on e.deptno=d.deptno where empno=7934;

例:查询SCOTT所在的城市使用内连接写

select ename,loc from emp e inner join dept d on e.deptno=d.deptno

where e.ename='scott';

三表内连接语法 语法1:

select列 ----最终要显示的数据列注意应有别名点出来
from第一张表 ---第一个数据源需要取别名
inner join第二张表 ---第二个数据源需要取别名
on 第一张表的列=第二张表的列 ---建立—二表的联系lnner join第三张表 ---第三个数据源需要取别名
on第二张表的列=第三张表的列 ---建立二三表的联系
where表达式 ---给到条件来筛选

例:查询讴歌教过哪些学生

select sname ,teacher
from student st inner join score sc on st.sno=sc.sno
inner join course cs on sc.cno=cs.cno where cs.teacher='讴歌';

语法2:

select列
from 第1张表

inner join 第2张表

inner join 第3张表
on 1表列=2表列 and 2表列=3表列

where 条件表达式

例:查询讴歌教过哪些学生

select sname ,teacher
from
student st inner join score sc inner join course cs
on st.sno=sc.sno and sc.cno=cs.cno where cs.teacher='讴歌';

外连接 左表:在多连接查询时第一个出现的表,称之为左表 右表:在多表连接查询是最后出现的表,称之为右表 左外连接:

说明:使用左连接进行多表连接查询时，如果某个表中的数据需要全部显示，那查询时就使用这个方法。

查询时将这个表作为主表即左表,在右表符合条件的数据会显示

在查询的结果中,在右表中不符合条件的数据,也会显示,但是不符合条件的查询字段显示是null

关键字:left join 语法:

select 别名1.列名,别名2.列名2

from 表名1 [as] 别名1

left join 表名2[as]别名2

on 别名1.列名3=别名2.列名3

where 条件表达式

例如:查询哪个部门没有员工,结果显示部门名称、员工编号

select dname,empno from dept d left join emp e
on e.deptno=d.deptno where empno is null;

例如:查询哪个部门没有员工,结果显示部门名称、员工编号

select dname,empno from dept d left join emp e
on e.deptno=d.deptno where empno is null;

例如:查询哪个人没有领导 显示姓名,mgr

select e.ename,e.mgr from emp e left join emp d on e.empno=d.empno
where e.mgr is null;

select ename,mgr from emp where mgr is null;

例如:查询每位的员工的工资和津贴(comm),显示员工姓名,工资和津贴

select e.ename,e.sal ,comm from emp e left join emp d on e.empno=d.empno;

右外连接:

说明:使用右连接进行多表连接查询时，如果某个表中的数据需要全部显示，那查询时就使用这个方法。

查询时将这个表作为主表即右表,在左表符合条件的数据会显示

在查询的结果中,在左表中不符合条件的数据,也会显示,但是不符合条件的查询字段显示是null

关键字:right join 语法:

select 别名1.列名,别名2.列名2

from 表名1 [as] 别名1

right join 表名2[as]别名2

on 别名1.列名3=别名2.列名3

where 条件表达式

注意:
左右连接的语句写法上区别就是主表副表相反

左连接的主表是关键字左边的表

右链接的主表是关键字右表的表

左右连接的显示结果会根据主副表的不同而不同

左连接左表列会全显示，右表匹配上的列但是没有相应值的会显示null，若左表的列少于右表的列则右表多的列不会显示
右链接右表列会全显示，左表匹配上的列但是没有相应值的会显示null，若右表的列少于左表的列则左表多的列不会显示

全外连接

注意:mysql没有全外连接,只做了解

关键字:full join

语法:

select 别名1.列名,别名2.列名2

from 表名1 [as] 别名1

full join 表名2[as]别名2 ----

on 别名1.列名3=别名2.列名3 ----建立两张表之间的联系

where 条件表达式

联合查询

关键字:union 只做了解

作用:主要是用作将左连接和右连接查询拼接到一起

语法:

(select 别名1.列名,别名2.列名2

from 表名1 [as] 别名1

left join 表名2[as]别名2 ----

on 别名1.列名3=别名2.列名3 ----建立两张表之间的联系

where 条件表达式)

union

(select 别名1.列名,别名2.列名2

from 表名1 [as] 别名1

right join 表名2[as]别名2 ----

on 别名1.列名3=别名2.列名3 ----建立两张表之间的联系

where 条件表达式);

自连接

说明:在 mysaL中有时会有一些特殊情况，需要将一张表当做两张表来查询，显示出需要的数据,因为两张是相同的,所以几乎所有的列都是通过别名点出来的,那么可以使用到自连接.

关键字: exists

例如:查询哪些员工是领导

select a.ename --先确定需要查询显示的列,但是这个列是
我们首次查询这张表时的列
from emp a ---数据源，是首次查询的表
where exists --- exists意思是存在，根据该关键字后面的子查询
结果来进行判断，如果判断结果是 true (子查询有结果)，那么主查询语句将有符合条件的返回值
(select b.empno from emp b where b.mgr = a.empno); ---子查询中的数据源，可以理解成我们第二次查这张表.

语法:

Select 别名1.列名1，别名1.列名2

from 表名 [as] 别名1

where exists

(select别名⒉.列名 3 from表名[as]别名 2 where建立两张表联系语句);

例如:查询哪些员工是领导

第一种方法:

select ename from emp where empno in(select distinct mgr from emp);

第二种方法:

自连接:

select a.ename from emp a

where exists

(select b.empno from emp b where b.mgr=a.empno );

例如:查询哪些员工不是领导

第一种方法:

select ename from emp where empno not in(select distinct mgr from emp where mgr is not null );

第二种方法:

自连接:

select a.ename from emp a

where not exists

(select b.empno from emp b where b.mgr=a.empno );