04 Bases SQL Postgres - damiencorpataux/infradon1 GitHub Wiki

Données relationnelles dans Postgres

Un SGBDR comme Postgres permet de gérer des données relationnelles. Ces données sont stockées sous forme d'enregistrements dans des tables composées de colonnes, chaque colonne étant définie par un type, et d'autres options comme des index, des contraintes, etc.

Les tables peuvent être organisées par projet dans une database, éventuellement sous-organisés via des schemas. Ce sont les schémas qui contiennent directement les tables qui contiennent nos précieuses données.

Entre les tables, il y a des relations sous forme de clés étrangères qui sont à la base du concept de données relationnelles. Dans Postgres, chacun de ces élément est un objet.

Pour actionner le tout, c'est à dire créer une DATABASE et des TABLES contenant des COLUMNs, pour ensuite y INSERT INTO des données, nous utilisons: des instructions SQL.

Les objets de Postgres

Dans Postgres, pour organiser nos données, nous trouverons notamment les objets suivants:

  • DATABASE: Objet racine d'un ensemble de données (personnes, clients, commandes, paiements, etc.), les databases sont cloisonnées et n'ont pas accès entre elles

  • SCHEMA: Objet permettant de séparer les tables en groupes sémantiquement cohérents - nous utilisons uniquement le schéma par défaut public

  • TABLE: Permet de définir un entité et ses champs (terme relationnel: les colonnes)

  • COLUMN: Objet représentant un champs d'une entité

  • TYPE: Objet définissant le type de donnée contenu dans un colonne (booléen, numérique, caractère, chaîne de caractère, etc.)

  • FOREIGN KEY: Objet créant une référence vers une autre colonne - d'une autre table, ou de la même table pour créer une récusrivité (!)

  • CONSTRAINT: Objet permettant le contrôle des données lors de la modification de données

  • INDEX: Objet permettant d'accélérer l'interrogation des données

Data objects in Postgres

Structure vs. Contenu

Notez que nous pouvons classer les commandes SQL en deux groupes:

  • La partie structure: CREATE, ALTER, DROP
  • La partie contenu: INSERT INTO, UPDATE, DELETE FROM, SELECT

SQL Structure vs. Contenu

Les instructions SQL: Structure

Postgres met donc à disposition un ensemble d'objets qui nous permettent d'implémenter la structure de notre base de données, en fonction du modèle de données que nous avons conceptualisé en amont.

Databases

Tables

Les types de données

Au sens large, un type de donnée définit la manière dont une donnée va être encodée (en binaire) lors de son insertion dans un système numérique - ici un SGBDR.

Parmi les types les plus répandus, nous avons par exemple:

Les qualifieurs de colonnes

Par exemple:

CREATE TABLE contact (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    status_id INTEGER DEFAULT 1,
    CONSTRAINT status_id FOREIGN KEY(status_id) REFERENCES status(id)
)

Dans Postgres, nous trouvons:

Les opérateurs et comparateurs

Les opérateurs:

  • +, eg. SELECT 1+1
  • -, eg. SELECT 10-1
  • *, eg. SELECT 2*2
  • /, eg. SELECT 10/2
  • ^, eg. SELECT 3^2
    • Trouvez sur internet une liste de fonctions built-in Postgres et essayez en quelques unes
    • Notez vos essais, résultats et surtout: vos observations personnelles

Les comparateurs:

  • =, eg. SELECT 1=1
  • <, eg. SELECT 1<2
  • >, eg. SELECT 2>1
  • <=, eg. SELECT 1<=2
  • >=, eg. SELECT 2>=1
  • AND eg. SELECT 1=1 AND 2=2
  • OR eg. SELECT 1=1 AND 2=200
  • NOT eg. SELECT NOT 1=1
  • IN, eg. SELECT 1 IN (1, 2, 3)

Exemples complets:

  • SELECT * FROM contact WHERE id IN (1,2,3)
  • SELECT * FROM contact WHERE id IN (1,2,3) OR id > 100
  • SELECT * FROM contact WHERE id IN (SELECT id FROM status WHERE id IN (1, 3))

Les fonctions

  • SUM(), eg. SELECT SUM(1, 2, 3)
  • AVG(), eg. SELECT AVG(1, 2, 3)
  • CONCAT(), eg. SELECT CONCAT('abc', ' ', 'def')
  • COUNT(), eg. SELECT COUNT(*) FROM table
    • Trouvez sur le web une liste de fonctions built-in dans Postgres, leurs définition et usage - et essayez en quelques unes
    • Notez vos essais, résultats et surtout: vos observations personnelles

Les instructions SQL: Contenu

Actions sur les données

Interrogation, requêtage

Exercice: Bases

Opérations de base (sans stockage de données):

  • SELECT 1+1;
  • SELECT SUM(1, 1);
  • SELECT CONCAT('1' || '1');
  • SELECT CONCAT('1' || 1); - lisez bien le message d'erreur
  • etc...

Opérations sur la structure de données:

  • CREATE DATABASE mydatabase
  • List databases (dans psql: \l)
  • Select database (dans psql: \c mydatabase)
  • CREATE TABLE personne (id SERIAL PRIMARY KEY, name TEXT)
  • List tables (dans psql: \d ou \dt)
  • Describe table (dans psql: \d personne)

Opérations sur les données (peuplement du contenu):

  • INSERT INTO personne (name) VALUES ('Name 1')
  • INSERT INTO personne (name) VALUES ('Nom 2')
  • INSERT INTO personne (name) VALUES ('Näme 3')

Interrogation des données:

  • SELECT * FROM personne
  • SELECT * FROM personne WHERE name = 'Name 1'
  • SELECT * FROM personne WHERE name = 'Name 1' OR name = 'Nom 2'
  • SELECT * FROM personne WHERE name LIKE 'Name'
  • SELECT * FROM personne WHERE name LIKE 'Name%'
  • SELECT * FROM personne WHERE name LIKE '%'
  • SELECT * FROM personne WHERE name LIKE 'Mon%' OR name LIKE 'Ton%'
  • SELECT * FROM personne WHERE NOT name LIKE 'Son%'
  • SELECT COUNT(*) FROM personne
  • SELECT string_agg(name, ', ') FROM personne

Toutes les commandes SQL:

Exercice: Dérivation des modèles

A l'aide de quelques exemples minimalistes, nous allons démontrer tout le workflow pour passer d'un diagramme de classes (niveau conceptuel) au code SQL (niveau implémentation) pour faire réellement fonctionner notre service SGBDR.

Votre serviteur va vous proposer différents diagrammes, tirés des exemples du cours, que nous allons dériver pour les implémenter au niveau SQL.

Take Away

...todo

Si vous n'êtes pas présent à cette séance, il vous est fortement recommendé de la rattraper en communiquant avec vos collègues - une grande quantité de connaissance est transmise par la pratique ce jour-là.

⚠️ **GitHub.com Fallback** ⚠️