====== mysql : Utiliser l'objet Adresse : villes_départements_régions_de_france ====== * Objet : Installer une BDD mysql et y insérer des données * Niveau requis : FIXME {{tag>avisé}} * 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 ===== Introduction ===== ==== Script création base de données mysql et utilisateur ==== #!/bin/sh # Fonction d'affichage du retour du script # printf '\e[32m\e[m' "$1 $2 $3"; ok() { printf "$1 $2 $3\n"; } EXPECTED_ARGS=3 E_BADARGS=65 MYSQL=`which mysql` Q1="CREATE DATABASE IF NOT EXISTS $1;" Q2="GRANT ALL ON *.* TO '$2'@'localhost' IDENTIFIED BY '$3';" Q3="FLUSH PRIVILEGES;" SQL="${Q1}${Q2}${Q3}" if [ $# -ne $EXPECTED_ARGS ] then echo "Usage: $0 dbname dbuser dbpass" exit $E_BADARGS fi $MYSQL -uroot -p -e "$SQL" ok "Database $1 and user $2 created with a password $3" * Donner les droits d'exécution : cd ~/Téléchargements && chmod +x creerBDDetUser.sh * Exécuter le script : ./scriptCreationBDDetUser.sh nomBDD nomUser_de_BDD mot_passe_user_de_BDD Pour la suite, et faire simple, par exemple ''./scriptCreationBDDetUser.sh site1 site1 site1'' ===== Création des tables ===== ===MLD de la BDD=== {{http://pix.toile-libre.org/upload/original/1461051633.png}} ===Script de création=== #------------------------------------------------------------ # Script MySQL de la base de données site1. #------------------------------------------------------------ USE site1; DROP TABLE IF EXISTS `Rencontres_personnes`; DROP TABLE IF EXISTS `Personnes`; DROP TABLE IF EXISTS `Rencontres`; DROP TABLE IF EXISTS `Adresses`; DROP TABLE IF EXISTS `Villes_france`; DROP TABLE IF EXISTS `Departements`; DROP TABLE IF EXISTS `Regions`; CREATE TABLE `Regions` ( `num_region` varchar(2) NOT NULL, `nom` varchar(255) NOT NULL, PRIMARY KEY (`num_region`) )ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `Departements` ( `num_departement` varchar(3) NOT NULL, `id_region_dpt` varchar(2) NOT NULL, `nom` char(32) NOT NULL, PRIMARY KEY (`num_departement`), CONSTRAINT FK_region_dpt FOREIGN KEY(`id_region_dpt`) REFERENCES Regions(`num_region`) )ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `Villes_france` ( `ville_id` int NOT NULL, `departement_code` varchar(3) DEFAULT NULL, `ville_slug` varchar(255) DEFAULT NULL, `ville_nom` varchar(45) DEFAULT NULL, `ville_nom_simple` varchar(45) DEFAULT NULL, `ville_nom_reel` varchar(45) DEFAULT NULL, `ville_nom_soundex` varchar(20) DEFAULT NULL, `ville_nom_metaphone` varchar(22) DEFAULT NULL, `ville_code_postal` varchar(255) DEFAULT NULL, `ville_commune` varchar(3) DEFAULT NULL, `ville_code_commune` varchar(5) NOT NULL, `ville_arrondissement` smallint(3) unsigned DEFAULT NULL, `ville_canton` varchar(4) DEFAULT NULL, `ville_amdi` smallint(5) unsigned DEFAULT NULL, `ville_population_2010` mediumint(11) unsigned DEFAULT NULL, `ville_population_1999` mediumint(11) unsigned DEFAULT NULL, `ville_population_2012` mediumint(10) unsigned DEFAULT NULL COMMENT 'approximatif', `ville_densite_2010` int(11) DEFAULT NULL, `ville_surface` float DEFAULT NULL, `ville_longitude_deg` float DEFAULT NULL, `ville_latitude_deg` float DEFAULT NULL, `ville_longitude_grd` varchar(9) DEFAULT NULL, `ville_latitude_grd` varchar(8) DEFAULT NULL, `ville_longitude_dms` varchar(9) DEFAULT NULL, `ville_latitude_dms` varchar(8) DEFAULT NULL, `ville_zmin` mediumint(4) DEFAULT NULL, `ville_zmax` mediumint(4) DEFAULT NULL, PRIMARY KEY (`ville_id`), UNIQUE KEY `ville_code_commune_2` (`ville_code_commune`), UNIQUE KEY `ville_slug` (`ville_slug`), KEY `ville_departement` (`departement_code`), KEY `ville_nom` (`ville_nom`), KEY `ville_nom_reel` (`ville_nom_reel`), KEY `ville_code_commune` (`ville_code_commune`), KEY `ville_code_postal` (`ville_code_postal`), KEY `ville_longitude_latitude_deg` (`ville_longitude_deg`,`ville_latitude_deg`), KEY `ville_nom_soundex` (`ville_nom_soundex`), KEY `ville_nom_metaphone` (`ville_nom_metaphone`), KEY `ville_population_2010` (`ville_population_2010`), KEY `ville_nom_simple` (`ville_nom_simple`), CONSTRAINT FK_villes_dpt FOREIGN KEY(`departement_code`) REFERENCES Departements(`num_departement`) )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( idAdresse int NOT NULL AUTO_INCREMENT, numRue smallint(5), rue varchar(25) NOT NULL, Id_adresse_ville int, codePostal varchar(255), PRIMARY KEY (`idAdresse`), CONSTRAINT FK_ville_adresses FOREIGN KEY(`Id_adresse_ville`) REFERENCES Villes_france(`ville_id`) )ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE Personnes( idPersonne int NOT NULL AUTO_INCREMENT, nomPers Varchar (30), prenomPers Varchar (30), id_personne_login Int NOT NULL, id_personne_adresse Int NOT NULL, 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 Rencontres( idRencontre int NOT NULL AUTO_INCREMENT, nomLieu varchar(50) NOT NULL, id_rencontre_Adresse int NOT NULL, dates date NOT NULL, heure_debut time NOT NULL, heure_fin time, PRIMARY KEY (idRencontre ), CONSTRAINT FK_adresse_rencontre FOREIGN KEY(`id_rencontre_Adresse`) REFERENCES Adresses(`idAdresse`) )ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE Rencontres_personnes( id_personne_rencontre INT REFERENCES PERSONNES(`idPersonne`), id_rencontre_personne INT REFERENCES RENCONTRES(`idRencontre`) )ENGINE=InnoDB DEFAULT CHARSET=utf8; ===Connexion à la base de donnée et requêtes simples=== * Voir le détail de la table ''Rencontres'' en mysql : (Donner le mot de passe de l'utilisateur ''utilisateur1'') mysql -u utilisateur1 -p site1 -e "describe Rencontres;" Enter password: +----------------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------------+-------------+------+-----+---------+----------------+ | idRencontre | int(11) | NO | PRI | NULL | auto_increment | | nomLieu | varchar(50) | NO | | NULL | | | id_rencontre_Adresse | int(11) | NO | MUL | NULL | | | dates | date | NO | | NULL | | | heure_debut | time | NO | | NULL | | | heure_fin | time | YES | | NULL | | +----------------------+-------------+------+-----+---------+----------------+ * équivalent à : mysql -u utilisateur1 site1 -p site1 mysql>describe Rencontres; * équivalent à : mysql -u utilisateur1 -p site1 mysql> use site1; describe Rencontres; ===== Insertions dans les tables ===== * Insertion régions de France : 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'); * Insertion departements de France : 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'); * Récupérer ce fichier pour l'insertion des Villes de France : {{https://debian-facile.org/images/file-Ra89d9c9c9934b162b81f6b8f944e7e34}} * Décompression : apt-get install unrar-free cd ~/Téléchargements && unrar-free -x insert_villes_departements_regions_france.rar * Insertion : C'est un peu long, le fichier pèse 8 Mo !\\ Patience... mysql -u site1 -p * script mysql d'insertion des adresses, des personnes, des logins et rencontres : #------------------------------------------------------------ # Script MySQL insertion dans les tables : # Adresses # Personnes # Rencontres # de la basse de données 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'); #------------------------------------------------------------ # Table: Adresses #------------------------------------------------------------ SET FOREIGN_KEY_CHECKS=0; TRUNCATE `Adresses`; SET FOREIGN_KEY_CHECKS=1; delete from `Adresses`; INSERT INTO `Adresses` (`numRue`, `rue`, `codePostal`, `Id_adresse_ville`) VALUES (25, 'rue de hypathie', '73000', 29963), (56, 'rue de capitaine', '07170', 2123), (23, 'Avenue Pierre Semard', '26000', 9492); #------------------------------------------------------------ # Table: Personnes #------------------------------------------------------------ SET FOREIGN_KEY_CHECKS=0; TRUNCATE `Personnes`; SET FOREIGN_KEY_CHECKS=1; delete from `Personnes`; INSERT INTO `Personnes` (`nomPers`, `prenomPers``id_personne_adresse`) VALUES ('Apatchie', 'coquine', 1), ('Toutbeau', 'Choubinou', 2); #------------------------------------------------------------ # Table: Rencontres # to insert the value '1999-03-00', use '990300'. # As a string with no delimiters in 'HHMMSS' format, provided that it makes sense as a time. For example, '101112' is understood as '10:11:12', but '109712' is illegal # You can also use one of the following “relaxed” syntaxes: 'HH:MM:SS', 'HH:MM', 'D HH:MM', 'D HH', or 'SS'. Here D represents days and can have a value from 0 to 34 #------------------------------------------------------------ SET FOREIGN_KEY_CHECKS=0; TRUNCATE `Rencontres`; SET FOREIGN_KEY_CHECKS=1; delete from `Rencontres`; 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); ===== Utilisation ===== ==== Faire des jointures sur tables ==== Selon le MLD proposé, c'est l'identifiant d'une ville (champ ''ville_id'' de la table ''Villes_france'' qu'il faut pour associer une ville à une Adresse.\\ Cela permet par exemple d'utiliser au niveau du code php des requêtes à la base de données afin de proposer les villes existantes à l'utilisateur qui remplit un formulaire, et de sélectionner pour lui le code postale.\\ Voyons quelques jointures sur tables ! === Afficher les personnes associées aux logins === mysql -u site1 -p site1 -e "select Personnes.nomPers, Logins.pseudo from Personnes \ JOIN Logins on Personnes.id_personne_login=Logins.idLogin;" Enter password: +----------+----------+ | nomPers | pseudo | +----------+----------+ | Apatchie | hypathie | | Toutbeau | capitain | +----------+----------+ ===Afficher personnes, login et adresse avec ville=== 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; +----------+------------+----------+---------------------+--------+------------------+------------+--------------------+ | 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 | +----------+------------+----------+---------------------+--------+------------------+------------+--------------------+ ===Obtenir selon un numéro de département toutes les villes dont le nom commence par une lettre, leur code postale et leur identifiant=== mysql -u site1 -p site1 -e "select Departements.num_departement, Villes_france.ville_id, \ Villes_france.ville_nom, Villes_france.ville_code_postal from Departements \ join Villes_france on Departements.num_departement=Villes_france.departement_code \ where Departements.num_departement like '73' AND ville_nom like 'c%' order by ville_nom;" +-----------------+----------+--------------------------------+-------------------+ | num_departement | ville_id | ville_nom | ville_code_postal | +-----------------+----------+--------------------------------+-------------------+ | 73 | 30137 | CESARCHES | 73200 | | 73 | 29997 | CESSENS | 73410 | | 73 | 29968 | CEVINS | 73730 | | 73 | 29974 | CHALLES-LES-EAUX | 73190 | | 73 | 29963 | CHAMBERY | 73000 | | 73 | 29999 | CHAMOUSSET | 73390 | | 73 | 30011 | CHAMOUX-SUR-GELON | 73390 | | 73 | 30126 | CHAMP-LAURENT | 73390 | | 73 | 30058 | CHAMPAGNEUX | 73240 | | 73 | 30009 | CHAMPAGNY-EN-VANOISE | 73350 | | 73 | 29925 | CHANAZ | 73310 | | 73 | 30142 | CHATEAUNEUF | 73390 | | 73 | 30079 | CHIGNIN | 73800 | | 73 | 30060 | CHINDRIEUX | 73310 | | 73 | 29851 | CLERY | 73460 | | 73 | 30122 | COGNIN | 73160 | | 73 | 30120 | COHENNOZ | 73400 | | 73 | 30097 | COISE-SAINT-JEAN-PIED-GAUTHIER | 73800 | | 73 | 29988 | CONJUX | 73310 | | 73 | 30014 | CORBEL | 73160 | | 73 | 29846 | CREST-VOLAND | 73590 | | 73 | 30143 | CRUET | 73800 | | 73 | 29892 | CURIENNE | 73190 | +-----------------+----------+--------------------------------+-------------------+ ==="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"=== mysql -u site1 -p site1 -e "select Villes_france.ville_nom, Departements.nom from Villes_france \ JOIN Departements on Villes_france.departement_code=Departements.num_departement \ where Villes_france.ville_nom like 'v%' AND ville_code_postal=07170;" +--------------------+----------+ | ville_nom | nom | +--------------------+----------+ | VILLENEUVE-DE-BERG | Ardèche | +--------------------+----------+ ==="LIKE" et plusieurs jointures : on affiche une ville, son département et sa région === mysql -u site1 -p site1 -e "select Villes_france.ville_nom, Departements.nom, Regions.nom from Villes_france \ JOIN Departements on Villes_france.departement_code=Departements.num_departement \ JOIN Regions ON Departements.id_region_dpt = Regions.num_region \ where Villes_france.ville_nom like 'C%' AND ville_code_postal=73000;" +-----------+--------+-------------+ | ville_nom | nom | nom | +-----------+--------+-------------+ | CHAMBERY | Savoie | Rhone Alpes | +-----------+--------+-------------+ === "REGEXP" et jointure : Afficher les villes associées à la ou les rencontre(s) qui aurait eu lieu en janvier === 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.*';" Enter password: +----------------------------+-----------+------------+ | nomLieu | ville_nom | dates | +----------------------------+-----------+------------+ | spécial tango du carousel | VALENCE | 2016-01-15 | +----------------------------+-----------+------------+ === Afficher les rencontres, leurs villes, leurs dates et les personnes qui s'y trouvaient === 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;" | 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 | +----------------------------+-----------+------------+-----------------------+-----------------------+----------+-----------