MySQL CREATE table and delete table

Source: Internet
Author: User
Tags mysql create mysql create table

MySQL Create and delete tables

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 INTone), name VARCHAR (255), birthday DATE);

To view the MySQL creation table:

SHOW CREATE table person; CREATE TABLE ' person ' (  int(one) DEFAULT NULL,  ' name ' varchar (255  default NULL,  date  default 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( One)      | NULL |     YES | |       NULL | |Select, Insert,update,references | || name | varchar255) | 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), name VARCHAR (255), Birthday DATE);

when you create a table, you can use the Temporary keywords. thetemporary table is visible only in the current connection situation. When the connection is closed, thetemporary 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 can prevent errors from occurring.

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

Note that the structure of the original table and CREATE TABLE is not validated if the structure of the table represented in the statement is the same. Note: If you are in CREATE TABLE ... If not EXISTSis used in a SELECT statement, the record selected by the Select section is inserted regardless ofwhether the table already exists .

in the CREATE TABLE the end of the statement adds a SELECT statement to create a table based on a table

CREATE TABLE new_tbl SELECT * from ORIG_TBL;

Note that with SELECT statement creates columns attached to the right side of the table, rather than overwriting them on the table

Mysql> SELECT * from foo; 1 |+---+mysql> CREATE TABLE Bar (M INT) SELECT n from foo;mysql> select * from bar;
    +------+---+| M    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;

depending on the definition of the other table (including all the column properties and indexes defined in the original table), use the like Create an empty 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,  Char ()  not NULL,char(+) is not null,  ' age ' smallint ( 6 ) NOT NULL,  ' Townid ' smallint (6) is not NULL,  PRIMARY KEY (' Peopleid '),  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;

MySQL CREATE table and delete table

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.