Clé primaire

La clé primaire concourt à identifier uniquement chaque ligne d’une table. Elle peut représenter une partie d’un enregistrement concret, ou être un champ artificiel (un champ qui n’a rien à voir avec l’enregistrement réel). La clé primaire peut représenter un ou plusieurs champs d’une table. Lorsque la clé primaire représente plusieurs champs, elle est appelée clé composite

Clé étrangère

La clé étrangère représente un champ (ou des champs) qui pointe vers la clé primaire d’une autre table. L’objectif de la clé étrangère est d’assurer l’intégrité référentielle des données. En d’autres mots, seules les valeurs devant apparaître dans la base de données sont permises.

La création d'une table utilise le mot clé create table. On définira ensuite le nom ainsi que les colonnes avec leur type et les éventuelles contraintes.

Par exemple, voici une version minimale pour la création de la table Auteur des exercices précédents avec ses trois colonnes.

CREATE TABLE `Auteur` (
  `id` int(11),
  `Nom` varchar(200),
  `LieuNaissance` varchar(200));

Ici, la version complète avec les éléments supplémentaires (auto incrémentation, encodage des données, clés primaire et étrangères, contraintes).

CREATE TABLE IF NOT EXISTS `Auteur` (-- On créé la table auteur seulement si elle n'existe pas
  `id` int(11) NOT NULL AUTO_INCREMENT, -- Colonne avec un entier disposant d'incrémentation automatique
  `Nom` varchar(200) COLLATE utf8_bin NOT NULL,  -- Colonne avec une chaîne de caractères,
                                                 -- la taille et l'encodage associé en contrainte
  `LieuNaissance` varchar(200) COLLATE utf8_bin DEFAULT NULL, -- valeur par défaut à null
  PRIMARY KEY (`id`), -- définition de la clé primaire
  Foreign Key (`LieuNaissance`) references `Lieux`(`Ville`) -- définitions de la clé étrangère
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ; -- précisions du
                                                                         -- comportement de la table

Types de données

MySQL supporte différents types de colonnes, quelques uns seront cités ici comme exmple.

Types numériques

smallint

Type numérique sur un octet pour un entier compris entre -128 et 127

smallint

Type numérique sur 2 octets pour un entier compris entre -32768 et 32767

int ou integer

Type numérique sur 4 octets pour un entier compris entre -2 147 483 648 et 2 147 483 647

float

Type numérique flottant sur 4 octets

double

Type numérique flottant sur 8 octets

decimal(P,E) ou numeric(P,E)

Type numérique exacte permettant d'indiquer la précision P et l'échelle E

La précision est le nombre de chiffres stockés, l'échelle le nombre de chiffres après la virgules

salaire DECIMAL(6,2) permettra ainsi de stocker des nombres flottans entre -9999,99 et 9999,99

Types chaînes

char (M)

Chaîne de M caractères. Occupera toujours M octets en base, quelque soit le contenu

varchar (M)

Chaîne de M caractères maximum. Occupera longueur de la chaîne + 1 octets

blob

Type permettant de stocker un objet binaire de longueur variable. Il ex exite quatre types qui ne diffèrent que par la taille

tinyblob 255 caractères maximum

blob 65 535 caractères maximum

mediumblob 16 777 215 caractères maximum

longblob 4 294 967 295 caractères maximum

Types temporels

date

Occupe 3 octets : AAAA-MM-JJ

datetime

Occupe 8 octets : AAAA-MM-JJ HH:MM:SS

time

Occupe 3 octets : HH:MM:SS

timestamp

Occupe 4 octets : temps unix en secondes

Pour obtenir les différentes caractéristiques d'une table, on utilisera le mot clé describe. On y retrouve une partie des inf

mysql> DESCRIBE `Auteur`;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| id            | int(11)      | NO   | PRI | NULL    | auto_increment |
| Nom           | varchar(200) | NO   | MUL | NULL    |                |
| LieuNaissance | varchar(200) | YES  | MUL | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

La suppression d'une table se fait avec le mot clé drop table. Si des clés étrangères d'une autre table pointent vers la table à supprimer, une exception pour violation de contrainte est levée (il faudra donc supprimer la contrainte au préalable).

DROP TABLE `Auteur`;

ALTER TABLE `Auteur`

Ajouter une colonne

ADD "colonne" "type de données"

Supprimer une colonne

DROP "colonne"

Changer un nom de colonne

CHANGE "vieux nom" "nouveau nom" "type de données"

Changer le type de données d’une colonne

MODIFY "colonne" "nouveau type de données"

Les vues sont des tables virtuelles créées à partir de requêtes, qui ne stockent aucune donnée.

Elles permettent de masquer une partie de la complexité du modèle relationnel.

CREATE VIEW V_VIEW AS SELECT

Insertion simple

INSERT INTO 'Lieux' ('Ville', 'Pays') VALUES
('Florida', 'Royaume-Uni');

A partir d'autres tables

INSERT INTO 'table1' ('column1', 'column2')
SELECT 'column3', 'column4'
FROM 'table2';

UPDATE 'table'
SET colonne1 = [valeur1], colonne2 = [valeur2]
WHERE {condition}

DELETE FROM 'table'
WHERE {condition}