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

  • 2019 年 10 月 8 日
  • 笔记

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

进阶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开始，给我们输出了排序之后的前五条记录。

VirMach 便宜 VPS

QNews

QNews