Learning MySQL3 from scratch --- basic database operations, mysql3 --- basic operations

Learning MySQL3 from scratch --- basic database operations, mysql3 --- basic operations

• Create a database
  • After MySQL is installed, several required databases will be automatically created under the Data Directory.Show databases;To view the existing database
  • Database creation is to divide a region on the system disk for data storage and management. If the Administrator creates a database for the user when setting permissions, it can be directly used. Otherwise, you must create your own database.
  • Basic SQL syntax format for creating a database:Creat database database_name;
• Delete Database
  • Deleting a database clears an existing database from the disk. After the database is deleted, all data in the database will be deleted.
  • Basic SQL syntax format for deleting a database:Drop database database_name
  • UseExercise caution when running the drop database Command. MySQL does not provide any reminder and confirmation information when executing the command,The drop database statement deletes all data tables and data stored in the DATABASE.
• The database storage engine is the underlying software component of the database. The database management system uses the data engine to create, query, update, and delete data. The core of MySQL is the storage engine.

  AvailableSHOW ENGINESStatement to view the engine types supported by the systemDEFAULTIndicates that this engine is the current default storage engine.

• InnoDB Storage Engine
  • Provides a transaction security storage engine with the capabilities of submission, rollback, and crash recovery
  • Designed to handle the maximum performance of massive data volumes
  • Fully integrated with MySQL servers
  • Supports foreign key integrity constraints
  • It is used on many large database sites that require high performance.
• MyISAM storage engine
  • Large files are supported on file systems and operating systems that support large files.
  • When the delete, update, and insert operations are mixed, dynamic-size rows produce fewer fragments.
  • 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.
  • The maximum key length is 1000 bytes, which can be changed through recompilation.
  • BLOB and TEXT columns will be used
  • The NULL value is allowed in the indexed column.
  • All numeric keys are stored with the highest byte priority to allow a higher index compression
  • The AUTO_INCRENENT column of each MyISAM table is updated faster.
  • You can store data files and index files in different directories.
  • Each character column can have different character sets.
  • Labels with VARCHAR can be fixed or the length of dynamic records
  • The VARCHAR and CHAR columns can be up to 64 KB.
• MEMORY storage engine
• Each table can have 32 indexes, 16 columns for each index, and a maximum key length of 500 bytes.
• Execute HASH and BTREE Indexes
• A table can have a non-unique key.
• Use a fixed length format
• BLOB and TEXT columns are not supported.
• Supports AUTO_INCRENENT columns and indexes that can contain NULL value columns.
• Share among all clients
• The content is stored in memory. The memory is occupied by the MOMORY table and the server during query and processing. The created internal table is shared.
• When you do not need the MEMORY table content, you need to release the MEMORy used by the MEMORY table and executeDELETE FROMOrTRUNCATE TABLEOr delete the entire table (DROP TABLE)