mysql常用命令

显示表的索引：SHOW INDEXES FROM TABLE

外键约束：

CASCADE 从父表删除或更新时自动删除或更新子表中匹配的行

SET NULL 从父表删除或更新，并设置字表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL

RESTRICT 拒绝对父表的删除或更新操作

NO ACTION 标准SQL的关键字，在mysql中与RESTRICT相同

添加单列

ALTER TABLE tbl_name ADD [COLUMN] column_name column_definition [FIRST|AFTER column_name]

添加多列

ALTER TABLE tbl_name ADD [COLUMN] (col_name column_definition,....)

删除列

ALTER TABLE tbl_name DROP [COLUMN] col_name

删除多列

ALTER TABLE tbl_name DROP [COLUMN] col_name,DROP col_name

添加主键约束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name)

eg: ALTER TABLE user ADD CONSTRAINT PK_user_id PRIMARY KEY (id);

删除主键约束

ALTER TABLE tbl_name DROP PRIMERY KEY 

eg:ALTER TABLE user DROP PRIMARY KEY

添加唯一约束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,.....)

eg:ALTER TABLE user ADD UNIQUE (username)

删除唯一约束

ALTER TABLE tbl_name DROP {INDEX|KEY} index_name

eg: SHOW INDEXES FRON user\G;

ALTER TABLE user DROP INDEX username; (删除约束并不是删除字段) 

添加外键约束

ALTER TABLE tbl_name ADD [CONSTARAINT[symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition 

eg:ALTER TABLE user ADD FOREIGN KEY (aid) REFERENCES address (id);

添加外键必须数据类型相似,相同的存储引擎,必须在字段上创建索引

删除外键约束

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol

eg: SHOW CREATE TABLE user; (查看约束的名称)

ALTER TABLE user DROP FOREIGN KEY user_ibfk_1 ;

添加删除默认约束

ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal| DROP DEFAULT}

eg: ALTER TABLE user ALTER age SET DEFAULT 12;

eg:ALTER TABLE user DROP DEFAULT