精华内容
下载资源
问答
  • 数据库知识点总结

    2018-03-03 12:51:53
    数据库知识点总结,大学计算机专业本科生数据库课程期末复习资料
  • 1 数据库概念(了解) 1.1 什么是数据库 数据库就是用来存储和管理数据的仓库! 数据库存储数据的优先: 1.2 数据库的发展历程,
  • 数据库知识点总结.docx
  • 这是一篇MySQL数据库知识点的集合 将数据库的一些基本知识点和MySQL语法介绍结合起来的一篇文档 里面将讲解了MySQL数据库8.0的安装 以及数据库的基本操作 大致内容包含 数据库基础知识 2 安装MySQL8.0压缩版 3 运行...
  • 数据库知识点整理.md

    2020-04-03 11:40:41
    非常详细的数据库知识点整理,主要涵盖:数据库基础知识、DDL、DML、DQL、DCL、事务讲解、Mysql、JDBC、Java操作Mysql、数据库连接池、Redis型非关系数据库、Java使用Jedis操作Redis数据库
  • MySQL数据库的全套思维导图,各大高校考试必考知识点归纳,入门MySQL数据库必读。
  • mysql数据库知识点

    2021-09-27 16:26:08
    mysql数据库知识点
  • 数据库基础知识点 启动,关闭及登录MySQL 使用SQL语句操作数据库 使用SQL语句操作表结构
  • 数据库系统概念,知识点总结 方便期末复习 清晰明了可复制文字 中文笔记,术语带有英文标注 重点知识详细简明
  • 面试必备之数据库知识点总结

    万次阅读 多人点赞 2019-04-12 21:03:06
    日志记录了事务对数据库所做的更新,如果某个事务在执行过程中发生错误,就可以根据日志,撤销事务对数据库已做的更新,使数据库退回到执行事务前的初始状态。 数据库管理系统采用锁机制来实现事务的隔离性 。当多...

    原文地址:https://github.com/DmrfCoder/interview/blob/master/DataBase/DataBase.md

    事物

    事务的概念

    数据库事务通常包含了一个序列的对数据库的读/写操作(一个单元的一系列SQL语句的集合)。包含有以下两个目的:

    1. 为数据库操作序列提供了一个从失败中恢复到正常状态的方法,同时提供了数据库即使在异常状态下仍能保持一致性的方法。(系统错误恢复)
    2. 当多个应用程序在并发访问数据库时,可以在这些应用程序之间提供一个隔离方法,以防止彼此的操作互相干扰。(并发数据库访问)

    当事务被提交给了数据库管理系统(DataBaseManagerService,DBMS),则DBMS需要确保该事务中的所有操作都成功完成且其结果被永久保存在数据库中,如果事务中有的操作没有成功完成,则事务中的所有操作都需要回滚,回到事务执行前的状态;同时,该事务对数据库或者其他事务的执行无影响,所有的事务都好像在独立的运行。

    例子

    某人要在商店使用电子货币购买100元的东西,当中至少包括两个操作:

    1. 该人账户减少100元
    2. 商店账户增加100元

    支持事务的数据库管理系统(transactional DBMS)就是要确保以上两个操作(整个“事务”)都能完成,或一起取消;否则就会出现100元平白消失或出现的情况

    事务的特性(ACID)

    并非任意的对数据库的操作序列都是数据库事务。数据库事务拥有以下四个特性,习惯上被称之为ACID特性

    • A, atomacity 原子性 事务必须是原子工作单元;对于其数据修改,要么全都执行,要么全都不执行。通常,与某个事务关联的操作具有共同的目标,并且是相互依赖的。如果系统只执行这些操作的一个子集,则可能会破坏事务的总体目标。原子性消除了系统处理操作子集的可能性。

    • C, consistency 一致性

      事务将数据库从一种一致状态转变为下一种一致状态。也就是说,事务在完成时,必须使所有的数据都保持一致状态(各种 constraint 不被破坏)。

    • I, isolation 隔离性 由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。事务查看数据时数据所处的状态,要么是另一并发事务修改它之前的状态,要么是另一事务修改它之后的状态,事务不会查看中间状态的数据。换句话说,一个事务的影响在该事务提交前对其他事务都不可见。

    • D, durability 持久性

      事务完成之后,它对于系统的影响是永久性的。该修改即使出现致命的系统故障也将一直保持。

    原子性和一致性的区别:

    转账:张三给李四转账100元。那数据库假设需要 张三扣100,李四加100,记录一条流水。
    如果流水没记录成功,那整体回滚,张三也没转账成功,李四也没多钱。这就是原子性的体现。

    而张三必须扣100,李四必须加100,这个就是一致性了,如果因为某些逻辑原因,导致张三扣了100,流水记录100转账,而李四只加了60。然后这3条操作都成功了,那原子性就符合了,但是一致性就不符合了

    并发控制

    并发控制的主要方法是封锁(Locking)。

    读写异常

    数据库是要被广大客户所共享访问的,那么在数据库操作过程中很可能出现以下几种不确定情况:

    • 丢失修改:两个事务T1,T2读入同一数据并修改,T2提交的结果被T1破坏了,导致T1的修改丢失。(订票系统)
    • 不可重复读:事务T1读取数据后,事务T2执行更新操作,使T1无法再次读取结果。
    • 读脏数据:事务T1修改某个数据并写回磁盘,事务T2读取同一数据,但T1由于某种原因撤销了,这时T1修改过的数据恢复原来的值,T2读取的数据就与数据库中的数据不一致。
    • 幻读:事务在操作过程中进行两次查询,第二次查询结果包含了第一次查询中未出现的数据(这里并不要求两次查询SQL语句相同)这是因为在两次查询过程中有另外一个事务插入数据造成的

    封锁

    封锁是实现并发控制的一个非常重要的技术,所谓 封锁就是事务T在对某个数据对象例如表、记录等操作之前,先向系统发出请求,对其加锁。数据库系统提供两种锁:

    • 排他锁(写锁):若事务T对数据对象A加写锁,则只允许T读取和修改A,其他事务都不能再对A加任何类型的锁,直到T释放A上的锁为止
    • 共享锁(读锁):若事务T对数据对象A加读锁,则只允许T可以读取但不能修改A,其他事务只能再对A加读锁,而不能加写锁,直到T释放A上的读锁为止。

    事务的隔离级别

    为了避免上面出现几种情况在标准SQL规范中定义了4个事务隔离级别,不同隔离级别对事务处理不同 。

    未提交读(Read Uncommitted)

    未提交读(READ UNCOMMITTED)是最低的隔离级别。允许脏读(dirty reads),但不允许更新丢失,事务可以看到其他事务“尚未提交”的修改

    提交读(Read Committed)

    允许不可重复读取,但不允许脏读取。这可以通过“瞬间共享读锁”和“排他写锁”实现。读取数据的事务允许其他事务继续访问该行数据,但是未提交的写事务将会禁止其他事务访问该行

    可重复读(Repeatable Read)

    禁止不可重复读取和脏读取,但是有时可能出现幻读数据。这可以通过“共享读锁”和“排他写锁”实现。读取数据的事务将会禁止写事务(但允许读事务),写事务则禁止任何其他事务。

    可序列化(Serializable)

    最高的隔离级别,它要求事务序列化执行,事务只能一个接着一个地执行,不能并发执行。仅仅通过“行级锁”是无法实现事务序列化的,必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。

    隔离级别越高,越能保证数据的完整性和一致性,但是对并发性能的影响也越大。

    封锁协议

    使用读锁和写锁时,需要约定一定的规则。比如:何时申请、持续时间、何时释放等。这些规则被称为 封锁协议。针对不同的事务隔离级别,有不同的封锁协议。

    • 一级封锁协议:事务T在修改数据R之前必须先对其加写锁,直到事务结束才释放。一级封锁协议防止了丢失修改,但不能保证可重复读和不读脏数据。
    • 二级封锁协议:在一级封锁协议的基础上增加事务T在读数据R前必须加读锁,读完就可以释放。二级封锁协议进一步防止读脏数据,但不能保证可重复读。
    • 三级封锁协议:一级封锁协议的基础上增加事务T在读数据R前必须加读锁,直到事务结束才释放。三阶封锁协议除了防止丢失修改和读脏数据外,进一步防止了不可重复读。
    • 四级封锁协议:四级封锁协议是对三级封锁协议的增强,其实现机制也最为简单,直接对事务中所读取或者更改的数据所在的表加表锁,也就是说,其他事务不能 读写 该表中的任何数据。

    并行调度

    调度是一个或多个事务的重要操作按时间排序的一个序列。如果一个调度的动作首先是一个事务的所有动作,然后是另一个事务的所有动作,以此类推,而没有动作的混合,那么我们说这一调度是串行的。

    事务的正确性原则告诉我们,每个串行调度都将保持数据库状态的一致性。 通常,不管数据库初态怎样,一个调度对数据库状态的影响都和某个串行调度相同,我们就说这个调度是可串行化的。

    可串行性是并行调度正确性的唯一准则,两段锁(简称2PL)协议是为保证并行调度可串行性而提供的封锁协议。两段锁协议规定:在对任何数据进行读、写操作之前,事务道首先要获得对该数据的封锁,而且在释放一个封锁之生,事务不再获得任何其他封锁。

    所谓“两段”锁的含义是:事务分为两个阶段,第一阶段是获得封锁,也称为扩展阶段,第二阶段是释放封锁,也称为收缩阶段

    使用事务

    在MySQL中使用START TRANSACTIONBEGIN开启事务,提交事务使用COMMITROLLBACK用来放弃事务。MySQL默认设置了事务的自动提交,即一条SQL语句就是一个事务。

    总结

    事务的(ACID)特性是由关系数据库管理系统(RDBMS,数据库系统)来实现的。数据库管理系统采用日志来保证事务的原子性、一致性和持久性。日志记录了事务对数据库所做的更新,如果某个事务在执行过程中发生错误,就可以根据日志,撤销事务对数据库已做的更新,使数据库退回到执行事务前的初始状态。

    数据库管理系统采用锁机制来实现事务的隔离性。当多个事务同时更新数据库中相同的数据时,只允许持有锁的事务能更新该数据,其他事务必须等待,直到前一个事务释放了锁,其他事务才有机会更新该数据。

    索引

    基本概念

    在数据库中,索引的含义与日常意义上的“索引”一词并无多大区别(想想小时候查字典),它是用于提高数据库表数据访问速度的数据库对象

    • 索引可以避免全表扫描。多数查询可以仅扫描少量索引页及数据页,而不是遍历所有数据页。
    • 对于非聚集索引,有些查询甚至可以不访问数据页。
    • 聚集索引可以避免数据插入操作集中于表的最后一个数据页。
    • 一些情况下,索引还可用于避免排序操作。

    索引的存储

    一条索引记录中包含的基本信息包括:键值(即你定义索引时指定的所有字段的值) 逻辑指针(指向数据页或者另一索引页)。

    index_1

    当你为一张空表创建索引时,数据库系统将为你分配一个索引页,该索引页在你插入数据前一直是空的。此页此时既是根结点,也是叶结点。每当你往表中插入一行数据,数据库系统即向此根结点中插入一行索引记录。当根结点满时,数据库系统大抵按以下步骤进行分裂:

    • 创建两个儿子结点
    • 将原根结点中的数据近似地拆成两半,分别写入新的两个儿子结点
    • 根结点中加上指向两个儿子结点的指针

    通常状况下,由于索引记录仅包含索引字段值(以及4-9字节的指针),索引实体比真实的数据行要小许多,索引页相较数据页来说要密集许多。一个索引页可以存储数量更多的索引记录,这意味着在索引中查找时在I/O上占很大的优势,理解这一点有助于从本质上了解使用索引的优势。

    索引的分类

    汉语字典的正文本身就是一个聚集索引。比如,我们要查“安”字,就会很自然地翻开字典的前几页,因为“安”的拼音是“an”,而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的,那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字,那么就说明您的字典中没有这个字;同样的,如果查“张”字,那您也会将您的字典翻到最后部分,因为“张”的拼音是“zhang”。也就是说,字典的正文部分本身就是一个目录,您不需要再去查其他目录来找到您需要找的内容。正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。

    如果您认识某个字,您可以快速地从自动中查到这个字。但您也可能会遇到您不认识的字,不知道它的发音,这时候,您就不能按照刚才的方法找到您要查的字,而需要去根据“偏旁部首”查到您要找的字,然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法,比如您查“张”字,我们可以看到在查部首之后的检字表中“张”的页码是672页,检字表中“张”的上面是“驰”字,但页码却是63页,“张”的下面是“弩”字,页面是390页。很显然,这些字并不是真正的分别位于“张”字的上下方,现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序,是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字,但它需要两个过程,先找到目录中的结果,然后再翻到您所需要的页码。

    聚集索引

    表数据按照索引的顺序来存储的。对于聚集索引,叶子结点即存储了真实的数据行,不再有另外单独的数据页。在聚集索引中,叶结点也即数据结点,所有数据行的存储顺序与索引的存储顺序一致。

    在一张表上只能创建一个聚集索引,因为真实数据的物理顺序只可能是一种。如果一张表没有聚集索引,那么它被称为“堆集”(Heap)。这样的表中的数据行没有特定的顺序,所有的新行将被添加的表的末尾位置。

    index_2

    非聚集索引

    表数据存储顺序与索引顺序无关。对于非聚集索引,叶结点包含索引字段值及指向数据页数据行的逻辑指针,该层紧邻数据页,其行数量与数据表行数据量一致。

    非聚集索引与聚集索引相比:

    • 叶子结点并非数据结点
    • 叶子结点为每一真正的数据行存储一个“键-指针”对
    • 叶子结点中还存储了一个指针偏移量,根据页指针及指针偏移量可以定位到具体的数据行。
    • 类似的,在除叶结点外的其它索引结点,存储的也是类似的内容,只不过它是指向下一级的索引页的。

    index_3

    索引失效

    索引并不是时时都会生效的,比如以下几种情况,将导致索引失效:

    1. 如果条件中有or,即使其中有条件带索引也不会使用。 >要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引
    2. 对于多列索引,不是使用的第一部分,则不会使用索引。
    3. like查询是以%开头。
    4. 如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引。
    5. 如果 mysql 估计使用全表扫描要比使用索引快,则不使用索引。例如,使用<>not innot exist,对于这三种情况大多数情况下认为结果集很大,MySQL就有可能不使用索引。

    索引设计的原则

    • 表的某个字段值得离散度越高,该字段越适合选作索引的关键字。主键字段以及唯一性约束字段适合选作索引的关键字,原因就是这些字段的值非常离散。
    • 占用存储空间少的字段更适合选作索引的关键字。例如,与字符串相比,整数字段占用的存储空间较少,因此,较为适合选作索引关键字。
    • 存储空间固定的字段更适合选作索引的关键字。与 text 类型的字段相比, char 类型的字段较为适合选作索引关键字。
    • Where 子句中经常使用的字段应该创建索引,分组字段或者排序字段应该创建索引,两个表的连接字段应该创建索引
    • 更新频繁的字段不适合创建索引,不会出现在 where 子句中的字段不应该创建索引
    • 最左前缀原则。
    • 尽量使用前缀索引。

    总结

    聚集索引是一种稀疏索引,数据页上一级的索引页存储的是页指针,而不是行指针。而对于非聚集索引,则是密集索引,在数据页的上一级索引页它为每一个数据行存储一条索引记录。

    与非聚集索引相比,聚集索引有着更快检索速度、更快的字段排序。

    在MySQL中InnoDB按照主键进行聚集,如果没有定义主键,InnoDB会试着使用唯一的非空索引来代替。如果没有这种索引,InnoDB就会定义隐藏的主键然后在上面进行聚集,但是主键和聚集索引是不等价的。在InnoDBNormal索引即非聚集索引。

    SQL语句

    SQL语言由一些简单句子构成基本的语法,所有的SQL语句均有自己的格式,典型的SQL语句(查询语句)结构如图所示:

    23

    QL语法的基础是子句(clause),子句中会包括一些关键词(keyword)。每条SQL语句均由一个关键词开始,该关键词描述这条语句要产生的动作。SQL中常用的关键词及其功能如表所示:

    image-20190318202147011

    image-20190318202203656

    image-20190318202221064

    CRUD

    CREATE TABLE

    CREATE TABLE `user` (
      `id` INT AUTO_INCREMENT,
      `name` VARCHAR (20),
      PRIMARY KEY (`id`)
    );
    

    VARCHAR记得指定长度。

    UPDATE

    UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
    

    INSERT

    INSERT INTO 表名称 VALUES (1,2,....)
    
    INSERT INTO table_name (1,2,...) VALUES (1,2,....)
    

    DELETE

    DELETE FROM 表名称 WHERE 列名称 =

    修改表结构

    ALTER TABLE table_name add column_name datatype
    
    ALTER TABLE table_name drop COLUMN column_name
    
    ALTER TABLE table_name modify COLUMN column_name datatype
    

    权限分配

    grant select,insert on userdb.userinfo to'zhangsan'@'localhost'
    

    模糊查询

    %:表示任意0个或多个字符。可匹配任意类型和长度的字符,有些情况下若是中文,请使用两个百分号(%%)表示。

    select * from test where text like '%1%';
    

    _ : 表示任意单个字符。匹配单个任意字符,它常用来限制表达式的字符长度语句。

    --倒数第三个字符为 1 ,且最小长度为 5
    select * from test where text like '__%1__';
    

    连接

    在数据库原理中,关系运算包含 选择投影连接 这三种运算。相应的在SQL语句中也有表现,其中Where子句作为选择运算,Select子句作为投影运算,From子句作为连接运算。

    连接运算是从两个关系的笛卡尔积中选择属性间满足一定条件的元组,在连接中最常用的是等值连接和自然连接。

    • 等值连接:关系R、S,取两者笛卡尔积中属性值相等的元组,不要求属性相同。比如 R.A=S.B
    • 自然连接(内连接):是一种特殊的等值连接,它要求比较的属性列必须是相同的属性组,并且把结果中重复属性去掉

    关系R:

    ABC
    a1b15
    a1b26
    a2b38
    a2b412

    关系S:

    BE
    b13
    b27
    b310
    b32
    b52

    自然连接 R & S的结果为:

    ABCE
    a1b153
    a1b267
    a2b3810
    a2b382

    两个关系在做自然连接时,选择两个关系在公共属性上值相等的元组构成新的关系。此时关系R中某些元组有可能在S中不存在公共属性上相等的元组,从而造成R中这些元组在操作时被舍弃,同样,S中某些元组也可能被舍弃。这些舍弃的元组被称为 悬浮元组

    如果把悬浮元组也保存在结果中,而在其他属性上置为NULL,那么这种连接就成为 外连接,如果只保留左边关系R中的悬浮元组就叫做 左外连接(左连接),如果只保留右边关系S中的悬浮元组就叫做 右外连接(右连接)

    Join

    Join 用于多表中字段之间的联系,语法如下:

    ... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditiona
    
    -- table1:左表;table2:右表。
    
    

    JOIN 按照功能大致分为如下三类:

    • INNER JOIN(内连接,或等值连接):取得两个表中存在连接匹配关系的记录。
    • LEFT JOIN(左连接):取得左表(table1)完全记录,即是右表(table2)并无对应匹配记录。
    • RIGHT JOIN(右连接):与 LEFT JOIN 相反,取得右表(table2)完全记录,即是左表(table1)并无匹配对应记录。

    在下面的示例中使用以下数据:

    mysql> select A.id,A.name,B.name from A,B where A.id=B.id;
    
    
    idnamename
    1PirateRutabaga
    2MonkeyPirate
    3NinjaDarth Vader
    4SpaghettiNinja

    4 rows in set (0.00 sec)

    Inner Join

    内连接,也叫等值连接,inner join产生同时符合A和B的一组数据。

    mysql> select * from A inner join B on A.name = B.name;
    
    
    idnameidname
    1Pirate2Pirate
    3Ninja4Ninja

    join_1

    Left Join

    mysql> select * from A left join B on A.name = B.name;
    -- 或者:select * from A left outer join B on A.name = B.name;
    
    
    idnameidname
    1Pirate2Pirate
    2MonkeyNULLNULL
    3Ninja4Ninja
    4SpaghettiNULLNULL

    4 rows in set (0.00 sec)

    join_2

    left join,(或left outer join:在Mysql中两者等价,推荐使用left join)左连接从左表(A)产生一套完整的记录,与匹配的记录(右表(B))。如果没有匹配,右侧将包含null。

    如果想只从左表(A)中产生一套记录,但不包含右表(B)的记录,可以通过设置where语句来执行,如下:

    mysql> select * from A left join B on A.name=B.name where A.id is null or B.id is null;
    
    
    idnameidname
    2MonkeyNULLNULL
    4SpaghettiNULLNULL

    2 rows in set (0.00 sec)

    join_3

    根据上面的例子可以求差集,如下:

    SELECT * FROM A LEFT JOIN B ON A.name = B.name
    WHERE B.id IS NULL
    union
    SELECT * FROM A right JOIN B ON A.name = B.name
    WHERE A.id IS NULL;
    
    
    idnameidname
    2MonkeyNULLNULL
    4SpaghettiNULLNULL
    NULLNULL1Rutabaga
    NULLNULL3Darth Vader

    union :用于合并多个 select 语句的结果集,并去掉重复的值。 union all :作用和 union 类似,但不会去掉重复的值。

    join_4

    Cross join

    交叉连接,得到的结果是两个表的乘积,即笛卡尔积。

    实际上,在 MySQL 中(仅限于 MySQLCROSS JOININNER JOIN 的表现是一样的,在不指定 ON 条件得到的结果都是笛卡尔积,反之取得两个表完全匹配的结果。

    INNER JOINCROSS JOIN 可以省略 INNERCROSS 关键字,因此下面的 SQL 效果是一样的:

    ... FROM table1 INNER JOIN table2
    ... FROM table1 CROSS JOIN table2
    ... FROM table1 JOIN table2
    
    

    主键与外键

    SQL 的主键和外键的作用:

    外键取值规则:空值或参照的主键值
    (1)插入非空值时,如果主键值中没有这个值,则不能插入。
    (2)更新时,不能改为主键表中没有的值。
    (3)删除主键表记录时,可以在建外键时选定外键记录一起联删除还是拒绝删除。
    (4)更新主键记录时,同样有级联更新和拒绝执行的选择。
    
    

    简而言之,SQL的主键和外键就是起约束作用。
    关系型数据库中一条记录中有若干个属性,若其中某一个属性组(注意是组)能唯一标识一条记录,该属性就可以成为一个主键。例如:

    学生表(学号,姓名,性别,班级)
    
    

    其中每个学生的学号是唯一的,学号就是一个主键;

    课程表(课程编号,课程名,学分)
    
    

    其中课程编号是唯一的,课程编号就是一个主键;

    成绩表(学号,课程号,成绩)
    
    

    成绩表中单一一个属性无法唯一标识一条记录,学号和课程编号的组合才可以唯一标识一条记录,所以学号和课程编号的属性组是一个主键。
    成绩表中的学号不是成绩表中的主键,但它和学生表中的学号相对应,并且学生表中的学号是学生表的主键,则称成绩表中的学号是学生表的外键;同理,成绩表中的课程号是课程表的外键。

    定义主键和外键主要是为了维护关系数据库的完整性,总结一下:

    1.主键是能确定一条记录的唯一标识。比如,一条记录包括身份证号码,姓名,年龄。身份证号码是唯一确认你这个人的,其他的都可能有重复,所以身份证号码是主键。
    外键用于与另一张表相关联。是能确认另一张表记录的字段,用于保持数据的一致性。比如,A表中的一个字段,是B表的主键,那它就可以是A表的外键。

    2.主键、外键和索引的区别

    定义:
    主键:唯一标识一条记录,不能有重复,不允许为空。
    外键:表的外键是另一表的主键,外键是可以有重复的,可以是空值。
    索引:该字段没有重复值,但可以有一个空值。
    作用:
    主键:用来保证数据完整性
    外键:用来和其他表建立联系用
    索引:用来提高查询排序的速度
    个数:
    主键:主键只能有一个。
    外键:一个表可以有多个外键。
    索引:一个表可以有多个唯一索引。
    
    

    创建SQL的主键和外键约束的方法:

    --如果在表创建好了以后再加约束,则格式分别为
    
    --主键
    ALTER TABLE 表名
    --"PK"为主键的缩写,字段名为要在其上创建主键的字段名,"PK_字段名"就为约束名
    ADD CONSTRAINT PK_字段名   
    --同上
    PRIMARY KEY(字段名)
    
    --唯一约束
    ALTER TABLE 表名
    ADD CONSTRAINT UQ_字段名
    UNIQUE(字段名)
    
    --外键约束
    ALTER TABLE 表名
    --"FK"为外键的缩写
    ADD CONSTRAINT FK_字段名
    FOREIGN KEY(字段名) REFERENCES 关联的表名(关联的字段名)
    
    --举个例子
    ALTER TABLE 表A
    ADD CONSTRAINT FK_B 
    FOREIGN KEY(TicketNo) REFERENCES 表B(TicketNo)
    
    --级联更新,级联删除,这样在删除主表Student时,成绩表中该学生的所有成绩都会删除
    ALTER TABLE 成绩表 
    ADD CONSTRAINT FK_StudentNo 
    FOREIGN KEY(StudentNo) REFERENCES Student(StudentNo)
    ON UPDATE CASCADE ON DELETE CASCADE
    
    --检查约束
    ALTER TABLE 表名
    ADD CONSTRAINT CK_字段名
    --括号中的"CK_字段名>0"为条件表达式,用关系运算符连接
    CHECK(字段名>0)
    
    --默认值约束
    ALTER TABLE 表名
    ADD CONSTRAINT DF_字段名
    --其中的'默认值'为想要设置的默认值,注意'FOR'
    DEFAULT '默认值' FOR 字段名
    
    --删除创建的约束
    ALTER TABLE 表名
    --约束名为前面创建的如:FK_字段名这样的约束名
    DROP CONSTRAINT 约束名
    --注意:如果约束是在创建表的时候创建的,则不能用命令删除
    --只能在'企业管理器'里面删除
    
    
    --获取SqlServer中的表结构
    SELECT syscolumns.name,systypes.name,syscolumns.isnullable,syscolumns.length
    FROM syscolumns,systypes
    WHERE syscolumns.xusertype = systypes.xusertype
    AND syscolumns.id = OBJECT_ID('Student')
    
    --单独查询表递增字段
    SELECT [NAME] FROM syscolumns 
    WHERE 
    id = OBJECT_ID(N'Student') AND COLUMNPROPERTY(id,name,'IsIdentity')=1
    
    --获取表主外键约束
    EXEC sp_helpconstraint 'Student'
    
    --查询表主键外键信息
    SELECT 
    sysobjects.id AS objectId,
    OBJECT_NAME(sysobjects.parent_obj) AS TableName,
    sysobjects.name AS constraintName,
    sysobjects.xtype AS constraintType,
    syscolumns.name AS columnName
    FROM sysobjects INNER JOIN sysconstraints
    ON sysobjects.xtype IN('C','F','PK','UQ','D')
    AND sysobjects.id = sysconstraints.constid
    LEFT OUTER JOIN syscolumns ON sysconstraints.id = syscolumns.id
    WHERE OBJECT_NAME(sysobjects.parent_obj) = 'Student' 
    
    
    展开全文
  • 1、sql语言是非过程的语言,易学易。 2、sql语言具有数据定义,数据操作,数据控制的功能。 3、sql是结构化查询语言...7、关系数据库规范化是为解决关系数据库中插入、删除和数据冗余问题而引入的。 8、在关系模式R中,

    1、sql语言是非过程的语言,易学易。
    2、sql语言具有数据定义,数据操作,数据控制的功能。
    3、sql是结构化查询语言。
    4、SQL语言的数据定义功能包括定义数据库,定义基本表,定义视图,定义索引。
    5、视图是一个虚表,他是从一个或几个基本表中导出的表。在数据库中,只存放试图的定义,不存放试图的试图对应的数据。
    6、关系规范化中的删除操作异常是指不该删除的数据被删除,插入操作异常是指应该插入的数据未被插入。
    7、关系数据库规范化是为解决关系数据库中插入、删除和数据冗余问题而引入的。
    8、在关系模式R中,若其函数以来集中所有候选关键字都是决定因素,则R最高范式是4NF。
    9、候选关键字中的属性称为主属性。
    10、R的最高范式必定是3NF,最高能达到BC范式。
    关系模式中若属性都是主属性,则不会存在非主属性对码的部分函数依赖,也不会存在非主属性对码的传递函数依赖,消除这两种分别代表达到第二范式和第三范式(这里的码指的是候选码)。若关系模式中全都是主属性,则至少是第三范式,若想达到BC范式,还要消除主属性对码的部分函数依赖和传递函数依赖。
    11、关系模式的分解不唯一.
    12、关系规范化的目的是控制冗余,避免插入和删除异常。从而增强数据库结构的稳定性和灵活性。
    13、对于非规范化的模式,经过使属性域变为简单域转变为1NF,将1NF经过消除非主属性对主关键字的部分依赖转变为2NF,将2NF经过消除非主属性对主关键字的传递依赖转变为3NF。
    14、在关系数据库的规范化理论中,在执行“分解”时,必须遵守规范化原则:保持原有的依赖关系和无损连接性。

    数据库设计

    1、在数据库的概念设计中,最常用的数据模型是实体-联系模型
    2、如果两个实体之间的联系是 m∶n,则 需要引入第三个交叉关系
    3、数据流程图(DFD)是用于᧿述结构化方法中 需求分析阶段的工具
    4、数据库设计的几个步骤是 需求分析,概念设计,逻辑设计,物理设计,编码和调试、
    5、数据库应用系统的设计应该具有对于数据进行收集、存储、加工、抽取和传输等功能,
    包括数据设计和处理设计,而 数据设计是系统设计的基础和核心
    6、在设计分 E-R 图时,由于各个子系统分别有不同的应用,而且往往是由不同的设计人员
    设计的,所以各个分 E-R 图之间难免有不一致的地方,这些冲突主要有 属性冲突、命名冲突和 结构冲突图 三类。
    7、E-R 图向关系模型转化要解决的问题是如何将实体和实体之间的联系转换成关系模式,
    如何确定这些关系模式的 属性和码
    8、数据库逻辑设计中进行模型转换时,首先将概念模型转换为与特定的DBMS无关的,但为一般的关系模型、网状模型或层次模型所表示的一般模型 ,然后将 一般模型 转化为 特定的DBMS逻辑模型。
    9、数据库的 完整性 是指数据的正确性和相容性。
    10、事务 是 DBMS 的基本单位,它是用户定义的一组逻辑一致的程序序列。
    11、事务的一致性是指 事务必须是使数据库从一个一致性状态变到另一个一致性状态。
    12、事务的隔离性是指 一个事务内部的操作及使用的数据对并发的其他事务是隔离的。。
    13、事务的持续性是指 事务一旦提交,对数据库的改变是永久的。
    14、常见并发一致性问题包括:丢失的修改、不可重复读、读脏数据

    读“脏‘数据
    读"脏"数据是指事务T1修改某一数据,并将其写回磁盘,事务T2读取同一数据后,T1由于某种原因被撤消,这时T1已修改过的数据恢复原值,T2读到的数据就与数据库中的数据不一致,则T2读到的数据就为"脏"数据,即不正确的数据。

    不可重复读:
    不可重复读是指事务T1读取数据后,事务T2执行更新操作,使T1无法再现前一次读取结果。具体地讲,不可重复读包括三种情况:
    事务T1读取某一数据后,事务T2对其做了修改,当事务1再次读该数据时,得到与前一次不同的值。例如,T1读取B=100进行运算,T2读取同一数据B,对其进行修改后将B=200写回数据库。T1为了对读取值校对重读B,B已为200,与第一次读取值不一致。
    事务T1按一定条件从数据库中读取了某些数据记录后,事务T2删除了其中部分记录,当T1再次按相同条件读取数据时,发现某些记录神密地消失了。
    事务T1按一定条件从数据库中读取某些数据记录后,事务T2插入了一些记录,当T1再次按相同条件读取数据时,发现多了一些记录。(这也叫做幻影读)

    丢失修改:
    下面先来看一个例子,说明并发操作带来的数据的不一致性问题。
    考虑飞机订票系统中的一个活动序列:
    甲售票点(甲事务)读出某航班的机票余额A,设A=16.
    乙售票点(乙事务)读出同一航班的机票余额A,也为16.
    甲售票点卖出一张机票,修改余额A←A-1.所以A为15,把A写回数据库.
    乙售票点也卖出一张机票,修改余额A←A-1.所以A为15,把A写回数据库.
    结果明明卖出两张机票,数据库中机票余额只减少1。
    归纳起来就是:两个事务T1和T2读入同一数据并修改,T2提交的结果破坏了T1提交的结果,导致T1的修改被丢失。前文(2.1.4数据删除与更新)中提到的问题及解决办法往往是针对此类并发问题的。

    15、解决并发操作带来的数据不一致性总是普遍采用 封锁。
    16、数据库中的封锁机制是 并发控制 的主要方法。
    17、对并发操作若不加以控制,可能会带来不一致。(并发操作带来的是破坏数据库一致性的问题。死锁是在并发控制中采用了两阶段封锁协议后带来的问题,而不是并发操作本身的问题)
    18、数据库恢复的基础是利用转储的冗余数据。这些转储的冗余数据包括 日志文件、数据库后备副本。
    19、在数据库的安全性控制中,为了保证用户只能存取他有权存取的数据。在授权的定义中,
    数据对象的 范围越小,授权子系统就越灵活。
    20、数据库保护包含数据的 安全性、完整性、并发控制、恢复。
    21、保护数据安全性的一般方法是 设置用户标识和存取权限控制。
    22、数据的安全性是指 保护数据库,防止未经授权的,或不合法的使用造成的数据汇漏、更改或破坏。
    23、安全性控制的一般方法有 用户标识鉴定 、存取控制 、审计 、数据加密和视图的保护五级安全措施。
    24、存取权限包括两方面的内容,一个是 要存取的数据对象 ,另一个是 对此数据对象进行操作的类型。
    25、①授权编译系统 和 ②合法权检查机制 一起组成了安全性系统。
    26、事务 是 DBMS 的基本单位,它是用户定义的一组逻辑一致的程序序列。
    27、有两种基本类型的锁,分别是 共享锁 和 排他锁。
    28、、如果数据库中只包含成功事务提交的结果,就说数据库处于 一致 状态。
    29、数据库系统在运行过程中,可能会发生故障。故障主要有 ①事务故障 、 ②系统故障 、介质故障和 ③计算机病毒 四类。
    30、数据库系统是利用存储在外存上其他地方的 ① 冗余数据 来重建被破坏的数据库。它主要有两种: ②后援副本 和 ③日志文件 。
    31、制作后援副本的过程称为 ①转储。它又分为 ②增量转储 和 ③日志文件 。
    32、事务故障、系统故障的恢复是由 ①系统自动 完成的,介质故障是由 ②DBA执行恢复操作 完成的。
    33、数据库的完整性是指数据的 实体完整性 和 参照完整性。
    34、实体完整性是指在基本表中 外码可以是空值或者另一个关系主码的有效值。
    35、为了保护数据库的实体完整性,当用户程序对主码进行更新使主码值不惟一时,DBMS就 拒绝此操作。
    36、在 Visual FoxPro 中完整性规则可分为 ①域完整性规则 和 ②关系完整性规则 两大类。

    展开全文
  • 三级数据库知识点总结

    千次阅读 2019-04-01 12:12:38
    三级数据库知识点总结 一、数据库的基本知识 1.数据库系统(DBS):主要提供应用数据的组织、存储、维护、访问等数据管理功能。 2,。数据库应用系统(DBAS):不仅为用户提供数据管理功能,还根据具体应用领域业务...

    三级数据库知识点总结
    一、数据库的基本知识
    1.数据库系统(DBS):主要提供应用数据的组织、存储、维护、访问等数据管理功能。
    2,。数据库应用系统(DBAS):不仅为用户提供数据管理功能,还根据具体应用领域业务规则,通过数据库应用程序,实现了更为复杂的数据处理功能。
    3.数据库应用系统设计与开发:
    (1)根据具体应用领域数据管理和处理需求,设计应用数据在数据库中的组织和存储方式,即设计数据库模式或结构。
    (2)数据库的三级模式:由外模式,模式,内模式构成。外模式是用户可见的部分数据的存在形式;模式可以等价为全体数据逻辑结构且用户不可见,是三级模式的中间部分;内模式对应数据库的物理结构和存储方式。
    4.软件开发方法
    (1)瀑布模型(也称为软件生命周期模型):项目规划、系统分析、总体设计、详细设计、编码调试与集成测试、运行维护阶段。
    (2)快速原型模型:快速分析、设计构造原型、运行原型、评价原型、改进原型。
    (3)螺旋模型:项目规划、风险评估、工程实现、用户评估。
    5.数据库应用系统(DBAS)
    (1)面向数据管理和数据处理的复杂软件系统
    (2)活动组成:项目规划、需求分析、系统设计、实现与部署、运行与维护
    6.规划与分析
    (1)系统规划与定义:任务陈述、确定任务目标、确定系统范围与边界、确定用户视图
    (2)可行性分析:技术可行性、经济可行性、操作可行性、开发方案选择
    (3)项目规划:项目团队、项目环境、项目活动、成本预算、进度计划
    7.需求分析:需求获取(面谈、实地考察、问卷调查、查阅资料),需求分析(标示问题、建立需求模型、描述需求、确认需求),需求描述与规范说明、需求验证
    (1)数据需求分析:数据字典(数据项、数据结构、数据流、数据存储、处理)
    (2)功能需求分析{
    (3)性能需求分析:数据操作或数据访问相应时间、系统吞吐量(每秒事务数TPS)、允许并发访问的最大用户数、每TPS代价值
    (4)其他需求分析:存储需求分析、安全性能需求分析、备份与恢复需求分析
    (5)需求分析方法
    ①DFD需求建模方法(也被称为过程建模和功能建模方法)(自顶向下):数据流、处理、数据存储、外部项(也称数据源或数据终点)
    ②IDEF0(自顶向下):矩形框(代表功能活动)和箭头
    8.系统设计
    (1)概念设计:数据库概念模型设计(ER方法)、系统总体设计
    (2)逻辑设计:数据库逻辑结构设计(关系模型、层次模型、网状模型)、数据库事务概要设计(read、write两个元操作)、应用程序概要设计
    (3)物理设计:数据库物理结构设计(文件形式存放)、数据库事务详细设计、应用程序详细设计
    9.实现与部署:建立数据库结构,数据加载,事务和应用程序的编码及测试,系统集成、测试与试运行,系统部署
    10.运行管理与维护
    (1)日常维护:备份与恢复、完整性维护、安全性维护、存储空间管理、并发控制
    (2)监控与分析:数据采集与统计、操作分析、基准程序评估
    (3)性能优化调整:查询调整与优化、索引调整、事务调整、模式调整、参数调整、硬件调整与升级、应用程序优化
    (4)系统进化:应用程序升级、数据库重组、DBMS和OS升级
    二、数据库结构设计
    1.数据库概念设计:
    (1)明确建模目标
    (2)定义实体集(分类标识、概括命名)
    (3)定义联系(主要工作:表示依赖、命名联系及关于联系的说明)
    (4)建立信息模型(ER方法,构造ER模型)
    (5)确定实体集属性
    (6)对信息模型进行集成与优化
    2.ER建模方法(实体联系方法)
    (1)实体或实例(也称为实体集实例或实例)
    (2)实体集(矩形框表示)
    (3)属性:每个属性的取值范围称为域。属性的具体取值称为属性值。(椭圆或圆角矩形表示)
    (4)码:实体集中能唯一标识每一个实例的属性或属性组称为该实体集的码
    (5)联系:联系描述现实世界中实物之间的联系(菱形表示)
    ①一对一联系(1:1))
    ②一对多联系(1:n)
    ③多对多联系(n:m)
    3.IDEF1X建模方法
    (1)实体集

    (2)联系:标定型联系、非标定型联系、分类联系、非确定联系
    2.数据库逻辑设计
    (1)任务:把数据库概念设计的结果(ER模型),转换为具体的数据库管理系统支持的数据模型。数据库逻辑设计是面向机器世界的,这个阶段将按照具体数据库管理支持的数据模型来组织和存储数据,包括定义和描述数据库的全局逻辑结构、数据之间的关系、数据的完整性及安全性要求等。
    (2)目标:得到实际的数据库管理系统可处理的数据库模式,各模式结构合理,数据之间不存在不合理的数据依赖关系,且满足各类应用的处理和使用要求为数据库的物理设计及数据库搞笑、正确地运行打下基础。
    (3)优化关系模式
    1NF:每个属性都不可再分割
    2NF:满足1NF消除了非主属性对码的部分函数依赖
    3NF:满足2NF,其次非主属性之间不存在函数依赖(即传递依赖)
    3.数据库物理设计
    (1)目的是将数据库的逻辑描述转换为实现技术规范,其目标是设计数据存储方案,以便提供足够好的性能并确保数据库的完整性、安全性和可恢复性。
    (2)数据库的物理结构:数据库中的应用数据是以文件形式存储在外设存储介质(如磁盘)上的,文件在逻辑上被组织成记录的序列,也就是说,每个DB文件可以看作是逻辑记录的集合。
    (3)索引:索引技术的关键是建立记录域取值到记录的物理地址间的映射关系,这种映射关系成为索引
    有序索引:聚集索引和非聚集索引,稠密索引和稀疏索引,主索引和辅索引,唯一索引,单层索引(线性索引)和多层索引(B树和B+树索引)
    散列索引(也称为哈希索引机制):利用一个散列函数实现记录域取值到物理地址间的直接映射关系。
    (4)物理设计内容:
    ①数据库逻辑模式描述(关系模式及其视图的转换):面向目标数据库描述基本表和视图、设计基本表业务规则
    ②文件组织与存取设计:
    堆文件:数据量很少,并且插入、删除、更新等操作非常频繁;向新建表批量加载数据
    顺序文件:支持基于查找码的顺序访问;也支持快速的二分查找
    索引文件
    散列文件:基于散列域值得等值匹配;访问顺序是随机的也用散列文件
    ③数据分布设计(水平划分和垂直划分)
    ④确定系统配置
    ⑤物理模式评估(时间、空间、维护代价等方面)
    三、数据库应用系统功能设计与实施
    系统设计:概要设计、逻辑设计、物理设计
    功能设计:总体设计、详细设计
    数据库事务功能设计:事务概要设计、事务详细设计
    总体上按照DBAS生命周期模型:
    1.DBAS总计设计
    (1)DBAS体系结构设计
    客户端/服务器(C/S)结构(胖客户端):人机交互功能、多个客户端应用提供共享的数据管理功能、通过网络访问多个不同数据库
    浏览器/服务器(B/S)结构(瘦客户端):表示层(Web浏览器)、功能层(Web应用服务器)、数据层(数据库服务器)
    (2)DBAS软件总体设计
    (3)软硬件选型与配置设计
    (4)业务规则初步设计
    2.DBAS功能概要设计
    表示层概要设计:人机界面设计
    业务逻辑层概要设计:高内聚,松隅合
    数据访问层概要设计:事务的设计(概要设计、详细设计)
    3.DBAS功能详细设计
    表示层详细设计:原型迭代法
    业务逻辑层详细设计:UML的类图,活动图,协作图(通信图)
    4.应用系统安全构架设计
    (1)数据安全设计
    数据库的安全性保护:用户身份鉴别、权限控制、视图机制
    数据库的保护(约束):列(值的类型,范围,精度,排序)、元组(记录各属性之间关系)、关系
    数据库的并发控制:排它锁(x锁)和共享锁(s锁)
    数据库的备份与恢复:双机热备、数据转储(也称为数据备份)、数据加密存储
    数据加密传输:数字安全证书、对称密钥加密、数字签名、数字信封
    (2)环境安全设计:漏洞与补丁、计算机病毒防护(安装杀毒软件,计算机实时监控)、网络环境安全(防火墙、入侵检测系统(IDS)、网络隔离(物理隔离和逻辑隔离))、物理环境安全(UPS也被称为不间断电源)
    (3)制度安全设计
    5.DBAS实施
    创建数据库:初始空间大小、数据库增量大小、访问性能
    数据装载:筛选数据、转换数据格式、输入数据、校验数据
    编写与调试应用程序
    数据库系统试运行:功能测试、性能测试、系统联调
    四、UML(统一建模语言)与数据库应用系统
    UML语义的四层建模概念框架:元元模型层、元模型层、模型层、用户模型层
    UML包括以下五种视图:结构视图、实现视图、行为视图、环境视图和用例视图
    {

    1. DBAS业务流程与需求表达
      (1)业务流程与活动图
      活动图:主要用于描述系统,用例和程序模块中逻辑流程的先后执行次序,并行次序
      一张活动图只有一个起点,可有多个终点
      活动之间的转换由箭头表示,箭头表示执行方向,其标注为执行下一个活动的条件,加粗直线为同步条
      (2)系统需求与用例图
      用例模型由用例图构成。用例模型由用例、角色(与系统进行交互的外部实体)和系统(边界以内的区域)三部分组成。
      一个角色可以执行多个用例,在用例图中,用通用化关系来描述角色之间的行为。通用化关系是指把某些角色的共同行为抽取出来作为通用行为,这些通用行为构成他们的超类(用带空心三角形表示)。
      用例与角色之间有连接关系,此关系属于关联,又称作通信关联
      用例之间也有关系,包括扩展(一个用例增加新内容成为另一个用例,<>)、使用(一个用例使用另一个用例<>)、组合(把相关用例打包当作整体)。
    2. DBAS系统内部结构的表达
      2.1系统结构与类图
      (1)属性(可见性 名称:类型=缺省值{约束性})
      可见性:公有(+)、受保护(#)、私有(-)
      缺省值:属性的初始值
      (2)操作(可见性 名称(参数表):返回类型表达式{约束性})
      (3)关系
      关联:通常情况下关联是双向的(用直接连接)、导航关联(单向,带有方向的箭头)、聚集(共享聚集,空心菱形、组成,实心菱形)
      通用化(继承):带实线的空心三角形
      依赖:Y=f(x)
      精化:带虚线的空心三角形
      2.2系统结构与顺序图(强调时间)
      顺序图主要用于描述系统内对象之间的消息发送和接收序列,纵轴表示时间的持续过程,横向表示对象,每个对象用矩形框表示,纵向的虚线表示对象在序列中的执行情况,称为对象的“生命线”。
      2.3系统结构与通信图(强调空间)
      通信图中包含一组对象,并在图中展示这些对象之间的联系以及对象间发送和接收的信息
      3.DBAS系统微观设计的表达
      微观设计与对象图
      微观设计与状态机图
      微观设计与时间图
      4.DBAS系统宏观设计的表达
      宏观设计与包图(可见性:私有、保护、公有和实现,缺省的可见性为公有)
      宏观设计与交互概述图
      宏观设计与复合结构图
      5.DBAS系统实现与部署的表达
      系统实现与组件图(组件图展示了一组组价之间的组织和依赖,用于对源代码、可执行的发布、物理数据库等的系统建模。组件是逻辑设计中定义的概念和功能在物理架构中的实现)
      系统实现与部署图
      五、高级数据查询
      1.使用TOP限制结果集
      查询前n个数的结果:TOP n [precent] WITH TIES
      2.使用CASE函数
      简单CASE函数:WHEN 简单表达式 THEN 结果表达式
      搜索CASE函数:
      CASE
      WHEN 布尔表达式 THEN 结果表达式
      3.将查询结果保存到新表中
      SELECT 查询列表序列 INTO <新表名>(可以是临时表,局部临时表#(只能在当前连接中使用),全局临时表##)
      4.并运算(UNION)(ALL表示在结果集中包含所有查询语句产生的全部记录,包括重复的记录,若没有指定ALL则默认是删除合并后结果集中的重复记录)
      JOIN 操作是水平地合并数据(添加更多的列)
      UNION是垂直地合并数据(添加更多的行)
      5.交运算(INTERSECT)
      SELECT 语句1 INTERSECT SELECT 语句2
      6.差运算(EXCEPT)
      7.相关子查询(聚合函数不能出现在WHERE子句中)
      使用子查询进行基于集合的测试(先执行子查询再执行外层查询):WHERE 表达式 [NOT] IN(子查询)
      使用子查询进行比较测试(不相关子查询或嵌套子查询):WHRER 表达式 比较运算符(子查询)
      使用子查询进行存在性测试(先执行外层查询再执行内层查询):WHERE [ONT] EXISTS(子查询)
      DISTINCT:去掉重复行
      8.其他形式子查询
      (1)替代表达式的子查询:AS
      (2)派生表(有时也称为内联视图):类似于临时表
      9.其他一些查询功能
      (1)开窗函数(分区或分组计算的函数)
      OVER子句与聚合函数结合使用:SELECT----SUM(列)OVER(PARTITION BY----)AS’ —‘
      OVER子句与排名函数一起使用
      RANK()函数:函数并不一定返回连续整数
      DENSE_RANK()返回连续的整数值
      NTILE()返回所属组的编号
      ROW_NUMBER()返回结果集中每个分区内的序列号,每个分区的第一行从1开始
      (2)公用表表达式(CTE,不允许出现重复的列名)
      WITH—AS(SELECT语句)
      可以在一个WITH子句中定义多个CTE,也可以在一个查询中多次引用同一个CTE
      六、数据库及数据库对象
      1.SQL Server数据库概述
      (1)系统数据库:
      Master:记录所有系统级信息包括实例范围的元数据(例如登录账户)、端点、连接服务器和系统配置设置
      Msdb:供SQL Server代理服务调度报警和作业以及记录操作员时使用,保存关于调度报警、作业、操作员等信息。作业的执行不需要任何人人工干预
      Tempdb:临时数据库,每次启动会重新创建
      Resource:只读数据库
      (2)数据库的组成
      数据文件:主要数据文件(.mdf,只有一个,且不能小于3MB),次要数据文件(.ndf,0个或多个)
      事务日志文件.ldf
      数据库存储空间的分配:1页有8KB,行不能跨页存储
      (3)数据库文件组:主文件组(PRIMARY),用户定义文件组(FILEGROUP)
      (4)数据库文件的属性:文件名及其位置、初始大小、增长方式、最大大小
      2.创建数据库
      SELECT DATABASE database_name
      ON PRIMARY
      (NAME=----,
      FILENAME=’----‘,
      SIZE=—,
      MAXSIZE=----,
      FILEGOWTH=----)
      LOG ON(

    )
    3.修改数据库
    (1)扩大数据中已有文件的大小
    (2)为数据库添加新的文件
    ALTER DATABASE —
    ADD FILE(添加新的数据文件)
    ADD LOG FILE(添加新的日志文件)
    MODIFY FILE(指定要修改的文件)
    4.收缩数据库空间
    (1)收缩整个数据库的大小:DBCC SHRINKDATABASE
    (2)收缩指定文件的大小:DBCC SHRINKFILE
    5.添加和删除(REMOVE FILE)数据库文件
    6.分离(EXEC sp_detach_db ‘ ‘,’ ture|false‘)和附加(ON—FOR)数据库
    7.架构
    定义架构:create schema(架构)----authorization(授权)----
    删除架构drop schema <架构名> {|}
    Cascade:删除架构的同时将该架构中的所有架构对象一起全部删除
    Restrict:如果被删除的架构中包含架构对象,则拒绝删除此架构
    8.分区表(水平方式)
    创建分区函数:create patition function----as rangeleft|right for values----
    创建分区方案:create patition scheme—as partition—to----
    9.索引
    (1)创建索引:create [unique] [clustered] [nonclustered] index—on—
    (2)删除索引:drop index----
    10.索引视图:建有唯一聚集索引的视图称为索引视图,也称为物化视图。
    (1)适合建立索引试图的场合:
    如果很少更新基础数据,则索引视图的效果更佳
    如果经常更新基础数据,则维护索引视图的成本可能超过使用索引视图带来的性能收益
    如果基础数据以批处理的形式定期更新,但在更新之间主要作为只读数据进行处理,则考虑在更新前删除所有索引视图,然后再重建索引视图,这样做可以提高更新的性能。
    索引视图可以提高下列查询类型的性能:处理大量行的连接和聚合,许多查询经常执行的连接和聚合操作。
    索引视图通常不会提高下列查询类型的性能:具有大量写操作的OLTP系统,具有大量更新操作的数据库,不涉及聚合或连接的查询,GROUP BY列具有高基数度的数据聚合(高基数度表示列包含许多不同的值)
    (2)定义索引视图:
    定义索引视图时,视图不能引用任何其他视图,只能引用基本表。
    视图引用的所有基本表必须与视图位于同一个数据库,并且所有者也与视图相同
    必须使用schemabinding选项创建索引视图
    视图中的表达式引用的所有函数必须是确定的
    对视图创建的第一个索引必须是唯一聚集索引,之后再创建其他的非聚集索引
    定义视图
    Create view — with schemabinding as (select语句)go
    在视图上定义一个唯一聚集索引
    Create unique clustered index — on ---- go
    六、数据库后台编程技术
    1.存储过程(系统存储过程、用户自定义存储过程、扩展存储过程)
    创建存储过程create proc|procedure ---- as----
    执行存储过程:exce — ---
    删除存储过程:drop proc -----
    2.用户定义函数(标量函数、内联表值函数、多语句表值函数)
    (1)创建和调用标量函数(返回单个数据值的函数)
    定义标量函数:create function— as begin — return — end
    调用标量函数(函数拥有者名和函数名必须要有):select ----
    (2)创建调用内联表值函数(返回值是一个表,该表的内容是一个查询语句的结果)
    创建内联表值函数:create function---- returns(表名)as return(select语句)
    调用内联表值函数:select * from—
    (3)创建和调用多语句表值函数(返回一个表,表中的内容类似于存储过程
    创建多语句表值函数:create function—returns— as begin—return end
    调用多语句表值函数:select * from----
    (4)删除用户自定义函数:drop function----
    3.触发器(DML、DDL和登录触发器三种)
    触发器是一种特属于的存储过程,其特殊性在于它不需要由用户来直接调用,而是在对表中的数据进行更新,插入或删除操作时自动触发执行的。触发器通常用于保证业务规则和数据完整性,其主要优点是用户可以用编程的方法来实现复杂的处理逻辑和商业规则,增强了数据完整性约束的功能。
    创建触发器:create trigger—on—as—
    创建后触发型触发器:create trigger—on—asif exists—begin rollback—end else----
    创建前触发型触发器:create trigger —on—as if not exists----
    删除触发器:drop trigger----
    4.游标(cursor)
    声明游标:declare----
    打开游标:open----(只能打开已声明的游标)
    提取游标:fetch—from
    关闭游标:close—
    释放游标:deallocate----
    七.安全管理
    (1)安全性是指保护数据以防止不合法的使用和造成数据被泄露、更改和破坏(保护数据以防止不合法的用户故意造成的破坏)
    (2)完整性是指数据的准确性和有效性(保护数据以防止合法用户无意中造成的破坏)
    数据库安全的威胁:可用性的损失、机密性数据的损失、私密性数据的损失、偷窃和欺诈、意外的损害
    (3)安全控制模型:身份验证、操作权限控制、文件操作控制、加密存储与冗余
    授权和认证:
    (4)授权和认证:授权是将合法访问数据库或数据对象的权限授予用户的过程。认证是一种坚定用户身份的机制。
    (5)存取控制
    自主存取控制:权限种类(语句权限、对象权限),用户分类(系统管理员、数据库对象拥有者、普通用户)
    强制存取控制:
    敏感度标记:绝密、秘密、可信、公开。
    主体的敏感度标记被称为许可证级别
    客体的敏感度标记被称为密级:仅当主体的许可证级别大于或等于客体的密级时,该主体才能读取相应的客体;仅当主体的许可证级别等于客体的密级时,该主体才能写相应的客体。
    橘皮书和紫皮书两份文献给出了通用安全性分级模式,共定义了D、C、B和A四类安全级别,从D类到A类级别依次增高。D类提供最小保护,C类提供自主保护,B类提供强制保护,A类提供验证保护。
    (6)审计跟踪:实质上是一种特殊的文件或数据库,系统在上面自动记录用户对常规数据的所有操作。
    1.SQL Server的安全控制
    Windows身份验证模式
    混合身份验证模式(SQL Server允许Windows授权用户和SQL授权用户登录到SQL Server数据库服务器
    建立登录账户:create login with — from—windows----with ---- password—
    修改登录账户属性:alter login—
    删除登录账户:drop login—
    建立数据库用户:create user— login----
    Guest用户:启用grant connect to—,禁用(回收)revoke connect to—
    删除数据库用户:drop user----
    角色:系统角色、用户定义角色
    Oracle的安全控制机制可分为数据库级的安全控制、表级、行级、列级
    运行状态监控与分析:自动监控机制、手动监控机制
    事务内部故障分为预期和非预期的,其中大部分是非预期的(运算溢出故障、并发事务死锁故障、违反了某些完整性限制而导致的故障等)

    展开全文
  • Oracle数据库知识点.pdf

    2021-10-11 00:46:45
    Oracle数据库知识点.pdf
  • 高薪职位面试数据库知识点总结
  • 数据库知识点

    2014-06-18 17:56:26
    数据库复习知识点,打开即可使用,无需输入密码,请君慢慢享用
  • 于2019.7-2019.10备战秋招,准备的C++软件开发岗的面试资料,资料涵盖了:1、C++中的基本语法、STL、C++11新特性的面试知识点;2、计算机网络的面试知识点...3、MYSQL数据库的面试知识点;4、计算机网络的面试知识点
  • 四级数据库知识点

    2011-09-22 23:24:58
    看后整体思路有所提高,知识点梳理清楚。有助于理解记忆知识。。
  • 计算机三级数据库知识点

    万次阅读 多人点赞 2018-09-15 21:35:40
    考完三级瞬间轻松,做题时记的知识点,(乱序版,懒得整理了,主要用于选择和填空)   dbo:database owner(数据库的创建者,创建该对象的用户.) guest:顾客(能够访问数据库中对象的数据,要求dbo分配权限给...

    考完三级瞬间轻松,做题时记的知识点,(乱序版,懒得整理了,主要用于选择和填空)

     

    dbo:database owner(数据库的创建者,创建该对象的用户.)
    guest:顾客(能够访问数据库中对象的数据,要求dbo分配权限给guest,一般给他查看的权限select,它没有对应的登录账户名)

    数据库应用系统设计每个步骤都按照数据组织与存储、数据访问与处理、应用设计等几个方面进行。数据库概念设计自下而上ER设计。
    数据库应用系统日常维护工作一般包括数据库的备份、锁管理、日志的维护等。
    数据库维护:1.数据库的转储和恢复2.数据库安全性、完整性控制;3.数据库性能功能的检测和改善;4.数据库的重组和重构
    数据库的重构应尽量避免应用程序的改动,部分修改内模式和外模式。
    数据库恢复顺序:1.最近完全数据库备份2.完全备份后的最近差异数据库备份3.完全备份和差异之后的所有日志备份。
    数据恢复三种模式:1)简单恢复模式(只用于测试和开发数据库,或用于主要包含只读数据的数据库,如数据仓库)。2)大容量日志恢复模式:一般只作为完整性恢复模式的附加模式,不支持点恢复。3)完整恢复模式
    数据库性能优化:
    1.数据库运行环境与参数调整:外部调整(CPU、网络、调整内存分配、调整磁盘I/O):增加CPU数量或终止需要许多资源的进程。令I/0时间最小化,减少磁盘上文件竞争带来的瓶颈。
    2.模式调整与优化。
    反规范化(增加派生冗余列,增加冗余列,重新组表、分割表和新增汇总表)破坏数据完整性。
    3.存储优化:1)索引视图。2)聚集。
    4.查询优化。1)合理使用索引。2)避免和简化排序。3)消除大型表数据的顺序存储。4)避免复杂正则表达式。5)使用临时表加速查询。6)不随意使用游标。7)事务处理
    数据库应用系统从功能角度分为四个层次:1,表现层2.业务逻辑层(将表现层获取的数据进行组织传递给数据访问层)3.数据访问层(负责与DBMS系统实现交互)4.数据持久层(负责保存和管理应用系统)
    分布式数据库中,导致数据传输量大的主要原因是数据间的连接操作和并操作。
    数据库模式定义语言DDL是用于描述数据库中要存储的现实世界实体的语言。
    聚集索引:索引文件按其索引列列值指定的顺序与数据文件中的数据记录的排列顺序相一致。

    索引视图只能引用同一数据库中同一所有者的对象。

    SOL Server 2008 支持三种用户自定义函数:标量函数、内嵌表值函数和多语句表值函数。标量函数可以出现在SELECT语句目标列中,其他两个函数必须放在SELECT和FROM子句中。

    INSTEAD OF创建前触发器,FOR或AFTER创建后触发器。
    一个表上可以建立多个后触发器,但只能建立一个前触发器。

    声明游标语句:DECLARE x CURSOR FOR  SELECT.......

    用户数据库文件扩展名为MDF

    分布式数据库分片类型:水平分片、垂直分片、导出分片、混合分片。
    分布式数据库最基本特征是本地自治、非集中管理以及高可用性。
    分布式数据库系统中的分布透明性是位置独立性、数据分片独立性和数据复制独立性。


    分布透明性包括分片透明性、位置透明性、局部数据模型透明性。分布式数据库的分片透明性是最高级别的透明性,位于全局概念与分片模式之间。位置透明性是下一层次,指用户只需了解数据分片情况,不需要了解场地。局部数据模型透明性用户需要了解场地。 

    数据处理大致可分为两类:OLTP(联合事务处理)--是传统的关系型数据库的主要应用,主要是基本的、日常的事务处理。OLAP(联合分析处理)--数据仓库的主要应用,支持复杂的分析操作,侧重决策支持,并且提供直观易懂的查找结果。
    ROLAP以关系数据库为核心,进行多维数据的表示和存储。
    MOLAP基于多维数组的OLAP的实现。
    HOLAP是基于混合数据组织的OLAP实现。

    default表示如果缺省,默认一个值。


    视图的特点:1.简单性。2.安全性3.逻辑独立性
    视图的作用:1.简化数据查询语句2.使用户从多角度看待同一数据3.提高数据的安全性4.提供了一定程度的逻辑独立性。
    对视图创建唯一聚集索引后,视图的结果集将存储在数据库中。
    子查询语句不可使用ORDER BY。

    分割表分为水平分割表和垂直分割表。分割表增加了维护数据完整性的代价。水平分割后,查询数据需要使用UNION操作,垂直分割后,查询数据需要JOIN操作。

    结构图包括用例图、类图、组件图、对象图和部署图()。

    基于C/S数据应用系统体系结构:客户端/服务器端架构,也叫胖客户端架构,客户端需要实现绝大多数的业务逻辑和界面展示,客户端一般需要单独开发。
    基于B/S架构,浏览器/服务器架构,瘦客户端。客户端只需安装WEB浏览器即可,且业务规则一般在WEB服务器端实现。
    SOL SERVER中的数据库分类:
    1.系统数据库:master,model,msdb,tempdb(前三个修改较少,可进行定期备份,tempdb是系统临时文件的数据库,每次启动后会清空,没必要备份)
    2.用户数据库。
    它的数据库组成:1.数据文件:1).mdf 主数据文件,只有一个,不能小于3MB 2).ndf 次数据文件,有0个或多个
    2.日志文件:.ldf 至少有一个

    数据库存储最小单位:数据页,1页是8kb的连续磁盘空间。行不能跨页存储。

    选择前三:TOP 3 WITH TIES 通常与order by相结合使用。
    选择前n%行:TOP n percent;

    数据表对某一列数据的唯一性约束有UNIQUE约束和PRIMARY约束。UNIQUE允许出现空值但不允许出现重复值,PRIMARY KEY不允许出现空值和重复值。

    SQL数据库服务器角色权限:
    bulkadmin:执行BULK INSERT语句。
    dbcreator:创建、修改、删除和还原数据库、
    diskadmin:管理磁盘文件。
    processadmin:管理在SQL SERVER实例中运行的进程。
    severadmin:配置服务器范围的设置。
    sysadmin:在SQL SERVER中进行任何活动,该角色的权限跨越所有其他固定服务角色。
    setupadmin:添加和删除链接服务器。

    SQL SERVER固定数据库角色:
    db_owner:具有在数据库中进行全部操作的权限。
    db_datawriter:具有插入删除和更新权限。
    db_datareader:具有查询数据库所有用户数据权限。
    db_denydatareader:不具有查询数据库权限。
    db_backupoperator:具有备份数据库、备份日志权限。
    db_securityadmin:具有管理数据库角色、角色成员以及数据库中的语句和对象的权限。
    db_accessadmin:具有添加或删除数据库用户的权限。
    数据库管理系统一般通过周期性检查事务等待图来实现死锁检测。(出现回路则发生了死锁)

    并发操作带来的数据不一致性包括丢失修改、不可重复读和读脏数据。产生上述三类数据的不一致性主要原因就是并发操作破坏了事务的隔离性,所以数据库管理系统必须提供并发控制机制。

    系统故障造成数据库不一致状态,对于故障发生前已提交的事务(既有BEGIN Transaction,而没有commit或rollback)做redo操作,对于故障发生时尚未完成的事务(只有BEGIN Transaction,而没有commit或rollback)做undo操作。


    CLUSTERED(clustered)表示聚簇索引。 
    UNIQUE CLUSTERED:唯一非聚簇索引。

    HAVING内可以使用聚合函数,如(sum,min,max,avg,count),WHERE中不可以。

    顺序图:横向矩形代表对象,水平消息线用于对象间的通信,虚线称作“生命线”,表示一个对象在其时间周期内的存在。

    游标:
    在DECLARE中cursor声明游标,创建一个命名的查询语句。
    open:打开游标。    fetch:取出游标中的一条记录装入变量。   close:释放游标。
    SCROLL关键字指明游标可以在任意方向上滚动,所有fetch选项(first,last,prior,next【当前位置的下一行】,relative n【从当前位置数,第n行】,absolute【从游标的第一行开始数,第n行】,Into)都可以在游标中使用,若忽略该选项,则游标只能向前滚动(next)。

    安全性级别:A类:提供验证保护。B类:提供强制保护。C类:提供自主保护。D类:提供最小保护。

    UML图:
    1)用例图:描述系统与外部系统及用户之间的交互。
    2)类图
    3)对象图
    4)序列图
    5)协作图:交互图的一种,其中包含一组对象、对象之间的联系以及对象间发送和接受的消息。
    6)状态图:用来描述一个实体在发生一些事件时的状态变化情况。
    7)活动图:陈述活动与活动之间的流程控制的转移。
    8)构建图
    9)部署图:

    用户自定义函数包括标量函数和表值函数。
    在使用REVOKE命令回收用户权限时,如果要将用户转授给其他用户的权限也一并回收,则命令中需要加上CASCADE选项。对应于GRANT语句中的WITH GRANT OPTION。

    定义索引语句:CREATE UNIQUE CLUSTERED INDEX 索引名 ON 视图名。

    数据库故障:1.事务内部故障2.系统故障3.介质故障4.计算机病毒。

    分布式数据库有局部数据库管理系统、全局数据库管理系统GDBMS、全局数据字典、通信管理四部分组成。

    ETL是实现数据集成的主要技术。即填充更新数据仓库的数据抽取、转换、装载的数据采集过程。

    DFD的主要组成包括外部实体(外部项)、处理过程、数据存储和数据流。

    主文件组是系统定义好的一个文件组,它包含主要数据文件和任何没有明确分配给其他文件组的其他数据文件。
    日志文件不包含在文件组里。
    三层浏览器/服务器结构的数据库应用系统是由浏览器、应用服务器和数据库服务器三部分组成,触发器是在数据库服务器上执行的。

    用例之间的关系包括扩展、使用和组合。

    开窗函数公式:函数名 OVER(),OVER关键字表示把函数当成开窗函数而不是聚合函数。聚合开窗函数只能使用PARTITION BY子句或不带任何语句。ORDER BY不能与聚合函数一同使用。

    并行数据库结构:1)共享内存结构。2)共享磁盘结构。3)无共享结构

    数据挖掘的三个阶段:数据准备、数据挖掘、结果的解释和评估。

    备份日志语法:BACKUP LOG 数据库名 TO 备份的地方
    数据仓库是一个面向主题的、集成的、非易失的,且随时间变化的数据集合。

    关联规则的成立一般与支持度和置信度两个指标进行描述。
    SQL四个排名函数:
    1.rank():返回结果集的分区内每行的排名。
    2.dense_rank():返回结果集分区中行的排名。
    3.row_number():返回结果集分区内行的序列号。
    4.ntile():将有序分区中的行分发到指定数组的组中。

    SQL SERVER主要采用发布、分发、订阅的方式来处理复制。复制技术包括快照复制、事务复制和合并复制。  

     

    操作题:

    创建存储过程:

    create procedure 存储名

    @参数名 参数类型,@参数名 参数类型 outpout//此为输出函数

    as

    begin

    /*SQL语句*/

    end

    如:

    创建触发器:

    create trigger 触发器名

    on 操作的表名 for 操作类型/*主要有insert,update,drop*/

    as

    begin

    end

     

    创建分区表步骤:

    1)创建分区函数

    create partion function 函数名

    as range left for(a1,a2,a3)//分区的范围

    2)创建分区方案

    create partition scheme

    as 分区函数名//表示依据分区函数

    to(a1,a2,a3,a4)//分组

    3)使用分区方案创建表

    create table 表名(

    各个属性值及类型

    )

    on 分区方案

     

    创建自定义函数:

    create function 函数名 

    returns 返回值类型/*可以是一个表,类似于结构体*/

    /*例:

    @表名 table(

    各个属性值及类型

    )*/

    as

    begin

    end

    展开全文
  • 学生学习sql server的数据库课件完整课件资料,课件包含习题,实验,知识点。复习题等等,数据库编程,关系数据库,查询处理和查询优化,触发器实验,数据库安全,数据库导论,数据库复习题,完整性约束
  • 数据库知识点整理,包括数据库和表的创建,增删改查,子查询,关联查询,连接,交并差集,索引,序列,视图,各种函数,DDL、DML、DQL的实例。 存储过程,触发器,PL/SQL的基本使用。 每个知识点都有基于ORACLE ...
  • MySQL数据库知识点总结.docx
  • 三级数据库知识点学习(一)

    千次阅读 2020-01-09 18:14:05
    数据挖掘的目标是从数据库中发现隐含的、有意义的知识。 数据挖掘技术包括: 关联分析 序列分析 分类分析 预测分析 聚类分析 时间序列分析 数据挖掘的流程: 问题定义 建立数据挖掘库 ...
  • Java 程序员常用的8个数据库知识点

    千次阅读 2018-07-03 02:46:07
    本篇 Chat 以 Oracle 数据库为例,从以下8个方面对Java程序员常用数据库知识点进行阐述: 什么是数据库的 DDL 和 DML? 创建数据库的用户并授权。 如何优雅地建立一张数据库的表? 数据库表的...
  • 数据库知识点整理,包括数据库和表的创建,增删改查,子查询,关联查询,连接,交并差集,索引,序列,视图,各种函数,DDL、DML、DQL的实例。 每个知识点都有基于ORACLE scott 账户下employee 表的实例代码。
  • 数据库sql语句和重要知识点总结
  • 这是我三级数据库考试的时候总结的知识点,自己觉得比较重要的部分都高亮了,如果大家考试三级数据库的时候可以下载下来作为参考.
  • 自制数据库知识梳理思维导体思维导图 期末数据库原理98分 我jio得没问题
  • 全国计算机等级考试三级数据库知识点总结

    千次阅读 多人点赞 2019-07-05 12:56:39
    知识点(部分,分类可能不准确) 一、数据库引用系统开发方法 1.数据库运行维护工作:日常维护;监控与分析;性能优化调整;系统进化。 2.DBAS实施与部署工作:建立数据库结构;数据加载;事务和应用程序的编码及...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 451,842
精华内容 180,736
关键字:

数据库知识点