In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
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.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.