MySQL基础day03_存储引擎和外键-MySQL 5.6

MySQL基础day03_存储引擎和外键-MySQL 5.6

外键的条件：

1，表的存储引擎为innodb存储引擎

2，表中外键字段的类型要与参考表的字段类型一致

3，外键字段要是索引类型中的一种

MySQL数据库的存储引擎

存储引擎：是mysql结构中的组成部分

mysql体系结构：

由8部分组成：连接池、Sql接口、分析器、优化器、缓存和缓冲、存储引擎、管理工具、物理存储设备

1.客户端：mysql 2.连接器： NativeC API 本地C语言接口、JDBCJAVA的数据库连接器、ODBC开放式数据库互联，非常底层 Oracle，SOLServer都支持 3.连接池（conetcionpool）：它的后方是MYSQL的真正功能 4.SQL接口：用户通过sql客户端发过来的命令，由sql接口接收（DML数据操作语言：查询，修改，升级数据等DDL数据定义语言：创建一个新的数据库，新的索引，删除一个用户等存储过程视图触发器） 5.Parser:分析器（查询 事务 对象权限） 6.optimizer：（访问路径 、 生成执行树） 7.caches&buffers：缓存和缓存 （优化里最重要的是缓存的优化） 8.pluggablestroage  engines存储引擎（插件式）：将逻辑结构转换为物理结构的程序 9.ManagementServices  & Utilites工具：备份，恢复，安全，移植，集群等，这些工具一般和文件系统打交道，不需要和mysql-server打交道，它们对应的都是命令。

查看当前mysql服务器所支持的存储引擎：show engines；

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

mysql> show engines; + --------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints | + --------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | PERFORMANCE_SCHEMA | YES     | Performance  Schema                                              |  NO            |  NO    |  NO          | | CSV                | YES     | CSV storage engine                                             |  NO            |  NO    |  NO          | | MRG_MYISAM         | YES     | Collection  of  identical MyISAM tables                          |  NO            |  NO    |  NO          | | BLACKHOLE          | YES     | /dev/ null  storage engine (anything you write  to  it disappears) |  NO            |  NO    |  NO          | | MyISAM             | YES     | MyISAM storage engine                                          |  NO            |  NO    |  NO          | | MEMORY             | YES     | Hash based, stored  in  memory, useful  for  temporary  tables      |  NO            |  NO    |  NO          | | ARCHIVE            | YES     | Archive storage engine                                         |  NO            |  NO    |  NO          | | InnoDB             |  DEFAULT  | Supports transactions, row- level  locking,  and  foreign  keys     | YES          | YES  | YES        | | FEDERATED          |  NO       | Federated MySQL storage engine                                 |  NULL          |  NULL  |  NULL        | + --------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9  rows  in  set  (0.00 sec) mysql>

指定存储引擎：

1，建表时指定存储引擎

create table 表名(字段列表)engine=存储引擎名;

注：在指定之前需要用showengine；查看所支持的引擎，Support列为是否支持！

2，建表之后修改使用的存储引擎，建表时没有指定则为默认的：

alter table 表名 engine=存储引擎名;

查看一个表的存储引擎：（即查看表的创建过程）

show create table 表名;

存放表结构的文件为/var/lib/mysql/库名/表名.frm

存放表数据的文件为/var/lib/mysql/库名/表名.ibd

事务：对表中数据的一次操作，从开始到结束的过程称作事务;

事务回滚：如果操作没有成功，则恢复到之前的状态;

InnoDB支持事务回滚;

锁机制：（解决多个用户对数据库记录并发访问问题）

所得类型：读锁、写锁

读锁(共享锁)：不会引起数据内容的改变;

写锁(互斥锁/排它锁)，锁定的程度为锁粒度;

注：写锁的优先级高于读锁;

锁粒度：表锁、行锁、页锁

表锁：myisam存储引擎支持表锁

行锁：innodb存储引擎支持行锁

页级锁定：查询的数据存放存的存储单位为页(单位默认为M)

row-level-locking 行级锁定(行锁);

写锁的优先级高于读锁;

myisam存储引擎：

特点：

支持表锁，不支持行锁；

不支持事务；

独享表空间；（每一个表都有相应的三个相关文件存储）

多用在查询、插入操作较多的表。

相关文件：表名.frm(表结构文件)、表名.MYI(存放索引)、表名.MYD(存放表记录)

InoDB存储引擎：

特点：

支持表锁、行锁;

支持事务、支持外键;

共享表空间;()

相关文件：

表名.frm(表结构文件)、表名.ibd(索引和表记录文件)

   ibdata1(支持回滚)

   ib_logfile0、ib_logfile1

设置mysql服务器默认支持的存储引擎：

在配置文件/etc/my.cnf

在mysqld字段添加：

default-storage-engine=存储引擎

default-storage-engine=myisam（默认的为innodb）

查看当前的锁状态：

即检查Table_lock开头的变量，%作为通配符；

show status like'table_lock%';

1 2 3 4 5 6 7 8 9

mysql> show status  like  'table_lock%' ; + -----------------------+-------+ | Variable_name         | Value | + -----------------------+-------+ | Table_locks_immediate | 70    | | Table_locks_waited    | 0     | + -----------------------+-------+ 2  rows  in  set  (0.00 sec) mysql>

外键的条件：

1，表的存储引擎为innodb存储引擎

2，表中外键字段的类型要与参考表的字段类型一致

3，外键字段要是索引类型中的一种

创建外键字段的语法格式

foreign key(字段名) references 参考表的表名(字段名)

同步更新、同步删除

在建表的时候指定同步更新、删除：

ON  UPDATE  CASCADE 同步更新

ON  DELETE CASCADE  同步删除

示例过程：

create  database engine;   //创建一个名为engine的数据库 create table  engine.yg(            //在engine库中创建yg表 yg_id int(3)  primary key auto_increment, name  varchar(10) not null )engine=innodb;           //指定存储引擎为innodb create table  engine.gz(         //在engine库中创建yg表 gz_id  int(3), gz  float(7,2) not null default 0.00, foreign  key(gz_id) references engine.yg(yg_id)  //指定外键字段为gz_id和yg的ge_id对应 on update  cascade on delete cascade         //指定同步更新，同步删除 )engine=innodb; use  engine;                        //切换到engine数据库 1：要求： 在yg表中插入名字为jim、tom两个名字的条目； 再插入一个名字为tom的条目。 desc yg; insert into  yg(name)values('jim'); insert into  yg(name)values('tom'); insert into  yg(name)values('tom'); 2：要求： 在gz表中添加第2和第3个人的工资为10000,和12000； desc gz; insert into  gz values(2,10000); insert into  gz values(3,12000); 3：要求： 在yg表中添加一个员工，名字为lucy； 在gz表中插入lucy的工资为4000； 删除yg表中lucy的员工信息；注：删除之后gz表中将没有gz_id=4的工资信息 使用select * from  gz;查看gz中是否还有gz_id为4的条目。 insert into  yg(name)values('lucy'); insert into  gz vlaues(4,4000); delete from  yg where yg_id=4; select *  from gz; 4：要求 更新yg表中的yg_id为2的员工信息； update yg set  yd_id=8 where yg_id=2; 5，要求： 删除gz表中的外键； 注：在删除外键的时候，首先使用show  create table from gz;来查看一下外键的名字（注：在创建外键的时候使用的字段名为gz_id，gz_id并不是外键名）。 show create  table from gz;          //首先查看建表过程查找froeign key字段 alter  table gz drop foreign key gz_ibfk_1;    //删除外键 show create  table from gz;             //再次查看外键已经被删除

总结：想要创建一个外键，必须先了解一下MySQL的存储引擎，只有innodb存储引擎才支持创建外键，在MySQL5.6里面，默认的存储引擎为innodb！！！

本文转自 murongqingqqq  51CTO博客，原文链接:http://blog.51cto.com/murongqingqqq/1378306