MARIADB (MySQL) Create, delete, select and data type use detailed _mysql

Introduction of MARIADB (MySQL introduction skip)

MARIADB database management System is a branch of MySQL, mainly by the open source community in the maintenance, the use of GPL licensed license MARIADB is fully compatible with MySQL, including APIs and command line, so that it can easily become a replacement for MySQL. On the storage engine side, use XTRADB (English: xtradb) to replace the MySQL InnoDB. MARIADB, led by MySQL founder Michael Widenius (English: Michael Widenius), had previously sold his company, MySQL AB, to Sun at a price of 1 billion dollars, and since then, as Sun was acquired by Oracle, MySQL's ownership also falls into Oracle's hands. The name of MARIADB is from the name of Michael Widenius's daughter Maria.

The mariadb Maria storage engine, which replaces MySQL's MyISAM storage engine, uses Percona's XTRADB,INNODB variants, and branch developers want to provide access to the upcoming MySQL 5.4 InnoDB performance. This version also includes the PrimeBase XT (PBXT) and the Federatedx storage engine.

Ii. mariadb of the creation, deletion, selection and use of data types (Note: This article is shown as root permissions)

(1) MARIADB CREATE database

1.1: Create a database using Mysqladmin

[root@test01 10.19.166.166 ~] 
# mysqladmin-u Root-p Create testdb1
Enter Password:  //Here to fill in the MARIADB database password
 
[root@test01 10.19.166.166 ~]
   # MySQL
 
mariadb [(none)]> show databases;
+--------------------+
| Database      |
+--------------------+
| hellodb      |
| information_schema |
| mydb        |
| mysql       |
| Performance_schema |
| Test        |
| | testdb       |
| TESTDB1      |<---created a good library
+--------------------+

1.2: Login MARIADB Library creation

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 the database using Mysqladmin

[root@test01 10.19.166.166 ~] 
# mysqladmin-u root-p Drop testdb1
Enter password://Enter database password
 
//After executing the above delete command, a prompt box prompts you to confirm that you want to delete the database
Dropping the "database is" potentially a very bad thing todo.
Any data stored in the database would be destroyed.
 
Do your really want to drop the ' testdb1 ' database [y/n] y  //delete
database "TESTDB1" dropped

Login View

MARIADB [(None)]> show databases; Confirm TESTDB1 has been deleted 
+--------------------+
| 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 deleted
+--------------------+
| 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 
Changed  
mariadb [mydb]> show tables;  View MyDB Database All table information
+----------------+
| Tables_in_mydb |
+----------------+
| SSC      |
| T1       |
| tb2      |
| TB4      |
| tb5      |
+----------------+
5 rows in Set (0.00 sec)

(4) MARIADB data type
The type of data field defined in MARIADB is important for database optimization
MARIADB supports a variety of types and can be broadly grouped into three categories: numeric, date/time, and string (character type)

4.1 Numeric 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)	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+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 values
DECIMAL	to decimal (m,d), if m>d, for m+2 otherwise for d+2	Values that depend on M and D	Values that depend on M and D	Decimal value

4.2 Date and time types

Date and time types that represent time values are datetime, date, TIMESTAMP, times, and year.
Each time type has a valid range of values and a value of "0" that uses the "0" value when specifying a value that is not valid for MySQL to represent.
The timestamp type has proprietary automatic update attributes, which are 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	Mixed date and time values
TIMESTAMP	8	1970-01-01 00:00:00/2037 A certain time of year	YYYYMMDD HHMMSS	Mixed date and time value, timestamp

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 they are used in queries.

Type

Size

Use

CHAR	0-255 bytes	Fixed-length strings
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	Maximum text data in binary form
Longtext	0-4 294 967 295 bytes	Maximum text data

Char and varchar types are similar, but they are stored and retrieved in different ways. Their maximum length and whether the trailing spaces are retained are also different. No case conversion is made during storage or retrieval.

The binary and varbinary classes are similar to char and varchar, but they contain binary strings rather than binary strings. In other words, they contain a byte string instead of a character string. This means that they do not have a character set, and they sort and compare numeric values based on column-valued bytes.
A blob is a binary large object that can hold a variable number of data. There are 4 types of blobs: Tinyblob, BLOBs, Mediumblob, and Longblob. They are only the maximum lengths that can accommodate the values.

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.