"SQL chapter" "SQL Statement grooming:--based on MySQL5.6" "has been combed: ALTER table parsing"

ALTER TABLE Parse instance: sql:1.   Add columns 2. Add columns, adjust column order 3. Increase the index 4. Increase the constraint 5. Increase the full-text index FULL-TEXT6. Change the default value of the column 7. Change the column name (type, order) 8. Do not change the column name 9. Delete Column 10. Delete the primary key 11. Delete Index 12. Change the character set Create a table

CREATE TABLE T1 (a integer,b CHAR (10));

1. Add Column format:

ADD [COLUMN] (col_name column_definition,...)

Example:

ALTER TABLE T1 ADD COLUMN c TIMESTAMP;

Mysql> desc t1;+-------+-----------+------+-----+-------------------+-----------------------------+| Field | Type      | Null | Key | Default           | Extra                       |+-------+-----------+------+-----+-------------------+-----------------------------+| a     | int (11)   | YES  |     | NULL              | |                             | b     | char (TEN)  | YES  |     | NULL              | |                             | c     | timestamp | NO   |     | Current_timestamp | On update current_timestamp |+-------+-----------+------+-----+-------------------+-----------------------------+

　　

2. Add columns, adjust column order {only first and after} formats:

ADD [COLUMN] col_name column_definition [First | After Col_name]

Example:

ALTER TABLE t1 ADD COLUMN D int after A;

Mysql> desc t1;+-------+-----------+------+-----+-------------------+-----------------------------+| Field | Type      | Null | Key | Default           | Extra                       |+-------+-----------+------+-----+-------------------+-----------------------------+| a     | int (11)   | YES  |     | NULL              |                             | | d     | int (one)   | YES  |     | NULL              | |                             | b     | char (TEN)  | YES  |     | NULL              | |                             | c     | timestamp | NO   |     | Current_timestamp | On update current_timestamp |+-------+-----------+------+-----+-------------------+-----------------------------+

　　

3. Increase the index format:

ADD {index| KEY} [Index_name] [Index_type] (Index_col_name,...) [Index_option] ...

Example:

ALTER TABLE T1 ADD INDEX idx_d (d); (column D increases the index)

Table structure:

CREATE TABLE ' t1 ' (  ' a ' int (one) default null,  ' d ' int (one) default null,  ' B ' char (TEN) default null,  ' C ' Tim Estamp not NULL default current_timestamp on UPDATE current_timestamp,  KEY ' idx_d ' (' d ')) Engine=innodb default Charse T=latin1

　　

4. Adding constraints

• Primary Key
• Unique Key
• Foreign Key

Create a new table: S1,s2,

CREATE TABLE S1 (id int,name VARCHAR (), Address CHAR (20)); CREATE TABLE s2 (id int,s1_id INT);

Example 1:

ALTER TABLE S1 ADD PRIMARY KEY (ID); (column is set to NOT NULL)

Mysql> desc s1;+---------+-------------+------+-----+---------+-------+| Field   | Type        | Null | Key | Default | Extra |+---------+-------------+------+-----+---------+-------+| ID      | int     | NO   | PRI | 0       |       | | name    | varchar (10) | YES  |     | NULL    |       | | address | char |    YES  |     | NULL    |       | +---------+-------------+------+-----+---------+-------+

Example 2:

ALTER TABLE S1 ADD UNIQUE KEY (NAME);

Table structure:

Mysql> desc s1;+---------+-------------+------+-----+---------+-------+| Field   | Type        | Null | Key | Default | Extra |+---------+-------------+------+-----+---------+-------+| ID      | int     | NO   PRI | 0       | |       | name    | varchar (10) | YES  UNI | NULL    |       | | address | char |    YES  |     | NULL    |       | +---------+-------------+------+-----+---------+-------+

Format:

ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition

Example 3:

ALTER TABLE S2 ADD FOREIGN KEY (s1_id) REFERENCES s1 (ID); ID column pointing to table S1

Table structure:

CREATE TABLE ' s2 ' (  ' id ' int (one) default null,  ' s1_id ' int (one) default null,  KEY ' s1_id ' (' s1_id '),  CONST Raint ' S2_ibfk_1 ' FOREIGN KEY (' s1_id ') REFERENCES ' s1 ' (' id ')) engine=innodb DEFAULT charset=latin1

　　

5. Increase full-text indexing Full-text

ALTER TABLE T1 ADD Fulltext Full_f (f);

Table structure:

CREATE TABLE ' t1 ' (  ' a ' int (one) not null default ' 0 ',  ' d ' int (one) default NULL,  ' B ' char (TEN) ' Default null,
   
     ' C ' timestamp not NULL DEFAULT current_timestamp on UPDATE current_timestamp,  ' F ' text,  PRIMARY KEY (' a '),  Key ' Idx_d ' (' d '),  fulltext key ' Full_f ' (' F ')) Engine=innodb DEFAULT charset=latin1
   

　　

6. Change the default value format for columns:

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

Example 1:

Alter TABLE T1 ALTER COLUMN a SET DEFAULT ' 1 ';

Example 2:

Alter TABLE T1 ALTER COLUMN d DROP DEFAULT;

CREATE TABLE ' t1 ' (  ' a ' int (one) NOT null default ' 1 ',  ' d ' int (one) ',  ' B ' char (TEN) default NULL,  ' C ' timestam P not NULL DEFAULT current_timestamp on UPDATE current_timestamp,  ' F ' text,  PRIMARY key (' a '),  key ' Idx_d ' (' d '),  fulltext KEY ' Full_f ' (' F ')) Engine=innodb DEFAULT charset=latin1

　　

7. Change the column name (type, order)

CREATE TABLE T1 (a integer,b CHAR (10));

Format:

Change [COLUMN] old_col_name new_col_name column_definition [first| After Col_name]

Example:

ALTER TABLE T3 Change a a_1 int. not NULL after B;

　　

8. Do not change the column name format:

MODIFY [COLUMN] col_name column_definition [First | After Col_name]

Example:

ALTER TABLE T4 MODIFY a INT not Null,change b C VARCHAR (20);

　　

9. Delete Column formatting:

DROP [COLUMN] Col_name

Example:

ALTER TABLE T1 DROP COLUMN A;

　　

10. Delete the primary key format:

DROP PRIMARY KEY

Example:

ALTER TABLE T1 DROP PRIMARY KEY;

　　

11. Delete the index format:

DROP {index| KEY} index_name

Example:

ALTER TABLE T1 DROP INDEX idx_d;

　　

12. Delete the constraint format:

DROP FOREIGN KEY Fk_symbol

Example:

ALTER TABLE S2 DROP FOREIGN KEY s2_ibfk_1

S2 Table Structure:

CREATE TABLE ' s2 ' (  ' id ' int (one) default null,  ' s1_id ' int (one) default null,  KEY ' s1_id ' (' s1_id '),  CONST Raint 's2_ibfk_1' FOREIGN KEY (' s1_id ') REFERENCES ' s1 ' (' id ')) engine=innodb DEFAULT charset=latin1

　　

13. Change the table name format:

RENAME [To|as] New_tbl_name

Example:

ALTER TABLE t1 rename to T2;

　　

14. Change the character set format:

[DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]

Example:

ALTER TABLE t1 CHARACTER SET = UTF8;

　　

"SQL chapter" "SQL Statement grooming:--based on MySQL5.6" "has been combed: ALTER table parsing"