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