首页 > 代码库 > MySQL基础day03 存储引擎和外键MySQL 5.6

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;

    01.mysql> show engines;
    02.+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
    03.| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
    04.+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
    05.| PERFORMANCE_SCHEMA | YES     | PerformanceSchema                                             NO           NO   NO         |
    06.| CSV                | YES     | CSV storage engine                                             | NO           NO   NO         |
    07.| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           NO   NO         |
    08.| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           NO   NO         |
    09.| MyISAM             | YES     | MyISAM storage engine                                          | NO           NO   NO         |
    10.| MEMORY             | YES     | Hash based, stored in memory, useful for temporarytables      | NO           NO   NO         |
    11.| ARCHIVE            | YES     | Archive storage engine                                         | NO           NO   NO         |
    12.| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreignkeys     | YES          | YES  | YES        |
    13.| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         NULL NULL       |
    14.+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
    15.rows in set (0.00 sec)
    16.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%‘;

    01.mysql> show status like ‘table_lock%‘;
    02.+-----------------------+-------+
    03.| Variable_name         | Value |
    04.+-----------------------+-------+
    05.| Table_locks_immediate | 70    |
    06.| Table_locks_waited    | 0     |
    07.+-----------------------+-------+
    08.rows in set (0.00 sec)
    09.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!!!