Shulou(Shulou.com)06/01 Report--

MySQL commands are mainly divided into the following categories:

DML:Data Manapulate Language: data manipulation language (manipulating data in tables)

INSERT, REPLACE, UPDATE, DELETE

DDL:Data Defination Lanuage: data definition language (operation table)

CREATE, ALTER, DROP

DCL:Data Control Language: data control language

GRANT, REVOKE

DQL:Data Query Language: data query language

SELECT,WHERE,ORDER BY,GROUPBY,HAVING

TPL: transaction processing language

BEGIN,TRANSACTION,COMMIT

2.1. Create a database

After MySQL is installed, several necessary databases are automatically created 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 is required in the above database, which describes user access rights, and users can use the test database to do testing work.

The database is created by dividing a piece on the system disk for data storage and management. If the administrator creates a database for the user when setting permissions, it can be used directly, otherwise, you need to create the database yourself. The basic SQL syntax format for MySQL to create a database is:

CREATE DATABASE db_name; # create database directly CREATE DATABASE [IF NOT EXISTS] db_name; # create when database does not exist

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\ gateway * 1. Row * * Database: testCreate Database: CREATE DATABASE `test` / *! 40100 DEFAULT CHARACTER SET utf8 * / 1 row in set (0.00 sec)

2.2. Delete the database

Deleting a database removes an existing database from disk space, and after it is cleared, all data in the database will be deleted as well. 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 underlying software component of the database. The database management system (DBMS) uses the data engine to create, query, update and delete data. Different storage engines provide different storage mechanisms, indexing techniques, locking levels and other functions. The core of MySQL is the storage engine.

MySQL provides a variety of different storage engines, including an engine for transactional security tables and an engine for non-transactional security tables. In MySQL, instead of using the same storage engine throughout the server, you can use a different engine for each table. You can use the SHOW ENGINES statement to view the types of engines supported by the system:

Mysql > SHOW ENGINES\ gateway * 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)

The main features of common storage engines are:

MyISAM: storage format: .frm: table structure. MYD: table data. MYI: table index

Features: support full-text index, can compress: used to implement data warehouse, can save storage space and improve performance; support spatial index; support table-level lock; support deferred update index; do not support transaction, foreign key and row-level lock; cannot safely recover data after crash

Applicable scenarios: read-only data, smaller tables, ability to tolerate modifications after crashes, and data loss.

InnoDB: two storage formats

Innodb_file_per_table=OFF, even with shared tablespaces

A unique format definition file for each table: tb_name.frm

Tablespace file shared under the default data directory: ibdata#

Innodb_file_per_table=ON, even with independent tablespaces

Each table stores two files in the database directory: tb_name.frm tb_name.ibd

Tablespace: a unique format data file managed by InnoDB that stores data and indexes internally

Features: support transactions, have transaction logs; support foreign keys; multi-version concurrency control MVCC; supports clustered indexes

(indexes other than clustered indexes, often called secondary indexes); support row-level locks (gap locks); support secondary indexes; support adaptive hash indexes; support hot backup.

ARCHIVE: only support INSERT and SELECT, support good compression function; do not support transactions, can not well support index; suitable for storing log information, or other applications of data collection based on time series. CSV: stores data in CSV format; does not support indexing; only applies to data exchange scenarios. BLACKHOLE: no storage mechanism, any data sent to this engine will be discarded; it records binary logs, so it is often used as a transit server in a multi-level replication architecture. MEMORY: store data in memory, memory tables; commonly used to store intermediate data, such as periodic aggregate data; also used to implement temporary tables; support hash indexes, use table-level locks, and do not support BLOB and TEXT data types. MRG_MYISAM: is a variant of MYISAM that combines multiple MyISAM tables into a single virtual table. NDB: is a dedicated storage engine in MySQL CLUSTER.

Storage engine comparison:

Functional MyISAMMemoryInnoDBArchive storage restrictions 256TBRAM64TBNone supports transactions NoNoYesNo supports full-text indexing YesNoNoNo supports number of indexes

YesYesYesNo supports hash indexing

NoYesNoNo supports data indexing

NoN/AYesNo supports foreign keys

NoNoYesNo

Third-party storage engine:

OLTP class: (On-Line Transaction Processing online transaction processing)

XtraDB: enhanced InnoDB, provided by Percona; when compiled and installed, replace the source code of InnoDB in the MySQL storage engine with the source code of XtraDB.

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

TokuDB: use Fractal Trees index, suitable for storing big data, with very compression ratio; MariaDB is introduced.

Column storage engine:

Infobright: a well-known determinant engine, suitable for mass data storage scenarios, such as PB level, specially designed for data analysis and data warehouse.

InfiniDB 、 MonetDB 、 LucidDB

Open source community storage engine:

Aria: formerly known as Maria, it can be understood as an enhanced version of MyISAM (support for secure recovery after crash, data caching)

Groona: full-text indexing engine. Mroonga is a secondary development version based on Groona.

OQGraph: a storage engine developed by Open Query and supporting graph structure

SphinxSE: provides SQL interface for Sphinx full-text search server

Spider: data can be sliced into different slices, and parallel queries are supported on slicing.

Criteria for selecting an engine:

Whether a transaction is required; support for the type of backup; recovery after a crash; unique features.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.