H.
TSOUNGUI Scripts de gestion d’une BDD MYSQL 1/5
Installation du serveur MySQL-server sous linux ubuntu 16.04
henri@ubuntu16:~$ sudo -s
[sudo] Mot de passe de henri :
root@ubuntu16:~# apt install mysql-server
Lecture des listes de paquets... Fait
Construction de l'arbre des dépendances
Lecture des informations d'état... Fait
Les NOUVEAUX paquets suivants seront installés :
libevent-core-2.0-5 mysql-client-5.7 mysql-client-core-5.7 mysql-server
mysql-server-5.7 mysql-server-core-5.7
0 mis à jour, 6 nouvellement installés, 4 à enlever et 93 non mis à jour.
Il est nécessaire de prendre 18,2 Mo dans les archives.
Après cette opération, 23,5 Mo d'espace disque supplémentaires seront utilisés.
Souhaitez-vous continuer ? [O/n] o
...... … …… …… ……
root@ubuntu16:~# mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
root@ubuntu16:~# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.5.5-10.0.31-MariaDB-0ubuntu0.16.04.2 Ubuntu 16.04
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema|
| zabbix |
+------------------------------+
4 rows in set (0,26 sec)
mysql> create database employes;
Query OK, 1 row affected (0,04 sec)
mysql> use employes;
Database changed
mysql> grant all on *.* to henri@'%' identified by 'henri2007';
Query OK, 0 rows affected (0,14 sec)
mysql> exit;
Bye
H. TSOUNGUI Scripts de gestion d’une BDD MYSQL 2/5
root@ubuntu16:~# mysql -u henri -p
Enter password:
ERROR 1045 (28000): Access denied for user 'henri'@'localhost' (using password: YES)
root@ubuntu16:~# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.5.5-10.0.31-MariaDB-0ubuntu0.16.04.2 Ubuntu 16.04
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use employes;
Database changed
mysql> grant all on *.* to 'henri'@localhost identified by 'henri2017';
Query OK, 0 rows affected (0,00 sec)
mysql> grant all on *.* to 'henri'@'%' identified by 'henri2017';
Query OK, 0 rows affected (0,01 sec)
mysql> exit
Bye
Connexion distante autorisée ? voir le fichier [Link] ou [Link] et commenter la ligne … [Link]
root@ubuntu16:/etc/mysql/conf.d# gedit [Link]
root@ubuntu16:/etc/mysql#
[1]+ Fini gedit [Link]
root@ubuntu16:/etc/mysql#
Création des tables SITE et SALARIE
root@ubuntu16:/etc/mysql# mysql -u henri -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.5.5-10.0.31-MariaDB-0ubuntu0.16.04.2 Ubuntu 16.04
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use employes;
Database changed
mysql> create table site(siteCode char(2) not null primary key, siteVille varchar(15) not null);
Query OK, 0 rows affected (0,57 sec)
mysql> create table salarie(salNum char(3) not null primary key, salNom varchar(15) not null, salSite char(2)
not null, salSalaire numeric(7,2));
Query OK, 0 rows affected (0,23 sec)
H. TSOUNGUI Scripts de gestion d’une BDD MYSQL 3/5
Création de la contrainte de clé étrangère
mysql> alter table salarie add constraint fk_salarie foreign key (salSite) references site(siteCode) on delete
cascade;
Query OK, 0 rows affected (0,78 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc site;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| siteCode | char(2) | NO | PRI | NULL | |
| siteVille | varchar(15) | NO | | NULL | |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0,06 sec)
mysql> desc salarie;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| salNum | char(3) | NO | PRI | NULL | |
| salNom | varchar(15) | NO | | NULL | |
| salSite | char(2) | NO | MUL | NULL | |
| salSalaire | decimal(7,2) | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
4 rows in set (0,03 sec)
Insertion des données dans les tables
mysql> insert into site values('s1','Valenciennes');
Query OK, 1 row affected (0,05 sec)
mysql> insert into site values('s3','Dunkerque');
Query OK, 1 row affected (0,04 sec)
mysql> insert into site values('s2','Lille');
Query OK, 1 row affected (0,01 sec)
mysql> insert into salarie values('E05','Padre','s1',2109.18);
Query OK, 1 row affected (0,05 sec)
mysql> insert into salarie values('E18','Lannoy','s2',1853.45);
Query OK, 1 row affected (0,03 sec)
mysql> insert into salarie values('E29','Melissa','s2',2215.87);
Query OK, 1 row affected (0,03 sec)
mysql> insert into salarie values('E43','Dupire','s3',2610.23);
Query OK, 1 row affected (0,07 sec)
Affichage des données des tables
mysql> select * from site;
+--------------+------------------+
| siteCode | siteVille |
+--------------+------------------+
| s1 | Valenciennes|
| s2 | Lille |
| s3 | Dunkerque |
+--------------+------------------+
3 rows in set (0,02 sec)
H. TSOUNGUI Scripts de gestion d’une BDD MYSQL 4/5
mysql> select * from salarie;
+--------+---------+---------+------------+
| salNum | salNom | salSite | salSalaire |
+--------+---------+---------+------------+
| E05 | Padre | s1 | 21
09.18 |
| E18 | Lannoy | s2 | 1853.45 |
| E29 | Melissa | s2 | 2215.87 |
| E43 | Dupire | s3 | 2610.23 |
+--------+---------+---------+------------+
4 rows in set (0,00 sec)
Travail à distance avec le client graphique SQLYOG
H. TSOUNGUI Scripts de gestion d’une BDD MYSQL 5/5