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 );
