SQLServer

Un guide d'introduction à SQL

Aperçu le 22 mai 2023 sur https://www.prnewswire.com/ (opens in a new tab)

Introduction

Les bases de données relationnelles et le langage de requête SQL sont des outils essentiels dans le contexte des entreprises Internet depuis le début de la révolution numérique. Malgré l'émergence des technologies Big Data et NoSQL, SQL reste pertinent et constitue un atout majeur pour une carrière dans le domaine de la technologie. En effet, la plupart des applications importantes utilisent des bases de données relationnelles, et SQL est la norme pour interagir avec celles-ci. De plus, la connaissance de SQL s'avère également utile dans le domaine du Big Data, car de nombreux systèmes logiciels utilisent une syntaxe SQL pour interagir avec les données.

Relationnel vs non relationnel

Données

Les données sont des éléments distincts d'informations pouvant prendre différentes formes telles que des nombres, du texte, des images ou des vidéos. Elles peuvent être stockées et transmises électroniquement. La signification des données dépend généralement du contexte dans lequel elles sont utilisées. On distingue principalement trois catégories de données :

Données structurées Les données structurées ont une organisation prédéfinie et sont facilement consultables et analysables. Elles sont soutenues par un modèle qui dicte la taille de chaque champ de données, son type, sa longueur et les restrictions sur les valeurs qu'il peut prendre. Les bases de données SQL stockent des données structurées, et la plupart des outils SQL sont utilisés pour traiter ce type de données.
Données non structurées Les données non structurées sont caractérisées par un manque d'organisation et ne possèdent pas de modèle de données pour décrire la structure d'un enregistrement unique ou les attributs de tous les champs individuels de l'enregistrement. Les exemples de données non structurées incluent des vidéos, de l'audio, des blogs, des publications sur les réseaux sociaux, etc.
Données semi-structurées Les données semi-structurées se situent entre les données structurées et non structurées. Elles contiennent des balises sémantiques mais ne sont pas conformes à la structure associée aux bases de données relationnelles typiques. Les exemples de données semi-structurées incluent les données JSON et XML, ainsi que les métadonnées liées aux fichiers vidéos et audios. Les données semi-structurées contiennent certaines parties qui sont structurées et d'autres qui ne le sont pas.

Système de gestion de base de données (SGBD)

Les systèmes de gestion de bases de données, tels que MySQL et PostgreSQL, sont des logiciels qui permettent aux utilisateurs de créer, maintenir et supprimer plusieurs bases de données. Ils fournissent également des services périphériques et des interfaces pour permettre aux utilisateurs de stocker, de gérer et d'accéder facilement aux données dans une base de données.

Base de données

Une base de données est une collection organisée et structurée de données, stockées et récupérées généralement de manière électronique. La structure et l'organisation des données sont importantes pour faciliter la récupération efficace des données. Il existe essentiellement deux types de bases de données :

Les bases de données relationnelles ou SQL
Les bases de données non relationnelles ou NoSQL

Bases de données relationnelles/SQL

Les bases de données relationnelles stockent les données sous forme de lignes dans des tables, où les colonnes suivent un schéma défini pour décrire le type et la taille des données qu'une colonne de table peut contenir. Les tables ont généralement une colonne comme clé pour identifier de manière unique chaque ligne de la table. Les relations entre deux tables sont définies par une colonne ou un ensemble de colonnes apparaissant dans les deux tables. Les bases de données relationnelles ne traitent que des données structurées. Les RDBMS, ou systèmes de gestion de bases de données relationnelles, sont une technologie largement adoptée, avec des implémentations populaires telles que Oracle, DB2, Microsoft SQL Server, PostgreSQL et MySQL.

Bases de données non relationnelles/NoSQL

L'essor des entreprises Web 2.0 a popularisé les bases de données NoSQL, en réponse à la gestion d'ensembles de données de plus en plus volumineux. Les bases de données NoSQL ont une approche différente de la conception des bases de données, évitant le schéma strict des bases de données relationnelles au profit d'une base de données sans schéma. Il existe différents types de bases de données NoSQL, tels que des magasins clé-valeur, des magasins de colonnes, des magasins de documents, des bases de données de graphes et des moteurs de recherche. La principale différence entre les bases de données NoSQL et les bases de données SQL est l'absence de schéma rigide dans les premières. Les bases de données NoSQL, les données non structurées et semi-structurées sont liées au Big Data.

Mégadonnées

Le Big Data est composé d'ensembles de données dont la taille dépasse la capacité des outils logiciels traditionnels ( comme les technologies SQL) pour capturer, conserver, gérer et traiter les données dans un délai tolérable. La définition de "gros" dans le terme Big Data est une cible en mouvement qui évolue vers un nombre plus élevé à mesure que les capacités logicielles et matérielles s'améliorent pour traiter des volumes de données plus importants.

Histoire et architecture de MySQL

SQL, acronyme de Structured Query Language, était initialement connu sous le nom de SEQUEL (Structured English Query Language). Cependant, le nom a été modifié en raison des droits d'auteur détenus par une autre entreprise. SQL a été développé par deux ingénieurs d'IBM pour manipuler des données stockées dans une base de données relationnelle développée par IBM.

Norme SQL et implémentations

SQL a été accepté comme norme par l'ANSI en 1986 et par l'ISO en 1987. Depuis, il a été révisé à plusieurs reprises, et diverses entreprises ont créé des implémentations de cette norme, telles que MySQL, SQL Server, PostgreSQL, etc. Les implémentations des différents fournisseurs sont similaires car elles sont toutes conformes à la même norme, mais il peut y avoir des différences de syntaxe mineures. Les implémentations populaires incluent Oracle RDBMS, IBM DB2, Microsoft SQL Server, Teradata et MySQL. SQL est important car de nombreuses entreprises ont des bases de données relationnelles alimentées par des technologies liées à SQL.

MySQL est basé sur une architecture client-serveur et se compose de différents composants. Les applications, telles qu'un site Web ou le client de ligne de commande MySQL, se connectent et interagissent avec les bases de données via le serveur MySQL, qui répond aux requêtes des clients. MySQL peut être divisé en trois couches :

Couche Application/Client

La couche application est responsable des connexions client, de l'autorisation, de l'authentification et de la sécurité.

Couche Serveur MySQL

La couche serveur de MySQL est chargée de l'analyse, de l'optimisation et de la gestion des requêtes soumises, ainsi que de la gestion des caches et des tampons. Elle fournit également des fonctionnalités intégrées telles que la récupération et la sauvegarde des partitions. En outre, cette couche gère également l'interface SQL pour interagir avec la base de données. Cette couche est également appelée moteur relationnel, qui produit un plan d'exécution de requête qui est transmis au moteur de stockage.

Couche Moteur de stockage

Le moteur de stockage modifie ou récupère les données conformément au plan d'exécution de requête transmis par le moteur relationnel. Cette couche est responsable de l'accès physique aux données, du stockage et de la récupération des données sur le disque. MySQL prend en charge plusieurs moteurs de stockage, tels que InnoDB, MyISAM, et bien d'autres, chacun ayant ses propres caractéristiques et avantages.

SQL de base

Explorer MySQL

SHOW DATABASES ; : La requête affiche uniquement les bases de données que vous avez le privilège d'afficher.

USE mysql; : Afin d'explorer une base de données particulière, nous devons dire au SGBD que nous voulons que nos requêtes soient dirigées vers la base de données de notre choix.

SHOW CREATE DATABASE mysql; : La base de données MySQL a déjà été créée pour nous. Nous pouvons examiner comment la base de données a été créée à l'aide de la requête

SHOW TABLES;: Nous aimerions savoir quelles tables contient la base de données MySQL

DESCRIBE user; : explorer la structure d'une table. La sortie affichera les différentes colonnes dont le tableau est composé, le type de données de chaque colonne et d'autres métadonnées associées.

sql SHOW CREATE TABLE servers; : afficher comment la table a été créée

sql SHOW COLUMNS FROM servers; : afficher les informations de colonne d'une table

Créer une base de données

Une base de données contient toutes les tables et une table contient des données organisées en lignes et colonnes. Les colonnes représentent les attributs d'une entité et les lignes définissent les attributs d'une entité.

Nous pouvons utiliser l’instruction CREATE pour créer une base de données. Exemple : CREATE DATABASE MovieIndustry;

Le résultat attendu après l'exécution réussie d'une instruction SQL. Si tout se passe bien, un message de confirmation similaire à « Requête OK, 1 ligne affectée (0,00 sec) » est affiché. En outre, un fichier db.opt est généré en arrière-plan par MySQL, qui stocke les options de base de données.

Si nous tentons de recréer une base de données existante, MySQL signalera une erreur. Nous pouvons contourner cette erreur en utilisant la commande IF NOT EXISTS comme indiqué ci-dessous CREATE DATABASE IF NOT EXISTS MovieIndustry;

DROP DATABASE MovieIndustry;: Supprimer une base de données. Toutes les tables, index et autres structures créés dans la base de données sont également supprimés.

Types de données

Nous allons apprendre à créer des tables dans une base de données. Une table est constituée de colonnes qui représentent les attributs d'une entité. Par exemple, une table d'acteurs peut avoir des colonnes pour le nom, le prénom, l'âge, la date de naissance, etc. MySQL fournit différents types de données pour représenter les types de colonnes dans une table, tels que les types de caractères pour les noms, les nombres pour les âges, les dates pour les dates de naissance, etc. Ces types de données permettent de structurer les données des bases de données relationnelles en utilisant un typage et un dimensionnement forts, similaires à un langage fortement typé tel que Java ou C#.

Numérique : par exemple INT, BIGINT, TINYINT, DECIMAL, etc.
Date et heure : par exemple, DATE, TIME, TIMESTAMP, YEAR, etc.
Chaîne : par exemple VARCHAR, CHAR, ENUM, SET, BLOB, etc.
JSON par exemple, JSON
Les données spatiales : représentent l'emplacement, la taille et la forme d'un objet sur la planète Terre, tel qu'un bâtiment, un lac, une montagne ou un canton. MySQL prend également en charge les types de données spatiales, par exemple GEOMETRY, POINT, etc.

Exemple:

tabDonnées.png

Créer un tableau

Lors de la création d'une table dans MySQL, nous devons spécifier le nom de la table ainsi que les noms et les types de données de chaque colonne. D'autres contraintes peuvent également être spécifiées, mais pour l'instant, nous allons nous concentrer sur la création de la table de la manière la plus simple possible.

Syntaxe

CREATE TABLE nomtable
(
    col1 <TypeDonnées> <Restrictions>,
    col2 <TypeDonnées> <Restrictions>,
    col3 <TypeDonnées> <Restrictions>, <
    Définitions
    de
    clé
    primaire
    ou
    d_index>
);

Exemple création d'une table :

DESC Actors; : La commande DESC affiche toutes les colonnes de la table et les métadonnées associées telles que le type de données et la valeur NULL pour chaque colonne.

Lors de la création d'une table, il est important de définir une clé primaire pour identifier de manière unique chaque ligne. Dans le cas de la table Actors, il est recommandé d'ajouter une colonne ID numérique qui s'auto-incrémente à chaque nouvelle ligne, en utilisant la fonctionnalité AUTO_INCREMENT de MySQL. Cette fonctionnalité est soumise à certaines restrictions, telles que la nécessité de définir la colonne comme clé primaire ou comme unique, et de la définir comme non nulle. La valeur de départ pour AUTO_INCREMENT est 1 pour une table vide.

Il ne peut y avoir qu'une seule colonne marquée comme AUTO_INCREMENT dans une table.
La colonne AUTO_INCREMENT ne peut pas avoir de valeur par défaut.
La colonne AUTO_INCREMENT doit être indexée.

La fonctionnalité AUTO_INCREMENT n'est pas portable vers d'autres bases de données et le compteur est réinitialisé lorsque nous tronquons ou supprimons une table. La prochaine restriction que nous voulons imposer aux utilisateurs de la table Actors est d'interdire l'insertion de null comme valeur de colonne. Nous pouvons marquer une colonne comme NOT NULL pour atteindre cet objectif. Nous pouvons également spécifier une valeur par défaut pour une colonne à l'aide du mot-clé DEFAULT. Nous allons recréer la table Actors, ajouter une valeur "enum".

"Unknown" à la liste et marquer les autres colonnes comme NOT NULL pour que les utilisateurs soient obligés de fournir des valeurs valides pour les autres colonnes. Nous pouvons également utiliser NOT NULL et DEFAULT ensemble.

Exemple :

Nous pouvons également utiliser la clause IF EXISTS lors de la création d'une table, de la même manière que nous l'avons fait lors de la création d'une base de données.

Tableau temporaire

MySQL permet de créer des tables temporaires qui sont supprimées automatiquement à la fin de la session. Les tables temporaires sont conservées pendant la durée de la session MySQL. Le programme Monitor affiche l'invite MySQL dans le terminal. Syntaxe

CREATE
TEMPORARY TABLE tableName (
col1 <TypeDonnées> <Restrictions>,
col2 <TypeDonnées> <Restrictions>,
col3 <TypeDonnées> <Restrictions>,
<Définitions de clé primaire ou d_index> );

Comment capturer uniquement les prénoms des acteurs dans une table temporaire en MySQL. CREATE TEMPORARY TABLE ActorNames (FirstName CHAR(20));

Classements et jeux de caractères

Le jeu de caractères détermine les caractères que MySQL peut stocker, tandis que le jeu de classement décide de l'ordre des chaînes. Les bases de données peuvent contenir des caractères de langues différentes, qui peuvent être classés différemment en fonction de la langue. Un exemple de caractère partagé par plusieurs langues est "ü", qui peut apparaître à des positions différentes dans les alphabets allemand, suédois et finnois.

SHOW CHARACTER SET; : Les jeux de caractères disponibles sur le serveur peuvent être répertoriés

MySQL utilise par défaut le jeu de caractères latin-1, avec une collation par défaut latin1_swedish_ci qui est insensible à la casse et tri les caractères accentués selon les conventions suédoises.

SHOW COLLATION; : nous pouvons lister les classements

SHOW VARIABLES LIKE "c%"; : inspecter les valeurs par défaut de votre serveur

Insertion de données

nous allons apprendre à ajouter des données dans une table en utilisant l'instruction INSERT. Nous allons utiliser la table Actors que nous avons créée dans une leçon précédente et récupérer les lignes ajoutées à l'aide du mot-clé SELECT.

Exemple :

INSERT INTO table (col1, col2, coln)
    VALEURS ( val1, val2, valn);

Plusieurs enregistrements :

Interroger des données

Syntaxe :

SELECT col1, col2, … coln
 
FROM table
 
WHERE < condition >;

Exemple :

SELECT FirstName, SecondName
from Actors
WHERE NetWorthInMillions > 0;

Le tableau suivant répertorie les différents opérateurs pouvant être utilisés dans une clause WHERE .

Opérateur LIKE

Like est un opérateur de comparaison qui peut être utilisé pour comparer des valeurs de chaîne. Il est utilisé avec la clause WHERE pour rechercher des motifs dans une colonne. Il existe deux caractères génériques qui peuvent être utilisés avec LIKE.

Syntaxe :

SELECT col1,
       col2, …
       col
           DE table
 
WHERE col3 LIKE "%some-string%";

Exemple :

-- The lesson queries are reproduced below for convenient copy/paste into the terminal. 
 
-- Query 1
SELECT *
from Actors
WHERE FirstName LIKE "Jen%";
 
-- Query 2
SELECT *
from Actors
where FirstName LIKE "Jennifer%";
 
-- Query 3
SELECT *
from Actors
where FirstName LIKE "%";
 
-- Query 4
SELECT *
from Actors
WHERE FirstName LIKE "_enn%";
 
-- Query 5
SHOW
DATABASES LIKE "M%";
 
-- Query 6
SHOW
TABLES LIKE "A%";

L' opérateur LIKE fonctionne uniquement avec les types de données de chaîne et nous permet de récupérer des lignes en fonction de la correspondance de modèle sur une colonne particulière.

ORDER BY

ORDER BY est un mot-clé qui peut être utilisé pour trier les résultats d'une requête. Il peut être utilisé avec les colonnes numériques et de chaîne. Par défaut, ORDER BY trie les résultats dans l'ordre croissant. Nous pouvons utiliser l'ordre DESC pour trier les résultats dans l'ordre décroissant.

Exemple :

-- The lesson queries are reproduced below for convenient copy/paste into the terminal. 
 
-- Query 1
SELECT *
FROM Actors
ORDER BY FirstName;
 
-- Query 2
SELECT *
FROM Actors
ORDER BY FirstName DESC;
 
-- Query 3
SELECT *
FROM Actors
ORDER BY NetWorthInMillions, FirstName;
 
-- Query 4
SELECT *
FROM Actors
ORDER BY NetWorthInMillions, SecondName;
 
-- Query 5
SELECT *
FROM Actors
ORDER BY NetWorthInMillions DESC, FirstName ASC;
 
-- Query 6
SELECT *
FROM Actors
ORDER BY NetWorthInMillions DESC, FirstName DESC;
 
-- Query 7
SELECT *
FROM Actors
ORDER BY BINARY FirstName;
 
-- Query 8
SELECT *
FROM Actors
ORDER BY NetWorthInMillions;
 
-- Query 9
SELECT *
FROM Actors
ORDER BY CAST(NetWorthInMillions AS CHAR);

LIMIT

Limit est un mot-clé qui peut être utilisé pour limiter le nombre de lignes renvoyées par une requête. Il est généralement utilisé avec ORDER BY pour récupérer les lignes supérieures ou inférieures d'une table.

Syntaxe :

SELECT col1,
       col2, …
       col
           DE table
 
WHERE col3 LIKE "%some-string%"
    COMMANDER PAR col3

Exemple :

-- The lesson queries are reproduced below for convenient copy/paste into the terminal. 
 
-- Query 1
SELECT FirstName, SecondName
from Actors
ORDER BY NetWorthInMillions DESC LIMIT 3;
 
-- Query 2
SELECT FirstName, SecondName
from Actors
ORDER BY NetWorthInMillions DESC LIMIT 4
OFFSET 3;
 
-- Query 3
SELECT FirstName, SecondName
from Actors
ORDER BY NetWorthInMillions DESC LIMIT 3,4;
 
-- Query 4
SELECT FirstName, SecondName
from Actors
ORDER BY NetWorthInMillions DESC LIMIT 1000
OFFSET 3;
 
-- Query 5
SELECT FirstName, SecondName
from Actors
ORDER BY NetWorthInMillions DESC LIMIT 18446744073709551616;

Suppression de données

Nous pouvons supprimer des lignes d'une table à l'aide de l' instruction DELETE . Une instruction de suppression supprime une ligne entière et non des colonnes individuelles. Si vous souhaitez modifier une valeur de colonne particulière pour une ligne, utilisez l' instruction UPDATE , que nous aborderons ensuite. Sachez également que la suppression de toutes les lignes d'un tableau ne supprime pas le tableau lui-même.

Syntaxe :

DELETE
FROM table
 
WHERE col3 > 5 ORDER BY col1
 
LIMIT 5;

Exemple :

-- The lesson queries are reproduced below for convenient copy/paste into the terminal. 
 
-- Query 1
DELETE
FROM Actors
WHERE FirstName = "priyanka";
 
-- Query 2
DELETE
FROM Actors
WHERE Gender = "Male";
 
-- Query 3
DELETE
FROM Actors ORDER BY NetWorthInMillions DESC LIMIT 3;
 
-- Query 4
DELETE
FROM Actors;

Tronquer

Tronquer est un mot-clé qui peut être utilisé pour supprimer toutes les lignes d'une table. Il est similaire à l' instruction DELETE , mais il ne prend pas de clause WHERE . Il supprime toutes les lignes d'une table et réinitialise l' auto-incrémentation de la table à 1.

Syntaxe :

TRUNCATE table;

Exemple :

Mise à jour des données

Nous pouvons utiliser l' instruction UPDATE pour modifier la valeur d'une colonne pour une ligne ou plusieurs lignes.

Syntaxe :

UPDATE table
 
SET col1   = val1,
    col2   = val2,
    … coln = valn
 
WHERE < condition >
 
ORDER BY col5
    LIMIT 5;

Exemple :

-- The lesson queries are reproduced below for convenient copy/paste into the terminal. 
 
-- Query 1
UPDATE Actors
SET NetWorthInMillions=1;
 
-- Query 2
UPDATE Actors
SET NetWorthInMillions=5 ORDER BY FirstName LIMIT 3;
 
--Query 3
UPDATE Actors
SET NetWorthInMillions=50,
    MaritalStatus="Single";

Clé primaire et index

Une clé primaire et un index sont utilisés pour améliorer l'efficacité des recherches dans une base de données.

Lorsqu'une requête avec une clause WHERE est émise, MySQL doit analyser l'ensemble de la table pour trouver les lignes correspondantes. Cela peut être inefficace si la table est très grande.
L'ajout d'un index à la table peut accélérer la recherche de lignes correspondantes en créant une représentation ordonnée des données. Cela fonctionne comme l'index d'un livre : il permet de trouver rapidement la page où un mot spécifique est mentionné, sans avoir à parcourir chaque page.
L'index est particulièrement utile si vous recherchez un élément spécifique, par exemple un sujet commençant par une certaine lettre. Il vous permet d'accéder directement à la partie de l'index qui contient cet élément. Cependant, l'index occupe de l'espace dans la base de données, ce qui est le compromis pour cette amélioration de l'efficacité.

Exemple :

-- The lesson queries are reproduced below for convenient copy/paste into the terminal. 
 
-- Query 1
SHOW
INDEX FROM Actors;
 
-- Query 2
ANALYZE
TABLE Actors;
SHOW
INDEX FROM Actors;
 
-- Query 3
INSERT INTO Actors (Id, FirstName, SecondName, DoB, Gender, MaritalStatus, NetWorthInMillions)
VALUES (15, "First", "Row", "1999-01-01", "Male", "Single", 0.00);
INSERT INTO Actors (Id, FirstName, SecondName, DoB, Gender, MaritalStatus, NetWorthInMillions)
VALUES (13, "Second", "Row", "1999-01-01", "Male", "Single", 0.00);
INSERT INTO Actors (Id, FirstName, SecondName, DoB, Gender, MaritalStatus, NetWorthInMillions)
VALUES (12, "Third", "Row", "1999-01-01", "Male", "Single", 0.00);

Index groupé

Dans les bases de données, un index groupé organise physiquement les lignes d'une table en se basant sur l'ordre de l'index, utilisant une structure B-tree ou un R-tree pour les index spatiaux. Cette disposition facilite l'accès à des valeurs qui sont proches les unes des autres à la fois dans l'ordre de l'index et sur le disque. Les arbres B et les arbres B+, des structures de données courantes pour le stockage sur disque, garantissent un nombre fixe de lectures de disque grâce à leur équilibrage, rendant la recherche de données plus facile même pour les très grandes tables qui ne peuvent pas être entièrement conservées en mémoire principale. Un arbre B est composé d'un nœud racine, de nœuds de branche et de nœuds feuilles.

Dans un arbre B+, les données sont stockées exclusivement dans les nœuds feuilles, tandis que la racine et les nœuds internes contiennent uniquement la clé de tri. Dans le contexte de MySQL, ces lignes sont stockées dans des entités appelées "pages", qui constituent la plus petite unité de données pouvant être écrite ou lue sur un disque par une base de données. Ces pages contiennent des lignes de données et forment les nœuds feuilles de l'arbre B+. Pour illustrer, une table des acteurs triée par prénom dans un arbre B+ se structurerait de telle manière que chaque page contiendrait des lignes associées à des acteurs, organisées par prénom.

Les nœuds d'un arbre B+ sont reliés par des pointeurs précédents et suivants, facilitant la navigation dans l'index, ce qui est particulièrement utile pour les requêtes de plage.

Les bases de données travaillent avec des "pages" plutôt qu'avec des lignes individuelles. Lorsqu'une page est chargée en mémoire, toutes les lignes qu'elle contient sont également chargées. Dans MySQL, la taille par défaut d'une page est de 16 Ko, bien que cela soit configurable.

Une collection de pages forme une "étendue", plusieurs étendues forment un "segment", et les segments composent un " tablespace". Un tablespace contient des tables et leurs index associés. Il y a un tablespace appelé "tablespace système", et dans les versions précédentes de MySQL, toutes les tables utilisateurs en faisaient partie. Avec les versions plus récentes, il est possible de configurer un tablespace séparé pour chaque table utilisateur. Les concepts de page, d'étendue, et de segment sont reliés comme indiqué dans le schéma mentionné.

Les pages du B-tree peuvent se diviser ou se fusionner en fonction des besoins. Par exemple, lorsqu'une page est pleine et qu'une nouvelle clé est insérée, la page se divise. À l'inverse, si suffisamment de lignes sont supprimées d'une page, elle peut fusionner avec une autre.

Dans une page de nœud feuille, les enregistrements ou lignes existent sous la forme d'une liste liée unique, qui maintient l'ordre de l'index sur les lignes. Lorsqu'une nouvelle ligne est ajoutée, elle est placée dans l'espace libre disponible sur la page, sans déplacer les enregistrements existants, même si l'ordre de l'index indiquerait qu'elle devrait apparaître entre les lignes existantes. Au lieu de cela, les pointeurs de la liste liée sont réorganisés pour maintenir le bon ordre.

Un index groupé ne garantit pas que les lignes de données sont stockées de manière contiguë sur le disque dur, mais assure simplement que l'ordre physique et logique des lignes est le même.

Dans MySQL, lorsqu'on utilise le moteur de base de données InnoDB, chaque table est stockée sous forme d'index groupé avec la clé primaire comme clé de tri. Un moteur de base de données est un logiciel qui permet la création, la lecture, la mise à jour et la suppression de données dans une base de données. MySQL propose plusieurs moteurs de stockage, dont InnoDB et MyISAM.

MySQL crée un index groupé sur la clé primaire. S'il n'y a pas de clé primaire, il cherche le premier index UNIQUE avec toutes les colonnes définies comme NOT NULL. Si aucun tel index n'est trouvé, MySQL génère un index caché, appelé GEN_CLUST_INDEX, sur une colonne synthétique contenant des valeurs d'ID de ligne.

Cela implique qu'il ne peut y avoir qu'un seul index groupé par table, car les lignes de la table ne peuvent être organisées que dans un seul ordre sur le disque. Tous les autres index sont des index secondaires.

Index non clusterisé

Dans un index non clusterisé, contrairement à un index clusterisé, les nœuds feuilles ne contiennent pas les données réelles, mais un pointeur vers les données stockées ailleurs sur le disque.

Lorsque le moteur de base de données MyISAM est utilisé, les lignes ne sont pas stockées dans un ordre spécifique mais apparaissent de manière désordonnée, dans une structure appelée "heap" ou "tas". MyISAM repose sur ISAM (Indexed Sequential Access Method), une méthode d'indexation conçue par IBM pour récupérer rapidement des informations à partir de grands ensembles de données. Dans cette configuration, un index non clusterisé contient des pointeurs vers les lignes de données au sein du tas.

Lors de l'utilisation du moteur de base de données MyISAM, les lignes apparaissent dans l'ordre d'insertion et tous les index, y compris le primaire, sont des index secondaires non groupés.

Avec InnoDB, les nœuds feuilles de l'index secondaire ne pointent pas directement vers les lignes comme c'est le cas avec MyISAM. Étant donné que les pages peuvent être divisées et fusionnées et que les lignes peuvent être physiquement déplacées sur le disque, les nœuds feuilles de l'index secondaire InnoDB stockent les valeurs de clé primaire au lieu de pointeurs vers des lignes. Cela permet d'éviter la réécriture coûteuse de pointeurs chaque fois qu'une ligne est déplacée.

Par conséquent, lors de la recherche de données avec InnoDB, on consulte d'abord l'index secondaire pour obtenir la clé primaire, puis utilise cette clé pour naviguer dans l'index primaire et localiser la ligne. À l'inverse, un index secondaire MyISAM peut accéder directement aux données de la ligne une fois le nœud feuille de son index atteint.

Coût de l'indexation

Un index n'est pas gratuit. D'une part, il prend de l'espace disque supplémentaire, et d'autre part, il doit être modifié chaque fois qu'une insertion ou une mise à jour est effectuée dans la table.

Alter

Alter est un mot-clé SQL qui permet de modifier la structure d'une table. Il est utilisé pour ajouter, supprimer ou modifier des colonnes, des contraintes, des index, etc. Il est également utilisé pour ajouter et supprimer des tables.

Syntaxe ALTER TABLE

ALTER TABLE table
    CHANGE oldColumnName newColumnName <datatype> <restrictions>;

Exemple

-- The lesson queries are reproduced below for convenient copy/paste into the terminal. 
 
-- Query 1
ALTER TABLE Actors CHANGE FirstName First_Name varchar (120);
 
-- Query 2
ALTER TABLE Actors MODIFY First_Name varchar (20) DEFAULT "Anonymous";
 
-- Query 3
ALTER TABLE Actors CHANGE First_Name First_Name varchar (20) DEFAULT "Anonymous";
 
-- Query 4
ALTER TABLE Actors MODIFY First_Name INT;
 
-- Query 5
ALTER TABLE Actors MODIFY First_Name varchar (300);
 
-- Query 6
ALTER TABLE Actors
    ADD MiddleName varchar(100);
 
-- Query 7
ALTER TABLE Actors DROP MiddleName;
 
-- Query 8
ALTER TABLE Actors
    ADD MiddleName varchar(100) FIRST;
 
-- Query 9
ALTER TABLE Actors DROP MiddleName;
ALTER TABLE Actors
    ADD MiddleName varchar(100) AFTER DoB;
 
--Query 10
ALTER TABLE Actors DROP MiddleName, ADD Middle_Name varchar(100);

Alter index

La création d'index nécessite une compréhension des schémas d'accès des données de l'application, ce qui est souvent difficile à prédire sans observation directe. Après le déploiement de l'application, il est possible d'ajouter, de supprimer ou de modifier des index en fonction des besoins de performance. Il est important de noter que ces modifications sur les index n'affectent pas les données contenues dans la table elle-même.

Syntaxe ALTER INDEX

ALTER TABLE table
 
    ADD INDEX indexName (col1, col2, … coln);

Exemple

-- The lesson queries are reproduced below for convenient copy/paste into the terminal. 
 
-- Query 1
ALTER TABLE Actors
    ADD INDEX nameIndex (FirstName);
 
-- Query 2
ALTER TABLE Actors
    ADD INDEX nameIndexWithOnlyTenChars (FirstName(10));
 
-- Query 3
ALTER TABLE Actors DROP INDEX nameIndex;
 
-- Query 4
ALTER TABLE Actors DROP PRIMARY KEY;
 
-- Query 5
CREATE TABLE Movies
(
    Name     VARCHAR(100),
    Released DATE,
    PRIMARY KEY (Name)
);
DESC Movies;
ALTER TABLE Movies DROP PRIMARY KEY;
ALTER TABLE Movies
    ADD PRIMARY KEY (Released);

Distinct

Nous allons travailler avec la clause DISTINCT dans cette leçon. Cette clause peut être utilisée pour générer des lignes uniques dans un jeu de résultats. N'oubliez pas que DISTINCT est un filtre de post-traitement, ce qui signifie qu'il est appliqué aux lignes résultantes d'une requête.

Syntaxe DISTINCT

SELECT DISTINCT col1 table DEPUIS;

Exemple

-- The lesson queries are reproduced below for convenient copy/paste into the terminal. 
 
-- Query 1
SELECT DISTINCT MaritalStatus
from Actors;
 
-- Query 2
SELECT DISTINCT MaritalStatus, FirstName
from Actors;

Group by

La clause GROUP BY, comme son nom l'indique, regroupe des lignes ensemble en différents groupes. Cette clause renvoie une ligne pour chaque groupe. Les données sont organisées en utilisant une liste de colonnes séparées par des virgules, spécifiée après la clause GROUP BY. La clause GROUP BY est souvent utilisée avec des fonctions d'agrégation telles que COUNT, MAX, MIN, SUM et AVG pour calculer une statistique agrégée pour chaque groupe.

Syntaxiquement, la clause GROUP BY doit apparaître après les clauses FROM et WHERE et est également évaluée après celles-ci. Cependant, GROUP BY est évalué avant les clauses ORDER BY, LIMIT et HAVING.

Syntaxe GROUP BY

SELECT col1, AggregateFunction(col3)
 
FROM table;
 
GROUP BY col1, col2,
… coln
 
ORDER BY col2;

Exemple

-- The lesson queries are reproduced below for convenient copy/paste into the terminal. 
 
-- Query 1
SELECT FirstName
FROM Actors
GROUP BY FirstName;
 
-- Query 2
SELECT FirstName, SecondName
FROM Actors
GROUP BY FirstName;
 
-- Query 3
SELECT Gender, COUNT(*)
FROM Actors
GROUP BY Gender;
 
-- Query 4
SELECT Gender
FROM Actors
GROUP BY Gender;
 
-- Query 5
SELECT MaritalStatus, AVG(NetworthInMillions)
FROM Actors
GROUP BY MaritalStatus
ORDER BY MaritalStatus ASC;

Having

Having est un mot-clé SQL qui permet de filtrer les lignes résultantes d'une requête GROUP BY. Il est utilisé pour appliquer des conditions sur les groupes créés par la clause GROUP BY.

syntaxe HAVING

SELECT col1, AggregateFunction(col3) AS count
 
FROM table
 
GROUP BY col1, col2, … coln
 
HAVING count > 75
 
ORDER BY col2;

Exemple

-- The lesson queries are reproduced below for convenient copy/paste into the terminal. 
 
-- Query 1
SELECT MaritalStatus, AVG(NetworthInMillions) AS NetWorth
FROM Actors
GROUP BY MaritalStatus
HAVING NetWorth > 450
    OR NetWorth < 250;
 
-- Query 2
SELECT MaritalStatus, AVG(NetworthInMillions) AS NetWorth
FROM Actors
GROUP BY MaritalStatus
HAVING MaritalStatus = 'Married';
 
-- Query 3
SELECT MaritalStatus, AVG(NetworthInMillions) AS NetWorth
FROM Actors
WHERE MaritalStatus = 'Married'
GROUP BY MaritalStatus;

Types de jointures

Les bases de données relationnelles établissent des relations entre les tables, et souvent, les requêtes nécessitent des informations provenant de deux tables ou plus. Les jointures nous permettent de combiner les lignes de plusieurs tables en utilisant les colonnes qu'elles ont en commun. En effet, les relations définies entre les tables sont ce qui fait des bases de données relationnelles, des bases relationnelles.

Pour illustrer ces concepts, nous allons travailler avec deux tables qui ont une colonne en commun. Les deux tables sont présentées ci-dessous :

Jointure croisée

Nous allons commencer par la jointure croisée, également appelée produit cartésien. Dans ce cas, nous prenons la première ligne de la table A et la mettons en correspondance avec chaque ligne de la table B. Ensuite, nous faisons la même chose avec la deuxième ligne de la table A. Il n'y a pas de condition spécifique à vérifier pour déterminer si une ligne de la table A doit être jointe à une ligne de la table B. Le résultat d'une jointure croisée entre les deux tables exemples serait comme suit :

Jointure interne

Dans le cas d'une jointure interne, une ou plusieurs conditions sont évaluées pour déterminer si une ligne de la table A doit être jointe à une ligne de la table B. Cette condition est appelée le prédicat de jointure. Dans notre exemple, les deux tables partagent la colonne "movie iD" comme valeur commune. Cette colonne établit une relation entre les deux tables. Grâce à cette colonne commune, nous pouvons savoir si un film donné a été projeté dans l'un des cinémas de notre base de données et si oui, pendant combien de jours.

Jointure externe gauche

Dans le cas d'une jointure gauche, le résultat comprend des lignes qui correspondent au prédicat de jointure et aussi des lignes de la table spécifiée à gauche de la clause de jointure qui ne correspondent pas à ce prédicat. Null est inséré pour les colonnes de la table B pour lesquelles il n'y a pas de correspondance dans la table A. En d'autres termes, toutes les lignes de la table de gauche sont toujours incluses dans le résultat, et les lignes de la table de droite sont incluses seulement si elles correspondent au prédicat de jointure.

Jointure externe droite

La jointure droite est l'inverse de la jointure gauche. Dans ce cas, toutes les lignes de la table de droite sont toujours incluses dans le résultat, et seules les lignes de la table de gauche qui satisfont la condition de jointure sont présentes dans le résultat. Avec les jointures gauche et droite, nous spécifions de quel côté de la jointure les lignes sont autorisées à figurer dans le résultat même lorsque la condition de jointure n'est pas satisfaite.

Jointure externe complète

Dans le cas d'une jointure complète, les lignes des deux tables sont incluses dans le résultat. Les lignes qui satisfont la condition de jointure sont incluses une seule fois. Pour les lignes qui ne correspondent pas à la condition de jointure, NULL est inséré pour les colonnes de l'autre table. Il faut noter que MySQL ne prend pas en charge la jointure complète.

auto-jointure

Une auto-jointure est le jeu de résultats lorsqu'une table est jointe à elle-même. Si nous créons une auto-jointure de la table de film basée sur l'ID de film, le résultat sera le suivant :

Auto-jointure

Self Inner Join

syntaxe

SELECT *
 
FROM table1
 
         INNER JOIN table1
                    ON < join condition >;

exemple

-- The lesson queries are reproduced below for convenient copy/paste into the terminal. 
 
-- Query 1
SELECT *
FROM Actors a
         INNER JOIN Actors b;
 
-- Query 2
SELECT *
FROM Actors a
         INNER JOIN Actors b USING (FirstName);
 
-- Query 3
SELECT *
FROM Actors a
         INNER JOIN Actors b USING (NetWorthInMillions);

Jointure interne

Syntaxe

SELECT *
 
FROM table1
 
         INNER JOIN table2
                    ON < join condition >;

Exemple

-- The lesson queries are reproduced below for convenient copy/paste into the terminal. 
 
-- Query 1
SELECT FirstName, SecondName, AssetType, URL
FROM Actors
         INNER JOIN DigitalAssets
                    ON Actors.Id = DigitalAssets.ActorID;
 
-- Query 2
SELECT FirstName, SecondName, AssetType, URL
FROM Actors
         INNER JOIN DigitalAssets
                    USING (Id);
 
-- Query 3
SELECT FirstName, SecondName, AssetType, URL
FROM Actors,
     DigitalAssets
WHERE ActorId = Id;
 
-- Query 4
SELECT FirstName, SecondName, AssetType, URL
FROM Actors,
     DigitalAssets;
 
-- Query 5
SELECT FirstName, SecondName, AssetType, URL
FROM Actors
         INNER JOIN DigitalAssets;
 
-- Query 6
-- Makes no sense to join tables on FirstName and URL columns as they aren't related. 
SELECT *
FROM Actors
         INNER JOIN DigitalAssets ON URL = FirstName;
 
-- Query 7
-- Again no sense in combining net worth and actor id. Additionally, one is an int and the other a decimal but still comparable.
SELECT *
FROM Actors
         INNER JOIN DigitalAssets
                    ON NetWorthInMillions = ActorId;

Jointures gauche et droite

Rejoindre à gauche

Rejoindre à droite

syntaxe jointure gauche

SELECT *
 
FROM table1
    LEFT [OUTER] JOIN table2
 
ON < join condition >

Syntaxe jointure droite

SELECT *
 
FROM table1
    RIGHT [OUTER] JOIN table2
 
ON < join condition >

exemple

-- The lesson queries are reproduced below for convenient copy/paste into the terminal. 
 
-- Query 1
SELECT FirstName, SecondName, AssetType, URL
FROM Actors
         LEFT JOIN DigitalAssets
                   ON Actors.Id = DigitalAssets.ActorID;
 
-- Query 2
SELECT FirstName, SecondName, AssetType, URL
FROM DigitalAssets
         LEFT JOIN Actors
                   ON Actors.Id = DigitalAssets.ActorID;
 
-- Query 3
SELECT FirstName, SecondName, AssetType, URL
FROM Actors
         RIGHT JOIN DigitalAssets
                    ON Actors.Id = DigitalAssets.ActorID;

Requêtes imbriquées

Dans cette leçon, nous allons étudier les requêtes imbriquées, c'est-à-dire une requête placée à l'intérieur d'une autre requête. L'utilisation des requêtes imbriquées permet de combiner plusieurs requêtes pour obtenir le résultat souhaité en une seule requête globale, plutôt que d'exécuter chaque requête individuellement. Les requêtes imbriquées sont généralement plus lentes, mais elles sont plus lisibles et expressives que leurs équivalents en jointures. De plus, dans certaines situations, les requêtes imbriquées sont le seul moyen d'obtenir les informations souhaitées à partir d'une base de données.

exemple

-- The lesson queries are reproduced below for convenient copy/paste into the terminal. 
 
-- Query 1
SELECT URL AS "Brad's Insta Page"
FROM Actors
         INNER JOIN DigitalAssets
WHERE AssetType = "Instagram"
  AND FirstName = "Brad";
 
-- Query 2
SELECT URL
FROM DigitalAssets
WHERE AssetType = "Instagram"
  AND ActorId = (SELECT Id
                 FROM Actors
                 WHERE FirstName = "Brad");
 
-- Query 3
SELECT FirstName
FROM Actors
         INNER JOIN DigitalAssets
                    ON ActorId = Id
WHERE LastUpdatedOn = (SELECT MAX(LastUpdatedOn)
                       FROM DigitalAssets);

Requêtes de colonne imbriquées

Dans cette leçon, nous abordons les requêtes imbriquées renvoyant des valeurs de la même colonne.

exemple

-- The lesson queries are reproduced below for convenient copy/paste into the terminal. 
 
-- Query 1
SELECT *
FROM Actors
         INNER JOIN DigitalAssets ON ActorId = Id
WHERE AssetType = ANY (SELECT DISTINCT AssetType
                       FROM DigitalAssets
                       WHERE AssetType != 'Website');
 
-- Query 2
SELECT *
FROM Actors
         INNER JOIN DigitalAssets ON ActorId = Id
WHERE AssetType != 'Website';
 
-- Query 3
SELECT FirstName, SecondName
FROM Actors
WHERE Id = ANY (SELECT ActorId
                FROM DigitalAssets
                WHERE AssetType = 'Facebook');
 
-- Query 4
SELECT FirstName, SecondName
FROM Actors
WHERE Id IN (SELECT ActorId
             FROM DigitalAssets
             WHERE AssetType = 'Facebook');
 
-- Query 5
SELECT FirstName, SecondName
FROM Actors
WHERE NetworthInMillions > ALL (SELECT NetworthInMillions
                                FROM Actors
                                WHERE FirstName LIKE "j%");

Requêtes de lignes imbriquées

Cette leçon porte sur les requêtes imbriquées renvoyant des lignes, ce qui permet à la requête externe de correspondre à plusieurs valeurs de colonne différentes. Jusqu'à présent, les requêtes imbriquées ont été utilisées uniquement avec la clause WHERE, mais maintenant nous allons les utiliser également avec la clause FROM.

exemple

-- The lesson queries are reproduced below for convenient copy/paste into the terminal. 
 
-- Query 1
SELECT FirstName
FROM Actors
         INNER JOIN DigitalAssets
                    ON Id = ActorId
                        AND MONTH (DoB) = MONTH (LastUpdatedOn)
    AND DAY (DoB) = DAY (LastUpdatedOn);
 
-- Query 2
SELECT FirstName
FROM Actors
WHERE (Id, MONTH(DoB), DAY(DoB))
    IN (SELECT ActorId, MONTH (
    LastUpdatedOn)
    , DAY (LastUpdatedOn)
FROM DigitalAssets);
 
--Query 3
SELECT ActorId, AssetType, LastUpdatedOn
FROM DigitalAssets;
 
-- Query 4
SELECT FirstName, AssetType, LastUpdatedOn
FROM Actors
         INNER JOIN (SELECT ActorId, AssetType, LastUpdatedOn
                     FROM DigitalAssets) AS tbl
                    ON ActorId = Id;
 
-- Query 5
SELECT FirstName, AssetType, LastUpdatedOn
FROM Actors
         INNER JOIN (SELECT ActorId, AssetType, LastUpdatedOn
                     FROM DigitalAssets) AS tbl
                    ON ActorId = Id
WHERE FirstName = "Kim";
 
-- Query 6
SELECT FirstName, AssetType, LastUpdatedOn
FROM Actors
         INNER JOIN (SELECT ActorId, AssetType, LastUpdatedOn
                     FROM DigitalAssets) AS tbl
                    ON ActorId = Id
WHERE FirstName = "Kim"
ORDER BY LastUpdatedOn DESC LIMIT 1;

Opérateur EXISTE

L'opérateur booléen EXISTS et son complément NOT EXISTS sont utilisés pour vérifier si une sous-requête renvoie des lignes ou aucune.

exemple

-- The lesson queries are reproduced below for convenient copy/paste into the terminal. 
 
-- Query 1
SELECT *
FROM Actors
WHERE EXISTS (SELECT *
              FROM DigitalAssets
              WHERE BINARY URL LIKE "%clooney%");
 
-- Query 2
SELECT *
FROM Actors
WHERE NOT EXISTS (SELECT *
                  FROM DigitalAssets
                  WHERE BINARY URL LIKE "%clooney%");

Requêtes corrélées

Les requêtes corrélées sont un type de requêtes imbriquées où la requête interne fait référence à une table ou une colonne de la requête externe.

exemple

-- The lesson queries are reproduced below for convenient copy/paste into the terminal. 
 
-- Query 1
SELECT FirstName
FROM Actors 
INNER JOIN DigitalAssets
ON Id = ActorId
WHERE URL LIKE CONCAT("%",FirstName,"%") 
AND AssetType="Twitter";
 
-- Query 2
SELECT FirstName
FROM Actors
WHERE EXISTS (SELECT URL 
              FROM DigitalAssets
              WHERE URL LIKE CONCAT("%",FirstName,"%") 
              AND AssetType="Twitter");

Opérations multi-tables

Supprimer plusieurs tables

Pour supprimer des données d'une table et de toutes les tables associées, utilisez des requêtes de suppression multi-tables. Par exemple, si vous supprimez un acteur de la table Actors, vous pouvez également supprimer toutes les lignes de la table DigitalAssets appartenant à cet acteur.

syntaxe

DELETE T1, T2
 
FROM T1, T2, T3
 
WHERE <condition>

exemple

-- The lesson queries are reproduced below for convenient copy/paste into the terminal. 
 
-- Query 1
DELETE Actors, DigitalAssets   -- Mention tables to delete rows from
FROM Actors   -- The inner join creates a derived table with matching rows from both tables    
INNER JOIN DigitalAssets
ON Actors.Id = DigitalAssets.ActorId
WHERE AssetType = "Twitter";
 
-- Query 2
DELETE FROM Actors, DigitalAssets
USING Actors        
INNER JOIN DigitalAssets
ON Actors.Id = DigitalAssets.ActorId
WHERE AssetType = "Twitter";
 
-- Query 3
DELETE Actors 
FROM Actors 
WHERE EXISTS ( SELECT * 
               FROM Actors 
               INNER JOIN DigitalAssets
               ON  Id = ActorId 
               WHERE AssetType="Twitter");
 
-- Query 4
DELETE Actors 
FROM Actors 
WHERE EXISTS (SELECT * 
              FROM DigitalAssets 
              WHERE ActorId = Id AND AssetType = "Twitter");
 
-- Query 5
DELETE Actors, DigitalAssets   -- specify the tables to delete from
FROM Actors, DigitalAssets   -- reference tables
WHERE ActorId = Id   -- conditions to narrow down rows         
AND FirstName = "Johnny"
AND AssetType != "Pinterest";

Mise à jour de plusieurs tables

La mise à jour de plusieurs tables et la suppression de plusieurs tables sont possibles.

syntaxe

UPDATE T1, T2
 
SET col1 = newVal1, col2 = newVal2
 
WHERE <condition1>

exemple

-- The lesson queries are reproduced below for convenient copy/paste into the terminal. 
 
-- Query 1
UPDATE 
Actors INNER JOIN DigitalAssets 
ON Id = ActorId 
SET FirstName = UPPER(FirstName), SecondName = UPPER(SecondName), URL = UPPER(URL) 
WHERE AssetType = "Facebook";
 
-- Query 2
UPDATE  Actors, DigitalAssets
SET FirstName = UPPER(FirstName), SecondName = UPPER(SecondName), URL = UPPER(URL) 
WHERE AssetType = "Facebook"
AND ActorId = Id;

SELECT et INSERT

MySQL permet d'insérer plusieurs lignes d'une autre table dans une table existante en utilisant les instructions SELECT et INSERT. Il est également possible de créer une nouvelle table et de la remplir avec les lignes d'une autre table.

syntaxe to insert

INSERT INTO table1 (col1, col2)
 
SELECT col3, col4
 
FROM table2;

Syntax to Insert in a New Table#

CREATE TABLE newTable (col1 <datatype>, <col2>)
 
SELECT col3, col4
 
FROM table2;

exemple

-- The lesson queries are reproduced below for convenient copy/paste into the terminal. 
 
-- Query 1
CREATE TABLE Names (name VARCHAR(20),
                    PRIMARY KEY(name));
 
-- Query 2
INSERT INTO Names(name) 
SELECT SecondName FROM Actors;
 
-- Query 3
INSERT IGNORE INTO Names(name) 
SELECT SecondName 
FROM Actors WHERE Id = 1;
 
-- Query 4
CREATE TABLE MyTempTable SELECT * FROM Actors;
 
-- Query 5
CREATE TABLE NamesWithDoBs ( 
Id INT AUTO_INCREMENT,
Name VARCHAR(20) NOT NULL DEFAULT "unknown",  
DoB DATE,  
PRIMARY KEY(Id), KEY(Name), KEY(DoB))  SELECT FirstName, DoB FROM Actors;
 
-- Query 6
CREATE TABLE CopyOfActors LIKE Actors;

Vues

Création d'une vue

Les vues sont des tables virtuelles créées à partir de requêtes SELECT. Elles permettent de présenter un sous-ensemble de données pertinent aux utilisateurs et de limiter l'affichage de lignes et de colonnes pour des raisons de sécurité. Les vues simplifient les requêtes en transformant des requêtes multitable en requêtes à table unique sur une vue. Elles sont stockées avec les tables dans la base de données. Une vue peut être créée à partir d'une seule table, de la jointure de deux tables ou d'une autre vue.

syntaxe

CREATE [OR REPLACE] VIEW view_name AS
 
SELECT col1, col2, …coln
 
FROM table
 
WHERE < condition>

Vues actualisables

Les vues peuvent être utilisées pour interroger et mettre à jour les données dans les tables sous-jacentes. Il est possible d'insérer, mettre à jour ou supprimer des lignes dans la table de base à l'aide d'une vue modifiable. Cependant, certaines conditions doivent être respectées pour que la vue puisse être mise à jour.

La mise à jour d'une vue est interdite si la requête SELECT qui crée la vue contient des fonctions d'agrégation, un mot-clé DISTINCT, des clauses LEFT JOIN, GROUP BY, HAVING ou UNION. De plus, les mises à jour de la table de base sont également interdites si une sous-requête fait référence à la même table que celle spécifiée dans la clause FROM.

syntaxe

UPDATE view
 
SET col1 = value1, col2 = value2,…coln = valuen
 
WHERE <condition>

exemple

-- The lesson queries are reproduced below for convenient copy/paste into the terminal. 
 
-- Query 1
CREATE VIEW ActorView AS
SELECT Id, FirstName, SecondName, NetWorthInMillions 
FROM Actors;
 
-- Query 2
UPDATE ActorView 
SET 
NetWorthInMillions = 250 
WHERE 
Id =1;
 
-- Query 3
SELECT Table_name, is_updatable
FROM information_schema.views
WHERE table_schema = 'MovieIndustry';
 
-- Query 4
DELETE FROM ActorView
WHERE Id = 11;

AVEC CHOIX OPTION

Une vue est un sous-ensemble de lignes d'une table. On peut insérer ou mettre à jour des lignes non visibles dans la vue. La clause "WITH CHECK OPTION" maintient la cohérence lors de la mise à jour d'une table via une vue modifiable. Cette clause interdit les opérations sur des lignes invisibles dans la vue.

syntaxe

CREATE [OR REPLACE] VIEW view_name AS
 
select_statement
 
WITH CHECK OPTION;

exemple

-- The lesson queries are reproduced below for convenient copy/paste into the terminal. 
 
-- Query 1
CREATE VIEW SingleActors AS 
SELECT FirstName, SecondName, DoB, Gender, MaritalStatus, NetWorthInMillions 
FROM Actors 
WHERE MaritalStatus = 'Single';
 
-- Query 2
INSERT INTO SingleActors (FirstName, SecondName, DoB, Gender, MaritalStatus,  NetWorthInMillions) 
VALUES ('Tom', 'Hanks', '1956-07-09', 'Male', 'Married', 350);
 
-- Query 3
CREATE OR REPLACE VIEW SingleActors AS 
SELECT FirstName, SecondName, DoB, Gender, MaritalStatus, NetWorthInMillions 
FROM Actors 
WHERE MaritalStatus = 'Single' 
WITH CHECK OPTION;
 
-- Query 4
INSERT INTO SingleActors (FirstName, SecondName, DoB, Gender, MaritalStatus, NetWorthInMillions) 
VALUES ('Matt', 'Damon', '1970-10-08', 'Male', 'Married', 160);
 
-- Query 5
INSERT INTO SingleActors (FirstName, SecondName, DoB, Gender, MaritalStatus, NetWorthInMillions) 
VALUES ('Charlize', 'Theron', '1975-08-07', 'Female', 'Single', 130);

Procédures stockées

Que sont les procédures stockées ?

Les procédures stockées sont des instructions SQL enregistrées sur le serveur MySQL. Elles peuvent être invoquées avec l'instruction CALL pour exécuter une requête et renvoyer le résultat. Les procédures stockées peuvent prendre des paramètres, inclure des instructions conditionnelles et appeler d'autres procédures.

Les avantages des procédures stockées sont la réduction du trafic entre les applications et le serveur, la réutilisation de code, les gains de performances grâce à la pré-compilation, et la possibilité de gérer l'accès aux tables en donnant accès uniquement aux procédures.

Cependant, les procédures stockées sont difficiles à déboguer, peuvent entraîner une surcharge de ressources et ne peuvent pas être annulées une fois modifiées.

Créer, supprimer et modifier une procédure stockée

Pour créer une procédure stockée, utilisez l'instruction CREATE PROCEDURE, spécifiez un nom et éventuellement des paramètres entre parenthèses. Le corps de la procédure est délimité par les mots clés BEGIN et END.

Pour supprimer une procédure stockée, utilisez l'instruction DROP PROCEDURE avec le privilège ALTER ROUTINE. Vous pouvez ajouter la clause facultative IF EXISTS pour éviter les erreurs si la procédure n'existe pas.

syntaxe

DELIMITER **
 
CREATE PROCEDURE procedure_name( parameter_list )
 
BEGIN
 
procedure_body
 
END**
 
DELIMITER ;

 
DROP PROCEDURE [IF EXISTS] procedure_name;

exemple

-- The lesson queries are reproduced below for convenient copy/paste into the terminal. 
 
-- Query 1
DELIMITER **
CREATE PROCEDURE ShowActors()
BEGIN
    SELECT *  FROM Actors;
END **
DELIMITER ;
 
-- Query 2
CALL ShowActors();
 
-- Query 3
SHOW PROCEDURE STATUS;
 
-- Query 4
SHOW PROCEDURE STATUS WHERE db = 'MovieIndustry';
 
-- Query 5
SELECT routine_name
FROM information_schema.routines
WHERE routine_type = 'PROCEDURE'
    AND routine_schema = 'sys';
 
-- Query 6        
DROP PROCEDURE IF EXISTS ShowActors;

Variable

Les variables sont des objets de données avec un nom associé, utilisées pour stocker des valeurs temporaires définies par l'utilisateur. Elles doivent être déclarées avant d'être utilisées, avec un type de données et une valeur par défaut facultative. L'affectation de valeurs se fait avec le mot-clé SET ou en utilisant SELECT INTO dans une requête. La portée d'une variable détermine sa durée de vie et son accessibilité. Dans une procédure stockée, la portée d'une variable est locale et se limite à l'intérieur de la procédure. Il est possible d'avoir plusieurs variables du même nom dans une procédure, tant qu'elles ont des portées différentes.

syntaxe

DECLARE VarName DataType ( VarLength ) [DEFAULT DefaultValue ] ;
 
SET VarName = valeur ;
 
SELECT NomCol
 
INTO NomVar
 
FROM NomTable ;

exemple

-- The lesson queries are reproduced below for convenient copy/paste into the terminal.
 
-- Query 1
DELIMITER **
CREATE PROCEDURE Summary()
BEGIN
DECLARE TotalM, TotalF INT DEFAULT 0;
DECLARE AvgNetWorth DEC(6,2) DEFAULT 0.0;
 
    SELECT COUNT(*) INTO TotalM
    FROM Actors
    WHERE Gender = 'Male';
    
    SELECT COUNT(*) INTO TotalF
    FROM Actors
    WHERE Gender = 'Female';
    
    SELECT AVG(NetWorthInMillions) INTO AvgNetWorth
    FROM Actors;
    
    SELECT TotalM, TotalF, AvgNetWorth;
END**
DELIMITER ;
 
-- Query 2
CALL Summary();

instruction IF

Les déclarations conditionnelles permettent d'exécuter du code uniquement si une condition est vraie. MySQL prend en charge les instructions de contrôle conditionnelles IF et CASE. L'instruction IF est utilisée pour exécuter des instructions en fonction d'une condition spécifique. La condition est évaluée à TRUE, FALSE ou NULL. Si la condition est vraie, les instructions entre IF-THEN et END IF sont exécutées. Plusieurs instructions peuvent être incluses dans un bloc IF, comme des appels de procédures stockées, des instructions SET, des boucles et des IF imbriqués.

syntaxe

IF Condition THEN
 
If_statements;
 
END IF;

IF Condition THEN
 
If_statements;
 
ELSE
 
else_statements;
 
END IF;

Déclencheurs

Que sont les déclencheurs ?

Les déclencheurs MySQL sont des programmes exécutés automatiquement en réponse à des événements de modification de table tels que les instructions INSERT, UPDATE et DELETE. Ils peuvent être invoqués avant ou après ces événements.

La norme SQL comprend deux types de déclencheurs : les déclencheurs au niveau de la ligne et les déclencheurs au niveau de l'instruction. Les premiers sont utilisés lorsqu'une ligne est insérée, mise à jour ou supprimée. Les déclencheurs au niveau de l'instruction sont appelés une seule fois, même si l'instruction affecte plusieurs lignes. MySQL ne prend en charge que les déclencheurs au niveau de la ligne.

Les déclencheurs offrent plusieurs avantages. Ils permettent de prévenir l'exécution de commandes DML non valides et sont utilisés pour gérer les erreurs de la base de données, assurant ainsi l'intégrité des données. Les déclencheurs offrent une alternative aux événements planifiés. Contrairement à ces derniers, les déclencheurs sont appelés à chaque exécution d'une commande DML. Ils sont également utiles pour l'audit et la conservation d'un journal des modifications effectuées sur une table.

Les limitations des déclencheurs sont les suivantes : surcharge accrue sur le serveur MySQL, difficulté de débogage, surcharge en cas de nombre élevé d'événements, absence de validations au niveau de la couche application. Les déclencheurs ne peuvent être associés qu'aux instructions INSERT, UPDATE et DELETE, et ne sont pas exécutés en cas d'utilisation d'autres instructions similaires telles que TRUNCATE pour supprimer toutes les lignes d'une table.

Création d'un déclencheur

Un déclencheur dans MySQL s'exécute automatiquement lorsqu'un événement spécifié se produit. Il existe six types d'événements auxquels un déclencheur peut être associé : avant ou après l'insertion, la mise à jour ou la suppression de données dans une table. Un déclencheur doit être lié à une table spécifique et posséder un nom unique. La création d'un déclencheur se fait à l'aide de l'instruction CREATE TRIGGER, en précisant le moment (BEFORE ou AFTER) et l'événement déclencheur (INSERT, UPDATE ou DELETE). Pour supprimer un déclencheur, on utilise l'instruction DROP TRIGGER, et lorsque la table associée est supprimée, les déclencheurs liés sont également supprimés. Si l'on souhaite supprimer un déclencheur appartenant à une autre base de données, il faut spécifier le nom de la base de données avant celui du déclencheur.

syntaxe

CREATE TRIGGER trigger_name trigger_time trigger_event
 
ON table_name
 
FOR EACH ROW
 
trigger_body
 
DROP TRIGGER [IF EXISTS] [database_name.]trigger_name;

Déclencheur insérer

Les déclencheurs d'insertion sont utilisés lors des opérations INSERT. Ils peuvent être exécutés avant ou après l'insertion. Dans les déclencheurs d'insertion, seul le mot-clé NEW permet d'accéder aux nouvelles valeurs. Le mot-clé OLD n'est pas utilisable car il n'y a pas de valeurs précédentes lors d'une insertion. Les déclencheurs BEFORE INSERT servent à la validation des données ou à la création d'une table récapitulative. Les déclencheurs AFTER INSERT servent à la création d'un journal d'activité ou à la copie de valeurs entre tables.

syntaxe

CREATE TRIGGER trigger_name [BEFORE | AFTER] INSERT
 
ON table_name
 
FOR EACH ROW
 
trigger_body

exemple

-- The lesson queries are reproduced below for convenient copy/paste into the terminal. 
 
-- Query 1
CREATE TABLE NetWorthStats (
AverageNetWorth DECIMAL(10,4)
);
INSERT INTO NetWorthStats(AverageNetWorth) 
Values ((SELECT AVG(NetWorthInMillions) FROM Actors));
 
-- Query 2
DELIMITER **
CREATE TRIGGER BeforeActorsInsert
BEFORE INSERT ON Actors 
FOR EACH ROW
BEGIN
  DECLARE TotalWorth, RowsCount INT;
          
  SELECT SUM(NetWorthInMillions) INTO TotalWorth
  FROM Actors;
  SELECT COUNT(*) INTO RowsCount
  FROM Actors;
 
  UPDATE NetWorthStats
  SET AverageNetWorth = ((Totalworth + new.NetWorthInMillions) / (RowsCount+1));
END **
DELIMITER ;
 
-- Query 3
INSERT INTO Actors (FirstName, SecondName, DoB, Gender, MaritalStatus, NetWorthInMillions) 
VALUES ('Charlize', 'Theron', '1975-08-07', 'Female', 'Single', 130);
 
SELECT * FROM NetWorthStats;
 
-- Query 4
CREATE TABLE ActorsLog (
    LogId INT AUTO_INCREMENT PRIMARY KEY,
    ActorId INT NOT NULL,
    FirstName VARCHAR(20),    
    LastName VARCHAR(20),
    DateTime DATETIME DEFAULT NULL,
    Event VARCHAR(50) DEFAULT NULL
);
 
-- Query 5
CREATE TRIGGER AfterActorsInsert 
AFTER INSERT ON Actors
FOR EACH ROW 
INSERT INTO ActorsLog
SET ActorId = NEW.Id, 
    FirstName = New.FirstName, 
    LastName = NEW.SecondName, 
    DateTime = NOW(), 
    Event = 'INSERT';
 
-- Query 6
INSERT INTO Actors (FirstName, SecondName, DoB, Gender, MaritalStatus, NetWorthInMillions) 
VALUES ('Matt', 'Damon', '1970-10-08', 'Male', 'Married', 160);
 
SELECT * FROM ActorsLog;

Déclencheur mise à jour

Les déclencheurs de mise à jour sont exécutés automatiquement lorsqu'une table est mise à jour. Ils peuvent s'exécuter avant ou après la mise à jour et utilisent les mots clés NEW et OLD pour accéder aux valeurs des colonnes. Lorsqu'ils s'exécutent avant, seule la valeur NEW peut être mise à jour. Lorsqu'ils s'exécutent après, nous ne pouvons que lire les valeurs NEW et OLD, sans les modifier.

Les déclencheurs de mise à jour ne peuvent pas être utilisés avec les vues. Les déclencheurs BEFORE UPDATE sont utiles pour valider les données, effectuer des corrections ou avertir l'utilisateur avant une mise à jour. Ils permettent également de conserver un journal des modifications. Les déclencheurs AFTER UPDATE peuvent également servir à maintenir un journal des modifications ou à mettre à jour un tableau récapitulatif.

syntaxe

CREATE TRIGGER trigger_name [BEFORE | AFTER] UPDATE
 
ON table_name
 
FOR EACH ROW
 
trigger_body

exemple

-- The lesson queries are reproduced below for convenient copy/paste into the terminal. 
 
-- Query 1
CREATE TABLE DigitalActivity (
RowID INT AUTO_INCREMENT PRIMARY KEY,
ActorID INT NOT NULL,
Detail VARCHAR(100) NOT NULL,
UpdatedOn TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
 
-- Query 2
DELIMITER **
 
CREATE TRIGGER BeforeDigitalAssetUpdate
BEFORE UPDATE
ON DigitalAssets 
FOR EACH ROW
BEGIN
 DECLARE errorMessage VARCHAR(255);
 
 IF NEW.LastUpdatedOn < OLD.LastUpdatedOn THEN
   SET errorMessage = CONCAT('The new value of LastUpatedOn column: ', 
     NEW.LastUpdatedOn,' cannot be less than the current value: ', 
     OLD.LastUpdatedOn);
 
   SIGNAL SQLSTATE '45000'
   SET MESSAGE_TEXT = errorMessage;
 END IF;
 
 IF NEW.LastUpdatedOn != OLD.LastUpdatedOn THEN
   INSERT into DigitalActivity (ActorId, Detail)
   VALUES (New.ActorId, CONCAT('LastUpdate value for ',NEW.AssetType,
          ' is modified from ',OLD.LastUpdatedOn, ' to ', 
          NEW.LastUpdatedOn));   
 END IF;
END **
DELIMITER ;
 
-- Query 3
UPDATE DigitalAssets 
SET LastUpdatedOn = '2020-02-15 22:10:45'
WHERE ActorID = 2 AND Assettype = 'Website';
 
UPDATE DigitalAssets 
SET LastUpdatedOn = '2018-01-15 22:10:45'
WHERE ActorID = 5 AND AssetType = 'Pinterest';
 
SELECT * FROM DigitalActivity;
 
-- Query 4
DELIMITER **
CREATE TRIGGER AfterActorUpdate
AFTER UPDATE ON Actors 
FOR EACH ROW
BEGIN
   DECLARE TotalWorth, RowsCount INT;
   INSERT INTO ActorsLog
   SET ActorId = NEW.Id, FirstName = New.FirstName, LastName =  NEW.SecondName, DateTime = NOW(), Event = 'UPDATE';
 
  IF NEW.NetWorthInMillions != OLD.NetWorthInMillions THEN
    SELECT SUM(NetWorthInMillions) INTO TotalWorth
    FROM Actors;
    SELECT COUNT(*) INTO RowsCount
    FROM Actors;
 
    UPDATE NetWorthStats
    SET AverageNetWorth = ((Totalworth) / (RowsCount));
END IF;
END **
DELIMITER ;
 
-- Query 5
SELECT * FROM NetWorthStats;
 
UPDATE Actors
SET NetWorthInMillions = '100'
WHERE Id = 5;
 
SELECT * FROM NetWorthStats;
SELECT * FROM ActorsLog;
 
-- Query 6
UPDATE Actors
SET MaritalStatus = 'Single'
WHERE Id = 7;
 
SELECT * FROM NetWorthStats;
SELECT * FROM ActorsLog;

Déclencheur suppression

Les déclencheurs de suppression d'une table sont déclenchés lorsqu'une instruction DELETE est exécutée. Tout comme les déclencheurs d'insertion et de mise à jour, les déclencheurs de suppression peuvent être exécutés avant ou après la suppression d'un enregistrement de la table. Étant donné que l' instruction DELETE est destinée à supprimer un enregistrement, les colonnes n'ont pas de valeur NEW . Seule la valeur OLD d'une colonne est accessible et celle-ci ne peut pas non plus être mise à jour.

Les déclencheurs de suppression peuvent être utilisés pour archiver les enregistrements supprimés. Dans certains cas, les déclencheurs BEFORE DELETE sont utilisés pour arrêter une opération de suppression non valide. Par exemple, s'il existe deux tables pour les informations sur le cours et l'inscription, il n'est pas logique de supprimer un cours lorsque des étudiants y sont inscrits. Les déclencheurs de suppression peuvent également être utilisés pour mettre à jour un tableau récapitulatif ou maintenir un journal des modifications après la suppression d'enregistrements du tableau. Les déclencheurs de suppression ne sont pas disponibles pour les vues.

syntaxe

CREATE TRIGGER trigger_name [BEFORE | AFTER] DELETE
 
ON table_name
 
FOR EACH ROW
 
trigger_body

exemple

-- The lesson queries are reproduced below for convenient copy/paste into the terminal. 
 
-- Query 1
CREATE TABLE ActorsArchive (
       RowId INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
       DeletedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP)
AS (SELECT * FROM Actors WHERE 1=2);
 
-- Query 2
DELIMITER **
CREATE TRIGGER BeforeActorsDelete
BEFORE DELETE
ON Actors
FOR EACH ROW
BEGIN
  INSERT INTO ActorsArchive 
         (Id, Firstname, SecondName, DoB, Gender, MaritalStatus, NetWorthInMillions)
  VALUES (OLD.Id, OLD.Firstname, OLD.SecondName, OLD.DoB, OLD.Gender, OLD.MaritalStatus, OLD.NetWorthInMillions);
END **
DELIMITER ;
 
-- Query 3
DELETE FROM Actors
WHERE NetWorthInMillions < 150;
 
-- Query 4
DELIMITER **
CREATE TRIGGER AfterActorsDelete
AFTER DELETE ON Actors 
FOR EACH ROW
BEGIN
   DECLARE TotalWorth, RowsCount INT;
   
   INSERT INTO ActorsLog
   SET ActorId = OLD.Id, FirstName = OLD.FirstName, LastName =  OLD.SecondName, DateTime = NOW(), Event = 'DELETE';
   SELECT SUM(NetWorthInMillions) INTO TotalWorth
   FROM Actors;
   SELECT COUNT(*) INTO RowsCount
   FROM Actors;
 
   UPDATE NetWorthStats
   SET AverageNetWorth = ((Totalworth) / (RowsCount));
END **
DELIMITER ;
 
-- Query 5
DELETE FROM Actors
WHERE Id = 13;
 
SELECT * FROM NetWorthStats;
SELECT * FROM ActorsLog;

Divers

Transactions

Une transaction de base de données représente une unité de travail cohérente et fiable indépendante des autres transactions. Elle est essentielle pour éviter les incohérences lorsque plusieurs utilisateurs interagissent avec la base de données. Par exemple, lors d'un transfert d'argent entre comptes bancaires, une transaction permet d'effectuer la lecture et la mise à jour du solde de manière atomique, évitant ainsi les erreurs. Les transactions regroupent des instructions SQL en un ensemble indivisible qui réussit ou n'a aucun effet sur la base de données.

Syntaxe pour démarrer et valider une transaction

START TRANSACTION;
 
** – SQL statements
 
COMMIT;

Syntaxe pour démarrer et annuler une transaction

START TRANSACTION;
 
** – SQL statements
 
ROLLBACK;

exemple

-- The lesson queries are reproduced below for convenient copy/paste into the terminal. 
 
-- Query 1
START TRANSACTION;
UPDATE Actors 
SET Id = 100 
WHERE FirstName = "Brad";
COMMIT;
 
-- Query 2
START TRANSACTION;
UPDATE Actors 
SET Id = 200 
WHERE FirstName = "Tom";
ROLLBACK;
 
-- Query 3
SHOW ENGINES;

Clés étrangères

Cette leçon porte sur les clés étrangères. Les clés étrangères sont prises en charge par certains moteurs de stockage, tels que InnoDB dans MySQL, mais pas par MyISAM. Les clés étrangères permettent de lier deux tables en utilisant des colonnes communes. Dans notre exemple, la table "DigitalAssets" contient des comptes en ligne pour les acteurs répertoriés dans la table "Actors". Ces deux tables sont liées par les colonnes "ID" et "ActorID". L'utilisation de clés étrangères garantit que chaque ligne ajoutée à la table "DigitalAssets" a une correspondance dans la table "Actors" et que les lignes associées sont supprimées lorsque l'acteur est supprimé de la table "Actors". Cette relation est un à plusieurs, avec la table "Actors" comme table parent et la table "DigitalAssets" comme table enfant. syntaxe

CREATE TABLE childTable (
 
col1 <dataType>,
 
col2 <dataType>,
 
CONSTRAINT fkConstraint
 
FOREIGN KEY (col2)
 
REFERENCES parentTable(referencedCol);

exemple

-- The lesson queries are reproduced below for convenient copy/paste into the terminal. 
 
-- Query 1
ALTER TABLE DigitalAssets
ADD FOREIGN KEY (ActorId)
REFERENCES Actors(Id);
 
-- Query 2
INSERT INTO DigitalAssets
VALUES ("www.dummy.url", "instagram", "2030-01-01 00:00:00", 100);

Last updated on June 21, 2024

Querry vs XSLT Cycle de vie des données