Nous analysons les bases de données et le langage SQL. (Partie 3) - "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.

• Vérification des devoirs
  • Ajout d'une clé primaire
  • Ajouter la Moldavie
  • Créer le tableau des villes
  • Ajouter une clé étrangère aux villes
  • Partie bonus. Essai
• Instruction SELECT
  • OÙ paramètre
  • Paramètre ORDER BY
  • Paramètre GROUPE PAR
• Devoirs
• Conclusion

Bonjour, mesdames et messieurs, continuons à parler de bases de données, de SQL et d'autres choses. Le matériel d'aujourd'hui contiendra une partie de théorie et une partie de pratique. Permettez-moi de vous rappeler que la dernière fois, nous avons expliqué comment tout configurer, comment créer une base de données, une table et en extraire des données. Il est temps de voir si quelque chose a fonctionné avec la télédétection. À mon avis, la moitié aurait pu être réalisée uniquement sur la base de l’article précédent. Il s'est avéré que pour assembler correctement une application et tout rendre plus ou moins beau, il faut parler de bases de données, et pour en parler, il faut passer beaucoup de temps.

Vérification des devoirs

Un immense respect à tous ceux qui ont accompli avec succès les tâches. Cela signifie que vous comprenez que vous seul en avez besoin et que cela ne fait que vous aider. Pour ceux qui ont négligé ma tâche, permettez-moi de vous rappeler la condition :

1. Vous devez ajouter une clé primaire (PRIMARY KEY) du champ ID au schéma de la table country.
2. Ajoutez un autre pays au tableau des pays - Moldavie.
3. Selon le schéma de l'article précédent, créez une table city, qui contiendra tous les champs décrits. Les noms des champs seront les suivants : id, name, country_id, population.
4. Ajoutez une clé primaire à la table city.
5. Ajoutez une clé étrangère à la table city.

Pour commencer, utilisons la première partie de l'article précédent et allons sur le terminal de la base de données.

Ajout d'une clé primaire

Vous pouvez ajouter une clé primaire (PRIMARY KEY) de deux manières : immédiatement lors de la création d'une table, ou après la création, à l'aide de ALTER TABLE.

Clé primaire lors de la création de la table

Puisque nous avons déjà créé une table, et sans la supprimer nous ne pourrons pas montrer cette approche au sein de cette base de données, nous allons simplement créer une base de données de test temporaire dans laquelle nous ferons tout. Entrons les commandes suivantes :

• créer une nouvelle base de données :

  $CRÉER UN TEST DE BASE DE DONNÉES ;

• créez une table en ajoutant une clé primaire :

  $ CREATE TABLE country(id INT, nom VARCHAR(30), PRIMARY KEY (id));

En général, rien de compliqué. Après avoir déclaré les variables, la partie suivante PRIMARY KEY (id) est ajoutée , où le nom du champ qui sera la clé primaire est passé entre parenthèses. Et voyons comment le schéma de la table a changé : $ DESC country ; Comme vous pouvez le constater, la valeur PRI est apparue dans le champ Clé de l'enregistrement id .

Clé primaire après la création de la table

Comme je l'ai dit plus tôt, la première clé après la création d'une table peut être attribuée en utilisant ALTER TABLE . Nous allons exécuter cet exemple dans notre base de données de villes :

• Passons à notre base de données à partir de celle de test :

  $USE villes ;

• Vérifions que nous sommes bien dans notre base de données (il devrait y avoir un autre champ - population). Pour ce faire, nous écrivons :

  $DESCpopulation ;

tout est correct, la table est à nous. Écrivons ce qui suit :

$ ALTER TABLE pays ADD PRIMARY KEY (id);

• et vérifiez-le immédiatement avec la commande :

  pays $DESC ;

Comme vous pouvez le voir sur la photo, tout est correct, la valeur PRI est exactement là où elle devrait être. À propos, nous avons travaillé avec une base de données de test. Il faut maintenant le supprimer : pourquoi devons-nous encombrer le serveur, non ? Pour ce faire, nous utilisons une commande assez connue : $ DROP DATABASE test ;

Ajouter la Moldavie

Nous devons d’abord décider de ce que nous allons enregistrer. Notre prochain identifiant sera 4. Le nom sera Moldavie et sa population est de 3550900. Par conséquent, nous exécutons la commande INSERT INTO que nous connaissons déjà : $ INSERT INTO country VALUES (4, 'Moldova', 3550900) ; Et on vérifie si cette valeur est exactement dans la base de données : $ SELECT * FROM country WHERE id = 4; Dans la demande de données, j'ai immédiatement déterminé quel champ serait recherché, nous n'avons donc obtenu qu'un seul enregistrement, ce dont nous avions besoin.

Créer le tableau des villes

En utilisant le diagramme du premier article sur la base de données, nous obtenons les informations nécessaires sur la table. Il contiendra les champs suivants :

• id - identifiant unique ;
• nom — nom de la ville ;
• country_id — clé étrangère du pays ;
• population — population de la ville.

C’est un peu stressant d’écrire une pièce d’identité unique à chaque fois, vous ne trouvez pas ? Je voudrais laisser cela aux autorités MySQL . Et il existe un tel moyen - AUTO INCREMENT . Nous devons l'ajouter au champ numérique, et si nous ne transmettons pas explicitement les valeurs, MySQL lui-même augmentera l'ID de un par rapport au précédent. Par conséquent, la création d'une table ressemblera à ceci : $ CREATE TABLE ville ( id INT AUTO_INCREMENT, nom VARCHAR(30), country_id INT, population INT, PRIMARY KEY (id)); Regardons le diagramme du tableau pour voir si tout a été fait correctement : $ DESC city; Comme vous pouvez le voir sur le diagramme du tableau, nous avons une nouvelle description pour le champ id - auto_increment. Nous avons donc tout fait correctement. Vérifions les données sur une table entièrement configurée. Pour ce faire, nous effectuerons la dernière partie de la tâche : la clé étrangère.

Ajouter une clé étrangère aux villes

Pour une clé étrangère il y aura cette commande : $ ALTER TABLE city ADD FOREIGN KEY (country_id) REFERENCES country(id); Et vérifions immédiatement ce qui ne va pas avec le schéma de la table : a-t-il changé en une heure ? $DESC ville ;

Partie bonus. Essai

J'ai oublié de l'ajouter à la tâche - remplissez les données qui figuraient dans la capture d'écran de la première partie. J'ai oublié, alors maintenant je vais le faire moi-même. Et pour ceux qui sont intéressés, vous pouvez le faire vous-même sans moi et ensuite nous vérifierons ;) Il y avait Kharkov, Kiev, Minsk, Odessa, Voronej, et nous ajouterons aussi Chisinau. Mais cette fois, nous ne transmettrons pas les identifiants, nous les ignorerons : $ INSERT INTO city (name, country_id, population) VALUES ('Kharkov', 1, 1443000), ('Kyiv', 1, 3703100), ('Minsk' , 3, 2545500), ('Odessa', 1, 1017699), ('Voronej', 2, 1058261), ('Kishinev', 4, 695400); Comme vous pouvez le constater, vous pouvez effectuer plusieurs entrées simultanément à l'aide d'une seule commande INSERT INTO. Une chose pratique, rappelez-vous) Et voyons immédiatement ce qu'il y a dans le tableau : $ SELECT * FROM city ; AUTO_INCREMENT - a fonctionné exactement comme nous le souhaitions. Les dossiers d’identification sont tous remplis, même si nous ne les avons pas soumis. Une clé étrangère est une chose dépendante. Pour vérifier si cela fonctionne correctement, vous pouvez essayer d'écrire une clé étrangère qui n'existe pas dans la table étrangère. Disons que nous décidons que id = 5 correspond au Kazakhstan. Mais en réalité, ce n’est pas dans le tableau des pays. Et pour vérifier que la base de données jure, ajoutez la ville - Astana : $ INSERT INTO city (name, country_id, population) VALUES ('Astana', 5, 1136156) ; Et nous obtenons naturellement l'erreur : désormais, la clé étrangère garantit que nous n'essayons pas d'attribuer à la ville un pays qui ne figure pas dans notre base de données. Cette partie des devoirs peut être considérée comme terminée - passez à la nouvelle :)

Instruction SELECT

Eh bien, tout ne semble plus si effrayant, n'est-ce pas ? Je tiens à souligner encore une fois que pour les développeurs Java, la connaissance de la base de données est indispensable. Sans base de données, vous ne pouvez aller nulle part. Oui, je souhaite déjà commencer à rédiger une candidature, je suis d'accord. Mais c'est nécessaire. Nous allons donc continuer ainsi. À l'aide de l'instruction SELECT, nous récupérons les données de la base de données. Autrement dit, il s'agit d'une opération DML typique (avez-vous déjà oublié ce que c'est ?...))) Relisez les articles AVANT). Quels sont les avantages des bases de données relationnelles ? Ils disposent d’excellentes fonctionnalités pour agréger et récupérer des données. C'est à cela que sert l'instruction SELECT. Il semblerait qu’il n’y ait rien de compliqué là-dedans, n’est-ce pas ? Mais il s’avère qu’il reste encore beaucoup à comprendre. Il est important pour nous de comprendre les bases sur lesquelles nous pouvons nous appuyer. La requête la plus simple avec une instruction SELECT consiste à sélectionner toutes les données d'une table. J'ai vraiment aimé la description du wiki sur l'ordre exact dans lequel les opérateurs doivent passer dans une requête SELECT, je vais donc la copier effrontément ici :

SELECT
  [DISTINCT | DISTINCTROW | ALL]
  select_expression,...
FROM table_references
[WHERE where_definition]
[GROUP BY {unsigned_integer | col_name | formula}]
[HAVING where_definition]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC], ...]

Ici, vous pouvez voir que vous ne pouvez pas placer l'opérateur GROUP BY en premier, puis l'opérateur WHERE. Il faut s'en souvenir afin que plus tard il n'y ait pas de ressentiment pour des erreurs dont on ne sait pas d'où elles viennent. $SELECT * FROM ville ; Mais supprimer toutes les données n’est clairement pas amusant pour nous. C'est exactement la même chose si l'on voulait enfoncer des clous avec un microscope [1] , [2] . Étant donné que la base de données effectue les opérations de filtrage, de tri et d'agrégation beaucoup plus rapidement que le code Java, il est préférable de laisser cette question à la base de données. Par conséquent, en compliquant les tâches, nous ouvrirons de nouvelles fonctionnalités.

OÙ paramètre

Pour filtrer une sélection, le mot WHERE est utilisé . Cela doit être interprété comme suit : SELECT * FROM tablename (sélectionne tous les champs de la table tablename) WHERE talbe_row = 1 (où dans les enregistrements le champ table_row est égal à 1). Il est important de noter que l’ordre des mots-clés dans la requête est important. Vous ne pouvez pas écrire WHERE a =1 FROM table_name SELECT *. Pour la langue russe, cela est acceptable, et pour certains, cela ne semble pas si grave, mais pour SQL, c'est inacceptable. Nous écrivons la requête suivante : $ SELECT * FROM city WHERE country_id = 1; Et nous avons choisi des villes ukrainiennes. Pas mal, non ? Et si nous voulions non seulement l’ukrainien, mais aussi le biélorusse ? A cet effet, nous pouvons lister l'ensemble des valeurs que le champ peut prendre : $SELECT * FROM city WHERE country_id IN(1, 3); Et nous avons déjà des villes de deux pays qui répondent. Que se passe-t-il s’il existe plusieurs conditions à filtrer ? Disons que nous voulons des villes de plus de deux millions d'habitants ? Pour ce faire, utilisez les mots OR et AND : $ SELECT * FROM city WHERE country_id IN (1, 3) AND population > 2000000; Génial, mais que se passe-t-il si nous devons ajouter une condition supplémentaire - rechercher des noms via une expression régulière (je ne décrirai pas les expressions régulières ici : voici une personne qui a fait cela « brièvement » en 4 parties ) ? Par exemple, on se souvient comment épeler une ville, mais pas complètement... Pour ce faire, vous pouvez ajouter le mot-clé LIKE à l'expression de filtrage : $ SELECT * FROM city WHERE country_id IN (1, 3) AND population > 2000000 OR nom COMME « %hark% » ; Et de cette façon, nous avons également obtenu Kharkov. En conséquence, nous pouvons dire que notre recherche s'est avérée très bonne. Mais j'aimerais trier non pas par pièce d'identité, mais par population, mais comment ? Oui, très simple...

Paramètre ORDER BY

En utilisant ORDER BY, nous pouvons trier les enregistrements que nous avons reçus selon un champ spécifique. Il trie à la fois les nombres et les chaînes. Développons la requête précédente, trions par population, en ajoutant ORDER BY population : $ SELECT * FROM city WHERE country_id IN (1, 3) AND population > 2000000 OR name LIKE « %hark% » ORDER BY population ; Comme nous pouvons le constater, le tri s’est effectué dans l’ordre naturel, c’est-à-dire par ordre croissant. Et si nous voulions le contraire ? Pour ce faire, vous devez ajouter le mot DESC : $ SELECT * FROM city WHERE country_id IN (1, 3) AND population > 2000000 OR name LIKE « %hark% » ORDER BY population DESC ; Désormais, le tri est basé sur la réduction de la population. Et la base de données le fait très rapidement : aucun Collections.sort ne peut y être comparé. Trions maintenant par ligne, par nom dans l'ordre inverse : $ SELECT * FROM city WHERE country_id IN (1, 3) AND population > 2000000 OR name LIKE “%hark%” ORDER BY name DESC;

Paramètre GROUPE PAR

Utilisé pour regrouper les enregistrements par champs spécifiques. Ceci est généralement nécessaire pour utiliser fonctions d'agrégation... Que sont les fonctions d'agrégation ?)) Il est logique de regrouper selon certains champs s'ils sont les mêmes pour différents enregistrements. Voyons ce que cela signifie à l'aide de notre exemple. Disons que les villes ont des clés étrangères – des identifiants de pays. Ainsi, l’identifiant est le même pour les villes d’un même pays. Par conséquent, vous pouvez prendre et regrouper les enregistrements par eux : $ SELECT country_id, COUNT(*) FROM city GROUP BY country_id ; Mais sans fonctions d’agrégation, cela semble un peu terne, il faut l’admettre. Par conséquent, examinons quelques-unes des fonctions les plus courantes :

• COUNT — nombre d'enregistrements, peut être utilisé sans regroupement, utilisé comme COUNT(*) . Dans le cas d'un regroupement par champ - COUNT(groupped_field);
• MAX - trouve la valeur maximale pour un champ spécifique ;
• MIN - trouve la valeur minimale pour un champ spécifique ;
• SOMME - trouve la somme pour un champ spécifique ;
• AVG - trouve la valeur moyenne.

En général, ces fonctions peuvent être utilisées sans regroupement, alors seulement un seul champ sera affiché. Essayons-les pour la population de notre ville : $ SELECT COUNT(*) FROM city ; Ce qu’ils ont demandé, c’est ce qu’ils ont obtenu. Juste le nombre d'enregistrements. Parfois, cela est utile. Par exemple, si nous avons besoin de connaître le nombre d’articles d’un certain auteur. Il n’est pas nécessaire de les extraire de la base de données et de les compter. Vous pouvez simplement utiliser COUNT(). $ SELECT AVG(population) FROM ville ; $ SELECT MIN(population) FROM ville ; Et c’est là que le regroupement entre en vigueur. Par exemple, la tâche consiste à obtenir la plus petite ville du pays. Vous savez déjà comment faire cela ? Essayez-le vous-même, puis regardez : $ SELECT country_id as Country, MIN(population) FROM city WHERE GROUP BY country_id ; Jusqu'à présent, nous ne voyons que la carte d'identité du pays, mais cela n'a pas d'importance : la prochaine fois, nous ferons tout. Et donc il y a déjà un résultat, et nous avons obtenu ce que nous voulions - la plus petite ville du pays avec ID = 1. Le reste des fonctions sera le même. Il est important de noter que ratisser tous les champs via * lors de l'utilisation du regroupement et de l'agrégation ne fonctionnera pas ! Pensez-y ;)

Devoirs

Sur la base des résultats des articles précédents, il est clair que les devoirs sont faits, alors continuons)) Oui, tous ceux qui font les devoirs continueront à mettre « + » dans les commentaires. Il est important pour moi que le sujet des devoirs vous intéresse, afin que je continue à le faire à l'avenir. Oui, je lis régulièrement vos commentaires. Bien sûr, je réponds moins souvent. J'ai vu qu'ils demandaient de poser des problèmes SQL plus difficiles. Jusqu'à ce que nous apprenions les jointures, il n'y aura pas de problèmes intéressants, il y aura donc ceux dont j'aurai besoin pour plus de matériel.

Tâches:

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 : de cette façon, je pourrai également le vérifier si j'ai le temps.
1. 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 avec le travail 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.
2. Écrivez des demandes à recevoir 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.

Conclusion

Aujourd'hui, nous avons discuté en détail des devoirs de la dernière leçon. De plus, je considère que cela est important tant pour ceux qui ne l’ont pas fait que pour ceux qui l’ont fait. Pour les premiers, c’est l’occasion de connaître la réponse, et pour les seconds, de la comparer avec votre résultat. Abonnez-vous à mon compte GitHub pour rester informé des modifications apportées au projet. J'y conserverai l'intégralité de la base de code. Tout se passera dans cette organisation . Ensuite, nous avons discuté de l'instruction SELECT. Il est le plus important pour nous. C’est par lui que transiteront toutes les demandes de données, et il faut le comprendre. Le plus important est de se souvenir de l'ordre dans lequel les paramètres sont ajoutés (WHERE, ORDER BY, GROUP BY, etc.). Oui, je n’ai pas dit tout ce qui était possible, mais je ne me suis pas fixé un tel objectif. Oui, je sais que vous avez déjà hâte de rédiger une candidature. Soyez patient, c'est tout ce dont vous avez besoin. Tant pour le projet que pour votre évolution professionnelle. Pendant que vous attendez, assurez-vous que Git vous est déjà familier. Je l'utiliserai par défaut, comme un outil bien connu. Merci à tous d'avoir lu. Dans le prochain article, nous parlerons des connexions et des jointures aux bases de données. C'est là que seront les tâches sympas))

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