前端面试题

计算机三级

生命周期模型

项目规划

  • 系统规划与定义

  • 可行性分析

    • 经济

    • 操作：人员、软硬件、工作环境等

    • 技术

    • 开发方案可行性

  • 项目规划

  • 需求分析

    • 数据需求分析

    • 性能需求分析

  • 系统设计

    • 概念设计：数据库概念模型设计、系统总体设计

    • 系统总体设计：DBAS 体系结构设计，硬件平台选型和配置，应用软件结构设计，对业务规则进行初步设计，对关键技术进行选型和初步设计

    • 逻辑设计：数据库逻辑结构设计、数据库事务概要设计、应用程序概要设计。

    • 物理设计：数据库物理结构设计、数据库事务详细设计、应用程序详细设计。

  • 实现与部署

    建立数据库结构，数据加载，事务和应用程序的编码与测试，系统集成、测试与试运行，系统部署

  • 运行与维护

数据库运行维护工作主要包括数据库的转储和恢复；数据库的安全性和完整性控制；数据库性能的监控分析和改进；数据库的重组和重构。

需求分析

过程：标识问题，建立需求模型，描述需求，确认需求

需求获取的方法

1.面谈

2.实地观察

3.问卷调查

4.查阅资料

需求

功能需求

性能需求

性能指标

  • 数据操作响应时间

  • 系统(数据)吞吐量：系统单位时间内所完成的事务或查询的数量，单位是 TPS。

  • 允许并发访问的最大用户数

  • 每 TPS 代价值，用于衡量系统性价比的指标。

提高吞吐量：大事务分解为小事务，避免死锁，降低隔离性等级

数据需求

数据模型三要素：

  • 数据结构

  • 数据操作

  • 数据完整性约束

其他需求

  • 存储需求：存储量

  • 安全需求

建模方法

IDEF0

两种元素：

  • 矩形框（活动）

  • 箭头

参考解析：组成IDEF0图的基本元素是矩形框和箭头, 如下图所示, 矩形框内的动词短语描述功能 活动的名称, 活动的编号按要求写在矩形框右下角指定的位置。

左边的输入箭头表示完成活动需要的数据; 矩形框上方的控制箭头描述了影响这个活动执行的事件 或约束条件; 右边的输出箭头说明由此活动产生的结果集信息; 下方进入的机制箭头表示实施该活 动的物理手段或完成活动所需要的资源（计算机系统、人或组织）。

例：

UML

UML的五种视图：结构、实现、行为、环境和用例视图

13种图( UML2.0) :静态结构图及行为图两类。一类是结构图，主要用于对系统静态结构建模，包括类图、对象图、复合结构图、包图、组件图、部署图；另一类是行为主要用于对系统的动态行为建模，包括用例图、交互图(顺序图、通信图、交互概述图、时间图)， 状态图和活动图。包图属于结构图，不是行为图。

业务流程与活动图

  • 活动图主要描述系统、用例和程序模块中逻辑流程的执行次序,并行次序。

  • 最适合描述系统或子系统的工作流程。

  • 活动图用于低层次程序模块的作用类似于流程图，但活动图可以描述并行操作，而

  • 流程图只能描述串行操作。

  • 一张活动图中有且只能有一个起点，可有多个结束点。

系统需求与用例图

  • 系统需求：用户心中的真正期望。

  • 用例模型是把满足用户需求的所用功能表示出来的工具。

  • 用例模型由用例、角色和系统三部分组成。

  • 系统：各种用例的“黑匣子”

  • 角色：与系统交互的人或其他实体

  • 用例：完整功能所有动作(一次操作)

角色之间的关系：

  • 通用化关系：指把某些角色的行为抽取出来作为通用行为,这些通用行为构成超类。

用例与角色之间的关系：

  • 连接关系(关联，通信关联)：表明那种角色能与该用例通信,是双向的一对一关系。

用例之间的关系：

  • 扩展：一用例增加新内容成为另一个用例。

  • 包含(使用) ：一个用例使用另一个用例。

  • 关联(组合) ：把相关用例打成包当作整体

系统结构与类图

系统内部结构一般分为静态结构和动态结构。

在UML中,用类图来描述系统静态结构，用顺序图和通信图来表示系统动态结构。

类图主要表达的是问题领域的概念模型。

类图由类名、属性及操作组成。

**类与类之间的关系：**关联(聚集(共享聚集，组成或组合))(或叫泛化)，依赖，精化(或叫实现)

系统结构与顺序图

针对每一个特定用例，如何利用类图规范的对象来完成用例交付的任务,必须要利用顺序图

顺序图主要用于描述系统内对象之间的消息发送和接收序列。

顺序图中所有的元素，都必须在类图中存在。

系统结构与通信图

通信图是交互图的一种，也称为协作图。

通信图显示对象间组织交互关系和链接。不侧重交互顺序，用序列号来确定消息及其并发线程的顺序。

顺序图强调时间，通信图强调空间。

微观设计与对象图

系统设计中，需要考虑细节部分。UML中，对于细节方面的内容可用对象图、状态机图及时间图来表达、分析和描述某个特定状况下系统的运作情况。

对象图是类图的实例，描述特定时间中所有对象在系统中的结构,是一个快照。

微观设计与状态机图

状态图用来描述有关事件或对象的状态转移。

状态图只能有一个起始状态 ,可有多个结束状态。

状态间的转移由事件驱动。

微观设计与时间图

当状态的转换由时间因素决定时，使用时间图来描述状态的变化。

描述时间驱动的状态转换，即当状态维持多少时间后转移。

时间图中，整个矩形框就是一个生命线。

宏观设计与包图

宏观设计指将涉及的焦点放在研究比较大范围中的元素之间的联系，如包、命名空间、子系统等。

一个良好的命名空间，便于开发人员理解， 并使得各个命名空间之间能够松耦合,而命名空间内则可满足高内聚的要求。

包图表示系统中不同包、命名空间或不同项目间的彼此关系。也就是逻辑层次上与实体层次上的关联性。

宏观设计与交互概述图

是将活动图和顺序图嫁接在一起的图。

以活动图为基础，在控制流间连接交互图，从而将所有交互图关系呈现出来。

交互概述图可以把不同的交互图结合在同一张图中来表达。

宏观设计与复合结构图

外部系统的整合关系着项目的成败。

在项目开始前，最好将待开发的系统与外部系统的关系做一个初步的定义。

复合结构图适用于系统间的沟通接口，,适合做构架师在初期阶段评估系统复杂度的工具，也可以是系统维护的参考图。

系统实现与组件图

组件图用来表示系统的静态实现视图。

用来展现一组组件间的组织和依赖，用于对源代码、可执行的发布、物理数据库等的系统建模。

组件是逻辑设计中定义的概念和功能在物理构架中的实现。

系统实现与部署图

部署图又叫配置图，描述系统中硬件和软件的物理配置情况与系统体系结构。

部署图说明实体组件，如可执行程序，将如何部署到实际的计算机中。

部署图要在项目进行集成测试前提供。

DFD

四种基本元素：

  • 数据流：箭头。

  • 处理：矩形框。数据逻辑处理，数据变换

  • 数据存储：圆角矩形框。

  • 外部项：圆角矩形框或平行四边形。数据来源和去路。（系统外部）

数据流图

  • 一个处理至少一个输入流和输出流

  • 一个存储必须有流入和流出的数据

  • 一个数据流至少一段是处理框

数据库结构设计

概念设计

数据建模方式

ER

E-R ：Entity Relationship Diagrams（实体关系模型） E-R图：实体-联系图 E-R图可以认为是关系模型的雏形，每个实体是一张表，实体与实体之间的关系可以合并到其中一个实体中，也可以是另外一张表，关系表和相关的实体表是通过主键或外键来联系的。

E-R图3个要素：实体，属性，关系。

  • 实体：用矩形表示，矩形框内写明实体名。

  • 属性：用椭圆形表示,椭圆内写明属性名称，并用连线与实体连接起来。如果属性较多,为使图形更加简明,有时也将实体与其相应的属性另外单独用列表表示。

  • 联系：用菱形表示，菱形框内写明联系名，并用连线分别与有关实体连接起来，同时在连线旁标上联系的类型。

连线：实体和属性之间、实体与联系之间、联系与属性之间用直线连接，并在直线上标注联系的类型。（注意：对于1：1的联系，要在两个实体连线方向各写1，1：n关系的，要在一的方向写1，多的方向写N；对于N:M关系的，则要在两个实体连线方向各写N,M）

IDEF1X

  • 实体集：

    • 独立实体集：每个实例都能够被唯一标识而不决定于它与其它实体集的联系 –> 矩形框

    • 从属实体集：实例依赖于其它实体集的实例 –> 圆角矩形框

  • 联系：

    • 标定型联系：由两个父实体集共同确定（外码有两个，并取决于不同的表） –> 实线连接单点，1 端是起点，n 端是终点(用实心圆点表示)

    • 非标定型联系：无需了解父端的实例 –> 虚线连接单点

    • 分类联系：是两个或多个实体集之间的联系，且在这些实体集中存在一个一般实体集，它的每一个实例都恰好与一个且仅一个分类实体集的一个实例相联系。例如，本科生和学生就属于分类联系。 –> 单点实线两杠

    • 不确定联系：一个非确定联系又称为多对多联系，这种联系关联的两个实体集之间，任一实体集的一个实例都将对应另一实体集的 0 个、1 个或多个实例。 –> 两点实线

  • 一些结论：

    • 标定型联系中，子女实体集总是从属实体集

逻辑设计

关系模式设计、规范化处理

例：视图设计

ER 图转关系模型

关系模式数量 = 实体集数量 + 多对多联系数量

  • 实体转化为独立的模式

  • 多对多联系转换为关系模式

  • 一对多联系中一端并入到多端的实体中

范式

函数依赖

  • 完全函数依赖

    • 例：学生基本信息表 R（学号，班级，姓名）假设不同的班级学号有相同的，班级内学号不能相同，在 R 关系中，（学号，班级）->（姓名），但是（学号）->(姓名)不成立，（班级）->(姓名)不成立，所以姓名完全函数依赖与（学号，班级）

  • 部分函数依赖

    • 例：学生基本信息表 R 中（学号，身份证号，姓名）当然学号属性取值是唯一的，在 R 关系中，（学号，身份证号）->（姓名），（学号）->（姓名），（身份证号）->（姓名）；所以姓名部分函数依赖于（学号，身份证号）

  • 传递函数依赖

    • 定义：设 X,Y,Z 是关系 R 中互不相同的属性集合，存在 X → Y(Y !→X),Y → Z，则称 Z 传递函数依赖于 X。

    • 例：在关系 R(学号 ,宿舍, 费用)中，(学号) -> (宿舍),宿舍 !->学号，(宿舍) -> (费用),费用 !-> 宿舍，所以符合传递函数的要求。

  • 多值依赖

    • 例如：职工表(职工编号，职工孩子姓名，职工选修课程)，在这个表中，同一个职工可能会有多个职工孩子姓名，同样，同一个职工也可能会有多个职工选修课程，即这里存在着多值事实，不符合第四范式。如果要符合第四范式，只需要将上表分为两个表，使它们只有一个多值事实，例如职工表一(职工编号，职工孩子姓名)，职工表二(职工编号，职工选修课程)，两个表都只有一个多值事实，所以符合第四范式。

范式

  • 第一范式 1NF：关系模式不包含多值属性

  • 第二范式 2NF：消除非主属性对主码的部分函数依赖。实体的属性完全依赖于主关键字

  • 第三范式 3NF：消除传递依赖。属性不依赖于其它非主属性

  • 巴斯-科德范式 BCNF：消除对主码子集的依赖。任何非主属性不能对主键子集依赖

  • 第四范式 4NF：消除多值依赖。

一般来说，只有两个属性的表可以到达 4NF

物理设计

物理结构角度要考虑的问题：文件的组织，文件的结构，文件的存取和索引技术。

内容：数据库逻辑模式描述，文件组织和存取设计，数据分布设计，确定系统配置，物理模式评估。

例：去规范化、表分区

索引

  • 散列索引：不适合用于模糊(范围)查询，适合点查询

  • 有序索引：适合模糊(范围)查询和点查询

      • 聚集索引(CLUSTERED)：索引文件中索引项排列顺序和数据排列顺序相一致

      • 非聚集索引(NONCLUSTERED)：反之。

    • 聚集索引跟适合范围查询

      • 稠密索引：每个查找码都对应一个索引记录

    • 稀疏索引：部分查找码对应了索引记录

      • 主索引：主码属性集上建立的索引

    • 辅索引：非主属性上建立的索引

    • 唯一索引(UNIQUE)：索引属性列都是唯一的

    • 单层索引

    • 多层索引

    • 复合索引：效率高于单一索引

    • 单一索引

适合索引使用条件：

  • 在经常需要搜索的列上，可以加快搜索的速度

  • 在经常使用连接的列上（这些列主要是一些外键）可以加快连接的速度，在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的

  • 在经常使用WHERE子句中的列上面创建索引，加快条件的判断速度。

  • Group by,Order by 也行，但 select 不行

不适合使用的条件：

  • 对于那些在查询中很少使用或者参考的列不应该创建索引

  • 对于那些只有很少数据值的列也不应该创建索引

  • 经常更新的列不适宜创建索引

模糊查询不会访问索引

文件分类

  • 散列文件：散列存储方式组织的文件

    • 优点：文件随机存放，无需排序，插入删除方便，存取速度快，不需要索引节省空间

    • 缺点：不能顺序存取

  • 堆文件

    • 数据库中的一个基本表的数据量很少，并且插入删除更新频繁，可以采用堆文件

    • 优点：无需建立索引，维护代价低，在数据量少时定位文件记录时间短

    • 缺点：数据访问效率低

  • 顺序文件

    • 如果用户的查询条件定义在查找码上，则顺序文件是比较适合的文件结构

    • 优点：支持顺序存取和随机存取，顺序存取快

    • 缺点：文件不能动态增长，不利于插入和删除

  • 聚集文件

    • 将不同关系表中有关联关系的记录存储到一起。

    • 如果频繁使用多表连接的查询，可以考虑聚集文件来改善查询效率

数据库应用系统功能设计与实施

B/S 结构的数据库应用系统

按功能分为四个层次

  • 表示层：用户交互

  • 业务逻辑层

    • 表示层的数据加工处理传递给数据访问层

    • 数据访问层获得的数据加工处理在表示层展示

    • 例：存储过程设计、

      构件设计

      • 构件设计

        • 单一责任原则

        • 功能独立，减少重叠

        • 接口简单明确

        • 如果两个构件间的关系比较复杂，应考虑进一步进行模块划分（不能合并！）

        • 如果构件过于复杂，可以细分

  • 数据访问层：与数据库交互，提取或存入数据。

  • 数据持久层：负责保存和管理应用系统数据。

    • 例：数据完整性维护、调整数据文件组织结构、索引设计、存储结构设计

B/S架构的优点

  1. 客户端无需安装，有Web浏览器即可。

  2. BS架构可以直接放在广域网上，通过一定的权限控制实现多客户访问的目的，交互性较强。

  3. BS架构无需升级多个客户端，升级服务器即可。可以随时更新版本，而无需用户重新下载。

B/S架构的缺点：

  1. 在跨浏览器上，BS架构不尽如人意。

  2. 表现要达到CS程序的程度需要花费不少精力。

  3. 在速度和安全性上需要花费巨大的设计成本，这是BS架构的最大问题。

  4. 客户端服务器端的交互是请求-响应模式，通常需要刷新页面，这并不是客户乐意看到的。（在Ajax风行后此问题得到了一定程度的缓解）

数据库安全性

用户身份鉴别

用户身份鉴别是数据库管理系统提供的最外层安全保护措施。

每个用户在系统中都有一个用户标识，每个用户标识由用户名（user name）和用户标识号（UID）组成，用户标识号在系统的整个生命周期内是唯一的。

1. 静态口令鉴别

  • 静态口令一般由用户自己设定，这些口令是静态不变的。

  • 数据库管理系统从口令的复杂度，口令的管理、存储及运输等多方面来保障口令的安全可靠。

  • 特点：简单，容易被攻击，安全性较低

2. 动态口令鉴别

  • 口令是动态变化的，每次鉴别时均需使用动态产生的新口令登录数据库管理系统，即采用一次一密的方法。

  • 常用方式：短信密码和动态令

  • 特点：与静态口令鉴别相比，安全性相对高一些

3. 生物特征鉴别

  • 通过生物特征进行认证的技术，生物特征是指生物体唯一具有的，可测量、识别和验证的稳定生物特征，如指纹、虹膜和掌纹等。

  • 特点：与传统的口令鉴别相比，安全性较高

4. 智能卡鉴别

  • 智能卡是一种不可复制的硬件，内置集成电路的芯片，具有硬件加密功能。

  • 智能卡由用户随身携带，登录数据库管理系统时用户将智能卡插入专用的读卡器进行身份验证。

  • 由于每次从智能卡中读取的数据是静态的，通过内存扫描或网络监听等技术还是可能截取到用户的身份验证信息，存在安全隐患。因此，实际应用中一般采用个人身份识别码（PIN）和智能卡相结合的方式。

存取控制

存取控制机制主要包括定义用户权限和合法权限检查两部分。

（1）定义用户权限，并将用户权限登记到数据字典中

  • 用户对某一数据对象的操作权力称为权限

  • 数据库管理系统提供适当的语言来定义用户权限，这些定义经过编译后存储在数据字典中，被称做安全规则或授权规则。

（2）合法权限检查

  • 用户发出存取数据库操作请求，数据库管理系统查找数据字典，根据安全规则进行合法权限检查。

权限定义和合法权检查机制一起组成了数据库管理系统的存取控制子系统。

C2 级的数据库管理系统支持自主存取控制（Discretionary Access Control，简称 DAC），B1 级的数据库管理系统支持强制存取控制（Mandatory Access Control，简称 MAC）。

自主存取控制（DAC）

  • C2级：非常灵活

  • 用户对不同的数据库对象有不同的存取权限

  • 不同的用户对同一对象也有不同的权限

  • 用户还可将其拥有的存取权限转授给其他用户

强制存取控制（MAC）

  • B1级：相对比较严格

  • 每一个数据库对象被标以一定的密级

  • 每一个用户也被授予某一个级别的许可证

  • 对于任意一个对象，只有具有合法许可证的用户才可以存取

自主存取控制方法

  • 自主存取控制主要通过 SQL 的 GRANT 语句和 REVOKE 语句实现。

  • 用户权限由数据库对象和操作类型组成。

  • 定义用户的存取权限：就是定义用户可以在哪些数据库对象上进行哪些类型的操作。

  • 在数据库系统中，定义存取权限称为授权（authorization）。

  • 在非关系系统中，用户只能对数据进行操作，存取控制的数据库对象也仅限于数据本身。

  • 在关系数据库系统中，存取控制的对象不仅有数据本身（基本表中的数据、属性列上的数据），还有数据库模式（包括模式、基本表、视图和索引的创建等）。

对列的 UPDATE 权限指对于表中存在的某一列的值可以进行修改。有了这个权限之后，在修改的过程中还要遵守表在创建时定义的主码及其他约束。

列上的 INSERT 权限指用户可以插入一个元组。对于插入的元组，授权用户可以插入指定的值，其他列或者为空，或者为默认值。在给用户授予列 INSERT 权限时，一定要包含主码的 INSERT 权限，否则用户的插入动作会因为主码为空而被拒绝。

视图机制

可以为不同的用户定义不同的视图，把数据对象限制在一定的范围内。即通过视图机制把要保密的数据对无权存取这些数据的用户隐藏起来，从而自动对数据提供一定程度的安全保护。

视图机制间接地实现支持存取谓词的用户权限定义。

例：建立计算机系学生的视图，把对该视图的 SELECT 权限授于王平，把该视图上的所有操作权限授于张明。

/* 先建立计算机系学生的视图 CS_Student */CREATE VIEW CS_Student AS SELECT * FROM Student WHERE Sdept='CS'; /* 在视图上进一步定义存取权限 */ GRANT SELECT ON CS_Student TO 王平; GRANT ALL PRIVILIGES ON CS_Student TO 张明;

UML

四层建模概念框架

下层的每个概念是上层的一个实例

  • 元元模型层：组成了 UML 的最基本的元素“事物”

  • 元模型层：组成 UML 的基本元素

  • 模型层：组成 UML 的模型

  • 用户模型层：其中的所有元素都是 UML 模型的实例

结构图

定义：对系统静态结构建模，反应模块层次结构

  • 类图：展现一组类、接口和协作以及它们之间的关系的一种静态视图

    • 关系：

      • 聚合：空心菱形实线

      • 组合(不能离开整体)：实心菱形实线

      • 实现：空心三角虚线

      • 泛化：空心实线三角，即继承

      一对多关系用：1 : *表示

  • 组件图

  • 对象图

  • 部署图

行为图

定义：系统动态行为建模

  • 用例图

    • 组成

      • 用例、系统、角色

    • 关系

      • 扩展、包含、泛化（扩展、使用、组合）

  • 交互图

    • 顺序图：描述对象自身及对象间消息传递顺序的视图

    • 通信图(协作图)：交互图的一种，其中包含一组对象、对象之间的联系以及对象发送和接收的消息

    • 时间图

    • 交互概述图

  • 状态图：描述一个实体在发生一些事件时的状态变化情况

  • 活动图

顺序图表示递归过程，当一个操作调用它本身时，消息总是同步的

SQL

谓词

EXIST

EXISTS 运算符用于判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False。

SQL EXISTS 语法

SELECT column_name(s)FROM table_nameWHERE EXISTS(SELECT column_name FROM table_name WHERE condition);

集合操作符

UNION / UNIAN ALL

请注意，UNION (ALL)内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

UNION

合并两个或多个 SELECT 语句的结果集，会对结果去重。

例：

SELECT column_name(s) FROM table_name1UNIONSELECT column_name(s) FROM table_name2-- 获取表1、表2中所有的column_name，重复的只取一次

UNIAN ALL

UNION ALL 命令和 UNION 命令几乎是等效的，不过 UNION ALL 命令会列出所有的值，即不去重。

SELECT E_Name FROM Employees_ChinaUNION ALLSELECT E_Name FROM Employees_USA-- 获取Employees_China和USA中所有的E_Name，不去重

IN

IN 操作符允许我们在 WHERE 子句中规定多个值。

SELECT column_name(s)FROM table_nameWHERE column_name IN (value1,value2,...)

EXCEPT

QL Server EXCEPT比较两个查询的结果集，并返回第一个查询中不是由第二个查询输出的不同行。 换句话说，EXCEPT从一个查询结果中减去另一个查询的结果集。

以下是SQL Server EXCEPT的语法：

query_1 EXCEPT query_2

以下是在上述语法中组合两个查询的结果集的规则：

  • 两个查询中列的数量和顺序必须相同。

  • 相应列的数据类型必须相同或兼容。

下图显示了两个结果集T1和T2的EXCEPT操作：

img

在此图中：

  • T1结果集包括：1,2和3。

  • T2结果集包括：2,3和4。

T1和T2差集结果将返回1，它是在T1结果集中，并T2结果集中没有出现的行。

例：

使用EXCEPT运算符查找没有销售的产品：

SELECT    product_idFROM    production.productsEXCEPTSELECT    product_idFROM    sales.order_items;

INTERSECT

INTERSECT运算符是一个集合运算符，它从SELECT语句返回两个或多个结果集的不同行。

假设有两个表记录：A(1,2)和B(2,3)。

下图说明了A和B表的交集。

img

紫色部分是绿色和蓝色结果集的交集。

与UNION运算符一样，INTERSECT运算符从最终结果集中删除重复的行。以下语句说明了如何使用INTERSECT运算符查找两个结果集的交集。

要使用INTERSECT运算符，SELECT语句的列需要遵循以下规则：

  • 列的数据类型必须兼容。

  • SELECT语句中的列数及其顺序必须相同。

例：

SELECT    idFROM    a INTERSECTSELECT    idFROM    b;

语句

DISTINCT

查询结果去重

例：

SELECT DISTINCT Company FROM Orders

TOP

top n (with ties) 表示选取最多的前 n 个，with ties 要与 top 和 order by 一起使用，表示包含最后一行并列结果。

top n percent 表示选取查询结果的前%n 行。

例：

SELECT top 3 with ties a, b, c  from t1 order by id-- a值前3的记录(含并列)

CASE

简单的条件赋值语句

语法结构：

CASE 测试表达式    When 简单表达式1 THEN 结果表达式1    When 简单表达式2 THEN 结果表达式2    When 简单表达式3 THEN 结果表达式3    [ELSE 结果表达式]END

例：

CASE sex    When '1' THEN '男'    When '0' THEN '女'    ELSE '爬开'ENDCASE    WHEN sex='1' THEN '男'    WHEN sex='0' THEN '女'    ELSE '爬开'END

公用表达式

常用函数

DATEDIFF: 计算时间差

常用的字符串函数:

charindex()     -- 字符索引len()           -- 长度upper()         -- 大写Ltrim()         -- 左裁减Rtrim()         -- 右裁减Left()          -- 左Right()         -- 右stuff()         -- 删除并添加replace()       -- 查找并替换

开窗函数

在一个T_Person表中，如果我们计算所有人员的总数，可以执行下面的SQL语句：

SELECT COUNT(FName) FROM T_Person

这种方式比较直接，只返回一个聚合列的值，没有任何基础行的列的值。但是有时需要从不在聚合函数中的行的列中访问这些聚合计算的值（即基础行的列）。比如我们想查询每个工资小于5000元的员工信息（城市以及年龄），并且在每行中都显示所有工资小于5000 元的员工个数，尝试编写下面的SQL语句：

SELECT FName, FCITY, FAGE, FSalary, COUNT(FName)FROM T_PersonWHERE FSALARY<5000

执行上面的SQL以后我们会得到下面的错误信息：选择列表中的列'T_Person.FCity' 无效，因为该列没有包含在聚合函数或GROUP BY 子句中。 这是因为所有不包含在聚合函数中的列必须声明在GROUP BY 子句中，使用子查询的方式是可以解决：

SELECT FName, FCITY, FAGE, FSalary,(SELECT COUNT(FName) FROM T_PersonWHERE FSALARY<5000)FROM T_PersonWHERE FSALARY<5000

虽然使用子查询能够解决这个问题，但是子查询的使用非常麻烦，使用开窗函数则可以大大简化实现，下面的SQL语句展示了如果使用开窗函数来实现同样的效果：

SELECT FName, FCITY, FAGE, FSalary, COUNT(FName) OVER()FROM T_PersonWHERE FSALARY<5000

可以看到与聚合函数不同的是，开窗函数在聚合函数后增加了一个OVER关键字。 开窗函数的调用格式为：函数名(列) OVER(选项)

SQL SERVER也支持开窗函数中使用ORDER BY子句，同时也因此，开窗函数被分为两大类。

第一大类：聚合开窗函数-> 聚合函数(列) OVER (选项)，这里的选项可以是PARTITION BY子句，但不可是ORDER BY子句

第二大类：排序开窗函数-排序函数(列) OVER(选项)，这里的选项可以是ORDER BY子句，也可以是　OVER（PARTITION BY子句　ORDER BY子句），但不可以是PARTITION BY子句

用户自定义函数

声明

CREATE FUNCTION  -- 定义ALTER FUNCTION  -- 修改DROP FUNCTION  -- 删除

标量函数

返回值可以是除了timestamp类型以外的所有数据类型

内嵌表值函数

类似带参数的视图

  • 返回值是一个表，是查询语句的结果

  • 使用时，一般放在查询的 FROM 子句中

  • 通过 SELECT 语句填充函数返回的表值

多语句表值函数

  • 只有标量函数可以出现在 SELECT 语句目标列中，其余两个函数必须放在 SELECT 语句的 FROM 子句中

触发器

定义

FOR / AFTER声明的触发器是后触发器，在语句操作完成，约束检查成功后执行

INSTEAD OF 创建的是前触发器，执行触发器来替代语句

一张表可以有多个后触发器，当对于同一数据操作(如 UPDATE)只能有一个前触发器

视图不能定义触发器

例：

-- 后触发器CREATE TRIGGER tri_name on T FOR UPDATE AS ...-- 前触发器CREATE TRIGGER tri_name on T INSTEAD OF UPDATE AS ...

游标

声明

CREATE PROC proc_name@变量1, @变量2 AS sql语句

使用

如果声明游标时不指定 SCROLL 选项，使用 FETCH 时只能使用 NEXT

FETCH [NEXT | PRIOR | FIRST | LAST | ABSOLUTE{n|@nvar} | RELATIVE{n|@nvar}] FROM cursor_nameNEXT：下一行PRIOR：上一行FIRST：第一行LAST：最后一行ABSOLUTE{n|@nvar}：如果n或@nvar为正，则返回从第一行开始数的第n行；如果n为负，则返回倒数第n行RELATIVE{n|@nvar}：如果n为正，返回当前行往下数的第n行；如果n为负，则返回当前行往上数的第n行*/

判断游标数据提取状态的全局变量：FETCH_STATUS

  • =0 时：表明 FETCH 成功

  • =-1 时：表明 FETCH 失败，或行不在结果集中

  • =-2 时：提取的行不存在

存储过程

CREATE PROC proc_name@变量1, @变量2 AS sql语句

安全权限管理

多角色权限冲突时，优先 DENY，其余权限取并集。

数据库用户可以分为：系统管理员、对象拥有者、普通用户

主要角色及权限

  • 服务器角色：
服务器角色 描述
sysadmin 可以在SQL Server中执行任何活动。
serveradmin 可以设置服务器范围的配置选项和关闭服务器。
setupadmin 可以添加和使用Transact-SQL语句删除链接的服务器。 （使用SQL Server管理套件，当系统管理员成员需要。）
securityadmin 可以管理登录及其属性。他们可以GRANT, DENY和REVOKE服务器级别的权限。他们还可以GRANT, DENY和REVOKE数据库级别的权限，如果他们有机会获得一个数据库。 他们还可以重置SQL Server登录密码。
processadmin 可以结束了在SQL Server实例中运行的进程。
dbcreator 可以创建，修改，删除，并恢复所有数据库。
diskadmin 可以管理磁盘文件。
bulkadmin 可以执行BULK INSERT语句。
public 每一个SQL Server登录属于公共服务器角色。当一个服务器主体没有被授予或拒绝对受保护对象的特定权限，用户继承对象授予public权限。只有当你想提供给所有用户对象上的任何对象分配公共权限。不能改变的成员在公共权限。
  • 数据库角色：
数据库角色 描述
db_owner db_owner 固定数据库角色的成员可以执行数据库的所有配置和维护活动，还可以 drop SQL Server 中的数据库。 （在 SQL 数据库 和 Synapse Analytics 中，某些维护活动需要服务器级别权限，并且不能由 db_owners 执行。）
db_securityadmin db_securityadmin 固定数据库角色的成员可以仅修改自定义角色的角色成员资格和管理权限。 此角色的成员可能会提升其权限，应监视其操作
db_accessadmin db_accessadmin 固定数据库角色的成员可以为 Windows 登录名、Windows 组和 SQL Server 登录名添加或删除数据库访问权限。
db_backupoperator db_backupoperator 固定数据库角色的成员可以备份数据库。
db_ddladmin db_ddladmin 固定数据库角色的成员可以在数据库中运行任何数据定义语言 (DDL) 命令。 此角色的成员可以通过操作可能以高特权执行的代码来提升其特权，并且应监视其操作。
db_datawriter db_datawriter 固定数据库角色的成员可以在所有用户表中添加、删除或更改数据。
db_datareader db_datareader 固定数据库角色的成员可以从所有用户表和视图中读取所有数据。 用户对象可能存在于除 sys 和 INFORMATION_SCHEMA 以外的任何架构中 。
db_denydatawriter db_denydatawriter 固定数据库角色的成员不能添加、修改或删除数据库内用户表中的任何数据。
db_denydatareader db_denydatareader 固定数据库角色的成员不能读取数据库内用户表和视图中的任何数据。
public 每个数据库用户都属于 public 数据库角色。如果未向某个用户授予或拒绝对安全对象的特定权限时，该用户将继承授予该对象的 public 角色的权限。

权限管理

赋予权限：

GRANT 权限名 ON 库名(表名) TO 用户名GRANT 权限名 ON 表名 TO 用户名-- 例：授予用户U1具有创建表和视图权限GRANT CREATE TABLE, CREATE VIEW TO 用户名
  • 在授权时，如果要使用户可以将授予他的权限转授他人，需要加上WITH GRANT OPTION选项
GRANT 权限名 ON 表名 TO 用户名 WITH GRANT OPTION

不允许用户获得某种权限：

DENY 权限名 ON 库名(表名) TO 用户名DENY 权限名 ON 表名 TO 用户名

收回已经授予的权限：

REVOKE 权限名 ON 表名 FROM 用户名
  • 使用 REVOKE 回收权限时，如果要将用户转授给其他用户的权限一起回收，要加入CASCADE选项

安全性分级

  • A：验证保护

  • B：强制保护

  • C：自主保护

  • D：最小保护

账户管理

身份验证模式

两种身份验证方式：混合身份验证模式、Windows 身份验证

在 Windows 身份验证模式下，不允许SA登录到 SQL server 服务器

不管哪种身份验证模式，Windows 的 Administrator 无需授权就能登录到 SQl server 服务器

guest 账户

没有对应的登录名

任何 SQL server 登录账户都可以访问 guest 用户的数据库

创建账户

CREATE LOGIN 用户名 WITH PASSWORD='密码'

删除账户

DROP LOGIN 用户名

运行与维护

例：引入汇总表

性能优化：

  • 重组：不修改原有设计的逻辑结构和物理结构

  • 重构：部分修改

冗余列：减少连接

派生冗余列：减少聚合函数

故障管理

转储机制：

效率：完全 < 差量 < 增量

空间占用：差量 > 增量

恢复速度：完全 > 差量 > 增量

  • 完全转储：对整个数据库的数据全部重新备份

  • 差量转储：基于上一次完全转储基点之后变化转储，对修改和删除记录的转储

  • 增量转储：只复制上次转储后发生变化的文件或数据块。

  • 静态转储：保证数据有效性，牺牲数据库可用性。数据库不能运行其他事务，不允许有任何修改活动

  • 动态转储

故障：

  • 事务(内部)故障

    • 预期

    • 非预期：运算溢出、并发事务死锁、违反完整性限制

  • 系统故障：又叫软故障

    • 如：硬件故障、数据库软件及操作系统漏洞、突然停电等

  • 介质故障

  • 计算机病毒

数据库镜像

  • 高保护

  • 高可用

  • 高性能

备份

  • 完全备份：备份所有数据和日志

  • 差异备份：备份自上一次完全备份后产生的完全备份

BACKUP DATABASE 数据库名 TO 设备名 [选项]WITH DIFFERENTIAL：表示差异备份NOINIT：表示将此次备份追加到指定的媒体集，以保留原有的备份集。*/

事务日志备份：

  • 纯日志备份：仅包含一定间隔的事务日志记录而不包含再大容量日志恢复模式下执行的大容量更改的备份

  • 大容量操作日志备份：包含日志记录和大容量操作更改的数据页的备份，不允许对大容量操作日志备份进行时点恢复

  • 结尾备份：对可能已损坏的数据库进行日志备份，可以百行纯日志记录或大容量操作记录

文件备份只对数据文件备份，日志文件需要再备份

对 master 数据库只支持完整备份

执行任一更新数据库的操作后，要对 model 数据库备份

恢复

恢复模式

  • 简单恢复：无日志备份。用于测试和开发，或用于主要包含只读数据的数据库(如数据仓库)。

  • 完整恢复：需要日志备份。如，还原单个数据页

  • 大容量日志恢复：需要日志备份。是完整恢复的附加模式，不支持时点恢复，使用最小方式记录大多数大容量操作，来减少日志空间使用量。

恢复顺序

恢复最近的完全数据库备份

恢复完全备份之后的最近的差异数据备份

按日志备份的先后顺序恢复自最近的完全或差异数据备份之后的所有日志备份

数据库及数据库对象

数据文件

  • 数据库中主要数据文件的大小不能小于model数据库主要文件大小，model 数据库为新创建的数据库提供模板。

  • 主数据文件必须建立在主文件组中（大小不能小于 3MB），次要数据文件也可以存在在主文件组

  • 每个数据库只有一个主要数据文件，可以有多个次要数据文件

  • 日志文件不包含在文件组中

  • 一个数据文件属于一个文件组

分离附加数据库

  • 分离前要断开连接

  • 分离数据文件和日志文件

  • 分离数据库需要停用被使用的数据库，但不能停止 SQL server 服务

数据库

  • master：记录数据库实例的系统级信息

  • msdb：存储实例的作业信息

  • tempdb：每次启动都会重新创建

  • Resource：包含数据库所有系统对象

分区：–> 对表中数据进行水平/垂直划分

  • 步骤

    • 创建分区函数：确定以什么方式对表分区

    • 创建分区方案：分区函数生成的分区映射到文件组中

    • 使用分区方案创建表

  • 分区函数

    • LEFT，范围包右不包左；RIGHT，范围包左不包右

标识列：identity

大规模数据库架构

分布式数据库

通信代价最大的是各个站点分片间连接和并操作

分布式数据库的分布透明性包括

  • 分片透明性(最高层次)：只对全局关系进行操作，不考虑关系分片。位于全局概念模型和分片模型之间

  • 位置透明性：只需了解数据分片情况，不需要了解片段的存储场地

  • 局部数据模型透明性：了解数据分片情况，了解片段的存储场地。位于分配模式和局部概念之间

分片类型：

  • 水平分片

  • 垂直分片

  • 导出分片

  • 混合分片

分布式数据库中，使用半连接操作可以减少场地之间的数据传输量

分布式数据库中，使用分配模式来描述各片段到物理存放场所的映像

最基本特征：本地自治、非集中式管理、高可用性

分布式数据库系统的恢复控制采用的最典型策略是基于两阶段的提交协议

并行数据库

划分方法

  • 轮转法：最适合整表扫描

  • 散列划分：适合点查询，也适合顺序扫描

  • 范围划分：适合范围查询和点查询

分布策略

  • 集中式：所有数据片段都安排在一个场地

  • 分割式：全局数据只有一份，但被分割成多个片段，每个片段分配在特定场地

  • 全复制式：每个站点上都有全局数据的复制样本，数据冗余大

  • 混合式：混合使用上述策略，介于分割式和全复制式

结构

  • 无共享结构：独立磁盘，内存。 –> 满足高并发，OLTP

  • 共享结构：共享磁盘，内存。

  • 层次结构：顶层是无共享结构，底层是共享结构

数据挖掘与数据仓库

数据挖掘

算法：

  • 关联规则挖掘

    • 指标：支持度、置信度

  • 聚类

  • 分类

数据管理和分析形式

  • OLTP(联机事务处理)：对数据的查询和修改。要求快速响应操作，对数据的安全性、完整性及事务吞吐量要求高

  • OLAP(联机分析处理)：访问的数据率大，查询和分析操作复杂

OLAP 的实现方式

  • MOLAP：基于多维数据库

  • ROLAP：基于关系型数据库

  • HPLAP：混合型

知识发现由三个步骤组成，数据准备、数据挖掘、结果解释评估

高粒度数据视图切换到低粒度数据视图的分析操作叫钻取

数据仓库

数据仓库是面向主题的、集成的、非易失的、且随时间变化的数据集合

元数据可以分为技术元数据和业务元数据

为了解决不同数据源格式上的不统一，需要进行的数据操作是转换

特征

  • 不可更新性：用户对在提取仓库中的数据进行分析时不会同时对数据仓库中的数据进行更新操作

  • 数据变化性：数据仓库每隔一段时间进行数据的更新和处理。更新与时间间隔有关，不会实时更新

其他

  • 分布式数据库

    • 目标：本地自治、非集中式管理、高可用性、位置独立性、数据分片独立性、数据复制独立性、分布式查询处理、分布式事务管理、硬件独立性、操作系统独立性、网络独立性、数据管理系统独立性。

    • 特点：独立透明性、集中结点结合、复制透明性、易于扩展性

    • 缺点：开销大，主要是通信；数据安全性和保密性难处理

  • 并行式数据库

    • 目标：高性能和高可用性，通过多个处理节点并行执行数据库任务，提高整个数据库系统的性能和可用性。

约束

关系级别约束：主外键约束

列级约束：非空、唯一

元组约束：CHECK

数据完整性定义：

  • 实体完整性：主键

    • 级别

      • 元组

      • 列：值类型、范围、精度、排序等

      • 表(关系)：

  • 参照完整性：外键

  • 自定义完整性：定义属性上的约束条件，如非空(NOT NULL)、列值唯一(UNIQUE)、列值满足表达式(CHECK)

三级模式与二级映像

三级模式

  • 外模式：用户可见的部分数据的存在形式。

  • 模式：全体数据的逻辑结构且用户不可见。

  • 内模式(唯一)：数据库的物理结构和存储方式。

二级映像

  • 外模式/模式映像保证了数据与程序的逻辑独立性。

  • 模式/内模式的映射保证了数据库中数据与应用程序间的物理独立性。

关系与关系模式

  • 关系模式：二维表的表头属性等，即一个二维表的主要架构。由于二维表的属性名一般不会修改，所以呈现出静态。

  • 关系是一张二维表的具体数据，除去表头外各数据间的联系。由于二维表中是数据会时常修改，所以呈现出动态。随用户对数据库的操作而改变。

磁盘

RAID1：提高了写速度，磁盘利用率低

RAID5：写入速度比 RAID1 慢，磁盘利用率高

RAID10：这种结构是为了把 RAID0 和 RAID1 的优缺点相互补充，达到既安全又高速的目的。

视图

特点：

  • 简单性：见到的就是需要的。

  • 安全性：通过视图，用户只能查询和修改他们所能见到的数据。

  • 逻辑数据独立性：屏蔽真实表结构变化带来的影响。

作用：

  • 简化数据查询语句

  • 使用户从多角度看待同一数据

  • 提高数据安全性

索引视图：–> 建立了唯一聚集索引的视图

  • 数据源只能是同一数据库中的基表，不能是视图

  • 索引视图的数据被物理地保存在数据库中，会占空间。因为创建唯一聚集索引会物理地保存数据。普通视图不占空间。

  • 不合适情况：

    数据更新频繁

    不涉及聚集和连接的查询

    group by 具有高基数度(表示列具有许多不同值)

事务

事务规范：

  • 事务名称

  • 事务描述(处理逻辑)

  • 事务所访问的数据项(关系表/模式)

  • 事务用户

检测死锁：

  • 超时法

  • 等待图法：周期性的生成事务等待图进行检测。(不是在执行每个事务时检测！)

防止死锁：

  • 大事务切分为小事务

  • 事务按同一顺序访问资源

  • 使用绑定链接

  • 降低事务隔离性级别

事务特性：

  • 原子性

  • 一致性：事务的隔离执行，保持数据库的一致性

  • 隔离性：

  • 持久性：每个事务成功完成后，它对数据库的改变必须是永久的，即使系统出现故障

事务概要设计阶段，用 read、write原语来表达存取结构

两阶段加锁协议保证事务调度的可串行性

数据定义语言DDL

  • 数据定义语句经过 DDL 编译器编译后，各种对象的描述信息存放在数据库的数据字典中。

  • 数据字典包括数据项、数据结构、数据流、数据存储和处理过程。

  • 执行后不能回滚

概念数据模型不描述数据的完整性约束。

不同数据库管理系统有着不同的逻辑实现结构，数据库空间管理方法不同！

数据库架构与用户是一对多关系

← 计算机三级知识点 →