十、连接和视图

Join Expressions

select *

from student, takes

where student.ID =takes.ID;

=

select *

from student natural join takes;

=

select *

from student join takes using (ID);

=

select *

from student jointakes on student.ID=takes.ID;

 

Danger in natural join: beware of unrelatedattributes with same name which get equated incorrectly.

The difference between  join…on and natural join is that the resultof join…on has the ID attribute listed twice.

 

Outer Joins（外连接）

An extension of thejoin operation that avoids loss of information. (避免信息丢失)

FullOuter Join（全外连接）

Left OuterJoin（左外连接）

Right OuterJoin（右外连接）

例如：1.Display a list of all students in the Comp. Sci. department, alongwith the course sections, if any, that they have taken in Spring 2009; allcourse sections from Spring 2009 must be displayed, even if no student from theComp. Sci. department has taken the course section.

select *

    from (select*

            from student

            where dept name= ‟Comp. Sci‟)

             naturalfull outer join

         (select*

            from takes

            where semester = ‟Spring‟ and year = 2009);

2.

Findall students who have not taken a course.

     select ID

   from studentleft outer join takes on student.ID=takes.ID

   wherecourse_id is null;

 

连接类型和连接条件

 

 

The default jointype, when the join clause is used without the outer prefix is the inner join.

默认情况下，除去outer就相当于innerjoin。

select *

from student join takes on student.ID=takes.ID

=

select *

from student inner join takes on student.ID=takes.ID;

 

 

Views（视图）

In some cases, it isnot desirable(令人满意的) for all users to see the entire logical model.

一定程度上实现信息隐藏。

A viewprovides a mechanism to hide certain data from the view of certain users.

 

如何创建一个视图：

SQL provides thecreate view command to create a view, which is stored in the database in thelong

run ：

createview v as <query expression>

l<queryexpression> is any legal expression

lthe view nameis represented by v

例如：A view of instructors without their salary：

create view facultyas 

    select ID, name, dept_name

    from instructor

 

Createa view of department salary totals ：

create view  departments_total_salary(dept_name,total_salary) as    

selectdept_name, sum (salary)    

frominstructor

groupby dept_name;

 

 

视图和关系的区别：

View definition isnot the same as creating a new relation by evaluating the query expression.

Rather, a viewdefinition causes the saving of an expression; the expression is substitutedinto queries using the view. (存储的是表达式)

 

Views Defined Using Other Views（用其他的视图来创建视图）

create view physics_fall_2009 as

   select course.course_id, sec_id, building,room_number

   from course, section

   where course.course_id = section.course_id

              andcourse.dept_name = ‟Physics‟

              andsection.semester = ‟Fall‟

              and section.year= ‟2009‟;

 

create viewphysics_fall_2009_watson as

    select course_id, room_number

    from physics_fall_2009

    where building= ‟Watson‟;

=

create viewphysics_fall_2009_watson as

      select course_id, room_number

      from 

(selectcourse.course_id, building, room_number

          from course, section

          where course.course_id =section.course_id

               andcourse.dept_name = ‟Physics‟

               andsection.semester = ‟Fall‟

               andsection.year = ‟2009‟)

      where building= ‟Watson‟;

 

 

Materialized Views（物化视图）

Materializinga view: create a physical table containing all the tuples in the result of thequery defining the view.

当某个视图经常被使用，且其执行的相应的查询语句复杂度较高，这时候就可以考虑，使用物化视图，真正在数据库中建立一个表，存入相应数据。

If relations used in the query are updated, thematerialized view result becomes out of date.

So in order to maintain the view, we need to updatethe view whenever the underlying relations are updated.

 

删除视图

The Drop Viewcommand deletes the definition the view from the data dictionary.

drop view view_name;

 

视图的更新

Adda new tuple to faculty view which we defined earlier.

insert into faculty values (‟30765‟,‟Green‟, ‟Music‟);

处理这类语句，有两种方式：

Two reasonableapproaches:

– Reject theinsertion 

– Insert the tuple

(‟30765‟, ‟Green‟, ‟Music‟, null)

  into the instructor relation

为了保持一致性，必须将这个语句，转化未对实际关系的修改。

ButSome Updates cannot be Translated Uniquely.

比如：

create view instructor_info as

        select ID, name,building

       from instructor, department

       whereinstructor.dept_name=department.dept_name;

insert into instructor_info values (‟69987‟,‟White‟, ‟Taylor‟); 插入这条语句。

l which department, if multipledepartments in Taylor?

l what if nodepartment is in Taylor?

在转化时，instructor_info里面的building中如果有多个或者没有department,那么就不知道将那一个department作为插入的值。

MostSQL implementations allow updates only on simple views, said to be updatable.

– The from clausehas only one database relation. 

– The select clausecontains only attribute names of the relation, and does not have anyexpressions, aggregates(聚集), or distinct specification.

– Any attribute notlisted in the select clause can be set to null. 

– The query does nothave a group by or having clause.

还有一些其他的情况：

例如：

create viewhistory_instructors as

   select *

   from instructor

   where dept_name= ‟History‟;

if we insert (‟25566‟, ‟Brown‟, ‟Biology‟, 100000) intohistory_instructors, database may reject this query.

实际上在插入数据时，数据库系统都会对插入数据和视图进行检查，如果不满足数据库所定义的规则，那么数据库将不会执行这条插入语句。 if a tuple inserted into the view doesnot satisfy the view‟swhere clause condition, theinsertion is rejected by the database system.