Basic operation of MySQL database learning notes

The main content includes: Create DATABASE, delete database, different types of data storage engine and storage engine selection.

• Create a database, create a few required databases in the default data directory, with show DATABASES, and see the following:

Where the MySQL database describes user access rights, creating a database is a partition of the system disk for data storage and management. The basic format is CRATE DATABASE database_name;

such as creating a test_db database

Use the following command to view the definition of test_db

• Delete the database, the basic format drop databases database_name;

Delete the test_db as follows:

With the drop command, there is no prompt, and all data for the database is deleted and cannot be recovered.

• Database Storage Engine

is the database underlying software build, DBMS with the data engine to create queries, update and delete data operations. Different storage engines provide different storage mechanisms, indexing techniques, locking levels, and other features, using different storage engines, and you can also get specific features. The core of MySQL is the storage engine.

• • The introduction of the
  • MySQL storage engine provides a number of unmanaged storage engines, including the engine that handles transaction security tables and the engine that handles non-transactional security tables, without the need to use the same storage engine across the server, and for specific requirements, you can use an out-of-the-box storage engine for each table. MySQL5.7 supported storage engines are: InnoDB, MyISAM, memory,merge,archive,federated,csv,blackhole, etc., using SHOW Engines view the type of engine supported by the system: where the value yes of support is supported, no indicates that it cannot be used, and default is the current default storage engine.
  • The

  •  innodb storage engine, transactional, supports transaction security tables (ACID), supports row locking and foreign keys. As the default storage engine, the main features are:

    • InnoDB provides MySQL with a transaction-safe (acid-compatible) storage engine with Commit, rollback, and crash resiliency. InnoDB locks the row-level and also provides a non-locking read similar to Oracle in the SELECT statement. These features add to multi-user deployment and performance, and in SQL queries, you can freely mix tables of type InnoDB with other MySQL table types, even in the same query. The
    • InnoDB is the largest performance design for handling large amounts of data. His CPU efficiency may be unmatched by any other disk-based relational database engine lock. The
    • InnoDB storage Engine is fully integrated with the MySQL server, and the InnoDB storage engine maintains its own buffer pool to cache data and indexes in main memory. InnoDB has a logical tablespace where its tables and indexes exist, and the tablespace can contain several files (or raw disk partitions). This is different from MyISAM, such as in the MyISAM table where each table is in a separate file. The InnoDB table can be any size, even if the file size is limited to 2GB on the operating system. The
    • InnoDB supports foreign key integrity constraints (FOREIGN key). When you store data in a table, the storage for each table is stored in the primary key order, and if you do not specify a primary key when the table definition is displayed, InnoDB generates a 6B row ID for each row, which is used as the primary key. The
    • InnoDB is used on many large database sites that require high performance.
    • InnoDB do not create a directory, when using InnoDB, MySQL will create a 10MB size auto-extended data file named Ibdata1 in MySQL Data directory, and two Ib_logfile0 and ib_ Logfile1 log file of 5MB size.
  • The
  • MyISAM storage engine   ISAM-based storage engine, and extends it. It is one of the most commonly used storage engines in the Web, data storage, and other application environments. MyISAM has a higher insert, query speed, but does not support transactions. In previous versions of 5.5.5, MyISAM was the default storage engine, and the main features were as follows.
    • Large files (up to 63-bit file length) that are supported on file systems and operating systems that support large files.
    • When the delete, update, and insert operations are mixed, rows with dynamic dimensions produce less fragmentation. This is done automatically by merging adjacent deleted blocks, and if the next block is deleted, it expands to the next one.
    • 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. The maximum key length for
    • is 1000B, which can also be changed by compiling. For a key length of more than 250B, a key greater than 1024B will be used.
    • BLOBs and text columns can be indexed. The
    • null value is allowed in the indexed column, which accounts for 0~1 bytes per key.
    • All numeric key values are stored in high-byte precedence to allow a higher index compression.
    • the internal processing of one auto_increment column per table. MyISAM automatically updates this column for insert and update operations. This makes the Auto_increment column faster. The value of the top of the sequence can no longer be exploited after it has been killed. The
    • can place data and index files in different directories.
    • Each character column can have a non-pass character set. A table with a varchar
    • can either fix or dynamically record the length. The
    • varchar and char columns can be up to 64KB.
    • creates a database using the MyISAM engine, which results in 3 files. The name of the file begins with the name of the table, extension expense file type: frm file storage table definition, data file with extension. MYD (MYData), the index file extension is. MYI (myindex)
  • The Nemory storage engine stores the data in a table in memory, providing quick access to queries and references to other table data. The main features of memory are:
    • Each table in the memory table can have 32 indexes, 16 columns per index, and a maximum key length of 500B.
    • The memory storage engine performs hash and btree indexes.
    • You can have non-unique keys in a memory table.
    • The memory table uses a fixed record length format.
    • Memory does not support blob or text columns.
    • Memory supports auto_increment columns and indexes on columns that can contain null values.
    • The memory table supports sharing among all clients (just like any other non-temporary table).
    • The memory table content is in memory, which is the internal shared table created by the storage table and the server when it is idle during query processing.
    • When the contents of the memory table are no longer needed, to release the RAM used by the memories table, the delete from goods TRUNCATE table should be executed, or the entire table will be deleted (using drop table).
  • Storage engine selection, various engine comparisons
    • InnoDB is a good choice if you want to provide transactional security (acid-compatible) capabilities for commit, rollback, and crash resiliency, and require concurrency control.
    • If the data table is mainly used for inserting and querying records, the MyISAM engine can provide high processing efficiency.
    • If the data is only temporarily stored, the amount of data is small, and there is no need for high data security, you can choose to have the data in memory engine, MySQL use the engine as a temporary table, stored in the query intermediate results.
    • If you have only insert and select operations, you can select the archive engine, and the archive storage engine supports high concurrency inserts, but is not transaction-safe by itself. The archive storage engine is ideal for storing archived data, such as logging information that can be used with the archive engine.

Basic operation of MySQL database learning notes