数据库

概述

数据(Data)：

数据库中储存的基本对象

数据库(DataBase,简称DB):

数据库是长期储存在计算机内，有组织，可共享的大量数据的集合

数据库数据具有永久储存，有组织、可共享三个基本特点

数据库管理系统(DataBase Management System,简称DBMS)

主要功能包括：

数据定义功能：
DBMS提供定义语言DDL，用户通过它可以方便的对数据库中数据对象进行定义
数据组织、存储和管理
数据操作功能
数据库的事务管理和运行管理
数据库的建立和维护功能

数据库系统(DataBase System，简称DBS)

数据库系统一般由数据库、数据库管理系统（及开发工具）、应用系统、数据库管理员构成

数据模型(Data Model,DM)

对现实世界数据特征的抽象。数据模型就是现实世界的模拟

满足三方面的要求：

  1. 能比较真实客观的模拟现实世界
  2. 容易为人所理解
  3. 便于在计算机上实现

根据模型应用的不同，可以分为两类：概念模型、逻辑模型和物理模型

概念模型：也称信息模型，主要用于数据库设计

逻辑模型主要包括：层次模型、网状模型、关系模型、面向对象模型、对象关系模型

物理模型：是对数据最底层的抽象。面向计算机系统的

数据模型的组成要素

数据模型通常由数据结构、数据操作和完整性约束三部分组成

在关系模型中，任何关系必须满足实体完整性和参照完整性

概念模型

概念模型是现实世界到机器世界的一个中间层次，用于信息世界的建模，现实世界到信息世界的第一层抽象，数据库设计人员进行数据库设计的有力工具，数据库设计人员和用户之间进行交流的语言。

概念模型要求：

具有较强的语义表达能力

能够方便、直接地表达一样中的各种语义知识

简单、清晰、易于用户理解

信息世界的基本概念

实体：客观存在并可相互区别的事物称为实体

属性：实体所具有的某一特性称为属性

码（key）：唯一标识实体的属性称为码

域：是一组具有相同数据类型的值的集合。属性的取值范围来自某个域

实体型：具有相同属性的实体

实体集：同一类型实体的集合称为实体集

联系：在现实世界中，事物内部以及事物之间的联系，这些联系在信息信息世界中反映为实体内部的联系和实体之间的联系

实体内部的联系通常是指组成实体的各属性之间的联系；实体之间的联系通常是指不同实体集之间的联系

两个实体集之间的联系
- 一对一联系(1:1)
  对于实体集A中的每一个实体，实体B中至多有一个实体与之联系，反之亦然，则称A与B为一对一的关系记为1：1
  例：班级与班长
- 一对多（1:n）
  对于实体集A中的每一个实体，实体B中有n个实体与之联系（n>=0）,反之对于B中的实体每一个实体，A中至多只有一个实体与之联系，A与B的联系记为1：n
  
  例：班级与学生，班级对学生是1：n，学生对班级是1:1
- 多对多（m：n）
  对于实体集A中的每一个实体，实体B中有n个实体与之联系（n>=0）,反之对于B中的实体每一个实体，A中有m个实体与之联系（m>=0），A与B的联系记为m：n(A中的实体对B中的实体是1对多，B中的实体对A中的实体是1对多)
  
  例：课程与学生
两个以上的实体集之间的联系
也存在1对1，1对多，多对多联系
当个实体集之间的联系

也存在1对1，1对多，多对多联系

例：职工：有领导和被领导，1：n
概念模型的一种表示方法：实体-联系图（E-R图）

E-R图提供了表示实体型、属性和联系的方法

实体型：用矩阵表示

属性：用椭圆表示

联系：用菱形表示（如果一个联系具有属性，则这些属性也要用用无向边联系起来）

数据模型

关系模型

关系模型的数据结构
- 关系
  一个关系对应通常说的一张表
- 元组
  
  表中的一行即为一个元组
- 属性
  
  表中的一列即为一个属性
- 码
  
  也称称码键。表中的某个属性组，它可以唯一确定一个元组
- 域
  
  属性的取值范围
- 分量
  
  元组中的一个属性值
- 关系模式
  对关系的描述，一般表示为：关系名（属性1，属性2…….）
关系数据模型的操纵与完整性约束
关系数据模型的操作主要包括，查询、插入、删除和更新数据，这些操作必须满足关系的完整性约束条件
- 关系的完整性约束条件包括三大类：
  实体完整性
  参照完整性
  用户定义完整性
关系模型的优点
关系模型是建立在严格的数学概念的基础上的
关系模型概念单一
关系模型的存储路径对用户透明，从而有更高的数据独立性、更好的安全保密性也简化程序员的工作和数据库开发

数据库系统结构

数据库系统模式概念
模式是数据库中全体数据的逻辑结构和特征的描述，同一个模式可以有很多实例
数据库系统的三级模式结构
数据库系统的三级模式结构是指数据库系统是由外模式（用户模式）、模式（逻辑模式）和内模式（存储模式）三级构成

外模式是模式的一个子集，一个数据库可以有多个外模式
一个数据库只能有一个内模式

同一外模式也可以为某一用户的多个应用系统所用，但一个应用程序只能使用一个外模式
外模式是保证数据库安全性的有力措施，每个用户只能看见和访问所对应的外模式中的数据，数据库中的其余数据是不可见的

数据库系统的组成

数据库系统一般由数据库、数据库管理系统（及开发工具）、应用系统、数据库管理员构成

数据库管理员(DBA)
系统分析员和数据库设计人员
应用程序人员
用户

关系数据库

关系数据库结构及形式化定义

域
一组具有数据相同数据类型的值集合
笛卡尔积
所有域的所有取值的一个组合，笛卡尔积的子集不是都有实际含义
元组
笛卡尔积中每个元素称作一个n元组
例：(张三，计算机专业，李勇)
分量
笛卡尔积元素中的每一个di值都是分量
例：张三，计算机专业，李勇，都是分量
基数
是每个域的取值个数
笛卡尔积是由每个域的基数相乘
笛卡尔积的表示方法
可表示为一个二维表，表中每行对应一个元组，表中每列对应一个域
关系
D1*D2…….Dn的子集在域D1，D2……Dn上的关系表示为R(D1,D2,……,Dn),R表示关系名，n表示关系的度或目
关系中的每个元素是关系中的元组，通常用t表示
n=1时，称该关系为单元关系
n=2时，称该关系为二元关系
关系的表示：
关系也是一个二维表，表的每行对应一个元组，表的每列对应一个域，一个属性
关系中不同列可以对应相同的域，n目关系必有n个属性
码（可能是多个属性）
- 候选码：关系中的某一属性组的值能唯一的表示一个元组
- 全码：关系模式的所有属性是这个关系模式的候选码
- 主码（Primary key）：若一个关系有多个候选码，则其中一个为主码
- 主属性（Primary attribute）：候选码的属性称为主属性
- 非主属性：不包含在任何候选码的属性
三类表
- 基本关系（基本表）实际存在的表，是实际存储数据的逻辑表示）
- 查询表：查询结果对应的表
- 视图表：由基本表或其它视图表导出的表，是虚表，不对应实际存储的数据（只能查看不能增删）
基本关系的性质

关系模式

关系模式是对关系的描述。关系模式是型，关系是值

元组集合的结构
属性构成、属性来自的域、属性与域之间的映像关系

一个关系通常由赋予它的元组语义确定

现实世界中还存在着完整性的约束

定义关系模式
关系模式可以形式化的表示为：R(U,D,DOM,F)
R：关系名
U：组成该关系的属性名集合
D：属性组U中属性所来自的域
DOM：属性向域的映像集合
F：属性间的数据依赖关系的集合
关系模式与关系
关系模式是静态的、稳定的
关系是动态的、随时间变化的
关系是关系模式在某一时刻的状态或内容

关系数据库

在一个给定的应用领域中，所有关系的集合构成一个关系的数据库

关系数据库的型与值
- 关系数据库的型：关系数据库模式，对关系数据库的描述
- 关系数据库模式：若干域的定义，在这些域上的定义的若干关系模式
- 关系数据库的值：关系模式在某一时刻对应的关系的集合，简称关系数据库

关系模型的存储结构

有的关系数据库管理系统中一个表对应一个操作系统文件，将物理数据组织交给操作系统
有的关系数据库管理系统从操作系统那里申请若干个大的文件，自己划分文件空间，组织表索引等存储结构，并进行存储管理

关系操作

基本关系操作

常用的关系操作
查询：选择、投影、连接、除、并、交、差等，其中选择、投影、并、差，笛卡尔积是5种基本操作
数据更新：插入，删除，修改
查询的表达能力是其中最主要部分
关系操作的特点
操作的对象和结果都是集合，一词一集合的方式

关系数据库的语言分类

关系代数语言
用对关系的运算来表达查询的要求。例:ISBL
关系演算语言
元组关系语言：APLHA，QUEL
域关系语言：QBE
具有关系代数和关系演算双重特点的语言
SQL

关系的完整性

关系模型的中有三类的完整性约束：实体完整性、参照完整性和用户自定义的完整性

其中实体完整性、参照完整性是关系模型必须满足的完整性约束条件，称为两个不变性

实体完整性
实体完整性规则是指若属性A是基本关系的主属性，则属性A不能取空值

实体完整性规则规定基本关系的所有主属性都不能取空值
参照完整性
- 关系间的引用
  在关系模型中实体之间的联系都是用关系来描述的，存在着关系与关系间的引用
外码
设F是基本关系R的一个属性组或属性，但不是关系R的码。如果F与基本关系S的主码K相对应，则称F是基本关系R的外码
基本关系R称为参照关系
基本关系S称为被参照关系或目标关系
- 参照完整性的规则
  若属性组或属性F是基本关系R的外码，它与基本关系S的主码K相对应，则对于R中每个元组在F上的值必须为等于S中某个元组的主码值或空值
用户定义完整性

关系代数

关系代数是一种抽象的查询语言，是对关系的运算来表达查询。

关系代数的运算对象是关系，运算结果也是关系

关系代数按运算符的不同可分为传统的集合运算和专门的关系运算两类

集合运算是从关系的水平方向即行的角度进行

专门的关系运算不仅涉及行而且涉及例

传统的集合运算

并（Union）
关系R和S具有相同的目n（即两个关系都有n个属性），相应的属性取自同一个域
R于S的并运算表示为$ R\cup S:R\cup S=t|t\in R,or,t\in S$
运算结果为：n目关系，由属于R或属于S的元组组成
差（except）
关系R和S具有相同的目n（即两个关系都有n个属性），相应的属性取自同一个域
R于S的差运算表示为$R-S:R-S=t|t\in R\bigwedge t\notin S$
运算结果为：n目关系，由属于R而不属于S的元组组成
交（intersection）
关系R和S具有相同的目n（即两个关系都有n个属性），相应的属性取自同一个域
R于S的交运算表示为$R\bigcup S:R\bigcup S=t|t\in R\bigwedge t\in S$
运算结果为：n目关系，即属于R又属于S的元组组成
笛卡尔积（Cartesian Product)
严格意义上讲是广义的笛卡尔积
R：n目关系，k1个元组，S：m目关系，k2个元组
R与S的笛卡尔积运算表示为$R \times S$：

运算结果为：行：k1*k2个元组，列：（n+m）列元组的集合，其中元组的前n列是关系R的一个元组，后m例是关系S的一个元组
专门的关系运算
选择
选择又称限制，在关系中选择你满足给定条件的元组，记作：
$\sigma _F(R)= \{t\in R\wedge F(t)='真'\}$
其中：F为选择条件，基本形式为：
$X_1 \theta Y_1$
其中，$\theta$表示比较运算符，它可以是$>,\geq,<,\leq,=,<>。X_1,Y_1$等是属性名，或为常量，或为简单函数；属性名可以用它的序号来代替
投影
从R中选择出若干属性组成新的关系
连接

运算结果：从R和S的广义笛卡尔积R*S中选取（R关系）在A属性组上的值与（S关系）在B属性组上值满足比较关系$ \theta $元组
- 等值连接
  $ \theta $为=的连接运算称为等值连接
  等值连接的含义：从关系R与S的广义笛卡尔积中选取A、B属性值相等的那些元组
- 自然连接
  自然连接是一种特殊的等值连接，两个关系中进行比较的分量必须是相同属性组，在结果中把重复的属性列去掉
  自然连接的含义：R与S具有相同的属性组B
- 悬浮元组
  两个关系R和S在自然连接时，关系R和S中被舍弃的元组称为悬浮元组
- 外连接
  如果把悬浮元组舍弃的元组也保存在结果关系中，而在其他属性上填空值，这种连接叫做外连接
- 左外连接
  如果只保留左边关系R中的悬浮元组叫做左外连接
- 右外连接
  如果只保留右边关系S中的悬浮元组叫做右外连接
除
给定关系R(X，Y)，和S(Y,Z)，其中X，Y，Z为属性组。R中的Y与S的Y可以有不同属性名，但必须是出自相同的域集

关系演算

关系演算可分为元组关系演算和域关系演算

元组关系演算语言ALPHA
主要有GET，PUT,HOLD,UPDATE,DELETE,DROP，6条
语句基本格式：
操作语句工作空间名 (表达式):操作条件
表达式用于说明要查询的结果，它可以是关系名或属性名
- 检索操作
  检索操作用GET语句实现
  - 简单检索（即不带条件的检索）
    例：
    查询所有被选项的课程号码
    GET W(SC.Cno)
    查询所有学生的数据
    GET W(Student)
  - 限定的检索（带条件的检索）
    例：
    查询信息系中年龄小于20岁的学生的学号和年龄
    GET W(Student.Sno,Student.Sage):Student.Sdept=’IS’$\wedge$Student.Sage<20
  - 带排序的检索
    例：查询计算科学系（CS）学生的学号、年龄、结果按年龄降序排序
    GET W(Student.Sno,Student.Sage):Student.Sdept=‘SC’ DOWN Student.Sage
    DOWN表示降序排序，UP表示升序
  - 指定返回元组的个数
    例：取出一个信息系学生的学号
    GET W(1) (Student.Sno]):Student.Sdept=‘IS’
    查询信息系年龄最大的三个学生的学号及其年龄，结果按年龄降序排序
    GET W(3) (Student.Sno,Student.Sage):Student.Sdept=‘IS’ DOWN Student.Sage
  - 用元组变量的检索
    给元组起一个额外的名字，简化关系名，操作条件中使用量词时必须用元组变量
    ALPHA语言用RANGE来说明元组变量
    例：查询信息系的学生的名字
    RANGE Student X
    GET W(X.Sname):X.Sdept=‘IS’
  - 用存在量词的检索条件中使用量词必须用元组变量
    例：查询选修2号课程的学生名字
  - 带有多个关系的表达式的检索
    例：查询成绩90分以上的学生名字与课程
  - 全称量词的检索
    例：查询不选1号课程的学生名字
  - 用两种量词的检索
    例：查询选修了全部课程的学生名字
  - 用蕴含的检索
    蕴含就是充分条件
  - 聚集函数
    COUNT,TOTAL,MAX,MIN,AVG
    查询学生所在系的数目
    GET W(COUNT(Student.Sdept))
    COUNT函数在计数时会自动排除重复值
    查询信息系学生的平均年龄
    GET W(AVG(Student.Sage)):Student.Sdept=‘IS’
- 更新操作
  - 修改操作
    用UPTATE
    用HOLD语句将修改的元组从数据库读到操作空间（HOLD语句加锁）
    然后用宿主语言修改工作空间的元组的属性值
    最后用UPDATE语句修改后的元组送回数据库中
    例：把201215127学生从计算机科学系转到信息系
    HOLD W(Student.Sno,Student.Sdept):Student.Sno=‘201215127’
    (从Student关系中读取出201215127学生的数据)
    MOVE ‘IS’ TO W.Sdept
    UPDATE W (把修改后的元组送回Student)
    主码不能直接修改，只能先删除后修改
  - 插入操作
    用PUT
    首先用宿主语句在工作空间中建立新的元组
    然后用PUT语句把该元组存入指定关系中
    例：
    MOVE ‘8’ TO W.Cno
    MOVE ‘计算机组织与结构’ TO W.Cname
    MOVE ‘6’ TO W. Cpno
    MOVE ‘2’ TO W.Ceredit
    PUT W(Course)
  - 删除
    用DELETE
    用HOLD语句将修改的元组从数据库读到操作空间
    用DELETE语句删除该元组
    例：
    HOLD W(Student):Student.Sno=‘201215230’
    DELETE W
    删除全部学生
    HOLD W(Student)
    DELETE W
元组关系演算
- 三类原子公式
  R(t) ，R是关系名，t是元组变量
- 公式可以递归定义以下
  每个原子公式是公式
- 元组演算公式中,各种运算符的优先次序为
域关系演算语言QBE
域关系演算以元组变量的分量即域变量作为谓词变元的基本对象，是基于屏幕表格的查询语言，用户通过终端屏幕编辑程序以填写表格的方式构造插叙要求

关系数据库标准语言SQL

SQL概述

SQL:结构化查询语言，是关系数据库的标准语言

SQL的特点
- 集数据定义语言（DDL），数据操作语言（DML），数据控制语言（DCL）功能一体
- 可以独立完成数据库生命周期的全部活动
  
  定义关系模式，插入数据，建立数据库；对数据库中的数据进行查询和更新，数据库重构和维护；数据库安全性完整性控制等
- 用户数据库投入运行后，可根据需要随时逐步修改模式，不影响数据的运行
- 操作符统一
高度非过程化
- 非关系数据模型的数据操纵语言“面向过程”，必须制定存取路径
- SQL只要提出“做什么”，无需了解存储路径
- 存储路径的选择以及SQL的操作过程由系统自动完成
面向集合的操作方式
- 非关系数据模型采用面向记录的操作方式，操作对象是一条记录
- SQL采用集合操作方式
  操作对象、查走结果可以是元组的集合
  一次插入、删除、关系操作的对象可以是元组的集合
以同一种语法结构提供多种使用方式
- SQL是独立语言
  能够独立地用于联机交互地使用
- SQL又是嵌入式语言
  SQL能够嵌入到高级语言（C，C++，java）程序中
语言简洁，易学易用
SQL功能极强，完成核心功能只用了9个单词

SQL功能动词

数据查询 SELECT

数据定义 CREATE,DROP,ALTER

数据操作 INSERT,UPDATE,DELETE

数据控制 GRANT,REVOKE
SQL的基本概念
SQL支持关系数据库三级模式结构

SQL功能	动词
数据查询	SELECT
数据定义	CREATE,DROP,ALTER
数据操作	INSERT,UPDATE,DELETE
数据控制	GRANT,REVOKE

学生-课程数据库

学生-课程模式S-T：

学生表：Student(Sno,Sname,Ssex,Sage,Sdept)

课程表：Course(Cno,Cname,Cpno,Ccredit)

学生选课表：SC(Sno,Cno,Grade)

数据定义

SQL数据定义功能：模式定义，表定义，视图和索引的定义

操作对象
	创建	删除	修改
模式	CREATE SCHEMA	DROP SCHEMA
表	CREATE TABALE	DROP TABLE	ALTER TABLE
视图	CREATE VIEW	DROP VIEW
索引	CREATE INDEX	DROP INDEX	ALTER INDEX

模式的定义与删除

定义模式

格式：
CREATE SCHEMA <模式名> AUTHORIZATION <用户名>
例：为用户WANG定义一个学生-课程模式S-T
CREATE SCHEMA “S-T” AUTHORIZATION WANG;
为用户WANG定义了一个模式S-T
- 若没有指定模式名，那么模式名默认为用户名
- 在CREATE SCHEMA中可以接受CREATE TABLE，CREATE VIEM和GRANT子句
  格式如下
  CREATE SCHEMA <模式名> AUTHORTZATION <用户名> [<表定义语句>|<视图定义语句>|<授权定义语句>];
  例：为用户ZHANG创建一个模式TEST，并且其中定义一个表TBA1
  CREATE SCHEMA TAB1 AUTHORIZATION ZHANG
  CREATE TABLE(COL1 SMALLINT,
  COL2 INT,
  COL3 char(20),
  COL4 NUMERIC(10,3),
  COL5 DECIMAL(5.2)
  );
执行创建模式语句必须拥有DBA权限，或者DBA授予CREATE SCHEMA权限

删除模式
语句格式：DROP SCHEMA <模式名> <CASCADE|RESTRICT>;
CASCADE和RESTRICT必须二选一
CASCADE(级联)：删除模式的同时把该模式下中所有的数据库对象全部删除
RESTRICT(限制)：如果该模式中定义了下属的数据库对象（如表，视图），则拒绝该删除语句的执行，只有该模式中没有任何下属的对象时才能执行
例：
DROP SCHEMA S-T CASCADE;
基本表的定义、删除与修改
定义
语句格式：
CREATE TABLE <表名>
（<列名> <数据类型> [<列级完整性约束条件>]，
<列名> <数据类型> [<列级完整性约束条件>]，
<表级约束条件>);
例：
建立”学生“表Student”，学号是主码，姓名取唯一值
CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY,
Sname CHAR(20) UNIQUE, //姓名取值唯一
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20)

);
建立一个“课程”表Course
CREATE TABLE Course
(Cno CHAR(4) PRIMARY KEY,
Cname CHAR(40) NOT NULL, //Cname不能取空值
Cpno CHAR(4),
Ccredit SMALLINT,

FOREIGN KEY(Cpno) REFERENCES Course(Cno) //表级约束

)
建立一个“学生选课”表SC
CREATE TABLE SC
(Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY(Sno,Cno), //主码由两个属性构成，必须作为表级完整性进行定义
FOREIGN KEY(Sno) REFERENCES Student(Sno), //表级完整性约束条件，Sno是外码，被参照表是Student
FOREIGN KEY(Cno) REFERENCES Course(Cno) //表级完整性约束条件，Cno是外码，被参照表是Course

);
模式与表
每一个基本表属于一个模式，一个模式包含多个基本表
创建基本表时，若没有指定模式，系统会根据搜索路径来确定该对象所属的模式
显示当前的搜索路径：
SHOW search_path;
显示的结果：
$user,PUBLIC;
DBA用户可以设置搜索路径
SET search_path TO “S-T”,PUBLIC;
若搜索路径中的模式名不存在，系统将给出错误
若搜索路径中存在模式，RDMBS会使用模式列表中第一个存在的模式作为数据库对象的模式模式名
创建基本表
- 创建基本表时给出模式名
  CREATE TABLE “S-T”.Student(…….);
- 在创建模式中同时创建表
- 设置所属模式，在创建表名中不必给出模式名
  SET search_path TO “S-T”,PUBLIC;
  CREATE TABLE Student(…….);
修改基本表
语句格式：
ALTER TABLE <表名>

[ADD [COLUMN] <列名> <数据类型> <完整性约束>]
[ADD <表级完整性约束>]

[DROP [COLUMN] <列名> [CASCADE|RESTRICT]]
[DROP CONSTRAINT <完整性约束名> [CASCADE|RESTRICT]]

[ALTER COLUMN <列名> <数据类型>]

例：
向Student表增加“入学时间”列，其数据类型为日期性
ALTER TABLE Student ADD S_entrance DATE;
不论基本表中原来是否已有数据，新增加的列一律为空值
将年龄的数据类型由字符型改为整型：
ALTER TABLE Student ALTER Sage INT;

增加课程名必须取唯一值的约束条件：|
ALTER TABLE Student ADD CONSTRAINT UNIQUE(Cname);
删除基本表
语句格式：
DROP TABLE <表名> [CASCADE|RESTRICT];
例：
删除Student表：

DROP TABLE Student CASCADE;
基本表定义被删除，数据被删除，表上建立的索引，视图，触发器等一般也将删除
例：
索引的建立与删除
目的：加快查询速度，DBA或表的属主可以建立索引
DBMS一般会自动建立以下列上索引：
PRIMARY KEY;

UNIQUE;
DBMS自动维护索引；

DBMS自动选择是否使用索引以及使用哪些索引
- EDMBS索引一般采用B+树、HASH索引才实现
- 索引是关系数据库内部实现的技术，属于内模式的范畴
- CREATE INDEX语句定义索引可以定义为唯一索引(UNIQUE)、非唯一索引(不用写)、聚簇索引(CLUSTER)
语句格式：
CREATE [UNIQUE] [CLUSTER ] INDEX <索引名> ON <表名> (<列名> [<次序>]，<列名> [<次序>]…..)；
UNIQUE索引表明此索引每一个索引值只对应唯一的数据
例：为学生-课程数据库中的Student,Course,SC三个表建立索引
CREATE UNIQUE INDEX Stusno ON Student(Sno);

CREATE UNIQUE INDEX Coucno ON Course(Cno);
CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC); //ASC升序，DESC降序
CLUSTER表示要建立的索是聚簇索引，聚簇索引是值索引顺序表中记录的物理顺序一致索引组织，聚簇索引是最快的

例：在Student表的Sname列上建立一个聚簇索引
CREATE CLUSTER INDEX Stusname ON Student(Sname);
- 在最经常查询的列上建立聚簇索引以提高查询效率
- 一个基本表上最多只能建立一个聚簇索引
- 经常更新的列不宜建立聚簇索引
删除索引
语句格式：
DROP INDEX <索引名>;

删除索引时，系统会从数据字典中删去有关该索引的描述
DROP INDEX Stusname;
- 数据字典是关系数据库管理系统内部的一组系统表
- 数据字典记录了数据库中所有的定义信息，包括模式定义、索引定义、视图定义、完整性约束定义、各类用户对数据库的操作权限、统计信息等
- RDBMS执行SQL数据定义时，实际就是更新数据字典

数据查询

语句格式：

SELECT [ALL|DISTINCT] <目标列表达式> //默认是ALL

FORM <表的名字>|<表的视图>

[WHERE <条件表达式>] [GROUP BY <列名> [HAVING <列名>] ORDER BY <列名> [ASC|DESC]];

注意

语句中的字母不分大小写

语句中的标点符号必须是英文下的半角字符

[ ]中的内容，不是语句必须的内容，只有为了实现某系功能时才添加上

单表查询

对一个表的内容进行查询

选择表中的若干列
例：查询全体学生的学号与姓名
SELECT Sno,Sname FROM Student;
查询全部列：
SELECT * FROM Student;
查询经过计算的，表达式可以为，算术表达式，字符串常量，函数等
例：
查询全体学生的姓名及其出生年份
SELECT Sname,2014-Sage FROM Student; //2014-Sage是算术表达式
查询全体学生的姓名、出生年份和所在的院系，要求用小写字符表示系名
SELECT Sname,’Yyear of Birth:’,2014-Sage,LOWER(Sdept) FROM Student;
列别名
格式 <列名> <别名>
例：SELECT Sname NAME,’Yyear of Birth:’ BIRTH,2014-Sage BIRTHDAY,LOWER(Sdept) DEPARTMENT FROM Student;
选择表中的若干元组
- 消除取值重复的行
  如果没有指定DISTINCT关键词，则缺省为ALL
  例：查询选修了课程的学生号
  SELECT Sno FROM SC;
  等价于：SELECT ALL Sno FROM SC;
  指定DISTINCT关键词，去掉表中重复的行
  SELECT DISTINCT Sno FROM SC;
- 比较大小
  查询计算机科学系全体学生的名字
  SELECT Sname FROM Student WHERE Sdept=‘CS’;
  查询所有年龄在20岁以下的学生姓名及其年龄
  SELECT Sname,Sage FROM Student WHERE Sage<20;
  查询考试成绩有不及格的学生的学号
  SELECT DISTINCT Sno FROM SC WHERE Grade<60;
- 确定范围
  谓词：BETWEEN ….AND…..和NOT BETWEEN ……AND…..
  例：查询年龄在20-23岁学生的姓名、系别和年龄
  SELECT Sname,Sdept,Sage
  FROM Student WHERE Sage BETWEEN 20 ADN 30;
  例：查询年龄不在20-23岁学生的姓名、系别和年龄
  SELECT Sname,Sdept,Sage
  FROM Student WHERE Sage NOT BETWEEN 20 ADN 30;
- 确定集合
  谓词：IN<值表>，NOT IN<值表>
  例：查询信息系、数学系、和计算机科学系、学生的姓名和性别
  SELECT Sname,Ssex FROM Student
  WHERE Sdept IN(‘IS’,’MA”,’CS’);
  例：查询不是信息系、数学系、和计算机科学系、学生的姓名和性别
  SELECT Sname,Ssex FROM Student
  WHERE Sdept NOT IN(‘IS’,’MA”,’CS’);
- 字符匹配,%是任意长度，是任意一个字符
  谓词：[NOT] LIKE ‘<匹配串>’ [ESCAPE ‘<换码字符>’]
  例：查询学号为200215121的学生的详细情况
  SELECT * FROM Student WHERE Sno LIKE ‘200215121’;
  等价于
  SELECT * FROM Student WHERE Sno=‘200215121’;
  匹配串为含通配符的字符串
  例：查询所有姓刘学生的姓名、学号和性别
  SELECT Sname,Sno,Ssex
  FROM Student WHERE LIKE Sname ‘刘%’;
  例：查询‘欧阳’且全名为三个字的学生姓名
  SELECT Sname FORM Student WHERE Sname LIKE ‘欧阳_’;
  例：查询名字中第二字为’阳‘字的学生姓名和学号
  SELECT Sname,Sno FROM Student WHERE Sname LIKE ‘ _阳%’;
  例：查询所有不姓刘的学生姓名，学号和性别
  SELECT Sname,Sno,Ssex FROM Student WHERE Sname NOT LIKE ‘刘%’;
- 使用换码字符将通配符转义为普通字符
  例：查询DB_Design课程的课程号和学分
  SELECT Cno,Ccredit FROM Course
  WHERE Cname LIKE ‘DB\ _Design’ ESCAPE ‘\’;
  例：查询以”DB”开头，且倒数第三个字符为i的课程的详细情况
  SELECT * FROM Course
  WHERE Cname LIKE ‘DB\ _%i _ _’ ESCAPE ‘\’;
  ESCAPE说明 \ 为转义字符
- 涉及空值查询
  谓词 IS NULL 或 IS NOT NULL
  IS 不能用 =代替
  例：
  SELECT Sno,Cno FROM SC WHERE Grade IS NULL;
  例：查询所有有成绩的学生学号和课程号
  SELECT Sno,Cno FORM SC WHERE Grade IS NOT NULL;
- 多重条件查询
  用逻辑运算符AND和OR来联结多个查询条件，AND的优先级高于OR，可以用括号改变优先级
ORDER BY
ORDER BY 子句可以按一个或多个属性排列
升序：ASC
降序：DESC
缺省值为升序
当排序列含空值时：（空值默认为最大值）
ASC：排序列为空值的元组最后显示
DESC：排序列为空值的元组最先显示
例：查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列
SELECT Sno,Grade FROM SC WHERE Cno=‘3’ ORDER BY Grade DESC;
例：查询全体学生情况，查询结果先按所在系的系号升序，后同一系中的学生按年龄降序排列
SELECT * FROM Student ORDER BY Sdept, Sage 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;
例：查询学生200215012选修课程的总学分书
SELECT SUM(Ccredit) FORM SC,Course WHERE Sno=‘200215012’ AND SC.Cno=Course.Cno;
例：计算1号课程的学生平均成绩
SELECT AVG(Grade) FROM SC WHERE Cno=‘1’;
例：查询选修1号课程的学生的最高分
SELECT MAX(Grade) FROM SC WHERE Cno=‘1’;
聚集函数只能用于SELECT子句和GROUP BY中的HAVING子句中
GROUP BY
GROUP BY子句作用是：指定的一列或多列值分组，值相等的为一组，来细化聚集函数的作用对象
未对查询结果分组，聚集函数将作用于整个查询结果
对查询结果分组后，聚集函数将分别作用于每个组
例：求各个课程号级相应的选课人数
SELECT Cno,COUNT(Sno) FROM SC GROUP BY Cno;

GROUP BY子句分组后，可以使用HAVING短句指定筛选条件
例：查询选修了3门以上课程的学生号
SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) 大于 3;
HAVING短句与WHERE子句的区别
作用对象不同：WHERE子句作用于基表或视图，从中选择满足条件的元组。HAVING短句作用于组，从中选择满足条件组
WHERE子句是不能用聚集函数做为表达式的
例：查询平均成绩大于等于90分的学生学号和平均成绩
SELECT Sno,AVG(Grade) FROM SC
GROUP BY Sno HAVING AVG(Grade) 大于等于 90;
连接查询
连接查询的WHERE子句中用来连接两个表的条件称为

视图

建立视图
语句格式：
CREATE VIEW <视图名> [<列名>,…….] AS 查询语句 [WITH CHECK OPTION]
说明
组成视图的属性列名：全部省略或全部指定
子查询不允许含有ORDER BY子句和DISTINCT短句
CREATE VIEW IS_Student AS
SELECT Sno,Sname,Sage
FROM Student WHERE Sdept=‘IS’;

CREATE VIEW IS_Student AS
SELECT Sno,Sname,Sage
FROM Student WHERE Sdept=‘IS’
WITH CHECK OPTION;
例：建立信息选修了1号课程的学生视图
CREATE VIEW IS_SI(Sno,Sname,Grade)
AS
SELECT Student.Sno,Sname,Grade
FROM Student,SC
WHERE Sdept=‘IS’ ADN Student.Sno=SC.Sno AND SC.Cno=‘1’;
基于视图的视图
建立信息系选修1号课程且成绩在90分以上的学生的视图
CREATE VIEW IS_S2 AS
SELECT Sno,Sname,Grade
WHERE Grade 大于等于 90;
分组视图
CREATE VIEW S_G(Sno,Gavg)
AS
SELECT Sno,AVG(Grade)
FROM SC GROUP BY Sno;

数据库安全性

存取控制

用户权限定义和合法权限检查机制一起组成了数据库管理系统的存取控制子系统

常用存取控制
自主存取控制（DAC）
用户对不同数据对象有不同存取权限，不同用户对同一对象有不同的权限，用户可将其拥有的存取权限转授给其他用户
强制存取控制（MAC）
每一个数据对象被标以一定的密级，每一个用户也被授予某一个级别的许可证，对任意一个对象，只有具有合法许可证的用户才可以存取
授权：授予与回收
SQL通过GRANT和REVOKE语句实现
GRANT：对指定对象的指定操作权限授予指定的用户
语句格式：
GRANT <权限> ,[<权限>…..]
ON <对象类型> <对象名>,……
TO <用户>,……
[WITH GRANT OPTION];
其中[WITH GRANT OPTION],表示该权限可以再授予其他用户，如果没有，表示该权限不可以再授予其他用户
SQL标准不允许循环授权
例：把查询Student表权限给用户U1
GRANT SELECT
ON TABLE Student
TO U1;
例：把对表SC的查询权限授予所有用户
GRANT SELECT
ON TABLE SC
TO PUBLIC;
例：把查询Student表和修改学生学号的权限授予用户U4
GRANT UPDATE(Sno)
ON TABLE Student
TO U4;
REVOKE:授予的权限可以由数据库管理员或其他授权者用REVOKE语句收回
REVOKE <权限>,……
ON <对象类型> 对象名,….
FROM <用户>,….[CASCADE|RESTRICT];
例：把U4修改学生学号的权限收回
REVOKE UPDATE(Sno)
ON TABLE Student
FROM U4;
创建新用户
CREATE ROLE < username> [WITH CONECT|RESOURCE|DBA]

数据库角色
数据库角色是指被命名的一组与数据库操作相关的权限，角色是权限的结合
SQL中用CREATE ROLE语句创建角色，然后用GRANT语句授权，用REVOKE收回权限
格式
GRANT ROLE <角色名>;
GRANT <权限>,…..
ON <对象类型> 对象名
TO <角色>,……;
将一角色授予其他角色或用户
GRANT <角色1>,…
TO <角色3>,….
[WITH ADMIN OPTION];
例：
GREATE ROLE R1;

GRANT SELECT,UPDATE,INSERT
ON TABLE Student
TO R1;
例：将这个角色授予其他人
GRANT R1
TO 王平,张明,赵三;
可以一次性通过R1来回收王平的三个权限

REVOKE R1
FROM 王平;
强制存取
规则
仅当主的许可证级别大于或等于客体的密级时，该主体才能读取相应的客体
仅当主体的许可证级别小于或等于客体的密级时，该主体才能写相应的客体
视图机制
例：授予王平SELECT权限
GRANT SELECT
ON CS_Student
TO 王平;

审计（Audit）

提供多种审计查阅方式

审计分为用户级审计和系统级审计

AUDIT语句设置审计功能 NOAUDIT语句取消审计功能

例：对SC表结构或修改SC表数据的操作进行审计

AUDIT ALTER,UPDATE

ON SC;

取消表的一切审计

NOAUDIT ALTER,UPDATE

ON SC;

posted @ 2024-04-03 00:03 lkylin070 阅读(32) 评论(0) 收藏举报

刷新页面返回顶部

lkylin070

数据库

概述

数据(Data)：

数据库(DataBase,简称DB):

数据库管理系统(DataBase Management System,简称DBMS)

数据库系统(DataBase System，简称DBS)

数据模型(Data Model,DM)

数据模型的组成要素

概念模型

信息世界的基本概念

数据模型

关系模型

数据库系统结构

数据库系统的组成

关系数据库

关系数据库结构及形式化定义

关系模式

关系数据库

关系模型的存储结构

关系操作

基本关系操作

关系数据库的语言分类

关系的完整性

关系代数

传统的集合运算

关系演算

关系数据库标准语言SQL

SQL概述

学生-课程数据库

数据定义

数据查询

单表查询

视图

数据库安全性

存取控制

审计（Audit）

公告