06-mysql Database----Table Operations

This section holds

• Storage Engine Introduction (Learn)
• Table additions and deletions to change

One, storage engine (Learn)

The first few sections we know that MySQL built in the Library = = = folder, the table in the Library = = = "File

There are different types of files that we use to store data in real life, and each file type corresponds to a different processing mechanism: For example, TXT type for processing text, Excel for processing tables, PNG for image processing, etc.

Tables in the database should also have different types, with different types of tables, and different access mechanisms for MySQL, and the table type is also known as the storage engine.

PS: Storage Engine White is how to store data, how to index the stored data and how to update, query data and other techniques 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)

There is only one storage engine in a database such as Oracle and SQL Server, and all data storage management mechanisms are the same. and MySQL
The database provides a variety of storage engines. Users can choose different storage engines for their data tables according to different requirements, and users can also
Your own need to write your own storage engine

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.

Second, MySQL-supported storage engine

Mysql> show engines\g;# View all supported engines mysql> show variables like ' storage_engine% '; # View the storage engine in use

1, InnoDB storage engine----common

Support transactions, whose design goals are primarily for online transaction processing (OLTP) applications. Its

Features a row lock design, support for foreign keys, and support for non-locking reads like Oracle, where the default read operation does not generate locks. Starting with MySQL version 5.5.8 is the default storage engine.

The InnoDB storage engine places the data in a logical table space, which is managed by the InnoDB storage engine itself like a black box. Starting with MySQL 4.1 (including 4.1), you can store the table for each InnoDB storage engine in a separate IBD file. In addition, the InnoDB storage engine supports the use of a bare device (row disk) to establish its tablespace.

InnoDB achieves high concurrency by using multi-version concurrency control (MVCC), and implements 4 isolation levels for the SQL standard, default to the repeatable level, and uses a strategy called Netx-key locking to avoid the generation of Phantom Read (Phantom) phenomena 。 In addition to this, the InnoDB storage engine provides high performance and high-availability features such as insert buffer, two write (double write), Adaptive Hash Index (Adaptive hash indexes), pre-read (read ahead), and more.

For storage of data in tables, the InnoDB storage engine uses aggregation (clustered), each table is stored in the order of the primary key, and if no primary key is explicitly specified when the table is defined, the InnoDB storage engine generates a 6-byte ROWID for each row as the primary key.

The InnoDB storage engine is the most commonly used engine for MySQL databases, and successful applications from companies such as Facebook, Google, and Yahoo! have proven that the InnoDB storage engine is highly available, high performance, and highly scalable. The mastery and understanding of its underlying implementation also requires time and technology to accumulate. If you want to learn more about how the InnoDB storage engine works, implemented, and applied, you can refer to the "MySQL Insider: InnoDB Storage Engine" book.

2, MyISAM storage engine----common

Transactions, table lock design, support for full-text indexing are not supported, mainly for OLAP database applications, which are the default storage engine (except for Windows versions) before MySQL 5.5.8. A big difference between a database system and a file system is the support for transactions, which are not supported by the MyISAM storage engine. It is not difficult to understand the fundamental. Does the user need to have a transaction in all applications? In the Data warehouse, if there is no ETL these operations, but simply through the report query also need transaction support? In addition, the MyISAM storage engine is unique in that its buffer pool caches only (cache) index files, Instead of caching data files, this is not the same as most databases.

3. NDB Storage Engine

MySQL AB acquired the NDB storage engine from Sony Ericsson Corporation. The NDB storage engine is a clustered storage engine, similar to Oracle's RAC cluster, but unlike the share everything structure of the Oracle RAC, its structure is a cluster architecture that share nothing, thus providing a higher level of high availability. The NDB storage engine is characterized by the fact that the data is all in memory (starting with version 5.1, non-indexed data can be placed on disk), so the primary key lookup (primary key lookups) is extremely fast and can be added online NDB data node In order to improve database performance linearly. Thus, the NDB storage engine is a highly available, high-performance, highly scalable database cluster system, which is also a database application type for OLTP.

4, Memory storage engine----commonly used

As its name holds, the data in the memory storage engine is stored in RAM, the database restarts or crashes, and the data in the table disappears. It is ideal for staging tables that store temporary data in an OLTP database application or as a dimension table for the Data warehouse in an OLAP database application. The memory storage engine uses a hash index by default instead of a B + tree index that is usually familiar.

5. Infobright Storage Engine

Third-party storage engines. It is characterized by columns rather than rows, making it ideal for OLAP database applications. Its official website is http://www.infobright.org/, which has a number of successful data warehousing cases to analyze.

6. Ntse Storage Engine

NetEase developed a storage engine for its internal use. The current version does not support transactions, but provides features such as compression, row-level caching, and, in the near future, memory-oriented transaction support.

7, blackhole----commonly used

A black hole storage engine that can be applied to the distribution Master library in primary and standby replication.

The MySQL database also has many other storage engines, which are just some of the most commonly used engines. If you like, you can write your own engine, this is the ability of open source to us, but also the charm of open source.

Specify table type/Storage engine

CREATE TABLE t1 (id int) engine=innodb;# is not written by default is InnoDB

Small exercise:

Create four tables with Innodb,myisam,memory,blackhole storage engine for insert data testing

CREATE TABLE t1 (id int) engine=innodb;create table t2 (id int) engine=myisam;create table t3 (id int) engine=memory;create table t4 (id int) engine=blackhole;

To view the files in the DB1 database under the data file:

#.frm is the framework structure for storing data tables #. IBD is a MySQL data file #. MyD is the extension # of the data file for the MyISAM table. Myi is the extension of the index of the MyISAM table # Two storage engines found after the table structure, no data #memory, after restarting MySQL or restarting the machine, the data in the table is emptied #blackhole, inserting any data into the table, is equivalent to throw into the black hole, the table never stored records

Iii. Introduction of the table

Table is equivalent to a file, a record in the table is equivalent to a row of the file, but a record in the table has a corresponding title, called the table field

Id,name,sex,age,birth is called a field, the rest, a row of content is called a record

Iv. Creating a Table

Grammar:

CREATE TABLE table name (field name 1 type [(width) constraint], field name 2 Type [(width) constraint], field name 3 Type [(width) constraint]); #注意: 1. In the same table, the field names cannot be the same as 2. Width and constraints are optional 3. Field names and types are required

1. Create a database

Create database DB2 charset UTF8;

2. Using the database

Use DB2;

3. Create a A1 table

CREATE TABLE A1 (id int, name varchar (), age int (3));

4. Inserting records into a table

INSERT into A1 values (1, ' mjj ', +), (2, ' Wusir ', 28);

PS: to; As a conclusion of MySQL

5. Querying the data and structure of the table

(1) Querying the stored data in the A1 table

Mysql> select * FROM A1;
+------+-------+------+
| ID | name | Age |
+------+-------+------+
| 1 | MJJ | 18 |
| 2 | Wusir | 28 |
+------+-------+------+
2 rows in Set (0.02 sec)

Mysql>

(2) View the structure of the A1 table

Mysql> desc a1;+-------+-------------+------+-----+---------+-------+| Field     | Type           | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| ID        | int        | YES  |      | NULL    |       | | name      | varchar    | YES  |      | NULL    |       | | age       | INT (3)         | YES  |      | NULL    |       | +-------+-------------+------+-----+---------+-------+3 rows in Set (0.16 sec)

(3) View the detailed structure of the table

Mysql> Show CREATE TABLE a1\g;*************************** 1. Row ***************************       table:a1create table:create Table ' A1 ' (  ' id ' int (one-by-one) DEFAULT NULL,  ' name ' varchar (+) default NULL,  ' age ' int (3) default null) Engine=innodb default charset=utf81 row in Set (0.00 sec)

6. Copying a table

(1) Create a new database db3

Mysql> CREATE Database db3 charset UTF8; Query OK, 1 row Affected (0.00 sec)

(2) using DB3

mysql> Use db3;database changed

#这是上个创建的db2数据库中的a1表mysql > select * from db2.a1;+------+-------+------+| ID   | name  | Age  |+------+-------+------+|    1 | MJJ   |   | |    2 | Wusir |   |+------+-------+------+

(3) Copy the table structure and record of db2.a1

# This is the Copy table operation (both duplicate the table structure and copy the record) mysql> CREATE TABLE B1 select * from Db2.a1; Query OK, 2 rows affected (0.03 sec)

(4) View the data and table structure in DB3.B1

#再去查看db3文件夹下的b1表发现 the same as the A1 table data under the DB2 file mysql> select * from db3.b1;+------+-------+------+| ID   | name  | Age  |+------+-------+------+|    1 | MJJ   |   | |    2 | Wusir |   |+------+-------+------+2 rows in Set (0.00 sec)

PS1: If only the table structure, do not log

#在db2数据库下新创建一个b2表, give a Where condition, condition is not set, condition is false, copy table structure mysql> CREATE TABLE b2 select * from db2.a1 where 1>5; Query OK, 0 rows affected (0.05 sec) records:0  duplicates:0  warnings:0

To view the table structure:

# view table Structure mysql> desc b2;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| ID    | int     | YES  |     | NULL    |       | | name  | varchar (50) | YES  |     | NULL    |       | | age   | INT (3)      | YES  |     | NULL    |       | +-------+-------------+------+-----+---------+-------+3 rows in Set (0.02 sec) #查看表结构中的数据, found to be empty data mysql> select * FROM B2; Empty Set (0.00 sec)

PS2: There is another way to use like (Copy only the table structure, do not copy records)

Mysql> CREATE table b3 like db2.a1; Query OK, 0 rows affected (0.01 sec) mysql> desc b3;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| ID    | int     | YES  |     | NULL    |       | | name  | varchar (50) | YES  |     | NULL    |       | | age   | INT (3)      | YES  |     | NULL    |       | +-------+-------------+------+-----+---------+-------+3 rows in Set (0.02 sec) mysql> select * from db3.b3; Empty Set (0.00 sec)

7. Delete the table:

drop table name;

06-mysql Database----Table Operations