MySQL数据表操作

数据表

数据表在数据库中用于存储数据，表名要求是唯一的，不允许包含特殊字符。

查看数据库中的所有数据表

SHOW [FULL] TABLES [{FROM|IN} db_name] [LIKE 'pattern' | WHERE expr];

可以使用like或者where条件限制查询结果

创建数据表

CREATE TABLE [IF NOT EXISTS] tbl_name (字段名称 字段属性 [完整性约束条件], ...) ENGINE=存储引擎 CHARSET=编码方式

约束条件：

1. UNSIGNED：无符号位，长度从0开始
2. ZEROFILL：填充0，当数据的显示长度不足时，会在字段值前补充0，同时字段会自动添加UNSIGNED
3. NOT NULL：非空约束，插入数据不允许为空
4. DEFAULT：默认值，如果插入记录为空时会按照默认值插入
5. PRIMARY KEY：主键约束，记录标识的唯一性，值不能重复，一个表只能存在一个主键，作为主键的字段不能为空
6. AUTO_INCREMENT：自增长，只能用于数值类型的字段，配合索引使用。起始默认值为1，每次增长1
7. UNIQUE KEY：唯一约束，可以由多个字段组成，作为唯一索引的字段不能重复，NULL除外
8. FOREIGN KEY：外检约束

CREATE TABLE IF NOT EXISTS tb_employee (
    no TINYINT AUTO_INCREMENT KEY,
    id VARCHAR(4) NOT NULL UNIQUE KEY,
    name VARCHAR(4) NOT NULL UNIQUE KEY,
    age INT NOT NULL DEFAULT 18,
    salary INT,
    department VARCHAR(100)
) ENGINE = innoDB, DEFAULT CHARSET = utf8;

除了正常用CREATE语句设置字段建表以外还可以复制其他表的表结构。例如

CREATE TABLE tb_employee1 LIKE tb_employee;

则会创建一张与tb_employee结构相同的空表tb_employee1

查看建表语句

SHOW CREATE TABLE tbl_name;

查看表结构

DESC|DESCRIBE tbl_name;
SHOW COLUMNS FROM tbl_name;

字段名	描述
Field	字段描述
Type	字段类型即范围
Null	是否为空值
Key	主键约束/唯一约束/外键
Default	默认值
Extra	其他约束

删除数据表

DROP TABLE tbl_name;

表结构操作

添加、删除字段

ALTER TABLE tbl_name 
ADD (字段名称 字段属性 [约束条件] [FIRST|AFTER 字段名称], ...);

MySQL可以在添加字段时选择添加的位置

ALTER TABLE tbl_name DROP 字段名;

添加、删除默认值

ALTER TABLE tbl_name ALTER 字段名 SET DEFAULT 默认值;
ALTER TABLE tbl_name ALTER 字段名 DROP DEFAULT;

修改字段名称、字段类型、字段属性、约束条件

-- 修改字段属性
ALTER TABLE tbl_name MODIFY 字段名 字段类型 字段属性 [约束条件];

-- 修改字段名称
ALTER TABLE tbl_name CHANGE 原字段名 新字段名 字段类型 字段属性 [约束条件];

修改属性的同时也能移动字段的位置，可以添加AFTER 字段名移动位置
如果只修改字段的类型，字段的属性和约束条件也要写在语句中，否则会将其他属性也修改

例如：将age字段放到department字段后面，如果只写ALTER TABLE tb_employees MODIFY age int AFTER department;则效果如下图

age字段的默认值和是否为空属性也会被删掉。

增加、删除主键

ALTER TABLE tbl_name ADD PRIMARY KEY (字段名);
ALTER TABLE tbl_name DROP PRIMARY KEY;

对于存在的AUTO_INCREMENT，需要先将该属性删除才能删除主键

增加、删除唯一索引

ALTER TABLE tbl_name ADD UNIQUE KEY|INDEX [IDX_NAME] (COLUMN,...);
ALTER TABLE tbl_name DROP UNIQUE IDX_NAME;

在添加唯一索引时若不指定索引名称，会按字段名称命名索引

如果没有主键，唯一索引则会作为主键存在

修改数据表名

ALTER TABLE tbl_name 
RENAME [TO|AS] new_tbl_name

RENAME TABLE tbl_name TO new_tbl_name;

修改AUTO_INCREMENT的值

ALTER TABLE tbl_name AUTO_INCREMENT=值