Mysql-4-basic database operations, mysql-4-basic operations

Mysql-4-basic database operations, mysql-4-basic operations

1. Create a database

Create database database_name; example: create database aa; show create database aa; (view database aa)

2. delete a database

drop database database_name;

3. storage engines supported by mysql:

Show engines \ G (note that defaut is the default engine)

InnoDB Storage engine: it is the first choice for transaction-based databases. It supports ACID and lock and Foreign keys. InnoDB is the default storage engine after mysql5.5.5. Features:

(1) provides a transaction security storage engine for mysql with the ability to submit, roll back, and crash recovery.

(2) InnoDB is designed to provide maximum performance for processing massive data volumes.

(3) fully integrated with the mysql server, the InnoDB Storage engine caches data and indexes in the master memory to maintain its own buffer pool.

(4) Support for foreign key integrity constraints (foregin key ).

(5) It is used on many large database sites that require high performance.

 

MyISAM storage engine: it is based on the ISAM storage engine and can be expanded. It is one of the most commonly used storage engines in web, data warehousing, and other application environments. MyISAM has a high insert speed and fast query speed, but does not support transactions. Before MySQL 5.5.5, This is the default storage engine. Features:

(1) large files (up to 63 characters in length) are supported on file systems and operating systems that support large files.

(2) When the delete, update, and insert operations are mixed, dynamic-size rows produce fewer fragments. This is automatically completed by merging adjacent deleted blocks and extending to the next block if the next block is deleted.

(3) the maximum number of indexes in each MyISAM table is 64, which can be changed by recompiling. The maximum number of columns for each index is 16.

(4) The maximum key length is 1000 bytes, which can be changed by compiling. If the key exceeds 250 bytes, a key exceeding 1024 bytes will be used.

(5) BLOB and TEXT columns can be indexed.

(6) The NULL value is allowed in the indexed column. Each value occupies 0-1 bytes of each key.

(7) All numeric key values are stored with high byte priority to allow compression of a higher index.

Using this storage engine to create a database generates three files. The file name starts with the name of the table. The extension indicates the file type: the file extension for storing the table definition file is FPM, and the data file extension is. MYD (MYDate). The extension of the index file is. MYI (MYIndex ).

 

MEMORY storage engine: Stores Table data in the MEMORY to provide quick access for searching and referencing other data. Features:

(1) Each table can have up to 32 indexes, 16 columns for each index, and a maximum key length of 500 bytes.

(2) Perform HASH and BTREE indexes.

(3) a memory table can have a non-unique key.

(4) use a fixed record length format.

(5) BLOB or TEXT columns are not supported.

(6) shared among all clients.

(7) To release the memory used by the table when no longer needed content is required, delete from or truncate table or delete the entire table.

 

 

Select storage engine

 

Function	MyISAM	MEMORY	InnoDB
Storage restrictions	256 TB	RAM	64 TB
Transaction support	No	No	Yes
Full-text index supported	Yes	No	No
Supports data index	Yes	Yes	Yes
Support hash Index	No	Yes	No
Supports data caching	No	N/	Yes
Supports Foreign keys	No	No	Yes

 

Choice: InnoDB is a good choice if you require the ability to commit, roll back, and crash recovery (ACID compatibility), and require concurrency control.

 

If a data table is mainly used to insert and query records, the MyISAM engine provides high processing efficiency.

 

If you only store data temporarily, the data volume is small, and you do not need high data security, you can choose to save the data in the Memory engine, mysql uses this engine as a temporary table, stores the intermediate query results.