Detailed explanation of MariaDB (MySQL) creation, deletion, selection and data type usage_MySQL

1. Introduction to MariaDB (the introduction to MySQL is skipped)

          MariaDB database management system is a branch of MySQL, mainly maintained by the open source community, and licensed under GPL. The purpose of MariaDB is to be fully compatible with MySQL, including API and command line, so that it can easily become a replacement for MySQL. In terms of storage engine, XtraDB (English: XtraDB) is used instead of MySQL's InnoDB. MariaDB was developed by Michael Widenius (English: Michael Widenius), the founder of MySQL. He had earlier sold the company he founded, MySQL AB, to SUN for US$1 billion. Since then, as SUN was acquired by Oracle, MySQL The ownership also fell into the hands of Oracle. The name MariaDB comes from the name of Michael Widenius' daughter Maria.

        MariaDB The transaction-based Maria storage engine, which replaces MySQL’s MyISAM storage engine, uses Percona’s XtraDB, a variant of InnoDB that the developers of the fork hope to provide access to the upcoming MySQL 5.4 InnoDB performance. This version also includes PrimeBase XT (PBXT) and FederatedX storage engines.

2. Detailed explanation of MariaDB creation, deletion, selection and data type usage (Note: This article demonstrates root privileges)

(1)MariaDB creates database

1.1: Use mysqladmin to create a database

[root@test01 10.19.166.166 ~ ] 
# mysqladmin -u root -p create testdb1
Enter password:  // 此处填写MariaDB数据库的密码
 
[root@test01 10.19.166.166 ~ ] 
# mysql
 
MariaDB [(none)]> show databases;
+--------------------+
| Database      |
+--------------------+
| hellodb      |
| information_schema |
| mydb        |
| mysql       |
| performance_schema |
| test        |
| testdb       |
| testdb1      |<---创建的好的库
+--------------------+

1.2: Log in to 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      |<---创建好的库
+--------------------+

(2) MariaDB delete database

2.1: Use mysqladmin to delete the database

[root@test01 10.19.166.166 ~ ] 
# mysqladmin -u root -p drop testdb1
Enter password: //输入数据库密码
 
//执行完上面的删除命令后，会出现一个提示框，提示是否确认删除此数据库
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  //删除
Database "testdb1" dropped

Log in to view

MariaDB [(none)]> show databases; //确认testdb1已被删除 
+--------------------+
| Database      |
+--------------------+
| hellodb      |
| information_schema |
| mydb        |
| mysql       |
| performance_schema |
| test        |
| testdb       |
| testdb2      |
+--------------------+

2.2: Log in to MariaDB library to delete

MariaDB [(none)]> drop database testdb2;
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> show databases; //确认testdb2已被删除
+--------------------+
| Database      |
+--------------------+
| hellodb      |
| information_schema |
| mydb        |
| mysql       |
| performance_schema |
| test        |
| testdb       |
+--------------------+

(3) MariaDB selects the database and views all tables under the selected database

MariaDB [(none)]> show databases; //查看所有库
+--------------------+
| Database      |
+--------------------+
| hellodb      |
| information_schema |
| mydb        |
| mysql       |
| performance_schema |
| test        |
| testdb       |
+--------------------+
7 rows in set (0.00 sec)
 
MariaDB [(none)]> use mydb //选择mydb数据库 
Database changed  
MariaDB [mydb]> show tables;  //查看mydb数据库下面所有表信息
+----------------+
| Tables_in_mydb |
+----------------+
| ssc      |
| t1       |
| tb2      |
| tb4      |
| tb5      |
+----------------+
5 rows in set (0.00 sec)

(4) MariaDB data types
The types of data fields defined in MariaDB are very important for database optimization
MariaDB supports multiple types, which can be roughly divided into three categories: numerical values, date/time, and strings (character types)

4.1 Numeric type

Type	Size	Range (signed)	Range (unsigned)	Use
TINYINT	1 byte	(-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)	Extremely large 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, it is M+2 otherwise it is D+2	Depends on the values ​​of M and D	Depends on the values ​​of M and D	Decimal value

4.2 Date and time types

Date and time types representing time values ​​are DATETIME, DATE, TIMESTAMP, TIME and YEAR.
Each time type has a range of valid values ​​and a "zero" value, which is used when specifying an illegal value that MySQL cannot represent.
The TIMESTAMP type has proprietary automatic update features, which will be described later.

Type	Size (bytes)	Scope	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	Mixed date and time values
TIMESTAMP	8	1970-01-01 00:00:00/Sometime in 2037	YYYYMMDDHHMMSS	Mixed date and time values, timestamps

​​​ 4.3 String type

String types refer to 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

Use

CHAR	0-255 bytes	Fixed length string
VARCHAR	0-255 bytes	Variable length string
TINYBLOB	0-255 bytes	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	Extremely large text data in binary form
LONGTEXT	0-4 294 967 295 bytes	Very large text data

CHAR and VARCHAR types are similar, but they are saved and retrieved differently. They also differ in terms of their maximum length and whether trailing spaces are preserved. No case conversion is performed during storage or retrieval.

BINARY and VARBINARY classes are similar to CHAR and VARCHAR, except that they contain binary strings instead of non-binary strings. That is, they contain byte strings rather than character strings. This means that they do not have a character set, and sorting and comparison are based on the numeric value of the column value bytes.
BLOB is a binary large object that can hold a variable amount of data. There are 4 BLOB types: TINYBLOB, BLOB, MEDIUMBLOB and LONGBLOB. They only differ in the maximum length they can hold a value.

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