Descarga la aplicación para disfrutar aún más
Vista previa del material en texto
CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 1 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca OPERACIONES EN EL MODELO RELACIONAL SQL - LENGUAJE DE CONSULTA ESTRUCTURADO EJE CONCEPTUAL Realizar operaciones con el modelo relacional y traducirlas a lenguajes estándar en las Bases de Datos Relacionales OBJETIVOS Que el alumno logre: • Manejar las operaciones del Algebra Relacional y del Cálculo Relacional • Manejar el estándar SQL: para la definición, manipulación de datos, administración de usuarios y seguridad en Bases de Datos Relacionales • Manejar interfaces QBE para las consultas de usuarios a las Bases de Datos TEMAS INTRODUCCIÓN ............................................................................ 3 ORÍGENES Y EVOLUCIÓN ................................................................................ 4 CARACTERÍSTICAS GENERALES ......................................................................... 6 OPTIMIZACIÓN ........................................................................................... 6 COMPONENTES DEL SQL ................................................................................ 6 LENGUAJE DE DEFINICIÓN DE DATOS (DDL) ........................................... 7 DICCIONARIO DE DATOS ................................................................................ 7 OPERACIONES BÁSICAS DEL DDL ..................................................................... 7 CREACIÓN Y BORRADO DE UNA BASE DE DATOS RELACIONAL ..................................... 8 CREACIÓN DE TABLAS ................................................................................... 9 TIPOS DE DATOS ....................................................................................... 10 CREACIÓN, MODIFICACIÓN Y BORRADO DE DOMINIOS ............................................. 11 DEFINICIONES POR DEFECTO ......................................................................... 13 RESTRICCIONES DE COLUMNA ........................................................................ 14 RESTRICCIONES DE TABLA ............................................................................ 14 MODIFICACIÓN Y BORRADO DE CLAVES PRIMARIAS CON CLAVES FORÁNEAS QUE HACEN REFERENCIA A ÉSTAS .................................................................................. 16 ASERCIONES............................................................................................ 17 CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 2 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca MODIFICACIÓN Y BORRADO DE TABLAS ............................................................. 18 CREACIÓN Y BORRADO DE VISTAS ................................................................... 20 BORRADO DEL CONTENIDO DE UNA TABLA ......................................................... 21 LENGUAJE DE MANIPULACIÓN DE DATOS (DML) ...................................... 22 OPERACIONES BÁSICAS DEL DML ................................................................... 22 CONSULTAS SIMPLES A UNA BASE DE DATOS RELACIONAL ........................................ 23 PALABRAS Y SIMBOLOS PARA LAS CONSULTAS ............................................................ 24 INSERCIÓN DE FILAS EN UNA TABLA ................................................................. 25 INSERCIÓN DE UNA FILA EN UNA TABLA .................................................................... 25 INSERCIÓN DE MÚLTIPLES FILAS EN UNA TABLA .......................................................... 26 COPIAR FILAS DE OTRAS TABLAS ............................................................................ 26 MODIFICACIÓN DE FILAS DE UNA TABLA............................................................. 27 BORRADO DE FILAS DE UNA TABLA ................................................................... 28 CONSULTAS DE MAYOR COMPLEJIDAD A UNA BASE DE DATOS RELACIONAL ..................... 29 SUBCONSULTAS ............................................................................................... 29 OTROS PREDICADOS .......................................................................................... 30 ORDEN DE LAS FILAS OBTENIDAS EN RESPUESTAS A CONSULTAS ...................................... 34 CONSULTAS CON AGRUPACIÓN DE FILAS DE UNA TABLA ................................................. 35 FUNCIONES DE AGREGACIÓN ................................................................................ 35 CONSULTAS A MÁS DE UNA TABLA ................................................................... 38 PRODUCTO CARTESIANO ..................................................................................... 38 JOIN NATURAL ................................................................................................. 40 JOIN INTERNO Y EXTERNO ................................................................................... 41 COMBINACIONES CON MÁS DE DOS TABLAS ............................................................... 43 UNIÓN .......................................................................................................... 44 INTERSECCIÓN ................................................................................................. 45 DIFERENCIA .................................................................................................... 47 LENGUAJE DE CONTROL DE DATOS (DCL) ............................................ 49 USUARIOS .............................................................................................. 49 LAS AUTORIZACIONES Y DENEGACIONES DE PRIVILEGIOS ......................................... 49 AUTORIZACIONES ............................................................................................. 49 DENEGACIONES ................................................................................................ 50 LENGUAJE DE CONTROL DE TRANSACCIONES (TCL) ................................. 52 LAS TRANSACCIONES .................................................................................. 52 ANEXO I: ENUNCIADO Y MODELO CONCEPTUAL ...................................... 54 CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 3 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca INTRODUCCIÓN La llegada y la implementación de las BASES DE DATOS RELACIONALES trajo consigo la necesidad de un lenguaje de base de datos que fuera suficientemente amistoso para el usuario, a la vez que adecuado para el programador y el creador de aplicaciones. El Lenguaje de consulta estructurado (SQL Structured Query Language) es un lenguaje declarativo(1) que da acceso a bases de datos relacionales y que permite especificar diversos tipos de operaciones sobre las mismas. Una de sus características es el manejo del álgebra y el cálculo relacional permitiendo hacer: • consultas con el fin de recuperar información • cambios con el fin de agregar, borrar y modificar información en una base de datos. Es un lenguaje de cuarta generación (4GL). Se puede intercalar en los lenguajes procedimentales(2) como C, Cobol, entre otros, actualmente es utilizado en los lenguajes orientados a objetos como Java, Power Builder, etc.. (1) Lenguajes declarativos solo hay que indicar que se quiere hacer. (2) Lenguajes procedimentales, es necesario especificar cómo hay que hacer cualquier acción sobre la BD. CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJEDE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 4 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca ORÍGENES Y EVOLUCIÓN Cuando E. F. Codd introdujo el concepto de Base de Datos relacionales en 1970, indicó: “la adopción de un modelo relacional de datos...permite el desarrollo de un sublenguaje universal de datos basado en el cálculo aplicado a los predicados” Luego hasta 1974, que apareció el artículo de Chamberlain y Boyce en el que sugerían la forma de un lenguaje de consulta estructurado, al que en el momento se llamó SEQUEL (Structured English QUEry Language). En 1975, Boyce, Chamberlin, King y Hammer publicaron un artículo en el que enunciaban el sublenguaje SQUARE, que era muy parecido al SEQUEL, excepto que SQUARE utilizaba expresiones matemáticas en vez de los términos ingleses de SEQUEL. Al artículo SQUARE le siguió otro de Chamberlin y otros en 1976, cuando se cambió el nombre SEQUEL 2, y se utilizó como lenguaje de consulta para la investigación de la base de datos System R de IBM. Relational Software, Inc. (Oracle Corporation) creó un producto denominado Oracle en el año 1979. Cuando Chamberlin escribió en 1980 un resumen de la experiencia de los usuarios con este lenguaje, el nombre ya se había cambiado a su forma actual: SQL, que denota un Lenguaje de Consulta Estructurado. El SEQUEL terminaría siendo el predecesor de SQL, siendo éste una versión evolucionada del primero. El hecho de que los que los desarrolladores supieran de antemano lo que debía ser el SQL y lo que se requería que hiciese, le dio una fuerte base teórica. Este hecho, dio lugar a un lenguaje que consta de relativamente pocos comandos que se pueden utilizar para satisfacer la mayoría de las necesidades de una base de datos muy compleja. Su sencillez hace que SQL sea adecuado tanto para el usuario ocasional como para el desarrollador avanzado. Se puede usar para consultas “ad hoc” y también se puede intercalar en un programa de aplicación. En 1986 el ANSI, da lugar a la primera versión estándar de este lenguaje, el SQL-86 o SQL1. Al año siguiente este estándar es también adoptado por la ISO. Sin embargo este primer estándar no cubrió todas las necesidades de los desarrolladores e incluyó funcionalidades de definición de almacenamiento que se consideraron suprimir. Así que en 1992 se lanza un nuevo estándar ampliado y revisado del SQL llamado SQL-92 o SQL2. En la actualidad el SQL es el estándar de la mayoría de los SGBD comerciales. Y, aunque la diversidad de agregados particulares que incluyen las distintas implementaciones comerciales del lenguaje es amplia, el soporte al estándar SQL-92 es general y muy amplio. CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 5 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca El ANSI SQL tuvo varias revisiones y agregados a lo largo del tiempo: Año Nombre Alias Comentarios 1986 SQL-86 SQL-87 Primera publicación hecha por ANSI. Confirmada por ISO en 1987. 1989 SQL-89 Revisión menor. 1992 SQL-92 SQL2 Revisión mayor. 1999 SQL:1999 SQL2000 Se agregaron expresiones regulares, consultas recursivas (para relaciones jerárquicas), triggers y algunas características orientadas a objetos. 2003 SQL:2003 Introduce algunas características de XML, cambios en las funciones, estandarización del objeto sequence y de las columnas autonuméricas. 2006 SQL:2006 ISO/IEC 9075-14:2006 Define las maneras en las cuales el SQL se puede utilizar conjuntamente con XML. Define maneras importar y guardar datos XML en una base de datos SQL, manipulándolos dentro de la base de datos y publicando el XML y los datos SQL convencionales en forma XML. Además, proporciona facilidades que permiten a las aplicaciones integrar dentro de su código SQL el uso de Xquery, lenguaje de consulta XML publicado por el W3C (World Wide Web Consortium) para acceso concurrente a datos ordinarios SQL y documentos XML. El estándar existente actualmente representa tanto un subconjunto de las principales implementaciones comunes como un superconjunto de casi todas las implementaciones. Es decir, el núcleo del estándar consta de características que podemos encontrar virtualmente en cada implementación comercial del lenguaje, aunque el estándar completo incluye características mejoradas que muchos vendedores ya han implementado. CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 6 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca CARACTERÍSTICAS GENERALES El SQL es un lenguaje de acceso a bases de datos que explota la flexibilidad y potencia de los sistemas relacionales permitiendo gran variedad de operaciones sobre los mismos. Es un lenguaje declarativo de alto nivel o de no procedimiento, que gracias a su fuerte base teórica y su orientación al manejo de conjuntos de registros, y no a registros individuales, permite una alta productividad en codificación. De esta forma una sola sentencia puede equivaler a uno o más programas que utilizas en un lenguaje de bajo nivel. OPTIMIZACIÓN Es un lenguaje declarativo, que especifica qué es lo que se quiere y no cómo conseguirlo, por lo que una sentencia no establece explícitamente un orden de ejecución. El orden de ejecución interno de una sentencia puede afectar gravemente a la eficiencia del SGBD, por lo que se hace necesario que éste lleve a cabo una optimización antes de la ejecución de la misma. Muchas veces, el uso de índices acelera una instrucción de consulta, pero ralentiza la actualización de los datos. Dependiendo del uso de la aplicación, se priorizará el acceso indexado o una rápida actualización de la información. La optimización difiere sensiblemente en cada motor de base de datos y depende de muchos factores. COMPONENTES DEL SQL Aún cuando se describe a SQL como un lenguaje de consulta, en realidad es mucho más que eso, porque dispone de muchas otras posibilidades además de las de consultar una base de datos. Cada posibilidad tiene su conjunto de instrucciones propias que se expresan: • Lenguaje de Definición de datos (DDL) • Lenguaje de Manejo de datos (DML) • Lenguaje de Control de datos (DCL) • Lenguaje de Control de Transacciones (TCL) CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 7 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca LENGUAJE DE DEFINICIÓN DE DATOS (DDL) El lenguaje de definición de datos (en inglés Data Definition Language, o DDL), es el que se encarga de la modificación de la estructura de los objetos de la base de datos. Los nombres se almacenan inmediatamente después de su creación|modificación|borrado, en el diccionario de datos. DICCIONARIO DE DATOS El Diccionario de Datos o catálogo, es una base de datos del sistema que contiene información sobre las bases de datos, las tablas, las vistas, los derechos de acceso, usuarios y demás elementos del motor de base de datos. El ANSI indica que el diccionario de datos debe ser accedido a través de una serie de vistas conocidas como INFORMATION_SCHEMA. Se puede consultar utilizando las instrucciones SELECT del SQL. Su finalidad es proporcionar información relativa a la estructura de las base de datos. El usuario normalmente no puede aplicar al catálogo los comandos del DML: UPDATE, INSERT, DELETE, porque el funcionamiento de la base de datos depende de él, cualquier cambio que se lleve a cabo por el usuario podríadestruir la integridad de la base de datos. Los cambios en el diccionario de datos se realizan en forma indirecta a través de los comandos DDL: CREATE, DROP, ALTER y DCL: GRANT, REVOKE. OPERACIONES BÁSICAS DEL DDL Existen cuatro operaciones básicas: • CREATE ->para crear base de datos, tablas, dominios, aserciones y vistas • ALTER -> para modificar tablas y dominios • DROP -> para borrar base de datos, tablas, dominios aserciones y vistas • TRUNCATE.-> para eliminar datos de una tabla Para ejemplificar la aplicación de las sentencias de SQL utilizaremos el motor de base de datos de MySQL y una base de datos de ejemplo de la empresa “Tintorería siempre limpia” de ahora en más la denominaremos Ejemplo TSL. Ver narrativa y Modelo Conceptual en el Anexo I. Los scripts para generación de la base se encuentran a disposición de los alumnos en el eGroup de la Cátedra. CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 8 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca DROP DATABASE Muchos de los sistemas relacionales comerciales (como por ejemplo Informix, DB2, SQL Server y otros) han incorporado sentencias de borrado de bases de datos con la siguiente sintaxis: DROP DATABASE CREACIÓN Y BORRADO DE UNA BASE DE DATOS RELACIONAL CREACIÓN El estándar SQL92 no dispone de ninguna sentencia de creación de bases de datos. Una base de datos no es más que un conjunto de tablas y, por lo tanto, las sentencias que nos ofrece el SQL92 se concentran en la creación, la modificación y el borrado de estas tablas. Sí disponemos de una sentencia más potente que la de creación de bases de datos: la sentencia de creación de esquemas denominada CREATE SCHEMA. Con la creación de esquemas podemos agrupar un conjunto de elementos de la base de datos que son propiedad de un usuario. CREATE SCHEMA {[nombre_esquema]} | [AUTHORIZATION usuario]} [lista_de_elementos_del_esquema]; La sentencia de creación de esquemas hace que varias tablas lista_de_elementos_del_esquema se puedan agrupar bajo un mismo nombre (nombre_esquema) y que tengan un propietario (usuario). Aunque todos los parámetros de la sentencia CREATE SCHEMA son opcionales, como mínimo se debe dar o bien el nombre del esquema, o bien el nombre del usuario propietario de la base de datos. Si sólo especificamos el usuario, éste será el nombre del esquema. La creación de esquemas puede hacer mucho más que agrupar tablas, porque lista_de_elementos_del_esquema puede, además de tablas, ser también dominios, vistas, privilegios y restricciones, entre otras cosas. Ejemplo TSL: CREATE DATABASE ‘ropa_siempre_limpia’ En MySQL debe registrarse la Base de Datos para que …. BORRADO El estándar SQL92 sólo nos ofrece la sentencia de borrado de esquemas DROP SCHEMA, que presenta la siguiente sintaxis: DROP SCHEMA nombre_esquema {RESTRICT|CASCADE}; Donde: CREATE DATABASE Muchos de los sistemas relacionales comerciales (como ocurre en el caso de Informix, DB2, SQL Server y otros) han incorporado sentencias de creación de bases de datos con la siguiente sintaxis: CREATE DATABASE CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 9 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca RESTRICT hace que el esquema sólo se pueda borrar si no contiene ningún elemento. CASCADE borra el esquema aunque no esté completamente vacío. Ejemplo TSL: DROP DATABASE ‘ropa_siempre_limpia’ CREACIÓN DE TABLAS La estructura de almacenamiento de los datos del modelo relacional son las tablas CREATE TABLE nombre_tabla ( definición_columna [, definición_columna...] [, restricciones_tabla]); Donde definición_columna es: nombre_columna {tipo_datos|dominio} [def_defecto] [restric_col] A cada una de las columnas se le asigna un tipo de datos predefinido o bien un dominio definido por el usuario. También podremos dar definiciones por defecto y restricciones de columna. Una vez definidas las columnas, sólo nos quedará dar las restricciones de tabla. Ejemplo TSL para la creación de la tabla Clientes CREATE TABLE `clientes’ ( ‘tipo_doc’ varchar(4) NOT NULL `nro_doc’ int(11) NOT NULL ‘nom_ape’ varchar(50) NOT NULL, ‘tel’ VARCHAR(2) default NULL, `dir`varchar(50) default NULL CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 10 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca Tipos de datos predefinidos Tipos de datos Descripción CHARACTER (longitud) Cadenas de caracteres de longitud fija. CHARACTER VARYING (longitud) Cadenas de caracteres de longitud variable. CHARACTER LARGE OBJECT Cadena de caracteres de longitud variable hasta el máximo definido por la implementación de la BD BIT (longitud) Cadenas de bits de longitud fija. BOOLEANO (bit(1)) V o F BINARY LARGE OBJECT Cadena de bit de longitud variable hasta el max permitido por la implementación. (permite guardar imágenes) BIT VARYING (longitudb) Cadenas de bits de longitud variables. NUMERIC (precisión, escala) Número decimales con tantos dígitos Como indique la precisión y tantos decimales como DECIMAL (precisión, escala) Número decimales con tantos dígitos Como indique la precisión y tantos decimales como INTEGER Números enteros. SMALLINT Números enteros pequeños. REAL Números con coma flotante con precisión predefinida. FLOAT (precisión) Números con coma flotante con la precisión especificada. DOUBLE PRECISION Números con coma flotante con más precisión predefinida que la del tipo REAL. DATE Fechas. Están compuestas de: YEAR año, MONTH mes, DAY día. TIME Horas. Están compuestas de HOUR hora, MINUT minutos, SECOND segundos y fraccion de Segundos TIMESTAMP Fechas y horas. Están compuestas de YEAR año, MONTH mes, DAY día, HOUR hora, MINUT minutos, SECOND segundos. TIPOS DE DATOS Para cada columna tenemos que elegir entre algún dominio definido por el usuario o alguno de los tipos de datos predefinidos que se describen a continuación: CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 11 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca CREACIÓN, MODIFICACIÓN Y BORRADO DE DOMINIOS Además de los dominios dados por el tipo de datos predefinidos, el SQL92 nos ofrece la posibilidad de trabajar con dominios definidos por el usuario. CREACIÓN Para crear un dominio es necesario utilizar la sentencia CREATE DOMAIN: CREATE DOMAIN nombre dominio [AS] tipos_datos [def_defecto] [restricciones_dominio]; Donde restricciones_dominio tiene el siguiente formato: [CONSTRAINT nombre_restricción] CHECK (condiciones) Ejemplo TSL: Si quisiéramos definir un dominio para las ciudades donde se encuentran los clientes para el ejemplo TSL, partiendo que la empresa no tomará prendas a limpiar de clientes que no pertenezcan a las ciudades definidas: CREATE DOMAIN dom_ciudades AS CHAR (20) CONSTRAINT ciudades_validas CHECK (VALUE IN (‘Rosario’, ‘Perez’, ‘Galvez’, ‘Funes’)); De este modo, cuando definimos la columna ciudades dentro de la tabla clientes no se tendrá que decir que es de tipo CHAR (50), sino de tipo dom_ciudades. La creación de un dominio como el del ejemplo nos debería asegurar, segúnel modelo relacional, que sólo haremos operaciones sobre la columna ciudades con otras columnas que tengan este mismo dominio definido por el usuario; sin embargo, el SQL92 no nos ofrece herramientas para asegurar que las comparaciones que hacemos sean entre los mismos dominios definidos por el usuario. Por ejemplo, si tenemos una columna con los nombres de los empleados definida sobre el tipo de datos CHAR (50), el SQL nos permite compararla con la columna ciudades, aunque semánticamente no tenga sentido. Según el modelo relacional, esta comparación no se debería haber permitido. CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 12 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca BORRADO Para borrar un dominio definido por el usuario DROP DOMAIN: DROP DOMAIN nombre_dominio {RESTRICT|CASCADE}; Donde: RESTRICT hace que el dominio sólo se pueda borrar si no se utiliza en ningún sitio. CASCADE borra el dominio aunque esté referenciado, y pone el tipo de datos del dominio allí donde se utilizaba. MODIFICACIÓN Para modificar un dominio semántico es necesario utilizar la sentencia ALTER DOMAIN. ALTER DOMAIN nombre_dominio {acción_modificar_dominio| acción_modif_restricción_dominio}; donde acción_modif_restricción_dominio puede ser: {ADD restricciones_dominio|DROP CONSTRAINT nombre_restricción} Ejemplo TSL: Si quisiéramos añadir una nueva ciudad (Villa Constitución) al dominio que hemos creado antes para las ciudades donde se encuentran los clientes: ALTER DOMAIN dom_ciudades DROP CONSTRAINT ciudades_validas; Con esto hemos eliminado la restricción de dominio antigua. Y ahora tenemos que introducir la nueva restricción: ALTER_DOMAIN dom_ciudades ADD CONSTRAINT ciudades_validas CHECK(VALUE IN (‘Rosario’, ‘Perez’, ‘Galvez’, ‘Funes’, ‘Villa Constitución’)); CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 13 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca DEFINICIONES POR DEFECTO La opción por defecto nos permite especificar qué nomenclatura queremos dar a nuestros valores por omisión. Hay que tener en cuenta que si elegimos la opción DEFAULT NULL, la columna para la que daremos la definición por defecto de valor nulo debería admitir valores nulos. La opción DEFAULT tiene el siguiente formato: DEFAULT (literal|función|NULL) La opción más utilizada y por defecto, es la palabra reservada NULL. También podemos definir nuestro propio literal, o bien recurrir a una de las funciones que aparecen en la tabla siguiente: Función Descripción {USER|CURRENT_USER} Identificador del usuario actual SESSION_USER Identificador del usuario de esta sesión SYSTEM_USER Identificador del usuario del sistema CURRENT_DATE Fecha actual CURRENT_TIME Hora actual CURRENT_TIMESTAMP Fecha y hora actuales CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 14 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca RESTRICCIONES DE COLUMNA Una vez les hemos dado un nombre a las tablas y se ha definido su dominio, podemos imponer ciertas restricciones que siempre se tendrán que cumplir. Las restricciones que se pueden dar son: Restricciones de columna Restricción Descripción NOT NULL La columna no puede tener valores nulos. UNIQUE La columna no puede tener valores repetidos. Es una clave PRIMARY KEY La columna no puede tener valores repetidos ni nulos. Es la clave primaria. REFERENCES Tabla [(columna)] La columna es la clave foránea de la columna de la tabla especificada. CHECK (condiciones) La columna debe cumplir las condiciones especificadas. Ejemplo TSL: CREATE TABLE ‘empleados’ ‘cuil`varchar(20) NOT NULL PRIMARY KEY , ‘nom_ape’ varchar(50) NOT NULL, sexo char(1) CHECK VALUE IN (“F”,”M”) RESTRICCIONES DE TABLA Una vez que definido una tabla y hemos impuesto ciertas restricciones para cada una de las columnas, podemos aplicar restricciones sobre toda la tabla, que siempre se deberán cumplir. Las restricciones que se pueden dar son las siguientes: Restricciones de tabla Restric Descripción UNIQUE (columna [, columna. . .]) El conjunto de las columnas especificadas no puede tener valores repetidos. Es una clave alternativa. PRIMARY KEY (columna [, columna. . .]) El conjunto de las columnas especificadas no puede tener valores nulos ni repetidos. Es una clave primaria. CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 15 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca Orden de creación Antes de crear una tabla con una o más claves foráneas, se deben haber creado las tablas que tienen como clave primaria las referenciadas por las foráneas. FOREIGN KEY (columna [, columna. . .]) REFERENCES tabla [(columna2 [, columna2. . .])] El conjunto de las columnas especificadas es una clave foránea que referencia la clave primaria formada por el conjunto de las columnas2 de la tabla dada. Si las columnas y las columnas2 se denominan exactamente igual, entonces no sería necesario poner columnas2. CHECK (condiciones) La tabla debe cumplir las condiciones especificadas. Ejemplo TSL. CREATE TABLE `procesos_realizados` ( `nro_servicio` int(11) NOT NULL, `orden` int(11) NOT NULL, `cod_proceso` int(11) NOT NULL, `fecha_inicio` date NOT NULL, `hora_inicio` time NOT NULL, `cuil_empleado` varchar(20) NOT NULL, `fecha_fin` date default NULL, `hora_fin` time default NULL, `resultado_proceso` varchar(20) default NULL, PRIMARY KEY (`nro_servicio`,`orden`,`fecha_inicio`,`hora_inicio`), KEY `procesos_realizados_fk1` (`cod_proceso`), KEY `procesos_realizados_fk2` (`cuil_empleado`), CONSTRAINT `procesos_realizados_fk` FOREIGN KEY (`nro_servicio`, `orden`) REFERENCES `tratamiento_limpieza` (`nro_servicio`, `orden`) ON UPDATE CASCADE, CONSTRAINT `procesos_realizados_fk1` FOREIGN KEY (`cod_proceso`) REFERENCES `procesos` (`cod_proceso`) ON UPDATE CASCADE, CONSTRAINT `procesos_realizados_fk2` FOREIGN KEY (`cuil_empleado`) REFERENCES `empleados` (`cuil`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; NOTA Al crear una tabla vemos que muchas restricciones se pueden imponer de dos formas: • como restricciones de columna Tenemos que elegir restricción de tabla porque la clave primaria está compuesta por más de un atributo. CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 16 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca • como restricciones de tabla. Cuando queremos decir cuál es la clave primaria de una tabla, tenemos las dos posibilidades: • En el caso de que la restricción haga referencia a un solo atributo, podemos elegir la posibilidad que más nos guste. • En el caso de que la restricción es compuesta por más de un atributo, tenemos que elegir por fuerza la opción de restricciones de tabla. Observar en la creación de tablas que hay casos donde tenemos que elegir restricción de tabla porque la clave primaria está compuesta por más de un atributo. En general, lo pondremos todo como restricciones de tabla, excepto NOT NULL y CHECK cuando haga referencia a una sola columna. MODIFICACIÓNY BORRADO DE CLAVES PRIMARIAS CON CLAVES FORÁNEAS QUE HACEN REFERENCIA A ÉSTAS En casos de borrado y modificación de filas que tienen una clave primaria referenciada por claves foráneas hay que tener en cuenta que existen políticas de restricción, actualización en cascada y la anulación. CREATE TABLE nombre_tabla ( definición_columna [, definición_columna. . .] [, restricciones_tabla]); Donde una de las restricciones de tabla es la definición de claves foráneas, que tiene el siguiente formato: FOREIGN KEY clave_secundaria REFERENCES tabla [(clave_primaria)] ON DELETE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}] ON UPDATE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}] Donde: NO ACTION corresponde a la política de restricción; CASCADE a la actualización en cascada; SET NULL sería la anulación; SET DEFAULT se podría considerar una variante de SET NULL, donde en lugar de valores nulos se puede poner el valor especificado por defecto. CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 17 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca ASERCIONES Una aserción es una restricción general que hace referencia a una o más columnas de más de una tabla. Para definir una aserción se utiliza la sentencia CREATE ASSERTION, y tiene el siguiente formato: CREATE ASSERTION nombre_aserción CHECK (condiciones); Ejemplo TSL: Creamos una aserción sobre la base de datos que nos asegure que no hay ningún empleado con un sueldo menor a 2.000 y que haya realizado el proceso 11 (Limplieza Térmica): CREATE ASSERTION restriccion1 CHECK (NOT EXISTS (SELECT * FROM empleados e, procesos_realizados p WHERE e.cod_proceso = e.cod_proceso and e.sueldo < 2000 And e.cod_proceso = 11 ); Para borrar una aserción es necesario utilizar la sentencia DROP ASSERTION: DROP ASSERTION nombre_aserción; Ejemplo TSL: Para borrar la aserción restriccion1, utilizaríamos la sentencia: DROP ASSERTION restriccion1; Nota: MySQL 5.1 no lo incluye CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 18 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca MODIFICACIÓN Y BORRADO DE TABLAS Para modificar una tabla es preciso utilizar la sentencia ALTER TABLE: ALTER TABLE nombre_tabla {acción_modificar_columna| acción_modif_restricción_tabla}; Donde: acción_modificar_columna: {ADD [COLUMN] columna def_columna | ALTER [COLUMN] columna {SET def_defecto|DROP DEFAULT}| DROP [COLUMN ] columna {RESTRICT|CASCADE}} acción_modif_restricción_tabla: {ADD restricción| DROP CONSTRAINT restricción RESTRICT|CASCADE}} Modificar una tabla puede implicar: • Añadir una columna (ADD columna). • Modificar las definiciones por defecto de la columna (ALTER columna). • Borrar la columna (DROP columna). • Añadir alguna nueva restricción de tabla (ADDCONSTRAINT restricción). • Borrar alguna restricción de tabla (DROPCONSTRAINT restricción). Para borrar una tabla es preciso utilizar la sentencia DROP TABLE: DROP TABLE nombre_tabla {RESTRICT|CASCADE}; Donde: RESTRICT, la tabla no se borrará si está referenciada, por ejemplo, por alguna vista. CASCADE, todo lo que referencie a la tabla se borrará con ésta. CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 19 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca Ejemplo TSL: Agregamos a la tabla EMPLEADOS los atributos ciudad, fecha_ingreso y salario ALTER TABLE empleados ADD COLUMN ciudad varchar(30) default NULL, ADD COLUMN fecha_ingreso date default NULL, ADD COLUMN salario decimal (10,3) default NULL Agregamos a la tabla CLIENTES el atributo sexo con la restricción CHECK ALTER TABLE clientes ADD COLUMN sexo char(1) CHECK VALUE IN ("F","M") ALTER TABLE valores_tratamientos ADD CONSTRAINT `valores_tratamientos_fk2` FOREIGN KEY (`cod_tratamiento`) REFERENCES `tratamientos` (`cod_tratamiento`) ON UPDATE CASCADE; ALTER TABLE valores_tratamientos DROP FOREIGN KEY valores_tratamientos_fk2; CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 20 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca CREACIÓN Y BORRADO DE VISTAS Para crear una vista es necesario utilizar la sentencia CREATE VIEW. CREATE VIEW nombre_vista [(lista_columnas)] AS (consulta) [WITH CHECK OPTION]; Para crear una vista se define qué nombre le queremos poner (nombre_vista). Si queremos cambiar el nombre de las columnas, o bien poner nombre a alguna que en principio no tenía, lo podemos hacer en lista_columnas. Luego quedará por definir la consulta que formará nuestra vista. Las vistas no existen realmente como un conjunto de valores almacenados en la base de datos, sino que son tablas ficticias, denominadas derivadas (no materializadas). Se construyen a partir de tablas reales (materializadas) almacenadas en la base de datos, y conocidas con el nombre de tablas básicas (o tablas de base). La no existencia real de las vistas hace que puedan ser actualizables o no. Una de las ventajas que podemos mencionar al usar vistas es que simplifican las consultas generando independencia de los datos. Para crear una vista, se “embebe” una Subconsulta dentro de la sentencia CREATE VIEW, esta subconsulta puede contener un comando SELECT que utilice funciones de grupo. Además podemos clasificar a las vistas en • Simples: Poseen una sola tabla, no contiene funciones, no contiene grupos. Por estas características se pueden hacer operaciones de DML a través de la vista • Compuestas: Poseen una o más tablas, contienen funciones y/o funciones de grupo. Por estas características NO se pueden hacer operaciones de DML a través de la vista Ejemplo TSL: Creamos una vista sobre la base de datos TSL que contenga para cada empleado y por cada proceso, la cantidad de veces que se ha realizado el proceso hasta el momento. CREATE VIEW procesos_empleados (nombre_emp, desc_proceso, cant_procesos) AS (SELECT nom_ape, desc_proceso, COUNT(*) FROM empleados e, procesos_realizados pr, procesos p WHERE e.cuil = pr.cuil_empleado Vistas Una vista en el modelo relacional no es sino una tabla virtual derivada de las CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 21 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca AND pr.cod_proceso = p.cod_proceso GROUP BY nom_ape, desc_proceso); Para borrar una vista es preciso utilizar la sentencia DROP VIEW: DROP VIEW nombre_vista (RESTRICT|CASCADE); Donde: RESTRICT, la vista no se borrará si está eferenciada,por ejemplo, por otra vista. CASCADE, todo lo que referencie a la vista se borrará con ésta. Ejemplo TSL: Para borrar la vista procesos_empleados: DROP VIEW procesos_empleados RESTRICT; BORRADO DEL CONTENIDO DE UNA TABLA El comando TRUNCATE borra todo el contenido de una tabla. Si bien, en un principio, esta sentencia parecería ser DML (Lenguaje de Manipulación de Datos), es en realidad una DDL, ya que internamente, el comando TRUNCATE borra la tabla y la vuelve a crear y no ejecuta ninguna transacción. El comando similardel DML es el comando DELETE que borra registros, pero, en casos que se quiera borrar todo el contenido de la tabla el comando TRUNCATE es más eficiente por su rapidez, especialmente si la tabla es muy grande y contiene muchos índices. Ejemplo TSL: TRUNCATE valores_tratamientos CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 22 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca LENGUAJE DE MANIPULACIÓN DE DATOS (DML) Un lenguaje de manipulación de datos (Data Manipulation Language, o DML en inglés) es un lenguaje proporcionado por el sistema de gestión de base de datos que permite a los usuarios llevar a cabo las tareas de consulta o manipulación de los datos. OPERACIONES BÁSICAS DEL DML Una vez creada la base de datos con sus tablas, debemos poder insertar, modificar y borrar los valores de las filas de las tablas. Para poder hacer esto, el SQL92 nos ofrece las siguientes sentencias: • INSERT para agregar filas a una tabla, • UPDATE para modificar filas de una tabla, • DELETE para borrar filas de una tabla Una vez que hemos insertado valores en nuestras tablas, tenemos que poder consultarlos. La sentencia para hacer consultas a una base de datos con el SQL92 es: • SELECT FROM. NOTA: Comenzaremos por la sentencia SELECT FROM para consultas simples a UNA TABLA ya que la misma puede aplicarse luego con las sentencias INSERT, UPDATE y DELETE. Volveremos a crear la Base de Datos del Ejemplo TSL y ejecutaremos los INSERT que figuran en el ANEXO I para poder tener un conjunto de datos y realizar las consultas. Tener en cuenta que si no se vuelve a crear la Base, en los ejemplos anteriores la tabla empleados fue modificada con lo cual, si se ejecutan los INSERT luego de esta modificación faltarán los datos de salarios, fecha de ingreso y ciudad. CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 23 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca CONSULTAS SIMPLES A UNA BASE DE DATOS RELACIONAL Para hacer consultas sobre una tabla con el SQL se utiliza la sentencia SELECT FROM SELECT nombre_columna_a_seleccionar [[AS] col_renombrada], nombre_columna_a_seleccionar [[AS] col_renombrada]...] FROM tabla_a_consultar [[AS] tabla_renombrada]; La opción AS nos permite renombrar las columnas que queremos seleccionar o las tablas que queremos consultar, es decir, nos permite la definición de alias. La palabra clave AS es opcional, y es bastante habitual poner sólo un espacio en blanco en lugar de toda la palabra. Ejemplo TSL SELECT * FROM maquinas; Con la sentencia SELECT FROM podemos seleccionar columnas de una tabla, pero para seleccionar filas de una tabla es preciso añadirle la cláusula WHERE SELECT nombre_columnas_a_seleccionar FROM tabla_a_consultar WHERE condiciones; Ejemplo TSL SELECT nro_servicio AS servicio, cod_proceso AS proceso, resultado_proceso AS resultado FROM procesos_realizados La cláusula WHERE nos permite obtener las filas que cumplen la condición especificada en la consulta. Para definir las condiciones en la cláusula WHERE, podemos utilizar alguno de los operadores de los que dispone el SQL. SELECT El * después de SELECT indica que queremos ver todos los atributos que existen en la tabla. CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 24 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca Si necesitamos que en una consulta muestre las filas sin repeticiones, es necesario utilizar la palabra clave DISTINCT inmediatamente después de SELECT. La opción por defecto es ALL, implica mostrar todo, incluso las repeticiones SELECT DISTINCT nombre_columnas_a_seleccionar FROM tabla_a_consultar [WHERE condiciones]; Ejemplo TSL. Mostrar los servicios y el resultado del empleado cuyo cuil es: ‘66- 66666666-6’ SELECT DISTINCT nro_servicio, resultado_proceso FROM procesos_realizados WHERE cuil_empleado = ‘66-66666666-6’ PALABRAS Y SIMBOLOS PARA LAS CONSULTAS Las expresiones de valores Suma ( + ) Resta ( - ) Multiplicación ( * ) División ( % ) Conectores Lógicos AND OR NOT Predicados Un predicado es una condición que se puede evaluar con el fin de que nos dé un valor verdadero que puede ser “verdadero”, “falso”, o “desconocido”. Este resultado se consigue aplicando el predicado a una fila dada de una tabla. Los predicados que se incluyen en SQL son: Comparación: (=, <>, <, >, <=, =>) Entre (...BETWEEN...AND...) IN, (NOT IN) LIKE NULL Cuantificador (ALL, SOME, ANY) EXISTS, (NOT EXISTS) Si verificamos, realizando el mismo SELECT pero sin el DISTINCT, notamos que muestra filas repetidas CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 25 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca Inserción de múltiples filas Para insertar más de una fila con una sola sentencia, tenemos que obtener los valores como resultado de una consulta realizada en una o más tablas. INSERCIÓN DE FILAS EN UNA TABLA INSERCIÓN DE UNA FILA EN UNA TABLA Una sentencia INSERT de SQL agrega uno o más registros a una (y sólo una) tabla en una base de datos relacional. INSERT INTO nombre_tabla [(columnas)]{VALUES({v1|DEFAULT|NULL}, ..., {vn/DEFAULT/NULL})|<consulta>}; Los valores v1, v2, ..., vn se deben corresponder exactamente con las columnas que hemos dicho que tendríamos con el CREATE TABLE y deben estar en el mismo orden, a menos que las volvamos a poner a continuación del nombre de la tabla. En este último caso, los valores se deben disponer de forma coherente con el nuevo orden que hemos impuesto. Podría darse el caso de que quisiéramos que algunos valores para insertar fuesen valores por omisión, definidos previamente con la opción DEFAULT. Si se trata de introducir valores nulos, también podemos utilizar la palabra reservada NULL. Si ocurre un error de sintaxis o si alguna de las restricciones es violada, no se agrega la fila y se devuelve un error. Ejemplo TSL: INSERT INTO máquinas (nro_maquina, desc_maquina) VALUES (1, ‘Plancha Manual’); Cuando se especifican todos los valores de una tabla, se puede utilizar la sentencia reducida: INSERT INTO ''tabla'' VALUES (''valor1'', [''valor2,...'']) Ejemplo TSL: Asumiendo que nro_maquina y desc_maquina son las únicas columnas de la tabla maquinas: INSERT INTO máquinas VALUES (5, 'Plancha Manual'); CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 26 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca INSERCIÓN DE MÚLTIPLES FILAS EN UNA TABLA Una característica de SQL (desde SQL-92) es el uso de constructores de filas para insertar múltiples filas a la vez, con una sola sentencia SQL: INSERT INTO ''tabla'' (''columna1'', [''columna2,... '']) VALUES (''valor1a'', [''valor1b,...'']), (''value2a'', [''value2b,...'']),... Ejemplo TSL: Asumiendo que nro_maquina y desc_maquina son las únicas columnas de la tabla maquinas: INSERT INTO máquinas VALUES (1, 'Plancha Automática'), (2, ‘Maquina de Teñir’), (3, ‘Lavadora-Secadora’), (4, ‘Lavadora en Seco’); COPIAR FILAS DE OTRASTABLAS Un INSERT también puede utilizarse para recuperar datos de otras tablas, modificarlos si es necesario e insertarlos directamente en la tabla. Todo esto se hace en una sola sentencia SQL que no implica ningún procesamiento intermedio en la aplicación cliente. Se utiliza una consulta SELECT denominada en este caso subconsulta en lugar de la cláusula VALUES. Ejemplo TSL: Vamos a suponer que existe otra tabla con la misma estructura que máquinas: padron_máquinas donde existen todas la máquinas del mercado INSERT INTO maquinas SELECT * FROM padron_maquina WHERE codigo IN (10, 11, 12) En el caso que la tabla de donde sacamos los datos tenga una estructura diferente a la de la tabla en la que estamos insertando (padron_maquina(codigo, descripcion, modelo) , y debemos elegir algunos atributos, necesitamos elegir los atributos en el SELECT. CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 27 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca INSERT INTO maquinas (cod_maquina, desc_maquina) SELECT codigo, descripcion FROM padron_maquina WHERE codigo IN (10, 11, 12) El SELECT aquí utilizado produce una tabla temporal, y el esquema de la tabla temporal debe coincidir con el esquema de la tabla donde los datos son insertados. El SELECT se evalúa antes que la operación INSERT este iniciada. NOTA: En lo que sigue veremos sentencias SELECT más complejas que pueden ser utilizadas para la inserción de datos. Por ejemplo el SELECT puede contener JOIN, llamadas a funciones, y puede incluso consultar en la misma TABLA los datos que se inserta. MODIFICACIÓN DE FILAS DE UNA TABLA Para modificar los valores de algunas filas de una tabla, utilizar la sentencia UPDATE SET WHERE. UPDATE nombre_tabla SET columna = {expresión|DEFAULT|NULL} [, columna = {expr|DEFAULT|NULL} ...] WHERE condiciones; Ejemplo TSL: Volveremos a agregar a la tabla EMPLEADOS los atributos ciudad, fecha_ingreso y salario ALTER TABLE empleados ADD COLUMN ciudad varchar(30) default NULL, ADD COLUMN fecha_ingreso date default NULL, ADD COLUMN salario decimal (10,3) default NULL Para actualizar entonces los datos a la tabla así modificada veremos los siguientes ejemplos • Los empleados '10-10101010-1', '66-66666666-6', '77-77777777-7' ingresaron el 01/01/1999 su salario es de 2000 pesos y son de Rosario UPDATE empleados CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 28 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca SET ciudad ='Rosario', fecha_ingreso = "1999-01-01", salario =2000 WHERE cuil IN ( '10-10101010-1','66-66666666-6', '77-77777777-7') • Los empleados '88-88888888-8','99-99999999-9' ingresaron el 01/01/2005 y su salario es de 1500 pesos y son de la ciudad de Casilda UPDATE empleados SET ciudad = 'Casilda', fecha_ingreso = "2005-01-01", salario =1500 WHERE cuil IN ( '88-88888888-8','99-99999999-9') • Ahora necesitamos incrementar el sueldo de todos los empleados que ingresaron antes del 01/01/2000 en 10% . La modificación a ejecutar sería: UPDATE empleados SET salario = salario * 1.1 WHERE fecha_ingreso < "2000-01-01" BORRADO DE FILAS DE UNA TABLA Para borrar valores de algunas filas de una tabla podemos utilizar la sentencia DELETE FROM WHERE. DELETE FROM nombre_tabla [WHERE condiciones]; Si quisiéramos borrar todas las filas de una tabla, entonces sólo tendríamos que poner la sentencia DELETE FROM, sin WHERE. Ejemplo TSL DELETE FROM maquinas WHERE cod_maquina = 10; DELETE FROM tratamientos WHERE nro_maquina_utiliza = 10 Borrado de múltiples filas Notemos que la maquina nro 10 podría ser utilizada en varios tratamientos, por lo tanto se borraría más de una fila con una sola sentencia. CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 29 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca CONSULTAS DE MAYOR COMPLEJIDAD A UNA BASE DE DATOS RELACIONAL SUBCONSULTAS Una subconsulta es una consulta incluida dentro de una cláusula WHERE o HAVING de otra consulta. En ocasiones, para expresar ciertas condiciones no hay otra alternativa que obtener el valor que buscamos como resultado de una consulta. Ejemplo TSL Si quisiéramos saber los tratamientos que tienen el precio más alto, en primer lugar tendremos que encontrar cuál es el precio más alto, luego traemos todos los tratamientos con la fecha del valor más alto. Cabe aclarar que todos los valores serán iguales (el más alto) SELECT cod_tratamiento, fecha_desde, valor FROM valores_tratamientos WHERE valor = (SELECT MAX(valor) FROM valores_tratamientos) Para ver mejor el caso anterior, actualizamos el valor igual al más alto al Cod_tratamiento = 3, agregando un registro INSERT INTO valores_tratamientos (`cod_tratamiento`, `fecha_desde`, `valor`) Values (3, '2008-10-20',414) Volvemos a realizar el SELECT anterior, vemos que en lugar de mostrar un solo registro con el valor más alto, ahora muestra el mismo que mostraba antes más el que agregamos. Los tratamientos de precio más bajo Si en lugar de los cod_tratamiento, y la fecha del precio más alto hubiésemos querido saber los de precio más bajo, habríamos aplicado la función de agregación MIN. CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 30 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca OTROS PREDICADOS BETWEEN. Para expresar una condición que quiere encontrar un valor entre unos límites concretos, podemos utilizar BETWEEN: SELECT nombre_columnas_a_seleccionar FROM tabla_a_consultar WHERE columna BETWEEN límite1 AND límite2; Ejemplo TSL Se pide los empleados que ganan entre 2.000 y 3.000 pesos mensuales: SELECT cuil, nom_ape, salario FROM empleados WHERE salario BETWEEN 2000 and 3000; El mismo resultado se obtiene realizando el siguiente SELECT SELECT cuil, nom_ape, salario FROM empleados WHERE salario >= 2000 and salario <= 3000; IN. Para comprobar si un valor coincide con los elementos de una lista utilizamos IN, y para ver si no coincide, NOT IN: SELECT nombre_columnas_a_seleccionar FROM tabla_a_consultar WHERE columna [NOT] IN (valor1, ..., valorN); Ejemplo TSL Se pide mostrar las precauciones de los procesos: Lavado, Teñido, Secado y Limpieza SELECT desc_proceso, precauciones FROM procesos WHERE desc_proceso in("Lavado", "Teñido", "Secado", "Limpieza"); CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 31 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca Otros patrones Aunque _ y % son los caracteres elegidos por el estándar, cada sistema relacional comercial ofrece diversas variantes. Ej ‘ ¿’ y ´*´ LIKE. Para comprobar si una columna de tipo carácter cumple alguna propiedad determinada, podemos usar LIKE: SELECT nombre_columnas_a_seleccionar FROM tabla_a_consultar WHERE columna LIKE característica; Los patrones del SQL92 para expresar características son los siguientes: • Un carácter ´_´ para cada carácter individual que queramos considerar. • Un carácter ´%´ para expresar una secuenciade caracteres, que puede no estar formada por ninguno. Ejemplo TSL Se pide mostrar los tratamientos que incluyan en su descripción a la palabra Lavado SELECT desc_tratamiento FROM tratamientos where desc_tratamiento like "%Lavado%" IS NULL. Para comprobar si un valor es nulo utilizaremos IS NULL, y para averiguar si no lo es, IS NOT NULL. SELECT nombre_columnas_a_seleccionar FROM tabla_a_consultar WHERE columna IS [NOT] NULL; Ejemplo TSL Se pide mostrar los tratamientos que no utilizan máquina SELECT desc_tratamiento FROM tratamientos WHERE nro_maquina_utiliza IS NULL CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 32 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca Los predicados ANY/SOME Podemos elegir cualquiera de los dos predicados para pedir que alguna fila satisfaga una condición. Se pide mostrar los tratamientos que utilizan máquina SELECT desc_tratamiento FROM tratamientos WHERE nro_maquina_utiliza IS NOT NULL ANY/SOME y ALL. Para ver si un atributo cumple que: • (ALL) todas sus filas • (ANY/SOME) algunas de sus filas satisfacen una condición, podemos hacer: SELECT nombre_columnas_a seleccionar FROM tabla_a_consultar WHERE columna operador_comparación {ALL|ANY|SOME}subconsulta; Ejemplo TSL Para encontrar el código, fecha desde y valores de los tratamientos cuyo valor supere a al sueldo de todos los empleados SELECT cod_tratamiento, fecha_desde, valor FROM valores_tratamientos WHERE valor > ALL (SELECT salario FROM empleados) Como resultado vemos que no existe ningún tratamiento que supere el salario de todos los empleados Para ver este resultado, vamos a actualizar el valor del cod_tratamiento = 3 a un importe = 2300. Traerá este registro ya que supera a todos los salarios. INSERT INTO valores_tratamientos (`cod_tratamiento`, `fecha_desde`, `valor`) Values (3, '2008-11-20',2300) También se puede resolver usando funciones de agregación SELECT cod_tratamiento, fecha_desde, valor CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 33 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca FROM valores_tratamientos WHERE valor > (SELECT max(salario) FROM empleados) Para encontrar los empleados cuyo salario es menor que algún valor de tratamiento SELECT * from empleados Where salario < ANY (SELECT valor FROM valores_tratamientos) Para ver mejor este resultado creamos un empleado cuyo salario supere el máximo valor de tratamiento INSERT INTO `empleados` (`cuil`, `nom_ape`, fecha_ingreso, salario) VALUES ('22-20202020-2','Alvaro Rodriguez', '2009-01-01',10000) Si volvemos a ejecutar el SELECT vemos que al empleado Alvaro Rodriguez, no lo muestra ya que su salario no supera a ningún valor de tratamiento EXISTS. Para comprobar si una subconsulta produce alguna fila de resultados, podemos utilizar la sentencia denominada test de existencia: EXISTS. Para comprobar si una subconsulta no produce ninguna fila de resultados, podemos utilizar NOT EXISTS. SELECT nombre_columnas_a_seleccionar FROM tabla_a_consultar WHERE [NOT] EXISTS subconsulta; Ejemplo TSL Se buscan los empleados han que realizado algún proceso: SELECT cuil, nom_ape FROM empleados WHERE EXISTS (SELECT * FROM procesos_realizados WHERE cuil_empleado = cuil); Como vemos el último empleado agregado que no realizó ningún proceso no se muestra. Este resultado también se encuentra haciendo INNER JOIN que veremos más adelante. CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 34 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca ORDEN DE LAS FILAS OBTENIDAS EN RESPUESTAS A CONSULTAS Si se necesita que las filas aparezcan en un orden determinado se deberá utilizar la cláusula ORDER BY en la sentencia SELECT SELECT nombre_columnas_a seleccionar FROM tabla_a_consultar [WHERE condiciones] ORDER BY columna_según_la_cual_se_quiere_ordenar [DESC] [, col_ordenación [DESC]...]; Ejemplo TSL Queremos consultar los nombres de los empleados ordenados según el sueldo que ganan, y si ganan el mismo sueldo, ordenados alfabéticamente por el nombre: SELECT cuil, nom_ape, salario FROM empleados ORDER BY salario, nom_ape; Si no se especifica un orden, se seguirá un orden ascendente, pero si se desea seguir un orden descendente es necesario utilizar el DESC detrás de cada factor de ordenación expresado en la cláusula ORDER BY: ORDER BY columna_ordenación [DESC] [, columna [DESC] ...]; También se puede explicitar un orden ascendente poniendo la palabra clave ASC (opción por defecto). Ejemplo TSL Queremos consultar los nombres de los empleados ordenados según el sueldo que ganan en forma ascendente, y si ganan el mismo sueldo, ordenados alfabéticamente por el nombre en forma descendente: SELECT cuil, nom_ape, salario FROM empleados ORDER BY salario ASC, nom_ape DESC; CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 35 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca CONSULTAS CON AGRUPACIÓN DE FILAS DE UNA TABLA Las cláusulas siguientes, de la instrucción SELECT FROM, permiten organizar las filas por grupos: • GROUP BY nos sirve para agrupar filas según las columnas que indique esta cláusula. • HAVING especifica condiciones de búsqueda para grupos de filas; lleva a cabo la misma función que cumple la cláusula WHERE para las filas de toda la tabla, pero ésta aplica las condiciones a los grupos obtenidos. SELECT nombre_columnas_a seleccionar FROM tabla_a_consultar [WHERE condiciones] GROUP BY columnas_según_las_cuales_se_quiere_agrupar [HAVING condiciones_por_grupos] [ORDER BY columna_ordenación [DESC] [, columna [DESC]...]]; Como vemos las sentencias SQL van agregando cláusulas a medida que la dificultad o la exigencia de la consulta lo requiere. FUNCIONES DE AGREGACIÓN Funciones de agregación se utilizan para efectuar varias operaciones sobre los datos de una base de datos: Funciones de agregación Función Descripc COUNT Nos da el número total de filas SUM Suma los valores de una MIN Nos da el valor mínimo de una MAX Nos da el valor máximo de una AVG Calcula el valor medio de una Las funciones de agregación se aplican a una columna, excepto la función de agregación COUNT, que normalmente se aplica a todas las columnas de la tabla o tablas seleccionadas. Los atributos de la cláusula GROUP BY deben ser, como mínimo, las columnas que figuran en SELECT, exceptuando las columnas afectadas por funciones de agregación. CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 36 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca • COUNT (*) contará todas las filas de la tabla o las tablas que cumplan las condiciones. • COUNT (DISTINCT columna), cuenta las filas que poseen los valores que no fuesen nulos ni repetidos. • COUNT (columna), cuenta los valores que no son nulos en la columna mencionada. Ejemplo en TSL. Mostrar la cantidad de cada servicio realizado por empleado SELECT nro_servicio, cuil_empleado, COUNT(*)cantidad FROM procesos_realizados GROUP BY 1,2 Mostrar la cantidad de cada servicio realizado SELECT nro_servicio, COUNT(*) cantidad FROM procesos_realizados GROUP BY 1 Mostrar la cantidad de procesos realizados y que hayan sido finalizados SELECT COUNT(fecha_fin) FROM procesos_realizados El ejemplo anterior también se puede resolver de la siguiente manera: SELECT count(*) FROM procesos_realizados Where fecha_fin IS NOT NULL Mostrar el importe total abonado sueldo para los empleados que ingresaron luego del año 2005 (3) SELECT sum(salario) salario FROM empleados where fecha_ingreso > "2004-01-01" Mostrar por tratamiento la máxima fecha de sus valores SELECT cod_tratamiento, MAX(fecha_desde) Max_fecha FROM valores_tratamientos No tiene group by ya que no hay atributos a agrupar (delante de la función de grupo sum) Recordemos que COUNT (columna), cuenta los valores que no son nulos en la columna mencionada CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 37 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca group by cod_tratamiento Mostrar por tratamiento el promedio de sus valores SELECT cod_tratamiento, AVG(valor) Promedio FROM valores_tratamientos GROUP BY cod_tratamiento Queremos saber el valor promedio de los tratamientos SELECT cod_tratamiento, AVG(valor) AS valor_prom FROM valores_tratamientos GROUP BY cod_tratamiento; Queremos saber solo aquellos tratamientos cuyo promedio supera los 100 pesos SELECT cod_tratamiento, AVG(valor) AS valor_prom FROM valores_tratamientos GROUP BY cod_tratamiento HAVING AVG(valor) > 100; CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 38 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca CONSULTAS A MÁS DE UNA TABLA Para consultar datos de más de una tabla hacemos combinaciones de columnas de tablas diferentes. Es posible listar más de una tabla que se quiere consultar especificándolo en la cláusula FROM. PRODUCTO CARTESIANO El producto cartesiano crea una sola tabla a partir de las tablas especificadas en la cláusula FROM, haciendo coincidir los valores de las columnas relacionadas de estas tablas. Si trabajamos con más de una tabla, puede ocurrir que la tabla resultante tenga dos columnas con el mismo nombre. Por ello es obligatorio especificar a qué tabla corresponden las columnas a las que nos estamos refiriendo, denominando la tabla a la que pertenecen antes de ponerlas (por ejemplo, empleados.cuil). Para simplificarlo, se utilizan los alias que, en este caso, se definen en la cláusula FROM. Ejemplo TSL Necesitamos saber para aquellos tratamientos que utilizan máquinas, los nombres de los tratamientos y las máquinas que utilizan (en este caso no es obligatorio utilizar alias ya que los nombres de los atributos son diferentes) SELECT desc_tratamiento, desc_maquina FROM tratamientos, maquinas WHERE nro_maquina = nro_maquina_utiliza La forma de expresar el producto cartesiano que acabamos de ver pertenece al SQL92 introductorio. Una forma alternativa es utilizando el SQL92 intermedio o completo, sería la siguiente: SELECT nombre_columnas_a_seleccionar FROM tabla1 JOIN tabla2 {ON condiciones|USING (columna [, columna...])} [WHERE condiciones]; En WHERE necesitamos expresar el vínculo que se establece entre las dos tablas, en este caso nro_aquina y nro_maquina_utiliza. En el álgebra relacional, sería un JOIN en lugar de un producto cartesiano. O un equi_join ya que aparecen igualadas dos columnas de igual dominio CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 39 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca Ejemplo anterior con el SQL92 intermedio o completo, obteniendo el mismo resultado. (aquí utilizamos alias, pero tampoco es obligatorio ya que los atributos se llaman diferentes) SELECT desc_tratamiento, desc_maquina FROM tratamientos t INNER JOIN maquinas m ON t.nro_maquina_utiliza = m.nro_maquina; La opción ON, no solamente exige ser utilizada para expresar condiciones de igualdad, se puede utilizar para expresar condiciones con los demás operadores de comparación que no sean el de igualdad. También podemos utilizar una misma tabla dos veces con alias diferentes, para distinguirlas. Ejemplo TSL Si necesitamos conocer el cuit, nombre y apellido de los empleados que ganan más que el empleado que tiene el cuit:10-10101010-1 haríamos lo siguiente: SELECT e1.cuil, e1.nom_ape FROM empleados e1 JOIN empleados e2 ON e1.salario > e2.salario WHERE e2.cuil = '10-10101010-1'; Hemos tomado la tabla e2 para fijar la fila del empleado con cuil 10- 10101010-1, de modo que podamos comparar el sueldo de la tabla e1, que contiene a todos los empleados, con el sueldo de la tabla e2, que contiene sólo al empleado cuyo cuit es 10-10101010-1. Si comparamos por >= veremos que vuelve a trae también al empleado del cuil 10-10101010-1 CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 40 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca JOIN NATURAL El JOIN NATURAL de dos tablas consiste básicamente, al igual que en el álgebra relacional, en hacer un equijoin entre columnas del mismo nombre y eliminar las columnas repetidas. El join natural, utilizando el SQL92 intermedio o completo, se haría de la forma siguiente: SELECT nombre_columnas_a_seleccionar FROM tabla1 NATURAL JOIN tabla2 [WHERE condiciones]; Ejemplo TSL Queremos saber los distintos valores y fechas de cambio que tuvo el tratamiento = 1, mostrando la descripción del tratamiento, fecha valor y el valor SELECT desc_tratamiento, fecha_desde, valor FROM tratamientos NATURAL JOIN valores_tratamientos WHERE cod_tratamiento = 1; La combinación natural también se podría hacer con la cláusula USING, sólo aplicando la palabra reservada JOIN: SELECT desc_tratamiento, fecha_desde, valor FROM tratamientos JOIN valores_tratamientos USING (cod_tratamiento) WHERE cod_tratamiento = 1; CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 41 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca JOIN INTERNO Y EXTERNO El JOIN INTERNO (INNER JOIN) sólo se queda con las filas que tienen valores idénticos en las columnas de las tablas que compara. Esto puede hacer que perdamos alguna fila interesante de alguna de las dos tablas; por ejemplo, porque se encuentra en NULL en el momento de hacer la combinación. SELECT nombre_columnas_a_seleccionar FROM t1 [NATURAL] [INNER] JOIN t2 {ON condiciones| |USING (columna [,columna...])} [WHERE condiciones]; El JOIN EXTERNO (OUTER JOIN), nos permite obtener todos los valores de la tabla que hemos puesto a la derecha, los de la tabla que hemos puesto a la izquierda o todos los valores de las dos tablas. Su formato es: SELECT nombre_columnas_a_seleccionar FROM t1 [NATURAL] [LEFT|RIGHT|FULL] [OUTER] JOIN t2 {ON condiciones| [USING (columna [,columna...])} [WHERE condiciones]; Ejemplo TSL • JOIN EXTERNO IZQUIERDO. Queremossaber todas los tratamientos que máquinas utiliza y aquellos que no utilizan ninguna máquina, también mostrarlos. SELECT cod_tratamiento, desc_tratamiento, nro_maquina_utiliza, desc_maquina FROM tratamientos LEFT OUTER JOIN maquinas ON nro_maquina = nro_maquina_utiliza; El OUTER es opcional, si no lo ponemos obtenemos el mismo resultado SELECT cod_tratamiento, desc_tratamiento, CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 42 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca nro_maquina_utiliza, desc_maquina FROM tratamientos LEFT JOIN maquinas ON nro_maquina = nro_maquina_utiliza; • JOIN EXTERNO DERECHA. El mismo ejemplo anterior, solo cambiamos de lugar las tablas (LEFT a RIGHT) SELECT cod_tratamiento, desc_tratamiento, nro_maquina_utiliza, desc_maquina FROM maquinas RIGHT OUTER JOIN tratamientos ON nro_maquina = nro_maquina_utiliza; • JOIN EXTERNO PLENO. Obtenemos el mismo resultado que los anteriores debido a que en el único lugar que podemos tener NULL el número de máquina es en la tabla TRATAMIENTOS, nunca en MÁQUINAS. Esta consulta da mejores resultados cuando el atributo que une puede tener NULL en ambas tablas SELECT cod_tratamiento, desc_tratamiento, nro_maquina_utiliza, desc_maquina FROM maquinas FULL OUTER JOIN tratamientos ON nro_maquina = nro_maquina_utiliza; Nota: MySQL 5.1 no lo incluye pero puede resolverse con RIGHT JOIN, LEFT JOIN y UNION SELECT cod_tratamiento, desc_tratamiento, nro_maquina_utiliza, desc_maquina FROM tratamientos LEFT JOIN maquinas ON nro_maquina = nro_maquina_utiliza UNION SELECT cod_tratamiento, desc_tratamiento, nro_maquina_utiliza, desc_maquina FROM tratamientos RIGHT JOIN maquinas ON nro_maquina = nro_maquina_utiliza; CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 43 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca COMBINACIONES CON MÁS DE DOS TABLAS Si queremos combinar tres tablas o más con el SQL92 introductorio, sólo tenemos que agregar todas las tablas en el FROM y los vínculos necesarios en el WHERE. Si queremos combinarlas con el SQL92 intermedio o con el completo, tenemos que ir haciendo combinaciones de tablas por pares, y la tabla resultante se convertirá en el primer componente del siguiente par. Ejemplo TSL • Queremos combinar las tablas procesos, procesos_realizados y tratamientos_limpieza. SELECT * FROM procesos p, procesos_realizados pr, tratamiento_limpieza tl WHERE p.cod_proceso = pr.cod_proceso AND pr.nro_servicio = tl.nro_servicio o bien: SELECT * FROM procesos p JOIN procesos_realizados pr ON p.cod_proceso = pr.cod_proceso JOIN tratamiento_limpieza tl ON pr.nro_servicio = tl.nro_servicio; O, utilizando NATURAL JOIN ya que los atributos se denominan iguales: SELECT * FROM procesos p NATURAL JOIN procesos_realizados pr NATURAL JOIN tratamiento_limpieza tl; Otros ejemplos donde utilizamos varias tablas con OUTER JOIN • Qué empleados han realizado algún proceso para el servicio nro. 2 y qué máquinas se han usado en el mismo, mostrando código y descripción del tratamiento, nro. y descripción de la máquina (teniendo en cuenta que hay tratamientos que pueden no tener máquinas, en este caso el nro y descripción de la maquina se mostraran en nulos), código y descripción del proceso, cuil y nombre y apellido del empleado. SELECT tl.`cod_tratamiento`, t.`desc_tratamiento`, m.`nro_maquina`, m.`desc_maquina`, CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 44 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca p.`cod_proceso`,p.`desc_proceso`, e.`cuil`, e.`nom_ape` FROM tratamiento_limpieza tl INNER JOIN tratamientos t on tl.`cod_tratamiento`=t.`cod_tratamiento` LEFT JOIN `procesos_realizados` pr on tl.`nro_servicio`=pr.`nro_servicio` and tl.`orden`=pr.`orden` LEFT JOIN `procesos` p on pr.`cod_proceso`=p.`cod_proceso` LEFT JOIN `empleados` e on pr.`cuil_empleado`=e.`cuil` LEFT JOIN maquinas m on t.`nro_maquina_utiliza`=m.`nro_maquina` WHERE tl.nro_servicio = 2 • Para realizar un control del estado de las máquinas se necesita saber ¿Cuáles máquinas fueron utilizadas más de 4 veces? Indicar nro., descripción y usos. Asuma que si un tratamiento con máquina fue definido para un servicio de limpieza la máquina se ha utilizado SELECT m.`nro_maquina`, m.`desc_maquina`, count(*) usos FROM `tratamiento_limpieza` tl INNER JOIN `tratamientos` t on tl.`cod_tratamiento`=t.`cod_tratamiento` RIGHT JOIN maquinas m on t.`nro_maquina_utiliza`=m.`nro_maquina` GROUP BY m.`nro_maquina`, m.`desc_maquina` HAVING usos>4 UNIÓN La cláusula UNION permite unir consultas de dos o más sentencias SELECT FROM. Su formato es: SELECT columnas FROM tabla [WHERE condiciones] UNION [ALL] SELECT columnas CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 45 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca FROM tabla [WHERE condiciones]; Si ponemos la opción ALL, mostrarán las filas obtenidas a causa de la unión. Si no la ponemos, eliminamos las filas repetidas. Lo más importante de la unión es que somos nosotros quienes tenemos que procurar que se efectúe entre columnas definidas sobre dominios compatibles (concepto visto en AR). El SQL92 no nos ofrece herramientas para asegurar la compatibilidad semántica entre columnas. Ejemplo TSL Necesitamos conocer las ciudades de nuestros clientes y de los empleados SELECT ciudad FROM clientes UNION SELECT ciudad FROM empleados; INTERSECCIÓN Para hacer la intersección entre dos o más sentencias SELECT FROM, podemos utilizar la cláusula INTERSECT, cuyo formato es: SELECT columnas FROM tabla [WHERE condiciones] INTERSECT [ALL] SELECT columnas FROM tabla [WHERE condiciones]; Si indicamos la opción ALL, aparecerán todas las filas obtenidas a partir de la intersección. No la pondremos si queremos eliminar las filas repetidas. Es importante que las columnas estén definidas sobre dominios compatibles Ejemplo TSL Necesitamos conocer las ciudades donde viven empleado y clientes. Si existen ciudades donde viven clientes y no vive ningún empleados (o viceversa) no mostrarlas CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO 01/06/2010 Autor: Vilma Martín Pág 46 Revisores: Fabiana María Riva, Horacio Valentini, María Inés Seguenzia, Adrián Meca SELECT ciudad FROM clientes INTERSECT SELECT ciudad FROM empleados; Nota: MySQL 5.1 no lo incluye pero puede resolverse con IN y EXISTS • Intersección utilizando IN SELECT columnas FROM tabla WHERE columna IN (SELECT columna FROM tabla [WHERE condiciones]); • Intersección utilizando EXISTS SELECT columnas FROM tabla WHERE EXISTS (SELECT * FROM tabla WHERE condiciones); Ejemplo TSL Ejemplo anterior expresado con IN y con EXISTS SELECT ciudad FROM clientes WHERE ciudad IN (SELECT ciudad FROM empleados); SELECT ciudad FROM clientes WHERE EXISTS (SELECT * FROM empleados); CARRERA: INGENIERIA EN SISTEMAS DE INFORMACIÒN CÁTEDRA: GESTION DE DATOS CONTENIDO: SQL - LENGUAJE DE CONSULTA ESTRUCTURADO
Compartir