数据库基本概念

关注校招、实习信息

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

存储过程和函数的区别

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

视图的作用：

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