Cours1.pdf

Base de données et interopérabilité Département Informatique École supérieure d'ingénieurs Léonard-de-Vinci (ESILV) 09 Février, 2023 Ruiwen HE [email protected] i n t e r o p é r a b i l i t é & B D D 2 Ruiwen HE Enseignant chercheur en informatique Doctorat en informatique Spécialisation en visi...

Base de données et interopérabilité Département Informatique École supérieure d'ingénieurs Léonard-de-Vinci (ESILV) 09 Février, 2023 Ruiwen HE [email protected] i n t e r o p é r a b i l i t é & B D D 2 Ruiwen HE Enseignant chercheur en informatique Doctorat en informatique Spécialisation en vision par ordinateur Domaine de recherche : Traitement d'images/vidéos basé sur l'apprentissage profond Classification Segmentation Détection/suivi d'objets Génération d'images de synthèse 3 B D D & i n t e r o p é r a b i l i t é [email protected] Contact [email protected] +33 1 81 00 29 63 L.405 [email protected] i n t e r o p é r a b i l i t é & B D D 4 Déroulé 6 CMs CM1 : Rappels des cours précédents CM2 : Les Vues, Index, Sequence CM3 : Transactions CM4 : PL/SQL Procedural Language/SQL CM5 : JSON/XML CM6 : Interopérabilité avec le langage C# 30 TDs de 1h30 (15 TD et 15 TDo) Le découpage respecte donc le nouveau format d’une 1h30 5 B D D & i n t e r o p é r a b i l i t é [email protected] Evaluation du module Deux TDs notés (30%) Examen (70%) RAPPELS DES COURS PRÉCÉDENTS PÔLE LÉ ONARD DE VINCI [email protected] i n t e r o p é r a b i l i t é & B D D 7 Base de données (DataBase, BDD) Définition d'une base de données : Une base de données est un ensemble d’informations structurées mémorisées sur un support persistant. Caractéristiques de base d'une base de données : Les données sont organisées, décrites et stockées selon certain modèle de données. Elles peuvent être partagées par différents utilisateurs. Moins de redondance Plus grande indépendance des données Facilement extensible [email protected] i n t e r o p é r a b i l i t é & B D D 8 Système de Gestion de Base de Données (DataBase Management System, SGBD) La gestion et l'accès à une base de données sont assurés par un ensemble de programmes qui constituent le Système de gestion de base de données (SGBD). Un SGBD doit permettre l'ajout, la modification et la recherche de données. Un système de gestion de bases de données héberge généralement plusieurs bases de données, qui sont destinées à des objectifs différents. Actuellement, la plupart des SGBD fonctionnent selon un mode client/serveur. Le serveur (sous-entendu la machine qui stocke les données) reçoit des requêtes de plusieurs clients et ceci de manière concurrente. Le serveur analyse la requête, la traite et retourne le résultat au client. [email protected] i n t e r o p é r a b i l i t é & B D D 9 MCD (Modèle Conceptuel des Données) Le MCD est une représentation graphique de haut niveau qui permet facilement et simplement de comprendre comment les différents éléments sont liés entre eux. [email protected] i n t e r o p é r a b i l i t é & B D D 10 MCD (Modèle Conceptuel des Données) Les entités Les propriétés Les relations(Associations) qui expliquent et précisent comment les entités sont reliées entre elles (les ovales avec leurs « pattes » qui se rattachent aux entités) ; Les cardinalités (les petits chiffres au dessus des « pattes »). [email protected] i n t e r o p é r a b i l i t é & B D D 11 Modèle de base de données relationnelle Une base de données relationnelle (RDB) est une méthode de structuration des informations dans des tables, des lignes et des colonnes. Une RDB permet d'établir des liens (ou des relations) entre des informations en joignant des tables, afin de comprendre et d'obtenir facilement des insights sur la relation entre différents points de données. [email protected] i n t e r o p é r a b i l i t é & B D D 12 Modèle de base de données relationnelle Supposons que vous disposiez d'une table Customer (Client) et d'une table Order (Commande). La table Customer (Client) contient des données sur le client : Numéro client (clé primaire) Nom du client Adresse de facturation Adresse de livraison La table Order (Commande) contient des informations transactionnelles sur une commande : ID de commande (clé primaire) Numéro client (clé étrangère) Date de la commande Date d'expédition État de la commande [email protected] i n t e r o p é r a b i l i t é SQL est le langage standard des systèmes de bases de données relationnelles. Data Definition Language (DDL): Langage de Définition de données (LDD). Data Manipulation Language (DML) : Langage de Manipulation de données (LMD). B D D & SQL : Structured Query Language Data Query Language (DQL): Langage de Requête (LR). 13 [email protected] i n t e r o p é r a b i l i t é & B D D 14 SQL : Structured Query Language Les mots-clés SQL ne sont pas sensibles à la casse. Cependant, pour différentes bases de données, pour les noms de tables et de colonnes, certaines bases de données sont sensibles à la casse et d'autres ne le sont pas. Pour une même base de données, certaines sont sensibles à la casse sous Linux et d'autres ne le sont pas sous Windows. Petit conseil: Les mots-clés SQL sont toujours en majuscules pour plus de visibilité, et les noms de tables et de colonnes sont en minuscules. [email protected] MySQL est un système de gestion de base de données relationnelle open source proposé par Oracle. Les développeurs peuvent télécharger et utiliser MySQL sans payer de frais de licence. ProceduralLanguage-SQL (PL/SQL) est un langage fondé sur les paradigmes de programmation procédurale et structurée. Il est propriétaire, créé par Oracle et utilisé dans le cadre de bases de données relationnelles. B D D & i n t e r o p é r a b i l i t é SQL : Structured Query Language 15 Le Transact-SQL (T-SQL) est une extension propriétaire de Sybase et Microsoft au langage SQL. [email protected] CREATE DATABASE [IF NOT EXISTS] database_name; USE database_name SHOW database_name B D D & i n t e r o p é r a b i l i t é Création et suppression de bases de données 16 DROP DATABASE [IF EXISTS] database_name; [email protected] i n t e r o p é r a b i l i t é & B D D 17 Création d’un table CREATE TABLE table_name( column1 datatype, column2 datatype, column3 datatype,..... columnN datatype, PRIMARY KEY( one or more columns ) ); [email protected] i n t e r o p é r a b i l i t é & B D D 18 Exemple CREATE TABLE sc( sid VARCHAR(10), # student identification cid VARCHAR(10), # course identification score DECIMAL(18,1), #notes ); [email protected] i n t e r o p é r a b i l i t é & B D D 19 Suppression d’un table DROP TABLE table_name; TRUNCATE TABLE table_name; [email protected] i n t e r o p é r a b i l i t é & B D D 20 Syntaxe SQL Select SELECT * FROM table_name SELECT score FROM sc SELECT score, sid, cid FROM sc [email protected] i n t e r o p é r a b i l i t é & B D D 21 Syntaxe SQL SELECT DISTINCT La syntaxe SELECT DISTINCT est utilisée pour ne renvoyer que des valeurs différentes. Dans un table, une colonne contient généralement de nombreuses valeurs en double ; parfois, vous souhaitez simplement dresser la liste des valeurs différentes. SELECT DISTINCT column1, column2,... FROM table_name; 22 B D D & i n t e r o p é r a b i l i t é [email protected] Exemple [email protected] i n t e r o p é r a b i l i t é & B D D 23 Exemple SELECT country FROM customers; SELECT DISTINCT country FROM customers; @Aline Ellul Syntaxe SQL SELECT avec condition : WHERE Utilisation d’opérateurs de comparaison : < , > , = , B D D & i n t e r o p é r a b i l i t é Combinaisons logiques de prédicats possible (AND/OR) Recherche textuelle : LIKE ‘A%’ ou ’%A’ ou ’A%A’ Recherche dans un intervalle de valeurs : BETWEEN val1 AND val2 présence de la valeur d’un attribut dans une liste, IN (liste de valeurs) Cas de la valeur NULL : IS NULL ou IS NOT NULL Attention : = NULL est incorrect 24 [email protected] i n t e r o p é r a b i l i t é & B D D 25 Syntaxe SQL SELECT avec condition SELECT * FROM table_name WHERE condition; SELECT * FROM customers WHERE country=‘Mexico’; [email protected] i n t e r o p é r a b i l i t é & B D D 26 Insérer dans un table INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...); CREATE TABLE teacher ( tid VARCHAR(10), tname VARCHAR(10)); INSERT INTO teacher VALUES('01' , 'Emma LOPEZ’); INSERT INTO teacher VALUES('02' , 'Anna LOPEZ’); INSERT INTO teacher VALUES('03' , 'Raphael ANDRE'); [email protected] i n t e r o p é r a b i l i t é & B D D 27 Insérer dans un table SQL INSERT INTO SELECT: il nous permet de copie les données d'une table et les insère dans une table existante. Les lignes existantes dans la table cible ne sont pas affectées. INSERT INTO table2 SELECT * FROM table1; INSERT INTO table2 (column_name(s)) SELECT column_name(s) FROM table1; [email protected] SQL SELECT INTO SELECT * INTO newtable [IN externaldb] FROM table1; B D D & i n t e r o p é r a b i l i t é Insérer dans un table 28 SELECT column_name(s) INTO newtable [IN externaldb] FROM table1; [email protected] i n t e r o p é r a b i l i t é & B D D 29 Modification d’un table SQL UPDATE (mise à jour des enregistrements d'une table) UPDATE table_name SET column1 = value1, column2 = value2,... WHERE condition; Attention Soyez prudent lorsque vous mettez à jour des enregistrements dans une table ! Faites attention à la clause WHERE dans l'instruction SQL UPDATE ! La clause WHERE spécifie quelles lignes doivent être mises à jour. Si la clause WHERE est omise, toutes les lignes seront mises à jour ! [email protected] i n t e r o p é r a b i l i t é & B D D 30 Modification d’un table SQL UPDATE (mise à jour des enregistrements d'une table) UPDATE table_name SET column1 = value1, column2 = value2,... WHERE condition; Attention Soyez prudent lorsque vous mettez à jour des enregistrements dans une table ! Faites attention à la clause WHERE dans l'instruction SQL UPDATE ! La clause WHERE spécifie quelles lignes doivent être mises à jour. Si la clause WHERE est omise, toutes les lignes seront mises à jour ! [email protected] i n t e r o p é r a b i l i t é & B D D 31 Modification d’un table ALTER TABLE est utilisée pour ajouter, supprimer ou modifier des colonnes dans une table existante. Ajout d’une colonne dans une table ALTER TABLE table_name ADD column_name datatype; Suppression d’une colonne dans une table ALTER TABLE table_name DROP COLUMN column_name; Modifier le type de données d'une colonne dans un table ALTER TABLE table_name MODIFY COLUMN column_name datatype; [email protected] i n t e r o p é r a b i l i t é & B D D 32 Alias SQL En utilisant AS, vous pouvez spécifier des alias (Alias) pour les noms de tables ou de colonnes. Les alias SQL sont utilisés pour fournir des noms temporaires aux tables ou aux colonnes d'une table ; les noms réels des tables dans la base de données ne changent pas. Les alias SQL sont souvent utilisés pour rendre les noms de colonnes plus lisibles. SQL Un alias n'existe que pour la durée d'une requête. Un alias de table est utilisé pour renommer une table dans une instruction SQL spécifique. Un alias de colonne est utilisé pour renommer les colonnes d'une table pour une requête SQL spécifique. [email protected] i n t e r o p é r a b i l i t é & B D D 33 Contraintes Les contraintes sont des règles qui agissent sur les colonnes d'un table de données afin de limiter le type de données qu'il contient. La présence de contraintes garantit l'exactitude et la fiabilité des données dans la base de données. Contrainte NOT NULL : garantit que les données de la colonne ne peuvent pas avoir de valeur NULL. Contrainte DEFAULT : fournit la valeur par défaut lorsque les données de la colonne ne sont pas spécifiées. Contrainte UNIQUE : garantit que toutes les données de la colonne sont différentes. Contrainte de clé primaire : identifie de manière unique la ligne/l'enregistrement dans la table de données. Contrainte de clé étrangère : identifie de manière unique une ligne/un enregistrement dans d'autres tables. Contrainte CHECK : cette contrainte garantit que toutes les valeurs d'une colonne répondent à une certaine condition. Index : utilisé pour créer ou récupérer rapidement des données dans la base de données. [email protected] i n t e r o p é r a b i l i t é & B D D 34 Définition des contraintes by SQL CHECK La contrainte CHECK stipule que la colonne "P_Id" ne doit contenir que des nombres entiers supérieurs à 0. [email protected] i n t e r o p é r a b i l i t é & B D D 35 Définition des contraintes by SQL CHECK Nommez la contrainte CHECK et définissez des contraintes CHECK pour plusieurs colonnes. [email protected] i n t e r o p é r a b i l i t é & B D D 36 Contraintes SQL CHECK sur ALTER TABLE /Annulation de la contrainte CHECK Pour créer une contrainte CHECK sur la colonne "P_Id" lorsque la table a déjà été créée Si vous devez annuler la contrainte CHECK B D D & i n t e r o p é r a b i l i t é @Aline Ellul SELECT : ORDER BY 37 SELECT attribut1, attribut2.... attributs à projeter) FROM table1, table 2 WHERE conditions sur les attributs ORDER BY attribut1, attribut 2 => Projection (liste des => Relations / Tables => Sélection / Jointure ORDER BY attribut DESC (ou ASC) pour descendant ou ascendant (ASC par défaut) [email protected] i n t e r o p é r a b i l i t é & B D D 38 SQL Jointures Le joint SQL est utilisé pour combiner les lignes de deux tables ou plus sur la base de champs communs entre ces tables. En d'autres termes, une table primaire est identifiée comme l'ensemble de résultats, puis les lignes des autres tables sont sélectivement "jointes" à l'ensemble de résultats de la table primaire. [email protected] i n t e r o p é r a b i l i t é & B D D 39 SQL Jointures Notez que la colonne "CustomerID" de la table "Orders" pointe vers les clients de la table "Customers". Les deux tables sont liées par la colonne "CustomerID". [email protected] i n t e r o p é r a b i l i t é & B D D 40 SQL Jointures SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID; [email protected] i n t e r o p é r a b i l i t é & B D D Différents SQL Jointures INNER JOIN : renvoie les lignes s'il existe au moins une correspondance dans la table. LEFT JOIN : renvoie toutes les lignes du table de gauche même s'il n'y a pas de correspondance dans le table de droite Right JOIN : renvoie toutes les lignes de la table de droite même s'il n'y a pas de correspondance dans la table de gauche. FULL JOIN : renvoie les lignes tant qu'il existe une correspondance dans l'une des tables. 41 42 B D D & i n t e r o p é r a b i l i t é [email protected] SQL FULL OUTER JOIN [email protected] i n t e r o p é r a b i l i t é & B D D 43 NATURAL JOIN Une jointure naturelle est un type particulier de jointure à valeur égale. Elle exige que les composants à comparer dans deux tables relationnelles soient le même ensemble d'attributs et que les colonnes d'attributs en double soient supprimées du résultat. SELECT *FROM course NATURAL JOIN teacher [email protected] i n t e r o p é r a b i l i t é & B D D 44 Group By GROUP BY est utilisée conjointement avec la fonction Aggregate pour regrouper un ensemble de résultats sur la base d'une ou plusieurs colonnes. SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s); [email protected] Group By AVG() pour calculer la moyenne sur un ensemble d’enregistrements B D D & i n t e r o p é r a b i l i t é COUNT() pour compter le nombre d’enregistrements sur une table ou une colonne distincte MAX() pour récupérer la valeur maximale d’une colonne sur un ensemble de lignes. Cela s’applique à la fois pour des données numériques ou alphanumériques MIN() pour récupérer la valeur minimale de la même manière que MAX() SUM() pour calculer la somme sur un ensemble d’enregistrements 45 [email protected] i n t e r o p é r a b i l i t é & B D D 46 Group By: example SELECT ssex, COUNT(*) FROM student GROUP BY ssex; SELECT COUNT(*), ssex FROM student WHERE YEAR(student.sage)