Basic operation of mysql-4-database

1. Create a database

CREATE DATABASE database_name, example: CREATE DATABASE aa;show CREATE database AA; (View database AA)

2. Deleting a database

DROP DATABASE database_name;

3.mysql Supported storage engines:

Show Engines\g (Note that Defaut is the default engine)

InnoDB Storage Engine: is the first choice for the object database, supports the Thing Security table (ACID), supports locking and foreign keys, and after mysql5.5.5, InnoDB as the default storage engine. Features are as follows:

(1) provides MySQL with a secure storage engine for things such as commit, rollback, and crash resilience.

(2) InnoDB is designed to provide maximum performance for handling large volumes of data.

(3) fully integrated with MySQL server, the InnoDB storage engine caches data and indexes in main memory and maintains its own buffer pool.

(4) Support for FOREIGN key integrity constraints (Foregin key).

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

MyISAM Storage Engine: The ISAM-based storage engine and extends it. It is one of the most commonly used storage engines in the Web, data warehousing, and other application environments. MyISAM has a high insert, fast query speed, but does not support things. This is the default storage engine before the mysql5.5.5 version. Features are as follows:

(1) Large files (up to 63-bit file lengths) are supported on file systems and operating systems that support large files.

(2) When the delete and update and insert operations are mixed, the dynamic dimension lines produce less fragmentation. This is done automatically by merging adjacent deleted blocks and extending to the next block if the next block is deleted.

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

(4) The maximum key length is the number of bytes, which can also be changed by compiling. For cases where the key is more than One byte, a key that is greater than the octet is used.

(5) the BLOB and TEXT columns can be indexed.

(6)a NULL value is allowed in the indexed column, with each value occupying 0-1 bytes per key .

(7) All numeric key values are stored in high-byte precedence to allow for a higher index compression.

using this storage engine to create a database, 3 files will be generated . The file name begins with the name of the table, and the extension indicates the type: the store table definition file has an extension of FPMand the data file has an extension of . MYD (mydate), the index file has an extension of . MYI (Myindex).

Memory Storage Engine: Stores the data in a table in RAM, providing quick access to search and reference other data. Features are as follows

(1) Each table can be up to three indexes, each index is a column, and The maximum key length of five bytes.

(2) Execute HASH and BTREE index.

(3) in a MEMORY table can have non-unique keys.

(4) use a fixed record length format.

(5) BLOB or TEXT columnsare not supported .

(6) shared among all clients.

(7) to free the memory that is used by the table when the content is no longer needed, you should execute the delete from or truncatetable, or delete the entire table.

Select the storage engine

Function	MyISAM	MEMORY	InnoDB
Storage limits	256TB	Ram	64TB
Supporting Things	No	No	Yes
Support Full-Text indexing	Yes	No	No
Support Number Index	Yes	Yes	Yes
Support for Hash indexes	No	Yes	No
Support for data caching	No	N/A	Yes
Support for foreign keys	No	No	Yes

Select: If you require commit, rollback, and crash recovery things to be safe ( ACID- compatible) capabilities and require concurrency control,innoDB is a good choice.

If the data table is used primarily for inserting and querying records, the MyISAM engine can provide high processing efficiency.

If you only temporarily store data, the amount of data is small, and you do not need high data security, you can choose to save the data in memory In the Memory engine,MySQL uses the engine as a temporary table to hold the intermediate results of the query.

Basic operation of mysql-4-database