Basic operation and storage engine of MySQL database

First, the Knowledge reserve

Database server: One computer (high memory requirements)

Database management system: Like MySQL, is a software

Database: Oldboy_stu, equivalent to Folder

Table: Student,scholl,class_list, equivalent to a specific file

Entries: 1 Susan 158426544 22, which is equivalent to a line of content in a file

Second, the initial SQL language

SQL (structured query Language, structured queries language)
The SQL language is primarily used to access data, query data, update data, and manage relational database systems, which are developed by IBM. There are 3 types of SQL languages:
DDL statement Database Definition Language: Database, table, view, index, stored procedure, such as Create DROP ALTER
DML statement Database manipulation language: Inserting data insert, deleting data Delete, updating data update, querying data Select
DCL Statement Database Control Language: for example, to control user access rights grant, REVOKE

Third, the system database

SQL (structured query Language, structured queries language)
The SQL language is primarily used to access data, query data, update data, and manage relational database systems, which are developed by IBM. There are 3 types of SQL languages:
DDL statement Database Definition Language: Database, table, view, index, stored procedure, such as Create DROP ALTER
DML statement Database manipulation language: Inserting data insert, deleting data Delete, updating data update, querying data Select
DCL Statement Database Control Language: for example, to control user access rights grant, REVOKE

Iv. Creating a Database

Database naming rules:

can be by letter, number, underscore, @, #, $ case-sensitive uniqueness cannot use keywords such as create select cannot use the number maximum 128 bits alone

Create DATABASE DB1

V. Database-related Operations

Database Related Operations Note: The database is equivalent to the folder table is equivalent to the naming rules of the file database: I. Database additions and deletions (SQL statements) add: Create database db1; Delete: Drop database db1; Change: Alter DATABASE DB1 charset UTF8 check: show databases;#View all DatabasesShow CREATE database db1; View the default UTF8 for the specified DB settings, in the configuration file: Write Character_set_server=Utf8use DB2#On behalf of your mouse double-click Switch DirectorySelect Database ()#See which folder you are currently in two. Add and delete the table : CREATE TABLE t1 (int id, char name); (T1.FRM table structure, T1.IBD table data) Delete: drop table t1; change: Insert data: INSERT INTO Test (id,name) VALUES (1,'x'do not write to whom the value of the designation is transmitted, but also according to the location of the transmission: show tables; #View All TablesShow CREATE table T1;#View the specified tableSELECT * fromT1#View Table DataSelect Id,name fromT1#View Table DataEngine =the storage engine for the InnoDB table is InnoDB

Six, what is the storage engine?

The storage engine plainly is how to store the data, how to index the stored data, and how to update, query the data and other technologies to implement the
Method. Because the storage of data in a relational database is stored as a table, the storage engine can also be called a table type (that is, the type that stores and operates this table)

Components such as SQL parser, SQL optimizer, buffer pool, storage engine, and so on are present in each database, but not every database has so many storage engines. MySQL's plug-in storage engine allows developers of the storage engine layer to design the storage tier they want, for example, some applications need to meet the requirements of the transaction, some applications do not need to have such strong requirements for the transaction, some want the data to be persisted, and some only want to put in memory, temporarily and quickly to provide data query.

One type indicates a way of storing
Storage Engine: Is the type of the table, different types will correspond to different processing mechanisms to deal with him
Transaction: Either success at the same time or unsuccessful at the same time

Using the storage engine

Method 1: Specify when the table is built

Storage Engine 1.create table t1 (id int) engine = InnoDB will appear 2 files 2.create table t2 (id int) engine =3.create table t3 (ID int) engine = memory only the table structure has no table data, is created into the memory of the 4.create table t4 (id int) engine = Blackhole  black hole, eating data (data stored in it is not, It's not in the store.)

Method 2: Specify the default storage engine in the configuration file

/etc/my.cnf[mysqld]default-storage-engine=innodbinnodb_file_per_table=1

Basic operations and storage engine for MySQL database