数据库复习大全
0、引言
早期，数据都是存放在文件系统当中的，但是回有数据冗余、访问困难、由于文件格式太多程序编写困难、修改数据不一致的问题（银行转账）和安全型问题等等。
0.1 数据模型是怎么形成的？
0.1.1、数据抽象
视图层：隐藏底层细节，也可以出于安全考虑隐藏信息问题
逻辑层：描述数据库存储什么数据以及数据之间的关系
物理层：描述数据如何存储
0.1.2、实例和模式
模式：数据库的总体设计，反应数据库的结构–设计了一个管理学生的数据库
实例：特定时刻存储在数据库中信息的集合–具体学生的信息
0.1.3、数据模型
数据模型：描述数据库四要素的工具：数据、数据联系、数据语义和一致性约束
对于关系型数据库来说，我们使用的是关系模型，含有：
- 实体-联系数据模型（E-R模型）：主要用于数据库设计
- 面向对象的数据模型：面向对象和对象关系
- 半结构化数据模型：XML、JSON（和一般纯文本相比，这类数据有一定的结构）
0.2、关系模型
0.2.1、举例
表：关系
列：属性/字段
行：元组/记录
0.2.2、SQL语言分类（DQL、DML、DDL、DCL）
- 数据查询语言（DQL）
用于对数据进行查询。主要有select from where - 数据操纵语言（DML）
用于访问和操作由适当数据模式组织起来的数据。主要有：insert、delete、update - 数据定义语言（DDL）
数据定义语言DDL用来创建数据库中的各种对象-----表、视图、索引、同义词、聚簇等。主要有create database/table/view 等。 - 数据控制语言（DCL）
数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。如：grant、rollback、commit等
0.2.3、如何在程序中使用SQL
- 允许向数据库发送SQL查询（C++中的ODBC和Java中的JDBC）
- 语言允许嵌入式SQL
0.2.4、事务管理
事务是数据库应用中完成单一逻辑功能的操作集合。由事务管理器实现，事务管理器包括恢复管理器（确保数据正常）和并发控制管理器（确保事务间不会相互影响）。
事务的ACID特性：
- 原子性（Atomicity）
- 一致性（Consistency）
- 隔离性（Lsolation）
- 持久性（Durability）
0.2.5、基本概念
- 数据（data）：描述事物的符号记录
- 数据库（DB）：大量数据集合
- 数据库管理系统（DBMS）：介于用户与操作系统直接的数据管理软件系统
- 数据库系统（DBS）：由数据库、数据库管理系统、应用程序】数据库管理员和用户组成
0.3、数据库设计
设计过程：
- 初始阶段：刻画用户数据需求，指定需求规格说明文档
- 概念设计：数据需求转换为概念设计
实体-联系（E-R模型）：将企业数据模型化为实体联系得集合，使用实体-联系图来表示
实体，对应现实生活就是具体的事或者物体，对应数据库就是一张表，使用一系列属性描述
联系：描述实体间关系的表
规范化理论：输入所有属性集，通过一些算法，生成一组关系表。避免没有必要的冗余，是判断是否是一个好的数据库设计的标准
- 逻辑设计：将高层概念模式转化为实现数据模型
- 物理设计：指定数据库的物理特性
0.4、存储管理
存储管理器是在数据库中存储的底层数据与应用，程序及向系统提交查询等的接口模块
主要功能：
- 与文件管理器交互
- 高效地存储、检索和更新数据
0.5、查询过程
- 分析和翻译
- 优化
- 评估执行
0.6、数据库体系结构
- 集中式
- 客户-服务器模式
- 并行（多处理器）模式
- 分布式
0.7、数据库管理员（DBA）
数据库设计、规划、协调的人员，最高特权用户
- 模式定义（常常会听到架构，不同数据库有具体的架构，不能说和模式就相同，我就不再讨论两者的关系了）
- 存储结构及存取方法定义
- 模式及物理组织的修改
- 数据访问权限
- 日常维护
0.8、总结
首先我们了解到了定义一个模型需要视图层、逻辑层和物理层三层结构，然后我们知道了数据库（之后再提及数据库就是SQL Server）就是一种模型，这种模型是关系模型，是相对于非关系模型说的，之后我们说了数据库中的一些操作，是我们能直接用到的，最后 我们大致了解了一些数据库的底层实现包含了什么。以上内容只是大概，下来详细讲解。
一、关系型数据库
1.0、一个贯穿全文的图
1.1、关系模型介绍
1.1.1、关系模型的组成
- 关系：一系列域（一组具有相同数据类型的值的集合）上的笛卡尔积的子集。因此可以用来指代表
- 列：字段/属性。每个列必须有不同的名字，每列所允许值的集合称为该属性的域，空值（null）是一切域的成员，但是在操作过程中会出现错误。
- 行：元组/记录。每行包括若干列，且是无序的。
注：即将讨论的数据库就是一种关系模型
1.1.2、数据库模式
- 数据库模式：数据库的逻辑设计。就是表头
- 数据库实例：给定时刻数据库的数据。表头下面的具体数据
1.1.3、码（key）
码对于数据库来说是一个十分重要的概念，用来区分不同实体的一个或多个属性。
- 超码：一个实体的一个或多个属性，他们的值唯一的决定每个实体
- 候选码：最小化超码，可以不止一个
- 主码：候选码之一，选择由数据库管理者决定
- 外码：A实体中的属性参照B实体的主码，A中参照的这个属性（集）叫做外码
1.2、SQL
1.2.1、数据操作语言（DML）
1.2.1.1、数据库的操作
1.2.1.2、表的操作
- 创建表、关系
--模板
create table 表名();
--创建政府表
create table department (
dept.name varchar(20),
building varchar(15),
budget numeric(12,2),
primary key (dept_name) );
- 删除表、关系
--模板
drop table 表名--删除关系表及其内容
delete from 表名--删除表的内容，保留表的结构
- 更新表、关系
--模板
alter table r add A D--将域是D的属性名A添加到关系r中，新值置为空值
alter table r drop A--表r中删除A属性（大多数DBMS不支持）
1.2.1.3、数据的操作
- 插入数据
--注意要和表中列名一一对应
--1、基本插入
insert into course(course_id,title,dept_name,credits)
values('CS-437','Database System','Comp. Sci.',4);
--2、含null插入
insert into student
values('3003','Green','Finance',null);
- 删除数据
--模板
delete from instructor
where dept_name='Finance';
- 更新数据
--模板
--1、基本更新
update instructor
set salary=200000
where name='Mozart';
--2、包含case的更新
update instructor
set salary = case
when salary <= 100000 then salary * 1.05
else salary * 1.03
end
1.2.2、数据定义语言（DDL）
- SQL Server数据定义：
int,numeric(p,d),money--精确数字
real,float--近似数字
date,time,datetime--日期和时间
char,varchar,text--字符串
nchar,nvarchar,ntext--Unicode字符串
binary,varbinary--二进制字符串
cursor,xml--其他类型
blob,clob--大对象类型，查询返回的是定位器而不是大对象本身
--用户自定义数据类型
create type Dollars as numeric(12,2) finall;
drop type--只能删除，不能alter
- 视图
--1、基本视图，只能是查询的第一条语句，其他语句不能创建视图
--模板
create view {} as <query expression>
--创建教师视图，不显示薪水
create view faculty as
select ID, name,dept_name
from instructor;
--2、物化视图：数据变，视图跟着变。
--3、可更新视图：
--from子句中只有一个关系；
--select子句中只有关系的属性名，没有任何关键字；
--除select后面的属性外，都可以取空值；
--查询中没有group by，having子句。
create view haistorv..instnuctors as
select *
from instructor
where dept..name= 'History ';
insert into history..instructors
values ('25566','Brown','Biology',100000);
- 创建索引：在关系的属性上所创建的一种数据结构，允许数据库系统高效的找到关系中那些在索引属性上给定的元组
create index studentlD_index.on student(ID);
select *
from student
where ID ='12345';
1.2.3、数据查询语言（DQL）
- 单、多关系查询
select distinct/all {},{}--distinct去除重复元组，all不去除重复元组
from {},{} as T--as表示重命名，常用来子连接as
where {} and {} or {} not {} in {} not in {} between {} and {}--也可以添加逻辑连词<,>,<>
order by {} desc--以什么属性进行排序，默认升序，asc升序，desc表示降序
group by {};--以什么属性分组，注意分组的对象必须是select后面有的属性，且不能是聚集函数使用的对象
having {}--必须跟到group by 后面才有意义
--2、group by --having--
select course..id, semester, year,sec_id, avg (tot_cred)
from student, takes
where student.lD = takes.lD and year = 2009
group by course_id, semester, year,sec_id.
having count(student.ID) >= 2;
- 连接
--1、自然连接（内连接）：SQL Server不支持nature join所以用内连接代替，内连接不保留未匹配元组的连接运算
select name,course_id
from instructor (inner) join teaches--inner可以省略
on instructor.ID=teaches.ID
--2、外连接（左外连接left outer join、右外连接right outer join、全外连接full outer join）
select *
from student left outer join takes on studrnt_ID=takes.ID
where course_id is null;
--3、等值连接：等值连接会保存重复元组，内连接不会
select *
from student，takes
where studrnt_ID=takes.ID;
- 字符串匹配
--1、SQL Server不区分大小写
--2、like操作符实现模式匹配：%匹配任意字串，_匹配任意一个字符
--3、用' '框上字符串
--找到姓名中包含dar的教师
select name from instructor where name like '%dar%'
- 集合间运算
--1、并运算（union）:自动去除重复元组，保留用union all
(select course_id from section where semester ='Fall' and year = 2009)
union
(select course_id from section where semester = 'Spring' andyear = 2010);
--2、交运算（intersect）:自动去除重复元组，保留用intersect all（不支持）
(select course_id from section where semester ='Fall' and year = 2009)
intersect
(select course_id from section where semester = 'Spring' andyear = 2010);
--3、差运算（except）：自动去除重复元组，保留用except all（不支持）
(select course_id from section where semester ='Fall' and year = 2009)
except
(select course_id from section where semester = 'Spring' andyear = 2010);
- 空值（null）
1、任何值与null作用结果都是unkown
2、在函数中，除了count将null视为0，其他函数主动忽略null
| A | Operate | B | Result |
|---|---|---|---|
| null | =、<>、>、< | null | unknown |
| unknown | OR | true | true |
| unknown | OR | false | unknown |
| unknown | OR | unknown | unknown |
| unknown | AND | true | true |
| unknown | AND | false | false |
| unknown | AND | unknown | unknown |
| - | NOT | unknown | unknown |
- 集合成员资格
这个和上一个不要搞混了，这个是判断集合中元组是否存在在集合中的，上一个是两个集合间的运算
1、存在测试（in、not in）
--找出2009年秋季和2010年春季同时开课的所有课程
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);
2、比较（some(any),all）
--some只要存在一个就可以
select name
from instructor
where salary > some (select salary
from instructor
where dept_name='Biology');
--all必须所有都满足
select name
from instructor
where salary > all (select salary
from instructor
where dept_name= 'Biology');
3、空关系测试（exists、not extists）
--使用了外层的查询名称S，所以称为相关子查询
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= L.course_id );
如何理解exists是一个难点，像这个例子中一样，先找到满足2009，秋天的课程，再带入exists后面的子查询中进行判断，如果存在返回true，不存在反之。
1、可以找到满足外层条件的有这三条信息
2、满足内层子查询的有这几条信息，与外层那些信息的课程名进行对比
3、找到满足条件的为以下信息，则为最终的结果，结果为CS-101
- 子查询
1、嵌套子查询
在select后面，同理
在where后面，见6
2、标量子查询：子查询结果只返回包含单个属性的单个元组
select dept..name,
(select count(*)
from instructor
where department.dept_name = instructor.dept_name)
as num_instructors
from department;
3、相关子查询，不相关子查询
- with子句
--注意临时表的作用范围，在单个select、insert、update、delete语句中,也就是只在紧跟后面的语句中有效
--1、创建一个临时表
with max_budget (value) as--定义临时关系max..budget,
(select max(budget)--注意在SQL Server中此处列名必须要写出来
from department)
select dept_name.
from department,max_budget.
where department.budget = max_budget
--2、创建多个临时表
with dept .total (dept..name, value) as
(select dept_name, sum(salary)
from instructor
group by dept_name),--逗号不能省
dept_total_avg(value) as--as不能省
(select avg(value)
from dept_total)
1.2.4、数据控制语言（DCL）
- 数据库和架构
描述方法：目录/模式/关系、视图
- 授权
--1、授权模板
grant <权限列表select、insert、update、delete、all>
on <关系名或视图名>
to <用户/角色列表>
[with grant option]
--2、权限收回
revoke [grant option for] <权限列表>
on <关系名或视图名> from <用户/角色列表>
[cascade];
--授权例子
grant select on instructor to U1,U2;
grant reference (dept_name) on department to U1;
--收回权限
revoke grant option for select
on department from U1,U2 cascade;
- 角色
引入角色的原因：权限的级联收回不合适；权限可以授予角色；角色可以授予用户和角色
级别：服务器级别；数据库级别；应用程序级别
--创建角色：create role
--修改角色：alter role，更新用户角色名
--删除角色：drop role
--当处于U1角色时，啥都能干，比如：
insert into instructor(Id,name) values('100','Tom');
--现在创建一个角色可以插入数据
create role instuctor_role;
--将U1角色加入角色中
alter role instructor_role add member U1;
--授权
grant insert on instructor to instructor_role;
1.2.5、约束
1.2.5.1、单个关系上的约束
- 主码约束：primary key(–)
- 外码约束：foreign ket(–) references r
- 非空约束：not null
- 唯一约束：unique
- 检查约束：check§
- 默认值：default
--写法1：主码另写
create table instructor (
ID char(5),
name varchar(20) not null,
dept_name varchar(20),
salary numeric(8,2),
primary key (ID),
foreign key (dept_name) references department)
--写法2：主码合在一起
create table instructor (
ID char(5) primary key,
name varchar(20) not null,
dept_name varchar(20),
salary numeric(8,2) default 0,
foreign key (dept_name) references department)
--unique
alter table instructor add constraint unique_name unique (name);
--check
create table section (
course_id varchar (8),
sec_id varchar (8),
semester varchar (6),
year numeric (4,0),
building varchar (15),
room_number varchar (7),
time_slot .id. varchar (4),
primary key (course_id,sec_id, semester, year),
check (semester in ('Fall', 'Winter' , 'Spring' , 'Summer')));
1.2.5.2、多个关系上的约束
- 完整性约束：是防止对数据的恶意破坏，保证授权用户对数据库所作的修改不会破坏数据的一致性
- 参照完整性：保证一个关系中给定属性集上的取值在另一关系的特定属性集的取值中出现
--1、参照完整性中的级联操作
--on delete cascade 删除父表，引用表也被删除
--on delete no action 回滚父表行删除操作
--on delete set null 父表被删，此处变成null
--on delete set default 父表被删，此处变为默认值努null
create table course (
dept_name varchar(20),
foreign key (dept_name) references department
on delete cascade
on update cascade,
};
1.2.6、函数
- 聚集函数
avg()--平均值
min()--最小值
max()--最大值
sum()--总和
count()--计数
--1、平均值
select avg (salary) as avg_salary
from instructor
where dept_name= 'Comp. Sci.'";
--2、计数：count(*)的时候，不能使用distinct关键字
select count(*)
from course;
1.3、高级SQL
1.3.1、使用程序设计语言访问数据库
因为SQL没有提供程序设计语言那样的表达能力，所以与程序设计语言结合起来，大致有两种方法
1、动态SQL：通用设计语言通过函数或者方法连接服务器，允许程序运行时构建和提交查询
2、嵌入式SQl：嵌入式SQL语句必须在编译时就全部确定，并交给预处理器，预处理器提交SQL语句到数据库系统进行预编译和优化，然后把程序中的SQL语句替换成相应的代码，最后调用程序设计语言的编译器进行编译
- 动态SQL
API：应用程序编程接口，可以让一个程序和数据库服务器进行交互 - 嵌入式SQL
1.3.2、函数和过程
- 函数
--1、基本函数
create function dbo.dept_count(@dept_name varchar(20))--dept_name相当于形参
returns int
as
begin--函数主体开始
declare @d_count int;
select @d_count=count(*)
from instructor
where instructor.dept_name=@dept_name
return @d_count;
End
--2、表函数：返回表
create function instructors_of(@dept_name varchar(20))
returns table
as
return(select ID,name,dept_name,salary
from instructor
where dept_name=@dept_name)
- 过程：同一架构下不允许多个同名过程
--上面第一个函数可以写成
create procedure dept_count_proc (@dept_name varchar(20),@d_count int out)
as
begin
select @d_count=count(*)
from instructor
where dept_name=@dept_name
end
--执行存储过程
declare @my_count int;
execute dept_count_proc 'Comp. Sci. ',@my_count out;
select @my_count;
- 异常
THROW 51000, 'The record does not exist.',1;USE tempdb;
GO
CREATE TABLE dbo.TestR.ethrow ( ID INT PRIMARY KEY );
BEGIN TRY
INSERT dbo.TestR.ethrow(ID) VALUES(1);
-- Force error 2627, Violation of PRIMARY KEY constraint to be raised.
INSERT dbo.TestR.ethrow(ID) VALUES(1);
END TRY
BEGIN CATCH
PRINT 'In catch block.;
THROW;
END CATCH;
1.3.3、触发器
触发器是一条语句，当对数据库做修改时，它自动被系统执行
DML触发器：for、after、after后指定操作执行、instead of、deleted、inserted表，触发器执行
DDL触发器：create、alter、drop语句
登陆触发器：响应建立用户会话时触发
create trigger timeslot_check1 on section after insert
as
begin
declare @new_val varchar(4) ;
select @new_val = time_slot_id from inserted;
if(@new_val not in (select time_slot_id from time_slot))
rollback transaction;
end
--验证，以下语句会触发触发器
insert into section(course_id ,sec_id, semester，year,time_slot_id)
values(' CS-101’, '3', 'Spring’ , 2020,'M’);
alter trigger--修改触发器
drop trigger--删除触发器
enable trigger/disable trigger--将触发器设置为有效、无效
1.3.4、存储过程
存储过程 (Stored Procedure) 是在大型数据库系统中 , 一组为了完成特定功能的 SQL 语句集 , 存储在数据库中 , 经过第一次编译后再次调用不需要再次编译 , 用户通过指定存储过程的名字并给出参数 (如果该存储过程带有参数) 来执行它 , 存储过程是数据库中的一个重要对象 ; 存储过程中可以包含 逻辑控制语句 和 数据操纵语句 , 它可以接受参数 , 输出参数 , 返回单个或多个结果集以及返回值 .
好处：
减少了服务器、客户端网络流量
更强的安全性
代码复用
容易维护
性能较高
3335
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
