This chapter contains a list of the most frequently used functions, offering definitions, explanations, and examples.

MariaDB Aggregate Functions

Most frequently used aggregate functions are given below −

MariaDB Age Calculation

The TIMESTAMPDIFF function provides a way to calculate age −

SELECT CURDATE() AS today;
SELECT ID, DOB, TIMESTAMPDIFF(YEAR,DOB,''2015-07-01'') AS age FROM officer_info;

MariaDB String Concatenation

The CONCAT function returns the resulting string after a concatenation operation. You can utilize one or more arguments. Review its syntax given below −

SELECT CONCAT(item, item,...);

Review the following example −

SELECT CONCAT(''Ram'', ''bu'', ''tan'');
Output:Rambutan

MariaDB Date/Time Functions

Given below are important date functions −

Following are some important time functions −

MariaDB Numeric Functions

Given below are some important numeric functions in MariaDB −

MariaDB String Functions

Important string functions are given below −

The WHERE clause provides a way to retrieve data when an operation uses an exact match. In situations requiring multiple results with shared characteristics, the LIKE clause accommodates broad pattern matching.

A LIKE clause tests for a pattern match, returning a true or false. The patterns used for comparison accept the following wildcard characters: “%”, which matches numbers of characters (0 or more); and “_”, which matches a single character. The “_” wildcard character only matches characters within its set, meaning it will ignore latin characters when using another set. The matches are case-insensitive by default requiring additional settings for case sensitivity.

A NOT LIKE clause allows for testing the opposite condition, much like the not operator.

If the statement expression or pattern evaluate to NULL, the result is NULL.

Review the general LIKE clause syntax given below −

SELECT field, field2,... FROM table_name, table_name2,...
WHERE field LIKE condition

Employ a LIKE 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 TUTORIALS;
Database changed
mysql> SELECT * from products_tbl
   WHERE product_manufacturer LIKE ''XYZ%
+-------------+----------------+----------------------+
| ID_number   | Nomenclature   | product_manufacturer |
+-------------+----------------+----------------------+
| 12345       | Orbitron 4000  | XYZ Corp             |
+-------------+----------------+----------------------+
| 12346       | Orbitron 3000  | XYZ Corp             |
+-------------+----------------+----------------------+
| 12347       | Orbitron 1000  | XYZ Corp             |
+-------------+----------------+----------------------+

PHP Script Using Like Clause

Use the mysql_query() function in statements employing the LIKE 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 LIKE "xyz%"

   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

In previous discussions and examples, we examined retrieving from a single table, or retrieving multiple values from multiple sources. Most real-world data operations are much more complex, requiring aggregation, comparison, and retrieval from multiple tables.

JOINs allow merging of two or more tables into a single object. They are employed through SELECT, UPDATE, and DELETE statements.

Review the general syntax of a statement employing a JOIN as shown below −

SELECT column
FROM table_name1
INNER JOIN table_name2
ON table_name1.column = table_name2.column;

Note the old syntax for JOINS used implicit joins and no keywords. It is possible to use a WHERE clause to achieve a join, but keywords work best for readability, maintenance, and best practices.

JOINs come in many forms such as a left join, right join, or inner join. Various join types offer different types of aggregation based on shared values or characteristics.

Employ a JOIN either at the command prompt or with a PHP script.

The Command Prompt

At the command prompt, simply use a standard statement −

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

mysql> SELECT products.ID_number, products.Nomenclature, inventory.inventory_ct
   FROM products
   INNER JOIN inventory
   ON products.ID_numbeer = inventory.ID_number;
+-------------+----------------+-----------------+
| ID_number   | Nomenclature   | Inventory Count |
+-------------+----------------+-----------------+
| 12345       | Orbitron 4000  | 150             |
+-------------+----------------+-----------------+
| 12346       | Orbitron 3000  | 200             |
+-------------+----------------+-----------------+
| 12347       | Orbitron 1000  | 0               |
+-------------+----------------+-----------------+

PHP Script Using JOIN

Use the mysql_query() function to perform a join operation −

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

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

   $sql = ''SELECT a.product_id, a.product_manufacturer, b.product_count
      FROM products_tbl a, pcount_tbl b
      WHERE a.product_manufacturer = b.product_manufacturer

   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 "Manufacturer:{$row[''product_manufacturer'']} <br&gt ".
         "Count: {$row[''product_count'']} <br&gt ".
         "Product ID: {$row[''product_id'']} <br&gt ".
         "--------------------------------<br&gt";
   }

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

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

ID Number: 12345
Nomenclature: Orbitron 4000
Inventory Count: 150
--------------------------------------
ID Number: 12346
Nomenclature: Orbitron 3000
Inventory Count: 200
--------------------------------------
ID Number: 12347
Nomenclature: Orbitron 1000
Inventory Count: 0
--------------------------------------
mysql> Fetched data successfully

When working with NULL values, remember they are unknown values. They are not empty strings or zero, which are valid values. In table creation, column specifications allow for setting them to accept null values, or reject them. Simply utilize a NULL or NOT NULL clause. This has applications in cases of missing record information like an ID number.

User-defined variables have a value of NULL until explicit assignment. Stored routine parameters and local variables allow setting a value of NULL. When a local variable has no default value, it has a value of NULL.

NULL is case-insensitive, and has the following aliases −

• UNKNOWN (a boolean value)
• N

NULL Operators

Standard comparison operators cannot be used with NULL (e.g., =, >, >=, <=, <, or !=) because all comparisons with a NULL value return NULL, not true or false. Comparisons with NULL or possibly containing it must use the “<=>” (NULL-SAFE) operator.

Other available operators are −

• IS NULL − It tests for a NULL value.

• IS NOT NULL − It confirms the absence of a NULL value.

• ISNULL − It returns a value of 1 on discovery of a NULL value, and 0 in its absence.

• COALESCE − It returns the first non-NULL value of a list, or it returns a NULL value in the absence of one.

Sorting NULL Values

In sorting operations, NULL values have the lowest value, so DESC order results in NULL values at the bottom. MariaDB allows for setting a higher value for NULL values.

There are two ways to do this as shown below −

SELECT column1 FROM product_tbl ORDER BY ISNULL(column1), column1;

The other way −

SELECT column1 FROM product_tbl ORDER BY IF(column1 IS NULL, 0, 1), column1 DESC;

NULL Functions

Functions generally output NULL when any parameters are NULL. However, there are functions specifically designed for managing NULL values. They are −

• IFNULL() − If the first expression is not NULL it returns it. When it evaluates to NULL, it returns the second expression.

• NULLIF() − It returns NULL when the compared expressions are equal, if not, it returns the first expression.

Functions like SUM and AVG ignore NULL values.

Inserting NULL Values

On insertion of a NULL value in a column declared NOT NULL, an error occurs. In default SQL mode, a NOT NULL column will instead insert a default value based on data type.

When a field is a TIMESTAMP, AUTO_INCREMENT, or virtual column, MariaDB manages NULL values differently. Insertion in an AUTO_INCREMENT column causes the next number in the sequence to insert in its place. In a TIMESTAMP field, MariaDB assigns the current timestamp instead. In virtual columns, a topic discussed later in this tutorial, the default value is assigned.

UNIQUE indices can hold many NULL values, however, primary keys cannot be NULL.

NULL Values and the Alter Command

When you use the ALTER command to modify a column, in the absence of NULL specifications, MariaDB automatically assigns values.

Beyond the pattern matching available from LIKE clauses, MariaDB offers regular expression-based matching through the REGEXP operator. The operator performs pattern matching for a string expression based on a given pattern.

MariaDB 10.0.5 introduced PCRE Regular Expressions, which dramatically increases the scope of matching into areas like recursive patterns, look-ahead assertions, and more.

Review the use of standard REGEXP operator syntax given below −

SELECT column FROM table_name WHERE column REGEXP ''[PATTERN]

REGEXP returns 1 for a pattern match or 0 in the absence of one.

An option for the opposite exists in the form of NOT REGEXP. MariaDB also offers synonyms for REGEXP and NOT REGEXP, RLIKE and NOT RLIKE, which were created for compatibility reasons.

The pattern compared can be a literal string or something else such as a table column. In strings, it uses C escape syntax, so double any “” characters. REGEXP is also case-insensitive, with the exception of binary strings.

A table of possible patterns, which can be used are given below −

Review the pattern matching examples given below −

Products starting with “pr” −

SELECT name FROM product_tbl WHERE name REGEXP ''^pr

Products ending with “na” −

SELECT name FROM product_tbl WHERE name REGEXP ''na$

Products starting with a vowel −

SELECT name FROM product_tbl WHERE name REGEXP ''^[aeiou]

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.