数据库系统概论读书随笔(85-124)

内连接

缺省的连接类型是内连接，当join子句中没有outer前缀时，使用的是内连接。

笛卡尔积，from table1, table2，中间省略了关键字cross join。

自然连接，natural join，只考虑在两个关系中都出现的那些属性上取值相同的元组对。

条件自然连接，为了避免不正确的相等属性所带来的危险（当自然连接的关系存在多个相等属性时，自然连接会变得混乱），使用r1 join r2 using ([属性]...)

select name, course_id from student natural join takes;# 等价于下面
select name, course_id from student, takes where student.ID = takes.ID;
# 理论上后者的效率高于前者，SQL优化后实际上效率基本等效
select name, title from (student natural takes) join course using (course_id);
select name, title from (student natural takes), course where takes.course_id = course.course_id

连接条件，on关键字，on条件可以表达任何SQL谓词，使用on条件的表达式可以表示比自然连接更为丰富的条件。其与where极其相似，但使用on进行表连接通常会使SQL条理清晰，更容易读懂。

 

外连接

上述连接，本质上都是使用r1.xxx = r2.xxx进行连接，这种方式会使参与连接的任何一个关系中的某些元组可能会因为其中一个关系缺少某些元组，造成连接后的部分元组丢失。

外连接运算通过创建包含空值的元组保留在连接中丢失的元组。

左外连接：left outer join

右外连接：right outer join

全外连接：full outer join

select * from student natural left outer join takes;
select * from student left join takes on student.ID = takes.ID;
# 两者等价
select * from student left join takes on true where student.ID = takes.ID;
# 与上面不等价

外连接执行顺序，先执行外连接生成笛卡尔积，再判断on关键字中的条件，on默认为true，当on为false时，为元组补上null值；而where则会出现因为左外连接而takes的ID为空的情况，又因为谓词判断，则会将student有而takes没有的ID过滤掉，最后导致两者结果不一致。因此where和on并不相等。总而言之，涉及到外连接一定要使用on，而非where。

 

视图

为什么存在视图？出于安全性的考虑，不能将表的查询权限给用户，但是又希望用户能得到表的实时部分信息。视图就是这样的一种关系，它并不预先计算和存储，而是在使用虚拟关系的时候才通过执行查询计算出来。

视图与with语句的不同之处：视图一旦创建就一直存在，而with只在当前查询语句可用。

create view view1([可填属性名，也可省略]) as ([查询语句])

不建议对视图进行除了查询外的其他操作。

 

物化视图保证如果定义视图的实际关系发生改变，则视图也跟着修改以保持最新。

视图维护分为两种，一种是当构成视图定义的任何关系被更新时，立刻执行视图维护；另一种是周期性的对物化视图进行维护，其中的内容可能是陈旧、过时的。

显然在大型数据库中，加入每次调用视图都进行查询，开销是很大的。而物化视图采用空间换时间的方式提高了数据库的运行效率。至于视图维护的方式，则需要看当前业务决定。

 

事务

SQL标准规定当一条SQL语句被执行时，就隐式的开始了一个事务，这样可以确保SQL语句的原子性，而不会出现部分数据更新的情况。

MySQL等SQL实现，都采用了单条SQL执行完毕后事务自动提交的实现方式，因此在需要进行多条语句事务时，需要手动输入关闭自动提交的命令。

 

完整性约束

非空约束：not null

唯一性约束：unique，在mysql中被称为唯一性索引，被声明为唯一性索引的属性会加入超码集。

alter table <关系名> add/drop constraint <约束名> unique key 'name' ('name');
create assertion <断言名> check <P>

检查完整性：check(<谓词P>)：需要注意的是，如果check子句不为假，则它是满足的，因此计算结果如果为未知也是满足的，所以当使用check时，需要谨慎的看待空值的存在。同时，SQL标准允许谓词包含子查询，但在当前的数据库产品中，都不允许谓词包含子查询，因为带子查询的检测开销可能是很大的。

引用完整性：foreign key(<属性>) references <属性>，有时，关系的修改会不得不违反引用完整性，为了维护引用完整性，可以采用级联(on delete cascade on update cascade)，设空(on delete set null)，设默认值三种方式。

 

三种复杂检查性约束的实现方式，检查完整性的复杂子查询，断言，触发器。其中前两种大部分SQL实现都不支持，后一种大多都支持，如MySQL。

 

数据类型与模式

日期（date）：年月日，'2018-04-25'

时间（time）：时分秒，'09:30:00'

时间戳（timestamp）：日期+时间，'2018-04-25 09:30:00'

同格式的日期和时间及时间戳可以直接用运算符加减。日期运算在SQL中会很便捷，因此建议在SQL中，将时间存储在上述格式中，而非字符串。

 

cast方法可以实现显式类型转换，方便排序、输出等一系列操作。

format方法在MySQL中可以实现日期到字符串的格式转换；to方法可以实现字符串到日期的格式转换。

decode方法可以实现将null值替换为'N/A'...

 

<属性声明> default <默认值>：为属性设置缺省值

 

大对象类型有：

字符数据（clob）：<字段名> clob(<占用内存最大大小>)

二进制数据（blob）：<字段名> blob(<占用内存最大大小>)

然而，对于包含大对象的结果元组而言，把大对象存入数据库读取时读入内存是非常低效或不现实的，因此建议存储文件的路径或者其他定位大对象的方式，而非大对象本身。

 

当主码设置为自动递增时（auto_increment = <初始值>），请使用指定了属性顺序的insert语法。

 

关于schema（模式）和database（数据库），在MySQL中这两者是等价的。MySQL的文档中指出，在物理上，模式与数据库是同义的，所以，模式和数据库是一回事；但是，Oracle的文档却指出，某些对象可以存储在数据库中，但不能存储在schema中。在MySQL中模糊了schema这个概念，更多的是使用database。三层体系结构：目录（catalog）-模式（schema）-关系（table）。

 

索引

create index <索引名> on <关系名>(<属性名>...)

索引本质上就是通过名字使用散列函数直接映射到内存中，这样通过名字就可以直接找到对应数据。索引对于正确性来说不是必须的，因为它们是冗余的数据结构，但用这点空间去换性能是值得的。

所以一定要为频繁查找的属性加上索引。