MySQL Database and table management

Source: Internet
Author: User
Tags compact

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:// -wmp_4-s_3892430760.png "style=" Float:none; "title=" 1.png "alt=" Wkiom1hr63rw24wbaabfivubdec505.png-wh_50 "/>

650) this.width=650; "Src=" Http:// -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:// -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:// -wmp_4-s_406056528.png "title=" 4.png "alt=" Wkiol1hsljzr52sgaaa5hwgb2yu554.png-wh_50 "/>

650) this.width=650; "Src=" Http:// -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:// -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:// -wmp_4-s_275739120.png "style=" Float:none; "title=" 1.png "alt=" Wkiom1hslv2isa0uaaavi96vmig510.png-wh_50 "/>

650) this.width=650; "Src=" Http:// -wmp_4-s_3319661695.png "style=" Float:none; "title=" 2.png "alt=" Wkiol1hslv2tdgsjaabpkdgjbc4680.png-wh_50 "/>

650) this.width=650; "Src=" Http:// -wmp_4-s_2119650250.png "style=" Float:none; "title=" 3.png "alt=" Wkiom1hslwkbxyxiaaa7ozn7mea585.png-wh_50 "/>

650) this.width=650; "Src=" Http:// -wmp_4-s_2947597198.png "style=" Float:none; "title=" 4.png "alt=" Wkiol1hslwkbqyrxaabfod1amz0799.png-wh_50 "/>


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)


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


650) this.width=650; "Src=" Http:// -wmp_4-s_3630345718.png "style=" Float:none; "title=" 2.png "alt=" Wkiol1hslggc0fzhaaaw3nhf2iu216.png-wh_50 "/>

650) this.width=650; "Src=" Http:// -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

MySQL Database and table management

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.