数据库基础知识点

一、数据库特点：

 永久储存 有组织 可共享
 功能：
  1.数据定义功能：
   (1)数据定义语言DDL
  2.数据操纵功能：
   (2)数据操纵语言DML
   //(3)数据控制语言DCL
  3.数据组织、储存和管理：
   数据组织和存储的基本目标是提高存储空间利用率和方便存取，提供多种存取方法（如索引查找、hash查找、顺序查找等）来提高存取效率
  4.数据库事务管理和运行管理
  5.数据库的建立和维护功能
  6.其他功能
二、概念模型：
 （1）实体：客观存在并可相互区别的事物
 （2）属性：实体所具有的某一特性
 （3）码：唯一标识实体的属性集
 （4）实体型：用实体名及其属性名集合来抽象和刻画的同类实体
 （5）实体集：同一类型的实体的集合
 （6）联系：
   实体间的联系
    1)两个实体型间的联系
    2)两个以上实体型间的联系
    3)单个实体型内的联系
     都存在 一对一联系 1：1 一对多联系 1：n 多对多联系 m：n
    一般的，把参与联系的实体型的数目称为联系的度。N个实体型间的联系度为N，也称N元联系
 概念模型的一种表示方法：
   E - R模型
    (1)实体型用矩形表示
    (2)属性用椭圆形表示，并用无向边将其与相应的实体型连接起来
    (3)联系用菱形表示
三、数据模型：
 由数据结构、数据操作和数据的完整性约束条件三部分组成
   数据结构  描述数据库的组成对象以及对象之间的联系
   数据操作  对数据库中各种对象（型）的实例（值）允许执行操作的集合，包括操作及有关的操作规则
   数据的完整性约束条件  是一组完整性规则
 
 常用数据模型：
   1.层次模型（树）
     优点：
      （1）数据结构比较清晰简单
      （2）层次数据库的查询效率高
      （3）层次数据模型提供了良好的完整性支撑
     缺点：
      （1）现实世界中很多联系时非层次的
      （2）对插入和删除操作的限制比较多
      （3）查询子女节点必须通过双亲结点
      （4）层次命令趋于程序化

   2.网状模型（图）
     优点：
      （1）能够更直接地描述现实世界
      （2）具有良好的性能，存取效率高
     缺点：
      （1）结构复杂，随着应用环境的扩大，数据库的结构变得越来越复杂，不利于最终   用户掌控
      （2）网状模型的DDL DML复杂，并且要嵌入一门高级语言中，用户不易掌握，不容 易使用
      （3）记录间的联系是通过存取路径实现的，应用程序在访问数据的时候必须选择适   当的存储路径，因此用户必须了解系统结构的细节，加重了编写应用程序的负担
    层次模型和网状模型统称为格式化模型
   
   3.关系模型（表）
    数据模型：
      关系：一个关系对应一张表
      元组：表中的一行为一个元组
      属性：表中的一列为一个属性
      码：也称码建，表中的某个属性组
      域：一种具有相同数据类型的值的集合
      分量：元组中的一个属性的值
    对关系的描述一般表示为：
      关系名（属性1，属性2，...，属性n）
     关系模型要求关系必须是规范化的，关系的每一个分量必须是一个不可分的数据项
    完整性规则：
       实体完整性 主属性不能为空
       参照完整性
       用户定义完整性
   

   //面向对象数据模型（对象）
   //对象关系数据模型
   //半结构化数据模型
  
四、数据库系统的三级模式二级映像功能
   型：对某一数据的结构和属性的说明
   值：型的一个具体赋值
  模式仅涉及型的描述，是数据库中全体数据的逻辑结构和特征的描述
  模式的一个具体值称为模式的一个实例，模式是相对稳定的，实例是相对变动的
 三级模式结构：
   1.模式
    数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图
   2.外模式
    也称子模式或是用户模式，它是数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述，是数据库用户的数据视图，是与某一应用有关的数据的逻辑表示。
   3.内模式
    也称存储模式，一个数据库只有一个内模式。是数据物理结构和存储方式的描述，是数据在数据库内部的组织方式。
 二级映像功能：
   1.外模式/模式映像
    数据的逻辑独立性
   2.内模式/模式映像
    数据的物理独立性

五、关系模型三要素：数据结构 操作集合 完整性约束
 域
 笛卡儿积
 关系：D1*D2*D3*...*Dn的子集叫做在域D1, D2..., Dn, 上的关系，表示为R(D1,D2,...,Dn),R表示关系的名字，n是关系的目或度（属性的个数）
   n=1时，称单元/目关系
   n=2时，称二元/目关系
  关系是张二维表，每行代表一个元组，每列代表一个域。n目关系必有n个属性
  若关系中某个属性组能唯一地标识一个元组，而其子集不能，则称该属性组为候选码
  若一个关系有多个候选码，则选定其中一个为主码
  候选码中的诸属性称为主属性。不包含在任何候选码中的属性称为非主属性或非码属性
 属性 候选码 主码

六、关系代数：
  
  并 ：R U S = {t|t∈R V t∈S}
  交 ：R - S = {t|t∈R^t∈S }
  差 ：R - S = {t|t∈R^t!∈S}
  广义笛卡儿积：R*S = {tr ts|tr∈R^ts∈S}
  专门的关系运算：
   选择 ：在关系R中选择满足给定条件的元组
   投影 ：从R中选择出若干属性列组成新的关系
   连接：从两个关系的笛卡儿积中选择属性间的满足一定条件的元组
    等值连接：从关系R与S的广义笛卡儿积中选取A、B属性值相等的那些元组
    自然连接：一种特殊的等值连接
    自然链接时被舍弃的元组称为悬浮元组，如果把悬浮元组也保存在结果关系中，而在其他属性上填空值，那么这种连接就叫做外连接。  如果只保留左边关系R中的悬浮元组就叫做左连接，  如果只保留右边关系S中的悬浮元组就叫做右外连接
   除运算：设关系R除以关系S的结果为关系T，则T中包含所有在R但不在S中的属性及其值，且T的元组与S的元组的所有组合都在R中

七、SQL概述及语句
  *集数据定义语言DDL:create drop alter 数据操纵语言DML：update delete insert 数据控制语言DCL : grant revoke 于一体
  *高度非过程化
  *面向集合的操作方式
  *以同一种语法结构提供两种使用方法：能独立用于联机交互，是嵌入式语言
 1.模式的定义和删除：
   create schema<模式名>authorization<用户名>
  用户可以在创建模式的同时在这个模式中进一步创建基本表、视图，定义授权等
   drop schema<模式名><cascade|restrict>
  其中cascade和restrict两者必选其一。选择cascade（级联），表示在删除模式的同时把该模式中的数据库对象全部删除。选择了restric（限制），表示如果该模式中已经定义了下属的数据库对象（如表、视图等），则拒绝该删除语句的执行。
 2.表的定义、删除及修改
  create table<表名>(<列名><数据类型>[列级完整性约束条件]
       [, <列名><数据类型>[列级完整性约束条件]]
       [, <列名><数据类型>[列级完整性约束条件]]
       ...
       [, <列名><数据类型>[列级完整性约束条件]])
   模式与表：
    要在S - T中定义表
     1.create table "S-T".student(...)
     2.在创建模式时就创建表
     3.设置所属模式，当用户创建基本表时若没有指定模式，系统根据搜索路径来确定该对象所属模式
     show search_path 显示当前的搜索路径
     set search_path to "S_T",public  设置搜索路径
  alter table<表名>
  [add[column]<新列名><数据类型>[完整约束性]]
  [add<表级完整性约束性>]
  [drop[column]<列名>[cascade|restrict]]
  [drop constraint<完整性约束名>[restrict|cascade]]
  [alter column<列名><数据类型>]

  drop table<表名>[restrict|cascade]
   若选择restrict，则该表的删除是有限制条件的，欲删除的表不能被其他表的约束所引用（如check，foreign key等约束），不能有视图、触发器、存储过程等。
   若选择cascade，则该表的删除没有限制条件。在删除表的同时，相关的依赖对象都将被一起删除
   默认restrict
 3.索引的建立和删除
  当表的数据量比较大时，查询操作会比较耗时，建立索引时加快查询速度的有效手段。
  create[unique][cluster]index<索引名>
   on <表名>(<列名>[<次序>][,<列名>[<次序>]]...)
   unique表明此索引的每一个索引值只对应唯一的数据记录
   cluster表示要建立的索引时聚簇索引 聚簇：为了提高某个属性的查询速度，把这个或这些属性上具有的相同值的元组集中存放在连续的物理块中称为聚簇。
  alter index<旧索引名>rename to<新索引名>
  drop index<索引名>
 4.数据查询
  select[all | distinct]<目标表达式>[,<目标表达式>]...
  from<表名或视图名>[, <表名或视图名>...] | (select 语句)[as]<别名>
  [where<条件表达式>]
  [group by<列名1>[having<条件表达式>]]
  [order by<列名2>[asc|desc]]
  常用查询条件：
   =,>,<,>=,<=,!=,<>,!>,!<,not+上述比较运算符
    select * from student where sage<20
   between and, not between and
    select * from student where sage (not) between 20 and 23
   in,ont in
    select * from student where sdept (not) in('CS','MA','IS')
   is null,is not null
   and,or,not
   like
   [not]like'<匹配串>'[escape'<换码字符>']
    select * from course where cmane like 'DB\_Design' escape'\'
   escape'\'表示"\"为换码字符，这样匹配串中紧跟在"\"后的字符"_"不再具有通配符的含义
    select * from student where sname like'刘%'
  通配符：
    % (百分号)代表任意长度（可为0）的字符串。a%b表示以a开头以b结尾的字符串
    _（下横线）代表任意单个字符。a_b表示以a开头，以b结尾长度为3的字符串
  聚集函数
    count(*) 统计元组个数
    count([distinct|all]<列名>)  统计一列中值的个数
    sum([distinct | all]<列名>)  计算一列值得总和（此列必须是数值型）
    avg([distinct | all]<列名>)  计算一列值的平均值（此列必须是数值型）
    max([distinct | all]<列名>)  求一列中的最大值
    min([distinct | all]<列名>)  求一列中的最小值
      如果制定distinct短语，则表示在计算时要取消指定列中的重复值。默认为all
     select count(*) from student
     select count(distinct sno) from sc
     select avg(grade) from sc where cno = '1'
     select sum(grade) from sc, course where sno = '201215012' and sc.cno=course.cno
    当聚类函数遇到空值时，除count(*)外，都只跳过空值而只处理非空值
  连接查询
   等值与非等值连接
    select student.*,sc.* from student,sc where student.sno=sc.sno
   自身连接
    当course表中只有每门课的直接先修课消息，而没有先修课的先修课时，要得到这个信息就要使用自身连接
    select first.cno,second.cpno from course first,course second where first.cpno=second.cno
   外连接
    inner join = join
    仍把悬浮元组保存在结果中
    select * from student left outer join(左外连接) sc on(或using)(student.sno=sc.sno)
    左外连接(LEFT OUTER JOIN或LEFT JOIN)、右外连接(RIGHT OUTER JOIN或RIGHT JOIN) 和全外连接(FULL OUTER JOIN或FULL JOIN)
   多表连接
    select student.sno,sname,cname,grade from student,sc,course where student.sno=sc.sno and sc.cno=sourse.cno
   嵌套查询：
    带有in的子查询
    带有比较运算符的子查询
    带有any或all谓词的子查询
    带有exists谓词的子查询
    （不）相关子查询：子查询条件（不）依赖于父查询
    
  集合查询
   集合操作主要包括并操作union、交操作intersect、差操作except
  基于派生表的查询
   子查询不仅可以出现在where字句中，还可以出现在from字句中，这时子查询生成的临时派生表成为主查询的查询对象
   select cno, cno fromsc, (select cno, avg(grade)from sc group by sno)as avg_sc(avg_sno,avg_grade) where sc.sno=avg_sc.avg_sno and sc.grade>=avg_sc.avg_grade
   如果子查询中没有聚集函数，派生表可以不指定属性，子查询select字句后面的列名为其默认属性。通过from字句生成派生表时，as关键字可以省略，但必须为派生关系指定一个别名
 5.数据更新
  插入数据
   插入元组
    insert into<表名>[(<属性列1>[, <属性列2>]...)] value(<常量1>[,<常量2>...])
   插入子查询结果
    insert into<表名>[(<属性列1>[, <属性列2>]...)]子查询
  修改数据
   update<表名> set<列名>= <表达式>[, <列名>= <表达式>]...[where<条件>]
   其功能是修改指定表中满足where字句条件的元组，其中set字句给出<表达式>的值用于取代相应的属性列值。如果省略where字句，则表示要修改表中的所有元组
   update student set sage = 22 where sno = '201215121'
   update student set sage=sage+1
  删除数据
   delete from<表名>[where<条件>]
   若省略where则删除表中的全部元组
 6.空值的处理
  (1)产生：插入元组时，外连接
  (2)空值的判断：用is null或 is not null
  (3)空值约束条件：属性定义中有not null约束条件的不能取空值，加了unique限制的属性不能取空值，码属性不能取空值
  (4)空值的算术运算、比较运算和逻辑运算
   空值与另一个值的比较结果为unknown，在查询语句中，只有使where和having子句中的选择条件为true的元组才被选出作为输出结果
 7.视图
  建立：
   create view<视图名>[(<列名>[,<列名>])] as<子查询>[with check option]
   其中子查询可以是任意select语句，是否可以含有order by字句和distinct短语，则取决于具体系统的实现
   with check option 表示对视图进行update、insert的delete操作时要保证更新、插入和删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）
   组成视图的属性列名或者全部省略或者全部指定，没有第三种选择。如果省略了视图的各个属性列名，则隐含该视图由子查询中select字句目标中的诸字段组成，但在下列三种情况下必须明确指定组成视图的所有列名：
    (1)某个目标不是单纯的属性名，而是聚集函数或列表达式
    (2)多表连接时选出了几个同名列作为视图的字段
    (3)需要在视图中为某个列启用新的更合适的名字
   create view IS_student as select sno, sname, sage from student where sdept = 'IS'
   视图不仅可以建立在一个或多个基本表上，也可以建立在一个或多个已经定义好的视图上或建立在基本表与视图上。若某个视图是从单个表上导出的，并且只是去掉了基本表的某些行和某些列，但保留了主码，则称这类视图为行列子集视图。
   带虚拟列的视图(帯表达式的视图)、分组视图
  删除：
   drop view<视图名>[cascade]
  查询：
   与表查询类似
  更新：
   (1)由于视图时不实际的存储数据的虚表，因此对此视图的更新最终要转换为对基本表格的更新
   (2)并不是所有视图都能更新，因为有些视图的更新不能唯一地有意义地转换成相对应基本表的更新
   (3)一般的，行列子集视图是可更新的。
  视图的作用：
   (1)简化用户操作
   (2)使用户能以多种角度看待同一数据
   (3)对重构数据库提供了一定程度的逻辑性
   (4)能够对机密数据提供安全保护
   (5)适当利用视图可以更清晰地表达查询
 8.授权：授予与收回
  授予：
   GRANT <权限>[, <权限>] …[ON <对象类型> <对象名>]
   TO <用户>[, <用户>] …[WITH GRANT OPTION];
    说明：
     WITH GRANT OPTION子句：若指定了该子句，则获得某种权限的用户还可以把该权限再授予别的用户；若没有指定该子句，则获得某种权限的用户只能使用该权限，不能传播该权限。
  收回：
   REVOKE <权限>[, <权限>]…[ON <对象类型> <对象名>]
   FROM <用户>[, <用户>] …
 9.嵌入式SQL
  SQL语言提供了两种不同的使用方式
  交互式：作为独立语言在终端交互方式下使用，这是面向集合的描述性语言，是非过程性的。
  嵌入式：将SQL语言嵌入到某种高级语言中，利用高级语言的过程性结构实现事务处理。
 10.存储过程
  创建存储过程的语法：
   create procedure 存储过程名[{@参数名 数据类型}[=default][output]
   [{@参数名 数据类型}[=default][output]
   AS
   Select 语句
  如 CREATE PROCEDURE xs AS select * from student
   
   create procedure student_grade3
   @student_name char(10),
   @course_name char(20) = '数据库原理'
   as
   select sname, cname, grade
   from student s inner join sc on s.sno = sc.sno
   inner join course c on c.cno = sc.cno
     where sname = @student_name
     and cname = @course_name
  执行：
   exec 存储过程名
    exec xs
    exec student_grade3 '王五'
    exec student_grade3 @student_name = ‘王五‘, @course_name = ‘数据库原理’
  修改：
   alter procedure 存储过程名 as select
  删除：
   drop procedure 存储过程名
 11.触发器
  触发器是一种特殊的存储过程。它与表紧密相连，主要用来保证数据的完整性。
  语句： create trigger <触发器名> on <表名> for[before|after][instead of][insert | delete | update]
   AS
   SQL语句
   If update 测试insert 和update语句是否对指定字段有影响
   rollback

  触发器的建立必须是批处理中的第一条语句。触发器建立好后，由SQL Server系统自动执行。（与索引一样）
  一个表中可以建立很多个触发器。一般一种操作建立一个。
  删除触发器：
   drop trigger 触发器名
  修改触发器：与建立语句一样。
八、关系数据理论(关系规范化理论)
 数据依赖：
  是一个关系内部属性与属性之间的一种约束关系，通过属性间值的相等与否体现出来的数据间相关联系其中最重要的是函数依赖和多值依赖
 函数依赖：
  设R(U)是属性集U上的关系模式，X, Y 是U的子集，若对于任意r∈R(U), 任意t, s∈r, t[X] = s[X]--> t[Y] = s[Y]则称“X函数决定Y”, 或“Y函数依赖于X”, 记作：X->Y 称X为决定因素。
  若存在t, s∈r, t[X] = s[X] 但t[Y]！=s[Y]，则称“X不函数决定Y”或“Y不函数依赖于X”
  
  平凡/非平凡函数依赖
   如果X->Y，且Y!⊆X，则称X->Y为非平凡的函数依赖。
   如果X->Y，且Y⊆X，则称X->Y为平凡的函数依赖。

  完全/部分函数依赖
   如果X->Y，对于任意X的真子集X′，都有X'!->Y ，
   则称Y对X完全函数依赖，记作X f-> Y
   如果X->Y，存在X的真子集X′，使得X'->Y ，
   则称Y对X部分函数依赖，记作X p-> Y

  传递函数依赖
   在R(U)中, 如果X->Y, (Y!⊆X), Y!->X, Y->Z, 则称Z对X传递函数依赖
 码：
  候选码：设K为R< U, F >的属性或属性组，若K f-> U，则称K为R的候选码
  主码：若R(U, F)有多个候选码，则可以从中选定一个作为R的主码
  主属性：包含在每一个候选码中的属性
  非主属性：不包含在任何候选码中的属性
  全码：关系模式的码由整个属性组构成

 范式：
  关系数据库中的关系是要满足一定要求的，满足不同程度要求的为不同范式。
   1NF 第一范式 （满足最低要求的）属性不可再分
   2NF 第二范式 R∈1NF 且非主属性完全依赖于码
   3NF 第三范式 非主属性既不部分也不传递依赖于码
   BCNF 通常被认为是修正的第三范式，有时也称为扩充的第三范式 每一个决定性的因素都包含码
   在函数依赖的范畴内，bcnf达到了最高的规范化程度。
  多值依赖：
   设R(U)是属性集U上的一个关系模式。X，Y，Z是U的子集，并且Z = U - X - Y。对给定的一对（x，z）值有一组Y的值，这组值仅仅决定于x值而与z值无关，关系模式R(U)中多值依赖X->->Y成立。称Y多值依赖于X
  多值依赖与函数依赖的区别：
  （1）多值依赖是元组产生依赖，函数依赖是相等产生依赖。
  （2）多值依赖的有效性与属性集的范围有关，而函数依赖的有效性则与属性集的范围无关。
   第四范式：关系模式R<U, F>∈1NF, 若对于每一个是非平凡的多值依赖x->->y，x都包含码，则称R∈4NF。
   任何一个二目关系都能达到第四范式。在多值依赖的范畴内，第四范式达到了最高的规范化程度。
   全是主属性的关系模式一定可以达到第三范式，但是不一定达到bc范式。
   全码一定可以达到bc范式
 规范化：一个低一级范式的关系模式通过模式分解可以转换为若干个高一级范式的关系模式的集合，这种过程就叫规范化
 逻辑蕴含：关系模式R，F是其函数依赖，X，Y是其属性子集，如果从F的函数依赖能够推出XY，则称F逻辑蕴涵XY。
 闭包：被F所逻辑蕴涵的函数依赖的全体所构成的集合称作F的闭包，记作F +
 自反律：若Y⊆X⊆U，则X->Y为F所蕴含。
 增广律：若X->Y为F所蕴含，Z⊆U，则XZ->YZ为F所蕴含。
 传递律：若X->Y及Y->Z为F所蕴含，则X->Z为F所蕴含。

 模式分解：
  关系模式R<U, F>的一个分解是指ρ= { R1<U1, F1>, R2<U2, F2>, …, Rn<Un, Fn> }，其中U = U1UU2U…UUn ，并且没有Ui⊆Uj ，1≤i，j ≤n， Fi是F在Ui上的投影。
  要求：
   分解前后模式要等价
   等价标准：
    (1)分解要具有无损连接性
    (2)分解要保持函数依赖
    (3)分解既要保持函数依赖又要具有无损连接性
  无损分解    交集能决定差集
  保持函数依赖的分解  并集的闭包和原来的相等 没有丢失函数依赖
补充：
 1.使用top的查询
  只取前面几个或者一部分结果
  格式 :
   TOP n[percent] with ties
    N 为非负的整数
    TOP n : 表示取查询结果的前n行;
   Select TOP 3 sname, sdept from student order by sage desc
   Select TOP 3 with ties sname, sdept from student order by sage desc  存在并列
   TOP n percent : 表示取查询结果的前n%行
    With ties 显示并例的结果
    TOP写在select或者distinct后面, 查询列表的前边
   用了with ties 需要order by
   不用order得到的结果可能与希望的不同。

 2.将查询结果保存到新表
  语法格式:
   Select 查询表序列 into 新表名
    from 数据源 where[<条件>]…..
   新表有二类 :
      1)永久的表, 起一个表名就可以了;
      2)局部临时表：表名前加#，当前连接可用，生存期为当前连接的生存期．
       全局临时表：表名前加##，所有连接都可用．生存期为当前连接的生存期．
  3.视图定义后，用户可以象对基本表一样对视图进行查询。DBMS实现视图查询时，首先进行有效性检查，检查所查询的表、视图是否存在。若存在，则从数据字典中取出视图的定义，把定义中的子查询和用户的查询结合起来，转换成等价的对基本表的查询，然后再执行修正了的查询。这一转换过程称为视图消解(View Resolution)。
  4.一般DBMS对视图更新的限制：
  (1) 若视图是由两个以上基本表导出的，则此视图不允许更新。
  (2)若视图的字段来自字段表达式或常数，则不允许对此视图执行INSERT和UPDATE操作，但允许执行DELETE操作。
  (3)若视图的字段来自集函数，则此视图不允许更新。
  (4)若视图定义中含有GROUP BY子句，则此视图不允许更新。
  (5)若视图定义中含有DISTINCT短语，则此视图不允许更新。
  (6)一个不允许更新的视图上定义的视图也不允许更新。
  (7)若视图定义中有嵌套查询，并且内层查询的FROM子句中涉及的表也是导出该视图的基本表，则此视图不允许更新。
 5.三个有用的推理规则：
  合并规则：由X->Y, X->Z, 有X->YZ。
  伪传递规则：由X->Y, WY->Z, 有XW->Z。
  分解规则：由X->Y, Z⊆Y, 有X->Z。
 6.关于模式分解的若干结论：
  (1)分解具有无损连接性和分解保持函数依赖是两个互相独立的标准。具有无损连接性的分解不一定保持函数依赖，保持函数依赖的分解不一定具有无损连接性。一个关系模式的分解可能有三种情况。
  (2)若要求分解具有无损连接性，那么分解后的模式一定能达到BCNF。
  (3)若要求分解保持函数依赖，那么分解后的模式总可以达到3NF，但不一定能达到BCNF。
  (4)若要求分解既具有无损连接性，又保持函数依赖，则分解后的模式可以达到3NF，但不一定能达到BCNF。

注：where字句不饿能用聚集函数作为表达式，需使用having