数据库原理概要复习

数据库原理概要复习资料，详细的总结了数据库概述。数据库设计，SQL语言基础，数据库编程，关系模式规范化，数据库安全管理。

数据库原理

• 数据库概述
  • 数据库发展阶段
    • 人工管理阶段
    • 文件系统阶段
    • 数据库系统阶段
      • 数据共享
      • 数据统一管理控制
      • 独立性高/强
      • 结构化集成
  • 数据库管理系统（DBMS）主要功能
    • 数据定义功能（创建）
    • 数据操作功能（操纵）
    • 事务与运行管理
    • 组织、管理和存储数据
    • 数据库的建立和维护功能
    • DBMS的其它功能，如数据转换
  • 数据库系统：由数据库、操作系统、数据库管理系统、支持数据库运行的硬件、应用系统、数据库管理员和终端用户组成。
  • 数据库系统的类型
    • 集中式数据库系统
    • CS/BS数据库系统
    • 分布式数据库系统
    • 并行式数据库系统
  • 模式结构
    • 概念
      • 数据模型：数据库中的数据是按照一个的结构组织起来的，这种结构就是数据模型。
      • 模式：数据库中全体数据的逻辑结构和特征的描述，模式的一个具体值成为模式的一个实例。
    • 三级模式结构（三级模式两级映像）
      • 外模式（又称子模式或用户模式）
        • 用于描述用户界面所需的数据库数据（部分数据）的局部逻辑结构和特征。
      • 模式/外模式映像
      • 模式（又称概念模式）
        • 是数据库中所有数据的整体逻辑结构和特征的描述。
      • 内模式/模式映像
      • 内模式
        • 描述数据库内部（存储）的表示形式，可具体描述数据复杂的物理结构和存储方式。 一个数据库只有一个内模式，内模式决定了数据库各数据库对象的具体编码、存储、排序方式，一个数据库有且只有一种内模式。
  • 概念数据模型
    • 实体相关概念
      • 实体：现实世界中可以相互区别的事务或活动。
      • 实体集：同类实体的集合。
      • 实体型：对同类实体共同特征的抽象定义。可以理解为实体的属性集。
      • 实体值：符合实体型定义的、某个实体的具体描述。理解为某个实体集中的某个具体人、事、物、联系或抽象概念。如7215教室。
    • 联系
      • 实体与实体之间的相互联系，一般表示一种活动或关系。
      • 一对一、一对多、多对多
    • 属性相关概念
      • 定义：描述实体或属性的一种特征（或特性），一个实体或联系一般需要多个属性来描述。
      • 属性组：一个或多个属性组成的属性组，通常是指多个属性的组合。
      • 主属性：候选键所包含的属性。
      • 非主属性：主属性以外的属性都是非主属性。
      • 域：属性的取值范围。
    • 表示方式
      • 实体
      • 属性
      • 联系
      • 联系类型
        • 一对一（1:1）
        • 一对多（1:n）
        • 多对多
          • m:n
          • m:n:p
• 数据库设计
  • 需求分析
  • 概念结构设计
    • ER图
      • 分ER图
      • 总ER图
      • ER图合并过程常见的冲突
        • 结构冲突
          • 同一对象在不同子ER图中有不同的抽象
        • 属性冲突
          • 属性值的类型、取值范围、值域不同或单位不同
        • 命名冲突
          • 一般发生于实体名、属性名、联系名
          • 类型
            • 同名异义：名字相同含义不同
            • 异名同义：含义相同名字不同
      • 联系类型
        • 一对一
          • 1:1
        • 一对多
          • 1:n
        • 多对多
          • m:n
          • m:n:p
  • 逻辑结构设计
    • ER图转关系模式
      • 实体
        • 转换为单独的关系模式
      • 联系
        • 一对一
          • 方法1： 将联系转换为单独的关系模式
          • 方法2： 将实体A的主键添加到实体B的属性中，并将其设为外键 或者 将实体B的主键添加到实体A的属性中，并将其设为外键 （假设实体A和实体B）
        • 一对多
          • 方法1：将联系转换为单独的关系模式
          • 方法2： 将实体B的主键添加到实体A的属性中，并将其设置为关系模式A的外键 （假设实体A与实体B之间是一对多联系）
        • 多对多
          • 唯一方法：将联系转换为单独的关系模式，在该关系模式中包括实体A和实体B的主键和联系本身的属性，其主键为实体A和实体B主键共同组成的符合主键。
    • 主键：一个关系模式只有一个主键
    • 外键
      • 当前关系模式中某个属性（组）引用的其它关系模式的主键或唯一约束，该属性（组）在当前关系模式中就是外键。
    • 候选键：一个关系模式可以有多个候选键
  • 物理结构设计
    • 索引
      • 按存储结构、列数、唯一性划分，只是三种对索引的不同分类方式，本身并不具有互斥性，比如我可以在student关系模式中的sno上创建一个单列的唯一的聚集索引。
      • 按存储结构
        • 聚集索引
          • 一个关系模式（一张表）只能设置一个聚集索引，因为数据存储时的排列顺序是根据聚集索引来排序的，同一条记录只能按照一种方式排列，不能同时按照多种方式排序。
        • 非聚集索引
      • 按列数
        • 单列索引
        • 多列索引
      • 按唯一性
        • 唯一索引
        • 非唯一索引
    • 加密
  • 数据库实施
    • 创建数据库
    • 创建数据表
      • 表结构
      • 主键
      • 外键
      • 约束
      • 索引
    • 插入数据
      • 录入数据
      • 导入数据
        • 将已有数据导入数据库，通常是指系统升级或更换服务商
  • 数据库运行与维护
• SQL语言基础
  • 基础动词
    • 数据定义
      • 数据库、数据表、视图、索引、存储过程、触发器、函数等数据库对象
      • Create
      • Alter
      • Drop
    • 数据查询
      • 对数据表中的数据执行查询操作。
      • Select
    • 数据操作
      • 对数据表中的数据进行添加、更新、删除操作。
      • Insert
      • Update
      • Delete
    • 数据控制
      • 对权限的授予、撤销和拒绝。
      • Grant
      • Revoke
      • Deny
  • 约束
    • 主键：Primary key
      • 列级约束
      • 表级约束
    • 外键：Foreign key …… References ……
      • 列级约束
      • 表级约束
    • 空：Null、Not Null
    • 默认值：Default
    • 唯一约束：Unique
      • 列级约束
      • 表级约束
    • Check约束
  • 数据插入
    • 单条记录（指定字段）
      • Insert into SC (sno,cno,grade) values ('S001','C001',90)
    • 单条记录（省略字段）
      • 此处values中的字段数量需与表结构对应的上，字段类型需与表结构对应的顺序相同，否则可能会报错。
      • Insert into SC values ('S001','C001',90)
    • 单条记录（部分字段）
      • Insert into SC (sno,cno) values ('S001','C001')
    • 插入多条记录
      • Insert into SC (sno,cno,grade) values ('S001','C001',90),('S002','C001',80),('S001','C002',70)
  • 数据更新
    • 带条件更新单字段
      • Update student set smobile='13500135000' where sname='张洁'
    • 不带条件更新单字段（慎用，会导致整张表的数据全部被更新）
      • Update student set smobile='13500135000'
    • 更新多个字段
      • Update student set smobile='13500135000',ssex='女' where sname='张洁'
    • 多条件更新
      • Update student set smobile='13500135000' where sname='张洁' and ssex='男'
  • 数据删除
    • 单条件删除
      • delete from student where sname='张洁'
    • 多条件删除
      • delete from student where sname='张洁' and sno='S010'
    • 无条件删除（慎用，整张表数据会全部被删除）
      • delete from student
  • 简单数据查询
    • 查询全部列
      • select * from student
    • 有条件查询全部列
      • select * from student where ssex='女'
    • 查询部分列
      • select sno,sname from student
    • 有条件查询部分列
      • select sno,sname from student where ssex='男'
    • 给字段取别名
      • select sno as 学号,sname as 姓名 from student where ssex='男'
      • select sno 学号,sname 姓名 from student where ssex='男'
        • as是可以省略的
      • select sno '学 号',sname from student where ssex='男'
        • 别名字符串有空格，此时必须带上''。
    • 消除重复值
      • select distinct sno from sc
    • where条件
      • Null判断
        • 空值判断
          • select * from sc where grade is null
        • 非空判断
          • select * from sc where grade is not null
      • 模糊匹配
        • 查询姓张的同学
          • select * from student where sname like '张%'
        • 查询姓名带钰的同学
          • select * from student where sname like '%钰%'
        • ……
    • 排序
      • 升序（默认）
        • select * from student order by sno
        • select * from student order by sno asc
      • 降序
        • select * from student order by sno desc
      • 多字段排序
        • select * from sc order by cno desc, sno asc
    • 分组
      • 查询每门课程的选课人数
        • select cno,count(*) 选课人数 from sc goup by cno
    • 分组后过滤
      • 查询选课人数小于30人课程号及选课人数
        • select cno,count(*) 选课人数 from sc goup by cno having count(*) < 30
    • 统计函数及其示例
      • select sno,count(*) 选课门数 from sc group by sno
      • select avg(grade) from sc where cno = 'C001'
      • select max(grade) from sc where cno = 'C001'
      • select min(grade) from sc where cno = 'C001'
      • select cno,avg(grade),min(grade),max(grade) from sc where cno = 'C001'
      • select sno,sum(grade) from sc where sno = 's001'
    • TOP
      • 查询C001课程前五名的学生学号
        • select top 5 sno from sc where cno='C001' order by grade
      • 查询C001课程并列前五名的学生学号
        • select top 5 with ties sno from sc where cno='C001' order by grade
      • 查询C001课程成绩在前5%的学生学号
        • select top 5 percent sno from sc where cno='C001' order by grade
  • 复杂数据查询
    • 嵌套查询
      • 查询成绩大于80的学生学号、姓名
        • select sno,sname from student where sno in (select sno from sc where grade > 80)
      • 查询选修了'数据库原理'课程的学生学号和姓名
        • select sno,sname from student where sno in (select sno from sc where cno = (select cno from course where cname='数据库原理'))
    • 存在性测试
      • 查询选修了C001课程的学生姓名
        • select sname from student where exists (select * from sc where sno=student.sno and cno='C001')
    • 等值连接查询
      • 查询C001课程的选修学生学号姓名
        • select s.sno,sname from student s,sc where s.sno=sc.sno and cno='C001'
        • select s.sno,sname from student as s join sc on sc.sno=s.sno where cno='C001'
      • 查询“数据库原理”的选修学生学号姓名
        • select s.sno,sname from student s,course c,sc where s.sno=sc.sno and c.cno=sc.cno and cname='数据库原理'
        • select s.sno,sname from student as s join sc on sc.sno=s.sno join course c on c.cno=sc.cno where cno='数据库原理'
    • 左连接
      • select * from course as c left join sc on c.cno=sc.cno
    • 右连接
      • select * from sc right join student s on s.sno=sc.sno
  • 视图
    • 概念
      • 是一个从一个或多个数据表中导出来的虚表，是一种并不真正存在的数据表。
      • 具有和数据表已知的查询功能。
      • 视图本身并不保存数据，数据还存储在原始表中。
      • 和数据表一样，可以插入、更新、删除数据，但是增删改操作受到一定限制。
    • 特点
      • 优点
        • 简单性：降低了数据查询复杂度。
        • 安全性：通过视图可以控制用户只能查询和修改所能见到的数据。
        • 逻辑独立性：视图可以使应用程序和数据库表在一定程度上独立。
      • 缺点
        • 性能
          • 对视图所有的操作都会转化为对基本表的操作。有时一个视图是由多张表进行复杂操作得到的，即使对该视图执行一个简单的查询，那么这个也需要经过这个复杂的多表查询才能得到结果，对性能是有一定的影响的。
        • 修改限制
          • 有些修改会无法完成，主要是有由于非空、外键等约束所限制。
    • 创建视图
      • Create VIEW v1 as select * from student
    • 修改视图
      • Alter VIEW v1 as select * from course
    • 删除视图
      • Delete View v1
  • 索引
    • 分类
      • 按存储结构
        • 聚集索引
          • 一个关系模式（一张表）只能设置一个聚集索引，因为数据存储时的排列顺序是根据聚集索引来排序的，同一条记录只能按照一种方式排列，不能同时按照多种方式排序。
        • 非聚集索引
      • 按列数
        • 单列索引
        • 多列索引
      • 按唯一性
        • 唯一索引
        • 非唯一索引
    • 特点
      • 优点
        • 提高查询速度
      • 缺点
        • 创建和维护索引需要耗费时间
        • 索引会占用更多的存储空间
        • 降低了插入、更新、删除的效率
    • 创建索引
      • 创建非聚集索引
        • Create NonClustered Index stu_idx On student(sno)
      • 创建非聚集的唯一的单列索引
        • Create NonClustered Unique Index stu_uqidx_sid On student(sid)
      • 创建唯一的多列索引
        • Create Unique Index stu_idx_sid On student(sno,sid)
    • 删除索引
      • 删除单个索引
        • Drop Index stu_idx
      • 删除多个索引
        • Drop Index stu_idx,stu_uqidx_sid,stu_idx_sid
• 数据库编程
  • T-SQL语言 P103
  • 存储过程
    • 概念
      • 存储过程是具有一定功能的一组SQL语句的集合，完成特定的功能。
      • 存储过程需要用户在需要时主动调用，否则不会执行。
    • 创建存储过程
      • 无参数存储过程
        • Create proc query_stu as select * from student
      • 带入参存储过程
        • Create proc stu_que_sno @sno char(7) as select * from student where sno=@sno
      • 带入参且带出参
        • Create proc stu_mobile_sno @sno char(7), @smobile char(11) output as select @smobile=smobile from student where sno=@sno
    • 调用存储过程
      • 无参数
        • exec query_stu
      • 只有入参
        • exec stu_que_sno 'S001'
      • 有入参有出参
        • Declare @smobile char(11); exec stu_mobile_sno 'S001',@smobile
    • 修改存储过程
      • 将创建时的Create改为Alter即可
    • 删除存储过程
      • Drop proc procname
  • 触发器
    • 概念
      • 是一种特殊的存储过程，无需用户显示调用，数据库管理系统会根据用户指定时机自动触发。
      • 前置型触发器
        • 当触发器执行时，会替换触发操作；触发操作实际未执行；通常需要在触发器中完成触发操作的动作；
      • 后置型触发器
        • 当触发操作执行完成后，执行触发器的代码。
    • 触发时机
      • Insert
      • Update
      • Delete
    • 两张临时表
      • Inserted
        • 当插入、修改数据时该表有数据，该表的结构和执行插入或修改操作的数据表的表结构完全相同，是临时的，只能在触发器中使用。 在触发器中使用时就像普通数据表一样。
      • Deleted
        • 当修改、删除数据时该表有数据，该表的结构和执行删除或修改操作的数据表的表结构完全相同，是临时的，只能在触发器中使用。 在触发器中使用时就像普通数据表一样。
    • 创建触发器
      • sc表上更新后置触发器
        • Create Trigger tri1 on sc after update as select * from deleted； select * from inserted;
      • course表上创建删除的前置触发器
        • Create Trigger tri_delete on course instead of delete as delete from sc where cno = (select cno from deleted); delete from course where cno = (select cno from deleted);
    • 修改触发器
      • 将创建时的Create改为Alter即可
    • 删除触发器
      • Drop trigger tri1
• 关系模式规范化
  • 函数依赖
    • 完全函数依赖
    • 部分函数依赖
    • 传递依赖
  • 第一范式
    • 每个属性都不可再分
  • 第二范式
    • 每个非主属性完全依赖主键（候选键）
  • 第三范式
    • 每个非主属性都不传递依赖于其候选键
  • BCNF范式
    • 每个属性都不传递依赖于其候选键
• 数据库安全管理
  • 事务
    • 事务是数据库执行的基本单位。 事务中的所有语句是一个整体，只能全部执行或全部不执行。
    • 基本特征（ACID）
      • 原子性
      • 一致性
      • 隔离性
      • 持久性
  • 并发操作带来的数据不一致
    • 丢失修改（或丢失更新） P138-139
    • 读脏数据 P139
    • 不可重复读 P139
  • 数据并发控制技术
    • 共享锁（读锁 S锁）
      • 假设有事务T1和T2，有数据A，若T1事务对A添加了共享锁，则T2事务不能再对A事务加排他锁，但可以加共享锁。
    • 排它锁（写锁 X锁）
      • 假设有事务T1和T2，有数据A，若T1事务对A添加了排它锁，则T2事务不能再对A事务加任何锁，即T2不能再对A加排它锁也不能加共享锁。
  • 用户权限管理
    • 用户管理
      • 登录用户
        • Create login
      • 数据库用户
        • create user
    • 角色管理
      • 服务器角色
      • 数据库角色
    • 权限管理
      • 赋予权限 Grant
      • 收回权限 Revoke
      • 拒绝权限 Deny
  • 数据库备份与恢复
    • 数据库备份
      • 将数据库中数据备份到存储介质的过程。
    • 备份类型
      • 完整备份
      • 差异备份
      • 事务日志备份
    • 备份策略
      • 完整备份
      • 完整备份+事务日志备份
      • 完整备份+差异备份+事务日志备份
    • 数据库恢复
      • 将数据库数据备份从存储介质恢复到数据库的过程。
    • 数据导入与导出
• 数据独立性
  • 逻辑独立性
  • 物理独立性