Langage SQL Lcd

Embed Size (px)

Citation preview

Langage SQLY.Safsouf

1

Jusqu' prsent, nous avons vu comment crer une requte en utilisant la fentre requte d'ACCESS. Il existe une autre faon de faire les requtes : en passant directement par le langage SQL. D'ailleurs, ACCESS utilise aussi ce langage pour faire ses requtes, il traduit ce que vous avez entr en SQL, puis excute la requte SQL.

2

Qu'est-ce que SQL ?SQL (Structured Query Language) est un langage de programmation informatique destin stocker, manipuler et retrouver des donnes enregistres dans des bases de donnes relationnelles. Le langage SQL est apparu pour la premire fois en 1974, lorsquun groupe dIBM a mis sur pied le premier prototype dune base de donnes relationnelle. La premire base de donnes relationnelle a t commercialise par Relational Software (plus tard Oracle).

3

Qu'est-ce que SQL ?SQL permet, pour rsumer, trois types de manipulations sur les bases de donnes :

Les manipulations des bases de donnes : Slection, modification, suppression d'enregistrements. La maintenance des tables : cration, suppression, modification de la structure des tables. La gestion des droits d'accs aux tables : Contrle des donnes : droits d'accs, validation des modifications.

4

LMD (Langage de manipulation des donnes)Dans ce chapitre nous verrons comment utiliser les requtes SQL pour la rcupration (requte de slection) et la manipulation des donnes (linsertion, la modification et la suppression)

5

La commande SELECTLa commande SELECT est la commande la plus utilise de SQL. Cette commande va servir faire des requtes pour rcuprer des donnes dans les tables.Sa syntaxe est : SELECT champ1, champ2, champ3, ... FROM table;6

La commande SELECTS'il y a plus d'un champ spcifi aprs SELECT, les champs doivent tre spars par des virgules. Exemple. Pour slectionner les champs "prnom" et "nom" de tous les enregistrements de la table Clients :SELECT nom, prnom FROM Clients ;7

La commande SELECTSi on veut rcuprer tous les champs des enregistrements slectionns, la syntaxe est la suivante :SELECT * FROM table;

8

La commande SELECTLes clauses SELECT et FROM doivent obligatoirement apparatre au dbut de chaque requte, on peut, ensuite, indiquer des critres de slection avec la clause WHERE :

SELECT * FROM table WHERE condition;

9

La commande SELECTPar exemple, pour slectionner tous les Clients de la table "Clients" dont le code postal est 40000 :

SELECT * FROM Clients WHERE code_postal = 40000;

10

La commande SELECTLes oprateurs de condition :

11

La commande SELECTPour slectionner tous les articles dont le prix est suprieur 100 DH :SELECT * FROM Article WHERE prix_unitaire > 100;

12

La commande SELECTIl est possible de combiner plusieurs conditions avec des oprateurs logiques : L'oprateur AND runit deux ou plusieurs conditions et slectionne un enregistrement seulement si cet enregistrement satisfait TOUTES les conditions listes. (C'est--dire que toutes les conditions spares par AND sont vraies).

13

La commande SELECTPar exemple, pour slectionner tous les clients nomms Hassan' qui habitent Fes :SELECT * FROM Clients WHERE nom = 'Hassan' AND ville = 'Fs';

14

La commande SELECTL'oprateur OR runit deux conditions mais slectionne un enregistrement si UNE des conditions listes est satisfaite. Par exemple, pour slectionner tous les clients nomms 'Hassan' ou 'Mourad' : SELECT * FROM Clients WHERE nom = 'Hassan' OR nom = 'Mourad';15

La commande SELECTPour slectionner des enregistrements dont la valeur d'un champ peut tre comprise dans une liste ou entre deux valeurs, on utilise les clauses IN et BETWEEN. Par exemple : Pour slectionner les clients vivant Marrakech ou Casablanca :

SELECT * FROM Clients WHERE ville IN ('Marrakech', 'Casablanca');

16

La commande SELECTOu pour slectionner les produits dont le prix est compris entre 100 et 1000 DH :SELECT * FROM Produits WHERE prix_unitaire BETWEEN 100 AND 1000;

17

La commande SELECTPour slectionner les produits dont le prix n'est pas dans cet intervalle :SELECT * FROM Produits WHERE prix_unitaire NOT BETWEEN 100 AND 1000;

De la mme faon, NOT IN slectionne les enregistrements exclus de la liste spcifie aprs IN.18

La commande SELECTLa clause LIKE permet de faire des recherches approximatives sur le contenu d'un champ. Par exemple, pour slectionner les clients dont le nom commence par la lettre D : SELECT * FROM Clients WHERE nom LIKE 'S*';19

La commande SELECTTout comme dans les requtes Access, le symbole * remplace un ensemble de caractres, pour reprsenter tous les noms commenant par S, on utilisera 'S*', tous ceux se terminant par S, on utilisera '*S', et tous ceux comportant la lettre S : '*S*'. Le symbole ? ne remplace qu'un seul caractre. Si on a deux clients nomms Hassan et Hussan, on utilisera 'H?ssan'.

20

La commande SELECT

Attention : certaines versions de SQL n'utilisent pas les caractres * et ? mais d'autres caractres spcifiques, certaines versions utilisent notamment le caractre % la place de *.

21

ExerciceELEVE(matricule_elv, nom_elv, prenom_elv, ville_elv, cp_elv, age, sexe, date_insc,moy_elv)

Liste des lves. Les matricules des lves possdant une moyenne suprieur 10. Les filles qui habite Marrakech. Les lves g de plus de 25 ans habitant en dehors de Marrakech. Les lves habitant Rabat et Fs g entre 18 et 26 ans. Les noms et prnoms des lves dont le nom dbute par 'B' . Les matricules et noms des lves inscrit a lanne 2002 ayant une moyenne infrieur a 10.

22

La commande SELECTLa jointure va nous permettre de slectionner des informations dans plusieurs tables grce aux relations existant entre ces tables. Il va nanmoins falloir indiquer comment se fait la relation entre ces tables.

23

La commande SELECTPar exemple : rcuprer le nom et le prnom du client ayant pass la commande n1 :SELECT nom, prnom FROM Clients, Commande WHERE Commande.num_client = Client.num_client AND num_commande = 1;

24

La commande SELECT

Notez que lorsqu'on utilise plusieurs tables, il faut faire attention que deux tables n'aient pas de champs ayant le mme nom, si c'est le cas, et pour les diffrencier, on utilise, la notation: Nom_table.nom_du_champ. Si on est sur que le nom ne se retrouvera pas dans plusieurs tables, on peut l'utiliser sans le prfixer avec le nom de la table.

25

ExerciceREPRESENTATION (n_reprsentation, titre_reprsentation, lieu) MUSICIEN (nom, n_reprsentation*) PROGRAMMER (date, n_reprsentation*, tarif)

Donner la liste des titres des reprsentations. Donner la liste des titres des reprsentations ayant lieu l'opra Hassan2. Donner la liste des noms des musiciens et des titres des reprsentations auxquelles ils participent. Donner la liste des titres des reprsentations, les lieux et les tarifs pour le mois de janvier. Donner la liste des titres des reprsentations, les lieux et les dates auxquelles le musicien Kamal' a particip.

26

La commande SELECTSupprimer les doubles avec DISTINCT.

Supposons que nous voulions la liste des clients ayant achet quelque chose. nous ne voulons pas savoir ce qu'a achet chaque client , nous voulons juste connatre les clients qui ont achet quelque chose.

27

La commande SELECTSELECT Client.num_client,nom, prnom FROM Clients, Commande WHERE Commande.num_client = Client.num_client;

Si on excute cette requte directement, SQL va nous renvoyer une liste des numros, prnom et nom correspondants aux noms et prnoms des clients ayant pass chaque commande, il est clair qu'un client ayant pass plusieurs commandes va se retrouver plusieurs fois dans cette liste.28

La commande SELECTPour cela, nous allons devoir dire SQL de supprimer les doubles du rsultat de la slection pour n'afficher les clients qu'une seule fois. Pour cela, nous allons utiliser la clause DISTINCT.SELECT DISTINCT Clients.num_client,nom, prnom FROM Clients, Commande WHERE Commande.num_client = Client.num_client AND num_commande = 1;

29

La commande SELECTEn indiquant la clause DISTINCT avant le champ num_client, on indique SQL qu'on ne veut pas voir apparatre plusieurs fois un client ayant ce numro dans la slection renvoye. On peut mme rendre le rsultat de la slection plus agrable la lecture en utilisant la clause ORDER BY :30

La commande SELECTla clause ORDER BY permet de dfinir le tri des colonnes de la rponse, soit en prcisant le nom littral de la colonne, soit en prcisant son n d'ordre dans l'numration qui suit le mot clef SELECT. ASC spcifie lordre ascendant et DESC lordre descendant du tri. ASC ou DESC peut tre omis, dans ce cas c'est l'ordre ascendant qui est utilis par dfaut.31

La commande SELECTPour spcifier l'ordre de tri, on doit placer les noms des colonnes spares par des virgules juste aprs le mot clef "ORDER BY", dans l'ordre voulu.. On peut aussi utiliser le rang de chaque colonne dans l'ordre spcifi dans la clause SELECT.

SELECT * FROM Clients WHERE code_postal = 40000; ORDER BY nom_cli, prnom DESC;32

ExerciceAUTEUR(cod_aut, nom_aut, prenom_aut) LIVRE(cod_livre, nom_livre, edition, cod_aut#, prix) COMPORTER(cod_livre#, cod_cde#, quantite) COMMANDE(cod_cde, date_cde, cod_cli#) CLIENT(cod_cli, nom_cli, prenom_cli, adr_cli, localite)crire les requtes SQL correspondant aux questions suivantes :

Afficher le nom, le prnom et la localit des clients. Afficher les noms des livres des ditions Folio avec le nom de lauteur. Afficher pour toutes les commandes les diffrentes lignes de commande. Afficher les diffrentes ditions en ordre dcroissant et ceci en vitant les rptitions. Afficher la date, le nom du livre ainsi que la quantit donne pour le client dont le code est 23 par ordre chronologique. Afficher pour tous les livres le nom de lauteur, son prnom, ldition, ainsi que le nom du livre et ceci tri suivant le nom de lauteur en laissant de ct le livre dAndorra. Afficher les donnes des livres commands par des clients dont le nom commence avec la lettre K .33

La commande SELECTSQL a cinq fonctions importantes :

SUM AVG MAX MIN COUNT.

On les appelle fonctions d'ensemble parce qu'elles rsument le rsultat d'une requte plutt que de renvoyer une liste d'enregistrements.34

La commande SELECT

35

La commande SELECTExemple :SELECT MIN(prix_unitaire),MAX(prix_unitaire), AVG(prix_unitaire) AS MOYENNE_PRIX FROM PRODUIT; Va retourner le prix le plus petit de la table Produit, le prix le plus lev et le prix moyen en renommant la champ de la moyenne.36

La commande SELECTSELECT COUNT (*) FROM Produits WHERE libelle LIKE 'P*';Va retourner le nombre de produits dont le libell commence par la lettre 'P'.

37

La commande SELECTUne des utilisations les plus courantes de la clause GROUP BY est son association avec une fonction d'ensemble (le plus souvent COUNT, pour compter le nombre d'enregistrements dans chaque groupe).Par exemple, si nous voulons la liste des vendeurs, avec pour chaque vendeur le nombre de ventes qu'il a fait :38

La commande SELECTSELECT num_vendeur,COUNT (*) FROM Commandes GROUP BY num_vendeur;Le mot-cl GROUP BY sutilise lorsque diverses colonnes dune ou de plusieurs tables sont slectionnes et quune fonctions d'ensemble au moins apparat dans linstruction SELECT.39

La commande SELECTSi vous avez une condition qui sapplique sur une fonctions d'ensemble utilis ou non dans la requte la clause WHERE ne sera plus valable, vous devez utilis une deuxime clause. il faut utiliser la clause HAVING, rserve pour les fonctions dagrgation, la place de la clause WHERE dans linstruction SQL.

40

La commande SELECTSELECT store_name, SUM(sales) FROM Store_Information GROUP BY store_name HAVING SUM(sales) > 1500 ;

41

ExerciceELEVE(matricule_elv, nom_elv, prenom_elv, rue_elv, localite_elv, cp_elv, age, sexe, tel, code_dpt#) DEPARTEMENT(code_dpt, niveau, date_debut, tarif, dt_inscription, lib_dpt, id_salle#) SALLE(id_salle, description, type, batiment) THEME(code_theme, des_theme, langue, code_dpt#, no_prof#) PROF(no_prof, nom_prof, prenom_prof, rue_prof, localite_prof, cp_prof)

Etablir une liste avec toutes les donnes des diffrents lves. Afficher par ordre alphabtique le prnom des lves qui sont en btiment C. Afficher le nom et le code postal des professeurs qui tiennent des thmes en franais. Faire une liste dlves en indiquant le tarif et le code postal pour tous les lves qui ont leur salle dans le btiment C et ceci dans lordre dcroissant des numros de tlphone regroup par dpartement. Calculer lge moyen des lves intresss dans Atomthorie Quelle dpartement inscrit plus de 20 lves. Combien dlves participent au thme La future entreprise . Liste des professeurs qui enseigne plus de 3 thmes dans le btiment C. Afficher les noms, le prnoms et les numros de tlphone des llves qui ont pays plus pour tre inscrit.42

La commande SELECTLes sous-requtes :On peut imbriquer autant de requtes que l'on veut. La condition aprs la clause WHERE peut porter sur le rsultat d'une autre requte (ou sous-requte).

43

La commande SELECTSupposons les tables suivantes :

Cette table contient, pour chaque acheteur, le produit qu'il a achet et le prix d'achat. Nous voulons la liste des acheteurs ayant achet des articles chers. Nous considrerons qu'un article cher est un article dont le prix est suprieur la moyenne du prix des produits achets + 100 DH.

44

La commande SELECTSELECT Num_acheteur FROM Vente WHERE prix > (( SELECT AVG (prix) FROM Vente) + 100) ;

Vous pouvez constater que condition de la requte est base sur le rsultat d'une autre requte. Dans cet exemple, chaque fois qu'un acheteur aura achet un article cher, son numro apparatra, pour viter cela, on utilise la clause DISTINCT num_acheteur pour liminer les doubles.45

La commande CREATE VIEWLes vues peuvent tre considres comme des tables virtuelles. Gnralement, une table contient un jeu de dfinitions et elle est destine stocker physiquement les donnes. Une vue a galement un jeu de dfinitions, cr au-dessus des tables ou dautres vues, et elle ne stocke pas physiquement les donnes.

46

La commande CREATE VIEWLa syntaxe pour la cration dune vue est comme suit :CREATE VIEW nom de vue AS requte SQL

47

La commande CREATE VIEWet pour crer une vue appele V_Client_Kech contenant seulement les colonnes Nom, Prnom, Age de la table Client, il faut saisir :CREATE VIEW V_Client_Kech AS SELECT Nom,Prnom, Age FROM Client WHERE ville = 'Marrakech';48

La commande CREATE VIEWl est aussi possible dutiliser une vue comme source dune autre requte SQL :SELECT * FROM V_Client_Kech WHERE Age BETWEEN 30 and 50;

49

La commande CREATE VIEWLa spcification des noms des colonnes de la vue est facultative : par dfaut, les colonnes de la vue ont pour nom les noms des colonnes rsultats du SELECT. Si certaines colonnes rsultats du SELECT sont des expressions sans nom, il faut alors obligatoirement spcifier les noms de colonnes de la vue.Le SELECT peut contenir toutes les clauses d'un SELECT, sauf la clause ORDER BY.50

Les manipulations la BDUne fois les tables cres, on peut commencer y insrer des donnes, les mettre jour, les supprimer. Toutes ces oprations sont des oprations de manipulation des bases de donnes. Pour effectuer ces manipulations, SQL dis pose de 3 instructions :INSERT UPDATE DELETE51

La commande INSERTLa commande INSERT est utilise pour ajouter des enregistrements ou des parties d'enregistrements dans des tables. Elle est utilise gnralement sous deux formes :

52

La commande INSERT1re forme :INSERT INTO table (champ1,champ2,...) VALUES ('valeur1','valeur2',...);Cette forme est utilise lorsqu'on veut insrer un seul enregistrement ou une partie d'un seul enregistrement. On crera un nouvel enregistrement dont le contenu du champ1 sera valeur1, le contenu du champ2 sera valeur2, etc...53

La commande INSERT2me forme :INSERT INTO table (champ1,champ2,...) (requte SELECT);

Dans cette seconde forme, le rsultat de la requte va tre insr dans les champs indiqus de la table. Cette mthode est utilise lorsque plusieurs enregistrements sont ajouts simultanment.54

La commande INSERTDans les deux cas, les valeurs insres doivent correspondre au type de donnes du champ dans lequel l'insertion va tre faite, on ne peut pas, par exemple demander l'insertion d'une chane de caractres dans un champ de type numrique ou montaire. Les chanes de caractres doivent tre places entre apostrophes ( ' ), les champs numriques ou vides (NULL) ne doivent pas tre placs entre apostrophes.55

La commande INSERTSi des valeurs doivent tre insres dans tous les champs de l'enregistrement de la table, la liste des noms des champs n'a pas besoin d'tre explicitement indique dans la commande. Les valeurs des champs insrer doivent cependant apparatre dans le mme ordre que les noms des champs lors de la cration de la table, sans oublier un seul champ.INSERT INTO table VALUES ('valeur1','valeur2','valeur3',...);56

La commande INSERTPar exemple, dans notre table Client, si nous voulons insrer un nouveau client, nous allons entrer :INSERT INTO Clients VALUES (100,'Mr','Dawali','Jalil','Medina N44',40000,'Marrakech');

57

La commande UPDATELa commande UPDATE est utilise pour changer des valeurs dans des champs d'une table. Sa syntaxe est :UPDATE table SET champ1 = nouvelle_valeur1, champ2 = nouvelle_valeur2, champ3 = nouvelle_valeur3 WHERE condition;58

La commande UPDATE

La clause SET indique quels champs de la table vont tre mis jour et avec quelles valeurs ils vont l'tre. Les champs non spcifis aprs la clause SET ne seront pas modifis.

59

La commande UPDATEPar exemple, si nous voulons, dans la table produit, modifier le prix d'un produit dont le nom est "prod1", nous taperons :UPDATE produits SET prix_unitaire = 1000 WHERE libelle = 'prod1';

60

La commande UPDATELa commande UPDATE affecte tous les enregistrements qui rpondent la condition donne dans la clause WHERE. Si la clause WHERE est absente, tous les enregistrements de la table seront affects.

61

La commande UPDATEPar exemple, si nous tapons :UPDATE produits SET prix_unitaire = 1000;

Le prix unitaire de TOUS les produits de la table produit va tre modifi.

62

La commande UPDATETout comme la commande INSERT, la commande UPDATE peut contenir une requte. Dans ce cas la syntaxe est la suivante :

UPDATE table SET champ1 = nouvelle_valeur1, champ2 = nouvelle_valeur2, champ3 = nouvelle_valeur3 WHERE condition = (requte SELECT);63

La commande DELETEPour supprimer des enregistrements d'une table, utilisez la commande DELETE. La syntaxe est la suivante :DELETE FROM table WHERE condition;

64

La commande DELETEOn ne peut pas supprimer seulement le contenu de quelques champs des enregistrements. La commande DELETE supprime des enregistrements entiers, c'est pour cela qu'il n'est pas ncessaire d'indiquer ici des noms de champs. La condition spcifie aprs WHERE va dterminer quels sont les enregistrements supprimer.

65

La commande DELETEPar exemple, pour supprimer tous les clients dont la ville est Marrakech :DELETE FROM Clients WHERE ville='Marrakech';

66

La commande DELETEPour supprimer tous les enregistrements d'une table, n'indiquez pas de clause WHERE : DELETE FROM table;Cette variante de la commande DELETE ne supprime pas la table, elle supprime seulement les enregistrements contenus dans cette table et laisse une table vide.67

La commande DELETEOn peut aussi, comme prcdemment utiliser une requte qui servira dterminer la condition de la suppression. La syntaxe est la suivante : DELETE FROM table WHERE condition = ( requte SELECT);68

ExerciceCONTACT (NumCont, CivCont, NomCont, PrenCont, EmailCont, #NumCli) CLIENT (NumCli, DesignCli, AdresseCli, CPCli, VilleCli, TelCli) INTERVENIR (NumCli, NumTech, Date, Heure, Duree, Probleme) TECHNICIEN (NumTech, NomTech, PrenTech) (Les Dure des interventions sont en minutes.)

Afficher les dates, heure et dure des interventions, dans lordre chronologie, du technicien Ahmed Laalaj pour le mois de septembre 2006. Compter le nombre dinterventions de plus dune heure. De combien de temps dintervention a dj bnfici la SARL Harvia sonna. Quelle est la dure moyenne des interventions en 2006. Quelle est le contact ayant demand le plus grand nombre dintervention. Modifier la dure de lintervention du technicien T007. La dure nest pas de 1,50 heure mais de 2 heures. Ajouter un nouveau contact pour le client 41100985. Il sagit de Mademoiselle Fatima Nassimi. Elle se verra attribuer le numro de contact 41100985A, avec lemail [email protected].

69

LDD (Langage de dfinition des donnes)Ce chapitre dcrit les instructions SQL qui permette de dcrire les tables et autres objets manipuls par le SGBD.

70

Cration d'une tableL'ordre CREATE TABLE permet de crer une table dfinissant le nom et le type de chacune des colonnes la table. Nous ne verrons ici que trois des types donnes utiliss dans SQL : numrique, chane caractres et date. en de de de

Pour pouvoir crer une table dans votre base, il faut que vous ayez reu le privilge CREATE. Le mcanisme des privilges est dcrit dans le chapitre suivant.

71

Cration d'une table

table est le nom que l'on donne la table ; colonne1, colonne2,.. sont les noms des colonnes ; type1, type2,.. sont les types des donnes qui seront contenues dans les colonnes.

72

Types des colonnesPour dcrire les colonnes dune table, les SGBD fournit les types prdfinis suivants :Type CHAR(n)VARCHAR(n) VARCHAR2(n) NUMBER[(t,d)] INTEGER[(n)] DECIMAL[(n,p)]

SGBDMYSQL et ORACLE MYSQL ORACLE ORACLE MYSQL MYSQL MYSQL et ORACLE

DescriptionChane fixe de n octets Chane variable de n octets Chane variable de n octets Valeur numrique Valeur entire Dcimal virgule fixeDates du 1er janvier de lan 1000 au 31 dcembre 9999 aprs J.-C.

Commentaire Taille fixe (maximum de 255). Taille variable (maximum de 65 535) Taille variable (maximum de 65 535) Le (t,d) est facultatif, t chiffres dont d dcimales. Entier (sur 4 octets), Le (n) est facultatif p dsigne la prcision (nombre de chiffres aprs la virgule Sur 3 octets. Laffichage est au format YYYY-MM-DD.73

DATE

Types des colonnesExemple :CREATE TABLE Compagnie ( numcomp INTEGER(4), nomcomp CHAR(15), villecomp CHAR(20), adresse VARCHAR(400) );numcomp--------------

nomcomp--------------------

villecomp

adresse--------------------------------------------------------------------

0012--------------

Harvia SONA--------------------

Marrakech Massira 3 N 146 Rue des --------------------------------------------------------------------

74

ContraintesLes contraintes ont pour but de programmer des rgles de gestion au niveau des colonnes des tables. Les contraintes peuvent tre dclares de deux manires :

En mme temps que la colonne (valable pour les contraintes monocolonnes) ; ces contraintes sont dites en ligne (inline constraints). Aprs que la colonne est dclare ; ces contraintes ne sont pas limites une colonne et peuvent tre personnalises par un nom (out-of-line constraints).

75

ContraintesIl est recommand de dclarer les contraintes NOT NULL et DEFAULT en ligne. Exemple : CREATE TABLE Compagnie ( numcomp INTEGER(4), nomcomp CHAR(15) NOT NULL, villecomp CHAR(20) DEFAULT Marrakech, adresse VARCHAR(400) );

76

Contraintestudions prsent les types de contraintes nommes (out-of-line). Les quatre types de contraintes les plus utilises sont les suivants :CONSTRAINT nomContrainte UNIQUE (colonne1 [,colonne2]...) PRIMARY KEY (colonne1 [,colonne2]...) FOREIGN KEY (colonne1 [,colonne2]...) REFERENCES nomTablePere [(colonne1 [,colonne2]...)] [ON DELETE {CASCADE | SET NULL}] [ON UPDATE {CASCADE | SET NULL}] CHECK (condition)77

Contraintes

La contrainte UNIQUE impose une valeur distincte au niveau de la table (les valeurs nulles font exception moins que NOT NULL soit aussi applique sur les colonnes). La contrainte PRIMARY KEY dclare la cl primaire de la table. Un index est gnr automatiquement sur la ou les colonnes concernes. Les colonnes cls primaires ne peuvent tre ni nulles ni identiques (en totalit si elles sont composes de plusieurs colonnes).

78

Contraintes

La contrainte FOREIGN KEY dclare une cl trangre entre une table enfant (child) et une table pre (parent). Ces contraintes dfinissent lintgrit rfrentielle. Les directives ON UPDATE et ON DELETE disposent de deux options : CASCADE propagera la suppression de tous les enregistrements fils rattachs lenregistrement pre supprim. SET NULL positionnera leur cl trangre a NULL. La contrainte CHECK impose un domaine de valeurs ou une condition simple ou complexe entre colonnes (exemple : CHECK (note BETWEEN 0 AND 20), CHECK (grade='Copilote' OR grade='Commandant')).79

ContraintesIl est recommand de ne pas dfinir de contraintes sans les nommer (bien que cela soit possible), car il sera difficile de les faire voluer (dsactivation, ractivation, suppression), et la lisibilit des programmes en sera affecte. Adoptez les conventions dcriture suivantes pour vos contraintes :

Prfixez par pk_ le nom dune contrainte cl primaire, fk_ une cl trangre, ck_ une vrification, un_ une unicit. Pour une contrainte cl primaire, suffixez du nom de la table la contrainte (exemple pk_Avion). Pour une contrainte cl trangre, renseignez (ou abrgez) les noms de la table source, de la cl, et de la table cible (exemple fk_Pil_compa_Comp).

80

Contraintes

Lordre nest pas important dans la dclaration des contraintes nommes. PRIMARY KEY quivaut : UNIQUE + NOT NULL. Lordre de cration des tables est important quand on dfinit les contraintes en mme temps que les tables. Il faut crer dabord les tables pres puis les tables fils. Le script de destruction des tables suit le raisonnement inverse.

81

Structure dune tableDESCRIBE (criture autorise DESC) est une commande qui vient de SQL*Plus dOracle et qui a t reprise par MySQL. Elle permet dextraire la structure brute dune table ou dune vue. DESCRIBE [nomBase.] nomTableouVue [colonne]; Ou DESC [nomBase.] nomTableouVue [colonne];

Si la base nest pas indique, il sagit de celle en cours dutilisation.

82

Exercice

La ville par dfaut de la compagnie est Casablanca. Les champs nomComp et nom du pilote ne sont pas NULL. Le champ compa dans la table Pilote est une cl trangre de la table Compagnie. Le nom du pilote doit tre unique dans la table. Le pilote doit obligatoirement avoir un nombre dheurs de vole entre 0 et 20000 heurs.83

volution dun schma

Lvolution dun schma est un aspect trs important prendre en compte, car il rpond aux besoins de maintenance des applicatifs qui utilisent la base de donnes. Nous verrons quil est possible de modifier une base de donnes dun point de vue structurel (colonnes) mais aussi comportemental (contraintes). Linstruction principalement utilise est ALTER TABLE (commande du LDD) qui permet dajouter, de renommer, de modifier et de supprimer des colonnes dune table. Elle permet aussi dajouter et de supprimer des contraintes.

Avant de dtailler ces mcanismes, tudions la commande qui permet de renommer une table.

84

Renommer une table (RENAME)

Linstruction RENAME renomme une ou plusieurs tables ou vues. Il faut possder le privilge ALTER et DROP sur la table dorigine, et CREATE sur la base.

Exemple : RENAME Pilote TO Naviguant;

85

Modifications structurelles(Renommer la table)

Il est aussi possible dutiliser loption RENAME TO de linstruction ALTER TABLE pour renommer une table existante. Lexemple suivant dcrit comment renommer la table avec linstruction ALTER TABLE :ALTER TABLE Pilote RENAME TO Naviguant;(Equivalant a linstruction RENAME Pilote TO Naviguant;)

86

Modifications structurelles(Ajout de colonnes)

La directive ADD de linstruction ALTER TABLE permet dajouter une nouvelle colonne une table. Cette colonne est initialise NULL pour tous les enregistrements ( moins de spcifier une contrainte DEFAULT, auquel cas tous les enregistrements de la table sont mis jour avec une valeur non nulle).

Syntaxe :ALTER TABLE [nomBase].nomTable ADD [COLUMN] nomColonne typeColonne [FIRST|AFTER nomColonne];

87

Modifications structurelles(Ajout de colonnes)

Le script suivant ajoute trois colonnes la table Pilote. La premire instruction insre la colonne nbHVol en linitialisant NULL pour tous les pilotes (ici il nen existe quune seule). La deuxime commande ajoute deux colonnes initialises une valeur non nulle. La colonne ville ne sera jamais nulle.

88

Modifications structurelles(Ajout de colonnes)

Linstruction ADD, permet dajouter une colonne a la fin de la table, vous pouvez modifier lemplacement dinsertion avec les deux instructions FIRST et AFTER.

Exemple :ALTER TABLE Pilote ADD (nbHVol DECIMAL(7,2)) AFTER ville;

ALTER TABLE Pilote ADD (id INTEGER(2)) FIRST;

89

Modifications structurelles(Renommer des colonnes)

Il faut utiliser loption CHANGE de linstruction ALTER TABLE pour renommer une colonne existante. Le nouveau nom de colonne ne doit pas tre dj utilis dans la table. Le type (avec une ventuelle contrainte) doit tre reprcis. La position de la colonne peut aussi tre modifie en mme temps. La syntaxe gnrale de cette option est la suivante :ALTER TABLE [nomBase].nomTable CHANGE [COLUMN] ancienNom nouveauNom typeColonne [NOT NULL | NULL] [DEFAULT valeur] [FIRST|AFTER nomColonne];

90

Modifications structurelles(Renommer des colonnes)

Linstruction suivante permet de renommer la colonne ville en adresse en la positionnant aprs la colonne nbHVol :ALTER TABLE Pilote CHANGE ville adresse VARCHAR(30) AFTER nbHVol;

91

Modifications structurelles(Modifier le type des colonnes)

Loption MODIFY de linstruction ALTER TABLE modifie le type dune colonne existante sans pour autant la renommer. La syntaxe gnrale de cette instruction est la suivante, les options sont les mmes que pour CHANGE :ALTER TABLE [nomBase].nomTable MODIFY [COLUMN] nomColonneAmodifier typeColonne [NOT NULL | NULL] [DEFAULT valeur] [FIRST|AFTER nomColonne];

92

Modifications structurelles(Modifier le type des colonnes)

Il est possible daugmenter la taille dune colonne numrique (largeur ou prcision) ou dune chane de caractres (CHAR et VARCHAR) ou de la diminuer si toutes les donnes prsentes dans la colonne peuvent sadapter la nouvelle taille. Les contraintes en ligne peuvent tre aussi modifies par cette instruction. Une fois la colonne change, les nouvelles contraintes sappliqueront aux mises jour ultrieures de la table, et les donnes prsentes devront toutes vrifier cette nouvelle contrainte.

93

Modifications structurelles(Modifier le type des colonnes)

Le tableau suivant prsente diffrentes modifications de colonnes :

94

Modifications structurelles(Valeurs par dfaut)

Loption ALTER COLUMN de linstruction ALTER TABLE modifie la valeur par dfaut dune colonne existante. La syntaxe gnrale de cette instruction est la suivante :ALTER TABLE [nomBase].nomTable ALTER [COLUMN] nomColonneAmodifier {SET DEFAULT chaine | DROP DEFAULT};

Le script suivant dfinit une valeur par dfaut pour la colonne adresse puis supprime celle relative la colonne compa :

95

Modifications structurelles(Supprimer des colonnes)

Loption DROP de linstruction ALTER TABLE permet de supprimer une colonne. La possibilit de supprimer une colonne vite aux administrateurs dexporter les donnes, de recrer une nouvelle table, dimporter les donnes et de recrer les ventuels contraintes. Lorsquune colonne est supprime. La syntaxe des ces options est la suivante :ALTER TABLE [nomBase].nomTable DROP { [COLUMN] nomColonne | PRIMARY KEY | FOREIGN KEY nomContrainte}

96

Modifications structurelles(Supprimer des colonnes)

Il nest pas possible de supprimer avec cette instruction : toutes les colonnes dune table ; les colonnes qui sont cls primaires (ou candidates par UNIQUE) rfrences par des cls trangres.

Exemple : La suppression de la colonne adresse de la table Pilote est programme par linstruction suivante :

97

Modifications structurelles(Rsum)ALTER TABLE [nomBase].nomTable (RENAME TO nouveau_nomTable)

(ADD [COLUMN] nomColonne typeColonne [FIRST|AFTER nomColonne])(CHANGE [COLUMN] ancienNom nouveauNom typeColonne[NOT NULL | NULL] [DEFAULT valeur] [FIRST|AFTER nomColonne])

(MODIFY [COLUMN] nomColonneAmodifier typeColonne [NOT NULL | NULL][DEFAULT valeur] [FIRST|AFTER nomColonne] )

(ALTER [COLUMN] nomColonne {SET DEFAULT chaine | DROP DEFAULT}) (DROP { [COLUMN] nomColonne | PRIMARY KEY | FOREIGN KEYnomContrainte}) ;

98

Modifications comportementales

Nous tudions dans cette section les mcanismes dajout, de suppression, dactivation et de dsactivation de contraintes. Faisons voluer le schma suivant. Les seules contraintes existantes sont les cls primaires nommes pk_Compagnie, pour la table Compagnie, et pk_Avion pour la table Avion.

99

Modifications comportementales(Ajout de contraintes)

La directive ADD CONSTRAINT de linstruction ALTER TABLE permet dajouter une contrainte une table. La syntaxe gnrale est la suivante :ALTER TABLE [nomBase].nomTable ADD CONSTRAINT nomContrainte typeContrainte

Trois types de contraintes sont possibles :UNIQUE (colonne1 [,colonne2]...) PRIMARY KEY (colonne1 [,colonne2]...) FOREIGN KEY (colonne1 [,colonne2]...) REFERENCES nomTablePre (col1 [,col2]...) [ON DELETE {CASCADE | SET NULL}] [ON UPDATE {CASCADE | SET NULL}]100

Modifications comportementales(Ajout de contraintes)

Ajoutons la contrainte dunicit portant sur la colonne du nom de la compagnie.

Ajoutons la cl trangre la table Avion au niveau de la colonne proprio

101

Modifications comportementales(Ajout de contraintes)

Ajoutons la table Affreter, en une seule instruction, sa cl primaire et deux cls trangres (une vers la table Avion et lautre vers Compagnie) :

102

Modifications comportementales(suppression de contraintes)

Il nexiste pas encore loption DROP CONSTRAINT linstruction ALTER TABLE pour supprimer une contrainte de nom donne Il faut donc utiliser une directive diffrente de linstruction ALTER TABLE pour supprimer chaque type de contrainte.Contrainte NOT NULL :ALTER TABLE Avion MODIFY proprio CHAR(4) NULL;

103

Modifications comportementales(suppression de contraintes)

Contrainte Cl trangre :ALTER TABLE [nomBase].nomTable DROP FOREIGN KEY nomContrainte;

Contrainte Cl primaire :ALTER TABLE Affreter DROP PRIMARY KEY;

104

Modifications comportementales(suppression de contraintes)

Attention !! : Si la colonne cl primaire supprimer contient des cls trangres, il faut dabord retirer les contraintes de cl trangre. Si la cl primaire supprimer est rfrence par des cls trangres dautres tables, il faut dabord ter les contraintes de cl trangre de ces autres tables.

105

Exercice

Renommer le champ compa dans la table pilote par comp Supprimer le champ nrue de la table Compagnie Ajouter le champ ville aprs le nom dans la table Pilote avec une valeur par dfaut quivalente a Casablanca Supprimer la contrainte valeur par dfaut pour le champ ville dans la table compagnie Renommer la table compagnie par company.106

Suppression d'une tablePour pouvoir supprimer une table dans une base, il faut possder le privilge DROP sur cette base. Linstruction DROP TABLE entrane la suppression des donnes, de la structure et la description dans le dictionnaire des donnes. DROP TABLE [IF EXISTS] [nomBase.] nomTable1 [,[nomBase2.]nomTable2,...] [RESTRICT | CASCADE]

107

Suppression d'une table

IF EXISTS : permet dviter quune erreur se produise si la table nexiste pas. RESTRICT et CASCADE ne sont pas encore oprationnels. Le premier permettra de vrifier quaucun autre lment nutilise la table (vue par exemple). Le second rpercutera la destruction tous les lments rfrencs.

Les lments qui utilisaient la table (vues, synonymes, fonctions et procdures) ne sont pas supprims mais sont temporairement inoprants. Attention, une suppression ne peut pas tre par la suite annule.108

Ordre des suppressionsIl suffit de relire lenvers le script de cration de vos tables pour en dduire lordre de suppression crire dans le script de destruction de votre schma. Exemple: (Les fils puis les pres )DROP TABLE Pilote; DROP TABLE Compagnie;

109

LCD (Langage de contrle des donnes)Dans ce chapitre nous verrons comment crer un utilisateur et comment lui attribuer des privilges daccs selon les besoins, et aussi de pouvoir les retirer.

110

Contrle des donnesComme dans tout systme multi-utilisateur, lusager dun SGBD doit tre identifi avant de pouvoir utiliser des ressources. Les accs aux informations et la base de donnes doivent tre contrls des fins de scurit et de cohrence.

111

Cration dun utilisateur (CREATE USER)

Pour pouvoir crer un utilisateur, vous devez possder le privilge CREATE USER ou INSERT sur la base systme mysql.CREATE USER utilisateur [IDENTIFIED BY [PASSWORD] 'motdePasse'];

les utilisateurs, une fois crs, nont aucun droit sur aucune base de donnes

112

Renommer un utilisateur (RENAME USER)

Pour pouvoir renommer un utilisateur, vous devez possder le privilge CREATE USER (ou le privilge UPDATE sur la base de donnes mysql). La syntaxe SQL est la suivante :RENAME USER utilisateur TO nouveauNom;

Penser spcifier laccs complet renommer (user@machine). Les privilges et le mot de passe ne changent pas.

113

Suppression dun utilisateur (DROP USER)

Pour pouvoir supprimer un utilisateur, vous devez possder le privilge CREATE USER (ou le privilge DELETE sur la base de donnes mysql). La syntaxe SQL est la suivante :DROP USER utilisateur [,utilisateur2 ...];

Il faut spcifier laccs liminer (user@machine). Tous les privilges relatifs cet accs sont dtruits. Si lutilisateur est connect dans le mme temps, sa suppression ne sera effective qu la fin de sa (dernire) session.114

Attribution de privilges (GRANT)

Linstruction GRANT permet dattribuer un (ou plusieurs) privilge(s) propos dun objet un (ou plusieurs) bnficiaire(s). Lutilisateur qui excute cette commande doit avoir reu lui mme le droit de transmettre ces privilges (reu avec la directive GRANT OPTION). Dans le cas de root, aucun problme, car il a implicitement tous les droits.

115

Attribution de privilges (GRANT)GRANT privilge [ (col1 [, col2...])] [,privilge2 ... ] ON [ {TABLE} ] {nomTable | * | *.* | nomBase.*} TO utilisateur@host [IDENTIFIED BY [PASSWORD] 'password] [WITH GRANT OPTION]; privilge : description du privilge (ex : SELECT, DELETE, etc.), voir le tableau suivant. GRANT OPTION : permet de donner le droit de retransmettre les privilges reus une tierce personne.116

Attribution de privilges (GRANT)

117

Exemples

Adil (en accs local) peut crer ou supprimer des tables dans la base DB.GRANT CREATE, DROP ON DB.* TO ADIL@localhost;

Adil peut insrer, extraire, supprimer et modifier la colonne ville de la table Livre contenue dans la base DB. Il pourra par la suite retransmettre ventuellement ces droits.GRANT INSERT, SELECT, DELETE, UPDATE(ville) ON DB.CLIENT TO ADIL@localhost WITH GRANT OPTION;

118

Voir les privilges

La commande SHOW GRANTS FOR liste les diffrentes instructions GRANT quivalentes toutes les prrogatives dun utilisateur donn. Cest bien utile quand vous avez attribu un certain nombre de privilges un utilisateur sans avoir pens les consigner dans un fichier de commande. SHOW GRANTS FOR utilisateur;

119

Rvocation de privilges (REVOKE)

La rvocation dun ou de plusieurs privilges est ralise par linstruction REVOKE. Pour pouvoir rvoquer un privilge, vous devez dtenir (avoir reu) au pralable ce mme privilge avec loption WITH GRANT OPTION. Dans la syntaxe suivante, les options sont les mmes que pour la commande GRANT:REVOKE privilge [ (col1 [, col2...])] [,privilge2 ... ] ON [ {TABLE | FUNCTION | PROCEDURE} ] {nomTable | * | *.* | nomBase.*} FROM utilisateur [,utilisateur2 ... ];

120

Rvocation de privilges (REVOKE)

ADIL (en accs local) ne peut plus crer de tables dans la base DB.REVOKE CREATE ON DB.* FROM ADIL;

Tout en une fois ! Il existe une instruction qui rvoque tous les droits en une fois. utilisez REVOKE ALL PRIVILEGES.REVOKE ALL PRIVILEGES, GRANT OPTION FROM utilisateur [, utilisateur2 ...] ;

121