MySQL 学习记录(2)

外键约束

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

CASCADE : 父表更新/删除记录时，子表也更新/删除记录

NOT NULL : 父表更新/删除记录时，子表设置为NULL

RESTRICT : 父表更新/删除记录时，报错

NO ACTION : 同RESTRICT

外键列和参照列必须创建过索引

​ 外键列没有索引，mysql会自动创建索引

[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

主键默认带有索引功能！！！

使用 SHOW INDEXES FROM tb_name \G;来查看表中的索引

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;