{"id":248,"date":"2008-11-30T17:25:44","date_gmt":"2008-11-30T16:25:44","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=248"},"modified":"2008-12-10T00:55:23","modified_gmt":"2008-12-09T23:55:23","slug":"mysql-5-les-vues-part-37","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2008\/11\/30\/mysql-5-les-vues-part-37\/","title":{"rendered":"MySQL 5 : Les vues &#8212; (part 3\/7)"},"content":{"rendered":"<p><!-- \t \t --><\/p>\n<address>(<a title=\"MySQL 5 : Les vues -- (part 2\/7)\" href=\"http:\/\/dasini.net\/blog\/2008\/11\/26\/mysql-5-les-vues-part-27\/\">&lt;- pr\u00e9c\u00e9dent<\/a>)<\/address>\n<p><!-- \t \t --><\/p>\n<h3>Restrictions<\/h3>\n<p>Lors de la cr\u00e9ation d&rsquo;une vue, certaines contraintes doivent \u00eatre prises en compte :<\/p>\n<ul>\n<li>Il n&rsquo;est pas \tpossible de cr\u00e9er un index sur une vue<\/li>\n<li>La vue ne peut pas contenir de \tsous-requ\u00eates dans la clause FROM du SELECT.<\/li>\n<li>Il n&rsquo;est pas possible d&rsquo;utiliser \tde variables dans une vue.<\/li>\n<li>Les objets (tables et vues) \tn\u00e9cessaires \u00e0 la cr\u00e9ation de la vue doivent exister avant de la \tcr\u00e9er.<\/li>\n<li>Si un objet r\u00e9f\u00e9renc\u00e9 par la \tvue est effac\u00e9, la vue n&rsquo;est alors plus accessible.<\/li>\n<li>Une vue ne peut r\u00e9f\u00e9rencer une \ttable temporaire (TEMPORARY TABLE)<\/li>\n<li>Il n&rsquo;est pas possible de cr\u00e9er \tdes vues temporaires.<\/li>\n<li>Il n&rsquo;est pas possible d&rsquo;associer \tun trigger \u00e0 une vue.<\/li>\n<li>La d\u00e9finition d&rsquo;une vue est \t\u00ab\u00a0gel\u00e9e\u00a0\u00bb dans une requ\u00eate pr\u00e9par\u00e9e.<\/li>\n<\/ul>\n<p>Exemple:<\/p>\n<pre>mysql&gt; CREATE VIEW ma_vue AS SELECT 'premi\u00e8re valeur';\r\nQuery OK, 0 rows affected (0.24 sec)\r\n\r\nmysql&gt; desc ma_vue;\r\n+-----------------+-------------+------+-----+---------+-------+\r\n| Field           | Type        | Null | Key | Default | Extra |\r\n+-----------------+-------------+------+-----+---------+-------+\r\n| premi\u00e8re valeur | varchar(15) | NO   |     |         |       |\r\n+-----------------+-------------+------+-----+---------+-------+\r\n1 row in set (0.50 sec)\r\n\r\nmysql&gt; PREPARE req_prepare FROM 'SELECT * FROM ma_vue';\r\nQuery OK, 0 rows affected (0.00 sec)\r\nStatement prepared\r\n\r\nmysql&gt; EXECUTE req_prepare;\r\n+-----------------+\r\n| premi\u00e8re valeur |\r\n+-----------------+\r\n| premi\u00e8re valeur |\r\n+-----------------+\r\n1 row in set (0.01 sec)\r\n\r\nALTER VIEW ma_vue AS SELECT 'deuxi\u00e8me valeur';\r\nQuery OK, 0 rows affected (0.05 sec)\r\n\r\nmysql&gt; desc ma_vue;\r\n+-----------------+-------------+------+-----+---------+-------+\r\n| Field           | Type        | Null | Key | Default | Extra |\r\n+-----------------+-------------+------+-----+---------+-------+\r\n| deuxi\u00e8me valeur | varchar(15) | NO   |     |         |       |\r\n+-----------------+-------------+------+-----+---------+-------+\r\n1 row in set (0.00 sec)\r\n\r\nmysql&gt; EXECUTE req_prepare;\r\n+-----------------+\r\n| premi\u00e8re valeur |\r\n+-----------------+\r\n| premi\u00e8re valeur |\r\n+-----------------+\r\n1 row in set (0.00 sec)<\/pre>\n<p>Il faut en fait recr\u00e9er la requ\u00eate pr\u00e9par\u00e9e :<\/p>\n<pre>mysql&gt; DEALLOCATE PREPARE req_prepare;\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nmysql&gt; PREPARE req_prepare FROM 'SELECT * FROM ma_vue';\r\nQuery OK, 0 rows affected (0.00 sec)\r\nStatement prepared\r\n\r\nmysql&gt; EXECUTE req_prepare;\r\n+-----------------+\r\n| deuxi\u00e8me valeur |\r\n+-----------------+\r\n| deuxi\u00e8me valeur |\r\n+-----------------+\r\n1 row in set (0.00 sec)<\/pre>\n<p><em>(<a title=\"MySQL 5 : Les vues -- (part 4\/7)\" href=\"http:\/\/dasini.net\/blog\/2008\/12\/08\/mysql-5-les-vues-part-47\/\">\u00e0 suivre\u2026 utiliser les vues<\/a>)<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Restrictions<\/p>\n<p>Lors de la cr\u00e9ation d&rsquo;une vue, certaines contraintes doivent \u00eatre prises en compte :<\/p>\n<p>    *      Il n&rsquo;est pas possible de cr\u00e9er un index sur une vue<br \/>\n    *      La vue ne peut pas contenir de sous-requ\u00eates dans la clause FROM du SELECT.<br \/>\n    *      Il n&rsquo;est pas possible d&rsquo;utiliser de variables dans une vue.<br \/>\n    *      Les objets (tables et vues) n\u00e9cessaires \u00e0 la cr\u00e9ation de la vue doivent exister avant de la cr\u00e9er.<br \/>\n    *      Si un objet r\u00e9f\u00e9renc\u00e9 par la vue est effac\u00e9, la vue n&rsquo;est alors plus accessible.<br \/>\n    *      Une vue ne peut r\u00e9f\u00e9rencer une table temporaire (TEMPORARY TABLE)<br \/>\n    *      Il n&rsquo;est pas possible de cr\u00e9er des vues temporaires.<br \/>\n    *      Il n&rsquo;est pas possible d&rsquo;associer un trigger \u00e0 une vue.<br \/>\n    *      La d\u00e9finition d&rsquo;une vue est \u00ab\u00a0gel\u00e9e\u00a0\u00bb dans une requ\u00eate pr\u00e9par\u00e9e.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"footnotes":""},"categories":[8],"tags":[12,64,58],"class_list":["post-248","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-mysql-5","tag-prepare-statement","tag-vue"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9LfWW-40","jetpack-related-posts":[{"id":209,"url":"https:\/\/dasini.net\/blog\/2008\/11\/26\/mysql-5-les-vues-part-27\/","url_meta":{"origin":248,"position":0},"title":"MySQL 5 : Les vues &#8212; (part 2\/7)","author":"Olivier DASINI","date":"26 novembre 2008","format":false,"excerpt":"Syntaxe d'une vue CREATE VIEW La commande MySQL pour cr\u00e9er une vue est assez proche de la syntaxe du standard SQL. CREATE VIEW nom_de_la_vue AS requ\u00eate_select","rel":"","context":"Dans &quot;MySQL&quot;","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":192,"url":"https:\/\/dasini.net\/blog\/2008\/11\/23\/mysql-5-les-vues-part-17\/","url_meta":{"origin":248,"position":1},"title":"MySQL 5 : Les vues &#8212; (part 1\/7)","author":"Olivier DASINI","date":"23 novembre 2008","format":false,"excerpt":"Le langage SQL acronyme de Structured Query Language (Langage Structur\u00e9 de Requ\u00eates), a \u00e9t\u00e9 con\u00e7u pour g\u00e9rer les donn\u00e9es dans un SGBDR. A l'aide des DML (Data Manipulation Language ie les requ\u00eates SELECT, INSERT, UPDATE, DELETE) il est possible de manipuler ces donn\u00e9es qui sont stock\u00e9es dans des tables. SQL\u2026","rel":"","context":"Dans &quot;MySQL&quot;","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":14,"url":"https:\/\/dasini.net\/blog\/2008\/10\/28\/mysql-50-un-sgbdr-mature-part-24\/","url_meta":{"origin":248,"position":2},"title":"MySQL 5.0 : Un SGBDR mature ? &#8212; (part 2\/4)","author":"Olivier DASINI","date":"28 octobre 2008","format":false,"excerpt":"Les vues sont la plupart du temps utiles pour donner aux utilisateurs l\u2019acc\u00e8s \u00e0 un ensemble de relations repr\u00e9sent\u00e9es sous la forme d'une table. Une vue est une table virtuelle ; les donn\u00e9es de la vue sont en fait des champs de diff\u00e9rentes tables regroup\u00e9es, ou des r\u00e9sultats d\u2019op\u00e9rations sur\u2026","rel":"","context":"Dans &quot;MySQL&quot;","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":403,"url":"https:\/\/dasini.net\/blog\/2009\/01\/13\/mysql-5-les-vues-part-77\/","url_meta":{"origin":248,"position":3},"title":"MySQL 5 : Les vues &#8212; (part 7\/7)","author":"Olivier DASINI","date":"13 janvier 2009","format":false,"excerpt":"Conserver la structure d'une table si elle doit \u00eatre modifi\u00e9e La probl\u00e9matique est de mettre \u00e0 jour le sch\u00e9ma de l'application en changeant la structure de certaines tables.","rel":"","context":"Dans &quot;MySQL&quot;","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":350,"url":"https:\/\/dasini.net\/blog\/2008\/12\/19\/mysql-5-les-vues-part-57\/","url_meta":{"origin":248,"position":4},"title":"MySQL 5 : Les vues &#8212; (part 5\/7)","author":"Olivier DASINI","date":"19 d\u00e9cembre 2008","format":false,"excerpt":"Masquer la complexit\u00e9 du sch\u00e9ma L'\u00e9quipe de d\u00e9veloppement doit \u00e9crire un moteur de recherche pour une application de commerce \u00e9lectronique. Voici un extrait des tables de la base de donn\u00e9es impliqu\u00e9es dans la recherche des produits du site.","rel":"","context":"Dans &quot;MySQL&quot;","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":622,"url":"https:\/\/dasini.net\/blog\/2009\/06\/16\/le-programmateur-devenements-event-scheduler-part-46\/","url_meta":{"origin":248,"position":5},"title":"Le programmateur d&rsquo;\u00e9v\u00e9nements ( Event Scheduler ) (part 4\/6)","author":"Olivier DASINI","date":"16 juin 2009","format":false,"excerpt":"Insertions de donn\u00e9es dans une table, toutes les 30 secondes mysql> SELECT * FROM _event.insert_event ; mysql> DELIMITER \u20ac\u20ac mysql> CREATE EVENT `_event`.`second_event` ON SCHEDULE EVERY 30 SECOND DO BEGIN \/*selectionne la val maximale de la colonne fois. Si elle vaut NULL alors retourne la valeur 0 dans la variable\u2026","rel":"","context":"Dans &quot;MySQL&quot;","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/248","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/comments?post=248"}],"version-history":[{"count":11,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/248\/revisions"}],"predecessor-version":[{"id":254,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/248\/revisions\/254"}],"wp:attachment":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/media?parent=248"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/categories?post=248"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/tags?post=248"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}