#!/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"
cd ~/Téléchargements && chmod +x creerBDDetUser.sh
./scriptCreationBDDetUser.sh nomBDD nomUser_de_BDD mot_passe_user_de_BDD
./scriptCreationBDDetUser.sh site1 site1 site1
#------------------------------------------------------------ # 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;
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 | | +----------------------+-------------+------+-----+---------+----------------+
mysql -u utilisateur1 site1 -p site1
mysql>describe Rencontres;
mysql -u utilisateur1 -p site1
mysql> use site1; describe Rencontres;
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');
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');
file-Ra89d9c9c9934b162b81f6b8f944e7e34
apt-get install unrar-free
cd ~/Téléchargements && unrar-free -x insert_villes_departements_regions_france.rar
mysql -u site1 -p <insert_villes_departements_regions_france.sql
#------------------------------------------------------------ # 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);
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 !
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 | +----------+----------+
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 | +----------+------------+----------+---------------------+--------+------------------+------------+--------------------+
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 | +-----------------+----------+--------------------------------+-------------------+
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 | +--------------------+----------+
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 | +-----------+--------+-------------+
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 | +----------------------------+-----------+------------+
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 | +----------------------------+-----------+------------+-----------------------+-----------------------+----------+-----------