跳转至

Chapter 4 Intermediate SQL

文本统计：约 2532 个字 • 112 行代码

4.1 Joined Relations

Join operations take two relations and return as a result another relation. They are typically used as sub-query expressions in the from clause, join 操作常被应用于 from 语句中

Join condition – defines which tuples in the two relations match, and what attributes are present in the result of the join.

Join type – defines how tuples in each relation that do not match any tuple in the other relation (based on the join condition) are treated.

4.2 SQL Data Types and Schemas

4.2.1 Built-in Data Types in SQL

在 3.2 处已阐述

4.2.2 User-Defined Types

create type construct in SQL creates user-defined type

create type Dollars as numeric (12,2) final

Note

final 关键字的意思是这个类型不能被继承，但并非所有数据库系统都支持 final 关键字

4.2.3 Domains

create domain construct in SQL-92 creates user-defined domain types

create domain person_name char(20) not null

Types and domains are similar. Domains can have constraints, such as not null, specified on them.

domain 相比 types 来说可以有条件，再举一个例子

create domain degree_level varchar(10)
constraint degree_level_test
check (value in (’Bachelors’, ’Masters’, ’Doctorate’));

4.2.4 Large-Object Types

Large objects (photos, videos, CAD files, etc.) are stored as a large object.

blob: binary large object -- object is a large collection of uninterpreted binary data (whose interpretation is left to an application outside of the database system)

MySQL BLOB datatypes:

  • TinyBlob ： 0 ~ 255 bytes.
  • Blob： 0 ~ 64K bytes.
  • MediumBlob ： 0 ~ 16M bytes.
  • LargeBlob : 0 ~ 4G bytes.

clob: character large object -- object is a large collection of character data

Note

When a query returns a large object, a pointer is returned rather than the large object itself.

4.3 Integrity Constraints

Integrity constraints guard against accidental damage to the database, by ensuring that authorized changes to the database do not result in a loss of data consistency.

4.3.1 Not Null and Unique Constraints

(1) not null

name varchar(20) not null
budget numeric(12,2) not null

声明 name 和 budget 非空

(2) unique \((A_1, A_2, …, A_m)\)

The unique specification states that the attributes A1, A2, … Am form a super key ( × candidate key) . 回顾一下super key 和 candidate key 的定义

Candidate keys are permitted to be null (in contrast to primary keys).

4.3.2 The check clause

check (P), where P is a predicate

Example

ensure that semester is one of fall, winter, spring or summer

create table section (
    course_id varchar (8),
    sec_id varchar (8),
    semester varchar (6),
    year numeric (4,0),
    building varchar (15),
    room_number varchar (7),
    time slot id varchar (4), 
    primary key (course_id, sec_id, semester, year),
    check (semester in (’Fall’, ’Winter’, ’Spring’, ’Summer’))
);

4.3.3 Referential Integrity

Ensures that a value that appears in one relation for a given set of attributes also appears for a certain set of attributes in another relation.

Example

If “Biology” is a department name appearing in one of the tuples in the instructor relation, then there exists a tuple in the department relation for “Biology”.

Let A be a set of attributes. Let R and S be two relations that contain attributes A and where A is the primary key of S. A is said to be a foreign key of R if for any values of A appearing in R these values also appear in S.

Cascading Actions in Referential Integrity 这部分的内容在3.3.1上已阐述

4.3.4 Integrity Constraint Violation During Transactions

这里以人的数据库为例解释如何避免违反完整性约束

create table person (
    ID  char(10),
    name char(40),
    mother char(10),
    father  char(10),
    primary key (ID),
    foreign key (father) references person,
    foreign key (mother) references  person);

How to insert a tuple without causing constraint violation ?

  • 在插入一个新的person之前，先将他的父母亲的信息插入到person表中。这样，在插入新person时，其mother和father字段所引用的记录就已经存在，不会违反外键约束。
  • 如果允许mother和father字段可以为空（即不是NOT NULL），那么可以在插入新person时，暂时将这两个字段设为NULL，待所有人的信息都插入完毕后，再进行相应的更新操作。但是，如果mother和father字段被声明为NOT NULL，则此方法不可行。
  • 某些数据库系统支持延迟检查完整性约束，直到整个事务提交时才进行检查。这样，在事务执行过程中，即使暂时违反了某些约束条件，只要在事务结束前能够恢复一致性，就不会报错。这种方法适用于复杂的事务处理场景。

4.3.5 Assertion

create assertion <assertion-name> check <predicate>;

Example

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’))

但是由于这个操作代价太高，很多数据库系统都没有实现

4.4 Views

A view is defined using the create view statement which has the form

create view v as < query expression >

where <query expression> is any legal SQL expression. The view name is represented by v.

Once a view is defined, the view name can be used to refer to the virtual relation that the view generates. View definition is not the same as creating a new relation by evaluating the query expression 视图被认为是一个虚拟的表，并没有创建一个新的表

A view definition causes the saving of an expression; the expression is substituted into queries using the view. 视图的作用在于简化表达，一些要在原表格中进行的复杂操作，可以在视图上简化

Example

A view of instructors without their salary

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

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
            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’;

4.4.1 Update of a View

我们先创建了一个视图

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

我们可以通过视图给原数据表中添加数据

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

这个语句跟下面这个语句相同

 insert into  instructor values (’30765’, ’Green’, ’Music’, null);

一些限制

注意到通过视图添加时，在原表中但不在视图中的属性被设置为 null，如果该属性被设置为 not null，那么这样的插入就不对了。

同时通过视图插入的时候视图中应当包含主键，否则也无法插入。

总结来说：

大多数SQL实现只允许在简单视图（updatable views）上进行更新操作，这些视图需要满足以下条件：

  • FROM子句：只能包含一个数据库关系（即单个表）。

  • SELECT子句：只能包含关系的属性名，不能包含表达式、聚合函数或DISTINCT关键字。

  • 未列出的属性：任何未在SELECT子句中列出的属性可以设置为NULL。

  • GROUP BY和HAVING子句：查询中不能包含GROUP BY或HAVING子句。

4.4.2 Materialized Views

Materializing a view: create a physical table containing all the tuples in the result of the query defining the view

If relations used in the query are updated, the materialized view result becomes out of date，it need to maintain the view, by updating the view whenever the underlying relations are updated.

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

select dept_name
from departments_total_salary
where total_salary > (select avg(total_salary) from departments_total_salary );

4.4.3 View and Logical Data Indepencence

If relation S(a, b, c) is split into two sub relations S1(a,b) and S2(a,c). How to realize the logical data independence? 

create table S1 …;      
create table S2 …;

insert into S1 select a, b from S;
insert into S2 select a, c from S;

drop table S;

这样我们将 S 分为了两个子表，接下来我们创建一个视图 S

create view S(a,b,c) as select a,b,c from S1 natural join S2;

那么对于视图的操作就可以等价于对子表的操作，且保持逻辑数据独立性

select * from S where …  等价于 select * from S1 natural join S2 where …

insert into S values (1,2,3) 
等价于
insert into S1 values (1, 2);
insert into S2 values (1 ,3);

4.5 Indexes

Indices are data structures used to speed up access to records with specified values for index attributes

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) )
create index studentID_index on student(ID)

加速查询，底层数据结构常为 B+ 树

4.6 Transactions

Atomic transaction, either fully executed or rolled back as if it never occurred

Isolation from concurrent transactions

Transactions begin implicitly, ended by commit work or rollback work. But default on most databases: each SQL statement commits automatically

  • Can turn off auto commit for a session (e.g. using API), In MySQL: SET AUTOCOMMIT=0;

Example

SET AUTOCOMMIT=0;
      UPDATE account SET balance=balance -100 WHERE ano =‘1001’;
      UPDATE account SET balance=balance+100 WHERE ano =‘1002’;
COMMIT;

那么这两个操作就为一个事务

ACID Properties

A transaction is a unit of program execution that accesses and possibly updates various data items.To preserve the integrity of data the database system must ensure:

Atomicity. Either all operations of the transaction are properly reflected in the database or none are.

  • 事务是一个不可分割的操作单元，所有操作要么全部成功，要么全部失败。
  • 如果事务中的某个操作失败，整个事务将被回滚，数据库状态恢复到事务开始前的状态。

Consistency. Execution of a transaction in isolation preserves the consistency of the database.

  • 事务执行前后，数据库必须保持一致性。
  • 事务的执行不能破坏数据库的完整性约束（如主键、外键等）。

Isolation. Although multiple transactions may execute concurrently, each transaction must be unaware of other concurrently executing transactions. Intermediate transaction results must be hidden from other concurrently executed transactions.

  • 并发执行的事务之间相互独立，一个事务的执行不应影响其他事务。
  • 数据库系统通过隔离级别来控制事务之间的相互影响。

That is, for every pair of transactions \(T_i\) and \(T_j\), it appears to \(T_i\) that either \(T_j\), finished execution before \(T_i\) started, or \(T_j\) started execution after \(T_i\) finished.

Durability. After a transaction completes successfully, the changes it has made to the database persist, even if there are system failures.

  • 一旦事务提交，其结果是永久性的，即使系统崩溃也不会丢失。
  • 数据库系统会将已提交的事务结果写入持久存储（如磁盘）。

4.7 Authorization

Forms of authorization on parts of the database:

  • Select - allows reading, but not modification of data.
  • Insert - allows insertion of new data, but not modification of existing data.
  • Update - allows modification, but not deletion of data.
  • Delete - allows deletion of data.

Forms of authorization to modify the database schema

  • Resources（MySQL：Create） - allows creation of new relations.
  • Alteration - allows addition or deletion of attributes in a relation.
  • Drop - allows deletion of relations.
  • Index - allows creation and deletion of indices.
  • Create view（MySQL） – allows creation of views.

4.7.1 Authorization Specification in SQL

The grant statement is used to confer authorization

grant <privilege list>  // privilege：权限
        on <relation name or view name> to <user list>

<user list> is:

  • a user-id
  • public, which allows all valid users the privilege granted
  • A role (more on this later)

Granting a privilege on a view does not imply granting any privileges on the underlying relations. 授予视图上的权限并不意味着授予对基础关系的任何权限。

The grantor of the privilege must already hold the privilege on the specified item (or be the database administrator). 权限的授予者必须已经拥有对该指定项的权限（或为数据库管理员）

Privileges in SQL

grant select on instructor  to U1, U2, U3
grant select on department  to public
grant update (budget) on department  to U1,U2
grant all privileges on department   to U1

4.7.2 Revoking Authorization in SQL

The revoke statement is used to revoke authorization. 收回权限

revoke <privilege list>
on <relation name or view name> 
from <user list>

<privilege-list> may be all to revoke all privileges the revokee may hold.

If <revokee-list> includes public, all users lose the privilege except those granted it explicitly.

Note

If the same privilege was granted twice to the same user by different grantees, the user may retain the privilege after the revocation.

All privileges that depend on the privilege being revoked are also revoked.

4.7.3 Roles

（1）创建一个 role

create role instructor;

（2）用 grant 将角色赋予某个用户

grant instructor to Amit;

（3）权限可以授权给 role

grant select on takes to instructor;

相应的用户也被赋予了相应的权限

（4）role 可以构成链

create role dean;
grant instructor to dean;
grant dean to Satoshi;

4.7.4 Authorization on Views

create view geo_instructor as
(select *
from instructor
where dept_name = ’Geology’);

将对geo_instructor视图的select权限授予geo_staff角色或用户。

grant select on geo_instructor  to  geo_staff

4.7.5 Other Authorization Features

（1）在数据库中，不是所有用户都有权修改表结构或创建外键约束。REFERENCES权限允许特定用户在指定的列上创建外键，从而维护引用完整性。

grant reference (dept_name) on department to Mariano;

这条命令授予用户Mariano在“department”表的“dept_name”列上创建外键的权限。

（2）授予权限并允许再授予 with grant option

grant select on department to Amit with grant option;

DBA（数据库管理员）将对department表的SELECT权限授予用户Amit，并且Amit可以进一步将这个权限授予其他用户。这样Amit现在不仅自己可以查询department表，还可以将这个权限转授给其他用户。

（3）级联撤销权限

revoke select on department from Amit, Satoshi cascade;

从Amit和Satoshi那里撤销对department表的SELECT权限，并且级联地撤销所有由他们转授出去的相同权限。

（4）限制性撤销权限

revoke select on department from Amit, Satoshi restrict;

从Amit和Satoshi那里撤销对department表的SELECT权限，并且级联地撤销所有由他们转授出去的相同权限。

（5）撤销转授权限的能力

revoke grant option for select on department from Amit;

撤销Amit对department表SELECT权限的转授能力，但保留Amit自己的SELECT权限。

Amit仍然可以查询department表，但不能再将这个权限转授给其他用户，但这不影响之前Amit已经转授出去的权限。

评论区

对你有帮助的话请给我个赞和 star => GitHub stars
欢迎跟我探讨！！！