基于Linux的MySQL操作实例(修改表结构，MySQL索引，MySQL数据引擎)

前言

本篇是基于Linux下针对MySQL表结构的修改，MySQL索引的操作以及MySQL数据引擎的配置和说明。
本篇结合上一篇文档，基于Linux的MySQL操作实例（软件安装，mysql基本操作，mysql数据类型，建表、插入数据操作）
若是有兴趣的朋友可以去看看。
http://www.jianshu.com/p/444482ff5986
根据本人的一贯风格，本篇依旧是前面就这几点做一些概述，并不会涉及太多的概念或理论，大篇幅的进行实际的命令实例操作。
最后依旧欢迎各路大神批评指教，鄙人不胜感激。谢谢大家。

修改数据库表结构

alter table 表名 执行动作；

执行动作：

• 添加新字段（add）

• 删除已有字段（drop）

• 修改字段类型（modify）

• 修改字段名（change）

语法格式

添加新字段

add 添加新字段
add  字段名   类型(宽度)；
add  字段名   类型(宽度)  约束条件  ；
add  字段名   类型(宽度)  约束条件  first；
add  字段名   类型(宽度)  约束条件  after  字段名；

eg：

alter  table  t1  add   class  char(7)  default "nsd1609" first,add  tel  char(11) ,add  sex  enum  ("boy","girl")  default  "boy" after  name;

删除字段

drop  删除字段
drop  字段名

eg：

alter table   t1  drop  name，drop  sex;

修改字段类型

modify 修改字段类型
不能与字段已经存储的数据冲突
modify   字段名  类型(宽度) 约束条件；

eg：

mysql> alter table   t1
    -> modify  
    -> sex  enum("boy","girl","no") not null  default  "no";

修改字段名

change 修改字段名
change 原字段名 新字段名  类型(宽度) 约束条件；

eg：

alter table  t1  change  tel  iphone char(11);

修改表名

alter  table  原表名  rename  [to]  新表名；

eg:

alter table  t1 rename t111;

mysql索引

概述

索引:相当于 "书的目录"

• 索引的优点

• 加快查询记录的速度.

• 索引的缺点

• 会减慢写的速度( insert update  delete ).

• 占用物理存储空间.

在表里建索引 设置在字段上

索引类型

• 普通索引 index

• 唯一索引 unique

• 主键    primary key

• 外键    foreign  key

• 全文索引 fulltext

使用索引

• 查看

• 创建

• 使用规则

• 删除

查看索引

• desc   表名； ---> 显示结果中的Key列即是索引值

• show  index  from  表名\G；
  Table: user
  Column_name: Host
  Key_name: PRIMARY //即是索引值
  Index_type: BTREE    //共有三种：BTREE（二叉树），B+tree，hash

index普通索引的使用规则

• 一个表中可以有多个INDEX字段

• 字段的值允许有重复，且可以赋NULL值

• 经常把做查询条件的字段设置为INDEX字段

• INDEX字段的KEY标志是MUL

创建普通索引

1.在已有表里创建index字段
create index  索引名  on  表名(字段名);
create index  sex  on  t111(sex);

2.建表时创建index字段
create  table  表名 （
字段名列表，
index(字段名)，index(字段名)
）;

删除普通索引

drop index  索引名  on  表名；
drop  index  sex   on   t24;

primary  key主键的使用规则

• 一个表中只能有一个primary  key字段

• 对应的字段值不允许有重复，且不允许赋NULL值

• 如果有多个字段都作为PRIMARY KEY，称为复合主键，必须一起创建。

• 主键字段的KEY标志是PRI

• 通常与 AUTO_INCREMENT 连用

• 经常把表中能够唯一标识记录的字段设置为主键字段[记录编号字段]

建表时创建主键字段

create  table  t25( name  char(10), age   int(2), primary key(name));create  table  t26( name  char(10) primary  key, age   int(2));

删除主键

alter table 表名 drop  primary key;

在已有表里创建主键

alter table 表名 add  primary key(字段名）；

复合主键的使用

多个字段一起做主键是复合主键 必须一起创建。
字段的值不允许同时相同。

建表时创建：
create  table  t29(host  char(10),db char(10),user char(10),primary key(host,db,user));

对已有的表进行添加：
alter table  t29 add  primary key(host,user,db);

通常和aUTO_INCREMENT 连用，实现字段值的字段增长
经常把表中能够唯一标识记录的字段设置为主键字段[记录编号字段]

唯一索引 unique

• 字段的值可以为Null  但不可以重复

• 一个表里可以有多个unique字段

• 标志 UNI

一般使用于：姓名，身份证，考试证，护照，驾驶证

建表时创建：

create  table  t29(
name  char(10),
stu_id  char(9),
age int(2),
unique(stu_id)
);

在已有表里创建unique字段
create   unique  index  索引名   on  表名（字段名）;
create  unique  index  stu_id  on  t29(stu_id);

外键(foreign  key)

功能:
让当前表某个字段的值，在另一个表某个字段值的范围内选择。

使用规则:

• 表的存储引擎必须是innodb

• 字段的数据类型要匹配

• 被参考的字段必须是key 中的一种 (primary key)

create  table  jfb(
jfb_id   int(2) primary key auto_increment,
name  char(10),
pay   float(7,2)
)engine=innodb;

create table  bjb(
bjb_id  int(2),
name  char(10),
foreign  key(bjb_id)  references   jfb(jfb_id) on  update  cascade   on  delete cascade
)engine=innodb;

删除外键

show create   table   表名；
alter table  表名 drop  foreign key  外键名；
alter table bjb drop foreign key  bjb_ibfk_1;

mysql存储引擎

概述

存储引擎：
表的处理器，是mysql数据库服务软件自动程序，不同处理器有不同的功能和数据存储方式。

基本操作

• 查看数据库服务支持哪些存储引擎：

show  engines;
InnoDB DEFAULT

• 修改mysql数据库服务默认使用的存储引擎:

vim  /etc/my.cnf
[mysqld]default-storage-engine=myisam
service  mysql  restart

• 建表时指定表使用的存储引擎

create  table  t31(name char(10))engine=memory;

• 修改表使用的存储引擎

alter  table  表名  engine=存储引擎名；
eg:
alter  table  t31  engine=innodb;

• 查看表使用的存储引擎
  show  create  table  表名；

• 工作中使用哪种存储引擎？

• myisam

• innodb

• myisam的特点

• 独享表空间

• t1.frm  表结构

• t1.MYD  表记录

• t1.MYI   表索引

• innodb的特点

• 支持行级锁

• 支持外键 、 事务 、事务回滚

• 共享表空间

• t3.frm    表结构

• t3.ibd   表记录+表索引

事务

事务：一次sql操作从开始到结束的过程。

事务回滚：执行一次事务，只要执行过程中，任何一步执行失败，就恢复之前所有的sql操作。

事务日志文件记录对所有inondb存储引擎的表执行过的sql命令。

ibdata1  记录sql命令产生的数据信息

ib_logfile0----|
|---> 记录SQL 命令
ib_logfile1----|

锁机制

锁机制是为了解决客户端的并发访问冲突问题。

锁粒度： 表级锁   行级锁    页级锁

锁类型：

• 读锁 （共享锁）  select  * from t1;

• 写锁  (互斥锁  排它锁)

建表时如何决定表使用的存储引擎：

执行写操作多的表适合使用inondb存储引擎，这样并发访问大。

执行读操作多的表适合使用myisam存储引擎.

实例操作（前面基本描述的具体实现）

//进入mysql数据库mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| game_db            |
| mysql              |
| performance_schema |
| test               |
| user_db            |
+--------------------+6 rows in set (0.00 sec)

mysql> use user_db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-------------------+
| Tables_in_user_db |
+-------------------+
| user_list         |
+-------------------+1 row in set (0.00 sec)

mysql> desc user_list;
+-----------+-------------------------------------+------+-----+---------+----------------+
| Field     | Type                                | Null | Key | Default | Extra          |
+-----------+-------------------------------------+------+-----+---------+----------------+
| id        | int(10)                             | NO   | PRI | NULL    | auto_increment |
| u_name    | char(10)                            | NO   |     | NULL    |                |
| u_sex     | enum('boy','girl')                  | NO   |     | NULL    |                |
| u_subject | enum('computer','chinese','engish') | NO   |     | NULL    |                |
| u_grade   | double(6,2)                         | NO   |     | NULL    |                |
+-----------+-------------------------------------+------+-----+---------+----------------+5 rows in set (0.01 sec)//添加字段mysql> alter table user_list add 
    -> mail2 varchar(25) default "stuff@wolf.cn";
Query OK, 0 rows affected (0.91 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> select * from user_list;Empty set (0.00 sec)

mysql> desc user_list;
+-----------+-------------------------------------+------+-----+---------------+----------------+
| Field     | Type                                | Null | Key | Default       | Extra          |
+-----------+-------------------------------------+------+-----+---------------+----------------+
| id        | int(10)                             | NO   | PRI | NULL          | auto_increment |
| u_name    | char(10)                            | NO   |     | NULL          |                |
| u_sex     | enum('boy','girl')                  | NO   |     | NULL          |                |
| u_subject | enum('computer','chinese','engish') | NO   |     | NULL          |                |
| u_grade   | double(6,2)                         | NO   |     | NULL          |                |
| mail1     | varchar(25)                         | YES  |     | NULL          |                |
| mail2     | varchar(25)                         | YES  |     | stuff@wolf.cn |                |
+-----------+-------------------------------------+------+-----+---------------+----------------+7 rows in set (0.00 sec)

mysql> alter table user_list add 
    -> u_id char(11) not null first ;
Query OK, 0 rows affected (0.80 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc user_list;
+-----------+-------------------------------------+------+-----+---------------+----------------+
| Field     | Type                                | Null | Key | Default       | Extra          |
+-----------+-------------------------------------+------+-----+---------------+----------------+
| u_id      | char(11)                            | NO   |     | NULL          |                |
| id        | int(10)                             | NO   | PRI | NULL          | auto_increment |
| u_name    | char(10)                            | NO   |     | NULL          |                |
| u_sex     | enum('boy','girl')                  | NO   |     | NULL          |                |
| u_subject | enum('computer','chinese','engish') | NO   |     | NULL          |                |
| u_grade   | double(6,2)                         | NO   |     | NULL          |                |
| mail1     | varchar(25)                         | YES  |     | NULL          |                |
| mail2     | varchar(25)                         | YES  |     | stuff@wolf.cn |                |
+-----------+-------------------------------------+------+-----+---------------+----------------+8 rows in set (0.00 sec)
mysql> insert into user_list(u_id,u_name,u_sex,u_subject,u_grade,mail1) values( 1,"tom","boy","computer","200.00","123456@aliyun.com");
Query OK, 1 row affected (0.07 sec)

mysql> insert into user_list(u_id,u_name,u_sex,u_subject,u_grade,mail1) values( 2,"jerry","boy","chinese","300.00","654321@aliyun.com");
Query OK, 1 row affected (0.03 sec)

mysql> insert into user_list(u_id,u_name,u_sex,u_subject,u_grade,mail1) values( 3,"cool","boy","chinese","240.00","654321@aliyun.com");
Query OK, 1 row affected (0.15 sec)

mysql> select * from user_list;
+------+----+--------+-------+-----------+---------+-------------------+---------------+
| u_id | id | u_name | u_sex | u_subject | u_grade | mail1             | mail2         |
+------+----+--------+-------+-----------+---------+-------------------+---------------+
| 1    |  1 | tom    | boy   | computer  |  200.00 | 123456@aliyun.com | stuff@wolf.cn |
| 2    |  2 | jerry  | boy   | chinese   |  300.00 | 654321@aliyun.com | stuff@wolf.cn |
| 3    |  3 | cool   | boy   | chinese   |  240.00 | 654321@aliyun.com | stuff@wolf.cn |
+------+----+--------+-------+-----------+---------+-------------------+---------------+3 rows in set (0.00 sec)//删除字段mysql> alter table user_list drop  mail1,drop  u_id;
Query OK, 0 rows affected (0.78 sec)
mysql> alter table user_list add homeaddr char(50);
Query OK, 0 rows affected (0.66 sec)
Records: 0  Duplicates: 0  Warnings: 0//改变字段名mysql> alter table user_list  change mail2 mailaddr varchar(25) default "userinfo@aliyun.com"
    -> ;
mysql> alter table user_list rename user_info;
Query OK, 0 rows affected (0.20 sec)

mysql> show tables;
+-------------------+
| Tables_in_user_db |
+-------------------+
| user_info         |
+-------------------+1 row in set (0.00 sec)
mysql> modify u_grade double(7,2) not null ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'modify u_grade double(7,2) not null' at line 1mysql> alter table user_info  modify u_grade double(7,2) not null ;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0//修改字段参数mysql> alter table user_info modify u_grade float(3,2) not null;
ERROR 1264 (22003): Out of range value for column 'u_grade' at row 1mysql> select u_name,u_grade from user_info;
+--------+---------+
| u_name | u_grade |
+--------+---------+
| tom    |  200.00 |
| jerry  |  300.00 |
| cool   |  240.00 |
+--------+---------+3 rows in set (0.00 sec)
mysql> alter table user_info add tel char(15) not null,add phone char(11) ;
Query OK, 0 rows affected (0.73 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> insert into user_info(u_name,u_sex,u_subject,u_grade,mailaddr,homeaddr,tel,phone) values( "uzi","boy","computer","3000.00",NULL,NULL,"123456789",NULL);
mysql> select * from user_info;
+----+--------+-------+-----------+---------+---------------+----------+-----------+-------+
| id | u_name | u_sex | u_subject | u_grade | mailaddr      | homeaddr | tel       | phone |
+----+--------+-------+-----------+---------+---------------+----------+-----------+-------+
|  1 | tom    | boy   | computer  |  200.00 | stuff@wolf.cn | NULL     |           | NULL  |
|  2 | jerry  | boy   | chinese   |  300.00 | stuff@wolf.cn | NULL     |           | NULL  |
|  3 | cool   | boy   | chinese   |  240.00 | stuff@wolf.cn | NULL     |           | NULL  |
|  4 | uzi    | boy   | computer  | 3000.00 | NULL          | NULL     | 123456789 | NULL  |
+----+--------+-------+-----------+---------+---------------+----------+-----------+-------+4 rows in set (0.00 sec)

mysql> desc user_info;
+-----------+-------------------------------------+------+-----+---------------------+----------------+
| Field     | Type                                | Null | Key | Default             | Extra          |
+-----------+-------------------------------------+------+-----+---------------------+----------------+
| id        | int(10)                             | NO   | PRI | NULL                | auto_increment |
| u_name    | char(10)                            | NO   |     | NULL                |                |
| u_sex     | enum('boy','girl')                  | NO   |     | NULL                |                |
| u_subject | enum('computer','chinese','engish') | NO   |     | NULL                |                |
| u_grade   | double(7,2)                         | NO   |     | NULL                |                |
| mailaddr  | varchar(25)                         | YES  |     | userinfo@aliyun.com |                |
| homeaddr  | char(50)                            | YES  |     | NULL                |                |
| tel       | char(15)                            | NO   |     | NULL                |                |
| phone     | char(11)                            | YES  |     | NULL                |                |
+-----------+-------------------------------------+------+-----+---------------------+----------------+9 rows in set (0.00 sec)

mysql> alter table user_info modify phone char(11) not null;
ERROR 1138 (22004): Invalid use of NULL valuemysql> alter table user_info modify mailaddr varchar(25)  not null;
ERROR 1138 (22004): Invalid use of NULL value//index索引操作实例mysql> create index u_name on user_info(u_name);
Query OK, 0 rows affected (0.61 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> create table tab1 (
    -> id char(10),
    -> name char(15),
    -> age int,
    -> index(name),
    -> index(age)
    -> );
Query OK, 0 rows affected (0.86 sec)

mysql> desc tab1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | char(10) | YES  |     | NULL    |       |
| name  | char(15) | YES  | MUL | NULL    |       |
| age   | int(11)  | YES  | MUL | NULL    |       |
+-------+----------+------+-----+---------+-------+3 rows in set (0.00 sec)
mysql> show index from tab1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tab1  |          1 | name     |            1 | name        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| tab1  |          1 | age      |            1 | age         | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+2 rows in set (0.00 sec)

mysql> show index from tab1 \G;
*************************** 1. row ***************************
        Table: tab1
   Non_unique: 1
     Key_name: name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: tab1
   Non_unique: 1
     Key_name: age
 Seq_in_index: 1
  Column_name: age
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
2 rows in set (0.00 sec)

ERROR: 
No query specified

mysql> drop index age on tab1;
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc tab1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | char(10) | YES  |     | NULL    |       |
| name  | char(15) | YES  | MUL | NULL    |       |
| age   | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+3 rows in set (0.00 sec)//主键 primary key操作实例mysql> create table tab2 (id int primary key ,name char(10));
Query OK, 0 rows affected (0.67 sec)
mysql> desc tab2;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   | PRI | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+2 rows in set (0.00 sec)
mysql> create table tab3 (id int(2), name char(10), primary key(id));
Query OK, 0 rows affected (0.79 sec)
mysql> desc tab3;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(2)   | NO   | PRI | 0       |       |
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+2 rows in set (0.00 sec)

mysql> alter table tab3 drop primary key;
Query OK, 0 rows affected (1.10 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc tab3;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(2)   | NO   |     | 0       |       |
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+2 rows in set (0.00 sec)
mysql> create table tab4 (
    -> cip char(16),
    -> port int(2),
    -> status enum("deny","allow") default "deny"
    -> );
Query OK, 0 rows affected (0.65 sec)

mysql> desc tab4
    -> ;
+--------+----------------------+------+-----+---------+-------+
| Field  | Type                 | Null | Key | Default | Extra |
+--------+----------------------+------+-----+---------+-------+
| cip    | char(16)             | YES  |     | NULL    |       |
| port   | int(2)               | YES  |     | NULL    |       |
| status | enum('deny','allow') | YES  |     | deny    |       |
+--------+----------------------+------+-----+---------+-------+3 rows in set (0.00 sec)

mysql> alter table tab4 add primary key(cip,port);
Query OK, 0 rows affected (1.05 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc tab4;
+--------+----------------------+------+-----+---------+-------+
| Field  | Type                 | Null | Key | Default | Extra |
+--------+----------------------+------+-----+---------+-------+
| cip    | char(16)             | NO   | PRI |         |       |
| port   | int(2)               | NO   | PRI | 0       |       |
| status | enum('deny','allow') | YES  |     | deny    |       |
+--------+----------------------+------+-----+---------+-------+3 rows in set (0.00 sec)

mysql> insert into tab4 values("1.1.1.1",22,"deny");
Query OK, 1 row affected (0.05 sec)

mysql> insert into tab4 values("1.1.1.1",25,"allow");
Query OK, 1 row affected (0.07 sec)

mysql> insert into tab4 values("1.1.1.1",22,"allow");
ERROR 1062 (23000): Duplicate entry '1.1.1.1-22' for key 'PRIMARY'mysql> insert into tab4 values("2.1.1.1",22,"deny");
Query OK, 1 row affected (0.04 sec)

mysql> select * from tab4;
+---------+------+--------+
| cip     | port | status |
+---------+------+--------+
| 1.1.1.1 |   22 | deny   |
| 1.1.1.1 |   25 | allow  |
| 2.1.1.1 |   22 | deny   |
+---------+------+--------+3 rows in set (0.00 sec)//删除tab4表的主键mysql> alter table tab4 drop primary key;
Query OK, 3 rows affected (1.16 sec)
Records: 3  Duplicates: 0  Warnings: 0//当没有主键约束后，相同数据可以添加成功mysql> insert into tab4 values("1.1.1.1",22,"allow");
Query OK, 1 row affected (0.07 sec)
mysql> insert into tab4 values("1.1.1.1",25,"allow");
Query OK, 1 row affected (0.04 sec)//重新添加主键，由于表中已经存在不符合约束条件的数据，所以无法添加主键成功mysql> alter table tab4 add primary key(cip,port);
ERROR 1062 (23000): Duplicate entry '1.1.1.1-22' for key 'PRIMARY'//删除不符合数据mysql> delete from tab4 where port=22;
Query OK, 3 rows affected (0.04 sec)

mysql> alter table tab4 add primary key(cip,port);
ERROR 1062 (23000): Duplicate entry '1.1.1.1-25' for key 'PRIMARY'mysql> delete from tab4 where port=25;
Query OK, 2 rows affected (0.08 sec)//主键添加成功mysql> alter table tab4 add primary key(cip,port);
Query OK, 0 rows affected (0.80 sec)
Records: 0  Duplicates: 0  Warnings: 0//可以直接在创建表时就指定复合主键，主键个数可以多个，语法格式不变mysql> create table tab5 (u_id int ,name char(10),other char(40),primary key(u_id,name));
Query OK, 0 rows affected (0.63 sec)

mysql> desc tab5;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| u_id  | int(11)  | NO   | PRI | 0       |       |
| name  | char(10) | NO   | PRI |         |       |
| other | char(40) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+3 rows in set (0.00 sec)//自增 auto_increment 只能修饰主键，必须是数值类型，最好是整形mysql> create table tab6 (id int(2) zerofill primary key auto_increment,
    -> name char(10) not null,
    -> age tinyint(2) not null default 18
    -> , sex  enum("boy","girl") default "boy",
    -> other char(50));
Query OK, 0 rows affected (0.65 sec)

mysql> desc tab6;
+-------+--------------------------+------+-----+---------+----------------+
| Field | Type                     | Null | Key | Default | Extra          |
+-------+--------------------------+------+-----+---------+----------------+
| id    | int(2) unsigned zerofill | NO   | PRI | NULL    | auto_increment |
| name  | char(10)                 | NO   |     | NULL    |                |
| age   | tinyint(2)               | NO   |     | 18      |                |
| sex   | enum('boy','girl')       | YES  |     | boy     |                |
| other | char(50)                 | YES  |     | NULL    |                |
+-------+--------------------------+------+-----+---------+----------------+5 rows in set (0.00 sec)//插入数据mysql> insert into tab6(name,age,sex,other) values("tom",12,"boy","This is Tom");
Query OK, 1 row affected (0.02 sec)

mysql> insert into tab6(name,age,sex,other) values("jerry",14,"boy","This is Jerry");
Query OK, 1 row affected (0.05 sec)

mysql> insert into tab6(name,age,sex,other) values("natasha",17,"girl","This is natasha.");
Query OK, 1 row affected (0.10 sec)

mysql> select * from tab6;
+----+---------+-----+------+------------------+
| id | name    | age | sex  | other            |
+----+---------+-----+------+------------------+
| 01 | tom     |  12 | boy  | This is Tom      |
| 02 | jerry   |  14 | boy  | This is Jerry    |
| 03 | natasha |  17 | girl | This is natasha. |
+----+---------+-----+------+------------------+3 rows in set (0.00 sec)

mysql> insert into tab6 values (7,"cool",22,"boy","This is cool");
Query OK, 1 row affected (0.05 sec)//会根据表中最大的数字进行自增计算mysql> insert into tab6(name,age,sex,other) values("uzi",19,"boy","This is uzi.");
Query OK, 1 row affected (0.05 sec)

mysql> select * from tab6;
+----+---------+-----+------+------------------+
| id | name    | age | sex  | other            |
+----+---------+-----+------+------------------+
| 01 | tom     |  12 | boy  | This is Tom      |
| 02 | jerry   |  14 | boy  | This is Jerry    |
| 03 | natasha |  17 | girl | This is natasha. |
| 07 | cool    |  22 | boy  | This is cool     |
| 08 | uzi     |  19 | boy  | This is uzi.     |
+----+---------+-----+------+------------------+5 rows in set (0.00 sec)//删除所有数据后，再次添加新值mysql> delete  from tab6;
Query OK, 5 rows affected (0.07 sec)

mysql> select * from tab6;Empty set (0.00 sec)

mysql> insert into tab6(name,age,sex,other) values("uzi",19,"boy","This is uzi.");
Query OK, 1 row affected (0.06 sec)

mysql> insert into tab6(name,age,sex,other) values("uzi",19,"boy","This is uzi.");
Query OK, 1 row affected (0.03 sec)//内部有计数器，会按照上次结果继续增加，这样可以保证数据不会出现重复mysql> select * from tab6;
+----+------+-----+------+--------------+
| id | name | age | sex  | other        |
+----+------+-----+------+--------------+
| 09 | uzi  |  19 | boy  | This is uzi. |
| 10 | uzi  |  19 | boy  | This is uzi. |
+----+------+-----+------+--------------+2 rows in set (0.00 sec)//unique约束实例//创建表时，unique参数需要分开创建mysql> create table tab7 (id int(2) zerofill primary key auto_increment,
    -> per_id char(9),
    -> car_id char(7),
    -> unique(per_id,car_id)
    -> );
Query OK, 0 rows affected (0.85 sec)

mysql> desc tab7;
+--------+--------------------------+------+-----+---------+----------------+
| Field  | Type                     | Null | Key | Default | Extra          |
+--------+--------------------------+------+-----+---------+----------------+
| id     | int(2) unsigned zerofill | NO   | PRI | NULL    | auto_increment |
| per_id | char(9)                  | YES  | MUL | NULL    |                |
| car_id | char(7)                  | YES  |     | NULL    |                |
+--------+--------------------------+------+-----+---------+----------------+3 rows in set (0.00 sec)//删除表，重新创建表mysql> drop table tab7;
Query OK, 0 rows affected (0.23 sec)

mysql> create table tab7 (id int(2) zerofill primary key auto_increment, per_id char(9), car_id char(7), unique(per_id),unique(car_id) );
Query OK, 0 rows affected (0.88 sec)

mysql> desc tab7;
+--------+--------------------------+------+-----+---------+----------------+
| Field  | Type                     | Null | Key | Default | Extra          |
+--------+--------------------------+------+-----+---------+----------------+
| id     | int(2) unsigned zerofill | NO   | PRI | NULL    | auto_increment |
| per_id | char(9)                  | YES  | UNI | NULL    |                |
| car_id | char(7)                  | YES  | UNI | NULL    |                |
+--------+--------------------------+------+-----+---------+----------------+3 rows in set (0.00 sec)

mysql> insert into tab7(per_id,car_id) values("123456789","1234567");
Query OK, 1 row affected (0.05 sec)

mysql> insert into tab7(per_id,car_id) values(NULL,"1234567");
ERROR 1062 (23000): Duplicate entry '1234567' for key 'car_id'mysql> insert into tab7(per_id,car_id) values(NULL,"7654321");
Query OK, 1 row affected (0.05 sec)

mysql> insert into tab7(per_id,car_id) values(NULL,NULL);
Query OK, 1 row affected (0.04 sec)

mysql> select * from tab7;
+----+-----------+---------+
| id | per_id    | car_id  |
+----+-----------+---------+
| 01 | 123456789 | 1234567 |
| 03 | NULL      | 7654321 |
| 04 | NULL      | NULL    |
+----+-----------+---------+3 rows in set (0.00 sec)

mysql> insert into tab7(per_id,car_id) values("123456789","1234567");
Query OK, 1 row affected (0.05 sec)

mysql> insert into tab7(per_id,car_id) values(NULL,"1234567");
ERROR 1062 (23000): Duplicate entry '1234567' for key 'car_id'mysql> insert into tab7(per_id,car_id) values(NULL,"7654321");
Query OK, 1 row affected (0.05 sec)

mysql> insert into tab7(per_id,car_id) values(NULL,NULL);
Query OK, 1 row affected (0.04 sec)

mysql> select * from tab7;
+----+-----------+---------+
| id | per_id    | car_id  |
+----+-----------+---------+
| 01 | 123456789 | 1234567 |
| 03 | NULL      | 7654321 |
| 04 | NULL      | NULL    |
+----+-----------+---------+3 rows in set (0.00 sec)

mysql> insert into tab7(per_id,car_id) values(NULL,NULL);
Query OK, 1 row affected (0.04 sec)

mysql> insert into tab7(per_id,car_id) values("012345678","1234567");
ERROR 1062 (23000): Duplicate entry '1234567' for key 'car_id'mysql> alter table tab7 drop index car_id;
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> insert into tab7(per_id,car_id) values("012345678","1234567");
Query OK, 1 row affected (0.05 sec)

mysql> insert into tab7(per_id,car_id) values("012345678","1234567");

mysql> create unique index car_id  on tab7(car_id);
ERROR 1062 (23000): Duplicate entry '1234567' for key 'car_id'mysql> desc tab7;
+--------+--------------------------+------+-----+---------+----------------+
| Field  | Type                     | Null | Key | Default | Extra          |
+--------+--------------------------+------+-----+---------+----------------+
| id     | int(2) unsigned zerofill | NO   | PRI | NULL    | auto_increment |
| per_id | char(9)                  | YES  | UNI | NULL    |                |
| car_id | char(7)                  | YES  |     | NULL    |                |
+--------+--------------------------+------+-----+---------+----------------+3 rows in set (0.00 sec)

mysql> select * from tab7;
+----+-----------+---------+
| id | per_id    | car_id  |
+----+-----------+---------+
| 01 | 123456789 | 1234567 |
| 03 | NULL      | 7654321 |
| 04 | NULL      | NULL    |
| 05 | NULL      | NULL    |
| 07 | 012345678 | 1234567 |
+----+-----------+---------+5 rows in set (0.00 sec)

mysql> delete from tab7 where per_id=012345678;
Query OK, 1 row affected (0.05 sec)

mysql> create unique index car_id  on tab7(car_id);
Query OK, 0 rows affected (0.73 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc tab7;
+--------+--------------------------+------+-----+---------+----------------+
| Field  | Type                     | Null | Key | Default | Extra          |
+--------+--------------------------+------+-----+---------+----------------+
| id     | int(2) unsigned zerofill | NO   | PRI | NULL    | auto_increment |
| per_id | char(9)                  | YES  | UNI | NULL    |                |
| car_id | char(7)                  | YES  | UNI | NULL    |                |
+--------+--------------------------+------+-----+---------+----------------+3 rows in set (0.00 sec)//外键操作实例//由于使用的是MySQL5.6版本，默认的存储引擎即是：innodb//该默认的存储引擎根据数据库的版本有所不同。//创建员工表mysql> create table work_tab (w_id int(4) zerofill primary key auto_increment,
    -> name char(10) not null,
    -> sex enum("man","woman") default "man",
    -> detials char(40) default ""
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> desc work_tab;
+---------+--------------------------+------+-----+---------+----------------+
| Field   | Type                     | Null | Key | Default | Extra          |
+---------+--------------------------+------+-----+---------+----------------+
| w_id    | int(4) unsigned zerofill | NO   | PRI | NULL    | auto_increment |
| name    | char(10)                 | NO   |     | NULL    |                |
| sex     | enum('man','woman')      | YES  |     | man     |                |
| detials | char(40)                 | YES  |     |         |                |
+---------+--------------------------+------+-----+---------+----------------+4 rows in set (0.00 sec)//插入测试数据mysql> insert into work_tab(name,sex,detials) values("tom","man","This is tom.");
Query OK, 1 row affected (0.00 sec)

mysql> insert into work_tab(name,sex,detials) values("jack","man","This is jack.");
Query OK, 1 row affected (0.00 sec)

mysql> insert into work_tab(name,sex,detials) values("natasha","woman","This is natasha.");
Query OK, 1 row affected (0.00 sec)/*
创建工资表，将该表的p_id与员工表的w_id进行外键绑定，即用来标识唯一用户（员工）
mysql>  
*/mysql> create table pay_tab (p_id int(4) zerofill , name char(10) not null ,
    -> pays double(8,2) not null, others char(30) default "" ,
    -> foreign key(p_id) references work_tab(w_id) on update cascade on delete cascade)
    -> engine=innodb;

mysql> desc pay_tab;
+--------+--------------------------+------+-----+---------+-------+
| Field  | Type                     | Null | Key | Default | Extra |
+--------+--------------------------+------+-----+---------+-------+
| p_id   | int(4) unsigned zerofill | YES  | MUL | NULL    |       |
| name   | char(10)                 | NO   |     | NULL    |       |
| pays   | double(8,2)              | NO   |     | NULL    |       |
| others | char(30)                 | YES  |     |         |       |
+--------+--------------------------+------+-----+---------+-------+4 rows in set (0.00 sec)//查看建表过程，验证创建表的结果是否正确mysql> show create table pay_tab\G;
*************************** 1. row ***************************
       Table: pay_tab
Create Table: CREATE TABLE `pay_tab` (
  `p_id` int(4) unsigned zerofill DEFAULT NULL,
  `name` char(10) NOT NULL,
  `pays` double(8,2) NOT NULL,
  `others` char(30) DEFAULT '',
  KEY `p_id` (`p_id`),
  CONSTRAINT `pay_tab_ibfk_1` FOREIGN KEY (`p_id`) REFERENCES `work_tab` (`w_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)

ERROR: 
No query specified

mysql> select * from work_tab;
+------+---------+-------+------------------+
| w_id | name    | sex   | detials          |
+------+---------+-------+------------------+
| 0001 | tom     | man   | This is tom.     |
| 0002 | jack    | man   | This is jack.    |
| 0003 | natasha | woman | This is natasha. |
+------+---------+-------+------------------+3 rows in set (0.00 sec)//向工资表插入在员工表存在的数据，可以插入mysql> insert into pay_tab values(2,"jack",9000.00,"jack pays");
Query OK, 1 row affected (0.06 sec)//向工资表插入在员工表不存在的数据，受外键约束无法插入mysql> insert into pay_tab values(4,"bob",8000.00,"jack pays");
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`user_db`.`pay_tab`, CONSTRAINT `pay_tab_ibfk_1` FOREIGN KEY (`p_id`) REFERENCES `work_tab` (`w_id`) ON DELETE CASCADE ON UPDATE CASCADE)//由于目前进行的约束仅仅是id，所以当向工资表插入数据时，name不一致的情况下，依旧可以插入//一般我们在这里编写的SQL指令，一般都是由开发进行操作的，在开发操作时，一般都是去员工表查询对应的用户，然后将查询的结果和新值进行添加操作，这样一般是不会出现该错误mysql> insert into pay_tab values(3,"wolf",10000.00,"wolf or natasha??");
Query OK, 1 row affected (0.08 sec)//删除受约束表的记录时，可以正常操作，并且不会对员工表产生影响mysql> delete from pay_tab where name="jack";
Query OK, 1 row affected (0.05 sec)

mysql> select * from  pay_tab;
+------+------+----------+-------------------+
| p_id | name | pays     | others            |
+------+------+----------+-------------------+
| 0003 | wolf | 10000.00 | wolf or natasha?? |
+------+------+----------+-------------------+1 row in set (0.00 sec)

mysql> insert into pay_tab values("tom",6000.00,"tom pays");
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into pay_tab values(1,"tom",6000.00,"tom pays");
Query OK, 1 row affected (0.06 sec)
mysql> select * from work_tab;
+------+---------+-------+------------------+
| w_id | name    | sex   | detials          |
+------+---------+-------+------------------+
| 0001 | tom     | man   | This is tom.     |
| 0002 | jack    | man   | This is jack.    |
| 0003 | natasha | woman | This is natasha. |
+------+---------+-------+------------------+
3 rows in set (0.00 sec)

mysql> select * from pay_tab;
+------+------+----------+-------------------+
| p_id | name | pays     | others            |
+------+------+----------+-------------------+
| 0003 | wolf | 10000.00 | wolf or natasha?? |
| 0001 | tom  |  6000.00 | tom pays          |
+------+------+----------+-------------------+
2 rows in set (0.00 sec)

//在定义外键取值范围的表（work_tab员工表）删除数据时，对应的受外键约束的表（工资表）的对应记录也会被删除
mysql> delete from work_tab where name="tom";
Query OK, 1 row affected (0.04 sec)

mysql> select * from pay_tab;
+------+------+----------+-------------------+
| p_id | name | pays     | others            |
+------+------+----------+-------------------+
| 0003 | wolf | 10000.00 | wolf or natasha?? |
+------+------+----------+-------------------+
1 row in set (0.00 sec)

mysql> select * from work_tab;
+------+---------+-------+------------------+
| w_id | name    | sex   | detials          |
+------+---------+-------+------------------+
| 0002 | jack    | man   | This is jack.    |
| 0003 | natasha | woman | This is natasha. |
+------+---------+-------+------------------+
2 rows in set (0.00 sec)

//在受外键约束的表中（pay_tab工资表），删除不受外键约束的字段时，可以正常删除
mysql> alter table pay_tab drop name;
Query OK, 0 rows affected (1.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

//删除受约束的字段，无法成功，会被告知外键约束
mysql> alter table pay_tab drop p_id;
ERROR 1553 (HY000): Cannot drop index 'p_id': needed in a foreign key constraint

//当删除外键约束的定义或直接删除外键字段，即可删除员工表或员工表内对应的记录
mysql> alter table pay_tab drop foreign key pay_tab_ibfk_1;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0
//mysql> drop table pay_tab;

mysql> drop table work_tab;
Query OK, 0 rows affected (0.28 sec)

//存储引擎操作实例

//default所在的行即是当前默认的存储引擎，Support表示当前可以使用，为NO即表示不可使用，
//Transactions 表示存储引擎不支持事务，Comment表示描述信息
//查看当前MySQL支持的数据引擎
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)

//不同的存储引擎生成的表文件也不同
.frm ---> 存放表结构

//指定不同的数据引擎创建数据表
mysql> create table tab8(id int)engine=MyISAM;
Query OK, 0 rows affected (0.11 sec)

mysql> create table tab9(id int)engine=MEMORY;
Query OK, 0 rows affected (0.11 sec)

mysql> create table tab9(id int)engine=InnoDB;
ERROR 1050 (42S01): Table 'tab9' already exists
mysql> create table tab10(id int)engine=InnoDB;
Query OK, 0 rows affected (0.56 sec)

//退出mysql，进入文件目录，查看对应文件
[root@mysql user_db]# pwd
/var/lib/mysql/user_db
[root@mysql user_db]# ls tab8*
tab8.frm  tab8.MYD  tab8.MYI
[root@mysql user_db]# ls tab9*
tab9.frm            //临时表，存放到内存中，当系统将内存收回，即停止mysql服务时，该表数据丢失。
[root@mysql user_db]# ls tab10*
tab10.frm  tab10.ibd        //共享表空间

//查看建表过程
mysql> show create tab9;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'tab9' at line 1
mysql> show create table tab9;
+-------+------------------------------------------------------------------------------------------+
| Table | Create Table                                                                             |
+-------+------------------------------------------------------------------------------------------+
| tab9  | CREATE TABLE `tab9` (
  `id` int(11) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter table tab9 engine=innodb;
Query OK, 0 rows affected (0.64 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table tab9;
+-------+------------------------------------------------------------------------------------------+
| Table | Create Table                                                                             |
+-------+------------------------------------------------------------------------------------------+
| tab9  | CREATE TABLE `tab9` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

//修改mysql默认的数据引擎
[root@mysql user_db]# vim /etc/my.cnf
[mysqld]
default-storage-engine=myisam
[root@mysql user_db]# service mysql restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL.. SUCCESS! 
[root@mysql user_db]# mysql -uroot -p123456 user_db
……
//DEFAULT所在的位置已经发生改变  
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             | DEFAULT | 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             | YES     | 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)


//数据库的锁，是为了进行并发操作时，操作冲突的情况。
//锁有读锁和写锁。

作者：海渊_haiyuan
链接：https://www.jianshu.com/p/1ca8da8ff190