SQL

• SQL
  • Syntax
    • Comment
    • Naming Convention
      • Column
      • Table
      • Primary key
      • Foreign key
      • Check
  • Create Database
    • Format
    • Example
  • Create Table
    • Format
    • Example
  • Queries
    • Simple
      • Format
      • Examples
        • Display one or several column_names
        • Display all column_names
    • Conditions
      • First - equality
      • IN
      • BETWEEN
      • LIKE
        • Search for one and only char after the input
        • Search for a string containing the input
    • DISTINCT keyword
      • Format
      • Examples
        • Without DISTINCT
        • With DISTINCT
    • Alias - AS
    • GROUP BY
    • ORDER BY
    • HAVING
    • WHERE VS HAVING
    • Reading order
  • Joins
  • Set operations
    • Union
    • Intersection
    • Difference
    • Cartesian product
  • Subqueries
    • Format of a simple subquery
  • Functions
    • Aggregation functions
    • Functions on strings
  • Stored procedures
    • Create a stored procedure
    • Call a stored procedure
    • Delete a prodecure
    • Modify a procedure
    • DELIMITER
    • BEGIN - END
    • Parameters
      • Format
      • IN
      • OUT
      • INOUT
    • Example of stored procedure
      • Procedure
      • Call
      • Result
  • Trigger
    • Create a trigger
    • Body
    • Events
    • Execution time
    • Convention
      • Rule on the number of triggers
      • Naming convention
    • OLD & NEW
    • Example
  • Database
    • Clients
    • Commandes
    • Produits
    • Details
  • Additional source

Syntax

• [] : Désigne une option
  
• | : Désigne un choix (ou)
  
• {} : Désigne une liste d'éléments à choisir

Comment

Insert a comment line is easy: you only have to add -- before your comment.

-- This is a comment line

Naming Convention

**From **

When having logical noun/verb language separations, component names will
be separated by the underscore character to indicate the separation.

Column

e.g. A column storing a user’s date of birth would be named date_of_birth and

a column storing time elapsed in seconds would be named time_elapsed_in_seconds.

Table

Table names will be defined in the plural. This is because a table is itself
a collection of 1 or more row entities (plural), and this naming convention
is also better understood in the object oriented world.

e.g. A table storing records of customer location addresses
would be named customer_location_addresses (rather than customer_location_address).

RQ: The name of a table can begin with a capital letter.

Primary key

Format

CONSTRAINT pk_table_name PRIMARY KEY (column_name)

Example:

CREATE TABLE IF NOT EXISTS Clients (
...,
CONSTRAINT pk_Clients PRIMARY KEY (numéro_client),
...
);

Foreign key

Format

The source is the source table, column is the column which will be the fk and target is the target table.

CONSTRAINT fk_source_column_target FOREIGN KEY (column_name) REFERENCES target_table(column_name)

Example:

CREATE TABLE IF NOT EXISTS Commandes (
...,
CONSTRAINT fk_commandes_numéro_client_clients FOREIGN KEY (numéro_client) REFERENCES Clients(numéro_client),
...
);

Check

Format

CONSTRAINT ck_column_name CHECK(column_name condition)

Example:

CREATE TABLE IF NOT EXISTS Produits (
...,
CONSTRAINT ck_quantité_stock CHECK(quantité_stock >= 0),
...
);

Create Database

Format

CREATE DATABASE [IF NOT EXISTS] db_name
[DEFAULT] CHARACTER SET [=] charset
[DEFAULT] COLLATE [=] collation;

Example

CREATE DATABASE IF NOT EXISTS Parc
CHARACTER SET utf8
COLLATE utf8_general_ci;

Create Table

Format

CREATE TABLE [IF NOT EXISTS] tbl_name (
    create definition…
) [table_options] [partition_options];

Example

CREATE TABLE IF NOT EXISTS Clients (
    numéro_client VARCHAR(4),
    nom VARCHAR(15),
    localité VARCHAR(15),
    catégorie VARCHAR(2),
    compte INT(7),
    CONSTRAINT pk_Clients PRIMARY KEY(numéro_client)
)
ENGINE = INNODB;

Queries

Simple

Format

SELECT column_name, function(column_name)
FROM table_name
WHERE condition;

Examples

Display one or several column_names

SELECT numéro_client, nom
FROM Clients
WHERE localité <=> 'TOULOUSE';

Display all column_names

SELECT *
FROM Clients
WHERE localité <=> 'TOULOUSE';

Conditions

First - equality

⇒ We'll use <=> instead of =.

IN

This condition is used for a search into a list.

RQ: When the search item isn't in the list, week-end can use NOT IN.

Format

SELECT numéro_client
FROM Clients
WHERE catégorie IN ('C1',  'C2', 'C3');

BETWEEN

This condition is used for a search in a range, instead of using <= and >=.

RQ: To exclude a range, use NOT BETWEEN.

SELECT numéro_client
FROM Clients
WHERE compte BETWEEN 1000 AND 4000;

LIKE

How to search for strings ?

Using LIKE is the solution, there are two jokers:

• %: any string of any length
  
• _: an only character

As always, you can exclude a string with NOT LIKE.

Format

SELECT column_name
FROM table_name
WHERE column_name LIKE 'name%';

EXAMPLES

Search for one and only char after the input

SELECT numéro_client
FROM Clients
WHERE catégorie LIKE 'B_';

The results could be: B1 or Bd.

They couldn't be: xB, B or B12.

Search for a string containing the input

SELECT numéro_produit
FROM Produits
WHERE Libelle LIKE '%SAPIN%';

The results could be: Boite en SAPIN or SAPIN verni.

They couldn't be: Boite en Sapin or S A P I N verni.

DISTINCT keyword

Format

RQ: Le mot clé doit se trouver à l'intérieur de la fonction.

SELECT DISTINCT column_name, function(DISTINCT  column_name)
FROM table_name
WHERE condition;

Examples

Without DISTINCT

SELECT localité
FROM Clients
WHERE catégorie <=> 'C1';

With DISTINCT

SELECT DISTINCT localité
FROM Clients
WHERE catégorie <=> 'C1';

Alias - AS

Example

SELECT numéro_produit AS Produit, 0.21*prix*quantité_stock AS Valeur_TVA
FROM Produits
WHERE quantité_stock > 50000; 

GROUP BY

To group several results from a same column and use it with an aggregation function.

An advantage of using GROUP BY is the automatic sorting of the values.

Format

SELECT column_name, func(column_name)
FROM table_name
GROUP BY column_name;

Example:

• Without GROUP BY

SELECT localité, COUNT(*) AS nombre_clients
FROM Clients;

Displays the first alpha-numerical value and count all values.

• With GROUP BY

SELECT localité, COUNT(*) AS nombre_clients
FROM Clients
GROUP BY localité;

Displays the sorted grouped values and the linked count values.

ORDER BY

To sort the result table after one or several columns in ascending (ASC) or descending (DESC) order.

RQ: The default order is ascending but it's better to use it.

Format

SELECT column_name
FROM table_name
ORDER BY column_name;

Example:

SELECT *
FROM Produits
ORDER BY prix;

HAVING

HAVING is like WHERE but it filters using functions like SUM, COUNT, AVG,….

It allows to select the chosen column, by grouping the identical values of the column, and to respect the condition.

Because the command groups the values, it isn't necessary to use GROUP BY whith it.

Format

SELECT column_name, func(column_name)
FROM table_name
GROUP BY column_name
HAVING condition;

Example: See the GROUP BY example

SELECT localité, COUNT(*) AS nombre_clients
FROM Clients
GROUP BY localité
HAVING COUNT(*) >= 3;

WHERE VS HAVING

In addition to working with functions, there is another difference between WHERE and HAVING:

• WHERE makes a selection on the rows
  
• Having makes it on the groups

Reading order

This is an example of the reading order of a more complex query:

7: SELECT column_name
1: FROM table_name
2: WHERE condition
3: AND otherCondition
4: GROUP BY column_name
5: HAVING condition
6: ORDER BY column_name

Joins

An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.

The most common type of join is: SQL INNER JOIN (simple join). An SQL INNER JOIN returns all rows from multiple tables where the join condition is met.

Set operations

Imagine you have 2 tables (T1 and T2):

Union

The UNION operator is used to combine the result-set of two or more SELECT statements.

Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types.

The mathematical notation is: T1 ∪ T2

The MySQL code will be:

SELECT Num FROM T1
UNION
SELECT Num FROM T2;

The result is:

Intersection

The intersection is used to return the results of two or more SELECT statements.

Each SQL statement must have the same number of fields in the result sets with similar data types.

The mathematical notation is: T1 ∩ T2

The MySQL code will be:

SELECT DISTINCT Num
FROM T1
WHERE T1.Num IN
    (SELECT T2.Num
     FROM T2);

The result is:

Difference

The difference is used to return all rows in the first SELECT statement that are not returned by the second SELECT statement.

Each SELECT statement must have the same number of fields in the result sets with similar data types.

The mathematical notation is: T1 − T2

The MySQL code will be:

SELECT DISTINCT Num
FROM T1
WHERE T1.Num NOT IN
    (SELECT T2.Num
     FROM T2);

The result is:

Cartesian product

Returns the Cartesian product of the sets of records from the two or more joined tables.

Thus, it equates to an inner join where the join-condition always evaluates to True or where the join-condition is absent from the statement.

The mathematical notation is: T1 × T2

The MySQL code will be:

SELECT T1.Num, T2.Num
FROM T1, T2;

The result is:

Subqueries

We use subqueries when we want a response from several tables.

For instance, we want the numéro_commande from the Clients living in Namur.

⇒ numéro_commande is from Commande and Namur is from Client.

Format of a simple subquery

The query will be:

SELECT numéro_commande, date_commande
FROM Commandes
WHERE numéro_client in (SELECT numéro_client
                FROM Clients
                WHERE localité <=> 'Namur');

Where the inner query is named subquery.

RQ: The reading direction is bottom-up.

Functions

Aggregation functions

There a five functions:

• AVG(): calculates the average of a set of records

• COUNT(): the number of records on a table or column

• MAX(): the maximum value (numeric or alphanumeric) of a column

• MIN(): the minimum value (numeric or alphanumeric) of a column

• SUM(): calculates the sum of a set of records

Functions on strings

RQ: _The strings can be values from a column as **catégorie

• CONCAT(string1, string2): concatenate several strings
  Example: CONCAT('192.168.1', '.', '10') → '192.168.1.10'
• SUBSTRING(string FROM position FOR length): the opposite of CONCAT(), it's used to truncate a string Example: SUBSTRING('A1' FROM 1 TO 1) → 'A'

Stored procedures

A stored procedure is actually a series of SQL statements identified by a name.

When a stored procedure is created, it is stored in the database that is being used, in the same way as a table for example.

Once the procedure is created, it can be called by name. The instructions of the procedure are then executed.

Create a stored procedure

DELIMITER |
CREATE PROCEDURE my_procedure([parameter1 [, parameter2,…]])
BEGIN
    SELECT column_name, function(column_name)
    FROM table_name
    WHERE condition;
END|
DELIMITER ;

Call a stored procedure

CALL my_procedure([parameter1 [, parameter2,…]);

Delete a prodecure

DROP PROCEDURE IF EXISTS my_procedure;

Modify a procedure

ALTER PROCEDURE IF EXISTS my_procedure;

DELIMITER

The delimiter has to be change by the DELIMITER command.

Indeed, the default delimiter is ; but, in the stored procedure, the delimiter has to be ;.

In that case, there'll be an error when arriving to the inside ;.

Thus the delimiter is replaced by |, // or $ for the stored procedure.

DELIMITER |

In this case, the delimiter is now |. That means that ; has no meaning anymore.

BEGIN - END

These two keywords are used to delimit one instruction block which can contain more than one instruction.

DELIMITER |
CREATE PROCEDURE name()
BEGIN
    instructions;
END|

Parameters

RQ: The name of a parameter has to begin by p_.

Example: p_client.

Format

CREATE PROCEDURE my_procedure(SENSE p_param_name TYPE)
...

There're three senses of parameters:

IN

It is an incoming parameter.

That is to say that it is a parameter whose value is supplied to the stored procedure. This value will be used during the procedure (for a calculation or a selection for example).

RQ: It is the default sense. That means that if no sense is given, it'll be this one who will be used.

OUT

It is an outgoing parameter whose value will be established during the procedure and which can then be used outside of this procedure.

INOUT

Such a parameter will be used during the procedure, may see its value changed by it, and will then be usable outside.

Example of stored procedure

Procedure

DELIMITER |
CREATE PROCEDURE get_nb_clients_by_location (IN p_nb_min_clients INT)
BEGIN
    SELECT localité, COUNT(*) AS nombre_clients
    FROM Clients
    GROUP BY localité
    HAVING COUNT(*) >= p_nb_min_clients;
END |
DELIMITER ;

Call

CALL get_nb_clients_by_location (3);

Result

Trigger

Create a trigger

CREATE TRIGGER trigger_name {BEFORE|AFTER} {INSERT|UPDATE|DELETE}
ON table_name FOR EACH ROW
trigger_body;

Body

The body of a trigger has the same format as a stored procedure.

Events

There are three different events which can trigger the execution of the instructions of a trigger:

• INSERT: The insertion of rows in the table is linked to the trigger.
• UPDATE: The modification of rows in the table is linked to the trigger
• DELETE: The deletion of rows in the table is linked to the trigger

A trigger is activated by only one of the three events.

But there can be several different triggers (one for each event).

Execution time

When a trigger is activated, its instructions can be executed at two different times:

• Either just before the trigger event occurs: BEFORE;
• or just after: AFTER.

Convention

Rule on the number of triggers

There can be only one trigger per combination.

That means that it can be only one BEFORE UPDATE per table.

RQ: Since there are two possibilities for the execution time, and three for the trigger event, we have a maximum of six triggers per table.

Naming convention

A trigger has an execution time, an event and a table name.

Thus, a trigger is unique and it can have an unique name:

trigger_name = time_event_table_name

OLD & NEW

Example

DELIMITER |
CREATE TRIGGER after_insert_clients AFTER INSERT
ON Clients FOR EACH ROW
-- BEGIN
    SET NEW.nom = UPPER(NEW.nom);
-- END|
DELIMITER ;

Database

Clients

Commandes

Produits

Details

Additional source

• sql.sh
  
• w3schools.com
  
• Les Hazlewood
  
• OpenClassrooms