基于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: 0

mysql> 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: 0

mysql> 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 1
mysql> 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 1
mysql> 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: 0
mysql> 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 value
mysql> 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: 0
mysql> 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: 0

mysql> 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: 0

mysql> 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: 0

mysql> 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: 0

mysql> 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: 0

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)

//外键操作实例

//由于使用的是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=latin1
1 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)


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

最后编辑于：2017.12.05 06:52:26

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 206,602评论 6赞 481
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 88,442评论 2赞 382
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 152,878评论 0赞 344
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 55,306评论 1赞 279
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 64,330评论 5赞 373
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 49,071评论 1赞 285
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 38,382评论 3赞 400
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 37,006评论 0赞 259
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 43,512评论 1赞 300
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,965评论 2赞 325
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 38,094评论 1赞 333
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,732评论 4赞 323
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 39,283评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 30,286评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,512评论 1赞 262
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 45,536评论 2赞 354
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,828评论 2赞 345

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

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

前言

修改数据库表结构

执行动作：

语法格式

添加新字段

删除字段

修改字段类型

修改字段名

修改表名

mysql索引

概述

索引类型

使用索引

查看索引

index普通索引的使用规则

创建普通索引

删除普通索引

primary key主键的使用规则

复合主键的使用

唯一索引 unique

外键(foreign key)

mysql存储引擎

概述

基本操作

事务

锁机制

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

推荐阅读更多精彩内容