La sentencia SELECT de MySQL se utiliza para recuperar datos de una o más tablas de la base de datos. Se puede utilizar para recuperar todas las filas y columnas de una tabla o sólo un subconjunto de ellas utilizando diferentes cláusulas como WHERE, ORDER BY, GROUP BY, JOIN, entre otras. La sintaxis básica de la sentencia SELECT es:
SELECT column1, column2, ...
FROM table_name;Donde "column1, column2, ..." son los nombres de las columnas que se desean recuperar de la tabla especificada en "table_name".
Para comprender mejor cómo funciona la sentencia SELECT en MySQL, a continuación te mostraré varios ejemplos utilizando la base de datos dbsales y la tabla employees con las siguientes columnas: employeeNumber, lastName, firstName, extension, email, officeCode, reportsTo y jobTitle.
Recuperar todos los datos de una tabla
Supongamos que queremos recuperar todos los datos de la tabla employees. Para lograr esto, simplemente escribimos la siguiente sentencia:
SELECT * FROM employees;Esta sentencia SELECT recuperará todos los datos de la tabla employees.
+----------------+-----------+-----------+-----------+---------------------------------+------------+-----------+----------------------+
| employeeNumber | lastName | firstName | extension | email | officeCode | reportsTo | jobTitle |
+----------------+-----------+-----------+-----------+---------------------------------+------------+-----------+----------------------+
| 1002 | Murphy | Diane | x5800 | dmurphy@classicmodelcars.com | 1 | NULL | President |
| 1056 | Patterson | Mary | x4611 | mpatterso@classicmodelcars.com | 1 | 1002 | VP Sales |
| 1076 | Firrelli | Jeff | x9273 | jfirrelli@classicmodelcars.com | 1 | 1002 | VP Marketing |
| 1088 | Patterson | William | x4871 | wpatterson@classicmodelcars.com | 6 | 1056 | Sales Manager (APAC) |
| 1102 | Bondur | Gerard | x5408 | gbondur@classicmodelcars.com | 4 | 1056 | Sale Manager (EMEA) |
| 1143 | Bow | Anthony | x5428 | abow@classicmodelcars.com | 1 | 1056 | Sales Manager (NA) |
| 1165 | Jennings | Leslie | x3291 | ljennings@classicmodelcars.com | 1 | 1143 | Sales Rep |
| 1166 | Thompson | Leslie | x4065 | lthompson@classicmodelcars.com | 1 | 1143 | Sales Rep |
| 1188 | Firrelli | Julie | x2173 | jfirrelli@classicmodelcars.com | 2 | 1143 | Sales Rep |
| 1216 | Patterson | Steve | x4334 | spatterson@classicmodelcars.com | 2 | 1143 | Sales Rep |
| 1286 | Tseng | Foon Yue | x2248 | ftseng@classicmodelcars.com | 3 | 1143 | Sales Rep |
| 1323 | Vanauf | George | x4102 | gvanauf@classicmodelcars.com | 3 | 1143 | Sales Rep |
| 1337 | Bondur | Loui | x6493 | lbondur@classicmodelcars.com | 4 | 1102 | Sales Rep |
| 1370 | Hernandez | Gerard | x2028 | ghernande@classicmodelcars.com | 4 | 1102 | Sales Rep |
| 1401 | Castillo | Pamela | x2759 | pcastillo@classicmodelcars.com | 4 | 1102 | Sales Rep |
| 1501 | Bott | Larry | x2311 | lbott@classicmodelcars.com | 7 | 1102 | Sales Rep |
| 1504 | Jones | Barry | x102 | bjones@classicmodelcars.com | 7 | 1102 | Sales Rep |
| 1611 | Fixter | Andy | x101 | afixter@classicmodelcars.com | 6 | 1088 | Sales Rep |
| 1612 | Marsh | Peter | x102 | pmarsh@classicmodelcars.com | 6 | 1088 | Sales Rep |
| 1619 | King | Tom | x103 | tking@classicmodelcars.com | 6 | 1088 | Sales Rep |
| 1621 | Nishi | Mami | x101 | mnishi@classicmodelcars.com | 5 | 1056 | Sales Rep |
| 1625 | Kato | Yoshimi | x102 | ykato@classicmodelcars.com | 5 | 1621 | Sales Rep |
| 1702 | Gerard | Martin | x2312 | mgerard@classicmodelcars.com | 4 | 1102 | Sales Rep |
+----------------+-----------+-----------+-----------+---------------------------------+------------+-----------+----------------------+
23 rows in set (0.00 sec)Recuperar datos específicos de una tabla
Para recuperar datos específicos de una tabla, se puede especificar las columnas que se desean recuperar después de la cláusula SELECT. Por ejemplo, si queremos recuperar los nombres y los salarios de todos los empleados, la sentencia SELECT sería la siguiente:
SELECT lastName,firstName,email FROM employees;Esta sentencia SELECT recuperará los datos de las columnas lastName, firstName y email de la tabla employees.Esta sentencia SELECT recuperará los datos de las columnas lastName, firstName y email de la tabla employees.
+-----------+-----------+---------------------------------+
| lastName | firstName | email |
+-----------+-----------+---------------------------------+
| Murphy | Diane | dmurphy@classicmodelcars.com |
| Patterson | Mary | mpatterso@classicmodelcars.com |
| Firrelli | Jeff | jfirrelli@classicmodelcars.com |
| Patterson | William | wpatterson@classicmodelcars.com |
| Bondur | Gerard | gbondur@classicmodelcars.com |
| Bow | Anthony | abow@classicmodelcars.com |
| Jennings | Leslie | ljennings@classicmodelcars.com |
| Thompson | Leslie | lthompson@classicmodelcars.com |
| Firrelli | Julie | jfirrelli@classicmodelcars.com |
| Patterson | Steve | spatterson@classicmodelcars.com |
| Tseng | Foon Yue | ftseng@classicmodelcars.com |
| Vanauf | George | gvanauf@classicmodelcars.com |
| Bondur | Loui | lbondur@classicmodelcars.com |
| Hernandez | Gerard | ghernande@classicmodelcars.com |
| Castillo | Pamela | pcastillo@classicmodelcars.com |
| Bott | Larry | lbott@classicmodelcars.com |
| Jones | Barry | bjones@classicmodelcars.com |
| Fixter | Andy | afixter@classicmodelcars.com |
| Marsh | Peter | pmarsh@classicmodelcars.com |
| King | Tom | tking@classicmodelcars.com |
| Nishi | Mami | mnishi@classicmodelcars.com |
| Kato | Yoshimi | ykato@classicmodelcars.com |
| Gerard | Martin | mgerard@classicmodelcars.com |
+-----------+-----------+---------------------------------+
23 rows in set (0.00 sec)