数据库的基本原理回顾

DDL：数据定义

DML：数据操纵

DCL：数据控制语言集成在一起（权限控制，完整性控制）

关系数据库的标准语言--SQL（Structured  Query  Language），ANSI解释为Standard  Query  Language。

SQL是一种高度的非过程化语言（仅有What，不问How）。

交互式和嵌入式两种使用方式，统一的语法结构

语言简洁，易学易用

完成核心功能只有9个动词：

数据查询：   SELECT

数据定义：   CREATE，DROP，ALTER

数据操纵：   INSERT，DELETE，UPDATE

数据控制：   GRANT，REVOKE

SQL数据定义功能

定义和修改基本表（定义模式）：

CREATE  TABLE

DROP  TABLE

ALTER  TABLE

定义视图（定义外模式）：

CREATE  VIEW

DROP  VIEW

定义索引（定义内模式）：

CREATE  INDEX

DROP  INDEX

说明：视图是从基本表导出的虚表，索引依赖于基本表，SQL没有修改视图和索引的操作，可通过先删除，再创建达此目的。

基本表的定义和修改

1，定义：基本格式为

CREATE  TABLE  表名（列名1   类型  [列级完整性约束]

[，列名2   类型  [列级完整性约束]…）；

CREATE  TABLE  S（ S#   CHAR（3） NOT NULL UNIQUE，

SN  CHAR（15），

SD  CHAR（15），）

其中，NOT NULL---不允许取空值，UNIQUE---取值唯一

SQL支持空值的概念。允许空值的列未输入数据时系统自动置为空值。

SQL支持的数据类型随系统不同而有所差异。

2，修改基本表

增加列：

ALTER  TABLE  表名  ADD  列名   类型  [完整性约束]； （不支持NOT  NULL选择）

例如：ALTER TABLE  S  ADD  SD  INT；

修改列

ALTER  TABLE  表名  MODIFY  列名   类型；

例如：ALTER TABLE  S  MODIFY  SD  CHAR（20）；

删除完整性约束

ALTER  TABLE  表名  DROP  完整性约束名；

注意：不能删除列，新增列的值一律为空值，

可增加列宽，但一般不能减小列宽，修改可能会破坏已有数据。

在定义基本表时要考虑充分。

3，删除

DROP  TABLE  表名；

注意：删除基本表时，表中的数据、建立在表上的索引和视图将一并被删除，因此应格外小心。

4，索引的建立和删除

由DBA或表的属主进行，存取数据时由系统自动选取合适的索引作为存取路径，用户不必也不能选择索引。

5，创建索引

CREATE  [ UNIQUE ]  [CLUSTER]  INDEX  索引名

ON  表名（列名 ASC/DESC，列名 ASC/DESC）…；

其中ASC为升序，DESC为降序。

如    CREATE  UNIQUE  INDEX  XSNO  ON  S（S#）；

CREATE  UNIQUE INDEX SCNO

ON   SC（SNO ASC，CNO DESC）；

6，删除索引

DROP  INDEX  索引名；

SQL数据操纵----查询

查询是数据库的核心操作。SQL仅提供了唯一的语句SELECT，其使用方式灵活，功能非常丰富。

SELECT [ALL | DISTINCT]   * | 目标列

FROM     基本表（或视图）

[ WHERE   条件表达式 ]

[ GROUP  BY  列名1   [ HAVING  内部函数表达式 ] ]

[ ORDER  BY  列名2  ASC/DESC ]；

其中，上面的语句中，”＊“代表全部字段，“❘”相当于投影

From 后面的语句，表示被查询的表。

Where 表示选择/连接。

Having 表示：满足条件的组才输出。

ASC/DESC：表示对查询结果的排序。

1，简单查询

例如1：求选修了课程的学生学号

SELECT  DISTINCT  S#

FROM SC；

//从结果中去掉重复的元组

例2：SELECT的后面可以是表达式。

如求计算机系学生的学号和出生年份：

SELECT   S#，'Birthday：’，2000-SA

FROM  S

WHERE  SD='CS’；

例3：连续范围查询，使用BETWEEN （NOT  BETWEEN）

SELECT   S#，SA

FROM  S

WHERE  SA  BETWEEN  20 AND 22；

//即求20到22岁之间的学生学号和年龄

例4：离散范围查询，使用 IN   （NOT  IN）

SELECT   *     //星号表示无投影

FROM  S

WHERE  SD  IN  （'MA’，'CS’）；  //相当于若干'OR’的缩写

// SD='MA’ OR SD='CS’

例5：模糊查询，使用 LIKE   （NOT  LIKE）

SELECT  *

FROM  S

WHERE  SN  LIKE  '%清%’； //查姓名中有'清’字的学生

DB2中，下划线 '_’表示匹配任何单个字符

百分号'%’表示匹配任何字符串

其它语言中, 常用 '?’

常用 '*’

例6:  涉及空值的查询 ,  IS  NULL  （IS   NOT  NULL）

SELECT   S#, C#   FROM  SC

WHERE  G  IS   NULL ;

2，连接查询：涉及至少两个表的查询

SQL中没有专门的JOIN命令，而是靠SELECT语句中的

WHERE子句来达到连接运算的目的，因此更加灵活、简便。

用来连接两个表的条件称为连接条件或连接谓词。

注意：在多个表中出现的列名，必须用表名限定，仅在一个表中出现的属性，可省略表名。

SELECT  S.S#,  SN, G     //此处，必须要用表名限定，因为此列名在多个表中出现了。

FROM  S,  SC

WHERE  S.S#=SC.S#

AND SC.C#='C1’;

3，使用外连接查询时

求每个学生选修的课程及成绩。

SELECT  S.S#,   SN,  C#,  G

FROM  S,  SC

WHERE  S.S# = SC.S#（＊） ;  //有些数据库系统用+  ， 有些将*放在=前后，*=、=* 。

4，嵌套查询（子查询）--SELECT-FROM-WHERE 查询块嵌入另一个查询块中

（1）嵌套查询由内向外处理

（2）SQL允许多层嵌套

（3）嵌套查询中最常用的

谓词是IN

（4）嵌套查询层次分明、

容易理解

5，UNION查询

（1）用UNION查询时，每个子查询选择的目标列必须相同，

但所基于的表可以不同

（2）UNION查询实际上是将几个子查询的结果合并在一起

（3）UNION查询时自动去掉重复的行

（4）可以通过将各子查询的限制条件合并而将UNION查询

变成一个单一查询

库函数（聚集函数）

COUNT            统计一列中的（NOT NULL）值的个数

COUNT（*）  计算记录个数

SUM                 对一列求和

AVG                 对一列求平均值

MAX                对一列求最大值

MIN                 对一列求最小值

将表按列的值分组，列的值相同的分在一组，产生一个结果行。GROUP  BY常

和库函数一起使用，用于分组统计。

WHERE是选择记录的条件；

HAVING是选择分组的条件且必须和GROUP  BY一起使用。

库函数只能作用于HAVING和目标列，而不能用于WHERE。

SQL数据操纵----数据更新

1，插入数据

插入单个元组

INSERT

INTO  表名  [（字段名 [，字段名 ] … ]

VALUES （常量 [，常量] … ] ；

例1：插入一条选课记录（S1，C5）。

INSERT

INTO SC（S#，C#）

VALUES（'S1’，'C1’）；

说明：

★  当在INTO后面仅指定部分属性列时，插入记录后其它列的值为空值；

★  如果INTO后面没有指定属性列，则必须按表列的定义次序为每个列指定一个值；

★  具有NOT NULL属性的列，必须指定值。

2，修改数据

UPDATE 表名

SET 列名 = 表达式[，列名 = 表达式]…

[WHERE  条件]；

说明：

★  当省略WHERE子句时，修改表中所有记录，否则仅修改满足条件的记录；

★  条件也可以使用子查询。

3，删除数据：

DELETE

FROM  表名

[ WHERE  条件 ]；

只能删除表记录，不删除表结构。无条件时，删除全部记录，仅剩一个空表；有条件时删除满足条件的记录。

为物理删除命令。

删除表结构用DROP TABLE。

4，更新操作与数据库的一致性

增、删、改操作只能对一个表操作，可能会造成数据的不一致性。

例如：

删除“物理”课程的全部信息，需使用两个操作实现，删除C表的课程记录和删除SC表的选课记录。若在完成第一个操作后发生意外，致使第二个操作未能实现，则造成数据库的不一致。因为SC中的物理课程号还存在，而被参照的C表中已没有该课程号的课程了。所以应保证这两个操作要么全做，要么全不做，为此，在数据库系统中引入了事物的概念。

为保证数据的一致性，大型数据库系统一般都提供若干策略：

删除主表（被参照表）中的数据时

（1）自动删除参照表中的相应数据；

（2）检查参照表中是否有数据参照，若有则拒绝删除。

向参照表中插入数据时

检查所有被参照表中是否有被参照的信息，若没有则拒绝插入。

修改主表中的被参照字段

检查参照表中是否有数据参照，若有则拒绝修改。

SQL数据控制功能

数据控制功能包括事物管理功能和数据保护功能。即 数据的安全性、完整性、事务控制、并发控制和恢复功能。

一、授权

1、机制：大的DBMS中有一个超级用户DBA，其他用户能否存在、对某类数据具有何种操作权力是由DBA决定的，系统提供授权机制。执行过程为：

（1）用数据控制语言把授权决定告知系统；

（2）系统把授权的结果存入数据字典；

（3）当用户提出 操作请求时，系统根据授权情况进行检查，

以决定是否执行。

2、权力的授予与收回

授予：

GRANT  权力 [，权力 ] … [ ON 对象类型  对象名 ]     //若干权力和操作对象

TO  用户名 [，用户名] …                                   //获得权力的对象

[ WITH  GRANT  OPTION ]；                         //有此项，被授权用户可再授权给其他用户

收回：

REVOKE   权力 [，权力 ] … [ ON 对象类型  对象名 ]

FROM  用户名 [，用户名] …；

数据库的属主、表的属主、数据库对象的属主在他创建的对象上具有一切可能的权力，其他用户得不到主人的授权就不能在该对象上操作；

·  SQL的授权机制十分灵活，各种系统又作了适当的补充，

使用十分方便；

¸  SQL的安全性控制除了上述授权机制外，还可设置口令进一步保密。

嵌入式SQL

一、SQL的使用方式

1、交互式：在终端上每输入一条SQL语句，系统立即执行，然后等待用户输入下一条语句。

2、自编程式：在实际的DMBS中，都对SQL进行了扩充，增加了条件、循环等控制语句，并提供编程机制。如SYBASE中，用户可以编写存储过程并调用它。

3、嵌入式（嵌入到某种主语言中使用）：

宿主语言负责：运算、处理、流程控制等

SQL负责：数据库操作

二、嵌入式SQL使用时的问题

必须解决和主语言相互配合、连接等问题

1、标识SQL语句

用前缀，如EXEC SQL或$等，标记SQL语句的开始；

用END-EXEC或分号'；’等标记SQL语句的结束。

SQL语句标识是通知主语言的预编译程序将SQL语句转化为等价的主语言语句，然后再由编译程序形成目标代码

2、SQL语句与主语言之间的通信

（1）通过SQL通信区（SQLCA）将SQL语句的执行状态传递给主语言。 SQLCA是一个数据结构，其中有一个重要变量SQLCODE，存放SQL语句是否执行成功的信息，每执行一个SQL语句，主语言都应测试该变量。

（2）SQL语句中可使用主语言的程序变量（叫主变量），但要加前缀标志，一般用冒号'：’。

（3）SQL语句和主语言的数据交换一般通过字段变量和主变量进行，但两者数据类型要匹配.

（4）一个SQL语句可以产生一组记录，而主语言一次只能处理一个记录，为此需协调两种方式：

办法：用游标（Cursor），有的叫位置指针

（5）通常用CONNECT语句来连接（申请使用）数据库

关系系统及其查询优化

一、关系系统的定义

1、关系模型：

数据结构：    关系（二维表）

数据操纵：    关系代数（或关系演算）

完整性约束：实体完整性、参照完整性、用户定义的完整性

2、关系系统的定义

*   关系系统是关系数据库系统的简称

*   从概念上讲，支持关系模型的系统称为关系系统。

*   按最小要求定义关系系统：

一个系统称为关系系统，当且仅当

（1）支持关系数据结构；

（2）支持选择、投影和连接运算。 对运算不要求定义任何物理存取路径。

关系上完备的系统：

支持关系结构，

支持所有的关系代数操作

如：SYBASE、ORACLE、DB2

全关系系统：支持关系模型的所有特征

如：SYBASE、ORACLE、DB2等系统已接近这个目标

三、全关系系统的十二条基本准则

基础（准则 0）：关系型DBMS必须能完全通过它的关系能力来管理数据库

//在关系一级上支持数据的插入、删除、修改，没有任何操作必须通过非关系的能力才能实现。

准则1：信息准则。逻辑上可用一种方法（表中的值）来表示所有信息。

//用户数据、元数据、索引、应用元数据统一用表格来表示

好处：u  提高用户生产率

u  便于DBA维护数据库

u  便于与其它软件接口

关系系统的查询优化

关系数据语言只需用户指出“干什么”，不必指出“怎么干”，为什么能做到这一点？

一个重要原因就是系统能自动进行查询优化。

查询优化的总目标：

选择有效的策略，求得给定的关系表达式的值

事物管理--数据库恢复技术

作为一个完善的DBMS，应该提供统一的数据保护功

能来保证数据的安全可靠和正确有效！

数据保护也叫数据控制，主要包括：

数据库恢复，并发控制，数据的安全性，数据的完整性。

1，事务（transaction）

一个数据库操作序列(主要是更新操作)，是数据库应用程序的基本逻辑单元。 这些操作要么都做，要么都不做，是一个不可分割的执行单位。

事务标记:

BEGIN   TRANSACTION （事务开始）

COMMIT （事务提交：事务完成了其包含的所有活动，正常结束）  或

ROLLBACK（事务回滚（中止）：撤消已做的所有操作，回到事务开始时的状态）

2，事务应具有的性质

1）原子性（Atomicity）：事务执行时的不可分割性，即事务所包含的活动要么都做，要么都不做。

若事务因故障而中止，则要设法消除该事务所产生的影响，使数据库恢复到该事务执行前的状态。

（2）一致性（Consistency）：事务对数据库的作用应使数据库从一个一致状态到另一个一致状态。

（3）隔离性（Isolation）：

多事务并发执行，应向各事务独立执行一样，不能相互干扰。一个正在执行的事务其中间结果不能为其它事务所访问。

4）持久性（Durability）：   一旦事务提交，不论执行何种操作或发生何种故障，都不应对该事务的执行结果有任何影响。（能恢复）

（5）可串行性（Serializability）：并发控制正确性的标准用户程序在逻辑上是正确的，它在串行执行时没有问题；

当多个事务并发执行时，可以等价于一个串行执行序列。

3、事务管理任务

事务管理的任务就是要保证事务满足上述性质。使事务不具有上述性质的因素可能是：

（1）事务在运行过程中被强行终止；

（2）多个事务并行运行时，不同事务的操作交叉执行

因此事物管理又分为两个方面：

恢复：保证事务在故障时满足上述性质的技术。

并发控制：保证事务在并发执行时满足上述性质的技术。