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章完整笔记跳转:知识传送门,点击传送
-
所用教材:《数据库系统设计、实现与管理》