首页 > 数据库 > mysql教程 > How to Create/Drop Database, Table_MySQL

How to Create/Drop Database, Table_MySQL

WBOY
发布: 2016-06-01 13:36:30
原创
855 人浏览过

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
来源:php.cn
本站声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
热门教程
更多>
最新下载
更多>
网站特效
网站源码
网站素材
前端模板