info:bdd:sqlitevscode

Sommaire Bdd

Le paragraphe “Programmation” fait référence au projet VSCode contenant la base sejour.db.Téléchargeable ici pour tester les exemples
  • MCD

Remarque
- Chaque client séjourne dans une à plusieurs stations
- Chaque station accueille zéro ou plusieurs clients

  • MLD

Remarque : les flèches pointent vers les parents.

AVANT de COMMENCER

SQLITE3 doit être installé sur le PC (voir How To Download & Install SQLite Tools). L'extension SQLite doit être installée dans VSCode.


2.1 Créer une base

  1. Créer un répertoire et l'ouvrir dans VSCode (Exemple ci-contre : Sejours)
  2. Créer un fichier avec l'extension .db (Exemple : sejour.db)
L’extension de fichier .db contient la base de données créée avec le système de gestion de base de données SQLite.


2.2 Ouvrir la base dans l'explorateur Sqlite

Exemple : sélection de sejours.db

Faire un clic droit sur le fichier .db et sélectionner Open database ou F1 → SQLite: Open database et choisir la base de données.L'explorateur Sqlite (SQLITE EXPLORER) s'ouvre et affiche la base sélectionnée en bas et à gauche de la fenêtre.


2.3 Se connecter à la base

  1. Dans un nouveau projet, cliquer sur dans la barre d'état et sélectionner SQLite. Dans un projet existant, cliquer sur un fichier .sql pour faire apparaître les données dans la barre d'état.
  2. Cliquer sur et sélectionner la base à utiliser (Ex : sejour.db).

2.4 Créer une table

Contrairement à la norme SQL et aux autres systèmes de base de données, seulement trois modifications sont possibles:
- Renommer une table.
- Renommer une colonne.
- Ajouter une nouvelle colonne à une table.
En dehors de ces deux possibilités, la table devra être reconstruite. Le nom des attributs doit donc être choisi avec soin.

  1. Faire un clic droit sur le nom de la base dans SQLITE EXPLORER et sélectionner New Query.
    • Une nouvelle page avec l'extension .sql s'ouvre dans l'éditeur.
    • L'enregistrer avec le nom creation.sql. Les requêtes SQL de création des tables seront sauvegardées dans ce fichier.
    • Entrer la requête ci-dessous dans le fichier creation.sql.
      *.sql
      CREATE TABLE clients
      (
          id INTEGER PRIMARY KEY, -- alias de rowid
          nom VARCHAR(50),
          prenom VARCHAR(50), 
          ville VARCHAR(50),
          region VARCHAR(50),
          solde INT
      );
  2. Sélectionner la requête, faire un clic droit dessus et sélectionner Run Selected Query. Si la requête est correcte, la page ci-dessous s'ouvre pour indiquer que la table a été créé.
  3. Rafraîchir la base en cliquant sur l'icône dans SQLITE EXPLORER pour voir apparaître la table clients.

CONTRAINTES DE CLÉ ÉTRANGÈRE

Le paramétrage de VSCode pour la prise en charge des contraintes de clé étrangère est traité au paragraphe 3 “Contraintes d'intégrité”.


2.5 Supprimer une table

DROP TABLE IF EXISTS nomtable;

Poursuivre les modifications ici

2.6 Ajouter et modifier des données dans une table

2.6.1 Ajouter des données

Syntaxe

*.sql
INSERT INTO table1 VALUES
(valeur1-1, sur toutes les colonnes...),
(valeur2-1, sur toutes les colonnes...),
...;

Exemple

  1. Cliquer sur l'icône pour créer une nouvelle page .sql. L'enregistrer sous le nom insertions.sql.
  2. Nous allons placer les requêtes SQL d'insertion des données dans les tables dans ce fichier.
    Entrer la requête ci-dessous dans le fichier insertions.sql.
    *.sql
    -- Un seul enregistrement
    -- Id est automatiquement incrémenté
    INSERT INTO clients(nom,prenom,ville,region,solde)
    VALUES
    ('Bauer','Elmut','Berlin','Europe',9825);
  3. Sélectionner la requête, faire un clic droit dessus et sélectionner Run Selected Query.Si la requête est correcte, un clic sur dans SQLITE EXPLORER affiche la table comme ci-dessous.
  4. On peut également entrer plusieurs enregistrements dans la table comme dans la requête ci-dessous.
    *.sql
    -- Plusieurs enregistrements
    -- Id est automatiquement incrémenté
    INSERT INTO clients(nom,prenom,ville,region,solde)
    VALUES
    ('Smith','John','Londres','Europe',12436),
    ('Jonhson','Britney','New York','Amérique',6721);
  5. Entrer cette requête dans le même fichier, la sélectionner et l'exécuter. La table doit maintenant correspondre à la figure ci-dessous.

2.6.2 Modifier des cellules

Syntaxe

*.sql
UPDATE TABLE SET col1=val1
WHERE [sélection]

Exemple

  1. Cliquer sur l'icône pour créer une nouvelle page .sql. L'enregistrer sous le nom mises_a_jour.sql.
  2. Entrer la requête ci-dessous dans le fichier mises_a_jour.sql.
    *.sql
    UPDATE clients SET solde = 9000
    WHERE nom="Bauer";
  3. Sélectionner la requête, faire un clic droit dessus et sélectionner Run Selected Query.Si la requête est correcte, un clic sur dans SQLITE EXPLORER affiche la table comme ci-dessous.

2.6.3 Supprimer des lignes

Syntaxe

*.sql
DELETE FROM table1 
WHERE [sélection]

Exemple

*.sql
DELETE FROM Clients 
WHERE Id>2 -- Supprime la troisième ligne dans la table


2.7 Interroger la base

  • Modèle logique de données de la base sejour

Les exemples ci-dessous peuvent être testés avec VSCode après avoir téléchargé et installé le projet Sejours.
2.7.1 Requête de base

Syntaxe

*.sql
SELECT colonne1 [AS alias1],
       colonne2 [AS alias2]
FROM table1 [AS t1], table2 [AS t2]
WHERE [critères de jointure et sélection]
GROUP BY colonnei
ORDER BY colonnej [ASC|DESC];

2.7.2 Sélection simple

On peut sélectionner des colonnes d'une ou plusieurs tables données en paramètre avec SELECT. La commande WHERE permet de spécifier des critères de sélection.

Exemple : nom et du lieu des stations se trouvant en Europe ?

*.sql
SELECT nomstation, lieu
FROM stations
WHERE region = 'Europe';
2.7.3 Tri des résultats
Il est possible de trier le résultat d'une requête avec la clause ORDER BY suivie de la liste des attributs suivie des mots-clés ASC (croissant) ou DESC (décroissant). Par défaut le tri est croissant.

Exemple

*.sql
SELECT *
FROM stations 
ORDER BY region ASC, lieu DESC;

2.7.4 Mise en forme

Il est possible de renommer les attributs avec le mot-clé AS. On utilise un alias pour en faciliter la lecture lors de l'affichage.

Exemple : le contenu des colonnes nom et prenom est regroupé dans une colonne avec l'alias Individu.

*.sql
SELECT prenom||' '||nom AS Individu, ville AS Ville
FROM clients;
2.7.5 Fonctions de groupes (agrégation)
Les fonctions de groupe permettent d'obtenir des informations sur un ensemble de lignes en travaillant sur les colonnes et non pas sur les lignes comme avec WHERE.


Exemples de fonctions

  • COUNT donne le nombre de lignes d'une colonne.
  • AVG calcule la moyenne d'une colonne.
  • SUM calcule la somme d'une colonne
  • MIN,MAX calculent le minimum et le maximum d'une colonne

Exemple : combien de stations situées en Europe sont-elles référencées dans la base de données ?

*.sql
SELECT COUNT(nomstation) AS Nombre
FROM stations 
WHERE region='Europe';
2.7.6 Jointures

Une jointure permet d'associer plusieurs tables dans une même requête.

Syntaxe

*.sql
SELECT colonne1 [AS c1],
       colonne2 [AS c2]
FROM table1 [AS t1] JOIN table2 [AS t2] ON t1.clea = t2.cleb
AND t1.cleb = t2.cleb;

Exemple : liste (partiel) des clients avec les séjours qu'ils ont effectués.

*.sql
SELECT nom AS "Nom Client", nomstation AS Station 
FROM clients 
JOIN sejours ON clients.id=sejours.id
ORDER BY nom;
  • info/bdd/sqlitevscode.txt
  • Dernière modification : 2024/03/20 15:44
  • de phil