The UPDATE statement is used to change, update or modify the existing records of a MariaDB table. It can be used with WHERE, ORDER BY and LIMIT clauses.

Syntax:

For example

We have a table "Test", having the following data:

Let's change the 'title' "Welcome to MariaDB" where 'title' was "Hello".

The MariaDB DELETE statement is used to delete one or more records from the table in the database. It can be used to delete records from the table as well the whole table if you use it without WHERE condition.

Syntax:

Let's delete data using one condition.

Example

The query is executed successfully. You can now see that selected data is deleted.

You can see that "Mahesh" is not available in the table.

Similarly, you can delete data using multiple conditions.

TRUNCATE TABLE statement is used to delete a table permanently. It deletes all the records from the table.

Syntax:

Difference between DELETE and TRUNCATE statement:

• DELETE statement is used to remove one or more columns from a table as well as the whole table. On the other hand, the TRUNCATE TABLE statement is used to delete the whole table permanently.
• TRUNCATE TABLE statement is same as DELETE statement without a WHERE clause.
• DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row.
• TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and record only the page deallocations in the transaction log. Hence it is faster than delete statement.

Example

Let's truncate the table "Students".

Output:

  Query OK, 0 rows affected (0.031sec). 

The TRUNCATE query is executed successfully. You can see that the records of "Student" table have been deleted permanently.

Output:

 No record found. 

In relational database management system, aggregate functions are the functions where the values of multiple rows are grouped together as input on certain criteria and provide a single value of more significant meaning such as a list, set, etc.

Following is a list of aggregate function in MariaDB:

MariaDB COUNT Function: In MariaDB database, COUNT function is used to return the count of an expression.

Syntax:

The COUNT () Function counts only NOTNULL values.

MariaDB SUM Function: MariaDB SUM function is used to return the summed value of an expression.

Syntax:

MariaDB MIN Function: MariaDB MIN () function is used to retrieve the minimum value of the expression.

Syntax:

MariaDB MAX Function: MariaDB MAX () function is used to retrieve the maximum value of the expression.

Syntax:

MariaDB AVG Function: MariaDB AVG() function is used to retrieve the average value of an expression.

Syntax:

Or

MariaDB BIT_AND Function: Returns the bitwise AND of all bits in exp.

Syntax:

MariaDB BIT_OR: Returns the bitwise OR of all bits in exp.

Syntax:

MariaDB BIT_XOR: Returns the bitwise XOR of all bits in exp.

Syntax:

MariaDB supports all clauses used in RDBMS. For example:

MariaDB Where Clause: In MariaDB, WHERE clause is used with SELECT, INSERT, UPDATE and DELETE statement to select or change a specific location where we want to change.

It has appeared after the table name in a statement.

Syntax:

Note: WHERE clause is an optional clause. It can be used with AND, OR, AND & OR, LIKE operators.

MariaDB Like Clause: In MariaDB, LIKE clause is used with SELECT statement to retrieve data when an operation needs an exact match. It can be used with SELECT, INSERT, UPDATE and DELETE statement.

It is used for pattern matching and returns a true or false. The patterns used for comparison accept the following wildcard characters:

"%" wildcard character: It matches numbers of characters (0 or more).

"_" wildcard character: It matches a single character. It matches characters within its set.

Syntax:

MariaDB Order By Clause: In MariaDB database, ORDER BY Clause is used to sort the records in your result set in ascending or descending order.

Syntax:

Note: You can sort the result without using ASC/DESC attributes. By default, the result will be stored in ascending order.

MariaDB DISTINCT Clause: MariaDB DISTINCT Clause is used to remove duplicates from the result when we use it with a SELECT statement.

Syntax:

Note: When you use the only expression in a DISTINCT clause, the query will return the unique values for that expression. When you use multiple expressions in the DISTINCT clause, the query will return unique combinations for the multiple expressions listed.

The DISTINCT clause doesn't ignore NULL values. So when using the DISTINCT clause in your SQL statement, your result set will include NULL as a distinct value.

MariaDB FROM Clause: MariaDB FROM Clause is used to fetch data from a table. It is also used to join the tables which you will study later.

Syntax:

Etc.

The WHERE clause is used to select or change a specific location to fetch the records from a table. It is used with SELECT, INSERT, UPDATE and DELETE statement.

Syntax:

WHERE Clause with Single Condition

Example

We have a table "Students" having some data. Let's retrieve all records from the "Student" table where student_id is less than 6.

Output:

MariaDB LIKE clause is used with SELECT, INSERT, UPDATE and DELETE statement to retrieve data when an operation needs an exact match.

It is used for pattern matching and returns a true or false. The patterns used for comparison accept the following wildcard characters:

"%" wildcard character: It matches numbers of characters (0 or more).

"_" wildcard character: It matches a single character. It matches characters within its set.

Syntax:

We have a table "Employees", having the following data.

Let's use % wildcard with LIKE condition to find all of the names which begins with "L".

MariaDB ORDER BY Clause is used to sort the records in your result set in ascending or descending order.

Note: You can sort the result without using ASC/DESC attribute. By default, the result will be stored in ascending order.

Syntax:

ORDER BY Clause without using ASC/DESC attributes:

"Employees" table, having the following data:

Output:

MariaDB DISTINCT Clause is used to remove duplicates from the result when it is used with a SELECT statement.

Syntax:

Note: When you use the only expression in the DISTINCT clause, the query will return the unique values for that expression. When you use multiple expressions in the DISTINCT clause, the query will return unique combinations for the multiple expressions listed.

The DISTINCT clause doesn't ignore NULL values. So when using the DISTINCT clause in your SQL statement, your result set will include NULL as a distinct value.

Single Expression:

We have a table name "Students", having some duplicate entries. A name "Ajeet" is repeated three times.

Let's use the DISTINCT clause to remove duplicates from the table.

Output:

You can see that "Ajeet" is repeated three times in the original "Students" table but after using DISTINCT clause, it is returned one time and duplicate entries are deleted.

The FROM clause is used with SELECT statement to retrieve data from the table. It is also used to join tables.

Syntax:

Example

Let's retrieve all employees from the table "Employees".

Output

As we know that FROM clause used along with the SELECT clause to join the data of two tables too.

Let's take an example of INNER JOIN: one of the most common types of join which returns all rows from multiple tables where the join condition is satisfied.

We have two tables "Student" and "Employee".

Use the following syntax to join both tables according to the given parameters:

Output: