Méthode d’analyse et de conception

MERISE - SQLMéthode d'Etude et de Réalisation Informatique pour

les Systèmes d'Entreprise

Strutured Query Langage

sommaire

• MERISE– le modèle logique des données

• les règles de passage du MCD au MLD (modèle logique de donnée)– création d’un modèle relationnel par langage SQL grâce au MLD

• déclaration des champs d’une table• définition des contraintes d’intégrité • mode d'accès en écriture/lecture

• SQL – généralité des bases de données

• accès et de sécurité • database et dataspace

– syntaxe• les tables

– création / suppression– définition des champs– définition des contraintes

• les requêtes– sélection– d'insertion– mise à jour / suppression– sous requête

le modèle logique des données

• Le MCD fait précédemment rend compte le plus fidèlement possible de la réalité.

• Mais cette représentation ne peut pas être directement manipulé par le système, il faut donc passer par un niveau conceptuel prenant en compte les impératifs du système : – Modèle hiérarchique– Modèle réseau– Modèle relationnel

• Le SGBD (système de gestion de base de donnée) est le système que nous allons étudier.

Modèle relationnel

• C’est une modélisation reposant sur un système de table.

• table constitué de propriété (ou rubrique, ou champs) et répondant à des contraintes relationnelles (entre table, entre propriété).

• Ex : table personne

nom prenom

dupont claude

loiseau bernard

les règles de passage du MCD au MLD

• Toute entité devient une table

• L’identifiant de l’entité devient la clef de la table.

• Les relations du MCD sont traduite grâce à leur cardinalité– Si l’on a 0,1 ou 1,1 sur une entité alors celle-

ci admettra dans le MLD la clef étrangère de l’entité en relation.

les règles de passage du MCD au MLD

• Exemple :

• Devient :

ville(nomVille, nbHabitant, nomPays#)

pays(nomPays, PNB, PIB)

ville

#nomVillenbHabitant

pays

#nomPaysPNBPIB

appartient1,1 0,n

clef Clef étrangère#

les règles de passage du MCD au MLD

• Si l’on a 0,n ou 1,n sur les deux entités alors la relation deviendra une table portant les 2 clefs des entités ainsi que les autres propriétés nécessaires au modèle.

• Exemple :

• Donne

composer(refCommand#, refProd#, quant)

commande(refCommand)

produit(refProd, designation, prixHT, typProd)

commande

#refCommand

produit

#refProddesignationprixHTtypProd

composerquantité

1,n 0,n

• Traduction de l’héritage

1ère traduction :EMPLOYE(numEmploye,nom,prenom,

nomSSII,dateEmbauche)

2ème traduction : EMPLOYE(numEmploye,nom,prenom)EXTERNE(numEmploye#, nomSSII)INTERNE (numEmploye#, dateEmbauche)

3ème traduction (attention redondance d’info possible): EXTERNE(numEmploye,nom,prenom,nomSSII)INTERNE (numEmploye,nom,prenom,dateEmbauche)

les règles de passage du MCD au MLDEMPLOYE

#numEmployeNomprenom

EXTERNE

nomSSII

INTERNE

dateEmbauche

sommaire

• SQL – généralité des bases de données

• accès • database • sécurité

– syntaxe• les tables

– création / suppression– définition des champs– définition des contraintes

• les requêtes– sélection– d'insertion– mise à jour / suppression– sous requête

Accès et database

Pour accéder à une base de donnée SQL, il faut d’abord s’y connecter (user et password)

Ensuite on communique avec le SGBD par requête auquel il répond le résultat (mise à jour effectué, résultat de la sélection ou code erreur).

Ces connections avec le SGBD peuvent se faire de façon manuelle (par une console SQL) ou par

traitement (fonction dédier dans le langage programmé).

Une database d’un SGBD est une base de donnée (le SGBD peut regrouper plusieurs databases)

sécurité• L’administrateur SQL autorise les utilisateurs à faire des demandes

au système par la commande GRANT, exemples :

donner le droit de créer des tables à userX

GRANT CREATE TABLE TO userX;

donner le droit de supprimer des utilisateurs à userX

GRANT DROP USER TO userX;

donner le droit de sélection, insertion, modification sur la table table_name aux userX, userY et userZ

GRANT SELECT, INSERT, UPDATE ON TABLE table_name TO userX, userY, userZ;

donner tout les droits sur la propriété prop_name de la table table_name à userX

GRANT ALL PRIVILEGES (prop_name) ON TABLE table_name TO userX;

Syntaxe des tables : création / suppression

créationcreate table nomTable(nomChamp type, nomChamp type,…);

suppressiondrop table nomTable;

Exemple :

create table employe (numEmpl integer, nom varchar(25), prenom varchar(25));

drop table employe;

Syntaxe des tables : modification

alter table nomTable add newPropertyName type, pour ajouter une propriété

modify name type, pour modifier le type

change oldName newName type, pour modifier nom et type

drop propertyDropName; pour supprimer une propriété

Exemple :create table employe

(numEmpl integer, nom varchar(25), prenom varchar(25));

alter table employe add dateEmb date, change prenom surname varchar(15), change nom name varchar(25));

Type de donnée Syntaxe Description

Type alphanum CHAR(n) Chaîne de caractères de longueur fixe n (n<16383)

Type alphanum VARCHAR(n) Chaîne de caractères de n car max (n<16383)

Type numérique NUMBER(n,[d]) Nombre de n chiffres [option: d chiffres après la virgule]

Type numérique SMALLINT Entier signé de 16 bits (-32768 à 32757)

Type numérique INTEGER Entier signé de 32 bits (-2E31 à 2E31-1)

Type numérique FLOAT Nombre à virgule flottante

Type horaire DATE Date sous la forme 16/07/99

Type horaire TIME Heure sous la forme 12:54:24.85

Type horaire TIMESTAMP Date et Heure

Syntaxe des tables : Type SQL

Syntaxe des tablesles contraintes des propriétés

• DEFAULT valeur affecte une valeur par défaut à la propriété.

• NOT NULL la propriété doit être renseigné dès l’initialisation.

• UNIQUE la propriété n’admet pas 2 fois la même valeur.

• CHECK (condition) Les valeurs renseignées doivent respecter les conditions

• PRIMARY KEY(nom_propriété) La propriété est la clef de la table

• FOREIGN KEY(nom_prop1) REFERENCES table_nom(nom_prop2)

La prop1 est lié a la prop2 de la table table_nom (donc la valeur entrée dans prop1 doivent être présente dans prop2)

Syntaxe des tablesles contraintes des propriétés

Exemple :

create table employe

(numEmpl integer not null,

numSecuSoc varchar(15) unique,

nom varchar(25) default 'dupond',

prenom varchar(25) check(prenom <> 'régis'),

primary key(numEmpl));

create table EmpSSII

(numEmpSSII integer not null,

nomSsii varchar(30) not null,

foreign key(numEmpSSII) references employe(numEmpl));

Syntaxe des requêtes : sélectionsélection des propriétés SELECT list_nom_propriété

list_nom_propriété : * pour sélectionner toute les propriétés, sinon la liste (séparé de virgule) des propriétés à renvoyer

Provenant des tables

FROM list_nom_table list_nom_table : la liste (séparé de virgule) des tables à scanner

Répondant au conditions logiques suivante

WHERE conditionsconditions

- logique et numérique habituelle (AND, OR, NOT, =, <, >, <>,>=,<=, IS NULL, IS NOT NULL)- nom_propriété IN ('valeur1', 'valeur2', 'valeur3')- nom_propriété BETWEEN 'valeur1' AND 'valeur2‘- nom_propriété LIKE '%val%eur1%' (le % représente un nombre de caractères quelconque)

Syntaxe des requêtes : sélection

exemple

SELECT employe.numEmpl, numSecuSoc

FROM EmpSSII, employe

WHERE employe.numEmpl= numEmpSSII AND numSecuSoc LIKE '1%';

Renverra :

numEmpl numSecuSoc

------ ---------------

012578 178455689634789

089578 174789456344778

012585 187454423478945

……… ………

sélection : alias

alias on peut renommer une table ou une propriété

exemple SELECT b.numEmpl AS numéroEmployé, numSecuSoc AS numéroSecuritéSocial FROM EmpSSII AS a, employe AS bWHERE b.numEmpl=a.numEmpSSII AND numSecuSoc LIKE '1%';

Renverra :

numéroEmployé numéroSecuritéSocial ------ ---------------012578 178455689634789089578 174789456344778012585 187454423478945……… ………

sélection : jointure

SELECT * FROM table1 AS a JOIN table2 AS b ON a.prop1Join=b.prop1Join AND a.prop2Join=b.prop2Join …

JOIN table3 AS c ON … WHERE …;

Veux dire existe obligatoirement en table1 et en table2

exemple SELECT b.numEmpl AS numéroEmployé, numSecuSoc

AS numéroSecuritéSocial

FROM EmpSSII AS a JOIN employe AS b ON

b.numEmpl=a.numEmpSSII

WHERE numSecuSoc LIKE '1%';

NB : JOIN peut-être remplacé par INNER JOIN dans certains SGBD

sélection : jointure

Autres jointures existantes

SELECT * FROM table1 AS a LEFT JOIN table2 AS b ON …

Veux dire existe obligatoirement en table1

SELECT * FROM table1 AS a RIGHT JOIN table2 AS b ON …

Veux dire existe obligatoirement en table2

SELECT * FROM table1 AS a OUTER JOIN table2 AS b ON …

Veux dire existe obligatoirement en table2 ou en table1

sélection : fonction et regroupement

– Fonction de groupe– Fonction de ligne– Regroupement des propriétés– condition post regroupement– ordonnancement

SELECT COUNT(*) AS nombre, MAX(a.age) AS ageMax,

MIN(a.age) AS ageMin, AVG(a.age) AS ageMoyen,

SUM(a.age) AS ageSomme, SUBSTR(a.typeClient,1,1)

FROM personne AS a

WHERE a.sexe = ‘F‘ AND LENGTH(TRIM(nom))

GROUP BY a.typeClient

HAVING COUNT(*) > 5 AND COUNT(*) < 55

ORDER BY COUNT(*) DESC, a.typeClient;I:\Bts\

SQL_memo.htm

Sélection : les sous requêtes

Une sous requête est comme son nom l’indique une requête inclus dans une requête :

SELECT * FROM (SELECT * FROM table a WHERE…)

AS a WHERE NOT EXISTS (SELECT * FROM table1 b

WHERE a.id=b.id);

La sous-requête est intéressante pour factoriser l’information, mais la complexité peut augmenter si les sous-requêtes sont nombreuses ou complexe à résoudre.

Syntaxe des requêtes : insertion / suppression / modification

Insertion d’un enregistrement :

INSERT INTO nom_table (nom_prop1, …) VALUES (valeur1,…);

ou

INSERT INTO nom_table (nom_prop1, …) SELECT nom_propA,…;

Suppression d’un enregistrement :

DELETE FROM nom_table (nom_prop1, …)

WHERE …;

Modification d’un enregistrement :

UPDATE nom_table

SET nom_prop1 = 'nouvelle valeur', nom_prop2 = 15,…

WHERE …;