L'icône rouge permet de télécharger chaque page du wiki visitée au format PDF et la grise au format ODT →

Différences

Ci-dessous, les différences entre deux révisions de la page.

--- utilisateurs:hypathie:tutos:mysql-utiliser-l-objet-adresse-villes_departements_regions_de_france [18/04/2016 10:38]
Hypathie [Insertions dans les tables]
+++ utilisateurs:hypathie:tutos:mysql-utiliser-l-objet-adresse-villes_departements_regions_de_france [02/12/2020 14:50] (Version actuelle)
Hypathie [mysql : Utiliser l'objet Adresse : villes_départements_régions_de_france]
@@ Ligne 5: / Ligne 5: @@
   * Commentaires : Utiliser les contraintes d'intégrété pour attribuer une localité à un objet "Adresse"
   * Débutant, à savoir : [[:doc:systeme:commandes:le_debianiste_qui_papillonne|Utiliser GNU/Linux en ligne de commande, tout commence là !.]] :-)
+  * Prérequis : [[utilisateurs:hypathie:tutos:installer_sql-mariadb|Installer mysql/mariadb]]
   * Référence : http://www.manuelphp.com/mysql/tutorial.php
@@ Ligne 55: / Ligne 56: @@
 ===MLD de la BDD===
-{{http://pix.toile-libre.org/upload/original/1460440240.png}}
+{{http://pix.toile-libre.org/upload/original/1461051633.png}}
 ===Script de création===
@@ Ligne 134: / Ligne 135: @@
 )ENGINE=InnoDB DEFAULT CHARSET=utf8;
+CREATE TABLE Logins (
+        idLogin int NOT NULL AUTO_INCREMENT,
+        pseudo     Varchar (30),
+        email      Varchar (50) NOT NULL,
+        motPasse   Varchar (50) NOT NULL,
+        PRIMARY KEY (idLogin)
+)ENGINE=InnoDB DEFAULT CHARSET=utf8;
 CREATE TABLE Adresses(
@@ Ligne 149: / Ligne 157: @@
         nomPers    Varchar (30),
         prenomPers Varchar (30),
+        id_personne_login Int NOT NULL,
         id_personne_adresse  Int NOT NULL,
-        PRIMARY KEY (idPersonne ),
+        PRIMARY KEY (`idPersonne`),
+        CONSTRAINT FK_personne_login FOREIGN KEY(`id_personne_login`) REFERENCES Logins(`idLogin`),
         CONSTRAINT FK_personne_adresse FOREIGN KEY(`id_personne_adresse`) REFERENCES Adresses(`idAdresse`)
-)ENGINE=InnoDB DEFAULT CHARSET=utf8;
-CREATE TABLE Logins (
-        idLogin int NOT NULL AUTO_INCREMENT,
-        pseudo     Varchar (30),
-        email      Varchar (50) NOT NULL,
-        motPasse   Varchar (50) NOT NULL,
-        idPersonne int,
-        PRIMARY KEY (idLogin),
-        UNIQUE (idPersonne)
 )ENGINE=InnoDB DEFAULT CHARSET=utf8;
@@ Ligne 231: / Ligne 231: @@
 ===== Insertions dans les tables =====
+  * Insertion régions de France :
+<code>
+use siteEssai;
+delete from `Regions`;
+INSERT INTO `Regions` VALUES
+('1', 'Alsace'),
+('2', 'Aquitaine'),
+('3', 'Auvergne'),
+('4', 'Basse Normandie'),
+('5', 'Bourgogne'),
+('6', 'Bretagne'),
+('7', 'Centre'),
+('8', 'Champagne Ardenne'),
+('9', 'Corse'),
+('10', 'Franche Comte'),
+('11', 'Haute Normandie'),
+('12', 'Ile de France'),
+('13', 'Languedoc Roussillon'),
+('14', 'Limousin'),
+('15', 'Lorraine'),
+('16', 'Midi-Pyrénées'),
+('17', 'Nord Pas de Calais'),
+('18', 'Provence Alpes Côte d\'Azur'),
+('19', 'Pays de la Loire'),
+('20', 'Picardie'),
+('21', 'Poitou Charente'),
+('22', 'Rhone Alpes'),
+('23', 'd\'outre-mer');
+</code>
+  * Insertion departements de France :
+<code>
+use siteEssai;
+delete from `Departements`;
+INSERT INTO `Departements` (`num_departement`, `id_region_dpt`, `nom`) VALUES
+('01', '22', 'Ain'),
+('02', '20', 'Aisne'),
+('03', '3', 'Allier'),
+('04', '18', 'Alpes de haute provence'),
+('05', '18', 'Hautes alpes'),
+('06', '18', 'Alpes maritimes'),
+('07', '22', 'Ardèche'),
+('08', '8', 'Ardennes'),
+('09', '16', 'Ariège'),
+('10', '8', 'Aube'),
+('11', '13', 'Aude'),
+('12', '16', 'Aveyron'),
+('13', '18', 'Bouches du rhône'),
+('14', '4', 'Calvados'),
+('15', '3', 'Cantal'),
+('16', '21', 'Charente'),
+('17', '21', 'Charente maritime'),
+('18', '7', 'Cher'),
+('19', '14', 'Corrèze'),
+('21', '5', 'Côte d\'or'),
+('22', '6', 'Côtes d\'Armor'),
+('23', '14', 'Creuse'),
+('24', '2', 'Dordogne'),
+('25', '10', 'Doubs'),
+('26', '22', 'Drôme'),
+('27', '11', 'Eure'),
+('28', '7', 'Eure et Loir'),
+('29', '6', 'Finistère'),
+('30', '13', 'Gard'),
+('31', '16', 'Haute garonne'),
+('32', '16', 'Gers'),
+('33', '2', 'Gironde'),
+('34', '13', 'Hérault'),
+('35', '6', 'Ile et Vilaine'),
+('36', '7', 'Indre'),
+('37', '7', 'Indre et Loire'),
+('38', '22', 'Isère'),
+('39', '10', 'Jura'),
+('40', '2', 'Landes'),
+('41', '7', 'Loir et Cher'),
+('42', '22', 'Loire'),
+('43', '3', 'Haute Loire'),
+('44', '19', 'Loire Atlantique'),
+('45', '7', 'Loiret'),
+('46', '16', 'Lot'),
+('47', '2', 'Lot et Garonne'),
+('48', '13', 'Lozère'),
+('49', '19', 'Maine et Loire'),
+('50', '4', 'Manche'),
+('51', '8', 'Marne'),
+('52', '8', 'Haute Marne'),
+('53', '19', 'Mayenne'),
+('54', '15', 'Meurthe et Moselle'),
+('55', '15', 'Meuse'),
+('56', '6', 'Morbihan'),
+('57', '15', 'Moselle'),
+('58', '5', 'Nièvre'),
+('59', '17', 'Nord'),
+('60', '20', 'Oise'),
+('61', '4', 'Orne'),
+('62', '17', 'Pas de Calais'),
+('63', '3', 'Puy de Dôme'),
+('64', '2', 'Pyrénées Atlantiques'),
+('65', '16', 'Hautes Pyrénées'),
+('66', '13', 'Pyrénées Orientales'),
+('67', '1', 'Bas Rhin'),
+('68', '1', 'Haut Rhin'),
+('69', '22', 'Rhône'),
+('70', '10', 'Haute Saône'),
+('71', '5', 'Saône et Loire'),
+('72', '19', 'Sarthe'),
+('73', '22', 'Savoie'),
+('74', '22', 'Haute Savoie'),
+('75', '12', 'Paris'),
+('76', '11', 'Seine Maritime'),
+('77', '12', 'Seine et Marne'),
+('78', '12', 'Yvelines'),
+('79', '21', 'Deux Sèvres'),
+('80', '20', 'Somme'),
+('81', '16', 'Tarn'),
+('82', '16', 'Tarn et Garonne'),
+('83', '18', 'Var'),
+('84', '18', 'Vaucluse'),
+('85', '19', 'Vendée'),
+('86', '21', 'Vienne'),
+('87', '14', 'Haute Vienne'),
+('88', '15', 'Vosges'),
+('89', '5', 'Yonne'),
+('90', '10', 'Territoire de Belfort'),
+('91', '12', 'Essonne'),
+('92', '12', 'Hauts de Seine'),
+('93', '12', 'Seine Saint Denis'),
+('94', '12', 'Val de Marne'),
+('95', '12', 'Val d\'Oise'),
+('2a', '9', 'Corse du Sud'),
+('2b', '9', 'Haute Corse'),
+('976', '23', 'Mayotte'),
+('971', '23', 'Guadeloupe'),
+('973', '23', 'Guyane'),
+('972', '23', 'Martinique'),
+('974', '23', 'Réunion'),
+('975', '23', 'Saint-Pierre-et-Miquelon');
+</code>
-  * Récupérer ce fichier pour l'insertion des Villes, départements et régions de France :
+  * Récupérer ce fichier pour l'insertion des Villes de France :
 {{https://debian-facile.org/images/file-Ra89d9c9c9934b162b81f6b8f944e7e34}}
@@ Ligne 264: / Ligne 409: @@
 # Table: Logins
 #------------------------------------------------------------
+TRUNCATE `Logins`;
+delete from `Logins`;
 insert into Logins (pseudo, email, motPasse) values
 ('hypathie', 'hypathie@hotmail.fr', '123'),
-('capitain', 'capitain@hotmail.fr', '456'),
+('capitain', 'capitain@hotmail.fr', '456');
 #------------------------------------------------------------
@@ Ligne 307: / Ligne 454: @@
 INSERT INTO `Rencontres` (`nomLieu`, `id_rencontre_Adresse`, `dates`, `heure_debut`) VALUES
 ('spécial tango du carousel', 3, '160115', '21:45');
+#------------------------------------------------------------
+# Table: Rencontres_personnes
+#------------------------------------------------------------
+INSERT INTO Rencontres_personnes VALUES (1,1), (2,1);
 </file>
@@ Ligne 319: / Ligne 472: @@
 Voyons quelques jointures sur tables !
+=== Afficher les personnes associées aux logins ===
+<code user>mysql -u site1 -p site1 -e "select Personnes.nomPers, Logins.pseudo from Personnes \
+JOIN Logins on Personnes.id_personne_login=Logins.idLogin;"</code>
+<code>Enter password:
++----------+----------+
+| nomPers  | pseudo   |
++----------+----------+
+| Apatchie | hypathie |
+| Toutbeau | capitain |
++----------+----------+
+</code>
+===Afficher personnes, login et adresse avec ville===
+<code user>
+select Personnes.nomPers, Personnes.prenomPers, \
+Logins.pseudo, Logins.email, \
+Adresses.numRue, Adresses.rue, \
+Adresses.codePostal, Villes_france.ville_nom from Personnes \
+JOIN Logins on Personnes.id_personne_login=Logins.idLogin \
+JOIN Adresses on Personnes.id_personne_adresse=Adresses.idAdresse \
+JOIN Villes_france on Adresses.Id_adresse_ville=Villes_france.ville_id;
+</code>
+<code>
++----------+------------+----------+---------------------+--------+------------------+------------+--------------------+
+| nomPers  | prenomPers | pseudo   | email               | numRue | rue              | codePostal | ville_nom          |
++----------+------------+----------+---------------------+--------+------------------+------------+--------------------+
+| Apatchie | coquine    | hypathie | hypathie@hotmail.fr |     25 | rue de hypathie  | 73000      | CHAMBERY           |
+| Toutbeau | Choubinou  | capitain | capitain@hotmail.fr |     56 | rue de capitaine | 07170      | VILLENEUVE-DE-BERG |
++----------+------------+----------+---------------------+--------+------------------+------------+--------------------+
+</code>
 ===Obtenir selon un numéro de département toutes les villes dont le nom commence par une lettre, leur code postale et leur identifiant===
@@ Ligne 357: / Ligne 545: @@
 </code>
-===Afficher les noms de villes et le nom du département pour les villes commençant par "v" et dont le code postal du département est "07170"===
+==="LIKE" : Afficher les noms de villes et le nom du département pour les villes commençant par "v" et dont le code postal du département est "07170"===
 <code user>mysql -u site1 -p site1 -e "select Villes_france.ville_nom, Departements.nom from Villes_france \
@@ Ligne 370: / Ligne 558: @@
 </code>
-===Plusieurs jointures, on donne une ville, son département et sa région ===
+==="LIKE" et plusieurs jointures : on affiche une ville, son département et sa région ===
 <code user>
@@ Ligne 385: / Ligne 573: @@
 | CHAMBERY  | Savoie | Rhone Alpes |
 +-----------+--------+-------------+
+</code>
+=== "REGEXP" et jointure : Afficher les villes associées à la ou les rencontre(s) qui aurait eu lieu en janvier ===
+<code user>mysql -u site1 -p site1 -e "select Rencontres.nomLieu, Villes_france.ville_nom, Rencontres.dates from Rencontres \
+JOIN Adresses on Rencontres.id_rencontre_Adresse=Adresses.idAdresse \
+JOIN Villes_france ON Adresses.Id_adresse_ville = Villes_france.ville_id \
+where Rencontres.dates REGEXP '.*01.*';"</code>
+<code>Enter password:
++----------------------------+-----------+------------+
+| nomLieu                    | ville_nom | dates      |
++----------------------------+-----------+------------+
+| spécial tango du carousel  | VALENCE   | 2016-01-15 |
++----------------------------+-----------+------------+</code>
+=== Afficher les rencontres, leurs villes, leurs dates et les personnes qui s'y trouvaient ===
+<code user>
+mysql -u site1 -p site1 -e "select Rencontres.nomLieu, Villes_france.ville_nom, \
+ Rencontres.dates, Rencontres_personnes.id_rencontre_personne, \
+ Rencontres_personnes.id_personne_rencontre, Personnes.nomPers, \
+ Personnes.prenomPers from Rencontres_personnes  \
+LEFT JOIN Personnes ON Rencontres_personnes.id_personne_rencontre=Personnes.idPersonne \
+LEFT JOIN Rencontres on Rencontres_personnes.id_rencontre_personne=Rencontres.idRencontre \
+LEFT JOIN Adresses on Rencontres.id_rencontre_Adresse=Adresses.idAdresse \
+LEFT JOIN Villes_france ON Adresses.Id_adresse_ville = Villes_france.ville_id;"
+</code>
+<code>
+| nomLieu                    | ville_nom | dates      | id_rencontre_personne | id_personne_rencontre | nomPers  | prenomPers |
++----------------------------+-----------+------------+-----------------------+-----------------------+----------+------------+
+| spécial tango du carousel  | VALENCE   | 2016-01-15 |                     1 |                     1 | Apatchie | coquine    |
+| spécial tango du carousel  | VALENCE   | 2016-01-15 |                     1 |                     2 | Toutbeau | Choubinou  |
++----------------------------+-----------+------------+-----------------------+-----------------------+----------+-----------
 </code>

utilisateurs/hypathie/tutos/mysql-utiliser-l-objet-adresse-villes_departements_regions_de_france.1460968687.txt.gz · Dernière modification: 18/04/2016 10:38 par Hypathie

Debian-facile

Différences

Pied de page des forums