Nous analysons les bases de données et le langage SQL. (Partie 4 - vérification des devoirs) - "Projet Java de A à Z"

Un article d'une série sur la création d'un projet Java (les liens vers d'autres documents se trouvent à la fin). Son objectif est d'analyser les technologies clés, le résultat est d'écrire un robot télégramme. Articles précédents et analyse des devoirs sur les bases de données : 1 , 2 , 3 . À tous ceux qui ont eu la patience et l’endurance, à tous ceux qui ont parcouru ce quatrième article avec moi, bravo. Comme on dit, celui qui marche peut maîtriser la route. Cette semaine sera publié le dernier article sur les bases de données, dans lequel nous parlerons des types de relations et de jointures . Mais avant d’aborder les nouvelles informations, vérifions nos devoirs… Je me sentais vraiment comme un professeur. Ne m’en voulez pas : je n’ai pas de formation pédagogique, c’est sûr. Étant donné que la semaine dernière, une vérification détaillée de la télécommande occupait la part du lion du matériel, j'ai décidé de diviser l'analyse des devoirs et la révision du nouveau matériel en deux parties.

En fait, l'analyse des devoirs

Je suis vraiment heureux qu'il y ait des gens qui font du PP et en parlent. C'est cool! Je suis aussi sûr que possible que le simple fait de lire sans consolider les connaissances ne mène à nulle part. Par conséquent, tous ceux qui ont fait ou essayé de faire sont respectés. Je vous rappelle les conditions des tâches :

1. Comprenez l' opérateur HAVING et écrivez un exemple de requête pour les tables de notre exemple. Si vous devez ajouter des champs ou plusieurs valeurs pour que ce soit plus clair, ajoutez-les. Si quelqu'un le souhaite, écrivez votre exemple de solution dans les commentaires - afin que je puisse également le vérifier si j'ai le temps.
2. Installez MySQL Workbench pour travailler avec la base de données via l'interface utilisateur. Je pense que nous avons déjà suffisamment de pratique pour travailler depuis la console. Connectez-vous à la base de données. Si vous utilisez autre chose pour travailler avec la base de données, n'hésitez pas à ignorer cette tâche. Ici et plus loin, j'utiliserai uniquement MySQL Workbench.
3. Rédiger les demandes de réception en utilisant nos données :
   1. le pays le plus petit/le plus peuplé ;
   2. nombre moyen d'habitants du pays ;
   3. nombre moyen d'habitants dans les pays dont le nom se termine par « a » ;
   4. le nombre de pays comptant plus de quatre millions d’habitants ;
   5. trier les pays par nombre décroissant d'habitants ;
   6. trier les pays par nom dans l'ordre naturel.

Parlons d'AVOIR

Connaître l'opérateur Avoir peut vous aider à réussir plus d'un entretien où il y aura des problèmes SQL. Il est donc extrêmement important de le comprendre. Il se trouve que vous ne pouvez pas utiliser de conditions pour agréger les fonctions (SUM, MIN, MAX, AVG). De plus, HAVING est utilisé pour les champs regroupés. Qu'est-ce que ça veut dire? Par exemple, si l'on veut obtenir des pays où le nombre moyen d'habitants dans les villes est supérieur à 50 000 habitants, on ne peut pas se passer d'utiliser HAVING. Si je comprends bien, cela est fait parce que l'agrégation se produit après l'exécution de l'instruction WHERE et il est impossible d'y ajouter des valeurs d'agrégation qui seront calculées ultérieurement. Même si jusqu’à présent mes opinions n’apportent pas grand-chose à la compréhension, vous pouvez simplement accepter cela comme un fait et vous en tenir à cela. En programmation, il arrive souvent que si quelque chose n’est pas clair à un moment donné, cela peut très bien signifier que le cerveau ne l’a tout simplement pas encore digéré. Dormez avec cette pensée, et le lendemain tout deviendra plus clair.

Installation de MySQL Workbench

Ici et plus loin, j'utiliserai Workbench pour les requêtes. Je vais vous montrer ce dont vous avez besoin pour installer et créer une connexion à la base de données. Il s'agit d'un produit Oracle, il vous suffit donc d'aller sur leur site Web et de sélectionner la version et le système d'exploitation souhaités. Pour ce faire, suivez ce lien : Ici vous pouvez sélectionner exactement le système d'exploitation dont vous avez besoin. Cliquez sur Télécharger , mais au lieu de télécharger, nous verrons cette fenêtre : Ne vous perdez pas, cherchez simplement le bouton appelé Non merci, démarrez simplement mon téléchargement , et le téléchargement commencera. Pourquoi font-ils ça ? Probablement, pour que plus de personnes s'inscrivent auprès d'eux, cela n'est pas important pour nous. Une fois le téléchargement réussi, exécutez le fichier d'installation. Sous MacOS, cela ressemble à ceci : Il suffit de glisser l’icône et le tour est joué, l’installation est terminée. Pas aussi difficile que d’installer MySQL lui-même, n’est-ce pas ? Ou bien nous nous y sommes simplement habitués et sommes devenus plus expérimentés ;) La deuxième partie de cette tâche consiste à établir une connexion à notre base de données. Que faut-il pour cela ? Cliquez sur le signe plus à côté de Connexions MySQL : Dans la fenêtre qui apparaît, saisissez les données nécessaires :

• Nom de la connexion - le nom de notre connexion. Écrivez des noms aussi clairs que possible afin qu'il n'y ait aucun problème d'identification plus tard. Je donne à cette connexion le nom JRTB_DB ;
• Nom d'hôte - sera déjà défini comme local 127.0.0.1 (alias localhost). Dans notre cas, rien ne doit être modifié, puisque la base de données est installée sur l'ordinateur, mais si la base de données se trouve ailleurs, alors l'hôte (l'adresse IP de la machine sur laquelle la base de données s'exécute) doit être modifié en conséquence ;
• Nom d'utilisateur - vous pouvez également spécifier votre utilisateur si nécessaire. Si vous n'avez rien ajouté à cette clé, laissez-la inchangée ;
• Mot de passe - cliquez sur Stocker dans le trousseau et définissez exactement le mot de passe que vous avez défini vous-même. J'ai tout laissé simple - root .

Pour vérifier si la connexion fonctionnera, cliquez sur Test de connexion : Eh bien, si tout a été fait correctement, le résultat ne se fera pas attendre : nous avons maintenant une connexion enregistrée dans la base de données, et nous n'aurons pas besoin de créer une connexion et de remplir dans le nom et le mot de passe à chaque fois. Et ce bonheur ressemblera à ceci : Nous entrons dans la connexion nouvellement créée et voyons la fenêtre de demande. Pour être sûr que tout est correct, vérifions la liste des bases de données, allons dans la nôtre et récupérons toutes les données sur les villes : Et ici, nous avons carte blanche pour ce dont nous avons besoin. La première section est responsable du script que nous entrons. Ensuite, dans la grille de résultats , nous voyons le résultat de la dernière opération du script. Et Action Output affiche une liste d’opérations et son résultat. Une chose très utile, je tiens à vous le dire : avec son aide, vous pouvez surveiller la vitesse d'exécution de certains scripts. Pourquoi c'est important? L'un des problèmes les plus courants liés à la vitesse d'exécution des tâches dans une application est la vitesse d'exécution des requêtes dans la base de données. Ici, vous pouvez les vérifier rapidement et facilement à la main.

Nous rédigeons les demandes nécessaires

Nous n'avons que 7 demandes à compléter, c'est parti !

1. Obtenez le pays le plus peuplé. Ici, vous pouvez y aller astucieusement et emprunter plusieurs itinéraires :

• Selon le tableau des pays

Il nous suffit ensuite de trier notre requête par population et de ne prendre qu'un seul enregistrement. Pour ce faire, vous devez ajouter un opérateur LIMIT à la fin du script et spécifier la quantité requise : $ SELECT * FROM country ORDER BY population DESC LIMIT 1;

• D'après le tableau de la ville

Tout est ici plus intéressant, car la demande sera plus complexe, mais aussi plus intéressante. Puisque nous n'avons toujours aucune idée des jointures, nous ne pouvons obtenir que l'ID du pays : $ SELECT country_id, SUM(population) FROM city GROUP BY country_id ORDER BY SUM(population) DESC LIMIT 1; Ici, nous avons fait une chose sympa : nous avons collecté la somme de la population de toutes les villes connues de chaque pays, triée par cette somme et pris le premier élément. Eh bien, c'est génial ? Je suis ravi :D Après cela, vous vous sentez immédiatement comme un gourou des requêtes... (pas pour longtemps, bien sûr))

2. Obtenez le plus petit pays. Ici, vous pouvez y aller astucieusement et emprunter plusieurs itinéraires

Dans ce cas, tout sera exactement pareil. La seule différence est que le tri sera inversé, c’est tout. Par conséquent, j'écris simplement des demandes :

• D'après le tableau de la ville

$ SELECT country_id, SUM(population) FROM city GROUP BY country_id ORDER BY SUM(population) LIMIT 1;

• Selon le tableau des pays

$ SELECT * FROM country ORDER BY population LIMIT 1; Et voyez le résultat par vous-même !

3. Nombre moyen d'habitants du pays

Là encore, la spécification technique n'est pas exacte, comme si le responsable l'avait écrite... Pourquoi ai-je décidé cela ? Parce qu'on ne sait pas dans quelle table travailler. Mais c'est normal : il n'y a tout simplement pas de tâches dans lesquelles tout sera immédiatement clair et compréhensible. Par conséquent, vous devez lire attentivement les tâches, et si vous avez des questions, posez-les tout de suite ! C'est vrai, une remarque. En tenant compte des données dont nous disposons dans la base de données, nous effectuerons une recherche en utilisant les données des villes. Pour ce faire, écrivez la requête suivante : $ SELECT country_id, AVG(population) FROM city GROUP BY country_id; C'est simple ici : nous utilisons la fonction AVG et regroupons nos fiches de villes par pays.

4. Nombre moyen d'habitants dans les pays dont le nom se termine par « a »

Il y aura un léger changement ici sur demande. Nous devons ajouter un filtrage par noms avant de procéder au regroupement. Je fais mes devoirs, comme tous les étudiants, avant de publier cet article, et je comprends que ce problème ne peut être résolu sans jointures. Pourquoi? Car en plus de la carte d’identité du pays, nous avons également besoin d’obtenir son nom. Et cela ne peut être fait sans joindre deux tables en un seul enregistrement. Par conséquent, je ferai cette tâche, bien sûr, mais c'est mon montant...))) Je voulais proposer une tâche en utilisant LIKE dans la requête...) $ SELECT ci.country_id, AVG(ci.population ) FROM city ci INNER JOIN country co ON ci.country_id = co.id WHERE co.name LIKE "%a" GROUP BY country_id; Que s'est-il passé ici? Tout d’abord, nous avons joint les enregistrements des tables city et country à l’aide de la clé étrangère country_id, filtrés par noms de pays afin qu’ils se terminent par « a », puis regroupés par country_id.

5. Nombre de pays ayant une population supérieure à quatre millions

Ici, il suffit d'utiliser la fonction COUNT et d'ajouter un filtrage sur la population : $ SELECT COUNT(*) from country WHERE population > 4000000 ; En conséquence, nous découvrons qu’il existe 3 pays de ce type. Est-ce exact ? Oui, seule la Moldavie ne franchit pas cette étape.

6. Trier les pays par nombre décroissant d'habitants

Pour ce faire, nous devons utiliser l’opérateur ORDER BY que nous connaissons déjà. Mais gardez à l’esprit que par défaut le tri se fait dans l’ordre naturel. Pour les nombres, cela signifie qu'ils sont triés par ordre croissant, pour les chaînes, cela signifie qu'ils sont triés à partir des premiers caractères. Si nous avons besoin d'un tri décroissant, nous avons besoin de l'inverse du tri naturel : $ SELECT * FROM country ORDER BY population DESC;

7. Trier les pays par nom dans l'ordre naturel

C’est là que la connaissance de l’ordre naturel s’avérera utile. Comme c'est la valeur par défaut, c'est un jeu d'enfant pour nous : $ SELECT * FROM country ORDER BY name;

Au lieu de la sortie

Il se trouve que la taille de la solution des devoirs sera très grande, nous ferons donc une exception : je publie cet article avec un chèque, et vendredi je publierai du nouveau matériel avec des connexions et des jointures. Merci à tous d'avoir lu. Jusqu'à vendredi!

Une liste de tous les matériaux de la série se trouve au début de cet article.