数据库,SQL专项练习

数据库

1、数据模型

关系模型：用二维表结构表示实体以及实体间联系。
层次模型：用树形结构表示各类实体以及实体间的关系。
网状模型：用有向图表示各类实体以及实体间的关系。

2、数据库事务的四大特性（ACID）

1）原子性（Atomicity）
原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。
2）一致性（Consistency）
一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。
拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。
3）隔离性（Isolation）
隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。
即要达到这么一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。
4）持久性（Durability）
持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作

3、数据库系统（Database System，DBS）

数据库系统包括数据库和数据库管理系统 [数据库系统>数据库管理系统>数据库]

4、数据库三级模式结构

三级模式结构:外模式、模式和内模式
（1）外模式： 又称子模式或用户模式，对应于用户级。它是某个或某几个用户所看到的数据库的数据视图，是与某一应用有关的数据的逻辑表示。外模式是从模式导出的一个子集，包含模式中允许特定用户使用的那部分数据。用户可以通过外模式描述语言来描述、定义对应于用户的数据记录(外模式)，也可以利用数据操纵语言(Data Manipulation Language，DML)对这些数据记录进行操作。外模式反映了数据库系统的用户观。
外模式是保证数据库安全性的一个有力措施。每个用户只能看见和访问所对应的外模式中的数据，数据库中的其余数据是不可见的。
（2）模式/概念模式： 又称模式或逻辑模式，对应于概念级。它是由数据库设计者综合所有用户的数据，按照统一的观点构造的全局逻辑结构，是对数据库中全部数据的逻辑结构和特征的总体描述，是所有用户的公共数据视图(全局视图)。它是由数据库管理系统提供的数据模式描述语言(Data Description Language，DDL)来描述、定义的。概念模式反映了数据库系统的整体观。
模式就是数据库数据在逻辑上的视图，且一个数据库只有一个模式。实际工作中，模式就等同于程序员创建一个具体的数据库的全部操作。
（3）内模式： 内模式又称存储模式，对应于物理级。它是数据库中全体数据的内部表示或底层描述，是数据库最低一级的逻辑描述，它描述了数据在存储介质上的存储方式和物理结构，对应着实际存储在外存储介质上的数据库。内模式由内模式描述语言来描述、定义的。内模式反映了数据库系统的存储观。
SQL语言支持数据库三级模式结构。
在SQL中，模式对应于基本表，内模式对应于存储文件，外模式对应于视图和部分基本表，元组对应于表中的行，属性对应于表中的列。

5、关系数据库约束

任何关系数据库系统都应该支持实体完整性（可以理解为主键约束）和参照完整性（可以理解为外键约束）。除此之外，不同的关系数据库系统根据其应用环境的不同，往往还需要一些特殊的约束条件，用户定义的完整性就是针对某一具体关系数据库的唯一约束条件。

1）实体完整性约束—主键约束

2）参照完整性约束—外键约束

3）用户自定义完整性约束—检查/非空/默认值约束

6、数据库保护

数据库保护又叫做数据库控制，是通过四方面实现的：
1）安全性控制
2）完整性控制
3）并发性控制
4）数据恢复

7、数据库的并发控制问题

丢失修改： 两个不同的事务同时获得了相同的数据,又都对这个数据进行了修改,那么先提交的事务的更新就会被后提交事务的更新覆盖掉.
脏读（未提交读) ： 一个事务正在访问数据，而其他事务正在更新该数据，但尚未提交（回滚），此时会发生脏读问题，即第一个事务所读取的数据是“脏”的。
不可重复读： 在一个事务内多次读同一数据，在该事务还没有结束时，另外的事务也访问该数据并对其做修改，从而导致第一个事务两次读取的数据不一样。
幻读： 有两种情况
(1):事务T1按一定条件从数据库中读取了某些数据记录后,事务T2删除了其中部分记录,当T1再次按照相同条件读取数据时,发现某些记录神秘的消失了.
(2):事务T1按一定条件从数据库中读取了某些数据记录后,事务T2插入了一些记录,当T1再次按照相同条件读取数据时,发现多了一些记录.

8、闭包

闭包就是由一个属性直接或间接推导出的所有属性的集合，例如：
f={a->b,b->c,a->d,e->f}
由a可直接得到b和d，间接得到c，则a的闭包就是{a,b,c,d}

9、数据库中有可能会存在不一致的数据。

造成数据不一致的原因主要有：
1）数据冗余
如果数据库中存在冗余数据，比如两张表中都存储了用户的地址，在用户的地址发生改变时，如果只更新了一张表中的数据，那么这两张表中就有了不一致的数据。
2）并发控制不当
比如某个订票系统中，两个用户在同一时间订同一张票，如果并发控制不当，可能会导致一张票被两个用户预订的情况。当然这也与元数据的设计有关。
3）故障和错误
如果软硬件发生故障造成数据丢失等情况，也可能引起数据不一致的情况。因此我们需要提供数据库维护和数据恢复的一些措施。

10、索引

索引会消耗更多的磁盘空间，并且在更新索引字段的时候，效率会更加的低下，所以是越少越好。

11、数据库的关系模式函数依赖

函数依赖的特性：
· 设X,Y是关系R的两个属性集合，当任何时刻R中的任意两个元组中的X属性值相同时，则它们的Y属性值也相同，则称X函数决定Y，或Y函数依赖于X。
· 当关系中属性集合Y是属性集合X的子集时(Y⊆X)，存在函数依赖X→Y，即一组属性函数决定它的所有子集，这种函数依赖称为平凡函数依赖。
· 当关系中属性集合Y不是属性集合X的子集时，存在函数依赖X→Y，则称这种函数依赖为非平凡函数依赖。
· 设X,Y是关系R的两个属性集合，X’是X的真子集，存在X→Y，但对每一个X’都有X’!→Y，则称Y完全函数依赖于X。
· 设X,Y是关系R的两个属性集合，存在X→Y，若X’是X的真子集，存在X’→Y，则称Y部分函数依赖于X。
· 设X,Y,Z是关系R中互不相同的属性集合，存在X→Y(Y !→X),Y→Z，则称Z传递函数依赖于X。

12、关系数据库设计

需求分析阶段的任务是在调查、分析的基础上明确用户对系统的需求。
概念设计阶段的任务是设计概念模型，较著名的是E-R图。
逻辑设计阶段的任务是把概念模型转化为特定DBMS的逻辑结构(模式和外模式)。
物理设计阶段的任务是设计合适的物理(存储)数据库结构。

13、封锁机智

基本的封锁类型有排它锁 X 和共享锁 S。

14、数据库系统特点

1）数据结构化
2）数据的共享性高，冗余度低，易扩充
3）数据独立性高
4）数据由数据库管理系统DBMS统一管理和控制
DBMS必须提供以下几方面的数据控制功能：
数据的安全性保护（security）
数据的完整性检查（integrity）
数据库的并发访问控制（concurrency）
数据库的故障恢复（recovery）

15、各种数据库

关系型数据库：MySQL、DB2、PostgreSQL
非关系型数据库：Mongo DB

SQL

1、定义

sql：一种是独立的交互使用数据查询、数据更新等操作，称为自含语言。另一种是嵌入到某种高级语言中，作为高级语言的一种扩充，是程序员编程时既可使用数据库语言又可使用常规的程序设计语言，这时高级语言叫宿主语言。

2、SQL的数据更新命令

insert ，update，delete都会造成数据更新。create一般是创建表。

3、SQL主要分为四个部分：

1）DDL（Data Definition Language）数据库定义语言

用于定义SQL模式、基本表、视图和索引的创建和撤消操作。
eg. CREATE ALTER DROP TRUNCATE COMMENT RENAME

2）DML（Data Manipulation Language）数据操纵语言

数据操纵分成数据查询和数据更新两类。数据更新又分成插入、删除、和修改三种操作。
eg. SELECT INSERT UPDATE DELETE MERGE CALL EXPLAIN PLAN LOCK TABLE

3）DCL（Data Control Language）数据库控制语言

包括对基本表和视图的授权，完整性规则的描述，事务控制等内容。
eg. GRANT 授权, REVOKE 取消授权

4) TCL（Transaction Control Language）事务控制语言

涉及到SQL语句嵌入在宿主语言程序中使用的规则。
eg. SAVEPOINT 设置保存点, ROLLBACK 回滚, SET TRANSACTION

4、SQL语言的功能

数据定义、数据控制、数据操纵。
数据定义：DDL data definition language 定义三大模式结构，两级映射，约束，如create turncate comment
数据操作：DML data manipulation language 增删改查功能，如insert select
数据控制：视图的授权DCL control如grant revoke， 事务控制TCL transation control如rollback等

5、视图

show create view view_name;

6、删除表

而DELETE和TRUNCATE TABLE都是删除表中的数据的语句，它们的不同之处在于：
1）TRUNCATE TABLE比DELETE的速度快；
2）TRUNCATE TABLE 是删除表的所有行，而DELETE是删除表的一行或者多行（除非DELETE不带WHERE语句）；
3）在删除时如果遇到任何一行违反约束（主要是外键约束），TRUNCATE TABLE仍然删除，只是表的结构及其列、约束、索引等保持不变，但DELETE是直接返回错误；
4）对于被外键约束的表，不能使用TRUNCATE TABLE，而应该使用不带WHERE语句的DELETE语句。
5）如果想保留标识计数值，要用DELETE，因为TRUNCATE TABLE会对新行标志符列搜用的计数值重置为该列的种子。

7、主键

表中删除主键为：
alert table table_test drop primary key;
表中增加主键为：
alert table table_test add primary key(id);