Rainsbrook

User Tools

Site Tools

Trace:
rb-projects-access:sqlexamples

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
rb-projects-access:sqlexamples [25/04/2025 10:50] – andrewrb-projects-access:sqlexamples [25/04/2025 12:13] (current) – andrew
Line 1: Line 1:
 ====== SQL examples ====== ====== SQL examples ======
  
 +Just useful code snippets, examples not functional code.
 +
 +
 +===== Table columns =====
  
 <code sql> <code sql>
Line 12: Line 16:
 |      4 | 4567       | 2024-05-07 12:28:54 | 0000-00-00 00:00:00 |        2 | |      4 | 4567       | 2024-05-07 12:28:54 | 0000-00-00 00:00:00 |        2 |
 +--------+------------+---------------------+---------------------+----------+ +--------+------------+---------------------+---------------------+----------+
-4 rows in set (0.001 sec) 
  
-MariaDB [accesscontrol]> select * from users + 
-    -> ;+> select * from users;
 +--------+-----------+-----------+----------+-----+---------------------+---------------------+ +--------+-----------+-----------+----------+-----+---------------------+---------------------+
 | userID | username  | firstname | surname  | pin | created             | deleted             | | userID | username  | firstname | surname  | pin | created             | deleted             |
Line 23: Line 26:
 |      3 | hs        | H*        | S*       |   0 | 2024-05-07 11:31:02 | 0000-00-00 00:00:00 | |      3 | hs        | H*        | S*       |   0 | 2024-05-07 11:31:02 | 0000-00-00 00:00:00 |
 +--------+-----------+-----------+----------+-----+---------------------+---------------------+ +--------+-----------+-----------+----------+-----+---------------------+---------------------+
-3 rows in set (0.000 sec) 
  
- +> SELECT * FROM access;
-localhost/accesscontrol/access/ http://192.168.1.197/phpmyadmin/sql.php?server=1&db=accesscontrol&table=access&pos=0 +
- Showing rows 0 -  3 (4 total, Query took 0.0005 seconds.) +
- +
-MariaDB [accesscontrol]> SELECT * FROM access;+
 +----------+--------+-------------+--------+ +----------+--------+-------------+--------+
 | accessID | doorID | accesslevel | cardID | | accessID | doorID | accesslevel | cardID |
Line 40: Line 38:
 |        6 |      2 | allowed     |      3 | |        6 |      2 | allowed     |      3 |
 +----------+--------+-------------+--------+ +----------+--------+-------------+--------+
-6 rows in set (0.001 sec) 
- 
  
 </code> </code>
  
-<code sql> 
  
 +===== Useful selects =====
  
 +<code sql>
  
- +> select username from users inner join keycards on keycards.issuedTo = users.userID where users.username = 'hs';
-MariaDB [accesscontrol]> select cardNumber from keycards inner join users on keycards.issuedTo = users.userID; +
-+------------+ +
-| cardNumber | +
-+------------+ +
-| 1234       | +
-| 2345       | +
-| 3456       | +
-| 4567       | +
-+------------+ +
-4 rows in set (0.000 sec) +
- +
-MariaDB [accesscontrol]> +
-MariaDB [accesscontrol]> +
-MariaDB [accesscontrol]> select username from users inner join keycards on keycards.issuedTo = users.userID; +
-+-----------+ +
-| username  | +
-+-----------+ +
-| hs        | +
-| hs        | +
-| as        | +
-| os        | +
-+-----------+ +
-4 rows in set (0.000 sec) +
- +
- +
-MariaDB [accesscontrol]> select username from users inner join keycards on keycards.issuedTo = users.userID where users.username = 'hs';+
 +----------+ +----------+
 | username | | username |
Line 82: Line 53:
 | hs       | | hs       |
 +----------+ +----------+
-2 rows in set (0.000 sec) 
  
-MariaDB [accesscontrol]> select username, keycards.cardNumber from users inner join keycards on keycards.issuedTo = users.userID where users.username = 'os';+> select username, keycards.cardNumber from users inner join keycards on keycards.issuedTo = users.userID where users.username = 'os';
 +----------+------------+ +----------+------------+
 | username | cardNumber | | username | cardNumber |
Line 92: Line 62:
 1 row in set (0.000 sec) 1 row in set (0.000 sec)
  
-MariaDB [accesscontrol]> select username, keycards.cardNumber from users inner join keycards on keycards.issuedTo = users.userID where users.username = 'hs';+> select username, keycards.cardNumber from users inner join keycards on keycards.issuedTo = users.userID where users.username = 'hs';
 +----------+------------+ +----------+------------+
 | username | cardNumber | | username | cardNumber |
Line 103: Line 73:
  
  
-MariaDB [accesscontrol]> SELECT * FROM access JOIN keycards ON access.userID = keycards.userID \G;+> SELECT * FROM access JOIN keycards ON access.userID = keycards.userID \G;
 *************************** 1. row *************************** *************************** 1. row ***************************
    accessID: 1    accessID: 1
Line 152: Line 122:
  
  
-<code>+<code sql>
 > select keycards.cardNumber, users.username > select keycards.cardNumber, users.username
     -> from keycards     -> from keycards
Line 161: Line 131:
 | 1234       | hds       | | 1234       | hds       |
 | 2345       | hds       | | 2345       | hds       |
-| 3456       | astringer |+| 3456       | as        |
 | 4567       | oas       | | 4567       | oas       |
 +------------+-----------+ +------------+-----------+
rb-projects-access/sqlexamples.1745578219.txt.gz · Last modified: by andrew
Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki