mysql villes départements régions de France

Objet : Installer une BDD mysql et y insérer des données
Niveau requis :
débutant
Référence :
- Référence : http://www.manuelphp.com/mysql/tutorial.php

Création des tables

MLD de la BDD :

Script de création

Il faut avoir créer la bdd site1 :

Lancer ce script : https://debian-facile.org/utilisateurs:hypathie:config:script_creation_bdd_mysql_et_user

#------------------------------------------------------------
#        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 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),
        email      Varchar (50) NOT NULL,
        id_personne_adresse  Int NOT NULL,
        pseudo     Varchar (30),
        motPasse   Varchar (50),
        PRIMARY KEY (idPersonne ),
        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;

Rappel

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

Récupérer ce fichier pour l'insertion des Villes, départements et régions de France :

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 <insert_villes_departements_regions_france.sql

script mysql d'insertion des adresses, des personnes, des logins et rencontres :

insert_adresse_personne_login_rencontre.sql

#------------------------------------------------------------
#        Script MySQL insertion dans les tables :
#          Adresses
#          Personnes
#          Rencontres
#        de la basse de données site1.
#------------------------------------------------------------
 
use site1;
 
#------------------------------------------------------------
# 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`, `email`,`pseudo`,`motPasse`, `id_personne_adresse`) VALUES
('Apatchie', 'coquine', 'apachie_coquine@free.fr','hypathie', 'motdepasse1', 1),
('Toutbeau', 'Choubinou', 'toutbeau_choubinou@free.fr','capitain', 'motdepasse2', 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');