数据库 - 基础1

qq_1403034144

已于 2024-05-22 13:58:39 修改

阅读量327

点赞数

分类专栏：数据库文章标签：数据库

于 2020-06-02 07:43:25 首次发布

本文链接：https://blog.csdn.net/qq_40893824/article/details/106404794

版权

数据库专栏收录该内容

13 篇文章 0 订阅

订阅专栏

部分内容思路来源
https://www.cnblogs.com/x739400043/p/5815099.html

下一节链接：https://blog.csdn.net/qq_40893824/article/details/106441068

0 联系

数据库就是结构体可视化

数据库 3 种数据模型

分类	解释
物理模型	数据在系统内部的表示、存取，实现数据的物理存储包括数据的存储位置、索引存放位置、存储文件位置、存储策略
概念模型（E-R模型）	实现用户的需求定义的模型，初步表达用户需求
逻辑模型	按用户观点对数据建模，用于数据库设计包括：层次模型、网状模型、关系模型、面向对象数据模型、对象关系数据模型、半结构化数据模型、非结构化数据模型

1 基本概念

概念 1	解释
数据 Data	数据库中存储的基本对象可以是数值,也可以是非数值
数据库 DB	长期存在计算机中，大量数据的集合
数据库管理系统 DBMS	获取、维护数据的软件
数据库系统 DBS	包括DB、DBMS、DBA(数据库管理员)

1.1 DBMS

功能

数据定义、操作、管理、维护

数据定义
创建、删除、修改数据
DDL(Create, Drop, Alter, truncate)
数据操纵
数据库查询、更新
DML(Delete, Insert, Update)
数据库事务管理、运行管理
并发控制、系统恢复、安全性、完整性检查等
数据库的建立和维护、组织和存储
初始数据输入、存储、管理（数据字典、用户数据）、转换、性能监视

ddl、dml、dql、tcl
https://www.techtarget.com/whatis/definition/Data-Definition-Language-DDL
https://blog.csdn.net/weixin_44169484/article/details/119255935
DDL(Create, Drop, Alter)，
DDL 语句执行时，它会立即在数据库中生效
定义和管理数据库中的所有对象的语言，对数据库中的某些对象(例如database,table)进行管理

DML(Delete, Insert, Update) 增删改
DQL(Select) 查询

TCL(Transaction Control Language)事务控制
commit、rollback、savepoint、set transaction

分类

中小型：mysql
大型：SQL Server ( 外国 )、达梦（国产数据库 = 高仿Oracle）

2 概念模型 ( 信息模型 = 数据库设计)

2.1 术语

名词	解释
实体 Entity	客观存在、并可互相区别
属性 Attribute	实体可以有多个属性学生实体由学号、姓名、性别、出生日期、系别属性组成
域 Domain	属性的取值范围
码 key	唯一标识实体
实体型 Entity Type	有同属性的实体所具有共同的特性和性质如：学生（学号，性别，出生年月）
实体集 Entity Set	同属性实体的集合
联系	不同实体集的联系

2.2 表示方法

概念模型的表示方法：E-R图，也称 E-R模型

	形状
实体	矩形
属性	椭圆
联系	菱形

2.3 联系

多实体型	单实体型

两类：多实体型联系、单实体型联系，均符合下面的关系
3类	例子
:–	:–
1对1 `1:1`	班级与班长
1对多 `1:n`	班级与学生
多对多 `m:n`	课程与学生

2.4 设计原则

真实、不冗余、简单

2.5 例子 - 工厂

实体：

仓库
(仓库号，面积，电话号码)
零件
(零件号，名称，规格，单价，描述)
供应商
(编号，姓名，地址，电话号码，账号)
项目
(项目号，预算，开工日期)
职工
(工号，姓名，生日，职称)

3 逻辑模型

格式化模型	层次模型
格式化模型	网状模型
主流	关系模型
新型模型	面向对象数据模型
	关系对象数据模型
	半结构化数据模型
	非结构化关系模型

关系模式：
关系名(属性1，属性2，…，属性n)

来源
https://www.cnblogs.com/x739400043/p/5815099.html

模型	逻辑结构	优点	缺点
层次模型	有序树	1 结构加简单 2 指针记录边向联系，查询效率高 3 完整性好	1 只能表示`1:n`的联系，可以表示`m:n`，但很复杂 2 树的结构限制了数据库访问路径 3 访问路径唯一
网状模型	图、网	1 表达清晰 2 可快速存取	1 结构复杂 2 不易掌握，需要配合另一种高级语言(如C语言) 3 程序复杂
关系模型	二维表	1 数据结构单一、简单 2 关系规范化 3 操作方便	1 查询效率不如格式化模型 2 为提高性能，可以优化用户查询，增加数据库管理系统的负担

3.1 关系模型二维表

逻辑结构：二维表

列 - 属性
表格 - 实体集
行、列无序
无重复行
列不可再分

例子

正误	图表
正确
错误不符合“列不可再分”

3.1.1 术语

术语	解释
关系 Relation	二维表
元组 Tuple	表的 1 行
属性 Attribute	表的 1 列
码 Key	表中某属性组，可确定唯一元组
域 Domain	属性的取值范围
分量	元组中 1 个属性值

关系模式：
关系名 ( 属性1，属性2，…，属性n )

3.1.2 操作

增、删、查、改

数据操作是集合操作
操作对象、操作结果都是关系（即若干元组的集合）

3.1.3 关系模型的完整性约束

实体完整性
参照完整性
用户定义完整性
具体见：本节 3.3.3

3.2 三级模式与二级映象

三级模式

3个模式	解释	特征
外模式(Subschema) 也称子模式或用户模式	局部(用户)数据的逻辑结构、特征的描述	1 常是模式子集 2 一个数据库可有多个外模式 3 一个应用程序仅能用 1 个外模式 4 多个应用程序可共用 1 个外模式
模式也称逻辑模式	数据逻辑结构、特征的描述	1 全体性：1 个数据库仅 1 个模式 2 逻辑性：student(sno,sname,ssex,sage,sdept) 3 特征性：数据类型、长度、约束
内模式(Storage Schema) 也称存储模式	数据物理结构、存储方式的描述	1 一个数据库仅 1 个内模式

1 个数据库系统中：模式、内模式均仅 1 个，外模式能有多个
外模式：不同用户可以看到不同视图，可设置不同的权限

图

二级映象与数据独立性

独立性	解释
逻辑独立性	数据逻辑结构修改时，应用程序不改变
物理独立性	数据存储结构修改时，应用程序不改变

外模式/模式映像
模式/内模式映像

图

3.3 关系数据库

3.3.0 关系的类型、模式、数据库

关系	解释
关系类型	1 基本关系 ( 基本表 ) 2 查询表（查询结果呈现出的表） 3 视图（由基本表或其他视图导出的虚表）
关系模式	就是 D₁×D₂×…×D_n的子集记为：R(D₁，D₂，…，D_n) 是二维表行是元组，列是域，列起个名，该名字是属性
关系数据库	关系模式的集合

3.3.1 术语

术语	解释
域 Domain	同数据类型的值的集合整数，实数，长度小于25字节的字符串集合 {0，1}，大于0且小于100的正整数都可以是域
笛卡尔集 Cartesian Product	域 D₁,D₂,…,D_n 其笛卡尔积为 D₁×D₂×…×D_n = {(d₁,d₂…,d_n`) l d_i∈D_i，i=1,2,…,n} 每个元素(d₁,d₂,…,d_n)叫 1 个 n 元组（n-tuple），简称元组（Tuple） d_i 称为分量（Component）元组个数 = 各属性值个数的乘积例子
关系 relation	就是 D₁×D₂×…×D_n的子集记为：R(D₁，D₂，…，D_n) 是二维表行是元组，列是域，列起个名，该名字是属性
-----------	----------------
候选码 Candidate key	能唯一标识某一元组的极小属性组
主码 / 主键 Primary key	若有多个候选码，选一个为主码
外码 / 外键 Foreign key	关系R中，某属性组不是R的候选码但它是另一关系S的候选码，则它是R的外码
全码 All key	关系的所有属性组成的码，必是候选码 = 全码
----------	-------------
主属性 Prime attribute	含在任何候选码中的属性
非主属性 Non-key attribute	不包含在任何候选码中的属性

3.3.2 操作

增删查改

3.3.3 关系模型的完整性约束

完整性	解释
实体完整性	主属性不能为空(主键)
参照完整性	属性 F 是关系 R 的外键，是关系 S 的主键，F 在 R 中：或是空，或是 S 的主键值（R 和 S 可以是同一关系）什么时候是空呢？例子： 1 同关系学生（学号，姓名，性别，年龄，专业号，班长）班长是外键，班长没选出，此时就是空 2 不同关系 R 学生（学号，姓名，性别，专业号，年龄） S 专业（专业号，专业名）专业号是 R 的外键、S 的主键，未分配专业，此时就是空
用户定义完整性	用户自定义如某非主属性不能取空工龄＜年龄成绩的取值范围：0到100之间
升级：
https://blog.csdn.net/qq_40893824/article/details/106441068 2.1.2 小节

3.4 关系代数

3.4.1 运算符

关系运算符	σ	选择
	π	投影
	⋈	连接
	÷	除
集合运算符	∩	交
	∪	并
	-	差
	×	广义笛卡尔积
逻辑运算符	∧	与
	∨	或
	﹁	非
比较运算符	>、>= <、<= =	大于、大于等于小于、小于等于等于
比较运算符	<>	不等号

3.4.2 记号

记号	解释
R	关系模式 R(D₁，D₂，…，D_n)
关系	关系是 R
t ∈ R	表示 t 是 R 的 1 个元组
t[A_i]	表示元组 t 对应属性 A_i 的 1 个分量，A_i = Sname
--------------------------------------	--------------------------------------
A={A_i1, A_i2 … }	A_in 是 A_i 的一部分 A 是属性列或属性组 ( 一列 )
t[A] = (t[A_i1], t[A_i2], … )	表示元组 t 在 A 上各分量集合
$\overline{\text{A}}$	表示 {A₁, A₂ … } 去掉 {A_i1, A_i2 … }后所剩
------------------------------------
$\mathop{t_r t_s}\limits^{\frown}$	两元组连接 R 是 n 目关系(n 列)，S 是 m 目关系(m 列) t_r ∈ R，t_s ∈ S $\mathop{t_r t_s}\limits^{\frown}$ 是 (n+m) 列，先 R 后 S
Z_x	象集关系 R(属性 X, 属性 Z) Z_x 是属性 X 是值 x 时对应属性 Z 的集合
	Z_x1={Z₁, Z₂, Z₃} Z_x2={Z₂, Z₃} Z_x3={Z₁, Z₃}
---------------------------------------	---------------------------------------
R ∩ S 交	R、S 属性要相同，都是 n 列交后还是 n 列找出 R 中与 S 相同的部分
R∪S 并	R、S 属性要相同，都是 n 列合并后还是 n 列合并，自动去重
R - S 差	R、S 属性要相同，都是 n 列差后还是 n 列找出 R 中与 S 不同的部分
R × S 笛卡尔积就是两元组连接 $\mathop{t_r t_s}\limits^{\frown}$	R - n 列、k₁行，S - m 列、k₂行之后是 n + m 列、k₁ 乘以 k₂行

3.4.3 查询

3.4.3.1 选择 σ - 选行

查信息系全体学生
σ_{Sdept=‘is’}(Student)
查年龄小于 20 的学生
σ_Saget<20(Student)

3.4.3.2 投影 π - 选列、自动去重

查学生姓名、所在系
π_Sname,Sdept(Student)
查有哪些系
π_Sdept(Student)

3.4.3.3 连接 ( 也叫内连接 ) ⋈ - 先列后行

等值连接（属性值相同的元组，先列后行）
自然连接（自身连接，同属性列，先列后行）

悬浮元组

连接时，一表对应值存在，另一表对应值不存在，这元组会被丢弃，这个被丢弃的元组叫悬浮元组
若不想丢弃，就使用外连接！

3.4.3.4 外连接

3个外连接	解释
左外连接 `left join`=`left outer join`	以左表为参考，把左表复合条件的全选出来不用考虑右表对应是值是否存在
右外连接 `right join`=`right outer join`	以右表为参考，把右表复合条件的全选出来不用考虑左表对应是值是否存在
全外连接 mysql 暂不支持 union 可实现全外连接	以两表为参考，把两表复合条件的全选出来不用考虑对方表对应是值是否存在全外连接 = 左外连接 + 右外连接 + 去重

全外连接：

3.4.3.5 除运算 ÷

R(X, Y)、S(Y, Z)
R ÷ S 是 R 和 S 中 Y 相同的 R部分的集合

3.4.4 综合

上图 2 行对应 2 种方法，第 2 行方法是优化的，效率更高
代码
法1

select Sname from Student,SC,Course 
	where Course.Cpno='5' and 
    Course.Cno=SC.Cno and 
    SC.Sno=Student.Sno;

法2 - 更高效

select Sname from Student where Sno in(
	select Sno from SC where  Cno in(
		select Cno from Course where Cpno='5'));

查至少选修了2门课的学生学号、姓名

select Sno,Sname from Student where Sno in(
	select Sno from SC group by Sno having count(Cno)>2);

解析
重点是全部课程，用除法运算
除以 π_Cno(Course) 即可

3.4.5 缺点

存在无法表达的查询！
如最值 max、min