How to Create/Drop Database, Table_MySQL

bitsCN.com

SHOW ALL DATABASES

SHOW {DATABASES | SCHEMAS}    [LIKE '<em><code>pattern</code></em>' | WHERE <em><code>expr</code></em>]

SHOW DATABASES lists the databases on the MySQL server host.

CREATE DATABASE

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] <em><code>db_name</code></em>    [<em><code>create_specification</code></em>] ...<em><code>create_specification</code></em>:    [DEFAULT] CHARACTER SET [=] <em><code>charset_name</code></em>  | [DEFAULT] COLLATE [=] <em><code>collation_name</code></em>

CREATE DATABASE creates a database with the given name. To use this statement, you need the CREATE privilege for the database. CREATE SCHEMA is a synonym for CREATE DATABASE.

DROP DATABASE

DROP {DATABASE | SCHEMA} [IF EXISTS] <em><code>db_name</code></em>

DROP DATABASE drops all tables in the database and deletes the database. Be very careful with this statement! To use DROP DATABASE, you need the DROP privilege on the database. DROP SCHEMA is a synonym for DROP DATABASE.

SHOW ALL TABLES

SHOW [FULL] TABLES [{FROM | IN} <em><code>db_name</code></em>]    [LIKE '<em><code>pattern</code></em>' | WHERE <em><code>expr</code></em>]

SHOW TABLES lists the non-TEMPORARY tables in a given database.

DROP TABLE

DROP [TEMPORARY] TABLE [IF EXISTS]    <em><code>tbl_name</code></em> [, <em><code>tbl_name</code></em>] ...    [RESTRICT | CASCADE]

DROP TABLE removes one or more tables.

CREATE TABLE

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] <em><code>tbl_name</code></em>    (<em><code>create_definition</code></em>,...)    [<em><code>table_options</code></em>]    [<em><code>partition_options</code></em>]

Or:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] <em><code>tbl_name</code></em>    [(<em><code>create_definition</code></em>,...)]    [<em><code>table_options</code></em>]    [<em><code>partition_options</code></em>]    <em><code>select_statement</code></em>

Or:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] <em><code>tbl_name</code></em>    { LIKE <em><code>old_tbl_name</code></em> | (LIKE <em><code>old_tbl_name</code></em>) }

<em><code>create_definition</code></em>:    <em><code>col_name</code></em> <em><code>column_definition</code></em>  | [CONSTRAINT [<em><code>symbol</code></em>]] PRIMARY KEY [<em><code>index_type</code></em>] (<em><code>index_col_name</code></em>,...)      [<em><code>index_option</code></em>] ...  | {INDEX|KEY} [<em><code>index_name</code></em>] [<em><code>index_type</code></em>] (<em><code>index_col_name</code></em>,...)      [<em><code>index_option</code></em>] ...  | [CONSTRAINT [<em><code>symbol</code></em>]] UNIQUE [INDEX|KEY]      [<em><code>index_name</code></em>] [<em><code>index_type</code></em>] (<em><code>index_col_name</code></em>,...)      [<em><code>index_option</code></em>] ...  | {FULLTEXT|SPATIAL} [INDEX|KEY] [<em><code>index_name</code></em>] (<em><code>index_col_name</code></em>,...)      [<em><code>index_option</code></em>] ...  | [CONSTRAINT [<em><code>symbol</code></em>]] FOREIGN KEY      [<em><code>index_name</code></em>] (<em><code>index_col_name</code></em>,...) <em><code>reference_definition</code></em>  | CHECK (<em><code>expr</code></em>)<em><code>column_definition</code></em>:    <em><code>data_type</code></em> [NOT NULL | NULL] [DEFAULT <em><code>default_value</code></em>]      [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]      [COMMENT '<em><code>string</code></em>']      [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]      [<em><code>reference_definition</code></em>]<em><code>data_type</code></em>:    BIT[(<em><code>length</code></em>)]  | TINYINT[(<em><code>length</code></em>)] [UNSIGNED] [ZEROFILL]  | SMALLINT[(<em><code>length</code></em>)] [UNSIGNED] [ZEROFILL]  | MEDIUMINT[(<em><code>length</code></em>)] [UNSIGNED] [ZEROFILL]  | INT[(<em><code>length</code></em>)] [UNSIGNED] [ZEROFILL]  | INTEGER[(<em><code>length</code></em>)] [UNSIGNED] [ZEROFILL]  | BIGINT[(<em><code>length</code></em>)] [UNSIGNED] [ZEROFILL]  | REAL[(<em><code>length</code></em>,<em><code>decimals</code></em>)] [UNSIGNED] [ZEROFILL]  | DOUBLE[(<em><code>length</code></em>,<em><code>decimals</code></em>)] [UNSIGNED] [ZEROFILL]  | FLOAT[(<em><code>length</code></em>,<em><code>decimals</code></em>)] [UNSIGNED] [ZEROFILL]  | DECIMAL[(<em><code>length</code></em>[,<em><code>decimals</code></em>])] [UNSIGNED] [ZEROFILL]  | NUMERIC[(<em><code>length</code></em>[,<em><code>decimals</code></em>])] [UNSIGNED] [ZEROFILL]  | DATE  | TIME  | TIMESTAMP  | DATETIME  | YEAR  | CHAR[(<em><code>length</code></em>)]      [CHARACTER SET <em><code>charset_name</code></em>] [COLLATE <em><code>collation_name</code></em>]  | VARCHAR(<em><code>length</code></em>)      [CHARACTER SET <em><code>charset_name</code></em>] [COLLATE <em><code>collation_name</code></em>]  | BINARY[(<em><code>length</code></em>)]  | VARBINARY(<em><code>length</code></em>)  | TINYBLOB  | BLOB  | MEDIUMBLOB  | LONGBLOB  | TINYTEXT [BINARY]      [CHARACTER SET <em><code>charset_name</code></em>] [COLLATE <em><code>collation_name</code></em>]  | TEXT [BINARY]      [CHARACTER SET <em><code>charset_name</code></em>] [COLLATE <em><code>collation_name</code></em>]  | MEDIUMTEXT [BINARY]      [CHARACTER SET <em><code>charset_name</code></em>] [COLLATE <em><code>collation_name</code></em>]  | LONGTEXT [BINARY]      [CHARACTER SET <em><code>charset_name</code></em>] [COLLATE <em><code>collation_name</code></em>]  | ENUM(<em><code>value1</code></em>,<em><code>value2</code></em>,<em><code>value3</code></em>,...)      [CHARACTER SET <em><code>charset_name</code></em>] [COLLATE <em><code>collation_name</code></em>]  | SET(<em><code>value1</code></em>,<em><code>value2</code></em>,<em><code>value3</code></em>,...)      [CHARACTER SET <em><code>charset_name</code></em>] [COLLATE <em><code>collation_name</code></em>]  | <em><code>spatial_type</code></em><em><code>index_col_name</code></em>:    <em><code>col_name</code></em> [(<em><code>length</code></em>)] [ASC | DESC]<em><code>index_type</code></em>:    USING {BTREE | HASH | RTREE}<em><code>index_option</code></em>:    KEY_BLOCK_SIZE [=] <em><code>value</code></em>  | <em><code>index_type</code></em>  | WITH PARSER <em><code>parser_name</code></em><em><code>reference_definition</code></em>:    REFERENCES <em><code>tbl_name</code></em> (<em><code>index_col_name</code></em>,...)      [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]      [ON DELETE <em><code>reference_option</code></em>]      [ON UPDATE <em><code>reference_option</code></em>]<em><code>reference_option</code></em>:    RESTRICT | CASCADE | SET NULL | NO ACTION<em><code>table_options</code></em>:    <em><code>table_option</code></em> [[,] <em><code>table_option</code></em>] ...<em><code>table_option</code></em>:    ENGINE [=] <em><code>engine_name</code></em>  | AUTO_INCREMENT [=] <em><code>value</code></em>  | AVG_ROW_LENGTH [=] <em><code>value</code></em>  | [DEFAULT] CHARACTER SET [=] <em><code>charset_name</code></em>  | CHECKSUM [=] {0 | 1}  | [DEFAULT] COLLATE [=] <em><code>collation_name</code></em>  | COMMENT [=] '<em><code>string</code></em>'  | CONNECTION [=] '<em><code>connect_string</code></em>'  | DATA DIRECTORY [=] '<em><code>absolute path to directory</code></em>'  | DELAY_KEY_WRITE [=] {0 | 1}  | INDEX DIRECTORY [=] '<em><code>absolute path to directory</code></em>'  | INSERT_METHOD [=] { NO | FIRST | LAST }  | KEY_BLOCK_SIZE [=] <em><code>value</code></em>  | MAX_ROWS [=] <em><code>value</code></em>  | MIN_ROWS [=] <em><code>value</code></em>  | PACK_KEYS [=] {0 | 1 | DEFAULT}  | PASSWORD [=] '<em><code>string</code></em>'  | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}  | UNION [=] (<em><code>tbl_name</code></em>[,<em><code>tbl_name</code></em>]...)<em><code>partition_options</code></em>:    PARTITION BY        { [LINEAR] HASH(<em><code>expr</code></em>)        | [LINEAR] KEY(<em><code>column_list</code></em>)        | RANGE{(<em><code>expr</code></em>) | COLUMNS(<em><code>column_list</code></em>)}        | LIST{(<em><code>expr</code></em>) | COLUMNS(<em><code>column_list</code></em>)} }    [PARTITIONS <em><code>num</code></em>]    [SUBPARTITION BY        { [LINEAR] HASH(<em><code>expr</code></em>)        | [LINEAR] KEY(<em><code>column_list</code></em>) }      [SUBPARTITIONS <em><code>num</code></em>]    ]    [(<em><code>partition_definition</code></em> [, <em><code>partition_definition</code></em>] ...)]<em><code>partition_definition</code></em>:    PARTITION <em><code>partition_name</code></em>        [VALUES             {LESS THAN {(<em><code>expr</code></em> | <em><code>value_list</code></em>) | <code>MAXVALUE</code>}             |             IN (<em><code>value_list</code></em> | <em><code>value_list</code></em>)}]        [[STORAGE] ENGINE [=] <em><code>engine_name</code></em>]        [COMMENT [=] <em><code>'comment_text'</code></em> ]        [DATA DIRECTORY [=] '<code><em><code>data_dir</code></em></code>']        [INDEX DIRECTORY [=] '<code><em><code>index_dir</code></em></code>']        [MAX_ROWS [=] <em><code>max_number_of_rows</code></em>]        [MIN_ROWS [=] <em><code>min_number_of_rows</code></em>]        [(<em><code>subpartition_definition</code></em> [, <em><code>subpartition_definition</code></em>] ...)]<em><code>subpartition_definition</code></em>:    SUBPARTITION <em><code>logical_name</code></em>        [[STORAGE] ENGINE [=] <em><code>engine_name</code></em>]        [COMMENT [=] <em><code>'comment_text'</code></em> ]        [DATA DIRECTORY [=] '<code><em><code>data_dir</code></em></code>']        [INDEX DIRECTORY [=] '<code><em><code>index_dir</code></em></code>']        [MAX_ROWS [=] <em><code>max_number_of_rows</code></em>]        [MIN_ROWS [=] <em><code>min_number_of_rows</code></em>]<em><code>select_statement:</code></em>    [IGNORE | REPLACE] [AS] SELECT ...   (<em><code>Some legal select statement</code></em>)

CREATE TABLE creates a table with the given name. You must have the CREATE privilege for the table.

 

for example:

CREATE TABLE account

(

user_Name char(15) not null,

user_Password char(15) not null

);

 

INSERT INTO account VALUES

(‘xiang’, ‘123456’);

bitsCN.com