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 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138
|
-- ---------------
DROP DATABASE IF EXISTS mon_blog;
-- Créer la base
-- -------------
CREATE DATABASE mon_blog CHARACTER SET 'utf8';
USE mon_blog;
-- Créer les tables
-- ----------------
CREATE TABLE Utilisateur
(id INT UNSIGNED AUTO_INCREMENT,
pseudo VARCHAR(40) NOT NULL,
email VARCHAR(40) NOT NULL,
mot_passe VARBINARY(16) NOT NULL,
PRIMARY KEY (id),
UNIQUE ind_uni_pseudo_email (pseudo, email),
FULLTEXT ind_pseudo (pseudo(20))
)
ENGINE=INNODB;
CREATE TABLE Categorie
(id INT UNSIGNED AUTO_INCREMENT,
nom VARCHAR(150) NOT NULL,
description TINYTEXT NOT NULL,
PRIMARY KEY (id),
FULLTEXT ind_nom (nom(20))
)
ENGINE=INNODB;
CREATE TABLE Article
(id INT UNSIGNED AUTO_INCREMENT,
art_date DATETIME,
titre VARCHAR(40) NOT NULL,
resume TINYTEXT NOT NULL,
art_texte TEXT NOT NULL,
art_util_id INT UNSIGNED NOT NULL,
art_cat_id INT UNSIGNED NOT NULL,
PRIMARY KEY (id),
FULLTEXT ind_titre (titre),
CONSTRAINT fk_art_utilisateur_id FOREIGN KEY (art_util_id) REFERENCES Utilisateur(id),
CONSTRAINT fk_art_categorie_id FOREIGN KEY (art_cat_id) REFERENCES Categorie(id)
)
ENGINE=INNODB;
CREATE TABLE Categorie_article
(cart_art_id INT UNSIGNED NOT NULL,
cart_cat_id INT UNSIGNED NOT NULL,
PRIMARY KEY (cart_art_id, cart_cat_id),
CONSTRAINT fk_catart_article_id FOREIGN KEY (cart_art_id) REFERENCES Article(id),
CONSTRAINT fk_catart_categorie_id FOREIGN KEY (cart_cat_id) REFERENCES Categorie(id)
)
ENGINE=INNODB;
CREATE TABLE Commentaire
(id INT UNSIGNED AUTO_INCREMENT,
com_date DATETIME,
com_texte TEXT NOT NULL,
com_art_id INT UNSIGNED NOT NULL,
com_util_id INT UNSIGNED,
PRIMARY KEY (id),
CONSTRAINT fk_comm_article_id FOREIGN KEY (com_art_id) REFERENCES Article(id),
CONSTRAINT fk_comm_utilisateur_id FOREIGN KEY (com_util_id) REFERENCES Utilisateur(id)
)
ENGINE=INNODB;
-- Alimenter la base
-- -----------------
INSERT INTO Utilisateur (id, pseudo, email, mot_passe)
VALUES (1,'Beaudelaire','beaudelaire@mail.fr','G15VB'),
(2,'Hugo','hugo@mail.fr','1F6E3'),
(3,'Camus','camus@mail.fr','VBN45'),
(4,'Laclos','laclos@mail.fr','D365E'),
(5,'Flaubert','flaubert@mail.fr','H57HN'),
(6,'Saint-Exupéry','saint-exupéry@mail.fr','AAA11');
INSERT INTO Categorie (id, nom, description)
VALUES (1,'Rouge','Soleil couchant'),
(2,'Orange','Fruit d''Espagne'),
(3,'Jaune','L''or en barre'),
(4,'Vert','Végétation au printemps'),
(5,'Bleu','Ciel du midi'),
(6,'Violet','Artichaut de Provence'),
(7,'Indigo','Pastel des teinturiers');
INSERT INTO Article (id, art_date, titre, resume, art_texte, art_util_id, art_cat_id)
VALUES (1,'2012/12/01 02:56','Titre_1','résumé_1','texte_1',1,3),
(2,'2014/03/10 17:01','Titre_2','résumé_2','texte_2',2,1),
(3,'2012/05/18 01:17','Titre_3','résumé_3','texte_3',3,5),
(4,'2014/08/13 22:14','Titre_4','résumé_4','texte_4',4,1),
(5,'2012/10/07 06:04','Titre_5','résumé_5','texte_5',5,3),
(6,'2013/05/05 12:56','Titre_6','résumé_6','texte_6',6,1),
(7,'2013/09/22 23:53','Titre_7','résumé_7','texte_7',6,1),
(8,'2013/02/04 05:42','Titre_8','résumé_8','texte_8',3,2),
(9,'2014/12/01 17:40','Titre_9','résumé_9','texte_9',1,1),
(10,'2013/08/18 01:57','Titre_10','résumé_10','texte_10',2,2),
(11,'2014/05/10 13:59','Titre_11','résumé_11','texte_11',2,2),
(12,'2013/11/26 07:55','Titre_12','résumé_12','texte_12',5,4),
(13,'2012/06/29 03:40','Titre_13','résumé_13','texte_13',5,1);
INSERT INTO Categorie_article (cart_cat_id, cart_art_id)
VALUES (3,1),(6,1),(1,2),(3,2),(6,2),(5,3),(6,3),(1,4),(3,5),(6,5),(1,6),(2,6),(1,7),(2,8),
(1,9),(2,9),(2,10),(3,10),(4,10),(2,11),(5,11),(6,11),(4,12),(5,12),(1,13),(6,13);
INSERT INTO Commentaire (id, com_date, com_texte, com_art_id, com_util_id)
VALUES (1,'2016/12/14 06:59','comm_1',12,1),
(2,'2014/09/02 11:41','comm_2',12,3),
(3,'2015/01/28 16:32','comm_3',1,3),
(4,'2016/03/29 00:03','comm_4',2,4),
(5,'2016/02/07 21:36','comm_5',2,6),
(6,'2016/12/22 10:33','comm_6',2,NULL);
-- Requêtes d'extraction de données
-- --------------------------------
SELECT A.art_date AS date_article, A.titre AS titre
FROM Categorie_article AS CA
INNER JOIN Article AS A ON CA.cart_art_id = A.id
INNER JOIN Categorie AS C ON CA.cart_cat_id = C.id
WHERE C.nom = 'rouge'
ORDER BY date_article DESC; |
Partager