jointure relationnelle

Dans une requête, si les noms de colonnes ne sont pas ambiguës, il n’est pas nécessaire de rajouter devant un champ le nom de la table.
Mais ce n’est pas “propre”, c’est difficile à relire, donc si il y a des jointures au minimum préfixer le nom des champs par de simple alias pour savoir à quelle table appartient chaque champ.

jointure interne

C’est la jointure par défaut celle que vous devez maîtriser parfaitement

Il existe 2 manières de réaliser (écrire) une jointure interne :

SELECT <fields>
FROM TableA 
[INNER] JOIN TableB  ON TableA.PrimaryKey = TableB.ForeignKey;

• ancienne notation

SELECT <fields>
FROM TableA , TableB 
WHERE TableA.PrimaryKey = TableB.ForeignKey;

La première méthode d’écrire une jointure avec “INNER JOIN” est recommandée bien que un peu plus difficile à écrire au début, elle est plus normalisée plus rapide pour les moteurs de SGBDR. C’est cette méthode que vous devez privilégier.

Si les clés dans les tables portent le même nom, avec le mot clé NATURAL, il est possible de définir la jointure sans utiliser le mot clé “ON” (à titre d’information, éviter cette notation).

SELECT <fields>
FROM TableA 
NATURAL JOIN TableB;

Les Différents types de jointures :

• équijointure non-équijointure : pour l’équijointure la comparaison des clés se fait avec une égalité
• les jointures internes sont les jointures naturelles
• les jointures externes (droite gauche) permettent de rajouter les champs quand une des clés est “NULL”
• les autres cas sont très peu utilisés et peuvent être réalisés sur MYSQL grâce à l’opérateur UNION (qui est vu dans la suite du TD)

Créer les 2 tables ci dessous :

DROP TABLE IF EXISTS TD_EMPLOYE,TD_DEPARTEMENT;

CREATE TABLE  TD_DEPARTEMENT (
    idDept INT,
    nom VARCHAR(255),
    lieu VARCHAR(255),
    PRIMARY KEY(idDept)
);

INSERT INTO TD_DEPARTEMENT (idDept,nom,lieu)  VALUES (10,"recherche","Besançon");
INSERT INTO TD_DEPARTEMENT (idDept,nom,lieu)  VALUES (20,"vente","Montbéliard");
INSERT INTO TD_DEPARTEMENT (idDept,nom,lieu)  VALUES (30,"direction","Belfort");
INSERT INTO TD_DEPARTEMENT (idDept,nom,lieu)  VALUES (40,"fabrication","Sochaux");

CREATE TABLE  TD_EMPLOYE (
    idEmploye INT,
    nom VARCHAR(255),
    fonction VARCHAR(255),
    idResponsable INT,
    date_embauche date,
    salaire NUMERIC(8,2),
    prime NUMERIC(8,2),
    departement_id INT,
    PRIMARY KEY(idEmploye)
);
INSERT INTO TD_EMPLOYE(nom,idEmploye,fonction,idResponsable,date_embauche,salaire,prime,departement_id) VALUES 
('MARTIN',16712,'directeur',25717,'2000-05-23',8000,NULL,30);
INSERT INTO TD_EMPLOYE(nom,idEmploye,fonction,idResponsable,date_embauche,salaire,prime,departement_id) VALUES 
('DUPONT',17574,'administratif',16712,'2005-05-03',1800,NULL,30),
('DUPOND',26691,'commercial',27047,'1998-04-04',5000,500,20),
('LAMBERT',25012,'administratif',27047,'2001-03-14',NULL,2400,20),
('JOUBERT',25717,'president',NULL,'1992-08-10',10000,NULL,30),
('LEBRETON',16034,'commercial',27047,'2001-06-01',3000,0,20),
('MARTIN',17147,'commercial',27047,'2005-05-03',4000,500,20),
('PAQUEL',27546,'commercial',27047,'1993-09-03',4400,1000,20),
('LEFEBVRE',25935,'commercial',27047,'1994-01-11',4700,400,20),
('GARDARIN',15155,'ingenieur',24533,'1995-03-22',4800,NULL,10),
('SIMON',26834,'ingenieur',24533,'1998-10-04',4000,NULL,10),
('DELOBEL',16278,'ingenieur',24533,'2004-11-16',4200,NULL,10),
('ADIBA',25067,'ingenieur',24533,'1997-10-05',6000,NULL,10),
('CODD',24533,'directeur',25717,'1985-11-12',11000,NULL,10),
('LAMERE',27047,'directeur',25717,'1999-09-07',9000,NULL,20),
('BALIN',17232,'administratif',24533,'1997-10-03',2700,NULL,10),
('BARA',24831,'administratif',16712,'1998-11-10',3000,NULL,30),
('toto','00001','livreur de pizzas',NULL,NULL,NULL,NULL,NULL);

EXERCICES : les différents cas vont être présentés à l’aide d’exemples

jointure

• R1 : Réaliser la jointure entre les 2 tables et afficher le nom, la fonction et le département de chaque employé (avec INNER JOIN)
• R2 : Réaliser la même requête avec la deuxième écriture (sans INNER JOIN) ; ancienne notation moins performante

Dans les 2 requêtes, le résultat sera trié selon l’ordre lexicographique sur le nom de département puis le nom d’employé

+----------+---------------+----------------+
| nom      | fonction      | nomDepartement |
+----------+---------------+----------------+
| BARA     | administratif | direction      |
| DUPONT   | administratif | direction      |
| JOUBERT  | president     | direction      |
| MARTIN   | directeur     | direction      |
| ADIBA    | ingenieur     | recherche      |
| BALIN    | administratif | recherche      |
| CODD     | directeur     | recherche      |
| DELOBEL  | ingenieur     | recherche      |
| GARDARIN | ingenieur     | recherche      |
| SIMON    | ingenieur     | recherche      |
| DUPOND   | commercial    | vente          |
| LAMBERT  | administratif | vente          |
| LAMERE   | directeur     | vente          |
| LEBRETON | commercial    | vente          |
| LEFEBVRE | commercial    | vente          |
| MARTIN   | commercial    | vente          |
| PAQUEL   | commercial    | vente          |
+----------+---------------+----------------+

Utiliser dans la suite le mot clé JOIN pour vos jointures

• R3 : refaire la même chose avec une jointure externe à droite et à gauche

• Quelle est la différence avec le résultat précédent ?

• R4 : afficher le nombre d’employés par département

+----------------+----------------+
| NombreEmployes | nomDepartement |
+----------------+----------------+
|              4 | direction      |
|              6 | recherche      |
|              7 | vente          |
+----------------+----------------+

• ajouter le lieu

+----------------+----------------+--------------+
| NombreEmployes | nomDepartement | lieu         |
+----------------+----------------+--------------+
|              4 | direction      | Belfort      |
|              6 | recherche      | Besançon     |
|              7 | vente          | Montbéliard  |
+----------------+----------------+--------------+

• PROVOQUER une erreur de type ONLY_FULL_GROUP_BY ; Regarder la valeur de la variable @@sql_mode ; ajouter puis retirer le mode ONLY_FULL_GROUP_BY

SELECT @@sql_mode ;



-- ajouter le mode
SET sql_mode=(SELECT CONCAT(@@sql_mode,',ONLY_FULL_GROUP_BY'));
-- supprimer le mode
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));

ne pas faire SET sql_mode = ''; ; conséquence => https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html
voir config sql_mode

• R5 : Refaire la même chose avec une jointure externe à droite et à gauche
• Comment doit être la jointure pour obtenir le résultat ci dessous :

+---------------+-------------+
| NombreEmploye | nom         |
+---------------+-------------+
|             4 | direction   |
|             0 | fabrication |
|             6 | recherche   |
|             7 | vente       |
+---------------+-------------+

• R6 : Retrouver les départements n’ayant aucun employé.

+-------------+
| nom         |
+-------------+
| fabrication |
+-------------+
+---------------+-------------+
| NombreEmploye | nom         |
+---------------+-------------+
|             0 | fabrication |
+---------------+-------------+

Jointure d’une table à elle-même

• R7 : Donner pour chaque employé le nom de son supérieur hiérarchique.

le résultat sera trié selon l’ordre lexicographique sur la fonction du responsable puis le nom du responsable puis la fonction de l’employé puis le nom de l’employé

+----------+---------------+----------------+---------------------+
| nom      | fonction      | nomResponsable | FonctionResponsable |
+----------+---------------+----------------+---------------------+
| BALIN    | administratif | CODD           | directeur           |
| ADIBA    | ingenieur     | CODD           | directeur           |
| DELOBEL  | ingenieur     | CODD           | directeur           |
| GARDARIN | ingenieur     | CODD           | directeur           |
| SIMON    | ingenieur     | CODD           | directeur           |
| LAMBERT  | administratif | LAMERE         | directeur           |
| DUPOND   | commercial    | LAMERE         | directeur           |
| LEBRETON | commercial    | LAMERE         | directeur           |
| LEFEBVRE | commercial    | LAMERE         | directeur           |
| MARTIN   | commercial    | LAMERE         | directeur           |
| PAQUEL   | commercial    | LAMERE         | directeur           |
| BARA     | administratif | MARTIN         | directeur           |
| DUPONT   | administratif | MARTIN         | directeur           |
| CODD     | directeur     | JOUBERT        | president           |
| LAMERE   | directeur     | JOUBERT        | president           |
| MARTIN   | directeur     | JOUBERT        | president           |
+----------+---------------+----------------+---------------------+

• R8 : Donner pour chaque employé le nom de son supérieur hiérarchique avec un supérieur hiérarchique qui n’a pas de supérieur.

+--------+-----------+----------------+---------------------+
| nom    | fonction  | nomResponsable | FonctionResponsable |
+--------+-----------+----------------+---------------------+
| MARTIN | directeur | JOUBERT        | president           |
| CODD   | directeur | JOUBERT        | president           |
| LAMERE | directeur | JOUBERT        | president           |
+--------+-----------+----------------+---------------------+

non-équijointure (la jointure n’utilise pas “=” entre les 2 clés)

• R9 : Quels sont les employés gagnant plus que SIMON ?

+----------+------------+----------+
| nom      | fonction   | salaire  |
+----------+------------+----------+
| GARDARIN | ingenieur  |  4800.00 |
| DELOBEL  | ingenieur  |  4200.00 |
| MARTIN   | directeur  |  8000.00 |
| CODD     | directeur  | 11000.00 |
| ADIBA    | ingenieur  |  6000.00 |
| JOUBERT  | president  | 10000.00 |
| LEFEBVRE | commercial |  4700.00 |
| DUPOND   | commercial |  5000.00 |
| LAMERE   | directeur  |  9000.00 |
| PAQUEL   | commercial |  4400.00 |
+----------+------------+----------+

Les sous-interrogations

Une caractéristique puissante de SQL est la possibilité qu’un critère de recherche employé dans une clause WHERE (expression à droite d’un opérateur de comparaison) soit lui-même le résultat d’un SELECT ; c’est ce qu’on appelle une sous-interrogation.

https://dev.mysql.com/doc/refman/5.7/en/any-in-some-subqueries.html

Remarque : Tout ces cas ne sont pas à connaître par cœur mais l’objectif est de vous montrer la complexité possible de certaines requêtes faites avec un SELECT

Sous-interrogation ramenant une seule valeur

R10 : Quels sont les employés ayant la même fonction que “codd” ?

+--------+-----------+----------+---------------+
| nom    | fonction  | salaire  | idResponsable |
+--------+-----------+----------+---------------+
| MARTIN | directeur |  8000.00 |         25717 |
| CODD   | directeur | 11000.00 |         25717 |
| LAMERE | directeur |  9000.00 |         25717 |
+--------+-----------+----------+---------------+

R11 : Liste des employés gagnant plus que la moyenne des salaires.

+---------+-----------+----------+
| nom     | fonction  | salaire  |
+---------+-----------+----------+
| MARTIN  | directeur |  8000.00 |
| CODD    | directeur | 11000.00 |
| ADIBA   | ingenieur |  6000.00 |
| JOUBERT | president | 10000.00 |
| LAMERE  | directeur |  9000.00 |
+---------+-----------+----------+

Remarques :

• Une sous-interrogation qui ne ramène aucune ligne se termine avec un code d’erreur.
• Une sous-interrogation ramenant plusieurs lignes provoquera aussi, dans ce cas, une erreur (pour traiter correctement ce cas, voir paragraphe ci-dessous)

Sous-interrogation ramenant plusieurs lignes

• l’opérateur IN (exemple dernière requête du tp3)
• les opérateurs obtenus en ajoutant ANY ou ALL à la suite d’un opérateur de comparaison classique (=, !=, >, >=, <, <=)
  • ANY: la comparaison est vraie si elle est vraie pour au moins un des éléments de l’ensemble. (alias some)
  • ALL: la comparaison sera vraie si elle est vraie pour tous les éléments de l’ensemble.

• R12 : Quels sont les employés gagnant plus que tous les employés du département 20.
• Commencer par afficher le salaire minimum dans le département 20

+---------+-----------+----------+
| nom     | fonction  | salaire  |
+---------+-----------+----------+
| CODD    | directeur | 11000.00 |
| JOUBERT | president | 10000.00 |
+---------+-----------+----------+

• R13 : Quels sont les employés gagnant plus que au moins un employé du département 20 ?

+--------------+
| min(salaire) |
+--------------+
|      3000.00 |
+--------------+

+----------+------------+----------+
| nom      | fonction   | salaire  |
+----------+------------+----------+
| GARDARIN | ingenieur  |  4800.00 |
| DELOBEL  | ingenieur  |  4200.00 |
| MARTIN   | directeur  |  8000.00 |
| MARTIN   | commercial |  4000.00 |
| CODD     | directeur  | 11000.00 |
| ADIBA    | ingenieur  |  6000.00 |
| JOUBERT  | president  | 10000.00 |
| LEFEBVRE | commercial |  4700.00 |
| DUPOND   | commercial |  5000.00 |
| SIMON    | ingenieur  |  4000.00 |
| LAMERE   | directeur  |  9000.00 |
| PAQUEL   | commercial |  4400.00 |
+----------+------------+----------+

Sous-interrogation ramenant plusieurs colonnes

Il est possible de comparer le résultat d’un SELECT ramenant plusieurs colonnes à une liste de colonnes. La liste de colonnes figurera entre parenthèses à gauche de l’opérateur de comparaison.

• R14 : Quels sont les employés ayant même fonction et même supérieur que CODD?

+--------+-----------+----------+---------------+
| nom    | fonction  | salaire  | idResponsable |
+--------+-----------+----------+---------------+
| MARTIN | directeur |  8000.00 |         25717 |
| CODD   | directeur | 11000.00 |         25717 |
| LAMERE | directeur |  9000.00 |         25717 |
+--------+-----------+----------+---------------+

Sous-interrogation synchronisée avec l’interrogation principale

Dans les exemples précédents, la sous-interrogation était évaluée d’abord, puis le résultat pouvait être utilisé pour exécuter l’interrogation principale.
SQL sait également traiter une sous-interrogation faisant référence à une colonne de la table de l’interrogation principale.
Le traitement dans ce cas est plus complexe car il faut évaluer la sous-interrogation pour chaque ligne de l’interrogation principale.

• R15 : Quels sont les employés ne travaillant pas dans le même département que leur supérieur hiérarchique ?

+--------+
| nom    |
+--------+
| CODD   |
| LAMERE |
+--------+

• IS NOT NULL est nécessaire car dans le cas de JOUBERT la colonne idResponsable est NULL et la sous-requête ne ramène alors aucune valeur.

Sous-interrogation ramenant au moins une ligne

L’opérateur EXISTS permet de construire un prédicat vrai si la sous-interrogation qui suit ramène au moins une ligne.

• R16 : Quels sont les employés travaillant dans un département qui a procédé à des embauches depuis le début de l’année 2005 ?

+----------+-----------+---------------+---------------+----------------+
| nom      | idEmploye | fonction      | date_embauche | departement_id |
+----------+-----------+---------------+---------------+----------------+
| LEBRETON |     16034 | commercial    | 2001-06-01    |             20 |
| MARTIN   |     16712 | directeur     | 2000-05-23    |             30 |
| MARTIN   |     17147 | commercial    | 2005-05-03    |             20 |
| DUPONT   |     17574 | administratif | 2005-05-03    |             30 |
| BARA     |     24831 | administratif | 1998-11-10    |             30 |
| LAMBERT  |     25012 | administratif | 2001-03-14    |             20 |
| JOUBERT  |     25717 | president     | 1992-08-10    |             30 |
| LEFEBVRE |     25935 | commercial    | 1994-01-11    |             20 |
| DUPOND   |     26691 | commercial    | 1998-04-04    |             20 |
| LAMERE   |     27047 | directeur     | 1999-09-07    |             20 |
| PAQUEL   |     27546 | commercial    | 1993-09-03    |             20 |
+----------+-----------+---------------+---------------+----------------+

Sous-interrogations multiples

Un SELECT peut comporter plusieurs sous-interrogations, soit imbriquées, soit au même niveau dans différents prédicats combinés par des AND ou des OR.

• R17 : Liste des employés du département 10 ayant même fonction que quelqu’un du département de DUPONT (le nom fini par un “T” et son département est 30).
  • Commencer par afficher le département de DUPONT
  • Afficher la liste des fonctions dans ce département

+-------+---------------+----------------+
| nom   | fonction      | departement_id |
+-------+---------------+----------------+
| BALIN | administratif |             10 |
| CODD  | directeur     |             10 |
+-------+---------------+----------------+

UNION (requête ensembliste)

• créer les 2 tables ci dessous et tester les requêtes

DROP TABLE IF EXISTS TD_CLIENT1,TD_CLIENT2;
CREATE TABLE TD_CLIENT1 (
    nom varchar(30)
);
INSERT INTO TD_CLIENT1 VALUES ('nom1');
INSERT INTO TD_CLIENT1 VALUES ('nom2');
INSERT INTO TD_CLIENT1 VALUES ('nom3');

CREATE TABLE TD_CLIENT2 (
    nom varchar(30)
);
INSERT INTO TD_CLIENT2 VALUES ('nom1');
INSERT INTO TD_CLIENT2 VALUES ('nom2');
INSERT INTO TD_CLIENT2 VALUES ('nom10');
INSERT INTO TD_CLIENT2 VALUES ('nom11');

SELECT nom FROM TD_CLIENT1;
SELECT nom FROM TD_CLIENT2;

SELECT nom FROM TD_CLIENT1
UNION
SELECT nom FROM TD_CLIENT2;

SELECT nom FROM TD_CLIENT1
UNION ALL
SELECT nom FROM TD_CLIENT2;

                             -- ==> ne fonctionne pas sur MYSQL mais sur ORACLE
SELECT nom FROM TD_CLIENT1
INTERSECT
SELECT nom FROM TD_CLIENT2;   -- (nom1 et nom2)

-- utiliser IN sur MYSQL

SELECT nom FROM TD_CLIENT1
WHERE nom IN
(SELECT nom FROM TD_CLIENT2);

        -- ==> ne fonctionne pas sur MYSQL mais sur ORACLE 
SELECT nom FROM TD_CLIENT1
EXCEPT              
SELECT nom FROM TD_CLIENT2; -- (nom3)

-- utiliser NOT IN sur MYSQL

SELECT nom FROM TD_CLIENT1
WHERE nom NOT IN
(SELECT nom FROM TD_CLIENT2);

• R18 : afficher tous les employés et tous les départements (nom fonction identifiant du département et nom du département) y compris ceux qui ont une valeur “identifiant de département” à NULL dans la table Employe ou les département qui n’ont pas d’employé grâce à une jointure à droite et à gauche

+-------------+--------+----------+-------------------+----------------+
| nom         | idDept | nom      | fonction          | departement_id |
+-------------+--------+----------+-------------------+----------------+
| recherche   |     10 | GARDARIN | ingenieur         |             10 |
....
| direction   |     30 | JOUBERT  | president         |             30 |
| fabrication |     40 | NULL     | NULL              |           NULL |
| NULL        |   NULL | toto     | livreur de pizzas |           NULL |
+-------------+--------+----------+-------------------+----------------+
19 rows in set (0.01 sec)

Les clés : “INDEX” ou “KEY”

Structure de données qui reprend la liste ordonnée des valeurs auxquelles il se rapporte. (exemple de définition)

Lorsque vous créez un index sur une table, MySQL stocke cet index sous forme d’une structure particulière, contenant les valeurs des colonnes impliquées dans l’index.

Création d’un index sur MySql

CREATE TABLE nom_table (
    colonne1 description_colonne1,
    [colonne2 description_colonne2,
    colonne3 description_colonne3,
    ...,]
    [PRIMARY KEY (colonne_clé_primaire)],
    [{INDEX|KEY} [nom_index] (colonne1_index [, colonne2_index, ...]],
    [UNIQUE [INDEX|KEY] [nom_index] (colonne1_index [, colonne2_index, ...]]
);

exemple :

SHOW CREATE TABLE TD_EMPLOYE;
ALTER TABLE TD_EMPLOYE ADD INDEX `departement_nom_index` (`nom`);
ALTER TABLE TD_EMPLOYE ADD INDEX `employe_date_embauche_index` (`date_embauche`);
SHOW CREATE TABLE TD_EMPLOYE;
SHOW INDEX FROM TD_EMPLOYE \G

Test de performance des INDEX

EXPLAIN SELECT TD_EMPLOYE.nom FROM TD_EMPLOYE WHERE fonction LIKE 'd%' AND date_embauche >='1985-11-12' \G


EXPLAIN
SELECT DISTINCT E1.nom 
  FROM TD_EMPLOYE E1, TD_DEPARTEMENT D1, TD_EMPLOYE E2, TD_DEPARTEMENT D2
WHERE E1.departement_id=D1.idDept 
       AND E2.departement_id=D2.idDept 
AND D1.nom='vente'
AND D2.nom='direction'
AND E1.date_embauche=E2.date_embauche
\G

réflexion sur l’optimisation des requêtes

• conseils optimisation
• conseils optimisation
• conseils optimisation
• interprétation EXPLAIN
• les variables d’environnement
• recherches sur la machine

• Tracer les requêtes

SHOW PROCESSLIST;
-- ouvrir 2 terminaux connectés à mysql
SET profiling = 1;

--exécuter une requête
SHOW PROFILE;

• Consulter les variables pour voir la charge du serveur (surveillance des bases de données, équilibrage des serveurs, supervision, graphique)

SHOW GLOBAL STATUS like "%used_connections";
show global status like 'opened_tables';
SELECT @@table_open_cache;
SELECT @@max_connections;

optimisation du code sur www.altidev.com/livres.php un article

Les VUES

Les vues permettent d’assurer l’objectif d’indépendance logique. Grâce à elles, chaque utilisateur pourra avoir sa vision propre des données.

On a vu que le résultat d’un SELECT est lui-même une table.
Une telle table, qui n’existe pas dans la base mais est créée dynamiquement lors de l’exécution du SELECT, peut être vue comme une table réelle par les utilisateurs. Pour cela, il suffit de cataloguer le SELECT en tant que vue.

Les utilisateurs pourront consulter la base, ou modifier la base (avec certaines restrictions) à travers la vue, c’est-à-dire manipuler la table résultat du SELECT comme si c’était une table réelle.

Créer une vue

La commande CREATE VIEW permet de créer une vue en spécifiant le SELECT constituant la définition de la vue :

CREATE VIEW nom_vue [(nom_col1,...)]
AS SELECT ...
[WITH CHECK OPTION] ;

La spécification des noms de colonnes de la vue est facultative. Par défaut, les noms des colonnes de la vue sont les mêmes que les noms des colonnes résultat du SELECT (si certaines colonnes résultat du SELECT sont des expressions, il faut renommer ces colonnes dans le SELECT, ou spécifier les noms de colonne de la vue).

Une fois créée, une vue s’utilise comme une table. Il n’y a pas de duplication des informations mais stockage de la définition de la vue.

Exemple : Création d’une vue constituant une restriction de la table TD_EMPLOYE aux employés du département 10.

CREATE VIEW v_emp10 AS
    SELECT *
    FROM TD_EMPLOYE
    WHERE departement_id = 10 ;

CREATE VIEW v_emp10co AS
    SELECT *
    FROM TD_EMPLOYE
    WHERE departement_id = 10 
WITH CHECK OPTION ;

-- pour les visualiser
SHOW TABLES;

Le CHECK OPTION permet de vérifier que la mise à jour ou l’insertion faite à travers la vue ne produisent que des lignes qui font partie de la sélection de la vue.

Ainsi donc, si la vue v_emp10co a été créée avec CHECK OPTION, on ne pourra à travers cette vue ni modifier, ni insérer des employés ne faisant pas partie du département 10.

• Il est possible d’effectuer des INSERT et des UPDATE à travers des vues, sous deux conditions :
  • Le SELECT définissant la vue ne doit pas comporter de jointure,
  • les colonnes résultat du SELECT doivent être des colonnes réelles et non pas des expressions.

Exemple : Modification des salaires du département 10 à travers la vue v_emp10.

SELECT * FROM v_emp10;
UPDATE v_emp10 SET salaire  = salaire * 1.1;
SELECT * FROM TD_EMPLOYE;

Toutes les lignes de la table TD_EMPLOYE, telles que le contenu de la colonne departement_id est égal à 10 seront modifiées.

• autre test :

SELECT * FROM v_emp10;
UPDATE v_emp10 SET departement_id=20 where idEmploye=15155;
SELECT * FROM v_emp10;
UPDATE v_emp10 SET departement_id=10 where idEmploye=15155;   
SELECT * FROM v_emp10;   
            --  on ne retrouve plus l'employe GARDARIN !

SELECT * FROM v_emp10co;
UPDATE v_emp10co SET departement_id=20 where idEmploye=26834;   
SELECT * FROM v_emp10co;
UPDATE v_emp10co SET departement_id=10 where idEmploye=26834;

Supprimer une vue

Une vue peut être détruite par la commande :

DROP  VIEW nom_vue;

Renommer une vue

On peut renommer une vue par la commande :

RENAME ancien_nom TO nouveau_nom;

DROP  VIEW  v_emp10,v_emp10co;

http://polymorphe.free.fr/cours/bd/sql/sql_avance/poly_39.html

https://perso.liris.cnrs.fr/fabien.duchateau/BDW1/

La suite : Supprimer et recréer les tables

Pour aller plus loin

• R19 : Donnez les salaire et le nom des employés gagnant plus que tous les ingénieurs

+---------+----------+
| nom     | salaire  |
+---------+----------+
| MARTIN  |  8000.00 |
| CODD    | 11000.00 |
| JOUBERT | 10000.00 |
| LAMERE  |  9000.00 |
+---------+----------+

• R20 : Donnez les noms des employés travaillant dans un département avec au moins un ingénieur mais qui ne sont pas ingénieur

+-------+-----------+---------------+
| nom   | nom       | fonction      |
+-------+-----------+---------------+
| BALIN | recherche | administratif |
| CODD  | recherche | directeur     |
+-------+-----------+---------------+

• R21 : Donnez les noms des employés du département “vente” embauchés le même jour qu’un employé du département “direction”

+--------+
| nom    |
+--------+
| MARTIN |
+--------+

• R22 : Donnez les noms des employés embauchés avant tous les employés du département “vente”.

+---------+
| nom     |
+---------+
| CODD    |
| JOUBERT |
+---------+

• R23 : Donnez le nom et la date d’embauche des employés embauchés avant leur responsable; donnez également le nom et la date d’embauche dudit responsable

+----------+---------------+---------+---------------+
| nom      | date_embauche | nom     | date_embauche |
+----------+---------------+---------+---------------+
| CODD     | 1985-11-12    | JOUBERT | 1992-08-10    |
| BARA     | 1998-11-10    | MARTIN  | 2000-05-23    |
| LEFEBVRE | 1994-01-11    | LAMERE  | 1999-09-07    |
| DUPOND   | 1998-04-04    | LAMERE  | 1999-09-07    |
| PAQUEL   | 1993-09-03    | LAMERE  | 1999-09-07    |
+----------+---------------+---------+---------------+

• R24 : Donner les noms des employés ayant le salaire maximum de chaque département.

+---------+----------+
| nom     | salaire  |
+---------+----------+
| CODD    | 11000.00 |
| JOUBERT | 10000.00 |
| LAMERE  |  9000.00 |
+---------+----------+

• R25 : Le salaire moyen le plus bas (par profession)?

+--------------+-------------------+--------------+
| AVG(salaire) | fonction          | min(salaire) |
+--------------+-------------------+--------------+
|  2500.000000 | administratif     |      1800.00 |
|  4220.000000 | commercial        |      3000.00 |
|  9333.333333 | directeur         |      8000.00 |
|  4750.000000 | ingenieur         |      4000.00 |
|         NULL | livreur de pizzas |         NULL |
| 10000.000000 | president         |     10000.00 |
+--------------+-------------------+--------------+

R26 : Donner les emplois ayant le salaire moyen le plus bas; donnez aussi leur salaire moyen.