Simple Query Language

Interrogation de Données

1. Select

SELECT * FROM users;

2. Column Aliases

SELECT
    "firstName" as first_name,
    last_name
FROM
    users;

3. Order By

SELECT
    "firstName" as first_name,
    last_name
FROM
    users
ORDER BY
    first_name ASC,
    last_name DESC;

4. Distinct

SELECT
    "firstName" as first_name,
    DISTINCT last_name
FROM
    users
ORDER BY
    first_name ASC,
    last_name DESC;

Filtrage de Données

1. Where

SELECT
    first_name,
    "lastName"
FROM
    users
WHERE
    first_name = 'John' AND
    "lastName" = 'Doe';

2. Limit / Offset

SELECT
    first_name,
    "lastName"
FROM
    users
WHERE
    first_name = 'John' OR
    "lastName" != 'Doe'
LIMIT 5 OFFSET 3;

3. In

SELECT
    user_id,
    first_name,
    "lastName"
FROM
    users
WHERE
    user_id IN (1, 2, 3) OR
    "lastName" NOT IN ('Doe', 'Smith');

4. Between

SELECT
    user_id,
    first_name,
    "lastName"
FROM
    users
WHERE
    user_id BETWEEN 1 AND 3;

5. Like

SELECT
    user_id,
    first_name,
    "lastName"
FROM
    users
WHERE
    first_name LIKE '%oh%' AND -- John
    "lastName" NOT LIKE '_o_'; -- Doe

6. Ilike #PSQL

SELECT
    user_id,
    first_name,
    "lastName"
FROM
    users
WHERE
    first_name ILIKE '%oh%' AND -- John | JOHN
    "lastName" NOT ILIKE '_o_'; -- Doe | DOE

7. Null

SELECT
    user_id,
    first_name,
    "lastName"
FROM
    users
WHERE
    first_name IS NULL OR
    "lastName" IS NOT NULL;

Jointures

1. Table Aliases

SELECT
    u.user_id,
    first_name,
    "lastName",
    phone_number,
    "phoneModel"
FROM
    users u
INNER JOIN phones p
    ON u.user_id = p.user_id;

2. Inner Join

SELECT
    user_id,
    first_name,
    "lastName",
    phone_number,
    "phoneModel"
FROM
    users
INNER JOIN phones USING(user_id);

3. Left Join

SELECT
    user_id,
    first_name,
    "lastName",
    phone_number,
    "phoneModel"
FROM
    users
LEFT JOIN phones USING(user_id);

4. Left (Outer) Join

SELECT
    u.user_id,
    first_name,
    "lastName",
    phone_number,
    "phoneModel"
FROM
    users u
LEFT JOIN phones p
    ON u.user_id = p.user_id
WHERE p.user_id IS NULL;

5. Right Join

SELECT
    user_id,
    first_name,
    "lastName",
    phone_number,
    "phoneModel"
FROM
    users
RIGHT JOIN phones USING(user_id);

6. Right (Outer) Join

SELECT
    u.user_id,
    first_name,
    "lastName",
    phone_number,
    "phoneModel"
FROM
    users u
RIGHT JOIN phones p
    ON u.user_id = p.user_id
WHERE u.user_id IS NULL;

Regroupement de Données

1. Group By

SELECT
    user_id,
    "firstName" || ' ' || last_name full_name,
    COUNT(phone_number)
FROM
    users
INNER JOIN phonep USING(user_id)
GROUP BY
    user_id
ORDER BY
    COUNT(phone_number) DESC;

2. Having

SELECT
    user_id,
    "firstName" || ' ' || last_name full_name,
    COUNT(phone_number)
FROM
    users
INNER JOIN phones USING(user_id)
GROUP BY
    user_id
HAVING
    COUNT(phone_number) < 5;

Modification de Données

1. Insert

INSERT INTO users (first_name, "lastName", random_number, birth_date)
VALUES
    ('Damien', 'Chazoule', 42, '1993-05-21'),
    ('Tim', 'Berners-Lee', 21, '1955-06-08');

2. Update

UPDATE users
SET first_name = 'Benjamin'
WHERE user_id = 42;

3. Delete

DELETE FROM users
WHERE user_id = 42;

Gestion de Tables

1. Create Table

CREATE TABLE [IF NOT EXISTS] users (
    user_id INT PRIMARY KEY,
    email VARCHAR (128) UNIQUE NOT NULL,
    first_name VARCHAR (64),
    last_name VARCHAR (64),
    year_old INT NOT NULL
);

2. Rename Table

ALTER TABLE [IF EXISTS] users
RENAME TO accounts;

3. Drop Table

DROP TABLE [IF EXISTS]
    users,
    phones;

4. Add Column

ALTER TABLE users
ADD COLUMN birth_date DATE NOT NULL;

5. Rename Column

ALTER TABLE users
RENAME COLUMN nick_name
TO user_name;

6. Drop Column

ALTER TABLE users
DROP COLUMN birth_date;

Type Composite

PSQL

1. Create Type

CREATE TYPE address AS (street TEXT, city TEXT, zip_code INTEGER);

2. Managing Table

CREATE TABLE contacts (name TEXT, contact_address address);

3. Modifying Data

INSERT INTO contacts (name, contact_address) VALUES ('John Doe', ROW('42 Place de la République', 'Le Mans', 72000));

4. Querying Data

SELECT name, contact_address.city FROM contacts;