数据库系统原理

数据编程

存储过程

概念：是一组为了完成某项特定功能的SQL语句集其实质就是一段存储在数据库中的代码它可以由声明式的sql语句和过程式sql语句组成优点：可增强SQL语言的功能和灵活性良好的封装性高性能可减少网络流量可作为一种安全机制来确保数据库的安全性和数据的完整性

创建存储过程例子：DELIMITER $$font color=\"#31a8e0\

存储函数

概念：是由SQL语句和过程式语句组成的代码片段。

存储函数和存储函数区别存储函数：不能拥有输出参数必须包含一条RETURN语句可以直接使用select调用存储函数，不需要CALL语句存储过程：可以拥有输出参数不允许包含RETURN语句需要CALL语句调用存储过程

数据库安全与保护

数据库完整性

数据库完整性是指数据库中数据的正确性和相容性。如性别：只能是男或女   年龄：为0-200岁      身份证：422422199011240088  格式固定

触发器

触发器是用户定义在关系表上的一类由事件驱动的数据库对象，也是一种保证数据完整性的方法。

创建CREATE TRIGGER trigger_name trigger_time trigger_eventON tbl_name FOR EACH ROW trigger_body删除DROP TRIGGER[IF EXISTS][schema_name.]trigger_name使用触发器1、 INSERT 触发器2、DELETE 触发器3、UPDATE 触发器

安全性与访问控制

用户账号管理

账户权限管理

事务与并发控制

概念所谓事务是用户定义的一个数据操作序列，这些操作可作为一个完整的工作单元，要么全部执行，要么全部不执行，是一个不可分割的工作单位。事务中的操作一般是对数据的更新操作，包括增、删、改。

特征（ACID）原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持续性（Durability）

使用事务以BEGIN TRANSACTION语句开始以COMMIT语句或ROLLBACK语句结束

备份与恢复

会造成数据库运行事务异常中断的因素可能是：（1） 计算机硬件故障（2） 计算机软件故障（3） 病毒（4） 人为误操作（5） 自然灾害（6） 盗窃

语句备份数据SELECT*INTO OUTFILE’file_name’export_options|INTO DUMPFILE’file_name’可选项[FIELDS[TERMINATED BY ‘string’][[OPTIONALLY] ENCLOSED BY’char’][ESCAPED BY’char’]][LINES TERMINATED BY’string’]恢复数据LOAD DATA INFILE ‘file_name.txt’INTO TABLE tbl_name[FIELDS[TERMINATED BY ’string’][OPTIONALLY] ENCLOSED BY ’char’][ESCAPED BY ’char’]][LINES[STARTING BY ’string’][TERMINATED BY ’string’]]

数据库系统原理

概述

数据（Data）：描述事物的符号记录，是指利用物理符号记录下来的可以鉴别的信息。数据是信息存在的一种形式，只有通过解释或处理的数据才能成为有用的信息

数据库（DB）：是指长期存储在计算机中的有组织的、可共享的数据集合数据要按照一定的数据模型组织、描述、和存储，具有较小的冗余度、较高的数据独立性，易于扩展，可共享

数据库管理系统（DBMS）：概念：是专门用于简历和管理数据库的一套软件功能：1、数据定义功能（建表）2、数据操纵（增删改查）3、数据库的运行管理4、数据库的建立和维护5、数据的组织、存储、管理6、网络通信等其他功能

数据库系统的构成：用户  →  应用程序（微信）→  DBMS  → DB  →  数据库管理员（DBA）

发展史

人工管理阶段

数据不保存

应用程序之间数据不能交换，自己管理自己的数据

文件系统阶段

可以保存

优点：物理数据独立性

数据库系统阶段

特点：1、目的：数据集成（把一堆数据集中在一起）2、数据共享性高3、数据冗余小4、数据一致性5、数据独立性高6、实施统一管理与控制主要包括数据的安全性、完整性、并发控制与故障恢复等，即数据保护

数据库系统的结构

数据库系统的结构分类

DBA视角

内部系统结构采用三级模式：→  应用  →  外模式  （子模式或用户模式） （数据视图或用户视图） 如：一小部分领域（用户级）→  模式  （概念模式或逻辑模式）（概念视图）  如：长度5，数据类型（varchar）（数据库核心，也是数据设计的关键）→  内模式  （存储模式）（内部视图或存储视图）   如：存照片，确定怎么存，存成什么样的→  数据库外部系统结构：集中式结构、分布式结构、并行结构

三级模式结构的两层映像与数据独立性映像：就是一种对应规则，它指出映像双方是如何进行转换的模式/内模式映像：保证了数据与程序的物理独立性，模式→内模式的映像（关系），指示内模式是如何转换成模式的外模式/模式映像：保证了数据与程序的逻辑独立性，外模式→模式

用户视角

C/S（客户/服务器结构）：单机、网络表示层→数据层B/S（浏览器/服务器结构）：web表示层→处理层→数据层

数据模型

如何把现实世界虚拟到计算机中？最高层：概念层（数据库设计阶段）中间层：逻辑层最低层：物理层

1、什么是数据模型？模型是现实世界特征的模拟和抽象表达（飞机模型）数据模型是对现实世界数据特征的抽象，描述的是数据的共性内容（表结构）

2、数据的特征静态特征：（不会变）1、数据的基本结构（链表、树）2、数据间的联系3、数据取值范围的约束（如：字段取值范围，性别：男、女）动态特征：对数据进行符合一定规则的操作（增删改）

3、数据模型组成要素1、数据结构：描述系统静态特征（包含数据类型、内容、属性、数据对象之间的联系）2、数据操作：描述系统的动态特征（增删改查）3、数据约束：描述数据结构中数据间的语法和语义关联（数据正确性、有效性等）

4、数据模型的分类→ 现实世界→ 概念模型或信息模型（信息世界）：也称为概念层，是数据抽象级别的最高层，处于数据库设计阶段→ 数据库type（型）：表头value（值）：值

5、什么是概念层数据模型？

6、信息世界涉及的基本概念信息世界涉及的基本概念：实体（如：学生）、属性（年龄）、码或键（特殊的属性，如：ID）、域（值域）、实体型（实体+属性）、实体集（同类型的实体集合）、关系（实体与实体之间的联系）

7、概念模型的表示方法使用ER图进行设计矩形：表示实体椭圆：表示属性菱形：表示联系

8、什么是逻辑层数据模型层次模型：早期使用的一种数据模型，有且仅有一个节点，没有父节点，称作根网状模型：以网状结构表示实体与实体间的关系关系模型（多表关系）：目前常用的一种模型面向对象模型

9、逻辑模式的类型

10、物理层数据模型描述数据在存储介质上的组织结构，是逻辑模型的物理实现，是数据库最底层的抽象，设计目标是提高数据库性能和有效利用存储空间

简答题：简述概念模型、逻辑模型、物理模型之间的关系这三个不同的数据模型之间既相互独立，又存在着关联。从现实世界到概念模型的转换是由数据库设计人员完成的；从概念模型到逻辑模型的转换可以由数据库设计人员完成，也可以用数据库设计工具协助设计人员完成；从逻辑模型到物理模型的转换主要是由数据库管理系统完成的。 

关系数据库

1970年IBM的E.F.Codd提出了关系模型，奠定了关系数据库的理论基础20世纪70年代末关系方法理论研究和软件系统的研制取得了重大突破1981年出现了比较成熟的关系数据库管理技术，证实了关系数据库的优点：高级的非过程语言接口、较好的数据独立性。 20世纪80年代后网状模型和层次模型与底层实现的结合紧密，关系模型具有坚实理论基础，成为主流数据模型。

关系数据模型的组成要素：1、关系数据结构2、关系操作集合3、关系完整性约束

关系数据结构就是表表也称为关系（Relation）有表名：必须唯一列：字段或者属性，数据类型相同、值域、必须唯一行：元组或记录，分量：指行中的一列

关系的三种类型基本关系（基本表）（实际存在的表）查询表（虚表）视图表（虚表）

码或者键属性（或者属性组）的值都能用来唯一标识该关系的元组，则称为该关系的码或者键也是就是表中的主键

超码或者超键在码中去除某个属性，它仍然是这个关系的码属性组中有多余的属性

候选码或候选键在码中不能从中移除任何一个属性，否则它不再是这个关系的码或者键候选码或者候选键是这个关系的最小超码或超键属性组中没有多余的属性

主属性或码属性在数据库中，关系中包含在任何一个候选码中的属性称为主属性或码属性

主码或主键（Primary Key）在若干个候选码中指定一个唯一标识关系的元组简单理解就是从候选码中人为选择的唯一

全码或全键（All Key）所有属性都是这个关系的主码或主键

外码或外键某个属性或属性组不是这个关系的主码或候选码，而是另一个关系的主码多表这样的两种关系称为参照关系/主从关系

域取值范围

关系数据库对关系的限定/要求？1）每一个属性都是不可分解的（不允许表中有表）；2）每一个关系仅仅有一种关系模式；3）每一个关系模式中的属性必须命名，属性名不同；4）同一个关系中不允许出现候选码或候选键值完全相同的元组；5）在关系中元组的顺序（即行序）是无关紧要的，可以任意交换；6）在关系中属性的顺序（即列序）是无关紧要的，可以任意交换。 

关系数据模型

基本的关系操作Query、Insert、Delete、Update

查询（query）

选择投影并差笛卡尔积

衍生：连接除交

关系的完整性约束

数据库的数据完整性是指数据库中的数据正确性、相容性、一致性

分类：1、实体完整性约束：主码的组成不能为空，主属性不能是空值NULL2、参照完整性约束（两个表关联的外键）：外码要么等于主码值，要么等于为空前面两条规则是不变的3、用户定义完整性约束（或域完整性约束）：针对某一应用环境的完整性约束、其他

简单应用：1、执行插入操作的检验需要所有检查检查实体完整性（主码不能为空）→检查参照完整性（检查外码表主码）→检查用户定义完整性约束2、删除只考虑参照完整性约束和用户定义完整性约束3、更新可以理解为先删除后更新

关系数据库的规范化理论

关系模式中可能纯在的冗余和异常问题：数据冗余：会造成以下异常更新异常：插入异常：删除异常：

函数依赖：设R为任一给定关系，如果对于R中属性X的每一个值，R中的属性Y只有唯一值与之对应，则称X函数决定Y或称Y函数依赖于X，记作X→Y。其中X称为决定因素。 反之则没有函数依赖如：知道学号就知道了学生姓名函数依赖分类1、完全函数依赖    就是候选码的关系，不能移除任何一个属性（如 学号，课程，成绩） 必须是学号+课程才能决定成绩     设R为任一给定关系，X、Y为其属性集，若X→Y，且对X中的任何真子集X‘ ，都有X’不能决定 Y，则称Y完全函数依赖于X。2、部分函数依赖   就是超码（如：学号，姓名，性别）学号或者姓名其中之一可以决定性别    设R为任一给定关系，X、Y为其属性集，若X→Y，且对X中的任何真子集X‘ ，都有X’→ Y，则称Y完全函数依赖于X3、传递函数依赖       X、Y、Z   多对一对一设R为任一给定关系，X、Y、Z为其不同属性子集，若X→Y，Y 不能决定 X，Y→Z，则有X→Z，称为Z传递函数依赖于X如：书号，出版社名，出版社地址，   书号可以推出出版社名，但出版社名推不出书号，出版社名可以推出出版社地址，得出书号可以处处出版社地址

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

范式与关系规范化过程

第一范式（1NF）设R为任一给定关系，若果R中每个列与行的交点处的取值都是不可再分的基本元素，则R为第一范式。 

第二范式（2NF）  消除第一范式中部分函数依赖即成为第二范式设R为任一给定关系，若R为1NF，且其所有非主属性都完全函数依赖于候选关键字，则R为第二范式。 

第三范式（2NF） 消除第二范式中传递函数依赖即成为第三范式设R为任一给定关系，若R为2NF，且其每一个非主属性都不传递函数依赖于候选关键字，则R为第三范式。 

第三范式的改进形式 -（巴斯范式） BCNF    消除传递函数依赖设R为任一给定关系，X、Y为其属性集，F为其函数依赖集，若R为3NF，且其F中所有函数依赖X→Y(Y 不属于X)中的X必包含候选关键字，则R为BCNF

数据库设计

数据库的生命周期1、数据库分析与设计阶段：需求分析、概念设计、逻辑设计、物理设计2、数据库实现与操作阶段：实现、操作与监督（类似测试）、修改与调整

数据库设计的目标满足应用功能的需求：存取删改良好的数据库性能：高效、节省空间、共享性、完整性、一致性、安全性高

数据库设计的内容结构设计（静态的）：数据库概念结构设计、逻辑结构设计、物理结构设计行为设计（动态的）：功能设计、事务设计、程序设计

数据库设计的方法直观设计法：最原始的数据库设计方法规范设计法：  1新奥尔良设计方法：需求分析、概念结构设计、逻辑结构设计、物理结构设计  2基于E-R模型的数据库设计方法  3基于第三范式的设计方法，是一类结构化设计方法计算机辅助设计法：辅助软件工程工具

数据库设计的过程需求分析结构设计、行为设计数据库实施 ：加载数据库数据、调试运行应用程序数据库运行与维护

数据库设计的基本步骤

需求分析数据库设计的起点目标：了解与分析用户的信息及应用的处理的要求，并将结果按一定格式整理而形成需求分析报告改分析报告是后续概念设计、逻辑设计、物理设计、数据库建立与维护的依据

确定数据范围：数据库的第一项工作，有效地利用计算机设备及数据库系统的潜在能力，提高数据的应变能力，避免应用过程中对数据库做太多或者太大的修改，延长数据库的生命周期

→分析数据应用过程：了解并分析数据与数据处理间的关系，（1）用到哪些数据；（2）数据使用的顺序；（3）对数据作何处理和处理的策略以及结果；应用过程分析的结果是数据库结构设计的重要依据

→收集与分析数据：数据收集与分析的任务是了解并分析数据的组成格式及操作特征，每个数据元素的语义及关系等，并将它们收集起来整理归档。静态结构 --- 不施加应用操作于其上时数据的原始状况数据分类表：用于数据的总体描述数据元素表：指通常意义下的数据项或属性动态结构 ---将应用操作施加于数据之上后数据的状况任务分类表：一个任务指为完成某一特定处理功能的相对独立的操作序列（如查询）数据特征操作表：用以描述任务和数据之间的关系，它包括不同任务对数据执行不同操作的频率数据约束 ---使用数据时的特殊要求1）数据的安全保密性2）数据的完整性3）响应时间4）数据恢复

→编写需求分析报告1）数据库的应用功能目标2）标明不同用户视图范围（如登录需要的数据）3）应用处理过程需求说明，包括：数据流程图；任务分类表；数据操作特征表；操作过程说明书。4）数据字典，是数据库系统中存储三级结构定义的数据库，通常指的是数据库系统中各类数据详细描述的集合。功能是存储和检索各种数据描述，即元数据。包括数据分类表、数据元素表和各类原始资料。5）数据量6）数据约束

概念结构设计

关系数据库设计方法

概念结构设计方法

E-R图的表示方法1-N    属于N-1    包含M-N   拥有

局部信息结构设计（也就是部分领域）1、确定局部范围：主要依据需求分析报告中标明的用户视图范围来确定2、选择实体：数据分类表是选择实体的直接依据3、选择实体的关键字属性：主码4、确定实体间的联系：数据间的联系必须在概念设计时确定5、确定实体的属性：分为标识属性（主码）和说明属性（非主属性）

全局信息结构设计（也就是整个系统设计）注意联系也有属性，也就是多对多联系的映射表属性

逻辑结构设计方法

1、E-R图转换为关系模型的原则1)一个实体型转换为一个关系模式，实体属性作为关系属性，实体码作为关系码，也就是实体转化为表2)一个一对一联系可以转换为一个独立的关系模式，也可以与任意一端对应的关系模式合并（联系转化为中间表，也可以使用主外键关联）3)一个一对多联系可以转换为一个独立的关系模式，也可以与N端对应的关系模式合并（一对多联系变成中间表，也可以在N端设置外码关联）4)一个多对多联系转换为一个关系模式，与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性（也就是转换成中间表）5)三个或者以上的实体键的一个多元联系可以转换为一个关系模式6)具有相同码的关系模式可合并

2、对关系数据模型进行优化1)确定各属性间的函数依赖关系2)对于各个关系模式之间的数据依赖进行极小化处理，消除冗余的联系3)判断各个关系模式的范式，根据实际需要确定最合适的范式4)按照需求分析阶段得到的处理要求，分析这些模式对于这样的应用环境是否合适，确定是否要对某些模式进行合并或分解5)对关系模式进行必要的分解，提高数据操作的效率和存储空间的利用率

3、设计面向用户的外模式1)可以通过视图机制在设计用户视图时，重新定义某些属性的别名，使其更符合用户的习惯，以方便使用，2)可以对不同级别的用户定义不同的视图，以保证系统的安全性3)简化用户对系统的使用也就是使用视图，视图的属性可以使用别名

4、物理设计方法建立索引1、静态建立索引2、动态建立索引建立聚集（分数据块）把数据放在不同的数据块中

SQL与关系数据库基础操作

SQL概述

什么是SQL？结构化查询语言（Structured Query Language，SQL）是专门用来与数据库通信的语言，它可以帮助用户操作关系数据库。

特点：不是某个特定数据库供应商专有的语言简单易学强大、灵活，可以进行非常复杂和高级的数据库操作

组成：数据查询数据定义数据操纵数据控制

数据定义语言（DDL）CREATEALTERDROP

数据操纵语言（DML）SELECTINSERTUPDATEDELETE

数据控制语言（DCL）GRANT   用于授予权限REVOKE   用于收回权限

MYSQL预备知识

使用基础：关系数据库管理系统（RDBMS）优点：体积小、书速度快、开源、遵循GPLLAMP：Linux  Apache Mysql PHP、PythonWAMP：Windows Apache ...常量：字符串常量  使用单引号  ASCII和Unicode编码数值常量   整数、浮点数十六进制常量  x时间日期常量  使用单引号位字段值  bit布尔值  bool   true、falseNULL值   空值变量：用户变量，   用户定义的   使用@符号定义系统变量，  使用@@符号定义运算符：算术运算符  + - * / %位运算符    & | ^ ~ >> <<比较运算符   = > < >= <= <> != <=>逻辑运算符   NOT(!)   AND(&&)  OR(||)  XOR（逻辑异或）函数：数学函数    ABS()聚合函数    COUNT()   SUM()字符串函数    ASCII()日期函数     NOW()加密函数    ENCODE()控制流程函数    IF()格式化函数   FORMAT()类型转换函数   CAST()系统信息函数  USER()

数据定义

show datatables;   显示数据库show columns from table_name;   显示表结构数据库模式（数据库）定义CREATE DATABASE或CREATE SCHEMA语法：CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name  [DEFAULT]CHARACTER SET[=]charset_name | [DEFAULT]COLLATE[=]collation_name查看数据库  SHOW {DATABASES | SCHEMAS} [LIKE’pattern’ | WHERE expr]选择数据库：USE db_name;修改数据库： ALTER{DATABASE|SCHEMA}[db_name] alter_specification…示例mysql>ALTER DATABASE mysql_test -> DEFAULT CHARACTER SET gb2312 -> DEFAULT COLLATE gb2312_chinese_ci;删除数据库：DROP{DATABASE|SCHEMA}[IF EXISTS]db_name

表定义

更新表ALTER Table  主句ADD[COLUMN] 子句CHANGE[COLUMN]子句         修改表中列的名称或数据类型ALTER[COLUMN]子句          修改或删除表中指定列的默认值MODIFY[COLUMN]子句        只修改指定列的数据类型，不会干涉它的列名。DROP[COLUMN]子句        删除表中多余的列。RENAME[TO]子句           为表重新赋予一个表名  mysql>ALTER TABLE mysql_test.customers            ->RENAME TO mysql_test.backup_customers;ADD INDEX index_name(column_name);       给表添加索引主语句： RENAME TABLE mysql_test.backup_customers TO mysql_test.customers;

删除表DROP [TEMPORARY] TABLE [IF EXISTS]  tbl_name [，tbl_name]…  [RESTRICT | CASCADE]

索引

数据更新

插入数据

删除数据

DELETE FROM tbl_name [WHERE where_condition] [ORDER BY …] [LIMIT row_count]

修改数据

数据查询

SELECT语句

列的选择

内置函数‘

查询条件：子查询分类：表子查询、行子查询、列子查询、标量子查询（一个值）语句： in 、 not in          包含expression IS [NOT] NULL      判断空值EXISTS (subquery)    子查询结果集是否存在，子查询的结果集不为空为true

视图

概念：视图是数据库中的一个对象，它是数据库管理系统提供给用户的以多种角度观察数据库中数据的一种重要机制。视图不是数据库中真实的表，而是一张虚拟表，其自身并不存储数据。

使用视图的优点：集中分散数据、简化查询语句、重用SQL语句、保护数据安全、共享所需数据、更改数据格式

使用INSERT语句通过视图向基本表插入数据insert into  视图名称更新数据 使用 update 删除数据  使用 delete