{"id":1786,"date":"2024-08-03T08:20:40","date_gmt":"2024-08-03T06:20:40","guid":{"rendered":"https:\/\/blog.sutilweb.eu\/?page_id=1786"},"modified":"2024-08-03T08:20:41","modified_gmt":"2024-08-03T06:20:41","slug":"14-consultas-multiples-y-fulltext-en-mysql","status":"publish","type":"page","link":"https:\/\/sutilweb.eu\/index.php\/lenguajes\/php\/poo-php\/14-consultas-multiples-y-fulltext-en-mysql\/","title":{"rendered":"14. Consultas m\u00faltiples y FullText en MySQL"},"content":{"rendered":"\n<p>Veamos la sintaxis para realizar consultas m\u00faltiples. Se recomienda crear un <strong>Alias<\/strong> a la tabla para crear un c\u00f3digo m\u00e1s legible.<\/p>\n\n\n\n<!--more-->\n\n\n\n<p><strong>Ejm1<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">SELECT * FROM table1 AS t1\n   INNER JOIN table2 AS t2;<\/pre>\n\n\n\n<p><strong>Ejm2<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">SELECT * FROM table1 AS t1\n   INNER JOIN table2 AS t2\n   ON t1.a_field = t2.a_field<\/pre>\n\n\n\n<p><strong>Ejm3<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">SELECT t1.field1, t1.field2, t1.field3, t2.fiel1, t2.field2\n   FROM table1 AS t1\n   INNER JOIN table2 AS t2\n   ON t1.field1 = t2.field5\n   WHERE t1.field1 = 'a_value'\n   ORDER BY t1.field3 DESC;<\/pre>\n\n\n\n<p>La cla\u00fasula <strong><em>INNER JOIN<\/em><\/strong> nos permite unir <strong>tablas<\/strong>. Si utilizamos el primer ejm, <strong>MySQL<\/strong> va a unir todos los campos de esas dos tablas, pudiendo duplicar los mismos, ya que no estamos especificando ninguna condici\u00f3n.<\/p>\n\n\n\n<p>Veamos un ejm utilizando nuestra base de datos.<\/p>\n\n\n\n<p><strong>Ejm<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">SELECT * FROM movies AS m INNER JOIN status AS s;<\/pre>\n\n\n\n<p>Esta sentencia duplicar\u00eda contenido ya que no le estamos poniendo ninguna restricci\u00f3n, para que \u00fanicamente crease un valor tenemos que escribir lo siguiente.<\/p>\n\n\n\n<p><strong>Ejm<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">SELECT * FROM movies AS m INNER JOIN status AS s ON m.status = s.status_id;<\/pre>\n\n\n\n<p>Cuando unimos dos tablas mediante los <strong>JOINS<\/strong> tenemos que utilizar la sintaxis <strong><em>tabla.registro<\/em><\/strong>.<\/p>\n\n\n\n<p><strong>Ejm<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">SELECT * FROM movies AS m INNER JOIN status AS s ON m.status = s.status_id WHERE s.status = 'Canceled' ORDER BY m.premiere;<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">FullText Key<\/h2>\n\n\n\n<p>En este caso la sintaxis es la siguiente.<\/p>\n\n\n\n<p><strong>Ejm<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">SELECT * FROM movies\n   WHERE MATCH(title, author, actors, genres)\n   AGAINST('ET' IN BOOLEAN MODE);<\/pre>\n\n\n\n<p>La funci\u00f3n <strong><em>MATCH()<\/em><\/strong> en <strong>MySQL<\/strong> lo que hace es unir en que <strong>campos<\/strong> queremos buscar, en nuestro ejm, los <strong>campos<\/strong> en los que queremos buscar es en <em>title<\/em>, <em>author<\/em>, <em>actors<\/em> y <em>genres<\/em>, y la cla\u00fasula <strong><em>AGAINST<\/em> <\/strong>hay que pasarle entre comillas el valor que estamos buscando, y las palabras reservadas <strong><em>IN BOOLEAN MODE<\/em><\/strong>.<\/p>\n\n\n\n<p>Tambi\u00e9n se pueden hacer consultas m\u00faltiples <strong>FullText Key<\/strong>, la sintaxis ser\u00eda la siguiente.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">SELECT t1.field1, t1.field2, t2.field1, t2.field2\n   FROM table1 AS t1\n   INNER JOIN table2 AS t2\n   ON t1.field1 = t2.field2\n   WHERE MATCH(t1.field1, t1.field2, t2.field1, t2.field2)\n   AGAINST('a_search' IN BOOLEAN MODE);<\/pre>\n\n\n\n<p>Como vemos, en el <strong><em>WHERE MATCH()<\/em><\/strong> ponemos los campos que necesitamos, utilizando nuestro <strong>JOIN<\/strong>.<\/p>\n\n\n\n<p><strong>Ejm<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">SELECT m.title, m.category, m.country, m.genres, m.premiere, s.status\n   FROM movies AS m\n   INNER JOIN status AS s\n   ON m.status = s.status_id\n   WHERE MATCH(m.title, m.author, m.actors, m.genres, s.status)\n   AGAINST('drama' IN BOOLEAN MODE)\n   ORDER BY m.premierre;<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Veamos la sintaxis para realizar consultas m\u00faltiples. Se recomienda crear un Alias a la tabla para crear un c\u00f3digo m\u00e1s [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":1735,"menu_order":13,"comment_status":"closed","ping_status":"closed","template":"","meta":{"_uag_custom_page_level_css":"","site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"class_list":["post-1786","page","type-page","status-publish","hentry"],"uagb_featured_image_src":{"full":false,"thumbnail":false,"medium":false,"medium_large":false,"large":false,"1536x1536":false,"2048x2048":false},"uagb_author_info":{"display_name":"Sutil Web","author_link":"https:\/\/sutilweb.eu\/index.php\/author\/sutilweb\/"},"uagb_comment_info":0,"uagb_excerpt":"Veamos la sintaxis para realizar consultas m\u00faltiples. Se recomienda crear un Alias a la tabla para crear un c\u00f3digo m\u00e1s [&hellip;]","_links":{"self":[{"href":"https:\/\/sutilweb.eu\/index.php\/wp-json\/wp\/v2\/pages\/1786","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sutilweb.eu\/index.php\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/sutilweb.eu\/index.php\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/sutilweb.eu\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sutilweb.eu\/index.php\/wp-json\/wp\/v2\/comments?post=1786"}],"version-history":[{"count":2,"href":"https:\/\/sutilweb.eu\/index.php\/wp-json\/wp\/v2\/pages\/1786\/revisions"}],"predecessor-version":[{"id":1788,"href":"https:\/\/sutilweb.eu\/index.php\/wp-json\/wp\/v2\/pages\/1786\/revisions\/1788"}],"up":[{"embeddable":true,"href":"https:\/\/sutilweb.eu\/index.php\/wp-json\/wp\/v2\/pages\/1735"}],"wp:attachment":[{"href":"https:\/\/sutilweb.eu\/index.php\/wp-json\/wp\/v2\/media?parent=1786"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}