数据库学习笔记

一、数据库基本概念

---

1. 数据库：是长期存储在计算机内、有组织的、可共享的大量数据的集合。数据库中存储的是数据及数据之间的关系。正常情况读写文件系统比数据库快一到两个数据级；数据库的查询，大量并发的时候可能最浪费时间的是connect和close。数据库的优势是体现的大量数据的查询、统计以及并发读写，不是在速度上。其特点：永久存储、有组织、可共享。（数据的最小存取单位是数据项）
2. DBMS：database management system，数据库管理系统
3. SQL：Structured Query Language，结构化查询语言
4. 事务：通常将一个或一组数据库操作组成一个事务；事务的ACID性质：1.atomicity原子性;2.isolation独立性；3.durability持久性；4.consistency一致性
5. E/R模型：实体关系模型
6. UML：统一模型语言Unified Modeling Language
7. 超键：一个包含键的属性集叫做超键

二、关系数据模型

---

数据模型：由数据结构、数据操作、数据上的约束组成

title	year	length	genre
Gone with the wind	1939	231	drama
Star Wars	1977	123	scifi
wayne’s world	1992	95	comedy

1. 关系模型基础

通俗来讲，一个关系就是一个二维表。
关系的列命名为属性
关系名和属性集合的组合成为这个关系的模式（schema）：描述一个关系模式时，先给出一个关系名，其后用圆括号括起所有属性，例如Movies(title,year,length,genre)
关系中除含有属性名所在行以外的其他行叫做元组，每个元组均有一个分量对应于关系的每个属性，元组表示Gone With the Wind,1939,231,drama。
域：关系中任一元组的分量值都必须属于对应列的域。上表对应的域分别：string,integer,integer,string。上表模型还可以表示为Movies(title:string,year:integer,length:integer,genre:string)
实例：一个给定关系中元组的集合叫做关系的实例
键：通过定义键可以保证关系实例上任何两个元组的值在定义键的属性集上取值不同，通常在形成键的属性下面画上横线。

2. 数据类型

1. 可变长度或固定长度字符串。类型CHAR(n)表示最大为n个字符串的固定长度字符串，也可以用VARCHAR(n)也可表示最多有n个字符的字符串。它们的区别与具体实现有关，一般来说，字符串类型var的区别：有var表示可变长度，没有var表示不可变长度，如果长度不够，末尾补空格。
2. 固定或可变长度的位串。和字符串相似，只不过它们的值是比特，BIT(n)表示长度为n的位串，而BIT VARING(n)表示最大长度为n的位串
3. BOOLEAN表示逻辑类型的值，该类属性的值可能是TRUE/FALSE和UNKNOWN
4. 类型INT和INTEGER表示典型的整数值
5. FLOAT和REAL表示浮点值，更高精度的有DOUBLE PRECISION
6. 日期和时间通过DATA和TIME数据类型表示

3. 最简单的表定义

最简单的关系模式的定义形式是由保留字CREAT TABLE后面跟着关系名及括起来的由属性名和类型组成的列表

CREAT TABLE Movies(
	title	CHAR(100),
	year	INT.
	length	INT,
	genre	CHAR(10)
);

4. 修改关系模式

删除某个关系：DROP TABLE RR表示关系名
修改：ALTER TABLE MovieStar ADD phone CHAR(15);添加phone属性
ALTER TABLE MovieStar DROP birthday;删除birthday属性
ALTER TABLE MovieStar MODIFY(ID char(10))//修改关系中的属性

5. 默认值

gender CHAR(1) DEFAULT '?'//gender属性默认值设？
birthday DATE DEFAULT DATE '000-00-00'
ALTER TABLE MovieStar ADD phone CHAR(15) DEFAULT 'UNLIST'//新增时设置默认值

6. 关系代数概述

• 通常的关系操作：
  交: R ∩ S
  并: R ∪ S
  差；R - S
• 除去某些行或列的操作：
  选择是消除某些行的操作，σ_length>=100(Movies)
  投影是消除某些列的操作, π_{title,year,length}(Movies)
• 组合两个关系元祖的操作。包括笛卡尔积运算:R x S
• “重命名”操作，改变关系模式，即属性的名称或者关系本身的名称改变
• 人们一般将关系代数的表达式称为查询
  并（∪）、差（-）、交（∩）、笛卡尔积（×）

笛卡尔积（直积）：表示为X × Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员。
例如，A={a,b}, B={0,1,2}，则
A×B={(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}

7. 引用完整性约束

***引用完整性约束***是一种普通普通的约束，它规定某个上下文中出现的值也必须在另一个相关的上下文出现。举例来说，在Movies数据库中，关系StarsIn的一个元组的starName分量的值为p，那么，人们希望值p作为某个演员的名字也出现在关系MoviesStar里。

关系代数将引用完整性表述为π_A®$\subseteq$π_B(S)

8. 键约束

9. 数据库设计范式

1NF：数据表中的每一列必须是不渴拆分的最小单元；确保每列保持原子性。

2NF：满足1NF后，要求表中的所有列，都必须依赖于整个主键，而不只是部分的键。确保表中的每列都和主键相关

3NF：必须先满足2NF，要求：表中的每一列只与主键直接相关而不是间接相关，（表中的每一列只能依赖于主键）；确保每列都和主键列直接相关，而不是间接相关

三、关系数据库设计理论

---

1. 函数依赖

关系R上的***函数依赖（functional dependancy),FD***是指“如果R的两个元组在属性A1.A2.A3…上一致，那么它们必定在其他属性B1.B2.B3…上也一致。依赖形式记为A1A2A1…An→B1B2…Bn，并称A1…AN决定B1…BN。

2. 关系数据库模式设计

2.1 异常

当试图在一个关系中包含过多信息时，产生的问题（如冗余）称为异常。
冗余：信息没有必要地在多个元组中重复
更新异常：可能修改了某个元组的信息，但是没有改变其他元组中的相同信息
删除异常：如果一个值集变成空集，就可能带来丢失信息的副作用。

2.2 分解关系

一般用分解关系来解决异常。关系R的分解设计分离R的属性，以构造两个新的关系。

四、高级数据库模型

---

4.1 E/R模型

实体-联系模型（entity-relationship model)，包括实体集，属性，联系
实体：实体是某种抽象对象，实体集是实体的集合。如表，所有电影的集合构成一个实体集（矩形）
属性：属性是这个实体集中实体所具有的性质。（椭圆）
联系：多个实体集的连接。（菱形）
键：在属性下画下划线

4.2 统一建模语言UML

4.2.1 UML类

与E/R模型中的实体集类似。一个类框分为三部分，顶部是类的名字，中间是属性，下面的是方法

Movie
title K year PK length genre
<place for method>

4.2.2 UML的键

PK表示主键

4.2.3 关联

在类之间的二元联系称为关联。UML中没有相似的多路联系，一个多路联系可以拆解成多个二元联系。
用线表示关联，名字放置在线下面

4.2.3 聚集和组合

聚集：是在两个类之间的一条线，这条线的末端是空的菱形。
组合：组合的菱形是黑色的

五、代数和逻辑查询语言

---

六、数据库语言SQL

---

6.1 SQL中的简单查询

FROM：给出查询所用的关系，本例中关系是Movies
SELECT：决定满足条件元组的那些属性应该在结果中列出。*表示列出所有属性
WHERE：条件子句，即满足的条件
AS：重命名结果关系的属性
s LIKE p：s是一个字符串，p是模式

SELECT title AS name,length AS duration/
FROM Movies
WHERE sudioName = 'Disney' AND (year=1990 OR title LIKE 'Star____'

关于匹配

SELECT title
FROM Movies
WHERE title LIKE '%''s%';//两端的单引号不代表字符串中的单引号，两个单引号代表一个单引号，%匹配任意字符串

日期和时间

DATE'1948-05-13'//保留字DATE+单引号括起来，单个数字前面加0
TIME'15：00：02.5'//保留字TIME+单引号，24小时制，分秒是在秒后加.
TIMESTAMP'1948-05-13 12:00:00'//中间用空格分开

关于空值NULL

1. 未知值：即知道它有一个值，但不知道是什么，如未知的生日
2. 不适用的值：如果moviestar有一个spouse属性，对未婚的影星，这个属性值可能为NULL值
3. 保留的值：属于某对象但无权知道的值，例如未公布的电话号码

注意点：

1. 对NULL和任何值进行算术运算，其结果仍是NULL
2. 当使用比较运算，NULL和任意值比较，结果都是UNKONWN(布尔值）

输出排序
ORDER BY <list of attributes> dsc
DSC表示降序；ASC表示升序。

6.2 多关系查询

SQL中的积和连接

SELECT name
FROM Movies,MoviesEXec
WHERE title='Star Wars' AND productC#cert#;

消除属性歧义
在属性前加上关系名，例如Movie.name

查询的交并差
使用保留字UNION INTERSECT和EXPECT

(SELECT name,address
FROM MovieStar
WHERE gender='F')
	INTERSECT
(SELECT name,address
FROM MovieExec
WHERE netWorth >100000);//给出既是女影星又同时是具有超过10000资产的制片人的名字和地址

6.3 子查询

当某个查询是另一个查询的一部分时，称为子查询。可以有多级子查询。

1. 子查询能够范围单个常量。这个常量能在WHERE中和另一个常量比较；
2. 子查询能够返回关系。该关系可以在WHERE子句中以不同的方式使用；
3. 像许多存储的关系一样，子查询形成的关系能出现在FROM子句中，并且后面紧跟该关系的元组变量

产生标量的子查询
能够成为元组字段值的原子值成为标量

SELECT name
FROM MovieExec
WHERE cert# =
	(SELECT producerC#
	 FROM Movies
	 WHERE title ='Star Wars');

关系的条件表达式

1. EXISTS R是一个条件，表示当且仅当R非空时为真
2. s IN R 为真，当且仅当s等于R中的某一个值

元组的条件表达式
如果某个元组s和关系R的元组有相同的组成分量个数，那么两个可以使用IN 或<> ANY R等等进行比较

SELECT name
FROM MovieExec
WHERE cert# IN
(SELECT producerC#
FROM Movies
WHERE (title,year) IN 
(SELECT movieTitle,MovieYear
FROM StarsIN
WHERE starName='Harrison Ford')
);//找出Harrison Ford演过的电影的制片人

FROM子句中的子查询

SELECT name
FROM MovieExec,(SELECT producerC#
			FROM Movies,StarsIn
			WHERE title=movieTitle AND
			year=MovieYear AND
			starName ='Harrison Ford')Prod//集合取别名为Prod
WHERE cert#=Prod.producerC#;//使用FROM子句中的子查询找出Ford出演的电影的制片人 

SQL的连接表达式
可以通过许多不同的连接运算符作用在两个关系上创建新的关系。这些不同的运算包括积、自然连接等等。结果本身可以作为一个查询。
连接表达式最简单的形式是***交叉连接***，即积或者笛卡尔积

6.3 全关系操作符

消除重复：可以在保留字SELECT后跟上DISTINCT（默认保留重复的元组）例如SELECT DISTINCT name
交并差中的重复：集合操作中的交并差在默认情况下消除重复，也就是说，被转化成了集合，如果要阻止消除重复，必须在保留字UNION等后加上ALL
聚集操作符：SUM、AVG、MIN、MAX、COUNT 例如```SELECT COUNT(starName) FROM StarsIN
分组：GROUP BY

SELECT dept_name avg(salary) AS avg_salary
FROM instructor
GROUP BY dept_name;//查找出每个系的平均工资

注意：任何没有出现在group by子句中的属性，如果出现在select子句中的话，它只能出现在聚集函数内部，否则这样的查询是错误的。
HAVING子句：类似于where子句，但其是对分组限定条件，而不是对元组限定条件。having子句中的为此在形成分组后才起作用，因此可以使用聚集函数

SELECT dept_name AVG(salary) AS avg_salary
FROM instructor
GROUP BY dept_name
HAVING AVG(salary)>42000//找出教师平均工资超过42000美元的系

注意：与select子句的情况相似，任何出现在having子句中，但没有被聚集函数的属性必须出现在group by子句中，否则错误

6.4 数据库更新

插入

INSERT INTO R(A1,...An)VALUES(v1,...vn);

该插入将创建一个元组，其属性A1_{An的值为v1}vn；如果属性列表中不包括关系R中所有的属性，那么创建的元组对那些没有赋值的属性赋予一个默认的值。==当省略属性时，一定要保证提供的属性值的顺序和关系属性的标准顺序一致。

删除

DELETE FROM WHERE <条件>

不能简单地指定把某个元组删除，必须通过where子句明确地描述要删除的元组

修改

UPDATE R SET<新值赋值>WHERE<条件>;

每一个新值赋值有一个属性、一个等号和一个表达式组成。若有不止一个赋值，就用逗号隔开。该语句效果，找出所有满足条件的元组，然后对每个元组计算出赋值表达式的结果，并根据R的对应属性对元组字段进行赋值

6.5 嵌套子查询

1. EXISTS R是一个条件，表示当且仅当R非空时为真
2. s IN R为真，当且仅当s等于R中某一个值。s NOT IN R为真，当且仅当s不等于R中的任何一个值。
3. s > ALL R为真，当且仅当s大于一元关系R中的任何一个值。
4. s > ANY R为真，当且仅当s至少大于一元关系R中的某个值

6.6 事务

事务是一组操作，事务具有原子性，一组操作会完整执行下来，不会走到一半遇到崩溃就不走了；事务具有可串行性，例如AB同时订票，必须串行执行。

SQL命令START TRANSACTION可用来标记事务的开始。有两种结束事务的方式：

1. SQL语句COMMIT使得事务成功结束。事务中进行的修改会被持久地建立在数据库中，即他们被***提交***了。在COMMIT语句前，改变是试探的。
2. SQL语句ROLLBACK用来回退或者撤销SQL语句，所做修改不会持久地出现在数据库中

七、约束与触发器

---

7.1 键和外键

在SQL中可以将关系的一个属性或属性组声明为***外键***，该外键引用另一个关系的属性。外键声明隐含着如下两层意思：

1. 被引用的另一个关系的属性在它所在的关系中，必须被声明为PRIMARY KEY或UNIQUE 否则，不能作为外键。
2. 在第一个关系中出现的外键值，也必须在被引用关系的某一个元组的属性中出现。
   对于主键，有两种方法设置外键：
3. 如果外键是单个属性，则可以在此属性的名字和类型后，声明其引用某个表的某个属性。REFERENCES <表明>(<属性名>)
4. 可以在CREATE TABLE语句的属性列表上追加一个或多个声明，来说明一组属性是一个外键。```FOREIGN KEY (<属性名列表>) REFERENCE <表明> (<属性名列表>)

7.2 属性和元组上的约束

在SQL的CREATE TABLE语句中可以声明两种约束：

1. 在单一属性上的约束
2. 在整个元组上的约束

基于属性的CHECK约束
如果数据库的修改没有改变与约束相关的属性，则不进行基于属性的CHECK约束检查。

gender CHAR(1) CHECK(gender in('F','M'))

基于元组的CHECK约束
为了对单个表R的元组声明约束，在用CREATE TABLE语句定义表时，可以在属性列表或外键声明上附加CHECK保留字，其约束条件用括号括起。每次向关系R插入元组以及当R的元组被修改时，都要检查基于元组的CHECK约束条件。

CREATE TABLE MovieStar(
	name CHAR(30) PRIMARY KEY,
	address VARCHAR(255),
	gender CHAR(1),
	birthday DATE,
	CHECK (gender='F' OR name NOT LIKE 'Ms.%')
);

修改约束
为了修改或删除一个已经存在的约束，约束必须要有名字
对上述SQL语句进行修改

name CHAR(30) CONSTRAINT NameIsKey PRIMARY KEY

gender CHAR(1) CONSTRAINT NoAndro CHECK(gender IN ('F','M'))

ALTER TABLE MovieStar DROP CONSTRAINT NameIsKey;-- 删除约束
ALTER TABLE MovieStar ADD CONSTRAINT NoAndro
CHECK (gender IN ('F','M'));-- 添加约束

7.3 触发器

触发器：仅当数据库程序中声明的事件发生时，触发器被激活。所允许的事件种类通常是对某个特定关系的插入、删除、或修改。当触发器声明的条件满足时，则与该触发器相连的动作由DBMS执行。
主要特征：

1. 触发器的条件检查和触发器的动作可以在触发事件执行之前的数据库的状态上或触发动作被执行后的状态上执行。
2. 条件和动作可以引用元组的旧值和触发事件中更新的新值
3. 更新事件可以被局限到某个特定的属性或某些属性
4. 程序猿可以选择动作执行的方式：1.一次只对一个更新元组；2.一次针对在数据库操作中被改变的所有元组

CREATE TRIGGER NetWorthTrigger -- 创建触发器
AFTER UPDATE OF netWorth ON MovieExec -- 指出触发器是在触发事件之前还是在触发事件之后使用数据库状态的子句
REFERENCING-- 此子句允许触发器的条件和动作引用正被修改的元组
	OLD ROW AS OldTuple,
	NEW ROW AS NewTuple
FOR EACH ROW-- 告诉触发器每修改一个元组执行一次的方式
WHEN (OldTuple.newWorth>NewTuple.netWorth)-- 使用保留字WHEN和逻辑表达式的条件，告诉触发器发送时间
	UPDATE MovieExec-- 触发器执行的动作
	SET netWorth = OldTuple.netWorth
	Where cert#=NewTuple.cert#；

八、试图和索引

---

8.1 虚拟试图

虚拟试图(virtual view)并不以物理形式存在，试图通过类似查询的表达方式定义。

假设有个试图是关系

Movies(title,year,length,genre,studio,studioName,producerC#)

试图创建

CREATE VIEW ParamountMovies(movieTitle,prodName) AS-- 属性重命名
	SELECT title,year
	FROM Movies
	WHERE studioName = 'Paramount';

视图查询

SELECT title
FROM ParamountMovies
WHERE year=1979;

8.2 视图更新

视图删除

DROP VIEW paramountMovies;

可更新视图

视图一般用于检索，也可以更新。如果MySQL不能正确地确定被更新的基数据，则不允许更新。即如果视图定义中包含：分组（使用GROUP BY 和 HAVING）、联结、子查询、并、聚集函数、DISTINCT、导出列，则不能进行视图更新。

视图更新操作同表的更新操作

8.3 SQL中的索引

关系中属性A上的索引是一种数据结构，可以认为是二叉查找树中的键-值对。索引的键可以来自关系的任何一个属性或属性组，而不必是建立索引的关系的键属性。为了区别索引的键与关系的键，将索引的属性称为***索引键***。典型的DBMS中用到的最重要的数据结构是B-树。

建立索引的动机：当关系变得很大时，通过扫描关系中所有的元组来找出那些匹配给定查询条件的元组的代价太高。例如：

SELECT *
FROM Movies
WHERE studioName='Disney' AND year=1990;

关系中可能存在10000个电影元组，但只有大约200部是1990年制作的。实现这个查询最原始的方法是获取10000个元组，并用WHERE子句逐个测试。

索引的声明：CREATE INDEX YearIndex ON Movies(year)
该语句结果是在关系Movies的属性year上创建一个名为YearIndex的索引。