I. Introduction to MARIADB (MySQL profile skipped)
MARIADB database management System is a branch of MySQL, mainly by the open source community in the maintenance, the use of GPL licensing MARIADB is fully compatible with MySQL, including the API and command line, so that it can easily become a substitute for MySQL. For the storage engine, use XtraDB (English: XtraDB) instead of the MySQL InnoDB. MARIADB, led by MySQL's founder Michael Widenius (English: Michael Widenius), sold his company, MySQL AB, to Sun for $1 billion earlier, and since Sun was acquired by Oracle, MySQL's ownership also falls into Oracle's hands. Mariadb name comes from Michael Widenius's daughter Maria's name.
MARIADB, the transaction-based Maria Storage engine, replaces the MySQL MyISAM storage engine, which uses a variant of the Percona Xtradb,innodb, which the 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. mariadb creation, deletion, selection and use of data types (Note: This article demonstrates root privileges)
(1) mariadb Create a database
1.1: Create a database using Mysqladmin;
[[Email protected] 10.19.166.166 ~ ] # mysqladmin -u root -p create testdb1Enter password: // fill in the password for the MARIADB database [[email protected ] 10.19.166.166 ~ ] # mysqlmariadb [(none)]> show databases;+------ --------------+| database |+------------ --------+| hellodb | | information_schema | | mydb | | mysql | | performance_schema | | test | | testdb | | testdb1 |<---Create a good library +--------------------+
1.2: Log in to the MARIADB library 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 |<---Create a good library +--------------------+
(2) MARIADB Delete database
2.1: Delete database using mysqladmin
[[Email protected] 10.19.166.166 ~] # mysqladmin-u root-p drop testdb1enter Password://Enter the database password//After executing the above delete command, a prompt box will appear, mentioning Confirm the deletion of this database dropping the potentially a very bad thing to do. Any data stored in the database would be destroyed. Do you really want to drop the ' testdb1 ' database [y/n] Y//delete database "TESTDB1" dropped
log in to view
MariaDB [(None)]> show databases; Confirm that TESTDB1 has been removed +--------------------+| Database |+--------------------+| Hellodb | | Information_schema | | MyDB | | MySQL | | Performance_schema | | Test | | TestDB | | TESTDB2 |+--------------------+
2.2: Login mariadb Library Delete
mariadb [(none)]> drop database testdb2; query ok, 0 rows affected (0.00 sec) mariadb [(none)]> show databases; //confirm Testdb2 has been removed +--------------------+| database |+--------------------+| hellodb | | information_schema | | mydb | | mysql | | performance_schema | | test | | testdb |+------------------ --+
(3) mariadb Select a database to view all tables below the selection library
mariadb [(None)]> show databases; //View all libraries +--------------------+| database |+--------------------+| hellodb | | information_schema | | mydb | | mysql | | performance_schema | | test | | testdb |+------------------ --+7 rows in set (0.00 sec) mariadb [(none)]> use mydb // Select MyDB Database Database changed MariaDB [mydb]> show tables; //View all table information under MyDB Database +----------------+| tables_in_mydb |+----------------+| ssc | | t1 | | tb2 | | tb4 | | tb5 |+----------------+5 rows in set (0.00&NBSP;SEC)
(4) MARIADB data type
The types of data fields defined in MARIADB are important for database optimization
MARIADB supports multiple types and can be broadly divided into three categories: numeric, date/time, and string (character type)
4.1 Numerical type
Type |
Size |
Range (signed) |
Range (unsigned) |
Use |
TINYINT |
1 bytes |
(-128,127) |
(0,255) |
Small integer value |
SMALLINT |
2 bytes |
(-32 768,32767) |
(0,65 535) |
Large integer value |
Mediumint |
3 bytes |
(-8 388 608,8 388 607) |
(0,16 777 215) |
Large integer value |
int or integer |
4 bytes |
(-2 147 483 648,2 147 483 647) |
(0,4 294 967 295) |
Large integer value |
BIGINT |
8 bytes |
(-9 233 372 036 854 775 808,9 223 372 036 854 775 807) |
(0,18 446 744 073 709 551 615) |
Maximum integer value |
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) |
Single 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+3 08) |
0, (2.225 073 858 507 201 4 e-308,1.797 693 134 862 315 7 e+308) |
Double precision Floating point value |
DECIMAL |
For decimal (m,d), if m>d, is m+2 otherwise d+2 |
Values that depend on M and D |
Values that depend on M and D |
Decimal value |
4.2 Date and Time type
The date and time types that represent time values are datetime, date, TIMESTAMP, hour, and year.
Each time type has a valid value range and a value of "0", and a value of "0" is used when specifying an illegal MySQL value that cannot be represented.
The timestamp type has a proprietary Automatic Update feature, which is described later.
Type |
Size (bytes) |
Range |
Format |
Use |
DATE |
3 |
1000-01-01/9999-12-31 |
Yyyy-mm-dd |
Date value |
Time |
3 |
' -838:59:59 '/' 838: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 |
Blend date and time values |
TIMESTAMP |
8 |
1970-01-01 00:00:00/2037, sometime |
YYYYMMDD HHMMSS |
Mixed date and time values, timestamp |
4.3 String type
The string type refers to Char, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, enum, and set. This section describes how these types work and how they are used in queries.
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 form |
TEXT |
0-65 535 bytes |
Long Text data |
Mediumblob |
0-16 777 215 bytes |
Medium-length text data in binary form |
Mediumtext |
0-16 777 215 bytes |
Medium-Length text data |
Logngblob |
0-4 294 967 295 bytes |
Large text data in binary form |
Longtext |
0-4 294 967 295 bytes |
Maximum text data |
Char and varchar types are similar, but they are saved and retrieved in different ways. They are also different in terms of their maximum length and whether trailing spaces are retained. No case conversions are made during the storage or retrieval process.
Binary and varbinary classes are similar to char and varchar, but they contain binary strings rather than binary strings. That is, they contain a byte string instead of a character string. This means that they do not have a character set, and sort and compare numeric values based on column-valued bytes.
A blob is a binary large object that can hold a variable amount of data. There are 4 types of blobs: Tinyblob, BLOBs, Mediumblob, and Longblob. They can only accommodate the maximum length of a value differently.
There are 4 types of text: Tinytext, text, Mediumtext, and Longtext. These correspond to 4 types of blobs, with the same maximum length and storage requirements.
This article is from the "Chun Blog" blog, please be sure to keep this source http://ssc4469.blog.51cto.com/6315913/1619909
MariaDB (MySQL) creation, deletion, selection, and data type usage