MySQL 5.6学习笔记（数据表基本操作）

1. 创建数据表

1.1 最基本的语法

CREATE TABLE tbl_name
    (col_name column_definition,...)
    [table_options]

-column_definition

详解：

column_definition:
    data_type [NOT NULL | NULL] [DEFAULT default_value]
      [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
      [COMMENT 'string']
      [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
      [STORAGE {DISK|MEMORY|DEFAULT}]
      [reference_definition]

-table_options

table_options: table_option [table_option] ...

详解：

table_option:
    ENGINE [=] engine_name
  | AUTO_INCREMENT [=] value
  | AVG_ROW_LENGTH [=] value
  | [DEFAULT] CHARACTER SET [=] charset_name
  | CHECKSUM [=] {0 | 1}
  | [DEFAULT] COLLATE [=] collation_name
  | COMMENT [=] 'string'
  | CONNECTION [=] 'connect_string'
  | DATA DIRECTORY [=] 'absolute path to directory'
  | DELAY_KEY_WRITE [=] {0 | 1}
  | INDEX DIRECTORY [=] 'absolute path to directory'
  | INSERT_METHOD [=] { NO | FIRST | LAST }
  | KEY_BLOCK_SIZE [=] value
  | MAX_ROWS [=] value
  | MIN_ROWS [=] value
  | PACK_KEYS [=] {0 | 1 | DEFAULT}
  | PASSWORD [=] 'string'
  | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
  | STATS_AUTO_RECALC [=] {DEFAULT|0|1}
  | STATS_PERSISTENT [=] {DEFAULT|0|1}
  | TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]
  | UNION [=] (tbl_name[,tbl_name]...)

更多语法详细信息，参考：http://dev.mysql.com/doc/refman/5.6/en/create-table.html

1.2 实例

创建基本表：

create table tb_temp1
(
  id int(11),
  name varchar(25)
);

创建带主键的表（两种方法）：

create table tb_temp2
(
  id int(11) primary key,
  name varchar(25)
);

create table tb_temp3
(
  id int(11),
  name varchar(25),
  primary key(id)
);

支持联合主键（同时命名约束名）：

create table tb_temp4
(
  id int(11),
  sub_id int(4),
  name varchar(25),
  constraint PK_TEMP4 primary key(id,sub_id)
);

创建带外键的表（注意：外键不能跨存储引擎使用）：

create table tb_temp5
(
  id int(11) primary key,
  yourdesc varchar(25),
  constraint FK_TEMP5_ID foreign key(id) references tb_temp2(id)
);

非空约束（略）

唯一性约束（略）

默认约束（略）

创建主键自动增加的表：

create table tb_temp8
(
  id int(11) primary key auto_increment,
  name varchar(25)
);

2. 查看数据表结构

查看表基本结构：

desc tablename

查看表详细结构：

show create table tablename\G

示范：

mysql> show create table tb_temp2\G
*************************** 1. row ***************************
       Table: tb_temp2
Create Table: CREATE TABLE `tb_temp2` (
  `id` int(11) NOT NULL,
  `name` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

3. 修改数据表

3.1 修改表名

alter table <旧表名> rename to <新表名>;

3.2 修改字段的数据类型

alter table <表名> modify <字段名> <数据类型>;

3.3 修改字段名

alter table <表名> change <旧字段名> <新字段名> <新数据类型>;

3.4 添加字段

alter table <表名> add <新字段名> <数据类型>
    [约束条件] [first | after 已存在的字段名];

“first或after已存在字段名”用于指定新增字段在表中的位置，默认加到最后。

示范：

alter table tb_temp4 add manageid int(10);

alter table tb_temp4 add f_id int(4) not null first;

3.5 删除字段

alter table <表名> drop <字段名>;

3.6 修改字段的排列位置

alter table <表名> modify <字段1> <数据类型> first|after <字段2>;

示范：

alter table tb_temp4 modify name varchar(25) after f_id;

3.7 更改表的存储引擎

alter table <表名> engine=<更改后的存储引擎名>;

3.8 增加表的外键约束

ALTER TABLE yourtablename
    ADD [CONSTRAINT 外键名] FOREIGN KEY [id] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name, ...)
    [ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
    [ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}];

示范：

alter table bbscomment add constraint FK_COMMENT_ID foreign key(detail_id) references bbsdetail(detail_id) on delete cascade;

3.9 删除表的外键约束

alter table <表名> drop foreign key <外键约束名>;

4. 删除数据表

语法：

drop table [if exists] 表1,表2,...表n;

如果表存在外键（被其它表引用），则需要先删除约束关系，参考3.8节。

示范：

drop table if exists tb_temp1;