Shulou(Shulou.com)06/01 Report--

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

two。 Delete database

Mysql > help drop database;DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

3. Modify the character set and sort characters of the database as well as 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 tabl

(a) the first method

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,...) Definition of [table_options] [partition_options] [create_definition] fields: field names, types, and type modifiers; keys, indexes, and constraints;-- > keys are indexes, indexes are not necessarily keys Keys can represent constraints primary key, unique key, foreign key, Check (conditional constraint) {index | key} [table_options] engine [=] engine_name-- > Note 1 AUTO_INCREMENT [=] value specifies the starting value of AUTO_INCREMENT [DEFAULT] CHARACTER SET [=] charset_name specifies the default character set CHECKSUM [=] {0 | 1} whether to use the check value [DEFAULT] COLLATE [=] collation_name collation COMMENT [=]' String' comment DELAY_KEY_WRITE [=] {0 | 1} whether to enable key deferred writing (index reduces write operations) ROW_FORMAT [=] {DEFAULT (default) | DYNAMIC (dynamic) | FIXED (static) | COMPRESSED (compression) | REDUNDANT (redundant) | COMPACT (compact)} tablespace TABLESPACE tablespace_name [STORAGE {DISK | MEMORY | DEFAULT}] tablespace-- > Note 2

(B) the second method (copying table data)

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] select_statement

(C) third method (copy table structure)

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name {LIKE old_tbl_name | (LIKE old_tbl_name)}

two。 View the status information of the table

Show table status like 'table_name'

3. Delete tabl

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 name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFTER col_name] modify field type and attribute MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]

(B) rename the table

Mysql > rename table to old_name TO new_name

(C) modify the storage engine

Engine =

(d) Fields that specify the sorting criteria

ORDER BY col_name [, col_name]...

(e) convert character sets and sorting rules

CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]

Note 1

Mysql > show engine

Note 2

TABLESPACE tablespace_name [STORAGE {DISK | MEMORY | DEFAULT}] tablespace

The MyISAM table, each with three files, is located in the database directory

Tb_name.frm: table structure definition

Tb_name.MYD: data fil

Tb_name.MYI: index fil

InnoDB table, which can be stored in two ways

1. Default: each table has a separate file and a file shared by multiple tables

Tb_name.frm: the definition of the table structure, located in the database directory

Tablespace files shared by ibdata#:, which are located in the data directory (the directory pointed to by datadir) by default

Figure

two。 Separate tablespace files:

Each table has a table structure file tb_name.frm

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

(a) innodb_file_per_table should be changed to ON

(B) it can be permanently valid in the [mysqld] paragraph by modifying the configuration file

Innodb_file_per_table=ON

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.