Bases de données (Cours) PDF

Bases de données J O NAT HA N NAVE Présentation 2009 à 2011 – GEII à l’IUT de Haguenau 2011 à 2015 – FIP EII à Télécom Physique Strasbourg / Siemens Depuis 2015 - Ingénieur Méthodes tests des positionneurs de vannes et transmetteurs de température chez Siemens Programme Qu’est-ce qu’un...

Bases de données J O NAT HA N NAVE Présentation 2009 à 2011 – GEII à l’IUT de Haguenau 2011 à 2015 – FIP EII à Télécom Physique Strasbourg / Siemens Depuis 2015 - Ingénieur Méthodes tests des positionneurs de vannes et transmetteurs de température chez Siemens Programme Qu’est-ce qu’un SGBD La conception d’une base de données Créer une BDD Utiliser un BDD L’utilisation de base de données dans le milieu Industriel Les Cours Lundi 9/12 14h-18j Amphi 2 Grp TD1 Grp TD2 Jeudi 12/12 Jeudi 12/12 10h30-12h30 8h30-10h30 B1TP04 B1TP04 Vendredi 13/12 Vendredi 13/12 8h30-10h30 10h30-12h30 B1TP04 B1TP04 Grp TP1 Grp TP2 Grp TP3 Mercerdi 18/12 Mardi 17/12 Vendredi 13/12 8h30-12h30 14-18h 14h-18h B1TP01_LANG A3TP01_INFO B1TP02_LANG Vendredi 20/12 Jeudi 19/12 Vendredi 20/12 8h30-12h30 8h30-12h30 14h-18h B1TP05_INFO A3TP01_INFO B1TP03_PROJ Mercerdi 22/01 Lundi 20/01 Jeudi 23/01 8h30-12h30 14h-18h 8h30-12h30 A1TP04_INFO A1TP04_INFO B1TP02_LANG SGBD: Systèmes de Gestion de Bases de Données Fichier VS BDD Lourdeur d’accès aux données. Risques élevés pour l’intégrité et la sécurité Pas de contrôle de concurrence Performances. Fichier VS BDD Accès aux fichiers de la base, garantissant leur intégrité, contrôlant les opérations concurrentes, optimisant les recherches et mises à jour. Interactions avec les applications et utilisateurs, grâce à des langages d’interrogation et de manipulation à haut niveau d’abstraction. 2 Principaux systèmes: - Base de données relationnel: SQL - Base de données d’entités: No-SQL Les bases No-SQL - Convient au stockage d’objet/document sous forme Clé/Valeur. - Parfait pour du stockage cloud - Totalement scalable Les bases SQL - Plus difficile à gérer en cloud - Les données sont structurées et liées - Structure relationnel - Base de données : L’ensemble du système de stockage. - Table: Ensemble de Données - Champ : Un « type » de donnée - Entrée : Une Donnée Les Bases avantages Stockage de manière ordonnée Simplicité d’accès Utilisation d’un langage commun aux SGBD: SQL Des systèmes qui permettent de gérer une très grandes quantités de données. Gestion du niveau d’accès Partage: accès concurrent. Sécurité: reprise après panne, journalisation Performance d’accès: index (hashage, arbres balancés...)  Les différents SGBD Les grands acteurs: MySQL : libre et gratuit, c'est probablement la BDD la plus connu. PostgreSQL : libre et gratuit comme MySQL. SQLite: libre et gratuit, très léger mais très limité en fonctionnalités. Oracle : utilisé par les très grosses entreprises. Microsoft SQL Server : le SGBD de Microsoft. La conception d’une base de données  Les étapes de la conception Données Modèle Entité association Le modèle entité-Association Données Le modèle relationnel Transformation en relationnel Création du script Schéma logique relationnel  Le modèle Entité-Association En anglais: Entity Relationship. Formalisme retenu par l'ISO. Les concepts: Les Entités Les propriétés L’identifiant Les associations Les cardinalités Les différents modèles de données  Le modèle Entité-Association LE CO NCE PT D’ ENTITÉ Représentation d’un objet matériel ou immatériel Par exemple un employé, un projet, un bulletin de paie Nom d entité CP nom de l attribut nom de l attribut nom de l attribut Le modèle Entité-Association -> Concepts L’ IDE NTIFIA NT L E S A S S O CI AT IO NS Propriété unique qui permet de Représentation d’un lien entre définir une instance dans une deux entités ou plus. entité. Exemple : Numéro de série En BDD: Clé primaire / Primary Key -> Unicité dans l’entité Le modèle Entité-Association -> Concepts L E S CA R DINA LIT ÉS La cardinalité d’une association pour une entité constituante est constituée d’une borne minimale et d’une borne maximale Minimale : nombre minimum de fois qu’une occurrence de l’entité participe aux occurrences de l’association, généralement 0 ou 1 Maximale : nombre maximum de fois qu’une occurrence de l’entité participe aux occurrences de l’association, généralement 1 ou n  Le modèle Entité-Association ->Les cardinalités UNI TA IR E MULTIPLE SIMPLE De 0,1 : une entité de A peut être reliée à aucune ou à une De 0 à plusieurs (0,N) : une entité de A peut être seule entité de B reliée à aucune ou à plusieurs entités de B … A B A B 0,1 0,n De 1,1 : une entité de A est reliée à une seule entité de B LIEN MAILLÉ N:M Une instance de A peut être associée à plusieurs A B instances de B et inversement 1 A B n m  Le modèle Entité-Association ->Les cardinalités L E CT U RE E X E MP LE 1 Verbe Dirige A B Travaille n m Employé Service * 1 * * Participe Une instance de A est associé à une instance B par n. * Une instance de B est associé à une instance A Projet par m Créer une BDD  Les types T Y PE S N U M ÉRIQ UES DECIMAL et NUMERIC : Prennent 2 paramètres La précision définit le nombre de chiffres significatifs stockés, donc les 0 à gauche ne comptent pas. En effet, 0024 est équivalent à 24. Il n'y a donc que deux chiffres significatifs dans 0024. L'échelle définit le nombre de chiffres après la virgule. FLOAT, DOUBLE (8 o), REAL (4 o) Les types T Y PE S TE M PO REL S DATE : 'AAAA-MM-JJ‘ DATETIME: 'AAAA-MM-JJ HH:MM:SS‘ TIME: 'HH:MM:SS‘ YEAR: ‘YY’ ou ‘YYYY’ TIMESTAMP: Temps Machine (01-01-1970 à 00h00) Les types TY PE S A L PH ANUMÉRI Q UES CHAR et VARCHAR permettent de stocker du Texte relativement court (Max 255) Plus de 255 caractères -> TEXT Les types B IE N C H O ISI R SE S T Y PE S Permet d’accélérer les recherches car la base construit des « arbres » d’indexation. Les différents type index: Primary KEY UNIQUE => Valeur est unique dans la table FULLTEXT: Unique sur Char, varchar ou Text. Gestion des tables CR É AT IO N DE TA B L E S Création de table SY N TAXE CREATE TABLE [NomTable] ( [Colonne1] Type [Options], [Colonne2] Type, … Définir dés le départ les clés primaire : PRIMARY KEY Restreindre les Valeurs non nulle : NOT NULL Donner des valeurs par défaut : DEFAULT([VALUE]) LES AUTO INCREMENTS : AUTO_INCREMENT Clés primaires et clés étrangères CL É S PR IMA I R ES Contrainte d'unicité : ceci ressemble fort à un index UNIQUE. Composée d'une ou plusieurs colonnes : comme les index, les clés peuvent donc être composites. Permet d'identifier chaque ligne de manière unique : dans ce cas, une clé primaire ne peut pas être NULL. A JO U TER/SU PPRIME R U N E C L É PR I M AI RE A PR È S C R É ATI ON Clés primaires et clés étrangère CL É S É T R A NG ÈR ES Les clés étrangères ont pour fonction principale la vérification de l'intégrité de votre base. Elles permettent des créer des liens entres les tables Peut être créée lors de la création de la table avec le terme FOREIGN KEY, mais la table distante doit déjà exister. A JO U TER/SU PPRIME R U N E C L É D I STANT E Modification de table A JO U T E T SU PPR ESSI ON D'U N E C O LO NNE ALTER TABLE [NomTable] ADD COLUMN [NAME] TYPE ALTER TABLE [NomTable] DROP COLUMN [NAME] ALTER TABLE [NomTable] ALTER COLUMN [NAME] [NEW TYPE] Insertion de données IN SE RTION D E TO U T E S L E S VA L E URS INSERT INTO [NomTable] VALUES (Value1,Value2…) IN SE RTION PA R T IE LLE INSERT INTO [NomTable](Colonne1,Colonnes2…) VALUES (Value1,Value2…) IN SE RTION M U LT IPLE INSERT INTO [NomTable](Colonne1,Colonnes2…) VALUES (Value1,Value2…), (Value1,Value2…) Utilisation de la BDD L E R E QUÊ TAGE S QL Les requêtes simples L E L A NG AG E SQL Interrogation: SELECT … FROM… SE L E CT WHE R E [Attribut1]… : Sélection Critères de sélection Spécifique WHERE ‘’Attribut1’’= ‘2’ * : toutes les colonnes Fonctions : Max(Attribut1) Min(Attribut1) SUM() MEAN() FR O M Indique les tables sources Interogation: WHERE … Critères de sélection E X E MP LES WHERE ‘’Attribut1’’= ‘2’ Interrogation: WHERE LE CAS DE NULL C O M B INAISONS D E C R I T È RES Les opérateurs logiques permettent Cas particulier qui définit: Pas de donnée d’avoir plusieurs conditions Colonne= NULL ne marche pas  Interrogation: WHERE R E C H ERC HE C H A I NE S D E SE T D E C R I T ÈRE C A R ACTÈRE S Au lieu d’utiliser l’operateur =, on va utiliser l’opérateur Au lieu de faire: ‘LIKE’ Non sensible à la casse % :qui représente n'importe quelle chaîne de caractères, quelle que soit sa longueur _ : qui représente un seul caractère On peut faire: Caractère d'échappement : \ R E C H ERC HE D A N S U N IN TE RVALLE Interrogation Fonctions supplémentaires TR I DE S DO N NÉE S L I M I T ER L E N O M B RE D E R É SU LTAT Tri croissant: ORDER BY [Attribut] ASC Ajouter après la clause WHERE : LIMIT nombre_de_lignes [OFFSET Tri décroissant: decalage]; ORDER BY [Attribut] DESC Exemple: É L IM INE R L E S D O U B LONS Ajouter la commande DISTINCT au SELECT Exemple:  Suppression / Modification SU PPR ESSION MO D I F ICATI ON UPDATE nom_table DELETE FROM [Table] WHERE … SET col1 = val1 [, col2 = val2,...] Cette opération est irréversible, soyez très prudents ! [WHERE...]; Les requêtes L E S J O INTUR ES Principe des jointures Animal PR INC IPE DE S J O I N TURE S CP Nom Espece Espece_Id CP espece_id Le principe des jointures est de Nom joindre plusieurs tables, et ainsi Description obtenir un résultat complets extrait de plusieurs Tables. SELECT espece_id FROM Animal WHERE nom = 'Cartouche'; Principe des jointures EN UNE REQUÊTE SELECT Espece.description FROM Espece INNER JOIN Animal ON Espece.id = Animal.espece_id WHERE Animal.nom = 'Cartouche';  Les jointures JO IN TU RE IN T E RNE SELECT * FROM Table1 [INNER] JOIN Table2 ON colonne_table1 = colonne_table2 [WHERE...] [ORDER BY...] [LIMIT...] Les informations dont l’ID est nulle dans la table1 ne sont pas reprises dans les résultats affichés. De même si un enregistrement existe dans la table2 et qu’il n’est pas référencé. Celui-ci n’est pas repris non plus.  Les jointures JO IN TU RE E X T E R NE Lorsque l'on fait une jointure par la gauche (grâce aux mots-clés LEFT JOIN ou LEFT OUTER JOIN), cela signifie que l'on veut toutes les lignes de la table de gauche (sauf restrictions dans une clause WHERE, bien sûr), même si certaines n'ont pas de correspondance avec une ligne de la table de droite. SELECT TABLE1.Colonne1, TABLE2.Colonne2 FROM TABLE1 -- Table de gauche LEFT JOIN TABLE2 -- Table de droite ON TABLE1.Colonne2 = TABLE2.Colonne1 WHERE … Les requêtes complexes L E S SO US -R EQU ÊTE S Les sous-requêtes DA N S L E F R O M Lorsque l'on fait une requête de type SELECT, le résultat de la requête nous est envoyé sous forme de table. Grâce aux sous-requêtes, il est tout à fait possible d'utiliser cette table et de refaire une recherche uniquement sur les lignes de celle-ci. Une sous-requête doit toujours se trouver dans des parenthèses, afin de définir clairement ses limites. SELECT MIN (Colonne1) FROM ( SELECT * FROM [TABLE] WHERE ) WHERE … Les sous-requêtes DA N S L E S C O N D I TIO NS Il est également possible d’utiliser les sous-requêtes dans le WHERE permettant ainsi d’utiliser la réponse de la première requête comme condition pour la deuxième. SELECT * FROM [TABLE] WHERE Colone1 = ( SELECT * FROM [TABLE] WHERE ) Merci de votre Attention Jonathan NAVE Mail: [email protected]