Base De Donnes Suite Commande SQL 1 Calcul

Base De Données Suite Commande SQL 1

Calcul dans la sélection ‘select’ • Calcul sans rapport avec table: il est possible d’utiliser la commande select pour calculer une expression arithmétique: select 1+2 as resultat; • Il est également possible d ’effectuer plusieurs opérations de calcul sur les données d ’une ou plusieurs tables: – Compter le nombre d’occurrence dans une table: select count(clé) as res [where condition]. Il est conseillé d’appliquer la fonction count sur la clé primaire, ce qui évite directement les redondance 2

Fonction de calcul avec ‘select’ • Opérations arithmétique: – Addition, multiplication, soustraction et division • Opérations binaires: – et bit à bit & – ou bit à bit | – << décalage de bit à gauche – >> décalage de bit à droite – select BIG_COUNt(n): compte le nombre de bit dans l’argument N • Opérations logiques – Not: select not 1 3

Fonction de calcul avec ‘select’ – or/ and – = !=, >=, <= – Between val 1 et val 2 – Rappelons que toutes ces opérations peuvent être effectuées sans table, et que les conversion implicite entre type sont appliquées automatiquement – in: select 1 in (0, 3, ‘ rrr ’); L’opérateur in est utiliser pour une appartenance d’ensemble, il peut également être utilisé pour comparer le résultat de deux sélection 4

Fonction de calcul avec ‘select’ – isnull(expression) répond 1 si l’expression et diffèrent de 0 et null 0 autrement – coalesce(liste): permet de retrouver le premier élément non null de la liste. – interval(v 1, v 2, …, v 3): 1 si tous les vi vérifie vi<vi+1. Elle retourne la position de l élément dans la liste à partir duquel la contrainte est vérifiée • Fonctions de comparaison de chaînes: – c 1 like c 2% – c 1 like c 2_ – c 1 like c 2_: avec difference Maj/ Min 5

Fonction de calcul avec ‘select’ – Expression regexp pat, Expression rlike pat: permettent de recherché le pattern pat dans la chaîne Expression. – ifnull(exp 1, exp 2): retourne exp 1 si exp 1 n’est pas null exp 2 autrement. – Binary: transforme la chaîne qui le suit en une chaîne insensible à la case. – Select BINARY ‘‘ a’’=‘‘A’’ répond vrai – if(exp 1, exp 2, exp 3): si exp 1 est vrai alors le test répond exp 2 sinon exp 3. – case value when v 1 then r 1 when v 2 then r 2 else r 3 – case when c 1 then r 1 when c 2 then r 2 else r 3 6

Fonction de calcul avec ‘select’ – abs(x): valeur absolue – sign (n): retourne le signe de l’argument – mod(n, m): retourne le modulo le reste de la division euclidienne de N par M. – floor(x): retourne le plus grand entier possible mais inférieur à x. – ceiling(x): retourne le plus petit entier possible mais plus grand que x. – round(x): x arrondi à l’entier plus proche – round(x, d): retourne le résultat arrondi au décimal el plus proche avec D décimals – log(x), log 10(x) 7

Fonction de calcul avec ‘select’ – pow(x, y), power(x, y): puissance x y – sqrt(x): racine carré de l’argument – pi(), retourne la valeur de pi – cos(x), sin(x), tan(x), acos(x), asin(x), atan 2(x, y), cot(x): fonctions trigonométrique – rand(), rand(n): valeur aléatoire – least(v 1, v 2, …) greats(v 1, v 2, …): plus petit et plus grand élément – ascii(n): retourne le code ascii du paramètre – conv(chaine, base_init, base_dest): conversion d ’une base à l ’autre – bin(n): une châine représentant n en binaire 8

Fonction de calcul avec ‘select’ • oct(n), hex(n): conversion en base 8 et base 16 • concat(x, y, z): concaténation des chaîne, si l’un des paramètre est NULL le résultat est NULL • longeur(str): taille de la chaîne • locate(substr, str), position(substr in str): recherche d ’une sous chaîne dans une chaîne • instr(str, substr): position de la première occurrence de substr dans str • lpad(str, len, padch); rpad(str, len padch): complète la chaîne str padch à droite ou à 9 gauche

Fonction de calcul avec ‘select’ – left(str, len): retourne les len premiers caractères de la chaîne – right(str, len): retourne les len derniers caractères de la chaîne. – ltrim(str), rtrim(str), trim(str): supprimer les caractères non significatifs – soundex(str): retourne une représentation phonétique de la chaîne – space(n): retourne une chaîne contenant n espace – replace(str, from_str, to_str): remplace toute les occurrences de from_str par to_str. – insert(str, pos, len, newstr): remplacer par newstr les 10 caractères de pos sur une longeur de len

Fonction de calcul avec ‘select’ – elt(n, str 1, str 2, …): renvois la n chaîne, null si n est inférieur à 0 ou supérieur au nombre de chaînes en paramètre – field(str, st 1, . . ) retourne l ’index de la chaîne str dans l ’ensemble des chaîne en paramètres – find_in_set(val, set) • Fonctions date et heures: – dayofweek(date): extrait le jour de la date passée en paramètre. Retourne le jour de la semaine sous forme d ’index commençant à 1. – weekday(date): le jour de la semaine en indx à 11 partir de 0

Fonction de calcul avec ‘select’ – dayname(date): le jour en toute lettre – fonctions d ’addition, de conversion et d ’extraction (jour, mois année, date_add, date_subb, to_days(), from_days() – fonctions de manipulation heure , minutes secondes (now(), sysdate(), …) • Fonctions diverses: – select database(); : nom de la base de données courante – identification de l’utilisateur user(), system_user(), session_user() – password(str): générer un mot de passe à partir de la 12 chaîne en paramètre

Fonction de calcul avec ‘select’ – encrypt(str): chiffrer la chaîne str en utilisant le chiffrement standard d’unix – md 5(string): applique la fonction de hachage md 5 à la chaîne de caractère en paramètre – Une panoplie de fonctions permettants toutes opérations utiles et nécessaires. 13

Regroupement • Il est possible de présenter le résultat d ’une sélection par tranche selon un critère donné. • Typiquement lor d ’une sélection entre plusieurs tables, où l ’une est maître on voudrais bien avoir le résultat regrouper selon la clé de la table maître: • select * from client commande where client. codc=commande. cod group by client. codc • Certaines fonctions de calcul sont utilisés conjointement au group by 14

Regroupement • count(distinct(arg)): nombre d’occurrence • distinct est utilisé pour assurer la non redondance des données prises en compte lor de l ’opération de comptage • avg(arg): moyenne • min(arg), max(arg): minimum et maximum • sum(arg): somme • std(arg), stddev(arg): dérivation standard de arg 15

Tri du résultat dans ‘select’ • Pour ordonner (à l ’affichage) le résultat d ’une sélection il est possible d ’utiliser la commande order by critère de tri. • L’ordre du tri est spécifié par asc: ascendant ou descendant par desc. • Select nom, count(nom)) as nb_nom from ma_table group by nom order by nb_nom asc. Compter le nombre de nom distincts et afficher le résultat trié par ordre ascendant selon le nombre d’occurrence du nom. 16

Exprimer des conditions sur des résultats calculés • Certaines requêtes nécessitent d ’exprimer des conditions non pas sur des colonnes de la tables mais plutôt sur le résultat d ’une fonction de calcul. • Ce type de condition est exprimé en utilisant la close having exp cond • select *, sum(ca) as chiffre_affaire from commade, client where commande. codc=client. codc having chiffre_affaire >100 17 order by chiffre_affaire

La clause Having • L’utilisation de having est toujours accompagné de l’utilisation d’un group by et est toujours utilisé avant tout contraintes order by. 18

Notion de jointure • Une jointure correspond à une liaison de référence entre deux ou plusieurs tables. • On distingue trois types de jointures: – Equi jointure entre deux tables: permet de sélectionner le résultat du produit cartésien des deux tables pour le quel la condition de jointure est vérifié. La jointure est généralement exprimé en référence à deux colonne respectives au niveaux des tables participant à la jointure. Avec My. Sql, il n’est pas possible d ’utiliser INNER JOIN, toute fois le résultat de la jointure peut être obtenu moyennant une condition where 19

Notion de jointure – Jointure à gauche: permet de sélectionner toutes les occurrences de la première tables n ’existant pas dans la deuxième table – Jointure à droite: permet de sélectionner toutes les occurrences de la deuxième table n ’existant pas dans la première table. – Avec My. Sql seul left join est fonctionnel. – Select * from tab 1 left join tab 2 where tab 1. id= tab 2. id – Select * from tab 1 left join tab 2 using(id) 20

Exists en SQL Standard Requêtes d’applications 21

Procédure utilisateur 22