Download - EPID-CPI-ISAIP Philippe Bancquart - mise à jour 24/02/2005 - page 1 SQL PHILIPPE BANCQUART
EPID-CPI-ISAIPPhilippe Bancquart - mise à jour 24/02/2005 - page 1
SQL
PHILIPPE BANCQUART
EPID-CPI-ISAIPPhilippe Bancquart - mise à jour 24/02/2005 - page 2
Introduction
S.Q.L. est un langage structuré permettant d’interroger et de modifier les données contenues dans une base de données relationnelle. S.Q.L. signifie Structured Query Language.Il permet d'interroger la BD, modification de données, même modification de structure de base.S.Q.L. est un langage interactif, mais il peut aussi être intégré dans un langage de programmation pour le développement d’applications. S.Q.L. n’est pas le meilleur langage, en particulier pour la manipulation des données, mais c’est un standard.
EPID-CPI-ISAIPPhilippe Bancquart - mise à jour 24/02/2005 - page 3
Vocabulaire
SQL Modèle logique Physique
TABLE ENTITE FICHIER
COLONNE ATTRIBUTS CHAMP
LIGNE INSTANCE ENREGISTREMENT
CLE PRIMAIRE CLE PRIMAIRE
EPID-CPI-ISAIPPhilippe Bancquart - mise à jour 24/02/2005 - page 4
Tables Toutes les données sont rangées dans des tables
Elles sont composées de lignes et de colonnes
Chaque colonne de chaque ligne contient exactement une valeur de données.
EPID-CPI-ISAIPPhilippe Bancquart - mise à jour 24/02/2005 - page 5
Colonnes & lignes Chaque colonne porte un nom
Contient des données
Données de même type. Entier, caractère, chaîne.
Chaque ligne contient des données sur une occurrence de la table.
EPID-CPI-ISAIPPhilippe Bancquart - mise à jour 24/02/2005 - page 6
Clés primaires Chaque ligne est accessible par l’intermédaire d’une
clé primaire unique.
Sur chaque ligne, une ou un groupe de colonnes permettent d’identifier cette ligne de façon unique.
EPID-CPI-ISAIPPhilippe Bancquart - mise à jour 24/02/2005 - page 7
Application Lancer l’analyseur de requête
Choisir votre serveur
Sélection de la base PUBS
Dans la zone saisie de requête, faire les exercices.
EPID-CPI-ISAIPPhilippe Bancquart - mise à jour 24/02/2005 - page 8
Présentation des BD de SQLServer Master : Base maître, base des bases
Model : Pour créer une new Base
Tempdb : base temporaire
Pubs : base exemples sur laquelle nous travaillons.
Les autres bases utilisateurs.
Choix BD : USE nomBaseDonnees
EPID-CPI-ISAIPPhilippe Bancquart - mise à jour 24/02/2005 - page 9
Recherche de données Select * from Publishers
/* cette requête sélectionne toutes les données de la table publishers, remarque forme commentaire */
-- le * désigne toutes les colonnes, commentaire – ou /*
Régles pour tous les objects :
ne pas dépasser 128 caractéres parmi : les lettres (non accentuéees), les chiffres, @, $, #, - ;
Commencer par une lettre et pas d’espace dans ce cas alors encadré par [ ] l’objet.
Pas sensible à la casse
Forme complète : baseDonnees.propriétaire.NomTable.nomColonne
EPID-CPI-ISAIPPhilippe Bancquart - mise à jour 24/02/2005 - page 10
SELECT
EPID-CPI-ISAIPPhilippe Bancquart - mise à jour 24/02/2005 - page 11
Distinct Distinct supprime mes doubles dans le résultat.
Select states from stores
Select distinct state from stores
select distinct au_lname , au_fname from authors
Observons 2 fois ringer, mais prénom différent
EPID-CPI-ISAIPPhilippe Bancquart - mise à jour 24/02/2005 - page 12
Recherche qualifié select/from/where
La clause WHERE permet de spécifier quelles sont les lignes à sélectionner. Elle est suivie d'un prédicat qui sera évalué pour chaque ligne de la table. Les lignes pour lesquelles le prédicat est vrai seront sélectionnées. SELECT * FROM nom_table WHERE predicat;Un prédicat n'est ni plus ni moins que la façon dont on exprime une propriété. Les prédicats, qu'ils soient simples ou composés, sont constitués à partir d'expressions que l'on compare entre elles
EPID-CPI-ISAIPPhilippe Bancquart - mise à jour 24/02/2005 - page 13
exemples
select stor_name , state from stores where state = 'CA‘
Vérification pour chaque ligne si clause est vérifiée.
select stor_name , state from stores where state = 'CA' or state = 'WA'
EPID-CPI-ISAIPPhilippe Bancquart - mise à jour 24/02/2005 - page 14
OPERATEURS DU WHERE > , < , = ,<> , <= , >= , !>, !=
Remarque , mettre entre cotes les données de type char, varchar, datetime.
Magasins hors californie
Magasins dont ID > 7066
Magasins dont l’initiale précède N
EPID-CPI-ISAIPPhilippe Bancquart - mise à jour 24/02/2005 - page 15
Opérateurs du where select stor_name , city, state from stores where state != 'CA'
select stor_id, stor_name from stores where stor_id > '7066'
select stor_name from stores where stor_name < 'N'
EPID-CPI-ISAIPPhilippe Bancquart - mise à jour 24/02/2005 - page 16
between
Spécifie un intervalle, bornes comprises
Non compris not between
Magasin dont ID compris entre 6380 et 7066
Magasin < 6380 et sup > 7100
select stor_id, stor_name from stores where stor_id between '6380' and '7066'
select stor_id, stor_name from stores where stor_id not between '6380' and '7100'
EPID-CPI-ISAIPPhilippe Bancquart - mise à jour 24/02/2005 - page 17
LIKE correspondance
Correspond à des portions de caractères Uniquement avec des types caratères.% désigne un ensemble de caractères
_ remplace une lettre[ ] tout caractère unique appartenant à l’ensemble dans crochetTous magasins commençant par BTous magasins ne commençant pas par BTous magasins commençant par B C D ETous magasins commençant par la lettre entre B et FTous magasins de 4 caractères ayant un identifiant dont les 2
premiers caractères sont 70
EPID-CPI-ISAIPPhilippe Bancquart - mise à jour 24/02/2005 - page 18
LIKE
select stor_name , stor_id from stores where stor_name like 'B%'
select stor_name , stor_id from stores where stor_name not like 'B%'
select stor_name , stor_id from stores where stor_name like '[BCDE]%'
select stor_name , stor_id from stores where stor_name like '[B-F]%‘
select stor_name , stor_id from stores where stor_id like '70__'
EPID-CPI-ISAIPPhilippe Bancquart - mise à jour 24/02/2005 - page 19
IN (‘C1’,’C2’,’Cn’,…) Sélection des lignes dont les colonnes correspont à
une des entrées dans la liste.
Donner les magasins dans Oregon et Californie
select stor_name , stor_id , state from stores where state in ('CA','OR')
EPID-CPI-ISAIPPhilippe Bancquart - mise à jour 24/02/2005 - page 20
Liaisons de conditions Si plusieurs opérateurs l’ordre et le suivant
NOT/AND/OR
Utilisation des ( )
Donner titres catégorie ‘business’ ou ‘psychology’ et advance > 5500
EPID-CPI-ISAIPPhilippe Bancquart - mise à jour 24/02/2005 - page 21
Liaisons de conditions
select title_id, type, advance from titles where type = 'business' or type = 'psychology' and advance > 5500
select title_id, type, advance from titles where (type = 'business' or type = 'psychology' ) and advance > 5500
EPID-CPI-ISAIPPhilippe Bancquart - mise à jour 24/02/2005 - page 22
Changement de noms colonnes
Nouveau_nom = au_id
au_id as Nouveau_nom
select au_id as 'num secu' , au_fname as prénom , nom = au_lname from authors where state ='UT'
EPID-CPI-ISAIPPhilippe Bancquart - mise à jour 24/02/2005 - page 23
Opérations arithmétiques +,-,*,/,%(modulo)
select title_id , advance , price , advance + price as 'avance + prix ' from titles where type = 'business‘
select title_id , advance , price , advance + price as 'avance + prix ' from titles where price * ytd_sales > 80000 and type = 'business‘
select title , ytd_sales / 12 as 'prix mensuel'
from titles where price * ytd_sales > 10000 and type = 'business'
EPID-CPI-ISAIPPhilippe Bancquart - mise à jour 24/02/2005 - page 24
Valeurs NULL
Une valeur nulle est une valeur inconnue
NULL n’est pas ZERO
Test fonction is Null
Un null jamais égal à un autre null
Les null peuvent être regroupés
Les colonnes sont conçues pour accepter les nulls.
EPID-CPI-ISAIPPhilippe Bancquart - mise à jour 24/02/2005 - page 25
Null select title , price from titles where price is null
select title , advance from titles where advance < 5000 or advance is null
Les calculs comportant des nuls donnent des résultats nuls
select title_id , advance , price , advance/price from titles where type not in ('business' , 'psychology','trad_cook' )
EPID-CPI-ISAIPPhilippe Bancquart - mise à jour 24/02/2005 - page 26
Order by
La clause order by tri le résultat des requêtes
Non obligatoire dans la liste sélection
Les valeurs nulles sont en têtes de liste
select stor_name , state from stores order by stor_name
select ytd_sales * price , title_id from titles where type ='psychology' order by ytd_sales * price
select ytd_sales * price , title_id from titles where type ='psychology' order by pubdate
select ytd_sales * price , title_id from titles order by price
EPID-CPI-ISAIPPhilippe Bancquart - mise à jour 24/02/2005 - page 27
Fonction agrégat
AVG([DISTINCT | ALL] expression)Renvoie la moyenne des valeurs d' expression.
COUNT(* | [DISTINCT | ALL] expression)Renvoie le nombre de lignes du résultat de la requête. Si expression est présent, on ne compte que les lignes pour lesquelles cette expression n'est pas NULL.
MAX([DISTINCT | ALL] expression)Renvoie la plus petite des valeurs d'expression.
MIN([DISTINCT | ALL] expression)Renvoie la plus grande des valeurs d'expression.
SUM([DISTINCT | ALL] expression)Renvoie la somme des valeurs
EPID-CPI-ISAIPPhilippe Bancquart - mise à jour 24/02/2005 - page 28
Fonction agrégat Nombre lignes dans titles
Nombre lignes ‘advance’ dans titles
Donner le prix maxi et mini
Somme des ventes pour livre psychology
Moyenne des ventes (advance)
EPID-CPI-ISAIPPhilippe Bancquart - mise à jour 24/02/2005 - page 29
Fonction agrégat select count(*) from titles
select count(advance) from titles
select max(price) , min(price) from titles
select sum(ytd_sales) from titles where type = 'psychology'
select avg(advance) from titles
EPID-CPI-ISAIPPhilippe Bancquart - mise à jour 24/02/2005 - page 30
Manipulations des nuls select price from titles
select avg(price) from titles
Affecter 10€ aux livres qui n’ont pas de prix
select avg(isnull(price , 10)) from titles
EPID-CPI-ISAIPPhilippe Bancquart - mise à jour 24/02/2005 - page 31
exemples Combien de livres ‘business’ ont-ils été vendus
(ytd_sales)
Considerez que toutes les advances non décidées sont de 5000€ , quelle est la valeur moyenne d’une advance.
EPID-CPI-ISAIPPhilippe Bancquart - mise à jour 24/02/2005 - page 32
exemples select sum(ytd_sales) from titles where type
='business‘
select avg(isnull(advance , 5000)) from titles
EPID-CPI-ISAIPPhilippe Bancquart - mise à jour 24/02/2005 - page 33
Group by Group by organise les données par groupes en
fonction du contenu d’une ou plusieurs colonnes
L’agrégat est calculé pour chaque groupe
Toutes les valeurs nulles sont considérées dans le même groupe.
EPID-CPI-ISAIPPhilippe Bancquart - mise à jour 24/02/2005 - page 34
Group by Regroupez les lignes par type, calculez le prix moyen
de chaque type.
select type , avg(price) as 'prix moyen' from titles group by type
Quel est le prix moyen d’un livre par éditeur.
select pub_id , avg(price) from titles group by pub_id
EPID-CPI-ISAIPPhilippe Bancquart - mise à jour 24/02/2005 - page 35
Group by Donner les title_id croissant des prix * nb livres
Combien de livre dans chaque catégorie
EPID-CPI-ISAIPPhilippe Bancquart - mise à jour 24/02/2005 - page 36
Having
La clause having restreint les groupes
Elle applique une condition aux groupes APRES leur formation
Recherche d’un type de livre et du prix moyen de ce type, afficher uniquement des groupes dont le prix moyen est > 12
select avg(price) , type from titles group by type having avg(price) > 12
EPID-CPI-ISAIPPhilippe Bancquart - mise à jour 24/02/2005 - page 37
Having Calculer le prix moyen de tous les livres des éditeurs
dont au moins un livre est vendu moins de 10€
select pub_id , avg(price) from titles group by pub_id having min(price) < 10
EPID-CPI-ISAIPPhilippe Bancquart - mise à jour 24/02/2005 - page 38
Group by select title_id , price * ytd_sales from titles order by
price * ytd_sales
select type , count(*) from titles group by type