Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL...
-
Upload
corine-puech -
Category
Documents
-
view
105 -
download
1
Transcript of Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL...
![Page 1: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/1.jpg)
Licence de Sciences et Techniques
Unité d’enseignement BASES DE DONNEES
Langage de manipulation SQL
François JacquenetProfesseur d'InformatiqueFaculté des SciencesLaboratoire Hubert Curien – UMR CNRS 551618 rue Benoit Lauras42023 Saint-Etienne cedex 2Tél : 04 77 91 58 07e-mail : [email protected] : http://eurise.univ-st-etienne.fr/~fj
![Page 2: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/2.jpg)
Information pourla semaine du 25 au 29
septembre
Cours, TD et TPaux horaires prévus
sur l’emploi du temps
![Page 3: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/3.jpg)
3
Aujourd’hui nous verrons
SQL = Langage de définition de données CREATE TABLE ALTER TABLE DROP TABLE
SQL = Langage de manipulation de données INSERT INTO UPDATE DELETE FROM
SQL = Langage de requêtes SELECT … FROM … WHERE ...
Sélection Projection Jointure Produit cartésien Union Intersection Différence
Les agrégats
![Page 4: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/4.jpg)
4
Introduction
SQL : Structured Query Language Inventé chez IBM (centre de recherche d'Almaden
en Californie), en 1974 par Astrahan & Chamberlin dans le cadre de System R
Le langage SQL est normalisé SQL2: adopté (SQL 92) SQL3: adopté (SQL 99)
Standard d'accès aux bases de données relationnelles
![Page 5: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/5.jpg)
5
SQL : Trois langages en un
Langage de définition de données (LDD/DDL) création de relations : CREATE TABLE modification de relations: ALTER TABLE suppression de relations: DROP TABLE vues, index …. : CREATE VIEW ...
Langage de manipulation de données (LMD /DML) insertion de tuples: INSERT mise à jour des tuples: UPDATE suppression de tuples: DELETE
Langage de requêtes (LMD/DML) SELECT ….... FROM ....... WHERE ..........
![Page 6: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/6.jpg)
6
Terminologie
Relation Table
Tuple Ligne
Attribut Colonne
![Page 7: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/7.jpg)
SQL
Un langage de
définition de données
![Page 8: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/8.jpg)
8
Un langage de définition de données
Commandes pour créer, modifier et supprimer les éléments du schéma
CREATE TABLE : créer une table (une relation), CREATE VIEW : créer une vue particulière sur les
données à partir d’un SELECT, DROP {TABLE ¦ VIEW } : supprimer une table ou
une vue, ALTER {TABLE ¦ VIEW } : modifier une table ou
une vue.
![Page 9: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/9.jpg)
9
CREATE TABLE
Commande créant une table en donnant son nom, ses
attributs et ses contraintes
CREATE TABLE nom_table
{ ( nom-col type-col
[DEFAULT val]
[ [CONSTRAINT] contrainte-col] )*
[ [CONSTRAINT] contrainte-table]
| AS requête-SQL };
Légende : {a | b} : a ou b, [option]; * : applicable autant de fois que souhaité; mots en capitale : mots-clé.
![Page 10: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/10.jpg)
10
CREATE TABLE
CREATE TABLE nom_table
{ ( nom-col type-col [DEFAULT val] [ [CONSTRAINT] contrainte-col] )*
[ [CONSTRAINT] contrainte-table]
| AS requête-SQL };
Exemples:CREATE TABLE Doctorant
(nom VARCHAR(20),
prenom VARCHAR(15),
annee_insc YEAR DEFAULT 2002) ;
CREATE TABLE Doctorant
AS SELECT nom, prenom, annee_inscr
FROM Etudiant WHERE statut='Doctorant';
![Page 11: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/11.jpg)
11
Type des colonnes (en MySQL)
Numériques NUMERIC : idem DECIMAL DECIMAL. Possibilité DECIMAL(M,D) M chiffre au total INTEGER
TINYINT 1 octet (de -128 à 127) SMALLINT 2 octets (de -32768 à 32767 MEDIUMINT 3 octets (de -8388608 à 8388607) INT 4 octets (de -2147483648 à 2147483647) BIGINT 8 octets (de -9223372036854775808 à
9223372036854775807) Possibilité de donner la taille de l’affichage : INT(6) => 674 s’affiche 000674 Possibilité de spécifier UNSIGNED
INT UNSIGNED => de 0 à 4294967296
FLOAT : 4 octets par défaut. Possibilité d’écrire FLOAT(P) REAL : 8 octets DOUBLE PRECISION : 8 octets
![Page 12: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/12.jpg)
12
Type des colonnes (en MySQL)
Date et Heure DATETIME
AAAA-MM-JJ HH:MM:SS de 1000-01-01 00:00:00 à '9999-12-31 23:59:59
DATE AAAA-MM-JJ de 1000-01-01 à 9999-12-31
TIMESTAMP Date sans séparateur AAAAMMJJHHMMSS
TIME HH:MM:SS (ou HHH:MM:SS) de -838:59:59 à 838:59:59
YEAR YYYY de 1901 à 2155
![Page 13: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/13.jpg)
13
Type des colonnes (en MySQL)
Chaînes CHAR(n) 1 ≤ n ≤ 255 VARCHAR(n) 1 ≤ n ≤ 255
Exemple :
CHAR(4) VARCHAR(4)
Valeur Stockée Taille Stockée Taille
'' ' ' 4 octets '' 1 octets
'ab' 'ab ' 4 octets 'ab' 3 octets
'abcd' 'abcd' 4 octets 'abcd' 5 octets
'abcdef' 'abcd' 4 octets 'abcd' 5 octets
![Page 14: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/14.jpg)
14
Type des colonnes (en MySQL)
Chaînes TINYBLOB Taille < 2^8 caractères BLOB Taille < 2^8 caractères MEDIUMBLOB Taille < 2^24 caractères LONGBLOB Taille < 2^32 caractères
TINYTEXT Taille < 2^8 caractères TEXT Taille < 2^8 caractères MEDIUMTEXT Taille < 2^24 caractères LONGTEXT Taille < 2^32 caractères
Les tris faits sur les BLOB tiennent compte de la casse,contrairement aux tris faits sur les TEXT.
![Page 15: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/15.jpg)
15
Type des colonnes (en MySQL)
ENUM Enumération ENUM("un", "deux", "trois") Valeurs possibles : "" , "un", "deux", "trois" Au plus 65535 éléments
SET Ensemble SET("un", "deux") Valeurs possibles : "" , "un", "deux", "un,deux" Au plus 64 éléments
![Page 16: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/16.jpg)
16
Contraintes
Contrainte-col : contrainte sur une colonne NOT NULL UNIQUE PRIMARY KEY REFERENCES nom_table [(nom-col)] [action] CHECK ( condition)
Contrainte-table : contraintes sur une table UNIQUE (nom-col)* PRIMARY KEY (nom-col)* FOREIGN KEY (nom-col)* REFERENCES nom_table [(nom-col)*]
[action] CHECK ( condition)
![Page 17: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/17.jpg)
17
Contraintes NOT NULL / UNIQUE
NOT NULL Après une colonne CREATE TABLE Pays (nom VARCHAR(20) NOT NULL, …
UNIQUE Clé secondaire, pas deux lignes avec la même valeur, éventuellement
null
Exemples :CREATE TABLE Etudiant (
no_etudiant INT UNIQUE,
…
CREATE TABLE Etudiant (
no_etudiant INT CONSTRAINT no_etu_u UNIQUE,
…
![Page 18: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/18.jpg)
18
Contraintes PRIMARY KEY
PRIMARY KEY : IDENTIFIANT de la relation UNIQUE + NOT NULL Exemple:
CREATE TABLE Dept
(dpt_id INT(4) PRIMARY KEY, …
CREATE TABLE Employé
( nom VARCHAR(30),
prénom VARCHAR(30),
adresse VARCHAR(60), …,
CONSTRAINT PK_emp PRIMARY KEY (nom, prénom)) ;
AUTO_INCREMENT
UNIQUE et PRIMARY KEY sont incompatibles
![Page 19: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/19.jpg)
19
Contraintes d'intégrité référentielle
Foreign key : att1 REFERENCES att2 référence soit une primary key soit une unique key. Exemple:
CREATE TABLE Dept
(dpt_id INT(4) PRIMARY KEY,
manager_id INT(4) CONSTRAINT fk_mgr
REFERENCES EMPLOYEE(emp_id)
, … )
CREATE TABLE Dept
(dpt_id INT(4) PRIMARY KEY,
manager_id INT(4),
…,
CONSTRAINT fk_mgr FOREIGN KEY (manager_id)
REFERENCES EMPLOYEE(emp_id), …
![Page 20: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/20.jpg)
20
Actions déclenchées
REFERENCES nom_table [(nom-col)] [action]
Qu'est ce qui se passe quand on détruit/MAJ une clé primaire ou unique qui est référencée par un tuple (foreign key) d'une autre table?
CREATE TABLE Dept
(dpt_id INT(4) PRIMARY KEY,
manager_id INT(4)
CONSTRAINT fk_mgr REFERENCES EMPLOYEE(emp_id),
…
Si on a le tuple (dpt_id=1, manager_id=21), que se passe-t-il si on détruit ou met à jour l'employé d'identifiant 21 dans la table Employé?
![Page 21: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/21.jpg)
21
Actions déclenchées
Deux circonstances ON DELETE ON UPDATE
Trois options SET NULL SET DEFAULT: valeur par défaut si elle existe, sinon NULL CASCADE : on répercute la mise à jour
Exemple : CREATE TABLE Dept
(dpt_id INT(4) PRIMARY KEY,
manager_id INT(4) CONSTRAINT fk_mgr
REFERENCES EMPLOYEE(emp_id)
ON DELETE SET NULL
ON UPDATE CASCADE) ;
![Page 22: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/22.jpg)
22
Contrainte CHECK
Condition que chaque ligne de la table doit vérifier Si contrainte de colonne, porte uniquement sur cette
colonne sinon porte sur plusieurs colonnes. Exemple:
CREATE TABLE divisions (
div_no INT CONSTRAINT check_divno
CHECK (div_no BETWEEN 10 AND 99),
div_name VARCHAR(9) CONSTRAINT check_divname
CHECK (div_name = UPPER(div_name)),
office VARCHAR(10) CONSTRAINT check_office
CHECK (office IN ('BOSTON', 'PARIS','TOKYO')) );
![Page 23: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/23.jpg)
23
Contrainte CHECK
CREATE TABLE Employee (
employe_id INT(4) PRIMARY KEY,
nom VARCHAR(10),
job_id VARCHAR(9),
manager_id INT(4),
salaire DECIMAL(7,2),
Commission DECIMAL(7,2),
departement_id SMALLINT(2),
CONSTRAINT check_sal
CHECK (salaire * commission <= 5000)); Si on cherche à entrer une valeur ne vérifiant pas la
contrainte, elle est rejetée.
![Page 24: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/24.jpg)
24
Statut des contraintes
Comment et quand vérifier une contrainte? DEFFERABLE / NOT DEFFERABLE ENABLE / DISABLE VALIDATE / NO VALIDATE
Peut être spécifié après chaque contrainte (de colonne ou de la table)
ENABLE / DISABLE Activation/désactivation d'une contrainte Si contrainte non vérifiée, on ne peut pas l'activer On ne peut pas activer une contrainte FK qui référence une
contrainte non active
![Page 25: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/25.jpg)
25
DROP TABLE
DROP TABLE : Supprimer une table supprime la table et tout son contenu
DROP TABLE nom_table [CASCADE CONSTRAINTS];
CASCADE CONSTRAINTS Supprime toutes les contraintes référençant une clé primaire (primary
key) ou une clé unique (UNIQUE) de cette table Si on cherche à détruire une table dont certains attributs sont référencés
sans spécifier CASCADE CONSTRAINT, on a un message d'erreur.
![Page 26: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/26.jpg)
26
ALTER TABLE
Modifier la définition d’une table: Changer le nom de la table
mot clé : RENAME Ajouter une colonne ou une contrainte
mot clé : ADD Modifier une colonne ou une contrainte
mot clé : MODIFY Supprimer une colonne ou une contrainte
mot clé : DROP renommer une colonne ou une contrainte
mot clé : RENAME
![Page 27: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/27.jpg)
27
ALTER TABLE
Syntaxe :
ALTER TABLE nom-table
{ RENAME TO nouveau-nom-table
| ADD (( nom-col type-col [DEFAULT valeur] [contrainte-col])*)
| MODIFY (nom-col [type-col] [DEFAULT valeur] [contrainte-col])*
| DROP COLUMN nom-col [CASCADE CONSTRAINTS]
| RENAME COLUMN old-name TO new-name
};
![Page 28: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/28.jpg)
28
Renommer une table
... RENAME TO nouveau-nom-table
Exemple :
ALTER TABLE country RENAME TO pays
![Page 29: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/29.jpg)
29
Ajouter une colonne
… ADD ( (nom-col type-col
[DEFAULT valeur]
[contrainte-col])* ) Exemple :
ALTER TABLE country
ADD (
duty DECIMAL(2,2)
DEFAULT 1
CHECK (duty < 10),
visa_needed VARCHAR(3)
);
![Page 30: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/30.jpg)
30
Modifier une colonne
…MODIFY ( (nom-col [type-col]
[DEFAULT valeur]
[contrainte-col])* )
Exemple :
ALTER TABLE pays
MODIFY (duty DECIMAL(3,2));
Tout ce qui n'est pas spécifié dans le MODIFY n'est pas modifié, par exemple ici default et check
![Page 31: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/31.jpg)
31
Supprimer une colonne
…DROP COLUMN nom-col
[CASCADE CONSTRAINTS]
Exemple :
ALTER TABLE pays
DROP COLUMN visa_needed
CASCADE CONSTRAINTS;
![Page 32: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/32.jpg)
32
Renommer une colonne
… RENAME COLUMN ancien_nom
TO nouveau_nom
Exemple :
ALTER TABLE pays
RENAME COLUMN duty TO taxe;
![Page 33: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/33.jpg)
33
ALTER TABLE sur les contraintes
ALTER TABLE nom_table
{ ADD contrainte_table
| DROP { PRIMARY KEY
| UNIQUE ( nom_colonne)*
| CONSTRAINT nom_contrainte }
[CASCADE CONSTRAINTS]
| RENAME CONSTRAINT ancien TO nouveau
| MODIFY [CONSTRAINT] nom_contrainte
statut_contrainte
};
![Page 34: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/34.jpg)
34
Ajouter une contrainte
… ADD contrainte-table
Exemple :
ALTER TABLE employes
ADD CONSTRAINT check_comp
CHECK (salaire +(commission*salaire) <= 5000);
ALTER TABLE pays
ADD CONSTRAINT pk_pays PRIMARY KEY (nom);
![Page 35: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/35.jpg)
35
Supprimer une contrainte
DROP {
PRIMARY KEY
| UNIQUE ( nom-colonne)*
| CONSTRAINT nom-contrainte }
[CASCADE CONSTRAINTS]
Exemple :ALTER TABLE departements
DROP PRIMARY KEY CASCADE CONSTRAINTS;
![Page 36: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/36.jpg)
36
Statut des contraintes
ENABLE VALIDATE : la contrainte est activée et contrôle que les données
de la table vérifient la contrainte NOVALIDATE : la contrainte est activée, les nouvelles données
entrées dans la BD devront vérifier la contrainte.
DISABLE VALIDATE : on cherche à désactiver la contrainte, si les données
ne sont pas valides, erreur.
Après DISABLE, on ne peut plus entrer, modifier ou supprimer des données de la table.
NOVALIDATE : on peut faire n'importe quelle opération y compris entrer des données non conformes à la contrainte
![Page 37: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/37.jpg)
37
Exemples
… MODIFY [CONSTRAINT] nom-contrainte
statut_contrainte Exemple :
ALTER TABLE pays
MODIFY CONSTRAINT pkc DISABLE
Équivalent à:ALTER TABLE pays DISABLE PRIMARY KEY Exemple :ALTER TABLE employes
ENABLE VALIDATE PRIMARY KEY
ENABLE NOVALIDATE CONSTRAINT nom_emp;
![Page 38: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/38.jpg)
38
SQL
Un langage de
manipulation de données
![Page 39: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/39.jpg)
39
Manipulation des données
INSERT INTO : ajouter un tuple dans une table ou une vue
UPDATE : changer les tuples d’une table ou d'une vue
DELETE FROM : éliminer les tuples d’une table ou d'une vue
![Page 40: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/40.jpg)
40
INSERT INTO
Année Lieu Pays
1896 Athènes Grèce
1900 Paris France
1904 St Louis USA
1908 Londres UK
JO
INSERT INTO JOVALUES (1996, ‘Atlanta’, ‘U.S.A’)
INSERT INTO JO(Année, Lieu)VALUES (1996, ‘Atlanta’)
Année Lieu Pays
1896 Athènes Grèce
1900 Paris France
1904 St Louis USA
1908 Londres UK
1996 Atlanta USA
JO
Année Lieu Pays
1896 Athènes Grèce
1900 Paris France
1904 St Louis USA
1908 Londres UK
1996 Atlanta NULL
JO
![Page 41: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/41.jpg)
41
INSERT INTO
Syntaxe :
INSERT INTO{nom_table ¦ nom_vue}[ (nom_col (, nom_col)*) ]{ VALUES (valeur (, valeur)*) ¦ sous-requête };
![Page 42: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/42.jpg)
42
UPDATE
Exemples : UPDATE Pays
SET capitale = ‘Londres’WHERE nom = ‘Ireland’
UPDATE InfosSET esp_vie = esp_vie+2 , poids = poids * 2WHERE continent = ‘Amérique' AND esp_vie < 80
Syntaxe : UPDATE {nom_table ¦ nom_vue}
SET { (nom_col)* = (sous-requête) ¦ nom_col = { valeur ¦ (sous-requête)} }*
WHERE condition;
![Page 43: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/43.jpg)
43
DELETE FROM
Exemple : DELETE FROM Pays
WHERE population > 300
Syntaxe : DELETE FROM {nom_table ¦ nom_vue}
WHERE condition;
Nom Capitale Population Surface Continent
Irlande Dublin 5 70 Europe
Autriche Vienne 10 83 Europe
UK Londres 50 244 Europe
Suisse Berne 7 41 Europe
USA Washington 350 441 Amérique
pays
![Page 44: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/44.jpg)
SQL
Un langage de
requêtes
![Page 45: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/45.jpg)
45
Structure générale d'une requête
Structure d'une requête formée de trois clauses:
SELECT <liste_attributs>
FROM <liste_tables>
WHERE <condition> SELECT définit le format du résultat cherché FROM définit à partir de quelles tables le résultat
est calculé WHERE définit les prédicats de sélection du
résultat
![Page 46: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/46.jpg)
46
Exemple de requête
SELECT * FROM pays
Afficher tous les attributs de tous les tuples dans la
table “pays”
Nom Capitale Population Surface
Irlande Dublin 5 70
Autriche Vienne 10 83
Royaume-Uni Londres 50 244
Suisse Berne 7 41
USA Washington 350 441
pays
![Page 47: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/47.jpg)
SQL
et
Algèbre relationnelle
![Page 48: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/48.jpg)
48
SQL / Algèbre : Identité
En algèbre : Id(R)
En SQL :
SELECT * FROM R
![Page 49: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/49.jpg)
49
SQL / Algèbre : Sélection
En algèbre : [condition] R
En SQL :
SELECT * FROM R WHERE condition
![Page 50: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/50.jpg)
50
SQL / Algèbre : Sélection
SELECT * FROM pays WHERE population < 20
Nom Capitale Population Surface
Irlande Dublin 5 70
Autriche Vienne 10 83
Royaume-Uni Londres 50 244
Suisse Berne 7 41
USA Washington 350 441
pays
![Page 51: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/51.jpg)
51
SQL / Algèbre : Projection
En algèbre : [A1, A2, …, An] R
En SQL :
SELECT A1, A2, …, An FROM R
![Page 52: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/52.jpg)
52
SQL / Algèbre : Projection
SELECT nom, capitale FROM pays ;
Nom Capitale Population Surface
Irlande Dublin 5 70
Autriche Vienne 10 83
Royaume-Uni Londres 50 244
Suisse Berne 7 41
USA Washington 350 441
pays
![Page 53: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/53.jpg)
53
SQL / Algèbre : Sélection + Projection
[capitale,capitale,population]([population<20]pays)
SELECT nom, capitale,populationFROM pays WHERE population < 20
Nom Capitale Population Surface
Irlande Dublin 5 70
Autriche Vienne 10 83
Royaume-Uni Londres 50 244
Suisse Berne 7 41
USA Washington 350 441
pays
![Page 54: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/54.jpg)
54
SQL / Algèbre : Renommage
En algèbre : α (A1:B1, ..., An:Bn) R
En SQL : Impossible de renommer des attributs. Il faut faire des “copies logiques” des relations.
SELECT *
FROM R, R R2
WHERE R.A = R2.A ….
R2 peut être vue comme une copie logique de R
![Page 55: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/55.jpg)
55
SQL / Algèbre : Produit cartésien
En algèbre : R × S
En SQL :
SELECT * FROM R , S
![Page 56: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/56.jpg)
56
SQL / Algèbre : Jointure
En algèbre : R S En SQL :
SELECT *FROM R, SWHERE R.A1 = S.A1
AND R.A2 = S.A2
… AND R.An = S.An
Avec A1, …, An tous les attributs communs à R et S
![Page 57: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/57.jpg)
57
SQL / Algèbre : Jointure SELECT année, lieu, pays,
capitale FROM JO, Pays WHERE JO.pays =Pays.nom;
Nom Capitale Population Surface
Irlande Dublin 5 70
Autriche Vienne 10 83
UK Londres 50 244
Suisse Berne 7 41
USA Washington 350 441
PaysAnnée Lieu Pays
1896 Athènes Grèce
1900 Paris France
1904 St Louis USA
1908 Londres UK
JO
Année Lieu Pays Capitale
1908 Londres UK Londres
1904 St Louis USA Washington
résultat
![Page 58: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/58.jpg)
58
Jointure sur la même table
Comment comparer les populations des pays? Exemple :
SELECT P1.nom, P1.population, P2.nom,P2.populationFROM Pays AS P1, Pays AS P2WHERE P1.population > P2.population ;
=> Toutes les paires de pays telles que le premier pays a une population plus grande que le deuxième pays
NB: La table Pays est renommée en P1 et P2 (alias)
![Page 59: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/59.jpg)
59
Jointure sur la même table
Nom Capitale Population Surface
Irlande Dublin 5 70
Autriche Vienne 10 83
UK Londres 50 244
Suisse Berne 7 41
USA Washington 350 441
pays
Nom Capitale Population Surface
Irlande Dublin 5 70
Autriche Vienne 10 83
UK Londres 50 244
Suisse Berne 7 41
USA Washington 350 441
pays
![Page 60: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/60.jpg)
60
Jointure sur la même table
Nom Capitale Population Surface
Irlande Dublin 5 70
Autriche Vienne 10 83
UK Londres 50 244
Suisse Berne 7 41
USA Washington 350 441
Pays-P1
Nom Capitale Population Surface
Irlande Dublin 5 70
Autriche Vienne 10 83
UK Londres 50 244
Suisse Berne 7 41
USA Washington 350 441
Pays-P2
SELECT P1.nom, P1.population, P2.nom, P2.populationFROM Pays AS P1, Pays AS P2WHERE P1.population > P2.population ;
![Page 61: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/61.jpg)
61
Jointure sur la même table
Résultat
P1.Nom P1.Population P2.Nom P2.Population
Autriche 10 Irlande 5
Autriche 10 Suisse 7
UK 50 Irlande 5
UK 50 Autriche 10
UK 50 Suisse 7
…. …. …. ….
![Page 62: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/62.jpg)
62
SQL / Algèbre : Union
En algèbre : R S
En SQL : BlocR UNION BlocS
Exemple :SELECT * FROM RUNIONSELECT * FROM S
Les tuples en double sont éliminés du résultat
![Page 63: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/63.jpg)
63
SQL / Algèbre : Intersection
En algèbre : R S
En SQL : BlocR INTERSECT BlocS
Exemple :SELECT * FROM RINTERSECTSELECT * FROM S
Les tuples en double sont éliminés du résultat
![Page 64: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/64.jpg)
64
SQL / Algèbre : Différence
En algèbre : R S
En SQL : BlocR MINUS BlocS
Exemple :SELECT * FROM RMINUSSELECT * FROM S
Les tuples en double sont éliminés du résultat
![Page 65: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/65.jpg)
65
Remarques
Le résultat d'une requête peut contenir plusieurs occurrences d’un tuple,
pour avoir une seule occurrence de chaque n-uplet dans une relation : DISTINCT
Exemple : select DISTINCT nom FROM Personne
Le résultat d'une requête peut être trié,
Il existe une valeur spéciale dite indéfinie (NULL) utilisée pour remplir un champ dont on ne connait pas la valeur.
![Page 66: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/66.jpg)
66
Remarques
En SQL, le produit cartésien est possible sans renommer les attributs communs.
Exemple : schéma(RxS) = A (de R), B (de R), B (de S), C (de S).
En SQL, si plusieurs attributs ont le même nom, pour résoudre l’ambiguité, on spécifie la relation auquel l’attribut appartient.
Exemple : SELECT A, R.B, C FROM R, S
![Page 67: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/67.jpg)
67
Opérateurs de comparaison
= égal WHERE surface = 200
<> différent WHERE capitale <> ‘Paris’
> plus grand que WHERE population > 8
>= plus grand ou égal WHERE population >= 8
< plus petit que WHERE surface < 83
<= plus petit ou égal WHERE surface <= 83
![Page 68: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/68.jpg)
68
Opérateurs logiques
AND WHERE population<10 AND surface<500
OR WHERE population<10 OR surface<500
Négation de la condition : NOT SELECT P1.nom, P2.nom, P1.capitale
FROM Pays P1, Pays P2WHERE P1.capitale = P2.capitaleAND NOT P1.nom = P2.nom ;
![Page 69: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/69.jpg)
69
Expressions logiques
Combinaisons:
WHERE( ensoleillement > 80 AND pluviosité < 200 )OR température > 30
WHEREensoleillement > 80AND ( pluviosité < 200 OR température >
30 )
![Page 70: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/70.jpg)
70
Appartenance à un ensemble : IN
WHERE monnaie = ‘Pound’OR monnaie = ‘Schilling’OR monnaie = ‘Euro’
Équivalent à:
WHERE monnaie IN (‘Pound’, ‘Schilling’, ‘Euro')
NOT IN: non appartenance à un ensemble
![Page 71: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/71.jpg)
71
Comparaison à un ensemble : ALL
SELECT * FROM EmployeWHERE salaire >= 1400AND salaire >= 3000 ;
Équivalent à:
SELECT * FROM EmployeWHERE salaire >= ALL ( 1400, 3000);
![Page 72: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/72.jpg)
72
Valeur dans un intervalle : BETWEEN
WHERE population >= 50 AND population <= 60
Équivalent à:
WHERE population BETWEEN 50 AND 60
NOT BETWEEN
![Page 73: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/73.jpg)
73
Conditions partielles (joker)
% : un ou plusieurs caractères WHERE pays LIKE ‘%lande’ -> Irlande, Islande, Finlande, Hollande
WHERE pays LIKE ‘%ran%’ -> Iran, France
_ : exactement un caractère WHERE pays LIKE ‘I_lande’ -> Irlande, Islande
NOT LIKE
![Page 74: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/74.jpg)
74
Valeurs calculées
SELECT nom, population, surface, natalitéFROM PaysWHERE (population * 1000 / surface) < 50 AND (population * natalité / surface) > 0
SELECT nom, (population * 1000 / surface )FROM Pays
![Page 75: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/75.jpg)
75
Attention : NULL 0
NULL = valeur inconnue ou non définie:
SELECT nom FROM Pays WHERE montagne IS NULL -> Pays Bas
SELECT nom FROM Pays WHERE montagne IS NOT NULL
-> Autriche, Suisse
NULL = Pas de valeur
![Page 76: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/76.jpg)
76
Opérations sur NULL
NULL dans les conditions: ( population > 0 ) ?
si population est NULL, le résultat est "unknown" donc "false" ( population = NULL ) ?
le test retourne toujours "false": La syntaxe correcte est: ( population IS NULL )
NULL dans une expression arithmétique: ( population + NULL ) retourne NULL
![Page 77: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/77.jpg)
77
Opérations sur NULL
NULL dans une fonction SQL Concat (nom, '-', prenom) retourne NULL si prénom est
NULL Concat ( nom, '-', NVL(prenom,' ')) retourne 'dupont-'
NVL(attribut,valeur_de_remplacement): retourne la valeur de remplacement lorsque que la valeur de l’attribut est NULL.
Fonctions d'agrégation ignorent les NULL: Moyenne(1000, NULL, NULL, NULL, 2000) =
(1000+2000)/2
![Page 78: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/78.jpg)
Requêtes
avec blocs emboîtés
![Page 79: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/79.jpg)
79
BD exemple
Produit(np,nomp,couleur,poids,prix) les produits
Usine(nu,nomu,ville,pays) les usines
Fournisseur(nf,nomf,type,ville,pays) les fournisseurs
Livraison(np,nu,nf,quantité) les livraisons np référence Produit.np nu référence Usine.nu nf référence Fournisseur.nf
![Page 80: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/80.jpg)
80
Nom et couleur des produits livrés par le fournisseur 1
SELECT nomp, couleur FROM ProduitWHERE np IN
Jointure par blocs emboîtés
Nom et couleur des produits livrés par le fournisseur 1
Solution 1 : la jointure déclarativeSELECT nomp, couleur FROM
Produit,LivraisonWHERE (Livraison.np = Produit.np) AND
nf = 1 ; Solution 2 : la jointure procédurale
(emboîtement)
Numéros de produits livrés par le fournisseur 1
(SELECT np FROM Livraison WHERE nf = 1) ;
![Page 81: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/81.jpg)
81
Jointure par blocs emboîtés
SELECT nomp, couleur FROM ProduitWHERE np IN
( SELECT np FROM Livraison WHERE nf = 1) ;
IN compare chaque valeur de np avec l'ensemble (ou multi-ensemble) de valeurs retournés par la sous-requête
IN peut aussi comparer un tuple de valeurs:SELECT nu FROM UsineWHERE (ville, pays)
IN (SELECT ville, pays FROM Fournisseur)
![Page 82: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/82.jpg)
82
Composition de conditions
Nom des fournisseurs qui approvisionnent une usine de
Londres ou de Paris en un produit rougeSELECT nomfFROM Livraison, Produit, Fournisseur, UsineWHERE couleur = 'rouge' AND Livraison.np = Produit.np AND Livraison.nf = Fournisseur.nf AND Livraison.nu = Usine.nu AND (Usine.ville = 'Londres'
OR Usine.ville = 'Paris');
![Page 83: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/83.jpg)
83
SELECT nomf FROM FournisseurWHERE nf IN
(SELECT nf FROM Livraison WHERE np IN AND nu IN
Composition de conditions
Nom des fournisseurs qui approvisionnent une usine de
Londres ou de Paris en un produit rouge
(SELECT np FROM Produit WHERE couleur = 'rouge')
(SELECT nu FROM Usine WHERE ville = 'Londres' OR ville = 'Paris') ) ;
![Page 84: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/84.jpg)
84
Quantificateur ALL
Numéros des fournisseurs qui ne fournissent que des produits rouges
SELECT nf FROM FournisseurWHERE 'rouge' = ALL
(SELECT couleur FROM Produit WHERE np IN
(SELECT np FROM Livraison WHERE Livraison.nf =
Fournisseur.nf ) ) ;
La requête imbriquée est ré-évaluée pour chaque tuple de la requête (ici pour chaque nf)
ALL: tous les éléments de l'ensemble doivent vérifier la condition
![Page 85: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/85.jpg)
85
Condition sur des ensemble : EXISTS
Test si l’ensemble n’est pas vide (E ) Exemple : Noms des fournisseurs qui
fournissent au moins un produit rougeSELECT nomfFROM FournisseurWHERE EXISTS
( SELECT * FROM Livraison, Produit WHERE Livraison.nf = Fournisseur.nf
AND Livraison.np = Produit.npAND Produit.couleur = 'rouge' );
Le produit fourniest rouge
ce fournisseur
![Page 86: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/86.jpg)
86
Blocs emboîtés - récapitulatif
SELECT …FROM …WHERE …
attr IN requêteattr NOT IN requête
attr opérateur ALL reqûete
EXISTS requêteNOT EXISTS requête
![Page 87: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/87.jpg)
Traitement
des résultats
![Page 88: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/88.jpg)
88
Fonctions sur les colonnes
Attributs calculés Exemple : SELECT nom, population*1000/surface FROM
Pays
Opérateurs sur attributs numériques SUM: somme des valeurs des tuples sélectionnés AVG: moyenne
Opérateurs sur tous types d’attributs MIN: minimum MAX: maximum COUNT: nombre de tuples sélectionnés
Opérateursd'agrégation
![Page 89: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/89.jpg)
89
Opérateurs d'agrégation
SELECT MIN(population), MAX(population), AVG(population),SUM(surface), COUNT(*)
FROM Pays WHERE continent = ‘Europe’
Donne le résultat :
Nom Capitale Population Surface Continent
Irlande Dublin 5 70 Europe
Autriche Vienne 10 83 Europe
UK Londres 50 244 Europe
Suisse Berne 7 41 Europe
USA Washington 350 441 Amérique
pays
MIN(population) MAX(population) AVG(population) SUM(surface) COUNT(*)
5 50 18 438 4
![Page 90: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/90.jpg)
90
DISTINCT
Suppression des doubles
SELECT DISTINCT continent FROM Pays
Donne le résultat :
Nom Capitale Population Surface Continent
Irlande Dublin 5 70 Europe
Autriche Vienne 10 83 Europe
UK Londres 50 244 Europe
Suisse Berne 7 41 Europe
USA Washington 350 441 Amérique
pays
Continent
Europe
Amérique
![Page 91: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/91.jpg)
91
ORDER BY
Tri destuplesdu résultat
SELECT continent, nom, populationFROM PaysWHERE surface > 60ORDER BY continent, nom ASC
2 possibilités : ASC / DESC
Nom Capitale Population Surface Continent
Irlande Dublin 5 70 Europe
Autriche Vienne 10 83 Europe
UK Londres 50 244 Europe
Suisse Berne 7 41 Europe
USA Washington 350 441 Amérique
pays
Continent Nom Population
Amérique USA 350 Europe Autriche 10 Europe Irlande 5 Europe Suisse 7 Europe UK 50
![Page 92: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/92.jpg)
92
GROUP BY
Partition de l'ensemble des tuples en groupes homogènes
SELECT continent, MIN(population), MAX(population),AVG(population), SUM(surface), COUNT(*)
FROM Pays GROUP BY continent ;
Nom Capitale Population Surface Continent
Irlande Dublin 5 70 Europe
Autriche Vienne 10 83 Europe
UK Londres 50 244 Europe
Suisse Berne 7 41 Europe
USA Washington 350 441 Amérique
pays
Continent MIN(population) MAX(population) AVG(population) SUM(surface) COUNT(*)
Europe 5 50 18 438 4
Amérique 350 350 350 441 1
![Page 93: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/93.jpg)
93
Renommage des attributs : AS
SELECT MIN(population) AS min_pop,MAX(population) AS max_pop,AVG(population) AS avg_pop,SUM(surface) AS sum_surface,COUNT(*) AS countFROM PaysWHERE continent = ‘Europe’ ;
min_pop max_pop avg_pop sum_surface count
5 50 18 438 4
![Page 94: Licence de Sciences et Techniques Unité denseignement BASES DE DONNEES Langage de manipulation SQL François Jacquenet Professeur d'Informatique Faculté](https://reader035.fdocument.pub/reader035/viewer/2022062511/551d9d84497959293b8bfa6e/html5/thumbnails/94.jpg)
Information pourla semaine du 25 au 29
septembre
Cours, TD et TPaux horaires prévus
sur l’emploi du temps