MySQL——常用更改表的命令汇总

必修的改表技能

-- 所有的改表结构操作都可通过ALTER TABLE 实现

-- ALTER TABLE 基本语法

ALTER TABLE table_name　changes;

-- changes 具体的更改命令

　　1.1 增加列

ALTER TABLE bird_families
ADD COLUMN order_id INT;

-- 将新增的列加在最前面用FIRST

ALTER TABLE bird_families
ADD COLUMN order_id INT FIRST;

-- 将新增的列加在某列的后面用AFTER

ALTER TABLE birds_new
ADD COLUMN wing_id CHAR(2) AFTER family_id;

　　1.2 复制表

CREATE TABLE test.birds_new LIKE birds; # 不复制主键和自增

-- 为附表插入相同数据

INSERT INTO birds_new 
SELECT * FROM rookery.birds;

-- 建表同时完全复制表

CREATE TABLE birds_new LIKE rookery.birds;

INSERT INTO birds_new
SELECT * FROM rookery.birds;

　　1.3 删除列

-- DROP COLUMN
ALTER TABLE birds_new
DROP COLUMN wing_id;

　　1.4 改列名与类型

-- CHANGE COLUMN old_colname new_colname type

ALTER TABLE birds_new
CHANGE COLUMN common_name common VARCHAR(255);

-- 无论是否只改列名或类型都需要写上新列名的类型

　　1.5 更改数据

-- 更改数据用 UPDATE table_name SET ... WHERE...

UPDATE birds_new SET endangered =0
WHERE bird_id IN(1,2,4,5);

　　2.选修的改表技能

　　　　2.1 更改列的默认值

-- CHANGE

ALTER TABLE birds_new
CHANGE COLUMN 
endangered conservation_status_id INT DEFAULT 8;

-- ALTER

ALTER TABLE birds_new
ALTER conservation_status_id SET DEFAULT 7;

-- 删除默认值
ALTER TABLE birds_new
ALTER conservation_status_id DROP DEFAULT;

　　　　2.2 设置AUTO_INCREMENT的值

ALTER TABLE birds
AUTO_INCREMENT =10; #重10开始而不是默认从1开始

　　　　2.3 重命名表

-- REMANE TABLE 重命名表同时移动表

RENAME TABLE rookery.birds TO rookery.birds_old,
test.birds_new TO rookery.birds;

　　　　2.4 索引

　　　　索引与其所基于的列是分离的，索引不是列。

　　　 ·以下显示索引的名字与human_id相同，但不等同。

　　　　·EXPLAIN 显示语句是如何检索数据的。

　　·key通常会列出语句实际用到的索引，这里数据较少固显示NULL

-- 添加索引

ALTER TABLE birdwatchers.humans
ADD INDEX human_names (name_last,name_first);

-- 添加UNIQUE索引

ALTER TABLE conservation_status
ADD UNIQUE INDEX(status_id);

-- 删除索引用DROP INDEX index_name