mysql表(table)的操作

创建表的命令

命令格式：create [temporary] table [if exists] [数据库名.]表名称(字段信息) [表选项]

每个字段必须有数据类型，最后一个字段后不能有逗号。temporary 临时表，会话结束时表自动消失。对于字段的定义如下：

字段名 数据类型 [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT '描述']

表选项如下表：

选项	用法	备注
字符集	CHARSET = 字符集	如果表没有设定，则使用数据库字符集
存储引擎	ENGINE =引擎名称	设置表使用什么引擎
数据文件目录	DATA DIRECTORY = '目录'	设置数据文件存放的路径
索引文件目录	INDEX DIRECTORY = '目录'	设置索引文件存放的路径
表注释	COMMENT = '描述'	表的描述及解释该表的用途
分区选项	PARTITION BY	表分区主要是用来优化数据查询操作

ps: 表在管理数据时采用的不同的数据结构，结构不同会导致处理方式、提供的特性操作等不同。常见的引擎：InnoDB MyISAM Memory/Heap BDB Merge Example CSV MaxDB Archive。不同的引擎在保存表的结构和数据时采用不同的方式:
MyISAM表文件含义：.frm表定义，.MYD表数据，.MYI表索引
InnoDB表文件含义：.frm表定义，表空间数据和日志文件

ex：

-- 显示存储引擎的状态信息
SHOW ENGINES 
-- 显示存储引擎的日志或状态信息
SHOW ENGINE 引擎名 {LOGS|STATUS} 
-- 创建一个名为student的临时表，包含名称，学号，性别等信息
create temporary table if not exists student(
 id int not null auto_increment,
 name varchar(20) not null default "" comment "这是学生名字",
 code varchar(20) not null default "" unique key comment "学生学号",
 gender varchar(6) not null default "male" comment "性别",
 primary key(id)
) engine = innodb charset=utf8 comment = "学生信息表";

---创建一个名为course的临时表(名称)，并让主键自增长从1001开始
create temporary table if not exists course(
    id int not null auto_increment,
    name varchar(10) not null,
    unique key(name),
    primary key(id),
    check( id >= 1001)
) engine=innodb charset=utf8 comment="课程表" auto_increment = 1001

--创建一个名为student_course临时表(student_id,course_id)
create temporary table if not exists student_course(
  student_id int not null,
  course_id int not null,
  foregin key(student_id) references student(id),
  foregin key(course_id) references course(id)
) engine = innodb charset=utf8 comment="学生课程中间表，学生和课程之间是多对多的关系";

显示表信息命令

显示表信息命令格式如下：

命令格式|作用
---|------|------
show tables|显示当前数据库下的所有表
show tables [like 'pattern']|显示当前数据库下匹配到的表
show tables from 数据库名称|显示某数据库下全部表名称
show tables from 数据库名 [like 'pattern']|显示某数据库下匹配到的表
show create table 表名称|显示表的详细信息
desc 表名| 显示表信息
describe 表名|显示表信息
explain 表名|显示表信息
show columns from 表名 [like 'pattern']|显示表信息
show table status [from 数据库名] [like 'pattern']|显示表信息
ex:

--显示当前数据库下的所有表
show tables;
--显示当前数据库下匹配到d的表
show tables like '%d%';
--显示demo数据库下全部表名称
show tables from demo;
--显示demo数据库下表名含有d的表
show tables from demo like '%d%';
--显示student表的详细信息
show create table student;
desc student;
describe student;
explain student;
--显示表student所有列信息
show columns from student;
--显示表student包含d的列信息
show columns from student like '%d%';
--显示demo数据库所有表的信息
show table status from demo；
--显示demo数据库表名包含d的所有表信息
show table status from demo like "%d%";

修改表信息的命令

• 修改表本身的选项
  命令格式：alert table if exists 表名 表选项
  ex:

--修改student表自增长值、字符集、描述信息等
alter table if exists student auto_increment=10 charset=gbk comment="学生信息";

• 表重命名、将表迁移到某个数据库
  命令格式: rename table old_table to [数据库.]new_table
  ex:

--把student表名改为new_student
rename table student to new_student;
--把new_student表移动到数据库demo2中
rename table new_student to demo2.student;

• 修改表字段信息
  命令格式：alter table 表名 操作
  操作列表如下：

操作命令格式	描述
add [column] 字段(dataType 约束) first	增加字段，表示增加在第一个位置
add [column] 字段(类型 约束) after 字段	增加该字段在某个字段后面
add primary key(字段名,...)	创建主键
add unique [索引名] (字段名)	创建唯一索引
add index [索引名] (字段名)	创建普通索引
add foreign key(字段名) references 表名(字段名)	创建外键
drop [column] 字段名	删除字段
modify [column] 字段名 字段属性	对字段属性进行修改
change [column] 原字段名 新字段名 字段属性	对字段名修改
drop primary key	删除主键(删除主键前需删除其auto_increment属性)
drop index 索引名	删除索引
drop foreign key 外键	删除外键

ex:

--向student表中添加birthday字段并放在字段id之后
alter table student add column birthday datetime default now() not null after id;
--向student表添加idcard字段并放在第一个位置
alter table student add idcard varchar(18) not null first;
--删除student表主键
alter table student drop primary key;
--为student表增加主键
alter table student add primary key(id);
--student表身份证信息创建索引
alter table student add unique 'idcard'(idcard);
--删除student表中索引名为idcard的索引
alter table student drop index idcard;
--student表身份证信息创建索引
alter table student add index 'idcard'(idcard);
--student_course表增加外键
alter table student_course add foreign key(student_id) references (id);
--student表删除idcard字段
alter table student drop idcard;
--修改student表birthday属性
alter table student modify birthday date  default current_date not null;
--修改student表birthday属性名为birth
alter table student change column birthday birth; 

删除表

命令格式：drop table [if exists] 表名

ex:

--删除student表
drop table if exists student;

清空表数据

命令格式：truncate [table] 表名

ps:truncate清空表是先删除表然后再重新创建表，自增长值从默认开始

ex:

--清空course表中数据
truncate table course;

复制表结构

命令格式: create table 表名 like 要复制的表名

ex:

--创建course副表
create table course course_temp like course;

复制表结构和数据

命令格式:create table 表名 [as] select * from 要复制的表名

ex:

--复制表course结构及数据到course_temp2
create table course_temp2 as select * from course;

检查表是否有错误

命令格式:check table 表名,.......

ex:

--检查表student，course是否有错误
check table student,course;

优化表

命令格式：OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ....

修复表

命令格式：REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ... [QUICK] [EXTENDED] [USE_FRM]

分析表

命令格式:ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

ps:mysql表的优化、修复、分析针对于存储引擎为MyISAM和ARCHIVE表才起作用