数据库 – kingflag' blog

本文最后更新于 7 天前，其中的信息可能已经有所发展或是发生改变。

本篇写于2025年4月贵师大调剂复试前，速成回顾了数据库知识，应试所用，理论知识较多。

准备仓促，所以sql很多代码大小写随意。
数据库重点是关系代数、SQL语句以及优化。

数据库概要

数据管理技术的发展

简单了解即可，重要程度为0。
数据管理技术经历了三个主要发展阶段：

人工管理阶段（20世纪50年代前）：
- 数据不保存在计算机内
- 没有专用的数据管理软件
- 只有程序概念，没有文件概念
- 数据面向程序，高度依赖
文件系统阶段（20世纪50-60年代）：
- 数据以文件形式长期保存
- 逻辑结构与物理结构有了区分
- 文件组织多样化
- 数据面向应用
- 操作以记录为单位
数据库系统阶段（20世纪60年代后期至今）：
- 采用数据模型表示复杂数据结构
- 具有较高的数据独立性
- 提供方便的用户接口
- 提供数据控制功能
- 操作以数据项为单位

数据描述

数据描述涉及三个世界：

现实世界：客观存在的世界
信息世界（概念世界）：
- 实体：客观存在并可区别的事物
- 属性：实体具有的特性
- 码：唯一标识实体的属性集
- 实体型：同类实体的抽象描述
- 实体集：同类实体的集合
- 联系：实体内部或实体间的关联
  物理世界：数据在计算机中的表示

数据模型

重点：层次模型

数据模型分类

概念模型（信息模型）：
- 按用户观点建模
- 用于数据库设计
- 常用E-R图表示
逻辑模型：
- 按计算机系统观点建模
- 用于DBMS实现
- 包括层次模型、网状模型、关系模型等
物理模型：数据最底层的抽象

数据模型三要素

数据结构：描述系统的静态特性
数据操作：描述系统的动态特性
完整性约束：数据及其联系的制约规则

常用逻辑模型

层次模型：树形结构
网状模型：有向图结构
关系模型：二维表格
面向对象模型：对象和类

数据库的体系结构

三级模式结构

外模式（子模式/用户模式）：
- 用户的数据视图
- 与某一应用相关的局部数据
- 一个数据库可有多个外模式
模式（概念模式/逻辑模式）：
- 全体数据的逻辑结构描述
- 所有用户的公共数据视图
- 一个数据库只有一个模式
内模式（存储模式）：
- 数据的物理存储描述
- 一个数据库只有一个内模式

两级映像

外模式/模式映像：
- 保证逻辑数据独立性
- 模式改变时外模式可保持不变
模式/内模式映像：
- 保证物理数据独立性
- 内模式改变时模式可保持不变

1.5 数据库管理系统(DBMS)

DBMS主要功能

数据定义功能：提供DDL定义数据库结构
数据操纵功能：提供DML进行数据操作
数据库运行管理：安全性、完整性、并发控制
数据库建立维护：初始输入、转储、恢复等
数据组织存储管理：提高存储和存取效率

DBMS工作模式

接受应用程序的数据请求
转换为对操作系统的调用
从数据库获取所需数据
将结果返回应用程序

SQl语句

常用数据类型

整数型：

tinyint 1字节
smallint 2字节
mediumint 3字节
int 4字节
bigint 8字节
小数：
float //单精度
double//双精度
decimal（5，2）//总位数5，小数点后2
字符串：
char（10）//可以存10个字符的定长字符串，1个中文字代表一个字符
varchar（250）//可变字符串，数字代表最大长度
text//长文本数据，最大65535
longtext//4g
enum（‘男’，‘女’）//枚举类型
时间：
year
data
time
datatime
布尔型
boolean
bit

常用约束项

primary key//主键
not null//非空
check//检查
foreign key (<列名> )references <表名>(<列名>)//外键
unique//唯一性
default//默认值

表格操作

创建表格

create table 表名 (
    列1 数据类型 [约束],
    列2 数据类型 [约束],
    ...
    [primary key (列名)],
    [foreign key (列名) references 其他表(列名)]
);

实例：

CREATE TABLE 学生 (
    student_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    age INT CHECK (age >= 18 AND age <= 60),
    department_id INT REFERENCES 院系(department_id),
    enrollment_date DATE DEFAULT CURRENT_DATE
);

删除表格

drop table 表名;

实例：

drop table students;

修改表格

增

alter table 表名 
add column 列名 数据类型 约束项;

实例：

alter table  students
add column 性别 enum('男','女');

删

alter table 表名
drop column 列名;

实例：

alter table students
drop column 性别;

修改表名

-- 单表重命名
RENAME TABLE 旧表名 TO 新表名;

-- 多表重命名
RENAME TABLE 旧表1 TO 新表1, 旧表2 TO 新表2;

改

以下四个函数都可以修改列，但是作用范围不一样。

函数名	作用
modify column	修改列的数据类型和约束，不能修改列名
alter column	修改列的默认值
rename column	重命名列
change column	包括以上功能，不能删除表级约束
drop 约束	取消约束

modify

alter table 表名
modify column 列名 数据类型 约束项；

alter table students
modify column 课程 int ，
add constraint 课程fk foreign key 课程id references  课程(课程id)；

alter

alter table 表名
alter column 列名{set default 值｜drop default};

实例：

alter table students
alter column 性别 set default '男' ；

alter table students
alter column 性别 drop default；

rename

alter table 表名
rename column 旧列名 to 新列名；

实例：

alter table students
rename column 性别 to sex;

change

alter table 表名
change column 旧列名 新列名 数据类型 约束项;

实例：

alter tabke students
change column 性别 sex enum('男','女') not null;

drop

alter table 表名
drop {primary key|index 唯一性约束名|foreign key 外键约束名};

唯一性约束名默认和列名相同。外键约束默认名：表名_ibfk_n
实例：

alter table students
drop primary key;

alter table students
drop index 唯一性约束名;

插入数据（增）

insert into 表名（列名，列名） values(值1-1,值1-2),(值2-1,值2-2)；

实例：

insert into students(student_id,name) values(1,毛民)(2,toby)；

删除数据（删）

delete 
from 表名
where 条件语句;

实例：

delete
from students
where student_id = 1;

修改数据（改）

update 表名
set 字段1 = 值1，字段2 = 值2
where 条件语句;

实例：

update students
set sex = '女'
where name = 'toby';

查询数据（查，重点）

条件查询

select 列名（*：占位符，代表所有列）
from 表名
where 条件判断语句;

实例：

select name
from students
where age=18;

模糊查询

select 列名
from 表名
where 字段 like 模糊值;

实例：

select *
from students
where name like 'to';

集合查询

select 列名
from 表名
where 字段 in 集合;

实例：

select *
from students
where name in ('toby','毛民');

排序子句

子句间可以一起使用，where是筛选的，order by是排序用的，两个一起用就可以筛选后排序。只能使用要输出的列名进行排序。DESC代表倒序，ASC正序，默认ASC

select 列名
from 表名
order by <FROM前面的其中若干个字段> <DESC>;

实例：

select *
from students
order by name DESC;

分组子句

常和聚合函数一起使用。

select 分组字段，聚合函数
from 表名
groud by 分组字段;

实例：

select 课程,AVG(成绩)
from students
groud by 课程;

常见聚合函数

AVG	求平均
COUNT	计数
SUM	求和
MAX	最大值
MIN	最小值

HAVING子句

组级过滤
查询子句执行顺序：FROM → WHERE（行级过滤）→ GROUP BY（分组）→ HAVING（组级过滤）→ SELECT → ORDER BY
过滤语句，作用和语法和where类似，优先级不同。

多表查询（难点）

内连接

select *
from 表1 inner join 表2 
on 表1.字段 = 表2.字段；

保留表1字段和表2字段中都存在的值。

外连接

select *
from 表1 left join 表2 
on 表1.字段 = 表2.字段；

会保留左边所有列，包括右边没数据的，没有数据的字段设空。右连接和左连接相似。
全连接会合并左右表都有的行，没有的也会保留，空字段设null

交叉连接

SELECT * FROM 表A CROSS JOIN 表B;
-- 或
SELECT * FROM 表A, 表B;  -- 无WHERE条件时

聚合查询

使用聚合函数参与查询

select 聚合函数（字段1、字段2……）
as 新字段名
from 表名
where 条件语句；

实例：

//假设有一张成绩表
select SUM（操作系统、数据库）
as 总分
from 成绩表
where 总分>150;

嵌套查询

select 列名
from 表名
where 列名 判断符 （子查询）

实例：

select *
from students
where 学号 = （select 学号 from score where 数据库=98）；

权限

赋权

grant insert,delect,update,select 
on 对象
to 用户名 
with grant option

grant insert on students
to root
with grant option

with grant option是可以让用户给其他人赋予他所有的权力的意思。
publish公众的，代表所有人

剥权

revoke on 对象
from 用户

REVOKE GRANT OPTION FOR SELECT ON student FROM zhang;

关系数据库的模式设计

关系模式的设计问题

数据冗余问题：同一数据多次出现
数据更新问题：一处信息更新，与他关联的数据没有同步更新
数据插入问题：因为数据完整性原因无法插入
数据删除问题：删除一个数据导致其他不应该被删除的数据被删除。

函数依赖

定义：设X和Y是关系R的属性子集，若对于R的任意两个元组，当X值相同时Y值也相同，则称X函数决定Y，记作X→Y。

平凡函数依赖：Y是X的子集
非平凡函数依赖：Y不是X的子集
完全函数依赖：X→Y且X的任何真子集X’都不决定Y
部分函数依赖：X→Y但X的某个真子集X’也能决定Y
传递函数依赖：X→Y，Y→Z且Y不决定X，则X→Z
Armstrong公理：函数依赖的推理规则
自反律：若Y⊆X，则X→Y
增广律：若X→Y，则XZ→YZ
传递律：若X→Y且Y→Z，则X→Z

关系模式的分解特性

关系模式分解是将一个关系模式分解为多个子模式的过程，需满足以下特性：

无损连接分解：分解后的关系通过自然连接能恢复原关系
- 测试方法：若(U1∩U2)→(U1-U2)或(U1∩U2)→(U2-U1)成立，则分解是无损的
保持函数依赖分解：分解后的关系模式集合能保持原函数依赖集
- 测试方法：检查函数依赖集F是否被投影后的函数依赖集覆盖
分解算法：
- 3NF分解算法：保证既保持函数依赖又具有无损连接性
- BCNF分解算法：保证无损连接但不一定能保持函数依赖

关系模式的范式（重点）

第一范式：满足属性不可再分
第二范式：满足第一范式的基础上再满足非主属性完全依赖候选键
第三范式：满足第二范式的基础上消除除传递依赖
BCNF：在第三范式基础上所有属性不传递依赖候选键
第四范式：在BCNF基础上消除多值依赖。

数据库设计

数据库设计概述

需满足以下规则：

确保每个表描述单一事物
每个表都有主键
字段不可分割
避免字段冗余（外键除外）
用外键保证表间关联
六个阶段：
规划
需求分析
概念设计
逻辑设计
物理设计
实施与运维

规划

需求分析

需求分析是数据库设计的第一步，也是最耗时、最重要的环节
。主要任务包括：

调查用户业务活动和数据使用情况
确定数据种类、范围、数量及交流情况
明确用户对系统的使用要求和约束条件
形成用户需求规约
常用方法：结构化分析(SA)方法，使用数据流图(DFD)和数据字典(DD)表示分析结果。

概念设计

概念设计是整个数据库设计的关键，通过ER模型(实体-联系模型)表示现实世界的抽象概念。

ER模型三要素：

实体：现实世界中可区分的对象（如学生、课程）
属性：描述实体的特性（如学号、姓名）
联系：实体间的关联（如”选修”、”教授”）

ER模型设计步骤：

局部ER模型设计：
- 选择局部应用（基于中层数据流图）
- 识别实体和属性
- 定义实体间联系
- 分配属性
整体ER模型设计：
- 合并局部ER图
- 消除冲突（属性冲突、命名冲突、结构冲突）
- 消除冗余
ER模型验证：
- 确保内部完整性和一致性
- 准确反映所有局部ER图结构
- 满足所有用户需求

逻辑设计

逻辑设计是将概念模型转换为特定DBMS支持的逻辑数据模型（如关系模型）。

转换规则：

实体转换：每个实体转换为一个关系模式，属性成为关系属性
学生(学号, 姓名, 性别, 年龄)
联系转换：
- 1:1联系：在任一实体关系中加入另一实体的主键
  班级(班级号, 班级名, 班主任工号)
- 1:N联系：在N端实体关系中加入1端实体的主键
  学生(学号, 姓名, 班级号)
- M:N联系：将联系转换为独立关系，包含两端实体的主键（联合主键）和联系自身属性
  选修(学号, 课程号, 成绩)
- 三元联系：将联系转换为独立关系，包含所有相关实体的主键
  进货(商店号, 商品号, 仓库号, 日期, 数量)

物理设计

为逻辑数据模型选取最适合应用环境的物理结构：

确定存储结构和存取方法
设计索引提高查询效率
考虑数据备份和恢复策略
选择合适文件类型和存放次序

实施

运用DBMS提供的语言：

创建数据库和表
定义索引和视图
组织数据入库
编制应用程序
进行试运行

数据库运行与维护

数据库系统正式运行后的长期工作：

系统评价、调整和修改
确保数据安全性和完整性
优化数据库性能
必要时进行重组织或重构造