MySQL Database and table management

Library Management

1. Create a database

mysql> help CREATE database; CREATE {DATABASE | SCHEMA} [IF not EXISTS] db_name [create_specification] ... create_specification: [DEFAULT] CHARACTER SET [=] charset_ name | [DEFAULT] COLLATE [=] Collation_name

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M02/8B/95/wKiom1hR63rw24WbAABFIvUbdEc505.png-wh_500x0-wm_3 -wmp_4-s_3892430760.png "style=" Float:none; "title=" 1.png "alt=" Wkiom1hr63rw24wbaabfivubdec505.png-wh_50 "/>

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M02/8B/91/wKioL1hR63qSr-MCAAAuHD_Ml5U350.png-wh_500x0-wm_3 -wmp_4-s_2814784678.png "style=" Float:none; "title=" 2.png "alt=" Wkiol1hr63qsr-mcaaauhd_ml5u350.png-wh_50 "/>

2. Deleting a database

mysql> Help drop Database;drop {database | SCHEMA} [IF EXISTS] Db_name

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M00/8B/95/wKiom1hR67HCmaGyAAATnjhcWQw529.png-wh_500x0-wm_3 -wmp_4-s_3238174143.png "title=" 3.png "alt=" Wkiom1hr67hcmagyaaatnjhcwqw529.png-wh_50 "/>

3. Modify the character set and sort characters of the database and the data dictionary

mysql> help ALTER DATABASE; ALTER {DATABASE | SCHEMA} [db_name] alter_specification ... ALTER {DATABASE | SCHEMA} db_name UPGRADE DATA DIRECTORY namealter_specification: [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] Collation_name

Table Management

1. Create a table

(a) The first mode of

create [temporary] table [if not exists] tbl_name     (Create _definition,...)     [table_options]    [partition_options][create_definition]       field definition: Field name, type and type modifier;      key, index, and constraint; -->  key is index, index may not be key The key can represent constraints           primary key, Unique key, Foreign  key, check (condition constraint)           {index|key}[table_options]      engine [=] engine_name  --->  note 1      AUTO_INCREMENT [=] value  specify the starting value of the Auto_increment      [default ] character set [=] charset_name  Specifies the default character set      checksum [ =] {0 | 1}  whether to use check values      [DEFAULT] COLLATE [=]  Collation_name  Sorting Rules      COMMENT [=]  ' string '   comments       DELAY_KEY_WRITE [=] {0 | 1}  whether to enable key delay write   (index reduces write operations)       row_format [=] {default (default) | Dynamic | FIXED (Static) | Compressed (compression) | Redundant (redundancy) | Compact (compact)}  table format      tablespace tablespace_name [storage {disk| memory| default}]  table Space   --->  NOTE 2

(b) Second way (copy table data)

CREATE [temporary] TABLE [IF not EXISTS] tbl_name [(create_definition,...)] [Table_options] Select_statement

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M00/8B/95/wKioL1hSLjzR52sGAAA5HWGB2yU554.png-wh_500x0-wm_3 -wmp_4-s_406056528.png "title=" 4.png "alt=" Wkiol1hsljzr52sgaaa5hwgb2yu554.png-wh_50 "/>

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M01/8B/99/wKiom1hSLk6wZeeSAABRPnRxwFQ883.png-wh_500x0-wm_3 -wmp_4-s_354820693.png "title=" 5.png "alt=" Wkiom1hslk6wzeesaabrpnrxwfq883.png-wh_50 "/>

(c) Third Way (duplicate table structure)

CREATE [temporary] TABLE [IF not EXISTS] tbl_name {like Old_tbl_name | (Like Old_tbl_name)}

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M02/8B/99/wKiom1hSLmChBp9WAACNS5nXsSU347.png-wh_500x0-wm_3 -wmp_4-s_1846609802.png "title=" 6.png "alt=" Wkiom1hslmchbp9waacns5nxssu347.png-wh_50 "/>

2. View status information for a table

Show table status like ' table_name '

3. Delete a table

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

4. Modify the table

ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name [alter_specification [, Alter_specification] ...]

(a) Modify the field definition

Insert new field: ADD [COLUMN] col_name column_definition [First | After Col_name] Delete field drop [column] col_name modify field Modify field name change [column] Old_col_name new_col_name column_defin ition [first| After Col_name] Modify the field type and properties MODIFY [COLUMN] col_name column_definition [First | After Col_name]

(b) Renaming of tables

MySQL > Rename table to Old_name to New_name

(c) Modifying the storage engine

Engine =

(d) Specify a field for the sorting criteria

ORDER by Col_name [, Col_name] ...

(e) Conversion of character sets and sorting rules

CONVERT to CHARACTER SET charset_name [COLLATE collation_name]

Note 1

MySQL > Show engine

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M00/8B/98/wKiom1hSLV2isa0uAAAvI96VMig510.png-wh_500x0-wm_3 -wmp_4-s_275739120.png "style=" Float:none; "title=" 1.png "alt=" Wkiom1hslv2isa0uaaavi96vmig510.png-wh_50 "/>

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M00/8B/95/wKioL1hSLV2TdgSJAABPKdgjBc4680.png-wh_500x0-wm_3 -wmp_4-s_3319661695.png "style=" Float:none; "title=" 2.png "alt=" Wkiol1hslv2tdgsjaabpkdgjbc4680.png-wh_50 "/>

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M01/8B/98/wKiom1hSLWKBxyXiAAA7OZn7mEA585.png-wh_500x0-wm_3 -wmp_4-s_2119650250.png "style=" Float:none; "title=" 3.png "alt=" Wkiom1hslwkbxyxiaaa7ozn7mea585.png-wh_50 "/>

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M01/8B/95/wKioL1hSLWKBqyRxAABfOd1aMZ0799.png-wh_500x0-wm_3 -wmp_4-s_2947597198.png "style=" Float:none; "title=" 4.png "alt=" Wkiol1hslwkbqyrxaabfod1amz0799.png-wh_50 "/>

NOTE 2

Tablespace Tablespace_name [STORAGE {disk| memory| DEFAULT}] Table space

MyISAM table with three files per table in the database directory

TB_NAME.FRM: Table Structure definition

Tb_name. MYD: Data files

Tb_name. MYI: Index File

InnoDB table, there are two ways of storage

1. Default: Each table has a separate file and a multi-table shared file

TB_NAME.FRM: The definition of a table structure, located in the database directory

ibdata#: Shared tablespace file, default in Data directory (DataDir point to directory)

Figure

2. Stand-alone tablespace files:

Each table has a table structure file tb_name.frm

A separate tablespace file Tb_name.ibd (data and index)

(a) The innodb_file_per_table should be modified to on

(b) can be permanently valid in the [MYSQLD] segment by modifying the configuration file

Innodb_file_per_table=on

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M02/8B/95/wKioL1hSLgGC0fZHAAAw3NHf2iU216.png-wh_500x0-wm_3 -wmp_4-s_3630345718.png "style=" Float:none; "title=" 2.png "alt=" Wkiol1hslggc0fzhaaaw3nhf2iu216.png-wh_50 "/>

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M00/8B/99/wKiom1hSLgLAOGdHAABP3KqBTgc446.png-wh_500x0-wm_3 -wmp_4-s_2946384484.png "style=" Float:none; "title=" 3.png "alt=" Wkiom1hslglaogdhaabp3kqbtgc446.png-wh_50 "/>

This article is from the "Homecoming" blog, make sure to keep this source http://sixijie123.blog.51cto.com/11880770/1882988

MySQL Database and table management