当前位置：首页 > news >正文

数据库原理

news 2025/7/29 5:46:23

第一章数据库系统概述

第一节数据库基本概念

第二节数据管理技术的发展

第三节数据库系统的结构

一、数据库系统的三级模式结构

二、数据库系统的运行与应用结构

第四节数据模型

一、数据特征与数据模型组面要素

二、数据模型的分类

第二章关系数据库

第一节关系数据库概述

第二节关系数据模型

第三节关系数据库的规范化理论 *

第三章数据库设计

第一节数据库设计概述

第二节数据库设计的基本步骤

第三节关系数据库设计方法

第四章 SQL与关系数据库基本操作

第一节 SQL概述

第二节 MySQL预备知识

第三节数据定义

第四节数据更新

第五节数据查询

第五章数据库编程

第一节存储过程

第二节存储函数

第六章数据库安全与保护

第一节数据库完整性

第二节触发器

第三节安全性与访问控制

第四节事务与并发控制

第五节备份与恢复

第七章数据库应用设计与开发实例

第一节需求描述与分析

第二节系统设计

第三节系统实现

第八章数据管理技术的发展

第一节数据库技术发展概述

第二节数据仓库与数据挖掘

第三节大数据管理技术

第一章数据库系统概述

第一节数据库基本概念

一、数据

数据( Data ) 是描述事物的符号记录，是指用物理符号记录下来的，可以鉴别的信息。

文本数据：数字，字母、文字、特殊字符组成的文本

多媒体数据：图形、图像、动画、影像、声音、语言等

例如：日常生活和工作中使用的客户档案记录，商品销售记录等

数据是信息的载体，信息是数据的表现。

二、数据库

数据库（ Database ，简称 DB ）是长期储存在计算机内，有组织的，可共享的大量数据集合。

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

三、数据库管理系统

数据库管理系统(Database Management System, 简称 DBMS) 是位于用户与操作系统之间的一层数据库管理软件。

主要功能：

1、数据定义功能

提供数据定义语言(DDL) 定义数据库中的数据对象。

例如： 表、视图、存储过程、触发器等

2、数据操纵功能

提供数据操纵语言( DML ) 操纵数据实现对数据库的基本操作.

例如： 查询、插入、删除和修改

3、数据库的运行管理

保证数据的安全性、完整性、多用户对数据的并发使用，以及发生故障后的系统恢复。

4、数据库的建立和维护功能

创建数据库及对数据库空间的维护、数据库的备份与恢复功能、数据库的重组织功能和性能监视、分析等。

5、数据组织、存储和管理功能

例如：索引查找、顺序查找

6、其他功能

主要包括其他软件的网络通信功能、不同数据库系统之间的数据传输以及相互访问功能等

四、数据库系统

数据库系统(Database System, DBS) 是指在计算机中引入数据库技术之后的系统。

通常一个完整的数据库系统包括数据库、数据库管理系统及相关实用工具、应用程序、数据库管理员和用户。数据库管理员( Database Administrator， DBA) 专门负责对数据库进行维护，并保证数据库正常、高效运行。

第二节数据管理技术的发展

一、人工管理阶段

特点：

数据不保存、
应用程序管理数据
数据面向应用。

注意：记录内无结构，整体无结构

二、文件系统阶段

特点：

数据的管理者：文件系统，数据可长期保存

数据面向的对象：某一应用程序.

数据的共享程序： 共享性差，冗余度大。

数据的结构化： 记录内有结构，整体无结构。

数据的独立性： 独立性差, 数据的逻辑结构改变必须改应用程序。

数据控制能力：应用程序自已控制。

三、数据库系统阶段

特点：

1.数据集成

2.数据共享性高

3.数据冗余小;

4.数据一致性;

5.数据独立性高;

6.实施统一管理与控制

7.减少应用程序开发与维护的工作量

第三节数据库系统的结构

一、数据库系统的三级模式结构

1. 模式

模式也称为 概念模式或逻辑模式, 它是数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图。

外模式

外模式也称为子模式(Subschema)或用户模式，它是数据库用户(包括应用程序员和最终用户)能够看见和使用的局部数据的逻辑结构和特征的描述。
内模式

内模式也称为存储模式(Storage Schema), 它是对数据库中数据物理结构和存储方式的描述，是数据在数据库内部的表示形式。
三级模式结构的两层映像与数据独立性

(1) 外模式/模式映像(逻辑独立性)

(2)模式/内模式映像(物理独立性)

DBMS提供子模式的描述语言(Subschema DDL)来严格的定义子模式。

二、数据库系统的运行与应用结构

1.客户/服务器(Client/Server. C/S)模式

例如：QQ \ 桌面客户端游戏

2.浏览器/服务器(Browser/Server. BS)模式

例如：网站、在线web游戏

第四节数据模型

一、数据特征与数据模型组面要素

1.数据结构

数据结构描述的是系统的静态特性, 即数据对象的数据类型、内容、属性以及数据对象之间的联系。

2.数据操作

数据操作描述的是系统的动态特性，是对各种对象的实例允许执行的操作的集合，包括操作及有序的操作规则。

3.数据的约束条件

数据约束描述数据结构中数据间的语法和语义关联，包括相互制约与依存关系以及数据动态变化规则，以保证数据的正确性、有效性、与相容性。

二、数据模型的分类

1.概念层数据模型

(1) 信息世界中的基本概念

实休(Entity)

客观存在并可相互区别的事物称为实体，可以是具体的人、事、物、或抽象的概念。

属性(Attribute)

实体所具有的某一特性称为属性。一人实体可以由若干个属性来刻画。

码(Key)

唯一标识实体的属性集称为码.

域(Domain)

属性的取值范围称为该属性的域

实体型(Entity Type)

用实体名及其属性名集合来抽象和刻画，同类实体称为实体型。

实体集(Entity Set)

同型实体的集合称为实体集

联系(Relationship)

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

实体型间联系：

一对多 1：N

一对一 1：1

多对多 N: M

(2).概念模型的表示方法

概念模型：用来描述现实世界的事物，与具体的计算机系统无关

最典型的概念模型是实体联系( E - R)

例如：

2.逻辑层数据模型

(1) 层交模型

(2) 网状模型

(3) 关系模型

(4) 面向对象模型

3.物理层数据模型

物理层数据模型，也称为数据的物理模型(PhysicalModel), 其描述数据在存储介质上的组织结构，是逻辑模型的物理实现，即每一种逻辑模型在实现时都有与其相对应的物理模型。

第二章关系数据库

第一节关系数据库概述

系统而严格的提出关系模型的是美国IBM公司E。F.Codd

1970年提出关系数据模型
E.F.Codd 《Communication of ACM》
之后，提出了关系代数和关系演算的概念
1972年提出了关系的第一、第二、第三范式
1974年提出了关系的BC范式

关系数据库应用数学方法来处理数据库的数据

80年代后，关系数据库系统成为最重要、最流行的数据库系统。

典型实验系统：

System R、 University INGRES

典型商用系统：

ORACLE、SYBASE、INFORMIX、IBM DB2

docker exec -it 17 bash

mysql -u'root' -p'root'

mysql -h 192.168.0.109 -P 3306 -u root -p

第二节关系数据模型

一、关系数据结构

关系模型的数据结构非常简单，只包含单一的数据结构，即关系。

例如一张记录学生基本信息的二维表格，该表称为学生基本信息登记表。

基本术语：

表(Table)

表，也称为关系，是一个二维的数据结构，它由表名，构成表的各个列(如学号、姓名)及若干行数据(各个学生的具体信息)组成。每个表有一个唯一的表名，表中每一行数据描述一条具体的记录值，如一个学生的基本信息。

关系（Relation）

一个关系逻辑上对应一张二维表，可以为每个关系取一个名称进行标识。

列(Column)

表中的列，也称作字段(Field)或属性(Attribute).表中每一列有一个名称，称为列名、字段名或属性名。每一列表示实体的一个属性，具有相同的数据类型。

属性(Attribute)

表中的一列即为一个属性，给每一个属性起一个名称即属性名。与之同义的术语是“列”。表中属性的个数称为关系的元或度。列的值称为属性值; 属性值的取值范围称为值域。

行(Row)

表中的行(Row), 也称作元组(Tuple)或记录(Record).

元组(Tuple)

表中的一行即为一个元组。例如（2013110101，张晓勇，男， 1997-12-11，山西，汉，AC1301,XXXI）

分量(Component)

元组中的一个属性值，称为分量。

码或键(Key)

如果在一个关系中，存在这样的属性(或属性组)，使得在该关系的全任何一个关系状态中的两个元组，在该属性(或属性组)上值的组合都不相同，即这些属性(或属性组)的值都能用来唯一标识该关系的元组。则称这些属性(或属性组)为该关系的码或键。

超码或超键(Super Key)

如果在关系的一个码中移去某个属性，它仍然是这个关系的码，刚称这样的码或键为该关系的超码或超键。

候选码或候选键(Candidate Key)

如果在关系的一个码或键中，不能从中移去任何一个属性，否则它就不是这个关系的码或键，则称这样的码或键为该关系的候选码或候选键。

主码或主键(Primary Key)

在一个关系的若干个候选码或候选键中指定一个用来唯一标识关系的元组，则称这个被指定的候选码或候选键为该关系的主码或主键。

全码或全键(All-Key)

一个关系模式的所有属性集合是这个关系的主码或主键则称这样的主码或主键为全码或全键。

主属性(Primary Attribute)和非主属性(Nonprimary Attribute)

关系中包含在任何一个候选码中的属性称为主属性或码属性，不包含在任何一个候选码中的属性称为非主属性或非码属性。

外码和外键(Fpreogn Key)

当关系中的某个属性(或属性组)不是这个关系的主码或候选码，而是别一关系的主码时，称该属性(或属性组)为这个关系的外码或外键。

参照关系(Referencing Relation) 和被参照关系(Referenced Relation)

参照关系也称为从关系，被参照关系也称为主关系，它们是指以外码相关联的两个关系。以外码作为主码的关系称为被参照关系，外码所在的关系称为参照关系。被参照关系与参照关系是通过外码相联系的，这种联系通常是一对多的联系。

域(Domain)

域表示属性的取值的范围

数据类型(Data Type)

表中每个列都有相应的数据类型，它用于限制(或容许)该列中存储的数据。每个字段表示同一类信息。具有相同的数据类型。

关系模式(Relation Schema)

同数据模型一样，数据库也有"型"(type)和“值”(value)之分。在关系数据库中，关系模式是型，关系是值，即关系模式是对关系的描述。关系模式是静态的、稳定的,而关系是动态的、随时间不断变化的。

关系数据库(Relation DataBase)

关系数据库是以关系模型作为数据的逻辑模型，并采用关系作为数据组织方式的一类数据库，其数据库操作建立在关系代数的基础上。

二、关系操作集合

1、基本的关系操作

查询

选择、投影、连接、除、并、差、交、笛卡尔积

数据更新

插入、删除、修改

查询的表达能力很强，是其中最主要的部分。

关系操作的特点

集合操作方式，即操作的对象和结果都是集合。

2.关系数据语言的分类

关系操作的能力可以用两种方式来表示： 代数方式和逻辑方式。

代数方式主要有关系代数，它是通过对关系的操作来表达查询要求的方式;

逻辑方式主要有关系演算，这是用谓词来表达查询要求的主式。

具有关系代数和关系演算双重特点的语言。

典型代表： SQL

3.关系代数

(1) 传统的集合运算

并、差、交、广义笛卡乐积(CARTESIAN PRODUCT)

并( UNION) S1∪S2 差 S1-S2 交 S1∩S2

(2) 专门的关系运算符

选择、投影、连接、除

选择(Select)

SELECT 关系名 WHERE 条件

其中，条件是由常数、属性名或列名、比较操作符及逻辑操作符组成的条件表达式。

比较操作符：＞ ≥ ＜ ≤ ＝ ≠

逻辑操作符：￢ ∧ ∨

SELECT S1 WHERE 性别="男"

投影 (PROJECTION)

投影操作表示为：ΠA（R）

其中，R为被运算关系名，A为属性序列。

PROJECTION 关系名 (属性名1，属性名2，属性名3 ...)

PROJECTION S1 (学号，宿舍)

连接 (JION)

连接运算表示为： R iθj S

R和S代表两个不同的关系;

i 和 j 分别代表R的第 i 列和 S 的第 j 列属性

θ代表的比较运算符：＞ ≥ ＜ ≤ ＝ ≠

* JOIN 关系名1 AND 关系名2 WHERE 条件 *

除 (DIVISION)

除关系表示为 R ÷ S

其中R和S代表两个不同的关系。在除运算中，若被除关系为 m 元关系，除关系为 n 元关系，则运算结果为一个 m-n 元关系。

三、关系的完整性约束

1、实体完整性约束(Entity Integrity Constraint)

* 实体完整性约束是指关系的主属性，即主码的组成不能为空，也就是关系的主属性不能是空值NULL。*

2、参照完慗性约束(Referential Integrity Constraint)

现实世界中的实体之间往往存在着某种联系

教师（职工号，姓名，性别，职称，系编号）

系（系编号，系名，办公地点，办公电话）

3、用户自定义完整性约束(User-defined Integrity Constraint)

用户定义的完慗性约束是针对某一应用环境的完整性约束条件，它反映了某一具体应用所涉及的数据就满足的要求。

4、关系模型完整性约束的检验

（1）执行插入操作

（2）执行删除操作

（3）执行更新操作

第三节关系数据库的规范化理论 *

一、关系模式中可能存在的冗余和异常问题

1.数据冗余

2.更新异常

3.插入异常

4.删除异常

二、函数依赖与关键字

定义2.1 设Ro任一给定关系，如果对于R中属性X的每一个值，R中的属性Y只有唯一值与之对应，则称X函数决定Y或称Y函数依赖于X，记作X->YY. 其中。X称为决定因素。

例如：表2.9所示的有关学生住宿登记的关系S1中存在如下函数依赖：

SNO -> SNAME

SNO -> SSEX

SNO-> SROOM （假定一个学生仅住一个宿舍）

1.完全函数依赖

定义2.2 设R为任一给定关系， X、Y为其属性集，若X -> Y, 且对X中的任何真子集X‘ 都有 X’ -> Y.则称Y完全函数依赖于X.

2.部分函数依赖

定义2.3 设R为任一给定关系， X、Y为期属性集，若 X->Y, 且X中存在一个真子集X‘满足X'-> Y, 则称Y部分函数依赖X。

3.传递函数依赖

定义2.4 设R为任一给定关系， X、Y、Z为其不同属性子集，若X -> Y, Y !->X, Y -> Z, 则有X -> Z, 称为Z传递函数依赖于X。

关键字

定义2.5 设R为任一给定关系，U为其所含的全部属性集合，X为U的子集，若有完全函数依赖X->U.则X为R的一个候选关键字。

三、范式与关系规范化过程

满足最低要求的称为第一范式，称简1NF，这是最基本的范式；在第一范式的基础上进一步满足一些新要求的称为第二范式（2NF）；以此类推，再进一步的范式是第三范式(3NF)以及改进形式BCNF(Boyce-Codd Normal Form); 当然，还有更进一步的高级范式，如第四范式(4NF)、第五范式(5NF)等。

1NF 不可再分

↓ 消除非主属性对码的部分函数依赖

2NF

↓ 消除非主属性对码的传递函数依赖

3NF

↓ 消除主属性对码的部分和传递函数依赖

BCNF

四、关系规范化现论的应用

关系规范化理论主要应用于数据库设计中的概念设计阶段，对所产生的概念设计，可用它来分析其实体划分是否适合，判断属性分配到哪个实体中更为合理。在实现设计中当将E-R图向关系模型转换时，还可以用它来分析并发现概念设计中可能存在的遗漏或不当之处，特别是联系实体是否不单独转换为一独立关系而集成到与之相联的基本实体中去处理时，规范化理论是最有效的评价准则。

第三章数据库设计

第一节数据库设计概述

一、数据库的生命周期

数据库的生命周期可分为两个阶段，分别是数据库分析与设计阶段、数据库实现与操作阶段。

其中，数据库分析与设计阶段包括需求分析、概念设计、逻辑设计和物理设计四个环节；数据库实现与操作阶段包含数据库的实现、操作与监督、修改与调整三个子阶段。

二、数据库设计的目标

事实上，数据库设计具有两个十分重要的目标，即满足应用功能需求和良好的数据库性能。

三、数据库设计的内容

数据库设计是从用户对数据的需求出发，研究并构造数据库的过程，其包含两个方面的内容：

1.数据库结构设计

2.数据库行业设计

四、数据库设计的方法

1.直观设计法

2.规范设计法

（1）新奥尔良(New Orleans)设计方法

（2）基于E-R模型的数据库设计方法

（3）基于第三范式的设计方法

3.计算机辅助设计法

使用工具： UML 用例图，对象图 rose visio

五、数据库设计的过程

第二节数据库设计的基本步骤

一、需求分析

1.确定数据库范围

数据库设计的第一项工作就是要确定数据库范围，即确定数据库应支持哪此应用功能。

调查问卷--实际工作

2.应用过程分析

应用过程分析是指了解并分析数据与数据处理间的关系，在数据库范围确定之后，数据库设计人员应逐次地了解和分析每一部门或功能要用到哪些数据、数据使用的顺序、对数据作何处理和处理的策略以及处理的结果等。

3.收集与分析数据

（1）静态结枸

数据的静态结构是指不施加应用操作于其上时数据的原始状况，这可通过数据分类表和数据元素表进行说明。

（2）动态结构

动态结构是指将应用操作施加于数据之上后数据的状况，可通过任务分类表和数据操作特征表进行说明。

（3）数据约束

数据约束是指使用数据时的特殊要求。

4.编写需求分析报告

需求分析报告通常包含如下内容：

（1）数据库的应用功能目标

（2）标明不同用户视图范围

（3）应用处理过程需求说明

（4）数据字典

（5）数据量

（6）数据约束

二、概念结构设计

概念结构设计的任务是在需求分析中产生的需求分析报告的基础上，按照特定的方法设计满足应用需求的用户信息结构，该信息结构通常称为概念模型。

其中是采用E-R图作为概念模型的描述工具。

三、逻辑结构设计

四、物理设计

物理设计是指对于一个给定的数据库逻辑结构，研究并构造物理结构的过程，其具体任务主要是确定数据库在存储设备上的存储结构及存取方法，因DBMS的不同还可能包括建立索引和聚集，以及物理块大小、缓冲区个数和大小、数据压缩的选择等。

五、数据库实施

1.加载数据

2.应用程序设计

3.数据库试运行

六、数据库运行和维护

只有经过试运行之后，确认系统无故障或暂未发现故障时，系统才能投入到生产实际中运行。数据库系统投入实际运行标志着数据库设计和应用开发的基本完成，但绝不意味着设计和应用开发工作的终止。

系统维护中最困难的工作是数据库重组与重构。

第三节关系数据库设计方法

一、关系数据库设计过程与各级模式

二、概念结构设计方法

E-R图的表示方法

两个实体型之间的三类关系

两个以上的实体型之间的联系

单个实体型内的联系
1. 局部信息结构设计
（1）确定局部范围

（2）选择实体

（3）选择实体的关键字属性

（4）确定实体间联系

（5）确定实体间属性

3.全局信息结构设计

（1）属性冲突

（2）命名冲突

（3）结构冲突

三、逻辑结构设计方法

1.E-R图向关系模型的转换

2.数据模型的优化

3.设计用户子模式

四、物理设计方法

1.建立索引

2.建立聚集

第四章 SQL与关系数据库基本操作

第一节 SQL概述

一、SQL的发展

结构化查询语言（Structured Query Language 简称SQL)是一种介于关系代数和关系演算之间的语言。由于具有语言简洁、方便实用、功能齐全等优点，很快得到推广和应用。随着关系数据库的流行，SQL语言最终发展成为关系数据库的标准语言和数据库领域中一个主流语言。SQL已经被ANSI(美国国家标准化组织)确定为数据库系统的工业标准，这是数据库系统的通用语言。利用SQL，用户可以用几乎相同的语句在不同的数据库系统上执行同样的操作。MySql利用SQL对数据库进行操作，此外SQL语言不区分大小写。

二、SQL的特点

1.SQL不是某个特定数据库供应商专有的语言。几乎所有重要的关系数据库管理系统都支持SQL，所以掌握SQL可以帮助用户与几乎所有的关系数据库进行交互。

2.SQL简单易学。它的语句全都是由具有很强描述性的英语单词所组成，而且这些单词的数目不多。

3.SQL尽管看上去很简单，但它实际上是一种强有力的语言，灵活使用其语言元素，可以进行非常复杂和高级的数据库操作。

三、SQL的组成

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

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

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

4.嵌入式和动态SQL规则

5.SQL调用和会话规则

第二节 MySQL预备知识

一、MySQL使用基础

1.LAMP(Linux + Apache + MySQL + PHP/Perl/Python)，即使用Linux作为操作系统，Apache作为Web服务器，MySQL作为数据库管理系统，PHP、Perl或Python语言作为服务器端脚本解释器。

2.WAMP(Windows + Apache + MySQL + PHP/Perl/Python), 即使用Windows 作为操作系统，Apache作为Web服务器，MySQL作为数据库管理系统，PHP、Perl或Python语言作为服务器端脚本解释器。

二、MySQL中的SQL

MySQL 中的SQL对关系数据库模式的支持

1.常量

（1）字符串常量

字符串是指用单引号或双引号括起来的字符序列，分为ASCII字符串常量（一个字节）和 Unicode字符串常量（两个字节）。

ASCII例如: 'hello', 'How are you!'

Unicode字符串常量与ASCII字符串常量相似，但它前面有一个N标志符，例如： N'hello'

（2）数值常量

数值常量可以分为整数常量和浮点数常量

整数常量即不带小数点的十进制数，例如：1894， 2 ，+145345234. -2147483648.

浮点数常量是使用小数点的数值常量，例如： 5.26， -1.39. 101.5E5,0.5E-2

（3）十六进制常量

MySQL支持十六进制值。一个十六进制值通常指定为一个字符串常量，每对十六进制数字被转化为一个字符，其最前面有一个大写字母“X”或小写字母“x”。在引号中只可以使用***数字"0"到“9”及字母"a"至"f"或“A”到“F”。倒如 X‘41’ 表示大写字母A. * X‘41’=4 * 16^1 + 1 * 16^0=65 *。 x'4D7953514C'表示字符串MySQL。

十六进制数值不区分大小写，其前缀“X”或"x"可以被"0x"取代而且不用引号，即X‘41’可以替换为0x41，注意： “0x”中一定要小写。

十六进制值的默认类型是字符串。如果想要确保该值作为数字处理，可以使用CAST(...AS UNSIGNED)。

（4）日期时间常量

日期时间常量: 用单引号将表示日期时间的字符串括起来构成。

日期型常量包括年、月、日，数据类型为DATE, 表示为"1999-06-17"这样的值。

时间型常量包括小时数、分钟数、秒数及微秒数，数据类型为TIME，表示为"12:30:4300013"这样的值。

（5）位字段值

可以使用b'value'符号写位字段值，value是一个0和1写成的二进制值，直接显示b'value'的值可能是一个特殊的符号。

（6）布乐值

布尔值只包含两个可能的值， TRUE和FALSE。FALSE的数字值为"0", TRUE的数字值为1：

（7）NULL值

NULL值可适用于各种列类型，它通常用来表示“没有值”、“无数据”等意义，并且不同于数字类型的“0”或字符串类型的空字符串。

2.变量

用户可以在表达式中使用自己定义的变是，这样的变是叫作用户变是。

用户可以先在用户变量中保存值，然后在以后引用它，这样可以将值从一个语句传递到另一个语句，在使用用户变量前必须定义和初始化。如果使用没有初始化的变量，它的值为NULL.

用户变量前加@用来区分列名。

必须在系统变是前加两个@。

3.运算符

（1）算术运算符在两个表达式上执行数学运算，这两个表达式可以是任何数字数据类型。算术运算符有： +（加）、-（减）、（乘）、/（除）和 % (求模)* 5种运算。

（2）位运算符有: &(位与)， | （位或）、^（位异或）、 ~（位取反）、 >>（位右移）<<(位左移)。

（3）比较运算符（又称关系运算符），用于比较两个表达式的值，其运算结果为逻辑值，可以为三种之一： 1（真）、0（假）及 NULL(不能确定)。

运算符含义运算符含义
= 等于 <= 小于等于
> 大于 <>、!= 不等于
< 小于 <=> 相等或都等于空
>= 大于等于

（4）逻辑运算符

运算符运算规则运算符运算规则
NOT 或！逻辑非 OR或\|\| 逻辑或
AND或&& 逻辑与 XOR 逻辑异或

4.表达式

表达式就是常量、变量、列名、复杂计算、运算符和函数的组合。一个表达式通常可以得到一个值。与常量和变量一样，表达式的值也具有某种数据类型，可能的数据类型有字符类型、数值类型、日期时间类型。这样，根据表达式的值的类型，表达式可分为字符型表达式、数值型表达式和日期表达式。

5.内置函数

数学函数：例如ABS()函数、SQRT()函数;

聚合函数：例如COUNT()函数;

字符串函数：例如ASCII()函数、CHAR()函数;

日期和时间函数: 例如NOW()函数、YEAR()函数

加密函数：例如ENCODE()函数、ENCRYPT()函数

控制流程函数：例如IF()函数、IFNULL()函数;

格式化函数：例如FORMAT()函数

类型转换函数: 例如CAST()函数

系统信息函数：例如USER()函数、VERSION()函数

第三节数据定义

一、数据库模式定义

1.创建数据库

语法格式：

CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] db_name

[DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name

说明：语句中"[]"内为可选项

db_name：数据库名

IF NOT EXISTS：在建数据库前进行判断，只有该数据库目前尚不存时才执行CREATE DATABASE操作。

DEFAULT：指默认值.

CHARACTER SET: 指定数据库字符集(charset)charset_name字符集名称。

COLLATE: 指定字符集的校对规则， collation_name为校对规则名称。

2.选择数据库

USE db_name;

3.修改数据库

语法格式：

ALTER { DABABASE| SCHEMA } [db_name] alter_specification [, alter_specification] ...

其中alter_specification:

[DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name

4.删除数据库

语法格式：

DROP DATABASE [IF EXISTS] db_name;

db_name是要删除的数据库名。可以使用IF EXISTS子句以避免删除不存在的数据库时出现的MySQL错误信息。

5.查看数据库

SHOW [DATABASES|SCHEMAS];

二、表定义

数值类型：整数类型： INT

浮点类型：FLOAT DOUBLE DECIMAL

日期和时间类型：日期类型： DATE (YYYY-MM-DD) YEAR

日期时间类型： DATETIME TIME

时间戳类型： TIMESTAMP

字符串类型：固定长度类型： CHAR 最大长度 255

可变长类型： VARCHAR 最大长度 65535

文本类型： TEXT

1.创建表

语法格式：

CREATE TABLE [IF NOT EXISTS] tbl_name (字段名1 数据类型[列级完整性约束条件] [默认值] , 字段名2 数据类型 [列级完整生束条件] [默认值] [, .......] [表级完整性约束])[ENGINE=引擎类型];

添加字段

ALTER TABLE tb_name ADD [COLUMN] 新字段名数据类型 [约束条件] [FIRST | AFTER 已有字段名]

修改字段 ALTER TABLE tb_name CHANGE [COLUMN] 原字段新字段名数据类型 [约束条件]

ALTER TABLEtb_nameALTER [COLUMN] 字段名 {SET | DROP} DEFUALT;

ALTER TABLE tb_name MODIFY [COLUMN] 字段名数据类型 [约束条件] [FIRST | AFTER 已有字段名]

删除字段

ALTER TABLE tb_name DROP [COLUMN] 字段名;

重命名表

语法格式

ALTER TABLE 原表名 RENAME [TO] 新表名 RENAME TABLE 原表名1 TO 新表名1 [, 原表名2 TO 新表名2] ....;

删除表

语法格式:

DROP TABLE [ IF EXISTS ] 表1 [, 表2] .......;

查看表:

查看表语法格式：

SHOW TABLES [{FROM | IN} db_name];

查看表结构：

SHOW COLUMNS {FROM | IN} tb_name [{FROM | IN} db_name];

三、索引定义

可以理解成目录，在查找时有目录查找速度会加快。

1.索引的创建

(1) CREATE TABLE tbl_name[col-name data_type] [CONSTRAINT index_name]

[UNIQUE] [INDEX|KEY] [index-name] (index_col_name[length]) [ASC|DESC]

(2) CREATE INDEX (常用)

CREATE [UNIQUE] INDEX index_name ON tbl_name(col_name[(length)] [ASC|DESC], ....)

(3) ALTER TABLE

ALTER TABLE tbl_name

ADD [UNIQUE| DULLTEXT] [INDEX|KEY] index-name (col_name[length] [ASC|DESC], ....)

2.索引的查看

语法格式

SHOW {INDEX | INDEXS|KEYS} {FROM | IN} tbl_name [{FROM | IN} db_name]

3.索引的删除

语法格式：

(1) DROP INDEX index_name ON tbl_name

(2) ALTER TABLE tbl_name DROP INDEX index_name

第四节数据更新

一、插入数据

INSERT 语法格式:

INSERT INTO tb_name (column_list) VALUES (value_list) [, (value_list2)], ....

从数据查询中插入数据

INSERT INTO tb_name1(column_kust1)

SELECT (column_list2)

FROM tb_name2 WHERE (condition)

注意：column_list1 与 column_list2 必须个数相同且数据类型一一对应。

使用INSERT...SET语句插入部分列值数据

INSERT [INTO] tbl_name SET col_name = {expr | DEFAULT}, ***

使用INSERT...SELECT语句插入子查询数据

INSERT [INTO] tbl_name [(col name, ***)]

SELECT ....

二、删除数据

语法格式：

DELETE FROM tb_name [WHERE <condition];

删除所有记录可以使用

TRUNCATE [TABLE ] tb_name

注意： TRUNCATE 是删除整个表，重新建一个新表，在删除全部数据时候效率高于DELETE,DELETE是逐条删除。TRUNCATE无法恢复，AUTO_INCREMEN计数据重置。

三、修改数据

语法格式：

UPDATE tbl_name

SET col_name = expr1 [, col_name2 = expr2 ...]

[WHERE where_definition]

第五节数据查询

一、SELECT语句

基本语法：

SELECT [ALL | DISTINCT | DISTINCTROW ] select_expr, ...

[FROM table_reference [, table_reference ] ... ]

[WHERE where_definition ] //WHERE子句//

基本语法：

[ GROUP BY {col_name | expr | position } [ASC | DESC], ... [WITH ROLLUP] ] //GROUP BY 子句//

[ HAVING where _definition ] //HAVING子句//

[ORDER BY { col_name | expr | position } [ ASC | DESC], ... ] //ORDER BY 子句 //

[ LIMIT {[offset, ] row_count | row_count OFFSET offset }] //LIMIT子句 //

SELECT语句中各子句的使用次序及说明

子句说明是否必须使用
SELECT 要返回的列或表达式是
FROM 从中检索数据的表仅在从表选择数据时使用
WHERE 等级过滤否
GROUP BY 分组说明仅在按组计算聚合时使用
HAVING 组级过滤否
ORDER BY 输出排序顺序否
LIMIT 要检索的行数否

二、列的选择与指定

1.选择指定的列

SELECT classNO, department, className FROM tbl_class;

选择所有的表

SELECT * FROM tbl_class;

2.定义并使用列的别名

SELECT studentName as 姓名， sex as 性别， YEAR(NOW()) - YEAR(birthday) as 年龄 from tbl_student;

3.替换查询结果集中的数据

CASE

WHEN 条件1 THEN 表达式1

      WHEN 条件2 THEN 表达式2

...

ELSE 表达式

END [AS] column_alias

4.计算列值

使用SELECT语句对列进行查询时，在结果集中可以输出对列值计算后的值，其具体使用方法是将SELECT语句的语法项"select_expr"指定对应列参与计算的表达式。

5.聚合函数

MySQL 中常用聚合函数
函数名说明
COUNT 求组中项数，返回INT类型整数
MAX 求最大值
MIN 求最小值
SUM 返回表达式中所有值的和
AVG 求组中值的平均值
STD或STDDEV 返回给定表达式中所有值的标准值
VARIANCE 返回给定表达式中所有值的方案
GROUP_CONCAT 返回由属于一组的列值连接组合而成的结果
BIT_AND 逻辑与
BIT_OR 逻辑或
BIT_XOR 逻辑异或

三、FROM子句与多表连接查询

1 . 交叉连接

2.内连接

FROM table1 [ INNER ] JOIN table2 ON 连接条件

等值连接

FROM table1[, table2] ...

WHERE table1.字段 <比较运算符> table2.字段

3.外连接

左连接：

FROM table1 [LEFT] JOIN table2 ON 连接条件

右连接：

FROM table1 [RIGHT] JOIN table2 ON 连接条件

四、WHERE子句与条件查询

查询条件操作符
比较 = ， <>, != , < . <= , >= , !< , !> NOT+ 比较运算符表达式
确定范围 BETWEEN AND, NOT BETWEEN AND
确定集合 IN， NOT IN
字符匹配 LIKE， NOT LIKE
空值 IS NULL。 IS NOT NULL
多重条件 AND , OR

子查询：

IN语法:

WHERE 列名 IN (值列表或者子SELECT[但必须只有一列])

比较运算符：

WHERE 列名 <比较运算符> (值列表或者子SELECT[但必须只有一列])

EXIST 查询

WHERE EXIST (子SELECT)

五、GROUP BY子句与分组数据

语法： [GROUP BY 字段列表 ] [HAVING <条件表达式>]

含义：按照某一列的值相同的分成一组，进行聚合计算。

六、 HAVING子句在SELECT语句中，除了能使用GROUP BY子句分组数据之外，还可以使用HAVING子句来过滤分组，即在结果集中规定包含哪些分组和排除哪些分组。

七、ORDER BY 子句

语法： [ORDER BY 字段] [ASC | DESC] [ , 字段2 [ ASC | DESC ]] ......

八、LIMIT 子句

LIMIT [位置偏移量, ] 行数

第六节视图

视图

视图是从一个或多个表(或视图)导出的表. 视图是数据库的用户使用数据库的观点。例如，对于一个学校，其学生的情况存于数据库的一个或多个表中，而作为学校的不同职能部门，所关心的学生数据的内容是不同的。

视图是一张虚表。

视图一经定义以后，就可以像表一样被查询、修改、删除和更新。

一、创建视图

语法格式：

CREATE [ OR REPLACE ] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE }]

VIEW view_name [ (column_list) ]

AS select_statement

[ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

二、删除视图

语法格式：

DROP VIEW [ IF EXISTS ] view_name [ , view_name ] ...

三、修改视图定义

语法格式：

ALTER VIEW wew_name [ (column_list) ]

AS select_statement

[ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

四、查看视图定义

语法格式:

SHOW CREATE VIEW view_name

五、更新视图数据

要通过视图更新基本表数据，必须保证视图是可更新视图，即可以在INSERT、UPDATE或DELETE等语句当中使用它们。对于可更新的视图，在视图中的行和基表中的行之间必须具有一对一的关系。还有一些特定的其他结构，这类结构会使得视图不可更新。

六、查询视图数据

视图一经定义后，就可以如同查询数据库中的真实表一样，对视图进行数据查询检索，这也是对视图使用最多的一种操作。

第五章数据库编程

第一节存储过程

一、存储过程的基本概念

存储过程是一组为了完成某项特定功能的SQL语句集，其实质上就是一段存储在数据库中的代码，它可以由声明式的SQL语句(如CREATE、UPDATE和SELECT等语句)和过程式SQL语句( 如IF ... THEN ... ELSE 控制结构语句)组成。

存储过程的优点：

1.提高运行速度。

2.增强了SQL的功能和灵活性。

3.可以降低网络的通信量

4.减轻了程序编写的工作量

5.间接实现安全控制功能。

二、创建存储过程

语法桡式.

CREATE PROCEDURE sp_name

( { proc_parameter[ , ... ] } )

[ characteristic ... ] routine_body [ BDGIN ... ... END ]

其中， proc_parameter 的参数如下：

[ IN | OUT | INOUT ] param_name type

characteristic特征如下：

LANGUAGE SQL

| [ NOT ] DETERMINISTIC

| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

| SQL SECURITY { DEFINER | INVOKER }

| COMMENT 'string'

DELIMITER语法桡式： ***

DELIMITER $$

修改结束符为 $$

说明：因为MySQL 运行命令中结束符号为分号，编写存储过程中为了避免冲突，修改结束符号。

三、存储过程体

1.局部变量

在存储过程中可以声明局部变是，它们可以用来存储临时结果。要声明局部变量必须使用DECLARE语句，在声明局部变是的同时也可以对其赋一初始值。

DECLARE 语法格式如下：

DECLARE var_name [, ...] type [ DEFAULT value ]

说明： var_name为变量名；type为变量类型;

DEFAULT子句给变量指定一个默认值，如果不指定默认为NULL的话.

例如： DECLARE sno CHAR(10)

2.SET语句

要给局部变量赋值可以使用SET语句，SET语句也是SQL本身的一部分。

语法格式为： SET var_name = expr [ , var_name = expr ] ...

3.SELECT ... INTO 语句

使用这个SELECT ... INTO 语法可以把选定的列值直接存储到变量中。因此，返回的结果只能有一行。

语法格式为： SELECT col_name [ , .... ] INTO var_name [ , ... ] table_expr

说明： col_name是列名， var_name是要赋值变量名， table_expr是SELECT语句中的FROM子句及后面部分。

4.流程控制语句

（1）IF语句

IF-THEN-ELSE语句可根据不同的条件执行不同的操作，语法格式为：

IF search_condition THEN statement_list

[ ELSEIF search_condition THEN statement_list ]

[ ELSE statement_list ]

END IF

说明：search_condition是判断的条件， statement_list中包含一个或多个SQL语句。 search_condition的条件为真时，就执行相应的SQL语句。区别是IF()函数，不要混淆。

（2）CASE语句说明：一个CASE语句经常可以充当一个IF-THEN_ELSE语句。

形式1 形式2
CASE case_value CASE
WHEN when_value THEN statement_list WHEN search_condition THEN statement_list
[ WHEN when_value THEN statement_list ] ... [ WHEN search_condition THEN statement_list ]...

[ ELSE statement_list ] [ ELSE statement_list ]

END CASE END CASE

case_value: 表达式或值 search_condition: 指定一个比较表达式

when_value: 与case_value比较

（3）循环语句

MySQL支持3条用来创建循环的语句：WHILE、REPEAT和LOOP语句。在存储过程中可以定义0个、1个或多个循环语句。

形式1 形式2 形式3
[ begin_label: ] [ begin_label: ] [ begin_label: ]
WHILE REPEAT LOOP
search_condition statement_list statement_list

DO UNTIL END LOOP

statement_list search_condition

END WHILE END REPEAT

[ end_label ] [ end_label ]

循环语句说明：

①WHILE先判断，REPEAT后判断，WHILE条件为真进行循环，REPEAT条件为真结束循环。

②在LOOP循环内的语句一直重复至循环被退出，退出时通常伴随着一个LEAVE语句。

③LEAVE语句经常和BEGIN...END或循环一起使用。

④LEAVE label

5.游标

一条SELECT ... INTO 语句返回的是带有值的一行，这样可以把数据读取到存储过程中，但是常规的SELECT语句返回的是多行数据，如果要处理它需要引入游标这一概念。

MySQL支持简单的游标。在MySQL中，游标一定要在存储过程或函数中使用，不能单独在查询中使用。名字要唯一，游标本身是执行SELECT检索出来的结果集。

（1）声明游标

语法格式： DECLARE cursor_name CURSOR FOR select_statement

说明： cursor_name是游标的名称，游标名称使用同表名同样的规则。select_statement是一个SELECT语句，返回的是一行或多行的数据。

（2）打开游标

声明游标后，要使用游标从中提取数据，就必须先打开游标。在MySQL中，使用OPEN语句打开游标，其格式为:

OPEN cursor_name

在程序中，一个游标可以打开多次，由于其他的用户或程序本身已经更新了表，所以每次打开结果可能不同。

（3）读取数据

游标打开后，就可以使用FETCH ... INTO语句从中读取数据。

语法格式： FETCH cursor_name INTO var_Name [ , var_name ] ...

说明：FETCH ... INTO 语句与 SELECT ... INTO 语句具有相同的意义，FETCH语句是将游标指向的一行数据赋给一些变是，子句中变是的数目必须等于声明游标时SELECT 子句中列的数目。var_name是存放数据的变量名。

（4）关闭游标

游标使用完以后，要及时关闭。关闭游标使用CLOSE语句，格式为： CLOSE cursor_name

语句参数的含义与OPEN语句中相同.

四、调用存储过程

存储过程创建完后，可以在程序、触发器或者存储过程中被调用，但是都必须使用到CALL语句.

语法格式： CALL sp_name([parameter[ ... ]])

说明： sp_name为存储过程的名称，如果要调用某个特定数据库的存储过程，则需要在前面加上该数据库的名称。parameter为调用该存储过程使用的参数，这条语句中的参数个数必须总是等于存储过程的参数个数。

五、删除存储过程

存储过程创建后需要删除时使用DROP PROCEDURE语句。在此之前，必须确认该存储过程没有任何依赖关系，否则会导致其他与之关联的存储过程无法运行。

语法格式： DROP PROCEDURE [ IF EXISTS ] sp_name;

说明： sp_name是要删除的存储过程的名称。IF EXISTS子句是MySQL的扩展，如果程序或函数不存在，它防止发生错误。

第二节存储函数

一、创建存储函数

创建存储函数使用CREATE FUNCTION 语句。要查看数据库中有哪些存储函数，可以例用SHOW FUNCTION STATUS命令。

CREATE FUNCTION语法格式：

CREATE FUNCTION sp_name ([ func_parameter[ , ... ]])

RETURNS type

[ characteristic ... ] routine_body

说明：存储函数的定义格式和存储过程相差不大。

二、调用存储函数

存储函数创建完后，就如同系统提供的内置函数(如VERSION()), 所以调用存储函数的方法也差不多，都是使用SELECT关键字。

语法格式为： SELECT sp_name ([func_parameter[ , ... ]])

三、删除存储函数

删除存储函数的方法与删除存储过程的方法基本一样，使用DROP FUNCTION语句。

语法格式为: DROP FUNCTION [ IF EXISTS ] sp_name;

第六章数据库安全与保护

第一节数据库完整性

一、完整性约束条件的作用对象

列级约束

列级约束主要指对列的类型、取值范围、精度等的约束。
元组约束

元组约束指元组中各个字段之间的相互约束，例如某个活动的开始日期小于结束日期。
表级约束

表级约束指若干元组之间、关系之间的联系的约束。

二、定义与实现完整性约束

实体完整性：保证记录不重复，其中至少有一列值不重复，用PRIMARY KEY添加约束

如：列级约束StuNo CHAR(10) PRIMARY KEY

表级约束 CREATE TABLE db_student ( StuNo CHAR(10), StuName VARCHAR(20) NOT NULL PRIMARY KEY(StuNo)) ENGINE=InnoDB;

候选键约束用关键字UNIQUE进行约束。

MySQL中候选键与主键之间存在以下几点区别：

（1）一个表中只能创建一个主键，但可以定义若干个候选键。

（2）定义主键约束时，系统会自动产生PRIMARY KKEY索引，而定义候选键约束时，系统自动产生UNIQUE索引。
参照完整性：主要指两个表之间的关系，比如课程表开设依赖于教师表，没有教师，就不应该有教师的课，保证数据的正确性。

语法格式如下：REFERENCES tbl_name [ (index_col_name, ... ) ] 放于最后.
自定义完整性

非空约束

在MySQL中，非空约束可以通过在CREATE TABLE或ALTER TABLE语句中的某个列定义后面，加上关键字"NOT NULL" 作为限定词。

用户自已定义的约束，比如分数0到100分，不是这个范围就报错。

语法格式如下： CHECK(expr)

三、命名完整性约束

语法格式为： CONSTRAINT [ symbol ]

四、更新完整性约束

1.删除约束

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>

ALTER TABLE <表名> DROP PRIMARY KEY

ALTER TABLE <表名> DROP {约束名| 候选键字段名}

2. 添加约束

ALTER TABLE <表名> ADD [ CONSTRAINT <约束名> ] PRIMARY KEY (主键字段);

ALTER TABLE <表名> ADD [ CONSTRAINT <约束名> ] FOREIGN KEY (外键字段名) REFERNCES 被参照表 (主键字段名)

ALTER TABLE <表名> ADD [CONSTRAINT <约束名> ] UNIQUE KEY (主键字段)

第二节触发器

触发器是保护表数据的数据库对象，当指定的表发生INSERT(插入数据)， UPDATE(修改数据)，DELETE(删除数据)时候被触发，进行相应的动作.

一、创建触发器语法格式： CREATE TRIGGER trigger name trigger_time trigger event ON tbl_name FOR EACH ROW trigger_body

二、删除触发器语法格式：DROP TRIGGER [ IF EXIST] [ schema_name. ] trigger_name

三、使用触发器

INSERT、UPDATE、DELETE都会触发。

第三节安全性与访问控制

一、用户帐号管理

1.创建用户

语法格式： CREATE USER user [ IDENTIFIED BY [ PASSWORD ] ' password ' ] [, user [ IDENTIFIED BY [ PASSWORD ] 'password' ] ] ...

其中， user的格式为:

'user_name'@'host name'

格式说明：

使用自选的IDENTIFIED BY子句，可以为账户给定一个密码。特别是要在纯文本中指定密码，需忽略PASSWORD关键词。如果不想以明文发送密码，而且知道PASSWORD()函数返回给密码的混编值，则可以指定该混编值，但要加关键字PASSWORD。

查看用户信息

SELECT user, passwrod from mysql.user;

2.删除用户

语法格式： DROP USER user [, user_name] ...

DROP USER 语句用于删除一个或多个MySQL帐户，并取消其权限，要使用DROP USER, 必须拥有mysql数据库的全局CREATE USER权限或DELETE权限。

3.修改用户账户

语法格式： RENAME USER old_user TO new_user, [ , old_user TO new_user ] ...

RENAME USER 语句用于对原有MySQL帐户进行重命名。要使用RENAME USER, 必须拥有全局CREATE USER权限或MySQL数据库UPDATE权限。如果旧帐户不存在或者新帐户已存在，则会出现错误。

4.修改用户口令

语法格式： SET PASSWORD [ FOR user ] = PASSWORD('newpassword')

说明：如果不加FOR user, 表示修改当前用户的密码，加了FOR user则是修改当前主机上的特定用户的密码，user为用户名。user的值必须以 'user_name'@'host name'的格式给定。

二、帐户权限管理

1.权限的授予

新的SQL用户不允许访问属于其他SQL用户的表，也不能立即创建自己的表，它必须被授权。可以授予的权限有以下几组。

（1）列权限：和表中的一个具体列相关。例如，使用UPDATE语句更新表XS学号列的值的权限。

（2）表权限：和一个具体表中的所有数据相关。例如，使用SELECT语句查询表XS的所有数据的权限。

（3）数据库权限：和一个具体的数据库的所有表相关。例如，在已有的XSCJ数据库中创建新表的权限。

（4）用户权限：和MySQL所有的数据库相关。例如，删除已有的数据库或者创建一个新的数据库的权限。

给某用户授予权限可以使用GRANT语句。使用SHOW GRANTS语句可以查看当前帐户拥有什么权限。

GRANT语法格式:

GRANT priv_type [ ( column_list ) ] [ , priv_type [ ( column_list ) ]] ... ON [ object_type ] { tbl_name | * | * . * | db_name.*} TO user [IDENTIFIED BY [ PASSWORD ]] 'password' [, user [ IDENTIFIED BY [PASSWORD] 'password']] ... [ WITH with_option [ with_option] ... ]

priv_type为权限的名称，如SELECT、UPDATE等，给不同的对象授予权限priv_type的值也不相同。TO子句用来设定用户的密码。ON关键字后面给出的是要授予权限的数据库或表名。

授予表权限和列权限

授予表权限时，priv_type可以是以下值：

①SELECT：给予用户使用SELECT语句访问特定的表的权力。用户也可以在一个视图公式中包含表。然而，用户必须对视图公式中指定的每个表(或视图)都有SELECT权限。

②INSERT：给予用户使用INSERT语句向一个特定表中添加行的权力。

③DELETE: 给予用户使用DELETE语句向一个特定表中删除行的权力。

④UPDATE:给予用户使用UPDATE语句修改特定表中值的权力。

⑤REFERENCES:给予用户创建一个外键来参照特定的表的权力。

⑥CREATE：给予用户使用特定的名字创建一个表的权力。

⑦ALTER:给予用户使用ALTER TABLE语句修改表的权力。

⑧INDEX：给予用户在表上定义索引的权力。

⑨DROP：给予用户删除表的权力。

⑩ALL或ALL PRIVILEGES：表示所有权限名。

在授予表权限时，ON关键字后面跟tbl_name, tbl_name为表名或视图名。

授予列权限：

只能赋予SELECT、INSERT和UPDATE, 同时权限的后面需加上列名列表column_list.

授予数据库权限：

表权限适用于一个特定的表。MySQL还支持针对整个数据库的权限。例如，在一个特定的数据库创建表和视图的权限。

授予数据库权限时，priv_type可以是以下值：

①SELECT：给予用户使用SELECT语句访问特定数据库中所有表和视图的权力。

②INSERT: 给予用户使用INSERT语句向特定数据库中所有表添加行的权力。

③DELETE: 给予用户使用DELETE语句删除特定数据库中所有表的行的权力。

④UPDATE：给予用户使用UPDATE语句更新特定数据库中所有表的值的权力。

⑤REFERENCES：给予用户创建指向特定的数据库中的表外键的权力。

⑥CREATE ROUTINE：给予用户为特定的数据库创建存储过程和存储函数等权力。

⑦ALTER ROOUTINE: 给予用户更新和删除数据库已有的存储过程和存储函数等权力。

⑧EXECUTE ROUTINE: 给予用户调用特定数据库已有的存储过程和存储函数的权力。

⑨LOCK TABLES: 给予用户锁定特定数据库的已有表的权力。

⑩ALL或ALL PRIVILEGES: 表示以上所有权限名。

在GRANT语法格式中，授予数据库权限时ON关键字后面跟"*"和"db_name.*"。

"*"表示当前数据库中的所有表。

"db_name.*" 表示某个数据库中的所有表。

2.权限转移与限制

GRANT语句的最后可以使用WITH子句

第四节事务与并发控制

一、事务的概念

事务(Transaction)是用户定义的一个数据库操作序列，这些操作要么全做，要么全不做，是一个不可分割的工作单位。

事务和程序是两个概念。

在关系数据库中，一个事务可以是一条SQL语句，一组SQL语句或整个程序。

一个应用程序通常包含多个事务。

事务是恢复和并发控制的基本单位。

二、事务的特征

事务的ACID特性：

原子性（Atomicity）: 一组更新操作是原子不可分的。

一致性（Consistency）：事务必须满足数据库的完整性约束，数据库由一个一致性状态转变到另一个一致性状态。

隔离性（Isolation）：隔离性要求事务是彼此独立的、隔离的。

持续性（Durability）：持续性也称为永久性(Permanence), 是指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。

三、并发操作问题

1.丢失更新

设有两个事务T1和T2，当它们同时读入同一数据并加以修改时，事务T2的提交结果会破坏事务T1提交的结果，由此导致事务T1的修改被丢失。

2.不可重复读

设有两个事务T1和T2，不可重复读是指事务T1读取数据后，事务T2执行更新操作，使事务T1无法再现前一次读取结果。

3.读”脏“数据

设有两个事务T1和T2，读”脏“数据是指，事务T1修改某一数据，并将其写回磁盘，事务T2读取同一数据后，事务T1由于某种原因被撤销，这时事务T1已修改过的数据恢复原值，事务T2读到的数据就与数据库的数据不一致，则事务T2读到的数据就为”脏“数据，即不正确的数据。

四、封锁

封锁是最常用的并发控制技术，它的基本思想是：需要时，事务通过向系统请求对它所希望的数据对象(如数据库中的记录)加锁，以确保它不被非预期改变。

1.锁

一个锁实质上就是允许或组止一个事务对一个数据对象的存取特权。

基本的封锁类型有两种：排他锁(Exclusive Lock, X锁) 和共享锁(SharedLock, S锁)。

2.用封锁进行并发控制

（1）若事务T对数据D加了X锁，则所有别的事务对数据D的锁请求都必须等待直到事务T释放锁。

（2）若事务T对数据D加了S锁，则别的事务还可对数据D请求S锁，而对数据D的X锁请求必须等待直到事务T释放锁。

（3）事务执行数据库操作时都要先请求相应的锁，即对读请求锁S锁，对更新（插入、删除、修改）请求 X锁，这个过程一般是由DBMS有执行操作时自动隐含地进行。

（4）事务一直占有获得的锁直到结束(COMMIT或ROLLBACK)时释放。

3.封锁的粒度

通常以粒度来描述封锁的数据单元的大小。

DBMS可以决定不同粒度的锁。由最底层的数据元素到最高层的整个数据库，粒度越细，并发性越大，但软件复杂性和系统开锁也就越大。

4.封锁的级别

(1)0级封锁

封锁的事务不重写其他非0级封锁事务的未提交的更新数据。这种状态实际上实用价值不大。

(2)1级封锁

被封锁的事务不允许重写未提交的更新数据。这防止了丢失更新的发生。

(3)2级封锁

被封锁的事务即不重写也不读未提交的更新数据。这除了1级封锁的交果外还防止了读脏数据。

(4)3级封锁

被封锁的事务不读未提交的更新数据，不写任何(包括读操作的)未提交数据。

5.活锁与死锁

封锁带来的一个重要问题是可能引起”活锁“与”死锁“。

活锁：级别低的事务无法执行。

避免方法：采用先来先服务的策略。

当多个事务请求封锁同一数据对象时，按请求封锁的先后次序对这些事务排队。该数据对象上的锁一旦释放，首先批准申请队列中第一个事务获得锁。

死锁：两上以上事务循环等待被同组中另一事务锁住的数据单元的情形，称为”死锁“。

（1）一次性请求。

（2）锁请求排序

（3）序列化处理

（4）资源剥夺

对待死锁的别一种办法是不去防止，而让其发生并随时进行检测，一旦检测到系统已发生了死锁再进行解除处理。

6.可串行性

一组事务的一个调度就是它们的基本操作的一种排序。通常，在数据库系统中，可串行性就是并发执行的正确性准则，即当且仅当一组事务的并发执行调度是可串行化的，才认为它们是正确的。

7.两段封锁法

事务划分成如下两个阶段：

（1）发展(Growing)或加锁阶段

在此段期间，对任一数据对象进行任何操作之前，事务都要获得对该对象的一个相应的锁。

（2）收缩（Shrinking）或释放阶段

一旦事务释放了一个锁，则标明它已进入了此阶段，此后它就不能再请求任何别外的锁。

定理6.1：遵循两段锁协议的事务的任何并发调度都是可串行化的。

第五节备份与恢复

备份与恢复

数据库中的数据丢失或被破坏可能是由于以下原因：

1.计算机硬件故障。由于使用不当或产品质量等原因，计算机硬件可能会出现故障，不能使用。如硬盘损坏会使得存储于其上的数据丢失。

2.软件故障。由于软件设计上的失误或用户使用的不当，软件系统可能会误操作数据引起数据破坏。

3.病毒。破坏性病毒会破坏系统软件，硬件各数据。

4.误操伯。如果用户误使用了诸如DELETE、UPDATE等命令而引起数据丢失或破坏。

5.自然灾害。如火灾、洪水或地震等，它们会造成极大的破坏，会毁坏计算机系统及其数据。

6.盗窃。一些重要数据可以会遭窃。

一、使用SELECT INTO ... OUTFILE语句备分数据

用户可以使用SELECT INTO ... OUTFILE 语句把表数据导出到一个文本文件中，并用LOAD DATA ... INFILE 语句恢复数据。 * 但是这种方法只能导出或导入数据的内容，不包括表的结构，如果表的结构文件损坏，则必须先恢复原来的表的结构 *

SELECT INTO ... OUTFILE 格式

SELECT * INTO OUTFILE 'file_name' export_options | DUMPFILE 'file_name'

其中，export_options为:

[ FIELDS [ TERMINATED BY 'string' ] [ [ OPTIONALLY ] ENCLOSED BY 'char' ] [ ESCAPED BY 'char' ]

[ LINES TERMINATED BY ' string' ]

说明：

这个语句的作用是将表中SELECT语句选中的行写入到一个文件中，file_name是文件的名称。文件默认在服务器主机上创建，并且文件名不能是已经存在的(这可能将原文件覆盖)。如果要将该文件写入到一个特定的位置，则要在文件名前加上具体的路径。在文件中，数据行以一定的形式存入，空值用"\N"表示。

使用OUTFILE时，可以在export_options中加入以下两个自选的子句，它们的作用是决定数据行在文件中存放的格式：

FIELDS子句：在FIELDS子句中有三个亚子句，TERMINATED BY、[OPTIONALLY] ENCLOSED BY和ESCAPED BY.如果指定了FIELES子句，则这三个亚子句中至少要指定一个。

（1）TERMINATED BY用来指定字段值之间的符号，例如， "TERMINATED BY ','"指定了逗号作为两个字段值之间的标志。

（2）ENCLOSED BY子句用来指定包裹文件中字符值的符号，例如， "ENCLOSED BY '"' "表示文件中字符值放在双引号之间，若加上关键字OPTIONALLY则表示所有的值都放在双引号之间。

（3）ESCAPED BY子句用来指定转义字符,例如， "ESCAPED BY '*' " 将 "*"指定为转义字符，取代"", 如空格将表示为"*N".

LINES子句：在：LINES子句中使用TERMINATED BY指定一行结束的标志，如“LINES TERMINATED BY '?'” 表赤一行以"?" 作这结束标志。

如果FIELDS和LINES子句都不指定，则默认声明以下子句： FIELDS TERMINATED BY "\t" ENCLOSED BY '' ESCAPED BY '\' LINES TERMINATED BY '\n'

如果使用DUMPFILE而不是使用OUTFILE，导出的文件里，所有的行都彼此紧挨着放置，值和行之间没有任何标记，成了一个长长的值。

二、使用LOAD DATA ... INFILE 语句恢复数据

LOAD DATA ... INFILE格式：

LOAD DATA [ LOW_PRIORITY | CONCURRENT ] [ LOCAL ] INFILE ' file_name.txt '

[ REPLACE | IGNORE ]

INTO TABLE tbl_name

[ FIELDS

[ TERMINATED BY 'string' ]

[ [OPTIONALLY] ENCLOSED BY 'char']

[ESCAPED BY 'char']

]

[ LINES

[STARTING BY 'string']

[TERMINATED BY 'string']

]

[IGNORE number LINES]

[(col_name_or_user_var, ...)]

[SET col_name = expr, ...]

说明：

LOW_PRIORITY | CONCURRENT: 若指定

LOW_PRIORITY, 则延迟语句的执行。若指定

CONCURRENT, 则当LOAD DATA正在执行的时候，其他线程可以同时使用该表的数据。

LOCAL: 若指定了LOCAL, 则文件会被客户主机上的客户端读取，并被发送到服务器，文件会被给予一个完整的路径名称，以指定确切的位置。

tbl_name: 需要导入数据的表名，该表在数据库中必须存在，表结构必须与导入文件的数据行一致。

REPLACE | IGNORE：如果指定了REPLACE, 则当文件中出现与原有行相同的唯一关键字值时，输入行会替换原有行。如果指定了IGNORE，则把与原有行有相同的唯一关键字值的输入行跳过。

FIELDS子句：此处的FIELDES子句和SELECT DATA ... OUTFILE语句中类似。用于判断字段之间和数据行之间的符号。

LINES子句：TERMINATED BY亚子句用来指定一行结束的标志。STARTING BY亚子句则指定一个前缀，导入数据行时，忽略行中的该前缀和前缀之前和内容。如果某行不包括该前缀，则整个行被跳过。

IGNORE number LINES: 这个选项可以用于忽略文件的前几行。例如, 可以使用IGNORE 1 LINES来跳过第一行。

col_name_or_user_var: 如果需要载入一个表的部分列或文件中字段值顺序与表中列的顺序不同就必须指定一个列清单，其中可以包含列名或用户变量。

load data infile 'c:/1.txt' into table t_ce_tech fields terminated by ',' optionally enclosed by ‘“’ lines terminated by '?'

第七章数据库应用设计与开发实例

第一节需求描述与分析

一、功能性需求

1.管理员后台模块（1）学生信息管理（2）教师信息管理（3）课程信息管理（4）班级信息管理

2.学生使用模块（1）查询课程（2）浏览所选课程（3）查询成绩

3.教师使用模块（1）我的课程（2）登分

二、非功能性需求

质量需求名称质量需求的详细要求
可靠性不在选课期间，系统每个月最多出现一次由外界因素造成的系统故障，而在选课期间，除非遇到特殊情况，否则要保证正常使用。
正确性需要保证数据库中的数据正确和系统各个功能模块的业务逻辑正确
兼容性可以在与IE内核兼容的任一主流浏览器上运行。
健壮性要经常进行健壮性测试，不断加强对非格式化操作的应变能力

第二节系统设计

一、功能模块设计

二、数据库设计

1.确定实体

2.局部信息结构

3.全司信息结构

4.逻辑结构与规范化设计

第三节系统实现

一、数据库的实现

第四节系统测试与维护

完成系统的实现工作之后，在正式交付用户使用之前，需要对所开发的系统进行必要的测试，验证其是否满足用户的功能需求，并根据测试的结果，以及用户的反馈意见，对该系统进行进一步的修改，完善和维护工作。例如，在本系统中，可以分别针对不两只的功能模块制定相应的测试方案。

第八章数据管理技术的发展

第一节数据库技术发展概述

一、第二代数据库系统

代表: 1.IMS 层次模型 IBM1969 2. CODASYL，网状模型， DBTG,1970

特点：

支持三级模式
用存取路径表表示联系
独立的DDL
导航的DML

二、第二代数据库系统

关系数据库系统

E.F.Codd 1970

系统原型： IBM System R Berkeley INGRES 1980s后，新开发的系统都是关系型的。

三、新一代数据库系统

OODB 第三代的代表？没有被市场普遍接受

ORDB, 新一代将是多种模型并存

“第三代”的基本特征：

1.应支持数据管理，对象管理和知识管理

2.保持或继承RDB的技术;

3.开放性.

应用：数据仓库、工程数据库、统计数据库、空间数据库、科学数据库等多种数据库。

第二节数据仓库与数据挖掘

一、从数据库到数据仓库

数据仓库主要特征：

1.面向主题

2.集成性

3.数据的非易失性

4.数据的时变性

数据仓库具有三个常用的重要概念.即粒度、分割和

1.粒度

粒度问题是设计数据仓库的一个最重要的方面。粒度是指数据仓库的数据单位中保存数据的细化或综合程度的级别。

2.分割

分割是将数据分散到各自的物理单元中，以便能分别处理，以提高数据处理的效率。

3.维

维是人们观察数据的特定角度，是考虑问题时的一类属性。

二、数据挖掘技术

数据挖掘(Data Mining)是从大量的、不完全的、有噪声、模糊的、随机的实际应用数据中发现并提取隐藏在其中的、人们事先不知道的、但有是潜在有用的信息和知识的一种技术。它又被称为数据库中的知识发现(Knowledge Discovery in DataBase, KDD), 其与数据库、数理统计、机器学习、模式识别、模糊数学等诸多技术相关。

数据挖掘具备下列几种功能：

1.概念描述

2.关联分析

3.分类与预测

4.聚类

5.孤立点检测

6.趋势和演变分析