MYSQL storage engine

Storage engine is how to store data, how to update data, how to query data, how to build indexes for stored data and a series of technologies. You can do this by show engines; Statement to view storage engines supported by mysql. Mysql storage engine

  1. MyISAM: has fast insert and query speed, but does not support transactions;
  2. InnoDB: ACID transaction support, indicating row-level locking, support for foreign keys, default storage engine after MYSQL5.5;
  3. MRG_MYISAM: aggregates a group of MyISAM tables with the same structure into a whole to add or delete the query operation;
  4. Memory: All data is stored in Memory for fast response. When MySQL restarts, all data is lost.
  5. Archive: Archive, and has a compression mechanism, suitable for archiving historical data.
  6. CSV: Data is logically separated by commas and a CSV file is created for each table.

MYSQL > select * from ‘MYSQL’

MYSQL supports a variety of data types, which can be divided into three types: numeric, date-time, and string (character) types.

  1. The value types include:
  • Timyint: a small 1-byte integer value used to store status.
  • Smallint: a large integer value of 2 bytes.
  • Mediumint: a 3-byte integer value;
  • Int: large integer value of 4 bytes;
  • Bigint: 8-byte maximum integer value;
  • Float: 4-byte single-precision floating point value;
  • Double: 8-byte double-precision floating-point value.
  • Decimal: Max(D+,M+) contains small values and is generally used for amounts.
  1. Date and time types include:
  • Date: 3 bytes YYYY-MM-DD;
  • Time: 3 bytes HH:MM:SS;
  • Year: 1 byte YYYY;
  • Datetime: 8 bytes YYYY-MM-DD HH:MM:SS;
  • Timestamp: 8 bytes YYYYMMDDHHMMSS;
  1. String types include:
  • Char: a fixed-length field string of 0 to 255 bytes.
  • Varchar: a variable length string of 0 to 65536 bytes.
  • Text: 0-65535 bytes long text data;
  • Blob: Text data in binary form.

Create table

DDL operations on data tables are created, modified, and deleted. To create a table, you need to define the following information: table name, field name, and field type.

  1. MYSQL > create table as follows:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name [(create_definition,...)] [table_options][select_statement]Copy the code
  1. Description:
  • TEMPORARY: Creates a TEMPORARY table that will disappear after the current session ends.
  • IF NOT EXISTS: Checks whether a table EXISTS before creating a table. The table is created only when it does NOT exist.
  • Create_definition: the key part of a table statement that defines the attributes of the columns in the table;
  • Table_options: table configuration options, such as the default storage engine and character set for the table.
  • Select_statement: create a table with select_statement. For example, create a new table with data queried from multiple tables. Use the SELECT statement to omit create_definition.

Alter table table_name alter table table_name

  1. ALTER TABLE table_name ADD table_name;
  2. ALTER TABLE TABLE name MODIFY field name field type;
  3. ALTER TABLE table_name DROP COLUMN name;
  4. DROP TABLE: DROP TABLE name.