Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Basic Application of Mariadb

2025-01-26 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

Mariadb Overview:

MariaDB database management system is a branch of MySQL, which is mainly maintained by the open source community and licensed by GPL. One of the reasons for developing this branch is that after Oracle acquired MySQL, there is a potential risk of shutting down MySQL, so the community uses a branching approach to avoid this risk.

The goal of MariaDB is to be fully compatible with MySQL, including API and the command line, making it an easy replacement for MySQL. In terms of the storage engine, version 10.0.9 has used XtraDB (code name Aria) instead of MySQL's InnoDB.

MariaDB was developed by Michael Vidnius, the founder of MySQL, who had earlier sold his company, MySQL AB, to SUN for $1 billion, and then MySQL was owned by Oracle as SUN was acquired by Oracle. The name MariaDB comes from the name of Maria (English: Maria), daughter of Michael Vidnius.

The basic concepts of Mariadb:

◆ data type:

Functions: storage format, data range, operations that can be participated in, sorting methods

Character type:

Fixed length character type: CHAR (#), BIARNY (#)

Variable length characters: VARCHAR (#), VARBINARY (#)

Object Storage:

TEXT

BLOB

Built-in type: ENUM,SET

Numerical type:

Exact numerical type:

INT:TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT

Approximate numerical type:

FLOAT

DOUBLE

Date and time type:

DATE

TIME

DATETIME

TIMESTAMP

YEAR

Modifiers: NOT NULL, DEFAULT

Type character: UNSIGNED

The design paradigm of ◆ database:

The first paradigm: fields are atomic

Second normal form: there are available primary keys

Third paradigm: no field should depend on the non-primary key field of other tables

◆ data constraints: restrictions to follow when inserting data into a data table

Primary key: primary key, a combination of one or more fields on a table that fills in the data in the primary key field, must be different from the same field in other rows that already exist, and cannot be empty; only one primary key can be stored in a table

Unique key: unique key, a combination of one or more fields on a table, in which the data in the field must be different from the data on the same field in other rows that already exist, but can be empty; a table can have multiple unique keys

Foreign key: foreign key, the range of values that can be inserted into a foreign key field in one table, depending on the existing data set on the primary key field on the referenced table

Check conditional constraints: check, custom logical expressions

The command line client program mysql:

Mysql [OPTIONS] [database]

Common options:

-uUSERNAME

-hHOST

-pPASSWORD

-Ddb_name

-S sock_file_path

-P port

-e 'STATEMENT'

[root@localhost ~] # mysql-uroot-h227.0.0.1 # use root to log in to Welcome to the MariaDB monitor. Commands end with; or\ g.Your MariaDB connection id is 3Server version: 5.5.44-MariaDB MariaDB ServerCopyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.MariaDB [(none)] >

Command:

Client commands: executing on client

Mysql > help

\?: get available command help

\ Q: exit the client program

\ d CHAR: custom statement Terminator

\ g: statement end tag

\ G: statement closing tag, displaying the results vertically

\! SHELL_CMD: running the shell command

\ s: current connection and server-related status information

\.

MariaDB [(none)] > helpGeneral information about MariaDB can be found at http://mariadb.orgList of all MySQL commands:Note that all text commands must be first on line and end with'; (\?) Synonym for `help'.clear (\ c) Clear the current input statement.connect (\ r) Reconnect to the server. Optional arguments are db and host.delimiter (\ d) Set statement delimiter.edit (\ e) Edit command with $EDITOR.ego (\ G) Send command to mysql server, display result vertically.exit (\ Q) Exit mysql. Same as quit.go (\ g) Send command to mysql server.help (\ h) Display this help.nopager (\ n) Disable pager, print to stdout.notee (\ t) Don't write into outfile.pager (\ P) Set PAGER [to_pager]. Print the query results via PAGER.print (\ p) Print current command.prompt (\ R) Change your mysql prompt.quit (\ Q) Quit mysql.rehash (\ #) Rebuild completion hash.source (\.) Execute an SQL script file. Takes a file name as an argument.status (\ s) Get status information from the server.system (\!) Execute a system shell command.tee (\ T) Set outfile [to_outfile]. Append everything into given outfile.use (\ u) Use another database. Takes database name as argument.charset (\ C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.warnings (\ W) Show warnings after every statement.nowarning (\ w) Don't show warnings after every statement.For server side help, type 'help contents'

Server command: SQL statement, sent to the server to run and retrieve the result; explicit statement Terminator is required

DDL: data definition language, mainly used for database components, such as databases, tables, indexes, views, triggers, event schedulers, stored procedures, stored functions

CREATE, ALTER, DROP

DML: data manipulation language, CRUD operation, mainly used to manipulate data in tables

INSERT,DELETE,UPDATE,SELECT

DCL: data control language

GRANT, REVOKE

The SQL statement of Mariadb:

◆ gets help:

Mysql > help KEYWORD

Mysql > help contents

◆ database management:

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name

ALTER {DATABASE | SCHEMA} [db_name] CHARACTER SET [=] charset_name

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

Related commands:

SHOW CHARACTER SET

SHOW COLLATION

SHOW CREATE DATABASE db_name

MariaDB [(none)] > SHOW CREATE DATABASE xiaoshui +-+-+ | Database | Create Database | +- -+-+ | xiaoshui | CREATE DATABASE `xiaoshui` / *! 40100 DEFAULT CHARACTER SET latin1 * / | +- -+ 1 row in set (0.00 sec) MariaDB [(none)] > ALTER DATABASE xiaoshui CHARACTER SET 'utf8'-> Query OK, 1 row affected (0.00 sec) MariaDB [(none)] > SHOW CREATE DATABASE xiaoshui +-+ | Database | Create Database | +- +-+ | xiaoshui | CREATE DATABASE `xiaoshui` / *! 40100 DEFAULT CHARACTER SET utf8 * / | + -+ 1 row in set (0.00 sec)

◆ Management:

Table creation:

CREATE TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_options]

Create_definition: a comma-separated list

Field definition:

Column_name column_defination

Constraint definition:

PRIMARY KEY (col1 [, col2,....])

UNIQUE KEY

FOREIGN KEY

CHECK (expr)

Index definition:

{INDEX | KEY}

{FULLTEXT | SPATIAL}

Column_definition:

Data_type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string']

MariaDB [xiaoshui] > CREATE TABLE tbl1 (id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMgender ENUM ('Flying Magnum M') DEFAULT' Manners, unique KEY (name,gender), INDEX (name); Query OK, 0 rows affected (0.06 sec) MariaDB [xiaoshui] > DESC tbl;ERROR 1146 (42S02): Table 'xiaoshui.tbl' doesn't existMariaDB [xiaoshui] > DESC tbl1 +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | id | smallint (5) unsigned | NO | PRI | NULL | auto_increment | | name | char (30) | NO | MUL | NULL | | age | tinyint (3) unsigned | YES | | NULL | | | gender | enum ('F') | 'M') | YES | | M | | +-+-- + 4 rows in set (0.00 sec)

Table_option:

ENGINE [=] engine_name

View the types of storage engines supported by the database:

SHOW ENGINES

View table status information:

SHOW TABLE STATUS [WHERE CLAUSE] [LIKE CLAUSE]

MariaDB [xiaoshui] > show TABLE STATUS\ gateway * 1. Row * * Name: tbl1 Engine: InnoDB Version: 10 Row_format: Compact Rows: 0 Avg_row_length: 0 Data_length: 16384Max_data_length : 0 Index_length: 0 Data_free: 0 Auto_increment: 1 Create_time: 2016-10-15 14:27:05 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 1 row in set (2016 sec)

Table modification:

ALTER TABLE tbl_name [alter_specification [, alter_specification]...]

Alter_specification:

(1) Table options

ENGINE=engine_name

...

(2) Table definition

(a) Field

ADD

DROP

CHANGE

MODIFY

(B) Keys and indexes

ADD {PRIMARY | UNIQUE | FOREIGN} key (col1, col2,...)

ADD INDEX (col1, col2,...)

DROP {PRIMARY | UNIQUE | FOREIGN} KEY key_name

DROP INDEX index_name

View the index information on the table:

SHOW INDEXES FROM tbl_name

Example:

# delete index MariaDB [xiaoshui] > ALTER TABLE tbl1 DROP INDEX name_2;Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: add field ClassID field (default is last) MariaDB [xiaoshui] > ALTER TABLE tbl1 ADD classID TINYINT UNSIGNED NOT NULL;Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0MariaDB [xiaoshui] > DESC tbl1 +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | id | smallint (5) unsigned | NO | PRI | NULL | auto_increment | | name | char (30) | NO | MUL | NULL | | age | tinyint (3) unsigned | YES | | NULL | gender | enum ('F') | 'M') | YES | | M | | classID | tinyint (3) unsigned | NO | | NULL | | +-+-- -+ # adjust ClassID to MariaDB [xiaoshui] > ALTER TABLE tbl1 MODIFY ClassID TINYINT UNSIGNED NOT NULL after age after the age field Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0MariaDB [xiaoshui] > DESC tbl1 +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | id | smallint (5) unsigned | NO | PRI | NULL | auto_increment | | name | char (30) | NO | MUL | NULL | | age | tinyint (3) unsigned | YES | | NULL | ClassID | tinyint (3) unsigned | NO | | NULL | | gender | enum ('F') | 'M') | YES | | M | | +-+-- + 5 rows in set (0.00 sec)

Table deletion:

DROP TABLE [IF EXISTS] tbl_name [, tbl_name]...

View the table creation statement:

SHOW CREATE TABLE tbl_name

Index Management:

Create:

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name ON tbl_name (index_col_name,...)

Index_col_name:

Col_name [(length)] [ASC | DESC]

Delete:

DROP INDEX index_name ON tbl_name

View:

SHOW {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name [{FROM | IN} db_name] [WHERE expr]

Example:

# delete the index MariaDB [xiaoshui] > DROP INDEX name ON tbl1;Query OK of the name field on tbl1, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: add the index MariaDB [xiaoshui] > CREATE INDEX name_and_gender ON tbl1 (name (5), gender) for the name and gender fields on tbl1; Query OK, 0 rows affected (0.51sec) Records: 0 Duplicates: 0 Warnings: 0MariaDB [xiaoshui] > SHOW INDEXES FROM tbl1 +- -+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +- -- +- -+ | tbl1 | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | tbl1 | 1 | name_and_gender | 1 | name | A | 0 | 5 | NULL | | | BTREE | | tbl1 | 1 | name_and_gender | 2 | gender | A | 0 | NULL | NULL | YES | BTREE | +-| -+- -+ 3 rows in set (0.00 sec)

DML statement:

INSERT,SELECT,DELETE,UPDATE

INSERT:

INSERT [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...), (...),...

SELECT:

(1) SELECT * FROM tbl_name

(2) SELECT col1, col2, FROM tbl_name

Field alias: col1 AS ALIAS

(3) ELECT col1, col2,... FROM tbl_name WHERE CLUASE

WHERE expr: Boolean expression

Col_name OPERATOR value | col_name

Operator:

>, INSERT INTO tbl1 (name,ClassID) VALUE ('tom',1); Query OK, 1 row affected (0.05 sec) MariaDB [xiaoshui] > SELECT * FROM tbl1 +-+ | id | name | age | ClassID | gender | +-+ | 1 | tom | NULL | 1 | M | +-+-- +-+-+ 1 row in set (0.00 sec) # insert multiple data MariaDB [xiaoshui] > INSERT INTO tbl1 (name ClassID) VALUES ('xiaoshui',2), (' zhao',1) Query OK, 2 rows affected (0.01sec) Records: 2 Duplicates: 0 Warnings: 0MariaDB [xiaoshui] > SELECT * FROM tbl1 +-+ | id | name | age | ClassID | gender | +-+ | 1 | tom | NULL | 1 | M | 2 | xiaoshui | NULL | 2 | M | | 3 | zhao | NULL | 1 | M | +-+ 3 rows in set (0.00 sec) # if no field is specified Then fill in the value of each field MariaDB [xiaoshui] > SELECT * FROM tbl1. +-+ | id | name | age | ClassID | gender | +-+ | 1 | tom | NULL | 1 | M | 2 | xiaoshui | NULL | 2 | M | | 3 | zhao | NULL | 1 | M | 4 | bla | 22 | 1 | F | +-+ 4 rows in set (0.00 sec) # query all data in the table MariaDB [xiaoshui] > SELECT * FROM tbl1 +-+ | id | name | age | ClassID | gender | +-+ | 1 | tom | NULL | 1 | M | 2 | xiaoshui | NULL | 2 | M | | 3 | zhao | NULL | 1 | M | 4 | bla | 22 | 1 | F | +-+ # query required fields MariaDB [xiaoshui] > SELECT name Gender FROM tbl1 +-+-+ | name | gender | +-+-+ | tom | M | | xiaoshui | M | zhao | M | bla | F | +-+-+ 4 rows in set (0.00 sec) # find MariaDB conditionally using where statement [xiaoshui] ] > SELECT name FROM tbl1 WHERE name LIKE'% o%' +-+ | name | +-+ | tom | | xiaoshui | | zhao | +-+ 3 rows in set (0.00 sec) # use RLIKE regular expressions to match content MariaDB [xiaoshui] > SELECT name FROM tbl1 WHERE name RLIKE'^. * o.matches' +-+ | name | +-+ | tom | | xiaoshui | | zhao | +-+ 3 rows in set (0.00 sec) # use the encirclement of IN as the search condition MariaDB [xiaoshui] > SELECT name,ClassID FROM tbl1 WHERE ClassID IN (1 xiaoshui 2) +-+-+ | name | ClassID | +-+-+ | tom | 1 | | xiaoshui | 2 | zhao | 1 | bla | 1 | +-+-+ 4 rows in set (0.00 sec) # sort in reverse order by id Order MariaDB [xiaoshui] > SELECT * FROM tbl1 ORDER BY id DESC +-+ | id | name | age | ClassID | gender | +-+ | 4 | bla | 22 | 1 | F | 3 | zhao | | NULL | 1 | M | | 2 | xiaoshui | NULL | 2 | M | | 1 | tom | NULL | 1 | M | +-+ 4 rows in set (0.00 sec) # sort by ClassID first | If the ClassID is the same, sort MariaDB [xiaoshui] > SELECT * FROM tbl1 ORDER BY ClassID,name through name +-+ | id | name | age | ClassID | gender | +-+ | 4 | bla | 22 | 1 | F | 1 | tom | | NULL | 1 | M | | 3 | zhao | NULL | 1 | M | | 2 | xiaoshui | NULL | 2 | M | +-+ 4 rows in set (0.00 sec) |

DELETE:

DELETE FROM tbl_name

[WHERE where_condition]

# Delete the lines of id=4 MariaDB [xiaoshui] > DELETE FROM tbl1 WHERE id=4;Query OK, 1 row affected (0.01 sec) MariaDB [xiaoshui] > SELECT * FROM tbl;ERROR 1146 (42S02): Table 'xiaoshui.tbl' doesn't existMariaDB [xiaoshui] > SELECT * FROM tbl1 +-+ | id | name | age | ClassID | gender | +-+ | 1 | tom | NULL | 1 | M | 2 | xiaoshui | NULL | 2 | M | | 3 | zhao | NULL | 1 | M | +-+ 3 rows in set (0.00 sec)

UPDATE:

UPDATE table_reference SET col_name1= {expr1 | DEFAULT} [, col_name2= {expr2 | DEFAULT}].

[WHERE where_condition]

# set line age=17MariaDB [xiaoshui] > UPDATE tbl1 SET age=17 WHERE id=2;Query OK of id 2, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0MariaDB [xiaoshui] > SELECT * FROM tbl1-> +-+ | id | name | age | ClassID | gender | +-+ | 1 | tom | NULL | 1 | M | 2 | xiaoshui | 17 | | 2 | M | | 3 | zhao | NULL | 1 | M | +-+ 3 rows in set (0.00 sec) MariaDB [xiaoshui] > |

Thank you for browsing.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 285

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report