MYSQL数据库课程-1至6章复习笔记

第一章 数据库介绍

1.1. 数据库的应用

• 图书馆、教务系统、银行 balabala

1.2. 传统的基于文件的系统

• 定义
• A collection of application programs that perform services for the end-users such as the production of reports. Each program defines and manages its own data.
• 局限性
  • 数据分离和孤立
    • 需要来自多个文件的数据时，会非常麻烦
  • 数据冗余
    • 浪费存储空间 + 破坏数据一致性
  • 数据依赖性
    • 文件结构和应用程序之间的依赖性
  • 文件的不兼容性
  • 文件的查询方式固定，应用程序更新困难

1.3. 数据库

• 定义：
  • A shared collection of logically related data, and a description of this data, designed to meet the information needs of an organization. （逻辑相关的数据的共享集合，以及对数据的描述，被设计来满足组织、机构的数据需求）
• DBMS：
  • 允许用户定义、创建、维护数据库和控制数据库的访问
  • DDL:
    • Specify the data types and structure and constraints on data.
  • DML:
    • 对数据库的增删查改
  • Control access to database:
    • Security, integrity, concurrency control, recovery control, user-accessible
      catalog.
  • Components of the DBMS Environment
    • 硬件
      • computer, network equipment
    • 软件
      • DBMS, application programs, operating system, network software
    • 数据
      • the database contains both the operational data and the meta-data. The structure of the database is called the schema
    • 规程
      • 指的是对数据库的设计和使用进行控制的一组命令和规则
      • 包括说明如何进行下列工作：log on to DBMS, start and stop the DBMS, backup copies of the database, change the structure, improve performance
    • 人
• Roles in the Database Environment
  • 数据管理员
    • 管理数据资源，包括数据库规划；开发和维护的标准，策略和规程；概念 / 逻辑数据库设计
  • 数据库管理员
    • 负责数据库的物理实现，包括物理数据库的设计和实现、安全性和完整性控制、最终运行系统的维护，以及确保应用程序能够满足用户的需求
  • 数据库设计人员
    • 逻辑数据库设计人员：负责标识数据、数据之间的联系，以及对将存储到数据库的数据的约束
    • 物理数据库设计人员：决定逻辑数据库的设计如何以物理的形式实现
  • 程序开发者
    • 开发满足终端用户需求的程序
  • 终端用户
    • 数据库的最终用户
• DBMS的优缺点
  • 优点：
    • Control of data redundancy
    • Data consistency
    • Sharing of data
    • Improved data integrity
    • Improved security
  • 缺点：
    • Complexity
    • Cost of DBMSs
    • Reduced performance
    • Higher impact of a failure

第二章 数据库环境

2.1. The Three-level ANSI-SPARC Architecture

• 目的：
  • 将每个用户的逻辑视图和数据库的物理实现相分离
• 图示：
  • 
• 层次划分：
  • 外部层： – 对应外模式
    • 数据库的用户视图。这一层描述与每一个用户相关的数据库部分
  • 概念层： – 对应概念模式
    • The community view of the database，this level describes what data is stored in the database and the relationships among the data.
  • 内部层： – 对应内模式
    • The physical representation of the database on computer. This level describes how the data is store in the database
  • 物理层：
    • Below the internal level there is a physical level that may be managed by the operating system under the direction of the DBMS.
• 模式：
  • The overall description of the database is called the database schema. There are three different types of schema in the database: external schema, conceptual schema, internal schema.
  • Each external schema is related to the conceptual schema by the external/conceptual mapping
  • The conceptual schema is related to the internal schema through a conceptual/internal mapping
• 内含 / 外延 / 实例
  • 数据库实例（instance）：
    • 在任意时间点上，数据库中的数据构成一个数据库实例
  • 内含（intension）：
    • 数据库模式有时被称作数据库的内含
  • 外延（extension）：
    • 数据库实例被称作数据库的外延
• 数据库独立性：
  • 逻辑数据独立性：
    • Logical data independence refers to the immunity of the external schema to changes in the conceptual schema.
  • 物理数据独立性：
    • Physical data independence refers to the immunity of the conceptual schema to changes in the internal
      schema.

2.2. 数据库语言

• DDL：
  • A language that allows the DBA or user to describe and name the entities, attributes, and relationships required for application, together with any associated integrity and security constraints.
  • The result of compilation of DDL statements is a set of tables stored in special files collectively called the system catalog.
• DML：
  • A language that provides a set of operations to support the basic data manipulation operations on the data held the
    database.
  • DML中，涉及数据检索的语言称为查询语言
  • 分类：
    • 过程式DML： – 非关系型数据库
      • 用户需要告诉系统需要哪些数据，以及如果获取这些数据
    • 非过程式DML： – 关系型数据库
      • 只需要告诉系统需要哪些数据，不需要说明如何检索这些数据

2.3. 数据模型和概念建模

• 数据模型：
  • 定义：
    • An integrated collection of concepts for describing and manipulating data, relationships between data, and constraints on data in an organization.
  • 三个部分：
    • 结构部分 – 数据之间的联系
      • consisting of a set of rules according to which database can be constructed
    • 操作部分 – 描述和操作数据
      • defining the types of operation that are allowed on data;
    • 一组完整的约束
      • ensures that the data is accurate.
• Object-Based Data Models
  • 
• Record-Based Data Models
  • Relational data model
    • in the relational data model, data and relationship are represents as tables, each of which has a number of columns with a unique name.
  • Network data model
    • In the network model, data is represented as collection of records, and relationships are represented by sets, which become pointer in the implementation.
    • 
  • Hierarchical data model
    • It is a restricted type of network model. However, it allows a node to have only one parent.
    • 
• Physical Data Models
  • Physical Data models describe how data is stored in the computer, representing information such as record structures, record ordering, and access paths.
• Conceptual Modeling
  • 

2.4. DBMS的功能

• Data Storage, Retrieval, and Update

  • A DBMS must furnish users with the ability to store, retrieve, and update data in the database

• A User-Accessible Catalog

  • A DBMS must furnish a catalog in which descriptions of data items are stored and which is accessible to users.

• Transaction Support

  • A DBMS must furnish a mechanism which will ensure either that all the updates corresponding to a given transaction are made or that none of them is made

• Concurrency Control Services

  • ADBMS must furnish a mechanism to ensure that the database is updated correctly when multiple users are updating the database concurrency

• Recovery Services

  • A DBMS must furnish a mechanism for recovering the database in the event that the database is damaged in any way.

• Authorization Services.

  • A DBMS must furnish a mechanism to ensure that only authorized users can access the database Support for Data Communication

  • A DBMS must be capable of integrating with communication software

• Integrity Services

  • A DBMS must furnish a means to ensure that both the data in the database and changes to the data follow certain rules

• Services to Promote Data Independence

  • A DBMS must include facilities to support the independence of programs from the actual structure of the database.

• Utility Services – 实用服务程序

  • A DBMS should provide a set of utility services
    • Import facilities
    • Monitoring facilities
    • Statistical analysis program, to examine performance
    • Index reorganization facilities
    • Garbage collection and reallocation

2.5. 多用户DBMS结构

• Teleprocessing – 远程处理
  • The traditional architecture for multi-user systems was teleprocessing, where there is single mainframe with a number of terminals attached.
  • 用户终端无处理能力，所有指令都由主计算机执行
• File - Server
  • 
  • File-server is connected to several workstations across a network
  • Database resides on file-server
  • DBMS and applications run on each workstation.
  • Disadvantages include:
    • Significant network traffic.
    • Copy of DBMS on each workstation.
    • Concurrency, recovery and integrity control more complex
• Client - Server
  • 
  • Client-server refers to the way in which software components interact to from a systems.
  • Server holds the database and the DBMS.
  • Client manages user interface and runs applications.
  • Advantages include:
    • wider access to existing databases
    • increased performance
    • possible reduction in hardware costs
    • reduction in communication costs
    • increased consistency
  • Transaction Processing Monitors
    • A Program that controls data transfer between clients and servers in order to provide a consistent environment, particularly for Online Transaction Processing (OLTP).

2.6. 系统元数据

• Repository of information describing the data in the database, that is the meta-data or the ‘data about data’.
• Used by modules such as Authorization Control and Integrity Checker

第三章 关系模型

3.1. Terminology – 术语

• Relational Data structure
  • Relation：
    • A relation is a table with columns and rows.
  • Attribute：
    • Attribute is a named column of a relation.
  • Domain
    • Domain is the set of allowable values for one or more attributes.
  • Tuple
    • A tuple is a row of a relation.
  • Degree – 度
    • The degree of a relation is the number of attributes in a relation
  • Cardinality – 基数、势
    • the cardinality of a relation is the number of tuples in a relation
  • Relational Database
    • A collection of normalized relations with distinct relation names.
• Database Relations
  • Relation schema
    • A named relation defined by a set of attribute and domain name pairs.
    • 用一组属性和域名对定义的具名的关系
  • Relational database schema
    • A set of relation schemas, each with a distinct name.
    • 不重名关系模式的集合
• Properties of Relations
  • 有一个关系名，同一个关系模式各关系不能重名
  • 关系中的每一个单元格都确切包含一个原子值
  • 每个属性都有一个不同的名字
  • 同一属性中的各个值都取自相同的域
  • 各元组互不相同，不存在重复元组
  • 属性的顺序并不重要
  • 理论上讲，元素的顺序也不重要（但是实际上会影响元组的访问效率
• Relation Keys
  • Super Key：
    • An attribute, or a set of attributes, that uniquely identifies a tuple within a relation.
  • Candidate Key：
    • Superkey (K) such that no proper subset is a superkey within the relation.
  • Primary Key：
    • Candidate key selected to identify tuples uniquely within relation.
  • Alternate Key：
    • Candidate keys that are not selected to be primary key
  • Foreign Key：
    • Attribute, or set of attributes, within one relation that matches candidate key of some (possibly same) relation.

3.2. Relational Integrity

• Nulls
  • Represents a value for an attribute that is currently unknown or not applicable for this tuple – 不知道或者不可用
• Entity integrity
  • In a base relation, no attribute of a primary key can be null.
• Referential integrity
  • If foreign key exists in a relation, either foreign key value must match a candidate key value of some tuple in its home relation or foreign key value must be wholly null.
• Enterprise constraints
  • Enterprise Constraints: Additional rules specified by the users or database administrators of a database

3.3. Views

• Terminology
  • Base Relation:
    • A named relation corresponding to an entity in conceptual schema, whose tuples are physically stored in database.
    • 与概念模式中的实体相对应的命名关系，其元组物理存储在数据库中
  • View：
    • The dynamic result of one or more relational operations operating on base relations to produce another relation. A view is a virtual relation that does not necessarily exist in the database but can be produced
      upon request by a particular user, at the time of request.
• Purpose of views
  • Provides powerful and flexible security mechanism by hiding parts of database from certain users.
  • Permits users to access data in a customized way, so that same data can be seen by different users in different ways, at same time.
  • Can simplify complex operations on base relations
• Updating views
  • All updates to a base relation should be immediately reflected in all views that reference that base relation.
    • 基关系的改变应该能立即反映到所有视图中
  • If view is updated, underlying base relation should reflect change. However, There are restrictions on types of modifications that can be made through views:
    • Updates are allowed if query involves a single base relation and contains a candidate key of base relation.
      • 视图只对应一个基关系，且含有基关系的候选键时可以更新
    • Updates are not allowed involving multiple base relations.
      • 视图引用多个基关系时，更新不被允许
    • Updates are not allowed involving aggregation or grouping operations.
      • 含有聚集函数或者group子句时，更新不被允许

第四章 关系代数

五个基本操作：选择，投影，笛卡尔积，并，差

三个扩展操作：连接，集合交，除运算

只有选择和投影是一元运算符

4.1 Unary Operation

• Selection
  • 
  • Works on a single relation R and defines a relation that contains only those tuples (rows) of R that satisfy the specified condition (predicate).
  • More complex predicate can be generated using the logical operators ∧(and), ∨(or) and ﹗(not).
• Projection
  • 
  • 投影运算作用于单个关系R，得到由R的一个垂直子集够成的新关系，该垂直子集抽取R中指定属性上的值并去掉了重复元组

4.2 集合运算

• Union – 并
  • R ∪ S
  • Union of two relations R and S defines a relation that contains all the tuples of R, or S, or both R and S, duplicate tuples being eliminated.
  • R and S must be union-compatible（满足并相容性
  • If R and S have I and J tuples, respectively, union is obtained by concatenating them into one relation with a maximum of (I + J) tuples.
• Set difference – 差
  • R - S
  • Defines a relation consisting of the tuples that are in relation R, but not in S.
  • R and S must be union-compatible.
• Intersection – 交
  • R ∩ S = R - (R - S)
  • Defines a relation consisting of the set of all tuples that are in both R and S
  • R and S must be union-compatible.
• Cartesian product – 笛卡尔积
  • R x S
  • Defines a relation that is the concatenation（级联、并列、并置） of every tuple of relation R with every tuple of relation S.
• Rename
  • 
  • Provides a new name S for the expression E, and optionally names the attributes as a1 , a2 ,…, a n .
  • It can be rewritten： S (a1 , a2 ,…, a n ) ← E

4.3 连接操作

• Theta join – θ 连接
  • 
  • 
  • θ 连接运算定义一个关系，它包含R和S的笛卡尔乘积中所有满足谓词F的元素
  • 谓词F的格式为
    • R.a [＜, ≤, >, ≥, ＝, ≠] S.b
• Equijoin – 等值连接
  • θ连接中，当谓词的格式为 R.a = S.b 时，就是等值连接
• Natural join – 自然连接
  • 
  • 自然连接是关系R和S在所有公共属性x上的等值连接。但是在得到的结果中，每个公共属性只保留一次，其余删除
• Outer join – 外连接(默认以自然连接为基础)
  • To display rows in the result that do not have matching values in the join column, use Outer join.
  • Left outer join
    • 
    • Left outer join is join in which tuples from R that do not have matching values in common columns of S are also included in result relation.
  • Right outer join
    • 
    • Right outer join is join in which tuples from S that do not have matching values in common columns of R are also included in result relation.
  • Full outer join
    • 
    • Keeps all tuples in both relations, padding tuples with nulls when no matching tuples are found.
• Semi join – 半连接 (默认以自然连接为基础)
  • 
  • 半连接运算定义的关系包含R中的这样一些元组， 他们参与了R与S满足谓词F的连接
  • 
• Division
  • R ÷ S
  • 除法运算（R ÷ S）定义了属性集合C上的一个关系，该关系的元组与 S 中的每个元组的组合都能在R中找到匹配元组

第五章 SQL : Data Manipulation

5.1 Introduction to SQL

• Objectives of SQL
  • 允许用户：
    • 建立数据库和关系结构
    • 完成基本数据管理任务，诸如关系中的插入、修改和删除
    • 完成简单或者复杂的查询
  • 必须是可移植的
  • 数据库语言必须功能丰富、结构简洁、易学易用
  • 只包含定义和操作指令，不包含流控制指令

5.2 Writing SQL Commands

• SQL语句包括保留字和用户自定义语句
• SQL语句不区分大小写

5.3 Data Manipulation

5.3.1 Simple Queries

• Select 语句

  • 

  • 子句的顺序不能改变

  • 只有select 和 from 是强制的

  • 用 distinct 消除重复

  • 用As子句给属性命名

    • select staffNo, fName,Lname,salary/12 AS monthlySalary from Staff;
      

  • and 和 or 关键字可以组成复合搜索条件

  • 范围查询

    • between 和 not between
    • between 包括范围的端点

  • Set membership search ：in / not in

  • Pattern match search (Like / not like)

    • % ： 表示一个或者多个字符
    • _ ：表示任何的单个字符

  • null search: is null / is not null

    • 判断是不是空，不能直接用等号判断

5.3.2 Sorting Results

• Single-column ordering
• Multiple column ordering

5.3.3 Using the SQL Aggregate Functions

• 五个聚集函数
  • count – 统计个数
  • sum – 计算和
  • avg – 计算平均值
  • min – 统计最小值
  • max – 统计最大值
• 聚集函数作用于表的单个列，返回单个值
• count. min, max可以用于非数值类型，但是sum和avg只能用于数值类型
• 除了count(*)，其他聚集函数都要先消除null值，且返回的值也是not null
• function(distinct column-name)，这样可以消除重复
• Aggregate functions can be used only in SELECT list and in HAVING clause.
• If SELECT list includes an aggregate function and there is no GROUP BY clause, SELECT list cannot reference a column out with an aggregate function.

5.3.4 Grouping Results

• select和group by紧密结合，select列表中的项要求每个组都有且仅有一个结果
• select list中的项要么出现在group by子句中，要么是聚集函数
• where和group by同时出现，先用where过滤不满足条件的元组
• ISO标准规定应用group by子句时，两个空值被认为是相等的，即如果两行在同一分组列上值都为空值，并且在不含空值的分组列上值相等，则这两行将被合并在同一组中
• Restricted Groupings (HAVING clause)
  • having子句使用的列名必须出现在group by子句列表中或者在聚集函数中，实际中，having子句的条件运算至少包括一个聚集函数，否则的话，可以把查询条件移到where子句中来过滤单个行

5.3.5 Subqueries

• A subselect can be used in WHERE and HAVING clauses of an outer SELECT, where it is called a subquery or nested query.
• 子查询也可以出现在 from里面
• Subselects may also appear in INSERT, UPDATE, and DELETE statements
• 子查询限制
  • order by 子句不能用于子查询（虽然可以用在最外面的select语句中）
  • 子查询select列表必须由单个列名或表达式组成，除非子查询使用了关键字exists
  • 在默认的情况下，子查询中列名取自子查询的from中给出的表，也可以通过限定列名的办法指定取自外查询的from子句中的表
  • 当子查询是比较表达式中的一个操作数时，子查询必须出现在表达式的右面

5.3.6 ANY and All

• ANY and ALL may be used with subqueries that produce a single column of numbers.
• With ALL, condition will only be true if it is satisfied by all values produced by subquery.
• With ANY, condition will be true if it is satisfied by any values produced by subquery.
  • some和any相同
• If subquery is empty, ALL returns true, ANY returns false.
• example：
  • 

5.3.7 Multi-Table Queries

• Simple join

  • from子句中列出所有的表，在where子句中指定表于表连接的规则（等值连接）

  • example:

    • select cSELECT c.clientNo, fName, lName, propertyNo
      FROM Client c, Viewing v
      WHERE c.clientNo = v.clientNo;
      

• Alternative join constructs

  • SQL 提供下面几种可选的连接形式

  • from Client c join viewing v on c.clientNo = v.clientNo 
    from Client join Viewing using clientNo
    from Client natural join Viewing
    -- 第一种方式，clientNo在结果种出现两次，并且可区分
    -- 剩下两种方式，clientNo都只出现一次
    

• sorting a join

  • example:

    • SELECT s.branchNo, s.staffNo, fName, lName, propertyNo
      FROM Staff s, PropertyForRent p
      WHERE s.staffNo = p.staffNo
      ORDER BY s.branchNo, s.staffNo,propertyNo;
      

  • 对多个属性进行排序时，先排序前面的属性；在前面属性排序的基础上，再排序后面的属性

• 三表连接

  • 方法一：Simple Join的方式进行，在where中指定表连接的规则

  • 方法二：

    FROM (Branch b JOIN Staff s USING branchNo) AS bs JOIN PropertyForRent p USING staffNo
    

• Cartesian product： – CROSS JOIN

  • SELECT [DISTINCT | ALL] {* | columnList} FROM Table1 CROSS JOIN Table2
    

• Outer Join

  • left outer join

    • SELECT b.*, p.* FROM Branch1 b LEFT JOIN PropertyForRent1 p ON b.bCity = p.pCity;
      

  • right outer join

    • SELECT b.*, p.* FROM Branch1 b RIGHT JOIN PropertyForRent1 p ON b.bCity = p.pCity;
      

  • full outer join

    • SELECT b.*, p.* FROM Branch1 b FULL JOIN PropertyForRent1 p ON b.bCity = p.pCity;
      

5.3.8 EXISTS and NOT EXISTS

• True if and only if there exists at least one row in result table returned by subquery
• False if subquery returns an empty result table.
• (not) exists只能判断有没有，不能判断有多少

5.3.9 Combining Results (UNION, INTERSECT, EXCEPT)

• Can use normal set operations of Union, Intersection, and Difference to combine results of two or more queries into a single result table.

• 基本语法：

  • relationA op [ALL] [CORRESPONDING [BY {column1 [, ...]}]] relationB
    -- except：做差
    

  • 如果指定了 CORRESPONDING BY，则集合操作就在给定的列上执行

  • 如果指定了 CORRESPONDING 但是没有BY子句，则集合操作就在两表共同的列上执行

  • 如果指定ALL，则查询包括一切重复的行

5.3.10 Database Updates

• Insert – Adding data to database

  • insert into TableName [(columnList)] values (dataValueList)
    

  • columnList可选，如果省略，SQL将严格按照它们呢在CREATE TABLE命令中的序

  • 如果给出columnList，则在columnList中未出现的列在建表时不能声明为NOT NULL，除非建该列时使用default选项

  • dataValueList和columnList必须要如下匹配

    • 列表columnList与dataValueList中项的数目必须是相同的。
    • 两个列表中项的位置必须是直接对应的，dataValueList中的第 i 项对应于columnList中的第 i 项
    • dataValueList列表中每一项的数据类型必须和对应列数据类型兼容

• Update – Modifying data in the database

  • update tableName set columnName1 = dataValue1[, columnName2 = dataValue2...] [where searchCondition]
    

  • TableName can be name of a base table or an updatable view

  • SET clause specifies names of one or more columns that are to be updated

  • WHERE clause is optional

    • if omitted, named columns are updated for all rows in table;
    • if specified, only those rows that satisfy searchCondition are updated

  • New dataValue(s) must be compatible with data type for corresponding column

• Delete – Delete data from the database

  • DELETE FROM TableName [WHERE searchCondition]
    

  • TableName can be name of a base table or an updatable view.

  • searchCondition is optional; if omitted, all rows are deleted from table. This does not delete table. If search_condition is specified, only those rows that satisfy condition are deleted.

第六章 SQL : Data Definition

6.1 the ISO SQL Data Types

• 

6.2 Integrity Enhancement Feature

6.2.1 Required Data

• Some columns must contain a valid value, they are not allowed to contain nulls.
• The ISO standard provides the NOT NULL column specifier in the CREATE and ALTER TABLE statements to provide this type of
  constraint.

6.2.2 Domain Constrains

• Every column has a domain, the ISO standard provides two mechanisms for specifying domains in the CREAT and ALTER TABLE statements.

• The first is the CHECK clause, which allows a constraint to be defined on a column or the entire table. The format of the CHECK clause is:

  CHECK (searchCondition)
   -- example：
   sex char not null check(set in ('M','F'))
  

• Secondly, the ISO standard allows domains to be defined more explicitly using the CREAT DOMAIN statement:

  CREATE DOMAIN DomainName [AS] dataType
  [DEFAULT defaultOption]
  [CHECK (searchCondition)]
  
  -- example
  CREATE DOMAIN SexType AS CHAR
  DEFAULT ‘M’
  CHECK (VALUE IN (‘M’, ‘F’));
  

• The searchCondition can involve a table lookup

  CREATE DOMAIN BranchNo AS CHAR(4) CHECK (VALUE IN (SELECT branchNo FROM Branch));
  

• Domains can be removed using DROP DOMAIN

  DROP DOMAIN DomainName [RESTRICT | CASCADE]
  -- RESTRICT
  -- 如果该域正在被用于某个现存的表、视图或者断言的定义，那么，撤销失败
  
  -- CASCADE
  -- 则任一表中基于该域的列都会自动地变为用该域的基类型定义，同时在合适的情况下，该域的任何约束或者default子句也都会被列约束和列default子句代替
  

6.2.3 Entity Integrity

• Primary key of a table must contain a unique, non-null value for each row.
• ISO standard supports entity integrity with the PRIMARY KEY clause in CREATE and ALTER TABLE statements.
• Can only have one PRIMARY KEY clause per table. Can still ensure uniqueness for alternate keys using UNIQUE.
  • UNIQUE关键字保证约束列的所有值都是唯一的

6.2.4 Referential Integrity

• 外键要么能参照到父表的候选键，要么为空

• ISO standard supports definition of FKs with FOREIGN KEY clause in CREATE and ALTER TABLE:

  FOREIGN KEY (branchNo) REFERENCES Branch
  FOREIGN KEY (staffNo) REFERENCES Staff ON DELETE SET NULL
  

• 父表中若试图用update和delete操作更新或者删除与子表有匹配行的候选关键字，SQL将根据foreign key子句中的on update或者on delete子句来决定如果执行该操作

  • CASCADE：
    • Delete row from parent and automatically delete matching rows in child, and so on in cascading manner.
  • SET NULL：
    • Delete row from parent and set FK column(s) in child to NULL. Only valid if FK columns are NOT NULL
  • SET DEFAULT：
    • Delete row from parent and set each component of FK in child to specified default. Only valid if DEFAULT specified for FK columns.
  • NO ACTION ：
    • Reject delete from parent. This is the default setting if ON DELETE rule is omitted.

6.2.5 Enterprise Constrains

• The ISO allows enterprise constraints to be specified using CHECK/UNIQUE in CREATE and ALTER TABLE and the CREATE ASSERTION.

• The CREATE ASSERTION statement is an integrity constraint that not directly linked with a table definition. The format of the statement is

  CREATE ASSERTION AssertionName CHECK (searchCondition)
  

6.3 Data Definition

• 

6.3.1 Creating a Database

• 创建 / 删除 schema

  CREATE SCHEMA [Name | AUTHORIZATION CreatorId ]
  DROP SCHEMA Name [RESTRICT | CASCADE ]
  

6.3.2 Creating a Table

• CREATE TABLE TableName
  ({colName dataType [NOT NULL] [UNIQUE]
  [DEFAULT defaultOption]
  [CHECK searchCondition] [,...]}
  [PRIMARY KEY (listOfColumns),]
  {[UNIQUE (listOfColumns),] […,]}
  {[FOREIGN KEY (listOfFKColumns)
  REFERENCES ParentTableName
  [(listOfCKColumns)], -- 如果 listOfColumns省略，则默认匹配到父表的主键
  [ON UPDATE referentialAction]
  [ON DELETE referentialAction ]] [,…]}
  {[CHECK (searchCondition)] [,…] })
  

6.3.3 Changing a Table Definition

• ALTER TABLE TabName
  -- 添加属性
  [ADD [COLUMN] columnName dataType
  [NOT NULL][UNIQUE][DEFAULT defaultOption]
  [CHECK (searchCondition)]]
  -- 删除属性
  [DROP [COLUMN] columnName [RESTRICT | CASCADE ]]
  -- 添加限制
  [ADD [CONSTRAINT [constraintName]]
  tableConstraintDefinition]
  -- 删除限制
  [DROP CONSTRAINT constraintName [RESTRICT | CASCADE ]]
  -- 更改属性的默认值
  [ALTER [COLUMN] SET DEFAULT defaultOption]
  [ALTER [COLUMN] DROP DEFAULT]
  

6.3.4 Removing a Table

• We can remove a table from the database using the DROP TABLE statement, which has the following format:

  DROP TABLE TableName [RESTRICT | CASCADE]
  

• RESTRIC：

  • With RESTRICT, if any other objects depend for their existence on continued existence of this table, SQL does not allow request

• CASCADE：

  • With CASCADE, SQL drops all dependent objects (and objects dependent on these objects).

6.3.5 Creating an Index

• The presence of an index can significantly improve the performance of a query.

• Indexes are usually created to satisfy particular search criteria after the table has been in use for some time and has grown in use

• CREATE [UNIQUE] INDEX IndexName ON TableName (columnName [ASC |DESC][,…])
  -- 在有大量数据之后建索引更号
  

6.3.6 Removing an Index

• DROP INDEX IndexName ;
  

6.4 Views

Dynamic result of one or more relational operations operating on base relations to produce another relation.

6.4.1 Creating a view

• CREATE VIEW ViewName [(newColumnName [,...])] 
  AS subselect [WITH[CASCADED | LOCAL] CHECK OPTION]
  -- example
  CREATE VIEW Manager3Staff
  AS SELECT *
  FROM Staff
  WHERE branchNo = ‘B003’;
  

• newColumnName的数量必须和subselect产生的列数量相同，如果省略则取子查询中的列名

• with check option确保那些不满足定义查询中where子句的行不会被添加到视图的基表中

• 如果想要成功创建视图，subselect中引用的表必须具有select权限，而对任何被引用列对应的域应该具有USAGE权限

6.4.2 Removing a View

• DROP VIEW ViewName [RESTRICT| CASCADE]
  -- CASCADE: all related dependent objects are deleted
  -- RESTRICT(DEFAULT): if any other objects depend for their existence on continued existence of view being dropped, command is rejected.
  
  

6.4.3 View Resolution – 视图分解

• 视图分解是将在视图上的查询和视图的定义查询合并，让对视图的查询直接转换到对基表的查询，步骤如下：
  1. 将select列表中给出的视图列名转换为定义查询中相应的列名
  2. from子句中的视图名可用定义查询中响应的from列表代替
  3. 用逻辑运算符AND将来自用户查询的where子句和定义查询的where子句合并
  4. 从定义查询赋值group by 和 having子句
  5. 最后，从用户查询复制order by子句，同时将视图的列名转换为定义查询的列名

6.4.4 Restrictions on views – 视图的局限性

• 如果视图中某个列是基于聚集函数的，那么在访问该视图的查询语句中，该列只能出现在select和order by子句里
  • 理解，在视图分解的时候，聚集函数会出现在where子句中
• 分组视图从来就不能与基表或视图连接

6.4.5 View Updatablity – 视图的可更新性

• 对一个基表的所有更新都会立即反映到包含这个基表的所有视图中；视图的更新也会反映到基表中
• 视图可更新的条件
  • DISTINCT is not specified.
  • Every element in SELECT list of defining query is a column name and no column appears more than once.
  • FROM clause specifies only one table, excluding any views based on a join, union, intersection or difference.
  • No nested SELECT referencing outer table.
  • No GROUP BY or HAVING clause.
• 能否更新的根本条件：
  • For a view to be updatable, the DBMS must be able to trace any row or column back to its row or column in the source table.

6.4.6 WITH CHECK OPTION

• Rows exist in a view because they satisfy WHERE condition of defining query.
• Rows that enter or leave a view are called migrating rows.（行迁移）
• WITH CHECK OPTION prohibits a row migrating out of the view.
  • with check option 防止行迁出
  • with local check option
    • 该视图或者由该视图直接或间接导出的视图上进行插入或者更新操作时，不允许行迁出，除非该行也迁移出底层视图或者表。
  • with cascade check option
    • 该视图或由该视图直接或者间接导出的视图上进行插入或者更新操作时，都不允许迁移处该视图

6.4.7 Advantages and Disadvantages of views

• Advantages
  • Data independence
  • Currency: Changes to any of the base tables are reflected in the view.
  • Improved security: Restricting and controlling each user’s access to the database.
  • Reduced complexity: Transforming multi-table queries into single-table queries.
  • Convenience: Providing greater convenience to users
  • Customization: Same underlying base tables can be seen by different users in different way.
  • Data integrity: Using WITH CHECK OPTION clause, ensuring the integrity of the view.
• Disadvantages
  • Update restriction: In some case, a view cannot be updated.
  • Structure restriction: If columns are subsequently added to the base table, then these columns will not appear in the view, unless the view is dropped and recreated.
  • Performance: There is a performance penalty to be paid when using a view.

6.4.8 View Materialization – 视图物化

• As what we discussed, the query based on view is modified into a query on the underlying base tables. This resolution mechanism may be slow, particularly if view is accessed frequently.
• View materialization：
  • store view as a temporary table in the database when view is first queried. Thereafter, queries based on materialized view can be faster than recomputing view each time.
  • 难度在于，更改基表时，保证基表和临时表的一致性
• 更新基表的同时引起物化视图的更新的过程叫做视图维护

6.5 Transaction

• SQL defines transaction model based on COMMIT and ROLLBACK

• Transaction is logical unit of work with one or more SQL statements guaranteed to be atomic with respect to recovery.

• An SQL transaction automatically begins with a transaction-initiating SQL statement (e.g., SELECT, INSERT).

• Changes made by transaction are not visible to other concurrently executing transactions until transaction completes.

• SQL transactions cannot be nested. SET TRANSACTION statement allows the user to configures transaction:

  SET TRANSACTION [READ ONLY | READ WRITE] |  [ISOLATION LEVEL READ UNCOMMITTED |READ COMMITTED|REPEATABLE READ |SERIALIZABLE ]
  
  -- READ ONLY 和 READ WRITE修饰符表明事务是只读的还是只写两种操作。
  -- 若什么修饰符也没有，默认设置为READ WRITE（除非隔离级为READ UNCOMMITIED）。
  -- READ ONLY允许事务对临时表进行增删改（仅限于临时表）
  
  -- 隔离级表明事务执行过程中允许和其他事务交互的程度。详见中文版教材Page 163
  

6.6 Access Control

6.6.1 Granting Privilege to other users

GRANT {PrivilegeList | ALL PRIVILEGES}
ON ObjectName
TO {AuthorizationIdList | PUBLIC}
[WITH GRANT OPTION]
-- PrivilegeList consists of one or more of above privileges separated by commas.
-- ObjectName can be a base table, view, domain, character set, collation or translation.
-- PUBLIC allows access to be granted to all present and future authorized users.
-- WITH GRANT OPTION allows privileges to be passed on.

6.6.2 Revoking Privilege from users

REVOKE [GRANT OPTION FOR]
{PrivilegeList | ALL PRIVILEGES}
ON ObjectName
FROM {AuthorizationIdList | PUBLIC}
[RESTRICT | CASCADE]

-- grant option for：允许被授权人传递的权限被独立地撤销
-- restrict:仅撤销权限
-- cascade:通过合适的drop语句，删除被抛弃的对象
	-- 被抛弃：由于创建对象需要相应的权限，那么撤销权限也就撤销了创建该对象的权力