apprendre le langage sql par l’exemple partie 1 : le ddl · le sql est un langage dont l’objet...

Apprendre le langage SQL - Le DDL - 1 / 28 -

Denis Szalkowski Formateur Consultant © – http://www.dsfc.net Licence Creative Commons CC-by-nc-nd Version 1.0 - 17/01/2013

Apprendre le langage SQL par l’exemple

Partie 1 : le DDL

Ce document est publié sous licence Creative Commons CC-by-nc-nd. Il ne

peut ni être modifié, ni faire l’objet d’une exploitation commerciale par un

centre de formation, une collectivité territoriale, une association ou une

entreprise.



Historique

• 1969-1970 : Edgar Frank Codd, salarié d’IBM définit les principes du

modèle relationnel.

• 1976 : création du SEQUEL par IBM

• 1977 :QUEL (QUEry Language), par Zook en 1977

• QBE (Query By Example), par Zloof

• 1981 : SQL (Structured Query Language ), par IBM



Normalisation du SQL

• 1986 : SQL 86 - ANSI

• 1989 : ISO et ANSI

• 1992 : SQL 2 - ISO et ANSI

• 1999 : SQL 3 - ISO



Les 3 sous-langages

Le SQL est un langage dont l’objet principal est de pouvoir manipuler les

données d’un système d’informations.

• LDD (Langage de Définition de Données) ou DDL (Data Definition

Language) Création, modification et suppression des objets (tables, index, séquences, déclencheurs

ou triggers, vues, triggers, synonymes, liens de bases de données, etc.)

CREATE, ALTER, DROP

• LMD (Langage de Manipulation de Données) ou DML (Data

Manipulation Language) Ajout, modification, suppression et extraction des données

INSERT, UPDATE, DELETE, SELECT

• LCD (Langage de Contrôle de Données) ou CDL (Control Data

Language)



Définition d’une base de données

Une base de données est un dispositif de stockage des données - total ou

partiel - du système d’information de l’entreprise.

Les objectifs (liste non exhaustive)

1. Assurer la cohérence des données

2. Eviter la redondance des informations

3. Extraire les informations

4. Sécuriser l’accès aux données

5. Assurer l’indépendance totale entre les données et les traitements

Les bases de données relationnelles obéissent à l’algèbre relationnel. Les

bases NoSQL se différencient par un stockage en mode colonne.



Marché de la base de données relationnelles

Nous ne disposons que de vieilles données sur les parts de marché des bases

de données relationnelles commerciales !

Tableau extrait de Zdnet



Les bases Open Source

Il est toujours de comparer sur la base de leurs prix des produits

commerciaux à des produits Open Source dont la valeur d’achat est nulle !

• MySQL, qui ne gère toujours pas à ce jour les contraintes de type

CHECK !

• PostgreSQL, un fork de INGRES, qui a su prendre beaucoup

d’autonomie

• Ingres, un des tout meilleurs moteurs de bases de données

relationnelles

• Firebird, un fork de Interbase

http://dev.mysql.com/downloads/

http://www.postgresql.org/download/

http://www.actian.com/downloads/ingres#community

http://www.firebirdsql.org/en/downloads/

http://www.dsfc.net/



Les usages en matière de bases de données

Le site DBeaver a récemment un

sondage organisé sur les usages en

matière de bases de données. Il en

ressort que MySQL serait en 1ère

position, devant Oracle Database,

puis ex aequo Microsoft SQL Server

et PostgreSQL.

http://dbeaver.jkiss.org/



Approche systémique



MERISE

La méthode MERISE est une méthode d’analyse du fonctionnement des

organisations qui s’inspire en très grande partie de l’approche systémique.

Initiée par l’INRIA, elle a vu le jour en 1979.

1. Objectifs assignés par la direction

2. Recueil des données par des interviews des opérationnels

3. Formalisation par un dictionnaire des données

4. Modèle Conceptuel des Données 5. Modèle Logique des Données

6. Modèle Physique des Données Pour plus d’information à ce sujet, vous pouvez consulter mon support de

cours relatif à Merise.

http://www.dsfc.net/wp-content/uploads/supports/SupportMerise.pdf



Pré-requis

Pour pouvoir mettre en œuvre les exercices présentés dans ce document,

vous devrez installer le moteur de base de données MySQL 5.5et MySQL

Workbench à partir de ce lien.

Ces deux logiciels s’installent indifféremment sur Windows, sous Mac OS X ou

sur Linux. Pour Linux, il est recommandé toutefois de passer par le

gestionnaire de paquets de sa distribution : yum, apt, zypper, urpmi, etc.

Documentation

Internet regorge d’informations sur le SQL. Je vous recommande, pour ma

part, d’aller directement à la source, sur le site de l’éditeur.

http://dev.mysql.com/downloads/

http://dev.mysql.com/doc/refman/5.5/en/



Installation de MySQL

Par défaut, l’accès à la base de données avec le compte root n’est possible

que localement ! Prenez soin de bien notre le mot de passe que vous avez

affecté au compte root.



Connexion via MySQL Workbench

Lancez MySQL Workbench. En bas à gauche, choisissez New Connection. Pour

mémoriser le mot de passe utilisé pour la connexion, cliquez sur le bouton

Store in Vault.



Le Modèle Physique des Données

L’exemple sur lequel nous nous appuierons est une application de Gestion de

la Relation Clientèle, basée sur deux entités dans le Modèle Conceptuel des

Données, transformées en tables dans le Modèle Physique des Données.



Création de la base de données

Pour créer la base de données :

• CREATE SCHEMA grc;

• CREATE DATABASE grc;

Pour la supprimer, remplacez CREATE par l’instruction DROP.

Pour vous placer dans la base, utilisez USE. Pour visualiser les bases d’une

instance MySQL, tapez l’instruction SHOW DATABASES. Et pour voir les

tables dans le contenu d’une base, exécutez la commande SHOW TABLES.



Principaux type de données

Catégorie Type

Numérique

entier

INTEGER, INT,

SMALLINT, TINYINT, MEDIUMINT, BIGINT

Numérique

Réel

DECIMAL, NUMERIC,

FLOAT, DOUBLE

Caractère

CHAR, VARCHAR

BINARY, VARBINARY

BLOB, TEXT

Date

DATE, DATETIME, TIMESTAMP,

TIME

YEAR



Contraintes

Elles sont issues de l’analyse Merise et des règles de gestion du

dictionnaire de données.

1. La clé primaire

Elle identifie la ligne de manière unique dans la mise en relation de

l’enregistrement avec les lignes d’une autre table

2. La contrainte d’unicité

Elle évite d’avoir des doublons sur le contenu des champs, en dehors de

ceux qui constituent la clé primaire.

3. Les contraintes CHECK

Elles permettent d’assurer la cohérence des données saisies. MySQL ne

prend en charge que les contraintes de type NOT NULL.

4. Les contraintes d’intégrité référentielle

Elles évitent d’inscrire des données dans une table « fille », si la référence

n’existe pas préalablement dans la table « parent ».

http://www.dsfc.net/wp-content/uploads/supports/SupportMerise.pdf



Création de table

MySQL dispose de différents moteurs. InnoDB est à la fois relationnel et

transactionnel.

CREATE TABLE grc.client (CREATE TABLE grc.client (CREATE TABLE grc.client (CREATE TABLE grc.client ( client_id INT(6) NOT NULL AUTO_INCREMENT ,client_id INT(6) NOT NULL AUTO_INCREMENT ,client_id INT(6) NOT NULL AUTO_INCREMENT ,client_id INT(6) NOT NULL AUTO_INCREMENT , client_raisonsociale VARCHAR(50) NOT NULL ,client_raisonsociale VARCHAR(50) NOT NULL ,client_raisonsociale VARCHAR(50) NOT NULL ,client_raisonsociale VARCHAR(50) NOT NULL , client_cp INT(6) NOT NULL ,client_cp INT(6) NOT NULL ,client_cp INT(6) NOT NULL ,client_cp INT(6) NOT NULL , client_ville VARCHAR(50) NULL ,client_ville VARCHAR(50) NULL ,client_ville VARCHAR(50) NULL ,client_ville VARCHAR(50) NULL , PRIMARY KEY (client_id) ,PRIMARY KEY (client_id) ,PRIMARY KEY (client_id) ,PRIMARY KEY (client_id) , INDEX client_idx_ville (client_ville ASC) ,INDEX client_idx_ville (client_ville ASC) ,INDEX client_idx_ville (client_ville ASC) ,INDEX client_idx_ville (client_ville ASC) , UNIQUE INDEX client_uk (cUNIQUE INDEX client_uk (cUNIQUE INDEX client_uk (cUNIQUE INDEX client_uk (client_raisonsociale ASC, client_cp ASC) )lient_raisonsociale ASC, client_cp ASC) )lient_raisonsociale ASC, client_cp ASC) )lient_raisonsociale ASC, client_cp ASC) ) ENGINE = InnoDB;ENGINE = InnoDB;ENGINE = InnoDB;ENGINE = InnoDB;



Création d’un déclencheur (trigger)

Ce trigger permet de convertir un champ automatiquement en majuscules

avec la fonction UPPER à chaque ajout de client !

DELIMITER $$DELIMITER $$DELIMITER $$DELIMITER $$ DROP TRIGGER IF EXISTS grc.DROP TRIGGER IF EXISTS grc.DROP TRIGGER IF EXISTS grc.DROP TRIGGER IF EXISTS grc.client_before_insert$$client_before_insert$$client_before_insert$$client_before_insert$$ CREATE TRIGGER grc.client_before_insertCREATE TRIGGER grc.client_before_insertCREATE TRIGGER grc.client_before_insertCREATE TRIGGER grc.client_before_insert BEFORE INSERT ON grc.clientBEFORE INSERT ON grc.clientBEFORE INSERT ON grc.clientBEFORE INSERT ON grc.client FOR EACH ROWFOR EACH ROWFOR EACH ROWFOR EACH ROW BEGINBEGINBEGINBEGIN set NEW.client_ville=UPPER(NEW.client_ville);set NEW.client_ville=UPPER(NEW.client_ville);set NEW.client_ville=UPPER(NEW.client_ville);set NEW.client_ville=UPPER(NEW.client_ville); ENDENDENDEND $$$$$$$$ DELIMITER;DELIMITER;DELIMITER;DELIMITER;



Un trigger comme CKECK CONSTRAINT

Comme MySQL ne dispose toujours pas à ce jour de CHECK CONSTRAINT,

l’un des moyens de poser une contrainte est d’utiliser un TRIGGER ! Les

expressions régulières peuvent alors d’un très grand secours. Ajoutez un

trigger sur l’événement BEFORE UPDATE.

DELIMITER $$DELIMITER $$DELIMITER $$DELIMITER $$ DROP TRIGGER grc.contact_before_inseDROP TRIGGER grc.contact_before_inseDROP TRIGGER grc.contact_before_inseDROP TRIGGER grc.contact_before_insert $$rt $$rt $$rt $$ CREATE TRIGGER grc.contact_before_insert CREATE TRIGGER grc.contact_before_insert CREATE TRIGGER grc.contact_before_insert CREATE TRIGGER grc.contact_before_insert BEFORE INSERT ON grc.contactBEFORE INSERT ON grc.contactBEFORE INSERT ON grc.contactBEFORE INSERT ON grc.contact FOR EACH ROW BEGINFOR EACH ROW BEGINFOR EACH ROW BEGINFOR EACH ROW BEGIN IF NOT NEW.contact_email REGEXP '^[aIF NOT NEW.contact_email REGEXP '^[aIF NOT NEW.contact_email REGEXP '^[aIF NOT NEW.contact_email REGEXP '^[a----z0z0z0z0----9]+@[a9]+@[a9]+@[a9]+@[a----z0z0z0z0----9]+9]+9]+9]+\\\\.(fr|com)$' THEN.(fr|com)$' THEN.(fr|com)$' THEN.(fr|com)$' THEN SET NEW.contact_email = NULL;SET NEW.contact_email = NULL;SET NEW.contact_email = NULL;SET NEW.contact_email = NULL; END IF;END IF;END IF;END IF; END;END;END;END; $$$$$$$$ DELIMITER;DELIMITER;DELIMITER;DELIMITER;



Création d’un index

Le rôle d’un index est avant tout d’accélérer la recherche. Les index prennent

de l’espace de stockage. Ils peuvent ralentir l’écriture lors de mises à jour

importantes.

CREATE INDEX client_idx_raisonsocialeCREATE INDEX client_idx_raisonsocialeCREATE INDEX client_idx_raisonsocialeCREATE INDEX client_idx_raisonsociale ON grc.client (client_ville);ON grc.client (client_ville);ON grc.client (client_ville);ON grc.client (client_ville);

Pour être efficaces, les index doivent être tenus à jour.



Contrainte d’intégrité référentielle

CREATE TABLE grc.contact (CREATE TABLE grc.contact (CREATE TABLE grc.contact (CREATE TABLE grc.contact ( contact_id INT(6) NOT NULL AUTO_INCREMENT,contact_id INT(6) NOT NULL AUTO_INCREMENT,contact_id INT(6) NOT NULL AUTO_INCREMENT,contact_id INT(6) NOT NULL AUTO_INCREMENT, contact_nom VARCHAR(30) NOT NULL ,contact_nom VARCHAR(30) NOT NULL ,contact_nom VARCHAR(30) NOT NULL ,contact_nom VARCHAR(30) NOT NULL , contact_prenom VARCHAR(20) NOT NULL ,contact_prenom VARCHAR(20) NOT NULL ,contact_prenom VARCHAR(20) NOT NULL ,contact_prenom VARCHAR(20) NOT NULL , contact_fonction VARCHAR(50) NOTcontact_fonction VARCHAR(50) NOTcontact_fonction VARCHAR(50) NOTcontact_fonction VARCHAR(50) NOT NULL ,NULL ,NULL ,NULL , client_id INT(6) NOT NULL ,client_id INT(6) NOT NULL ,client_id INT(6) NOT NULL ,client_id INT(6) NOT NULL , PRIMARY KEY (contact_id) ,PRIMARY KEY (contact_id) ,PRIMARY KEY (contact_id) ,PRIMARY KEY (contact_id) , UNIQUE INDEX contact_uk (contact_nom ASC, contact_prenom ASC, client_id ASC,UNIQUE INDEX contact_uk (contact_nom ASC, contact_prenom ASC, client_id ASC,UNIQUE INDEX contact_uk (contact_nom ASC, contact_prenom ASC, client_id ASC,UNIQUE INDEX contact_uk (contact_nom ASC, contact_prenom ASC, client_id ASC, contact_fonction ASC) ,contact_fonction ASC) ,contact_fonction ASC) ,contact_fonction ASC) , INDEX contact_fk_client_idx (client_id ASC) ,INDEX contact_fk_client_idx (client_id ASC) ,INDEX contact_fk_client_idx (client_id ASC) ,INDEX contact_fk_client_idx (client_id ASC) , CONSTRAINT contact_fk_client FORCONSTRAINT contact_fk_client FORCONSTRAINT contact_fk_client FORCONSTRAINT contact_fk_client FOREIGN KEY (client_id )EIGN KEY (client_id )EIGN KEY (client_id )EIGN KEY (client_id ) REFERENCES grc.client (client_id ) REFERENCES grc.client (client_id ) REFERENCES grc.client (client_id ) REFERENCES grc.client (client_id ) ON DELETE RESTRICT ON DELETE RESTRICT ON DELETE RESTRICT ON DELETE RESTRICT ON UPDATE CASCADEON UPDATE CASCADEON UPDATE CASCADEON UPDATE CASCADE ););););



Modification de table

Ajout de champ/colonne

ALTER TABLE grc.contact ALTER TABLE grc.contact ALTER TABLE grc.contact ALTER TABLE grc.contact ADD COLUMN contact_mail VARCHAR(100) NOT NULLADD COLUMN contact_mail VARCHAR(100) NOT NULLADD COLUMN contact_mail VARCHAR(100) NOT NULLADD COLUMN contact_mail VARCHAR(100) NOT NULL AFTER contact_fonction;AFTER contact_fonction;AFTER contact_fonction;AFTER contact_fonction;

Renommer une colonne

ALTER TABLE grc.contact ALTER TABLE grc.contact ALTER TABLE grc.contact ALTER TABLE grc.contact CHANGE COLUMN contact_mail CHANGE COLUMN contact_mail CHANGE COLUMN contact_mail CHANGE COLUMN contact_mail contact_email VARCHAR(100) NOT NULL;contact_email VARCHAR(100) NOT NULL;contact_email VARCHAR(100) NOT NULL;contact_email VARCHAR(100) NOT NULL;



Suppression

La suppression d’une colonne de table se fait par la commande ALTER.

ALTER TABLE grc.contact ALTER TABLE grc.contact ALTER TABLE grc.contact ALTER TABLE grc.contact DROP COLUMN contact_mail;DROP COLUMN contact_mail;DROP COLUMN contact_mail;DROP COLUMN contact_mail;

La suppression d’une table entraîne celle des objets qui lui sont associés, à

savoir les déclencheurs et les index.

DROP TABLE contact;DROP TABLE contact;DROP TABLE contact;DROP TABLE contact;



Réparation, réindexation

En mode MyISAM, la réparation d’une table se réalise à l’aide de l’instruction

REPAIR TABLE. En InnoDB, la seule possibilité de réparer est de reconstruire

les tables :

ALTER TABLE grc.contact ENGINE = InnoDB;ALTER TABLE grc.contact ENGINE = InnoDB;ALTER TABLE grc.contact ENGINE = InnoDB;ALTER TABLE grc.contact ENGINE = InnoDB;

La réindexation en MyISAM ou InnoDB s’effectue à l’aide de la commande

OPTIMIZE TABLE.



Les privilèges

Ils s’appliquent à l’instance MySQL, au schéma (la base de données), à la

table, aux colonnes :

ALL [PRIVILEGES], ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE ALL [PRIVILEGES], ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE ALL [PRIVILEGES], ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE ALL [PRIVILEGES], ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TABLESPACE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DELETE, DROP, TABLESPACE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DELETE, DROP, TABLESPACE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DELETE, DROP, TABLESPACE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, FILE, GRANT OPTION, INDEX, INSERT, LOCK TABLES,EVENT, EXECUTE, FILE, GRANT OPTION, INDEX, INSERT, LOCK TABLES,EVENT, EXECUTE, FILE, GRANT OPTION, INDEX, INSERT, LOCK TABLES,EVENT, EXECUTE, FILE, GRANT OPTION, INDEX, INSERT, LOCK TABLES, PROCESS, PROCESS, PROCESS, PROCESS, PROXY, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SELECT, SHOW DATABASES, PROXY, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SELECT, SHOW DATABASES, PROXY, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SELECT, SHOW DATABASES, PROXY, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SELECT, SHOW DATABASES, SHOW VIEW, SHUTDOWN, SUPER, TRIGGER, UPDATE, USAGESHOW VIEW, SHUTDOWN, SUPER, TRIGGER, UPDATE, USAGESHOW VIEW, SHUTDOWN, SUPER, TRIGGER, UPDATE, USAGESHOW VIEW, SHUTDOWN, SUPER, TRIGGER, UPDATE, USAGE



GRANT / REVOKE

La commande GRANT permet d’accorder des privilèges :

GRANT SELECT,UPDATE ON grc.* TO usergrc@'%'GRANT SELECT,UPDATE ON grc.* TO usergrc@'%'GRANT SELECT,UPDATE ON grc.* TO usergrc@'%'GRANT SELECT,UPDATE ON grc.* TO usergrc@'%' IDENTIDENTIDENTIDENTIFIED BY 'grc' ;IFIED BY 'grc' ;IFIED BY 'grc' ;IFIED BY 'grc' ;

Pour que les ajouts, suppressions, modifications de droit soient appliquées,

exécutez la commande :

FLUSH PRIVILEGESFLUSH PRIVILEGESFLUSH PRIVILEGESFLUSH PRIVILEGES ;;;;

Pour modifier le mot de passe d’un utilisateur :

SET PASSWORD FOR usergrc@'%' =PASSWORD('grc');SET PASSWORD FOR usergrc@'%' =PASSWORD('grc');SET PASSWORD FOR usergrc@'%' =PASSWORD('grc');SET PASSWORD FOR usergrc@'%' =PASSWORD('grc');

La commande GRANT permet d’accorder des privilèges :

REVOKE UPDATE ON grc.client FROM usergrc@'%';REVOKE UPDATE ON grc.client FROM usergrc@'%';REVOKE UPDATE ON grc.client FROM usergrc@'%';REVOKE UPDATE ON grc.client FROM usergrc@'%';



Les événements

Pour visualiser l’heure de votre système :

SELECT NOW();SELECT NOW();SELECT NOW();SELECT NOW();

La création d’un événement est relative à la base de données :

DROP EVENT IF EXISTS grc.e_repair_grc;DROP EVENT IF EXISTS grc.e_repair_grc;DROP EVENT IF EXISTS grc.e_repair_grc;DROP EVENT IF EXISTS grc.e_repair_grc; DELIMITDELIMITDELIMITDELIMITER $$ER $$ER $$ER $$ CREATE EVENT grc.e_repair_grcCREATE EVENT grc.e_repair_grcCREATE EVENT grc.e_repair_grcCREATE EVENT grc.e_repair_grc ON SCHEDULE AT '2013ON SCHEDULE AT '2013ON SCHEDULE AT '2013ON SCHEDULE AT '2013----01010101----17 09:55:00'17 09:55:00'17 09:55:00'17 09:55:00' DO DO DO DO BEGINBEGINBEGINBEGIN REPAIR TABLE grc.client;REPAIR TABLE grc.client;REPAIR TABLE grc.client;REPAIR TABLE grc.client; REPAIR TABLE grc.contact;REPAIR TABLE grc.contact;REPAIR TABLE grc.contact;REPAIR TABLE grc.contact; ENDENDENDEND $$$$$$$$ DELIMITER ;DELIMITER ;DELIMITER ;DELIMITER ;

Pour visualiser les événements :

USE grc;USE grc;USE grc;USE grc; SHOW EVENTS;SHOW EVENTS;SHOW EVENTS;SHOW EVENTS;

apprendre le langage sql par l’exemple partie 1 : le ddl · le sql est un langage dont l’objet...

Documents