关系模型

关系数据库的结构

关系数据库由table组成，表中的一行代表了一组值之间的一种联系，一个表就是这种联系的一个集合

元组表示一组值的序列。对应表中的一行。

关系用来指表，元组指代行，属性指代的是列。

关系实例：表示一个关系的特定实例，也就是所包含的一组特定的行。

由于关系是元组的集合，所以元组在关系中出现的顺序是无关紧要的，因为他们具有同样的元组集合。对于关系的每个属性，都存在一个允许取值的集合，称为该属性的域(domain)。

对所有关系r来说，r的所有属性都是原子的。如果域中元素被看作是不可再分的单元，则域是原子的(atomic)。啥叫原子性的，其实就是属性不可以再被划分为再小的属性单元,比如说像address这种,可以分成省份,城市啥啥啥的,这种可以再分的这种情况就是不原子性的,然而这是相对的,根据数据库定义人员对这个字段的定义以及实际业务开发中对这个字段的利用程度而言。

空（null）值是一个特殊的值。表示值位置或者不存在。空值会给数据库访问和更新带来困难，因此应该尽量避免使用null。

数据库模式

码

一个元组的属性值必须是能够唯一区分元组的。

超码是一个多个属性的集合，可以在一个关系中唯一表示一个元组

形式化地描述，设R表示关系r模式中的属性集合。如果我们说R的一个子集K是r的一个超码，则限制了关系r中任意两不同元组不会在K的所有属性上取值完全相等。

  • 超码中可能包含无关紧要的属性。如果K是一个超码，那么K的任意超集也是超码。我们通常研究这样的超码(它们任意的真子集都不能称为超码)。这样的最小超码称为候选码(candidate key)

我们用主码(primary key)这个属于来代表被数据库设计者选中的、主要用来在一个关系中区分不同元素的候选码。码(不论是主码、候选码、还是超码)都是整个关系的一种性质，而不是单个元组的性质。

一个关系模式(如r1)可能在它的属性集合中包括另一个关系模式(如r2)的主键。这个属性在r1上称作参照r2的外键。关系r1也称为外键依赖的参照关系(referencing relation)，r2被叫做外码的被参照关系(referenced relation)

参照完整性约束(referential integrity constraint):参照完整性约束要求在参照关系中任意元组在特定属性上的取值必然等于被参照关系中某个元组在特定属性上的取值

关系查询语言

查询语言：用户用来从数据库中请求获取信息的语言

过程化语言：用户指导数据库执行一系列操作以计算出所需结果的语言

非过程化语言：用户只需描述信息，不用给出获取该信息的具体过程

关系代数

选择语句

SELECT：选出具有特定属性的所有instructor，本质是过滤出一张新表，通常允许在选择谓词中进行比较，也可以用连词and，or，not来将谓词合并

投影语句

PROJECT:一元运算，返回作为参数的关系，但把某些属性排除在外，由于关系是一个集合，所以所有重复行都被去除。运算结果是一个集合

笛卡尔积运算语句

用X表示的笛卡尔积可以将任意两个关系的信息组合在一起。当某个关系自生做笛卡尔积时，必须给关系一个名字以引用其属性

集合并语句

当我们需要出现在这两个集合之一的或同时出现在这两个集合中的所有课程段的ID，可以通过并运算获得。

自然连接

JOIN:要求笛卡尔积的两个关系在所有属性上的值完全一致。先对两个关系计算笛卡尔积，基于两个关系模式都出现的属性上相等性进行选择，最后去重。

1
2
3
select *
from table1
natural join table2;

单个属性相同：table1(A,B) table2(A,C)

1
2
3
4
select t1.*
from table1 t1
inner join table2 t2
on t1.a=t2.a;

多个属性相同：table1(A,,B,D) table2(A,B,C)

1
2
3
4
select t1.*
from table1 t1
inner join table t2
on t1.a=t2.a and t1.b=t2.b

SQL

基本模式定义

1
2
3
4
5
6
7
create table department
(
dept_name varchar(20),
    building varchar(15),
    budget numeric(12,2),
    primary key(dept_name)
);

primary key(A1,A2,…,An):这个声明表示属性(A1,A2,…,An)构成关系的主码，主码属性必须非空且唯一。

foreign key(A1,A2,…,An):foreign key表示关系中任意元组在属性(A1,A2,…,An)上的取值必须对应于关系s中某元组在主码属性上的取值

not null:一个属性上的not null约束表明在该属性上不允许空值，此约束在空值排除在该属性域之外

SQL查询的基本结构

基本结构有三个子句构成select,from,where。

1
2
select name
from instructor;

强行删除重复时可以用

1
2
select distinct dept_name
from instructor;

也可以用all显式指明不去重

1
2
select all dept_name
from instructor;

select字句可以带含有算术表达式，运算对象可以是常数也可以是元组的属性

where子句中允许使用逻辑连词and or not。逻辑连词的原始对象可以是包含比较运算符的表达式。

自然连接

sql支持几种另外的方式来使得来自两个或者多个关系的信息可以被连接起来。

**自然连接(natural join)**运算可以作用于两个关系，并产生一个关系作为结果。自然连接只考虑那些在两个关系模式中都出现的属性上取值相同的元组对。

1
2
select name,course_id
from instructor natural join teaches
1
from r1 natural join r2 natural join ... natural join rm

要说明的是，当两个关系有多个属性名是相同的时候，这时候将要求两个关系中这些相同的属性名都相同。例如:

1
where t1.a = t2.a and t1.b = t2.b and t3.c = t3.c...

SQL还提供了一种自然连接的构造形式，允许用户来指定哪些列相同

1
2
select name,title
from (instructor natural join teaches) join course using(course_id);

其中join….using运算中需要给定一个属性名列表，其中两个输入中都必须具有指定名称的属性。

其他基本运算

更名运算

1
2
3
select name,course_id
from instructor,teaches
where instructor.ID = teaches.ID;

一般来说用as关键词来更改关系中的属性名输出，关系也可以用as关键词重命名（在sql语句中的重命名）。

字符串运算

sql中可以使用一堆单引号来表示字符串，如果单引号是字符串的组成部分，那就用两个单引号字符串来表示。

字符串上可以使用like操作符来实现模式匹配pattern

%：匹配任意的子串

_：匹配任意的一个字符

例子:

  • 寻找以Intro打头的字符串:Intro%
  • 匹配任何包含Comp子串的字符串:% Comp%
  • 匹配只含三个字符的字符串:___
  • 匹配至少含三个字符的字符串:___%

*表示在select子句中表示所有的属性

排列元组的显示次序

order by:字句默认使用升序，可以用desc表示降序，或者使用asc表示升序，当有多个属性时，使用案例如下：

1
2
3
select *
from instructor
order by salary desc,name asc

where子句谓词

对于数值是一个闭区间的where字句运算，我们可以使用between比较运算符

类似的，也可以用not between

sql用(v1,v2,...,vn) 来表示一个分量值为v1,v2,…,vn的n维元组，元组上可以使用比较运算符，按字典顺序进行比较，(a1,a2) <= (b1,b2)

集合运算

sql作用在关系上的union、intersect和except运算对应于数学集合论中的并集、交集和差集运算

并运算

与select字句不同的是，union运算自动会去重。

想保留重复，就用union all。

交运算

与union类似，想保留重复，用intersect all

查运算

类似的，保留重复用except all

聚合函数

聚合函数是以值的一个集合为输入、返回单个值的函数

  • 平均值:avg

  • 最小值:min

  • 最大值:max

  • 总和:sum

  • 计数:count

    sum和avg的输入必须是数字集，其他运算符还可以做用到非数字数据类型集合上，如字符串

基本聚合

考虑查询找出计算机系老师的平均工资

1
2
3
select avg(salary) as avg_salary
from instructor
where dept_name = "计算机系"

在某些情况下计算聚集函数需要先删除掉重复元组，可以在聚集表达式中使用关键词distinct

1
2
3
select count(distinct ID)
from teaches
where semester = "spring" and year = 2010;

sql不允许在使用count(*)的时候使用distinct，但是在使用max和min的时候使用distinct是合法的。

分组聚合

group by子句中给出的一个或者多个属性是用来构造分组的

在group by子句中的所有属性上的取值相同的元组将被分在一个组中。

以下sql示例了如何找到同一个系的各部门教师的平均工资

1
2
3
select dept_name,avg(salary) as avg_salary
from instructor
group by dept_name;

当SQL查询使用分组的时候，一个很重要的事情是需要保证出现在select语句中没有被聚集的属性只能是出现在group by子句中的那些属性。换句话说，任何没有出现在group by子句中的属性如果出现在select子句中的话，它只能出现在聚集函数内部，否则这样的查询就是错误的

1
2
3
4
/*错误查询*/
select dept_name,ID,avg(salary)
from instructor
group by dept_name;

在一个特定分组里边(通过dept_name)来进行定义中的每位教师都有一个不同的ID，既然每个分组只输出一个元组，那么就无法确定选择哪个ID值作为输出。这是原因。

having子句

有时候，对分组限定条件对元组限定条件更有用。

例如:我们只对教师平均工资超过42000美元的系感兴趣，但是该条件并不针对单个元组，而是针对group by子句构成的分组。为了表达这样的查询，可以使用SQL的having子句，having子句中的谓词，在形成分组之后才会起作用的，因此having中可以嵌套聚合函数

1
2
3
4
select dept_name,avg(salary) as avg_salary
from instructor
group by dept_name
having avg(salary) > 42000;
  • 与select子句的情况类似，任何出现在having子句中，但是没有被聚集的属性必须出现在group by 子句中，否则这个查询就是错误的。

嵌套子查询

集合成员资格

sql支持使用in来测试元组是否属于集合中的成员，集合是由select子句产生的一组值构成的，连接词not in测试元组中是否不是集合中的成员

1
2
3
4
5
6
7
select distinct course_id
from section
where semester = "Fall" and year = 2009 and
course_id in (select course_id
              from section
              where semester = "spring" and year = 2010
);

in和not in操作符也可以用于枚举集合的，例如:

1
2
3
select distinct name
from instructor
where name not in("Mozart","Einstein");

集合的比较

在之前的更名运算的学习阶段中，我们对于同一张表的比较通常通过更名运算来完成，如

1
2
3
select distinct T.name
from instructor as T,instructor as S
where T.salary > S.salary and S.dept_name = "Biology";

SQL提供了另外一种方式书写上面的查询。短语”至少比某一个要大”在SQL用>some来表示。

这个结构允许我们用一种更贴近此查询表达的形式重写上面的查询:

1
2
3
4
5
6
select name
from instructor
where salary > some(select salary
                    from instructor
                    where dept_name="Biology"
);

= some等价于in，然而<>some并不等价于not in

现在修改一下我们的查询，如果我们想要找出比所有都大的工资元组，该如何查找呢?

1
2
3
4
5
select name
from instructor
where salary > all(select salary
                  from instructor
                  where dept_name = "Biology");

考虑一个查询:找出平均工资最高的系

1
2
3
4
5
6
select dept_name
from instructor
group by dept_name
having avg(salary) >= all(select avg(salary)
                         from instructor
                         group by dept_name);

空关系的测试

测试一个子查询的结构中是否存在元组

exists结构在作为参数的子查询非空时返回true值。使用exists结构，可以有一种方法书写查询:

请找出在2009年秋季学期和2010年春季学期同时开课的所有课程

1
2
3
4
5
6
7
select course_id
from section as S
where semester = "Fall" and year = 2009 and
exists(select * 
        from section as T
     	where semester = "Spring" and year = 2010 and
      	S.course_id = T.couse_id)

如果一个相关名称既在子查询中定义，又在包含该子查询的查询中定义，则子查询中的定义是有效的。类似于局部变量和全局变量的意思

我们可以用not exists结构来测试子查询结果集中是否不存在元组，我们可以使用not exists结构来模拟集合包含的操作。

我们可以将关系A包含关系B写成not exists(B except A)

数据库的修改操作

删除的操作只能删除整个元组，而不能只删除某些属性上的值。SQL用如下的语句进行表示

1
2
delete from
where P;

注意:delete的执行过程是先检查，后删除的，比如说要删除那些小于大学教师的平均工资的教师，那么如果说我一边删除一边检查，那么最终导致的结果是多删除掉了一部分，这样是不合理的。合理的做法应该是先检查所有教师的工资，然后对其做好标记，待遍历完毕后再进行删除

插入操作

用来在关系中插入记录的

（）表示一个记录，也就是元组

1
2
insert into course
	values("1","111");

元组属性值的排列和关系模式中的属性排列的顺序需要保持一致。当然了，也可以手动指定属性，如:

1
2
3
4
insert into course(title,name)
	values(1,111);
insert into course(name,title)
	values(1,111);

我们想让Music系每个修满144学分的学生成为Muisc系的教师，其工资是18000美元，可以写作

1
2
3
4
insert into instructor
	select ID,name,dept_name,18000
	from student
	where dept_name = 'Music' and tot_cred>144;

相当于将查出来的信息插入到新的关系中

更新操作

所有的教师的工资将增长5%

1
2
update instructor
set salary = salary*1.05;
1
2
3
4
update instructor
set salary = salary *1.05
where salary < (select avg(salary)
                from instructor)

数据库完整性

数据库完整性是指数据库的正确性和相容性，由完整性约束来保证。正确性是指数据库与现实世界是相符合的，无误的。相容性是指表示同一实体的数据是一致的，不会相互矛盾。

分类：实体完整性约束，参照完整性约束，用户自定义完整性约束

实体完整性约束

实体完整性约束通过关系表定义主码来实现的。主码在创建表时使用PRIMARY KEY关键词来定义，也可以通过alter table来定义。若主码只包含一个属性，可以定义为列级约束或表级约束；若主码包含多个属性，则需要定义为表级约束。

实体完整性要求主码字段都不为空，且值或者值的组合在表中是唯一的。若不满足上述条件的话，数据库管理系统会拒绝插入或修改操作，以保证数据的实体完整性。

参照完整性约束

参照完整性是通过定义外码来实现的。

关键词：FOREIGN KEY

外码只包含一个属性，可以定义为列级约束或者表级约束，包含多个属性，定义为表级约束。参照完整性定义了一张表对另一张表的数据的引用，若被引用的纪录被修改或者被删除，引用关系中的记录需要给出处理方法，一般可以在定义外码时给出选择。

参照完整性要求参照表中每条记录中外码的值要么是空值，要么在被参照表中出现。意思是在参照表中的插入，修改外码，都要检查在参照表中检查有没有这个值，没有就出错了。比如takes中的ID是参照student中的ID，如果在takes中插入的元组中ID是student中没有的，那就是错误的。

其他完整性约束

属性的非空约束

sql可以指定指定列不能使用空值。

关键词：NOT NULL

唯一性约束

sql使用UNIQUE约束来指定一个或者一组属性的取值不能出现重复的值。UNIQUE约束等价于关系模型中候选码的概念。和主码的概念类似，但是主码中不能出现空值，而唯一性约束中允许出现空值。一张表也可以定义多个UNIQUE约束。

关键词：UNIQUE

CHECK约束

数据库管理系统用CHECK约束来限定一列或多列数据需要满足的条件。类似于自定义约束。

语法：CHECK(条件)

数据库安全

数据库安全概述

系统运行安全

系统信息安全

基于角色的权限管理机制

角色：指拥有相同权限的数据库用户的集合，每一个角色由不同的权限。

数据库角色：权限范围是数据库级别，该角色是数据库用户的集合。

服务器角色：权限范围为服务器内，控制服务器端对数据库访问的权限，权限不能被修改。系统只有固定服务器角色，不允许用户创建服务器角色。

权限分类：对象权限，语句权限，隐含权限。

sql中的安全管理

创建新用户

1
CREATE USER user[INDENTIFIED BY PASSWORD 'password'][][]...

修改root用户密码

1
2
3
UPDATE MYSQL.user set authentication_string=PASSWORD ("1234")
	WHERE User="root" and Host="localhost";
	FLUSH PRIVILEGES; #重新加载用户权限

MD5函数可以用来加密用户密码

修改普通用户密码

1
set PASSWORD FOR 'user'@'localhost' = 'new_password';

修改用户名

1
RENAME USER old_user TO new_user

删除用户

1
2
DROP USER user
DELETE FROM user WHERE user='username'  AND HOST = 'hostname';

权限管理

授予权限

1
2
3
4
5
6
GRANT ALL ON *.* #用于设置全局权限
GRANT ON db_name.* #用于设置数据库权限
GRANT ON table_name #为具体的表设置权限

#创建用户马秀敏，赋予所有权限
GRANT ALL ON *.* TO '马秀敏'@'localhost' WITH GRANT OPTION

查看权限

1
SHOW GRANT FOR 'user'@'host';

收回权限

1
2
3
4
REVOKE ALL PRIVILEGES,GRANT OPTION FROM 'username'@'hostname' #收回所有权限

#eg 收回马秀敏的CREATE和DELETE权限
REVOKE CREATE,DELETE ON *.* FROM '马秀敏'@'localhost'

数据库设计

基本步骤

需求分析：通常包括调查组织机构，熟悉业务活动，明确数据要求，确定数据库功能

概念结构设计：一般是设计ER模型，主要也是通过ER模型进行数据库的概念抽象

逻辑结构设计：将概念模型转化为数据模型，将ER图转化为关系模型。

物理结构设计：确定存取的数据结构。

数据库实施与维护:数据结构入库和数据转换、数据库试运行、数据库的运行和维护。

ER模型

ER模型包括了实体、属性、联系，表现形式通常为ER图。

实体：指的是客观存在并且可以相互区别的事物。可以是具体的人、事和物，也可以是抽象的概念或联系。同一类型的实体构成的集合称为实体集，ER模型中提到的实体通常是实体集。ER图中通常用矩形来表示实体，内部写实体的名称。

属性：指的是实体或联系所具有的性质或特征。在ER图中，一般使用椭圆来表示属性，并用无向边将其与相应的实体连接起来。其中主码用下划线标识。复合属性有多个子属性构成，通常与多个属性相连接。多值属性指的是一个实体可以用多个此属性，用两个椭圆进行标识。

联系：联系是指不同实体之间的相互关系。一元联系，实体集内部之间的关系，比如一个班级的学生中一些学生管理另外一些学生。二元联系是两个实体集之间的联系，例如学院和学生之间有从属关系，一个学院有多个学生，但一个学生只能对应一个学院。三元联系是由三个实体集参与的联系，例如学生、教师、课程之间有一个选修联系，学生选修一门课程，就意味着他选修有某个教师讲授的课程，教师讲一门课，受众一定是学生，课程一定是有某位老师来讲，一些学生来听的，所以构成了三元联系。在ER图中，联系用菱形来表示，并通过线连接到参与联系的各方实体。

联系中，二元联系最为常见。比较容易量化。二元联系又分为一对一，一对多，多对多的联系。在ER图中，在二元联系中注明其联系的类型。

关系模型

通常的格式记为 实体(属性1,属性2,…,属性n)，其中主码用下划线标识

数据库的外模式设计

也就是设计相应的用户视图。作用是简化用户对系统的使用，方便查询。

数据库的物理结构设计

如果一个或一组数据经常在查询条件中出现，则考虑在这组属性上添加索引。

如果一个属性经常作为最大值和最小值等聚集函数的参数，则考虑添加索引。

如果一个属性经常在连接操作的连接条件上出现，则考虑添加索引。

关系数据库规范化理论

数据库规范化

数据库规范化是根据一系列凡是来构造数据库的过程，以减少数据冗余并提高数据完整性。主要使用来解决数据冗余的问题。

函数依赖与范式

函数依赖:任意一个合法的实例使之符合所有R关系模式的约束的实例。任意一个模式都有任意多个合法的实例。对函数依赖a->b一般称a为决定因素，b为被决定因素。假设R是一个关系模式，a属于R，且a->b,若b是a的子集，则a->b称为平凡的函数依赖。

完全函数依赖：假设R是一个关系模式，a,b属于R，且a->b，若对a的所有子集k,都有，k->b不成立，则称b完全依赖于a，否则称b部分函数依赖a。