MySQL Operations database

A database is a collection of data that is stored on a computer, organized, and shared over a long period of time. In short, a database is a place where data is stored. Only, there are specific rules for how they are stored. This makes it easy to process data. The operations of the database include creating the database and deleting the database. These operations are the foundation of database management.

-Create DATABASE-delete database-storage engine for database-how to select the storage engine  5.1 creating a database creating a database is the partitioning of a space in the database system to store the corresponding data. This is the basis for table operations, but also the basis for database management. The creation of the database is implemented through the SQL language create Databases command. Create database name;  --mysql command to end the command with a semicolon (;) or \g  show DATABASES;--To display all databases in a list create database example;-- Create DATABASE IF not EXISTS example; --If no example database is created example database 5.2 Deleting a database deleting a database means deleting a database that already exists in the database system. After the database is deleted, the original allocated space is reclaimed. It is important to note that deleting a database deletes all the tables and all the data in that database. The deletion of the database is implemented by the SQL statement drop Databases command. Drop database Name Drop DB IF EXISTS example; --If the example database exists, delete the storage engine for the example database 5.3 database The concept of the storage engine is mentioned in MySQL. Simply instituted, the storage engine is the type of the table. The database storage engine determines how tables are stored on the computer.  type=InnoDBengine=InnoDB MyISAM:  Advantages: 1. OPTIMIZE table name; # defragment 2. Emphasize the disadvantage of fast read operation: Some features do not support innodb:  advantages: 1. Support multi-function (transaction, foreign key) 2. Emphasize the disadvantage of fast read operation: The speed is inferior to the MyISAM, occupy the space big 5.3.1 the MySQL storage engine introduction The concept of the storage engine is a feature of MySQL and is a plug-in storage engine concept. This determines that the tables in the MySQL database can be stored in different ways. Users can choose different storage methods, transaction processing, etc. according to their different requirements.   Use the show engines statement to see the types of storage engines supported by the MySQL database. SHOW ENGINES \gengine:myisamsupport:yescomment:default Engine at the MySQL 3.23 with greatPerformance (description)  transaction:no (Support transaction) Xa:no (is not supported for distributed XA specification) Savepoints:no (support SavePoint)  show VARIABLES like ' Have% '; Shwo VARIABLES like ' storage_engine '; # database default engine [storage: storage]1. You can modify the database default engine in the Mysql.ini configuration file Default-storage-engine = INNODB  5.3.2 INNODB storage Engine INNODB is a storage engine for the MySQL database. InnoDB to MySQL tables provides transaction security for transactions, rollback, crash repair, and multiple versions of concurrency control. The InnoDB storage engine is included in MySQL starting from 3.23.34a. InnoDB is the first engine on MySQL to provide a foreign key constraint. and InnoDB's ability to handle transactions is something that no other MySQL storage engine can match.   (1) The value of the auto-grow column in the InnoDB storage engine that supports autogrow columns (Auto_incremet) cannot be empty and the value must be unique. MySQL specifies that the self-increment column must be the primary key. When inserting a value, if the autogrow column does not enter a value, the inserted value is the auto-grow value, and if the value entered is 0 or empty (null), the inserted value is also the auto-grow value, and if a certain value is inserted and the value is not previously present, it can be inserted directly.   (2) InnoDB storage Engine supports foreign keys (FOREIGN key)   (3) InnoDB storage engine, table structure for creating tables is stored in the. frm file. Data and indexes are stored in INNODB_DATA_HOME_DIR and innodb_data_file_path defined table spaces.   (4) The advantage of the InnoDB storage engine is that it provides good transaction management, crash-repair capabilities, and concurrency control. The disadvantage is that the reading and writing efficiency is slightly poor, the occupied data space is relatively large. The 5.3.3 MyISAM storage Engine MyISAM storage engine is a common storage engine in MySQL and is the default storage engine for MySQL previously. The MyISAM storage engine is developed based on the ISAM storage engine. MyISAM adds a lot of extensions to use. The table for the  myisam storage engine is stored as three files. The name of the file is the same as the table name. Extensions include frm, myd, and Myi. Where frm is the structure of the file storage table for the extension; MyD stores the data for the file name extension, the base is myThe abbreviation for data, Myi is the file Store index for the extension, which is the abbreviation for Myindex. The advantage of the  myisam storage engine is its small footprint and fast processing speed. The disadvantage is that the integrity and concurrency of transactions are not supported. The 5.3.4 memory storage engine Memory storage engine is a special storage engine in MySQL. The base uses the content stored in memory to create the table, and all the data is put in memory. These features are different from the InnoDB storage engine, MyISAM storage engine.   Each memory storage engine-based table actually corresponds to a disk file. The file has the same filename as the table name, and the type is the frm type. The file only stores the structure of the table. and its data files are stored in memory. This facilitates the rapid processing of data and improves the processing efficiency of the whole table. It is important to note that the server needs to have enough memory to sustain the use of tables in the memory storage engine. If you don't need it, you can free up the memory and even delete the tables you don't need. The size of the  memory table is limited. The size of the table is mainly determined by two parameters, namely Max_Rows and Max_heap_table_size. The selection of 5.3.5 storage engine in practice, choosing a suitable storage engine is a complex issue. Each storage engine has its own advantages, and it cannot be generally said who is better than who.   Storage Engine comparison   features InnoDB MyISAM memory transaction Security support does not support data row lock support does not support foreign KEY constraints not supported   &NB Sp 　　　　  Support    does not support storage limit 64TB There is a high space consumption   low-full-text index unsupported support memory usage is not supported Low    high insertion data speed   Low   high   high foreign key support       support    No   Frequently asked questions and answers 1. How do I modify the default storage engine? (1) Use the Configuration Wizard to modify the default storage engine. (2) Modify the configuration file My.ini,default_storage_engine=innodb modify can be 2. How do I select a storage engine? CREATE TABLE  ' test '. ' Test ' (' a ' INT not NULL) ENGINE = MYISAM; 

MySQL Operations database