数据库基本概念
数据独立性:逻辑独立性、物理独立性
数据模型:数据模型 是在数据库用来描述现实世界的方法。
最早:层次模型、网状模型
关系数据模型 基本数据结构 table relation
所有模型查询结果–>表 --> 形成的封闭空间–> 代数的系统为 关系代数
SQL 是非过程化的语言。
soft link 软连接:指关系模型的数据关系 ,有区别于层次模型和网状模型的指针 和链表 。
属性:描述实体的每一个信息。
属性必须是原子的,即表中不能套表 。 —》一范式 1NF
关系是属性值域上定义的n元联系。
关系R =(A1/D1, A2/D2,…An/Dn) :关系R的模式
实例r = {t1, t2, …tn}, t=D1×D2×…×Dn t 为tuple
主键:决定该元组唯一的一个属性/属性组 (候选键)
超键:该属性组能唯一决定元组;但属性组的子集不满足条件。
全键:主键 是关系所有的属性组成。
外键:关系的一组属性引用另外一个元组,是另外一个关系的主键。
引用完整性:外键不为空。
完整性约束(系统会强制检查):域完整性约束(值在值域中);实体完整性约束(主键不为空)。
关系代数
是关系模型的理论基础。
五大基本操作:
- 选择
- 投影
- 笛卡尔积
- 集合差
- 并
并兼容:两个表的属性个数和内容一样,可做并、交、差运算。
关系数据库学习用例
5个基本操作,如果支持这5个则关系是完备的
操作 ρ(C(1->sid1.5->sid2),S1×S2) 得到下表(ρ 是重命名符)
将S1和S2笛卡尔乘积,将新表第一个属性命名为sid1,第五个命名为Sid2(这样属性名不会冲突了)
连接操作:看第一行,连接操作 是选择操作和笛卡尔操作组合而成。(但是这样效率低,之后的查询优化会有新)
(最后一行)这是条件连接,S1的sid小于R1的sid连接。 条件连接 有的书上为theta连接
等值连接:当条件全为等值判断。
自然连接:两张表在所有的公共属性上做等值连接。(用的最多的)、
除法操作
A/B :对于 任意y属于B 的,在关系A中一定有元组<x,y > 的x值。(注意下图的要全部整除的条件)
怎样用基本操作来表达除法操作?
外连接:左外连接 、右外连接、全外连接
左外连接:把连接运算符的左边关系的元组全部保留下来,找不到的元组就在右边补空值。
外并操作
外并操作的属性集就是参与运算的两属性的并。
关系演算
和关系代数等价的,但是是非过程化的。 是SQL语言的基础。
- Tuple relational calculus TRC 元组关系演算(变量定义在元组上)
- Domain relational calculus DRC 域关系演算(以属性为单位定义)
其他数据模型
普通的关系数据模型没有时间上的查询。
时态关系数据模型:在此基础上增加了时态查询等
空间数据模型:。。。
XML数据模型:
- Native XML DBMS 从底层文件存储结构就是XML设计的。(存XML 数据库系统)
- XML Enable DBMS 在原来关系数据库包了一层,可以i把外部数据转换成数据库里关系的表来存储,可以把SQL查询结果转换成XML文档发布。(Oracle 等都在用)
E-R数据模型虽然没有取代关系数据模型,但是E-R图在设计阶段得到很大用处。
实体–联系–属性(联系也有属性)
用户接口和SQL
用户接口:
- Query language
- GUI
- APIs
- Class Library
Query Language:
- 形式化查询语言 如SQL-Structual Query Language–>Standad,有严格文法,非过程化查询语言
- 表格式查询语言 、图形查询语言 TQL GQL
- 受限的自然语言
SQL
- DDL 数据定义语言 ,创建和删除(一般用GUI来做)
- QL 查询
- DML 数据操纵语言,插入、删除、更新
- DCL 数据控制语言 控制用户权限
重要术语
- 基表 一个关系,在磁盘上存在的
- 视图 一个虚表,通过映射、计算等
- 数据类型 不同系统 支持的数据类型有的不同
- NULL空值 关键字 出现三值逻辑
- UNIQUE 保留字 创建表时说明是否允许又重复字(和主键不同)
- DEFAULT 保留字 系统自动填补的缺省值
- 外键 定义了外键系统会自动检查引用完整性
- CHECK 保留字 创建表时定义完整性约束,之后系统自动检查
基本SQL查询
SELECT [DISTINCT] target-list -- distinct 要求系统消除查询结果的重复元组,
FROM relation-list
WHERE qualification; -- 后为bool表达式
/*
系统所做的:在FROM 的表做笛卡尔乘积 -->利用WHERE 的bool表达式做晒选--> SELECT 中的属性做投影 -->distinct排序
但是以上效率太低,之后有查询优化器
*/
SELECT S.sname
FROM Sailor S, Reserves R --这里给表设置别名 S和R 尽量这样用比较规范
WHERE S.sid=R.sid AND R.bid=103; --或则 WHERE Sailors.sid=Reserve.sid AND bid=103 bid单一,不会混淆
SELECT S.age,age1=S.age-5,2*S.age AS age2
FROM Sailors S
WHERE S.sname LIKE 'B_%B';
/*
将查询结果age-5 赋给age1 (新命名),AS 也是另一个取名方法
单引号的下划线 _ 表示匹配任意一个字符 % 表示任意匹配0到多个字符 模糊匹配
以上不一定每个系统都支持
*/
查询预定过红船或绿船的水手:
SELECT S.sid
FROM Sailors S, BoatS B, Reserves R --这里是拼接三张表
WHERE S.sid=R.sid AND R.bid=B.bid AND(B.color='red' OR B.color='green');
--以下是 使用 UNION
SELECT S.sid
FROM Sailors S,Boats B,Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid AND B.color='red'
UNION
SELECT S.sid
FROM Sailors S,Boats B,Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid AND B.color='green';
如果要查询预定了红船和绿船的水手? 上面的OR 不能单纯换成 AND
SELECT S.sid
FROM Sailors S,Boats B, Reserves R1,Boats B2,Reserves R2
WHERE S.sid=R1.sid AND R1.bid=B1.bid
AND S.sid=R2.sid AND R2.bid=B2.bid
AND (B1.color='red' AND B2.color='green');
关联嵌套
SQL语言关于集合的操作 UNION , INTERSECT , EXCEPT
-- 查找预定了103 船的水手姓名
SELECT S.sname
FROM Sailors S
WHERE EXISTS (SELECT *
FROM Reserves R
WHERE R.bid=103 AND S.sid=R.sid); --这里的 sid 就是外面的S 的sid
-- 里面的嵌套子查询 在多次循环里效率低
--查找只被一个水手预定过的船号
SELECT bid
FROM Reserves R1
WHERE bid NOT IN(
SELECT bid
FROM Reserves R2
WHERE R2.siz!=R1.sid);
可用格式 op ANY ,op可选{<,>,=,>=,<=,!=} 这个不一定所有都支持
--查找任意一个级别比Harry 高的水手
SELECT *
FROM Sailors S
WHERE S.rating > ANY (SELECT S2.rating
FROM Sailors S2
WHERE S2.sname='Harry');
--查找预定过红船和绿船的水手编号
SELECT S.sid
FROM Sailors S,Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid AND B.color='red'
INTERSECT
SELECT S.sid
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid AND B.color='green';
除法聚集函数
--Find sailors who've reserved all boats.
--solution 1:
SELECT S.sname
FROM Sailor S
WHERE NOT EXISTS
((SELECT B.bid
FROM Boats B)
EXCEPT
(SELECT R.bid
FROM Reserves R
WHERE R.sid=S.sid));
--solution 2:
SELECT S.sname
FROM Sailors S
WHERE NOT EXISTS (SELECT B.bid
FROM Boats B
WHERE NOT EXITS (SELECT R.bid
FROM Reserves R
WHERE R.bid=B.bid AND R.sid=S.sid));
统计函数计算
--查有多少属性
SELECT COUNT(column_name) FROM table_name;
SELECT COUNT