Cours de Mysql pour les Programmer PDF

Preview

Summary

MYSQL PROGRAMMEURdev.mysql/doc/SOMMAIREI Terminologie.............................................................................................................................II Comment construire un modèle relationnel..............................................................................I...

Description

MYSQL PROGRAMMEUR http://dev.mysql.com/doc/

Support de cours MYSQL

SOMMAIRE I II

Terminologie.............................................................................................................................1 Comment construire un modèle relationnel..............................................................................1 II.1 Construire à partir d’un MCD............................................................................................1 II.2 Normalisation du MCD......................................................................................................1 III Manipulation d’un modèle relationnel...................................................................................2 III.1 A travers l’algèbre relationnel........................................................................................2 III.2 A travers le langage SQL................................................................................................2 CHAPITRE II Le langage de définition de données.......................................................................3 I Gérer une BD.............................................................................................................................3 II Type de données........................................................................................................................3 III Créer une table.......................................................................................................................2 IV Modifier une table..................................................................................................................3 V Autres commandes....................................................................................................................4

CHAHIE THOMAS PAULIN

i

Support de cours MYSQL

RAPPELS SUR LES BASES DE DONNEES RELATIONNELLES I

Terminologie  

   

Bases de données relationnelles : Base de données dont le schéma est constitué d’un ensemble de relation. Relation: sous ensemble du produit cartésien d’une liste de domaines. C’est en fait un tableau à deux dimensions dont les colonnes correspondent aux domaines et dont les lignes contiennent des tuples. On associe un nom à chaque colonne. Domaine : ensemble des valeurs d’un attribut. Attribut : une colonne d’une relation, caractérisé par un nom. Tuple : liste des valeurs d’une ligne d’une relation. clé primaire : une ou plusieurs attributs minimum qui permettent d'identifier de manière unique un n-uplet de la relation. On peut définir la clé comme suit : une clé est définie comme un ensemble de K attributs vérifiant la double propriété suivante :  unicité (les valeurs de clé primaire sont uniques et non nulles)  minimalité (aucun attribut composant K ne peut être enlevé sans perte de la propriété d'unicité)"

II Comment construire un modèle relationnel II.1 Construire à partir d’un MCD A partir du MCD élaboré, on applique les règles ci-dessous : Règle 1 : Toute entité du MCD devient une Relation ou table et son identifiant devient sa clé primaire. Règle 2 : Les associations binaires de type (*,1-*,n) se traduisent par la migration de l’identifiant de l’identifiant de l’entité se trouvant du coté de la cardinalité maximale n dans la relation(Relationnel) se trouvant du coté de la relation de cardinalité maximale 1. Règle 3 : Les associations n-aires et de type plusieurs à plusieurs deviennent des relations avec pour clé primaire les identifiants des entités participant à la relation et pour autres propriétés celles portés par l’association. II.2 Normalisation du MCD Elles sont basées sur les formes normales. Les principales sont :  



1FN : Une relation est en 1FN si tous les attributs contiennent une valeur atomique. 2FN : Une relation est en 2FN si et seulement :  Si elle est en 1FN  Si tout attribut n’appartenant pas à la clé, dépend de la clé par une dépendance fonctionnelle élémentaire. 3FN : Une relation est en 3FN si et seulement si :  Elle est en 2FN  Tout attribut n’appartenant pas à la clé, ne dépend pas d’un attribut non clé. Cette règle peut encore se dire tout attribut n’appartenant pas à la clé, dépend de la clé par une dépendance fonctionnelle élémentaire directe.

CHAHIE THOMAS PAULIN

ii

Support de cours MYSQL

III Manipulation d’un modèle relationnel III.1

A travers l’algèbre relationnel

On distingue alors les opérations relationnelles tels que:

  

Projection: Pour afficher uniquement certaines colonnes La restriction: Pour afficher les lignes respectant une condition La jointure: Pour fabrique une relation à partir de deux ou plusieurs autres.

III.2 A travers le langage SQL On utilise alors l’un des langages que sont :  Le LDD : Langage de définition de données  LMD : Langage de manipulation de données  LCD : Langage de contrôle de données L’objectif de ce cours est de fournir les compétences nécessaires à la mise en place d’une base de données relationnelle de manière générale et plus précisément sous le SGBDR MYSQL. Il est à noter que MYSQL est un SGBDR open source qui fonctionne en client / Serveur. Le serveur MYSQL tourne sur le port 3306 par défaut.

CHAHIE THOMAS PAULIN

iii

Support de cours MYSQL

CHAPITRE I

Le langage de définition de données

IV Gérer une BD La syntaxe de création d’une base de données est : CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name[create_specification [, create_specification] ...] Où«create spécification» est de la forme [DEFAULT] CHARACTER SET charset_name| [DEFAULT] COLLATE collation_name  SCHEMA est un synonyme de DATABASE et est disponible à partir de la version 5.0.2  IF NOT EXISTS permet de créer la BD si elle n’existe pas.  CHARACTER SET défini le défini le mode d’encodage par défaut des informations.  COLLATE défini le mode de comparaison des valeurs.

Les commandes :  

SHOW COLLATION : permet d’afficher les noms des éléments de comparaisons. SHOW CHARACTER SET : Permet d’afficher les modes d’encodage exixtants. Exemple : CREATE DATABASE gestion_scolaire ;

Les commandes ci-dessous peuvent être utilisées dans le cadre de création des tables.

Commandes

Explication

SHOW DATABASES DROP {DATABASE | SCHEMA} [IF EXISTS] db_name USE db_name SHOW TABLES DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

Affiche la liste des bases de données du serveur. Supprime une base de données Fait de db_name la base par défaut. Affiche les tables de la BD Supprime une BD

V Type de données TINYINT[(length)] [UNSIGNED] [ZEROFILL] SMALLINT[(length)] [UNSIGNED] [ZEROFILL] MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL] INT[(length)] [UNSIGNED] [ZEROFILL] INTEGER[(length)] [UNSIGNED] [ZEROFILL] BIGINT[(length)] [UNSIGNED] [ZEROFILL] REAL[(length,decimals)] [UNSIGNED] [ZEROFILL] DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL] FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL] DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL] NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL] DATE TIME TIMESTAMP

 



DATETIME CHAR(length) [BINARY ASCII | UNICODE] VARCHAR(length) [BINARY] TINYBLOB BLOB MEDIUMBLOB LONGBLOB TINYTEXT TEXT MEDIUMTEXT LONGTEXT ENUM(value1,value2,value3,...) SET(value1,value2,value3,...)

ZEROFILL indique que l’entier est non signé. DATE :Une date représente un intervalle entre le '1000-01-01' et le '9999-12-31'. MySQL affiche les DATE au format 'YYYY-MM-DD' Toutefois des fonctions de conversion comme « date_format() permettent de changer de format. DATETIME : permet de combiner une date et un horaire. L'affichage se fait au format AAAAMM-JJ HH:MM:SS entre le 1000-01-01 00:00:00' et '9999-12-31 23:59:59.

CHAHIE THOMAS PAULIN

iv

Support de cours MYSQL 



   

TIMESTAMP[(M)] :Ce type stocke une date et un horaire sous forme d'un nombre de secondes écoulées depuis le 1er janvier 1970. La syntaxe est timestamp (M) où M est la longueur de l'affichage (14 par defaut) ENUM : permet d'indiquer un type énuméré dont les instances prennent leur unique valeur parmi un ensemble explicitement spécifié. MySQL contrôle qu'une valeur rentrée appartient bien à l'ensemble énuméré. Exemple: ENUM ('bleu', 'blanc', 'rouge') SET :identique à ENUM, mais un attribut de type SET peut prendre plusieurs valeurs parmi l'ensemble énuméré. BINARY : permet de gérer la casse. BLOB :permet de représenter des objets binaires (texte de grandes longueur,image, son, …) Les données spatiales peuvent être gérées en utilisant la spécification Open GIS

VI Créer une table La syntaxe est : CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [select_statement] Où : create_definition: column_definition | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) | KEY [index_name] [index_type] (index_col_name,...) | INDEX [index_name] [index_type] (index_col_name,...) | [CONSTRAINT [symbol]] UNIQUE [INDEX] [index_name] [index_type] (index_col_name,...) | [FULLTEXT|SPATIAL] [INDEX] [index_name] (index_col_name,...) | [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) [reference_definition] | CHECK (expr) column_definition: col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [[PRIMARY] KEY] [COMMENT 'string'] [reference_definition] index_col_name: col_name [(length)] [ASC | DESC] reference_definition: REFERENCES tbl_name [(index_col_name,...)][MATCH FULL | MATCH PARTIAL] [ON DELETE reference_option][ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT table_options: table_option [table_option] ... table_option: {ENGINE|TYPE} = {BDB|HEAP|ISAM|InnoDB|MERGE|MRG_MYISAM|MYISAM} select_statement: [IGNORE | REPLACE] [AS] SELECT ... (Some legal select statement)

 La commande SHOW ENGINES définit les moteurs disponibles;  Une table temporaire disparait à la fin de la connexion de l’utilisateur.

CHAHIE THOMAS PAULIN

v

Support de cours MYSQL  Les options IGNORE et REPLACE à placer entre la parenthèse fermante et le SELECT permettent respectivement d’ignorer les doublons et de remplacer les doublons par la dernière valeur trouvée. Ces options ne sont prises en compte que pour gérer le problème des contraintes d’unicité (UNIQUE, PRIMARY KEY). Exemple : CREATE TABLE CLIENTS ( codeclient` int(11) AUTO_INCREMENT, nom char(20) NOT NULL default, prenom char(20) NOT NULL, rue char(30) default NULL, ville char(15) default NULL, region char(20) default NULL, codepostal char(5) default NULL, pays char(15) default NULL, telephone char(24) default NULL, PRIMARY KEY (nom,prenom), INDEX (profession) ) TYPE=MyISAM SHOW CREATE TABLE nom_tbl : donne la requête ayant permis de créer une table

VIIModifier une table LA syntaxe de cette commande est : ALTER [IGNORE] TABLE tbl_name alter_specification [, alter_specification] ... alter_specification: ADD [COLUMN] column_definition [FIRST | AFTER col_name ] | ADD [COLUMN] (column_definition,...) | ADD INDEX [index_name] [index_type] (index_col_name,...) | ADD [CONSTRAINT [symbol]]PRIMARY KEY [index_type] (index_col_name,...) | ADD [CONSTRAINT [symbol]]UNIQUE [index_name] [index_type] (index_col_name,...) | ADD [FULLTEXT|SPATIAL] [index_name] (index_col_name,...) |ADD [CONSTRAINT [symbol]]FOREIGN KEY [index_name] (index_col_name,...)[reference_definition] | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} | CHANGE [COLUMN] old_col_namecolumn_definition[FIRST|AFTER col_name] | MODIFY [COLUMN] column_definition [FIRST | AFTER col_name] | DROP [COLUMN] col_name | DROP PRIMARY KEY | DROP INDEX index_name | DROP FOREIGN KEY fk_symbol | DISABLE KEYS | ENABLE KEYS | RENAME [TO] new_tbl_name | table_options  Les index permettent d’accélérer les recherches  UNIQUE permet de définir un index unique. Exemple : ALTER TABLE Personnes ADD PRIMARY KEY (nom,’prénom’) ALTER TABLE Personnes ADD UNIQUE u_fax (fax) ALTER TABLE Personnes ALTER ‘téléphone’ SET DEFAULT ‘9999999999’ ALTER TABLE Personnes

CHAHIE THOMAS PAULIN

vi

Support de cours MYSQL CHANGE fax num_fax VARCHAR(14)

On peut ajouter un index sur une table en utilisant la syntaxe : CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type] ON tbl_name (index_col_name,...) De même on peut supprimer un index en utilisant : DROP INDEX nom_de_l_index ON nom_de_table

VIII   

Autres commandes

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ... : Cette commande permet de supprimer une ou plusieurs table RENAME TABLE nom_de_table TO nouveau_nom_de_table[, nom_de_table2 TO nouveau_nom_de_table2,...] : Permet de renommer une table {DESCRIBE | DESC} nom_de_table : Permet d’afficher la description d’une colonne.

Exemple : RENAME PRODUIT TO ARTICLE DESC PRODUIT

CHAHIE THOMAS PAULIN

vii

Support de cours MYSQL

CHAPITRE II LES REQUETES LMD Dans ce chapitre, il sera question de présenter les requêtes SQL de manipulation des données. Il s’agira essentiellement des requêtes d’interrogation, d’insertion, de suppression de modification. Nous utiliserons la BD constitué des tables : EMPLOYE(mle, nom, prenom, sexe, datnais,codeserv*,salBase,avantage,retenue,mleChef*,pays) Service(codeser,nomserv) Projet(numP, Libelle,dateDebut,datefin,mleSuperviseur*) Participer(numP,mle,dateDeb,datefin)

I Les requêtes d’interrogation I.1 Syntaxe générale Une requête d’interrogation est de la forme: SELECT [STRAIGHT_JOIN] [DISTINCT | DISTINCTROW | ALL] select_expression,... [INTO {OUTFILE | DUMPFILE} 'nom_fichier' export_options] [FROM table_references [WHERE where_definition] [GROUP BY {unsigned_integer | nom_de_colonne | formula} [ASC | DESC], ... [HAVING where_definition] [ORDER BY {unsigned_integer | nom_de_colonne | formula} [ASC | DESC] ,...] [LIMIT [offset,]lignes] [PROCEDURE procedure_name(argument_list)] [FOR UPDATE | LOCK IN SHARE MODE]]

Où : select_expression : column| formula [[as] newcolumnName]          

   

Select :Spécifie les attributs dont on souhaite connaître les valeurs. DistinctRow, Distinct : Demande d’éliminer les doublons INTO OUTFILE :Spécifie le fichier sur lequel effectuer la sélection. FROM :Spécifie le ou les relations sur lesquelles effectuer la sélection. WHERE :Définie le ou les critères de sélection sur des attributs. GROUP BY : Permet de grouper les lignes de résultats selon un ou des attributs. HAVING :Définie un ou des critères de sélection sur des ensembles de valeurs d’attributs après groupement. ORDER BY : permet d’effectuer des tris. LIMIT : Sélectionne unique ment les enregistrements situés dans la plage FOR UPDATE : Permet de verrouiller les lignes pendant la sélection

I.2 Opérateur et fonctions MYSQL Les principaux opérateurs MYSQL sont : les opérateurs arithmétiques (+, -, *, /, %), les opérateurs logiques qui retournent 0 ou 1 (AND, OR, NOT, BETWEEN, IN), les opérateurs relationnels (=, ). LIKE permet de comparer deux chaînes. Le caractère ‘%’ est spécial et signifie : 0 ou plusieurs caractères.Le caractère ‘_’ est spécial et signifie : 1 seul caractère, n’importe

CHAHIE THOMAS PAULIN

viii

Support de cours MYSQL





lequel.Pour utiliser les caractères spéciaux ci-dessus en leur enlevant leur fonction spéciale, il faut les faire précéder de l’antislash : ‘\’. Les fonctions mathématiques : o POW(x,y): x^y o TRUNCATE(x,y) :Tronque le nombre X à la Yème décimale o RAND(), RAND(x) : Nombre aléatoire entre 0 et 1 ou 0 et x o EXP(x), LOG(x), SIN(x), COS(x), TAN(x), PI(),MOD(x,y) o ROUND(x) : Arrondi à l’entier le plus proche o CEILING(x),ROUND(x,d) :Arrondi à l’entier supérieur ou à d décimale. o FLOOR(x) :Arrondi à l’entier inférieur. o SIGN(x),ABS(x) Les fonctions de chaînes o ASCII(‘caractere’): retourne le code ascii d’un caractère o TRIM(x):. Supprime les espaces de début et de fin de chaîne. o LOWER(x), UPPER(x) : convertit en minuscules ou en majuscule o LOCATE(x,y) :Retourne la position de la dernière occurrence de x dans y. Retourne 0 si x n’est pas trouvé dans y. o CONCAT(x,y,…):Concatène ses arguments. o SUBSTRING(s,i,n) ouMID(str,pos,len):Retourne les n derniers caractères de s en commençant à partir de la position i. o SOUNDEX(x):Retourne une représentation phonétique de x. o LENGTH(x) : Longueur de la chaîne de caractère. o

INSTR(str,substr) : Renvoie la position de la première occurrence de la sous chaîne substr dans la chaîne str.

LOCATE(substr,str,pos) :Renvoie la position de la première occurrence de substr dans la chaine str en commençant à partir de la position pos. Si pos est omis, la recherche commence à la position 0. o LTRIM, RTRIM : supprimer les espaces à gauche ou droite o LPAD,RPAD(str,len,padstr) : Complete une chaine par padstrà gauche ou à droite pour fairelen caractère o REPEAT(str,count) :Repeter count fois la chaine str. o REPLACE(str, find_str,replace_str) : Remplace toutes les occurrences de la chaine find_str par replace_str dans la chaine str. Les fonctions de date o CURDATE() : Renvoie la date courante(jour, mois, année) o NOW() : Renvoie la date actuelle(jour, mois, année, heure, minute, seconde) o CURTIME():Renvoie l’heure actuelle(HH :MM :SS) o YEAR(dte), DAY(dt), MONTH(dte) o TO_DAYS(x) :Conversion de la date X en nombre de jours depuis le 1er janvier 1970. o DATE_FORMAT(date,format) Les fonctions d’agrégation : o Min(), Max(),Count(), Count(Distinct), Avg(), Std(), Sum() o





I.3 Clause FROM et ORDER BY et WHERE( Par l’exemple) 1) 2) 3) 4)

Liste des employés dont le salaire de base est > 200000 Liste des employés avec pour chacun le salaire net (base+avantages-retenue) Liste des projets qui se sont déroulées en 2000 Liste des employés avec leurs âges respectifs par ordre alphabétique

CHAHIE THOMAS PAULIN

ix

Support de cours MYSQL 5) Quel est la moyenne, le minimum, le maximum des salaires. 6) Qui n’a pas de supérieur ? 7) Quels sont les employés qui ont un supérieur ? 8) Liste des employés dont le salaire de base se situe ente 200000 et 240000 9) Liste des employés dont le prénom se prononce comme Paulin par âgecroissant. 10) Tous les services dont le nom commence par Com ou se termine par cial ou dont le deuxième caractère du nom est m

I.4 Les jointures Les jointures sont utilisées quand les informations à extraire proviennent de plusieurs tables. DE manières traditionnelle il suffit de :  lister les différents champs dans la select en les préfixant du nom de la table (surtout s’il peut avoir ambiguïté)  lister les tables dans la clause FROM  Ajouter la condition de jointure dans la clause WHERE Exemple : Liste des employés avec pour chacun son service. MYSQL utilise une syntaxe approprié pour rendre un peu plus rapide les jointures. Il s’agit de la clause JOIN. La syntaxe de cette requête est la suivante SELECT … FROM X [INNER|RIGHT [OUTER] |LEFT [OUTER]] JOIN Y ON condition_jointure …. Avec :  INNER JOIN: n’inclus les enregistrements de la première table que s’ils ont une correspondance dans la seconde table.  LEFT JOIN : inclus tous les enregistrements de la première table même s’ils n’ont pas de correspondance dans la seconde table. Dans ce cas précis, l’attribut non renseigné prendra la valeur NULL.  RIGHT JOIN :inclus tous les enregistrements de la duxième table même s’ils n’ont pas de correspondance dans la seconde table. Dans ce cas précis, l’attribut non renseigné prendra la valeur NULL. Exercice : 1) 2) 3) 4)

Liste des employés avec pour chacun son service. Liste des employés avec pour chacun son supérieur hiérarchique Liste des projets avec pour chacun son superviseur Liste des employés avec le service auquel il apparti...