>  기사  >  데이터 베이스  >  How to Create/Drop Database, Table_MySQL

How to Create/Drop Database, Table_MySQL

WBOY
WBOY원래의
2016-06-01 13:36:30838검색

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
성명:
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.