Share
Il est important que chaque développeur Web se familiarise avec les interactions avec les bases de données. Dans la deuxième partie de la série, nous allons continuer à explorer le langage SQL et appliquer ce que nous avons appris sur une base de données MySQL. Nous étudierons les index, les types de données et des structures de requête plus complexes..
Veuillez vous reporter à la section "Ce dont vous avez besoin" du premier article ici: SQL pour les débutants (première partie).
Si vous souhaitez suivre les exemples de cet article sur votre propre serveur de développement, procédez comme suit:
Les index (ou clés) sont principalement utilisés pour améliorer la rapidité des opérations d'extraction de données (par exemple, SELECT) sur les tables.
Ils constituent une partie si importante d’une bonne conception de base de données qu’il est difficile de les qualifier d’optimisation. Dans la plupart des cas, ils sont inclus dans la conception initiale, mais ils peuvent également être ajoutés ultérieurement avec une requête ALTER TABLE..
Les raisons les plus courantes d'indexation des colonnes de base de données sont les suivantes:
Presque toutes les tables devraient avoir une clé primaire, dans la plupart des cas en tant que INT avec l'option AUTO_INCREMET.
Si vous vous souvenez du premier article, nous avons créé un champ 'user_id' dans la table users et il s'agissait d'une clé primaire. De cette façon, dans une application Web, nous pouvons faire référence à tous les utilisateurs par leur numéro d'identification.
Les valeurs stockées dans une colonne PRIMARY KEY doivent être uniques. De plus, il ne peut y avoir plus d'une clé primaire sur chaque table..
Voyons un exemple de requête créant une table pour la liste des états américains:
CREATE TABLE états (id INT KEY PRIMARY KEY, nom VARCHAR (20));
Cela peut aussi être écrit comme ceci:
CREATE TABLE états (id INT AUTO_INCREMENT, nom VARCHAR (20), PRIMARY KEY (id));
Puisque nous nous attendons à ce que le nom de l'état soit une valeur unique, nous devrions un peu changer l'exemple de requête précédent:
CREATE TABLE états (id INT AUTO_INCREMENT, nom VARCHAR (20), PRIMARY KEY (id), UNIQUE (nom));
Par défaut, l'index sera nommé d'après le nom de la colonne. Si vous le souhaitez, vous pouvez lui attribuer un nom différent:
CREATE TABLE états (id INT AUTO_INCREMENT, nom VARCHAR (20), PRIMARY KEY (id), UNIQUE state_name (nom));
Maintenant, l'index s'appelle 'state_name' au lieu de 'name'.
Disons que nous voulons ajouter une colonne pour représenter l'année à laquelle chaque état a rejoint.
États CREATE TABLE (id INT AUTO_INCREMENT, nom VARCHAR (20), join_year INT, PRIMARY KEY (id), UNIQUE (nom), INDEX (join_year));
Je viens d'ajouter la colonne join_year et de l'indexer. Ce type d'index n'a pas la restriction d'unicité.
Vous pouvez également le nommer KEY au lieu de INDEX.
États CREATE TABLE (id INT AUTO_INCREMENT, nom VARCHAR (20), join_year INT, PRIMARY KEY (id), UNIQUE (nom), KEY (join_year));
L'ajout d'un index réduit les performances des requêtes INSERT et UPDATE. Étant donné que chaque fois que de nouvelles données sont ajoutées à la table, les données d'index sont également mises à jour automatiquement, ce qui nécessite un travail supplémentaire. Les gains de performance sur les requêtes SELECT l'emportent généralement de loin sur ce résultat. Néanmoins, ne vous contentez pas d’ajouter des index sur chaque colonne de la table sans vous préoccuper des requêtes que vous allez exécuter..
Avant d’aller plus loin avec plus de requêtes, je voudrais créer un exemple de table avec quelques données.
Ce sera une liste des États américains, avec leurs dates de participation (la date à laquelle l’État a ratifié la Constitution des États-Unis ou a été admise dans l’Union) et leurs populations actuelles. Vous pouvez copier coller les éléments suivants sur votre console MySQL:
États CREATE TABLE (id INT AUTO_INCREMENT, nom VARCHAR (20), join_year INT, population INT, PRIMARY KEY (id), UNIQUE (nom, KEY (join_year)); INSERT INTO States VALUES (1, 'Alabama', 1819, 4661900), (2, 'Alaska', 1959, 686293), (3, 'Arizona', 1912, 6500180), (4, 'Arkansas', 1836, 2855390 ), (5, "California", 1850, 36756666), (6, "Colorado", 1876, 4939456), (7, "Connecticut", 1788, 3501252), (8, "Delaware", 1787, 873092), (9, "Florida", 1845, 18328340), (10, "Géorgie", 1788, 9685744), (11, "Hawaii", 1959, 1288198), (12, "Idaho", 1890, 1523816), (13 , «Illinois», 1818, 12901563), (14, «Indiana», 1816, 6376792), (15, «Iowa», 1846, 3002555), (16, «Kansas», 1861, 2802134), (17, ' Kentucky ', 1792, 4269245), (18,' Louisiana ', 1812, 4410796), (19,' Maine ', 1820, 1316456), (20,' Maryland ', 1788, 5633597), (21,' Massachusetts ' , 1788, 6497967), (22, "Michigan", 1837, 10003422), (23, "Minnesota", 1858, 5220393), (24, "Mississippi", 1817, 2938618), (25, "Missouri", 1821 , 5911605), (26, 'Montana', 1889, 967440), (27, 'Nebraska', 1867, 1783432), (28, 'Nevada', 1864, 2600167), (29, 'New Hampshire', 1788, 1315809), (30, 'New Jersey', 1787, 8682 661), (31, 'Nouveau Mexique', 1912, 1984356), (32, 'New York', 1788, 19490297), (33, 'Caroline du Nord', 1789, 9222414), (34, 'Dakota du Nord', 1889, 641481), (35, "Ohio", 1803, 11485910), (36, "Oklahoma", 1907, 3642361), (37, "Oregon", 1859, 3790060), (38, "Pennsylvanie", 1787, 12448279), (39, 'Rhode Island', 1790, 1050788), (40, 'South Carolina', 1788, 4479800), (41, 'South Dakota', 1889, 804194), (42, 'Tennessee', 1796 , 6214888), (43, «Texas», 1845, 24326974), (44, «Utah», 1896, 2736424), (45, «Vermont», 1791, 621270), (46, «Virginie», 1788, 7769089 ), (47, 'Washington', 1889, 6549224), (48, 'West Virginia', 1863, 1814468), (49, 'Wisconsin', 1848, 5627967), (50, 'Wyoming', 1890, 532668) ;
La clause GROUP BY regroupe les lignes de données obtenues en groupes. Voici un exemple:
Alors qu'est-ce qui vient de se passer? Nous avons 50 lignes dans la table, mais cette requête a renvoyé 34 résultats. En effet, les résultats ont été regroupés dans la colonne "join_year". En d'autres termes, nous ne voyons qu'une ligne pour chaque valeur distincte de join_year. Étant donné que certains États ont la même jointure_année, nous avons obtenu moins de 50 résultats..
Par exemple, il n'y avait qu'une seule ligne pour l'année 1787, mais il y a 3 états dans ce groupe:
Donc, il y a trois états ici, mais seul le nom de Delaware est apparu après la requête GROUP BY plus tôt. En fait, il aurait pu s'agir de l'un des trois États et nous ne pouvons pas compter sur ces données. Alors quel est l'intérêt d'utiliser la clause GROUP BY?
Il serait pratiquement inutile d'utiliser une fonction d'agrégat telle que COUNT (). Voyons ce que font certaines de ces fonctions et comment elles peuvent nous fournir des données utiles..
C'est peut-être la fonction la plus couramment utilisée avec les requêtes GROUP BY. Il retourne le nombre de lignes dans chaque groupe.
Par exemple, nous pouvons l'utiliser pour voir le nombre d'états pour chaque join_year:
Si vous utilisez une fonction d'agrégation GROUP BY et ne spécifiez pas de clause GROUP BY, l'intégralité des résultats sera placée dans un seul groupe..
Nombre de toutes les lignes dans la table:
Nombre de lignes satisfaisant une clause WHERE:
Ces fonctions renvoient les valeurs minimale, maximale et moyenne:
Cette fonction concatène toutes les valeurs du groupe en une seule chaîne, avec un séparateur donné..
Dans le premier exemple de requête GROUP BY, nous ne pouvions voir qu'un seul nom d'état par an. Vous pouvez utiliser cette fonction pour voir tous les noms dans chaque groupe:
Si l'image redimensionnée est difficile à lire, voici la requête:
SELECT GROUP_CONCAT (nom SEPARATOR ','), join_year FROM états GROUP BY join_year;
Vous pouvez l'utiliser pour additionner les valeurs numériques.
Semblable à d'autres langages de programmation, SQL supporte un peu le flux de contrôle.
C'est une fonction qui prend trois arguments. Le premier argument est la condition, le deuxième argument est utilisé si la condition est vraie et le troisième argument est utilisé si la condition est fausse.
Voici un exemple plus pratique où nous l'utilisons avec la fonction SUM ():
SELECT SUM (IF (population> 5000000, 1, 0)) AS big_states, SUM (IF (population <= 5000000, 1, 0) ) AS small_states FROM states;
Le premier appel SUM () compte le nombre de grands états (plus de 5 millions d’habitants) et le second compte le nombre de petits états. L’appel IF () dans ces appels SUM () renvoie 1 ou 0 en fonction de la condition.
Voici le résultat:
Cela fonctionne de la même manière que les instructions de programmation que vous connaissez peut-être..
Disons que nous voulons catégoriser chaque état dans l'une des trois catégories possibles.
SELECT COUNT (*), CAS WHEN population> 5000000 THEN 'big' WHEN population> 1000000 THEN 'moyen' ELSE 'small' END AS state_size FROM États GROUP GROUP BY by_size;
Comme vous pouvez le constater, nous pouvons regrouper par la valeur renvoyée par l’instruction CASE. Voici ce qui se passe:
La clause HAVING nous permet d'appliquer des conditions aux champs "masqués", tels que les résultats renvoyés par les fonctions d'agrégat. Donc, il est généralement utilisé avec GROUP BY.
Par exemple, regardons la requête que nous avons utilisée pour compter le nombre d'états par année de jointure:
SELECT COUNT (*), join_year À partir des états GROUP BY join_year;
Le résultat était 34 lignes.
Cependant, supposons que nous ne nous intéressions qu'aux lignes dont le nombre est supérieur à 1. Nous ne pouvons pas utiliser la clause WHERE pour cela:
C’est là qu’AVENIR devient utile:
N'oubliez pas que cette fonctionnalité peut ne pas être disponible dans tous les systèmes de base de données..
Il est possible d'obtenir les résultats d'une requête et de les utiliser pour une autre requête..
Dans cet exemple, nous obtiendrons l'état avec la plus forte population:
SELECT * FROM États WHERE population = (SELECT MAX (population) FROM États);
La requête interne renvoie la population la plus élevée de tous les États. Et la requête externe recherchera à nouveau la table en utilisant cette valeur.
Vous pensez peut-être que c'était un mauvais exemple, et je suis un peu d'accord. La même requête pourrait être plus efficacement écrite comme ceci:
SELECT * FROM États ORDER BY population DESC LIMIT 1;
Les résultats dans ce cas sont les mêmes, mais il existe une différence importante entre ces deux types de requêtes. Peut-être qu'un autre exemple démontrera que mieux.
Dans cet exemple, nous aurons les derniers États ayant rejoint l’Union:
SELECT * FROM États WHERE join_year = (SELECT MAX (join_year) FROM États);
Il y a deux lignes dans les résultats cette fois-ci. Si nous avions utilisé ici le type de requête ORDER BY… LIMIT 1, nous n’aurions pas obtenu le même résultat..
Parfois, vous pouvez utiliser plusieurs résultats renvoyés par la requête interne..
La requête suivante trouve les années, lorsque plusieurs États ont rejoint l’Union, et renvoie la liste de ces États:
SELECT * FROM États WHERE join_year IN (SELECT join_year FROM États GROUP BY join_year HAVING COUNT (*)> 1) ORDER BY join_year;
Les sous-requêtes peuvent devenir assez complexes, je ne vais donc pas en parler davantage dans cet article. Si vous souhaitez en savoir plus à leur sujet, consultez le manuel de MySQL.
De plus, il convient de noter que les sous-requêtes peuvent parfois avoir de mauvaises performances, elles doivent donc être utilisées avec prudence..
Avec une requête UNION, nous pouvons combiner les résultats de plusieurs requêtes SELECT.
Cet exemple combine des états commençant par la lettre "N" et des états très peuplés:
(SELECT * FROM États WHERE nom LIKE 'n%') UNION (SELECT * FROM États WHERE population> 10000000);
Notez que New York est à la fois grande et que son nom commence par la lettre «N». Mais il ne s'affiche qu'une seule fois car les lignes en double sont automatiquement supprimées des résultats..
Un autre avantage de UNION est que vous pouvez combiner des requêtes sur différentes tables..
Supposons que nous ayons des tables pour les employés, les gestionnaires et les clients. Et chaque table a un champ e-mail. Si nous voulons récupérer tous les courriels avec une seule requête, nous pouvons exécuter ceci:
(SELECT e-mail DES employés) UNION (SELECT e-mail DES gestionnaires) UNION (SELECT e-mail DES clients WHERE abonné = 1);
Il irait chercher tous les emails de tous les employés et gestionnaires, mais seulement les emails des clients qui se sont abonnés pour recevoir des emails.
Nous avons déjà parlé de la requête INSERT dans le dernier article. Maintenant que nous avons exploré les index de base de données aujourd’hui, nous pouvons parler de fonctionnalités plus avancées de la requête INSERT..
C'est presque comme une déclaration conditionnelle. La requête essaie d’abord d’exécuter un INSERT donné et, si elle échoue en raison d’une valeur dupliquée pour une clé PRIMARY KEY ou UNIQUE, elle effectue une mise à jour à la place..
Créons d'abord une table de test.
C'est une table pour contenir des produits. La colonne 'stock' représente le nombre de produits en stock.
Maintenant, essayez d'insérer une valeur en double et voir ce qui se passe.
Nous avons eu une erreur comme prévu.
Disons que nous avons reçu un nouveau fabricant de pain et que nous souhaitons mettre à jour la base de données sans savoir s’il existe déjà un enregistrement. Nous pourrions vérifier les enregistrements existants, puis faire une autre requête en fonction de cela. Ou nous pourrions simplement tout faire en une seule requête:
Cela fonctionne exactement comme INSERT avec une exception importante. Si une ligne en double est trouvée, elle est d'abord supprimée puis exécutée par INSERT. Nous n'obtenons donc aucun message d'erreur..
Notez que puisqu'il s'agit en fait d'une toute nouvelle ligne, l'id a été incrémenté..
C'est un moyen de supprimer les erreurs de duplication, généralement pour empêcher la rupture de l'application. Parfois, vous voudrez peut-être essayer d’insérer une nouvelle ligne et de la laisser échouer sans se plaindre au cas où un doublon serait trouvé..
Aucune erreur renvoyée et aucune ligne n'a été mise à jour.
Chaque colonne de table doit avoir un type de données. Jusqu'ici, nous avons utilisé les types INT, VARCHAR et DATE, mais nous n'en avons pas parlé en détail. Il y a aussi plusieurs autres types de données que nous devrions explorer..
Commençons par les types de données numériques. J'aime les classer dans deux groupes distincts: les entiers et les non entiers..
Une colonne entière ne peut contenir que des nombres naturels (pas de décimales). Par défaut, ils peuvent être des nombres négatifs ou positifs. Mais si l'option UNSIGNED est définie, elle ne peut contenir que des nombres positifs.
MySQL supporte 5 types d'entiers, de tailles et de gammes différentes:
Ces types de données peuvent contenir des nombres décimaux: FLOAT, DOUBLE et DECIMAL..
FLOAT est de 4 octets, DOUBLE de 8 octets et fonctionne de la même manière. Cependant DOUBLE a une meilleure précision.
DECIMAL (M, N) a une taille variable en fonction du niveau de précision, qui peut être personnalisé. M est le nombre maximum de chiffres et N est le nombre de chiffres à droite du point décimal..
Par exemple, DECIMAL (13,4) a un maximum de 9 chiffres entiers et 4 chiffres fractionnaires.
Comme son nom l'indique, nous pouvons stocker des chaînes dans ces colonnes de type de données.
CHAR (N) peut contenir jusqu'à N caractères et a une taille fixe. Par exemple, CHAR (50) prendra toujours 50 caractères d’espace, par ligne, quelle que soit la taille de la chaîne qu’elle contient. Le maximum absolu est de 255 caractères
VARCHAR (N) fonctionne de la même manière, mais la taille de stockage n’est pas fixe. N n'est utilisé que pour la taille maximale. Si une chaîne plus courte que N caractères est stockée, cela prendra beaucoup moins d’espace sur le disque dur. La taille maximale absolue est 65535 caractères.
Les variantes du type de données TEXT conviennent mieux aux chaînes longues. TEXT est limité à 65 535 caractères, MEDIUMTEXT 16,7 millions de caractères et LONGTEXT 4,3 milliards de caractères. MySQL les stocke généralement sur des emplacements distincts sur le serveur, de sorte que la mémoire principale de la table reste relativement petite et rapide..
DATE stocke les dates et les affiche dans ce format 'AAAA-MM-JJ' mais ne contient pas les informations de temps. Il a une gamme de 1001-01-01 à 9999-12-31.
DATETIME contient à la fois la date et l'heure, et est affiché dans ce format "AAAA-MM-JJ HH: MM: SS". Il a une plage allant de '1000-01-01 00:00:00' à '9999-12-31 23:59:59'. Il faut 8 octets d'espace.
TIMESTAMP fonctionne comme DATETIME à quelques exceptions près. Il ne prend que 4 octets d’espace et la plage est «1970-01-01 00:00:01» UTC à «2038-01-19 03:14:07» UTC. Ainsi, par exemple, il peut ne pas être bon pour stocker les dates de naissance.
TIME ne stocke que le temps et YEAR ne stocke que l'année.
MySQL prend en charge d’autres types de données. Vous pouvez en voir une liste ici. Vous devriez également vérifier les tailles de stockage de chaque type de données ici.
Merci d'avoir lu l'article. SQL est un langage important et un outil dans l'arsenal des développeurs Web..
S'il vous plaît laissez vos commentaires et questions, et passez une bonne journée!
Êtes-vous prêt à faire évoluer vos compétences et à commencer à tirer profit de vos scripts et de vos composants? Découvrez notre marché jumeau, CodeCanyon.
Accentsconagua