Second, the basic operation of the database

MySQL commands are divided into the following categories:

Dml:data manapulate Language: Data Manipulation language (data in the action table)

INSERT, REPLACE, UPDATE, DELETE

Ddl:data defination lanuage: Data definition Language (action table)

CREATE, ALTER, DROP

Dcl:data control Language: Data Controls language

GRANT, REVOKE

Dql:data query Language: Data Query Language

Select,where,order by,groupby,having

TPL: The processing language of things

Begin,transaction,commit

2.1. Create a Database

After the MySQL installation is complete, you will automatically create several required databases in its data directory, and you can use the show databases statement to view all currently existing databases.

mysql> SHOW databases;+--------------------+| Database |+--------------------+| Information_schema | | MySQL | | Performance_schema | | Test |+--------------------+4 rows in Set (0.00 sec)

MySQL in the above database is required, it describes user access rights, users can use the test database to do the testing work.

Creating a database is a piece of data stored and managed on the system disk, which can be used directly if the administrator creates a database for the user when setting permissions, otherwise it is necessary to create the database yourself. The basic SQL syntax format for MySQL database creation is:

CREATE DATABASE db_name; #直接创建数据库CREATE DATABASE [IF not EXISTS] db_name; #当数据库不存在时创建

After the database is created, you can use the show create databases declaration to view the definition of the database.

mysql> SHOW CREATE DATABASE test\g*************************** 1.  Row *************************** database:testcreate database:create Database ' test '/*!40100 DEFAULT CHARACTER SET UTF8 */1 row in Set (0.00 sec)

2.2. Delete Database

Deleting a database clears the existing database from disk space, and all data in the database is deleted together. The basic syntax format for deleting a database in MySQL is:

DROP DATABASE [IF EXISTS] db_name;

mysql> CREATE DATABASE TEST_TB; Query OK, 1 row Affected (0.00 sec) mysql> DROP DATABASE test_tb; Query OK, 0 rows affected (0.02 sec) mysql> SHOW CREATE DATABASE test_tb\gerror 1049 (42000): Unknown database ' TEST_TB '

2.3. Database Storage Engine

The database storage engine is the database's underlying software component, and the database management system (DBMS) uses the data engine to create, query, update, and delete data operations. Different storage engines provide different storage mechanisms, indexing techniques, locking levels, and more. The core of MySQL is the storage engine.

650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M01/9B/C7/wKioL1lnFOmSfn9lAAGaxjAYVZs454.png-wh_500x0-wm_ 3-wmp_4-s_1687354689.png "title=" image 1.png "alt=" Wkiol1lnfomsfn9laagaxjayvzs454.png-wh_50 "/>

MySQL provides a variety of different storage engines, including the engine that handles transaction security tables and the engine that handles non-transactional security tables. In MySQL, you do not need to use the same storage engine across the server, and you can use different engines for each table. You can use the show engines statement to see which engine types the system supports:

mysql> show engines \g*************************** 1. row ********************       engine: csv     support: yes      Comment: CSV storage engineTransactions: NO           xa: no  savepoints: no********************  2. row ***************************      Engine:  performance_schema     support: yes     comment:  Performance schematransactions: no          xa:  no  savepoints: no*************************** 3. row *********************       engine: myisam     support: yes       comment: myisam storage enginetransactions: no           XA: NO  Savepoints: NO*************************** 4. row        Engine: BLACKHOLE      Support: YES     Comment: /dev/null storage engine  ( Anything you write to it disappears) transactions: no           xa: no  savepoints: no***************************  5. row ***************************      engine: mrg_myisam      support: yes     comment: collection of  identical MyISAM tablesTransactions: NO           xa: no  savepoints: no*************************** 6. row ***************************       Engine: MEMORY     Support: YES      Comment: Hash based, stored in memory, useful for  Temporary tablestransactions: no          xa:  no  savepoints: no*************************** 7. row *********************       engine: federated     support: no      Comment: Federated MySQL storage engineTransactions:  Null          xa: null  savepoints: null  8. row ***************************       engine: innodb     support: default     comment: supports  transactions, row-level locking, and foreign keystransactions: yes           XA: YES  Savepoints: YES8 rows  in set  (0.00 SEC)

Common storage Engine features include:

MyISAM: Storage format:. frm: Table structure. MYD: Table data. MYI: Table Index Features: Support full-text indexing, compression: for the implementation of the Data warehouse, can save storage space and improve performance, support spatial index, support table-level lock, support deferred update index, not support transaction, foreign key and row-level lock, unable to recover data safely after crash Scenarios: read-only data, smaller tables, tolerance for post-crash modification operations, and data loss.

InnoDB: Two kinds of storage formats Innodb_file_per_table=off, that is, using shared table spaces A unique format definition file for each table: TB_NAME.FRM Table space files shared by default data directory: ibdata# Innodb_file_per_table=on, which uses a separate table space Each table stores two files in a database directory: Tb_name.frm tb_name.ibd Tablespace: A unique format data file managed by InnoDB that can store both data and indexes internally Features: Supports transactions, has transaction logs, supports foreign keys, multi-version concurrency control MVCC, clustered index support (Indexes other than clustered indexes, commonly referred to as secondary indexes), support row-level locks (gap locks), support for secondary indexes, support for adaptive hash indexes, and support for hot backups.

ARCHIVE: Supports only insert and select, supports good compression, does not support transactions, does not support indexes well, and is suitable for storing log information or other applications of data collection classes implemented by Time series.

CSV: Stores data in CSV format; indexes are not supported; only for data exchange scenarios.

Blackhole: There is no storage mechanism, any data destined for this engine will be discarded, it will record the binary log, so it is often used in multi-level replication architecture as a staging server.

Memory: Storage of data in memory, table of memories, often used to hold intermediate data, such as periodic aggregated data, etc., also used to implement temporary tables, support hash index, use table-level lock, does not support BLOB and text data type.

Mrg_myisam: is a variant of MYISAM, capable of merging multiple MYISAM tables into a single virtual table.

NDB: is a dedicated storage engine in MySQL cluster.

Storage Engine Comparison:

Function	MyISAM	Memory	InnoDB	Archive
Storage limits	256TB	Ram	64TB	None
Support Transactions	No	No	Yes	No
Support Full-Text indexing	Yes	No	No	No
Support Number Index	Yes	Yes	Yes	No
Support for Hash indexes	No	Yes	No	No
Support for data indexing	No	N/A	Yes	No
Support for foreign keys	No	No	Yes	No

Third-party storage engines:

OLTP class: (On-line Transaction processing online transaction processing)

XtraDB: Enhanced InnoDB, provided by Percona, replaces InnoDB source in the MySQL storage engine with XtraDB source code when compiling and installing.

PBXT:MARIADB comes with this storage engine, supports engine-level replication, foreign key constraints, and provides appropriate support for SSD disks; support transactions, MVCC

Tokudb: Using fractal trees Index, it is suitable for storing big data, having very compression ratio; MARIADB Introduction.

Column Storage Engine:

Infobright: Currently the more famous column engine, suitable for Yu Hai volume data storage scenarios, such as PB level, designed for data analysis and Data Warehouse design.

INFINIDB, MonetDB, LUCIDDB

Open Source Community Storage Engine:

Aria: Formerly Maria, understood as an enhanced version of MyISAM (supports post-crash security recovery, data caching)

Groona: Full-text indexing engine, Mroonga is a Groona-based two-time development version

Oqgraph: A storage engine that supports graph structure developed by open query

SPHINXSE: Provides SQL interface for Sphinx full-Text Search server

Spider: Can data cut into different shards, on the Shard support parallel query.

Criteria for selecting engines:

Whether transactions are required, support for types of backups, post-crash recovery, and unique features.

This article is from the "Wind and Drift" blog, please be sure to keep this source http://yinsuifeng.blog.51cto.com/10173491/1947106

Second, the basic operation of the database