Cours de Bases de données








télécharger 391.04 Kb.
titreCours de Bases de données
page6/14
date de publication23.12.2016
taille391.04 Kb.
typeCours
ar.21-bal.com > droit > Cours
1   2   3   4   5   6   7   8   9   ...   14

VIII.LE LANGAGE SQL



C'est un langage fourni avec tout SGBD relationnel commercialisé. C'est un standard reconnu par l'IS0 depuis 87 (standard donc portabilité). On en est à la version 2 (SQL92) et la version 3 est annoncée pour bientôt. SQL est un LDD et un LMD. Il est aussi utilisé pour définir des vues, les droits d'accès, manipulation de schéma physique…

4.1) Structure de base



Une requête SQL typique est de la forme

SELECT A1,, ... , An

FROM r1,... ,rm

WHERE P
Les Ai sont des attributs, les rj sont des noms de relations et P est un prédicat.

Cette requête est équivalente à  A1 ,…, An ( P(r1 x … x rm))

La clause SELECT


La clause SELECT correspond à la projection de l'algèbre.
Les titres des films :

SELECT Titre

FROM film

L'utilisation de l'astérisque permet de sélectionner tous les attributs :

SELECT *

FROM film

SQL autorise par défaut les doublons. Pour le forcer à les éliminer, on utilise la clause DISTINCT : SELECT DISTINCT Titre

FROM film

SELECT peut contenir des expressions arithmétiques ainsi que le renommage d'attributs :

SELECT Prix_HT * 1.206 AS Prix TTC

FROM produit

La clause WHERE


Elle correspond au prédicat de sélection dans l'algèbre. La condition porte sur des attributs des relations qui apparaissent dans la clause FROM
SELECT DISTINCT Titre

FROM film

WHERE Réalisateur "Bergman" AND Acteur = "Stewart"
SQL utilise les connecteurs AND, OR et NOT. Pour simplifier la clause WHERE, on peut utiliser la clause BETWEEN.

SELECT Num

FROM compte

WHERE Solde BETWEEN 0 AND 10000

La clause FROM


Elle correspond au produit cartésien de l'algèbre.
Le titre et le réalisateur des films programmés à l'UGC de Bordeaux.

SELECT Titre, Réalisateur

FROM film, programme

WHERE film.titre = programme.titre AND programme.NomCiné = "UGC"

Les variables n-uplets



Elles sont définies dans la clause FROM

SELECT Titre, Réalisateur

FROM film AS f, programme AS p

WHERE f.titre = p.titre AND p.NomCiné = "UGC"
Soit Emp (Id, Nom, Id_chef)

SELECT el.Nom, e2.Nom AS Nom_Chef

FROM emp el, emp e2

WHERE el.Id_chef = e2.Id

La clause ORDER BY



SQL permet de trier les résultats de requête

SELECT *

FROM programme

WHERE NomCiné="UGC"

ORDER BY Horaire ASC, Titre DESC

4.2) Opérateurs ensemblistes



SELECT ...



UNION/ INTERSECT/ EXCEPT

SELECT ...
Attention : Ces opérations éliminent les doublons, pour pouvoir les garder, utiliser à la place INTERSECT ALL... Si t apparaît m fois dans r et n fois dans s alors il apparaît :

  • m + n fois dans r UNION ALL s

  • min(m, n) fois dans r INTERSECT ALL s

  • max(0, m - n) fois dans r EXCEPT ALL s


4.3) Les fonctions d’agrégats



Ce sont des fonctions qui agissent sur des ensembles (multi-ensembles) de valeurs :

AVG : la valeur moyenne de l'ensemble

MIN : la valeur minimale

MAX : la valeur maximale

SUM : le total des valeurs de l'ensemble

COUNT : le nombre de valeur dans l'ensemble
SELECT COUNT(Titre) FROM Programme

Cette requête retourne le nombre de films programmés à Bordeaux.

Attention : Un même titre peut être compté plusieurs fois s'il est programmé à des heures différentes et dans des salles différentes.
SELECT COUNT( DISTINCT Titre) FROM Programme

Agrégats et GROUP BY



Le nombre de films programmés dans chaque salle :
SELECT NomCiné, COUNT (DISTINCT Titre)

FROM Programme

GROUP BY NomCiné
Les attributs qui apparaissant dans la clause SELECT en dehors des agrégats doivent être associés à la clause GROUP BY

Agrégats et la clause HAVING



Les salles où sont programmés plus de 3 films :
SELECT NomCiné, COUNT(DISTINCT Titre)

FROM Programme

GROUP BY NomCiné

HAVING COUNT (DISTINCT Titre) > 3
Le prédicat associé à la clause HAVING est testé après la formation des groupes définis dans la clause GROUP BY.


4.4) Requêtes imbriquées


SQL fournit un mécanisme qui permet d'imbriquer les requêtes. Une sous requête est une requête SQL (SELECT-FROM-WHERE) qui est incluse dans une autre requête. Elle apparaît au niveau de la clause WHERE de la première requête.
Les films programmés à l'UGC non programmés au Trianon

SELECT Titre

FROM Programme

WHERE NomCiné="UGC" and Titre NOT IN (

SELECT Titre

FROM Programme

WHERE NomCiné ="Trianon" )
Trouver les comptes dont les soldes sont supérieurs aux soldes des comptes de Durand :

Compte (Num, Solde, NomTit)

SELECT *

FROM Compte

WHERE Solde > ALL (

SELECT Solde

FROM Compte

WHERE NomTit = "Durand" )
En remplaçant ALL par SOME, on obtient les comptes dont les soldes sont supérieur au solde d'au moins un compte de Durand.
Les cinémas qui passent tous les films programmés àl'UGC

SELECT NomCiné

FROM programme pi

WHERE NOT EXISTS (

(SELECT DISTINCT Titre

FROM programme

WHERE NomCiné-"UGC")

EXCEPT

(SELECT DISTINCT Titre

FROM programme p2

WHERE pl.NomCiné = p2.NomCiné))

Test d'absence de doublons


La clause UNIQUE permet de tester Si une sous requête contient des doublons.
Les titres de films programmés dans une seule salle et un seul horaire :

SELECT p.Titre

FROM programme p

WHERE UNIQUE (

SELECT pl.Titre

FROM programme p1

WHERE p.Titre = pl Titre)

Les relations dérivées



Titulaire (Nom, Adresse)

Compte (Num, Solde, NomTit)
Donner le solde moyen des comptes de chaque personne ayant un solde moyen > à 1000
SELECT NomTit, SoldeMoyen

FROM (

SELECT NomTit, AVG(Solde)

FROM Compte

GROUP BY NomTit )

AS Result (NomTit, SoldeMoyen)

WHERE SoldeMoyen > 1000
Noter qu'on aurait pu exprimer cette requête en utilisant la clause HAVING


4.5) Les vues



Equivalent à une requête Access. Une vue peut être considérée comme une relation quelconque lors de l’expression des requêtes. Une vue est une relation virtuelle dans le sens où elle ne contient pas effectivement des « tuples ». Elles permettent de définir des relations virtuelles dans le but de :

  • Cacher certaines informations à des utilisateurs,

  • Faciliter l'expression de certaines requêtes,

  • Améliorer la présentation de certaines données.


Une vue est définie par une expression de la forme :

CREATE VIEW V AS requête
Requête est une expression quelconque de requête et V est le nom de la vue.

Emp (NumE, Salaire, Dept, Adresse)

CREATE VIEW EmpGen AS (

SELECT NumE, Dept, Adresse

FROM Emp )

Toutes les informations concernant les employés du département 5 :

SELECT *

FROM EmpGen

WHERE Dept = 5

4.6) Modification des relations

Suppression :


Supprimer tous les employés du département 5 : DELETE FROM Emp

WHERE Dept = 5
Supprimer du programme tous les films programmés à l'UGC où un des acteurs est DiCaprio :

DELETE FROM programme

WHERE NomCiné = "UGC' AND EXISTS (

SELECT Titre

FROM film

WHERE programme.Tite = film.Titre AND film.Acteur = "DiCaprio" )
Supprimer les comptes dont le solde est < à la moyenne des soldes de tous les comptes :

DELETE FROM compte

WHERE Solde < (SELECT AVG (Solde) FROM compte)

Problème : Si les n-uplets sont supprimés un à un de la relation compte, alors à chaque suppression, on a une nouvelle valeur de AVG (Solde). La solution de SQL est de d'abord, calculer AVG(Solde) et ensuite de supprimer les tuples satisfaisant le test sans recalculer à chaque fois la nouvelle valeur de AVG (Solde).

Insertion


Insérer un n-uplet dans la relation "compte" :

INSERT INTO compte (Num, Solde, NomTit) VALUES (511,1000, "Dupont")

ou bien INSERT INTO compte VALUES (511,1000, «Dupont")
Insère un n-uplet avec un solde inconnu.

INSERT INTO compte VALUES (511, NULL, "Dupont")

ou bien INSERT INTO compte(Num, NomTit) VALUES (511, "Dupont")
Les 2 dernières MAJ sont équivalentes sauf si une valeur par défaut du Solde a été spécifiée lors de la définition de la table compte.
Supposons qu'on a crée une relation TitMoy (NomTit, Moyenne) qui doit contenir le nom des clients de la banque ainsi que la moyenne des soldes de leurs comptes.

INSERT INTO TitMoy (NomTit, Moyenne)

SELECT NomTit, AVG(Solde)

FROM compte

GROUP BY NomTit

Update


Rajouter 1% à tous les comptes dont le solde est inférieur à 500 :

UPDATE compte

SET Solde = Solde * 1.01

WHERE Solde  500

La condition qui suit la clause WHERE peut être une requête SQL.
SQL EN TANT QUE LDD


  • Le schéma des relations

  • Les domaines des attributs

  • Les contraintes d'intégrité

  • La gestion des autorisations

  • La gestion du stockage physique

  • Les index associés à chaque relation

5.1) Domaines





  • char(n) : chaîne de caractères de taille fixe n

  • varchar(n) : chaîne de caractères de taille variable mais inférieure à n

  • int : Entier (un sous ensemble fini des entiers, dépend de la machine)

  • smallint : Entier. Sous ensemble de int

  • numeric(p,d) : Réel codé sur p digits et max d digits pour partie à droite de la décimale.

  • real : Un réel flottant.

  • date : YYYY-MM-DD (année, mois, jours)

  • time : HH :MM :SS (heure, minute, seconde)


Les valeurs nulles (NULL) sont possibles dans tous les domaines. Pour déclarer qu'un attribut ne doit pas être nul, il faut rajouter NOT NULL


  • CREATE DOMAIN nom-client char(20)



5.2) Création des tables





  • On utilise la clause CREATE TABLE

CREATE TABLE compte (

Num int NOT NULL,

Solde int,

NomTit varchar( 20))


  • Rajout de contraintes

CREATE TABLE compte (

Num int NOT NULL,

Solde int DEFAULT 0,

NomTit varchar(20),

PRIMARY KEY (Num),

CHECK (Num > 1) )


  • En SQL92, si un attribut est clé alors il est différent de NULL.


Manipulation de schéma



La commande DROP TABLE permet de supprimer une table.

Ex: DROP TABLE compte.
Si une vue est définie sur la table compte alors il faut utiliser

DROP TABLE compte CASCADE
La commande ALTER TABLE permet de modifier le schéma d'une relation.

Exemple : ALTER TABLE compte ADD Date_ouverture

date

ALTER TABLE compte DROP Solde CASCADE

Clé étrangère



Soient Personne (NSS, Nom) et Voiture (Matricule, modèle, Proprio).

« Proprio » correspond au NSS du propriétaire. C'est une clé étrangère dans le schéma Voiture car c'est une clé dans un autre schéma.
CREATE TABLE Voiture (

Matricule CHAR(8),

Modele CHAR(10),

Proprio CHAR(3),

PRIMARY KEY(Matricule),

FOREIGN KEY(Proprio) REFERENCES Personne

ON [DELETE | UPDATE] CASCADE |

RESTRICT |

SET NULL

)
CASCADE: Si une personne est supprimée, alors les voitures qu'elle possède sont supprimées.

RESTRICT : Le système refuse la suppression d'une personne s'il y a des voitures qui lui sont rattachées. C'est l'option par défaut.

SET NULL: Si une personne est supprimée, alors l'attribut Proprio prend la valeur NULL.
L'insertion d'une voiture ne peut se faire que si le «proprio » existe dans Personne (ou bien valeur nulle).

Va1eurs nu11es





Employé

Nom

Salaire




Dupont

10 000




Martin

NULL



SELECT *

FROM Employé

WHERE Salaire > 12000 Ne retourne aucun tuple.

Pareil si la condition est WHERE Salaire < 8000
SELECT SUM (Salaire) FROM Employé; Retourne 10000
SELECT COUNT (Salaire) FROM Employé; Retourne 2
SELECT AVG (Salaire) FROM Employé; Retourne 10000

Très différent de SELECT SUM (Salaire) / COUNT (Salaire) FROM Employé car COUNT prend en compte la valeur NULL donc cela fera 10000/2=5000.

En fait c’est équivalent à :SELECT SUM (Salaire) / COUNT (Salaire) FROM Employé

WHERE Salaire IS NOT NULL
SELECT COUNT(*) FROM Employé

WHERE Salaire IS NOT NULL; Retourne 1

Mise à jour des vues



Personne (Nom, Salaire). Supposons que la table Personne est vide.

CREATE VIEW Gros_Salaire AS

SELECT *

FROM Personne

WHERE Salaire > 10000
INSERT INTO Gros_Salaire VALUES("Martin", 5000)

L'effet de cette commande est d'insérer dans la table Personne le tuple < "Martin", 5000>.

Noter que si l'on fait :

SELECT * FROM Gros Salaire; on n'obtient aucun tuple.
Si à la création de la vue on rajoute l’option :

WITH CHECK OPTION alors l'insertion est refusée.
Les mises à jours des vues sont traduites en des mises à jours des tables sous-jacente. La traduction n'est pas toujours unique. Quand nous avons plusieurs manières de traduire une mise à jour alors celle-ci est rejetée.  Certaines vues ne permettent pas des mises à jour. Il faut une relation biunivoque entre la mise à jour de la vue et la mise à jour de la table.

Jointure externe


Si on fait : Personne IXI Voiture, on n'aura que les personnes qui ont une(des) voiture(s) qui sont dans le résultat.

SELECT *

FROM Personne P Left Outer Join Voiture V

ON P.NSS = V.Proprio
Cette requête retourne aussi les personnes n'ayant pas de voiture. Ces tuples auront des valeurs nulles pour les champs provenant de Voiture. Si on met juste Outer Join alors on aura les personnes sans voitures et les voitures sans Propriétaire.
La jointure est exprimée par : T1 Inner Join T2 On Condition
Dans l'exemple, si l'on veut joindre Personne et Voiture alors

SELECT *

FROM Personne P Inner Join Voiture V

ON P.NSS = V.Proprio

Si l’on mets Right à la place de Left dans la jointure, alors on aura les voitures sans les personnes. Si on ne mets ni Left ni Right, on aura les voitures et les personnes qui ne sont pas dans la jointure.

5.3) Mécanisme des droits




Soit la table pers (Num, Nom, Adr, Num_Serv, Salaire)
1. Dupont ne peut pas accéder à Pers.

2. Dupont peut lire une partie de Pers mais ne peut rien modifier.

3. Dupont Peut lire un seul tuple (celui le concernant) sans pouvoir le modifier.

4. Dupont peut en plus modifier l'attribut Adr.

5. Dupont peut accéder à l'attribut salaire mais seulement entre 9h et 17h à partir du terminal 25.

6. Dupont peut modifier salaire si celui-ci est inférieur à 8000.

7. Dupont peut modifier la relation s’il est responsable du Num_Serv du tuple qu'il veut modifier.
Les droits dépendent du contenant, du contexte et/ou du contenu.

Les droits dans SQL


SELECT: privilège qu'il faut posséder pour lire une table

INSERT, DELETE, UPDATE: privilèges nécessaires pour mettre à jour une table.

INSERT(X), UPDATE(X): privilège nécessaire pour insérer, mettre à jour l'attribut X.

Octroi et retrait de privilèges



GRANT privilège ON objet TO utilisateur [WITH GRANT OPTION]
REVOKE [GRANT OPTION FOR] privilège ON objet FROM utilisateur

RESTRICT | CASCADE
Exemples :

GRANT ALL ON TABLE résultat TO directeur WITH GRANT OPTION;
GRANT INSERT ON TABLE résultat TO sec_1;
GRANT SELECT, UPDATE(points) ON TABLE resultat TO prof_1;
Remarque : Un utilisateur peut recevoir le même privilège de plusieurs sources. Cela est utile quand l'une d'elles veut le lui retirer.
Exemple : soit la séquence

A: GRANT SELECT ON TABLE T TOBWITH GRANT OPTION

B: GRANT SELECT ON TABLE T TOCWITH GRANT OPTION

C: GRANT SELECT ON TABLE T TODWITH GRANT OPTION

A: REVOKE SELECT ON TABLE T FROM B

Ni B ni C ne pourront lire T

Utilisation des vues



CREATE VIEW Informations_Perso

AS SELECT *

FROM Employé

WHERE nom = USER;
GRANT SELECT, UPDATE(adresse)

ON Informations_Perso

TO PUBLIC
1   2   3   4   5   6   7   8   9   ...   14

similaire:

Cours de Bases de données iconAdministrateur(trice) de bases de données / Intégrateur(trice) d’applications
«Accompagner les transitions agro-écologiques des systèmes et territoire d’élevage») et sur le pôle basmati («Bases de données, modèles...

Cours de Bases de données iconI ] Les différents modèles de bases de données

Cours de Bases de données iconUniversité Sidi Mohamed Ben Abdellah
«pose» sur le IaaS. IL permet d’externaliser l’infrastructure matérielle, mais également des applications middleware : bases de données,...

Cours de Bases de données iconC onsultant(e) système spécialiste O. S. et bases de données / Grenoble...

Cours de Bases de données iconProtection des logiciels et des bases de données

Cours de Bases de données iconM. Bilodeau est un professionnel dynamique, versé dans plusieurs...

Cours de Bases de données iconPour les projets de logiciels et/ou de bases de données, cette déclaration...

Cours de Bases de données iconCours : mat-4172-2 Collecte de données en contexte fondamental

Cours de Bases de données iconAccès sécurisés aux données partout dans le monde avec aprol
«parlantes» sont transférées à la base de données du cloud. Cette méthode basée sur les concepts du fog computing (informatique géodistribuée)...

Cours de Bases de données iconAu risque de refroidir les ardeurs de quelques dsi, le temps semble...
«humeur» des internautes. A nous les connexions vers les sites métiers pour remplir nos bases de données des informations professionnels...








Tous droits réservés. Copyright © 2016
contacts
ar.21-bal.com