MySQL Clauses

MySQL Clauses

MySQL system clauses are keywords or statements to handle information. It helps to operate a group of the data and apply it to require conditions. The clauses apply conditions or select patterns to get information. MySQL clauses are not used to insert new data. You retrieve data using several clauses. The table uses either single or multiple clauses.

Use of the MySQL clauses

The clauses are used for the following conditions and operations. Mainly, the MySQL clause works for select data. Sometimes, it helps to delete table information.

Searching data: it helps to search specific data from a MySQL table.

Sorting data: It sorts the data from the table as per requirement.

Grouping data: It creates a group of the data using clauses of the MySQL system.

Filtering data: It helps to avoid unwanted data and display the required table information.

Types of MySQL clauses

The below clauses use to operate the information of the database. These clauses work with each other.

Clauses	Description
MySQL "WHERE" clause	This clause uses a condition to retrieve and delete data.
MySQL "FROM" clause	This clause works with a pattern to display the table and its value.
MySQL "ORDER BY" clause	It helps to show data in ascending or descending order.
MySQL "GROUP BY" clause	This clause displays information in a particular group.
MySQL "HAVING" clause	This clause applies after the "group by" clause.
MySQL "DISTINCT" clause	It removes the duplicate information of the table.
MySQL "LIMIT" clause	This clause helps to assign a limit to the table data.

Syntax

The MySQL clauses syntax is below. This syntax shows several MySQL clauses with conditions.

 SELECT column1, column2, ..., columnN
 FROM table-name
 WHERE condition
 GROUP BY column1, column2, ..., columnN
 HAVING column regex value (condition);
 ORDER BY expression [ASC] | [DES]; 

The above syntax uses some clauses such as "FROM," "WHERE," "GROUP BY," and "ORDER BY." You select entire table columns or specific columns from the table. The particular field selects the other clauses.

Prerequisite

Use the database in the MySQL system. The table requires operating any MySQL query or condition. MySQL table stores information. The table requires rows, columns, and values. You should be aware of a table and its fields to use clauses.

MySQL "FROM" clause

MySQL system stores multiple databases and tables. This table helps to display information in the application. Suppose an application requires a table from the system. The "FROM" operator gives a specific table. The "FROM" clause helps to get a specific table in the database. This clause selects the required table from the database.

Syntax

The basic MySQL FROM clause syntax shows below.

Select column1, column2 FROM table name;

MySQL FROM clause with database syntax shows below.

Select column1, column2 FROM database.table name;

Examples of the "FROM" clause.

1) Example: the basic "FROM" clause example shows below.

Execute the below query to get a particular column from the table. The query selects the required table and table field.

mysql> select chapter, marks, grade from mysql_tutorials;

OUTPUT

The chapter, marks, and grade field and their value show in the output image. In the "FROM" clause, select the "mysql_tutorials" table.

2) Example: the basic "FROM" clause with database example shows below.

Execute the below query to get a particular column from the table. Here, the query required a select database and available table.

mysql> select chapter, marks, grade from tutorial.mysql_tutorials;

OUTPUT

The chapter, marks, and grade field and their value show in the output image. In the "FROM" clause, select the "mysql_tutorials" table. The query selects the "tutorial" database and "mysql_tutorial" table.

3) Example: the basic "FROM" clause example shows below.

Execute the below query to get a particular column from the table.

mysql> select * from mysql_tutorials;

OUTPUT  

The above image shows complete table information and structure. You get a table field and available data using the star (*) symbol.

MySQL "WHERE" clause

MySQL WHERE clause applies a condition on the table field. The table displays conditional information of the columns and rows. It supports the insert, updates, retrieve, and delete operations. This clause helps to display a particular position of the table. MySQL WHERE clause helps to apply other clauses on the table field.

Syntax

This clause comes with a condition of the table information. It requires a MySQL basic expression to display data. MySQL WHERE clause syntax shows below.

 MySQL expression WHERE column regular_expression value (condition);
 MySQL WHERE clause basic syntax shows below.
 SELECT column_name1, column_name
 FROM table-name
 WHERE condition; 

This condition uses other clauses and operators to retrieve data.

Examples of the MySQL WHERE clause

1) Example: MySQL WHERE clause used to retrieve data.

Execute the following query to retrieve data using the "WHERE" condition. Here, the condition applies to a "grade" column. The column is equal to an "A" grade.

mysql> select * from mysql_tutorials WHERE grade = "A";

OUTPUT

The output images display table information using the "grade = A" condition. The last column displays only an "A" grade.

2) Example: MySQL WHERE clause used to update data.

Execute the below query to get the updated chapter name. Here chapter column updates using the "WHERE" condition. The condition uses a marks chapter where value exists 40.

 mysql> update mysql_tutorials set chapter = 'mysql introduction' where marks = 40;
 Query OK, 1 row affected (0.36 sec)
 Rows matched: 1 Changed: 1 Warning: 0 

OUTPUT

Execute the below query to get an updated table.

mysql> select * from mysql_tutorials;

The above image shows modified table data using the "WHERE" condition. The chapter of the first row replaces "mysql intro" with "mysql introduction."

3) Example: MySQL WHERE clause with the logical condition.

The following query helps to get condition information from the table. Here, you use the "AND" logical condition with the "WHERE" clause.

mysql> select chapter, marks, grade from mysql_tutorials WHERE marks < 40 AND grade = 'A';

OUTPUT

You see the output image using the "AND" and "WHERE" conditions. The marks and grade column is used as a conditional query. You get the one row and all columns of the table.

4) Example: MySQL WHERE clause used to delete data.

The following query helps to delete the required row. The clause uses a conditional field to remove table data.

 mysql> delete from mysql_tutorials where remark = 'better';
 Query OK, 1 row affected (0.14 sec) 

OUTPUT

Execute the "select" query to get the output of the table data.

mysql> select * from mysql_tutorials;

The images show two rows of the table. The clause helps to apply conditions on the "remark" column. The remark column applies conditions on the "better" value. The last row deletes using the "WHERE" condition.

MySQL "GROUP BY" clause

The "GROUP BY" clause collects multiple columns together and displays data.This clause supports the use of aggregation functions such as MIN, MAX, SUM, so on.It creates a group of a column and rows as per the requirement.  This clause works with the "WHERE" condition. But, the "WHERE" condition shows optional. The aggregation function is optional to work with the "GROUP BY" clause.

Syntax

The basic syntax of the "GROUP BY" clause shows below.

 SELECT column1, column2, ..., column,
 FROM table name
 GROUP BY column1, column2, ..., columnN;
 MySQL syntax of the "GROUP BY" clause shows below.
 SELECT column1, column2, ..., column,
 Aggregation function
 FROM table-name
 WHERE condition
 GROUP BY column1, column2, ..., columnN; 

This clause comes after select and where clauses in the query. The list of columns separated using the comma (,) symbol.

Examples of the "Group BY" clause

1) Example: MySQL "Group BY" clause is used to retrieve data.

Execute the following query of the primary "Group BY" clause. The query uses a numerical column to create a group.

mysql> select chapter, marks from mysql_tutorials where marks > 30 group by marks;

OUTPUT

The above output image shows the chapter and marks column. The marks column selects to create a group using clauses.

2) Example: MySQL "Group BY" clause with count used to retrieve data.

Execute the following query of the basic "Group BY" clause. The query uses a numerical column to create a group. The count (*) is the aggregation function of the clause.

mysql> select chapter, marks, count(*) from mysql_tutorials group by marks;

OUTPUT

The above image shows the output of the "GROUP BY" clause query. The count (*) aggregation finds several operations and cycles of the data. Here, you get the three fields to display table information.

3) Example: MySQL "Group BY" clause with min aggregation used to retrieve data.

Execute the below query to get minimum values of the "marks" column. Here, MySQL query applies MIN aggregation with clauses.

mysql> select chapter, marks, MIN(marks) from mysql_tutorials where marks > 30  group by marks;

OUTPUT

The output image displays the required column with the given aggregation. In this example, you use MIN aggregation on the "marks" column.

4) Example: MySQL "Group BY" clause with max aggregation to retrieve data.

Execute the below query to get maximum values of the "marks" column. Here, MySQL query applies MAX aggregation with clauses.

mysql> select chapter, Max(marks) from mysql_tutorials where marks > 30 group by marks;

OUTPUT

The output image displays the required column with the given aggregation. In this example, you use Max aggregation on the "marks" column.

5) Example: MySQL "Group BY" clause with sum aggregation to retrieve data.

Execute the below query to get summation values of the "marks" column. Here, MySQL query applies SUM aggregation with clauses. You need not use the WHERE clause for every query.

mysql> select marks, SUM(marks) from mysql_tutorials group by marks;

OUTPUT

The output image displays the required column with the given aggregation. In this example, you use SUM aggregation on the "marks" column.

MySQL "HAVING" clause.

The "HAVING" clause comes with a "GROUP BY" clause. The "HAVING" clause is filtering data of specific rows. It provides a condition to the group of the row or aggregations. It works the same as a "where" clause after the "GROUP BY" clause. This clause works under the "SELECT" statement.  This clause supports retrieving table information with an advanced filter.

Syntax

The "HAVING" clause syntax shows below.  This syntax works without the "WHERE" condition.

 SELECT column1, column2
 FROM table name
 GROUP BY column1, column2
 HAVING condition; 

MySQL query with the "HAVING" clause shown below. This syntax works with the "WHERE" condition.

 SELECT column1, column2, column3,
 [Aggregation function]
 FROM table name
 WHERE condition
 GROUP BY column1, column2, column3
 HAVING column regex value [condition]; 

Examples of the "HAVING" clause

1) Example: The basic "HAVING" clause example shown below.

Execute the following query to filter table data. Here, you search data using the "Having" condition with the "GROUP BY" clause. This example shows the use of a basic "having" query.

 mysql> select * from mysql_tutorials
 GROUP BY marks
 HAVING marks = 40; 

OUTPUT

The above image shows table data with the "HAVING" condition. The condition works on the marks column. The marks field must be "40" value.

2) Example: The "HAVING" clause with condition example shows below.

Execute the below query to filter table data. Here you use the "Having" condition with the "GROUP BY" clause. This example shows the use of a basic "having" query.

 mysql> select chapter, marks, grade
 from mysql_tutorials
 GROUP BY marks
 HAVING marks < 40; 

OUTPUT

The above output image shows table data with the "HAVING" condition. The condition works on the marks column. The marks field shows less than the "40" value. The output shows chapter, marks, and grade field.

3) Example: The "HAVING" clause with the "WHERE" clause example shows below.

Execute the below query to display table data. The "HAVING" query works with the "WHERE" clause. The "WHERE" condition works with the "grade" field. The "group by" and "having" clauses work with the marks column. 

 mysql> select chapter, marks, grade
 from mysql_tutorials
 WHERE grade = 'A'
 GROUP BY marks
 HAVING marks < 40; 

OUTPUT

You see the above output image.The output shows chapter, marks, and grade field. The marks field shows less than the "40" value.

4) Example: The "HAVING" clause with aggregation example shows below.

Execute the MySQL query with the "HAVING" clause. The "WHERE" condition works with the "grade" field. The "group by" and "having" clauses work with the marks column. The count (*) aggregation works on the marks column of the table.

 mysql> select chapter, marks, grade,
 COUNT (*)
 FROM mysql_tutorials
 WHERE grade = 'A'
 GROUP BY marks
 HAVING marks > 35; 

OUTPUT

The above output image shows table data with the "HAVING" condition. The condition works on the marks column. The marks field shows greater than the "35" value. The output shows chapter, marks, and grade field.

MySQL "ORDER BY" clause

The "ORDER BY" clause helps to display the table field as per order. This clause shows the table column in the sequence.  The "ORDER BY" clause helps to retrieve data in ascending and descending format. The following keyword helps to create the order as per requirement.

• ASC: This keyword displays column data in ascending order.
• DESC: This keyword displays column data in descending order.

Syntax

The basic syntax of the "ORDER BY" clause shows below. This clause works without the "WHERE" condition.

 SELECT column1, column2
 FROM table name
 ORDER BY      
 Column1 [ASC | DESC],
 Column2 [ASC | DESC]; 

MySQL query of the "ORDER BY" clause shows below. This clause works with the "WHERE" condition.

 SELECT column1, column2,
 [aggregation function]
 FROM table name
 WHERE condition
 GROUP BY column1, column2
 HAVING column regex value [condition]
 ORDER BY      
 Column1 [ASC | DESC],
 Column2 [ASC | DESC]; 

Examples of the "ORDER BY" clause

1) Example: The basic "ORDER BY" clause example shows below.

Execute the below query to get table data in a sequence.  If the condition requires, then use the "HAVING" clause. The query does not need to set order using keywords.

 mysql> select chapter, marks, grade
 from mysql_tutorials
 GROUP BY marks
 HAVING marks >= 40
 ORDER BY marks; 

OUTPUT

The above output image shows table data as per the sequence.  The marks column automatically sets in ascending order. The chapter, marks, and grade column displays from the table.

2) Example: The basic "ORDER BY" clause with ascending and descending order example shows below.

Execute the below query to get table data in a sequence.  If the condition requires, then use other clauses. The query uses a keyword to set. You use either a single order keyword or both keywords.

 mysql> select chapter, marks, grade, remark
 from mysql_tutorials
 GROUP BY marks
 ORDER BY marks ASC, grade DESC; 

OUTPUT

The above output image shows table data as per the sequence.  The marks column sets in ascending order. The grade column sets in descending order. The remark, chapter, marks, and grade column displays from the table.

3) Example: The basic "ORDER BY" clause with the field example shown below.

 Execute the below query of the "ORDER BY" clause. This clause uses the table column and its value to arrange data as per requirement. Here, marks column used with data sequence. If the query requires other clauses, then use it with the condition.

 mysql> select chapter, marks, remark
 from mysql_tutorials
 ORDER BY field(marks, 40, 35, 41, 38); 

OUTPUT

The above output image shows table data as per the sequence.  The marks column sets in the required order. The chapter, marks, and remark column displays from the table. The mark sets value using the "field" keyword.

4) Example: The basic "ORDER BY" clause with other clauses example shows below.

Execute the MySQL query with the "ORDER BY" clause. The "WHERE" condition works with the "grade" field. The "group by" and "having" clauses work with the marks column. The ascending order is chosen by the clause.

 mysql> select chapter, marks, grade, count(*)
 from mysql_tutorials
  WHERE grade = 'A'
 GROUP BY marks
 HAVING marks > 35
 ORDER BY marks asc; 

OUTPUT

The above image displays the ascending order of the marks. The output shows count aggregation of the marks column. Here, you apply conditions on grade for "A" value. 

MySQL "DISTINCT" clause

The "DISTINCT" clause is to cut out the duplicate data of the database.This clause displays the unique data of the table.The "DISTINCT" clause is working with the "SELECT" statement.This clause can use the WHERE condition, but it is not necessary.The other clauses are used with the "DISTINCT" clause as per the user's requirement.

Syntax

The "DISTINCT" clause syntax shows below.  This syntax works without clauses and conditions.

 SELECT DISTINCT column1, column2
 FROM table name; 

MySQL query with the "DISTINCT" clause shows below. This syntax works with clauses and conditions.

 SELECT DISTINCT column1, column2, column3,
 FROM table-name
 WHERE condition
 GROUP BY column1, column2, column3
 HAVING condition; 

Examples of the "DISTINCT" clause

1) Example: The basic "DISTINCT" clause example shows below.

Execute the below query of the "DISTINCT" clause. Here, you use one column for a distinct clause.

 mysql> select DISTINCT  remark
 from mysql_tutorials; 

OUTPUT

The above output image shows a single field of the table. Here, the duplicate "good" value is removed from the column. You get a "remark" column with unique table data.

2) Example: The basic "DISTINCT" clause with the "WHERE" clause example shows below.

Execute the below query to display table data. The "DISTINCT" query works with the other clauses and its condition. The "WHERE" condition works with the "grade" field.

 mysql> select DISTINCT chapter, marks
 from mysql_tutorials
  WHERE grade = 'A'
 GROUP BY marks; 

OUTPUT

The above output image shows the chapter and marks column of the table. Here, duplicate values are removed from the columns. The "WHERE" clause applies conditions on the "grade" column. You get the "marks" column with unique table data.

3) Example: The basic "DISTINCT" clause with multiple columns example shows below.

Execute the below query to display table data. The "DISTINCT" clause applies to multiple columns of the table. The "WHERE" condition works with the "grade" field. The "group by" and "having" clauses work with the marks column. 

 mysql> select DISTINCT chapter, remark, marks
 from mysql_tutorials
  WHERE grade = 'A'
 GROUP BY marks
 HAVING marks > 35
 ORDER BY marks; 

OUTPUT

The above output image shows the chapter, remark, and marks column of the table. Here, duplicate values are removed from the columns. All columns show the unique data of the table.

4) Example: The basic "DISTINCT" clause with condition example shows below.

Execute the below query to display table data. The "DISTINCT" query works with the other clauses and its condition. The "WHERE" condition works with the "grade" field. The "group by" and "having" clauses work with the marks column. 

 mysql> select DISTINCT chapter, remark, marks, grade from mysql_tutorials
  WHERE grade = 'A'
 GROUP BY marks
 HAVING marks > 35
 ORDER BY marks asc; 

OUTPUT

The above output image shows the chapter and marks column of the table. Here, the table removes duplicate values from the columns. The "WHERE" clause applies conditions on the "grade" column. The grade must show an "A" value.  The distinct clause does not apply to the grade column.

MySQL "LIMIT" clause

The "LIMIT" clause is displaying several outputs. This clause is showing information from the starting row in the table. The "LIMIT" clause is working with the "SELECT" statement. This clause can use the WHERE condition, but it is not necessary.

The other clauses are used with the "LIMIT" clause as per the user's requirement. The "offset" is deciding the first row of the table to display as an output. The "row count" is deciding the number of row displays as an output.

Syntax

The "LIMIT" clause syntax shows below.  This syntax works without clauses and conditions.

 SELECT column1, column2
 FROM table name
 LIMIT [offset,] row count; 

MySQL query with "LIMIT" clause shows below. This syntax works with clauses and conditions.

 SELECT DISTINCT column1, column2, column3,
 FROM table name
 WHERE condition
 GROUP BY column1, column2, column3
 HAVING condition
  LIMIT [offset,] row count; 

Examples of the "DISTINCT" clause

1) Example: The introductory "LIMIT" clause with row count example shows below.

Execute the following query using the limit clause on the table. Here, the query uses the "limit" clause with row count. The limit applies to two row counts on the table.

mysql> select chapter, marks, remark from mysql_tutorials limit 2;

OUTPUT

The above image shows two rows of the table. The row count sets two rows. If offset does not apply, then the table shows from the first row. Hence, the table displays the first and second rows of the table.

2) Example: The "LIMIT" clause with offset example shows below.

Use offset (1) and row count (2)

Execute the following query using limit clause, offset and row count. Here, the query selects from the second row and one-row count. The offset sets the second row of the table. The row count sets a single row of the table.

mysql> select chapter, marks, remark from mysql_tutorials limit 2, 1;

OUTPUT

The above image shows two rows of the table. The row count sets a single row. If offset does not apply, then the table shows from the first row. Hence, the table displays the second row of the table.

Use offset (2) and row count (1)

Execute the following query using limit clause, offset and row count. Here, the query selects from the second row and one-row count. The offset sets the first row of the table. The row count sets two rows of the table.

mysql> select chapter, marks, remark from mysql_tutorials limit 1, 2;

OUTPUT

The above output image shows two rows of the table. The row count starts from the first row of the table.

You compare the offset and row count with the table data. The slight change of the limit clause changes the output.

3) Example: The "LIMIT" clause with the "WHERE" clause example shows below.

Execute the following query using limit clause with other clauses. Here, the query uses "from," "WHERE," and "limit" clauses. The condition applies to the respective clause. The "where" clause applies conditions to the chapter column.

 mysql> select chapter, marks, remark
 from mysql_tutorials
 where chapter = 'table' limit 2; 

OUTPUT

The above image shows the output of the clause. The where clause applies condition on the chapter. The chapter column is equal to the table. The limit provides two row count.

4) Example: The "LIMIT" clause with multiple clauses example shows below.

Execute the following query using limit clause with other clauses. Here, the query uses "from," "WHERE," "ORDER BY," and "limit" clauses. The condition applies to the respective clause.

 mysql> select chapter, marks, remark
 from mysql_tutorials
 where marks < 40
 order by marks desc
 limit 3; 

OUTPUT

The above output image displays data using the limit and where clause. The marks should be less than 40 values. The marks column displays data in descending order.

5) Example: The "LIMIT" clause with condition example shows below.

Execute the following query using limit clause and condition. Here, the "WHERE" clause uses the "AND" condition. The condition applies to the marks and grade columns. Here the limit clause uses 3-row counts. 

 mysql> select chapter, marks, remark, grade
 from mysql_tutorials
 where marks < 45 AND grade = 'A'
 order by marks desc
 limit 3; 

OUTPUT

The above image displays output using limit conditions. "WHERE" clause applies conditions on "marks" and "grade" columns. The marked columns arrange value in descending order. The offset is not used in the query.