外鍵約束

FOREIGN KEY (NAME) REFERENCES tb_name(name) ON DELETE ON UPDATE;

CASCADE : 父表更新/刪除記錄時(shí)，子表也更新/刪除記錄

NOT NULL : 父表更新/刪除記錄時(shí)，子表設(shè)置為NULL

RESTRICT : 父表更新/刪除記錄時(shí)，報(bào)錯(cuò)

NO ACTION : 同RESTRICT

外鍵列和參照列必須創(chuàng)建過(guò)索引

? 外鍵列沒有索引，mysql會(huì)自動(dòng)創(chuàng)建索引

[CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name, ...) REFERENCES tbl_name (index_col_name,...) [ON DELETE reference_option][ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION

主鍵默認(rèn)帶有索引功能?。?！

使用 SHOW INDEXES FROM tb_name \G;來(lái)查看表中的索引

ALTER TABLE

更改表名

ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name;

RENAME TABLE tbl_name TO new_tbl_name;

插入

單列：ALTER TABLE tbl_name ADD [COLUMN] column_name column_definition [FIRST|AFTER column_name]; (FIRST 所有列之前)

多列：ALTER TABLE tbl_name ADD [COLUMN] (column_name column_definition,...);

刪除

ALTER TABLE tbl_name DROP [COLUMN] column_name;

ALTER TABLE tbl_name DROP [COLUMN] column_name,ADD [COLUMN] column_name;

添加約束

ALTER TABLE tbl_name ADD [CONSTRAINT [constraint_name]] PRIMARY KEY (column_name,...);
ALTER TABLE tbl_name ADD [CONSTRAINT [constraint_name]] UNIQUE [KEY|INDEX][index_name] [index_type] (column_name,...);
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol_name]] FOREIGN KEY (column_name) REFERENCES (column_name);
ALTER TABLE tbl_name ADD [COLUMN] column_name {SET DEFAULT literal|DROP DEFAULT}

刪除約束

ALTER TABLE tbl_name DROP PRIMARY KEY
ALTER TABLE tbl_name DROP INDEX column_name;
ALTER TABLE tbl_name DROP FOREIGN KEY symbol_name;
ALTER TABLE tbl_name ADD [COLUMN] column_name {SET DEFAULT literal|DROP DEFAULT}

更改列

ALTER TABLE tbl_name MODIFY [COLUMN] column_name column_definition [FIRST|AFTER column_name];

ALTER TABLE tbl_name CHANGE column_old_name column_new_name column_definition;