Partie 2 · SQL de base

Les types d'attributs et CREATE TABLE

Chaque colonne a un type. Il faut choisir le plus adapté, car les types occupent plus ou moins de place : on ne stocke pas un prénom dans un LONGTEXT mais dans un VARCHAR(40).

Famille	Types et idées du cours
Entiers	`INT` (`INTEGER` en est un synonyme) et ses variantes ; `UNSIGNED` = non signé ; `ZEROFILL` = remplissage par des zéros
Flottants	nombres à virgule, dont le domaine n'est pas continu (précision absolue impossible) : `FLOAT`, `DOUBLE` (`REAL` en est un synonyme) ; `DECIMAL` (`NUMERIC` en est un synonyme) est de taille fixe
Chaînes	`CHAR` (taille fixe), `VARCHAR` (variable) ; `TINYTEXT`, `TEXT`, `MEDIUMTEXT`, `LONGTEXT` (insensibles à la casse dans les tris/recherches) et leurs équivalents `BLOB` (sensibles à la casse, données binaires)
Dates et heures	`DATE` au format AAAA-MM-JJ ; `TIME` HH:MM:SS ; format étendu : date, espace, heure ; `TIMESTAMP` prend la date et l'heure d'insertion si on le laisse vide
Énumération	`ENUM('v1','v2'…)` : une valeur parmi une liste fixée à la création (65 535 valeurs au maximum)
Ensemble	`SET('voiture','moto','vélo')` : zéro, une ou plusieurs valeurs de la liste (64 éléments au maximum)

Tailles : VARCHAR, TEXT et BLOB sont de taille variable ; CHAR et DECIMAL de taille fixe. Noms (bases, tables, colonnes, index, alias) : lettres, chiffres, _ et $, 64 caractères au plus ; le point . est réservé (base.table.colonne). La casse dépend de l'OS : sans importance sous Windows, importante sous Unix.

Vérifie que tu as compris

Quel type convient le mieux à un prénom ?

Exemple du cours : un article de presse

Pour la version web d'un journal, un article a un titre, un texte, une date de parution, un auteur, une rubrique. Le titre : VARCHAR(80) ; le texte : TEXT ; la parution : DATE ; l'auteur : VARCHAR(80) ; la rubrique : un ENUM.

CREATE TABLE article (
  id MEDIUMINT UNSIGNED PRIMARY KEY,
  titre VARCHAR(80),
  texte TEXT,
  parution DATE,
  auteur VARCHAR(80),
  rubrique ENUM('économie','sports','international','politique','culture')
)

Limite : le nombre et le nom des rubriques sont figés à la création. Mieux : une table rubrique(id, label) et, dans article, une référence rubrique_idx ; une jointure donne le nom de la rubrique (WHERE article.rubrique_idx = rubrique.id). C'est le principe de la clé étrangère de la partie 1.

À toi de coder

Choisir les types d'un article de presse

Le cours construit la version web d'un journal : un titre de longueur raisonnable (VARCHAR(80)), un texte très grand (TEXT), une date de parution (DATE). Il faut choisir le type le plus adapté : on ne stocke pas un prénom dans un LONGTEXT.

Complète les trois types. (La rubrique est un simple entier ici, comme dans la 2e version du cours qui la sort dans une table à part ; ENUM n'existe pas en SQLite.) La structure lue est dans info.

import sqlite3
con = sqlite3.connect(":memory:")
con.execute("""CREATE TABLE Article (
    id INTEGER PRIMARY KEY,
    titre ___,
    texte ___,
    parution ___,
    auteur VARCHAR(80),
    rubrique_idx INTEGER
)""")
info = con.execute("PRAGMA table_info(Article)").fetchall()   # (position, nom, type, not null, défaut, clé)

Ctrl/⌘+Entrée

La commande CREATE TABLE

CREATE [TEMPORARY] TABLE nom_relation [IF NOT EXISTS] (
    nom_attribut TYPE_ATTRIBUT [OPTIONS]
    …
)

TEMPORARY : la table dure le temps de la connexion, puis est détruite.
IF NOT EXISTS : ne crée la table que si elle n'existe pas déjà.
À l'intérieur des parenthèses : les attributs, clés et index de la table.

Une table est vide à sa création, mais on peut la remplir d'un coup avec une requête : CREATE TABLE Personne (…) SELECT firstname, name, town, tel FROM Users. Les options IGNORE et REPLACE (avant le SELECT) gèrent les doublons : ignorer ou remplacer par la dernière valeur trouvée.

Vérifie que tu as compris

Que fait l'option IF NOT EXISTS dans un CREATE TABLE ?