MariaDB (MySQL) creation, deletion, selection, and data type Usage Details, mariadbmysql

MariaDB (MySQL) creation, deletion, selection, and data type Usage Details, mariadbmysql

1. Introduction to MariaDB (skipped in MySQL)

The MariaDB database management system is a branch of MySQL and is mainly maintained by the open-source community. The purpose of using GPL to authorize MariaDB is to be fully compatible with MySQL, including APIs and command lines, it can easily become a substitute for MySQL. In terms of storage engine, XtraDB is used to replace InnoDB of MySQL. MariaDB was developed by Michael Widenius, MySQL founder. He sold his own company MySQL AB to SUN for $1 billion, as SUN was acquired by Oracle, the ownership of MySQL also fell into the hands of Oracle. MariaDB is named by Michael Widenius's daughter Maria.

MariaDB is a transaction-based Maria storage engine that replaces MySQL's MyISAM storage engine. It uses the XtraDB and InnoDB variants of Percona, branch developers want to provide access to the upcoming MySQL 5.4 InnoDB performance. This version also includes the PrimeBase XT (PBXT) and FederatedX storage engines.

Ii. Detailed description of MariaDB's creation, deletion, selection, and data type usage (Note: This article demonstrates root permission)

(1) MariaDB creates a database

1.1: Use mysqladmin to create a database

[Root @ test01 10.19.166.166 ~ ] # Mysqladmin-u root-p create testdb1Enter password: // enter the password of the MariaDB database here [root @ test01 10.19.166.166 ~ ] # Mysql MariaDB [(none)]> show databases; + -------------------- + | Database | + -------------------- + | hellodb | information_schema | mydb | mysql | performance_schema | test | testdb | testdb1 | <--- created Database + -------------------- +

1.2: log on to the MariaDB database to create

MariaDB [(none)]> create database testdb2; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> show databases; + -------------------- + | Database | + -------------------- + | hellodb | information_schema | mydb | mysql | performance_schema | test | testdb | testdb1 | testdb2 | <--- created database + -------------------- +

(2) MariaDB deletes a database

2.1: Use mysqladmin to delete a database

[Root @ test01 10.19.166.166 ~ ] # Mysqladmin-u root-p drop testdb1Enter password: // enter the Database password // After executing the preceding DELETE command, a prompt box appears, whether to delete this database Dropping the database is potentially a very bad thing to do. any data stored in the database will be destroyed. do you really want to drop the 'testdb1' database [y/N] y // delete Database "testdb1" dropped

Log on to view

MariaDB [(none)]> show databases; // confirm that testdb1 has been deleted + -------------------- + | Database | + -------------------- + | hellodb | information_schema | mydb | mysql | performance_schema | test | testdb | testdb2 | + -------------------- +

2.2: log on to MariaDB to delete the database.

MariaDB [(none)]> drop database testdb2; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show databases; // confirm that testdb2 has been deleted + -------------------- + | Database | + -------------------- + | hellodb | information_schema | mydb | mysql | performance_schema | test | testdb | + region +

(3) Select a database for MariaDB and view all tables under the selected database.

MariaDB [(none)]> show databases; // view all databases + ------------------ + | Database | + -------------------- + | hellodb | information_schema | mydb | mysql | performance_schema | test | testdb | + tables + 7 rows in set (0.00 sec) mariaDB [(none)]> use mydb // select mydb Database changed MariaDB [mydb]> show tables; // view all table information under the mydb database + ---------------- + | Tables_in_mydb | + ---------------- + | ssc | t1 | tb2 | tb4 | tb5 | + ---------------- + 5 rows in set (0.00 sec)

(4) MariaDB Data Type
The type of data fields defined in MariaDB is very important for database optimization.
MariaDB supports multiple types, which can be roughly divided into three types: numerical value, date/time, and string (character type)

4.1 numeric type

Type	Size	Range (Signed)	Range (unsigned)	Purpose
TINYINT	1 byte	(-128,127)	(0,255)	Small integer
SMALLINT	2 bytes	(-32 768,32767)	(535)	Large integer
MEDIUMINT	3 bytes	(-8 388 388 607)	(777 215)	Large integer
INT or INTEGER	4 bytes	(-2 147 483 147 483, 2 647)	(294 967 295)	Large integer
BIGINT	8 bytes	(-9 233 372 036 854 775 223 372 854 775 807)	(446 744 709 073 551 615)	Maximum integer
FLOAT	4 bytes	(-3.402 823 466 E + 38, 1.175 494 351 E-38), 0, (1.175 494 351 E-38, 3.402 823 466 351 E + 38)	0, (1.175 494 351 E-38, 3.402 823 466 E + 38)	Precision Floating point value
DOUBLE	8 bytes	(1.797 693 134 862 315 7 E + 308, 2.225 073 858 507 201 4 E-308), 0, (2.225 073 858 507 201 4 E-308, 1.797 693 134 862 315 7 E + 308)	0, (2.225 073 858 507 201 4 E-308, 1.797 693 134 862 7 E + 315)	Double Precision Floating point value
DECIMAL	For DECIMAL (M, D), if M> D is M + 2, otherwise D + 2	Values dependent on M and D	Values dependent on M and D	Small value

4.2 Date and Time types

The DATE and TIME types of the TIME value are DATETIME, DATE, TIMESTAMP, TIME, and YEAR.
Each time type has a valid value range and a "zero" value. It is used when an invalid MySQL value cannot be expressed.
The TIMESTAMP type has a proprietary automatic update feature, which will be described later.

Type	Size (bytes)	Range	Format	Purpose
DATE	3	April 1000-01-01/9999-12-31	YYYY-MM-DD	Date Value
TIME	3	'-838: 59: 59'/'2014: 59: 59'	HH: MM: SS	Time Value or duration
YEAR	1	1901/2155	YYYY	Year Value
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH: MM: SS.	Mixed Date and Time values
TIMESTAMP	8	2037 00:00:00/	YYYYMMDD HHMMSS	Mixed Date and Time Value, timestamp

4.3 string type

String type: CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, and SET. This section describes how these types work and how to use them in queries.

Type

Size

Purpose

CHAR	0-255 bytes	Fixed Length string
VARCHAR	0-255 bytes	Variable-length string
TINYBLOB	0-255 bytes	A binary string of no more than 255 characters
TINYTEXT	0-255 bytes	Short text string
BLOB	0-65 535 bytes	Long text data in binary format
TEXT	0-65 535 bytes	Long text data
MEDIUMBLOB	0-16 777 215 bytes	Moderate-length text data in binary format
MEDIUMTEXT	0-16 777 215 bytes	Moderate-length text data
LOGNGBLOB	0-4 294 967 295 bytes	Extremely large text data in binary form
LONGTEXT	0-4 294 967 295 bytes	Large text data

CHAR and VARCHAR types are similar, but they are stored and retrieved in different ways. Their maximum length and whether the trailing space is retained are also different. Case-insensitive conversion is not performed during storage or retrieval.

The BINARY and VARBINARY classes are similar to CHAR and VARCHAR. The difference is that they contain BINARY strings instead of BINARY strings. That is, they contain byte strings instead of character strings. This indicates that they do not have character sets and sort and compare the value values based on the column value byte.
BLOB is a large binary object that can hold a variable amount of data. There are four BLOB types: TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. They only have different maximum lengths that can hold values.

There are four types of TEXT: TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. These correspond to four BLOB types, with the same maximum length and storage requirements.