数据库 (四) -- 数据定义

• 约束
  • 静态约束
  • 动态约束
    • ** 触发器 Trigger
• 视图 View
• 存储过程 (函数)
• WITH
• 连接
  • 内连接
  • 外连接
• 组合查询 UNION

约束

防止合法用户向数据库加入不符合语义的数据，避免非法更新

1. 主键约束 Primary Key

增加一个主键将自动在主键中列出的列或列组上创建一个唯一 B-tree 索引

  CREATE TABLE products {
    product_no integer Primary Key

    -- 或者

    product_no integer,
    product_secno integer,
    Primary Key (product_no, product_secno)
  }

2. 外键约束 Foreign Key

   CREATE TABLE orders (
       order_id integer PRIMARY KEY,
       product_no integer REFERENCES products (product_no),
       quantity integer
   );

3. 属性约束 全局约束

• not null
• unique
• check 可以是任何 WHERE 子句

4. GRANT REVOKE

授权 和 收回权限

静态约束

Integrity Constraint ::= (O, P, A, R)

CREATE TABLE Course (
    C char(3),
    Credit float(1) constraint ctcredit check (Credit >= 0.0 AND Credit <= 5.0)
    -- 指定约束名为 ctcredit
)

动态约束

** 触发器 Trigger

实现动态完整性，在特定时刻自动触发

  CREATE TRIGGER updS# after undate of S#
  ON student
  referencing old oldi, new newi

  for each row
    begin
      update sc set S# = newi.S# where S# = :oldi.S#；
    end;

视图 View

• 基本表存储到文件中
• 虚拟表，对真实表的引用
• 使用表的一部分而不是整个表，也有利于保护数据
• 隐藏复杂 SQL

  CREATE VIEW vend_usa
  AS SELECT vend_id, vend_address, vend_country
  FROM vendors
  WHERE vend_country = 'USA'
  WITH CHECK OPTION ;

  -- 这里添加载 WITH CHECK OPTION，之后对视图的增删改自动添加当前的 WHERE 条件

视图更新: 视图由一个单一表子集构成，并且更新操作包含基本表的主键，可以更新

存储过程 (函数)

WITH

WITH 提供了一种方式来书写在一个大型查询中使用的辅助语句，可以看作查询时生成的临时表

  with mvtgeom AS ()

连接

应该总是提供连接条件

• 连接选项

  • inner join
  • left | right | full outer join

• 连接条件

  • natural
  • on <连接条件>
  • using (Col1, Col2 ...)

内连接

根据主键和外键自然连接

外连接

包含没有关联到的行

组合查询 UNION

同一个表的多个查询，也可以不同表的查询 (只要列数据格式相同，列名可以不同)

  SELECT cust_name, cust_contact, cust_email
  FROM Customers
  WHERE cust_state IN ('IL','IN','MI')
  UNION
  SELECT cust_name, cust_contact, cust_email
  FROM Customers
  WHERE cust_name = 'Fun4All'
  ORDER BY cust_name, cust_contact;