Management of MySQL database and table
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
