Modification et création d'une base de données

Les types de données en SQL

Types numériques

INTEGER

Type de données entier. La plage de valeurs possibles dépend du SGBDR utilisé.

Pour MySQL (version 32 bits): de -2147483648 to 2147483647 (de -231 à 231-1) -> stockage sur 4 octets

L'entier peut être défini non signé grâce à l'instruction UNSIGNED (INTEGER UNSIGNED).

SMALLINT

Petit entier. Stockage moins important en mémoire.

Pour MySQL (version 32 bits): -32768 à 32767 (de -215 à 215-1) -> stockage sur 2 octets

L'entier peut être défini non signé grâce à l'instruction UNSIGNED (SMALLINT UNSIGNED).

TINYINT

Un très petit entier. Stockage encore moins important en mémoire.

Pour MySQL (version 32 bits): -128 à 127 (de -27 à 27-1) -> stockage sur 1 octets

DECIMAL

Nombre décimal stockée sous la forme d'une chaîne de caractères.

DECIMAL(M,D):

  • M -> nombre total de chiffres décrivant le nombre décimal

  • D -> nombre de chiffres après la virgule

Par exemple, pour stocker des euros: DECIMAL(5,2) permettra de stocker les euros, au centime près, jusqu'à de -999.99 à 999.99 euros.

FLOAT et DOUBLE

Nombre réel à virgule flottante: une mantisse + un exposant.

La précision de FLOAT et DOUBLE dépend du SGBDR (et de la machine) utilisé(s).

Types caractères

CHARACTER ou CHAR

Stockage d'un ou plusieurs caractères:

  • CHAR : stockage d'un seul caractère

  • CHAR(M) : stockage de chaînes de M caractères. Si la chaîne entrée n'atteint pas M caractères, le SGBDR ajoute des caractères blancs pour compléter la chaîne jusqu'à M caractères.

  • VARCHAR(M) : idem que pour CHAR(M) mais sans complétion par des caractères blancs. La longueur de stockage (en octets) est alors variable.

Types temporels

DATE

DATE représente une date du calendrier limitée du 1er janvier de l'an 1 ('''0001-01-01''') au 31 décembre de l'an 9999 ('''9999-12-31''').

TIME

TIME désigne un temps sur vingt-quatre heures en heures, minutes et secondes ('''HH:MM:SS''').

TIMESTAMP

TIMESTAMP désigne une date combinée à un temps ('''AAAA-MM-JJ HH:MM:SS''')

Types BLOB/TEXT

Une valeur de type BLOB est un objet binaire de grande taille, qui peut contenir une quantité variable de données. Les quatre types BLOB (TINYBLOB, BLOB, MEDIUMBLOB, et LONGBLOB) ne différent que par la taille maximale de données qu'ils peuvent stocker.

Les quatre types TEXT (TINYTEXT, TEXT, MEDIUMTEXT, et LONGTEXT correspondent aux types BLOB équivalents, et ont les mêmes contraintes de stockage. Les seules différences entre les colonnes de type BLOB et celles de type TEXT se situent aux niveau des tris et comparaisons: Les tris, faits sur les BLOB, contrairement à ceux faits sur les TEXT, tiennent compte de la casse. En d'autres termes, une valeur TEXT est une valeur BLOB insensible à la casse.

Si vous assignez une valeur trop grande à une colonne de type BLOB ou TEXT, la valeur sera tronquée à la taille maximale possible.

Dans la majorité des cas, vous pouvez considérer une colonne de type TEXT comme une colonne de type VARCHAR, aussi grande que vous le souhaitez.

Autres types de données

BOOLEAN

BOOLEAN ou LOGICAL permet de stocker des variables booléennes qui peuvent prendre les valeurs TRUE, FALSE ou UNKNOWN.

DOMAIN

Il est possible en SQL de créer ses propres types à partir des types de données pré-existants.

CREATE DOMAIN DOM_CODE_POSTAL AS CHAR(5)

Créations des éléments d'une base de données

Nom des objets d'une même base de données

Il doit obéir à certains critères:

  • Le nom doit être unique dans le SGBDR utilisé (c'est un identifiant de la BDR).

  • Il doit commencer par un caractère alphabétique (A-Z ou a-z)

  • Le nom ne doit contenir que le jeu de caractères de A à Z, de 0 à 9 et le caractère blanc souligné (''%%_%%'')

  • Il ne doit pas être plus long que 128 caractères

  • Il ne doit pas être un mot réservé (ex. WHERE, SELECT, etc.)

Création/Suppression d'une base de données

La commande CREATE DATABASE crée une base de données.

mysql> CREATE DATABASE Base_test;

La commande DROP DATABASE supprime une base de données ainsi que son contenu.

mysql> DROP DATABASE Base_test;

Le nom de la base de données est sensible à la casse.

Création/Suppresion d'une table

La création d'une table s'effectue avec la commande CREATE TABLE, la suppression avec la commande DROP TABLE suivi du nom de la table. Ce nom est sensible à la casse.

Syntaxes:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] nomTable
   (nom_colonne type_colonne [, nom_colonne2 type_colonne2 ...]);
mysql> CREATE TABLE Table_test
       (Nombre INTEGER,
        Reel   FLOAT,
        Chaine VARCHAR(100));
mysql> desc Table_test;
+--------+--------------+------+-----+---------+-------+
 | Field | Type | Null | Key | Default | Extra | 
 | ----- | ---- | ---- | --- | ------- | ----- | 
+--------+--------------+------+-----+---------+-------+
 | Nombre | int(11)      | YES |  | NULL |  | 
 | ------ | -------      | --- |  | ---- |  | 
 | Reel   | float        | YES |  | NULL |  | 
 | Chaine | varchar(100) | YES |  | NULL |  | 
+--------+--------------+------+-----+---------+-------+
3 rows in set (0.35 sec)
DROP [TEMPORARY] TABLE [IF EXISTS]
    tbl_name [, tbl_name] ...

Contraintes de colonnes

Un certain nombre de contraintes peuvent être posées directement sur les colonnes des tables.

Lorsqu'une contrainte est posée, aucune insertion, modification ou suppression n'est possible sans le respect absolue de la contrainte.

Donnée obligatoire

Par le mot-clé NOT NULL, on peut obliger une colonne à toujours posséder une valeur.

mysql> CREATE TABLE Table_test
       (Nombre INTEGER NOT NULL,
        Reel   FLOAT,
        Chaine VARCHAR(100));
mysql> desc Table_test;
+--------+--------------+------+-----+---------+-------+
 | Field | Type | Null | Key | Default | Extra | 
 | ----- | ---- | ---- | --- | ------- | ----- | 
+--------+--------------+------+-----+---------+-------+
 | Nombre | int(11)      | NO  |  | NULL |  | 
 | ------ | -------      | --  |  | ---- |  | 
 | Reel   | float        | YES |  | NULL |  | 
 | Chaine | varchar(100) | YES |  | NULL |  | 
+--------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Valeur par défaut

Mot-clé: DEFAULT

mysql> CREATE TABLE Table_test
       (Nombre INTEGER NOT NULL,
        Reel   FLOAT,
        Chaine VARCHAR(100) DEFAULT "Toto",
        Date TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
mysql> desc Table_test;
+--------+--------------+------+-----+-------------------+-------+
 | Field | Type | Null | Key | Default | Extra | 
 | ----- | ---- | ---- | --- | ------- | ----- | 
+--------+--------------+------+-----+-------------------+-------+
 | Nombre | int(11)      | NO  |  | NULL              |  | 
 | ------ | -------      | --  |  | ----              |  | 
 | Reel   | float        | YES |  | NULL              |  | 
 | Chaine | varchar(100) | YES |  | Toto              |  | 
 | Date   | timestamp    | YES |  | CURRENT_TIMESTAMP |  | 
+--------+--------------+------+-----+-------------------+-------+
4 rows in set (0.00 sec)

colonne clef

Une colonne unique peut servir de clef à une table. Cela peut être précisé à la définition de la colonne grâce au mot-clé PRIMARY KEY.

En principe, lorsqu'on définit une clé sur une table, la valeur de cette clé doit être unique. Il est possible pour se faciliter la tâche d'employer en plus un autre mot-clé: AUTO_INCREMENT qui incrémente automatiquement à chaque ajout d'une ligne de données la valeur de la colonne correspondante.

mysql> CREATE TABLE animals (
          id MEDIUMINT NOT NULL AUTO_INCREMENT,
          name CHAR(30) NOT NULL,
          PRIMARY KEY (id)
       );
mysql> desc animals;
+-------+--------------+------+-----+---------+----------------+
 | Field | Type | Null | Key | Default | Extra | 
 | ----- | ---- | ---- | --- | ------- | ----- | 
+-------+--------------+------+-----+---------+----------------+
 | id   | mediumint(9) | NO | PRI | NULL | auto_increment | 
 | --   | ------------ | -- | --- | ---- | -------------- | 
 | name | char(30)     | NO |     | NULL |                | 
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

Valeur unique

Une colonne peut recevoir une valeur ayant un caractère unique au sein de la colonne grâce au mot-clé UNIQUE.

mysql> CREATE TABLE Table_test
       (Nombre INTEGER NOT NULL UNIQUE,
        Reel   FLOAT,
        Chaine VARCHAR(100) DEFAULT "Toto",
        Date TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
+--------+--------------+------+-----+-------------------+-------+
 | Field | Type | Null | Key | Default | Extra | 
 | ----- | ---- | ---- | --- | ------- | ----- | 
+--------+--------------+------+-----+-------------------+-------+
 | Nombre | int(11)      | NO  | PRI | NULL              |  | 
 | ------ | -------      | --  | --- | ----              |  | 
 | Reel   | float        | YES |     | NULL              |  | 
 | Chaine | varchar(100) | YES |     | Toto              |  | 
 | Date   | timestamp    | YES |     | CURRENT_TIMESTAMP |  | 
+--------+--------------+------+-----+-------------------+-------+
4 rows in set (0.00 sec)

Contrôle de validité

La contrainte CHECK permet de préciser une condition de validité des valeurs de la colonne.

mysql> CREATE TABLE Table_test
       (Nombre INTEGER NOT NULL UNIQUE CHECK (Nombre BETWEEN 1 AND 102),
        Reel   FLOAT,
        Chaine VARCHAR(100) DEFAULT "Toto",
        Date TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
mysql> desc Table_test;
+--------+--------------+------+-----+-------------------+-------+
 | Field | Type | Null | Key | Default | Extra | 
 | ----- | ---- | ---- | --- | ------- | ----- | 
+--------+--------------+------+-----+-------------------+-------+
 | Nombre | int(11)      | NO  | PRI | NULL              |  | 
 | ------ | -------      | --  | --- | ----              |  | 
 | Reel   | float        | YES |     | NULL              |  | 
 | Chaine | varchar(100) | YES |     | Toto              |  | 
 | DATE   | timestamp    | YES |     | CURRENT_TIMESTAMP |  | 
+--------+--------------+------+-----+-------------------+-------+
4 rows in set (0.00 sec)

Clé(s) étrangère(s)

Si une colonne se trouve être une référence à une clé dans un autre table, on peut établir un contrôle de validité sur cette colonne grâce aux mot-clés FOREIGN KEY et REFERENCES

mysql>CREATE TABLE Joueurs
(
 JoueurID          INTEGER AUTO_INCREMENT NOT NULL,
 Numero            INTEGER NOT NULL,
 Prenom            VARCHAR(100) NOT NULL,
 Nom               VARCHAR(100) NOT NULL,
 Poste             VARCHAR(100) NOT NULL,
 Date_de_naissance DATE NOT NULL,
 Nationalite       VARCHAR(3) NOT NULL,
 ClubID            INTEGER NOT NULL,
 PRIMARY KEY (JoueurID),
 FOREIGN KEY (ClubID) REFERENCES Club,
 UNIQUE (Numero)
);
mysql> desc Joueurs;
+-------------------+--------------+------+-----+---------+----------------+
 | Field | Type | Null | Key | Default | Extra | 
 | ----- | ---- | ---- | --- | ------- | ----- | 
+-------------------+--------------+------+-----+---------+----------------+
 | JoueurID          | int(11)      | NO | PRI | NULL | auto_increment | 
 | --------          | -------      | -- | --- | ---- | -------------- | 
 | Numero            | int(11)      | NO |     | NULL |                | 
 | Prenom            | varchar(100) | NO |     | NULL |                | 
 | Nom               | varchar(100) | NO |     | NULL |                | 
 | Poste             | varchar(100) | NO |     | NULL |                | 
 | Date_de_naissance | date         | NO |     | NULL |                | 
 | Nationalite       | varchar(3)   | NO |     | NULL |                | 
 | ClubID            | int(11)      | NO | MUL | NULL |                | 
+-------------------+--------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

Contraintes de table

D'autres contraintes peuvent être imposées sur la table elle-même et, par conséquent, concerner plusieurs colonnes de la table. Elles sont définies par le mot-clé CONSTRAINT.

Clé de table

PRIMARY KEY

mysql> CREATE TABLE Table_test
       ( Nom VARCHAR(100),
         Prenom VARCHAR(100),
         Date_de_naissance DATE,
         Ville VARCHAR(100),
         Nationalite VARCHAR(100),
         CONSTRAINT Identifieur PRIMARY KEY (Nom, Prenom, Date_de_naissance)
       );
mysql> desc Table_test;
+-------------------+--------------+------+-----+------------+-------+
 | Field | Type | Null | Key | Default | Extra | 
 | ----- | ---- | ---- | --- | ------- | ----- | 
+-------------------+--------------+------+-----+------------+-------+
 | Nom               | varchar(100) | NO  | PRI | NULL       |  | 
 | ---               | ------------ | --  | --- | ----       |  | 
 | Prenom            | varchar(100) | NO  | PRI | NULL       |  | 
 | Date_de_naissance | date         | NO  | PRI | 0000-00-00 |  | 
 | Ville             | varchar(100) | YES |     | NULL       |  | 
 | Nationalite       | varchar(100) | YES |     | NULL       |  | 
+-------------------+--------------+------+-----+------------+-------+
5 rows in set (0.00 sec)

Unicité de données

Mot-clé: UNIQUE

mysql>CREATE TABLE Joueurs
(
 JoueurID          INTEGER AUTO_INCREMENT NOT NULL,
 Numero            INTEGER NOT NULL,
 Prenom            VARCHAR(100) NOT NULL,
 Nom               VARCHAR(100) NOT NULL,
 Poste             VARCHAR(100) NOT NULL,
 Date_de_naissance DATE NOT NULL,
 Nationalite       VARCHAR(3) NOT NULL,
 ClubID            INTEGER NOT NULL,
 PRIMARY KEY (JoueurID),
 FOREIGN KEY (ClubID) REFERENCES Club,
 CONSTRAINT Numero_dans_club UNIQUE (Numero, ClubID)
);

Clés étrangères

Mot-clé: FOREIGN KEY

Modification d'une table

ALTER [IGNORE] TABLE tbl_name
    alter_specification [, alter_specification] ...

alter_specification:
    ADD [COLUMN] column_definition [FIRST | AFTER col_name ]
 | ADD [COLUMN] (column_definition,...)
 | ------------------------------------
 | ADD {INDEX                           | KEY} [index_name] [index_type] (index_col_name,...)
 | ADD [CONSTRAINT [symbol]]           
        PRIMARY KEY [index_type] (index_col_name,...)
 | ADD [CONSTRAINT [symbol]]
 | -------------------------
        UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...)
 | ADD [FULLTEXT             | SPATIAL] [INDEX | KEY] [index_name] (index_col_name,...)
 | -------------             | --------------- | --------------------------------------
 | ADD [CONSTRAINT [symbol]]
        FOREIGN KEY [index_name] (index_col_name,...)
        [reference_definition]
 | ALTER [COLUMN] col_name {SET DEFAULT literal   | DROP DEFAULT}
 | --------------------------------------------   | -------------
 | CHANGE [COLUMN] old_col_name column_definition
        [FIRST|AFTER col_name]
 | MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]
 | ---------------------------------------- | ---------------
 | DROP [COLUMN] col_name                  
 | DROP PRIMARY KEY                        
 | DROP {INDEX                              | KEY} index_name
 | DROP FOREIGN KEY fk_symbol              
 | DISABLE KEYS                            
 | ENABLE KEYS                             
 | RENAME [TO] new_tbl_name                
 | ORDER BY col_name                       
 | table_option ...                        
mysql> alter table Joueurs
    -> add column Pointure INTEGER DEFAULT 41 NOT NULL;
Query OK, 32 rows affected (0.03 sec)
Records: 32  Duplicates: 0  Warnings: 0
mysql> select Nom, Prenom, Pointure from Joueurs where numero < 10;
+-----------+-------------------+----------+
 | Nom | Prenom | Pointure | 
 | --- | ------ | -------- | 
+-----------+-------------------+----------+
 | Coupet    | Gregory           | 41 | 
 | ------    | -------           | -- | 
 | Muller    | Patrick           | 41 | 
 | Cris      | Cristiano Marques | 41 | 
 | Clerc     | Francois          | 41 | 
 | Cacapa    | Claudio Roberto   | 41 | 
 | Kallstrom | Kim               | 41 | 
 | Juninho   | Pernambucano      | 41 | 
 | Carew     | John              | 41 | 
 | Fabinho   |                   | 41 | 
 | Ciani     | Mickael           | 41 | 
+-----------+-------------------+----------+
10 rows in set (0.00 sec)
mysql> alter table Joueurs drop column Pointure;
Query OK, 32 rows affected (0.01 sec)
Records: 32  Duplicates: 0  Warnings: 0

Insertion de nouvelles données

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    VALUES ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]


INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    SET col_name={expr | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]


INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
mysql> insert Club (Nom) VALUES ("RC Strasbourg");
Query OK, 1 row affected (0.00 sec)
mysql> select * from Club where Nom="RC Strasbourg";
+--------+---------------+
 | ClubID | Nom | 
 | ------ | --- | 
+--------+---------------+
 | 21 | RC Strasbourg | 
 | -- | ------------- | 
+--------+---------------+
1 row in set (0.00 sec)

Modification de la valeur des données

Single-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

Multiple-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_condition]

Suppression de données

Single-table syntax:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

Multiple-table syntax:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]

Or:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*]] ...
    USING table_references
[WHERE where_condition]