MySQL DDL statements

This article describes MySQL's more common DDL statements. Include the following:

• CREATE table

• ALTER TABLE

• drop table

• TRUNCATE TABLE
  

1. CREATE TABLE

Create TABLE is a built-in statement that looks at several examples:

• Declaring a primary key after a column name

  CREATE TABLE t1 (ID int primary key, name varchar (20));

  
  

• Declaring a primary key at the table level

  CREATE TABLE t1 (id int, name varchar (), primary key (ID));

  
  

• Multi-character Chini (can only be declared at the table level)

  CREATE TABLE t1 (id int, name varchar (), primary key (ID, name));

  
  

• Specifying indexes when building tables

  CREATE TABLE t1 (id int, name varchar, primary key (ID), index idx_t1_name (name));

  
  

• Specifying foreign keys when building tables

  CREATE table t3 (id int, name varchar, primary key (ID), Constraint CST_T1_FK foreign key (ID) references T2 (ID));
  

  
  

• Declare the properties of the table at the end of the table, such as the storage engine and character set

  CREATE TABLE t1 (id int, name varchar (), primary key (ID, name)) Engine=innodb default Charset=utf8;

• Use the If not EXISTS keyword. If the table does not exist then the table is created, and if it already exists, it is reported as a warning

  CREATE table if not EXISTS t1 (id int, name varchar (), primary key (ID));

  
  

• After completion of the table can be used desc T1, or show create table t1\g view the structure of the table, in general, the results of Desc is sufficient, but if you want to get the statement to build the table is best to use show create TABLE T1\g

2. ALTER TABLE

ALTER TABLE can modify the structure and properties of tables, and the following ALTER TABLE statements can be executed using commas separated in the same statement. Look at a few examples below:

• Add field

  ALTER TABLE T1 Add (column) Age Int;

  ALTER TABLE T1 Add (column) Age First;=======> add field to the beginning of the table

  ALTER TABLE T1 Add (column) age after Name;

   
  

• modifying fields

  ALTER TABLE T1 modify age tinyint; ========> Modifying field data types

  ALTER TABLE T1 modify age tinyint first; ========> Modify the field data type and move the field to the beginning of the table

  ALTER TABLE T1 modify age tinyint after ID; ========> Modifying field data types and moving fields to field IDs

  ALTER TABLE T1 change age student_age tinyint; ========> modifying field names and data types

• Delete a field

  ALTER TABLE T1 drop (column) age;

  
  

• Add index

  ALTER TABLE T1 add index idx_t1_name (name); =======> Add a normal index, the index keyword can be replaced with Key,idx_t1_name as the index name

  ALTER TABLE t1 add unique index idx_t1_name (name); =======> Adding a unique index

• Delete Index

  ALTER TABLE T1 DROP INDEX idx_t1_name;

• Delete primary key

  ALTER TABLE add drop PRIMARY key;

  
  

• Add primary Key

  ALTER TABLE Add primary key (ID);
  

  
  

• Add foreign key

  ALTER TABLE t1 ADD constraint CST_T1_FK foreign key (ID) references T2 (ID);

  
  

• Delete foreign key

  ALTER TABLE T1 drop foreign key cst_t1_fk;
  

  
  

• Rename a table (three statements below are all possible)

  Rename table T1 to T2;

  ALTER TABLE t1 rename T2;

  ALTER TABLE T1 rename to T2;

  
  

• Modifying the storage engine or character set

  ALTER TABLE T1 ENGINE=MYISAM;

  ALTER TABLE T1 default charset=latin1;
  

3. Drop table

The meaning is simple, delete the table.

drop table T1; =========> directly delete the table, if not the table will be an error

drop table if exists T1; ========> if not the table will not report an error, only a warning

4. TRUNCATE TABLE

TRUNCATE TABLE is a command that truncates tables, which is equivalent to deleting the table and rebuilding it immediately.

TRUNCATE TABLE T1;

This article is from the "Trikker" blog, make sure to keep this source http://trikker.blog.51cto.com/7478890/1561622

MySQL DDL statements