Detailed explanation of creating, deleting, selecting, and using data types in MariaDB (MySQL)

1. Introduction to MariaDB (MySQL introduction is omitted)

　　　　　　　 MariaDB database management system is a branch of MySQL, mainly maintained by the open-source community, adopting the GPL authorization license. The purpose of MariaDB is to be fully compatible with MySQL, including API and command line, making it easy to become a substitute for MySQL. In terms of storage engines, it uses XtraDB (in English: XtraDB) to replace MySQL's InnoDB. MariaDB is led by Michael Widenius, the founder of MySQL, who had previously10At a price of 1 billion US dollars, he sold his company MySQL AB to SUN, and later, as SUN was acquired by Oracle, the ownership of MySQL also fell into the hands of Oracle. The name MariaDB comes from the name of Michael Widenius' daughter, Maria.

　　　　　　　 MariaDB's transaction-based Maria storage engine replaces MySQL's MyISAM storage engine, using Percona's XtraDB, a variant of InnoDB. The developers of the branch hope 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 Explanation of MariaDB Creation, Deletion, Selection, and Data Type Usage (Note: The demonstration in this article is for root privileges)

(1）MariaDB create database

　　　 1.1: Use mysqladmin to create a database　　　　　　　　　　　

[root@test01 10.19.166.166 ~ ] 
# mysqladmin -u root -p create testdb1
Enter password:  // Enter the password for 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 in to 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 delete database

　　　 2.1: Use mysqladmin to delete the database

[root@test01 10.19.166.166 ~ ] 
# mysqladmin -u root -p drop testdb1
Enter password: //Enter database password
//After executing the above deletion command, a prompt will appear to confirm 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  //Deleted
Database "testdb1" dropped

MariaDB [(none)]> show databases; //Confirm testdb1Deleted 
+--------------------+
| Database      |
+--------------------+
| hellodb      |
| information_schema |
| mydb        |
| mysql       |
| performance_schema |
| test        |
| testdb       |
| testdb2      |
+--------------------+

　 2.2Log in to MariaDB database for deletion

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

(3Select MariaDB database 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       |
+--------------------+
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 the mydb database
+----------------+
| Tables_in_mydb |
+----------------+
| ssc      |
| t1       |
| tb2      |
| tb4      |
| tb5      |
+----------------+
5 rows in set (0.00 sec)

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

　　 4.1 Numeric type

Type	Size	Range (signed)	Range (unsigned)	Purpose
TINYINT	1bytes	(-128,127)	(0,255)	Small integer values
SMALLINT	2bytes	(-32 768,32767)	(0,65 535)	Large integer values
MEDIUMINT	3 bytes	(-8 388 608,8 388 607)	(0,16 777 215)	Large integer values
INT or INTEGER	4 bytes	(-2 147 483 648,2 147 483 647)	(0,4 294 967 295)	Large integer values
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 values
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 values
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 315 7 E+308)	Double precision Floating-point values
DECIMAL	For DECIMAL(M,D), if M>D, it is M+2Otherwise, it is D+2	Dependent on the values of M and D	Dependent on the values of M and D	Decimal values

　　　 4.2 Date and time types

Date and time types that represent time values are DATETIME, DATE, TIMESTAMP, TIME, and YEAR.
Each time type has a valid value range and a 'zero' value. The 'zero' value is used when an illegal value that MySQL cannot represent is specified.
TIMESTAMP type has a special automatic update feature, which will be described later.

Type	Size (bytes)	Range	Format	Purpose
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/2037 Year at a certain time	YYYYMMDD HHMMSS	Mixed date and time values, timestamp

　　　　　 4.3 String types

String types are 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-255bytes	Fixed-length string
VARCHAR	0-255bytes	Variable length string
TINYBLOB	0-255bytes	not exceeding 255 a binary string of characters
TINYTEXT	0-255bytes	Short text string
BLOB	0-65 535bytes	Long text data in binary form
TEXT	0-65 535bytes	Long text data
MEDIUMBLOB	0-16 777 215bytes	Medium length text data in binary form
MEDIUMTEXT	0-16 777 215bytes	Medium length text data
LOGNGBLOB	0-4 294 967 295bytes	Extremely large text data in binary form
LONGTEXT	0-4 294 967 295bytes	Extremely large text data

CHAR and VARCHAR types are similar, but they save and retrieve data in different ways. They also differ in maximum length and whether trailing spaces are preserved, etc. No case conversion is performed during storage or retrieval.

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

There are4Types of TEXT: TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. These correspond to4Types of BLOB (Binary Large Object) with the same maximum length and storage requirements.

Declaration: The content of this article is from the Internet, and the copyright belongs to the original author. The content is contributed and uploaded by Internet users spontaneously, and this website does not own the copyright. It has not been manually edited and does not assume relevant legal liability. If you find any content suspected of copyright infringement, please send an email to: notice#oldtoolbag.com (Please replace # with @ when sending an email for reporting, and provide relevant evidence. Once verified, this site will immediately delete the content suspected of infringement.)

Basic Tutorial