大师网-带你快速走向大师之路 解决你在学习过程中的疑惑,带你快速进入大师之门。节省时间,提升效率

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表才起作用