【数据库04】中级开发需要掌握哪些SQL进阶玩法 1

1.连接表达式

前一篇文章我们使用笛卡尔积运算符来组合来自多个关系的信息，本文介绍“连接”查询，允许程序员以一种更自然的方式编写一些查询，并表达只用笛卡尔积很难表达的查询。

本节所用的示例设计student和takes两个关系，如下图所示。请注意对于ID为98988的学生，在2018年夏季选修的BIO-301课程的1号课程段的grade属性为空值，该空值表示它尚未得到成绩。

1.1 自然连接

请考虑以下SQL查询，该查询为每名学生计算该学生已经选修的课程的集合。

select name,curse_id
from student,takes
where student.ID = takes.ID;

请注意，此查询仅输出已选修某些课程的学生，未选修任何课程的学生不会被输出。请注意上面连接条件中student.ID与takes.ID具有相同的属性名ID，这在实际的SQL查询中含常见，自然连接被设计出来简化上述情况的查询。

自然连接运算作用于两个关系，并产生一个关系作为结果。与两个关系的笛卡尔积不同，自然连接只考虑在两个关系的模式中都出现的那些属性上取值相同的元组对，而笛卡尔积将第一个关系的每个元组与第二个关系的每个元组进行串接。

上面的SQL也可以这么写。

student natural join takes

其查询结果如下，注意属性的列出顺序，受限是两个关系模式中的公共属性，其次是第一个关系模式中的那些属性，最后是只出现在

第二个关系中的那些属性。

之前有一个查询，"对于大学中已经选课的所有学生，找出他们的姓名以及他们选修的所有课程的标识。”我们可以这么写。

select name, course_id
from student natural join takes;

在一条SQL查询的from字句中，可以用自然连接把多个关系结合在一起。

select A1,A2....An
from r1 natural join r2 natural join r3... natural join rm
where P;

更一般地，from子句可以写为如下形式。

from E1,E2...En

每个Ei可以是单个关系或者一个涉及自然连接的表达式。例如，假设我们要查询“列出学生的姓名和他们所选择的课程的名称”。可以写。

select name,title
from student natural join takes, course
where takes.course_id = course.course_id;

受限计算studnet和takes的自然连接，再计算该结果与course的笛卡尔积，where字句从结果中再过滤，过滤条件是course_id相匹配。

思考如下sql与上面的sql是否会等价。

select name,title
from student natural join takes natural join course

不是！！！

请注意student和takes做自然连接后包含的属性是(ID，name，dept_name,tot_cred,course_id,sec_id),而course包含的属性是(course_id,titile,dept_name,credits)。上面二者做自然连接，不仅需要course_id取值相同，还需要dept_name取值相同。

为了避免这样的错误出现，我们可以这样做。

select name,title
from (student natural join takes) join course using (course_id);

1.2 连接条件

除了上面的join using外，还可以使用on关键字指定连接条件。

select * 
from student join takes on student.ID = takes.ID;

这与自然查询的结构是一样的，唯一的区别在于查询结果ID出现两次，一次是student中的，一次是takes结果中的。如果希望ID只出现一次，可以这么做。

select student.ID as ID,name,dept_name,tot_cred,
  course_id,sec_id,semester,year,grade
from student join takes on student.ID = takes.ID; 

爱思考的读者会发现，on关键字似乎可以被where所替代，那它是不是一个冗余的语法？实际上，on关键字在外连接中与where表现是不同的，其次，如果使用on作为连接条件，并在where字句中出现其余的条件，sql查询会更加清晰易懂。

1.3 外连接

假设我们希望查询所有学生的个人信息与选修的课程，可能会想到如下检索。

select * from student natural join takes

不过如果一个学生没有选修课程，就不会出现在这个结果中。

我们可以改用外连接来实现我们的需求。外连接与我们已经学习过的连接运算类似，但是它会通过在结果中创建包含空值的元组，来保留那些在连接中会丢失的元组。

外连接分为三种，

• 左外连接。只保留连接关键字之前的关系的元组。
• 右外连接。只保留连接关键字之后的关系的元组。
• 全外连接。保留出现在两个关系中的元组。
• 相比较而言，我们之前学习的不保留未匹配元组的连接运算被称为内连接运算。

比如"查询所有学生的个人信息与选修的课程"可以这样用左外连接实现。

select * 
from student natural left outer join takes;

查询“一门课程也没有选修的学生”。

select ID
from student  natural left outer join takes
where course_id is null;

左外连接与右外连接是对称的，用右外连接实现"查询所有学生的个人信息与选修的课程"。

select * 
from take natural right outer join student ;

全外连接可以看做左外连接与右外连接的并运算。考虑查询，“显示Comp.Sci系中所有学生以及他们在2017年春季选修的所有课程段的列表。在2017年春季选修的所有课程段都必须显示。”

select *
from (select * 
  from student
  where dept_name = 'Comp.Sci')
  natural full outer join
  (select *
  from takes
  where semester = 'Spring' and year = 2017);

在外连接中，where和on关键字表现是不同的。on会作为外连接声明的一部分，而where却不是。使用where时不会补全具有空值的元组，使用on则会。

另外，常规连接也被称为内连接，可以使用缺省的关键字inner。

2.视图

让所有用户看到数据库关系中的完整集合并不合适，我们可以通过SQL授权来限制对关系的访问，但是如果仅需要向用户隐藏一个关系中的特定数据，可以使用视图。

除了安全型的考虑，视图还可以通过定制化更好的匹配特定用户的需求。

2.1 视图定义

创建视图语法是。

create view v as <查询表达式>;

考虑需要访问instrutor除了salary外的所有数据的职员。

create view faculty as
  select ID,name,dept_name
  from instructor;

视图在概念上包含查询结果中的元组，但是不进行预计算和存储。我通俗的理解成，创建视图是创建了一个规则，使用视图时再根据规则进行计算。

2.2 在SQL查询中使用视图

创建视图后可以像使用数据表一样使用视图。如。

select ID from faculty ;

可以显示的指定视图的属性名称。

create view department_total_salary(dept_name, total_salary) as
  select dept_name,sum(salary)
  from instructor
  group by dept_name;

直观的说，任意给定时刻，视图关系几种的元组集都是使用定义视图查询表达式求值的结果，因此如果定义并存储一个视图关系，一旦定义视图的关系被修改，那么视图就会过期。

一个视图还可以被用到另一个视图的定义中去。

2.3 物化视图

某些数据库系统中的视图关系保证：如果定义视图的实际关系发生改变，则视图也跟着修改以保持更新，这样的视图被称为物化视图。如果视图是物化的，则其计算结果会被存储在计算机中，从而在使用视图时可以更快的运行。

既然物化视图会预计算并存储，那么就需要保持物化视图的更新，保持物化视图一直在最新的状态的过程被称为物化视图维护，或者视图维护。这种维护策略可以是实时维护，周期维护，惰性维护(被使用时才更新)，人工维护等，支持的策略与数据库产品有关。

物化视图对于频繁使用的视图有帮助，对大型关系的聚集运算也较为适用，需要平衡其存储代价与性能开销。

2.4 视图更新

对视图进行增删改可能会带来严重的问题，因为用视图表达的修改必须被翻译为对数据库关系的实际修改。一般不允许对视图进行更新。不同的数据库可能会指定不同的条件，在满足这些条件的前提下可以对视图进行更新，具体可以参考其系统手册。

一般说来，如果定义视图的查询满足下面条件，那么称SQL视图是可更新的。

• from字句中只有一个数据库关系
• select子句中只包含关系的属性名，并不包含任何的表达式、聚集或者distinct声明。
• 没有出现在select子句中的任何属性都可以取null值。也就是说，这些属性没有非空约束，也不构成主码的一部分。
• 查询中不包含有group by或者having子句。
• 不过要注意，即使满足上面的限制条件，仍然不一定可以将数据顺利插入视图。定义如下视图。

create view history_instructors as
select *
from instructor
where dept_name = 'History';

考虑如下查询。尝试向history_instructors视图中插入元组(‘25566’,‘Brown’,‘Biology’,10000)。这个元组可以被插入instructor关系中，但是不满足视图的选择要求dept_name = 'History'。他不应该出现在视图history_instructors中。

我们可以在视图定义的末尾添加with check option子句做到这一点，如果新值满足where子句的条件，就可以插入视图，否则，数据库系统会拒绝该插入操作。

SQL:1999对于视图有更加复杂的规则集。这里不讨论。

触发器机制提供了另外一种视图修改数据库的机制，它更加可取，后续文章将详细介绍。