https://www.tutorialspoint.com/mariadb

Installation

Testing the Installation

# mysqladmin –version
mysql> SHOW DATABASES;

Post-Installation

# mysqladmin –u root password “[enter your password here]”;
# mysql –u root –p #connect to the server with your new credentials
#Administration
Three options for starting and stopping MariaDB.

• Run mysqld (the MariaDB binary).
• Run the mysqld_safe startup script.
• Run the mysql.server startup script.

Creating a User Account

‘newusername’@’localhost’ IDENTIFIED BY ‘userpassword’;
GRANT SELECT, INSERT, UPDATE, DELETE ON database1 TO ‘newusername’@‘localhost’;
A “FLUSH PRIVILEGES” operation tells the server to reload the grant tables.
MariaDB uses the system tables created with “mysql_install_db” to manage user rights and provide the data for the ‘help’ command

Administration Commands

• USE [database name]
• SHOW DATABASES
• SHOW TABLES
• SHOW COLUMNS FROM [table name]
• SHOW INDEX FROM TABLENAME [table name]
• SHOW TABLE STATUS LIKE [table name]\G

Connection

# mysql –u root –p

Create Database

# mysqladmin –u root –p create PRODUCTS
mysql > CREATE DATABASE PRODUCTS;

Drop Database

#mysqladmin –u root –p drop PRODUCTS;
mysql > DROP DATABASE PRODUCTS;

Data Types

MariaDB data types can be categorized as numeric, date and time, and string values.

Numeric Data Types

TINYINT, BOOLEAN, SMALLINT, MEDIUMINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE, BIT

Date and Time Data Types

DATE, TIME, DATETIME, TIMESTAMP, YEAR

Create Tables

mysql> CREATE TABLE products_tbl(product_id INT NOT NULL AUTO_INCREMENT, product_name VARCHAR(100) NOT NULL, product_manufacturer VARCHAR(40) NOT NULL, submission_date DATE, PRIMARY KEY ( product_id ));

Drop Tables

mysql> DROP TABLE products_tbl;

Insert Query

mysql> INSERT INTO tablename (field, field2, …) VALUES (value, value2, …), (value, value2, …), (value, value2, …),…;

Select Query

mysql> SELECT field, field2,… FROM table_name, table_name2,… WHERE…;

Where Clause

[COMMAND] field,field2,… FROM table_name,table_name2,… WHERE [CONDITION]

Update Query

mysql> UPDATE table_name SET field=new_value, field2=new_value2,…[WHERE …]

Delete Query

mysql> DELETE FROM table_name [WHERE …]

Like Clause

mysql> SELECT product_id, product_name, product_manufacturer, ship_date FROM products_tbl WHERE product_manufacturer LIKE “xyz%”

Order By Clause

mysql> SELECT product_id, product_name, product_manufacturer, ship_date FROM products_tbl ORDER BY product_manufacturer DESC;

Join

mysql> SELECT products.ID_number, products.Nomenclature, inventory.inventory_ct FROM products INNER JOIN inventory ON products.ID_numbeer = inventory.ID_number;

Null

NULL is case-insensitive, and has these two aliases – UNKNOWN (a boolean value), \N

NULL Operators

• IS NULL – It tests for a NULL value.
• IS NOT NULL – It confirms the absence of a NULL value.
• ISNULL
• COALESCE

Sorting NULL Values

DESC order results in NULL values at the bottom.

NULL Functions

IFNULL(), NULLIF()

Inserting NULL Values

On insertion of a NULL value in a column declared NOT NULL, an error occurs.

Regular Expression

Beyond the pattern matching available from LIKE clauses, MariaDB offers regular expression-based matching through the REGEXP/NOT REGEXP operator.
mysql> SELECT column FROM table_name WHERE column REGEXP ‘[PATTERN]’;

• ^, it matches the start of the string.
• $, it matches the string’s end.
• ., it matches a single character.
• [ ], it matches any character in the brackets.
• [^ ], it matches any character not in the brackets.
• p1|p2|p3, it matches any of the patterns.
• *, it matches 0 or more instances of the preceding element.
• +, it matches 1 or more instances of the preceding element.
• {n}, it matches n instances of the precede##ng element.
• {m,n} it matches m to n instances of the preceding element.

Transactions

Transactions are sequential group operations. They function as a single unit, and do not terminate until all operations within the group execute successfully. A single failure in the group causes the entire transaction to fail, and causes it to have no impact on the database.
Transactions conform to ACID (Atomicity, Consistency, Isolation, and Durability)

• START TRANSACTION begins the transaction.
• COMMIT saves changes to data.
• ROLLBACK ends the transaction, destroying any changes.
  On a successful transaction, COMMIT acts. On a failure, ROLLBACK acts.

Alter Command

The ALTER command provides a way to change an existing table’s structure, meaning modifications like removing or adding columns, modifying indices, changing data types, or changing names. ALTER also waits to apply changes when a metadata lock is active.
mysql> ALTER TABLE products_tbl DROP version_num;
mysql> ALTER TABLE products_tbl ADD discontinued CHAR(1);
ALTER TABLE products_tbl ADD discontinued CHAR(1) FIRST;
ALTER TABLE products_tbl ADD discontinued CHAR(1) AFTER quantity;
mysql> ALTER TABLE products_tbl CHANGE discontinued status CHAR(4);
mysql> ALTER TABLE products_tbl MODIFY discontinued CHAR(4);
mysql> ALTER TABLE products_tbl ALTER discontinued SET DEFAULT N;
mysql> ALTER TABLE products_tbl ALTER discontinued DROP DEFAULT;
mysql> ALTER TABLE products_tbl TYPE = INNODB;
mysql> ALTER TABLE products_tbl RENAME TO products2016_tbl;
#Indexes & Statistics Tables

• Primary (one record represents all records)
• Unique (one record represents multiple records)
• Plain
• Full-Text (permits many options in text searches)

The terms “key” and “index” are identical in this usage.

Create an Index

CREATE [UNIQUE or FULLTEXT or…] INDEX index_name ON table_name column;
CREATE UNIQUE INDEX top_sellers ON products_tbl product;

Drop an Index

You can drop an index with DROP INDEX or ALTER TABLE…DROP.
DROP INDEX index_name ON table_name;
DROP INDEX top_sellers ON product_tbl;

Rename an Index

ALTER TABLE table_name DROP INDEX index_name, ADD INDEX new_index_name;

Managing Indexes

mysql > SHOW INDEX FROM products_tbl\G

Table Statistics

MariaDB 10.0 made storage engine independent statistics tables available, which calculate data statistics for every table in every storage engine, and even statistics for columns that are not indexed.

Temporary Tables

The TEMPORARY keyword within a CREATE TABLE statement spawns a temporary table.
mysql>CREATE TEMPORARY TABLE order ( item_name VARCHAR(50) NOT NULL, price DECIMAL(7,2) NOT NULL DEFAULT 0.00, quantity INT UNSIGNED NOT NULL DEFAULT 0);
GRANT CREATE TEMPORARY TABLES ON orders TO ‘machine122’@‘localhost’;

Table Cloning

The procedure for duplicating a table is as follows –

• Utilize SHOW CREATE TABLE to produce a CREATE TABLE statement that details the entire structure of the source table.
• Edit the statement to give the table a new name, and execute it.
• Use an INSERT INTO…SELECT statement if you also need the table data copied.

Another method for creating a duplicate uses a CREATE TABLE AS statement.
CREATE TABLE clone_tbl AS SELECT columns FROM original_tbl WHERE conditions];

Sequences

Installing the Sequence Engine

INSTALL SONAME “ha_sequence”;
SHOW ENGINES\G #verify the installation

Creating Sequence

SELECT * FROM seq_77_to_99;
SELECT myseq.seq FROM seq_22_to_28 myseq LEFT JOIN table1 t ON myseq.seq= x.y WHERE x.y IS NULL;
SELECT x1.seq, x2.seq FROM seq_5_to_9 x1 JOIN seq_5_to_9 x2 ORDER BY 5, 6;
SELECT seq FROM seq_3_to_100_step_4;

Managing Duplicates

• Fish for them with JOIN, and delete them with a temporary table.
• Use INSERT… ON DUPLICATE KEY UPDATE to update on discovery of a duplicate.
• Use DISTINCT to prune the results of a SELECT statement and remove duplicates.
• Use INSERT IGNORE to stop insertion of duplicates.

SQL Injection Protection

Always consider data entered by users, suspect and are in need of strong validation prior to any processing. Perform this validation through pattern matching.
Also, utilize the REGEXP operator and LIKE clauses in creating input constraints.
Consider all types of necessary explicit control of input such as:

• Control the escape characters used.
• Control the specific appropriate data types for input. Limit input to the necessary data type and size.
• Control the syntax of entered data. Do not allow anything outside of the needed pattern.
• Control the terms permitted. Blacklist SQL keywords.
  Also, consider the pattern matching and regular expression tools provided by any programming/scripting language paired with MariaDB, which provide more control, and sometimes better control.

Backup Methods

OPTIONS

Logical backups offer the flexibility of restoring data on another machine with a different configuration in contrast to physical backups, which are often limited to the same machine and database type. Logical backups occur at database and table level, and physical occur at directory and file level.
Physical backups are smaller in size than logical, and also take less time to perform and restore. Physical backups also include log and configuration files, but logical backups do not.

Backup Tools

The main tool used for MariaDB backups is mysqldump. It offers logical backups and flexibility. There are three options for mysqldump backups — Raw data, Data/Definitions export, and Transfer.

SELECT…INTO OUTFILE

SELECT customer_id, firstname, surname INTO OUTFILE ‘/exportdata/customers.txt’ FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘"’ LINES TERMINATED BY ‘\n’ FROM customers;

Using CONNECT in Backups

Other Tools

XtraBackup, Snapshots, LVM, TokuBackup

INNODB Considerations

Backup Loading Methods

Using LOAD DATA

mysql> LOAD DATA LOCAL INFILE ‘products_copy.txt’ INTO TABLE empty_tbl FIELDS TERMINATED BY ‘|’ LINES TERMINATED BY ‘\n’;

Using MYSQLIMPORT

The mysqlimport tool acts as a LOAD DATA wrapper allowing the same operations from the command line.
$ mysqlimport -u root -p --local --fields-terminated-by=“|” --lines-terminated-by=“\n” database_name source_file.txt

Using MYSQLDUMP

Restoring with mysqldump requires this simple statement for loading the dump file back into the host −
shell> mysql database_name < source_file.sql

Useful Functions

Aggregate Functions

COUNT, MIN, MAX, AVG, SUM

Age Calculation

SELECT ID, DOB, TIMESTAMPDIFF(YEAR,DOB,‘2015-07-01’) AS age FROM officer_info;

String Concatenation

SELECT CONCAT(‘Ram’, ‘bu’, ‘tan’);

Date/Time Functions

CURDATE(),DATE(), CURTIME(), DATE_SUB(), DATEDIFF(), DATE_ADD(), EXTRACT(), NOW(), DATE_FORMAT(), HOUR(), LOCALTIME(), MICROSECOND(), MINUTE(), SECOND(), TIME_FORMAT(), TIMESTAMP()

Numeric Functions

TRUNCATE(), COS(), CEILING(), DEGREES(), DIV(), EXP(), FLOOR(), LN(), LOG(), SQRT()

String Functions

INSTR(), RIGHT(), LENGTH(), LOCATE(), INSERT(), LEFT(), UPPER(), LOWER(),STRCMP(), REPLACE(), REVERSE(), REPEAT(), SUBSTRING(), TRIM()