Query Results
Sorting Results
We can sort the results of any query using ORDER BY and specifying the column to sort by:
mysql> SELECT * FROM logins ORDER BY password;
+----+---------------+------------+---------------------+
| id | username | password | date_of_joining |
+----+---------------+------------+---------------------+
| 2 | administrator | adm1n_p@ss | 2020-07-02 11:30:50 |
| 3 | john | john123! | 2020-07-02 11:47:16 |
| 1 | admin | p@ssw0rd | 2020-07-02 00:00:00 |
| 4 | tom | tom123! | 2020-07-02 11:47:16 |
+----+---------------+------------+---------------------+
4 rows in set (0.00 sec)
By default, the sort is done in ascending order, but we can also sort the results by ASC or DESC:
mysql> SELECT * FROM logins ORDER BY password DESC;
+----+---------------+------------+---------------------+
| id | username | password | date_of_joining |
+----+---------------+------------+---------------------+
| 4 | tom | tom123! | 2020-07-02 11:47:16 |
| 1 | admin | p@ssw0rd | 2020-07-02 00:00:00 |
| 3 | john | john123! | 2020-07-02 11:47:16 |
| 2 | administrator | adm1n_p@ss | 2020-07-02 11:30:50 |
+----+---------------+------------+---------------------+
4 rows in set (0.00 sec)
It is also possible to sort by multiple columns, to have a secondary sort for duplicate values in one column:
mysql> SELECT * FROM logins ORDER BY password DESC, id ASC;
+----+---------------+-----------------+---------------------+
| id | username | password | date_of_joining |
+----+---------------+-----------------+---------------------+
| 1 | admin | p@ssw0rd | 2020-07-02 00:00:00 |
| 2 | administrator | change_password | 2020-07-02 11:30:50 |
| 3 | john | change_password | 2020-07-02 11:47:16 |
| 4 | tom | change_password | 2020-07-02 11:50:20 |
+----+---------------+-----------------+---------------------+
4 rows in set (0.00 sec)
LIMIT results
In case our query returns a large number of records, we can LIMIT the results to what we want only, using LIMIT and the number of records we want:
mysql> SELECT * FROM logins LIMIT 2;
+----+---------------+------------+---------------------+
| id | username | password | date_of_joining |
+----+---------------+------------+---------------------+
| 1 | admin | p@ssw0rd | 2020-07-02 00:00:00 |
| 2 | administrator | adm1n_p@ss | 2020-07-02 11:30:50 |
+----+---------------+------------+---------------------+
2 rows in set (0.00 sec)
``````shell-session
mysql> SELECT * FROM logins ORDER BY password DESC;
+----+---------------+------------+---------------------+
| id | username | password | date_of_joining |
+----+---------------+------------+---------------------+
| 4 | tom | tom123! | 2020-07-02 11:47:16 |
| 1 | admin | p@ssw0rd | 2020-07-02 00:00:00 |
| 3 | john | john123! | 2020-07-02 11:47:16 |
| 2 | administrator | adm1n_p@ss | 2020-07-02 11:30:50 |
+----+---------------+------------+---------------------+
4 rows in set (0.00 sec)
It is also possible to sort by multiple columns, to have a secondary sort for duplicate values in one column:
mysql> SELECT * FROM logins ORDER BY password DESC, id ASC;
+----+---------------+-----------------+---------------------+
| id | username | password | date_of_joining |
+----+---------------+-----------------+---------------------+
| 1 | admin | p@ssw0rd | 2020-07-02 00:00:00 |
| 2 | administrator | change_password | 2020-07-02 11:30:50 |
| 3 | john | change_password | 2020-07-02 11:47:16 |
| 4 | tom | change_password | 2020-07-02 11:50:20 |
+----+---------------+-----------------+---------------------+
4 rows in set (0.00 sec)
LIMIT results
In case our query returns a large number of records, we can LIMIT the results to what we want only, using LIMIT and the number of records we want:
mysql> SELECT * FROM logins LIMIT 2;
+----+---------------+------------+---------------------+
| id | username | password | date_of_joining |
+----+---------------+------------+---------------------+
| 1 | admin | p@ssw0rd | 2020-07-02 00:00:00 |
| 2 | administrator | adm1n_p@ss | 2020-07-02 11:30:50 |
+----+---------------+------------+---------------------+
2 rows in set (0.00 sec)
If we wanted to LIMIT results with an offset, we could specify the offset before the LIMIT count:
mysql> SELECT * FROM logins LIMIT 1, 2;
+----+---------------+------------+---------------------+
| id | username | password | date_of_joining |
+----+---------------+------------+---------------------+
| 2 | administrator | adm1n_p@ss | 2020-07-02 11:30:50 |
| 3 | john | john123! | 2020-07-02 11:47:16 |
+----+---------------+------------+---------------------+
2 rows in set (0.00 sec)
- Note: the offset marks the order of the first record to be included, starting from 0. For the above, it starts and includes the 2nd record, and returns two values.
WHERE Clause
To filter or search for specific data, we can use conditions with the SELECT statement using the WHERE clause, to fine-tune the results:
SELECT * FROM table_name WHERE <condition>;
The query above will return all records which satisfy the given condition. Let us look at an example:
mysql> SELECT * FROM logins WHERE id > 1;
+----+---------------+------------+---------------------+
| id | username | password | date_of_joining |
+----+---------------+------------+---------------------+
| 2 | administrator | adm1n_p@ss | 2020-07-02 11:30:50 |
| 3 | john | john123! | 2020-07-02 11:47:16 |
| 4 | tom | tom123! | 2020-07-02 11:47:16 |
+----+---------------+------------+---------------------+
3 rows in set (0.00 sec)
The example above selects all records where the value of id is greater than 1. As we can see, the first row with its id as 1 was skipped from the output. We can do something similar for usernames:
mysql> SELECT * FROM logins where username = 'admin';
+----+----------+----------+---------------------+
| id | username | password | date_of_joining |
+----+----------+----------+---------------------+
| 1 | admin | p@ssw0rd | 2020-07-02 00:00:00 |
+----+----------+----------+---------------------+
1 row in set (0.00 sec)
The query above selects the record where the username is admin. We can use the UPDATE statement to update certain records that meet a specific condition.
- Note: String and date data types should be surrounded by single quote (') or double quotes ("), while numbers can be used directly.
LIKE Clause
Another useful SQL clause is LIKE, enabling selecting records by matching a certain pattern. The query below retrieves all records with usernames starting with admin:
mysql> SELECT * FROM logins WHERE username LIKE 'admin%';
+----+---------------+------------+---------------------+
| id | username | password | date_of_joining |
+----+---------------+------------+---------------------+
| 1 | admin | p@ssw0rd | 2020-07-02 00:00:00 |
| 4 | administrator | adm1n_p@ss | 2020-07-02 15:19:02 |
+----+---------------+------------+---------------------+
2 rows in set (0.00 sec)
The % symbol acts as a wildcard and matches all characters after admin. It is used to match zero or more characters. Similarly, the _ symbol is used to match exactly one character. The below query matches all usernames with exactly three characters in them, which in this case was tom:
mysql> SELECT * FROM logins WHERE username like '___';
+----+----------+----------+---------------------+
| id | username | password | date_of_joining |
+----+----------+----------+---------------------+
| 3 | tom | tom123! | 2020-07-02 15:18:56 |
+----+----------+----------+---------------------+
1 row in set (0.01 sec)
Exercise
TARGET: 94.237.63.176:47185
Authenticate to target with username "root" and password "password"
Challenge 1
What is the last name of the employee whose first name starts with "Bar" AND who was hired on 1990-01-01?
First login to the mysql database server:
──(macc㉿kaliLab)-[~]
└─$ mysql -u root -h 94.237.55.124 -P 36075 --skip-ssl -p
--skip-ssltells the client to use a plain text connection.
Output:
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 10.7.3-MariaDB-1:10.7.3+maria~focal mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
List the databases on the server using show databases:
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| employees |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.110 sec)
Lets go ahead and use this database:
MariaDB [(none)]> USE employees;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
List the tables found on the employees database:
MariaDB [employees]> SHOW tables;
+----------------------+
| Tables_in_employees |
+----------------------+
| current_dept_emp |
| departments |
| dept_emp |
| dept_emp_latest_date |
| dept_manager |
| employees |
| salaries |
| titles |
+----------------------+
8 rows in set (0.109 sec)
Take a look at the columns of the employees table to see which fields we can use (use any sql command that will show you a part of the table or the columns):
MariaDB [employees]> SELECT * FROM employees LIMIT 2;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |
| 10002 | 1952-12-03 | Vivian | Billawala | F | 1986-12-11 |
+--------+------------+------------+-----------+--------+------------+
2 rows in set (0.110 sec)
Use the LIKE clause to select records matching the pattern we want, in this case we want first_name to start with "Bar" AND for hire_date to be exactly 1990-01-01
MariaDB [employees]> SELECT * FROM employees WHERE first_name like 'Bar%' AND hire_date = '1990-01-01';
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10227 | 1953-10-09 | Barton | Mitchem | M | 1990-01-01 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.110 sec)
- The last name of the only employee that matches this pattern is Mitchem.
flag: Mitchem