SQL

1. SQL PHILIPPE BANCQUART

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.

3. Vocabulaire

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.

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.

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.

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.

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

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

10. SELECT

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

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

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'

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

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'

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'

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 crochet Tous magasins commençant par B Tous magasins ne commençant pas par B Tous magasins commençant par B C D E Tous magasins commençant par la lettre entre B et F Tous magasins de 4 caractères ayant un identifiant dont les 2 premiers caractères sont 70

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

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

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

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

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'

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'

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.

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

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

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

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)

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

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

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.

32. exemples • select sum(ytd_sales) from titles where type ='business‘ • select avg(isnull(advance , 5000)) from titles

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.

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

35. Group by • Donner les title_id croissant des prix * nb livres • Combien de livre dans chaque catégorie

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

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

38. Group by • select title_id , price * ytd_sales from titles order by price * ytd_sales • select type , count(*) from titles group by type