关系代数与SQL

关系完整性

• 实体完整性规则: 主属性非空
• 参照完整性: 外码空值或为另一个表中的主码的值
• 用户自定义完整性:见下方

关系代数

选择

选择为从关系模式中选出一个元组，也就是选出一行作为结果，可以选择是否添加条件。(选择是选择所有的元组或者满足其中一个条件的元组)

$\sigma(R)$ 表示选择所有元组, $\sigma_F(R)$ 表示选择满足条件F的所有元组。等价的SQL语句为:

select * from R;
select * from R where F;

投影

投影为选择哪些列作为输出结果。也就是会选出所有的对应列。

$\Pi_{a,b,c}(R)$ 表示选择a,b,c列的所有元素。等价SQL语句为:

select a, b, c from R;

当选择和投影组合使用($\Pi_{a,b,c}(\sigma_F(R))$)时等价于:

select a, b, c from R where F;

并

tip

两个关系模式必须同构，跟传统集合运算相关的都要求操作数同构才能进行运算

将关系模式看成一个集合，集合里面的元素就是一个一个元组，也就是一行一行，两个关系模式取并集表示将两个关系模式所有的元素都放到同一个关系模式下。$\sigma_F(R_1)\cup \sigma_F(R_2)$

等价SQL语句:

select * from R1 where F;
intersect
select * from R2 where F;

差

将关系模式看成一个集合，集合里面的元素就是元组，跟传统集合相减相同。$\sigma_F(R_1) - \sigma_F(R_2)$

select * from R1 where F;
except
select * from R2 where F;

笛卡尔积

与集合笛卡尔积类似，把元组当作操作的元素即可。笛卡尔积的连接一般无物理意义，所以需要通过条件来约束。笛卡尔积就是运动员握手，每一个运动员都要跟所有人握手，而这里就是$R$表中一个元素都要和$S$表中所有元素进行连接。

select * from R, S where R.B=S.B;

连接

• 自然连接

自然连接是等值连接的一种，通常是将两个表中某一个属性相等的值所在的两个元组拼接成一个元组。有重复的列就合并成一个。$R\bowtie S$

• 左外连接

保证最后结果表的左边是非空，所有的空值都在右边。在自然连接的基础上补上左集合中没有的列。没有对应值的项补null

• 右外连接

保证最后结果表的右边是非空，所有的空值都在左边。在自然连接的基础上补上右集合中没有的列。没有对应值的项补null

交

将关系模式看成一个集合，集合里面的元素就是一个一个元组，也就是一行一行，两个关系模式取交集表示将两个关系模式共有的元素都放到同一个关系模式下。$\sigma_F(R_1)\cap \sigma_F(R_2)$

select * from R where F;
union
select * from S where F;

除

Y除掉相同的列，留下的X中包含Y的所有元组的元组，留下被除数。关系代数表示 $R\div S$

运算步骤:两个关系模式中相同的列删掉，只留下被除数$R$剩下的列A，然后在被删掉的列中找R与S值相同的元组，然后将对应列A的值加入到剩下的列A.

除法一般可以解决至少相关的问题，因为除法求解的就是包含关系

例子:

若有一个学生-课程数据库，查询至少选修1号课程和3号课程的学生的学号。则先建立一个临时的关系K，K的属性是Cno，有两个元组1和3，然后使用除法$\Pi_{Sno,Cno}(SC)\div K$

除法其实就是包含关系，除数表示的就是要包含的前提，比如说上面的例子，我要选出一个选修1号课程和3号课程的学生的学号，那么此时除数就是课程号为1和3的，只要一除，只要学号都是有1和3这个属性的都会被保留下来。除数就是条件，就是你要求包含哪几个的集合.

例子2:

要查询选修了所有课程的学生姓名，那么此时只需要把所有课程作为除数，找到选修了所有课程的学号，再跟学生表进行连接，就能得到所需要的结果。

去重

表示去掉元素值相同的元组，关系代数表示为$\delta(R)$

select distinct a from R where F;

重命名

表示将关系模式的属性重命名，关系代数表示为$\rho_{S(A,B,C...)}(R)$

重命名的目的是为了使得两个关系模式同构，能够进行关系代数运算，临时改变属性的名字，但是最终不会改变。

排序

表示关系模式按照某一个属性从小到大或者从大到小进行排序。关系代数表示为:$\tau_L(R)$表示含义是在关系模式$R$中以$L$进行排序.

select * from R order by asc L; --升序
select * from R order by desc L; --降序

分组

分组是使得具有相同值的分在同一组，比如说按照L属性分组，关系代数表示:$\gamma_{L,fun}(R)$，其中$fun$为聚类函数。(也就是按照什么属性分组，按照学号？姓名？电影名称？)

按照分组字段，将获取到的记录分为几块，保留每块的第一条记录。

group by 主要实现分组统计，分组统计主要要用到以下的聚类函数。

select L, fun, from R group by L;

常用的聚类函数有:

• count(*/字段名)： 统计分组字段对应的记录数量，元组数量/一列中值的个数
• max(*/字段名)：统计分组后某个字段的最大值，一列值的最大值
• min(*/字段名)：统计分组后某个字段的最小值，一列值的最小值
• avg(*/字段名)：统计分组后某个字段的平均值，一列值的平均值
• sum(*/字段名)：统计分组后某个字段的和，一列值的总和

SQL

• 条件where，python用法类似，基于表或视图，选中满足条件的组
• 去重distinct:去除掉元组中重复元素
• 子句having:having作用于组，选中满足条件的组，对group by分组结果进行筛选。
• order by:按照某一个属性进行排序
• group by:按照某一个属性进行分组
• exists:判断是否查询到数据(子查询)，不会返回数据，只会产生true/false两值
• not exists: 判断是否查询不到数据(子查询)
• any: 表示子查询任何一个满足就返回true
• all: 表示子查询全部都满足才返回true

结构更新

• 删除操作:

所有的删除操作都可以使用级联，表示删除与之相联系或连接的实体。用于删除某些结构。cascade

drop table SC;
drop database student;
drop view xxx;
drop index

• 更新操作:

用于更新数据的结构。

alter table sc add column starttime datetime;

• 创建操作:

create table SC;
create view view_1 as select * from sc;

数据更新

• 删除操作:

对数据进行删除操作。

delete from student where id='111';

• 更新操作:

对数据进行更新:

update student set name = "pm" where id='11'; 

多表之间关联更新:

update classes set score=score+1 from classes, students where classes.sno=students.sno and sage>=10;

• 插入操作:

插入一个新的元组:

insert into student values ("1", "ammy", "m", 10);

视图

视图是指一个虚表，实际不会存储任何元组元素的表，是由一个或者多个基本的表生成的具有不同属性的虚表。数据库会通过视图来找到存储在原表中的数据。创建视图是通过子查询语句创建，所运行的子查询语句就是视图所包含的属性也就是表头。一个视图的数据是从基本表中导出的，只是去掉了某些行或列。

create view view_1 as 
  select sno, sname ,sage from student where sdept='is';

索引

索引主要的用途是为了提高数据库各种操作的性能，加快数据查询的速度和减少系统的相应。相当于是把我们经常用的查询操作先提前存起来，用的时候只要根据索引去检索数据库就能极大提高效率。索引的作用主要是提高查询速度，时间换空间

create index index_1 on Person (name, age, sex);

模糊查询

一般我们可以检索数据库中某一个字段的部分关键字，这个时候只需要使用like就能进行模糊查询，这个操作会查询跟你所需要的关键字相似的数据。

select * from topic like "中国";

模糊匹配字符有%,_两种符号：

• %: 例如a%b，表示以a开头，以b结尾的字符串，如果不写其中一个，表示任意一个。可以匹配任意长度。
• : 例如a_b，表示以a开头，以b结尾，长度为3的任意字符串，匹配长度根据当前字符串长度和``的个数决定。

完整性约束的定义

• 实体完整性:主键非空唯一

create table Test(
  id int not null primary key,
  name varchar(20) unique,
  age int default 10,
  sex varchar(10) check (sex in ('M', 'F')),
  primary key (id, name),
  foreign key(age) references Test2(age),
  constraint const_name check(条件)
)

可以使用alter table的方式对带有名字的约束进行修改

• 参照性完整性: 外码存在，R的外码是S的主码

create table Test(
  id int not null primary key,
  age int default 10,
  foreign key(age) references Test2(age)
)

• 用户自定义完整性: 根据使用需求使用一些函数来确定完整性，常用的有:not null, unique, null, check

create table Test(
  id int not null primary key,
  name varchar(20) unique,
  age int default 10,
  sex varchar(10) check (sex in ('M', 'F')),
  primary key (id, name),
  foreign key(age) references Test2(age),
  constraint const_name check(条件)
)

tip

• UNIQUE 与 PRIMARY KEY 的差别与联系：

他们都声明了这个属性的唯⼀性；

⼀个关系模式只有⼀个 PRIMARY KEY，但可能有许多 UNIQUE 属性；

PRIMARY KEY 不能为空 （NULL），但是 UNIQUE 可以为空 （NULL）；

UNIQUE 中，可以出现多个 NULL 值。