SQL和关系代数语法总结

1. 关系代数

• 选择

  

• 投影

  

• 并：R ∪ S

• 差：R - S

• 交：R ∩ S = R - (R - S)

• 重命名：将E重命名为S

  

• 连接操作：

  • Theta join – θ 连接

    

    

  • 等值连接：Theta连接中，谓词使用等号时，就是等值连接

  • 自然连接： – 公共属性只保留一次

    

  • 外连接：

    • 左外连接

      

    • 右外连接

      

    • 全外连接

      

    • 半连接

      

• 除法：

  • R ÷ S
  • 除法运算（R ÷ S）定义了属性集合C上的一个关系，该关系的元组与 S 中的每个元组的组合都能在R中找到匹配元组

2. SQL语句

• select语法

  select [distinct | all] { * |[columnExpression[as newName]]}
  from TableName[alias][,……]
  [where condition]
  [group by columnList][having condition]
  [order by columnList] ;
  

  • (not) between (a,b) 是否 (不) 在 a - b 的闭区间内
  • in / not in { }：是否在in后面打括号指定的集合中
  • like / not like ……：判断某一字符串是否符合固定格式
    • %：表示一个或者多个字符
    • _：表示任何单个的字符
  • null search : is null / is not null，判断某一元素是否为空

• 聚集函数

  • count( * | attribute)
  • sum(attribute)
  • avg(attribute)
  • min(attribute)
  • max(attribute)

• any(some) | all

  -- example:
  select satffNo, fName,lName,position,salary
  from Staff 
  where salary > some(select salary from Staff where branchNo = 'B003');
  
  -- salary只要不是子查询生成结果中的最小值就可以
  

• 多表查询

  -- 简单连接
  select c.clientNo, fName, lName, propertyNo
  FROM Client c, Viewing v
  WHERE c.clientNo = v.clientNo;
  
  -- 连接
  from Client c join viewing v on c.clientNo = v.clientNo 
  from Client join Viewing using clientNo
  from Client natural join Viewing
  -- 第一种方式，clientNo在结果种出现两次，并且可区分
  -- 剩下两种方式，clientNo都只出现一次
  
  -- 笛卡尔积
  SELECT [DISTINCT | ALL] {* | columnList} FROM Table1 CROSS JOIN Table2
  
  -- 左外连接
  SELECT b.*, p.* FROM Branch1 b LEFT JOIN PropertyForRent1 p ON b.bCity = p.pCity;
  -- 右外连接
  SELECT b.*, p.* FROM Branch1 b RIGHT JOIN PropertyForRent1 p ON b.bCity = p.pCity;
  -- 全外连接
  SELECT b.*, p.* FROM Branch1 b FULL JOIN PropertyForRent1 p ON b.bCity = p.pCity;
  

• exists / not exists

  • True if and only if there exists at least one row in result table returned by subquery
  • True if and only if there exists at least one row in result table returned by subquery

• Combining Results (UNION, INTERSECT, EXCEPT)

  • relationA op [ALL] [CORRESPONDING [BY {column1 [, ...]}]] relationB
    -- op = UNION | INTERSECT | EXCEPT
    -- except：做差
    

• Database Updates

  • Insert

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

  • Update

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

  • Delete

    • DELETE FROM TableName [WHERE searchCondition]
      

3. Data Definition

• Domain Constrains

  • -- check语法
    check (searchCondition)
    -- example: 
    sex char not null check(set in('M','F'));
    
    -- 创建自定义域
    CREATE DOMAIN DomainName [AS] dataType
    [DEFAULT defaultOption]
    [CHECK (searchCondition)]
    -- example
    CREATE DOMAIN SexType AS CHAR DEFAULT ‘M’ CHECK (VALUE IN (‘M’, ‘F’));
    CREATE DOMAIN BranchNo AS CHAR(4) CHECK (VALUE IN (SELECT branchNo FROM Branch));
    -- 删除自定义域
    DROP DOMAIN DomainName [RESTRICT | CASCADE]
    -- RESTRICT：如果该域正在被用于某个现存的表、视图或者断言的定义，那么，撤销失败
    -- CASCADE：则任一表中基于该域的列都会自动地变为用该域的基类型定义，同时在合适的情况下，该域的任何约束或者default子句也都会被列约束和列default子句代替
    

• Referential Integrity

  • FOREIGN KEY (columnList) REFERENCES TableName
    FOREIGN KEY (staffNo) REFERENCES Staff ON DELETE SET NULL
    -- on update / on delete，如果删除父表中与子表有匹配行的候选关键字
    	-- CASCADE:将子表中对应行也删除
    	-- SET NULL:将子表中的外键设置为空
    	-- SET DEFAULT:将子表中的外键设置成默认值，前提是创建属性时指定了默认值
    	-- NoAction:如果子表中有对应匹配行，则delet操作不被允许
    
    

• Enterprise Constrains

  • CREATE ASSERTION AssertionName CHECK (searchCondition)
    

• Creating a Database

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

• 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)] [,…] })
    
    -- example
    CREATE TABLE PropertyForRent (
    propertyNo char NOT NULL,
    rooms char NOT NULL DEFAULT 4,
    rent char NOT NULL DEFAULT 600,
    ownerNo char NOT NULL,
    staffNo char,
    CONSTRAINT StaffNotHandlingTooMuch
    CHECK ( NOT EXISTS ( SELECT staffNo
    FROM propertyForRent
    GROUP BY staffNo
    HAVING COUNT(*)>100)),
    branchNo char NOT NULL,
    PRIMARY KEY (propertyNo),
    FOREIGN KEY (staffNo) REFERENCES Staff
    ON DELETE SET NULL ON UPDATE CASCADE);
    
    -- 更改表
    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]
    
    -- 删除表
    DROP TABLE TableName [RESTRICT | CASCADE]
    

• index

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

• view

  • -- 创建视图
    CREATE VIEW ViewName [(newColumnName [,...])] 
    AS subselect [WITH[CASCADED | LOCAL] CHECK OPTION]
    -- example
    CREATE VIEW Manager3Staff
    AS SELECT *
    FROM Staff
    WHERE branchNo = ‘B003’;
    
    -- 删除视图
    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.
    

• 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
    

• access control

  • -- 授予权限
    GRANT {PrivilegeList | ALL PRIVILEGES}
    ON ObjectName
    TO {AuthorizationIdList | PUBLIC}
    [WITH GRANT OPTION]
    -- ObjectName 可以是基表，域，交易等
    -- WITH GRANT OPTION 允许被授权用户将权限授予其他人
    
    -- 撤销权限
    REVOKE [GRANT OPTION FOR]
    {PrivilegeList | ALL PRIVILEGES}
    ON ObjectName
    FROM {AuthorizationIdList | PUBLIC}
    [RESTRICT | CASCADE]
    -- grant option for：允许被授权人传递的权限被独立地撤销
    -- restrict:仅撤销权限
    -- cascade:通过合适的drop语句，删除被抛弃的对象
    	-- 被抛弃：由于创建对象需要相应的权限，那么撤销权限也就撤销了创建该对象的权力
    

• 这篇博客只是总结了有哪些语法，具体语法细节并未列出，如需了解可以阅读下面这篇笔记

• 数据库1-6章完整笔记跳转：知识传送门，点击传送

• 所用教材：《数据库系统设计、实现与管理》