SQL - Basic Syntax for MySQL/MariaDB

Set Up #

Tonight’s lecture is completely browser based with nothing to install locally.

You can follow along by visiting astarml.com

We’ll be starting with basic SQL Syntax for MySQL on the sql-executor page.

Basic SQL Syntax (MySQL/MariaDB Specific) #

Working with Tables #

To list out any tables in the current database

SHOW TABLES;

To create a table, we use the CREATE command, and specify any columns for the table

CREATE TABLE logs(
    user varchar(100),
    action varchar(100),
    timestamp timestamp
)

To insert data into a table, we use the INSERT INTO command

INSERT INTO logs
VALUES ("jacob", "delete", "2025-01-19 03:14:07")

At this point, we noticed in our logs table, that we don’t actually say what record the action was taken on. We can need to change the table to add a column.

ALTER TABLE logs
ADD record varchar(100)

We can now view the columns of the table using the DESCRIBE command

DESCRIBE logs

Query Data From Tables #

The general syntax when trying to retrieve data from SQL tables is SELECT <what columns want to select> FROM <what table the data is in> WHERE <a conditional to filter the resulting rows

To grab everything in a table, we can use the shortcut * for “all columns”

SELECT * FROM products

Generally, we’d want to apply some filtering however, which we can do using the WHERE keyword.

SELECT * FROM products WHERE price > 100;

We can also select only a few columns, for example, if we only want the name and price columns.

SELECT name, price FROM products WHERE price > 100;

We can also order the query results in ascending or descending order by using the ORDER BY keyword.

SELECT name, price FROM products WHERE price > 100 ORDER BY price DESC;

To count the number of rows in a table, without actually returning the data, you can use the COUNT() function.

NOTE: You do need to include the SELECT keyword because you are selecting the count to be returned by the query.

SELECT COUNT(*) FROM products;

Joins #

If we want to join the columns of two related tables together, we can use the JOIN keyword. Note that the columns used as joining criteria need to be of the same type and values.

To prep this example, lets add a column to products table and call it owner_id

ALTER TABLE products
ADD owner_id integer

Then we need to assign the values to the rows. This query is a bit complicated so just copy and paste it, we’re essentially creating a switch statement and then executing it via the WHERE id IN an array of rows

UPDATE products
SET owner_id = CASE id
                WHEN 1 THEN 2
                WHEN 2 THEN 1
                WHEN 3 THEN 3
                WHEN 4 THEN 4
    END
WHERE id IN (1, 2, 3, 4);

So now we could do a join where we get the products from the products table, but join it with the users table to add in the names of the owners to the returned results. While not mandatory, when performing joins, it’s common to rename columns for clarity.

NOTE: Inner joins only grab the rows from each table that has matching columns. If either table has a row where the value used for comparison is missing, that row would be excluded from the results. If you want all rows from the first table and are okay with null values if the matching row in the second table is missing, you can use a LEFT JOIN instead.

SELECT
    p.id AS product_id,
    p.name AS product_name,
    p.price,
    p.owner_id,
    u.id AS user_id,
    u.username
FROM products AS p
INNER JOIN users AS u
ON u.id = p.owner_id;

UNIONS #

Union is used to return two separate select statements as a single list. NOTE that the number and type of each column in the UNION has to match. So if table_one has three columns and they are all integers, you can only UNION on 3 columns from table_two and those columns must also integers.

SELECT
    id,
    name
FROM products
UNION
SELECT
    id,
    username
FROM users;

Comments #

There are three basic ways to produce comments in MySQL/MariaDB.

Inline Comments #

We can use -- to specify a comment until the end of the line. Note that there needs to be a space after the “–”, so when writing SQL injection commands, I’ll often use -- - just to ensure I have that space as expected.

SELECT id, name FROM products -- - UNION SELECT id, username FROM users;

You’ll notice that although there’s a union after the -- -, the only data returned was from the prior to the dashes.

You can also use the hash sign # to specify a comment until the end of the line.

SELECT id, name FROM products # UNION SELECT id, username FROM users;

Block Comments #

MySQL/MariaDB also features block comments, though not as often used in SQL Injection Attacks.

SELECT
    id,
    name
FROM products
/* UNION
SELECT
    id,
    username
FROM users;
 */