大师网-带你快速走向大师之路 解决你在学习过程中的疑惑,带你快速进入大师之门。节省时间,提升效率

Mysql--连接查询和子查询

连接查询和子查询

一、连接查询

1.1 概念

连接查询:也可以叫跨表查询,需要关联多个表进行查询

1.2 根据年代分类

SQL92:1992
SQL99:1999,更新的语法,主要掌握这种

DBA:DataBase Administrator【数据库管理员】
老的DBA可能还在编写SQL92语法。

1.3 根据连接方式分类

内连接:等值连接
       非等值连接
       自连接

外连接:左外连接(左连接)
       右外连接(右连接)

全连接

1.4 多表的连接查询

所有涉及到的表:

员工表:


员工表.PNG

部门表:


部门表.PNG

薪水等级表:


薪水等级表.PNG
1.4.1 内连接中的等值连接

注意:在进行多表连接查询的时候,尽量给表起别名,这样效率高,可读性高。

select e.ename,d.dname from emp e,dept d;
但不能使用as

若两张表进行连接查询的时候没有任何条件限制,最终的查询总数是两张表记录条数的乘积,这种现象被称为笛卡尔积现象。为了避免笛卡尔积现象的发生,必须在进行表连接的时候添加限制条件

例一:查询每一个员工所在的部门名称,要求最终显示员工和对应的部门名

SQL92语法:内连接中的等值连接
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;

SQL99语法:内连接中的等值连接
select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;

SQL99语法的优点:

表连接独立出来了,结构更清晰。对表连接不满意的话,可以再追加where进行过滤。
select e.ename,d.dname from emp e inner join dept d on e.deptno=d.deptno;

select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;//inner可以省略

注意:在连接查询的时候虽然使用了限制条件,但是匹配的次数没有减少,还是56次,只不过这一次的结果都是有效记录

1.4.2 内连接中的非等值连接

例二:找出每一个员工对应的工资等级,要求显示员工名,工资,工资等级


员工薪水.PNG
SQL92语法:内连接中的非等值连接
select e.ename,e.sal,s.grade from emp e,salgrade s 
where e.sal between s.losal and s.hisal;

SQL99语法:内连接中的非等值连接(inner可以省略)
select e.ename,e.sal,s.grade from emp e (inner)join salgrade s 
on e.sal between s.losal and s.hisal;
1.4.3 内连接中的自连接

例三:找出每一个员工的上级领导,要求显示员工名以及对应的领导名


例三.PNG
SQL92语法:内连接中的自连接
select a.ename empname,b.ename leadername from 
emp a ,emp b where a.mgr=b.empno;


SQL99语法:内连接中的自连接
select a.ename empname,b.ename leadername from 
emp a (inner)join emp b on a.mgr=b.empno;
1.4.4 外连接
内连接:查询出A表和B表能够完全匹配的记录
外连接:查询出A表和B表能够完全匹配的记录之外,
       将其中一张表的记录无条件的完全查询出来,对方表没有匹配的记录,
       会自动模拟出null与之匹配。

左外:把join关键字左边的表数据全部显示
右外:把join关键字右边的表数据全部显示

外连接的查询结果条数>=内连接的查询结果条数

例四:找出每一个员工对应的部门名称,要求部门名全部显示

SQL99语法:
外连接中的右外连接(右连接)(outer可以省略)
select e.ename,d.dname from emp e right (outer) join 
dept d on e.deptno=d.deptno;

外连接中的左外连接(左连接)(outer可以省略)
select e.ename,d.dname from dept d left (outer)join 
emp e on e.deptno=d.deptno;

注意:任何一个右外连接都可以写成左外连接,任何一个左外连接也同样可以写成右外连接

为什么inner和outer可以省略,加上去有什么好处?

可以省略,因为区分内连接和外连接依靠的不是这些关键字,
而是看SQL语句中是否存在left/right;
若存在,表示一定是一个外连接,其他都是内连接
加上去的好处是增强可读性。

例五:找出每一个员工对应的领导名,要求显示所有的员工:

select a.ename empname,b.ename leadername from 
emp a left join emp b on a.mgr=b.empno;

例六:找出每一个员工对应的部门名称,以及该员工对应的工资等级。
要求显示员工名、部门名、工资等级

多表进行表连接的语法格式:

select xxx from a join b on 条件 join c on 条件;
原理:a表和b表连接之后,a表再和c表连接

例六.PNG

例六2.PNG
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 s.losal and s.hisal;

二、子查询

子查询就是select语句嵌套select语句,可以理解为子查询是一张表

2.1 在where语句中使用子查询

找出薪水比公司平均薪水高的员工,要求显示员工名和薪水

select ename,sal from emp where sal>avg(sal);
报错!分组函数不能直接使用在where后面

第一步:找出公司的平均薪水
select avg(sal) from emp; 

第二步:找出薪水大于平均薪水的员工信息
select ename,sal from emp where sal>2073.214286;

即:select ename,sal from emp where sal>(select avg(sal) form emp);

2.2 在from语句中使用子查询(将查询结果当做临时表)

找出每个部门的平均薪水,并且要求显示平均薪水的薪水等级

第一步:找出每个部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;

第二步:将上面的查询结果当做临时表t,t表和salgrade s表进行表连接,
条件:t.avgsal between s.losal and s.hisal

即:
select t.deptno,t.avgsal,s.grade
from (select deptno,avg(sal) as avgsal from emp group by deptno) t
join salgrade s
on t.avgsal between s.losal and s.hisal;

2.3 在select语句中使用子查询

select e.ename,(select d.dname from dept d where e.deptno=d.deptno)
as dname from emp e;

三、union和limit

3.1 union

作用:合并查询结果集

找出工作岗位是salesman和manager的员工

select ename,job from emp where 
job='manager' or job='salesman';

或:select ename,job from emp where
job in('manager','salesman');

使用union:
select ename,job from emp where job='manager'
union
select ename,job from emp where job='salesman';

注意:使用union,要求两个select的字段数量相同,类型可以不同,但在oracle中,类型也要求相同。

例:

select ename as enamedname from emp
union
select dname as enamedname from dept;

3.2 limit

  • 1.limit用来获取一张表中的某部分数据
  • 2.limit只有在mysql数据库中存在,不通用,是mysql数据库管理系统的特色。

例:找出员工表中前5条记录

select ename from emp limit 5;

以上sql语句的limit中的5表示从表中记录下标0开始,取5条
等同于下面的sql语句:

select ename from emp limit 0,5;

limit的语法:

limit 起始下标,长度
如果起始下标没有指定,默认从0开始,0表示表中第一条记录。

例:找出公司中工资排名在前5名的员工(limit出现在sql语句的最后位置上)

思路:按照工资降序排列取前5个
select ename,sal from emp order by sal desc limit 5;

例:找出工资排名在3-9名的员工

select ename,sal from emp order by sal desc limit 2,7;
MySql中通用的分页sql语句:
每页显示3条记录
第1页:0,3
第2页:3,3
第3页:6,3
第4页:9,3
...

每页显示pageSize条记录
第pageSize页:(pageNo-1)*pageSize,pageSize

通用的分页SQL(只适用于mysql数据库管理系统)

select t.* from t
order by t.x desc/asc
limit (pageNo-1)*pageSize,pageSize;