SQL examples

Just useful code snippets, examples not functional code.

MariaDB [accesscontrol]> SELECT * FROM keycards;
+--------+------------+---------------------+---------------------+----------+
| cardID | cardNumber | Created             | Deleted             | issuedTo |
+--------+------------+---------------------+---------------------+----------+
|      1 | 1234       | 2024-05-07 12:28:20 | 0000-00-00 00:00:00 |        3 |
|      2 | 2345       | 2024-05-07 12:28:32 | 0000-00-00 00:00:00 |        3 |
|      3 | 3456       | 2024-05-07 12:28:45 | 0000-00-00 00:00:00 |        1 |
|      4 | 4567       | 2024-05-07 12:28:54 | 0000-00-00 00:00:00 |        2 |
+--------+------------+---------------------+---------------------+----------+
 
 
> SELECT * FROM users;
+--------+-----------+-----------+----------+-----+---------------------+---------------------+
| userID | username  | firstname | surname  | pin | created             | deleted             |
+--------+-----------+-----------+----------+-----+---------------------+---------------------+
|      1 | AS        | A*        | S*       |   0 | 2024-05-07 11:29:25 | 0000-00-00 00:00:00 |
|      2 | os        | O*        | S*       |   0 | 2024-05-07 11:30:30 | 0000-00-00 00:00:00 |
|      3 | hs        | H*        | S*       |   0 | 2024-05-07 11:31:02 | 0000-00-00 00:00:00 |
+--------+-----------+-----------+----------+-----+---------------------+---------------------+
 
> SELECT * FROM access;
+----------+--------+-------------+--------+
| accessID | doorID | accesslevel | cardID |
+----------+--------+-------------+--------+
|        1 |      1 | allowed     |      1 |
|        2 |      1 | denied      |      2 |
|        3 |      1 | denied      |      3 |
|        4 |      2 | denied      |      1 |
|        5 |      2 | allowed     |      2 |
|        6 |      2 | allowed     |      3 |
+----------+--------+-------------+--------+

> SELECT username FROM users INNER JOIN keycards ON keycards.issuedTo = users.userID WHERE users.username = 'hs';
+----------+
| username |
+----------+
| hs       |
| hs       |
+----------+
 
> SELECT username, keycards.cardNumber FROM users INNER JOIN keycards ON keycards.issuedTo = users.userID WHERE users.username = 'os';
+----------+------------+
| username | cardNumber |
+----------+------------+
| os       | 4567       |
+----------+------------+
1 ROW IN SET (0.000 sec)
 
> SELECT username, keycards.cardNumber FROM users INNER JOIN keycards ON keycards.issuedTo = users.userID WHERE users.username = 'hs';
+----------+------------+
| username | cardNumber |
+----------+------------+
| hs       | 1234       |
| hs       | 2345       |
+----------+------------+
2 ROWS IN SET (0.000 sec)
 
 
 
> SELECT * FROM access JOIN keycards ON access.userID = keycards.userID \G;
*************************** 1. ROW ***************************
   accessID: 1
     userID: 1
accesslevel: {
        "description": "access",
        "front_door": "allowed",
        "cellar_door": "denied"
}
     cardID: 3
 cardNumber: 3456
    Created: 2024-05-07 12:28:45
    Deleted: 0000-00-00 00:00:00
     userID: 1
*************************** 2. ROW ***************************
   accessID: 2
     userID: 2
accesslevel: {
        "description": "access",
        "front_door": "denied",
        "cellar_door": "denied"
}
     cardID: 4
 cardNumber: 4567
    Created: 2024-05-07 12:28:54
    Deleted: 0000-00-00 00:00:00
     userID: 2
2 ROWS IN SET (0.079 sec)

With WHERE to select just one card

SELECT `accesslevel` 
FROM `access` 
JOIN `keycards` ON `access`.`cardID` = `keycards`.`cardID` 
WHERE `keycards`.`cardID` = 2 ;


{
1 : "denied",
2 : "denied",
3 : "allowed"
}

> SELECT keycards.cardNumber, users.username
    -> FROM keycards
    -> INNER JOIN users ON keycards.userID = users.userID;
+------------+-----------+
| cardNumber | username  |
+------------+-----------+
| 1234       | hds       |
| 2345       | hds       |
| 3456       | AS        |
| 4567       | oas       |
+------------+-----------+