Mariadb introduction, mariadb

Mariadb introduction, mariadb

Transaction: organizes multiple operations into one whole. They are either executed in full or not rolled back or rollback.

SQL interface: SQL statement analyzer and optimizer

Table: Splits a dataset into multiple

 

Constraint: constraint, rules to be followed for data inserted into a data table

Primary Key: a combination of one or more fields. Fill in the data in the primary key. It must be different from existing data. It cannot be blank.

Foreign key: the data that can be inserted in a field in a table depends on the data in the primary key of another table.

Unique key: a combination of one or more fields. Enter the data in the unique key, which must be different from existing data. It can be blank.

Check constraint: Depends on the Expression requirements

 

Index: Extracts one or more fields from a table and organizes them into a unique data structure.

Common Index types: Tree hash (only tables using the MEMORY engine can be used)

Index helps read requests but is not conducive to write requests

 

MariaDB features: plug-in-type storage engine: The Storage Manager has multiple implementation versions, and the features and features may be slightly different from each other. You can flexibly choose as needed

The storage engine is also called "Table type"

 

Comparison between MariaDB and MySQL:

(1) Support for more storage engines

(2) MyISAM --> Aria (release) does not support transactions.

(3) InnoDB --> XtraDB (transaction version) supports transactions

(4) many extensions and new features

(5) provides many test components

(6) completely open-source software

Directly install: yum-y install mariadb-server on centos7 start service: systemctl start mariadb

Data Directory:/var/lib/mysql/command line Input mysql, you can directly log on to the configuration file:/etc/my. cnf. d/server. cnf

Use mysql -- help to view the location and read sequence of the configuration file, and the previous read will be overwritten.

 

DDL (Data Definition Language): CREATE, ALTER, DROP

DML (Data Manipulation Language): INSERT, DELETE, UPDATE, SELECT

DCL (Data Control Language): GRANT, REVOKE

Three socket addresses: IPv4, IPv6 3306/tcp

Unix Sock:/var/lib/mysql. sock (rpm installation)/tmp/mysql. sock (source code installation) through 127.0.0.1 address communication

Client:

Mysql: CLI Interactive Client Program

Mysqldump: Backup Tool

Mysqladmin: administrative tool

Mysqlbinlog: binary log viewing tool

 

Command line Interactive Client Program: mysql

-UUSERNAME: User name. The default value is root.

-HHOST: the address of the remote host (mysql Server). The default value is localhost.

-P [PASSWORD]: PASSWORD of the user indicated by USERNAME; empty by default

Note: The mysql user account is composed of two parts: 'username' @ 'host'. The HOST is used to restrict the remote hosts on which the user can connect to the current mysql server.

The HOST representation. Wildcards are supported. %: match any character of any length. _: match any single character.

 

Mysql-D mysql directly log on to mysql database mysql-e 'show databases' and obtain the execution result of the show databases command on the command line.

 

Client Command: execute local command to get help: MariaDB [(none)]> help for example: Get status information: \ s

Help COMMAND

View the character sets supported by mariadb: show character set;

Create a database: create database nihao character set ustf8;

Delete A database: drop database nihao. You cannot find the database after it is deleted.

View: show database like '';

 

Table creation format: create table [if not exits] tbl_name (create_defination) [table_options]

Create_defination:

Field: col_name data_type

KEY: primary key (col1, col2,...) unique key (col1, col2,...) foreign key (column)

Table_options: engine = egine name

Example: create table students (id int unsigned not null primary key, name char (10) not null, age tinyint unsigned, gender

Enum ('M', 'F '));

Char of the character type has better performance for mariadb than varchar. You can use desc tablename to view the table structure.

Define multiple primary keys: create table students (id int unsigned not null, name char (10) not null, age tinyint unsigned, gender

Enum ('M', 'F'), primary key (id, name ));

Note that unsigned can only be followed by the value type; otherwise, an error is returned.

 

View all the storage engine types supported by the database: show engines; view the storage engine used by the table: show table status \ G; view the specific table: show

Table status like 'tablename ';

 

Modify:

Add a field: alter table students add class varchar (10) not null after name;

After indicates the field to be placed after.

Delete a field: alter table students drop class;

If the name is not modified, only the attributes of a column are modified: alter table students modify class varchar (99) after age;

　　

Delete primary key: alter table students drop primary key;

Add a unique key: alter table students add unique key (name); If the unique key is not null, this primary key is automatically converted.

 

View the table index: show indexes from tablename \ G; (note that the key can make the index, but the index is not necessarily a key)

Delete An index: alter table students drop index name; (name indicates the index name)

Add an index: alter table students add index class (class); [(class) indicates the name of the field, and class indicates the index name]