MySQL Create/delete table example

Source: Internet
Author: User
Tags mysql create

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.