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


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.


Vocabulaire

SQL Modèle logique Physique

TABLE ENTITE FICHIER

COLONNE ATTRIBUTS CHAMP

LIGNE INSTANCE ENREGISTREMENT

CLE PRIMAIRE CLE PRIMAIRE


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.


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.


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.


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.


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


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


SELECT


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


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


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'


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


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'


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'


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


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__'


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')


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


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


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'


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'


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.


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' )


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


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


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)


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


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


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.


exemples select sum(ytd_sales) from titles where type

='business‘

select avg(isnull(advance , 5000)) from titles


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.


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


Group by Donner les title_id croissant des prix * nb livres

Combien de livre dans chaque catégorie


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


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


Group by select title_id , price * ytd_sales from titles order by

price * ytd_sales

select type , count(*) from titles group by type

epid-cpi-isaip philippe bancquart - mise à jour 24/02/2005 - page 1 sql philippe bancquart

Documents