DML数据操作语言之复杂查询

1.视图(View)

我们知道,在关系型数据库中,用来保存实际数据记录的是数据表。和表同等概念也是用来保存东西是:视图。

但是数据表是用来保存实际数据记录的,而视图是用来保存常用select语句的。

一个视图保存一条select语句。

使用视图的好处:

1.节省存储空间:

一般情况,如果我们希望将某条select语句的执行的结果表保存起来,我们就需要新建一张表,将结果表的数据记录保存在我们新建的这张表中。

这样,如果是少量数据还好,但是如果面对的大量的数据,这样势必会耗费去大量的存储空间。

如果选用视图,我们利用一个视图来保存这条select语句。当我们需要使用这条select语句执行之后的结果表的数据记录时,我们可以选取这个视图中

的select语句进行执行,执行之后就会形成一张保存了数据记录的临时表。

2.不用重新书写常用select语句

将常用的select语句保存为视图,我们在使用时,就可以不用每次都书写相同的select语句。其实这时候,就相当于我们将常用的select语句拷贝出来,

保存在我们本地的文本文件中,一个文本文件保存一个select语句,在使用的时候,找到相应的文本文件,进行执行。这个文本文件就是视图。

所以我们应该将常用的select语句保存在视图中。

创建视图的方法:

创建视图需要使用create view语句 ,基本语法格式:

create  view   <视图名>   (<视图列名1>,<视图列名2>,......)   as   <select语句>;

(<视图列名1>,<视图列名2>,......)称为视图列名清单。

这里的视图列名相当于给后面的select语句中的列 起的别名。所以视图列名应该和select语句中的列名 进行一一对应。如果select语句中已经为列名指定了

别名,不需要再指定相同功能的视图列名,可以省略视图列名清单。

使用视图进行查询:

select  <列名1>,<列名2>,......  from   <视图名>;

这里from子句选择了视图,就相当于执行了该视图中的select语句,得到了一张保存了数据记录的结果表。

前面的select子句再在结果表中选出哪几列来进行显示,形成新的结果表。如果显示结果表的全部列,则使用 select  * 。

由此可见,选取一条视图,至少执行了2次的select语句。

关于多重视图:

我们可以在一个视图保存的select语句,该select语句又选取了另一个视图。这样嵌套,形成了多重视图。

但是由于多重视图的可阅读性不好,建议不要使用。

视图的限制:

1.在视图保存的select语句中不能使用order by子句进行排序

该select语句可以使用 where子句,group by子句,having 子句。但是不能使用order by子句。

2.利用视图来增加,删除,更新原表数据记录

我们知道可以利用视图中保存的select语句,结合select语句来进行 使用视图进行查询。初次之外,我们还可以利用视图中保存的select语句,集合insert语句,

delete语句,update语句 ,来实现 利用视图来进行原表数据的增,删,改操作。

insert  into  <视图名>  values  (<值1>,<值2>,......);

但是 利用视图中保存的select语句 来实现增,删,改要保证 通过视图保存的select语句查询出来的结果表没有进行聚合操作(group by ,聚合函数等)。否则,

对于结果表的增,删,改操作,将无法与原表中的数据进行同步,破坏了数据的一致性。

比较有代表性的几个条件是:

select子句中不能使用distinct来去重;

from子句中只能有一张表;

不能使用group  by子句;

不能使用having子句;

不能使用聚合函数;

删除视图:

删除视图需要用到drop view语句。基本语句格式是:

drop  view  <视图名>;

2.子查询

子查询就是将保存在视图中的select语句用于from子句中。如下格式:

select   ......   from   (select   .....  from .....)  as   <子查询结果表的别名>;

其实子查询 和 视图的作用过程都是一样的 。 先通过写在后面的 select语句 进行查询得到一张子查询结果表,为该表起一个别名。

然后在通过前面的select语句对子查询结果表进行查询,形成最终的结果表。

需要注意的一点是:默认我们都需要为 查询出来的子查询结果表起一个别名。在其他数据库中需要使用as,在oracle数据库中为表起别名,

不能使用as。

select   ......   from   (select   .....  from .....)   <子查询结果表的别名>;

如果在子查询中再嵌套子查询,就形成了多层嵌套的子查询。 但是随着子查询嵌套层数的增加,SQL语句就变得越来越难读懂,而且执行

效率会下降。所以应该避免使用多层的嵌套子查询。

标量子查询:

标量子查询区别于普通子查询的是 :普通子查询 形成的结果表是保存有若干条记录的表。 标量子查询 形成的结果是一个数值。(也可看成是一行一列的一条记录)

标量子查询就是返回单一值的子查询。

由于标量子查询返回的是一个数值,所以在可以使用数值的子句中,均可以使用标量子查询。

select  .....   from  ......   where   age  <=  (select  avg(age)  from  tb_person);

select avg(age)  from tb_person;子查询返回的是单一的数值,属于标量子查询。

3.关联子查询

关联子查询会在细分的组内进行比较时使用。

分析一种查询情况,根据学生的班级对学生进行分组,计算每个班级的平均总分,让后筛选出总分高于他所在班级平均总分的学生。

由于是分组内进行比较,需要使用关联子查询。

select  t2.name  , t2.score  from  tb_student as  t2  where  t2.score   >=  (select  avg(score)  from  tb_student  as  t1  where   t2.classId  =  t1.classId  group  by  t1.classId);

可以看出通过

select  avg(score)  from  tb_student  as  t1   group  by  t1.classId;

这个普通子查询,我们会得到N个班级的平均总分的一张表,表有N行记录。

但是这时前面的where子句  where t2.score >= ?  显然需要一个标量值进行比较,也就是需要一个标量子查询的结果。
这时在子查询中添加  的  where t2.classId = t1.classId    这样where子句 把每条记录的总分与 select子查询生成的结果表的总分 进行了一对一的关联。

也就是要比较一个学生的总分和平均总分的大小,先根据该学生的classId  在子查询生成的结果表中 找到这个班级的平均总分,然后再比较。

结合条件一定要写在子查询中。这是因为关联名称的作用域。

子查询中的可以使用 外层查询的关联名称  t1

但是外层查询不能使用 子查询的关联名称  t2

上一篇:PHP用PDO


下一篇:Linux网络编程客户\服务器设计范式