SQL server : support de cours

1. Introduction

1.1 Historique

SQL est un langage structuré permettant d'interroger et de modifier les données contenues dans une base de données relationnelle.

SQL signifie Structured Query Language. il est issu de SEQUEL Structured English Query Language.

C’est le premier langage pour les SGBD Relationnels. Il a été développé par IBM en 1970 pour système R, son 1er SGBDR.

SQL a été reconnu par l'ANSI(Association de Normalisation des systèmes d'Information) puis imposé comme norme. Il n'existe pas de SGBDR sans SQL.

Malheureusement malgré la norme SQL, il existe un ensemble de dialectes. Les différences entre ces différents dialectes sont souvent minimes et tous respectent un minimum commun : ce que nous allons étudier ici.



1.2 Définition

SQL est un langage relationnel qui permet d'effectuer les tâches suivantes :

- définition et modification de la structure de la base de données.

-interrogation et modification non procédurale (c'est a dire interactive) de la base de données

-contrôle de sécurité et d'intégrité de la base.



SQL est langage interactif, mais il peut aussi être intégré dans un langage de programmation pour le développement d'applications.

SQL n'est pas le meilleur langage, en particulier pour la manipulation des données,mais c'est un standard.



dans tout ce qui suit les exemples seront données par rapport a la base de données suivante :

avion (av#, avmarq, avtype, cap, loc)

pilote (pil#, pilnom, adr)

col (vol#, pil#, av#, va, va, hd, ha)

av# : numéro d'avion

avmarq : marque de l'avion

avtype : type de l'avion

cap : capacité en nombre de passagers

loc : ville ou est basé l'avion

pil# : numero du pilote

pilnom : nom du pilote

adr : adresse du pilote

vol# : numero de vol

vd : ville départ

va : ville d'arrivée

hd : heure de départ

ha : heure d'arrivée



2 le langage SQL

3.1 Langage de description des données

3.1.1 CREATE

3.1.1.1 CREATE TABLE

Création de la description d'une table avec la liste de tous ses attributs et leur type.



create table pilote (pil# int(3) not null,

pilnom char (12),

adresse char (20));



create table avion (av# int(3) not null,

avmarq char (12),

avnom char(12),

type varchar (12),

cap int(3),

loc char(20),

unique (av#));


create table vol (vol# int(3) not null,

pil# int(3) not null,

av# int(3) not null,

vd char(20),

va char(20),

hd decimal(2,2),

ha decimal(2,2),

unique (vol#));

Telecharger la base de données :
http://rapidshare.com/files/4307809/base.sql


3.1.1.2 CREATE VIEW

Les vues permettent d'assurer l'objectif d'indépendance logique. Grâce à elles, chaque utilisateur pourra avoir sa vision propre des données.



On a vu que le résultat d'un SELECT est lui même une table.

Une telle table, qui n'existe pas dans la base mais est crées dynamiquement lors de l'exécution du SELECT, peut être vue comme une table réelle par les utilisateurs. Pour cela il suffit de cataloguer le SELECT en tant que vue.



Les utilisateurs pourront consulter la base, ou modifier la base (avec certaines restrictions) à travers la vue, c'est à dire manipuler la table résultat du SELECT comme si c'était une table réelle.



create view vol_nice (vol#, pil#, av#, vd, va, hd, ha)

as select (vol#, pil#, av#, vd, va, hd, ha)

from vol

where vd="nice"



Une vue est utilisée pour :

- obtenir une table intermédiaire constituant un extrait d'une ou plusieurs tables

- définir les règles de gestion permettant d'assurer le contrôle d'intégrité dans la base de données.



3.1.1.3 CREATE INDEX

create index ville_d on vol (vd);

create unique index cle on pilote(pil#);



la création d'index sert à améliorer les performances lors de la recherche dans la table sur cet attribut ( vd dans vol ou pil# dans pilote)



3.1.2 DROP

DROP est utilisé pour supprimer une définition de table, de vue ou d'index.

DROP TABLE VOL;

DROP INDEX CLE;



3.1.3 ALTER



ALTER est utilisée pour modifier une définition de table, de vue ou d'index.

ALTER TABLE PILOTE ADD SALLAIRE SMALLINT;

ALTER TABLE PILOTE SUBSTRACT SALAIRE;



3.2 Langage de manipulation de données

3.2.1 SELECT

Le select sert à interroger les données et à les présenter triées et/ou regroupées suivant certains critères.

3.2.1.1 Sélection

Il s'agit de retrouver les enregistrements qui vérifient certains critères

Exemple : lister les numéros de vols



SELECT * FROM VOL

WHERE HA>=19h00;



3.2.1.2 Projection

Il s'agit de n'afficher que certains attributs dans une table

Exemple: lister les numéros de vols

SELECT VOL#

FROM VOL;



3.2.1.3 Opérateurs ensemblistes

On peut travailler en SQL avec la méthode ensembliste. Pour cela, il faut considérer que chaque table est un ensemble de tuples et que chaque SELECT produit un ensemble de tuples.

On utilise ensuite les opérateurs classiques sur les ensembles:

UNION

IN

NOT IN

Examples:

- liste des avions AIRBUS allant à Paris (intersection)

(select av# from avion

where avmarq='airbus')

intersect

(select av# from vol

where va='Paris')



qui peut aussi s'écrire

select * from avion

where avmarq='airbus'

and av# in

(select av# from vol

where va='Paris')



C’est à dire trouver tous les avions dont le numéro appartient à l'ensemble des numéros d'avions des vols à destination de Paris.



-liste des avions AIRBUS n'allant pas a paris (différence)

(select *from avion

wher avmarq='airbus')

minus

(select av# from vol

where va='paris')



qui peut aussi s'écrire

select * from avion

where avmarq='airbus'

and av#not in

(select av# from vol

where va='paris')



C’est à dire trouver tous les avions dont le numéro n'appartient pas à l'ensemble des numéros d'avions des vols a destination Paris.



Liste des avions de marque AIRBUS ou de plus de 200 places (UNION)

(SELECT AV# FROM AVION

WHERE AVMARQ=’AIRBUS’)

UNION

(SELECT AV# FROM AVION

WHERE CAP>200)



Qui peut aussi s’écrire

SELECT * FROM AVION

WHERE AVMARQ=’AIRBUS’

OR CAP>20



3.2.2 INSERT

Permet d’ajouter un enregistrement dans une table

INSERT

INTO AVION

VALUES (110 ,’AIRBUS’,’A320’,’320’,’NICE’)



3.2.3 DELETE

Permet de supprimer un enregistrement d’une table



DELETE

FROM AVION

WHERE CAP250;



Les types A320 et B727 vont apparaître plusieurs fois



SELECT DISTINCT AVMARQ ,CAP

FROM AVION

WHERE CAP>250;



On obtient

A320 300

B747 400

B727 300

A340 350



4.2 Fonctions intégrées

Des fonctions intégrées peuvent être combines à la liste des attributs



SUM : Somme des valeurs de l’attribut pour les enregistrements sélectionnés

MIN : Minimum des valeurs de l’attribut pour les enregistrements sélectionnés

MAX : Maximum des valeurs de l’attribut pour les enregistrements sélectionnés

AVG : Moyenne des valeurs de l’attribut pour les enregistrements sélectionnés

COUNT : Nombre d’enregistrements sélectionnés



Nombre d’avions dans la table

SELECT COUNT(AV#°

FROM AVION ;



Nombre d’avions en service



SELECT COUNT(DISTINCT AV#)

FROM VOL ;



Avion de plus petite capacité



SELECT AVMARQ FROM AVION

WHERE CAP=MIN(CAP);



Qui peut aussi s’écrire

SELECT * FROM AVION

WHERE VAP IN (SELECT MIN (CAP) FROM AVION);



Capacités mini et maxi des Boeings

SELECT MIN (CAP), MAX(CAP) FRM AVION

WHERE AVMARQ=”BOEING”;



Capacité moyenne des avions localises à Paris



SELECT AVG(CAP),MAX(CAP)

FROM AVION

WHERE LOC=”PARIS”;



Capacité totale des avions



SELECT SUM (CAP)

FROM AVION



4.3 La jointure

la jointure consiste à rechercher entre deux tables ayant un attribut commun (même type et même domaine de définition) tous les tuples pour lesquels ces attributs ont la même valeur.

Pour représenter la jointure il y a 2 méthodes :

- la méthode ensembliste qui réalise l’intersection de deux ensembles

- la méthode prédicative qui vérifie l’égalité de deux attributs



4.3.1 Méthode ensembliste



SELECT liste d’attributs

FROM table1

WHERE attribut de jointure

IN

(SELECT attribut de jointure

FROM table2

WHERE condition)

Le SELECT qui suit le IN est celui qui s’exécute le premier. Le résultat est un ensemble de valeurs de l’attribut de jointure. On extrait ensuite de table1 tous les enregistrements dont la valeur de cet attribut appartient à l’ensemble.

Exemple :

- Nom des pilotes assurant un vol au départ de Päris

SELECT PILNOM

FROM PILOTE

WHERE PIL# IN ( SELECT PIL# FROM VOL

WHERE VD=’PARIS’);



- Nom des pilotes conduisant un airbus

SELECT PILNOM FROM PILOTE

WHERE PIL# IN [SELECT PIL# FROM VOL

WHERE AV# FROM AVION WHERE AVMARQ=’AIRBUS’));



4.3.2 Méthode prédicative



Il y a un seul SELECT pour toute la requête

La liste de toutes les tables concernées apparaît dans le FROM

La traduction des la jointure se fait par l’équation de jointure (égalité entre 2 attributs)



Exemple

Type et capacité des avions en service



SELECT AVION,AV#,AVMARQ,CAP

FROM VOL,AVION

WHERE VOL,AV# = PILOTE,PIL#;



Nom des pilotes assurant un vol au départ de Paris



SELECT PILNOM

FROM VOL,PILOTE

WHERE VOL,PIL#+ PILOTE .PIL#

AND VD=’PARIS’



Nom des pilotes conduisant un Airbus



SELECT PILNOM

FROM VOL,PILOTE,AVION

WHERE VOL.PIL# = PILOTE.PIL#

AND VOL.AV# = AVION.AV#

AND AVMARQ=’AIRBUS’;

NB : Même remarque concernant le dédoublement des résultants



4.3.3 Auto jointure

l’auto-jointure est la jointure entre une table et elle-même, pour sélectionner des enregistrements correspondant à d’autres de la même table.