How to Create/drop database, table

Show all databases

 
Show {databases | schemas} [like'Pattern'| WhereExpr]
 

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'| WhereExpr]
 

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{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] [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_type  Index_col_name : Col_name [(Length )] [ASC | DESC] Index_type : Using {btree | hash | rtree} Index_option : Key_block_size [=] Value | Index_type | With parser Parser_name  Reference_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 {( 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 ');