数月不见，甚是想念。

因最近参与了两个新项目以及分专业带来的亿点点作业(苦逼计科人)，所以一直没更新(还是懒)。

但接下来会努力周更！！！

---

本篇文章的所有例子均来自 我的github仓库。你可以在这里找到数据和代码来运行。

Simple Query

本部分为最基础的 query语法内容。

PostgreSQL使用如下的结构来进行简单查询

SELECT * -- *代表query所有的列
FROM employees -- 查找的关系
WHERE first_name='Peternela' AND last_name='Anick'; --附加条件

作用是寻找出employees表中 姓名为 Peternela Anick 的一行的所有数据

Simple SELECT

可以使用 AS关键字来为关系中的列取 Alias

SELECT emp_no AS emp_number --取别名
FROM employees -- 查找的关系
WHERE first_name='Peternela' AND last_name='Anick'; --附加条件

可将employees 中名为 P~ A~ 的emp_no元素输出 并将列名改为emp_number

 

可以在SELECT 中使用 算数表达式 以及 常量 来query

SELECT emp_no*10 --使用表达式取代一个列
FROM employees -- 查找的关系
WHERE first_name='Peternela' AND last_name='Anick'; --附加条件

可以看到最后输出值为 emp_no的10倍

SELECT emp_no*10 , 'temp' AS first_name--使用表达式取代一个列,并加入一个常量列
FROM employees -- 查找的关系
WHERE first_name='Peternela' AND last_name='Anick'; --附加条件

可以看到最后多了一个名叫 first_name的列，且列中值为 ‘temp’

Simple WHERE

可以使用 =, <>, <, >, <=, >=等比较操作符限制条件

可以使用数学运算符/表达式，例如emp_no*emp_no<10002*10002作为WHERE的条件

同样可以对多个Boolean表达式间使用逻辑运算符：AND OR NOT.

SELECT *
FROM salaries
WHERE emp_no+1024 > 10001+1024 AND (salary >= 10000 OR salary <=50000);
--可能没有特殊的实际意义，但只是一个示例。

SQL的模式匹配

使用keyword LIKE ESCAPE以及通配符% _进行模式匹配

通配符%作用为代表任意串

通配符_作用为代表任意 单个字符

关键字ESCAPE说明在其后面的字符 不是通配符，只是普通字符。

格式如下：

s LIKE p-- p可以是含有通配符的字符串面板值

例子：

SELECT *
FROM employees
WHERE first_name LIKE'Peter%' OR last_name LIKE 'Peter%';

作用为查找名/姓中含有peter前缀的employee

NULL和三值逻辑

3种NULL

其实在SQL中我们只有一个NULL，但我们在讨论NULL的时候会将其划分为三种类型：

• Value unknown(未知值)
• Value inapplicable(不适用的值)
• Value withheld(隐藏值，隐私保护等)

既然NULL是表中的数据，那么他势必会参与计算。但对于这个NULL值，使用数学计算会得到什么？使用逻辑(Boolean)运算又会得到什么？

下面给出NULL的两条规则：

• NULL参与数学运算，结果为NULL
• 把NULL和任意值比较，使用比较符，结果为UNKNOWN

NULL不是一个常量，所以不能使用NULL作为操作数

sql中针对NULL的语句有 IS NULL 和 IS NOT NULL都返回一个Boolean。

若使用= 或 !=就与上面规则矛盾了，所以才使用关键字IS 和 IS NOT

UNKONOWN

UNKNOWN除了对真值表带来了一些(很多)变化，他还会带来出乎意料的结果

例如在使用NOT语句时，假设以下情景：

在对网络中在线流量的统计中，普通的二值逻辑只有：多和少两个概念。

所以如果你想要筛选出流量少的时段，只需要找满足 NOT ‘流量多’的行即可。

但在三值逻辑中却有三个标准：多，适量，少

当你要筛选流量少的时段，使用二值逻辑相同的方法，会发现得不到想要的结果。

上述例子在使用多次NOT后更为明显！

假如使用两次 NOT，

在二值逻辑中，还会是他本身。

但在三值逻辑中，值是什么又会有谁知道呢？

ORDER BY 子句

使用ORDER BY子句可以对输出进行排序

常用的形式为：

ORDER BY <SOME ATTRIBUTES>
-- 你同样可以使用算术表达式
ORDER BY A+B

ORDER BY语句默认为 升序排列

你可以使用关键字 DESC来更改为降序排列；使用ASC来设置为升序排列(Useless)

ORDER BY A+B DESC;

子句ORDER BY 常用在 FROM WHERE中，即在SELECT子句之前使用即可。

example:

--ORDER BY IN FROM
SELECT *
FROM TABLE_A ORDER BY ATTRIBUTES_A;

--ORDER BY IN WHERE
SELECT *
FROM TABLE_B
WHERE ATTRIBUTES_B== CONST_NUMBER
ORDER BY ATTRIBUTES_C;

SQL多表查询

积和连接

我们在做查询时往往会遇到这种状况：想要得到的数据在一个表中不能全得到，必须要两个/多个表共同给出数据才行。

例如：你在employees表中能找到员工号和姓名，但你现在想知道员工号、姓名和薪水，那你就必须也在salary表中寻找，并将这两个表中相同的行”∪”起来。

这是，前面的simple query语句就无法满足需求。我们只能通过将两个表建立联系的方式进行查询

example:

我们想知道Let the bullets fly的producer

--Movies(title,year,length,genre,studioName,producerC#)
--MovieExec(name,address,cert#,netWorth)

SELECT name
FROM Movies,MovieExec
WHERE title='Let the bullets fly' AND producerC#=cert#;

处理冲突属性名

如果我们想要得到的结果中有两个名字相同但来自不同表的属性怎么办？

如果我们想要在一个表中比较两个、多个元组怎么办？

如果我们想要在WHERE子句中对两个名字相同的属性进行限制怎么办？

这就引出了我们的话题，处理冲突属性名

---

对于两个表中相同名字的属性，我们可以使用如下方式：

R.A 代指R关系中的A属性，S.A代指S关系中的A属性

即使在没有冲突的情况下，这样的使用方法也是被允许的。

example：

SELECT MovieStar.name,MovieExec.name
FROM MovieStar,MovieExec
WHERE MovieStar.address=MovieExec.address

---

如果我们相对单个表中的多个元组进行查找

那么我们可以使用 元组变量的方法：

SQL允许对每次在FROM子句中出现的关系取一个别名，且默认缺省 AS 关键字

也就是说

FROM MovieStar Star1, MovieStar Star2	--缺省AS

就把一个关系重赋给了两个新关系。后续就可以使用 Star1和Star2来对MovieStar关系中的元组进行查询

对多表查询的解释

在前面我们只是简单地讨论了SQL中如何使用多表查询，但对它具体的实现过程并没有深究。

在本节，将给出几个等价的对多表查询的解释。

嵌套循环

//让在FROM子句中的元组变量包括：关系R1,R2,...,Rn
for each tuple t1 in R1 do
	for each tuple t2 in R2 do
			...
		for each tuple tn in Rn do
			if (当所有的t1,t2,...,tn赋值给元组变量后Satisfied WHERE Clause)
				开始计算SELECT语句，得到结果。

并行赋值

在 嵌套循环的解释中，我们将元组变量赋值的顺序做了一个硬性的规定。

但其实也可以将这个赋值的过程认为是 一种任意的，适当的顺序，将所有可能的赋值都给元组。

然后根据每一个赋值，判断WHERE子句是否为真。

对于产生真值的元组，再交给SELECT子句来构造结果

关系代数

FROM子句后面的元组变量开始，可以将其转化为 求笛卡尔积的过程。且如果两个元组变量表示一个关系，则该关系出现两次，且属性名都不同。

再把WHERE子句转换为一个选择条件来进行选择操作。

最后SELECT子句转化为关系代数中的投影操作

查询的 交 差 并

SQL中可以使用 INTERSECT,EXCEPT,UNION三个关键字来对两个查询结果进行 集合的交，差，并运算。

UNION- ∪

EXCEPT- -

INTERSECT- ∩

前提是这两个查询结果必须有相同的属性和相同的属性数据类型。

example: 查询名字为 Margo Anily 且出生日期在 1959-10-30~1989-09-12之间的员工号

(SELECT employees.emp_no
FROM employees
WHERE employees.first_name='Margo' AND employees.last_name='Anily')
UNION
(SELECT employees.emp_no
FROM employees
WHERE employees.birth_date='1959-10-30'AND employees.hire_date='1989-09-12');

约束&触发器

键和外键

外键的声明

CREATE TABLE Studio{
	name CHAR(30) PRIMARY KEY,
	address VARCHAR(255),
	presC# INT REFERENCES MovieExec(cert#)
	//或者单独声明外键
	FOREIGN KEY (presC#) REFERENCES MovieExec(cert#)
}

外键对于引用完整性的保护：

如上例子：若对关系 MovieExec（即被引用的关系）进行操作，外键有以下三种方案来确保引用完整性：

1. 缺省原则：即reject

   拒绝违法更新

2. 级联原则(Cascade)：

   被引用属性的改变被同步到外键上，即若删除MovieExec中的元组，Studio中的元组也要伸出

3. 置空值原则：Set-NULL

   即被引用关系上的改变影响到外键时，将外键值置为空值

通过如下的形式设置方案：

CREATE TABLE Studio{
	name CHAR(30) PRIMARY KEY,
	address VARCHAR(255),
	presC# INT REFERENCES MovieExec(cert#)
		ON DELETE SET NULL
		ON UPDATE CASCADE//对不同操作设置不同的方案
}

延迟约束检查：

​ 试想这样一种情况：你想对一个有外键的表中插入一个新元组，在此之前你需要在被引用表中插入对应的元组。但如果这两个相互形成约束，那么就变成了一个类似 死锁的情形。这时，约束阻止了我们的操作，所以我们需要延迟约束检查。

​ 在约束后可添加 DEFERABLE和NOT DEFERABLE选项来设置是推迟到事务提交前检查还是在可能违反外键约束的更新语句后立即检查。

在DEFERABLE后面有 INITIALLY DEFERRED或INITIALLY IMMEDIATE选项分别表示检查推迟到事务提交前；检查在每个语句后立刻执行。

CREATE TABLE Studio{
	name CHAR(30) PRIMARY KEY,
	address VARCHAR(255),
	presC# INT REFERENCES MovieExec(cert#)
		DEFERRABLE  INITIALLY DEFERRED//检查被推迟知道事务提交前
		DEFERRABLE  INITIALLY IMMEDIATE//每个语句后都立即检查
}

我们可以修改有名字的约束，可更改约束类型，Example:

SET CONSTRAINT MyConstraint DEFERRED;

属性和元组上的约束

非空值约束：NOT NULL

作用：不允许元组的该属性取空值* (也就是说这是一个属性上的约束)*

基于属性的CHECK约束

Format:

CHECK (Expression...)

其中Expression可以为任何在WHERE子句中允许的描述：可以为简单的数值约束、可以存在查询

但要注意，若要引用其他属性/关系，必须在WHERE关系中出现或者是约束属性所在关系的其他属性。

Example:

CREATE TABLE XXX{
/*简单的值约束*/
presC# INT REFERENCES MovieExec(cert#)
	CHECK(presC#>=100000)
/*含有子查询的约束*/
presC# INT CHECK
          (presC# IN (SELECT cert# FROM MovieExec))
}

注意：CHECK的触发条件

只有当元组为该属性获取新值时被检查，若违背约束，拒绝修改。

当数据库中修改没有改变与CHECK相关的属性时，CHECK不会被触发。

CHECK约束是单方面的

CHECK约束只会约束被设置的关系，但如果你的CHECK条件中有其他关系，当你修改这些关系时，CHECK不会被触发，即对条件中的关系而言该CHECK是透明的。很可能出现改动导致违背CHECK约束。

这也说明CHECK并不能起到Foreign Key的作用

 

基于元组的CHECK

Example:

CREATE TABLE XXX{
	...,
	CHECK (gender='F'OR name NOT LIKE 'Ms.%')
};

每次向关系中插入元组 或 R元组修改时，都会触发CHECK约束

一般来说，基于元组的约束会被更频繁的检查

修改约束

给约束命名

为了修改/删除一个已经存在的约束，我们必须给约束命名。

使用关键字 CONSTRAINT：

name CHAR(30) CONSTRAINT NameIsKey PRIMARY KEY,
gender CHAR(1) CONSTRAINT NoAndro CHECK (gender IN ('F','M')),
CONSTRAINT RightTitle 
			CHECK (gender='F' OR name NOT LIKE 'Ms.%');

 

修改表上的约束

可以使用 SET CONSTRAINT语句将约束检查设置进行更改。

通过使用 ALTER TABLE语句可以对约束做其他改变：

ALTER TABLE XXX DROP CONSTRAINT NameIsKey;//使用DROP CONSTRAINT 删除约束
ALTER TABLE XXX ADD CONSTRAINT NoAndro 
								CHECK(gender IN('F','M'));//使用ADD CONSTRAINT 添加约束

注意：添加的约束一定是与元组有关的：基于元组的、键、外键。

另外：除非要添加的约束在那个时刻持有表中的每个元组，否则不能对表添加约束。

断言

SQL中主动元素最强有力的形式：断言和触发器。他们是数据库的一部分，与表是一个级别。

创建断言

CREATE ASSERTION <name> CHECK(<conditions>)

断言建立时，断言的条件必须为真，并且要保持永真。即任何导致断言为假的操作都会被拒绝

断言中使用的任何属性都需要说明。且由于coditions必须是逻辑值，所以常常使用聚类函数来获得一个逻辑值。

Example:

CREATE ASSERTION RichPress CHECK
				(NOT EXISTS
                    (SELECT Studio.name
                     FROM Studio,MovieExec
                     WHERE pressC# = cert# AND netWorth<10000000
                    )
                );

删除断言

DROP ASSERTION <name>

触发器

1.当触发器声明的事情发生时，触发器被激活。(事件常常是对特定关系的插入、删除或修改)

2.当触发器被激活时，触发器测试触发的条件，若不成立，则不做任何事情

3.若条件成立，则与该触发器相连的动作由DBMS执行。(可以是任何操作)

创建触发器

触发器有很多选项：

• 触发器的条件检查和动作可以在触发事件执行之前的数据库状态上或触发动作执行后的状态上执行。
• 条件和动作可以引用元组的旧值和/或 触发事件中更新的元组的新值
• 更新事件可以被局限到某个特定的属性或一些属性
• 可以选择动作执行的方式：一次只对一个更新元组(行级触发器)/一次针对所有被改变元组(语句级触发器)

Example：

CREATE TRIGGER NetWorthTrigger
AFTER UPDATE OF netWorth ON MovieExec//指出在触发事件后执行
REFERENCING //引用旧元组和新元组
	OLD ROW AS OldTuple,
	NEW ROW AS NewTuple
FOR EACH ROW//告诉触发器一次只对一个更新元组执行
WHEN (OldTuple.netWorth>NewTuple.netWorth)//执行动作
	UPDATE MovieExec
	SET netWorth = OldTuple.netWorth
	WHERE cert#=NewTuple.cert#;

some notes:

• AFTER可以替换为BEFORE或者INSTEAD OF
• 除了UPDATE之外，可能的触发事件还有INSERT DELETE, OF是可选项，表示事件只是OF保留字后列出属性的修改。OF在INSERT DELETE中不可用，因为二者都是针对元组的。
• WHEN是可选项，若该短语缺省，则触发器一定会做出动作。
• 动作可以不是单个SQL语句，可在BEGIN...END括起来的区域中写出多个SQL语句
• 当触发事件为修改时，会有新旧元组之分，分别用：NEW ROW AS、OLD ROW AS命名；在INSERT和DELETE中，分别只有新和旧。
• FOR EACH ROW表示为行级触发器，FOR EACH STATEMENT表示语句级触发器。语句级触发器不能直接引用新旧元组，可以引用 新旧元组的关系：OLD TABLE AS OldStuff、NEW TABLE AS NewStuff(语句级也可以)

视图&索引