Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

Cours sur les Bases de données Professeur : Jacques Faubert ETIENE Master en Ma

Cours sur les Bases de données Professeur : Jacques Faubert ETIENE Master en Management des Systèmes d’Information UNIVERSITE JEAN MOULIN LYON3 Date: 22 / 03/ 2019 1 FACULTE DES SCIENCES EN2 / GC2 /EM2 /AR2 SOMMAIRE I. Le langage SQL.....................................................................................................................................3 II. SQL comme Langage de définition des données.................................................................................3 1. Les types de données......................................................................................................................3 1.1 Numériques.............................................................................................................................3 1.2 Caractère.................................................................................................................................4 1.3 Dates et heures........................................................................................................................5 1.4 Les autres types de données....................................................................................................7 2. L’ordre CREATE................................................................................................................................7 2.1 SYNTAXE..................................................................................................................................8 2.2 Les commentaires........................................................................................................................9 2.3 Structure requête SQL SFW.......................................................................................................10 2.4 Créer une table à partir d’une autre..........................................................................................10 2.5 Utilisation des synonymes.........................................................................................................12 2.6 Les séquences............................................................................................................................13 3. La suppression de tables................................................................................................................15 3.1 L’ordre DROP.........................................................................................................................15 4. La modification de tables...................................................................................................................15 4.1 L’ordre ALTER.........................................................................................................................15 4.2 Renommer une table (RENAME)............................................................................................16 5. Quelques instructions SQL.............................................................................................................17 6. Fonctions de calcul et aggrégats....................................................................................................17 7. Gestion de la date (SQLServer)......................................................................................................18 2 I. Le langage SQL Les bases de données relationnelles sont manipulées à travers un langage de requêtes appelé SQL (Structured Query Language). Langage divisé en quatre parties : • SQL comme langage de définition des données ; • SQL comme langage de manipulation des données ; • SQL comme langage de contrôle de transactions ; • SQL comme langage de contrôle de données. II. SQL comme Langage de définition des données SQL comme langage de définition des données, permet de créer et de modifier l'organisation des données dans la base de données. 1. Les types de données Il existe trois (3) grandes familles de données : numérique, caractère (alphanumérique) et temporelles (dates et heures). Chaque SGBDR a décliné des types spécifiques pour un besoin précis ou pour des problématiques de stockage. Il est important de bien comprendre chaque type de données, afin de choisir le meilleur type possible lorsque vous définissez les colonnes de vos tables. En effet, choisir un mauvais type de données pourrait entraîner: un gaspillage de mémoire (ex. : si vous stockez de toutes petites données dans une colonne faite pour stocker de grosses quantités de données) ; des problèmes de performance (ex. : il est plus rapide de faire une recherche sur un nombre que sur une chaîne de caractères) ; un comportement contraire à celui attendu (ex. : trier sur un nombre stocké comme tel, ou sur un nombre stocké comme une chaîne de caractères ne donnera pas le même résultat) ; 1.1 Numériques Les types numériques principaux de la norme SQL2 sont NUMERIC (OU DECIMAL) pour les données à virgule, INTEGER pour les entiers longs et SMALLINT pour les entiers courts. Oracle a simplifié en fusionnant les deux types NUMERIC et INTEGER en un seul type nommé NUMBER (OU DECIMAL). Exemple Oracle : PRIX NUMBER ( 8,2) ou PRIX (DECIMAL(8,2) ) sont identiques. NBTELEP NUMBER (5) OU NBTELEP (5,0) sont identiques. 3 Dans le premier exemple, on décrit un type décimal de 8 chiffres de longueur dont deux décimales comme 123456, 12. Dans le deuxième exemple, on ne précise pas de longueur après la virgule donc il s’agit d’un entier. MySQL, de son côté, a également fusionné les deux types numériques dans le type DECIMAL. Exemple MySQL : PRIX DECIMAL (8,2) NBTELEP DECIMAL (5) CODETEL SMALLINT Dans la majorité des cas, le type NUMBER (OU DECIMAL pour MySQL) est suffisant pour décrire une donnée numérique. De plus , elle garantit l’exactitude des arrondis dans les calculs. 1.2 Caractère Le type alphanumérique classique se note CHAR pour les données de taille fixe et VARCHAR pour les chaines de caractère de longueur variable. La majorité du temps, il est conseillé d’utiliser VARCHAR. En effet, une donnée déclarée sur 50 par exemple et qui ne contient que deux caractères sera effectivement stockée sur deux caractères avec VARCHAR alors qu’elle sera stockée sur 50 caractères avec CHAR. CHAR complète avec des espaces jusqu’à longueur maximum. Dans le cas VARCHAR, la longueur effective de la chaine est stockée sur un octet permettant ainsi au SGBDR de restituer correctement la donnée. Dans ORACLE : Taille maximum d’une donnée CHAR : 2000 caractères. Taille maximum d’une donnée VARCHAR : 4000 caractères. Normalement, on parle en octet et non en caractère mais la majorité des SGBD sont paramétrés pour que 1 octet = 1 caractère. Dans MySQL : Taille maximum d’une chaine CHAR OU VARCHAR est normalement de 255 caractères. Néanmoins, si la longueur saisie est de longueur supérieure, MySQL transforme automatiquement le type en TEXT, MEDIUMTEXT, OU LONGTEXT. Attention, cette transformation affecte le stockage de la donnée, et on perd la notion de taille variable, les types TEXT, MEDIUMTEXT, et LONGTEXT sont de longueur fixe. 4 Remarque : Oracle préconise d’utiliser le type VARCHAR2 à la place du VARCHAR. Mais il est préférable d’utiliser VARCHAR qui est le standard SQL, VARCHAR2 étant spécifique à Oracle. Exemple : TYPE : CHAR(2), COULEUR VARCHAR(25)) ; 1.3 Dates et heures Les types de format temporel sont principalement DATE, TIME et TIMESTAMP. Les formats par défaut des dates ou des heures varient d’une base de donnés à l’autre. Pour connaitre le format par défaut, il faut utiliser : Pour Oracle : SELECT SYSDATE FROM DUAL ; Pour MySQL : SELECT NOW () ; Qui donnent la date du jour au format utilisé par la base. Il est préférable de ne pas s’appuyer sur un format défini pour une BDD, en effet ce format peut évoluer ou la séquence de code s’appliquer sur une autre base qui n’aura pas le même format de date par défaut. Lors de la création de vos tables, il n’est pas utile de connaitre le format de stockage, il faut déclarer la colonne DATE, TIME OU TIMESTAMP. DATE : format date, norme ISO AAAA-MM-JJ TIME : format heure, norme ISO HH :MM :ss.xxx TIMESTAMP : format date et heure, norme ISO AAAA-MM-JJ HH :MM :SS.XXX Remarque : Oracle ne connait pas le type TIME, il faut utiliser le type TIMESTAMP, ou stocker l’heure dans un format CHAR(10) puis utiliser les formats de conversion ou stocker les heures en les convertissant en secondes dans un champ de type NUMBER. MySQL propose en complément les types suivants : - DATETIME équivalent au TIMESTAMP. - YEAR qui donne l’année sur quatre caractères. 5 Exemple : DATE_ACHAT DATE, DATE_MAJ TIMESTAMP La manipulation des dates et des heures est très différente d’un SGBDR à l’autre, les fonctions de conversion notamment sont différentes. MySQL Fonctions Date CURRENT_DATE () date courante • CURRENT_TIME () heure courante • DATEADD (D1, INTERVAL X TYPE) SECOND, DAY, MONTH, MINUTE_SECOND, … • PERIOD_DIFF (D1, D2) nombre de mois entre D1 et D2, format des dates : YYMM ou YYYYMM • DAYOFWEEK (date) 1 à 7 1 pour dimanche DAYOFMONTH 1 à 31 DAYOFYEAR 1 à 366 • MONTH, DAYNAME, MONTHNAME, QUARTER, WEEK, YEAR, HOUR, MINUTE, SECOND. MySQL Fonctions chaines UPPER (…) mise en majuscules • LOWER (…) mise en minuscules • CONCAT ('C1', 'C2',…) chaine 'C1C2…' • LEFTRIGHT ('C1', X) X premiers derniers caractères • LENGTH ('C1') longueur de C1 • LRTRIM ('C1') retourne C1 sans espace au début fin • REPLACE ('C1', 'C2', 'C3') dans C1, C3 remplace C2 • SUBSTRING ('C1', X [, Y]) C1 à partir X longueur Y 6 1.4 Les autres types de données 1.4.1 Les autres types numériques MySQL utilise les types suivants : Types numériques Valeur min Signé ou non signé Valeur max Signé ou non signé Stockage (octet) TINYINT -128 0 127 255 1 SMALLINT -32768 0 32767 65535 2 MEDIUMINT -8388608 0 8388607 16777215 3 BIGINT - 922337203685477580 8 0 9223372036854775807 1844674407370955161 5 8 Il existe également les types FLOAT, REAL, et DOUBLE qui sont utilisés principalement pour diminuer la place disque utilisée car ils sont stockés en binaire. 1.4.2 Les autres types caractères Types caractères Valeur min Valeur max LONG 1 2 Go CLOB OU NCLOB 1 4 Go MySQL utilise les types suivants : Types caractères Valeur min Valeur max TINYBLOB 1 255 octets TINYTEXT 1 255 octets BLOB 1 65535 octets TEXT 1 65535 octets MEDIUMBLOB 1 16777215 octets MEDIUMTEXT 1 16777215 octets LONGBLOB 1 4 294 967 295 octets LONGTEXT 1 4 294 967 295 octets Dans cette section, nous allons voir comment créer une table, ajouter ou supprimer des colonnes, mettre des commentaires sur les colonnes et les tables, mais également, la méthode pour copier une table dans une autre puis comment attribuer un synonyme à un nom de table. ‘ 7 2. L’ordre CREATE CREATE est l’ordre de base en langage SQL pour créer un élément. Celui-ci sert à créer une base de données, une TABLE, un INDEX, une VUE, ou un SYNONYME, des procédures stockées, des triggers. En fonction du besoin, la syntaxe est différente. Base de données CREATE DATABASE nom; // création d’une base de données USE nom; // pour utiliser cette base. TABLE Une table se définit principalement par les colonnes qui la composent et les règles qui s’appliquent à ces colonnes. Nous n’aborderons pas le stockage physique de la table. En effet, chaque SGBDR a sa propre syntaxe dans ce domaine. Ce sont les DBA qui spécifient les normes de stockage et les options à appliquer sur les tables. Règles uploads/Management/ cours-sur-les-bases-de-donnees-22032019.pdf