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

The UPDATE command modifies existing fields by changing values. It uses the SET clause to specify columns for modification, and to specify the new values assigned. These values can be either an expression or the default value of the field. Setting a default value requires using the DEFAULT keyword. The command can also employ a WHERE clause to specify conditions for an update and/or an ORDER BY clause to update in a certain order.

Review the following general syntax −

UPDATE table_name SET field=new_value, field2=new_value2,...
[WHERE ...]

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

The Command Prompt

At the command prompt, simply use a standard commandroot −

root@host# mysql -u root -p password;
Enter password:*******
mysql> use PRODUCTS;
Database changed
mysql> UPDATE products_tbl
   SET nomenclature = ''Fiber Blaster 300Z'' WHERE ID_number = 112;
mysql> SELECT * from products_tbl WHERE ID_number=''112
+-------------+---------------------+----------------------+
| ID_number   | Nomenclature        | product_manufacturer |
+-------------+---------------------+----------------------+
| 112         | Fiber Blaster 300Z  | XYZ Corp             |
+-------------+---------------------+----------------------+

PHP Update Query Script

Employ the mysql_query() function in UPDATE 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 = ‘UPDATE products_tbl
      SET product_name = ”Fiber Blaster 300z”
      WHERE product_id = 112’;

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

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

   echo “Updated data successfullyn”;
   mysql_close($conn);
?>

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

mysql> Updated data successfully

In this chapter, we will learn how to select data from a table.

SELECT statements retrieve selected rows. They can include UNION statements, an ordering clause, a LIMIT clause, a WHERE clause, a GROUP BY…HAVING clause, and subqueries.

Review the following general syntax −

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

A SELECT statement provides multiple options for specifying the table used −

• database_name.table_name

• table_name.column_name

• database_name.table_name.column_name

All select statements must contain one or more select expressions. Select expressions consist of one of the following options −

• A column name.

• An expression employing operators and functions.

• The specification “table_name.*” to select all columns within the given table.

• The character “*” to select all columns from all tables specified in the FROM clause.

The command prompt or a PHP script can be employed in executing a select statement.

The Command Prompt

At the command prompt, execute statements as follows −

root@host# mysql -u root -p password;
Enter password:*******
mysql> use PRODUCTS;
Database changed
mysql> SELECT * from products_tbl
+-------------+---------------+
| ID_number   | Nomenclature  |
+-------------+---------------+
| 12345       | Orbitron 4000 |
+-------------+---------------+

PHP Select Script

Employ the same SELECT statement(s) within a PHP function to perform the operation. You will use the mysql_query() function once again. Review an example given below −

<?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
   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
----------------------------------------------
mysql> Fetched data successfully

Best practices suggest releasing cursor memory after every SELECT statement. PHP provides the mysql_free_result() function for this purpose. Review its use as shown below −

<?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
   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_NUM)) {
      echo "Product ID :{$row[0]} <br> ".
         "Name: {$row[1]} <br> ".
         "Manufacturer: {$row[2]} <br> ".
         "Ship Date : {$row[3]} <br> ".
         "--------------------------------<br>";
   }

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

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.

MariaDB partners well with a wide variety of programming languages and frameworks such as PHP, C#, JavaScript, Ruby on Rails, Django, and more. PHP remains the most popular of all available languages due to its simplicity and historical footprint. This guide will focus on PHP partnered with MariaDB.

PHP provides a selection of functions for working with the MySQL database. These functions perform tasks like accessing it or performing operations, and they are fully compatible with MariaDB. Simply call these functions as you would call any other PHP function.

The PHP functions you will use for MariaDB conform to the following format −

mysql_function(value,value,...);

The second part of the function specifies its action. Two of the functions used in this guide are as follows −

mysqli_connect($connect);
mysqli_query($connect,"SQL statement");

The following example demonstrates the general syntax of a PHP call to a MariaDB function −

<html>
   <head>
      <title>PHP and MariaDB</title>
   </head>

   <body>
      <?php
         $retval = mysql_function(value, [value,...]);

         if( !$retval ) {
            die ( "Error: Error message here" );
         }
         // MariaDB or PHP Statements
      ?>
   </body>
</html>

In the next section, we will examine essential MariaDB tasks, using PHP functions.

Good field definitions are essential for the optimization of your database. The ideal approach requires that you exclusively use a field of the type and size needed. For example, if you will only use a field, five-characters wide, do not define a field, 20-characters wide. Field (or column) types are also known as data types given the data types stored within the field.

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

Numeric Data Types

The numeric data types supported by MariaDB are as follows −

• TINYINT − This data type represents small integers falling within the signed range of -128 to 127, and the unsigned range of 0 to 255.

• BOOLEAN − This data type associates a value 0 with “false,” and a value 1 with “true.”

• SMALLINT − This data type represents integers within the signed range of -32768 to 32768, and the unsigned range of 0 to 65535.

• MEDIUMINT − This data type represents integers in the signed range of -8388608 to 8388607, and the unsigned range of 0 to 16777215.

• INT(also INTEGER) − This data type represents an integer of normal size. When marked as unsigned, the range spans 0 to 4294967295. When signed (the default setting), the range spans -2147483648 to 2147483647. When a column is set to ZEROFILL( an unsigned state), all its values are prepended by zeros to place M digits in the INT value.

• BIGINT − This data type represents integers within the signed range of 9223372036854775808 to 9223372036854775807, and the unsigned range of 0 to 18446744073709551615.

• DECIMAL( also DEC, NUMERIC, FIXED)− This data type represents precise fixed-point numbers, with M specifying its digits and D specifying the digits after the decimal. The M value does not add “-” or the decimal point. If D is set to 0, no decimal or fraction part appears and the value will be rounded to the nearest DECIMAL on INSERT. The maximum permitted digits is 65, and the maximum for decimals is 30. Default value for M on omission is 10, and 0 for D on omission.

• FLOAT − This data type represents a small, floating-point number of the value 0 or a number within the following ranges −

  • -3.402823466E+38 to -1.175494351E-38

  • 1.175494351E-38 to 3.402823466E+38

• DOUBLE (also REAL and DOUBLE PRECISION) − This data type represents normal-size, floating-point numbers of the value 0 or within the following ranges −

  • -1.7976931348623157E+308 to -2.2250738585072014E-308

  • 2.2250738585072014E-308 to 1.7976931348623157E+308

• BIT − This data type represents bit fields with M specifying the number of bits per value. On omission of M, the default is 1. Bit values can be applied with “ b’[value]’” in which value represents bit value in 0s and 1s. Zero-padding occurs automatically from the left for full length; for example, “10” becomes “0010.”

Date and Time Data Types

The date and time data types supported by MariaDB are as follows −

• DATE − This data type represents a date range of “1000-01-01” to “9999-12-31,” and uses the “YYYY-MM-DD” date format.

• TIME − This data type represents a time range of “-838:59:59.999999” to “838:59:59.999999.”

• DATETIME − This data type represents the range “1000-01-01 00:00:00.000000” to “9999-12-31 23:59:59.999999.” It uses the “YYYY-MM-DD HH:MM:SS” format.

• TIMESTAMP − This data type represents a timestamp of the “YYYY-MM-DD HH:MM:DD” format. It mainly finds use in detailing the time of database modifications, e.g., insertion or update.

• YEAR − This data type represents a year in 4-digit format. The four-digit format allows values in the range of 1901 to 2155, and 0000.

String DataTypes

The string type values supported by MariaDB are as follows −

• String literals − This data type represents character sequences enclosed by quotes.

• CHAR − This data type represents a right-padded, fixed-length string containing spaces of specified length. M represents column length of characters in a range of 0 to 255, its default value is 1.

• VARCHAR − This data type represents a variable-length string, with an M range (maximum column length) of 0 to 65535.

• BINARY − This data type represents binary byte strings, with M as the column length in bytes.

• VARBINARY − This data type represents binary byte strings of variable length, with M as column length.

• TINYBLOB − This data type represents a blob column with a maximum length of 255 (28 – 1) bytes. In storage, each uses a one-byte length prefix indicating the byte quantity in the value.

• BLOB − This data type represents a blob column with a maximum length of 65,535 (216 – 1) bytes. In storage, each uses a two-byte length prefix indicating the byte quantity in the value.

• MEDIUMBLOB − This data type represents a blob column with a maximum length of 16,777,215 (2²⁴ – 1) bytes. In storage, each uses a three-byte length prefix indicating the byte quantity in the value.

• LONGBLOB − This data type represents a blob column with a maximum length of 4,294,967,295(2³² – 1) bytes. In storage, each uses a four-byte length prefix indicating the byte quantity in the value.

• TINYTEXT − This data type represents a text column with a maximum length of 255 (2⁸ – 1) characters. In storage, each uses a one-byte length prefix indicating the byte quantity in the value.

• TEXT − This data type represents a text column with a maximum length of 65,535 (2¹⁶ – 1) characters. In storage, each uses a two-byte length prefix indicating the byte quantity in the value.

• MEDIUMTEXT − This data type represents a text column with a maximum length of 16,777,215 (2²⁴ – 1) characters. In storage, each uses a three-byte length prefix indicating the byte quantity in the value.

• LONGTEXT − This data type represents a text column with a maximum length of 4,294,967,295 or 4GB (2³² – 1) characters. In storage, each uses a four-byte length prefix indicating the byte quantity in the value.

• ENUM − This data type represents a string object having only a single value from a list.

• SET − This data type represents a string object having zero or more values from a list, with a maximum of 64 members. SET values present internally as integer values.

Before attempting to run MariaDB, first determine its current state, running or shutdown. There are three options for starting and stopping MariaDB −

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

If you installed MariaDB in a non-standard location, you may have to edit location information in the script files. Stop MariaDB by simply adding a “stop” parameter with the script.

If you would like to start it automatically under Linux, add startup scripts to your init system. Each distribution has a different procedure. Refer to your system documentation.

Creating a User Account

Create a new user account with the following code −

CREATE USER ''newusername''@''localhost'' IDENTIFIED BY ''userpassword

This code adds a row to the user table with no privileges. You also have the option to use a hash value for the password. Grant the user privileges with the following code −

GRANT SELECT, INSERT, UPDATE, DELETE ON database1 TO ''newusername''@''localhost

Other privileges include just about every command or operation possible in MariaDB. After creating a user, execute a “FLUSH PRIVILEGES” command in order to refresh grant tables. This allows the user account to be used.

The Configuration File

After a build on Unix/Linux, the configuration file “/etc/mysql/my.cnf” should be edited to appear as follow −

# Example mysql config file.
# You can copy this to one of:
# /etc/my.cnf to set global options,
# /mysql-data-dir/my.cnf to get server specific options or
# ~/my.cnf for user specific options.

#

# One can use all long options that the program supports.
# Run the program with --help to get a list of available options

# This will be passed to all mysql clients
[client]
#password = my_password
#port = 3306
#socket = /tmp/mysql.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# The MySQL server
[mysqld]
#port = 3306
#socket = /tmp/mysql.sock
temp-pool

# The following three entries caused mysqld 10.0.1-MariaDB (and possibly other
   versions) to abort...
# skip-locking
# set-variable = key_buffer = 16M
# set-variable = thread_cache = 4

loose-innodb_data_file_path = ibdata1:1000M
loose-mutex-deadlock-detector
gdb

######### Fix the two following paths

# Where you want to have your database
data = /path/to/data/dir

# Where you have your mysql/MariaDB source + sql/share/english
language = /path/to/src/dir/sql/share/english

[mysqldump]
quick
MariaDB
8
set-variable = max_allowed_packet=16M
[mysql]
no-auto-rehash

[myisamchk]
set-variable = key_buffer = 128M

Edit the lines “data= ” and “language= ” to match your environment.

After file modification, navigate to the source directory and execute the following −

./scripts/mysql_install_db --srcdir = $PWD --datadir = /path/to/data/dir --
   user = $LOGNAME

Omit the “$PWD” variable if you added datadir to the configuration file. Ensure “$LOGNAME” is used when running version 10.0.1 of MariaDB.

Administration Commands

Review the following list of important commands you will regularly use when working with MariaDB −

• USE [database name] − Sets the current default database.

• SHOW DATABASES − Lists the databases currently on the server.

• SHOW TABLES − Lists all non-temporary tables.

• SHOW COLUMNS FROM [table name] − Provides column information pertaining to the specified table.

• SHOW INDEX FROM TABLENAME [table name] − Provides table index information relating to the specified table.

• SHOW TABLE STATUS LIKE [table name]G – − Provides tables with information about non-temporary tables, and the pattern that appears after the LIKE clause is used to fetch table names.

Creation or deletion of databases in MariaDB requires privileges, typically, only given to root users or admins. Under these accounts, you have two options for deleting a database: the mysqladmin binary and a PHP script.

Note that deleted databases are irrecoverable, so exercise care in performing this operation. Furthermore, PHP scripts used for deletion do not prompt you with a confirmation before the deletion.

mysqladmin binary

The following example demonstrates how to use the mysqladmin binary to delete an existing database −

[root@host]# mysqladmin -u root -p drop PRODUCTS
Enter password:******
mysql> DROP PRODUCTS
ERROR 1008 (HY000): Can''t drop database ''PRODUCTS database doesn''t exist

PHP Drop Database Script

PHP employs the mysql_query function in deleting MariaDB databases. The function uses two parameters, one optional, and returns either a value of “true” when successful, or “false” when not.

Syntax

Review the following drop database script syntax −

bool mysql_query( sql, connection );

The description of the parameters is given below −

Try the following example code for deleting a database −

<html>
   <head>
      <title>Delete a MariaDB Database</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 DATABASE PRODUCTS
         $retval = mysql_query( $sql, $conn );

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

         echo "Database PRODUCTS deleted successfullyn";
         mysql_close($conn);
      ?>
   </body>
</html>

On successful deletion, you will see the following output −

mysql> Database PRODUCTS deleted successfully