HowtoCreate/DropDatabase, Table_MySQL

HowtoCreateDropDatabase, Table bitsCN.com

SHOW ALL DATABASES

SHOW {DATABASES | SCHEMAS}    [LIKE 'pattern' | WHERE expr]

SHOW DATABASESLists the databases on the MySQL server host.

CREATE DATABASE

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

CREATE DATABASECreates a database with the given name. To use this statement, you needCREATEPrivilege for the database.CREATE SCHEMAIs a synonymCREATE DATABASE.

DROP DATABASE

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

DROP DATABASEDrops all tables in the database and deletes the database. BeVeryCareful with this statement! To useDROP DATABASE, You needDROPPrivilege on the database.DROP SCHEMAIs a synonymDROP DATABASE.

SHOW ALL TABLES

SHOW [FULL] TABLES [{FROM | IN} db_name]    [LIKE 'pattern' | WHERE expr]

SHOW TABLESLists the non-TEMPORARYTables in a given database.

DROP TABLE

DROP [TEMPORARY] TABLE [IF EXISTS]    tbl_name [, tbl_name] ...    [RESTRICT | CASCADE]

DROP TABLERemoves one or more tables.

CREATE TABLE

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name    (create_definition,...)    [table_options]    [partition_options]

Or:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name    [(create_definition,...)]    [table_options]    [partition_options]    select_statement

Or:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name    { LIKE old_tbl_name | (LIKE old_tbl_name) }

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

CREATE TABLECreates a table with the given name. You must haveCREATEPrivilege for the table.

 

For example:

Create table account

(

User_Name char (15) not null,

User_Password char (15) not null

);

 

Insert into account VALUES

('Shanghai', '123 ');

BitsCN.com