ToB企服应用市场:ToB评测及商务社交产业平台

标题: 数据库基础概论期末复习（已完结） [打印本页]
作者: 怀念夏天    时间: 2024-7-12 05:01
标题: 数据库基础概论期末复习（已完结）
所有内容均总结自自中国大学慕课 MOOC-北京信息科技大学-车蕾老师的数据库系统基础课程 https://www.icourse163.org/shortUrl/parse/RcDZo可以从该链接直接观看解说视频和课件
第一章 数据库概论

1.1 数据管理及其发展过程


1.1.1 人工管理阶段

配景


特点

1.1.2 文件系统阶段

配景


特点

文件系统的缺陷

1.1.3 数据库系统阶段

标志着数据库技术诞生的三个事故
层次数据模型第一代数据库系统
网状数据模型
关系数据模型（二维表）第二代数据库系统

1.2 数据库系统的特点

1.2.1 数据库是相互关联的数据的聚集

1.2.2 用综合的方法构造数据

1.2.3 低冗余与数据共享

        包管数据的同等性
1.2.4 数据具有较高的独立性


        数据独立性是指数据的构造和存储方式与应用步伐互不依靠、彼此独立。
1.2.5 包管数据的精确性


        包管数据精确的特性在数据库中称之为数据完整性。
1.2.6 包管数据的安全、可靠


1.2.7 数据可以并发使用并能同时包管其同等性

        在多个用户同时使用数据库时，能够包管不产生辩说和抵牾，包管数据的不停信奉和精确性。


1.3 数据模型初步

1.3.1 数据模型

能比较真实地模拟现实世界概念数据模型（认知模型）
轻易被人们理解
便于在计算机上实现构造数据模型（实现模型）

1.3.2 概念数据模型（认知模型）

        描述现实世界的数据模型成为概念模型或概念数据模型。
E-R（Entity Relationship）的三要素

1.3.3 构造层数据模型（实现模型）

1 层次数据模型

2 网状数据模型

3 关系数据模型


1.4 三层模式结构与数据库管理系统

1.4.1 三层模式结构与数据独立性

1 数据库管理系统的根本功能

DBMS根本功能：

2 数据库的三层模式结构

3 数据独立性：
        指应用步伐与数据的构造和存储结构相互独立的特性。（具体说，就算修改数据的构造方法和存储结构，应用步伐不用修改）。
4 数据库管理系统的根本框架

当一个用户步伐通过DBMS读取一条记录时，

1.5 数据库系统

1.5.1 构成

        以数据为主体的数据库，和管理数据库的系统软件数据库管理系统（DBMS），····。
1.5.2 数据库管理和数据库管理员

  数据库管理员（DBA，Database Adminstrator)



第二章 概念数据模型

2.1 概念数据模型及实体联系方法

2.1.1 概念


2.1.2 实体-联系方法（E-R图）

 1 三要素：实体、属性、联系

2 概念
        实体：客观存在并可以相互区分的客观事物或抽象事故
        属性：实体的特征（性质）；
        实体集：具有相同属性的一类实体的聚集；
        弱实体：仅靠自身的特征不能区分一个个实体，须要借助其他实体的特征才能够举行区分；
        联系：数据之间的关联聚集。
3 实体之间的根本联系
        1：1；
        1：n；
        m：n；
4 依靠联系
        被弱实体所依靠的实体集也称作强实体集，强弱实体机之间的联系叫依靠联系。

2.2 深入讨论联系的几个标题

2.2.1 多对多联系

由于技术上的原因，不直接使用多对多，而是将它们转换为一对多联系。

并且，两个实体之间多对多的联系一定能转换成一对多的联系！
2.2.2 概念、逻辑、物理数据模型


第三章 关系数据库基础

3.1 关系数据库系统概述

3.1.1 SQL（关系数据库标准语言）

SQL的特点：

3.1.2 关系数据库的三层模式结构


用户可以直接举行操纵的:根本表和视图.
3.1.3 SQL Server的数据库存储结构


master数据库
 用于存储所有系统级信息，包括：       

tempdb数据库
用于保存所有的临时表和临时存储过程，还可以满足任何别的的临时存储要求 

model数据库
是一个模板数据库，必须不停存在于SQL Server系统中。
数据库的物理存储根本上是由SQL Server自动管理的。
3.1.4 SQL Server的用户数据库


1. 主数据文件

2. 次数据文件

3. 事务日志文件


3.2 关系数据模型

3.2.1 关系数据模型的三个要素和关系的形式定义

1. 三个要素
a.关系数据结构：
        实体及实体与实体之间的联系均用关系来表现，关系就是数二维表；
b.关系操纵聚集：
        关系操纵可以用代数操纵（通过代数对关系的运算来表达查询要求的方式）和逻辑方式（通过关系演算、用谓词表达对关系的查询要求的方法）来表现；                     
          关系数据语言分三类：关系代数语言、关系演算语言、具有双重特点的SQL语言。
c.关系完整性约束：
        分三类：实体完整性、参照完整性、用户自定义完整性。
2.关系的形式定义
a.


b.
须要说明两点：

3.2.2 关系的根天性质

X
3.2.3 关系模型的数据结构和根本术语


3.3 关系模型的完整性约束


3.4 关系代数

3.4.1传统的:

        交,   并,   差,   广义笛卡尔积

3.4.2专门的:

SELECT选择运算行筛选
PROJECT投影运算列筛选
JOIN连接运算(选择某些行形成新关系)自然连接(撤除重复)
÷DIVISION除运算

第四章 关系数据理论

4.1 函数依靠

4.1.1 定义

        对于Y=f(X),给定一个X,都有一个Y对应,则X决定Y(X→Y),Y依靠于X；
严格形式化定义：
        

4.1.2 术语和符号


"不良"函数依靠:传递, 部分.
4.1.3 为什么要讨论函数依靠

4.1.4 模式分解(拆表)

定义
        把一个关系模式分解成两个或多个关系模式，在分解的过程中消除那些“不良”的函数依靠，从而获得好的关系模式。
比方：
        仓库（仓库号，地点，设备号，设备名，库存数量）
-----> 分解为：仓库（仓库号，地点） 
                        设备（设备号，设备名） 
                        库存（仓库号，设备号，库存数量）。
要求:

4.2 函数依靠的推理规则

4.2.1 函数依靠的推理规则、Amstrong公理的推论

        自反律、增广率、传递律

定理：Amstrong公理是精确的
Amstrong公理的推论及精确性
        合并、分解、伪传递

引理：
        根据合并规则和分解规则，可以推导出：

4.2.2 逻辑蕴涵和闭包

属性集闭包
        直接计算F的闭包太复杂，以是引入了属性集闭包。


4.2.3 公理的完备性


4.2.4 属性集闭包的计算

算法：

举例：
例1：关系模式R（U,F），U={A,B,C,D,E}，F={AB→C,B→D,C→E,EC→B,AC→B},求
。
1令
={A,B}
2
 F中左部为{A,B}的任意子集的函数依靠有：AB→C，B→D
 Z = {C,D}
3
{A,B,C,D}
4则F中左部为{A,B,C,D}的任意子集的函数依靠有：AB→C,B→D,C→E,AC→B
5
 
 = {A,B,C,D,E}
6因为
 = U，以是
 =  {A,B,C,D,E}
4.2.5 函数依靠集的等价和最小化

1 覆盖和等价


2 最小函数依靠
证实：
方法：

        也就是X的任何真子集Z无法把X→A的所有内容包括，即A完全函数依靠于X 。            
举例：
例1：假设有属性集U={A,B,C,D,E}，函数依靠集F={A→B,B→C,AD→E}，叨教F是否是最小函数依靠。
1F中任一函数依靠的右部都仅含有一个属性；√
2

只用找左部个数大于1的就行
√
3
√



求解
算法：

举例：
例3：关系模式R(U,F), U={A,B,C,D,E}，F={AB→E,DE→B,B→C,C→E,E→A}，求F的最小覆盖。 。
1 因为右部都是单一属性，以是不须要用分解规则化简；
2 化简左侧使得每一个函数依靠的左部没有多余属性

等价变换后的函数依靠集为：{B→E,DE→B,B→C,C→E,E→A}
3 令G=F-{B→E}={DE→B,B→C,C→E,E→A}，则
={A,B,C,E}
E
       
 F与G等价，即B→E是多余的
 F=G={DE→B,B→C,C→E,E→A}
4 令G=F-{DE→B}={B→C,C→E,E→A}，则
={A,D,E}
 B
       
 DE→B不是多余函数依靠，不能去除
5 令G=F-{B→C}={DE→B,C→E,E→A}，则
 = {B}
 C
         
 B→C不是多余函数依靠，不能去除
6 同样，检验C→E,E→A都不是多余函数依靠，不能去除。
 F的最小函数依靠是：{DE→B,B→C,C→E,E→A}

4.3 规范化

        规范化的目标就说要设计“好”的关系，使关系只管淘汰操纵非常甚至拒绝操纵非常征象。
4.3.1 第一范式（1NF）

        所有分量都必须是不可分的最小数据项。
4.3.2 第二范式（2NF）

        如果R(U,F) ∈1NF，并且R中的每个非主属性都完全函数依靠于关键字，则R (U,F) ∈2NF。



判断关系模式是否满足2NF的方法：

4.3.3 第三范式（3NF）

        如果R(U,F) ∈2NF，并且所有非主属性都不传递依靠于关键字，则R(U,F) ∈3NF。
        



判断是否为第三范式的方法：

4.3.4 BC范式

定义：
   
  结论：
        不能将3NF分解成BCNF。

4.4 模式分解

4.4.1 模式分解的准则


1 无损连接
a 形式定义

b 判断一个分解是否具有无损连接特性的法则
        关系模式R分解为R1和R2时无损链接分解的充要条件：
                

2 保持函数依靠
a 定义

分解后的函数依靠的聚集，与原函数依靠等价
b 判断
        判断一个分解是否保持函数依靠，可以根据函数依靠的最小覆盖和等价来判断。
3 例题
例1.设有关系模式R(U,F)，U={职工号,仓库号,都会}，F={职工号→仓库号, 仓库号→都会}，如下分解哪个是保持函数依靠和包管无损连接的分解。
A . ρ1={R1 (职工号,Φ),R2 (仓库号, Φ),R3 (都会, Φ)}
显然不满足函数依靠，都是Φ，无法推出F；

∵ R1∩R2=Φ   
∴ 及不存在R1∩R2→R1-R2，也不存在R1 ∩ R2 → R2 – R1
∴ ρ1不满足无损连接
B. ρ2={R1 ({职工号,仓库号},{职工号→仓库号}), R2 ({职工号,都会},{职工号→都会})}
∵ R1∩R2={职工号}，R1-R2={仓库号}
又 ∵ 存在公共号→仓库号
∴ 存在R1∩R2→R1-R2
∴ 满足无损链连接；

由ρ2无法推导出仓库号→都会
∴ ρ2不满足保持函数依靠
   C.ρ3= {R1 ({职工号,仓库号},{职工号→仓库号}), R2 ({仓库号,都会},{仓库号→都会})}   
∵ R1∩R2={仓库号}，R2-R1={都会}
又∵ 存在仓库号→都会
∴ 存在R1∩R2→R2-R1
∴ 满足无损连接；

由ρ3可以推导出职工号→仓库号，仓库号→都会
∴ρ3还满足保持函数依靠。
  
  例2.设有关系模式R(U,F)，U={A,B,C,D},F={AD→C, B→D }, ρ={R1 (A,B,C),R2 (B,D)} 为R的一个分解,那么分解ρ为：
          A 保持函数依靠和无损连接
          B 无损连接，但不保持函数依靠
          C 不是无损连接，但保持函数依靠
          D 即不是无损连接，也不保持函数依靠
  
∵ R1∩R2={B}，R1-R2={A，C}，R2-R1={D}
又∵ 存在B→D
∴ 存在R1∩R2→R2-R1
∴ 满足无损连接；

由于ρ中，A、D、C三个并没有放在一个函数依靠中，因此AD→C丢失了
∴ ρ不满足保持函数依靠
  例3. 设有关系模式R(U,F)，U={A,B,C,D},F={A→B, C→D },ρ={={R1(A,B),R2(C,D)}为R的一个分解，那么分解ρ为：
  
∵ R1∩R2=Φ，R1-R2={A，B}，R2-R1={C，D}
∴ Φ 无法决定R1-R2或R2-R1
∴ 不满足无损链接；

满足保持函数依靠。
  紧张结论：
  

     
       如果要求分解既   保持函数依靠   又具有   无损连接的特性   ，那么分解   可以达到   3NF   ，但是不一定能达到   BCNF   。    4.4.2 3NF无损链接和保持函数依靠算法


例题
   例1.设有关系模式R(U,F)，其中U={A,B,C}，F={AB→C,B→C}，请使用算法4.3举行模式分解   
0             先求候选关键字：                   因为所有函数的依靠的右部都没有       AB，以是首先求              
               ={A,B,C}=U，则       AB为候选关键字。      
1

计算得到F={B→C}
2

不绝止
3

R0（{A},Φ）,R1（{B,C}，{B→C}）
4

不存在分组标题
5

             ρ={R0 ({A},Φ),R1 ({B,C},{B→C})，U={B,C}      
6

X = {A,B}，Rx（{A,B}，Φ）
             τ=ρ∪Rx (X,Fx )                   ={R0 ({A},Φ),R1 ({B,C},{B→C}),Rx ({A,B},Φ)      
7

U0
X，将R0从τ中去掉
8            
                   分解效果：τ={R1({B,C},{B→C}),Rx({A,B},Φ)}      
  例2.设有关系模式R=（{C,T,S,N,G},{C→T,CS→G,S→N}）
    解：
    
  第五章 逻辑数据模型和物理数据模型

  5.1 数据库设计的概念和方法

  5.1.1 数据库设计的概念

          描述了 现实世界的数据管理需求。
          这里只关注侠义的，即数据模型的设计。
  5.1.2 数据库设计的一样寻常步骤

  

    

  
  5.2 逻辑数据模型设计

  设计关系数据模型，决定了用什么方法来实现现实世界的数据管理需求。
  5.2.1 主要内容

  确定各个关系模式的主关键字、外部关键字、属性的约束，三种完整性约束，设计视图。
  5.2.2 把E-R模型转换为关系数据模型

  ① 将每一个实体转换为一个关系模式，使其包含对应实体的全部属性，并确定关键字
  ② 联系：
  
  例1        1：1联系
  

  或者↓
  

亦或者↓


例2        1：n联系


例3        m：n联系


例4        单选
   把  E-R  模型转换成关系模型时，其中两个一对多联系的实体（ ）   
答案：C

5.3 物理数据模型设计

        决定怎样去实现现实世界的数据管理。
5.3.1 有逻辑数据模型生成物理数据模型

 一样寻常涉及如下内容：（就是一堆SQL语句实现的东西）

5.3.2 创建数据库



例：创建“仓储订货”数据库，其中数据主文件的初始大小为10MB，最大为50MB，增 量为5MB；日志文件的初始大小为5MB，最大为25MB，增量为5MB，并将数据 文件安排在c盘\mssql\data\文件夹下，把日志文件安排在d盘的\mssql\log\文件夹下。
  1. CREATE DATABASE 仓储订货
  2. ON
  3. (NAME = order_dat,
  4.         FILENAME = 'C:\mssql\data\orderdat.mdf',
  5.         SEIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 5MB)
  6. LOG ON  
  7. (NAME = order_log,
  8.         FILENAME = 'D:\mssql\log\orderlog.ldf',
  9.         SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB)
复制代码
下面是建了三个文件组来存放数据文件，然后一个来放日志文件


第六章 数据定义、操纵、完整性约束

6.1 SQL的架构和定义

6.1.1 什么是架构（schema）

        一个逻辑上的概念，是数据库中一组用户对象的逻辑聚集。通过架构（schema）管理对象

6.1.2 定义架构


         架构通常由数据库管理员创建，AUTHORIATION owner_name可以指定架构的管理者（默认是创建者）。

6.1.3 架构的使用（对象的引用格式）


6.1.4 默认架构



6.2 SQL的表定义和完整性定义功能

        主要内容：1.CREATE TABLE命令的根本格式； 2.列的定义； 3.计算列的定义； 4.表级约束
6.2.1 定义表及其完整性约束

1 CREATE TABLE命令的根本格式


2 列的定义（<column_definition>）

        根本形式

   
  
        SQL Server的数据类型


Tips：
3 计算列的定义

 


 ON DELETE 和ON UPDATE少了 SET NULL和SET DEFAULT（因为没有外键约束或参照完整性约束）
4 表级约束

在列的定义后面，比方：
PRIMARY KEY(column1,column2,...)
UNIQUE(column1,column2,...)
5 例子

  1. CREATE TABLE 订货.订购明细(
  2.     订购单号 CHAR(5) FOREIGN KEY REFERENCES 订货.订购单
  3.             ON DELETE CASCADE ON UPDATE CASCADE,
  4.     序号 SMALLINT CHECK(序号>=1),
  5.     器件号 CHAR(8) FOREIGN KEY REFERENCES 基础.器件,
  6.     单价 SMALLMONEY,
  7.     数量 INT CONSTRAINT num CHECK(数量>=0),
  8.     PRIMARY KEY(订购单号,序号)
  9. )
复制代码
6 总结：

在完整性约束中：

6.2.2 修改表结构

例子

  1. /*删除完整性约束*/
  2. ALTER TABLE 基础.职工
  3. DROP CONSTRAINT ref_wh
  5. /*添加完整性约束*/
  6. ALTER TABLE 基础.职工
  7. ADD CONSTRAINT ref_wh
  8. FOREIGN KEY(仓库号) REFERENCES 仓储.仓库(仓库号)
  9. ON DELETE SET NULL
  10. ON UPDATE CASCADE
  12. /*增加和删除字段*/
  13. ALTER TABLE 订货.订购明细
  14. ADD 完成日期 datatime
  16. ALTER TABLE 订货.订购明细
  17. DROP COLUMN 完成日期
复制代码

6.3 SQL数据操纵与完整性约束的作用

6.3.1 插入操纵及其完整性约束

1 根本形式


Tips：
2 例子

  1. INSERT INTO 订货.订购明细 VALUES('OR67',1,'P2',120,5)
复制代码
3 插入操纵涉及的完整性约束：


 6.3.2 删除操纵及其完整性约束

1 根本形式


2 例子

  1. DELETE FROM 仓储.仓库 WHERE 仓库号='WH4'
复制代码
3 删除操纵涉及的完整性约束

        删除操纵只与参照完整性有关，并且只有删除被参照表的记录时，才须要检查参照完整性，这时，系统会根据定义参照完整性时确定的处置惩罚方法（拒绝删除-ON ACTION、控制删除-SET NULL、默认值删除-SET DEFAULT、级联删除-CASCADE）举行处置惩罚
6.3.3 更新操纵及其完整性约束

1 根本形式



2 例子

  1. UPDATE 仓储.仓库 SET 面积=800 WHERE 仓库号='WH1'
复制代码
3 更新操纵涉及的完整性约束

        更新操纵可以看作时先删除旧记录再插入新记录，因此更新操纵的数据完整性检查综合了插入操纵和删除操纵的数据完整性检查。

第七章 SQL查询

7.1 查询语句根本格式

7.1.1 根本格式


   SELECT [ALL|DISTINCT] {*|<  表达式  >,…,<  表达式  >}  
   FROM   <  数据来源  >  
   WHERE <  逻辑表达式  >  
      GROUPBY    <   列名   >[,<   列名   >…]    [HAVING   <   谓词   >]   
       ORDER BY    <   列名   >    [ASC|DESC]   ,[ <   列名   >    [ASC|DESC]]…   
     
  7.2 简朴查询

  7.2.1 简朴无条件查询

  1. SELECT * FROM 基础.职工
  3. /*查询职工的年工资（=工资*12）*/
  4. SELECT 姓名,工资*12 AS 年工资 FROM 基础.职工    --用AS对查询到的列命名，AS可以省略
  6. /*去掉重复行*/
  7. SELECT DISTINCT 班组长 FROM 基础.职工
复制代码
7.2.2 简朴条件查询

  

  如这张图（简朴的大于 小于 BETWEEN IS NULL就不说了）
  1 字符串匹配查询

          格式为：列名[NOT] LIKE 匹配串；
          匹配串类型：可以是字符串常量，也可以含有通配符；
          通配符种类：
  
%（百分号）匹配0或多个字符
_（下划线）匹配1个字符
[]匹配括号中的字符，如[a-f]=[abcdef]
[^]不匹配括号中的字符
  字符串，LIKE后面的匹配串记着加' '。
  1. --查询名字有三个字，且中间是靖的员工
  2. SELECT * FROM 基础员工 WHERE 姓名 LIKE '_靖_'
  4. --查询任意位置包含字符串'DDR'的所有记录
  5. SELECT * FROM 基础.器件 WHERE 规格 LIKE '%DDR%'
  7. --查询前三位是OR6、最后一位为0~9的所有订购单记录
  8. SELECT * FROM 订货.订购单 WHERE 订购单号 LIKE 'OR7[0-9]'
复制代码
2 使用[NOT] IN表达式的查询

          以下两个SQL语句等价
  1. SELECT * FROM 基础.器件 WHERE 器件名称 IN ('内存','鼠标')
  3. SELECT * FROM 基础.器件 WHERE 器件名称 = '内存' OR 器件名称 = '鼠标'
复制代码
7.2.3 存储查询效果

  使用INTO短语将查询效果存储到指定的新表中
  1. SELECT 订购单号,供货方,订购日期 INTO 订货.E3 FROM 订货.订购单 WHERE 经手人 = 'E3'
复制代码
7.2.4 查询效果的排序

  ORDER BY order_expression[ASC|DESC]（默认ASC升序）
  1. --按单价升序列出所有器件信息
  2. SELECT * FROM 基础.器件 ORDER BY 单价
复制代码
！如果ORDER BY column1, column2 DESC，指的是先按column1升序，再按column2降序！
  1. --查询所有订购明细，先按订购单号升序排序，再按金额降序排序
  2. SELECT * FROM 订货.订购明细 ORDER BY 订购单号,金额 DESC
复制代码
7.2.5 TOP短语的作用

           TOP(expression) [PERCENT] [WITH TIES]
   其中expression指出返回多少效果，如果同时选用了PERCENT则按百分比举行计算，如果选用WITH TIES则会取并列，末了返回的数量多于expression。     
  7.3 连接查询

  7.3.1 连接查询的语法格式

  

  内连接：
  

  7.3.2 一样寻常连接

  1. -- 查询在北京或是上海工作的员工的仓库号、职工号、姓名
  2. SELECT 职工.仓库号, 职工号, 姓名
  3. FROM 基础.职工 JOIN 仓储.仓库
  4. ON 职工.仓库号 = 仓库.仓库号
  5. WHERE 城市 IN('北京','上海')
复制代码
如果连接的列名相同，那么就须要表明哪个表的列
  7.3.3 多个表的连接

  

  7.3.4 自连接查询

  只是逻辑上通过取别名的方法，来把一张表分成两张，物理上还是一张表。
  

  7.3.5 外连接查询

  左右全连接要理解！
  

  
  
  7.4 分组及汇总查询

  7.4.1 聚合函数与汇总查询

  1 常见聚合函数

          COUNT（计数）、AVG（平均数）、MIN（最小值）、MAX（最大值）、SUM（求和）
  7.4.2 一样寻常汇总查询

  1 根本格式

  

  2 举例

  1. --找出供应商所在地的数目
  2. SELECT COUNT (DISTINCT 地址) FROM 订货.供应商
  4. --求支付的工资总数、职工人数、所有职工的平均工资、最高工资、最低工资
  5. SELECT SUM(工资) 总工资,COUNT(*) 人数,
  6.     AVG(工资) 平均工资,
  7.     MAX(工资) 最高工资,MIN(工资) 最低工资
  8.   FROM 基础.职工
复制代码
3 COUNT(*)和COUNT(<列名>)的区别

          在聚合函数碰到空值的时间，除COUNT(*)外，其他都跳过空值而只处置惩罚非空值。
  7.4.3 使用GROUP BY的分组汇总查询

  1 格式

  

  2 例子

  1. --查询每个仓库的职工人数和平均工资
  2. SELECT 仓库号,COUNT(*) 人数,AVG(工资) 平均工资
  3. FROM 基础.职工
  4. GROUP BY 仓库号
复制代码

  3 使用GROUP BY + HAVING 的分组汇总查询举例

  

  这里可以看出来，HAVING的用法和WHERE很像，那么可以使用WHERE来代替吗？
  

  
  
  7.5 嵌套查询

  

  7.5.1 普通嵌套查询

  1 两种形式：


  Tip：只有确切知道子查询返回的是单值时，才可以使用比较运算符（>、<、=、>=、<=、<>）。
  2 例子

  1. --查询哪些仓库目前还没有职工
  2. SELECT * FROM 仓储.仓库
  3. WHERE 仓库号 NOT IN
  4.     (SELECT 仓库号 FROM 基础.职工)
  6. --找出哪些城市的仓库向北京的供应商发出了订购单
  7. SELECT 城市 FROM 仓储.仓库
  8. WHERE 仓库号 IN (SELECT 仓库号 FROM 基础.职工
  9.     WHERE 职工号 IN (SELECT 经手人 FROM 订货.订购单
  10.         WHERE 供货方 IN (SELECT 供应商号 FROM 订货.供应商
  11.                 WHERE 地址='北京')))
  12.                
复制代码
7.5.2 使用量词的嵌套查询

  1 格式

  

  2 例子

  

  

  7.5.3 内、外层互相关嵌套查询

  
  

  7.5.4 使用EXISTS的嵌套查询

  

  Tip：EXISTS或NOT EXISTS是用来检查在子查询中是否有效果返回的

   
7.6 须要查询支持的数据操纵

7.6.1 插入

  1. INSERT INTO 仓储.库存
  2.     SELECT 'WH3',器件号,NULL FROM 基础.器件
  3. --第一列、第二列是常量，第二列时从器件表查询到的器件号，查询结果插入库存表
复制代码
7.6.2 更新

  1. UPDATE 订货.订购单 SET 金额 =
  2. (SELECT SUM(金额*数量) FROM 订货.订购明细
  3. WHERE 订购单号 = 订购单.订购单号)
复制代码
7.6.3 删除

  1. DELETE FROM 订货.供应商 WHERE 供应商号 NOT IN
  2. (SELECT 供货方 FROM 订货.订购单 WHERE 供货方 IS NOT NULL)
  3. --删除现在没有任何订购单记录的供应商记录
复制代码

7.7 视图及其操纵

7.7.1 视图的创建和使用


1 行列子集视图

        从单个根本表选取某些行和某些列，并且包含根本表的关键字所定义的视图
  1. CREATE VIEW emp_v1 AS
  2.         SELECT 职工号,仓库号,姓名
  3.         FROM 基础.职工
复制代码
视图是捏造表，所有对视图的操纵实际上都要转换成对根本表的操纵！ 
实验查询：等价于AS后面的SELECT语句；
  1. SELECT * FROM emp_v1
复制代码

同时，也可以对视图举行插入、删除、更新等，这里就提一下插入：
  1. INSERT INTO emp_v1 VALUES('E13','WH1','张三')
复制代码
 等价于对根本表中，被选取的职工号、仓库号、姓名这三列举行插入，其他的列会为NULL或DEFAULT，这时大概会因为完整性约束而导致插入失败。
2  WITH CHECK OPTION的作用


 3 基于多个表的视图

  1. CREATE VIEW emp_v2 AS
  2. SELECT 仓库.仓库号,城市,职工号,姓名
  3. FROM 基础.职工 JOIN 仓储.仓库 ON 职工.仓库号 = 仓库.仓库号
复制代码
 之后查询：
  1. SELECT 职工号,姓名 FROM emp_v2
  2. WHERE 城市 = '北京'
复制代码
            这种涉及多个根本表的视图，插入删除更新操纵是不允许的。  4 包含虚列的视图

  1. --定义视图的时候可以指定列名，只是一般都直接用基本表的名字，所以省略了
  2. CREATE VIEW emp_v3(职工号,姓名,月工资,年工资) AS
  3. SELECT 职工号,姓名,工资,工资*12
  4. FROM 基础.职工
复制代码
         计算列只能查询、不能操纵

7.7.2 视图的修改和删除

 修改：

删除：

7.7.3 视图的作用


第八章 数据库编程基础

8.1  Transact-SQL

变量声明
        DECLARE @<变量名> <数据类型> [,@<变量名> <数据类型>...]
         注意：变量名前必须有@前缀，以便和关系的属性名相区分
赋值
           SELECT @local_variable=expression[,@local_variable=expression…]             FROM <表名>|<视图名> …     
  8.2 游标与SQL的宿主使用

   
  8.2.1 游标的使用

  1 声明游标（文件）

        DECLARE <游标名> CURSOR FOR <SELECT-查询>
游标可以看作是一个临时存储或临时文件，它的内容就是SELECT语句的查询效果。
  1. DECLARE wh_cursor CURSOR FOR
  2. SELECT 仓库号,城市,面积 FROM 仓储.仓库
复制代码
2 打开游标

           OPEN <  游标名  >    实验该语句意味着实验CURSOR语句中的SELECT查询，并使游标指针指向查询效果的第一条记录。只能打开已经声明但还没有打开的游标。
  1. OPEN wh_cursor
复制代码
3 从游标中读数据

           FETCH <游标名  > [INTO <  主变量  1  >,   <主变量  2  > …]   INTO后的主变量要与游标中的SELECT的字段相对应。
该语句的功能是取出游标的当前记录并送入主变量，同时使游标指针指向下一条记录。
使用全局变量@@FETCH_STATUS来判断FETCH语句对游标的操纵状态

循环是否继续读取游标： WHILE @@FETCH_STATUS=0
  1. FETCH FROM wh_cursor INTO @whno,@city,@area
  2. WHILE @@FETCH_STATUS=0
  3. BEGIN
  4.     PRINT @whno+' '+@ciry+STR(@area,4)
  5.     FETCH FROM wh_cursor INTO @whno,@city,@area
  6. END
复制代码
4 关闭游标

        CLOSE <游标名>
此时系统并没有完全释放游标，可以再次OPEN打开
  1. CLOSE wh_cursor
复制代码
5 释放游标

        DEALLOCATE <游标名>
释放以后就无法再OPEN打开了
  1. DEALLOCATE wh_cursor
复制代码
 8.2.2 游标概念归纳


游标还可以嵌套：


8.3 存储过程

        来源于C/S服务器数据库体系结构，在C/S结构的数据库中，数据库端还可以存放步伐，因为这些步伐以数据库对象的形式存储在数据库中，以是称为存储过程。
8.3.1 存储过程的作用



8.3.2 存储过程的创建与使用


几点说明：

 8.3.2 实验存储过程


8.3.3 存储过程的修改和删除


8.3.4 例子

使用带有参数和返回值的简朴过程查询指定仓库订单金额大于指定值的订单数，查询效果通过RETURN语句返回
  1. --创建
  2. CREATE PROCEDURE useGetOrderNum
  3. @whno char(6), @sum money
  4. AS
  5. DECLARE @count int
  6. SELECT @count = COUNT(*) FROM 订货.订购单
  7. WHERE 金额>=@sum AND 经手人 IN
  8. (SELECT 职工号 FROM 基础.职工 WHERE 仓库号 = @whno)
  9. RETURN @count
  12. --执行
  13. DECLARE @count int
  14. EXECUTE @count = uspGetOrderNum 'WH1',1000
  15. PRINT 'WH1仓库金额在1000以上的订单数是：'+STR(@count)
复制代码

8.4 触发器及其用途


 8.4.1 概念

        触发器和存储过程都是事先设计好存储在数据库中的，但是不同的是，触发器不须要专门调用或实验。（有DML（数据操纵语言）、DDL（数据定义语言）、LOGIN触发器）
1. DML触发器依附于表（或视图）。
2. DML触发器分为

 3. 触发器的三个要素：

 8.4.2 创建触发器


8.4.3 删除触发器

 


8.4.4 重点！

1 触发器FOR和INSTEAD OF的区别


2 deleted表和inserted表


它们的作用：

8.4.5 触发器的应用举例

1 使用包含提醒消息的DML触发器
  1. CREATE TRIGGER reminder1
  2. ON 仓储.库存
  3. FOR UPDATE
  4. AS
  5. DECLARE @amount int
  6. SELECT @amount=数量 FROM
  7. inserted
  8. IF @amount<5
  9. RAISERROR (‘库存数量已经小于5！’, 16, 10) --报错语句
复制代码
 


2  由于CHECK约束只能定义列级或表级约束，表间的任何约束或业务规则都必须通过定义触发器来实现。
   使用DML触发器实现表之间逼迫业务规则“如果当地 有供应商则订购单不许发往异地”。
  1. CREATE TRIGGER isSameCity ON 订货.订购单
  2. FOR INSERT,UPDATE AS
  3. DECLARE @city char(10), @addr char(20), @sno char(5)
  4. --从新欸顶订货单记录得到供货方和经手人信息
  5. SELECT @sno = 供货方, @eno = 经手人 FROM inserted
  6. --若指定了供应商则做如下操作
  7. IF @sno IS NOT NULL
  8. BEGIN --得到发出该订单仓库所在城市
  9.     SELECT @city = 城市 FROM 仓储.仓库 WHERE 仓库号 IN
  10.     (SELECT 仓库号 FROM 基础.职工 WHERE 职工号 = @eno)
  11.     --得到供应商的地址
  12.     SELECT @addr = 地址 FROM 订货.供应商 WHERE 供应商号 = @sno
  13.     --如果仓库和供应商在不同城市，且存在同城的供应商则拒绝
  14.     IF @city!=@addr AND
  15.         EXISTS (SELECT * FROM 订货.供应商 WHERE @city = 地址)
  16.         BEGIN
  17.             RAISERROR('本地有供应商，订购单不许发往异地！',16,1)
  18.             ROLLBACK TRANSACTION
  19.         END
  20. END
复制代码

第九章 数据库安全

9.1 安全性概述

        包管数据库安全是对数据库系统的根本要求。
        数据库安全的焦点标题：防止数据被非法使用和恶意粉碎，是要防范非法用户的故意偷取和粉碎（人为）
9.1.1 安全性步伐的层次


 数据库安全的焦点标题是：身份识别。
9.1.2 数据库管理系统的安全功能

1 访问控制



2 数据加密

3 管好数据库的安全是DBA的紧张职责

9.1.3 数据库管理系统的身份验证模式

1 登任命户的主要来源


 




 9.2 用户管理

9.2.1 登任命户和数据库用户


9.2.2 登任命户管理

        谁来管理？
1 创建登任命户   




2 修改登陆用户

  



3 删除数据库用户

 


9.2.3 数据库用户管理

        要访问哪个数据库，就必须在此数据库下创建与登任命户相对应的数据库用户
1 创建数据库用户

在已经 USE 本数据库之后，创建角色不须要指定命据库



2 修改数据库用户属性



3 删除数据库用户



9.2.4 数据库用户的分类





9.3 角色管理

        角色一样寻常分为：系统管理员角色、背景数据管理角色、客户角色
9.3.1 用户和角色的关系

        1.可以将用户指定为角色的成员
        2.用户自动从角色继承权限
        3.可以取消用户的角色成员资格
9.3.2 Public角色


9.3.3 角色管理

1 定义角色（CREATE ROLE）



 2 指定用户角色（sp_addrolemember）

        数据库管理中角色代表了一种只能，，每个数据库用户可以继承一个或多个角色，为此须要将数据库用户指定为数据库角色的成员。


3 取消用户角色（sp_droprolemember）



4 修改角色名称（ALTER ROLE）


5 删除角色（DROP ROLE）



9.3.4 系统预定义角色



1 系统管理预定义角色


 a.指定系统预定义角色



b.取消系统角色成员资格

先切换到sa用户


2 数据库预定义角色


a.数据库预定义角色的指定




9.4 权限管理

        系统初始只有一个sa用户，每个数据库用户的权限都来源于sa
        通过授权GRANT，收回授权REVOKE动态管理权限
9.4.1 对象权限管理


1 授予对象操纵权限




例题：仓储订货”数据库的安全控制办理方案
   一个  DBA   

  1. --将wang指定为”仓储订货“的DBA
  2. sp_addrolemember 'db_owner','wang'
  4. --授权所有用户都可以查询除职工工资以外的所有信息
  5. GRANT SELECT ON 仓储.仓库 TO public
  6. GRANT SELECT ON 仓储.库存 TO public
  7. GRANT SELECT ON 基础.职工(职工号,仓库号,姓名,班组长) TO public
  8. GRANT SELECT ON 基础.器件 TO public
  9. GRANT SELECT ON 订货.供应商 TO public
  10. GRANT SELECT ON 订货.订购单 TO public
  11. GRANT SELECT ON 订货.订购明细 TO public
  13. --建立角色store_man和order_man，然后按要求授权
  14. CREATE ROLE store_man
  15. CREATE ROLE order_man
  17. GRANT INSERT,UPDATE(数量),DELETE ON 仓储.库存 TO store_man
  19. GRANT INSERT,UPDATE(经手人,供货方,订购日期,金额),DELETE ON 订货.订购单 TO order_man
  20. GRANT INSERT,UPDATE(数量,单价),DELETE ON 订货.订购明细 TO order_man WITH GRANT OPTION
  22. --指定用户zhang为角色store_man的成员，指定wu为角色order_man的成员
  23. sp_addrolemember 'store_man','zhang'
  24. sp_addrolemember 'order_man','wu'
  26. --授权用户zhang可以对”器件“表进行插入操作
  27. GRANT INSERT ON 基础.器件 TO zhang WITH GRANT OPTION
  29. --授权zhang可以对”器件“表进行删除操作
  30. GRANT DELETE ON 基础.器件 TO zhang
复制代码
在这之后，登陆zhang，给huang授权向基础.器件的插入、删除操纵，插入可以成功，因为有 WITH GRANT OPTION。
但是，以wu的身份给huang授权：
  1. GRANT INSERT,DELETE ON 订货.订购明细 TO huang
复制代码
这时就不能成功了，因为wu的这个权限是从order_man身上继承下来的，继承要用AS短语说明以哪个角色举行授权，以是应该：
  1. GRANT INSERT,DELETE ON 订货.订购明细 TO huang AS order_man
复制代码
2 收回对象操纵权限


  1. REVOKE INSERT ON 基础.器件 FROM huang
复制代码
同样，如果以wu的身份收回对huang的授权，也无法收回，因为wu是以order_man角色完成授权的，要加AS：
  1. REVOKE INSERT ON 订货.订购明细 FROM huang AS order_man
复制代码
9.4.2 架构权限管理

        架构：对象的容器
        通过用户管理对象，删除用户时，通过该用户建的表或对象都要删除，为安全管理带来很多贫苦
        通过架构管理对象的特点：

 1 架构权限的授予



  1. --用户wang以DBA身份将对架构”仓储“的INSERT权限授予zhang
  2. GRANT INSERT ON SCHEMA :: 仓储 TO zhang
  4. --用户wang以DBA身份授予zhang在”仓储“架构上的CONTROL权限
  5. GRANT CONTROL ON SCHEMA :: 仓储 TO zhang
复制代码
2 架构权限的收回


  1. --收回用户zhang在架构”仓储“上的INSERT权限
  2. REVOKE INSERT ON SCHEMA :: 仓储 FROM zhang
复制代码
3 转移架构所有权


  1. --将”器件“表的所有权传递给用户wu
  2. ALTER AUTHORIZATION ON 基础.器件 TO wu
  4. --将”基础“架构的所有权传递给用户tang
  5. ALTER AUTHORIZATION ON SCHEMA :: 基础 TO tang
  7. --将”器件“表的所有权传递给所属架构的所有者
  8. ALTER AUTHORIZATION ON 基础.器件 TO SCHEMA OWNER
复制代码
d 在架构之间移动对象


  1. --将器件表从基础架构移动到仓储架构
  2. ALTER SCHEMA 仓储 TRANSFER 基础.器件
复制代码
9.4.3 授予语句权限


1 授予语句权限




授权可以成功，但是只有用户zhang在”仓储“架构下可以创建表，tang在”基础“架构下可以创建表，wu在两种架构下都无法创建表。
2 收回语句权限


3 总结

一个用户的权限包括：

 9.4.4 禁止继承权限


实验DENY X TO A的效果

 主观题

   设  wang  为“门生”数据库用户，系统管理员兼数据库管理员sa创建  role1  角色，并指定  wang  为角色成员，  sa  授予角色role1查询表  t7  的权限；除此之外，  sa  授予  wang  如下权限：查询表t1  、  t2  ，向表  t3  、  t4  插入数据；更新表  t5  、  t6  ，随后禁止wang对  t5  举行更新；撤销角色  role1  对表  t7  的查询权限再授权的权利。叨教最终wang  具有哪些权限？     
  
   
  第十章 事务管理与并发控制

  10.1 事务管理

  10.1.1 事务的性质（ACID特性）

  
  10.1.2 SQL对事务的支持

1 开始事务

        BEGIN TRANSACTION
2 结束事务

        成功：COMMIT TRANSACTION
        失败：ROLLBACK TRANSACTION（回到事务起始点）
 3 事务保存点

        保存：SAVE TRANSACTION savepoint_name
        取消：ROLLBACK TRANSACTION savepoint_name
   事务和步伐是两个概念。一样寻常来讲，一个步伐中包含多个事务。一个事  务可以是一条SQL语句 、一组SQL语句或整个步伐。  10.1.3 隐含事务与自动提交



10.2 并发控制

10.2.1 干扰标题（并发事务中的不同等标题）

1 丢失更新


2 未提交依靠（读”脏“数据）

           查询一个已经被其他事务更新、但尚未提交的元组，将会引起未提交依靠标题。     
  3 不同等分析（不可重复读--更新）

           连续两次或多次读数据举行校验和分析，效果由于其他事务的干扰，使得前后效果不同等，从而产生校验错误（即不同等的分析）。   
  4 幻像读标题（--插入/更新）

           与不同等分析标题有关，当事务A  读数据时，事务  B  在对同一个关系举行插入或删除操纵，这时事务A  再读同一条件的元组时，会发现神秘地多出了一些元组或丢失了一些元组，把这种征象称作幻象读   
  10.2.2 可串行性


判断


        可串行性描述的是事务的隔离性。 
 10.2.3 封锁


1 封锁机制

a.共享封锁（读）

        读封锁，简称S锁； 在读数据的时间，不允许其他用户对该数据举行任何修改；

b.独占封锁（修改操纵，最严格）

        排他锁，简称X锁； 举行修改操纵时，拒绝来自其他用户的任何封锁，但不拒绝一样寻常的查询（即不能避免脏读）

c.更新封锁（更新）

        防止其他用户在同一时刻修改同意记录

有些封锁操纵完成绩释放，有些事务结束才释放

2 SQL Server中与封锁有关的命令

        WITH(<table_hint>)
关键字：
TABLOCK对表共享封锁，读完立刻释放可以避免脏读，但不具有可重复读
HOLDLOCK与TABLOCK一起用，将共享锁持续到事务结束包管可重复读
NOLOCK不举行封锁，仅应用于SELECT大概会读取未提交的数据，导致脏读
TABLOCKX实施独占封锁
UPDLOCK实施更新封锁可以对其他记录实施共享封锁，但是不允许对表实施共享封锁和独占封锁
10.2.4 死锁

1 产生死锁的原因



2 避免死锁


3 发现死锁息争决死锁


 10.2.5 活死锁（活锁）

        没有发生死锁也有大概某个事务永远处于期待状态
 

避免活锁：先来先服务（排队）
10.2.6 隔离级别

隔离级别封锁脏读不可重复读幻象丢失更新
未提交读（READ UNCOMMITTED）NOLOCK是是是是
提交读（READ COMMITTED）TABLOCK否是是是
可重复读（REPEATABLE READ）TABLOCK+HOLDLOCK否否否是
可串行化（SERIALIZABLE）TABLOCKX 或UPDLOCK否否否否
1 设置隔离级别的命令




第十一章 数据库存储管理与数据规复

11.1 数据库存储管理与存储优化

11.1.1 SQL Server数据库的存储结构


优化存储的本领

 11.1.2 调解数据库


  1. --为”仓储订货“数据库增加一个5M大小的物理文件
  2. ALTER DATABASE 仓储.订货
  3. ADD FILE
  4. (NAME = Test1dat2,
  5. FILENAME = 'C:\mssql\data\t1dat2.ndf',
  6. SIZE = 5MB,
  7. MAXSIZE = 100MB,
  8. FILEGROWTH = 5MB)
  10. -- 将”仓储订货“数据库test1dat2所对应的物理文件增加到10M大小
  11. ALTER DATABASE 仓储.订货
  12. MODIFY FILE
  13. (NAME = test1dat2,
  14. SIZE = 10MB)
  16. -- 将“仓储订货”数据库的test1dat2文件改名为test1dat1
  17. ALTER DATABASE 仓储.订货
  18. MODIFY FILE
  19. (NAME = Test1dat2,
  20. NEWNAME = Test1dat1)
  22. -- 将“仓储订货”数据库的test1dat1文件删除
  23. ALTER DATABASE 仓储订货
  24. REMOVE FILE test1dat1
复制代码
例：将“仓储订货”数据库的主数据文件orderdat/mdf（逻辑文件名是order_dat）移动到
d:\mssql\data目次下
实现步骤：

11.1.3 文件组


1 文件组的目标


2 文件组的概念 


例6 创建一个数据库MyDB，该数据库包括一个主数据文件、一个用户定义文件组和一个日志文件。



怎样验证MyTable及其数据存储到了指定物理文件？
添加文件组 

 添加物理文件

11.1.4 分区


 

 1 创建分区表的步骤











11.1.5 索引


 











11.2 备份与规复


11.2.1 故障类型


11.2.2 备份类型


11.2.3 日志的概念


11.2.4 规复模型


'ALTER DATABASE database_name SET RECOVERY FULL' 修改为完全规复。
规复的时间不允许操纵，备份的时间允许操纵。 
11.2.5 备份和规复计谋

1 备份的类型


 






2 动态备份和静态备份


 11.2.6 备份操纵




例子

系统数据库也要备份，必须经常性地、定期地备份master数据库（只能举行全备份）
11.2.7 规复或还原











期末加油！


免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。



欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/) Powered by Discuz! X3.4