MySQL Create/delete table example

Create a table

The Simple Way

CREATE TABLE Person (number INT (one), name VARCHAR (255), birthday DATE);

or a

CREATE TABLE IF not EXISTS person (number INT one), name VARCHAR (255), birthday DATE);

To view the MySQL creation table:

SHOW CREATE table person; CREATE TABLE ' person ' (  ' number ' int (one) default null,  ' name ' varchar (255) default NULL,  ' birthday ' date DEFA ULT NULL) Engine=myisam DEFAULT Charset=utf8;

To view all the columns in the table:

SHOW full COLUMNS from person;+----------+--------------+-----------------+------+-----+---------+-------+-------- -------------------------+---------+| Field    | Type         | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |+----------+--------------+-----------------+------+-----+---------+-------+--------------------------- ------+---------+| Number   | int      | NULL            | YES  |     | NULL    |       | select,insert,update,references |         | | name     | varchar (255) | utf8_general_ci | YES  |     | NULL    |       | select,insert,update,references |         | | birthday | date         | NULL            | YES  |     | NULL    |       | select,insert,update,references |         | +----------+--------------+-----------------+------+-----+---------+-------+---------------------------------+- --------+

Create a temporary table

CREATE Temporary TABLE Temp_person (number INT (one), name VARCHAR (255), birthday DATE);

When creating a table, you can use the TEMPORARY keyword. The temporary table is visible only in the current connection situation. When the connection is closed, the temporary table is automatically canceled. This means that two different connections can use the same temporary table name, while two temporary tables do not conflict with each other and do not conflict with the original non-temporal table with the same name. (The original table is hidden until the temporary table is canceled.) You must have create temporary tables permission to create a temporary table.

If the table already exists, use the keyword if not exists to prevent an error from occurring.

CREATE TABLE IF not EXISTS person2 (number INT (one), name VARCHAR (255), birthday DATE);

Note that the structure of the original table is the same as the structure of the table represented in the CREATE TABLE statement, which is not verified. Note: If you are in Create TABLE ... If not EXISTS is used in a SELECT statement, the record selected by the Select section is inserted regardless of whether the table already exists.

Add a SELECT statement at the end of the CREATE TABLE statement to create a table based on a table

CREATE TABLE new_tbl SELECT * from ORIG_TBL;

Note that columns created with the SELECT statement are appended to the right side of the table instead of being overwritten on the table

Mysql> SELECT * FROM foo;+---+| N |+---+| 1 |+---+mysql> CREATE TABLE bar (M INT) select n from foo;mysql> select * from bar;+------+---+| M    | n |+------+---+| NULL | 1 |+------+---+

You can also explicitly specify a type for a generated column

CREATE TABLE foo (a TINYINT not NULL) SELECT b+1 as a from bar;

Use like to create an empty table based on the definition of other tables, including all the column properties and indexes defined in the original table:

CREATE TABLE new_tbl like orig_tbl;

Create a table with a primary key, a unique index, and a normal index:

CREATE TABLE ' People ' (  ' Peopleid ' smallint (6) NOT NULL auto_increment,  ' FirstName ' char (a) NOT NULL,  ' LastName ' char (NOT NULL),  ' age ' smallint (6) is not NULL,  ' Townid ' smallint (6) is not NULL,  PRIMARY KEY (' Peoplei d '),  UNIQUE key ' Unique_fname_lname ' (' FirstName ', ' LastName '),  key ' Fname_lname_age ' (' FirstName ', ' LastName ', ' age ');

Where Peopleid is the primary key, a unique index is established with the FirstName and LastName two columns, and a normal index is established in the Firstname,lastname,age three columns

Delete a table

DROP TABLE  tbl_name;

or a

DROP TABLE IF EXISTS tbl_name;

For more information about indexes, refer to: http://www.cnblogs.com/ggjucheng/archive/2012/11/04/2754128.html

MySQL Create/delete table example