数据库基本概念

心已赠人 2022-08-04 00:59 213阅读 0赞

# 关注校招、实习信息 #

![这里写图片描述][20160117165229123]

> 引言：由于前段时间在实习，好长一段时间没更新博客了。

### 存储过程和函数的区别 ###

> 存储过程是用户定义的完成特定功能的一系列SQL语句的集合，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。 涉及特定表或其它对象的任务，用户可以调用存储过程，而函数通常是数据库已定义的方法，它接收参数并返回某种类型的值并且不涉及特定用户表。基本语法如下:

DELIMITER //
    DROP PROCEDURE IF EXISTS usp_demo;
    CREATE PROCEDURE usp_demo(IN param1 INT,...)
    BEGIN
        -- 完成某种功能的 SQL 语句集
    END //
    DELIMITER ;

> 存储过程的参数有三种类型:  
> 1. IN: 输入参数. 在调用存储过程时指定, 默认未指定类型时则是此类型。  
> 2. OUT: 输出参数. 在存储过程里可以被改变, 并且可返回。  
> 3. INOUT: 输入输出参数. IN 和 OUT 结合。

*  存储过程是一个独立的执行单元，而函数可作为查询语句的一部分；
 *  函数只能返回一个值或对象，而存储过程可以返回多个参数；

### 内部连接与外部连接的区别 ###

> **内部连接：**只有两个表相匹配的行才能出现在结果集中，相当于在其中一个结果表中删除与其他连接表不匹配的所有行，所有内部连接可能会造成信息丢失；  
> **外部连接：**外部连接不仅包含符合连接条件的行，而且还包括左表（左外部连接）、右表（右外部连接）或两个连接表（全外部连接）中所有数据行；

### 事务 ###

> **事务属性：**事务是作为一个逻辑单元执行的一系列操作，一个逻辑工作单元必须有四个属性：原子性、一致性、隔离性、持久性。

*  原子性：事务是一个不可分割的整体，所有操作要么全执行、要么全不执行；
 *  一致性：一个事务执行之前与执行之后数据库数据必须保持一致性状态；
 *  隔离性：当两个或多个事务并发执行时，将一个事务内部操作与事务的操作隔离，不被其他正在执行的事务看见。例如：对于任何一对事务T1、T2，对于T1而言，T2要么在T1执行完毕之后执行、要么在T1执行之前执行；
 *  持久性：事务完成之后，数据库的数据是永久保存的，可以通过备份与恢复保证；

### 主键与外键 ###

> 主键是能确定数据表中一条记录的唯一标识，外键是表示两个关系之间的联系，用于与另一张表的关联，是能确定另一张表记录的字段；

### 数据库范式 ###

> 设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。
> 
> 目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。  
> 范式的包含关系：一个数据库设计如果符合第二范式，一定也符合第一范式。如果符合第三范式，一定也符合第二范式 …

*  第一范式：要求属性不可再分割，即不存在重复的列。每一列都是不可分割的基本数据项，同一列中不能存在多个值；例如：一个表中的某条记录的联系方式包含的固定电话、移动电话，则必须把它分为两列：固定电话 和 移动电话；
 *  第二范式：是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性，更通俗说有主键ID。
 *  第三范式：要求消除传递依赖，是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余；
 *  第四范式：要求把同一表内的多对多关系删除；

### 索引 ###

> 索引是一种提高数据库查询速度的机制，它是在数据库的表或视图上按照某个关键字段的值，升序或降序排序创建的对象。用户查询索引字段时，不需要扫描整个表就可以快速找到所需的数据。
> 
> SQL索引类型：实际上是一组指向表中数据的指针，索引的排列顺序就是这组指针的顺序。

*  ***唯一索引***：对索引添加唯一性的约束，当添加或修改索引列中数据时，不允许出现重复值。
 *  ***主键索引***：把主键列定义为索引，主键具有唯一性，所以主键索引是唯一索引的一种特殊形式。
 *  ***聚集索引***：表的物理存储顺序与指针(即逻辑)顺序相同。
 *  ***非聚集索引***：物理与逻辑顺序不同。

> 创建索引时，系统分配一个索引页，在表中插入一行数据，同时也向该索引页插入一行索引记录，因此，创建索引和维护索引需要时间与空间，并不是越多索引越好。

### 游标 ###

> 游标是一种从多条记录的结果集中每次提取一条记录的机制，然后逐条进行数据处理。游标总是与SQL查询语句相关联，因为游标由结果集（零条、一条或多条记录）和结果集中所指向特定的游标位置组成，使用游标时，使用游标的顺序： *定义游标*、*打开游标*、*读取数据*、*关闭游标*、*删除游标*。

//定义游标
    DECLARE <cursor_name:游标名> CURSOR FOR <SELECT statement:select操作语句>; 
    //OPEN 打开游标
    OPEN cursor_name; 
    //FETCH 获取游标当前指针的记录，并传给指定变量列表,注意变量数必须与MySQL游标返回的字段数一致,要获得多行数据，使用循环语句去执行 FETCH
    FETCH cursor_name INTO variable list; 
    //CLOSE关闭游标
    CLOSE cursor_name ;
    //删除游标
    DEALLOCATE  cursor_name;

> 游标允许应用程序对查询语句`select`返回的结果集中每一行进行相同或不同的操作，而不是一次对整个结果集进行同一个操作，另外提供对基于游标位置的表中的行进行删除和更新的能力。

### 存储引擎 ###

> 存储引擎就是指表的类型。数据库的存储引擎决定了表在计算机中的存储方式。MySQL存储引擎包括处理事务安全表的引擎和处理非事务安全表的引擎，下面注意记录了MyISAM与InnoDB存储引擎：

*  MyISAM：管理非事务表，提供高速存储和查询，以及全文搜索能力。适用于：  
    
    
     *  查询密集型的表：MyISAM存储引擎能够在大量数据中快速筛选出目标数据。
     *  插入密集型的表：MyISAM的并发插入特性允许同时查询和插入数据。例如：MyISAM存储引擎很适合管理邮件或Web服务器日志数据。
 *  InnoDB：用于事务处理应用程序，提供了事务、行级锁机制和外键约束的功能。适用于：  
    
    
     *  更新密集的表：InnoDB存储引擎适合处理多重并发的更新请求。
     *  事务：InnoDB存储引擎是支持事务标准。
     *  自动故障恢复：InnoDB表能够自动从故障中恢复。
     *  外键约束：MySQL支持外键的存储引擎只有InnoDB。
     *  支持自动增加列AUTO\_INCREMENT属性。

### 触发器 ###

> 触发器是与表有关的命名数据库对象，当表上出现特定事件时，将激活该触发器。主要用于监视表的update、delete、insert操作。

触发器的基本要点：

1.  事件：insert、update、delete
2.  触发时间：before、after
3.  在触发程序中可以使用new和old来访问更改前后的数据，old是只读属性。

触发器基本语法：

delimiter $
    create trigger 《触发器名称》 
    《触发时间》(after/before) 
    《触发事件》(insert/update/delete) 
    on 《表名》
    for each row //表示行级触发器，Mysql目前仅支持行级触发器
    begin
    //sql语句
    end;
    delimiter ;//将结尾符改回；

例如：该触发器的功能：当表`order`执行`insert`之后，更新表`product`相应的产品数量`ProductNum`；

delimiter $    /*mysql语句的结尾换成以`$`结束*/
    create trigger tri_after_insert    
    after insert on order
    for each row 
    begin
    update product set ProductNum=ProductNum-3 where ProductID =  new.ProductID ; 
    end;
    delimiter ;    /*mysql语句的结尾换回分号*/

### 视图 ###

> 视图是基于 SQL 语句的结果集的可视化的表或其他视图中导出的表，其结构和数据是建立在对表的查询基础上的。和表一样，视图也是包括几个被定义的数据列和多个数据行，但就本质而言这些数据列和数据行来源于其所引用的表，视图是一个虚表。视图中的字段是来自一个或多个数据库中的基本表中的字段，但是必须注意，在数据库中，存放的只是视图的定义，不存放视图所包含的数据项，这些数据项仍然存放在原来的基本表结构中。

视图的作用：

*  提高数据的安全性：在设计数据库应用系统时，对不同的用户定义不同的视图，对某些用户隐藏一些重要的数据。
 *  简化数据查询语句：视图机制使用户可以将注意力集中在所关心地数据上，不必关注所有数据。

[20160117165229123]: /images/20220731/d776a663fb294fc0b5655e0f97aa7c66.png