Sintaxis SQL - SQL syntax

La sintaxis del lenguaje de programación SQL está definida y mantenida por ISO / IEC SC 32 como parte de ISO / IEC 9075 . Este estándar no está disponible gratuitamente. A pesar de la existencia del estándar, el código SQL no es completamente portátil entre diferentes sistemas de bases de datos sin ajustes.

Elementos del lenguaje

${\ Displaystyle \ left. {\ begin {array} {rl} \ scriptstyle {\ mathtt {ACTUALIZAR ~ cláusula}} & \ {{\ mathtt {ACTUALIZAR \ país}} \\\ scriptstyle {\ mathtt {SET ~ cláusula} } & \ {{\ mathtt {SET \ Population = ~}} \ overbrace {\ mathtt {población + 1}} ^ {\ mathtt {expresión}} \\\ scriptstyle {\ mathtt {WHERE ~ cláusula}} & \ { {\ mathtt {DONDE \ \ underbrace {{nombre =} \ overbrace {'USA'} ^ {expresión}} _ {predicado};}} \ end {matriz}} \ right \} {\ scriptstyle {\ texttt {declaración }}}}$

Un gráfico que muestra varios de los elementos del lenguaje SQL que componen una sola declaración. Esto agrega uno a la población de los EE. UU. En la tabla de países.

El lenguaje SQL se subdivide en varios elementos de lenguaje, que incluyen:

• Las palabras clave son palabras que se definen en el lenguaje SQL. Están reservados (por ejemplo SELECT , COUNT y YEAR ) o no reservados (por ejemplo ASC , DOMAIN y KEY ). Lista de palabras reservadas SQL .
• Los identificadores son nombres en objetos de base de datos, como tablas, columnas y esquemas. Un identificador puede no ser igual a una palabra clave reservada, a menos que sea un identificador delimitado. Identificadores delimitados significa identificadores encerrados entre comillas dobles. Pueden contener caracteres que normalmente no se admiten en identificadores SQL y pueden ser idénticos a una palabra reservada, por ejemplo, una columna nombrada YEAR se especifica como "YEAR" .
• Cláusulas , que son componentes constitutivos de declaraciones y consultas. (En algunos casos, estos son opcionales).
• Expresiones , que pueden producir valores escalares o tablas que constan de columnas y filas de datos.
• Predicados , que especifican condiciones que se pueden evaluar con lógica de tres valores de SQL (3VL) (verdadero / falso / desconocido) o valores de verdad booleanos y se utilizan para limitar los efectos de declaraciones y consultas, o para cambiar el flujo del programa.
• Consultas , que recuperan los datos en función de criterios específicos. Este es un elemento importante de SQL .
• Declaraciones , que pueden tener un efecto persistente sobre esquemas y datos, o pueden controlar transacciones, flujo de programa, conexiones, sesiones o diagnósticos.
  • Las sentencias SQL también incluyen el terminador de sentencias con punto y coma (";"). Aunque no es obligatorio en todas las plataformas, se define como una parte estándar de la gramática SQL.
• Los espacios en blanco insignificantes generalmente se ignoran en las declaraciones y consultas de SQL, lo que facilita el formateo del código SQL para su legibilidad.

Operadores

En ocasiones se han sugerido o implementado otros operadores, como el operador de horizonte (para encontrar solo aquellas filas que no son "peores" que las demás).

SQL tiene la case expresión, que se introdujo en SQL-92 . En su forma más general, que se denomina "caso buscado" en el estándar SQL:

CASE WHEN n > 0
          THEN 'positive'
     WHEN n < 0
          THEN 'negative'
     ELSE 'zero'
END

SQL prueba las WHEN condiciones en el orden en que aparecen en la fuente. Si la fuente no especifica una ELSE expresión, SQL toma como valor predeterminado ELSE NULL . También se puede utilizar una sintaxis abreviada llamada "caso simple":

CASE n WHEN 1
            THEN 'One'
       WHEN 2
            THEN 'Two'
       ELSE 'I cannot count that high'
END

Esta sintaxis utiliza comparaciones de igualdad implícitas, con las advertencias habituales para comparar con NULL .

Hay dos formas cortas para CASE expresiones especiales : COALESCE y NULLIF .

La COALESCE expresión devuelve el valor del primer operando no NULL, que se encuentra trabajando de izquierda a derecha, o NULL si todos los operandos son NULL.

COALESCE(x1,x2)

es equivalente a:

CASE WHEN x1 IS NOT NULL THEN x1
     ELSE x2
END

La NULLIF expresión tiene dos operandos y devuelve NULL si los operandos tienen el mismo valor; de lo contrario, tiene el valor del primer operando.

NULLIF(x1, x2)

es equivalente a

CASE WHEN x1 = x2 THEN NULL ELSE x1 END

Comentarios

SQL estándar permite dos formatos para comentarios :, -- comment que termina en la primera línea nueva y /* comment */ , que puede abarcar varias líneas.

Consultas

La operación más común en SQL, la consulta, hace uso de la declaración SELECT declarativa. SELECT recupera datos de una o más tablas o expresiones. Las SELECT declaraciones estándar no tienen efectos persistentes en la base de datos. Algunas implementaciones no estándar de SELECT pueden tener efectos persistentes, como la SELECT INTO sintaxis proporcionada en algunas bases de datos.

Las consultas permiten al usuario describir los datos deseados, dejando que el sistema de gestión de la base de datos (DBMS) lleve a cabo la planificación , optimización y realización de las operaciones físicas necesarias para producir ese resultado como elija.

Una consulta incluye una lista de columnas para incluir en el resultado final, normalmente inmediatamente después de la SELECT palabra clave. Se * puede utilizar un asterisco (" ") para especificar que la consulta debe devolver todas las columnas de las tablas consultadas. SELECT es la declaración más compleja en SQL, con palabras clave y cláusulas opcionales que incluyen:

• La FROM cláusula, que indica la (s) tabla (s) para recuperar datos. La FROM cláusula puede incluir JOIN subcláusulas opcionales para especificar las reglas para unir tablas.
• La WHERE cláusula incluye un predicado de comparación, que restringe las filas devueltas por la consulta. La WHERE cláusula elimina todas las filas del conjunto de resultados donde el predicado de comparación no se evalúa como Verdadero.
• La GROUP BY cláusula proyecta filas que tienen valores comunes en un conjunto más pequeño de filas. GROUP BY se utiliza a menudo junto con funciones de agregación SQL o para eliminar filas duplicadas de un conjunto de resultados. La WHERE cláusula se aplica antes que la GROUP BY cláusula.
• La HAVING cláusula incluye un predicado que se utiliza para filtrar filas resultantes de la GROUP BY cláusula. Dado que actúa sobre los resultados de la GROUP BY cláusula, las funciones de agregación se pueden utilizar en el HAVING predicado de la cláusula.
• La ORDER BY cláusula identifica qué columna (s) usar para ordenar los datos resultantes y en qué dirección ordenarlos (ascendente o descendente). Sin una ORDER BY cláusula, el orden de las filas devueltas por una consulta SQL no está definido.
• La DISTINCT palabra clave elimina los datos duplicados.
• La OFFSET cláusula especifica el número de filas que se deben omitir antes de comenzar a devolver datos.
• La FETCH FIRST cláusula especifica el número de filas a devolver. En cambio, algunas bases de datos SQL tienen alternativas no estándar, por ejemplo LIMIT , TOP o ROWNUM .

Las cláusulas de una consulta tienen un orden de ejecución particular, que se indica con el número del lado derecho. Es como sigue:

El siguiente ejemplo de una SELECT consulta devuelve una lista de libros caros. La consulta recupera todas las filas de la tabla Libro en las que la columna de precio contiene un valor superior a 100,00. El resultado se clasifica en orden ascendente por título . El asterisco (*) en la lista de selección indica que todas las columnas de la tabla Libro deben incluirse en el conjunto de resultados.

SELECT *
 FROM  Book
 WHERE price > 100.00
 ORDER BY title;

El siguiente ejemplo muestra una consulta de varias tablas, agrupación y agregación, al devolver una lista de libros y el número de autores asociados con cada libro.

SELECT Book.title AS Title,
       count(*) AS Authors
 FROM  Book
 JOIN  Book_author
   ON  Book.isbn = Book_author.isbn
 GROUP BY Book.title;

La salida de ejemplo puede parecerse a la siguiente:

Title                  Authors
---------------------- -------
SQL Examples and Guide 4
The Joy of SQL         1
An Introduction to SQL 2
Pitfalls of SQL        1

Bajo la condición previa de que isbn es el único nombre de columna común de las dos tablas y que una columna llamada título solo existe en la tabla Libro , se podría volver a escribir la consulta anterior en la siguiente forma:

SELECT title,
       count(*) AS Authors
 FROM  Book
 NATURAL JOIN Book_author
 GROUP BY title;

Sin embargo, muchos proveedores no admiten este enfoque o requieren ciertas convenciones de nomenclatura de columnas para que las combinaciones naturales funcionen de manera eficaz.

SQL incluye operadores y funciones para calcular valores en valores almacenados. SQL permite el uso de expresiones en la lista de selección para proyectar datos, como en el siguiente ejemplo, que devuelve una lista de libros que cuestan más de 100,00 con una columna sales_tax adicional que contiene una cifra de impuesto sobre las ventas calculada al 6% del precio .

SELECT isbn,
       title,
       price,
       price * 0.06 AS sales_tax
 FROM  Book
 WHERE price > 100.00
 ORDER BY title;

Subconsultas

Las consultas se pueden anidar para que los resultados de una consulta se puedan utilizar en otra consulta mediante un operador relacional o una función de agregación. Una consulta anidada también se conoce como subconsulta . Si bien las combinaciones y otras operaciones de tabla proporcionan alternativas computacionalmente superiores (es decir, más rápidas) en muchos casos, el uso de subconsultas introduce una jerarquía en la ejecución que puede ser útil o necesaria. En el siguiente ejemplo, la función de agregación AVG recibe como entrada el resultado de una subconsulta:

SELECT isbn,
       title,
       price
 FROM  Book
 WHERE price < (SELECT AVG(price) FROM Book)
 ORDER BY title;

Una subconsulta puede usar valores de la consulta externa, en cuyo caso se conoce como subconsulta correlacionada .

Desde 1999, el estándar SQL permite WITH cláusulas para subconsultas, es decir, subconsultas con nombre, generalmente llamadas expresiones de tabla comunes (también llamadas factorización de subconsultas ). Los CTE también pueden ser recursivos al referirse a sí mismos; el mecanismo resultante permite recorridos de árboles o gráficos (cuando se representan como relaciones) y, de manera más general, cálculos de puntos fijos .

Tabla derivada

Una tabla derivada es el uso de hacer referencia a una subconsulta SQL en una cláusula FROM. Básicamente, la tabla derivada es una subconsulta que se puede seleccionar o unir. La funcionalidad de la tabla derivada permite al usuario hacer referencia a la subconsulta como una tabla. La vista en línea también se conoce como vista en línea o subselección .

En el siguiente ejemplo, la instrucción SQL implica una combinación de la tabla "Libro" inicial a la tabla derivada "ventas". Esta tabla derivada captura la información de ventas de libros asociada utilizando el ISBN para unirse a la tabla "Libro". Como resultado, la tabla derivada proporciona el conjunto de resultados con columnas adicionales (el número de artículos vendidos y la empresa que vendió los libros):

SELECT b.isbn, b.title, b.price, sales.items_sold, sales.company_nm
FROM Book b
  JOIN (SELECT SUM(Items_Sold) Items_Sold, Company_Nm, ISBN
        FROM Book_Sales
        GROUP BY Company_Nm, ISBN) sales
  ON sales.isbn = b.isbn

Lógica nula o de tres valores (3VL)

El concepto de nulo permite que SQL se ocupe de la información que falta en el modelo relacional. La palabra NULL es una palabra clave reservada en SQL, que se utiliza para identificar el marcador especial Null. Las comparaciones con Null, por ejemplo, la igualdad (=) en las cláusulas WHERE, dan como resultado un valor de verdad desconocido. En las sentencias SELECT, SQL solo devuelve resultados para los que la cláusula WHERE devuelve un valor de True; es decir, excluye los resultados con valores de Falso y también excluye aquellos cuyo valor es Desconocido.

Junto con Verdadero y Falso, el Desconocido resultante de comparaciones directas con Nulo trae un fragmento de lógica de tres valores a SQL. Las tablas de verdad que usa SQL para AND, OR y NOT corresponden a un fragmento común de la lógica de tres valores de Kleene y Lukasiewicz (que difieren en su definición de implicación, sin embargo, SQL no define tal operación).

Sin embargo, existen disputas sobre la interpretación semántica de Nulls en SQL debido a su tratamiento fuera de las comparaciones directas. Como se ve en la tabla anterior, las comparaciones directas de igualdad entre dos NULL en SQL (por ejemplo {{{1}}} ) devuelven un valor de verdad de Desconocido. Esto está en línea con la interpretación de que Null no tiene un valor (y no es miembro de ningún dominio de datos) sino que es un marcador de posición o "marca" para la información faltante. Sin embargo, el principio de que dos nulos no son iguales entre sí se viola de manera efectiva en la especificación SQL para los operadores UNION y INTERSECT , que identifican nulos entre sí. En consecuencia, estas operaciones de conjunto en SQL pueden producir resultados que no representan información segura, a diferencia de las operaciones que involucran comparaciones explícitas con NULL (por ejemplo, las de una WHERE cláusula discutida anteriormente). En la propuesta de Codd de 1979 (que fue básicamente adoptada por SQL92) esta inconsistencia semántica se racionaliza argumentando que la eliminación de duplicados en operaciones de conjuntos ocurre "en un nivel de detalle más bajo que las pruebas de igualdad en la evaluación de operaciones de recuperación". Sin embargo, el profesor de ciencias de la computación Ron van der Meyden concluyó que "las inconsistencias en el estándar SQL significan que no es posible atribuir ninguna semántica lógica intuitiva al tratamiento de nulos en SQL".

Además, debido a que los operadores de SQL devuelven Desconocido cuando comparan algo con Null directamente, SQL proporciona dos predicados de comparación específicos de Null: IS NULL y IS NOT NULL prueba si los datos son o no Null. SQL no admite explícitamente la cuantificación universal y debe resolverlo como una cuantificación existencial negada . También existe el operador de comparación infijo "<expresión de valor de fila> IS DISTINCT FROM <expresión de valor de fila>", que devuelve VERDADERO a menos que ambos operandos sean iguales o ambos sean NULL. Asimismo, NO ES DISTINTO DE se define como "NO (<expresión de valor de fila> ES DISTINTO DE <expresión de valor de fila>)". SQL: 1999 también introdujo BOOLEAN variables de tipo, que de acuerdo con el estándar también pueden contener valores desconocidos si son anulables. En la práctica, varios sistemas (por ejemplo, PostgreSQL ) implementan BOOLEAN Unknown como BOOLEAN NULL, que el estándar dice que NULL BOOLEAN y UNKNOWN "pueden usarse indistintamente para significar exactamente lo mismo". C. Fecha (2011). SQL y teoría relacional: cómo escribir código SQL preciso . O'Reilly Media, Inc. pág. 83. ISBN   978-1-4493-1640-2 . </ref>

Manipulación de datos

El lenguaje de manipulación de datos (DML) es el subconjunto de SQL que se utiliza para agregar, actualizar y eliminar datos:

• INSERT agrega filas (formalmente tuplas ) a una tabla existente, por ejemplo:

INSERT INTO example
 (column1, column2, column3)
 VALUES
 ('test', 'N', NULL);

• UPDATE modifica un conjunto de filas de tabla existentes, por ejemplo:

UPDATE example
 SET column1 = 'updated value'
 WHERE column2 = 'N';

• DELETE elimina filas existentes de una tabla, por ejemplo:

DELETE FROM example
 WHERE column2 = 'N';

• MERGE se utiliza para combinar los datos de varias tablas. Combina los elementos INSERT y UPDATE . Está definido en el estándar SQL: 2003; antes de eso, algunas bases de datos proporcionaban una funcionalidad similar a través de una sintaxis diferente, a veces llamada " upsert ".

 MERGE INTO table_name USING table_reference ON (condition)
 WHEN MATCHED THEN
 UPDATE SET column1 = value1 [, column2 = value2 ...]
 WHEN NOT MATCHED THEN
 INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...])

Controles de transacciones

Las transacciones, si están disponibles, envuelven las operaciones de DML:

• START TRANSACTION (o BEGIN WORK , o BEGIN TRANSACTION , según el dialecto SQL) marca el inicio de una transacción de base de datos , que se completa por completo o no se completa.
• SAVE TRANSACTION (o SAVEPOINT ) guarda el estado de la base de datos en el punto actual de la transacción

CREATE TABLE tbl_1(id int);
 INSERT INTO tbl_1(id) VALUES(1);
 INSERT INTO tbl_1(id) VALUES(2);
COMMIT;
 UPDATE tbl_1 SET id=200 WHERE id=1;
SAVEPOINT id_1upd;
 UPDATE tbl_1 SET id=1000 WHERE id=2;
ROLLBACK to id_1upd;
 SELECT id from tbl_1;

• COMMIT hace que todos los cambios de datos en una transacción sean permanentes.
• ROLLBACK descarta todos los cambios de datos desde el último COMMIT o ROLLBACK , dejando los datos como estaban antes de esos cambios. Una vez COMMIT que se completa el extracto, los cambios de la transacción no se pueden revertir.

COMMIT y ROLLBACK terminar la transacción actual y liberar bloqueos de datos. En ausencia de una START TRANSACTION declaración o similar, la semántica de SQL depende de la implementación. El siguiente ejemplo muestra una transacción clásica de transferencia de fondos, donde el dinero se retira de una cuenta y se agrega a otra. Si falla la eliminación o la adición, se revierte toda la transacción.

START TRANSACTION;
 UPDATE Account SET amount=amount-200 WHERE account_number=1234;
 UPDATE Account SET amount=amount+200 WHERE account_number=2345;

IF ERRORS=0 COMMIT;
IF ERRORS<>0 ROLLBACK;

Definición de datos

El lenguaje de definición de datos (DDL) gestiona la estructura de índices y tablas. Los elementos básicos de la mayoría de los DDL son CREATE , ALTER , RENAME , DROP y TRUNCATE declaraciones:

• CREATE crea un objeto (una tabla, por ejemplo) en la base de datos, por ejemplo:

CREATE TABLE example(
 column1 INTEGER,
 column2 VARCHAR(50),
 column3 DATE NOT NULL,
 PRIMARY KEY (column1, column2)
);

• ALTER modifica la estructura de un objeto existente de varias formas, por ejemplo, agregando una columna a una tabla existente o una restricción, por ejemplo:

ALTER TABLE example ADD column4 INTEGER DEFAULT 25 NOT NULL;

• TRUNCATE elimina todos los datos de una tabla de una manera muy rápida, eliminando los datos dentro de la tabla y no la tabla en sí. Por lo general, implica una operación COMMIT posterior, es decir, no se puede revertir (los datos no se escriben en los registros para revertirlos más tarde, a diferencia de DELETE).

TRUNCATE TABLE example;

• DROP elimina un objeto en la base de datos, generalmente de forma irrecuperable, es decir, no se puede deshacer, por ejemplo:

DROP TABLE example;

Tipos de datos

Cada columna de una tabla SQL declara los tipos que esa columna puede contener. ANSI SQL incluye los siguientes tipos de datos.

Cadenas de caracteres y cadenas de caracteres nacionales

• CHARACTER(n) (o ): cadena de n caracteres de ancho fijo , rellenada con espacios según sea necesario CHAR(n)
• CHARACTER VARYING(n) (o ): cadena de ancho variable con un tamaño máximo de n caracteres VARCHAR(n)
• CHARACTER LARGE OBJECT(n [ K | M | G | T ]) (o ): objeto de carácter grande con un tamaño máximo de n [K | M | G | T] caracteres CLOB(n [ K | M | G | T ])
• NATIONAL CHARACTER(n) (o ): cadena de ancho fijo que admite un juego de caracteres internacional NCHAR(n)
• NATIONAL CHARACTER VARYING(n) (o ): cadena de ancho variable NVARCHAR(n)NCHAR
• NATIONAL CHARACTER LARGE OBJECT(n [ K | M | G | T ]) (o ): objeto grande de carácter nacional con un tamaño máximo de n [K | M | G | T] caracteres NCLOB(n [ K | M | G | T ])

Para los tipos de datos CHARACTER LARGE OBJECT y NATIONAL CHARACTER LARGE OBJECT , los multiplicadores K (1 024), M (1 048 576), G (1 073 741 824) y T (1 099 511 627 776) se pueden utilizar opcionalmente al especificar la longitud.

Binario

• BINARY(n) : Cadena binaria de longitud fija, longitud máxima n .
• BINARY VARYING(n) (o ): Cadena binaria de longitud variable, longitud máxima n . VARBINARY(n)
• BINARY LARGE OBJECT(n [ K | M | G | T ]) (o ): objeto binario grande con una longitud máxima n [K | M | G | T] . BLOB(n [ K | M | G | T ])

Para el BINARY LARGE OBJECT tipo de datos, los multiplicadores K (1 024), M (1 048 576), G (1 073 741 824) y T (1 099 511 627 776) se pueden utilizar opcionalmente al especificar la longitud.

Booleano

• BOOLEAN

El BOOLEAN tipo de datos puede almacenar los valores TRUE y FALSE .

Numérico

• INTEGER (o INT ), SMALLINT y BIGINT
• FLOAT , REAL y DOUBLE PRECISION
• NUMERIC(precision, scale) o DECIMAL(precision, scale)
• DECFLOAT(precision )

Por ejemplo, el número 123.45 tiene una precisión de 5 y una escala de 2. La precisión es un entero positivo que determina el número de dígitos significativos en una base particular (binaria o decimal). La escala es un número entero no negativo. Una escala de 0 indica que el número es un entero. Para un número decimal con la escala S, el valor numérico exacto es el valor entero de los dígitos significativos dividido por 10 ^S .

SQL proporciona las funciones CEILING y FLOOR para redondear valores numéricos. (Las funciones específicas de los proveedores populares son TRUNC (Informix, DB2, PostgreSQL, Oracle y MySQL) e ROUND (Informix, SQLite, Sybase, Oracle, PostgreSQL, Microsoft SQL Server y Mimer SQL.))

Temporal (fecha y hora)

• DATE : para valores de fecha (p 2011-05-03 . ej .).
• TIME : para valores de tiempo (p 15:51:36 . ej .).
• TIME WITH TIME ZONE : lo mismo que TIME , pero con detalles sobre la zona horaria en cuestión.
• TIMESTAMP : Esto es un DATE y un TIME juntos en una variable (p 2011-05-03 15:51:36.123456 . Ej .).
• TIMESTAMP WITH TIME ZONE : lo mismo que TIMESTAMP , pero con detalles sobre la zona horaria en cuestión.

La función SQL EXTRACT se puede utilizar para extraer un solo campo (segundos, por ejemplo) de una fecha y hora o un valor de intervalo. El sistema de fecha / hora actual del servidor de base de datos puede ser llamado mediante el uso de funciones como CURRENT_DATE , CURRENT_TIMESTAMP , LOCALTIME , o LOCALTIMESTAMP . (Funciones populares son específicos del proveedor TO_DATE , TO_TIME , TO_TIMESTAMP , YEAR , MONTH , DAY , HOUR , MINUTE , SECOND , DAYOFYEAR , DAYOFMONTH y DAYOFWEEK .)

Intervalo (fecha y hora)

• YEAR(precision) : varios años
• YEAR(precision) TO MONTH : varios años y meses
• MONTH(precision) : varios meses
• DAY(precision) : varios días
• DAY(precision) TO HOUR : varios días y horas
• DAY(precision) TO MINUTE : varios días, horas y minutos
• DAY(precision) TO SECOND(scale) : una cantidad de días, horas, minutos y segundos
• HOUR(precision) : varias horas
• HOUR(precision) TO MINUTE : una cantidad de horas y minutos
• HOUR(precision) TO SECOND(scale) : una cantidad de horas, minutos y segundos
• MINUTE(precision) : una cantidad de minutos
• MINUTE(precision) TO SECOND(scale) : una cantidad de minutos y segundos

Control de datos

El lenguaje de control de datos (DCL) autoriza a los usuarios a acceder y manipular datos. Sus dos declaraciones principales son:

• GRANT autoriza a uno o más usuarios a realizar una operación o un conjunto de operaciones en un objeto.
• REVOKE elimina una subvención, que puede ser la subvención predeterminada.

Ejemplo:

GRANT SELECT, UPDATE
 ON example
 TO some_user, another_user;

REVOKE SELECT, UPDATE
 ON example
 FROM some_user, another_user;

Notas