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