Les bases fondamentales du langage Transact SQL
Version 1.0
Sommaire
Dans ce cours, nous allons étudier les bases du langage Transact SQL. La version du langage Transact SQL utilisée est celle de SQL Server 2008. Pour ce faire, nous allons définir les différentes parties du langage (DML, DDL, DCL), puis détailler la partie DML, qui est celle qui sert à manipuler les données de façon générale.
Avant de lire ce cours, nous vous conseillons :
- D’avoir déjà utilisé l’interface d’administration de SQL Server 2008 : SQL Server Management Studio (Chapitre 1).
- D’avoir les bases dans la construction d’un modèle relationnel de données (Chapitre 2). - Les bases fondamentales du langage T-SQL
Le T-SQL (Transact Structured Query Langage) est un langage de communication avec une base de données relationnelle SQL Server. Il définit une batterie « simple » mais complète de toutes les opérations exécutables sur une base de données (lecture de données, opérations d’administration du serveur, ajout, suppression et mises à jour d’objets SQL - tables, vues, procédures stockées, déclencheurs, types de données personnalisés … -). Ce langage est composé d’instructions, réparties dans de 3 catégories distinctes :
• DML : Data Modification Language, soit langage de manipulation de données. Dans cette catégorie, s’inscrivent les instructions telles que l’instruction SELECT ou encore les instructions qui nous permettent la création, la mise à jour et la suppression de données stockées dans les tables de la base de données. Il est important de retenir que le DML sert simplement pour les données, et en aucun cas pour la création, mise à jour ou suppression d’objets dans la base de données SQL Server.
• DDL : Data Definition Language, soit langage de définition de données. Les instructions de cette catégorie, permettent d’administrer la base de données, ainsi que les objets qu’elle contient. Elles ne permettent pas de travailler sur les données. Aussi, elles ne seront pas traitées dans ce chapitre.
• DCL : Data Control Language, soit langage de contrôle d’accès. Cette catégorie d’instructions nous permet de gérer les accès (autorisations) aux données, aux objets SQL, aux transactions et aux configurations générales de la base.
Ces trois catégories combinées permettent que le langage T-SQL prenne en compte des fonctionnalités algorithmiques, et admette la programmabilité. Le T-SQL est non seulement un langage de requêtage, mais aussi un vrai langage de programmation à part entière. Sa capacité à écrire des procédures stockées et des déclencheurs (Triggers), lui permet d’être utilisé dans un environnement client de type .NET, au travers d’une application en C# ou en . Dans ce chapitre, nous allons détailler la partie DML du T-SQL exclusivement. Auparavant, nous étudierons différents éléments syntaxiques qui composeront la syntaxe de ce langage, à savoir les expressions, les opérateurs et les fonctions. Par la suite, nous traiterons l’aspect procédural (algorithmique) de ce langage.
Dans le T-SQL, nous pouvons utiliser des expressions, permettant de mettre en œuvre l’aspect algorithmique du langage. Les expressions peuvent prendre plusieurs formes.
- Les constantes : une constante est une variable, dont la valeur ne peut être changée lors de l’exécution d’instructions T-SQL.
- Les noms de colonnes : ils pourront être utilisés comme expressions. La valeur de l’expression étant la valeur stockée dans une colonne pour une ligne donnée.
- Les variables : il s’agit d’entités qui peuvent être employées en tant qu’expressions ou dans des expressions. Les variables sont préfixées par le caractère @. Les variables systèmes sont préfixées par les caractères @@. La valeur de l’expression variable est la valeur de la variable elle-même.
- Les fonctions : il est possible d’utiliser comme expression n’importe quelle fonction. Elles permettent d’exécuter des blocs d’instructions T-SQL, et de retourner une valeur.
- Les expressions booléennes : elles sont destinées à tester des conditions. Elles sont utilisées dans des structures algorithmiques de type WHILE, IF ou encore dans la clause WHERE d’une requête SQL, à affiner de permettre d’afficher une recherche, ou bien à poser une condition d’exécution.
- Les sous-requêtes : une sous requête SELECT peu être placée en tant qu’expression. La valeur de l’expression est la valeur renvoyée par la requête.
Les opérateurs nous permettent de combiner des expressions, des expressions calculées ou des expressions booléennes. Il existe plusieurs types d’opérateurs, que nous allons détailler :
- Les opérateurs arithmétiques :
| + | Addition |
| - | Soustraction |
| * | Multiplication |
| / | Division |
| % | Modulo (reste de division) |
- Les opérateurs de bits :
| & | ET |
| | |
OU |
| ^ | OU exclusif |
| ~ | NON |
- Les opérateurs de comparaison :
| = | Égale |
| > | Supérieur |
| >= | Supérieur ou égal |
| < | Inférieur |
| <= | Inférieur ou égal |
| <> |
Différent |
| Exp1 IN (exp2, exp3, …) | Compare l’expression seule à toutes les expressions de la liste |
| IS NULL | Renvoie True si l’expression est NULL. False le cas échéant |
| Exp1 BETWEEN minimum AND maximum | Recherche si la valeur de Exp1 est comprise entre la valeur « minimum » et « maximum ». |
| Les bornes minimum et maximum sont incluses | |
| EXISTS (Sous Requête) | Renvoie True, si et seulement si la sous requête renvoie au moins une ligne |
| Exp1 LIKE | Permet de filtrer des données suivant un modèle |
Pour l’opérateur de comparaison LIKE, les expressions permettent de définir un modèle de recherche pour la correspondance des données :
| _ | Un caractère quelconque |
| % | N caractères quelconques |
|
*ab…+ | |
| [a-z] | Un caractère dans l’intervalle a-z |
| *^ab…+ | Un caractère en dehors de la liste ou de l’intervalle spécifié |
| ab… | Le ou les caractères eux-mêmes |
- Les opérateurs logiques :
| OR | Retourne True si une expression des deux expressions (opérandes) est vraie |
| AND | Retourne True si les deux expressions (opérandes) sont vraies. |
| NOT | True si l’expression est fausse. |
Les fonctions se distinguent en deux catégories : celles créées par l’utilisateur, ou les fonctions système. Nous allons détailler ci-dessous les fonctions système, les fonctions utilisateur seront traitées dans un autre cours. Les fonctions système se divisent en différentes catégories :
- Les fonctions d’agrégation :
| COUNT (*) | Dénombre les lignes sélectionnées |
| COUNT ([ALL|DISTINCT] exp1) | Dénombre toutes les expressions non nulles ou les expressions non nulles distinctes |
| COUNT_BIG | Possède le même fonctionnement que la fonction COUNT, simplement, le type de données de sortie est de type bigint au lieu de int |
| SUM ([ALL|DISTINCT] exp1) | Somme de toutes les expressions non nulles ou des expressions non nulles distinctes |
| AVG ([ALL|DISTINCT] exp1) | Moyenne de toutes les expressions non nulles ou des expressions non nulles distinctes |
| MIN (exp1) OU MAX (exp1) | Valeur MIN ou valeur MAX d’exp1 |
| STDEV ([ALL|DISTINCT] exp1) | Ecart type de toutes les valeurs de l’expression donnée |
| STDEVP ([ALL|DISTINCT] exp1) | Ecart type de la population pour toutes les valeurs de l’expression donnée |
|
VAR ([ALL|DISTINCT] exp1) | Variance de toutes les valeurs de l’expression donnée |
| VARP ([ALL|DISTINCT] exp1) | Variance de la population pour toutes les valeurs donnée |
| GROUPING | |
| CHECKSUM (* | *exp1…+) | Permet de calculer un code de contrôle par rapport à une ligne de la table ou par rapport à une liste d’expression. Cette fonction permet la production d’un code de hachage |
| CHECKSUM_AGG ([ALL|DISTINCT] exp1) | Permet le calcul d’une valeur de hachage par rapport à un groupe de données. Ce code de contrôle permet de savoir rapidement si des modifications ont eu lieu sur un groupe de données, car cette valeur de contrôle n’est plus la même après modification des données |
- Les fonctions mathématiques :
| ABS (exp1) | Valeur absolue d’exp1. |
| CEILING (exp1) | Plus petit entier supérieur ou égal à exp1. |
| FLOOR (exp1) | Plus grand entier supérieur ou égal à exp1. |
|
SIGN (exp1) | Renvoie 1 si exp1 est positive, -1 si elle est négative, et 0 si elle est égale à 0. |
| SQRT (exp1) | Racine carrée d’exp1. |
| POWER (exp1, n) | Exp1 à la puissance n. |
| SQUARE (exp1) | Calcul du carré d’exp1. |
- Les fonctions trigonométriques :
| PI () | Valeur de PI. |
| DEGREES (exp1) | Conversion d’exp1 de radian vers degrés. |
| RADIANS (exp1) | Conversion d’exp1 de degrés vers radians. |
| SIN (exp1), COS (exp1), TAN (exp1), COT (exp1) | Sin, cos ou tangente d’exp1. |
|
ACOS (exp1), ASIN (exp1), ATAN (exp1) | Arc cos, arc sin ou arc tan d’exp1. |
| ATN2 (exp1, exp2) | Angle dont la tangente se trouve dans l’intervalle exp1 et exp2. |
- Les fonctions logarithmiques :
| EXP (exp1) | Exponentielle d’exp1. |
| LOG (exp1) | Logarithme d’exp1. |
| LOG10 (exp1) | Logarithme base 10 d’exp1. |
- Les fonctions de dates :
| Format | Abréviation | signification |
| Year | Yy, yyyy |
Année (1753 à 9999) |
| quarter | Qq, q | Trimestre (1 à 4) |
| Month | Mm, m | Mois (1 à 12) |
| Day of year | Dy, y | Jour de l’année (1 à 366) |
| Day | Jour dans le mois (1 à 31) | |
| Weekday | Dw, ww | Jour de la semaine (1 à 7) |
| Hour |
Hh | Heure (0 à 23) |
| Minute | Mi, n | Minute (0 à 59) |
| Seconds | Ss, s | Seconde (0 à 59) |
| milliseconds | Ms | Milliseconde (0 à 999) |
| GETDATE () | Date et Heure système. |
| DATENAME (format, exp1) | Renvoie la partie date sous forme de texte. |
| DATEPART (format, exp1) | Renvoie la valeur de la partie date selon le format donné. |
|
DATEDIFF (format, exp1, exp2) | Différence entre les deux tables selon le format donné. |
| DATEADD (format, p, exp1) | Ajoute p format à la date exp1. |
| DAY (exp1) | Retourne le numéro du jour dans le mois. |
| MONTH (exp1) | Retourne le numéro du mois. |
| YEAR (exp1) | Retourne l’année. |
| SWITCHOFFSET (datetimeoffset, zone_horaire) | Convertis le type datetimeoffset en le type passé en second paramètre. |
| SYSDATETIME | Retourne la date et l’heure usuelle du serveur dans le format datetime2. |
| SYSDATETIMEOFFSET | Fonctionne de la même manière que SYSDATETIME, mais il prend en compte le décalage GMT. |
- Les fonctions de chaîne de caractères :
|
ASCII (exp1) | Valeur du code ASCII du premier caractère d’exp1. |
| UNICODE (exp1) | Valeur numérique correspondant au code UNICODE d’exp1. |
| CHAR (exp1) | Caractère correspondant au code ASCII d’exp1. |
| NCHAR (exp1) | Caractère UNICODE correspondant au code numérique d’exp1. |
| LTRIM (exp1), RTRIM (exp1) | Supprime les espaces à droit pour RTRIM et à gauche pour LTRIM d’exp1. |
| STR (exp1, n, p) | Convertit le nombre exp1, en chaine de longueur maximale n dont p caractères seront à droite de la marque décimale. |
| SPACE (n) | Renvoie n espaces. |
| REPLICATE (exp1, n) | Renvoie n fois exp1. |
| CHARINDEX (‘masque’, exp1) PATINDEX (‘%masque%’, exp1) |
|
| LOWER (exp1), UPPER (exp1) | Change la casse. LOWER va convertir exp1 en minuscules et UPPER va convertir exp1 en majuscules. |
| REVERSE (exp1) | Retourne les caractères d’exp1 dans le sens inverse. |
| RIGHT (exp1, n) | Renvoie les n caractères les plus à droite d’exp1. |
| LEFT (exp1, n) | Renvoie les n caractères les plus à gauche d’exp1. |
| SUBSTRING (exp1, n, p) | Renvoie p caractères d’exp1 à partir de n. |
| STUFF (exp1, n, p, exp2) | Supprime p caractères d’exp1, à partir de n, puis insère exp2 à la position n. |
| SOUNDEX (exp1) | Renvoie le code phonétique d’exp1. |
| DIFFERENCE (exp1, exp2) | Compare les SOUDEX des deux expressions. La valeur,qui peut être renvoyée va de 1 à 4,4, valeur pour laquelle, les deux expressions possèdent la plus grande similitude. |
|
LEN (exp1) | Retourne le nombre de caractères d’exp1. |
| QUOTENAME (exp1) | Permet de transformer exp1 en identifiant valide pour SQL Server. |
| REPLACE (exp1, exp2, exp3) | Permet de remplacer dans exp1 toutes les occurrences d’exp2 par exp3. |
- Les Fonctions systèmes :
| COALESCE (exp1, exp2…) | Renvoie la première expression non NULL. | |
| COL_LENGTH (nom_table, nom_colonne) | Longueur de la colonne. | |
| COL_NAME (id_table, id_colonne) | Nom de la colonne. | |
| DATALENGTH (exp1) | Longueur en octet de l’expression. | |
| DB_ID (Nom_base) | Numéro d’identification de la base de données. | |
|
DB_NAME (id_base) | Nom de la base. | |
| GETANSINULL (nom_base) | Renvoie 1 si l’option ‘ANSI NULL DEFAULT’ est positionné pour la base. | |
| HOST_ID () | Numéro d’identification du poste. | |
| HOST_NAME () | Nom du poste. | |
| IDENT_INCR (nom_table) | Valeur de l’incrémentation définit pour la colonne identité de la table spécifiée. | |
| IDENT_SEED (nom_table) | Valeur initiale définie pour la colonne identité de la table indiquée. | Retourne la dernière valeur de type identité utilisé par cette table. |
| INDEX_COL (nom_table, id_index, id_cle) | Nom de la colonne indexé correspondant à l’index. | |
| ISDATE (exp1) |
Renvoie 1 si l’expression de type varchar possède un format date valide. | |
| ISNULL (exp1, valeur) | Renvoie valeur si exp1 est NULL. | |
| ISNUMERIC (exp1) | Renvoie 1 si l’expression de type varchar a un format numérique valide. | |
| NULLIF (exp1, exp2) | Renvoie NULL si exp1 = exp2. | |
| OBJECT_ID (objet) | Numéro d’identification de l’objet. | |
| OBJECT_ID (name) | Nom de l’objet dont l’id est placé en argument. | |
| STATS_DATE (id_table, id_index) | Date de la dernière mise à jour de l’index. | |
| SUSER_SID (nom_acces) | Numéro d’identification correspondant au nom_acces. | |
| SUSER_SNAME (id) | Nom d’accès identifié par l’id. | |
|
USER_NAME (id) | Nom de l’utilisateur dont l’id est placé en | |
| argument. | ||
| CURRENT_TIMESTAMP | Date et heure système, équivalent à GETDATE (). | |
| SYSTEM_USER | Nom d’accès. | |
| CURRENT_USER, USER, SESSION_USER | Nom de l’utilisateur de la session. | |
| OBJECT_PROPERTY (id, propriété) | Permet de retrouver les propriétés de la base. | |
| ROW_NUMBER | Permet de connaitre le numéro d’une ligne issue d’une partition depuis un jeu de résultats. | |
| RANK | Permet de connaitre le rang d’une ligne issue d’une partition dans une série de résultats. | |
| DENSE_RANK |
Fonctionne comme RANK, mais ne s’applique qu’aux lignes de la série de résultat. | |
| HAS_DBACCESS (nom_base) | Permet de savoir si, avec le contexte de sécurité actuel, il est possible d’accéder à la base. (retourne 1 dans ce cas, dans le cas contraire, 0) | |
| HAS_PERMS_BY_NAME | Permet de savoir par programmation, si l’on dispose d’un privilège ou non. | |
| KILL | ||
| NEWID () | Permet de gérer une valeur de type UniqueIdentifier. | |
| NEWSEQUENTIALID () | Permet de gérer la prochaine valeur de type UniqueIdentifier. | |
| PARSENAME (nom_objet, partie_à_extraire) | Permet d’extraire à partir du nom complet de l’objet, le nom de l’objet. La partie partie_à_extraire peut prendre la valeur 1, 2, 3, 4 selon si l’on veut extraire le nom de l’objet, le schéma, la base, ou encore le nom du serveur. | |
| PUBLISHINGSERVERNAME | Permet de savoir qui est à l’origine d’une publication. | |
| STUFF (chaine1, n, p, chaine2) | Permet de supprimer p caractères de la chaine chaine1, à partir des positions n, puis d’y insérer chaine2 |
- Les fonctions conversion de types :
| CAST (exp1 AS types_données) | Permet de convertir une valeur dans le type spécifié en argument |
| CONVERT (types_données, exp1, style) | Conversion de l’expression dans le type de données spécifié. Un style peut être spécifié dans le cas d’une conversion date ou heure |
- Les fonctions diverses :
| RAND (exp1) | Nombre aléatoire compris en 0 et 1. Exp1 est la valeur de départ |
| ROUND (exp1, n) | Arrondis exp1 à n chiffres après la virgule |
Pour toutes les instructions du DML, il existe dans SQL Server un outil simple pour retrouver la syntaxe voulue rapidement (Pour des instructions simples, telle le SELECT, UPDATE…). La démarche est simple. Via le menu contextuel d’une table, sélectionnez « Générer un script de la table en tant que… ». Il nous est alors proposé de sélectionner l’action que nous voulons accomplir : SELECT, INSERT, UPDATE ou DELETE. Cette action peut aussi être réalisée sur d’autres objets SQL de la base de données.
INSERT INTO [Entreprise].[dbo].[Client]
([Nom_Client]
,[Prenom_Client]
,[Numero_Client]
,[Adresse_Client]
,[Mail_Client])
VALUES
(<Nom_Client, varchar(50),>
,<Prenom_Client, varchar(50),>
,<Numero_Client, varchar(20),>
,<Adresse_Client, varchar(50),>
,<Mail_Client, varchar(50),>)
GO
Dans ce code générique, nous demandons à SQL Server d’ajouter un enregistrement à la table Client, appartenant au schéma dbo dans la base de données Entreprise. Pour préciser les colonnes pour lesquelles nous allons ajouter des données, il est nécessaire de préciser le nom des colonnes, après l’instruction INSERT INTO. Le mot clé VALUES nous permet de fournir des valeurs aux champs. Il est impératif que les valeurs soient dans le même ordre que celui des colonnes, tout d’abord pour la cohérence des données, mais aussi pour respecter la compatibilité des données avec le type que vous avez assigné à votre table au moment de sa création. Dans le cas où certaines de vos colonnes acceptent des valeurs NULL, il existe deux méthodes pour obtenir cette valeur. La première, est d’omettre le nom de la colonne et la valeur correspondante dans l’instruction. La seconde vise à laisser la colonne dans la description, mais à préciser le mot clé NULL dans la clause VALUES. Pour des chaines de caractères, il faut placer celles-ci entre simples cotes. Dans le cas d’un champ de type identité (possédant une incrémentation automatique grâce à la contrainte IDENTITY), il n’est pas nécessaire de spécifier ni le nom du champ, ni sa valeur.
Procédons à un exemple pour mieux comprendre :
| INSERT INTO [Client] (Nom_Client, Prenom_Client, Numero_Client, Adresse_Client, Mail_Client) VALUES
'Grégory', +33563456764, '31 place de la chance', '') GO |
Après avoir exécuté le code ci-dessus, le message suivant apparait, confirmant de sa bonne exécution :
Dans le cas d’une insertion multiple d’enregistrements, la syntaxe sera la même, à l’exception près qu’au lieu d’une seule série de données après le mot clé VALUES, vous en spécifier le nombre voulu. Si nous voulons ajouter deux enregistrements dans une même instruction Insert, alors la syntaxe est la suivante :
| INSERT INTO [Client] (Nom_Client, Prenom_Client, Numero_Client, Adresse_Client, Mail_Client) VALUES ('CASANOVA', 'Grégory', +33563456764, '31 place de la chance', ''),
('RAVAILLE', 'James', +33567876435, '34 Avenue de le paix', '') GO |
Le message suivant s’affiche, après l’exécution de cette instruction, ce qui confirme bien que l’enregistrement multiple a été exécuté sans erreur :
Enfin, il est possible d’ajouter des enregistrements à l’aide de l’instruction SELECT, qui va copier les enregistrements d’une table (source) vers une autre table (destination). Voici un exemple :
INSERT Commande
SELECT Id_Client, GETDATE(), Id_Stock, 1
FROM Client, Stock
WHERE Id_Client = 3 AND Id_Stock = 5
Dans ce cas, nous allons ajouter dans la table commande, les informations sélectionnées. Ici,
Id_Client, la date du jour grâce à la fonction GETDATE(), Id_Stock, et le chiffre 1 qui correspond à la quantité que nous voulons ajouter à la commande de notre client. Les informations concernant Id_Client et Id_Stock seront sélectionnées en fonction des conditions précisées après la clause WHERE. Grâce à ce lot, nous allons ajouter la troisième ligne présente dans le résultat présenté cidessous.
L’instruction UPDATE, permet de mettre à jour un ou plusieurs enregistrements. La syntaxe
générique de cette instruction est la suivante :
SET [Nom_Client] = <Nom_Client, varchar(50),>
,[Prenom_Client] = <Prenom_Client, varchar(50),>
,[Numero_Client] = <Numero_Client, varchar(20),>
,[Adresse_Client] = <Adresse_Client, varchar(50),>
,[Mail_Client] = <Mail_Client, varchar(50),>
WHERE <Conditions de recherche,,>
GO
L’instruction ci-dessus permet de mettre à jour la table Client de la base de données Entreprise. La clause SET permet d’indiquer les champs à mettre à jour. La clause WHERE, sert à cibler les enregistrements à mettre à jour. Voici l’enregistrement de la table Client dont le champ Id-Client vaut 3 :
Voici une instruction SQL permettant de modifier le nom de ce client :
UPDATE [Entreprise].[dbo].[Client]
SET [Adresse_Client] = ‘18 Rue du cotton’
WHERE Id_Client = 3
GO
Après l’exécution de l’instruction ci-dessus, voici les données de l’enregistrement modifié :
Il est aussi possible d’effectuer des opérations grâce à un UPDATE. Par exemple, on peut augmenter les prix des articles d’un magasin de 10%, en multipliant le prix de tous les articles par 1,1.
L’instruction DELETE permet de supprimer des enregistrements. La syntaxe générique est la
suivante :
DELETE FROM [Entreprise].[dbo].[Client]
WHERE <Conditions de recherche,,> GO
DELETE FROM [Entreprise].[dbo].[Client]
WHERE Id_Client = 4
GO
Après avoir exécuté le code, on remarque que le client dont l’identifiant est 4, n’existe plus :
La suppression multiple de données est possible, par exemple si dans notre cas, nous avions précisé une plage d’identifiants dans notre clause WHERE.
L’instruction SELECT permet de sélectionner des données (tout ou partie d’enregistrements), d’une ou plusieurs tables. Elle offre aussi la possibilité de les trier, et de les regrouper. La syntaxe générale de cette instruction est la suivante :
SELECT [Id_Client]
,[Nom_Client]
,[Prenom_Client]
,[Numero_Client]
,[Adresse_Client]
,[Mail_Client]
FROM [Entreprise].[dbo].[Client]
GO
Voici une instruction SELECT permettant de lire le nom et l’adresse Email de tous les clients (si notre but avait été de sélectionner toutes les colonnes, au lieu de lister toutes celles-ci, il est possible d’indiquer que nous les sélectionnons toutes avec le simple caractère « * ») :
SELECT [Nom_Client]
,[Mail_Client]
FROM [Entreprise].[dbo].[Client] GO
Le résultat sera le suivant :
Par défaut, le nom de la colonne est celui du nom de la colonne dans la table. Il est possible d’en changer en utilisant des alias. Voici un exemple d’utilisation d’alias :
| ----- --Il existe deux manières de renommer les colonnes. --Celle-ci : ----- SELECT 'Nom Client' = [Nom_Client] ,'Mail Client' = [Mail_Client] FROM [Entreprise].[dbo].[Client] GO ----- --Ou encore celle là : ----- SELECT [Nom_Client] AS 'Nom Client'
,[Mail_Client] AS 'Mail Client' |
Le nom des colonnes est changé par un nom « plus explicite » :
Il est alors possible d’ajouter des conditions à notre recherche pour l’affiner, au travers de la clause WHERE. Les restrictions servent à limiter le nombre d’enregistrements à sélectionner. Les conditions contenues dans le WHERE sont des expressions booléennes qui peuvent être composées de noms de colonnes, de constantes, de fonctions, d’opérateurs de comparaison et d’opérateurs logiques. Prenons un exemple concret :
SELECT [Nom_Client] AS 'Nom Client'
,[Mail_Client] AS 'Mail Client'
FROM [Entreprise].[dbo].[Client]
WHERE Id_Client IN (1,2,3,6)
GO
Cette instruction SELECT sélectionne tous les champs de tous les enregistrements pour lesquels la colonne Id_Client est égale soit à 1, 2, 3 et 6. On remarque alors que dans notre code, nous avons utilisé la condition WHERE, une colonne, un opérateur de comparaison et un opérateur logique. Le résultat est le suivant :
SELECT [Nom_Client] AS 'Nom Client'
,[Mail_Client] AS 'Mail Client'
FROM [Entreprise].[dbo].[Client]
WHERE Id_Client BETWEEN 1 AND 10
GO
L’instruction ci-dessus présente l’utilisation des clauses WHERE et BETWEEN, qui permet de lire tous les enregistrements dont l’identifiant est compris entre 1 et 10 (bornes incluses). Le résultat est le suivant :
Les projections de données sont utiles dans certains cas, par exemple lorsque vous voulez lister les villes dans lesquelles sont présents vos clients. Une projection va grouper les enregistrements identiques dans un seul et même enregistrement. Voici les deux cas possibles de projection :
|
----- -- Deux façons de grouper les colonnes identiques : -- Celle-ci : ----- SELECT Mesure, COUNT(Mesure) AS 'Nombre article avec cette mesure' FROM Stock GROUP BY Mesure GO ----- -- Ou celle là : ----- SELECT DISTINCT Mesure FROM Stock GO Pour le second morceau de code, on pourra seulement afficher les résultats de façon distincte, c'est-à-dire en évitant les doublons comme dans le premier exemple. En revanche, il ne sera pas possible d’utiliser une fonction d’agrégation, type COUNT(), car elle doit être contenue dans une clause GROUP BY. On obtiendra alors le résultat identique au premier exemple, hors mis le fait que nous ne pouvons pas compter le nombre d’occurrence de chaque mesure dans la colonne Mesure. Les calculs, comme nous les appelons, regrouperont les calculs numériques mais aussi les manipulations sur les chaines de caractères, par exemple la concaténation. Les modèles sont les suivants : SELECT Id_Stock,
'Quantité Produit' = Quantite * 3 FROM Stock Ici, la quantité de chaque Stock sera multipliée par trois dans le résultat de la recherche par l’instruction SELECT. Mais la valeur de la quantité de produit ne sera en aucun cas changer dans la base de données. SELECT Nom_Client + ' ' + Prenom_Client AS 'NOM COMPLET' FROM Client Dans l’instruction ci-dessus, nous concaténons les champs Nom_Client et Prenom_Client en une seule colonne que nous appellerons NOM COMPLET. Le résultat est le suivant : Le but du produit cartésien est de croiser des données de plusieurs tables, de manière à obtenir toutes les combinaisons possibles. Il y aura autant d’enregistrements de retour que le produit du nombre de lignes de chaque table. Donnons un exemple : ----- -- Il existe deux manières de faire un produit cartésien : -- La syntaxe classique : ----- SELECT Id_Client, Nom_Client, Date_Commande FROM Client, Commande ----- -- La syntaxe en SQL ANSI : ----- SELECT Id_Client, Nom_Client, Date_Commande FROM Client CROSS JOIN Commande Le résultat est le suivant :
Une jointure est un produit cartésien avec une restriction. Une jointure permet d’associer logiquement des lignes de tables différentes. Les jointures sont généralement (pour des raisons de performances) utilisées pour mettre en relation les données de lignes comportant une clé étrangère avec les données de lignes comportant une clé primaire. Voyons-le en détail avec un exemple concret : ----- -- Il existe deux manières de faire une jointure : -- La syntaxe classique : ----- SELECT Client.Id_Client, Date_Commande FROM Client, Commande WHERE Client.Id_Client = Commande.Id_Client ----- -- La syntaxe SQL ANSI : ----- SELECT Client.Id_Client, Date_Commande FROM Client INNER JOIN Commande ON Client.Id_Client = Commande.Id_Client Le résultat est le suivant et il est le même pour les deux instructions SQL : 3.3.7.1 Les jointures externesLes jointures externes sont des jointures dans lesquelles la condition est fausse. Dans ce cas, le résultat retourné sera celui d’une des deux tables. Le résultat sera celui de la première table citée si on utilise l’option LEFT, et celui de la seconde table citée si l’on utilise l’option RIGHT. La syntaxe est la suivante : SELECT Client.Id_Client, Date_Commande
FROM Client LEFT OUTER JOIN Commande ON Client.Id_Client = Commande.Id_Client SELECT Client.Id_Client, Date_Commande FROM Client RIGHT OUTER JOIN Commande ON Client.Id_Client = Commande.Id_Client Et le résultat est le suivant : La clause ORDER BY est utilisée dans une instruction SELECT pour trier les données d’une table (ou plusieurs tables) en fonction d’une ou plusieurs colonnes. Par défaut, le rangement se fera par ordre croissant ou par ordre alphabétique. Avec le mot clé ASC, le rangement se fera dans l’ordre ascendant. Avec le mot clé DESC, le rangement se fera dans l’ordre descendant. Prenons un exemple :
Avec la close ORDER BY, nous obtiendrons le même résultat que précédemment, trié dans un ordre différent : les enregistrements sont triés selon le champ Nom_Client de façon croissante pour le premier lot, de façon décroissante pour le second lot. Le résultat est le suivant : Les enregistrements sont bien rangés dans l’ordre inverse, suivant la colonne Nom_Client. L’opérateur UNION va nous permettre d’obtenir un ensemble de ligne provenant de plusieurs requêtes différentes. Toutes les requêtes doivent fournir le même nombre de colonnes avec les mêmes types de données pour chaque colonne (correspondance deux à deux). SELECT Id_Stock, Quantite FROM Stock UNION SELECT Id_Stock, Quantite FROM Commande Le résultat est le suivant : L’opérateur EXCEPT permet d’extraire d’une solution les éléments que l’on ne veut pas y retrouver, c'est-à-dire, enlever une valeur précise ou un domaine que l’on ne veut pas retrouver dans notre solution finale. Il est donc évident que si on exclut des valeurs, les deux expressions SELECT séparées par le mot clé EXCEPT doivent avoir le même nombre de colonnes en argument. Prenons un exemple : SELECT Id_Stock, Quantite FROM Stock EXCEPT SELECT Id_Stock, Quantite FROM Stock WHERE Id_Stock = 3 Ici, on sélectionnera les colonnes Id_Stock et Quantite de la table Stock, excepté celle pour lesquelles l’Id_Stock est égal à 3. Grace à cet opérateur, il va être possible d’identifier en une seule requête, des lignes d’informations simultanément présentes dans deux jeux de résultats distincts, mais de mêmes structures. SELECT * FROM Client WHERE Id_Client BETWEEN 1 AND 3 SELECT * FROM Client WHERE Prenom_Client = 'Julien' SELECT * FROM Client WHERE Id_Client BETWEEN 1 AND 3 INTERSECT SELECT * FROM Client WHERE Prenom_Client = 'Julien' Le résultat est le suivant : Le jeu de données obtenu donne tous les clients dont l’Id est compris entre 1 et 3, et dont le nom est Julien. L’opérateur INTERSECT, fait l’intersection des deux jeux de résultats, et ne donne en sortie, que les valeurs communes aux deux jeux. Dans l’exemple donné, les deux jeux de résultats n’ont en résultat le client dont l’Id est 3. Le résultat final ne donnera donc que le client dont l’Id est 3, comme montré sur l’exemple ci-dessus. La close TOP permet d’extraire grâce à l’instruction SELECT, que les premiers enregistrements de la sélection. Elle est utilisable avec les instructions INSERT, UPDATE, DELETE. Prenons un exemple avec l’instruction SELECT : SELECT TOP 5 * FROM dbo.Client SELECT TOP 50 PERCENT WITH TIES * FROM dbo.Client ORDER BY Nom_Client Cette instruction permet de sélectionner 50% des enregistrements dans l’ordre de lecture des enregistrements. Dans le cas ou nous avons utilisé un pourcentage, la close WITH TIES ne s’utilise que si une close ORDER BY est appliquée au SELECT. Elle a pour effet de ne sélectionner les enregistrements qu’après la mise en leur tri. Il est possible de créer une table à l’aide de colonnes de tables déjà existantes. Grâce à un simple SELECT INTO, nous aurons à choisir les colonnes qui constitueront les champs de la nouvelle table. Toutes les closes et conditions disponibles pour l’instruction SELECT sont applicables pour l’instruction SELECT INTO. Voici un exemple : SELECT Id_Client, Nom_Client, Id_Commande INTO dbo.Exemple FROM dbo.Client, dbo.Commande WHERE Client.Id_Client = Commande.Id_Client La clause COMPUTE est utilisée à la suite de la clause ORDER BY, afin de retourner un sous résultat, en rapport avec le résultat principal. Le sous résultat est obligatoirement généré par une fonction d’agrégation telle que COUNT, SUM… Il est bon de noter que ces clauses sont maintenues pour des raisons de compatibilités, mais sont vouées à disparaitre dans les versions futures. L’exemple suivant retourne un résultat principal, et un sous résultat. Le résultat principal sélectionne toutes les colonnes de la table Client, ordonnées par le nom des clients, tandis que le sous résultat va compter le nombre de client. L’intérêt de la clause COMPUTE est de pouvoir générer un sous résultat, grâce à une même requête. SELECT * FROM .Client ORDER BY Nom_Client COMPUTE COUNT(Id_Client) Le mot clé BY de la clause COMPUTE, nous permet de retourner les sous résultats en fonction des différentes valeurs d’une colonne spécifique. Dans l’exemple, à la suite, on peu remarquer que l’on donne la quantité du stock, pour chaque Id_Stock en sous résultat. La colonne que l’on précise donc après le mot clé BY, nous permet de dire, de quelle manièr |
