How to Create/Drop Database, Table_MySQL
SHOW ALL DATABASES
SHOW {DATABASES | SCHEMAS} [LIKE 'pattern
' | WHEREexpr
]
SHOW DATABASES
lists 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 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] db_name
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}db_name
] [LIKE 'pattern
' | WHEREexpr
]
SHOW TABLES
lists the non-TEMPORARY
tables in a given database.
DROP TABLE
DROP [TEMPORARY] TABLE [IF EXISTS]tbl_name
[,tbl_name
] ... [RESTRICT | CASCADE]
DROP TABLE
removes 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
{ LIKEold_tbl_name
| (LIKEold_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] [DEFAULTdefault_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 SETcharset_name
] [COLLATEcollation_name
] | VARCHAR(length
) [CHARACTER SETcharset_name
] [COLLATEcollation_name
] | BINARY[(length
)] | VARBINARY(length
) | TINYBLOB | BLOB | MEDIUMBLOB | LONGBLOB | TINYTEXT [BINARY] [CHARACTER SETcharset_name
] [COLLATEcollation_name
] | TEXT [BINARY] [CHARACTER SETcharset_name
] [COLLATEcollation_name
] | MEDIUMTEXT [BINARY] [CHARACTER SETcharset_name
] [COLLATEcollation_name
] | LONGTEXT [BINARY] [CHARACTER SETcharset_name
] [COLLATEcollation_name
] | ENUM(value1
,value2
,value3
,...) [CHARACTER SETcharset_name
] [COLLATEcollation_name
] | SET(value1
,value2
,value3
,...) [CHARACTER SETcharset_name
] [COLLATEcollation_name
] |spatial_type
index_col_name
:col_name
[(length
)] [ASC | DESC]index_type
: USING {BTREE | HASH | RTREE}index_option
: KEY_BLOCK_SIZE [=]value
|index_type
| WITH PARSERparser_name
reference_definition
: REFERENCEStbl_name
(index_col_name
,...) [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE] [ON DELETEreference_option
] [ON UPDATEreference_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
)} } [PARTITIONSnum
] [SUBPARTITION BY { [LINEAR] HASH(expr
) | [LINEAR] KEY(column_list
) } [SUBPARTITIONSnum
] ] [(partition_definition
[,partition_definition
] ...)]partition_definition
: PARTITIONpartition_name
[VALUES {LESS THAN {(expr
|value_list
) |MAXVALUE
} | IN (value_list
|value_list
)}] [[STORAGE] ENGINE [=]engine_name
] [COMMENT [=]'comment_text'
] [DATA DIRECTORY [=] ''] [INDEX DIRECTORY [=] '
data_dir
'] [MAX_ROWS [=]
index_dir
max_number_of_rows
] [MIN_ROWS [=]min_number_of_rows
] [(subpartition_definition
[,subpartition_definition
] ...)]subpartition_definition
: SUBPARTITIONlogical_name
[[STORAGE] ENGINE [=]engine_name
] [COMMENT [=]'comment_text'
] [DATA DIRECTORY [=] ''] [INDEX DIRECTORY [=] '
data_dir
'] [MAX_ROWS [=]
index_dir
max_number_of_rows
] [MIN_ROWS [=]min_number_of_rows
]select_statement:
[IGNORE | REPLACE] [AS] SELECT ... (Some legal select statement
)
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