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

-- ALTER TABLE 基本语法

ALTER TABLE table_name　changes;

-- changes 具体的更改命令

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;

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

-- 为附表插入相同数据

INSERT INTO birds_new 
SELECT * FROM rookery.birds;

-- 建表同时完全复制表

CREATE TABLE birds_new LIKE rookery.birds
INSER INTO birds_new
SELECT * FROM rookery.birds;

-- DROP COLUMN
ALTER TABLE birds_new
DROP COLUMN wing_id;

-- CHANGE COLUMN old_colname new_colname type

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

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

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

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

-- 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;

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

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

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

-- 添加索引

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