mysql 13.2.7. select syntax

8/4/2019 Mysql 13.2.7. Select Syntax

1/29

El funcionamiento de SELECT

Ahora que hemos visto unos cuantos queries, podemos preguntarnos cmo funciona SELECT. Es un proceso formado por varias etapas sucesivas :

* FROM: Aqui se realizan los JOINS o un subquery.* WHERE: De las filas resultantes del FROM se separan las que cumplen las co

ndiciones especificadas en el WHERE.* GROUP BY: Las filas se ordenan y agrupan segn las columnas especificadas.* SELECT : Ahora a partir de las filas que han resultado de los pasos anteri

ores, se construyen otras segn las expresiones que figuren en el SELECT. Si hay un GROUP BY slo se construye una fila por cada grupo de filas del GROUP BY. Cuidado :en caso que haya valores distintos de una columna seleccionada dentro de un grupo, se selecciona un valor cualquiera al azar.

* Si hay un DISTINCT, se eliminan las duplicaciones de filas.* HAVING: Vuelven a descartarse filas que no cumplan con la condicin de esta

clusula.* ORDER BY : Se ordenan las filas, por el criterio indicado.* LIMIT : Si hay ms filas de las indicadas por esta clusula, se descartan.

* UNION : Se unen los resultados de varios queries. Puede haber un ORDER BYy un LIMIT despus del UNION.

- Nota final sobre eficiencia: Para bases de datos chicas, con algunos miles defilas, normalmente no hay grandes problemas de eficiencia. Con todo, si algn query es muy lento, eso no quiere decir que el problema sea intrnsecamente demasiadocomplicado para el computador. Los tiempos en que se ejecutan los queries varan muchsimo segn los detalles de la base de datos y los detalles de los queries mismos. O sea que si hay problemas de velocidad, es recomendable consultar con un programador con experiencia en SQL.

GlosarioComo el SQL es a menudo comprensible an para personas que no lo han estudiado, pero que hablan ingls, parece til tener un pequeo diccionario.

Trmino DescripcinALL Todos.ANY Cualquier.AS Como.ANY Cualquier.AVG Abreviatura de AVERAGE, promedio.COUNT Contar.CURDATE() Funcin SQL. Abreviatura de CURRENT DATE, fecha de hoy.

DATE Fecha.DATE_ADD() Funcin SQL. Literalmente, fecha sumar.DATE_SUB() Funcin SQL. Literalmente, fecha restar.DAY Da.DAYNAME() Funcin SQL, de DAY, da y NAME, nombre.DAYOFWEEK() Funcin SQL, concatenacin de DAY OF WEEK, da de la semana.DESC Abreviatura de 'descending' , que puede traducirse como 'en sentido descendiente'. Se refiere a ordenar en sentido descendente.DISTINCT Distintos.EXISTS Existe.FROM De.IN En.IS Es.

JOIN Literalmente unin, zona de contacto. En SQL, representa una manera especial de unir dos tablas. Matemticamente, es un subconjunto de un producto cartesiano..


2/29

LEFT JOIN Expresin usada en SQL. Literalmente, unin izquierda.LIKE Parecido a.MAX Abreviatura de MAXIMUM, mximo.MIN Abreviatura de MINIMUM, mnimo.MONTH Mes.NAME Nombre.NOT No.

NOT IN No en, no pertenece a.NULL NULO, pero en un sentido especial, no se refiere al nmero 0, sino a un valor desconocido.ON Sobre. En SQL indica una condicin en un JOIN.ORDER BY Ordenar por.RIGHT JOIN Expresin usada en SQL. Literalmente, unin derecha.RIGHT() Funcion SQL. Right significa derecha.SELECT Seleccionar.SHOW Mostrar, usado en SHOW TABLES (Mostrar tablas) y SHOW DATABASES (Mostrarbases de datos).SUM Sumar.TABLE Tabla.

UNION Unin.WHERE Donde.YEAR Ao.

SubqueriesSubqueries escalares

Ya antes, en la seccin 4, usamos subqueries escalares. Un subquery escalar produce un slo valor, que se usa en una condicin WHERE o HAVING.

Ejemplo: Listar los empleados que tienen salarios mayores que la mitad del salario ms alto.

SELECT * FROM EmpleadosWHERE Salario > (SELECT MAX(Salario) FROM Empleados)/2;

IN y NOT INUna manera de preguntar si un valor si el valor de una columna est en una lista es usando IN.

Ejemplo: Listar las ventas efectuadas por los empleados con cdigo 1,2.

SELECT * FROM VentasWHERE Vendedor IN (1,2);

La lista que figura despus de un IN puede obtenerse de un subquery.

SELECT * FROM ClientesWHERE Id IN (SELECT Cliente FROM Ventas);

Este query lista los Clientes a los cules se les han vendido algo.

NOT IN es la negacin de IN. Si queremos la lista de los clientes a los que no seles han vendido nada en los ltimos 30 dgas, escribimos

SELECT * FROM Clientes

WHERE Id NOT IN (SELECT Cliente FROM VentasWHERE Fecha>=DATE_SUB(CURDATE(),


3/29

INTERVAL 30 DAY));

ALL y ANYPodemos seleccionar filas que cumplen una condicin con todas las filas de un subquery.

Ejemplo: Qu conformes se vencieron despus de la ltima factura?

SELECT * FROM ConformesWHERE Vencimiento > ALL (SELECT Fecha FROM Facturas);

Tambin podemos seleccionar filas que cumplen una condicin con alguna de las filasde un subquery.

Ejemplo: Qu conformes se vencieron antes de la primera factura?

SELECT * FROM ConformesWHERE Vencimiento < ANY (SELECT Fecha FROM Facturas);

Nota: Se recomienda tener cuidado con el uso de ALL. Puede producir inestabilidad del sistema si la columna que figura antes del ALL se escribe incorrectamentey por lo tanto no existe.

Subqueries correlacionadosUn subquery se llama correlacionado cuando hace referencia a una tabla que no figura en su clusula FROM (aunque s debe figurar en un query que lo contiene).

Ejemplo: Hacer una tabla de las ventas cuyo importe es mximo dentro del artculo considerado.

SELECT Articulo, Importe FROM Ventas V1

WHERE Importe = (SELECT MAX(Importe) FROM Ventas V2WHERE V2.Articulo = V1.Articulo)

ORDER BY Articulo;

Claro que sera ms sencillo, en este caso, escribir

SELECT Articulo, MAX(Importe) AS MaximoFROM VentasGROUP BY Articulo;

Los subqueries correlacionados tienden a ser ineficientes con tablas grandes, aunque a menudo MySQL reformula internamente el query y lo hace eficiente.

EXISTS y NOT EXISTSA menudo lo nico que interesa de un subquery correlacionado es saber si tiene alguna fila o no. Para eso tenemos EXISTS y NOT EXISTS.

Por ejemplo, si queremos seleccionar todas las Sucursales en las que se hizo alguna venta,

SELECT * FROM SucursalesWHERE EXISTS (SELECT 1 FROM Ventas

WHERE Sucursal = Sucursales.Id);

El SELECT 1 es para enfatizar que no nos interesa el contenido de las filas delsubquery sino si existen o no. Si ejecutamos el subquery por s mismo, con un valor apropiado en vez de Sucursales.Id, obtenemos una columnas de unos. Esta tabla


4/29

tiene tantas filas como filas de Ventas hay con la Sucursal indicada.

En este caso tambin hubiese podido evitarse el subquery correlacionado con :

SELECT DISTINCT Sucursales.*FROM Sucursales

JOIN

Ventas ON Sucursales.Id = Ventas.Sucursal;

Si queremos las Sucursales donde no se hizo una venta, podemos usar

SELECT * FROM SucursalesWHERE NOT EXISTS (SELECT 1 FROM Ventas

WHERE Sucursal = Sucursales.Id);

o tambin

SELECT DISTINCT Sucursales.*FROM Sucursales

LEFT JOINVentas ON Sucursales.Id = Ventas.SucursalWHERE Ventas.Id IS NULL;

Problemas resueltos

1. Listar las Ventas que hayan sido facturadas;Solucin:

SELECT * FROM VentasWHERE Id IN (SELECT Venta

FROM Lineas_Factura);

2. Listar las Ventas que falte facturar.Solucin:

SELECT * FROM VentasWHERE Id NOT IN (SELECT Venta

FROM Lineas_Factura);

3. Ventas posteriores a la ltima factura.Solucin:

SELECT * FROM VentasWHERE Fecha > ALL (SELECT Fecha FROM Facturas);

Es equivalente a

SELECT * FROM VentasWHERE Fecha > (SELECT MAX(Fecha )

FROM Facturas);

De hecho, aunque se solicite el primer query, MySQL, internamente, ejecutael 2., que es mucho ms eficiente.

4. Artculos que no se han vendido, para cada regin, en el ltimo ao.Solucin:

SELECT Articulos.Nombre, Sucursales.DescripcionFROM Articulos, Sucursales


5/29

WHERE NOT EXISTS (SELECT 1 FROM VentasWHERE Sucursal = Sucursales.Id

AND Articulo = Articulos.IdAND Fecha > DATE_SUB(CURDATE(), INTERVAL

365 DAY))ORDER BY 1, 2;

Este es un ejemplo de un subquery correlacionado. En efecto, dos columnasdel query exterior, Sucursales.Id y Articulos.Id, figuran en el subquery. MySQLva combinando todas las Sucursales con todos los artculos y seleccionando aquellas combinaciones para las cuales no hay ventas correspondientes.

El SELECT 1 del subquery no tiene importancia, lo que realmente hace MySQLes averiguar si hay alguna fila de Ventas que cumpla la clusula WHERE, para darle un valor al NOT EXISTS. Si hay una fila que cumple el WHERE Sucursal..., entonces el NOT EXISTS es falso, etc.

El SELECT 1 puede sustituirse por cualquier otro SELECT, que quizs resultems intuitivo. El funcionamiento ser el mismo.

5. Clientes a los que se les han hecho menos de tres ventas en los ltimos 365das.Solucin:

SELECT * FROM ClientesWHERE 3 > (SELECT COUNT(*)

FROM VentasWHERE Fecha > DATE_SUB(CURDATE(), INTERVAL 365 DAY)

AND Cliente=Clientes.Id);

Otro ejemplo de subquery correlacionado. El subquery calcula el nmero de ventas que se le ha hecho a cada cliente en el ltimo ao. Cuando el nmero es menor que3, entonces se selecciona la fila de clientes.

6. Artculos que se han vendido en todas las sucursales.Solucin:

SELECT NombreFROM ArticulosWHERE NOT EXISTS( SELECT 1 FROM Sucursales

WHERE NOT EXISTS (SELECT 1FROM VentasWHERE Sucursal=Sucursales.IdAND Articulo=Articulos.Id))

ORDER BY 1;

Lo que se trata de lograr podra expresarse as :

SELECCIONAR NombreDE ArticulosCUANDO PARA TODA SucursalEXISTE Venta de Articulo en Sucursal.

El problema es " CUANDO PARA TODA Sucursal..." que no tiene una traduccin directa a MySQL, ni a ningn SQL que yo conozca. Pero esta expresin es equivalente a"NO EXISTE Sucursal TAL QUE NO...". Es decir, afirmar que algo se cumple para todas las Sucursales equivale a afirmar que no existe una regin para la que no secumpla. Resultara :

SELECCIONAR NombreDE Articulos


6/29

CUANDO NO EXISTE Sucursal TAL QUENO EXISTE Venta de Articulo en Sucursal.

El query es traduccin directa a MySQL de este seudocdigo.

7. Vendedores que hayan vendido todos los artculos a la venta en el ltimo ao (los ltimos 365 das).

Solucin:

SELECT * FROM EmpleadosWHERE NOT EXISTS (SELECT 1 FROM Articulos

WHERE NOT EXISTS (SELECT 1 FROM VentasWHERE Ventas.Vendedor=Empleados.IdAND Ventas.Articulo=Articulos.IdAND Fecha> DATE_SUB(CURDATE(),

INTERVAL 365 DAY)));

Este problema es anlogo al anterior. Se ha incluido con el propsito que el l

ector repita el anlisis del problema anterior.8. Vendedores que hayan vendido por lo menos un artculo de cada grupo en el lti

mo ao.Solucin:

SELECT * FROM EmpleadosWHERE NOT EXISTS (

SELECT 1 FROM GruposWHERE NOT EXISTS (

SELECT 1 FROM ArticulosWHERE Grupo=Grupos.IdAND EXISTS (

SELECT 1 FROM VentasWHERE Articulo=Articulos.IdAND Vendedor=Empleados.IdAND Fecha>DATE_SUB(CURDATE(),

INTERVAL 365 DAY))));)));

Podramos escribir el siguiente seudocdigo :

SELECCIONAR *DE EmpleadosDONDE PARA TODO GrupoEXISTE Articulo en GrupoTAL QUE EXISTE VentaDEL Empleado y Articulo EN ultimo ao.

Como no tenemos una manera directa de representar el PARA TODO, usamos laequivalencia de los problemas anteriores. Resulta,

SELECCIONAR *DE EmpleadosDONDE NO EXISTE Grupo TAL QUE NOEXISTE Articulo en GrupoTAL QUE EXISTE VentaDEL Empleado y Articulo EN ultimo ao.

El resto es una traduccin directa.

9. Grupos de los cuales no se vendieron artculos en el ltimo ao, para cada vende


7/29

dor, ordenado alfabticamente por vendedor y grupo.Solucin:

SELECT Empleados.Nombre,Grupos.Descripcion

FROM Empleados, GruposWHERE NOT EXISTS(

SELECT 1 FROM VentasWHERE Vendedor = Empleados.IdAND Fecha>DATE_SUB(CURDATE(), INTERVAL 365 DAY)AND EXISTS (

SELECT 1 FROM ArticulosWHERE Id = Ventas.ArticuloAND Grupo = Grupos.Id))

ORDER BY 1, 2;

Seudocdigo :

SELECCIONAR Vendedor, Grupo

DE Empleados, GruposDONDE NO EXISTE VentaTAL QUE Venta corresponde a Vendedor

Y es del ltimo aoY el Articulo de la Venta es del Grupo.

13.2.7. SELECT Syntax13.2.7.1. JOIN Syntax13.2.7.2. UNION SyntaxSELECT

[ALL DISTINCT DISTINCTROW ][HIGH_PRIORITY][STRAIGHT_JOIN][SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT][SQL_CACHE SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]

select_expr, ...[FROM table_references[WHERE where_condition][GROUP BY {col_name expr position}[ASC DESC], ... [WITH ROLLUP]]

[HAVING where_condition][ORDER BY {col_name expr position}[ASC DESC], ...]

[LIMIT {[offset,] row_count row_count OFFSET offset}][PROCEDURE procedure_name(argument_list)][INTO OUTFILE 'file_name' export_options INTO DUMPFILE 'file_name']

[FOR UPDATE LOCK IN SHARE MODE]]

SELECT is used to retrieve rows selected from one or more tables, and can include UNION statements and subqueries. See Section 13.2.7.2, UNION Syntax, and Section13.2.8, Subquery Syntax.

The most commonly used clauses of SELECT statements are these:

Each select_expr indicates a column that you want to retrieve. There must be atleast one select_expr.


8/29

table_references indicates the table or tables from which to retrieve rows. Itssyntax is described in Section 13.2.7.1, JOIN Syntax.

The WHERE clause, if given, indicates the condition or conditions that rows mustsatisfy to be selected. where_condition is an expression that evaluates to truefor each row to be selected. The statement selects all rows if there is no WHERE clause.

In the WHERE clause, you can use any of the functions and operators that MySQL supports, except for aggregate (summary) functions. See Chapter 12, Functions andOperators.

SELECT can also be used to retrieve rows computed without reference to any table.

For example:

mysql> SELECT 1 + 1;-> 2

You are allowed to specify DUAL as a dummy table name in situations where no tables are referenced:

mysql> SELECT 1 + 1 FROM DUAL;-> 2

DUAL is purely for compatibility with some other database servers that require aFROM clause. MySQL does not require the clause if no tables are referenced.

In general, clauses used must be given in exactly the order shown in the syntaxdescription. For example, a HAVING clause must come after any GROUP BY clause and before any ORDER BY clause. The exception is that the INTO clause can appear e

ither as shown in the syntax description or immediately preceding the FROM clause.

A select_expr can be given an alias using AS alias_name. The alias is used as the expression's column name and can be used in GROUP BY, ORDER BY, or HAVING clauses. For example:

SELECT CONCAT(last_name,', ',first_name) AS full_nameFROM mytable ORDER BY full_name;

The AS keyword is optional when aliasing a select_expr. The preceding example could have been written like this:

SELECT CONCAT(last_name,', ',first_name) full_nameFROM mytable ORDER BY full_name;

However, because the AS is optional, a subtle problem can occur if you forget the comma between two select_expr expressions: MySQL interprets the second as an alias name. For example, in the following statement, columnb is treated as an alias name:

SELECT columna columnb FROM mytable;

For this reason, it is good practice to be in the habit of using AS explicitly when specifying column aliases.

It is not allowable to use a column alias in a WHERE clause, because the columnvalue might not yet be determined when the WHERE clause is executed. See Section


9/29

A.5.4, Problems with Column Aliases.

The FROM table_references clause indicates the table or tables from which to retrieve rows. If you name more than one table, you are performing a join. For information on join syntax, see Section 13.2.7.1, JOIN Syntax. For each table specified, you can optionally specify an alias.

tbl_name [[AS] alias][{USEIGNOREFORCE} INDEX (key_list)]

The use of USE INDEX, IGNORE INDEX, FORCE INDEX to give the optimizer hints about how to choose indexes is described in Section 13.2.7.1, JOIN Syntax.

You can use SET max_seeks_for_key=value as an alternative way to force MySQL toprefer key scans instead of table scans. See Section 5.2.2, Server System Variables.

You can refer to a table within the default database as tbl_name, or as db_name.tbl_name to specify a database explicitly. You can refer to a column as col_name

, tbl_name.col_name, or db_name.tbl_name.col_name. You need not specify a tbl_name or db_name.tbl_name prefix for a column reference unless the reference wouldbe ambiguous. See Section 9.2.1, Identifier Qualifiers, for examples of ambiguitythat require the more explicit column reference forms.

A table reference can be aliased using tbl_name AS alias_name or tbl_name alias_name:

SELECT t1.name, t2.salary FROM employee AS t1, info AS t2WHERE t1.name = t2.name;

SELECT t1.name, t2.salary FROM employee t1, info t2WHERE t1.name = t2.name;

Columns selected for output can be referred to in ORDER BY and GROUP BY clausesusing column names, column aliases, or column positions. Column positions are integers and begin with 1:

SELECT college, region, seed FROM tournamentORDER BY region, seed;

SELECT college, region AS r, seed AS s FROM tournamentORDER BY r, s;

SELECT college, region, seed FROM tournamentORDER BY 2, 3;

To sort in reverse order, add the DESC (descending) keyword to the name of the column in the ORDER BY clause that you are sorting by. The default is ascending order; this can be specified explicitly using the ASC keyword.

Use of column positions is deprecated because the syntax has been removed from the SQL standard.

If you use GROUP BY, output rows are sorted according to the GROUP BY columns asif you had an ORDER BY for the same columns. To avoid the overhead of sorting that GROUP BY produces, add ORDER BY NULL:

SELECT a, COUNT(b) FROM test_table GROUP BY a ORDER BY NULL;

MySQL extends the GROUP BY clause so that you can also specify ASC and DESC afte


10/29

r columns named in the clause:

SELECT a, COUNT(b) FROM test_table GROUP BY a DESC;

MySQL extends the use of GROUP BY to allow selecting fields that are not mentioned in the GROUP BY clause. If you are not getting the results that you expect from your query, please read the description of GROUP BY found in Section 12.10, Fu

nctions and Modifiers for Use with GROUP BY Clauses.

GROUP BY allows a WITH ROLLUP modifier. See Section 12.10.2, GROUP BY Modifiers.

The HAVING clause is applied nearly last, just before items are sent to the client, with no optimization. (LIMIT is applied after HAVING.)

A HAVING clause can refer to any column or alias named in a select_expr in the SELECT list or in outer subqueries, and to aggregate functions. However, the SQLstandard requires that HAVING must reference only columns in the GROUP BY clauseor columns used in aggregate functions. To accommodate both standard SQL and the MySQL-specific behavior of being able to refer columns in the SELECT list, MyS

QL 5.0.2 and up allows HAVING to refer to columns in the SELECT list, columns inthe GROUP BY clause, columns in outer subqueries, and to aggregate functions.

For example, the following statement works in MySQL 5.0.2 but produces an errorfor earlier versions:

mysql> SELECT COUNT(*) FROM t GROUP BY col1 HAVING col1 = 2;

If the HAVING clause refers to a column that is ambiguous, a warning occurs. Inthe following statement, col2 is ambiguous because it is used as both an alias and a column name:

SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2;

Preference is given to standard SQL behavior, so if a HAVING column name is usedboth in GROUP BY and as an aliased column in the output column list, preferenceis given to the column in the GROUP BY column.

Do not use HAVING for items that should be in the WHERE clause. For example, donot write the following:

SELECT col_name FROM tbl_name HAVING col_name > 0;

Write this instead:

SELECT col_name FROM tbl_name WHERE col_name > 0;

The HAVING clause can refer to aggregate functions, which the WHERE clause cannot:

SELECT user, MAX(salary) FROM usersGROUP BY user HAVING MAX(salary) > 10;

(This did not work in some older versions of MySQL.)

MySQL allows duplicate column names. That is, there can be more than one select_expr with the same name. This is an extension to standard SQL. Because MySQL also allows GROUP BY and HAVING to refer to select_expr values, this can result in

an ambiguity:

SELECT 12 AS a, a FROM t GROUP BY a;


11/29

In that statement, both columns have the name a. To ensure that the correct column is used for grouping, use different names for each select_expr.

When MySQL resolves an unqualified column or alias reference in an ORDER BY, GROUP BY, or HAVING clause, it first searches for the name in the select_expr values. If the name is not found, it looks in the columns of the tables named in the

FROM clause.

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be non-negative integer constants (except when using prepared statements).

With two arguments, the first argument specifies the offset of the first row toreturn, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15

To retrieve all rows from a certain offset up to the end of the result set, youcan use some large number for the second parameter. This statement retrieves allrows from the 96th row to the last:

SELECT * FROM tbl LIMIT 95,18446744073709551615;

With one argument, the value specifies the number of rows to return from the beginning of the result set:

SELECT * FROM tbl LIMIT 5; # Retrieve first 5 rows

In other words, LIMIT row_count is equivalent to LIMIT 0, row_count.

For prepared statements, you can use placeholders (supported as of MySQL version5.0.7). The following statements will return one row from the tbl table:

SET @a=1;PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?';EXECUTE STMT USING @a;

The following statements will return the second to sixth row from the tbl table:

SET @skip=1; SET @numrows=5;PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?';EXECUTE STMT USING @skip, @numrows;

For compatibility with PostgreSQL, MySQL also supports the LIMIT row_count OFFSET offset syntax.

The SELECT ... INTO OUTFILE 'file_name' form of SELECT writes the selected rowsto a file. The file is created on the server host, so you must have the FILE privilege to use this syntax. file_name cannot be an existing file, which among other things prevents files such as /etc/passwd and database tables from being destroyed. As of MySQL 5.0.19, the character_set_filesystem system variable controlsthe interpretation of the filename.

The SELECT ... INTO OUTFILE statement is intended primarily to let you very quic

kly dump a table to a text file on the server machine. If you want to create theresulting file on some client host other than the server host, you cannot use SELECT ... INTO OUTFILE. In that case, you should instead use a command such as m


12/29

ysql -e "SELECT ..." > file_name to generate the file on the client host.

SELECT ... INTO OUTFILE is the complement of LOAD DATA INFILE; the syntax for the export_options part of the statement consists of the same FIELDS and LINES clauses that are used with the LOAD DATA INFILE statement. See Section 13.2.5, LOADDATA INFILE Syntax.

FIELDS ESCAPED BY controls how to write special characters. If the FIELDS ESCAPED BY character is not empty, it is used as a prefix that precedes following characters on output:

The FIELDS ESCAPED BY character

The FIELDS [OPTIONALLY] ENCLOSED BY character

The first character of the FIELDS TERMINATED BY and LINES TERMINATED BY values

ASCII NUL (the zero-valued byte; what is actually written following the escape character is ASCII 0, not a zero-valued byte)

The FIELDS TERMINATED BY, ENCLOSED BY, ESCAPED BY, or LINES TERMINATED BY characters must be escaped so that you can read the file back in reliably. ASCII NUL is escaped to make it easier to view with some pagers.

The resulting file does not have to conform to SQL syntax, so nothing else needbe escaped.

If the FIELDS ESCAPED BY character is empty, no characters are escaped and NULLis output as NULL, not \N. It is probably not a good idea to specify an empty escape character, particularly if field values in your data contain any of the characters in the list just given.

Here is an example that produces a file in the comma-separated values (CSV) format used by many programs:

SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'LINES TERMINATED BY '\n'FROM test_table;

If you use INTO DUMPFILE instead of INTO OUTFILE, MySQL writes only one row intothe file, without any column or line termination and without performing any escape processing. This is useful if you want to store a BLOB value in a file.

Note: Any file created by INTO OUTFILE or INTO DUMPFILE is writable by all userson the server host. The reason for this is that the MySQL server cannot createa file that is owned by anyone other than the user under whose account it is running. (You should never run mysqld as root for this and other reasons.) The filethus must be world-writable so that you can manipulate its contents.

The SELECT syntax description at the beginning this section shows the INTO clause near the end of the statement. It is also possible to use INTO OUTFILE or INTODUMPFILE immediately preceding the FROM clause.

A PROCEDURE clause names a procedure that should process the data in the resultset. For an example, see Section 24.3.1, Procedure Analyse.

If you use FOR UPDATE with a storage engine that uses page or row locks, rows examined by the query are write-locked until the end of the current transaction. Using LOCK IN SHARE MODE sets a shared lock that allows other transactions to rea


13/29

d the examined rows but not to update or delete them. See Section 14.2.10.5, SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE Locking Reads.

Following the SELECT keyword, you can use a number of options that affect the operation of the statement.

The ALL, DISTINCT, and DISTINCTROW options specify whether duplicate rows should

be returned. If none of these options are given, the default is ALL (all matching rows are returned). DISTINCT and DISTINCTROW are synonyms and specify removalof duplicate rows from the result set.

HIGH_PRIORITY, STRAIGHT_JOIN, and options beginning with SQL_ are MySQL extensions to standard SQL.

HIGH_PRIORITY gives the SELECT higher priority than a statement that updates a table. You should use this only for queries that are very fast and must be done at once. A SELECT HIGH_PRIORITY query that is issued while the table is locked for reading runs even if there is an update statement waiting for the table to befree.

HIGH_PRIORITY cannot be used with SELECT statements that are part of a UNION.

STRAIGHT_JOIN forces the optimizer to join the tables in the order in which theyare listed in the FROM clause. You can use this to speed up a query if the optimizer joins the tables in non-optimal order. See Section 7.2.1, Optimizing Queries with EXPLAIN. STRAIGHT_JOIN also can be used in the table_references list. SeeSection 13.2.7.1, JOIN Syntax.

SQL_BIG_RESULT can be used with GROUP BY or DISTINCT to tell the optimizer thatthe result set has many rows. In this case, MySQL directly uses disk-based temporary tables if needed, and prefers sorting to using a temporary table with a keyon the GROUP BY elements.

SQL_BUFFER_RESULT forces the result to be put into a temporary table. This helpsMySQL free the table locks early and helps in cases where it takes a long timeto send the result set to the client.

SQL_SMALL_RESULT can be used with GROUP BY or DISTINCT to tell the optimizer that the result set is small. In this case, MySQL uses fast temporary tables to store the resulting table instead of using sorting. This should not normally be needed.

SQL_CALC_FOUND_ROWS tells MySQL to calculate how many rows there would be in theresult set, disregarding any LIMIT clause. The number of rows can then be retrieved with SELECT FOUND_ROWS(). See Section 12.9.3, Information Functions.

SQL_CACHE tells MySQL to store the query result in the query cache if you are using a query_cache_type value of 2 or DEMAND. For a query that uses UNION or subqueries, this option effects any SELECT in the query. See Section 5.14, The MySQLQuery Cache.

SQL_NO_CACHE tells MySQL not to store the query result in the query cache. See Section 5.14, The MySQL Query Cache. For a query that uses UNION or subqueries, this option effects any SELECT in the query.

Previous / Next / Up / Table of ContentsUser Comments

Posted by Colin Nelson on February 26 2003 1:10am [Delete] [Edit]

You can simulate a CROSSTAB by the following method:-


14/29

Use IF function to select the key value of the sub table as in:

SELECTSUM(IF(beta_idx=1, beta_value,0)) as beta1_value,SUM(IF(beta_idx=2, beta_value,0)) as beta2_value,SUM(IF(beta_idx=3, beta_value,0)) as beta3_value

FROM alpha JOIN beta WHERE alpha_id = beta_alpha_id;

where alpha table has the form alpha_id, alpha_blah, alpha_blah_blahand beta table has the form beta_alpha_id, beta_other stuff,beta_idx, beta_value

This will create 3 columns with totals of beta values according to their idx field

Posted by [name withheld] on March 29 2003 2:49am [Delete] [Edit]

when selecting a single random row you have to use a query like this: SELECT ...FROM my_table ORDER BY RAND() LIMIT 1.as explain shows, mysql optimizes this VERY badly (or may be better said, doens't optimize it at all): it uses an temporary table and an extra filesort.couldn't this be optimized?!if not, may be add a syntax like SELECT RANDOM_ROW .... FROM my_table ...

Posted by David Phillips on April 2 2003 9:15am [Delete] [Edit]

This method of selecting a random row should be fast:

LOCK TABLES foo READ;

SELECT FLOOR(RAND() * COUNT(*)) AS rand_row FROM foo;SELECT * FROM foo LIMIT $rand_row, 1;UNLOCK TABLES;

Unfortunately, variables cannot be used in the LIMIT clause, otherwise the entire thing could be done completely in SQL.

Posted by [name withheld] on August 21 2003 12:55am [Delete] [Edit]

In reply to David Philips:

If your tables are not all that big, a simpler method is:SELECT * FROM foo ORDER BY RAND(NOW()) LIMIT 1;

If it's a big table, your method will almost certainly be faster.

Posted by Count Henry De Havilland-Fortesque-Smedley on January 13 2004 6:41am [Delete] [Edit]

If you want to find duplicates on a field that hasn't been uniquely indexed, youcan do this:

SELECT BookISBN, count(BookISBN) FROM Books GROUP BY BookISBN HAVING COUNT(BookISBN)>1;


15/29

Posted by Count Henry De Havilland-Fortesque-Smedley on January 13 2004 6:59am [Delete] [Edit]

Sometimes you want to retrieve the records that DONT match a select statement.

Consider this select:SELECT CarIndex FROM DealerCatalog, BigCatalog WHEREDealerCatalog.CarIndex=BigCatalog.CarIndex

This finds all the CarIndex values in the Dealer's catalog that are in the bigger distributor catalog.

How do I then find the dealer CarIndex values that ARE NOT in the bigger catalog?

The answer is to use LEFT JOIN - anything that doesn't join is given a NULL valu

e , so we look for that:SELECT CarIndex FROM DealerCatalog LEFT JOIN BigCatalog ON DealerCatalog.CarIndex=BigCatalog.CarIndex WHERE BigCatalog.CarIndex IS NULL

Posted by Johann Eckert on February 11 2004 1:14pm [Delete] [Edit]

To find double entries in a table:

SELECT db1.*FROM tbl_data db1, tbl_data k2WHERE db1.id db2.id

AND db1.name = db2.name

db1.id must be the PKdb1.name must be the fields that should be verified as double entries.

(I'm not sure wether the code is correct but in my case it works)

Johann


In order to anti-match fields by wildcards, one has to check whether the value of the field is not NULL:

For example: The table 'runs' contains 34876 rows. 205 rows have an 'info' fieldcontaining the string 'wrong'.

To select those rows for which the 'info' column does *NOT* contain the word 'wrong' one has to do:

mysql> select count(*) FROM runs WHERE info is null or info not like '%wrong%';

+----------+ count(*) +----------+ 34671 +----------+

but not:mysql> select count(*) FROM runs WHERE info not like %wrong%';


16/29

+----------+ count(*) +----------+ 5537 +----------+

which would lead to a much smaller number of selected rows.

Posted by M M on March 5 2004 12:28am [Delete] [Edit]

I have managed to select random records using php and MySQL like the following:

$min=1;$row=mysql_fetch_assoc(mysql_query("SHOW TABLE STATUS LIKE 'table';"));$max=$row["Auto_increment"];

$random_id=rand($min,$max);$row=mysql_fetch_assoc(mysql_query("SELECT * FROM table WHERE id='$random_id'");

Voila...

Cezarhttp://RO-Escorts.com


Random records without PHP, only MySQL:

select * from mailinglists order by rand() limit 1

Regards,Geert van der Ploeg

Posted by M M on May 5 2005 3:05pm [Delete] [Edit]

All examples about selecting random row like:SELECT * FROM foo ORDER BY RAND() LIMIT 1;SELECT * FROM foo ORDER BY RAND(NOW()) LIMIT 1;

are not usefull when it's about selecting a range of rows from a table.For example, if the table has 1000 records, and we want to select one record randomly but from only 2 records from table (based on a WHERE clause), the above examples fail because the same row is returned all the time.

This is my version in this special case...


17/29

This code is selecting the needed rows, and picks one of the rows randomly.This is very important when you need to select a huge number of rows liek we didon http://fantezii.com

Cezar

Posted by Cody Caughlan on May 26 2004 10:26pm [Delete] [Edit]

Sometimes it is nice to use the SELECT query options like SQL_CALC_FOUND_ROWS orSQL_CACHE, but to maintain compatibility across different databases or even older versions of MySQL which do not support those options, it is possible to enclose them in a comment block, e.g.:

SELECT /*! 40000 SQL_CALC_FOUND_ROWS */ foo,bar FROM some_table;

The /* construct will stop DBMS's other than MySQL from parsing the comment cont

ents, while /*! will tell ALL MySQL versions to parse the "comment" (which is actually a non-comment to MySQL). The /*!40000 construct will tell MySQL servers starting from 4.0.0 (which is the first version to support SQL_CALC_FOUND_ROWS) to parse the comment, while earlier versions will ignore it.

Posted by Boris Aranovich on June 9 2004 4:33pm [Delete] [Edit]

I am using this way to select random row or rows:

SELECT * [or any needed fileds], idx*0+RAND() as rnd_id FROM tablename ORDER BYrnd_id LIMIT 1 [or the number of rows]

Meanwhile, I didn't stumble in any problems with this usage.I picked this method in some forum, don't remember when, where or by who was itintroduced :)

Posted by James Gramosli on June 9 2004 11:24pm [Delete] [Edit]

I found through some testing that ordering by dates causes a significant difference in execution time. If you have a primary key which is inline with your Datefield(i.e. messages are inserted in date order), then order by ID instead, I found a massive 66% improvement across many queries.

Posted by Michal Nedoszytko on August 10 2004 10:19am [Delete] [Edit]

My method of retrieving duplicate entries

In a database with personal information (name, surname, etc..) with an auto_increment index I wanted to retrieve all the entries with same name and surname field (duplicate names), which by accident were inserted to the base.

I used this syntax

SELECT name,surname,COUNT(name) AS cnt_n, COUNT(surname) AS cnt_s FROM the_tableGROUP BY name HAVING cnt_n>1 AND cnt_s>1;

I hope this might be of help to anyone that wants to do some extended maintenance on the database


18/29

Posted by Dmitri Mikhailov on August 24 2004 9:30pm [Delete] [Edit]

On the other hand, for this case it's simplier to engage an appropriate index ifthere is such:

CREATE INDEX ccr_news_insert_date_i ON ccr_news (insert_date DESC);

SELECT *FROM ccr_newsWHERE insert_date > 0;

or, if for some reason MySQL still uses a full table scan:

SELECT *FROM ccr_news FORCE INDEX (ccr_news_insert_date_i)WHERE insert_date > 0;

Posted by Adam Tylmad on August 25 2004 12:36pm [Delete] [Edit]

If you want to ORDER BY [columnname] ASCand have the NULL rows in the bottomyou can use ORDER BY -[columnname] DESC

Posted by Edward Hermanson on October 6 2004 8:51am [Delete] [Edit]

Select Name,Category FROM authors ORDER BY Category,Name;

Will allow you to sort by categories listed in a seperate tableIF the category column in this primary table contains ID valuesfrom your ID column in your second reference table.

So your first "authors" table looks like:

id name category1 Henry Miller 23 June Day 13 Thomas Wolf 2

and your second reference table looks like:

id category1 Modern2 Classics

Now when the order of categories is changed in the second tablethe order of categories will be reflected in the primary table.

Then just select the categories from the reference table and putthe list into a numbered array. Then in your script when you runacross a category number from the first recordset just referencethe value from the index in the second array to obtain the value.In php in the above example it might look like:

foreach ($recordset as $key => $record) {echo $record["id"] . ":" . $record["name"] . ":" . $ordered_cats[$record["catego


19/29

ry"]];}

This may seem obvious to some but I was pulling my hair outtrying to figure out how to order a recordset based on a listfrom a different table. Hope this helps someone.

Ed

Posted by Greg Covey on December 3 2004 12:26am [Delete] [Edit]

The LIMIT clause can be used when you would use TOP in Access or MS SQL.

Posted by Kenan Bektas on December 14 2004 6:05pm [Delete] [Edit]

(LINUX) By default, if you don't specify absolute path for OUTFILE inselect ... into OUTFILE "..."

It creates the file in "/var/lib/mysql/"

Make sure current user has (NOT) a write permission in that directory.

Posted by James Day on December 23 2004 8:40pm [Delete] [Edit]

MySQL 4 and later string comparisons, including DISTINCT, aren't case sensitiveunless the field is declared as BINARY or you use BINARY in your comparison.

Posted by Kumar S on January 4 2005 9:27am [Delete] [Edit]

If You want to find the rows which are having a column with identical values then,

SELECT managerId, count(company) FROM manager GROUP BY company HAVING COUNT(company)>=8 (say)

Regards,Kumar.S

Posted by Herv Pags on February 10 2005 6:59pm [Delete] [Edit]

The keyword WHERE is NOT part of the "where_definition"!

The following definition of the "where_definition":"where_definition consists of the keyword WHERE followed byan expression that indicates the condition or conditions thatrows must satisfy to be selected"

is in contradiction with the SELECT given syntax:

SELECT...[FROM table_references

[WHERE where_definition]


20/29

Posted by Imran Chaudhry on February 17 2005 5:13pm [Delete] [Edit]

I found a nifty way of influencing the ORDER of rows returned by a query that helps in displaying a list with frequently accessed items at the top.

An example is a name/address form where the country is a selectable list. If mos

t of your users are from the UK and US you may want to do something like:

SELECT * FROM countries ORDER by iso_code IN ('UK', 'US') desc

Which returns something like:

+----------+----------------------------------------+ iso_code name+----------+----------------------------------------+

UK United Kingdom US United States AF Afghanistan

AL Albania DZ Algeria

AS American Samoa

Hope this helps someone! [email protected]

Posted by Fahed Bizzari on February 22 2005 4:18pm [Delete] [Edit]

It seems there is no way to select * from table where a certain field is distinct. In 2 sqls it is easy:

$sql9 = "SELECT DISTINCT field AS distinctfield FROM table ORDER BY distinctfield ";

$res9= $db->execute($sql9);for($ll=0;$llgetNumTuples();$ll++){$row = $res9->getTupleDirect($ll);$distinctfield = $row[distinctfield];$sql8="select * from table WHERE field='distinctfield' ORDER BY distinctfield LIMIT 1";}

But not one!

Fahed

Posted by Lex Berman on February 22 2005 9:28pm [Delete] [Edit]

reply to Fahed Bizzari's post, based on Havilland-Fortesque-Smedley's comment (above) the equivalent of select * while doing DISTINCT is:

select *, count(FIELD) from TABLE group by FIELD having count(FIELD)=1 into outfile 'foobar.txt';

then you can check the output. note that there are twice as many rows as records, because each unique row is followed by its count (in this case count=1). so just toss the .txt file into something and sort on the field containing the count

and throw out all the rows =1. this is the same result as a select * distinct FIELD (as far as I can tell).


21/29

anyway, works for me. aloha. Lex

Posted by Lex Berman on February 22 2005 9:32pm [Delete] [Edit]

oh, about the previous post, it's not correct because distinct should be

count(FIELD)=>1

which still doesn't solve the DISTINCT part

Lex

Posted by Gregory Turner on March 10 2005 5:00pm [Delete] [Edit]

In regards to:_______________________________________________******************************************

I found a nifty way of influencing the ORDER of rows returned by a query that helps in displaying a list with frequently accessed items at the top.

An example is a name/address form where the country is a selectable list. If most of your users are from the UK and US you may want to do something like:

SELECT * FROM countries ORDER by iso_code IN ('UK', 'US') desc

Which returns something like:

+----------+----------------------------------------+ iso_code name+----------+----------------------------------------+

UK United Kingdom US United States AF Afghanistan

AL Albania DZ Algeria AS American Samoa

_______________________________________________******************************************If found that if you also add in another 'iso_code' column in the order by statment after the first one containing the IN() statment, it will sort the remainingrecords:SELECT * FROM countries ORDER by iso_code IN ('UK', 'US') desc, iso_code

Posted by Heywood on March 11 2005 3:04pm [Delete] [Edit]

When using the SELECT ... INTO OUTFILE syntax, use a UNION to add headers. Here's an example for CSV output:

SELECT 'Fiscal Year','Location','Sales'UNIONSELECT FY, Loc, Sales INTO OUTFILE 'salesreport.csv'FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'FROM SalesTable;

This will add the text headers Fiscal Year, Location and Sales to your fields. Only caveat is with an ORDER BY statement, if you don't want your headers sortedalong with your data you need to enclose it in parenthesis:


22/29

SELECT 'Fiscal Year','Location','Sales'UNION{SELECT FY, Loc, Sales INTO OUTFILE 'salesreport.csv'FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'FROM SalesTableORDER BY Sales DESC);

Posted by [name withheld] on April 21 2005 7:54pm [Delete] [Edit]

To correct Lex one more time, it should be count(FIELD)>=1.

So the whole query for retrieving a whole row with one field distinct is:

select *, count(FIELD) from TABLE group by FIELD having count(FIELD)>=1;

Thanks, Lex. You are a lifesaver.

Posted by Jerry Nelson on May 6 2005 8:57pm [Delete] [Edit]

As a newbie to MySQL and to dealing with BLOBs, I had a difficult time trying todetermine how to extract a BLOB field from the database back to a file. It turns out to be quite simple by doing the following SQL:

select blobfield into dumpfile '/tmp/blobfile' from blobtable;

Posted by joel boonstra on May 13 2005 12:29am [Delete] [Edit]

In response to Heywood's tip about adding column headers to OUTFILEs...

Make sure that the format of the columns that match up with your headers doesn'tlimit the display of the headers. For instance, I was using the UNION tip to add a header to a column defined as char(2) (for storing a two-letter state code).The resulting CSV file only displayed the first two letters of my column header. The fix is simple, just use CAST() on the column in the second SELECT to convert it to the appropriate type. In my case, doing something like this:

SELECT 'state header' FROM table UNION SELECT CAST(state AS char) FROM table INTO OUTFILE [...]

worked just dandy. Hope that saves someone a little time.

Posted by Rene Liethof on June 8 2005 4:50pm [Delete] [Edit]

Arbitrary Ordering

I came across this example athttp://www.shawnolson.net/a/722/Neat way of using the CASE statement.

Example for ordering price informationprice is orderd ascending but the 0.00

prices end up underneath

SELECT dienst.dienst, dienst.url, dienst.info, dienst_prijs.dienst_eenheid, dien


23/29

st_prijs.prijs, dienst_prijs.inc_btw, dienst_prijs.dienst_optie,CASE dienst_prijs.prijsWHEN dienst_prijs.prijs = '0.00' THEN 1000WHEN dienst_prijs.prijs > '0.00' THEN 10ELSE NULLEND AS ordermeFROM dienst, dienst_prijs

WHERE dienst.taal = 'nl' &&dienst.dienst_type = 'Internet toegang' &&dienst.dienst != 'alle diensten' &&dienst.publiceer != '' &&dienst_prijs.dienst_eenheid IN ( 'maand', 'jaar' ) &&dienst.dienst = dienst_prijs.dienstORDER BY orderme, dienst_prijs.prijs

Posted by Callum Macdonald on June 27 2005 8:54pm [Delete] [Edit]

If you want to use ORDER BY before GROUP BY, the only way I've found to achieveit is with a subquery.

For example, if you want to get a list of users from a table UserActions sortedaccording to the most recent action (based on a field called Time) the query would be:

SELECT * FROM (SELECT * FROM UserActions ORDER BY Time DESC) AS Actions GROUP BYUserID ORDER BY Time DESC;

Without the subquery, the group is performed first, and so the first record thatappears in the database (which is not necessarily in the order you want) will be used to determine the sort order. This caused me huge problems as my data was

in a jumbled order within the table.

--Edit--This same result can be achieved with the use of MAX(Time), so the query would be:

SELECT *, MAX(Time) AS LatestAction GROUP BY UserID ORDER BY LatestAction DESC;

As far as I can see, the subquery model still holds up if you need more complexsorting before performing the GROUP.

Posted by Paul Montgomery on August 13 2005 10:06am [Delete] [Edit]

I've seen it asked elsewhere about how to select all duplicates, not just one row for each dupe.

CREATE TEMPORARY TABLE dupes SELECT * FROM tablename GROUP BY colname HAVING COUNT(*)>1 ORDER BY colname;SELECT t.* FROM tablename t, dupes d WHERE t.colname = d.colname ORDER BY t.colname;

Posted by Wayne Smith on November 4 2005 3:10am [Delete] [Edit]

Be careful about the "SELECT...INTO OUTFILE" options. They are similar to, but not exactly the same as, the mysqldump options.


24/29

Two things:

1) The options in mysqldump can be in any order, because they are true command-line options (that is, they are conceptually used together, but syntactically separate on the mysqldump command line). The options in the SELECT...INTO OUTFILE need to be in the exact order as specified in the documentation above.

2) The options MUST have dashes between the words (e.g., fields-enclosed-by) when use as options with the mysqldump utility, but MUST NOT have dashes when usedas options with the SELECT...INTO OUTFILE. This may not be clear in the documentation above.

Wayne

Posted by Geoff on November 9 2005 10:22pm [Delete] [Edit]

In reply to Fahed Bizzari et al...

If you want to select all fields from distinct rows why not use:SELECT DISTINCT * FROM table GROUP BY field;

Don't forget the DISTINCT relates to the ORDER BY / GROUP BY and has nothing todo with the 'select_expr'

If you want the count as well then use:SELECT DISTINCT *, count(*) AS count FROM table GROUP BY field;

Posted by Kumar Mitra-Endres on November 22 2005 1:51pm [Delete] [Edit]

Where is the pagination code as offered by the google search machine????

Kumar/Germany

Posted by Flavio Ventura on December 9 2005 1:17pm [Delete] [Edit]

If you have a binary string type field and you want a case insensitive sorting you can use CAST() as follow:

case sensitive example (DECODE return a binary string):----------------------------------------------------------------------------SELECT DECODE(EncodedField) AS DecodedFieldFROM TableWithEncodedFieldORDER BY DecodedField;

case insensitive solution:---------------------------------SELECT CAST(DECODE(EncodedField) AS CHAR) AS DecodedFieldFROM TableWithEncodedFieldORDER BY DecodedField;

I hope it may be usefull.


25/29

Posted by mike gieson on January 10 2006 6:13am [Delete] [Edit]

To select specific rows from the table use the IN statement.

Example:

SELECT * FROM table WHERE myid IN (2, 16, 93,102);

This would return multiple rows based on specific criteria.

Posted by Todd Farmer on January 22 2006 7:40am [Delete] [Edit]

For large tables with auto incremented primary key values, I have found the following to be most efficient in obtaining one random row:

SELECT * FROM my_tableWHERE pk_column >=(SELECT FLOOR( MAX(pk_column) * RAND()) FROM my_table)

ORDER BY pk_columnLIMIT 1;

Posted by Vlado Kocan on January 28 2006 11:35am [Delete] [Edit]

Reply to Edward Hermanson post (above):

I prefer this way of sorting table by column values listed in another table:

The accnumber column in primary table contains ID values from ID column in the secondary table.

Primary table "contacts":id name accnumber1 Cooke 32 Peterson 33 Stevens 1

Secondary table "accounts":id accname1 Company12 Company23 Company3

SELECT contacts.lname, accounts.accnameFROM contacts, accountsWHERE contacts.accnumber = accounts.id ORDER BY accname;

Posted by Lars-Erik Hoffsten on March 14 2006 11:35pm [Delete] [Edit]

ORDER BY textfield in natural order!?Lets say you want the following result:File1File2File10

I havn't found a way to do it in SQL, here is a way to do it in PHP (just replace 'order_by' to the field you want to order by):


26/29

$result = mysql_query("SELECT order_by,... FROM table");$rows = array();if($result){while(($row = mysql_fetch_array($result, MYSQL_ASSOC)))$rows[] = $row;usort($rows, create_function('$a, $b', 'return strnatcasecmp($a["order_by"], $b[

"order_by"]);'));}

Posted by Michal Carson on March 19 2006 6:27am [Delete] [Edit]

SELECT ... INTO OUTFILE requires the id to have the FILE privilege. That is,

GRANT SELECT, FILE ON * . * TO "[whomever]"@ "localhost";

As noted above, the output directory must be writable by the id under which the

mysqld process is running. Use "grep user= /etc/my.cnf " to find it.

Posted by Andrew Culver on March 20 2006 4:40pm [Delete] [Edit]

Selecting a random row in SQL? Try:

set @a = (SELECT COUNT(*)-1 c FROM palette)*RAND() DIV 1;PREPARE STMT FROM 'SELECT * FROM palette LIMIT ?,1';EXECUTE STMT USING @a;


If you want to keep field names, consider using mysqldump instead of SELECT INTOOUTFILE.

I use this method to transfer small amounts of data from our live database to our test database, for example when investigating a reported problem in our program code. (We cannot guarantee the field order across all our databases.)

rem Edit order number before runningrem Give password when promptedrem Result files will be in current working directory\mysql\bin\mysqldump livedb -uliveuser -p --no-create-info --tables orderpayment--where=orderid=2712>resultp.txt\mysql\bin\mysqldump livedb -uliveuser -p --no-create-info --tables orderitem --where=orderid=2712>resulti.txt\mysql\bin\mysqldump livedb -uliveuser -p --no-create-info --tables orderheader--where=id=2712>resulth.txt

Posted by Zhao Xinyou on March 29 2006 5:53pm [Delete] [Edit]

when you meet more conditions, you may use the following code:

select * from yourdatabase where fieldone='value1' and fieldtwo='value2';


27/29

Posted by John Bachir on April 4 2006 12:13am [Delete] [Edit]

Fahed Bizzari, that is not 2 queries, that is $res9->getNumTuples() + 1 queries!

Posted by Michael Heyman on April 28 2006 3:22pm [Delete] [Edit]

To select the identifiers with the greatest value in each class (where each identifier falls into one class):

SELECT id_class,id FROM tbl,(SELECT MAX(val) AS val FROM tbl GROUP BY id_class)AS _tbl WHERE tbl.val = _tbl.val;

We had a table logging state changes for a series of objects and wanted to findthe most recent state for each object. The "val" in our case was an auto-increment field.

This seems to be the simplest solution that runs in a reasonable amount of time.

Posted by Rich Altmaier on May 4 2006 5:24am [Delete] [Edit]

In a student signup list, use sql to find classes which arenot full. Involves combined use of RIGHT JOIN, COUNT, WHERE, GROUP BY, HAVING, and ORDER BY.

CREATE TABLE `classdescription` (`ClassID` mediumint(9) NOT NULL auto_increment,`ClassType` varchar(10) NOT NULL default '',`ClassName` varchar(50) NOT NULL default '',`ClassDate` datetime NOT NULL default '0000-00-00 00:00:00',`ClassMax` mediumint(9) default NULL,

PRIMARY KEY (`ClassID`)) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE `class_signups` (`s_PersonID` mediumint(9) NOT NULL default '0',`s_ClassID` mediumint(9) NOT NULL default '0',`s_Status` varchar(5) default NULL,KEY `s_ClassID` (`s_ClassID`),KEY `s_PersonID` (`s_PersonID`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `classdescription` VALUES (2, 'firstaid', '', '2005-01-02 11:00:00',2);INSERT INTO `classdescription` VALUES (3, 'advanced-med', '', '2005-01-02 13:00:00', 1);

INSERT INTO `class_signups` VALUES (11, 2, '');INSERT INTO `class_signups` VALUES (12, 2, '');

Now use RIGHT JOIN to list all class descriptions along with signups if any,SELECT cs.s_ClassID, cs.s_PersonID, cd.ClassName, cd.ClassID, cd.ClassType, cd.ClassDate, cd.ClassMaxfrom class_signups cs RIGHT JOINclassdescription cd on (cs.s_ClassID = cd.ClassID )in itself, not too useful, but you can see classes

having no one signed up as a NULL.

To count the number of signups for each class:


28/29

SELECT cs.s_ClassID, COUNT(s_ClassID) AS ClassTotal, cd.ClassName, cd.ClassID, cd.ClassType, cd.ClassDate, cd.ClassMaxfrom class_signups cs RIGHT JOINclassdescription cd on (cs.s_ClassID = cd.ClassID )GROUP BY cd.ClassIDThe COUNT/GROUP BY options show a row per unique ClassID, and the COUNT is adding up

non-null occurances of field s_ClassID. If we had used COUNT(*) then the class withno signups would have counted 1 record, rather than the desired 0/NULL for nosignups.

Now we show only classes where the count of signups is less than ClassMax, meaning theclass has openings!SELECT cs.s_ClassID, COUNT(s_ClassID) AS ClassTotal, cd.ClassName, cd.ClassID, cd.ClassType, cd.ClassDate, cd.ClassMaxfrom class_signups cs RIGHT JOINclassdescription cd on (cs.s_ClassID = cd.ClassID )

GROUP BY cd.ClassIDHAVING ClassTotal < cd.ClassMaxThe HAVING clause limits the after-JOIN output rows to ones matching its criteria, discarding others!

We may want to look only at the firstaid ClassType, so add a WHERE clause tothe JOIN,SELECT cs.s_ClassID, COUNT(s_ClassID) AS ClassTotal, cd.ClassName, cd.ClassID, cd.ClassType, cd.ClassDate, cd.ClassMaxfrom class_signups cs RIGHT JOINclassdescription cd on (cs.s_ClassID = cd.ClassID ) WHERE cd.ClassType='firstaid'GROUP BY cd.ClassID

HAVING ClassTotal < cd.ClassMaxNow there are no outputs as firstaid is full, butsuppose we are looking in this list with respectto a certain student PersonID==12. That is, we want to see classes this person can signupfor, including the ones they are already in!In the case we need to disregard signups by PersonID==12 for e.g.,

SELECT cs.s_ClassID, COUNT(s_ClassID) AS ClassTotal, cd.ClassName, cd.ClassID, cd.ClassType, cd.ClassDate, cd.ClassMaxfrom class_signups cs RIGHT JOINclassdescription cd on (cs.s_ClassID = cd.ClassID AND cs.s_PersonID 12) WHEREcd.ClassType='firstaid'GROUP BY cd.ClassIDHAVING ClassTotal < cd.ClassMaxIn the join we drop out signups of PersonID 12, so they don't get counted.

Finally we probably want to show the available classes in date order:

SELECT cs.s_ClassID, COUNT(s_ClassID) AS ClassTotal , cd.ClassName, cd.ClassID,cd.ClassType, cd.ClassDate, cd.ClassMaxfrom class_signups cs RIGHT JOINclassdescription cd on (cs.s_ClassID = cd.ClassID AND cs.s_PersonID 12)WHERE cd.ClassType='firstaid'GROUP BY cd.ClassID

HAVING ClassTotal < cd.ClassMax ORDER BY ClassDate

I had fun figuring this out, I hope it works for you.


29/29

(sorry it was so long).Rich

Posted by YI ZHANG on May 10 2006 6:27pm [Delete] [Edit]

If you cancel a long-time running query by Ctrl-C, you might find the CPU load of mysqld remains at 99%. That's because the query is still running on mysqld, and Ctrl-C only closes the client.Now, you can enter mysql again and use command SHOW PROCESSLIST to check the thread of the query, and kill the query by command KILL thread_id.I'm using mysql 5.0.21.

Posted by Wade Bowmer on May 15 2006 1:39am [Delete] [Edit]

Be aware that SQL_CALC_FOUND_ROWS disables ORDER BY ... LIMIT optimizations (seebugs http://bugs.mysql.com/bug.php?id=18454 and http://bugs.mysql.com/bug.php?i

d=19553). Until it's fixed, you should run your own benchmarks with and withoutit.

Add your own comment.Top / Previous / Next / Up / Table of Contents 1995-2006 MySQL AB. All rights reserved.About MySQL Careers Site Map Contact Us Legal Privacy Policy Trademark Info

mysql 13.2.7. select syntax

Documents