首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >第36次文章:数据库查询语句

第36次文章:数据库查询语句

作者头像
鹏-程-万-里
发布2019-09-27 17:28:24
1.7K0
发布2019-09-27 17:28:24
举报

本周继续进阶数据库的查询语句!内容还是有点多的呀!


进阶6:连接查询

一、基本内容

1、含义

又称为多表查询,当查询的字段来自于多个表时,就会使用到连接查询。

2、笛卡尔乘积现象

表1 有m行,表2 有n行, 结果= m*n行。

发生原因:没有有效的连接条件。

如何避免:添加有效的连接条件。

3、分类

(1)按年代分类,分别是1992年和1999年产生的标准。

sql92标准:仅仅支持内连接。

sql99标准【推荐】:支持内连接+外连接(左外和右外)+交叉连接。

(2)按功能分类:

  • 内连接:等值连接、非等值连接、自连接
  • 外连接:左外连接、右外连接、全外连接
  • 交叉连接
二、sql92语法

由于在sql语法中,仅仅支持内连接,所以我们对sql92语法标准的介绍仅限于内连接的三种方式。

1、等值连接

(1)基本语法

select 查询列表 from 表1,表2

where 连接条件 【and 筛选条件】

【group by 分组条件】

【having 分组后筛选】

【order by 排序条件】

【注】:【】中的内容均为可选项目,根据用户自己的需求进行添加。

(2)基本特点

  • 多表等值连接的结果为多表的交集部分
  • n表连接,至少需要n-1个连接条件
  • 多表的顺序没有要求
  • 一般需要为表起别名
  • 可以搭配前面介绍的所有子句使用,比如排序,分组,筛选

(3)示例

我们根据上面的基本特点,列举一个包含所有特点的案例,具体如下:

#案例:查询员工名,所在部门编号,部门名,以及部门所在城市
SELECT empname,e.deptId,d.dname,c.cityName
FROM emp e,dept d,city c
WHERE e.deptId = d.deptId AND d.location_id = c.location_id
ORDER BY e.deptId;

最后的结果如下

tips:在上面的案例中,我们实现了3表连接。为了将每张表进行一个连接,我们使用了2个连接条件。分别是emp表和dept表的部门编号进行关联,dept表的与city表的位置标号location_id 相关联。与此同时,我们为3张表格分别起了相应的别名,主要是为了在后续获取每张表的属性值时更加方便。在内连接的中,最后显示出来的属于三张表的交集部分,如果有两张表的关联条件不匹配的时候,那么这条记录就不会显示出来。

2、非等值连接

(1)基本特点

非等值连接与等值的语法和特点均类似,唯一的区别在于连接条件。此时的连接条件成为了一个不等式而不是一个等式。

(2)示例

为了对非等值连接的进行演示,我们创建一个job_grade表格,具体的内容如下:

我们的示例内容如下所示

#案例1:查询员工的工资和工资级别
SELECT empname,salary,grade_level
FROM emp e,job_grade g
WHERE e.salary BETWEEN g.lowest_sal AND g.highest_sal;

最后的结果如下

tips:通过上述的简单介绍,可以发现,整体的语法结构与等值连接完全相同,唯一差异的地方就在于连接条件由等值变为了区间范围连接。

3、自连接

(1)基本概念

自连接的语法与等值连接的语法完全相同。独特之处在于自连接的意义和内涵。自连接是将同一张表格当做两张表格来使用,也就是自己与自己进行相互连接。为两张表格起不同的别名。具体的妙处我们通过一个案例来感受一下!

(2)示例

#案例1:查询 员工名和上级的名称
select e.id,e.empname,e.manager_id,m.empname manager_name
from emp e,emp m
WHERE e.manager_id = m.id;

最后的结果:

tips:在这个查询案例中,我们需要根据每个员工对应的manager_id来判断其领导的编号,然后重新根据manager_id对应到emp表中的id中,再次寻找此id对应的姓名。内在逻辑在于:领导,也同样是员工,也处于员工表中。所以我们使用自连接,对第一次使用到的emp表用别名e来代替员工表,对第二次使用到的emp表用别名m来代替领导表。

三、sql99语法

1、基本内容

(1)语法

select 查询列表

from 表1 别名 【连接类型】 join 表2 on 连接条件

【where 筛选条件】

【group by 分组列表】

【having 分组后的筛选】

【order by 排序列表】

【limit 子句】

(2)分类

  • 内连接:inner
  • 外连接:

左外:left 【outer】

右外:right 【outer】

全外:full 【outer】

  • 交叉连接:cross

2、内连接

(1)特点

  • 表的顺序可以调换
  • 内连接的结果=多表的交集
  • n表连接至少需要n-1个连接条件

(2)分类

等值连接、非等值连接、自连接

(3)示例

在sql92语法中,我们已经对内连接的3种分类进行了详细讲解。为了避免冗余,我们在此处仅给出其中一种内连接的示例进行详解。

#案例.查询哪个部门的员工个数>3的部门名和员工个数,并按个数降序(排序)
SELECT dname,COUNT(*)
FROM emp e INNER JOIN dept d ON e.deptId = d.deptId
GROUP BY e.deptId
HAVING COUNT(*)>2
ORDER BY count(*) DESC;

查看一下结果:

tips:我们可以将上面给出的案例与sql92语法中的案例进行对比,可以发现,sql99语法将连接条件使用关键字on进行连接,与筛选条件分开,具有更强的可读性。在其他的条件语法上,与sql92没有区别。

3、外连接

应用场景:用于查询一个表中有,另一表没有的记录。

(1)特点

  • 查询的结果=主表中所有的行,如果从表和它匹配的将显示匹配行,如果没有匹配的则显示null。
  • left join 左边的为主表,right join 右边的为主表,左外和右外交换两个表的顺序,可以实现同样的效果。
  • 一般用于查询除了交集部分的剩余的不匹配的行。
  • full join 两边都是主表,左外和右外交换两个表的顺序,可以实现同样的效果。
  • 全外连接=内连接的结果+表1中有但表2中没有的+表2中有但表1中没有的。

(2)分类

左外连接、右外连接、全外连接(其中,mysql不支持全外连接)

(3)示例

#案例:查询哪个部门没有员工

#左外
SELECT d.deptId,d.dname
FROM dept d LEFT JOIN emp e ON e.deptId = d.deptId
WHERE e.id IS NULL;

#右外
SELECT d.deptId,d.dname
FROM emp e RIGHT OUTER JOIN dept d ON e.deptId = d.deptId
WHERE e.id IS NULL;

#全外(mysql不支持全外连接,此处仅作为语法展示,没有运行后的结果)
SELECT e.*,d.*
FROM emp e FULL OUTER JOIN dept d ON e.deptId = d.deptId;

左外结果与右外结果相同,如下所示

tips:sql99语法中,可以提供三种外连接,但是我们使用的是mysql数据库,不支持全外连接,所以我们最后仅仅运行左外和右外的结果。两者的结果相同,与我们在外连接特点中表述的相同。

4、交叉连接

(1)特点

类似于笛卡尔乘积

(2)示例

#交叉连接 也就是笛卡尔乘积
SELECT e.*,d.*
FROM emp e
CROSS JOIN dept d;

查看一下结果:

tips:上面展示出来的仅仅是结果集的一部分。全部结果是两张表的笛卡尔集。

四、小总结

1、sql92语法 PK sql99语法

功能:sql99支持的较多。

可读性:sql99实现连接条件和筛选条件的分离,可读性较高。

所以我们在使用连接查询的时候,更加推荐使用sql99语法。

2、几种连接区别

内连接,左外连接,右外连接,以及其他集合形式的实现方法

进阶7:子查询

一、基本概念

1、含义

嵌套在其他语句内部的select语句称为子查询或内查询。

外面的语句可以是insert、update、delete、select等,一般select作为外面的语句较多。

外面如果为select语句,则此语句称为外查询或主查询。

2、分类

(1)按出现位置

select后面:标量子查询

from后面:表子查询

where 或 having后面标量子查询列子查询、行子查询

exists后面:标量子查询、列子查询、行子查询、表子查询

(2)按结果集的行列

标量子查询(单行子查询):结果集为一行一列

列子查询(多行子查询):结果集为多行一列

行子查询:结果集为多行多列

表子查询:结果集为多行多列

【注】:子查询的分类较多,在使用的时候,主要出现的情况是上面标注颜色的几种情况。下面我们将根据不同的分类来给出具体的案例进行讲解。

二、where或having后面

1、主要分类

(1)标量子查询(单行子查询)

(2)列子查询(多行子查询)

(3)行子查询(多行多列)

2、特点

(1)子查询放在小括号内

(2)子查询一般放在条件的右侧

(3)标量子查询,一般搭配着单行操作符使用:< > = >= <= <>

(4)列子查询,一般搭配着多行操作符使用:IN、ANY/SOME、ALL

(5)子查询的执行优先于主查询执行,主查询的条件用到了子查询的结果

3、示例

我们根据上面的分类,对每一类给出一个典型案列,结合其语法和结果一起分析

#1、标量子查询
#案例:查询最低工资大于1号部门最低工资的部门id和其最低工资
select MIN(salary),deptId from emp GROUP BY deptId 
HAVING MIN(salary) > (select MIN(salary) from emp WHERE deptId = 1 );

#2、列子查询(多行子查询)
#案例:返回其他部门中比deptId = 1部门任一工资低的员工的信息
SELECT * FROM emp
WHERE salary < (SELECT MAX(salary) FROM emp WHERE deptId =1) AND deptId <> 1;
#或
SELECT * FROM emp
WHERE salary < ANY(SELECT salary FROM emp WHERE deptId =1) AND deptId <> 1;

#3、行子查询(结果集一行多列或多行多列)
#案例:查询员工编号最小并且工资最高的员工信息
SELECT * from emp 
WHERE id = (select MIN(id) FROM emp ) 
AND salary = (SELECT MAX(salary) FROM emp)
#或
SELECT * from emp 
WHERE (id,salary)= (select MIN(id),MAX(salary) FROM emp)

标量子查询结果:

列子查询结果:

行子查询结果:

tips:通过三者的对比我们可以做出一定的总结。出现在where和having后面的子查询,都属于产生筛选条件的值,标量子查询直接产生一个值;列子查询返回一系列的值,但是需要配合any,some等关键字进行使用,最后依旧是使用一个常量值来代替筛选条件;对于行子查询,是对于多个字段的值进行同时匹配,需要每个字段的交集,在这种筛选条件下,一般很难有相关的信息匹配成功,所以在日常情况中也很少使用到。

三、select后面

1、特点

仅仅支持标量子查询。

2、示例

#案例:查询每个部门的详细信息以及员工数
select d.*,(SELECT COUNT(*) FROM emp e WHERE e.deptId = d.deptId) 部门的员工个数 
FROM dept d;

查询结果

tips:这个案例很好的展示了一种情况,子查询得到的结果,也可以直接放在select语句后面,作为查询列表被直接显示出来。

四、from后面

1、特点

将子查询结果充当一张表,要求必须起别名。

2、示例

#案例:查询每个部门的平均工资的工资等级

#(1)查询每个部门的平均工资
SELECT AVG(salary),deptId FROM emp GROUP BY deptId;
#(2)连接(1)的结果集和job_grade表,筛选条件平均工资 between lowest_sal AND highest_sal
SELECT a.*,j.grade_level 
FROM (SELECT AVG(salary) ag_d,deptId FROM emp GROUP BY deptId) a
LEFT OUTER JOIN job_grade j ON a.ag_d BETWEEN j.lowest_sal AND j.highest_sal;

结果图:

tips:在上面的案例中,我们总共分为两步进行查询,第一步查询出每个部门的平均工资之后,mysql中会产生一个虚拟表格,我们需要对这个虚拟表格起一个别名,这样才能够在后续添加连接条件的时候使用到此虚拟表。当我们对第一次产生的虚拟表格起一个别名之后,我们就可以将其放在from后面,作为一张新表,与job_grade表格进行内连接,然后添加相应的连接条件,得到最后的结果。

五、exists后面

1、语法

EXISTS(完整的查询语句)

结果:1或0

2、示例

#案例1:查询有员工名的部门名
SELECT dname FROM dept d
WHERE EXISTS(SELECT * FROM emp e WHERE e.deptId = d.deptId);
#或
SELECT DISTINCT d.dname
FROM emp e INNER JOIN dept d
ON e.deptId = d.deptId;

结果

tips:在上面示例中,我们主要解释一下exists方式的查询。部门表中的每个部门编号如果存在员工表中,那么我们就列举出此员工的详细信息,然后使用exist来判断此子查询是否存在值,如果有值,则返回1,如果没有,则返回0,当返回1的时候,就满足筛选条件,然后主查询就显示出此部门的名称。

进阶8:分页查询

一、应用场景

当要查询的条目数太多,一页显示不全。这个时候我们就需要使用到分页查询,对每页显示的内容进行限定。

二、语法

select 查询列表 from 表

limit 【offset】,size;

【注】:

(1)offset代表的是起始的条目索引,当起始索引不写的时候,我们默认为从0开始;

(2)size代表的是显示的条目数。

三、特点

(1)limit语句放在查询语句的最后

(2)公式

假如要显示的页数为page,每一页条目数为size,那么起始索引为:

offset=(page-1)*size

四、示例
#案例:有奖金的员工信息,并且工资较高的前5名显示
select * FROM emp WHERE bonus is NOT NULL
ORDER BY salary DESC
LIMIT 5;

结果:

tips:在分页查询的案例中,我们省略了起始索引,程序中默认从0开始,给我们输出了排序之后的前五条记录。


本文参与 腾讯云自媒体分享计划,分享自微信公众号。
原始发表:2019-09-15,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 Java小白成长之路 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 进阶6:连接查询
  • 进阶7:子查询
  • 进阶8:分页查询
相关产品与服务
数据库
云数据库为企业提供了完善的关系型数据库、非关系型数据库、分析型数据库和数据库生态工具。您可以通过产品选择和组合搭建,轻松实现高可靠、高可用性、高性能等数据库需求。云数据库服务也可大幅减少您的运维工作量,更专注于业务发展,让企业一站式享受数据上云及分布式架构的技术红利!
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档