Apuntes sobre lenguajes de interrogación de bases de datos y SQL

Índice

ÍNDICE ....... 2

1. ESTÁNDAR ANSI SQL

   3

   1. Lenguaje de definición de datos (DDL)

      4

   2. Lenguaje de manipulación de datos (DML)

      11

   3. Lenguaje de control de datos (DCL)

      25

2. PROCEDIMIENTOS ALMACENADOS

   28

   28

   1. SQL Server

      31

   2. MySQL

      33

   3. Oracle

      35

3. EVENTOS Y DISPARADORES

   37

4. ESTÁNDARES DE CONECTIVIDAD: ODBC Y JDBC

Estándar ANSI SQL

SQL es el lenguaje estándar ANSI/ISO de definición, manipulación y control de bases de datos relaciones. Es un lenguaje declarativo, ya que se indica qué datos obtener, en contraposición con los lenguajes procedimentales, en los que hay que especificar cómo se obtienen los datos.

SQL (STRUCTURES QUERY LANGUAJE, lenguaje estructurado de consultas) es un lenguaje de consulta utilizado para manipular bases de datos relacionales. De los distintos lenguajes que presentaban los primeros SGBDR (Sistemas de Gestión de Bases de Datos Relacionales), ha acabado imponiéndose como estándar, tanto de iure como de facto, el SQL, lenguaje que actualmente ofrecen, aunque con variaciones, la mayor parte de los productos comerciales y que viene siendo objeto, desde hace varios años, de un intenso proceso de normalización.

Fue desarrollado a mediado de los años 70 por IBM. Por esos años se llamaba SEQUEL (Structured English QUEry Language) y servía como interfaz para un sistema experimental de base de datos llamado SYSTEM R. Fue introducido por Oracle por primera vez en una base de datos comercial en el año 1979. El lenguaje se utiliza para definir, consultar y actualizar la base de datos, y es el más popular de su estilo.

En 1982, el ANSI (American National Standars Institute) escogió SQL como lenguaje estándar en 1986 y e ISO (Internacional Standars Organization) en 1987. En 1989, el estándar fue objeto de una revisión y una ampliación que dieron lugar al lenguaje que se conoce con el nombre de SQL1 o SQL:1989.

En 1992, el estándar volvió a ser revisado y ampliado considerablemente para cubrir carencias de la versión anterior. Esta nueva versión de SQL se conoce con el nombre de SQL2 o SQL:1992.

En 1999, apareció la esperada versión SQL3 o SQL:1999, en la que, entre otras cosas, se definía formalmente uno de los componentes lógicos de control más útiles: los disparadores.

AÑO NOMBRE ALIAS COMENTARIOS 1986 SQL-86 SQL-87 Primera publicación por ANSI, confirmada por ISO 1989 SQL-89 SQL1 Revisión menor 1992 SQL-92 SQL2 Revisión mayor 1999 SQL:1999 SQL3 Expresiones regulares, consultas recursivas, disparadores y orientación a objetos 2003 SQL:2003 Características XML, objetos sequence y columnas autonuméricas 2005 SQL:2005 Integración con XML 2008 SQL:2008 Disparadores INSTEAD OF

2011 SQL:2011 Datos temporales 2016 SQL:2016 Compatibilidad ficheros JSON

La última publicación de estándar es ISO/IEC 9075:2016 (IEC significa International Electrotechnical Commission).

Esquema sentencias básicas SQL

DDL Creación -> CREATE Modificación ALTER Eliminación -> DROP Eliminación datos (no tabla) y reseteo contadores TRUNCATE DML Selección SELECT Inserción INSERT Modificación UPDATE Eliminación DELETE DCL Validación transacciones -> COMMIT, ROLLBACK, SAVEPOINT Autorización GRANT, REVOKE

Lenguaje de definición de datos (DDL)

Permite la especificación de un conjunto de relaciones y además de información sobre cada una de las relaciones, incluyendo:

• El esquema de cada relación.
• El dominio de los datos asociados a cada atributo.
• Restricciones de integridad.
• El conjunto de índices que se debe mantener para cada relación.
• Información de seguridad y autorización para cada relación.
• La estructura de almacenamiento físico de cada relación en disco.

Para las sentencias de definición utilizaremos una extensión de la Forma Normal de Backus (BNF) para especificar las cláusulas del lenguaje donde:

• < > representa los símbolos no terminales del lenguaje
• ::= es el operador de definición
• [] indica elementos opcionales
• {} agrupa elementos en una fórmula
• | indica una alternativa
• ... indica repetición

De los distintos elementos que puede contener un esquema relacional en SQL, estudiaremos los dominios, aserciones y tablas y se presentarán las opciones y posibilidades que resultan más interesantes.

Tipos de datos

TIPO DESCRIPCIÓN CHARACTER (longitud) CHAR(longitud) Cadena de caracteres de longitud fija CHARACTER VARYING (longitud) VARCHAR (longitud) Cadena de caracteres de longitud variable BIT (longitud) Cadena de bits de longitud fija BIT VARYING (longitud) Cadena de bits de longitud variable NUMERIC INT Número decimal DECIMAL Número decimal INTEGER Número entero SMALLINT Número entero pequeño Número con coma REAL flotante con precisión predefinida FLOAT (precisión) Número con coma flotante con la precisión especificada DOUBLE PRECISION Número con coma flotante con más precisión predefinida que la de tipo REAL DATE Fecha. Se compone de YEAR, MONTH, DAY TIME Horas. Se compone de HOUR, MINUT, SECOND TIMESTAMP Fechas y horas. Se compone de YEAR, MONTH, DAY, HOUR, MINUT, SECOND

Creación de un esquema

El estándar SQL no dispone de sentencias para la creación de una base de datos. Partiendo de que lo que se pretende es disponer de un conjunto de tablas relacionadas entre sí, se propone una sentencia de creación de esquemas denominada CREATE SCHEMA.

Así pues, un esquema agrupa un conjunto de elementos de la base de datos que son propiedad de un usuario. La sintaxis es:

CREATE SCHEMA {<nombre_esquema>| AUTHORIZATION <usuario>} [<lista_elementos_esquema>];

Esta sentencia permite que un conjunto de tablas <lista_elementos_esquema> se agrupen bajo un mismo nombre, <nombre_esquema>, y que tengan un propietario, <usuario>.

Eliminación de un esquema

La sentencia para borrar un esquema es:

DROP SCHEMA <nombre_esquema> {RESTRICT | CASCADE};

La opción RESTRICT borra el esquema solo si no contiene elemento alguno. Y la opción CASCADE, borra el esquema independientemente de si está vacío o no.

Creación de una tabla

Para crear una tabla hay que utilizar la sentencia CREATE TABLE con el siguiente formato:

CREATE TABLE nombre_tabla (<definicion_columna> [, <definicion_columna> ... ] [, <restricciones_tabla>] );

Donde definicion_columna es:

<nombre_columna> {<tipo_datos>|<dominio>} [<def_defecto>] [<restricciones_columna>]

El proceso que hay que seguir para crear una tabla es:

• Decidir el nombre de la tabla.
• Dar nombre a cada uno de los atributos que formarán las columnas.
• Asignar a cada una de las columnas un tipo de datos predefinido o bien un dominio definido por el usuario, así como el valor por defecto y, si existen, las restricciones de columna.
• Por último, se especifican las restricciones de la tabla.

Definiciones por defecto

La opción <def_defecto> permite especificar que valor dar a una columna cuando no se especifica dato alguno.

Así DEFAULT tiene el siguiente formato:

DEFAULT {<literal>|<función> | NULL}

Ejemplo: columna sueldo con valor por defecto cero.

sueldo INTEGER DEFAULT 0

Ejemplo: columna sueldo con valor por defecto nulo.

sueldo INTEGER DEFAULT NULL

Restricciones de columna

A cada una de las columnas de una tabla, una vez asignado nombre y definido el dominio en el que toma valores, se pueden imponer ciertas restricciones a satisfacer mediante la opción <restricción_columna>:

• NOT NULL: la columna no puede tener valores nulos.
• UNIQUE: la columna no puede tener valores repetidos (clave candidata).
• PRIMARY KEY: la columna no puede tomar valores repetidos ni nulos. Es la clave primaria.
• REFERENCES <nombre_tabla> [(<nombre_columna>)]: la columna es clave foránea de la columna de la tabla especificada.
• CONSTRAINT [<nombre_restriccion>] CHECK (<condiciones>): la columna tiene que cumplir las condiciones especificadas.

Restricciones de tabla

Además de las restricciones que se definen para las columnas de una tabla, también es posible aplicar restricciones a nivel de tabla mediante <restricciones_tabla>, las cuales siempre se tendrán que cumplir. Dichas restricciones pueden ser:

• UNIQUE (<nombre_columna> [, <nombre_columna> ... ]): el conjunto de las columnas especificadas no puede tener valores repetidos (clave candidata).
• PRIMARY KEY (<nombre_columna> [, <nombre_columna> ... ]): el conjunto de las columnas especificadas no puede tener valores nulos ni repetidos. Es la clave primaria.
• FOREIGN KEY (<nombre_columna> [, <nombre_columna> ... ]) REFERENCES <nombre_tabla> ([<nombre_columna> [, <nombre_columna> ... ]]): el conjunto de las columnas especificadas es una clave ajena que referencia a la clave primaria formada por el conjunto de las columnas de la tabla.
• CONSTRAINT [<nombre_restriccion>] CHECK (<condiciones>): la tabla tiene que cumplir las condiciones especificadas.

Tratamiento de claves ajenas

SQL nos ofrece la posibilidad de especificar cuando definimos una clave foránea, la política de actuación al modificar o eliminarse la clave primaria a la que hace referencia.

Acabamos de ver la definición de claves foráneas. Ahora ampliamos su definición:

FOREIGN KEY <clave_foranea> REFERENCES [<clave_primaria>)] [ON DELETE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}] [ON UPDATE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}]

Donde:

• NO ACTION: no se ejecutará ninguna acción.
• CASCADE: se actualizará en cascada la clave ajena.
• SET DEFAULT: se actualizará la clave ajena al valor por defecto.
• SET NULL: se actualizará la clave ajena al valor nulo.

Aserciones

Vistas las restricciones que se pueden especificar al definir una tabla, existen otro tipo de restricciones a nivel global, que se incluirán en este epígrafe. Son las llamadas aserciones, que hacen referencia a una o varias columnas de una o más tablas.

Para definir una aserción utilizamos la sentencia CREATE ASSERTION, con el siguiente formato:

CREATE ASSERTION <nombre_asercion> CHECK (<condiciones>);

Para borrar una aserción utilizamos la sentencia DROP ASSERTION, con el formato:

DROP ASSERTION <nombre_asercion>;

Modificación de una tabla

Para modificar una tabla utilizaremos la sentencia ALTER TALBLE. El formato es el que sigue:

ALTER TABLE <nombre_tabla> {<acción_modificar_columna>| <acción_modificar_restriccion_tabla>};

Modificación de una columna

<acción_modificar_columna> se define como:

{ADD [COLUMN] <nombre_columna> <def_columna> | ALTER [COLUMN] <nombre_columna> {SET <def_defecto>| DROP DEFAULT} | DROP [COLUMN] <nombre_columna> {RESTRICT | CASCADE} }