Selecting data

Select all data from the departments table of the employee database. You can use an asterisk (*) to select all columns from a table. It is not recommended to use it, you should always select only the data you need:

SELECT * FROM departments;

+---------+--------------------+
| dept_no | dept_name          |
+---------+--------------------+
| d009    | Customer Service   |
| d005    | Development        |
| d002    | Finance            |
| d003    | Human Resources    |
| d001    | Marketing          |
| d004    | Production         |
| d006    | Quality Management |
| d008    | Research           |
| d007    | Sales              |
+---------+--------------------+
9 rows in set (0.00 sec)

Selecting columns

Suppose you need emp_no and dept_no from dept_manager:

SELECT emp_no, dept_no FROM dept_manager;

+--------+---------+
| emp_no | dept_no |
+--------+---------+
| 110022 | d001    |
| 110039 | d001    |
| 110085 | d002    |
| 110114 | d002    |
| 110183 | d003    |
| 110228 | d003    |
| 110303 | d004    |
| 110344 | d004    |
| 110386 | d004    |
| 110420 | d004    |
| 110511 | d005    |
| 110567 | d005    |
| 110725 | d006    |
| 110765 | d006    |
| 110800 | d006    |
| 110854 | d006    |
| 111035 | d007    |
| 111133 | d007    |
| 111400 | d008    |
| 111534 | d008    |
| 111692 | d009    |
| 111784 | d009    |
| 111877 | d009    |
| 111939 | d009    |
+--------+---------+
24 rows in set (0.00 sec)

Count

Find the count of employees from the employees table:

SELECT COUNT(*) FROM employees;

+----------+
| COUNT(*) |
+----------+
|   300024 |
+----------+
1 row in set (0.06 sec)

Filter based on condition

Find emp_no of employees with first_name as Georgi and last_name as Facello:

SELECT emp_no FROM employees WHERE first_name='Georgi' AND last_name='Facello';

+--------+
| emp_no |
+--------+
|  10001 |
|  55649 |
+--------+
2 rows in set (0.12 sec)

All the filtering conditions are given through the WHERE clause. Except integers and floating points, everything else should be put inside quotes.

Operators

MySQL supports many operators for filtering results.

• Equality: Refer to the preceding example where you have filtered using =.
• IN: Check whether a value is within a set of values. For example, find the count of all employees whose last name is either Christ, Lamba, or Baba:

SELECT COUNT(*) FROM employees WHERE last_name IN ('Christ', 'Lamba', 'Baba');

+----------+
| COUNT(*) |
+----------+
|      626 |
+----------+
1 row in set (0.12 sec)

BETWEEN...AND: Check whether a value is within a range of values. For example, find the number of employees who were hired in December 1986:

SELECT COUNT(*) FROM employees WHERE hire_date BETWEEN '1986-12-01' AND '1986-12-31';

+----------+
| COUNT(*) |
+----------+
|     3081 |
+----------+
1 row in set (0.10 sec)

NOT: You can simply negate the results by preceding with the NOT operator. For example, find the number of employees who were NOT hired in December 1986:

SELECT COUNT(*) FROM employees WHERE hire_date NOT BETWEEN '1986-12-01' AND '1986-12-31';

+----------+
| COUNT(*) |
+----------+
|   296943 |
+----------+
1 row in set (0.13 sec)

Simple pattern matching

You can use the LIKE operator. Use underscore (_) for matching exactly one character. Use % for matching any number of characters.

Find the count of all employees whose first name starts with Christ:

SELECT COUNT(*) FROM employees WHERE first_name LIKE 'christ%';

+----------+
| COUNT(*) |
+----------+
|     1157 |
+----------+
1 row in set (0.09 sec)

Find the count of all employees whose first name starts with Christ and ends with ed:

SELECT COUNT(*) FROM employees WHERE first_name LIKE 'christ%ed';

+----------+
| COUNT(*) |
+----------+
|      228 |
+----------+
1 row in set (0.12 sec)

Find the count of all employees whose first name contains sri:

SELECT COUNT(*) FROM employees WHERE first_name LIKE '%sri%';

+----------+
| COUNT(*) |
+----------+
|      253 |
+----------+
1 row in set (0.12 sec)

Find the count of all employees whose first name ends with er:

SELECT COUNT(*) FROM employees WHERE first_name LIKE '%er';

+----------+
| COUNT(*) |
+----------+
|     5388 |
+----------+
1 row in set (0.12 sec)

Find the count of all employees whose first name starts with any two characters followed by ka and then followed by any number of characters:

SELECT COUNT(*) FROM employees WHERE first_name LIKE '__ka%';

+----------+
| COUNT(*) |
+----------+
|     1918 |
+----------+
1 row in set (0.09 sec)

Regular expressions

You can use regular expressions in the WHERE clause by using the RLIKE or REGEXP operators. There are many ways to use REGEXP, refer to https://dev.mysql.com/doc/refman/8.0/en/regexp.html for more examples:

Find the count of all employees whose first name starts with Christ:

SELECT COUNT(*) FROM employees WHERE first_name RLIKE '^christ';

+----------+
| COUNT(*) |
+----------+
|     1157 |
+----------+
1 row in set (0.23 sec)

Find the count of all employees whose last name ends with ba:

SELECT COUNT(*) FROM employees WHERE last_name REGEXP 'ba$';

+----------+
| COUNT(*) |
+----------+
|     1008 |
+----------+
1 row in set (0.21 sec)

Find the count of all employees whose last name does not contain vowels (a, e, i, o, or u):

SELECT COUNT(*) FROM employees WHERE last_name NOT REGEXP '[aeiou]';

+----------+
| COUNT(*) |
+----------+
|      148 |
+----------+
1 row in set (0.22 sec)

Limiting results

Select the names of any 10 employees whose hire_date is before 1986. You can get this by using the LIMIT clause at the end of the statement:

SELECT first_name, last_name FROM employees WHERE hire_date < '1986-01-01' LIMIT 10;

+------------+------------+
| first_name | last_name  |
+------------+------------+
| Bezalel    | Simmel     |
| Sumant     | Peac       |
| Eberhardt  | Terkki     |
| Otmar      | Herbst     |
| Florian    | Syrotiuk   |
| Tse        | Herber     |
| Udi        | Jansch     |
| Reuven     | Garigliano |
| Erez       | Ritzmann   |
| Premal     | Baek       |
+------------+------------+
10 rows in set (0.00 sec)

Using the table alias

By default, whatever column you have given in the SELECT clause will appear in the results. In the previous examples, you have found out the count, but it is displayed as COUNT(*). You can change it by using the AS alias:

SELECT COUNT(*) AS count FROM employees WHERE hire_date BETWEEN '1986-12-01' AND '1986-12-31';

+-------+
| count |
+-------+
|  3081 |
+-------+
1 row in set (0.11 sec)