Nuances
On me demande souvent : à qui s’adresse cet article ? Mais croyez-moi, il n'est pas toujours facile de répondre : d'un côté, il y a des développeurs ninja qu'il est difficile de surprendre avec quoi que ce soit, et de l'autre, les jeunes padawans. Mais je peux dire une chose avec certitude: pour un lecteur intéressé par SQL, capable de compléter sa riche image avec des détails petits mais très intéressants. Cet article ne contiendra pas de pages d'un kilomètre de requêtes SQL, un maximum de 1 ou 2 lignes et seulement ce qui est rare à mon avis. Mais comme je veux être tout à fait franc, si vous n'êtes pas étranger à SQL, l'article vous semblera un peu ennuyeux. Tous les exemples de l'article, à l'exception du premier et du quatrième, peuvent être attribués à la norme SQL-92.Données
Afin de nous faciliter la vie, j'ai apposé une simple plaque signalétique sur laquelle certains points seront testés et par souci de concision, je donnerai le résultat de l'expérience sur ceux-ci. Je vérifie toutes les requêtes sur PostgreSql.Scripts et table de données
CREATE TABLE marchandises (id bigint NON NULL, caractère de nom variable (127) NON NULL, caractère de description variable (255) NON NULL, prix numérique (16,2) NON NULL, caractère d'articulation variable (20) NON NULL, horodatage act_time NON NULL , disponibilité booléenne NOT NULL, CONSTRAINT pk_goods PRIMARY KEY (id)); INSÉRER DANS les marchandises (id, nom, description, prix, articul, act_time, disponibilité) VALEURS (1, "Pantoufles", "Soft", 100,00, "TR-75", (ts "2017-01-01 01:01 : 01.01"), VRAI); INSÉRER DANS les marchandises (identifiant, nom, description, prix, articul, act_time, disponibilité) VALEURS (2, "Oreiller", "Blanc", 200,00, "PR-75", (ts "2017-01-02 02:02 : 02.02"), VRAI); INSÉRER DANS les marchandises (identifiant, nom, description, prix, articul, act_time, disponibilité) VALEURS (3, "Couverture", "Down", 300,00, "ZR-75", (ts "2017-01-03 03:03 : 03.03"), VRAI); INSÉRER DANS les marchandises (id, nom, description, prix, articul, act_time, disponibilité) VALEURS (4, "Taie d'oreiller", "Gris", 400,00, "AR-75", (ts "2017-01-04 04:04 : 04.04"), FAUX); INSÉRER DANS les marchandises (identifiant, nom, description, prix, articul, act_time, disponibilité) VALEURS (5, "Feuille", "Soie", 500,00, "BR-75", (ts "2017-01-05 05:05 : 05.05"), FAUX);
Demandes
1. Guillemets doubles
Et la première chose que j'ai est une question simple : Pourriez-vous donner un exemple de requête SQL utilisant double citations? Oui, pas en simple, en double ?Exemple avec des guillemets doubles
SELECT nom "Nom du produit" FROM marchandises
J'ai été très surpris lorsque j'ai vu cela pour la première fois. Si vous essayez de remplacer les guillemets doubles par des guillemets simples, le résultat sera complètement autre!
Cela peut ne pas sembler être un exemple très utile pour un véritable développement. Pour moi, ce n'est pas le cas. Maintenant, je l'utilise activement dans tous mes modèles SQL. Le point est simple : lorsque vous revenez après six mois à une requête SQL de 40 colonnes, oh comme leur « notre » nom vous sauve. Malgré le fait que je n'ai pas mentionné SQL-92, dans la dernière édition, il est fait mention des guillemets doubles.
2. Pseudo-tableau. SQL-92
C'est un peu inexact du point de vue de la terminologie, mais l'essence est simple : le tableau résultant d'une sous-requête dans la section FROM. Peut-être le fait le plus célèbre de cet articlePseudo-tableau
SELECT mock.nickname "Pseudo", (CAS QUAND mock.huff ALORS "Oui" ELSE "Non" FIN) "Offensé ?" FROM (SELECT nom AS surnom, disponibilité AS huff FROM marchandises) simulé
Dans notre exemple, la simulation est une pseudo table (parfois appelée table virtuelle). Naturellement, ils ne visent pas du tout à déformer le vrai sens. Un exemple est ceci.
3. Constructeur de blocs de données. SQL-92
Cela semble effrayant, simplement parce que je n’ai pas trouvé de bonne traduction ou interprétation. Et comme toujours, c’est plus facile à expliquer avec un exemple :Exemple de constructeur de bloc de données
SELECT nom "Nom du produit", prix "Prix" FROM (VALEURS ("Pantoufles", 100,00), ("Oreiller", 200,00)) AS marchandises(nom, prix)
| Nom du produit | Prix |
| Chaussons | 100.00 |
| Oreiller | 200.00 |
Dans la section DEPUIS mot-clé utilisé VALEURS, suivi des données entre parenthèses, ligne par ligne. Le fait est que nous ne sélectionnons pas du tout les données d'une table, mais les créons simplement à la volée, « l'appelons » une table, nommons les colonnes puis les utilisons à notre discrétion. Cette chose s'est avérée extrêmement utile lors du test de différents cas de requête SQL, lorsqu'il n'y a pas de données pour certaines tables (dans votre base de données locale) et que l'écriture d'insertion est trop paresseuse ou parfois très difficile, en raison de la relation entre les tables et les restrictions. .
4. Heure, date et heure et date
Tout le monde a probablement rencontré dans des demandes la nécessité de spécifier l'heure, la date ou la date et l'heure. De nombreux SGBD prennent respectivement en charge les littéraux t, d et ts pour travailler avec ces types. Mais c’est plus facile à expliquer avec un exemple : pour les littéraux d et t, tout est similaire.Je m'excuse auprès du lecteur de m'avoir induit en erreur, mais tout ce qui est dit dans le paragraphe 4 ne concerne pas le langage SQL, mais concerne les capacités de prétraitement des requêtes dans JDBC.
5. Déni. SQL-92
Nous connaissons tous l'opérateur PAS, mais très souvent ils oublient qu'il peut s'appliquer aussi bien à un groupe de prédicats qu'à une seule colonne :6. Comparaison des blocs de données. SQL-92
Encore une fois, je m'excuse pour la terminologie. C'est l'un de mes exemples préférésExemple de comparaison de blocs de données
SELECT * FROM marchandises WHERE (nom, prix, disponibilité) = ("Taie d'oreiller", 400,00, FALSE) -- ou son équivalent SELECT * FROM marchandises WHERE nom = "Taie d'oreiller" ET prix = 400,00 ET disponibilité = FALSE
Comme le montre l'exemple, comparer des blocs de données est similaire à comparer élément par élément signification_ 1 _block_1 = valeur_ 1 _block_2, valeur_ 2 _block_1 = valeur_ 2 _block_2, valeur_ 3 _block_1 = valeur_ 3 _block_2 en utilisant ET entre eux.
7. Opérateurs de comparaison avec les modificateurs ANY, SOME ou ALL. SQL-92
C'est là que quelques précisions s'imposent. Mais comme toujours, d'abord un exemple Qu'est-ce que cela signifie TOUS dans ce cas? Et cela signifie que la condition de sélection n'est satisfaite que par les lignes dont les identifiants (dans notre cas ce sont 4 et 5) sont plus grands n'importe lequelà partir des valeurs trouvées dans la sous-requête (1, 2 et 3). 4 est supérieur à 1 et à 2 et à 3. 5 est identique. Que se passe-t-il si nous remplaçons TOUS sur N'IMPORTE LEQUEL?Qu'est-ce que ça veut dire N'IMPORTE LEQUEL dans ce cas? Et cela signifie que la condition de sélection n'est satisfaite que par les lignes dont les identifiants (dans notre cas ce sont 2, 3, 4 et 5) sont plus grands au moins unà partir des valeurs trouvées dans la sous-requête (1, 2 et 3). Pour ma part j'ai associé TOUS Avec ET, UN N'IMPORTE LEQUEL Avec OU. QUELQUES Et N'IMPORTE LEQUEL analogues les uns aux autres.
8. Opérateurs pour travailler avec/sous les demandes. SQL-92
Il est bien connu qu’on peut combiner 2 requêtes entre elles grâce aux opérateurs SYNDICAT ou UNION TOUS. Ceci est souvent utilisé. Mais il y a 2 autres opérateurs SAUF Et COUPER.Exemple avec SAUF
En fait, les données du deuxième ensemble sont exclues du premier ensemble de valeurs.
En fait, l'intersection du premier ensemble de valeurs et du deuxième ensemble se produit.
C'est tout, merci de votre attention.
Éditorial
N1. Merci à streetflush pour les critiques constructives. J'ai rédigé un article contenant des informations sur ce qu'est une norme linguistique et ce qui ne l'est pas.N2. Le point 4 a été corrigé pour préciser que ts/d/t ne fait pas partie du langage SQL. Merci pour votre attention Melkij.
Il n'y a qu'une seule règle importante à retenir lors de l'utilisation de l'instruction EXCEPT.
L'ordre, le nombre et les types de données des colonnes doivent être les mêmes dans toutes les requêtes.
Selon la norme ANSI, les opérateurs d'ensemble UNION et EXCEPT ont la même priorité, mais l'opérateur INTERSECT est exécuté avant les autres opérateurs d'ensemble. Nous vous recommandons de contrôler explicitement la priorité des opérateurs à l'aide de parenthèses. C'est généralement une très bonne pratique.
Selon la norme ANSI, vous ne pouvez utiliser qu'une seule clause ORDER BY dans une requête. Insérez-le à la toute fin de la dernière instruction SELECT. Pour éviter toute ambiguïté dans la dénomination des colonnes et des tables, veillez à attribuer le même alias à toutes les colonnes de table correspondantes. Par exemple:
SELECT au_lname AS "lastname", au_fname AS "firstname" FROM auteurs SAUF SELECT emp_lname AS "lastname", emp_fname AS "firstname" FROM employés ORDER BY nom, prénom ;
De plus, étant donné que chaque liste de colonnes peut spécifier des colonnes avec des types de données compatibles correspondants, différentes plates-formes SGBDR peuvent avoir différentes options pour traiter des colonnes de différentes longueurs. Par exemple, si la colonne au_lname de la première requête de l'exemple précédent est nettement plus longue que la colonne emp_lname de la deuxième requête, différentes plates-formes peuvent avoir des règles différentes pour déterminer la longueur du résultat final. Mais de manière générale, les plateformes choisiront une taille plus longue (et moins restrictive) pour le résultat.
Chaque SGBDR peut avoir ses propres règles d'utilisation d'un nom de colonne si les noms dans les listes de colonnes sont différents. En général, les noms de colonnes de la première requête sont utilisés.
Les types de données ne doivent pas nécessairement être identiques, mais ils doivent être compatibles. Par exemple, les types CHAR et VARCHAR sont compatibles. Par défaut, le résultat défini dans chaque colonne sera la taille correspondant au plus grand type à chaque position particulière. Par exemple, une requête qui récupère des données de colonnes contenant des valeurs de type VARCHAR(IO) et VARCHAR(15) utiliserait le type et la taille VARCHAR(15).
Aucune des deux plates-formes ne prend en charge la clause CORRESPONDING )) EXCEPT
(SELECT statemenr.2 | VALUES (expressionl, expression2 [, ...])) SAUF
Vous permet de spécifier une ou plusieurs colonnes spécifiées manuellement qui sont incluses dans le jeu de résultats final. (C'est ce qu'on appelle un constructeur de lignes.) La clause VALUES doit spécifier exactement autant de colonnes que les requêtes de l'instruction EXCEPT. Bien que l'instruction EXCEPT DISTINCT ne soit pas prise en charge, son équivalent fonctionnel est EXCEPT. La clause CORRESPONDING n'est pas prise en charge. De plus, les types de données LONG VARCHAR, LONG VARGRAPHIC, BLOB, CLOB, DBCLOB, DATALINK et les types de structure ne sont pas utilisés dans la clause EXCEPT, mais ils peuvent l'être dans la clause EXCEPT ALL.
Si le jeu de résultats comporte une colonne qui porte le même nom dans toutes les instructions SELECT, ce nom est utilisé comme nom final de la colonne renvoyée par l'instruction. Si une colonne donnée est nommée différemment dans différentes instructions SELECT, vous devez alors renommer la colonne dans toutes les requêtes en utilisant la même clause d'alias AS dans chacune d'elles.
Si une seule requête utilise plusieurs opérateurs pour travailler avec des ensembles de données, celui entre parenthèses est exécuté en premier. Après cela, l'ordre d'exécution sera de gauche à droite. Cependant, toutes les instructions INTERSECT sont exécutées avant les instructions UNION et EXCEPT. Par exemple:
SELECT empno FROM employé WHERE workdept LIKE "E%" SAUF SELECT empno FROM emp_act WHERE projno IN (TF1000", TF2000", -AD3110")) VALEURS UNION ("AA0001"), ("AB0002"), ("AC0003") ;
Dans l'exemple ci-dessus, les identifiants de tous les employés travaillant dans le service commençant par « E » sont récupérés de la table des employés, puis les identifiants des personnes employées dans les projets IF1000, IF200 et AD3110 sont supprimés de la table des comptes des employés (emp_act ). Enfin, trois ID supplémentaires sont ajoutés - AA0001, AB0002 et AC0003 à l'aide de l'opérateur d'ensemble UNION.
MySQL
MySQL ne prend pas en charge l'opérateur EXCEPT. Vous pouvez également utiliser les opérateurs NOT IN ou NOT EXISTS.
La leçon couvrira le sujet de l'utilisation des opérations de requêtes d'union, d'intersection et de différence. Exemples d'utilisation Requête SQL Union, Exists et utilisation des mots-clés SOME, ANY et All. Fonctions de chaîne couvertes
Vous pouvez effectuer les opérations d'union, de différence et de produit cartésien sur un ensemble. Les mêmes opérations peuvent être utilisées dans les requêtes SQL (effectuer des opérations avec des requêtes).
Un mot spécial est utilisé pour combiner plusieurs requêtes SYNDICAT.
Syntaxe:
< запрос 1 >UNION[TOUS]< запрос 2 > <запрос 1>SYNDICAT<запрос 2>
La requête Union SQL est utilisée pour combiner les lignes de sortie de chaque requête en un seul jeu de résultats.
Si utilisé paramètre TOUS, alors toutes les lignes de sortie en double sont enregistrées. Si le paramètre est manquant, seules les lignes uniques restent dans le jeu de résultats.
Vous pouvez combiner n’importe quel nombre de requêtes ensemble.
L'utilisation de l'opérateur UNION nécessite que plusieurs conditions soient remplies :
- le nombre de colonnes de sortie de chaque requête doit être le même ;
- les colonnes de sortie de chaque requête doivent être comparables entre elles par type de données (par ordre de priorité) ;
- l'ensemble résultant utilise les noms de colonnes spécifiés dans la première requête ;
- ORDER BY ne peut être utilisé qu'à la fin d'une requête composée car il s'applique au résultat de la jointure.
Exemple: Afficher les prix des ordinateurs et des ordinateurs portables, ainsi que leurs numéros (c'est-à-dire, décharger de deux tables différentes en une seule requête)
✍Solution :
| 1 2 3 4 5 6 | SELECT `Nombre`, `Prix` FROM pc UNION SELECT `Nombre`, `Prix` FROM notebook ORDER BY `Prix` |
SELECT `Nombre`, `Prix` FROM pc UNION SELECT `Nombre`, `Prix` FROM notebook ORDER BY `Prix`
Résultat:
Regardons un exemple plus complexe avec une jointure interne :
Exemple: Trouver le type de produit, le nombre et le prix des ordinateurs et ordinateurs portables
✍Solution :
| 1 2 3 4 5 6 7 8 | Sélectionner un produit. `Type` , pc. `Numéro`, `Prix` DEPUIS le produit INNER JOIN SUR le PC. `Numéro` = produit. Produit UNION SELECT `Numéro`. `Type`, cahier. `Numéro`, `Prix` DEPUIS le notebook INNER JOIN produit SUR le notebook. `Numéro` = produit. `Nombre` ORDER PAR `Prix` |
SELECT product.`Type` , pc.`Number` , `Price` FROM pc INNER JOIN product ON pc.`Number` = produit.`Number` UNION SELECT product.`Type` , notebook.`Number` , `Price` FROM notebook INNER JOIN produit ON notebook.`Number` = produit.`Number` ORDER BY `Price`
Résultat: 
Union SQL 1. Trouvez le fabricant, le numéro de pièce et le prix de tous les ordinateurs portables et imprimantes
Union SQL 2. Retrouvez les numéros et les prix de tous les produits fabriqués par le fabricant Russie
Le prédicat d'existence SQL EXISTE
SQL dispose de fonctionnalités permettant d'effectuer des opérations d'intersection et de différence sur les requêtes : la clause INTERSECT (intersection) et la clause EXCEPT (différence). Ces clauses fonctionnent de la même manière que UNION : le jeu de résultats inclut uniquement les lignes présentes dans les deux requêtes - INTERSECT, ou uniquement les lignes de la première requête qui manquent dans la seconde - EXCEPT. Mais le problème est que de nombreux SGBD ne prennent pas en charge ces propositions. Mais il existe un moyen de s'en sortir : utiliser le prédicat EXISTS.
Le prédicat EXISTS est évalué à TRUE si la sous-requête renvoie au moins quelques lignes ; sinon, EXISTS est évalué à FALSE. Il existe également un prédicat NOT EXISTS, qui fait le contraire.
EXISTS est généralement utilisé dans les sous-requêtes dépendantes (par exemple, IN).
EXISTE (sous-requête de table)
Exemple: Trouvez des fabricants d'ordinateurs qui produisent également des ordinateurs portables
✍Solution :
SELECT Fabricant DISTINCT FROM produit AS pc_product WHERE Type = "Ordinateur" ET EXISTE (SELECT Fabricant FROM produit WHERE Type = "Ordinateur portable" ET Fabricant = pc_product.Manufacturer)
Résultat: 
Trouvez les fabricants d'ordinateurs qui ne fabriquent pas d'imprimantes
SQL QUELQUES Mots-clés | TOUT et TOUS
Les mots-clés SOME et ANY sont synonymes, vous pouvez donc utiliser l'un ou l'autre dans votre requête. Le résultat d'une telle requête sera une colonne de valeurs.
Syntaxe:
< выражение>< оператор сравнения>CERTAINS | N'IMPORTE LEQUEL (< подзапрос> ) <выражение><оператор сравнения>CERTAINS | N'IMPORTE LEQUEL (<подзапрос>)
Si, pour une valeur X renvoyée par une sous-requête, le résultat de l'opération " " renvoie TRUE , alors le prédicat ANY est également évalué à TRUE .
Exemple: Trouver des fournisseurs d'ordinateurs dont les numéros ne sont pas en vente (c'est-à-dire pas dans le tableau PC)
✍Solution :
Données sources du tableau :
Résultat: 
Dans l'exemple, le prédicat Number = ANY(SELECT Number FROM pc) renverra la valeur TRUE lorsque le Number de la requête principale sera trouvé dans la liste des Numbers de la table pc (renvoyée par la sous-requête). De plus, NOT est utilisé. L'ensemble de résultats sera composé d'une colonne - Fabricant. Pour éviter qu'un fabricant soit affiché plusieurs fois, le mot de service DISTINCT a été introduit.
Voyons maintenant l'utilisation du mot-clé ALL :
Exemple: Trouvez le nombre et les prix d'ordinateurs portables qui coûtent plus cher que n'importe quel ordinateur
✍Solution :
Important: Il convient de noter qu'en général, une requête avec ANY renvoie un ensemble de valeurs. Par conséquent, l'utilisation d'une sous-requête dans une clause WHERE sans les opérateurs EXISTS , IN , ALL et ANY , qui produisent une valeur booléenne, peut entraîner une erreur d'exécution de la requête.
Exemple: Trouver le nombre et les prix des ordinateurs dont le coût dépasse le coût minimum des ordinateurs portables
✍Solution :
Cette requête est correcte car l'expression scalaire Prix est comparée à une sous-requête qui renvoie une valeur unique
Fonctions pour travailler avec des chaînes en SQL
La fonction LEFT coupe le nombre de caractères spécifié par le deuxième argument à partir de la gauche d'une chaîne :
GAUCHE (<строка>,<число>)
La fonction RIGHT renvoie le nombre spécifié de caractères à droite d'une expression de chaîne :
DROITE(<строка>,<число>)
Exemple: Imprimez les premières lettres des noms de tous les fabricants
✍Solution :
SELECT DISTINCT LEFT(`Fabricant`, 1) FROM `product`
Résultat: 
Exemple: Imprimer les noms de fabricants commençant et se terminant par la même lettre
✍Solution :
Fonction de remplacement SQL
Syntaxe:
SELECT `nom` , REPLACE(`nom` , "a", "aa") FROM `enseignants`
Dans ce tutoriel, vous apprendrez à utiliser Opérateur SAUF dans SQL Server(Transact-SQL) avec syntaxe et exemples.
Description
Instruction SAUF SQL Server(Transact-SQL) est utilisé pour renvoyer toutes les lignes de la première instruction SELECT qui ne sont pas renvoyées par la deuxième instruction SELECT. Chaque instruction SELECT définira un ensemble de données. L'opérateur EXCEPT récupérera tous les enregistrements du premier ensemble de données, puis supprimera tous les enregistrements du deuxième ensemble de données des résultats.
Sauf requête
Explication: La requête EXCEPT renverra les enregistrements dans une zone grisée. Ce sont des enregistrements qui existent dans SELECT 1 et non dans SELECT 2.
Chaque instruction SELECT dans une requête EXCEPT doit avoir le même nombre de champs dans des jeux de résultats avec des types de données similaires.
Syntaxe
Syntaxe de l'instruction EXCEPT dans SQL Server (Transact-SQL) :
Paramètres ou arguments
les expressions sont les colonnes ou les calculs que vous souhaitez comparer entre deux instructions SELECT. Il n'est pas nécessaire qu'il s'agisse des mêmes champs dans chaque instruction SELECT, mais les colonnes correspondantes doivent être de types de données similaires.
tables - tables à partir desquelles vous souhaitez obtenir des enregistrements. Il doit y avoir au moins une table répertoriée dans la clause FROM.
Conditions OÙ - facultatives. Conditions qui doivent être remplies pour les enregistrements sélectionnés.
Note
- Les deux instructions SELECT doivent avoir le même nombre d'expressions.
- Les colonnes correspondantes dans chaque instruction SELECT doivent avoir des types de données similaires.
- L'instruction EXCEPT renvoie tous les enregistrements de la première instruction SELECT qui ne figurent pas dans la deuxième instruction SELECT.
- L'opérateur EXCEPT dans SQL Server est équivalent à l'opérateur MINUS dans Oracle.
Exemple d'expression unique
Examinons un exemple d'instruction EXCEPT dans SQL Server (Transact-SQL) qui renvoie un seul champ avec le même type de données.
Par exemple:
Transact-SQL
SELECT product_id FROM produits SAUF SELECT product_id FROM inventaire ;
SELECT produit_id DE produits SELECT produit_id DE l'inventaire ; |
Cet exemple d'instruction EXCEPT renvoie toutes les valeurs product_id qui se trouvent dans la table products et non dans la table d'inventaire. Cela signifie que si une valeur product_id existe dans la table products et existe également dans la table d'inventaire, la valeur product_id n'apparaîtra pas dans les résultats de la requête EXCEPT.
Exemple avec plusieurs expressions
Examinons ensuite un exemple de requête EXCEPT dans SQL Server (Transact-SQL) qui renvoie plusieurs colonnes.
Par exemple:
Transact-SQL
Dans cet exemple, la requête EXCEPT renvoie les enregistrements de la table contacts portant les noms contact_id, last_name et first_name, qui ne correspondent pas aux valeurs Employee_id, Last_name et First_name de la table Employees.