Vous n'êtes pas identifié(e).
L'icône rouge permet de télécharger chaque page du wiki visitée au format PDF et la grise au format ODT →
Ci-dessous, les différences entre deux révisions de la page.
Les deux révisions précédentes Révision précédente Prochaine révision | Révision précédente | ||
utilisateurs:hypathie:tutos:mysql-utiliser-l-objet-adresse-villes_departements_regions_de_france [13/04/2016 06:38] Hypathie [Utilisation] |
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" | * 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à !.]] :-) | * 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 | * Référence : http://www.manuelphp.com/mysql/tutorial.php | ||
Ligne 53: | Ligne 54: | ||
===== Création des tables ===== | ===== Création des tables ===== | ||
- | * MLD de la BDD : | + | ===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 | + | ===Script de création=== |
Ligne 134: | Ligne 135: | ||
)ENGINE=InnoDB DEFAULT CHARSET=utf8; | )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( | CREATE TABLE Adresses( | ||
Ligne 149: | Ligne 157: | ||
nomPers Varchar (30), | nomPers Varchar (30), | ||
prenomPers Varchar (30), | prenomPers Varchar (30), | ||
- | email Varchar (50) NOT NULL, | + | id_personne_login Int NOT NULL, |
id_personne_adresse Int NOT NULL, | id_personne_adresse Int NOT NULL, | ||
- | pseudo Varchar (30), | + | PRIMARY KEY (`idPersonne`), |
- | motPasse Varchar (50), | + | CONSTRAINT FK_personne_login FOREIGN KEY(`id_personne_login`) REFERENCES Logins(`idLogin`), |
- | PRIMARY KEY (idPersonne ), | + | |
CONSTRAINT FK_personne_adresse FOREIGN KEY(`id_personne_adresse`) REFERENCES Adresses(`idAdresse`) | CONSTRAINT FK_personne_adresse FOREIGN KEY(`id_personne_adresse`) REFERENCES Adresses(`idAdresse`) | ||
)ENGINE=InnoDB DEFAULT CHARSET=utf8; | )ENGINE=InnoDB DEFAULT CHARSET=utf8; | ||
Ligne 175: | Ligne 182: | ||
</file> | </file> | ||
- | <note tip> | + | |
- | __**Rappel**__ | + | ===Connexion à la base de donnée et requêtes simples=== |
* Voir le détail de la table ''Rencontres'' en mysql : | * Voir le détail de la table ''Rencontres'' en mysql : | ||
Ligne 222: | Ligne 229: | ||
</code> | </code> | ||
- | </note> | ||
===== Insertions dans les tables ===== | ===== 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}} | {{https://debian-facile.org/images/file-Ra89d9c9c9934b162b81f6b8f944e7e34}} | ||
Ligne 254: | Ligne 405: | ||
use site1; | use site1; | ||
+ | |||
+ | #------------------------------------------------------------ | ||
+ | # Table: Logins | ||
+ | #------------------------------------------------------------ | ||
+ | TRUNCATE `Logins`; | ||
+ | delete from `Logins`; | ||
+ | |||
+ | insert into Logins (pseudo, email, motPasse) values | ||
+ | ('hypathie', 'hypathie@hotmail.fr', '123'), | ||
+ | ('capitain', 'capitain@hotmail.fr', '456'); | ||
#------------------------------------------------------------ | #------------------------------------------------------------ | ||
Ligne 276: | Ligne 437: | ||
delete from `Personnes`; | delete from `Personnes`; | ||
- | INSERT INTO `Personnes` (`nomPers`, `prenomPers`, `email`,`pseudo`,`motPasse`, `id_personne_adresse`) VALUES | + | INSERT INTO `Personnes` (`nomPers`, `prenomPers``id_personne_adresse`) VALUES |
- | ('Apatchie', 'coquine', 'apachie_coquine@free.fr','hypathie', 'motdepasse1', 1), | + | ('Apatchie', 'coquine', 1), |
- | ('Toutbeau', 'Choubinou', 'toutbeau_choubinou@free.fr','capitain', 'motdepasse2', 2); | + | ('Toutbeau', 'Choubinou', 2); |
#------------------------------------------------------------ | #------------------------------------------------------------ | ||
Ligne 293: | Ligne 454: | ||
INSERT INTO `Rencontres` (`nomLieu`, `id_rencontre_Adresse`, `dates`, `heure_debut`) VALUES | INSERT INTO `Rencontres` (`nomLieu`, `id_rencontre_Adresse`, `dates`, `heure_debut`) VALUES | ||
('spécial tango du carousel', 3, '160115', '21:45'); | ('spécial tango du carousel', 3, '160115', '21:45'); | ||
+ | |||
+ | #------------------------------------------------------------ | ||
+ | # Table: Rencontres_personnes | ||
+ | #------------------------------------------------------------ | ||
+ | |||
+ | INSERT INTO Rencontres_personnes VALUES (1,1), (2,1); | ||
</file> | </file> | ||
Ligne 305: | Ligne 472: | ||
Voyons quelques jointures sur tables ! | 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=== | ===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 343: | Ligne 545: | ||
</code> | </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 \ | <code user>mysql -u site1 -p site1 -e "select Villes_france.ville_nom, Departements.nom from Villes_france \ | ||
Ligne 356: | Ligne 558: | ||
</code> | </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> | <code user> | ||
Ligne 365: | Ligne 567: | ||
</code> | </code> | ||
- | <code>+-----------+--------+-------------+ | + | <code retour> |
+ | +-----------+--------+-------------+ | ||
| ville_nom | nom | nom | | | ville_nom | nom | nom | | ||
+-----------+--------+-------------+ | +-----------+--------+-------------+ | ||
| CHAMBERY | Savoie | Rhone Alpes | | | CHAMBERY | Savoie | Rhone Alpes | | ||
+-----------+--------+-------------+ | +-----------+--------+-------------+ | ||
- | /<code> | + | </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> |