MySQL数据表控制语句+存储引擎

                           ## MySQL数据表控制语句

一、约束条件相关语句
1、主键约束（主键约束要求主键列的数据唯一，并且不允许为空）

<!--创建库-->
mysql> create database test01; 
<!--切换至新库-->
mysql> use test01;
<!--创建一个带有主键约束的表-->
mysql> create table t1(
    -> id int(10),
    -> name varchar(10) primary key,
    -> sex varchar(5),
    -> info varchar(200)
    -> );

确定创建的是否为主键（该列是否有PRI字样）：

上述方法，是在定义列的同时定义主键，下面来写一下定义完所有列之后指定主键：

mysql> create table t2(
    -> id int(10),
    -> name varchar(10),
    -> sex varchar(5),
    -> primary key(id)
    -> );

确认是否有主键：

2、非空约束（不允许列的值为空）

mysql> create table t3(
    -> id int(6) not null,
    -> name varchar(10)
    -> );

查看表信息确认：

3、设置值的唯一性（不允许重复数据，可以为空，但只能有一个空，否则就会被视为重复）

mysql> create table t4(
    -> id int not null unique,
    -> name varchar(20)
    -> );

查看其表结构：

可以看到其被标识为了主键，但是在创建时并没有指定它是主键，而是这一列的属性基本满足了主键的要求，如唯一、不可以为空。

4、设置列的默认值（如果该列为空，则写入默认值）

mysql> create table t5(
    -> id int(2) not null,
    -> name varchar(20),
    -> project varchar(20) default 'mysql'
    -> );

查看确认：

5、设置自增值（一般用于id列，自增列必须设置为主键）
注：mysql只允许设置初始值，而不允许设置自增值，也就是说，可以设置为第一个值为5，然后依次递增，如：5、6、7…但不可以设置其一次递增2个数，比如：5、7、9…

mysql> create table t6(
    -> id int not null primary key auto_increment,
    -> name varchar(20)
    -> );

查看表结构进行确认：

测试其自增效果：

从上面的测试可以看出，只插入了两个name的值，并没有插入id的值，但是查看表数据时，id已经有值了，说明自增生效。

设置自增的起始值

#定义初始值为5
mysql> create table t7(                                  
    -> id int primary key auto_increment,
    -> name varchar(20)
    -> ) auto_increment=5;
#插入数据进行测试
mysql> insert into t7(name) values('zhangsan'),('lisi'); 

验证其自增值：

二、ALTER指令的使用
1、修改列值的数据长度
查看t1表的结构

mysql> alter table t1 modify name varchar(20);      <!--修改其name字段的长度为20-->

确认修改后的表结构：

2、修改字段名（在修改字段名的同时，还可以修改其新字段名的数据类型及数据长度）
查看其info列

mysql> alter table t1 change info infofo char(20);    <!--修改其info列的名字及数据类型-->

验证修改后的结果：

3、向表中插入一个新的字段
1）在最后一列插入新列：
确认t2列当前的字段

mysql> alter table t3 add tel int(13);         <!--插入一个tel列-->

2）在表格开头插入新列：

mysql> alter table t2 add aa char(1) first;

查看插入后的列：

3）在指定的列后面插入新列：

mysql> alter table t2 add bb varchar(255) after name;

4、添加字段时添加约束

mysql> alter table t2 add hobyy varchar(255) default 'work';

5、添加一个外键
进行此操作前，需要自行找一个有主键的表（我这里t1表的name列是主键）。

现在t1表的结构如下：

t3表的结构如下：

现在将t3表的name列添加为t1表的name列的外键（其中t3_t1_name为自定义的约束名称）：

mysql> alter table t3 add constraint t3_t1_name foreign key(name) references t1(name);

查看t3表的变化：

6、删除外键
将上面添加的外键删除，t3_t1_name是外键的名称。

mysql> alter table t3 drop foreign key t3_t1_name;
mysql> alter table t3 drop  key t3_t1_name;

7、删除列

mysql> alter table t3 drop name;                <!--删除t3表的name列-->

注意：如果要删除的列和其他表中的列有关联关系，则需要先删除关系，再删除列。否则当以后再创建了相同名称的列时，会自动将其建立关系。

8、修改列的顺序

mysql> alter table t3 modify hobyy varchar(10) first;

9、删除表

mysql> drop table t5;            <!--直接删除-->
Query OK, 0 rows affected (0.00 sec)

mysql> drop table t5;            <!--再次删除，由于已经删除了，所以表不存在，会报错-->
ERROR 1051 (42S02): Unknown table 'test01.t5'
mysql> drop table if exists t5;       <!--进行判断后删除，if exists表示如果存在就删除-->
Query OK, 0 rows affected, 1 warning (0.00 sec)
<!--可以看到上述返回的信息有1个warning事项，可以执行以下命令进行查看-->
mysql> show warnings;    <!--记录的信息时不知道test01库中的t5表-->
+-------+------+---------------------------+
| Level | Code | Message                   |
+-------+------+---------------------------+
| Note  | 1051 | Unknown table 'test01.t5' |
+-------+------+---------------------------+
1 row in set (0.00 sec)

同样，当执行删除表操作时，如果存在关联关系，则需要先删除关联关系，再删除表。

                                   ## 存储引擎

数据库存储引擎是数据库底层软件组件，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。

MySQL提供了多个不同的存储引擎，包括处理事务安全表的引擎和处理非事务安全表的引擎。在MySQL中，不需要在整个服务器中使用一种引擎，针对具体要求可以对每一个表使用不同的存储引擎。

存储引擎说白了就是数据存储的格式，不同的存储引擎功能不同，占用的空间大小不同，读取性能也不同;
数据库存储引擎是数据库底层软件组件，不同的存储引擎提供不同的存储机制;
在 MySQL 中，不需要在整个服务器中使用同一种存储引擎，可以对每一个表使用不同的存储引擎;
MySQL 支持多种存储引擎，如 InnoDB 、MyISAM 、Memory 、Merge 、Archive 、CSV 、Federated 等等。

一、MyISAM存储引擎
1、MyISAM存储引擎的特点

MySQL 5.5 之前默认使用 MyISAM 引擎，MySQL 5.5 之后默认使用 InnoDB 引擎；
MyISAM 引擎读取速度较快，占用资源相对较少，不支持事务，不支持外键约束，但支持全文索引；
读写互相阻塞，也就是说读数据的时候你就不能写数据，写数据的时候你就不能读数据；
MyISAM 引擎只能缓存索引，而不能缓存数据。

2、MyISAM适用场景

不需要事务支持的业务，例如转账就不行;
适用于读数据比较多的业务，不适用于读写频繁的业务;
并发相对较低、数据修改相对较少的业务;
硬件资源比较差的机器可以考虑使用 MyISAM 引擎。

二、InnoDB存储引擎
1、InnoDB存储引擎的特点

事务型数据库的首选引擎，支持事务安全表，支持行锁定和外键，MySQL 5.5.5 版本之后，InnoDB 作为默认存储引擎;
具有提交、回滚和崩溃恢复能力的事务安全存储引擎，能处理巨大数据量，性能及效率高，完全支持外键完整性约束;
具有非常高效的缓存特性，能缓存索引也能缓存数据，对硬件要求比较高;
使用 InnoDB 时，将在 MySQL 数据目录下创建一个名为 ibdata1 的 10MB 大小的自动扩展数据文件，以及两个名为 ib_logfile0 和 ib_logfile1 的 5MB 大小的日志文件。

2、InnoDB 适用场景

需要事务支持的业务、高并发的业务
数据更新较为频繁的场景，比如 BBS、SNS、微博等
数据一致性要求较高的业务，比如充值转账、银行卡转账

三、Memory存储引擎特点

Memory 存储引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问;
Memory 存储引擎执行 HASH 和 BTREE 索引，不支持 BLOB 和 TEXT 列，支持 AUTO_INCREMENT 列和对可包含 NULL 值得列的索引;
当不再需要 Memory 表的内容时，要释放被 Memory 表使用的内存，应该执行 DELETE FROM 或 TRUNCATE TABLE ，或者删除整个表。

四、如何选择合适的存储引擎？

不同的业务需求需要选择不同的存储引擎，具体可以参照以下几点来进行选择：

如果要提供提交、回滚和崩溃恢复能力的事务安全能力，并要求实现并发控制，InnoDB 是个很好的选择；
如果数据表主要用来插入和查询记录，则 MyISAM 引擎能提供较高的处理效率；
如果只是临时存放数据，数据量不大，并且不需要较高的安全性，可以选择将数据保存在内存中的 Memory 引擎，MySQL 使用该引擎作为临时表，存放查询的中间结果；
如果只有 INSERT 和 SELECT 操作，可以选择 Archive 引擎，支持高并发的插入操作，如记录日志信息可以使用 Archive 引擎。

主要的三个数据引擎所支持的功能如下表所示：

查看mysql数据库的引擎信息

可以看到InnoDB引擎是默认的存储引擎
Support列， YES表示当前版本支持这个存储引擎， DEFAULT表示该引擎是默认的引擎。NO表示不支持该存储引擎。
查看系统变量default_storage_engine或storage_engine

default_storage_engine 表示永久表（permanent tables）的默认存储引擎。；
default_tmp_storage_engine 表示临时表的默认存储引擎；

修改MySQL数据库的默认存储引擎

[root@localhost ~]# vim /etc/my.cnf                       //编写mysql服务的主配置文件
               ………………         //省略部分内容，添加如下内容
default-storage-engine=MyISAM
[root@localhost ~]#   /etc/init.d/mysqld restart          //重新启动mysql服务

再次查看发现MyISAM引擎已经成为默认引擎

在mysql数据库中直接修改存储默认引擎

mysql> set default_storage_engine=InnoDB;

再次查看发现InnoDB引擎已经成为默认引擎

注意，这个系统变量default_storage_engine是BOTH(全局和临时），而且可以动态修改。但是要注意，即使你修改了系统变量default_storage_engine，重启过后就会失效，如果你要永久修改，最好在my.cnf配置文件里面也设置default-storage-engine的值。

查看表使用的默认引擎

mysql> show create table s1 \G;
可以看到s1表默认使用的引擎是MyISAM
修改表的默认存储引擎

mysql> ALTER TABLE s1 ENGINE = InnoDB;

可以看出s1表默认使用的引擎是InnoDB

创建表的时候指定存储引擎
创建表的时候，如果要指定存储引擎，只需要设置参数ENGINE即可。