1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91
|
--------------
SET AUTOCOMMIT = 0
--------------
--------------
START TRANSACTION
--------------
--------------
DROP DATABASE IF EXISTS `base`
--------------
--------------
CREATE DATABASE `base`
DEFAULT CHARACTER SET `latin1`
DEFAULT COLLATE `latin1_general_ci`
--------------
--------------
DROP TABLE IF EXISTS `employe`
--------------
--------------
CREATE TABLE `employe`
(
`id` integer unsigned NOT NULL AUTO_INCREMENT primary key,
`matricule` integer unsigned NOT NULL,
`nom` varchar(255) NOT NULL,
`prenom` varchar(255) NOT NULL,
`matriculeChef` integer unsigned NULL,
CONSTRAINT `FK_01` FOREIGN KEY (`matriculeChef`) REFERENCES `employe` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
INSERT INTO `employe` (`matricule`, `nom`,`prenom`) VALUES
(123, 'nom 1', 'prenom 1'),
(456, 'nom 2', 'prenom 2'),
(763, 'chef 3', 'prenom 3'),
(899, 'nom 4', 'prenom 4')
--------------
--------------
update `employe` set matriculeChef = 3 where matricule != 763
--------------
--------------
select * from employe
--------------
+----+-----------+--------+----------+---------------+
| id | matricule | nom | prenom | matriculeChef |
+----+-----------+--------+----------+---------------+
| 1 | 123 | nom 1 | prenom 1 | 3 |
| 2 | 456 | nom 2 | prenom 2 | 3 |
| 3 | 763 | chef 3 | prenom 3 | NULL |
| 4 | 899 | nom 4 | prenom 4 | 3 |
+----+-----------+--------+----------+---------------+
--------------
select tb1.matricule as 'matricule personnel',
tb1.nom as 'nom personnel',
tb1.prenom as 'prénom personnel',
tb2.matricule as 'matricule chef',
tb2.nom as 'nom chef',
tb2.prenom as 'prénom chef'
from employe as tb1
left outer join employe as tb2
on tb2.id = tb1.matriculeChef
--------------
+---------------------+---------------+------------------+----------------+----------+-------------+
| matricule personnel | nom personnel | prénom personnel | matricule chef | nom chef | prénom chef |
+---------------------+---------------+------------------+----------------+----------+-------------+
| 123 | nom 1 | prenom 1 | 763 | chef 3 | prenom 3 |
| 456 | nom 2 | prenom 2 | 763 | chef 3 | prenom 3 |
| 899 | nom 4 | prenom 4 | 763 | chef 3 | prenom 3 |
| 763 | chef 3 | prenom 3 | NULL | NULL | NULL |
+---------------------+---------------+------------------+----------------+----------+-------------+
--------------
COMMIT
--------------
--------------
SET AUTOCOMMIT = 1
--------------
Appuyez sur une touche pour continuer... |
Partager