MariaDB is a fork of the MySQL relational database management system. The original developers of MySQL created MariaDB after concerns raised by Oracle”s acquisition of MySQL. This tutorial will provide a quick introduction to MariaDB, and aid you in achieving a high level of comfort with MariaDB programming and administration.

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) −

• Atomicity − It ensures the success of all operations by aborting on failures and rolling back changes.

• Consistency − It ensures the database applies changes on a successful transaction.

• Isolation − It enables independent transactions operation of transactions.

• Durability − It ensures the persistence of a successful transaction in the event of system failure.

At the head of a transaction statement is the START TRANSACTION statement followed by COMMIT and ROLLBACK statements −

• 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.

Note − Some statements cause an implicit commit, and they also cause an error when used within transactions. Examples of such statements include, but are not limited to CREATE, ALTER, and DROP.

MariaDB transactions also include options like SAVEPOINT and LOCK TABLES. SAVEPOINT sets a restore point to utilize with ROLLBACK. LOCK TABLES allows controlling access to tables during sessions to prevent modifications during certain time periods.

The AUTOCOMMIT variable provides control over transactions. A setting of 1 forces all operations to be considered successful transactions, and a setting of 0 causes persistence of changes to only occur on an explicit COMMIT statement.

Structure of a Transaction

The general structure of a transaction statement consists of beginning with START TRANSACTION. The next step is inserting one or more commands/operations, inserting statements that check for errors, inserting ROLLBACK statements to manage any errors discovered and finally inserting a COMMIT statement to apply changes on successful operations.

Review the example given below −

START TRANSACTION;
SELECT name FROM products WHERE manufacturer = ''XYZ Corp
UPDATE spring_products SET item = name;
COMMIT;

Indexes are tools for accelerating record retrieval. An index spawns an entry for each value within an indexed column.

There are four types of indexes −

• 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.

Indexes associate with one or more columns, and support rapid searches and efficient record organization. When creating an index, consider which columns are frequently used in your queries. Then create one or multiple indexes on them. In addition, view indexes as essentially tables of primary keys.

Though indexes accelerate searches or SELECT statements, they make insertions and updates drag due to performing the operations on both the tables and the indexes.

Create an Index

You can create an index through a CREATE TABLE…INDEX statement or a CREATE INDEX statement. The best option supporting readability, maintenance, and best practices is CREATE INDEX.

Review the general syntax of Index given below −

CREATE [UNIQUE or FULLTEXT or...] INDEX index_name ON table_name column;

Review an example of its use −

CREATE UNIQUE INDEX top_sellers ON products_tbl product;

Drop an Index

You can drop an index with DROP INDEX or ALTER TABLE…DROP. The best option supporting readability, maintenance, and best practices is DROP INDEX.

Review the general syntax of Drop Index given below −

DROP INDEX index_name ON table_name;

Review an example of its use −

DROP INDEX top_sellers ON product_tbl;

Rename an Index

Rename an index with the ALTER TABLE statement. Review its general syntax given below −

ALTER TABLE table_name DROP INDEX index_name, ADD INDEX new_index_name;

Review an example of its use −

ALTER TABLE products_tbl DROP INDEX top_sellers, ADD INDEX top_2016sellers;

Managing Indexes

You will need to examine and track all indexes. Use SHOW INDEX to list all existing indexes associated with a given table. You can set the format of the displayed content by using an option such as “G”, which specifies a vertical format.

Review the following example −

mysql > SHOW INDEX FROM products_tblG

Table Statistics

Indexes are used heavily to optimize queries given the faster access to records, and the statistics provided. However, many users find index maintenance cumbersome. 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.

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.

Using ALTER to Modify Columns

ALTER paired with DROP removes an existing column. However, it fails if the column is the only remaining column.

Review the example given below −

mysql> ALTER TABLE products_tbl DROP version_num;

Use an ALTER…ADD statement to add columns −

mysql> ALTER TABLE products_tbl ADD discontinued CHAR(1);

Use the keywords FIRST and AFTER to specify placement of the column −

ALTER TABLE products_tbl ADD discontinued CHAR(1) FIRST;
ALTER TABLE products_tbl ADD discontinued CHAR(1) AFTER quantity;

Note the FIRST and AFTER keywords only apply to ALTER…ADD statements. Furthermore, you must drop a table and then add it in order to reposition it.

Change a column definition or name by using the MODIFY or CHANGE clause in an ALTER statement. The clauses have similar effects, but utilize substantially different syntax.

Review a CHANGE example given below −

mysql> ALTER TABLE products_tbl CHANGE discontinued status CHAR(4);

In a statement using CHANGE, specify the original column and then the new column that will replace it. Review a MODIFY example below −

mysql> ALTER TABLE products_tbl MODIFY discontinued CHAR(4);

The ALTER command also allows for changing default values. Review an example −

mysql> ALTER TABLE products_tbl ALTER discontinued SET DEFAULT N;

You can also use it to remove default constraints by pairing it with a DROP clause −

mysql> ALTER TABLE products_tbl ALTER discontinued DROP DEFAULT;

Using ALTER to Modify Tables

Change table type with the TYPE clause −

mysql> ALTER TABLE products_tbl TYPE = INNODB;

Rename a table with the RENAME keyword −

mysql> ALTER TABLE products_tbl RENAME TO products2016_tbl;

In this chapter, we will learn how to create tables. Before creating a table, first determine its name, field names, and field definitions.

Following is the general syntax for table creation −

CREATE TABLE table_name (column_name column_type);

Review the command applied to creating a table in the PRODUCTS database −

databaseproducts_ 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 )
);

The above example uses “NOT NULL” as a field attribute to avoid errors caused by a null value. The attribute “AUTO_INCREMENT” instructs MariaDB to add the next available value to the ID field. The keyword primary key defines a column as the primary key. Multiple columns separated by commas can define a primary key.

The two main methods for creating tables are using the command prompt and a PHP script.

The Command Prompt

Utilize the CREATE TABLE command to perform the task as shown below −

root@host# mysql -u root -p
Enter password:*******
mysql> use PRODUCTS;
Database changed
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 )
   -> );
mysql> SHOW TABLES;
+------------------------+
| PRODUCTS               |
+------------------------+
| products_tbl           |
+------------------------+

Ensure all commands are terminated with a semicolon.

PHP Create Table Script

PHP provides mysql_query() for table creation. Its second argument contains the necessary SQL command −

<html>
   <head>
      <title>Create a MariaDB Table</title>
   </head>

   <body>
      <?php
         $dbhost = ''localhost:3036
         $dbuser = ''root
         $dbpass = ''rootpassword
         $conn = mysql_connect($dbhost, $dbuser, $dbpass);

         if(! $conn ){
            die(''Could not connect: '' . mysql_error());
         }
         echo ''Connected successfully<br />

         $sql = "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 )); ";

         mysql_select_db( ''PRODUCTS'' );
         $retval = mysql_query( $sql, $conn );

         if(! $retval ) {
            die(''Could not create table: '' . mysql_error());
         }
         echo "Table created successfullyn";

         mysql_close($conn);
      ?>
   </body>
</html>

On successful table creation, you will see the following output −

mysql> Table created successfully

In this chapter, we will learn how to insert data in a table.

Inserting data into a table requires the INSERT command. The general syntax of the command is INSERT followed by the table name, fields, and values.

Review its general syntax given below −

INSERT INTO tablename (field,field2,...) VALUES (value, value2,...);

The statement requires the use of single or double quotes for string values. Other options for the statement include “INSERT…SET” statements, “INSERT…SELECT” statements, and several other options.

Note − The VALUES() function that appears within the statement, only applies to INSERT statements and returns NULL if used elsewhere.

Two options exist for performing the operation: use the command line or use a PHP script.

The Command Prompt

At the prompt, there are many ways to perform a select operation. A standard statement is given below −

belowmysql>
INSERT INTO products_tbl (ID_number, Nomenclature) VALUES (12345,“Orbitron 4000”);
mysql> SHOW COLUMNS FROM products_tbl;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| ID_number   | int(5)      |      |     |         |       |
| Nomenclature| char(13)    |      |     |         |       |
+-------------+-------------+------+-----+---------+-------+

You can insert multiple rows −

INSERT INTO products VALUES (1, “first row”), (2, “second row”);

You can also employ the SET clause −

INSERT INTO products SELECT * FROM inventory WHERE status = ''available

PHP Insertion Script

Employ the same “INSERT INTO…” statement within a PHP function to perform the operation. You will use the mysql_query() function once again.

Review the example given below −

<?php
   if(isset($_POST[''add''])) {
      $dbhost = ''localhost:3036
      $dbuser = ''root
      $dbpass = ''rootpassword
      $conn = mysql_connect($dbhost, $dbuser, $dbpass);

      if(! $conn ) {
         die(''Could not connect: '' . mysql_error());
      }

      if(! get_magic_quotes_gpc() ) {
         $product_name = addslashes ($_POST[''product_name'']);
         $product_manufacturer = addslashes ($_POST[''product_name'']);
      } else {
         $product_name = $_POST[''product_name''];
         $product_manufacturer = $_POST[''product_manufacturer''];
      }
      $ship_date = $_POST[''ship_date''];
      $sql = "INSERT INTO products_tbl ".
         "(product_name,product_manufacturer, ship_date) ".
         "VALUES"."(''$product_name'',''$product_manufacturer'',''$ship_date'')";

      mysql_select_db(''PRODUCTS'');
      $retval = mysql_query( $sql, $conn );

      if(! $retval ) {
         die(''Could not enter data: '' . mysql_error());
      }

      echo "Entered data successfullyn";
      mysql_close($conn);
   }
?>

On successful data insertion, you will see the following output −

mysql> Entered data successfully

You will also collaborate validation statements with insert statements such as checking to ensure correct data entry. MariaDB includes a number of options for this purpose, some of which are automatic.

In this chapter, we will learn to delete tables.

Table deletion is very easy, but remember all deleted tables are irrecoverable. The general syntax for table deletion is as follows −

DROP TABLE table_name ;

Two options exist for performing a table drop: use the command prompt or a PHP script.

The Command Prompt

At the command prompt, simply use the DROP TABLE SQL command −

root@host# mysql -u root -p
Enter password:*******
mysql> use PRODUCTS;
Database changed
mysql> DROP TABLE products_tbl

mysql> SELECT * from products_tbl
ERROR 1146 (42S02): Table ''products_tbl'' doesn''t exist

PHP Drop Table Script

PHP provides mysql_query() for dropping tables. Simply pass its second argument the appropriate SQL command −

<html>
   <head>
      <title>Create a MariaDB Table</title>
   </head>

   <body>
      <?php
         $dbhost = ''localhost:3036
         $dbuser = ''root
         $dbpass = ''rootpassword
         $conn = mysql_connect($dbhost, $dbuser, $dbpass);

         if(! $conn ) {
            die(''Could not connect: '' . mysql_error());
         }
         echo ''Connected successfully<br />

         $sql = "DROP TABLE products_tbl";
         mysql_select_db( ''PRODUCTS'' );
         $retval = mysql_query( $sql, $conn );

         if(! $retval ) {
            die(''Could not delete table: '' . mysql_error());
         }
         echo "Table deleted successfullyn";

         mysql_close($conn);
      ?>
   </body>
</html>

On successful table deletion, you will see the following output −

mysql> Table deleted successfully

The DELETE command deletes table rows from the specified table, and returns the quantity deleted. Access the quantity deleted with the ROW_COUNT() function. A WHERE clause specifies rows, and in its absence, all rows are deleted. A LIMIT clause controls the number of rows deleted.

In a DELETE statement for multiple rows, it deletes only those rows satisfying a condition; and LIMIT and WHERE clauses are not permitted. DELETE statements allow deleting rows from tables in different databases, but do not allow deleting from a table and then selecting from the same table within a subquery.

Review the following DELETE syntax −

DELETE FROM table_name [WHERE …]

Execute a DELETE command from either the command prompt or using a PHP script.

The Command Prompt

At the command prompt, simply use a standard command −

root@host# mysql –u root –p password;
Enter password:*******
mysql> use PRODUCTS;
Database changed
mysql> DELETE FROM products_tbl WHERE product_id=133;
mysql> SELECT * from products_tbl WHERE ID_number=''133
ERROR 1032 (HY000): Can''t find record in ''products_tbl''

PHP Delete Query Script

Use the mysql_query() function in DELETE command statements −

<?php
   $dbhost = ''localhost:3036
   $dbuser = ''root
   $dbpass = ''rootpassword
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);

   if(! $conn ) {
      die(''Could not connect: '' . mysql_error());
   }

   $sql = ''DELETE FROM products_tbl WHERE product_id = 261
   mysql_select_db(''PRODUCTS'');
   $retval = mysql_query( $sql, $conn );

   if(! $retval ) {
      die(''Could not delete data: '' . mysql_error());
   }

   echo "Deleted data successfullyn";
   mysql_close($conn);
?>

On successful data deletion, you will see the following output −

mysql> Deleted data successfully
mysql> SELECT * from products_tbl WHERE ID_number=''261
ERROR 1032 (HY000): Can''t find record in ''products_tbl''

The ORDER BY clause, as mentioned in previous discussions, sorts the results of a statement. It specifies the order of the data operated on, and includes the option to sort in ascending (ASC) or descending (DESC) order. On omission of order specification, the default order is ascending.

ORDER BY clauses appear in a wide variety of statements such as DELETE and UPDATE. They always appear at the end of a statement, not in a subquery or before a set function, because they operate on the final resulting table. You also cannot use an integer to identify a column.

Review the general syntax of the ORDER BY clause given below −

SELECT field, field2,... [or column] FROM table_name, table_name2,...
ORDER BY field, field2,... ASC[or DESC]

Use an ORDER BY clause either at the command prompt or within a PHP script.

The Command Prompt

At the command prompt, simply use a standard command −

root@ host# mysql -u root -p password;
Enter password:*******
mysql> use PRODUCTS;
Database changed

mysql> SELECT * from products_tbl ORDER BY product_manufacturer ASC
+-------------+----------------+----------------------+
| ID_number   | Nomenclature   | product_manufacturer |
+-------------+----------------+----------------------+
| 56789       | SuperBlast 400 | LMN Corp             |
+-------------+----------------+----------------------+
| 67891       | Zoomzoom 5000  | QFT Corp             |
+-------------+----------------+----------------------+
| 12347       | Orbitron 1000  | XYZ Corp             |
+-------------+----------------+----------------------+

PHP Script Using Order By Clause

Utilize the mysql_query() function, once again, in statements employing the ORDER BY clause −

<?php
   $dbhost = ''localhost:3036
   $dbuser = ''root
   $dbpass = ''rootpassword
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);

   if(! $conn ) {
      die(''Could not connect: '' . mysql_error());
   }

   $sql = ''SELECT product_id, product_name, product_manufacturer, ship_date
      FROM products_tbl ORDER BY product_manufacturer DESC

   mysql_select_db(''PRODUCTS'');
   $retval = mysql_query( $sql, $conn );

   if(! $retval ) {
      die(''Could not get data: '' . mysql_error());
   }

   while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
      echo "Product ID :{$row[''product_id'']} <br> ".
         "Name: {$row[''product_name'']} <br> ".
         "Manufacturer: {$row[''product_manufacturer'']} <br> ".
         "Ship Date : {$row[''ship_date'']} <br> ".
         "--------------------------------<br>";
   }

   echo "Fetched data successfullyn";
   mysql_close($conn);
?>

On successful data retrieval, you will see the following output −

Product ID: 12347
Nomenclature: Orbitron 1000
Manufacturer: XYZ Corp
Ship Date: 01/01/17
----------------------------------------------
Product ID: 67891
Nomenclature: Zoomzoom 5000
Manufacturer: QFT Corp
Ship Date: 01/01/17
----------------------------------------------
Product ID: 56789
Nomenclature: SuperBlast 400
Manufacturer: LMN Corp
Ship Date: 01/04/17
----------------------------------------------
mysql> Fetched data successfully

WHERE clauses filter various statements such as SELECT, UPDATE, DELETE, and INSERT. They present criteria used to specify action. They typically appear after a table name in a statement, and their condition follows. The WHERE clause essentially functions like an if statement.

Review the general syntax of WHERE clause given below −

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

Note the following qualities of the WHERE clause −

• It is optional.

• It allows any condition to be specified.

• It allows for the specification of multiple conditions through using an AND or OR operator.

• Case sensitivity only applies to statements using LIKE comparisons.

The WHERE clause permits the use of the following operators −

WHERE clauses can be utilized at the command prompt or within a PHP script.

The Command Prompt

At the command prompt, simply use a standard command −

root@host# mysql -u root -p password;
Enter password:*******
mysql> use PRODUCTS;
Database changed
mysql> SELECT * from products_tbl WHERE product_manufacturer = ''XYZ Corp
+-------------+----------------+----------------------+
| ID_number   | Nomenclature   | product_manufacturer |
+-------------+----------------+----------------------+
| 12345       | Orbitron 4000  | XYZ Corp             |
+-------------+----------------+----------------------+
| 12346       | Orbitron 3000  | XYZ Corp             |
+-------------+----------------+----------------------+
| 12347       | Orbitron 1000  | XYZ Corp             |
+-------------+----------------+----------------------+

Review an example using the AND condition −

SELECT *
FROM products_tbl
WHERE product_name = ''Bun Janshu 3000
AND product_id <= 344;

This example combines both AND and OR conditions

SELECT *
FROM products_tbl
WHERE (product_name = ''Bun Janshu 3000'' AND product_id < 344)
OR (product_name = ''Bun Janshu 3000'');

PHP Scripts Using Where Clause

Employ the mysql_query() function in operations using a WHERE clause −

<?php
   $dbhost = ''localhost:3036
   $dbuser = ''root
   $dbpass = ''rootpassword
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);

   if(! $conn ) {
      die(''Could not connect: '' . mysql_error());
   }

   $sql = ''SELECT product_id, product_name, product_manufacturer, ship_date
      FROM products_tbl
      WHERE product_manufacturer = "XYZ Corp"

   mysql_select_db(''PRODUCTS'');
   $retval = mysql_query( $sql, $conn );

   if(! $retval ) {
      die(''Could not get data: '' . mysql_error());
   }

   while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
      echo "Product ID :{$row[''product_id'']} <br> ".
         "Name: {$row[''product_name'']} <br> ".
         "Manufacturer: {$row[''product_manufacturer'']} <br> ".
         "Ship Date: {$row[''ship_date'']} <br> ".
         "--------------------------------<br>";
   }

   echo "Fetched data successfullyn";
   mysql_close($conn);
?>

On successful data retrieval, you will see the following output −

Product ID: 12345
Nomenclature: Orbitron 4000
Manufacturer: XYZ Corp
Ship Date: 01/01/17
----------------------------------------------
Product ID: 12346
Nomenclature: Orbitron 3000
Manufacturer: XYZ Corp
Ship Date: 01/02/17
----------------------------------------------
Product ID: 12347
Nomenclature: Orbitron 1000
Manufacturer: XYZ Corp
Ship Date: 01/02/17
----------------------------------------------
mysql> Fetched data successfully