MySQL COUNT() Function

The MySQL COUNT() function returns the number of records as a result of an expression.

Syntax

SELECT COUNT(expression)
FROM table;

Parameters

  • expression — usually a field
  • table — the table from which records will come from

Example

mysql> SELECT name FROM apps;
+---------------------------------+
| name                            |
+---------------------------------+
| Zpanel                          |
| Zoom Unified Meeting Experience |
| Zolper                          |
| Zoho Notebook                   |
| Zoho Mail                       |
+---------------------------------+
5 rows in set (0.00 sec)

Get the number of records

We will get the number of records on the table:

mysql> SELECT COUNT(name) FROM apps;
+-------------+
| COUNT(name) |
+-------------+
|         5   |
+-------------+
1 row in set (0.00 sec)

 MySQL LIKE Condition

In MySQL, you can search for non exact matches using the LIKE condition

Syntax

expression LIKE pattern
  • parameters — usually is the field or column name
  • expression — is a string of characters to match with records

Example

In the example we have a list of 5 names.

mysql> SELECT name FROM apps;
+---------------------------------+
| name                            |
+---------------------------------+
| Zpanel                          |
| Zoom Unified Meeting Experience |
| Zolper                          |
| Zoho Notebook                   |
| Zoho Mail                       |
+---------------------------------+
5 rows in set (0.00 sec)

But we need to get only names starting with the word, Zoho. We will retrieve the names using the LIKE condition with %.

mysql> SELECT name FROM apps WHERE name LIKE 'Zoho%';
+---------------+
| name          |
+---------------+
| Zoho Notebook |
| Zoho Mail     |
+---------------+
2 rows in set (0.00 sec)

 MySQL ORDER BY Clause

The ORDER BY clause allows the ordering by a specific rule or rules

Example

mysql> SELECT first_name, last_name FROM users;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Thorpe     | Obazee    |
| Kathy      | Manilow   |
| Brandy     | Smith     |
+------------+-----------+
3 rows in set (0.00 sec)

Apply ORDER BY for the first_name field

mysql> SELECT first_name, last_name FROM users ORDER BY first_name;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Brandy     | Smith     |
| Kathy      | Manilow   |
| Thorpe     | Obazee    |
+------------+-----------+
3 rows in set (0.03 sec)

We can reverse the order by using DESC

mysql> SELECT first_name, last_name FROM users ORDER BY first_name DESC ;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Thorpe     | Obazee    |
| Kathy      | Manilow   |
| Brandy     | Smith     |
+------------+-----------+
3 rows in set (0.03 sec)

 MySQL WHERE Statement

The MySQL WHERE statement allows users to retrieve records from one or more tables using conditions.

Example

Selecting all users:

mysql> SELECT first_name, last_name FROM users;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Thorpe     | Obazee    |
| Kathy      | Manilow   |
| Brandy     | Smith     |
+------------+-----------+
3 rows in set (0.00 sec)

Selecting users based on a condition set by the WHERE statement:

mysql> SELECT first_name, last_name FROM users WHERE first_name = 'Thorpe';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Thorpe     | Obazee    |
+------------+-----------+
1 row in set (0.94 sec)

 MySQL SELECT Statement

The MySQL SELECT statement allows users to retreive information records from one or more database tables.

Example

mysql> SELECT * FROM users;
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  1 | Thorpe     | Obazee    |
|  2 | Kathy      | Manilow   |
|  3 | Brandy     | Smith     |
+----+------------+-----------+
3 rows in set (0.00 sec)

More efficient SELECT statement

Selecting only the fields you need is a more efficient way to retrieve data.

mysql> SELECT first_name, last_name FROM users;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Thorpe     | Obazee    |
| Kathy      | Manilow   |
| Brandy     | Smith     |
+------------+-----------+
3 rows in set (0.00 sec)