从0学习MySQL系列（三）概念篇

概要

在篇文章中提过：概念：数据库管理系统(Database Management System)一些语法的汇总点。

增删改查是基本的操作语法。对于一些概念，主键，外键，表，事务，锁，存储过程，视图，触发器，分区等概念，请参考上面的文章。概念是晦涩难看，但是有时候又忍不住多看几眼。

内容：主键，外键，视图，存储过程，触发器，事务，锁。

主外键

使用表：Department，Employee

主键是唯一标识一张表的数据，不允许为NULL，不允许重复，保证了数据的完整性。

外键是另一张表的主键，外键可以为NULL，可以有重复，与其他表建立联系。

图中有两个表，分别是：Department，Employee表，其中，DeptID、EmpID分别是这两个表的主键，DeptID是Employee表的外键。

代码：

create database DB_Company;
use DB_Company;

-- 创建Department 部门表
create table Department(
    DeptID int primary key auto_increment not null,
    DeptName varchar(20) not null,
    Description varchar(100)
)

-- 创建员工表
create table Employee (
    EmpID int primary key auto_increment not null,
    EmpName varchar(10) not null,
    Gender varchar(4) not null,
    Age int(2) not null,
    foreign key(EmpID) references Department(DeptID)
)         

视图

创建视图，视图是一种虚拟存在的表，对于使用视图的用户来说是透明的。视图具有简单、安全、数据独立等优点。

创建视图employee_view的语法：

create or replace view employee_view as select * from Employee

MySQL视图的定义有一些限制。from后面不能包含子查询。如果输入是从别的数据库迁移过来的，可以先将子查询的内容定义为视图。

另外，包含聚合函数（SUM(),MIN(),MAX(),COUNT()）、DROUP BY、HAVING、UNION等的视图不可更新。

包含常量视图，Select中包含子查询的，JION连接查询的视图都是不可更新的。

drop view employee_view; -- 删除视图

查看视图，从MySQL5.1版本以上，没有show views 这个查看视图的命令了，使用show table status

show table status like "employee_view" 

-- 如果要查询employee_view视图的定义
show create view employee_view 

存储过程

什么是存储过程呢？什么是函数呢？存储过程和函数，其实是事先经过编译的、并存储在数据库中的一段SQL语句的集合。

函数嘛，要有返回值，而存储过程不需要返回值。存储过程的参数可以使用IN,OUT,INOUT,但是函数只能用IN类型的。

存储过程中可以执行提交，或者是回滚。提交即确认之前的修改，回滚即放弃之前的修改。存储过程不允许执行LOAD DATA INFILTE 语句，此外，存储过程可以调用其他的存储过程。

create procedure employee_in_stock(in EmpID int, in EmpName varchar,
 in Gender varchar, in Age int)
Reads SQL Data
BEGIN
     ......
     ......
     .......
     
END

直接执行SQL和调用存储过程的效果是一样的，使用存储过程的优点在于处理逻辑封装在数据库中，调用的时候不需要知道真正去了解中间的处理逻辑。即使中间的逻辑发生了变化，修改即可，对程序没有预知内的影响。其余知识点不赘述。（包括变量的使用、流程的控制、时间调度器、存储过程的删除等）

事务

事务具有四种性质：

原子性：一组事务，要么成功；要么撤回。

稳定性 ： 有非法数据（外键约束之类），事务撤回。

隔离性：事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。

可靠性：软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit选项 决定什么时候吧事务保存到日志里。

索引

MySQL索引。首先，MySQL的索引是在存储引擎层面实现的，并不是在服务器层面实现的。所以每一种的存储引擎的索引都是不是完全相同的。MySQL目前提供了一下4种索引。

B-Tree索引：最常见的搜索引擎。大部分的搜索搜索引擎是支持B-Tree。

Hash索引：这个搜索引擎只有Memory引擎支持。

R-Tree索引：又称为空间索引。空间索引要是MyISAM的一个特殊搜索引擎。

Full-text：全文索引。全文索引也是MyISAM的一个特殊的索引类型。InnoDB 从MySQL 5.6版本开始提供了对全文的支持。

三种引擎对四种索引的支持情况