Le langage SQL Cours2: Introduction au langage SQL
H. MACHHOUR
Plan Présentation de SQL Normes SQL Objets manipulées par SQL Types de données Création d'une table Contraintes d'intégrité TP
2
Language SQL
06/03/2012
Présentation de SQL SQL = Structured Query Language =Langage d'interrogation structuré SQL: un langage de gestion de bases de données relationnelles. Conçu par IBM ( années 70) SQL: le langage standard des systèmes de gestion de bases de données (SGBD) relationnelles SGBDR: DB2, Oracle, Informix, Ingres, RDB,...
SQL :
un langage d'interrogation de la base (ordre SELECT) un langage de manipulation des données (LMD: ordres UPDATE, INSERT, DELETE) un langage de définition des données (LDD : ordres CREATE, ALTER, DROP) un langage de contrôle de l'accès aux données (LCD: ordres GRANT, REVOKE)
3
Language SQL
06/03/2012
Normes SQL 1986 : le SQL- ANSI (American National Standard Institute), 1989 : SQL- ISO (International Standard Organisation) et ANSI, 1992 : seconde version du SQL-ISO et ANSI ( SQL2), 1999 : la troisième version ET enfin la norme SQL3 en 2003.
4
Language SQL
06/03/2012
Objets manipulées par SQL Identificateurs suite de caractères (au plus 30 caractères) commençant obligatoirement par une lettre de l’alphabet. Exemples: nom de tables: ETUDIANT, PERSONNE, Entreprise,… Nom de champs: AGE, date_de_naissance, …
Les voyelles accentuées (é,è,à, ...) ne sont pas acceptées. SQL ne fait pas distinctions entre majuscules et minuscules:ETUDIANT=Etudiant= EtuDiant. Il ne faut pas choisir des mots clés réservés comme : ASSERT ASSIGN AUDIT COMMENT DATE DECIMAL DEFINITION FILE FORMAT INDEX LIST MODE OPTION PARTITION PRIVILEGES PUBLIC SELECT SESSION SET TABLE,… 5
Language SQL
06/03/2012
Objets manipulées par SQL Tables Les tables expriment les relations du modèle relationnel, ce sont les objets les plus utilisés par SQL, et ils sont composés de colonnes et de lignes Exemple : Pour la relation ETUDIANT(NoEtudiant, Nom, Prénom), on associé la table ETUDIANT
Nom de la table Nom de colonnes Identificateurs qui doivent respecter les remarques vu précédemment
6
Language SQL
06/03/2012
Objets manipulées par SQL Colonnes Les données d’une colonne doivent être d’un même type de données précisé lors de la création de la table. Chaque colonne est repérée par un identificateur unique. Une colonne peut porter le nom de sa table(mais à éviter). Deux colonnes de deux tables différentes peuvent porter le même nom. Exemple : colonne " Nom" de la table "Etudiant": Etudiant.Nom.
7
Language SQL
06/03/2012
Types de données
Domaines des attributs en modèle relationnel On distingue 4 types: Types alphanumériques Types numériques Types temporels Types binaires 8
Language SQL
06/03/2012
Types de données Types alphanumériques CHAR (n)
Longueur fixe de n caractères. n max : 16 383
VARCHAR (n)
Longueur variable, n représente le maximum
TEL CHAR(12) NOM VARCHAR(30) PRENOM VARCHAR(20)
9
Language SQL
06/03/2012
Types de données Types numériques Type
Instruction
Description
Nombres entiers
BYTE(TINYINT) sur 1 octet, de 0 à 255 SMALLINT
sur 2 octets, de -32.768 à 32.767
INTEGER
Sur 4 octets, de-2.147.483.648 à 2.147.483.647
Long
Sur 8 octets
Numériques non exacts à virgule flottante
REAL
simple précision, avec au moins 7 chiffres significatifs
NUMERIC,
double précision, avec au moins 15 chiffres significatifs
Nombres décimaux avec un nombre fixe de décimales
DECIMAL(p, d) (non reconnu sous Access!)
10
DOUBLE ou FLOAT
Language SQL
Couleur Byte Quantite INTEGER CNE LONG
POIDS NUMERIC
nombres décimaux qui ont p chiffres significatifs et d chiffres après la virgule
06/03/2012
Types de données Types temporels DATE
Champ date (ex 24/02/1997)
TIME
Champ heure (ex 14:45:10.95)
Date_N DATE
TIMESTAMP regroupe DATE et TIME
Types binaires permet d'enregistrer des données telles que les images et les sons, de très grande taille et avec divers formats. SQL-2 n'a pas normalisé ce type de données. LONG RAW pour Oracle, mais IMAGE pour Sybase, BYTE pour Informix, etc.) LONGBINARY, GENERAL, OLEOBJECT pour MSACCESS Exemple: photo LONGBINARY 11
Language SQL
06/03/2012
Création d'une table CREATE TABLE …. CREATE TABLE Personne ( ID INTEGER PRIMARY KEY , ID_Service INTEGER , Nom VARCHAR(20) , Prenom VARCHAR(20) , Sexe CHAR(1), Date_Naissance DATE , Ville_Naissance VARCHAR(20) , Situation_Familiale CHAR(15), Adresse VARCHAR(50) , Ville VARCHAR(20) , Telephone CHAR(14) , eMail VARCHAR(50) ); 12
Language SQL
06/03/2012
Contraintes d'intégrité Définition Les contraintes d’intégrité permettent d’indiquer les valeurs permises au sein des colonnes d’une table. Exemple : valeurs nulles, valeurs non nulles, clé primaire, clé étrangère…. Single-field constraint: CONSTRAINT name {PRIMARY KEY | UNIQUE | NOT NULL | REFERENCES foreigntable [(foreignfield1, foreignfield2)] [ON UPDATE CASCADE | SET NULL] [ON DELETE CASCADE | SET NULL]} Multiple-field constraint: CONSTRAINT name {PRIMARY KEY (primary1[, primary2 [, …]]) | UNIQUE (unique1[, unique2 [, …]]) | NOT NULL (notnull1[, notnull2 [, …]]) | FOREIGN KEY [NO INDEX] (ref1[, ref2 [, …]]) REFERENCES foreigntable [(foreignfield1 [, foreignfield2 [, …]])] [ON UPDATE CASCADE | SET NULL] [ON DELETE Language SQL CASCADE | SET NULL]} 06/03/2012 13
Contraintes d'intégrité Types de contraintes NOT NULL : La colonne ne peut pas contenir de valeurs NULL. ID_Service INTEGER Nom VARCHAR(20)
NOT NULL NOT NULL
UNIQUE : Chaque ligne de la table doit avoir une valeur différente ou NULL pour cette colonne. EMail VARCHAR(50) NOT NULL UNIQUE
PRIMARY KEY : Chaque ligne de la table doit avoir une valeur différente pour cette colonne. Les valeurs NULL sont rejetées. ID INTEGER PRIMARY KEY 14
Language SQL
06/03/2012
Contraintes d'intégrité Types de contraintes
(suite) FOREIGN KEY : Cette colonne fait référence à une colonne clé d'une autre table. constraint c1 foreign key (ID_Service) references SERVICE(ID)
CHECK : Permet de spécifier les valeurs acceptables pour une colonne.
Telephone CHAR(14) NULL CONSTRAINT ck_tph CHECK(Telephone LIKE ('[0-9][0-9] [0-9][0-9] [0-9][0-9] [0-9][0-9] [09]')), eMail VARCHAR(50) NOT NULL UNIQUE CONSTRAINT ck_email CHECK(eMail LIKE ('%@%.%')) Ne marche pas sous MS Access!!
REMARQUES
La colonne spécifiée comme référence doit être une colonne clé. Ces contraintes sont introduites directement après la définition d’une colonne ou à l’aide de la commande CONSTRAINT : CONSTRAINT nom_de_la_contrainte contrainte Ceci permet par la suite la suppression ou la modification de cette contrainte
15
Language SQL
06/03/2012
TP Créer les tables suivantes en mode SQL Colonne
NUMVOL
VD (Ville départ)
VA
HD (Heure départ)
HA
NUMPIL
NUMAV
Type
long
Varchar(30)
Varchar(30)
TIME
TIME
long
long
Contrainte
Primary Key, Not null
Not null
Not null
Not null
Not null
Not null, Foreign key
Not null, Foreign key
Table VOL Colonne
NUMAV
MARQUE
TYPE
NBRPLACE
Type
long
Varchar(20)
Varchar(20)
integer
Contrainte
Primary Key, Not null
Not null
Not null
Not null
Table AVION Colonne
NUMPIL
NOMPIL
SALAIRE
ADRPIL
Type
long
Varchar(50)
double
Varchar(200)
Contrainte
Primary Key, Not null
Not null
Table PILOTE
16
Language SQL
Exemple: CREATE TABLE Personne ( ID INTEGER NOT NULL PRIMARY KEY, ID_Service INTEGER NOT NULL, Nom VARCHAR(20) NOT NULL, Prenom VARCHAR(20) NOT NULL, Sexe CHAR(1) DEFAULT ‘M’, Date_Naissance DATE NOT NULL, Ville_Naissance VARCHAR(20) NOT NULL, Adresse VARCHAR(50) NOT NULL, Ville VARCHAR(20) NOT NULL, Telephone CHAR(14) NULL CONSTRAINT CONST_ID FOREIGN KEY ID_Service REFERENCES TABLE_Service(ID) ); 06/03/2012
TP Remplir les tables par les lignes suivantes: NUMVOL VD (Ville départ) VA
HD (Heure départ) HA
NUMPIL
NUMAV
1
Fes
Casa
14 :00
16 :00
1
100
2
Tanger
Casa
12 :00
14 :00
2
300
3
Casa
Fes
10 :00
12 :30
1
100
4
Oujda
Fes
08 :30
10 :20
3
400
5
Oujda
Tanger
08 :30
11 :00
2
300
Table VOL NUMAV
MARQUE TYPE
NBRPLACE
NUMPIL
NOMPIL
SALAIRE ADRPIL
100
Boeing
B 747
500
1
Ali
10000
Fes
200
Airbus
A 320
320
2
Adil
10500
Casa
300
Boeing
B 737
450
3
Med
8000
Fes
400
Airbus
A320
300
4
Reda
9000
Tanger
500
Boeing
B 700
380
5
Badr
11000
Rabat
17
Table AVION
Language SQL
Table PILOTE
06/03/2012