SER802 - SQL, Champs, Moteurs et Jointures

• SER802 - SQL, Champs, Moteurs et Jointures
  • Contenu du Module
  • SQL
    • Chaînes de caractères
    • Nombres
      • Nombres Entiers
      • Nombres Décimaux
      • Nombres Négatifs
    • Valeurs NULL
    • Noms de Fichiers
    • Variables Utilisateurs
    • Commentaires
    • Commandes
      • SELECT
      • UPDATE
      • DELETE FROM
      • DROP TABLE
      • INSERT
      • ALTER
      • MATCH
    • Opérateurs
      • Mathématiques
      • Logiques
      • Comparaison
    • Fonctions
      • Mathématiques
      • Chaînes
      • Dates
      • Contrôle
      • Agrégation
      • Autres
  • Types de Champs
    • Nombres entiers
    • Nombres à virgule flottante
    • Dates et Heures
      • Types de données TIMESTAMP
    • Chaînes
    • TEXT et BLOB
    • ENUM et SET
  • Types de Moteurs de Stockage
  • Caractéristiques des Moteurs
    • InnoDB
      • Mécanisme Interne
      • Transactions
      • Tablespace
      • Multiversion Concurrency Control
      • Transaction Isolation Levels
    • MyISAM
      • MyISAM FIXED
      • MyISAM DYNAMIC
      • MyISAM COMPRESSED
      • Particularités
    • Memory
      • Particularités
      • En Pratique
    • Archive
      • Particularités
    • CSV
    • FEDERATED
    • NDB Cluster
    • Autres Moteurs Non Standards
      • XtraDB
      • Aria
  • Jointures
    • FULL JOIN
    • LEFT JOIN
    • RIGHT JOIN
  • LAB #1 - Le Langage SQL

Les chaînes de caractères doivent être entourées de ' ou de “.

Exemples

'Linux est incroyable'

“Windows est …….”

“Je lui dit : ”“Je t'aime””“

Il existe 3 types de nombres :

Une séquence de chiffres sans espaces

Exemple

999256

0

Utilisent le point comme séparateur.

Exemple

120.54

5566.8956e+12

Sont précédés par le signe -.

Exemple

-458

-147.36

Une chaîne sans données.

Les noms de bases, tables et colonnes :

• peuvent contenir jusqu'à 64 caractères
• peuvent commencer par un chiffre
• ne peuvent pas contenir que de chiffres
• ne peuvent pas contenir un /, une \ ou un point

Les alias :

• peuvent contenir jusqu'à 255 caractères
• peuvent contenir un /, une \ ou un point

Les variables n'ont pas besoin d'être initialisées. Elles sont automatiquement crées avec la valeur NULL.

Les commentaires d'une ligne sont précédés par le caractère # ou - -.

Les commentaires sous plusieurs lignes sont entourés comme suit :

/*
Ceci est
un commentaire
sur
plusieurs lignes
*/

Obtenir un ensemble de données à partir d'une ou de plusieurs tables.

Syntaxe

SELECT [table.][colonne]|expression [AS nom][,[table.[colonne]
FROM nom-table [, nom_table ...]
[WHERE condition [AND|OR condition ...]
[GROUP BY [table.][colonne],[table].[colonne] ...]
[ORDER BY [table.][colonne][Description],[table].[colonne] ...]

SELECT nom, prenom FROM familles ORDER BY nom

SELECT nom, prenom FROM familles GROUP BY ville

Mettre à jour des données dans une table.

UPDATE [LOW_PRIORITY][IGNORE] nom_table
SET colonne = expression
WHERE expression
ORDER BY expression
LIMIT valeur

UPDATE familles SET Adresse2='*******', nb_enfants=2;

UPDATE familles SET nb_enfants=8 ORDER BY Nom LIMIT 3;

Supprimer des enregistrements d'une table.

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM nom_table
       [WHERE where_definition]
       [ORDER BY ...]
       [LIMIT row_count]

DELETE FROM familles WHERE Nom = Durant;

DROP DATABASE [IF EXISTS] db_name

Supprimer une table d'une base de données.

DROP TABLE Test;

INSERT [LOW_PRIORITY] | [DELAYED] [IGNORE]
[INTO] nom_table [(nom_colonne ...)]
VALUES ((expression | DEFAULT) ...) , (...)

ou

INSERT [LOW_PRIORITY] | [DELAYED] [IGNORE]
[INTO] nom_table [(nom_colonne ...)]
SELECT ...

Insérer des valeurs dans une table.

INSERT INTO familles (Nom, Prenom) VALUES ('Smith', 'John');

Insérer des valeurs en provenance d'une autre table dans la table cible.

INSERT INTO familles (Prenom) SELECT enfants.prenom FROM enfants;

Ajouter, supprimer ou modifier une colonne, index ou nom de table.

ALTER TABLE familles ADD Sports VARCHAR(50) NOT NULL;

ALTER TABLE familles ADD INDEX (Sports);

Permet de modifier le nom ou le type d'une colonne.

ALTER TABLE familles CHANGE PrenomPere Prenom_Pere VARCHAR(45) NOT NULL;

ALTER TABLE familles DROP Sports;

Permet de désactiver la clef primaire momentanément.

ALTER TABLE familles DISABLE KEYS;

ALTER TABLE familles ENABLE KEYS;

Permet de renommer une table.

ALTER TABLE enfants RENAME familles_enfants;

Permet de reclasser physiquement une table.

ALTER TABLE familles ORDER BY Prenom_Pere;

Permet la recherche d'un mot, d'une phrase ou d'une expression sur un texte entier

SELECT * FROM familles WHERE MATCH (Commentaire) AGAINST ('fleuve');

Par exemple :

MariaDB [(none)]> select DATE_FORMAT(now(), '%W %d %M %Y');
+-----------------------------------+
| DATE_FORMAT(now(), '%W %d %M %Y') |
+-----------------------------------+
| Monday 07 November 2022           |
+-----------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]>

• TEXT,
  • TEXT respecte la casse des données,
• BLOB (Binary Large OBject),
  • BLOB permet de stocker toute information binaire telle une image ou un son.

• ENUM,
  • permet de prendre une valeur ou NULL parmi une liste de valeurs prédéfinies à la création de la colonne,
• SET
  • permet de prendre un maximum de 64 valeurs ou NULL parmi une liste de valeurs prédéfinies à la création de la colonne,

Le type de moteur est spécifié lors de la création de la table. Le moteur d'une table existant peut être modifié avec la commande ALTER TABLE. Les différents types principaux sont résumés ci-après :

Bien que possible techniquement, il n'est pas préférable d'utiliser des types de moteurs différents dans la même base de données car ceci complique la tâche d'optimisation, augmente le nombre de buffers et de caches et complexifie le travail de l'administrateur.

Le choix d'un moteur de stockage se fait en fonction de l'adéquation entre les besoins et les caractéristiques suivants :

Le moteur InnoDB est le moteur par défaut de MariaDB. Les principaux caractéristiques du moteur InnoDB sont :

• Il est transactionnel de type ACID (Atomicité, Cohérence, Isolation, Durabilité),
• Il implémente le MVCC (Multi Version Concurrency Control) qui permet d'avoir des lectures qui ne bloquent pas des écritures et inversement,
• Il implémente un verrou au niveau enregistrement,
• Il implémente un mécanisme de restauration automatique en utilisant des journaux de transactions ib_logfile* ou * est 0, 1, 2 etc,
• La structure de la table est stockée dans un fichier .frm,
• Il supporte l'utilisation de clefs étrangères,
• Il peut être sauvegardé à chaud,
• Il est recommandé pour des applications avec beaucoup d'écritures.

Les transactions en cours sont stockées en mémoire dans le buffer des journaux de transactions (log_buffer). La taille du buffer est fixée par la valeur de l'option innodb_log_buffer_size. Chaque seconde les informations dans le buffer sont écrites sur disque dans l'un des deux journaux des transactions ib_logfile0 ou ib_logfile1. Le nombre de journaux des transactions est fixé par la valeur de l'option innodb_log_files_in_group. La taille des logs ib_logfile*, aussi appelés le Redo log, est fixée par l'option innodb_log_file_size mais ne peut pas dépasser 512 Go. Le buffer est aussi écrit sur disque lors de chaque commit (validation) sauf si ce mécanisme a été modifié par la valeur de l'option innodb_flush_log_at_trx_commit.

Une partie des données ainsi que les index sont stockés en mémoire dans le buffer pool. La taille du buffer est fixée par la valeur de l'option innodb_buffer_pool_size. Il faut éviter que la taille de cette mémoire tampon dépasse les 80 % de la RAM du serveur. Chaque seconde, les informations dans le buffer sont écrites sur disque dans le tablespace. L'emplacement du tablespace est fixé par la valeur de l'option innodb_data_file_path.

Le moteur Innodb est transactionnel. Ceci implique que le moteur collectionne des requêtes jusqu'à la commande COMMIT. A ce moment le moteur applique les modifications à toutes les tables concernées en même temps. Soit toutes les modifications réussissent, soit il y a un ROLLBACK. Par défaut, MariaDB utilise un autocommit avec le moteur Innodb. Ceci implique qu'à la fin de chaque requête SQL, le serveur voit un commit virtuel.

Il est possible de modifier ce comportement automatique en utilisant la commande BEGIN. Par exemple :

mysql> BEGIN;
mysql> INSERT INTO table (colonne) VALUES (value);
mysql> COMMIT;

Si la dernière commande est remplacée par la commande ROLLBACK la modification apportée par la requête INSERT INTO sera effacée :

mysql> BEGIN;
mysql> INSERT INTO table (colonne) VALUES (value);
mysql> ROLLBACK;

Depuis la version 5.6.6 de MySQL, il existe l'option innodb_file_per_table. Si cette option est 0, Innodb se comporte comme les versions précédentes, à savoir les données et les index de toutes les tables sont centralisées dans la Tablespace, c'est-à-dire, dans le fichier ibdata par défaut. Avec une valeur de 1, les données et index de chaque table sont stockées dans un fichier individuel ayant une extension .ibd se trouvant dans le répertoire du schéma à côté du fichier de définition de la structure, le .frm.

Cette modification permet le stockage des fichiers .ibd sur un autre espace de stockage physique. De cette façon, il est possible d'utiliser des espaces de stockage très rapides.

Malgré l'utilisation des fichiers .ibd, il est toujours necéssaire d'avoir le fichier ibdata car ce dernier stocke le dictionnaire des données qui est une copie de tous les fichiers .frm ainsi que les Redo logs. Les Redo logs journalisent toute requête de modification des objets.

Chaque table Innodb contient deux colonnes cachées :

• le numéro de transaction - txn#,
• un pointeur vers la version précédente de la ligne qui se trouve dans le Undo log. Ce pointeur s'appelle le Pointeur ROLLBACK.

Quand une ligne est modifiée, l'ancienne version de la ligne y compris son numéro de transaction est copiée dans le Undo log. La nouvelle version de la ligne y compris son numéro de transaction est copiée dans la Tablespace ou dans le fichier .ibd. Le pointeur de la nouvelle ligne identifie l'ancienne ligne dans le Undo log. Ce processus s'appelle le MVCC (Multiversion Concurrency Control).

Créez la base de données nombres :

MariaDB [none]> CREATE DATABASE `Nombres` ;
Query OK, 1 row affected (0.00 sec)

MariaDB [none]> USE Nombres;
Database changed

MariaDB [Nombres]> CREATE TABLE english (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, Number VARCHAR(10));
Query OK, 0 rows affected (0.02 sec)

MariaDB [Nombres]> INSERT INTO english (Number) VALUES ('One');
Query OK, 1 row affected (0.01 sec)

MariaDB [Nombres]> INSERT INTO english (Number) VALUES ('Two');
Query OK, 1 row affected (0.01 sec)

MariaDB [Nombres]> INSERT INTO english (Number) VALUES ('Three');
Query OK, 1 row affected (0.01 sec)

MariaDB [Nombres]> SELECT * FROM english;
+----+--------+
| id | Number |
+----+--------+
|  1 | One    |
|  2 | Two    |
|  3 | Three  |
+----+--------+
3 rows in set (0.000 sec)

MariaDB [Nombres]>

Mettez à jour l'enregistrement 1 sans faire de COMMIT ou de ROLLBACK :

MariaDB [Nombres]> BEGIN;
Query OK, 0 rows affected (0.000 sec)

MariaDB [Nombres]> UPDATE english SET Number = "Un" WHERE id = 1;
Query OK, 1 row affected (0.000 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [Nombres]> SELECT * FROM english;
+----+--------+
| id | Number |
+----+--------+
|  1 | Un     |
|  2 | Two    |
|  3 | Three  |
+----+--------+
3 rows in set (0.000 sec)

MariaDB [Nombres]>  

Ouvrez une autre terminal, connectez-vous à mysql et visualisez la modification :

[root@centos8 ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 17
Server version: 10.3.28-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| Nombres            |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)

MariaDB [(none)]> use Nombres;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [Nombres]> SELECT * FROM english;
+----+--------+
| id | Number |
+----+--------+
|  1 | One    |
|  2 | Two    |
|  3 | Three  |
+----+--------+
3 rows in set (0.000 sec)

MariaDB [Nombres]>  

Pour voir la valeur dans la Tablespace, il faut modifier la valeur de l'option TRANSACTION ISOLATION LEVEL :

MariaDB [Nombres]> SET TRANSACTION ISOLATION LEVEL read uncommitted;
Query OK, 0 rows affected (0.000 sec)

MariaDB [Nombres]> SELECT * FROM english;
+----+--------+
| id | Number |
+----+--------+
|  1 | Un     |
|  2 | Two    |
|  3 | Three  |
+----+--------+
3 rows in set (0.000 sec)

MariaDB [Nombres]>  

Dans ce mode de fonctionnement InnoDB suit les Pointeurs ROLLBACK jusqu'à il trouve la dernière version ayant subi un COMMIT :

MariaDB [Nombres]> SET TRANSACTION ISOLATION LEVEL read committed;
Query OK, 0 rows affected (0.000 sec)

MariaDB [Nombres]> SELECT * FROM english;
+----+--------+
| id | Number |
+----+--------+
|  1 | One    |
|  2 | Two    |
|  3 | Three  |
+----+--------+
3 rows in set (0.000 sec)

MariaDB [Nombres]>

Retournez au premier terminal :

MariaDB [Nombres]> COMMIT;
Query OK, 0 rows affected (0.000 sec)

MariaDB [Nombres]> 

Retournez au deuxième terminal :

MariaDB [Nombres]> SELECT * FROM english;
+----+--------+
| id | Number |
+----+--------+
|  1 | Un     |
|  2 | Two    |
|  3 | Three  |
+----+--------+
3 rows in set (0.000 sec)

MariaDB [Nombres]>

L'architecture du moteur MyISAM est plus simple que le moteur InnoDB. Les principaux caractéristiques du moteur MyISAM sont :

• Il n'est pas transactionnel,
• Il implémente un verrou au niveau table,
• Il n'y a pas de recouvrement automatique des données lors d'un crash,
• La structure de la table est stockée dans un fichier .frm,
• Les données sont stockées dans un fichier .MYD tandis que les index sont stockés dans un fichier .MYI,
• Il ne supporte pas de clefs étrangères,
• Il ne peut pas être sauvegardé à chaud,
• Il peut être compressé,
• Il supporte les index plain text (fulltext),
• Il est recommandé pour des applications avec beaucoup de lectures.

Il existe trois types de moteurs MyISAM : FIXED , DYNAMIC et COMPRESSED :

Une table est FIXED si elle ne contient aucun champ de type VARCHAR, VARBINARY, TEXT ou BLOB ou si la valeur de l'option row_format est FIXED ( row_format=FIXED ). Tous les enregistrements sont de la même taille. De ce fait, les tables sont plus rapides et plus robustes que les tables dynamiques mais elles prennent plus d'espace disque. Les données de la table ne peuvent pas être fragmentées. Une table devient FIXED quand la valeur de l'option row_format est FIXED ( row_format=FIXED ) :

MariaDB [Nombres]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> SHOW TABLE STATUS LIKE 'db' \G;
*************************** 1. row ***************************
            Name: db
          Engine: MyISAM
         Version: 10
      Row_format: Fixed
            Rows: 0
  Avg_row_length: 0
     Data_length: 0
 Max_data_length: 178173660257845247
    Index_length: 4096
       Data_free: 0
  Auto_increment: NULL
     Create_time: 2022-11-07 06:00:13
     Update_time: 2022-11-07 06:00:13
      Check_time: NULL
       Collation: utf8_bin
        Checksum: NULL
  Create_options: 
         Comment: Database privileges
Max_index_length: 1125899906841600
       Temporary: N
1 row in set (0.000 sec)

ERROR: No query specified

MariaDB [mysql]> 

Une table est DYNAMIC si elle contient au moins un champ de type VARCHAR, VARBINARY, TEXT ou BLOB ou la valeur de l'option row_format est DYNAMIC ( row_format=DYNAMIC ). Les données de la table peuvent être fragmentées. Dans ce cas, l'utilisation de la commande OPTIMIZE TABLE ou myisamchk est nécessaire :

MariaDB [mysql]> SHOW TABLE STATUS LIKE 'proc' \G;
*************************** 1. row ***************************
            Name: proc
          Engine: MyISAM
         Version: 10
      Row_format: Dynamic
            Rows: 2
  Avg_row_length: 566
     Data_length: 1132
 Max_data_length: 281474976710655
    Index_length: 4096
       Data_free: 0
  Auto_increment: NULL
     Create_time: 2022-11-07 06:00:48
     Update_time: 2022-11-07 06:02:06
      Check_time: NULL
       Collation: utf8_general_ci
        Checksum: NULL
  Create_options: 
         Comment: Stored Procedures
Max_index_length: 288230376151710720
       Temporary: N
1 row in set (0.000 sec)

ERROR: No query specified

Après avoir arrêté le serveur, la compression est obtenue en utilisant la commande myisampack suivi de la commande myisamchk -rq. Le décompression est obtenue en utilisant la commande myisamchk –unpack. La compression peut atteindre les 70% maximum. Une fois compressée les requêtes UPDATE, DELETE et INSERT ne peuvent pas être utilisées :

[root@centos8 ~]# systemctl stop mariadb

[root@centos8 ~]# myisampack /var/lib/mysql/mysql/help_keyword.MYI
Compressing /var/lib/mysql/mysql/help_keyword.MYD: (464 records)
- Calculating statistics
- Compressing file
95.2%     
Remember to run myisamchk -rq on compressed tables

[root@centos8 ~]# myisamchk -rq /var/lib/mysql/mysql/help_keyword.MYI
- check record delete-chain
- recovering (with sort) MyISAM-table '/var/lib/mysql/mysql/help_keyword.MYI'
Data records: 464
- Fixing index 1
- Fixing index 2

[root@centos8 ~]# myisamchk --unpack /var/lib/mysql/mysql/help_keyword.MYI
- recovering (with sort) MyISAM-table '/var/lib/mysql/mysql/help_keyword.MYI'
Data records: 464
- Fixing index 1
- Fixing index 2

• Le moteur MyISAM est particulièrement portable car il suffit de copier les fichiers *.frm, *.MYD et *.MYI pour obtenir une sauvegarde physique,
• Le nombre d'enregistrements est stocké dans la table,
• Les index non-uniques peuvent être désactivés momentanément,
• Il est possible d'avoir des INSERT et SELECT simultanés en utilisant l'option concurrent_insert.

Les principaux caractéristiques du moteur Memory sont :

• Il n'est pas transactionnel,
• Il implémente un verrou au niveau table,
• Il n'y a pas de recouvrement automatique des données lors d'un crash,
• La structure de la table est stockée dans un fichier .frm,
• Les données sont stockées dans la mémoire,
• Il ne supporte pas de clefs étrangères,
• Il ne peut pas être sauvegardé à chaud,
• Il est recommandé pour des applications ayant besoin de la vitesse.

• La consommation de la mémoire est fixée par les options MAX_ROWS et max_heap_table_size,
• Le moteur peut implémenter soit l'algorithme HASH pour les index pour augmenter la performance pour les recherches d'égalité soit l'algorithme BTREE pour les index pour augmenter la performance pour les recherches d'inégalité,
• Au démarrage du serveur les tables sont évidement vides. Il est possible de les pré-remplir grâce à des instructions SQL contenues dans un fichier référencé par l'option init-file.

Créez une table au format MEMORY en utilisant les données de la tables famille issue de la base carnetadresses :

mysql> CREATE TABLE tempfamilles TYPE=MEMORY SELECT * FROM familles ;

Visualisez les enregistrements de la table tempfamilles :

mysql> SELECT * FROM tempfamilles;

Supprimez la table tempfamilles :

mysql > DROP TABLE tempfamilles;

Les principaux caractéristiques du moteur Archive sont :

• Il n'est pas transactionnel,
• Il implémente un verrou au niveau enregistrement,
• Il n'y a pas de recouvrement automatique des données lors d'un crash,
• Les données sont stockées en les compressant,
• Il ne supporte pas de clefs étrangères,
• Il ne peut pas être sauvegardé à chaud,
• Il ne supporte pas d'Index,
• Il est recommandé pour réduire l'espace disque utilisé par les tables.

• Le moteur ne permet que des requêtes INSERT et SELECT.

Les principaux caractéristiques du moteur CSV sont :

• Il n'est pas transactionnel,
• Il implémente un verrou au niveau table,
• Il n'y a pas de recouvrement automatique des données lors d'un crash,
• La structure de la table est stockée dans un fichier .frm,
• Les données sont séparées par des ; et stockées dans un fichier .CSV tandis que les informations concernant l'état de la table ainsi que le nombre d'enregistrements sont stockées dans un fichier .CSM,
• Il ne supporte pas de clefs étrangères,
• Il ne peut pas être sauvegardé à chaud,
• Il ne supporte pas d'Index,
• Il est recommandé pour des tables ayant besoin d'être manipulées par des logiciels tiers.

Les principaux caractéristiques du moteur FEDERATED sont :

• Il n'est pas transactionnel,
• Il n'y a pas de recouvrement automatique des données lors d'un crash,
• Il ne supporte pas de clefs étrangères,
• Il ne peut pas être sauvegardé à chaud,
• Il ne supporte pas d'Index,
• Il supporte des requêtes SELECT, INSERT, UPDATE, DELETE, TRUNCATE et DROP TABLE,
• Il n'utilise pas le cache de requêtes,
• Il est utilisé pour accéder à des données localisées sur un serveur MariaDB distant.

Le moteur NDB Cluster est le moteur de MariaDB Cluster. Les principaux caractéristiques du moteur NDB Cluster sont :

• Il est transactionnel de type ACID (Atomicité, Cohérence, Isolation, Durabilité),
• Il implémente le MVCC (Multi Version Concurrency Control) qui permet d'avoir des lectures qui ne bloquent pas des écritures et inversement,
• Il implémente un verrou au niveau enregistrement,
• Il implémente la réplication synchrone,
• Il implémente le basculement automatique sur un autre nœud en cas de panne et la synchronisation automatique du nœud à son démarrage,
• Il peut être sauvegardé à chaud,
• Il permet le dimensionnement des requêtes de lecture et d'écriture,
• Il utilise l'architecture Shared Nothing où les nœuds ne partagent pas un disque de données,
• Il implémente l’absence du SPOF (Single Point Of Failure).

XtraDB est le moteur de la société Percona. Il est basé sur InnoDB et a pour but d'être plus performant. Les principaux caractéristiques du moteur XtraDB sont :

• Il est transactionnel de type ACID (Atomicité, Cohérence, Isolation, Durabilité),
• Il implémente le MVCC (Multi Version Concurrency Control) qui permet d'avoir des lectures qui ne bloquent pas des écritures et inversement,
• Il implémente un verrou au niveau enregistrement,
• Il implémente un mécanisme de restauration automatique,
• Il supporte l'utilisation de clefs étrangères,
• Il peut être sauvegarder à chaud,
• Le moteur peut implémenter soit l'algorithme HASH pour les index pour augmenter la performance pour les recherches d'égalité soit l'algorithme B+TREE pour les index pour augmenter la performance pour les recherches d'inégalité.

Aria est le moteur de Michael Widenius. Il a été créé pour être un alternatif au moteur MyISAM. Les principaux caractéristiques du moteur Aria sont :

• En version 2.0, il est transactionnel de type ACID (Atomicité, Cohérence, Isolation, Durabilité),
• Il implémente un verrou au niveau enregistrement,
• Il implémente un mécanisme de restauration automatique.

Les jointures permettent de créer un lien entre deux tables. Le champ utilisé pour la jointure dans la première table est souvent la clef primaire. Le champs utilisé dans la deuxième table est appelé le clef étrangère. La clef primaire et la clef étrangère doivent être du même type.

Dans le cas où on utilise un champs autre que la clef primaire pour assurer la jointure, cet autre champs doit être indexé.

Ce type de jointure permet de renvoyer uniquement les enregistrements des deux tables 1 et 2 ayant une correspondance :

mysql> SELECT table1.champ1, table2.champ1 FROM table1, table2 WHERE table1.clefprimaire = table2.clefetrangere;

ou

mysql> SELECT table1.champ1, table2.champ1 FROM table1 JOIN table2 WHERE table1.clefprimaire = table2.clefetrangere;

Ce type de jointure permet de renvoyer les enregistrements de la première table qui ont une correspondance dans la deuxième table. La syntaxe est la suivante :

mysql> SELECT table1.champ1, table2.champ1 FROM table1 LEFT JOIN table2 ON table1.clefprimaire = table2.clefetrangere;

Le résultat de cette requête est l'affichage de tous les enregistrements de la table 1 avec ou sans les enregistrements correspondants de la table 2. Dans le cas où la table 2 ne contient pas d'enregistrement correspondant un un enregistrement de la table 1, la valeur retournée est NULL. Cette information nous permet de rechercher uniquement les enregistrements dans la table1 n'ayant pas d'enregistrements dans la table 2 :

mysql> SELECT table1.champ1, table2.champ1 FROM table1 LEFT JOIN table2 ON table1.clefprimaire = table2.clefetrangere WHERE table2.clefetrangere IS NULL;

Ce type de jointure est l'inverse d'un LEFT JOIN.

Créez maintenant la base de données CarnetAdresses :

mysql> CREATE DATABASE `CarnetAdresses` ;
Query OK, 1 row affected (0.00 sec)

mysql> 

Créez ensuite deux tables familles et enfants dans la base CarnetAdresses :

MariaDB [(none)]> use CarnetAdresses;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [CarnetAdresses]>

MariaDB [CarnetAdresses]> CREATE TABLE familles (CodeFamille BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, Nom VARCHAR(40), PrenomPere VARCHAR(40) , PrenomMere VARCHAR(40) , Adresse1 VARCHAR(40) , Adresse2 VARCHAR(40), CodePostal VARCHAR(5), Ville VARCHAR(40), ProfPere VARCHAR(40), ProfMere VARCHAR(40));
Query OK, 0 rows affected (0.03 sec)

MariaDB [CarnetAdresses]> 

Pour plus de facilité, copiez simplement la requête et collez-la dans votre terminal :

familles

CREATE TABLE familles (CodeFamille BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, Nom VARCHAR(40), PrenomPere VARCHAR(40) , PrenomMere VARCHAR(40) , Adresse1 VARCHAR(40) , Adresse2 VARCHAR(40), CodePostal VARCHAR(5), Ville VARCHAR(40), ProfPere VARCHAR(40), ProfMere VARCHAR(40));

MariaDB [CarnetAdresses]> CREATE TABLE Enfants ( CodeFamille BIGINT NOT NULL, Prenom VARCHAR(40) NOT NULL, Sexe VARCHAR(1) NOT NULL, DateNaissance DATE NOT NULL, PRIMARY KEY (CodeFamille));
Query OK, 0 rows affected (0.02 sec)

MariaDB [CarnetAdresses]>

Pour plus de facilité, copiez simplement la requête et collez-la dans votre terminal :

enfants

CREATE TABLE Enfants ( CodeFamille BIGINT NOT NULL, Prenom VARCHAR(40) NOT NULL, Sexe VARCHAR(1) NOT NULL, DateNaissance DATE NOT NULL, PRIMARY KEY (CodeFamille));

Utilisez ensuite la commande SHOW pour visualiser le résultat de chaque instruction, par exemple :

MariaDB [CarnetAdresses]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| CarnetAdresses     |
| Nombres            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)

MariaDB [CarnetAdresses]>

MariaDB [CarnetAdresses]> SHOW TABLES FROM CarnetAdresses;
+--------------------------+
| Tables_in_CarnetAdresses |
+--------------------------+
| Enfants                  |
| familles                 |
+--------------------------+
2 rows in set (0.00 sec)

MariaDB [CarnetAdresses]> 

MariaDB [CarnetAdresses]> SHOW COLUMNS FROM familles FROM CarnetAdresses;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| CodeFamille | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| Nom         | varchar(40) | YES  |     | NULL    |                |
| PrenomPere  | varchar(40) | YES  |     | NULL    |                |
| PrenomMere  | varchar(40) | YES  |     | NULL    |                |
| Adresse1    | varchar(40) | YES  |     | NULL    |                |
| Adresse2    | varchar(40) | YES  |     | NULL    |                |
| CodePostal  | varchar(5)  | YES  |     | NULL    |                |
| Ville       | varchar(40) | YES  |     | NULL    |                |
| ProfPere    | varchar(40) | YES  |     | NULL    |                |
| ProfMere    | varchar(40) | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)

MariaDB [CarnetAdresses]> 

MariaDB [CarnetAdresses]> SHOW INDEX FROM familles;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| familles |          0 | PRIMARY  |            1 | CodeFamille | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

MariaDB [CarnetAdresses]> 

MariaDB [CarnetAdresses]> SHOW TABLE STATUS FROM CarnetAdresses;
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| Name     | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation         | Checksum | Create_options | Comment |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| Enfants  | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |            0 |   9437184 |           NULL | 2017-10-30 15:17:13 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
| familles | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |            0 |   9437184 |              1 | 2017-10-30 15:15:49 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
2 rows in set (0.00 sec)

MariaDB [CarnetAdresses]> 

Créez les enregistrements dans la table familles :

data

INSERT INTO familles (Nom , PrenomPere , PrenomMere , Adresse1 , Adresse2 , CodePostal , Ville , ProfPere , ProfMere) VALUES ('Durant', 'Jacques', 'Jane', '23 rue Dutor','','92200', 'Neuilly', 'Plombier',''); INSERT INTO familles (Nom , PrenomPere , PrenomMere , Adresse1 , Adresse2 , CodePostal , Ville , ProfPere , ProfMere) VALUES ('Tortua', 'Benoît', 'Carole', '7 rue Verget','', '75005', 'Paris', 'Cadre', 'Cadre'); INSERT INTO familles (Nom , PrenomPere , PrenomMere , Adresse1 , Adresse2 , CodePostal , Ville , ProfPere , ProfMere) VALUES ('Portier', 'Pierre', 'Elisabeth', '5 rue Toulet','', '92200', 'Neuilly', 'Dentiste',''); INSERT INTO familles (Nom , PrenomPere , PrenomMere , Adresse1 , Adresse2 , CodePostal , Ville , ProfPere , ProfMere) VALUES ('Renault', 'Damien', 'Anne', '2 rue Ragon','', '75007', 'Paris', 'Comptable', 'Enseignante'); INSERT INTO familles (Nom , PrenomPere , PrenomMere , Adresse1 , Adresse2 , CodePostal , Ville , ProfPere , ProfMere) VALUES ('Darduet', 'Jean','','','','','','',''); INSERT INTO familles (Nom , PrenomPere , PrenomMere , Adresse1 , Adresse2 , CodePostal , Ville , ProfPere , ProfMere) VALUES ('Rodier', 'Gérard', 'Aurélie', '6 rue Agien','', '77000', 'Fontainebleau', 'Professeur',''); INSERT INTO familles (Nom , PrenomPere , PrenomMere , Adresse1 , Adresse2 , CodePostal , Ville , ProfPere , ProfMere) VALUES ('Tarte', 'Alla', 'Crème', '1 allée Durond','', '92200', 'Neuilly', 'Cuisinier', 'Cuisinière'); INSERT INTO familles (Nom , PrenomPere , PrenomMere , Adresse1 , Adresse2 , CodePostal , Ville , ProfPere , ProfMere) VALUES ('Cohen', 'David', 'Sarah', '7 Av d\'Eylau','', '75016', 'Paris', 'PDG', 'Assistante Direction'); INSERT INTO familles (Nom , PrenomPere , PrenomMere , Adresse1 , Adresse2 , CodePostal , Ville , ProfPere , ProfMere) VALUES ('Dupont2', 'Bruno', 'Odile', '12 rue Sébastien','', '75008', 'PARIS', 'Electricien', 'Comptable'); INSERT INTO familles (Nom , PrenomPere , PrenomMere , Adresse1 , Adresse2 , CodePostal , Ville , ProfPere , ProfMere) VALUES ('Durand2', 'Jacques', 'Jane', '23 rue Dutor','', '92200', 'Neuilly', 'Plombier',''); INSERT INTO familles (Nom , PrenomPere , PrenomMere , Adresse1 , Adresse2 , CodePostal , Ville , ProfPere , ProfMere) VALUES ('Darmon2', 'Bruno', 'Béatrice', '2 rue Nicolo','', '13008', 'Marseille', 'Cadre', 'Peintre'); INSERT INTO familles (Nom , PrenomPere , PrenomMere , Adresse1 , Adresse2 , CodePostal , Ville , ProfPere , ProfMere) VALUES ('Darduet2', 'Jean', '','' ,'' ,'' ,'','',''); INSERT INTO familles (Nom , PrenomPere , PrenomMere , Adresse1 , Adresse2 , CodePostal , Ville , ProfPere , ProfMere) VALUES ('Tarte2', 'Alla', 'Crème', '1 allée Durond','', '92200', 'Neuilly', 'Cuisinier', 'Cuisinière'); INSERT INTO familles (Nom , PrenomPere , PrenomMere , Adresse1 , Adresse2 , CodePostal , Ville , ProfPere , ProfMere) VALUES ('Cohen2', 'David', 'Sarah', '7 Av d\'Eylau','', '75016', 'Paris', 'PDG', 'Assistante Direction');

MariaDB [CarnetAdresses]> INSERT INTO familles (Nom , PrenomPere , PrenomMere , Adresse1 , Adresse2 , CodePostal , Ville , ProfPere , ProfMere) VALUES ('Durant', 'Jacques', 'Jane', '23 rue Dutor','','92200', 'Neuilly', 'Plombier',''); INSERT INTO familles (Nom , PrenomPere , PrenomMere , Adresse1 , Adresse2 , CodePostal , Ville , ProfPere , ProfMere) VALUES ('Tortua', 'Benoît', 'Carole', '7 rue Verget','', '75005', 'Paris', 'Cadre', 'Cadre'); INSERT INTO familles (Nom , PrenomPere , PrenomMere , Adresse1 , Adresse2 , CodePostal , Ville , ProfPere , ProfMere) VALUES ('Portier', 'Pierre', 'Elisabeth', '5 rue Toulet','', '92200', 'Neuilly', 'Dentiste',''); INSERT INTO familles (Nom , PrenomPere , PrenomMere , Adresse1 , Adresse2 , CodePostal , Ville , ProfPere , ProfMere) VALUES ('Renault', 'Damien', 'Anne', '2 rue Ragon','', '75007', 'Paris', 'Comptable', 'Enseignante'); INSERT INTO familles (Nom , PrenomPere , PrenomMere , Adresse1 , Adresse2 , CodePostal , Ville , ProfPere , ProfMere) VALUES ('Darduet', 'Jean','','','','','','',''); INSERT INTO familles (Nom , PrenomPere , PrenomMere , Adresse1 , Adresse2 , CodePostal , Ville , ProfPere , ProfMere) VALUES ('Rodier', 'Gérard', 'Aurélie', '6 rue Agien','', '77000', 'Fontainebleau', 'Professeur',''); INSERT INTO familles (Nom , PrenomPere , PrenomMere , Adresse1 , Adresse2 , CodePostal , Ville , ProfPere , ProfMere) VALUES ('Tarte', 'Alla', 'Crème', '1 allée Durond','', '92200', 'Neuilly', 'Cuisinier', 'Cuisinière'); INSERT INTO familles (Nom , PrenomPere , PrenomMere , Adresse1 , Adresse2 , CodePostal , Ville , ProfPere , ProfMere) VALUES ('Cohen', 'David', 'Sarah', '7 Av d\'Eylau','', '75016', 'Paris', 'PDG', 'Assistante Direction'); INSERT INTO familles (Nom , PrenomPere , PrenomMere , Adresse1 , Adresse2 , CodePostal , Ville , ProfPere , ProfMere) VALUES ('Dupont2', 'Bruno', 'Odile', '12 rue Sébastien','', '75008', 'PARIS', 'Electricien', 'Comptable'); INSERT INTO familles (Nom , PrenomPere , PrenomMere , Adresse1 , Adresse2 , CodePostal , Ville , ProfPere , ProfMere) VALUES ('Durand2', 'Jacques', 'Jane', '23 rue Dutor','', '92200', 'Neuilly', 'Plombier',''); INSERT INTO familles (Nom , PrenomPere , PrenomMere , Adresse1 , Adresse2 , CodePostal , Ville , ProfPere , ProfMere) VALUES ('Darmon2', 'Bruno', 'Béatrice', '2 rue Nicolo','', '13008', 'Marseille', 'Cadre', 'Peintre'); INSERT INTO familles (Nom , PrenomPere , PrenomMere , Adresse1 , Adresse2 , CodePostal , Ville , ProfPere , ProfMere) VALUES ('Darduet2', 'Jean', '','' ,'' ,'' ,'','',''); INSERT INTO familles (Nom , PrenomPere , PrenomMere , Adresse1 , Adresse2 , CodePostal , Ville , ProfPere , ProfMere) VALUES ('Tarte2', 'Alla', 'Crème', '1 allée Durond','', '92200', 'Neuilly', 'Cuisinier', 'Cuisinière'); INSERT INTO familles (Nom , PrenomPere , PrenomMere , Adresse1 , Adresse2 , CodePostal , Ville , ProfPere , ProfMere) VALUES ('Cohen2', 'David', 'Sarah', '7 Av d\'Eylau','', '75016', 'Paris', 'PDG', 'Assistante Direction');
Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

MariaDB [CarnetAdresses]>

Saisissez maintenant chacune des commandes suivantes et expliquez le résultat obtenu. Les instructions sont numérotées pour faciliter vos prises de notes.

Copyright © 2021 Hugh Norris.