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 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122
| --------------
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 `auteur`
--------------
--------------
CREATE TABLE `auteur`
( `id` integer unsigned NOT NULL auto_increment primary key,
`nom` varchar(255) NOT NULL,
`prenom` varchar(255) NOT NULL
) ENGINE=InnoDB
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
INSERT INTO `auteur` (`id`,`nom`,`prenom`) VALUES
(15, 'Mailly', 'Mireille'),
(19, 'Zanier', 'Luisa'),
(27, 'Agusta', 'Pierre')
--------------
--------------
select * from `auteur`
--------------
+----+--------+----------+
| id | nom | prenom |
+----+--------+----------+
| 15 | Mailly | Mireille |
| 19 | Zanier | Luisa |
| 27 | Agusta | Pierre |
+----+--------+----------+
--------------
DROP TABLE IF EXISTS `adresse`
--------------
--------------
CREATE TABLE `adresse`
( `id` integer unsigned NOT NULL,
`rang` integer unsigned NOT NULL,
`ligne` varchar(255) NOT NULL,
`postal` integer unsigned NOT NULL,
`ville` varchar(255) NOT NULL,
primary key (`id`,`rang`),
CONSTRAINT `FK_AUTEUR` FOREIGN KEY (`id`) REFERENCES `auteur` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
INSERT INTO `adresse` (`id`,`rang`,`ligne`,`postal`,`ville`) VALUES
(15, 1, 'x x x x x x x', 75014, 'Paris'),
(15, 2, 'y y y y y y y', 69000, 'Lyon'),
(15, 3, 'z z z z z z z', 13000, 'Marseille'),
(27, 1, '- - - - - - -', 06400, 'Cannes'),
(27, 2, '- - - - - - -', 06000, 'Nice'),
(27, 3, '- - - - - - -', 06500, 'Menton')
--------------
--------------
select * from `adresse`
--------------
+----+------+---------------+--------+-----------+
| id | rang | ligne | postal | ville |
+----+------+---------------+--------+-----------+
| 15 | 1 | x x x x x x x | 75014 | Paris |
| 15 | 2 | y y y y y y y | 69000 | Lyon |
| 15 | 3 | z z z z z z z | 13000 | Marseille |
| 27 | 1 | - - - - - - - | 6400 | Cannes |
| 27 | 2 | - - - - - - - | 6000 | Nice |
| 27 | 3 | - - - - - - - | 6500 | Menton |
+----+------+---------------+--------+-----------+
--------------
select t1.nom,
t1.prenom,
t2.ligne,
t2.postal,
t2.ville
from `auteur` as t1
left outer join `adresse` as t2
on t2.id = t1.id
and t2.rang = 1
--------------
+--------+----------+---------------+--------+--------+
| nom | prenom | ligne | postal | ville |
+--------+----------+---------------+--------+--------+
| Mailly | Mireille | x x x x x x x | 75014 | Paris |
| Zanier | Luisa | NULL | NULL | NULL |
| Agusta | Pierre | - - - - - - - | 6400 | Cannes |
+--------+----------+---------------+--------+--------+
--------------
COMMIT
--------------
--------------
SET AUTOCOMMIT = 1
--------------
Appuyez sur une touche pour continuer... |
Partager