MySQL addition, deletion, query, modification, and usage example _ MySQL

This article mainly introduces the usage examples of adding, deleting, querying, and modifying MySQL statements. it is an induction of the basic knowledge points of MySQL learning. For more information, see 1. create a column.

alter table tablename add colname type not null default '0′;

Example:

  alter table mmanapp_mmanmedia add appid_id integer not null default 372;

2. delete columns

  alter table tablename drop column colname;

Example:

  alter table mmanapp_mmanmedia drop column appid_id;

3. create a foreign key association in an existing column

ALTER TABLE yourtablename  ADD [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, …)  REFERENCES tbl_name (index_col_name, …)  [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]  [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

Example:

ALTER TABLE mmanapp_mmanmedia ADD CONSTRAINT fk_mdappid FOREIGN KEY(appid_id)

4. delete foreign key Association:

  ALTER TABLE yourtablename DROP FOREIGN KEY fk_symbol;

Example:

 ALTER TABLE mmanapp_mmanmedia DROP FOREIGN KEY fk_mdappid

Two documents are attached:
I. mysql related operations on columns and tables

Add primary key

alter table tabelname add new_field_id int(5) unsigned default 0 not null auto_increment ,add primary key (new_field_id);

Add a new column

alter table infos add ex tinyint not null default '0′;

Delete column

alter table t2 drop column c;

Rename a column/change the column type

alter table t1 change a b integer;alter table t1 change b b bigint not null;alter table infos change list list tinyint not null default '0′;

Rename a table

alter table t1 rename t2;

Add index

Mysql> alter table tablename change depno int (5) not null; mysql> alter table tablename add index name (field name 1 [, field name 2…]); Mysql> alter table tablename add index emp_name (name );

Index with primary keywords

mysql> alter table tablename add primary key(id);

Add an index with unique conditions

mysql> alter table tablename add unique emp_name2(cardnumber);

Delete an index

mysql>alter table tablename drop index emp_name;

2. add/delete constraints to/from tables

InnoDB allows you to use alter table to add a new foreign key constraint to a TABLE:

ALTER TABLE yourtablename  ADD [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, …)  REFERENCES tbl_name (index_col_name, …)  [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]  [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

Remember to create the required index first. You can also use alter table to add a self-referenced foreign key constraint to a TABLE.
InnoDB also supports the use of alter table to remove foreign keys:

ALTER TABLE yourtablename DROP FOREIGN KEY fk_symbol;

When creating a foreign key in the current year, if the foreign key clause contains a CONSTRAINT name, you can reference that name to remove the foreign key. In addition, when the foreign key is created, the fk_symbol value is internally guaranteed by InnoDB. When you want to remove a foreign key, use the show create table statement to locate the tag. Example:

mysql> SHOW CREATE TABLE ibtest11c\G*************************** 1. row ***************************    Table: ibtest11cCreate Table: CREATE TABLE ibtest11c ( A int(11) NOT NULL auto_increment, D int(11) NOT NULL default '0′, B varchar(200) NOT NULL default ”, C varchar(175) default NULL, PRIMARY KEY (A,D,B), KEY B (B,C), KEY C (C), CONSTRAINT 0_38775 FOREIGN KEY (A, D)REFERENCES ibtest11a (A, D)ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT 0_38776 FOREIGN KEY (B, C)REFERENCES ibtest11a (B, C)ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=INNODB CHARSET=latin11 row in set (0.01 sec) mysql> ALTER TABLE ibtest11c DROP FOREIGN KEY 0_38775;

The InnoDB parser allows you... REFERENCES... Clause uses '(backticks) to enclose the table and column names. The InnoDB parser also takes into account the settings of lower_case_table_names system variables.
InnoDB returns the foreign key definition of a TABLE as part of the output of the show create table statement:

SHOW CREATE TABLE tbl_name;

From this version, mysqldump also generates the correct table definition to the dump file without forgetting the foreign key.
You can display the foreign key constraints for a table as follows:

SHOW TABLE STATUS FROM db_name LIKE ‘tbl_name';

The foreign key constraint is listed in the output Comment column.
When a foreign key check is executed, InnoDB sets a shared row-level lock for the child or parent record it looks after. InnoDB immediately checks the foreign key constraints and checks whether the transaction commit is delayed.
To make it easier to reload the dump file to a table with a foreign key relationship, mysqldump automatically includes a statement in the dump output to set FOREIGN_KEY_CHECKS to 0. This avoids issues related to tables that have to be reloaded in special order when the dump is reloaded. You can also manually set this variable:

mysql> SET FOREIGN_KEY_CHECKS = 0;mysql> SOURCE dump_file_name;mysql> SET FOREIGN_KEY_CHECKS = 1;

If the dump file contains a table with an incorrect sequence of foreign keys, the table is imported in any order. This will speed up the import operation. Setting FOREIGN_KEY_CHECKS to 0 is also useful for ignoring the foreign key restriction in the load data and alter table operations.
InnoDB does not allow you to delete a table referenced by the foreign key table constraints, unless you SET FOREIGN_KEY_CHECKS = 0. When you remove a table, the constraints defined in its creation statement are also removed.
If you recreate a removed table, it must have a definition that complies with the foreign key constraints that also reference it. It must have the correct column name and type, and as mentioned above, it must have an index on the referenced key. If these conditions are not met, MySQL returns error code 1005 and points to errno 150 in the error message string.