SQL Statements
INSERT
Used to add new records to a given table:
mysql> INSERT INTO logins VALUES(1, 'admin', 'p@ssw0rd', '2020-07-02');
Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO logins(username, password) VALUES('administrator', 'adm1n_p@ss');
Query OK, 1 row affected (0.00 sec)We can also insert multiple records at once by separating them with a comma:
mysql> INSERT INTO logins(username, password) VALUES ('john', 'john123!'), ('tom', 'tom123!');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0SELECT
Basic syntax:
SELECT * FROM table_name;SELECT column1, column2 FROM table_name;DROP
mysql> DROP TABLE logins;
Query OK, 0 rows affected (0.01 sec)ALTER
Used to change the name of any table and any of its fields or to delete or add a new column to an existing table.
mysql> ALTER TABLE logins ADD newColumn INT;
Query OK, 0 rows affected (0.01 sec)To rename a column:
mysql> ALTER TABLE logins RENAME COLUMN newColumn TO oldColumn;
Query OK, 0 rows affected (0.01 sec)We can change a column’s datatype:
mysql> ALTER TABLE logins MODIFY oldColumn DATE;
Query OK, 0 rows affected (0.01 sec)We can drop a column :
mysql> ALTER TABLE logins DROP oldColumn;
Query OK, 0 rows affected (0.01 sec)UPDATE
Used to update specific records within a table, based on certain conditions.
Basic syntax:
UPDATE table_name SET column1=newvalue1, column2=newvalue2, ... WHERE <condition>;For example:
mysql> UPDATE logins SET password = 'change_password' WHERE id > 1;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> SELECT * FROM logins;
+----+---------------+-----------------+---------------------+
| 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:47:16 |
+----+---------------+-----------------+---------------------+
4 rows in set (0.00 sec)QUERY
Sort Results
ORDER 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)Sort by multiple columns:
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
If the results are too big, we can limit the results to what we want only:
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)WHERE
SELECT * FROM table_name WHERE <condition>;e.g
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)e.g
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)LIKE Clause
enabling selecting records by matching a certain pattern.
e.g % symbol acts as a wildcard and matches all characters after 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) _ symbol is used to match exactly one character.
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)Operators
AND
mysql> SELECT 1 = 1 AND 'test' = 'test';
+---------------------------+
| 1 = 1 AND 'test' = 'test' |
+---------------------------+
| 1 |
+---------------------------+
1 row in set (0.00 sec)
mysql> SELECT 1 = 1 AND 'test' = 'abc';
+--------------------------+
| 1 = 1 AND 'test' = 'abc' |
+--------------------------+
| 0 |
+--------------------------+
1 row in set (0.00 sec)OR
mysql> SELECT 1 = 1 OR 'test' = 'abc';
+-------------------------+
| 1 = 1 OR 'test' = 'abc' |
+-------------------------+
| 1 |
+-------------------------+
1 row in set (0.00 sec)
mysql> SELECT 1 = 2 OR 'test' = 'abc';
+-------------------------+
| 1 = 2 OR 'test' = 'abc' |
+-------------------------+
| 0 |
+-------------------------+
1 row in set (0.00 sec)NOT
mysql> SELECT NOT 1 = 1;
+-----------+
| NOT 1 = 1 |
+-----------+
| 0 |
+-----------+
1 row in set (0.00 sec)
mysql> SELECT NOT 1 = 2;
+-----------+
| NOT 1 = 2 |
+-----------+
| 1 |
+-----------+
1 row in set (0.00 sec)Symbol
The AND, OR and NOT operators can also be represented as &&, || and !, respectively.
Operators in queries
The following query lists all records where the username is NOT john:
mysql> SELECT * FROM logins WHERE username != 'john';
+----+---------------+------------+---------------------+
| 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 |
| 4 | tom | tom123! | 2020-07-02 11:47:16 |
+----+---------------+------------+---------------------+
3 rows in set (0.00 sec)Selects users who have their id greater than 1 AND username NOT equal to john:
mysql> SELECT * FROM logins WHERE username != 'john' AND id > 1;
+----+---------------+------------+---------------------+
| id | username | password | date_of_joining |
+----+---------------+------------+---------------------+
| 2 | administrator | adm1n_p@ss | 2020-07-02 11:30:50 |
| 4 | tom | tom123! | 2020-07-02 11:47:16 |
+----+---------------+------------+---------------------+
2 rows in set (0.00 sec)