This article uses examples to explain the basic knowledge points of MySql database. Share with you for your reference, the details are as follows:

• Basic database operations

  1) Create a database

  Basic syntax: create database數據庫名稱;
  Create a database named itcast. The sql syntax is as follows:

   ``create database `itcast`;``
  

  It is important to note that in order to avoid conflicts between user-defined names and system commands, it is best to use backticks (``) to include the database name/field name and data table name <br /> If the database being created exists, the program will report an error. To prevent this from happening, you can use "if not exists" when creating a database. The syntax is as follows:

  recate database if not exists `itcast` 
  //This statement means that if the database named itcast does not exist in the database, the database will be created. Otherwise, the operation of creating the database itcast will not be executed.

  2) View the database

  After the database is created, if you want to view the database information, you can use this statement

   ``show caeate database database name``
  

  View the existing databases on the MySql database server

   ``show databases``
  

  3) Select the database

  The database server may have multiple databases. The command syntax for selecting a database is:

   `` use database name``
  

  4) Delete the database

  The deletion operation of the database will not only delete the data in it, but also reclaim the originally allocated storage space.

   ``drop database database name``
  

  When using the "drop database" command to delete a database, if the deleted database does not exist, the MySql server will report an error. Therefore, you can use the "if existe" command when deleting the database.

   ``drop database if exists `itcase` 
   //If the database itcase exists in the MySql database server, delete the database, otherwise do not delete the database itcasse``
  

• Data Types

When creating a data table, you need to select a data type for each field. The choice of data type determines the storage format, valid range and corresponding restrictions of the data.

MySQL provides a variety of data types, mainly divided into 3 categories

• Numeric Types

• String Type

• Date and time types

  1) Numeric Type

  MySql provides many numeric types, which are roughly divided into integer types and floating-point types. Integer types are divided into int, smallint, etc. according to their value range.
  Floating point types are divided into float, declmal, etc.

  Integer Types
  

  Floating-point types
  

  The valid value range of the decimal type is determined by M and D. Among them, M and D determine. Among them, M represents the data length, and D represents the length after the decimal point. For example, if the data type is set to DECIMAL(4,1), after inserting 3.1415926 into the database, the displayed result is 3.1.

  String Type

  When developing a project, most of the data that needs to be stored is in string format, so MySQL provides many data types for storing strings.
  
  
  Both BLOB and TEXT are used to store large amounts of data, but the difference between the two is that BLOB is case-sensitive when sorting and comparing the stored data, while TEXT is not case-sensitive.

  Date and time types <br /> To facilitate the storage of date and time in the database, MySQL provides several related data types, which can be flexibly selected according to actual development.
  

2) Storage Type

In a database, the rationality of data table design directly affects the effectiveness of the database, and the choice of storage engine when designing a data table determines what functions the data table has. Next, we will introduce the commonly used MySQL storage engines and their functions.

• InnoDB Storage Engine

• MyISAM storage engine

• MEMORY storage engine

• ARCHIVE storage engine

  InnoDB Engine

  The InnoDB storage engine has been designated as the default storage engine since MySQL version 5.5, and is used to complete transaction safety processing of transactions, rollbacks, crash recovery, and multi-version concurrency control. It is also the first table engine in MySQL to provide foreign key constraints, especially its transaction processing capabilities, which are unmatched by other MySQL storage engines.

  The advantage of InnoDB is that it provides good transaction management, crash recovery capabilities, and concurrency control.

  MyISAM storage engine

  The MyISAM storage engine is developed based on the ISAM storage engine. It not only solves many shortcomings of ISAM, but also adds many useful extensions.

  Among them, for data tables using the MyISAM storage engine, they will be stored in three files. The file name is the same as the table name, and the file extensions are frm, myd, and myi respectively.
  
  Compared with InnoDB, the advantage of MyISAM is fast processing speed; the disadvantage is that it does not support transaction processing, etc.

  MEMORY storage engine

  The MEMORY storage engine is a special type of storage engine in MySQL. In a table with the MEMORY storage engine, all data is stored in memory, so data processing is fast, but data cannot be persisted (data will be lost when a program error occurs or the computer is shut down), and data that is too large cannot be stored. The MEMORY storage engine is an ideal choice for data that requires fast read and write speeds but has a small amount of data and does not need to be persisted.

  ARCHIVE storage type

  The ARCHIVE storage engine is suitable for storing large amounts of data that is maintained for a long time but is rarely accessed. For data tables using the ARCHIVE storage engine, data is compressed using the zlib compression library when stored and decompressed in real time when records are requested. It should be noted that the ARCHIVE storage engine only supports query and insert operations, and because it does not support data indexing, the query efficiency is relatively low.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL transaction operation skills", "MySQL stored procedure skills", "MySQL database lock related skills summary" and "MySQL common function summary"

I hope this article will be helpful to everyone's MySQL database design.