{"id":532,"date":"2023-10-20T10:35:50","date_gmt":"2023-10-20T08:35:50","guid":{"rendered":"http:\/\/eines-informatiques.recursos.uoc.edu\/gestion-de-datos\/?page_id=532"},"modified":"2025-06-13T10:44:51","modified_gmt":"2025-06-13T08:44:51","slug":"1-8-consultes-basiques-filtres-i-condicions","status":"publish","type":"page","link":"http:\/\/eines-informatiques.recursos.uoc.edu\/gestion-de-datos\/1-8-consultes-basiques-filtres-i-condicions\/","title":{"rendered":"1.8. Consultes b\u00e0siques: filtres i condicions"},"content":{"rendered":"

L\u2019ordre SELECT<\/code> permet tamb\u00e9 extreure de les taules \u00fanicament aquells registres que posseeixen certes propietats. Per especificar el filtre a realitzar sobre el contingut d\u2019una taula, cal afegir-hi la cl\u00e0usula WHERE<\/code>:<\/p>\n

SELECT camp1, camp2,..., campn FROM taula WHERE condicio;<\/pre>\n
La condici\u00f3 pot ser simple o composta, avaluant-se sobre un o m\u00e9s atributs de diverses taules. Els operadors de comparaci\u00f3 m\u00e9s habituals es mostren a la taula 3.<\/p>\n
Taula 3. Operadors de comparaci\u00f3 en consultes de MySQL.<\/p>\n<\/div>\n\n\n\n\n\n\n\n\n\n\n
\u00a0Operador<\/strong><\/td>\n\u00a0Significat<\/strong><\/td>\n<\/tr>\n
=,<><\/code><\/td>\nIgual\/diferent<\/td>\n<\/tr>\n
<,><\/code><\/td>\nMenor\/major<\/td>\n<\/tr>\n
<=,>=<\/code><\/td>\nMenor\/major o igual<\/td>\n<\/tr>\n
LIKE<\/code><\/td>\nRecerca d\u2019un patr\u00f3 de text<\/td>\n<\/tr>\n
NOT<\/code><\/td>\nNegaci\u00f3 d\u2019una condici\u00f3<\/td>\n<\/tr>\n
AND\/OR<\/code><\/td>\nCondicions combinades<\/td>\n<\/tr>\n
REGEXP<\/code><\/td>\nExpressi\u00f3 regular<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Font: elaboraci\u00f3 pr\u00f2pia.<\/p>\n<\/div>\n
Els operadors num\u00e8rics tamb\u00e9 resulten molt \u00fatils per buscar registres en un rang concret de dates del calendari.<\/p>\n<\/div>\n
Provarem aquests operadors per realitzar consultes m\u00e9s concretes sobre la nostra base de dades cataleg<\/strong>. En primer lloc, podem interrogar la base de dades sobre els gens ubicats en el fil positiu de la cadena d\u2019ADN en qualsevol esp\u00e8cie, preguntar per aquells que no pertanyen a la nostra esp\u00e8cie o buscar els gens anotats abans del primer mili\u00f3 de bases en qualsevol cromosoma:<\/p>\n
mysql> SELECT * FROM gens WHERE cadena LIKE '+';\r\n\r\n+-------+-----------+--------+-----------+-----------+-----------+-----------------+\r\n|  nom  | cromosoma | cadena |   inici   |   final   | proteina  |     especie     |\r\n+------+------------+--------+-----------+-----------+-----------+-----------------+\r\n| ash2  | chr3R     |  +     |  20477248 |  20479098 | NP_733023 | D. melanogaster |\r\n| HNF1A | chr12     |  +     | 121416548 | 121440312 | NP_000536 | H. sapiens      |\r\n| MYC   | chr8      |  +     | 128748314 | 128753678 | NP_002458 | H. sapiens      |\r\n+-------+-----------+--------+-----------+-----------+-----------+-----------------+\r\n3 rows in set (0.00 sec)\r\n\r\nmysql> SELECT * FROM gens WHERE especie NOT LIKE 'H. sapiens';\r\n\r\n+-------+-----------+--------+-----------+-----------+-----------+-----------------+\r\n|  nom  | cromosoma | cadena |   inici   |   final   | proteina  |     especie     |\r\n+------+------------+--------+-----------+-----------+-----------+-----------------+\r\n| ash2  | chr3R     |  +     |  20477248 |  20479098 | NP_733023 | D. melanogaster |\r\n| cbt   | chr2L     |  -     |    476437 |    479046 | NP_722636 | D. melanogaster |\r\n+-------+-----------+--------+-----------+-----------+-----------+-----------------+\r\n2 rows in set (0.00 sec)\r\n\r\nmysql> SELECT * FROM gens WHERE inici <= 1000000;\r\n\r\n+-------+-----------+--------+---------+-----------+-----------+-----------------+\r\n|  nom  | cromosoma | cadena |  inici  |   final   | proteina  |     especie     |\r\n+------+------------+--------+---------+-----------+-----------+-----------------+\r\n| cbt   | chr2L     |  -     | 476437  |  479046   | NP_722636 | D. melanogaster |\r\n+-------+-----------+--------+---------+-----------+-----------+-----------------+\r\n1 row in set (0.00 sec)\r\n<\/pre>\n
La cl\u00e0usula es pot complementar amb el modificador %, que actua de comod\u00ed en les expressions alfanum\u00e8riques. A continuaci\u00f3, seleccionem nom\u00e9s registres que pertanyen al genoma de la mosca:<\/p>\n
mysql> SELECT * FROM gens WHERE especie LIKE '%melano%';\r\n\r\n+------+-----------+--------+-----------+----------+------------+-----------------+\r\n| nom  | cromosoma | cadena |   inici   |   final  |  proteina  |     especie     |\r\n+------+-----------+--------+-----------+----------+------------+-----------------+\r\n| ash2 | chr3R     | +      | 20477248  | 20479098 | NP_733023  | D. melanogaster |\r\n| cbt  | chr2L     | -      |   476437  |   479046 | NP_722636  | D. melanogaster |\r\n+------+-----------+--------+-----------+----------+------------+-----------------+\r\n2 rows in set (0.00 sec)\r\n<\/pre>\n
Tamb\u00e9 podem combinar preguntes sobre valors de diferents tipus. Per exemple, si desitgem esbrinar quants gens de la mosca de la fruita estan anotats al fil positiu de la cadena d\u2019ADN:<\/p>\n
mysql> SELECT * FROM gens WHERE especie LIKE '%melano%' \r\n    -> AND cadena LIKE '+';\r\n\r\n+------+-----------+--------+----------+----------+-----------+-----------------+\r\n| nom  | cromosoma | cadena |   inici  |   final  | proteina  |     especie     |\r\n+------+-----------+--------+----------+----------+-----------+-----------------+\r\n| ash2 | chr3R     | +      | 20477248 | 20479098 | NP_733023 | D. melanogaster |\r\n+------+-----------+--------+----------+----------+-----------+-----------------+\r\n1 row in set (0.00 sec)<\/pre>\n","protected":false},"excerpt":{"rendered":"
L\u2019ordre SELECT permet tamb\u00e9 extreure de les taules \u00fanicament aquells registres que posseeixen certes propietats. Per especificar el filtre a realitzar sobre el contingut d\u2019una taula, cal afegir-hi la cl\u00e0usula WHERE: SELECT camp1, camp2,…, campn FROM taula WHERE condicio; La condici\u00f3 pot ser simple o composta, avaluant-se sobre un o m\u00e9s atributs de diverses taules. […]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":[],"acf":[],"_links":{"self":[{"href":"http:\/\/eines-informatiques.recursos.uoc.edu\/gestion-de-datos\/wp-json\/wp\/v2\/pages\/532"}],"collection":[{"href":"http:\/\/eines-informatiques.recursos.uoc.edu\/gestion-de-datos\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"http:\/\/eines-informatiques.recursos.uoc.edu\/gestion-de-datos\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"http:\/\/eines-informatiques.recursos.uoc.edu\/gestion-de-datos\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/eines-informatiques.recursos.uoc.edu\/gestion-de-datos\/wp-json\/wp\/v2\/comments?post=532"}],"version-history":[{"count":11,"href":"http:\/\/eines-informatiques.recursos.uoc.edu\/gestion-de-datos\/wp-json\/wp\/v2\/pages\/532\/revisions"}],"predecessor-version":[{"id":947,"href":"http:\/\/eines-informatiques.recursos.uoc.edu\/gestion-de-datos\/wp-json\/wp\/v2\/pages\/532\/revisions\/947"}],"wp:attachment":[{"href":"http:\/\/eines-informatiques.recursos.uoc.edu\/gestion-de-datos\/wp-json\/wp\/v2\/media?parent=532"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}