我的数据库总结

——————————zhangguang——dalian——2012—10—05————————————
DB 数据库

数据库：长期存储在在计算机内、有组织的、可共享的大量数据的集合。
数据库具有 永久存储 、 有组织 、可共享特征。

数据库管理系统(DBMS)功能:
1 数据定义(create)
2 数据组织、存储和管理
  DBMS要分类组织、存储和管理各种数据，包括数据字典、用户数据、数据的存取路径等。
3 数据操作(select insert delete alter)
4 数据库的事物管理和运行管理
  数据的建立、运行和维护时由数据库管理系统统一管理、统一控制，以保证数据的安全性、完整性、多用户对数据的并发使用及发生故障后的系统恢复。
5 数据库的建立和维护功能
6 其他功能

数据库系统DBS: DBMS DBA APPLICATION DB

DBS特点:
1 结构化数据
2 数据共享性高  冗余度低  易扩充
3 数据独立性高
4 数据由DBMS统一管理和控制(数据的安全性保护、数据的完整性检查、并发控制、数据库恢复)

数据模型是数据库系统的核心和基础。
数据模型包括：数据结构(对系统的静态描述)、数据操作(对系统的动态描述)和完整性约束(实体、参照和用户自定义完整性约束)。

概念、逻辑、物理模型

数据库的三级模式结构：外模式、模式和内模式
外模式(子模式或用户模式)：是数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述，是数据库用户的数据视图，是与某一应用有关的数据的逻辑表示。
模式：是数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图。
内模式：是数据物理结构和存储方式的描述，是数据在数据库内部的表示方式。

数据看的二级映像功能和数据独立性
外模式/模式映像：当模式改变时，有数据管理员对各个外模式/模式的映像作相应改变，可以使外模式保存不变。————数据的逻辑独立性
模式/内模式映像：当内模式改变时，有数据管理员对各个模式/内模式的映像作相应改变，可以使模式保存不变。————数据的物理独立性

关系的三种类型：基本关系(基本表或基表)、查询表和视图表。
关系的描述称为关系模式(relation schema) R(U,D,DOM,F) R是关系名，U为组成该关系的属性名集合，D为属性组U中属性所来自的域，DOM为属性向域的映像集合，F为属性间数据的依赖关系集合。

5种基本关系操作：选择、投影、并、差和笛卡尔积。
关系操作的特点是集合的操作方式，关系操作包含查询和插入删除和修改操作两大部分。传统的集合运算有：并(Union)、差(Except)、交(Intersection)、笛卡尔积(Cartesian Product)
专门的关系运算：选择、投影、连接、除等。
查询操作可以分为：选择(select)、投影(Project)、连接(join)、除(Divide)、并(Union)、差(Except)、交(Intersection)、笛卡尔积等。

SQL特点：
1 综合统一
2 高度非过程化
3 面向集合的操作方式
4 以同一种语法结构提供多种实用方式
5 语言简洁，易学易用

SQL功能      动词
数据查询     select
数据定义     create drop  alter
数据操纵     insert update delete
数据控制     grant  revoke

模式
create schema test authorization zhang
drop schema test cascade/restrict

表：
create table students
( son char(10) primary key,
  sname char(20) unique,
  ssex char(2) ,
  sage smallint,
  sdept char(20)
);

create table course
( con char(10) primary key,
  cname char(20) unique,
  cpno char(4) ,
  ccredit smallint,
  foreign key cpno references course(cno)
);

create table sc
( sno char(10),
  cno char(10),
  grade smallint,
  primary key (cno ,sno),
  foreign key (sno) references students(sno),
  foreign key (cno) references course(cno),
);

alter table students add s_enterence DATE;
alter table students alter column sage int;
alter table course add unique(cname);

drop table 表1 [cascade | restrict];

视图
create view is_student
as
select * from student  where sdept ='IS';

索引
是为加快查询速度的有效手段。
create [unique | cluster] index stusname on student(sname);
drop index stusname

select [all | distinct] x,x,x,x
from table1,table2
[where 条件表达式 ]
[group by 列名1]
[order by 列明2 ASC | DESC];

常用的查询条件：
查询条件            谓词
比较                =、>、<、>=、<=、!=、<>、!>、!<、not
确定范围            between  and,  not  between and ,
确定集合            in ,  not in
字符匹配            like,  not like
空值                is null ,   is not null
多重条件(逻辑运算)    and , or, not
**************************************************************************
select sname from student where sdept = 'IS';
select sname from student where sage < 20;
select sname from student where grade < 60;
select sname from student where sage between 20 and 30 ; (20~30包括20和30)
select sname from student where sage not between 20 and 30 ;
select sname from student where sdept in('CS','MA','IS');
select sname from student where sdept not in('CS','MA','IS');
select sname from student where sno like '200101';
select sname from student where sname like '张%';
select sname from student where sname not like '王_';
select sname from student where sname not like  '_阳%'
select sname from student where grade is null; (这里的is不能够用=代替)
select sname from student where grade not is null;
select sname from student where sdept ='IS' AND sage <20;
**************************************************************************
order by子句：
对查询结果按照一个或者多个属性列的升序asc或降序desc排列。
select sname from student where cno = '3' roder by grade DESC;
select sname from student where cno = '3' roder by sdept,grade DESC;
**************************************************************************
聚集函数：
count([distinct|all]*)      统计元祖个数
count([distinct|all]<列名>)  统计一列中值的个数
sum([distinct|all]<列名>)    计算一列值的总和
avg([distinct|all]<列名>)    计算一列的平均值
max([distinct|all]<列名>)    求一列值中最大值
min([distinct|all]<列名>)    求一列值中最小值

select count(*) from student;        //总人数
select count(distinct sno) from SC   //选修了课程的人数
select max(grade) from SC where cno = '1';
在聚集函数遇到空值时，除count(*)外，都跳过空值而只处理非控制。
**************************************************************************
group by子句：
将查询结果按某一列或队列的值分组，值相等的为一组。
select cno ,count(sno) from SC group by cno;           //每门课程相应的选课人数
select sno from SC group by sno having by count(*)>3   //选修了3门以上的学生学号

连接查询：略
**************************************************************************
复合条件查询：
select student.sno,sname from student,SC where student.sno = SC.sno and SC.cno = '2' and SC.grade >90;
**************************************************************************
带有IN谓词的子查询：
select sno,sname,sdept from student where sdept in (select sdept from student where sname = '张三');
**************************************************************************
带有比较运算符的子查询：
select sno,cno from SC X where grade >=(select avg(grade) from SC Y where x.sno = y.sno);
**************************************************************************
带有any(some)或all谓词的子查询：
select sname,sage from student where sage<any (select sage from student where sdept = 'IS');
**************************************************************************
带有 EXISTS 谓词的子查询;
select sname from student where exists (select * from SC where sno = student.sno and cno = '1')    //查询选修了1号课程的学生姓名
select sname from student where not exists (select * from SC where sno = student.sno and cno = '1')    //查询没有选修1号课程的学生姓名

//查询选修了全部课程的学生姓名
select sname from student where not exists (select * from course where not exists (select * from SC where sno = Student.sno and cno = course.cno ));

// 查询至少选修了学生200201选修的全部课程的学生号码
select distinct sno from SC SCX where not exists (select * from SC SCY where SCY.sno ='200201' and not exist (select * from SC SCZ where SCZ.sno = SCX.sno and SCZ.cno = SCY.cno));

**************************************************************************
集合查询：
select sno from SC where cno ='1'
union
select sno from SC where cno ='2'

select sno from SC where cno ='1'
intersect
select sno from SC where cno ='2'

select sno from student where sdept = 'IS'
except
select sno from student where sage <19
**************************************************************************
数据更新：
insert  into  student (sno, sname, ssex, sdept, sage)  value ('200102','陈二','男','IS',10)

create  tabel  Dept_age  (sdept  char(15),  AVG_avg  amsllint);
insert  into  Dept_age (sdept, AVG_age)  select  sdept,AVG(sage)  from   student   group  by   sdept;
**************************************************************************
修改数据：
update student  set   sage = 22   where  sno = '200101';
update student  set   sage = sage  + 1 ;

删除表：
delete table;
**************************************************************************
视图：RDBMS执行对视图的查询时，首先进行有效性检查。检查查询中涉及的表、视图等是否存在。如果存在转换成等价的对基本表的查询，然后再执行修正了的查询。
create view  zhang 
as 
select  sno ,sname  from  student  where  sage< 20  with check option;

create  view  s_c(sno,  gavg)
as
select  sno , avg(grade)  from  SC  group  by  sno;

drop view  zhang;
drop view  s_c    cascase;

视图的作用：
1  能够简化用户的操作
2  使用用户以多种角度看待同一数据
3  能够对机密数据提供安全保护
4  适当的使用视图可以更清晰的表达查询
5  对重构数据库提供了一定程度的逻辑独立性
**************************************************************************
计算机的安全性概述：
三类安全性：技术安全性、管理安全性和政策安全性。
技术安全性：当计算机系统受到无意或恶意的攻击时仍能够保证系统正常的运行，保证系统的数据不增加、不丢失和不泄露。
管理安全性：以管理不善而导致的计算机设备和数据介质的物理破坏、丢失等软硬件意外故障等。
政策安全性：政府有关部门建立的计算机犯罪等。

数据库的安全性控制：
1 用户标识与监别(用户标示、口令)
2 存取控制(自主存取控制：用户对不同的数据库对象有不同的权限、强制存取控制：每一个数据库对象标示一定的级别，需要同或高级别的用户许可证才能够访问)

授权与回收
grant 和 revoke

grant all privileges
on table student,course
to U1,U2;

grant update(sno),select
on table student,course
to U4;

grant update(sno),select
on table student,course
to U4
with grant option;  //运行他再授权

revoke update(sno),select
on table student,course
to U4;

revoke update(sno),select
on table student,course
to U4 cascade;    //级联回收权限
**************************************************************************
审计(audit)
功能是：把用户对数据库的所有操作自动记录下来放入审计日志中。
DBA可以利用审计跟踪的信息，重现导致数据库现有状况的一系列事件，找出非法存取数据的人、时间和内容等。

audit alter,update
on SC;

数据加密：是防止数据库中数据在存储和传输中失密的有效有段。
加密方法两种：替换方法和置换方法。
替换方法：使用密钥将明文中的每个字符转换为密文中的一个字符。
置换方法：仅将明文的字符按不同的顺序重新排列。
**************************************************************************
数据库的完整性：数据的正确性和相容性。
数据的完整性是为防止数据库中存在不符合语义的数据，也就是防止数据库中存在不正确的数据。
分类：实体、参照和用户自定义。

用户自定义：
check
create table student(
sno char(20),
ssex char(2) check (ssex in ('男','女'))
)

create table SC(
sno char(20),
grade smallint check (grade>=0 and grade<=100)
)

create table SC(
sno char(20),
grade smallint constraint BM check (grade>=0 and grade<=100)
)

ALTER TABLE SC
drop constraint BM;

触发器：

create table zhang(
eno numeric(4) reference teacher(sno),
sal int
)

create trigger insert_zhang
  after insert on teacher
  for each row
    as begin
       insert into zhang value(new.eno,new.sal);
       end;

*********************************************************************************************************
数据库设计的基本规律：三分技术 + 七分管理 + 十二分基础数据。

对数据库设计人员的要求：
1 计算机的基础知识
2 软件工程的原理和方法
3 程序设计的方法和技巧
4 数据库的基础知识
5 数据库设计技术
6 应用领域的知识

数据库设计的基本步骤：
1 需求分析
2 概念结构设计(E-R图) 合并E-R图时要解决冲突：属性冲突、命名冲突和结构冲突。
3 逻辑结构设计(3NF)
4 物理结构设计
5 数据库事实
6 数据库运行和维护

需求分析和概念设计独立于DBMS运行，逻辑、物理设计和DBMS密切相关。

数据库设计开始之前，参加的设计人员：系统分析人员、数据库设计人员、应用开发人员、数据库管理员和用户。

具体每一步的工作：
1 需求分析阶段：准确了解与分析用户需求。(需求分析是设计过程的基础，是最困难、最耗时的)

2 概念设计阶段：是整个数据库设计的关键，它通过对用户需求进行综合、归纳与抽象，形成一个独立于具体DBMS的概念模型。通常有四种方法：自顶向下、自底向上、逐步扩张和混合策略。

3 逻辑设计阶段：将概念结构转换为某个DBMS所支持的数据模型，并对其进行优化。

4 物理设计阶段：为逻辑数据模型选取一个最适合应用环境的物理结构(包括存储结构和存取方式)。

5 数据库实施阶段：设计人员运用DBMS提供的数据库语言(如sql)及其宿主语言，根据逻辑设计和物理设计的结果建立数据库，编制与调试应用程序，组织数据入库，并进行试运行。

6 数据库运行和维护：在数据库运行过程中，必须不断地对其进行评价、调整和修改。(完善性维护50%  适应性维护25%  修正性维护21%   预防性维护4% )

设计一个完善的数据库应用系统是不可能一蹴而就的，它往往是上述6个阶段的不断重复。
*********************************************************************************************************
事务：用户定义的一个数据库操作序列，这些操作要么全做要么全不做，是一个不可分割的工作单位。 ( ACID 原子性 一致性 隔离性 持久性 )

数据库恢复
故障的种类：事务内部的故障、系统故障、介质故障和计算机病毒。

恢复的实现技术：数据转储和登录日志文件。

转储状态：动态和静态两种。
转储方式：海量和增量转储。

并发控制：
并发控制所带来的数据不一致性主要包括：丢失数据、不可重复读和读“脏”数据。
丢失数据：两个事务T1和T2读入同一数据并修改，T2提交的结果破坏了T1提交的结果，导致T1的修改被丢失。
不可重复读：是指事务T1读取数据后，事务T2执行更新操作，使T1无法再现前一次读取结果。
读“脏”数据：T1把A数据修改成B数据，后T2读取了B数据，之后T1又回滚了A数据，T2再读时B数据已经不存在。

活锁和死锁
活锁：T1封锁了R数据，T2请求时要等待，T3又来请求R，当T1释放掉R时，T2获得了机会，T3又来请求，T2释放后，T3获得了机会~~~~  T1一直得不到机会的情况即为活锁。

死锁
死锁的预防：一次封锁法、顺序封锁法

死锁的诊断和解除
超时法和等待图法

并发调度的可串行化：多个事务的并发执行是正确的，当且仅当其结果与按某一次序串行地执行这些事务时的结果相同。
可串行性：是并发事务正确调度的准则。

两段锁协议：加锁和解锁
1 对任何数据进行读、写操作之前，首先要申请并获得对该数据的封锁。
2 在释放一个封锁之后，事务不再申请和获得任何其他封锁。

两段锁协议和一次封锁法的不同：
一次封锁法要求每个事务必须一次将所有要使用的数据全部加锁，否则就不能执行。因此，一次封锁法遵守两段锁协议；但是两段锁协议并不要求必须一次将所有要使用的数据全部加锁，因此遵守两段锁协议的事务可能放生死锁。

封锁粒度：封锁粒度越大，数据库所能够封锁的数据单元就越少，并发度就越小；封锁粒度越小，并发度就越高，系统开销也就越大。

                                                                                 ——zhangguang——dalian——2012——10—05——