数据库与SQL简要复习

1.     数据视图与数据模型
数据抽象共有三个层次：物理层、逻辑层和视图层。在逻辑层使用的数据模型包括两类：一类是概念数据模型，主要用于数据库设计，它能被一般的用户理解，与人的思维表达方式比较接近。这样的模型有实体-联系模型（ERM）；另一类是逻辑数据模型，按计算机系统的观点对数据建模，使得数据更适合用计算机加以表示。这里模型主要用于DBMS的实现，比如关系模型、面向对象模型、层次模型和网状模型。设计师构建数据库模式的方法通常是首先使用E-R模型在高层对数据建模，然后再将其转变成关系模型。在物理层使用的数据模型称为物理数据模型。
数据模型通常由数据结构、数据操作和完整性约束三部分组成。
2.     数据库语言
根据功能不同，可以将数据库系统提供的语言分成三种类型：1）数据定义语言（DDL），用于定义数据库模式；2）数据操纵语言（DML），用于对数据库进行查询和更新；3）数据控制语言（DCL），用于对数据进行权限管理。
3.     数据库模式
根据数据的不同抽象层次，数据库有三级模式：物理模式（内模式）在物理层描述数据库中全体存储结构和存取方法，而逻辑模式（概念模式）则在逻辑层描述数据库中全体数据的逻辑结构和特征。在视图层也可分为若干模式，称为子模式（外模式），它描述了数据库用户能够看见和使用的局部数据的逻辑结构和特征。通常一个数据库只有一个物理模式和一个逻辑模式，但是子模式有若干个。
4.     E-R图
E-R图由以下元素构成：1）矩形，代表实体型；2）椭圆，代表属性；3）菱形，代表联系；4）线段，将属性和实体性相连，或将实体型和联系相连。
5.     关系模型
关系模型由关系数据结构、关系操作集合和关系完整性约束三部分构成。在关系模型中，现实世界实体以及实体间的联系均用关系来表示。
关系模型中常用的关系操作包括：选择、投影、连接、除、并、交、差等查询操作和增加、删除、修改两大部分。关系操作的特点是集合操作方式，即操作的对象和结果都是集合。关系操作可以使用两种方式定义：基于代数的定义称为关系代数；基于逻辑的定义称为关系演算。由于使用变量的不同，关系演算又分为元组关系演算和域关系演算。
关系模型允许定义三类完整性约束：实体完整性、参照完整性和用户定义完整性。其中实体完整性和参照完整性是关系模型必须满足的完整性约束条件。实体完整性规则是：关系的主码不能取空值。参照完整性规则是：外码必须是另一个表中主码的有效值，或者是“空值”。
6.     连接运算
连接运算是从两个关系的乘运算结果中选取属性间满足一定条件的元组，构成新的关系。连接运算有两种：等值连接和自然连接。自然连接要求两个关系中进行比较的分量必须是相同的属性组，并且在结果中把重复的属性列去掉。
7.     SQL
关系数据库系统支持三级模式结构，其模式（数据库）、外模式和内模式中的基本对象有表、视图和索引。因此SQL的数据定义功能包括模式定义、表定义、视图定义和索引定义。SQL通常不提供修改模式定义、修改视图定义和修改索引定义。用户如果想修改这些对象，只能先将它们删除，然后再重建。
8.     基本表的操作
1)       创建表
create table 基本表名
(列名类型，
……
完整性约束，
……
)
完整性约束主要有三种子句：主键子句（primary key）、外键子句（foreignkey）和检查子句（check）。
2)       修改表
alter  table  <基本表名>  add  <列名>  <类型>
alter  table  <基本表名>  drop  <列名>  <类型>  [cascade | restrict]（cascade表示所有引用到该列的视图和约束也要一起自动删除；restrict表示在没有视图或约束引用该属性时，才能在本表中删除该列，否则拒绝删除。）
alter  table  <基本表名>  modify  <列名>  <类型>
3)       撤销表
drop  table  <基本表名>  [cascade | restrict]
9.     SQL查询
where子句中可以使用下列运算符：
l  算术运算符
l  逻辑运算符
l  字符串匹配运算符，包括like，not like
l  集合成员资格运算符，包括in，not in
l  谓词，包括exists，all，some，unique
l  聚合函数，包括avg，min，max，sum和count
l  还可以是另一个select语句
select语句完整语法：
        select  目标表的列名或列表达式序列
        from 基本表名和（或）视图序列
        [where 行条件表达式]
        [group by  列名序列]
               [having  组条件表达式]
        [order by 列名[asc | desc]]
整个语句的执行过程如下：
1)       读取from子句中基本表、视图的数据，执行笛卡尔积操作；
2)       选取满足where子句中给出的条件表达式的元组；
3)       按group子句中指定列的值分组，同时提取满足having子句中组条件表达式的那些组；
4)       按select子句中给出的列名或列表达式求值输出；
5)       order子句对输出的目标表进行排序，按附加说明asc升序排列，或按desc降序排列。
10. 视图
创建视图：
        create view <视图名> [<列名> <列名>…]
        as <子查询>
        [with check option]
with check  option表示对视图进行增删改是要保证操作的行满足视图定义中的谓词条件（即子查询中的条件表达式）。
视图最终是定义在基本表之上的，对视图的一切操作最终也要转换为对基本表的操作。视图的好处：
l  视图能够简化用户的操作
l  视图是用户能以多种角度看待同一数据
l  视图对重构数据库提供了一定程度的逻辑独立性
l  视图能够对机密数据提供安全保护
11. 事务
事务是一系列的数据库操作，是数据库应用程序的基本单元，是反映现实世界需要以完整单位提交的一项工作。事务是用户定义的一个数据库操作序列。
事务的四个特征：原子性、一致性、隔离性和持久性。
事务处理包括数据库恢复和并发控制。数据库恢复有两个目的：保证事务的原子性和使数据库能恢复到正确状态。
数据恢复的原理概括为冗余，建立冗余数据最常用的技术是数据转储和登录日志文件。数据转储是由DBA定期地将整个数据库复制到磁盘或另一个磁盘上保存起来的过程。
12. 触发器
一个触发器用来定义一个条件以及在该条件为真时需要执行的动作。通常，触发器的条件以断言的形式定义。动作以过程的形式定义。
13. 索引
索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。
1.数据库是一个以某种有组织的方式存储的数据集合。最简单的办法是将数 据库想象为一个文件柜。这个文件柜是一个存放数据的物理位置，不管数据是什么，也不管数据是如何组织的。
2.数据库（database） 保存有组织的数据的容器（通常是一个文件或一组文件）。 
3.人们通常用数据库这个术语来代表他们使用的数据库软件，这是不正确的，也因此产生了许多混淆。确切地说，数据库软件应称为数据库管 理系统（即DBMS）。数据库是通过DBMS创建和操纵的容器，而具体它究竟是什么，形式如何，各种数据库都不一样。
4.表是一种结构化的文件，可用来存储某种特定类型的数据。表可以保存顾客清单、产品目录，或者其他信 息清单。 表（table）， 某种特定类型数据的结构化清单。 这里的关键一点在于，存储在表中的数据是同一种类型的数据或清单。决不应该将顾客的清单与订单的清单存储在同一个数据库表中，否则以 后的检索和访问会很困难。应该创建两个表，每个清单一个表。 数据库中的每个表都有一个名字来标识自己。这个名字是唯一的，即数据库中没有其他表具有相同的名字。 
5.表具有一些特性，这些特性定义了数据在表中如何存储，包含存储什么样的数据，数据如何分解，各部分信息如何命名等信息。描述表的这组 信息就是所谓的模式（schema），模式可以用来描述数据库中特定的表，也可以用来描述整个数据库（和其中表的关系）。模式      关于数据库和表的布局及特性的信息。 
6.表由列组成。列存储表中某部分的信息。   列（column） 表中的一个字段。所有表都是由一个或多个列组成的。 数据库中每个列都有相应的数据类型。   数据类型（datatype）定义了列可以存储哪些数据种类。例如，如果列中存储的是数字（或许是订单中 的物品数），则相应的数据类型应该为数值类型。如果列中存储的是日期、文本、注释、金额等，则应该规定好恰当的数据类型。 
7.表中的数据是按行存储的，所保存的每个记录存储在自己的行内。如果将表想象为网格，网格中垂直的列为表列，水平行为表行。 例如，顾客表可以每行存储一个顾客。表中的行编号为记录的编号。   行（row） 表中的一个记录。   你可能听到用户在提到行时称其为数据库记录（record）。这两个术语多半是可以交替使用的，但从技术上说，行才是正确的术语。 
8.表中每一行都应该有一列（或几列）可以唯一标识自己。顾客表可以使用顾客编号，而订单表可以使用订单ID。雇员表可以使用雇员ID或雇员 社会安全号。 主键（primary key） 一列（或一组列），其值能够唯一标识表中每一行。 唯一标识表中每行的这个列（或这几列）称为主键。主键用来表示一个特定的行。没有主键，更新或删除表中特定行就极为困难，因为你不能 保证操作只涉及相关的行。  提示：应该总是定义主键 虽然并不总是需要主键，但多数数据库设计者都会保证他们创建的每个表具有一个主键，以便于以后的数据操作和管理。 
9.表中的任何列都可以作为主键，只要它满足以下条件： 任意两行都不具有相同的主键值； 每一行都必须具有一个主键值（主键列不允许NULL值）； 主键列中的值不允许修改或更新； 主键值不能重用（如果某行从表中删除，它的主键不能赋给以后的新行）。 主键通常定义在表的一列上，但并不是必需这么做，也可以一起使用多个列作为主键。在使用多列作为主键时，上述条件必须应用到所有列， 所有列值的组合必须是唯一的（但单个列的值可以不唯一）。 
10.正如第1课所述，SQL语句是由简单的英语单词构成的。这些单词称为关键字，每个SQL语句都是由一个或多个关键字构成的。最经常使用的 SQL语句大概就是SELECT语句了。它的用途是从一个或多个表中检索信息。 关键字（keyword）   作为SQL组成部分的保留字。关键字不能用作表或列的名字。附录E列出了某些经常使用的保留字。
11.SQL语句不区分大小写，因此SELECT与select是相同的。同样，写成Select也没有关系。许多SQL开发人员喜欢对SQL关键字使用 大写，而对列名和表名使用小写，这样做使代码更易于阅读和调试。不过，一定要认识到虽然SQL是不区分大小写的，但是表名、列名和值 可能有所不同（这有赖于具体的DBMS及其如何配置）。 
12.子句（clause） SQL语句由子句构成，有些子句是必需的，有些则是可选的。一个子句通常由一个关键字加上所提供的数据组成。子句的例子有我们在前一 课看到的SELECT语句的FROM子句。 
13.数据库表一般包含大量的数据，很少需要检索表中的所有行。通常只会根据特定操作或报告的需要提取表数据的子集。只检索所需数据需要指 定搜索条件（search criteria），搜索条件也称为过滤条件（filter condition）。 在SELECT语句中，数据根据WHERE子句中指定的搜索条件进行过滤。WHERE子句在表名（FROM子句）之后给出。例如：
SELECT prod_name, prod_price 
FROM Products 
WHERE prod_price = 3.49; 
14.SQL支持下表列出的所有条件操作符：
WHERE子句操作符


聚集函数（aggregate function） 对某些行运行的函数，计算并返回一个值。 

SQL最强大的功能之一就是能在数据查询的执行中联结（join）表。联结是利用SQL的SELECT能执行的最重要的操作，很好地理解联结及其语 法是学习SQL的极为重要的部分。
简单说，联结是一种机制，用来在一条SELECT语句中关联表，因此称为联结。使用特殊的语法，可以联结多个表返回一组输 出，联结在运行时关联表中正确的行。
把数据插入表中的最简单方法是使用基本的INSERT语法，它要求指定表名和插入到新行中的值。下面举一个例子：


使用UPDATE语句非常容易，甚至可以说太容易了。基本的UPDATE语句由三部分组成，分别是： 要更新的表； 列名和它们的新值； 确定要更新哪些行的过滤条件。 

更新表

word版
https://share.weiyun.com/5277b6ae5eda2d70e8fd04fa8f40a71a

1. 主键 超键 候选键 外键
   主 键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。
   超 键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。
   候选键：是最小超键，即没有冗余元素的超键。
   外 键：在一个表中存在的另一个表的主键称此表的外键。

2.数据库事务处理的原则及含义
数据库事务transanction正确执行的四个基本要素。ACID,原子性(Atomicity)、一致性(Correspondence)、隔离性(Isolation)、持久性(Durability)。
原子性:整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性:在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
隔离性:隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。
持久性:在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。
由于一项操作通常会包含许多子操作，而这些子操作可能会因为硬件的损坏或其他因素产生问题，要正确实现ACID并不容易。ACID建议数据库将所有需要更新以及修改的资料一次操作完毕，但实际上并不可行。
目前主要有两种方式实现ACID：第一种是Write ahead logging，也就是日志式的方式。第二种是Shadow paging。

3.视图的作用，视图可以更改么？
视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询；不包含任何列或数据。使用视图可以简化复杂的sql操作，隐藏具体的细节，保护数据；视图创建后，可以使用与表相同的方式利用它们。
视图不能被索引，也不能有关联的触发器或默认值，如果视图本身内有order by 则对视图再次order by将被覆盖。
创建视图：create view XXX as XXXXXXXXXXXXXX;
对于某些视图比如未使用联结子查询分组聚集函数Distinct Union等，是可以对其更新的，对视图的更新将对基表进行更新；但是视图主要用于简化检索，保护数据，并不用于更新，而且大部分视图都不可以更新。

1. where 与 Having的区别
   WHERE 在分组和聚集计算之前选取输入行（因此，它控制哪些行进入聚集计算）， 而 HAVING 在分组和聚集之后选取分组的行。因此，WHERE 子句不能包含聚集函数； 因为试图用聚集函数判断那些行输入给聚集运算是没有意义的。相反，HAVING 子句总是包含聚集函数。

5.delete与truncate的区别
delete 从已经建立的表中删除行数据；如果要删除整列需要使用Update;而且删除表时，删除的是表的内容而不是表本身；如果要删除表，使用Truncate Table可以更快，实际上它会删除原来的表并重新创建一个表，而非逐行删除表中的数据。

1. 交叉连接 内连接 左/右外连接
   内连接(INNER JOIN)使用比较运算符进行表间某(些)列数据的比较操作，并列出这些表中与连接条件相匹配的数据行。根据所使用的比较方式不同，内连接又分为等值连接、自然连接和不等连接三种。
   外连接分为左外连接(LEFT OUTER JOIN或LEFT JOIN)、右外连接(RIGHT OUTER JOIN或RIGHT JOIN)和全外连接(FULL OUTER JOIN或FULL JOIN)三种。与内连接不同的是，外连接不只列出与连接条件相匹配的行，而是列出左表(左外连接时)、右表(右外连接时)或两个表(全外连接时)中所有符合搜索条件的数据行。
   联结可以用子查询的方式代替，但是一般情况下，联结速度更快。

7.最常用Select语句格式
Select 列1，列2
from 表1，表2
where 设定列过滤条件
group by 设定分组列
Having 设定分组过滤条件
order by 列 DESC/ASC
LIMIT a,b 设定返回的行数

8.聚集索引 非聚集索引 B-tree Hash
索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。建立索引的目的是加快对表中记录的查找或排序。为表设置索引要付出代价的：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。
数据库索引可以分为，聚集索引与非聚集索引。索引键值的逻辑顺序与索引所服务的表中相应行的物理顺序相同的索引，被称为聚集索引，反之为非聚集索引，索引一般使用二叉树排序索引键值的，聚集索引的索引值是直接指向数据表对应元组的，而非聚集索引的索引值仍会指向下一个索引数据块，并不直接指向元组，因为还有一层索引进行重定向，所以非聚集索引可以拥有不同的键值排序而拥有多个不同的索引。而聚集索引因为与表的元组物理顺序一一对应，所以只有一种排序，即一个数据表只有一个聚集索引。
Hash索引与B-tree索引，Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引。
Hash 索引仅仅能满足”=”,”IN”和”<=>”查询，不能使用范围查询；由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。Hash 索引无法被用来进行数据的排序操作；由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来进行任何排序运算。
B-Tree 索引是 MySQL 数据库中使用最为频繁的索引类型。一般来说， MySQL 中的 B-Tree 索引的物理文件大多都是以 Balance Tree 的结构来存储的，也就是所有实际需要的数据都存放于 Tree 的 Leaf Node ，而且到任何一个 Leaf Node 的最短路径的长度都是完全相同的，所以我们大家都称之为 B-Tree 索引。当然可能各种数据库（或 MySQL 的各种存储引擎）在存放自己的 B-Tree 索引的时候会对存储结构稍作改造。如 Innodb 存储引擎的 B-Tree 索引实际使用的存储结构实际上是 B+Tree ，也就是在 B-Tree 数据结构的基础上做了很小的改造，在每一个Leaf Node 上面出了存放索引键的相关信息之外，还存储了指向与该 Leaf Node 相邻的后一个 LeafNode 的指针信息，这主要是为了加快检索多个相邻 Leaf Node 的效率考虑。在 Innodb 存储引擎中，存在两种不同形式的索引，一种是 Cluster 形式的主键索引（ Primary Key ），另外一种则是和其他存储引擎（如 MyISAM 存储引擎）存放形式基本相同的普通 B-Tree 索引，这种索引在 Innodb 存储引擎中被称为 Secondary Index 。

9.SQL查询优化的原则
1)仅查询需要的列，使用where子句等限制条件，避免使用select *
2)尽量不要使用LIKE谓词
3)慎用where子句，某些操作将导致全表扫描而无法使用索引，比如IS NULL、IN、OR、!=、<>等。
4)其他等

1. DB2 MYSQL 及Orcale各自特点

11.数据库约束范式
数据库规范化，又称数据库或资料库的正规化、标准化，是数据库设计中的一系列原理和技术，以减少数据库中数据冗余，增进数据的一致性。
现在数据库设计最多满足3NF，普遍认为范式过高，虽然具有对数据关系更好的约束性，但也导致数据关系表增加而令数据库IO更易繁忙，原来交由数据库处理的关系约束现更多在数据库使用程序中完成。
1NF: 如果关系模式中所有属性的值域内每一个值都不可分，则称其符合第一范式；要求每条记录都只能存放单一值，而且每笔记录都用主键来加以识别。
2NF: 在满足第一范式的基础上，所有记录都要和主键有完全依赖关系；如果有记录只和主键的一部份有关，就得把它们独立出来变成另一个资料表。如果一个资料表的主键只有单一一个栏位的话，它就一定符合第二正规化。
3NF: 在满足第二范式的基础上，要求每个非主属性都不传递依赖R的候选键；即要求所有的非键属性互相之间应该是无关的。
BCNF: 在第三范式的基础上要求，任何属性（包括非主键和主键）都不能被非主键所决定。