12.1 Les bases de SQL

Cette partie comporte principalement des rappels de l’initiation à SQL. Les notions sont donc peu détaillées.

Une cheatsheet pratique ici.

12.1.1 Le langage SQL

Le langage SQL (structured query langage) est un langage de requêtes normalisé, standardisé, universel et descriptif.

Ce n’est pas un langage procédural : pas de boucles, de variables ou de gestion d’erreurs.

Il permet d’effectuer des opérations sur les bases de données et leur contenu.

Il s’utilise au sein de SBGDR (systèmes de gestion de données relationnelle) : PostgreSQL (logiciel libre), SQLite, Microsoft SQL Server, MySQL, Oracle Database…

Des IDE ont été développé pour simplifier le développement et la gestion des bases de données : pgAdmin pour PostgreSQL, Microsoft SQL Server Management Studio pour Microsoft SQL Server, SQL Plus et SQL Developer pour Oracle Database…

Il existe des langages procéduraux spécifiques à des SGBDR, ils permettent d’accéder à des fonctionnalités supplémentaires sous la forme de “procédures ou fonctions stockées”.
(!) Il est possible de faire 98% des choses avec simplement le SQL. Les langages procéduraux qui étendent le SQL sont propriétaires et présentent de nombreux désavantages.
Ex : Transact-SQL pour Microsoft SQL Server, PL-SQL pour Oracle Database

Les instructions peuvent être regroupées en quatre catégories (subjectives et divergentes selon les sources) :

DCL (Data Control Language) : GRANT, REVOKE
DDL (Data Description Language) : CREATE, ALTER, DROP
DML (Data Manipulation Language) : SELECT, INSERT, UPDATE, DELETE
TCL (Transaction Control Language) : BEGIN TRAN, COMMIT TRAN, SAVE TRAN, ROLLBACK TRAN

Par convention, les instructions SQL sont en majuscules et les noms de tables, colonnes, etc, sont en minuscules (et en snake_case).
Néanmoins, le langage n’est pas sensible à la casse et ce n’est donc pas obligatoire.

12.1.2 Normalisation des bases de données

Formes normales –> traduction :

une colonne = une et une seule donnée
(une colonne pour le nom, une pour le prénom : on ne mélange pas)
une seule colonne pour une même donnée
(une colonne “année” et surtout pas des colonnes “2014”, “2015”, etc)
une table = un et un seul thème
(une table “auteurs” avec la liste des auteurs et les informations qui les concernent, une autre table “ouvrages” avec la liste des ouvrages et les informations qui les concernent : oui, une colonne “auteur” dans la table “Ouvrages” mais on décrit l’auteur dans une table à part)
une ligne = une et une seule information donc une ligne est unique
(dans la table “auteur”, toutes les informations sur un auteur spécifique sont regroupées sur une seule ligne)

Plus de détails sur la normalisation des données ici.

12.1.3 Mise en place

Se connecter :

La connexion à une base de données est spécifique à chaque SGBD, avec toujours un login et un mot de passe.

Il existe différentes façon de se connecter, selon ses privilèges :

privilèges serveur : réservés à l’administrateur, notamment droits de propriété sur toutes les bases de données mais aussi sur les utilisateurs et leurs droits
privilèges base de donnée : pour les utilisateurs, notamment droits de propriété sur une base de donnée ou une table, droits d’écriture ou de lecture uniquement

Plus de détails sur les privilèges ici (article spécifique à SQL Server, mais la théorie est sensiblement la même pour les autres SGBD).

Créer une base de données :

CREATE DATABASE ma_bdd;

DROP DATABASE ma_bdd;

12.1.4 DCL

Les instructions telles que GRANT et REVOKE sont réservées à l’administrateur de la base de données. Elles permettent principalement de contrôler les accès utilisateur aux objets de base de données et à leur contenu.

Plus de détails sur le DCL ici.

12.1.5 DDL

Créer une table :

CREATE TABLE ma_table(
    ma_colonne_1 TYPE,
    ma_colonne_2 TYPE,
    ma_colonne_3 TYPE,
    ...
    );

Les types possibles sont décrits ici.
Brièvement : bit | bigint |int | smallint | tinyint | numeric | decimal | float | real | binary| varbinary | image char| varchar | text | money | smallmoney | time | timestamp | date | datetime | smalldatetime | nchar | nvarchar | ntext | timestamp | xml | sql variant.

Intégrité des données :

contrainte de non nullité : NOT NULL
contrainte d’unicité : UNIQUE (aussi appelée clé secondaire)
contrainte de clé primaire : PRIMARY KEY (regroupe implicitement les deux contraintes précédentes)
contrainte d’intégrité référentielle (clé étrangère) : FOREIGN KEY
contraite de validation : CHECK (conditions imposées)
(+) possibilité de donner une valeur par défaut : DEFAULT
(+) possibilité d’ajouter un compteur (incrément automatique, un seul par table) : IDENTITY

Une contrainte peut être indiquée sur la ligne de définition de la colonne ou à la fin de la déclaration de toutes les colonnes.

Il est possible d’avoir un effet sur la colonne FK lors de la modification de la colonne PK liée grâce à ON DELETE (et ON UPDATE, plus rare) avec comme options CASCADE, SET NULL et SET DEFAULT.
Par défaut, c’est ON DELETE NO ACTION qui est utilisé.

CREATE TABLE ma_table(
    ma_colonne_1 TYPE CONSTRAINT nn_matable_macolonne1 NOT NULL,
    ma_colonne_2 TYPE CONSTRAINT un_matable_macolonne2 UNIQUE,
    ma_colonne_3 TYPE CONSTRAINT pk matable_macolonne3 PRIMARY KEY,
    ma_colonne_4 TYPE,
    ma_colonne_5 TYPE,
    ma_colonne_6 TYPE,
    ma_colonne_7 TYPE IDENTITY(1,1),
    ...

 -- CONSTRAINT fk_matable_macolonne4 FOREIGN KEY ma_colonne_4 REFERENCES une_autre_table(colonne_a_lier),
 -- il n'est pas conseille de gerer les cles etrangeres pendant la creation de la table mais plus tard, une fois toutes les tables creees

    CONSTRAINT ck_matable_macolonne5 CHECK (ma_colonne_5 > 0),
    CONSTRAINT df_matable_macolonne6 DEFAULT 0
    );

Il est conseillé de donner un nom explicatif à chaque contrainte, pour faciliter leur (ré)utilisation.
Par convention, on commence par rappeler le type de contrainte puis le nom de la table puis le nom de la ou les colonnes concernées : pk_table_col1col2

Modifier une table :

Colonnes : ajouter, modifier, renommer, supprimer

ALTER TABLE ma_table ADD ma_nouvelle_colonne TYPE;

ALTER TABLE ma_table ALTER COLUMN ma_colonne_a_modifier NOUVEAU_TYPE;

ALTER TABLE ma_table RENAME COLUMN ancien_nom TO nouveau_nom;

ALTER TABLE ma_table DROP COLUMN ma_colonne_a_supprimer;

Contraintes : ajouter, supprimer, activer/désactiver

ALTER TABLE ma_table [WITH CHECK | WITH NO CHECK] ADD CONSTRAINT (description de la contrainte);

 -- ajout d'une cle etrangere
ALTER TABLE ma_table ADD CONSTRAINT fk_matable_macolonne4 FOREIGN KEY ma_colonne_4 REFERENCES une_autre_table(colonne_a_lier) ON DELETE CASCADE;

ALTER TABLE ma_table DROP CONSTRAINT contrainte_a_suprrimer;

ALTER TABLE ma_table {CHECK | NO CHECK} CONSTRAINT {ALL | contrainte_a_activer_desactiver};

Il est possible de passer outre les contraintes déjà en place avec WITH NO CHECK : attention, ce n’est pas très recommandé.

Supprimer une table :
D’abord supprimer les contraintes, puis la table

ALTER TABLE ma_table {CHECK | NO CHECK} CONSTRAINT ALL;

DROP TABLE ma_table;

Index : créer, supprimer, réorganiser, reconstruire

Les index sont automatiquement créés pour les clés primaires (CLUSTERED) et les clés secondaires.

On en définit généralement sur les clés étrangères et les colonnes de tri et de recherche.

CREATE [UNIQUE] UNCLUSTERED INDEX mon_index ON ma_table(une_colonne_a_indexer [ASC | DSC]);

DROP INDEX mon_index ON ma_table;

ALTER INDEX {ALL | mon_index} ON ma_table REORGANIZE;

ALTER INDEX {ALL | mon_index} ON ma_table REBUILD;

12.1.6 DML

Insérer des données :

INSERT [INTO] ma_table VALUES (val1,val2,val3...);

INSERT [INTO] ma_table(col1,col2) VALUES (val1,val2);

Modifier des données :

 -- pour toutes les lignes de la table
UPDATE ma_table SET ma_colonne = nouvelle_valeur;

 -- pour les lignes concernees par la condition
UPDATE ma_table SET ma_colonne = nouvelle_valeur
    WHERE {condition};

Supprimer des données :

 -- pour toutes les lignes de la table
DELETE [FROM] ma_table;

 -- pour les lignes concernees par la condition
DELETE [FROM] ma_table
    WHERE {condition};

Extraire (rechercher) des informations :

Projection : sélection des colonnes

Il est notamment possible d’utiliser ALL | DISTINCT et de faire des calculs élémentaires comme UPPER(colonne_a_mettre_en_majuscule) | CONVERT(type, expression, code du format de date) | ISNULL(boolean) | [texte a afficher] | TRIM().

 -- pour la table en entier
SELECT * FROM ma_table;

 -- pour toutes les lignes mais certaines colonnes uniquement
SELECT col1, col2 FROM ma_table;

 -- pour les lignes concernees par la condition et certaines colonnes
SELECT col4, col8 FROM ma_table
    WHERE {condition};

Restriction : sélection des lignes avec WHERE

Condition boléenne qui peut être composée de : colonnes, constantes, fonctions, opérateurs de comparaison et logiques tels que OR | AND, > | >= | < | <= | = | != ou <>, IN | BETWEEN | LIKE | IS NULL | IS NOT NULL.

Calcul d’agrégat, çàd regroupement de lignes, avec GROUP BY et des fonctions telles que COUNT() | SUM() | AVG() | MIN() | MAX().
Il est possible d’ajouter une restriction sur les lignes calculées avec HAVING.

Il est nécessaire d’avoir une liste de regroupements qui comprend au minimum la liste de la projection, à laquelle on enlève les colonnes utilisées pour le calcul d’agrégat.

SELECT col5 FROM ma_table
    WHERE {condition}
    GROUP BY {expression}
    HAVING {condition};

Tri : dernière clause du SELECT avec ORDER BY (qui peut être ASC | DESC).

A utiliser avec parcimonie car très coûteux.

Jointures : fusion de tables de différentes manières avec cross join, inner join, left | right | full outer join

SELECT a.col2, a.col6, a.col9 FROM ma_table AS a
    INNER JOIN autre_table AS b
    ON a.col1 = b.col1;

Les notions suivantes ne sont pas détaillées ici :

⊹ tables temporaires :

-- table temporaire locale
SELECT AVG(col2) AS moyenne INTO #table_temporaire FROM ma_table;

-- table temporaire globale
SELECT AVG(col2) AS moyenne INTO ##table_temporaire FROM ma_table;

⊹ sous-requêtes (imbriquées, corrélées) :

-- requete interne imbriquee
DELETE FROM ma_table_a 
    WHERE col4 NOT IN(
        SELECT DISTINCT col4 FROM ma_table_b 
    );
-- requete interne correlee ou subordonnee
DELETE FROM ma_table_a AS a 
    WHERE NOT EXISTS (
        SELECT DISTINCT col4 FROM ma_table_b AS b
            WHERE a.col4 = b.col4
    );

⊹ table CTE (Common Table Expression) :
éléments de la norme ANSI SQL99, pour simplifier l’écriture et donc la compréhension des requêtes complexes –> plus performantes que les tables temporaires

With Table_name_cte[(col1, col2)] AS (
  SELECT ...
);

⊹ UNION, EXCEPT, INTERSECT

⊹ vues :
Une vue stocke une requête SELECT mais jamais de données.

-- creer une vue
CREATE VIEW ma_vue[(colonne1, colonne2, ...)] AS 
  SELECT ...[WITH CHECK OPTION];

-- supprimer une vue
DROP VIEW ma_vue;

12.1.7 TCL

Les transactions permettent de créer des “bacs à sable”, çàd des environnements où l’on peut tester, se tromper, remonter dans le temps et, quand on est prêt, valider les modifications.

Une transaction est un ensemble indivisible d’instructions, qui suit les principes ACID (??).

Gérer une transaction :

BEGIN TRANSACTION ma_transaction;

-- si reussie, on la valide
COMMIT TRANSACTION ma_transaction;

-- si echouee, on l'annule (on remonte dans le temps)
ROLLBACK TRANSACTION ma_transaction;

-- on peut faire des points d'arrêt au sein d'une transaction
SAVE TRANSACTION mon_point_arret;

Il est possible d’abréger TRANSACTION par TRAN.

Un verrou permet de restreindre voire interdire l’accès à une partie des données. On l’utilise lorsque l’on ouvre une transaction, pour pas que les utilisateurs puissent interférer ou avoir accès à des données non validées.

Imposer un verrou pour n’afficher que les données validées :

SET TRANSACTION ISOLATION LEVEL READ COMMITED;

Il existe d’autres options : READ UNCOMMITED | REPEATABLE READ | SERIALIZABLE.