【摘要】升级了。
来见见中级段位的SQL查询：

视图定义、事务、完整性约束、关于SQL数据定义的更多细节、授权 view definition, transactions, integrity constraints, more details regarding SQL data definition, and authorization.

（英文精编·6版·…

升级了。

来见见中级段位的SQL查询：

视图定义、事务、完整性约束、关于SQL数据定义的更多细节、授权
view definition, transactions, integrity constraints, more details regarding SQL data definition, and authorization.

（英文精编·6版·第四章）

1.Join Expressions

1.1Join….on 可以表达更丰富的语义

查询语句简短
和where语句结合，使得查询语句可读性好

Select * 
From student join takes on student.ID=takes.ID

  
 
  1
  2

与下面的查询相同

Select * 
From student ,takes 
Where student.ID=takes.ID

  
 
  1
  2
  3

1.2Outer Joins

在之前写的DMBS第三篇中提及这个外连接：
在这里插入图片描述

先来回顾一下natural join的执行结果特点：

假设 r1 naturla join r2
如果r1中有tuple不与r2匹配，那么这个tuple就会被drop
自然连接又叫做内连接（Inner Join）

而外连接，就会保留不匹配的tuple(s)

左外连接，保留左边表的nonmatched元组
右外连接保留右边。
全外连接，全保留。

SQL语句如下：

-- 左外连接
course left outer join prereq on 
course.course_id = prereq.course_id

-- 右外连接
course natural right outer join prereq

-- 全外连接
course full outer join prereq using (course_id)

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9

小结：
1.join时，若不用前缀outer join时候，默认都是inner join
2.natural inner join is equivalent to natural join

2.Views

In our examples up to this point，
都是逻辑模型级别上操作

What & Why

视图—— 隐藏信息
视图提供了一种机制，可以向某些用户的视图隐藏某些数据
视图是一种虚表，在数据库中并不实际存在，
数据库中只存在视图的定义，
执行视图定义时才从基本表中获得数据。

在某些情况下，不希望所有用户都看到整个逻辑模型——安全考虑
创建一个个性化的关系集合——用户体验

View Definition

使用 create view..as..< query expression >
必须要使用视图名，和查询表达式
视图关系在概念上包含查询结果中的元组，
但不是预先计算和存储的
像一组查询表达式的别名，而不是一个实际的表
访问视图关系，则它的元组都是即时计算查询结果创建的

Example

1.一个不显示薪水的讲师的视图

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

  
 
  1
  2
  3

Faculty是一个虚拟关系

一个View的高级用法：

在视图的基础上建立视图

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 AND course.dept_name = 'Physics'
 	AND section.semester = 'Fall' AND section.year = '2009';
 -- --- - -- - - - -- - - - - - - -- - -
create view physics_fall_2009_watson as select course_id,room_number from physics_fall_2009 where building = 'Watson';

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12

小结：

o 综合信息的局部描述，高屋建瓴的焦点访谈
o 它可能是冰山的一角，大象的耳朵，多面体的一条边，
o 它是虚拟的关系，计算的公式，虚中有实，实中有虚
o 也可以是信息隐藏的面纱，安全的工具和欺骗的方式。

Materialized Views

这是个： 物化视图/实体化视图

Certain database systems allow view relations to be stored,
but they make sure that,
if the actual relations used in the view definition change,
the view is kept up-to-date.

Such views are called materialized views.
物化视图

The process of keeping the materialized view up-to-date is called
materialized view maintenance (or often, just view maintenance)
又叫做视图维护——view maintenance

Update of a View

虚关系更新实值

如果我们用view来表示更新、插入或删除，会有大问题。
因为视图的字段名不一定和实际表中的字段名一致
则必须
将以视图形式表示的修改转换为逻辑模型中实际关系的修改。

Example

1.向前面定义的教员视图添加一个新的元组

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

  
 
  1

实际上会给instructor 表中添加这条元组，
但是instructor中的salary字段怎么办？
有两种合理的方法来处理这个插入:
1.拒绝插入，并向用户返回错误消息。
2.将一个元组(’ 30765 ‘，’ Green ‘， ’ Music ‘， null)插入到教员关系中。

2.创建视图 instructor_info,然后插入一条记录,提示不能成功执行

create view instructor_info 
as 
 select ID, name, building 
 from instructor, department 
 where instructor.dept_name= department.dept_name;
 --插入记录: 
 insert into instructor_info 
values ('69987','White','Taylor');

  
 
  1
  2
  3
  4
  5
  6
  7
  8

注意: 该插入记录的操作不能成功执行,
因为该视图不是一个可更新视图；
因为这个view用到了两个表instructor和department
而，插入操作，要插入实际的表，而插入哪个表是ambiguous的

小结：
1.大多数SQL实现只允许对简单视图进行更新
2.The from clause has only one database relation. 只对一个表
3.The select clause contains only attribute names of the relation,
and does not have any expressions, aggregates,
or distinct specification.没有表达式
4.Any attribute not listed in the select clause can be
set to null 没有列出的属性可以为null，也不是主键或主键的一部分
5.The query does not have a group by or havingclause. 没有分组

3.Transactions

一个事务由一系列查询和/或更新语句组
在执行SQL语句时隐式地执行事务
Ended by commit work or rollback work
commit work: 提交当前事务
由事务执行的更新，在数据库中永久生效 。
rollback work：使当前事务回滚
撤消事务中SQL语句执行的所有更新。

如果程序在没有执行这些命令的情况下终止，更新将被提交或回滚。

4.Integrity Constraints

做一些约束，不至于犯下（大）错：比如

(防止用户对数据库的操作)导致数据一致性的丢失
这些约束常常 与语义或常识有关：
是数据库模式设计过程的一部分
具体有这些完整性约束：
1.Constraints on a Single Relation

2.Referential Integrity

Example

非空约束 not null

create table instructor (
ID varchar(5), 
name varchar(20) not null, /*非空约束*/ 
dept_name varchar(20), 
salary numeric(8,2) check (salary > 29000), 
primary key (ID), 
foreign key (dept_name) references department on delete set null );

  
 
  1
  2
  3
  4
  5
  6
  7

unique 约束

create table instructor (
ID varchar(5), 
name varchar(20) unique, /*unique 约束*/ 
dept_name varchar(20), 
salary numeric(8,2) check (salary > 29000), 
primary key (ID), 
foreign key (dept_name) references department on delete set null );

  
 
  1
  2
  3
  4
  5
  6
  7

用户自定义 check 约束

子句check（ P） 指定一个谓词P，让关系中的每个元组都必须满足。

create table section (
course_id varchar(8), 
sec_id varchar(8), 
semester varchar(6) check (  /* check*/
	semester in ('Fall','Winter','Spring','Summer')), 
year numeric(4,0) check (
	year > 1701 and year < 2100), 
building varchar(15),
room_number varchar(7),
time_slot_id varchar(4), 
primary key (course_id, sec_id, semester, year) );

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11

Referential Integrity

参照完整性中的级联动作:
如果存在跨多个关系的外键依赖链，
则链一端的删除或更新可以传播到整个链。

Assertions 断言

断言：我们希望数据库始终满足的条件的谓词。
create assertion <assertion-name> check <predicate>;

create assertion credits earned constraint check
(not exists (select ID from student where tot cred <> (select sum(credits) from takes natural join course where student.ID = takes.ID and grade is not null and grade<> 'F')
  
 
  1
  2
  3
  4
  5
  6
  7
  8

Date、time、timestamp

暂不做解释，先参考前辈的博客吧~~

date '2021-05-03'
time '09:30:00'
timestamp '2021-05-03 10:29:01.45'

  
 
  1
  2
  3

Default Values

为属性指定默认值

create table student(
	ID varchar (5),
	name varchar (20) not null,
	dept name varchar (20),
	tot_cred numeric (3,0) default 0,
primary key (ID));

  
 
  1
  2
  3
  4
  5
  6

Index Creation

索引，就是要提高query效率。避免不必要的检索~

Example

在学生name属性上，创建索引

create index studentname_index on student(name);

  
 
  1

User-Defined Types

Create type 子句可用于定义新类型

create type construct in SQL creates user-defined type
create type Dollars as numeric (12,2) final 
//定义

create table department 
(dept_name varchar (20), 
building varchar (15), 
budget Dollars); //应用

  
 
  1
  2
  3
  4
  5
  6
  7
  8