Compiladores - Primer Cuatrimestre-1

MICROSOFT SQL Server Gota a Gota en las Bases de Datos Relacionales Instructor Lic. Elvin German

MICROSOFT SQL Server Gota a Gota en las Bases de Datos Relacionales Justificación/Descripción Justificación/De scripción del curso

En el mundo actual la informática y con ella la programación y desarrollo de aplicaciones centradas en el manejo de los datos ha adquirido una importancia de primer orden para cualquier empresa o profesional, de modo que no vale de nada saber programar en cualquier lenguaje si no se entiende a la perfección el funcionamiento básico de cualquier base de datos y como los datos están organizados en ellas para ser llamados desde diversas aplicaciones en cualquier entorno operativo. Partiendo de esta base y en función de la exhaustividad de los datos y las relaciones en sí surge el concepto de Bases de Datos Relacionales, que son aquellas que integran diversos tipos de datos en varias tablas de una misma base y que se hacen indispensables para la agilidad y usabilidad de los mismos. De los enunciados anteriores se desprende que el manejo de las Bases de Datos Relacionales es indispensable en cualquier lenguaje de programación orientado al manejo de datos y por consiguiente en cualquier aplicación informática que pretenda la administración de los mismo, más aún cuando el mayor valor de las empresas en la actualidad radica en el tratamiento de sus productos, clientes, proveedores, agentes, etc Debe quedar claro desde el primer momento que del buen diseño de la base de datos que vayamos a utilizar depende el buen fin de la utilidad informática que pretendamos desarrollar y por tanto sería casi inútil saber mucho de cualquier lenguaje de programación sin conocer el funcionamiento de las bases de datos, ya que otra forma estaríamos condenados a reescribir todo el código del programa una y mil veces si no hemos planteado correctamente las "bases" del mismo. Objetivos Generales: Con este Material de Microsoft Microsoft SQL Server Gota a Gota en las Bases de Datos Relacionales pretendo dejar claro los conceptos de su funcionamiento y estructura, consiguiendo los siguientes objetivos:  

Aclarar el funcionamiento funcionamiento en todos los aspectos de las Bases de datos datos relacionales y la Gestión de las mismas Comprender los lenguajes que conforman las Bases de Datos (DDL, DML, DCL)

Objetivos Específicos: Especificando un poco más los objetivos objetivos de este material, podemos podemos declarar que el alumno conseguirá entender el funcionamiento y estructura para saber como aprovechar todas las capacidades de este tipo de bases de datos en la creación d e programas informáticos de cualquier nivel aprender la utilización de las bases de datos de SQL SERVER de cara a la programación. Estudiar el funcionamiento de las mismas, para una mejor base de cara a la utilización de sentencias SQL y de bases de datos de mayor complejidad. Formación específica muy demandada y valorada en el mercado de trabajo para los profesionales de la informática. Asentar los pilares de la programación no sólo habitual, sino también para intranets, aprovechando las funcionalidades de las bases de datos con respecto a las tecnologías de programación para la web. Metodología: El material se desarrollará completamente presencial, dotados de su material de apoyo, el cual tendrá como soporte su material práctico, para poder darle seguimiento a los temas expuestos en el aula, prácticas de autoevaluación y completa ayuda y asesoramiento por medio de Tutor.

MICROSOFT SQL Server Gota a Gota en las Bases de Datos Relacionales Lic. Elvin German

2

Índice de Contenido Unidad 1. Introducción ¿Qué es una Base de Datos? ¿Qué es un Sistema de Gestión de Bases de Datos? Gestor de Base de Datos ¿Diccionario de la Bases de Datos? Lenguajes Administrador de la Base Base de Datos Usuarios de la Base de Datos ¿Qué es el SQL? Características del lenguaje ¿Qué podemos hacer con SQL? Datos Variables Tablas de los ejemplos y ejercicios

5 5 5 5 5 6 6 6 6 7 8 8 11

Unidad 2. Las consultas simples Sintaxis de la SELECT (para consultas simples) La tabla origen (cláusula FROM) Selección de columnas Alias de Columnas Columnas calculadas Ordenación de las filas (ORDER BY) Selección de filas Las cláusulas DISTINCT / ALL La cláusula TOP La cláusula WHERE Condiciones de selección Test de rango (BETWEEN) Test de pertenencia a conjunto (IN) Test de valor nulo (IS NULL) Test de correspondencia con patrón (LIKE) Utilización de Alias Funciones Predefinidas Funciones Matemáticas Funciones de Cadena Funciones de Fecha

15 15 15 16 17 17 18 19 19 20 20 21 21 22 22 23 25 27 27 28 31

Unidad 3. Las consultas multitabla La unión de tablas La composición de tablas El operador UNION El producto cartesiano Inner Join left Join y Right Join Full Join Combinar los valores de una tabla sobre si misma Resumen del tema

34 34 35 36 37 39 40 43 45 47

Unidad 4. Las consultas de agrupacion Las funciones de columna La cláusula GROUP BY La cláusula HAVING Resumen del tema

48 49 50 51 53


3

Unidad 5. El DDL, lenguaje de definición de datos Introducción La sentencia CREATE TABLE Tipos de Datos Restricción Restricción (Tipo 1) Restricción (Tipo 2) La sentencia ALTER TABLE La sentencia DROP TABLE La sentencia CREATE INDEX

54 54 54 54 56 56 59 61 63 64

Unidad 6. Actualización de datos Introducción Insertar una fila INSERT INTO...VALUES Insertar varias fila INSERT INTO...SELECT Insertar filas en una tabla nueva SELECT...INTO... Modificar el contenido de las filas UPDATE Borrar fila DELETE Resumen del tema Conceptos básicos de integridad referencial Actualización y borrar en cascada

83 83 83 84 86 87 89 90 91 93

Unidad 7. Las subconsultas Anidar subconsultas Subconsulta en la lista de selección En la cláusula FROM Subconsulta en las cláusulas WHERE y HAVING Condiciones de selección con subconsultas Resumen del tema

95 96 96 97 97 97 103

Unidad 8. Vistas Creación y utilización de Vistas Eliminación de Vistas

104 104 107

Requisitos de Instalación de SQL Server Bibliografía Anexos

108 113 114


4

Unidad 1. Introducc Introducción ión ¿Qué es una Base de Datos? Una base de datos está constituida por un conjunto de información relevante para una empresa o entidad, junto con los procedimientos para almacenar, controlar, gestionar y administrar esa información. Además, la información contenida en una base de datos cumple una serie de requisitos o características:   

Los datos están interrelacionados, sin redundancias innecesarias. Los datos son independientes independientes de los programas que los usan. Se emplean emplean métodos métodos determinados determinados para recuperar los los datos almacenados o para incluir datos nuevos y borrar o modificar los los existentes

Una base de datos estará organizada de forma que se cumplan los requisitos para que la información se almacene con las mínimas redundancias, con capacidad de acceso para diferentes usuarios pero con un control de seguridad y privacidad. Debe tener mecanismos que permitan recuperar la información en caso de pérdida y la capacidad de adaptarse fácilmente a nuevas necesidades de almacenamiento.

Qué es un Sistema de Gestión de Bases de Datos? Un Sistema de Gestión de Bases de Datos (SGBD) es una aplicación formada por un conjunto de programas que permite construir y gestionar bases de datos. Proporciona al usuario de la base de datos las herramientas necesarias para realizar, al menos, las siguientes tareas:  Definir las estructuras de los datos. Manipular los los datos. Es decir, insertar nuevos nuevos datos, así como como modificar, borrar y  consultar los datos existentes.  Mantener la integridad de la información.  Proporcionar control de la privacidad y seguridad de los datos en la Base de Datos, permitiendo sólo el acceso a los mismos a los usuarios autorizados. Para realizar las funciones que acabamos de describir, el Sistema Gestor de Bases de Datos necesita un conjunto de programas que gestionen el almacenamiento y la recuperación de dichos datos y un personal informático que maneje dichos programas. Los componentes principales de un SGBD son: GESTOR DE LA BASE DE DATOS Es un conjunto de programas transparentes al usuario que se encargan de gestionar la seguridad de los datos y el acceso a ellos. Interacciona con el sistema operativo proporcionando una interfaz entre el usuario y los datos. Cualquier operación que se realice ha de estar procesada por este gestor. DICCIONARIO DE LA BASE DE DATOS Es donde se almacena toda la descripción de los diferentes objetos de la base de datos. Esta información se almacena con la misma estructura que los datos de los usuarios. El almacenamiento de esta información lo realiza el sistema gestor y cualquier usuario puede acceder a su contenido con el mismo lenguaje que al resto de los datos almacenados (SQL) LENGUAJES MICROSOFT SQL Server Gota a Gota en las Bases de Datos Relacionales Lic. Elvin German

5

El sistema gestor ha de proporcionar lenguajes que permitan definir la estructura de los datos, almacenar la información y recuperarla. Podrán utilizar estos lenguajes los usuarios y lo administradores de la base de datos. Estos lenguajes s on: Lenguaje de definición de datos (DDL) Para definir la estructura con la que almacenaremos los datos. Lenguaje de manipulación de datos (DML) Para añadir, modificar o eliminar datos, así como recuperar la información almacenada. Lenguaje de control de datos (DCL) Para controlar el acceso a la información y la seguridad de los datos. Permiten limitar y controlar los accesos a la información almacenada. De esta tarea se ocupa el administrador de la base de datos. ADMINISTRADOR DE LA BASE DE DATOS Es una persona o grupo de personas responsables de la seguridad y la eficiencia de todos los componentes del sistema de bases de datos. Deben conocer el sistema tanto a nivel físico como lógico y a todos los usuarios que interaccionan con él. USUARIOS DE LA BASE DE DATOS Tradicionalmente considerados como un componente más de los sistemas gestores de bases de datos, debido a que estos fueron los primeros en considerarlos una parte importante para el correcto funcionamiento del sistema. Pueden ser usuarios terminales (usuarios no especializados que interaccionan con la base de datos), usuarios técnicos (usuarios que desarrollan programas de aplicación para ser utilizados por otros) y usuarios especializados (usuarios que utilizan el sistema gestor de la base de datos como una herramienta de desarrollo dentro de otros sistemas más complejos).

¿Qué es el SQL? El SQL SQL (Structured query language), language), lenguaje de consulta estructurado, estructurado , es un lenguaje surgido de un proyecto de investigación de IBM para el acceso a bases de datos relacionales. Actualmente se ha convertido en un estándar de lenguaje de bases de datos, y la mayoría de los sistemas de bases de datos lo soportan, desde sistemas para ordenadores personales, hasta grandes ordenadores. Por supuesto, a partir del estándar cada sistema ha desarrollado su propio SQL que puede variar de un sistema a otro, pero con cambios que no suponen ninguna complicación para alguien que conozca un SQL concreto, como el que vamos a ver aquí correspondiente al SQL SERVER. Como su nombre indica, el SQL nos permite permite realizar consultas a la base de datos. datos. Pero el nombre se queda corto ya que SQL además realiza funciones de definición, control y gestión de la base de datos. Las datos. Las sentencias SQL se clasifican según su finalidad dando origen a tres „lenguajes‟ o mejor dicho sublenguajes: 



DDL (Data Definition Language), lenguaje de definición de datos, incluye órdenes para DDL (Data definir, modificar o borrar las tablas en las que se almacenan los datos y de las relaciones entre estas. (Es el que más varia de un sistema a otro) DCL (Data Control Language), lenguaje de control de DCL (Data control de datos, contiene elementos útiles para trabajar en un entorno multiusuario, en el que es importante la protección de los datos, la seguridad de las tablas y el establecimiento de restricciones en el acceso, así como elementos para coordinar la compartición de datos por parte de usuarios concurrentes, asegurando que no interfieren unos con otros.


6



DML (Data Manipulation Language), lenguaje de manipulación de DML (Data manipulación de datos, nos permite recuperar los datos almacenados en la base de datos y también incluye órdenes para permitir al usuario actualizar la base de datos añadiendo nuevos datos, suprimiendo datos antiguos o modificando datos previamente almacenados.

Características del lenguaje frase (escrita en inglés inglés)) con la que decimos lo Una sentencia SQL es como una frase que queremos obtener y de donde obtenerlo. obtenerlo . verbo (palabra Todas las sentencias empiezan con un verbo (palabra reservada que indica la acción a cción a realizar), cláusulas,, algunas obligatorias obligatorias y otras opcionales opcionales que completan la seguido del resto de cláusulas sintaxis para que se puedan ejecutar correctamente, frase. Todas las sentencias siguen una sintaxis para describir esa

Cómo interpretar un diagrama sintáctico


7

Las palabras que aparecen en mayúsculas son palabras reservadas se tienen que poner tal cual y no se pueden utilizar para otro fin, por ejemplo, en el diagrama de la figura tenemos las palabras reservadas SELECT SELECT,, ALL, DISTINCT, FROM, WHERE. WHERE. Las palabras en minúsculas son variables que el usuario deberá sustituir por un dato concreto. En el diagrama tenemos nbcolumna, expresion-tabla y condicion-de-busqueda. Una sentencia válida se construye siguiendo la línea a través del diagrama hasta el punto que abajo. Cuando se marca el final. Las líneas se siguen de izquierda a derecha y de arriba abajo. flecha.. quiere alterar el orden normal se indica con una una flecha ¿Cómo se interpretaría el diagrama sintáctico de la figura? Hay que empezar por la palabra SELECT SELECT,, después puedes poner ALL ALL o bien DISTINCT o nada, a continuación un nombre de columna, o varios separados separados por comas, a continuación la FROM y palabra FROM y una expresión-tabla, y por último de forma opcional puedes incluir la cláusula WHERE con WHERE con una condición-de-búsqueda. Por ejemplo: SELECT ALL col1,col2,col3 FROM mitabla SELECT col1,col2,col3 FROM mitabla SELECT DISTINCT col1 FROM mitabla SELECT col1,col2 FROM mitabla WHERE col2 = 0 Todas estas sentencias se podrían escribir y no darían lugar a errores sintácticos Cuando una palabra opcional está subrayada subrayada,, esto indica que ese es el valor por defecto (el defecto (el valor que se asume si no se pone nada). En el ejemplo anterior las dos primeras sentencias son equivalentes (en el diagrama ALL aparece subrayada).

¿Qué podemos hacer con SQL? Todos los principales SGBDR incorporan un motor SQL en el Servidor de Base Datos, así como herramientas de cliente que permiten enviar comandos SQL para que sean procesadas por el motor del servidor. De esta forma, todas las tareas de gestión de la Base de Datos (BD) pueden realizarse utilizando sentencias SQL. Lo que podemos hacer con este lenguaje SQL es:  Consultar datos de la Base de Datos.  Insertar, modificar y borrar datos.  Crear, modificar modificar y borrar objetos de la Base de Datos.  Controlar el acceso a la información. Garantizar la consistencia de los datos. 

Datos variables Las columnas de la base de datos almacenan valores que tendrán diferentes valores en cada fila. Estos datos se definen indicando su nombre ( NombreColumna) y el tipo de datos que almacenarán. La forma de almacenarlos no es la misma para todos, por lo tanto una parte importante de la definición de un dato es la especificación de su tipo. Al indicar el tipo de datos se suele indicar también el tamaño. MICROSOFT SQL Server Gota a Gota en las Bases de Datos Relacionales Lic. Elvin German

8

La cantidad de tipos de datos posibles es muy extensa, quedando la enumeración completa fuera de los objetivos de este curso. A continuación se indican algunos de los tipos de datos más utilizados suficientes para este curso (para mayor detalle consultar el manual).

Existen 3 tipos de instrucciones para el lenguaje en SQL, mencionadas anteriormente. -

Lenguaje de control de datos (DDL) : Creación y eliminación de tipos de datos y objetos. . CREATE Crear Objeto . ALTER Modificar los datos creados . DROP Eliminar el Objeto

-

Lenguaje de control de datos (DCL) : Se basa en los derechos que tiene el usuario sobre la base da datos (Permisos). . GRANT

Dar permisos a un usuario para efectuar determinadas instrucciones Eliminar el permiso que se ha concedido con el

. DENY GRANT . REVOKE Eliminar todos los permisos -

Lenguaje de manipulación de datos (DML) : Desarrollo de la programación de la base de datos. . SELECT . INSERT . UPDATE . DELETE

Elementos de sintaxis: Directivas de procesos por lotes -

-

T RANSACT SQL a las GO: Envia lotes de intrucciones de TRANSACT herramientas y utilidades (Sirve para separar bloques de instrucciones) EXEC O EXECUTE: Ejecuta funciones definidas por el usuario, procedimientos de sistema y procedimientos almacenados.

Comentarios en SQL: -

En línea: -En Bloque: /* comentario */

Tablas en SQL: MICROSOFT SQL Server Gota a Gota en las Bases de Datos Relacionales Lic. Elvin German

9

   

Tabla master: Es la tabla que contiene como generar una base de datos y sobre ella, se crean todas las bases de datos. Tabla model: Es la tabla modelo, las bases de datos creadas se basan en esta tabla como modelo. Tabla Northwind y Pubs: Son tablas de ejemplos que vienen con SQL y todo usuario puede trabajar con ellas. Identificadores para los objetos:

Los nombres que se le dan a las tablas, lo primero es que no pueden empezar por un número, deben empezar por un signo alfabético, pueden incluir el guion bajo ( _ ), la arroba @ y la almohadilla #. Generalmente para las variables locales se usan @ + el nombre. EJEMPLO: @Contador. Para las variables totales se usan dos arrobas + el nombre @@Contador EJEMPLO: @@Error #Nombre: indica una tabla o procedimiento temporal (Local) ##Nombre: Igual que el anterior pero global.

Tablas de los ejemplos y ejercicios A lo largo del curso basaremos todos los ejemplos y ejercicios en las tablas que aparecen a continuación. Nota: Estas tablas están orientadas a la didáctica, no a un diseño óptimo SELECT * FROM EMPLEADOS

SP_HELP EMPLEADOS


10

SELECT * FROM DEPARTAMENTOS


11

SP_HELP DEPARTAMENTOS

SELECT * FROM PRODUCTOS

SP_HELP PRODUCTOS


12

SELECT * FROM CLIENTES

SP_HELP CLIENTES

SELECT * FROM PEDIDOS


13

SP_HELP PEDIDOS


14

Unidad 2. Las consultas simples Objetivo Empezaremos por estudiar la sentencia SELECT SELECT,, que permite recuperar datos de de una o varias tablas tablas.. La sentencia SELECT es con mucho la más compleja y potente de las consultas más simples simples,, basadas en una una sola sentencias SQL. Empezaremos por ver las consultas tabla.. tabla Esta sentencia forma parte del DML (lenguaje de manipulación de datos), en este tema veremos cómo seleccionar columnas de columnas de una tabla, cómo seleccionar filas y filas y cómo obtener ordenadas por el criterio que queramos. las filas ordenadas por El resultado de la consulta es una tabla lógica, porque no se guarda en el disco sino que está en memoria y cada vez que ejecutamos la consulta se vuelve a calcular. Cuando ejecutamos la consulta se visualiza el resultado en forma de tabla con columnas y filas, pues en la SELECT tenemos que indicar qué columnas queremos que tenga el resultado y qué filas queremos seleccionar de la tabla origen.

Sintaxis de la sentencia SELECT (consultas simples)

La tabla origen – FROM Con la cláusula FROM FROM indicamos en qué tabla tabla tiene que buscar la información. información. En este capítulo de consultas simples el resultado se obtiene de una única tabla. La sintaxis de la cláusula es: FROM especificación de tabla


15

Una especificación de tabla puede ser el nombre de una consulta guardada (las que aparecen en la ventana de base de datos), o el nombre de una tabla que a su vez puede tener el siguiente formato:





Aliastabla es un nombre de alias Aliastabla es alias,, es como un segundo nombre que asignamos a la tabla tabla,, si en una consulta definimos un alias para la tabla, esta se deberá nombrar utilizando ese nombre y no su nombre real, además ese nombre sólo es sólo es válido en la consulta consulta donde se define. El alias se suele emplear en consultas basadas en AS que más de una tabla que veremos en el tema siguiente. La palabra AS que se puede poner delante del nombre de alias es opcional y es el valor por defecto por lo que no tienen ningún efecto. ofi ; equivalente a SELECT ......FROM Ejemplo: SELECT ......FROM oficinas ofi oficinas AS ofi esta ofi esta sentencia me indica que se van a buscar los datos en la tabla oficinas que queda renombrada en esta consulta con ofi . En una SELECT SELECT podemos podemos utilizar tablas que no están definidas en la base de datos (siempre que tengamos los permisos adecuados claro), si la tabla n o está en la l a base de datos activa, debemos indicar en qué base de datos se encuentra con la cláusula IN.. IN

Selección de columnas

Utilización del * tabla' . Se utiliza el asterisco * en la lista de selección para indicar 'todas las columnas de la tabla'. ventajas:: Tiene dos ventajas Evitar nombrar las columnas una a una (es más corto). Si añadimos una columna nueva en la tabla, esta nueva columna saldrá sin tener que modificar la consulta. Se puede combinar el * con el nombre de una tabla (ej. oficinas.*), pero esto se utiliza más cuando el origen de la consulta son dos tablas. SELECT * FROM departamentos o bien SELECT dnombre.* FROM departamentos --Lista -- Lista todos los datos de los departamentos columnas de la tabla origen Las columnas se pueden especificar mediante su nombre simple simple (nbcol) o su nombre cualificado (nbtabla.nbcol, el nombre de la columna precedido del nombre de la tabla que cualificado contiene la columna y separados por un punto). MICROSOFT SQL Server Gota a Gota en las Bases de Datos Relacionales Lic. Elvin German

16

El nombre cualificado se puede emplear siempre que queramos y es obligatorio en algunos casos que veremos más adelante. nombre de columna o blanco , hay que poner el Cuando el nombre de la columna o de la tabla contiene espacios en blanco, corchetes [ ] y además el número de espacios en blanco debe coincidir. Por nombre entre corchetes ejemplo [codigo de cliente] no es lo mismo que [ codigo de cliente] (el segundo lleva un espacio en blanco delante de código) Ejemplo : SELECT apellido, oficio, fecha_alta FROM empleados Alias de columna. columna. Cuando se visualiza el resultado de la consulta, normalmente las columnas toman el nombre que tiene la columna en la tabla, si queremos cambiar ese nombre lo podemos hacer definiendo un AS será el nombre que aparecerá como título de la alias de columna mediante la cláusula AS columna. Ejemplo: SELECT apellido as nombre, oficio as cargo, fecha_alta [fecha de entrada] FROM empleados Columnas calculadas. calculadas. Además de las columnas que provienen directamente de la tabla origen, una consulta SQL calculadas cuyos valores se calculan a partir de los valores de los datos puede incluir columnas calculadas cuyos almacenados. expresión en Para solicitar una columna calculada, se especifica en la lista de s elección una expresión en vez de un nombre de columna. La expresión puede contener sumas, restas, multiplicaciones y divisiones, concatenación +, paréntesis y también funciones predefinidas). Ejemplos: SELECT apellido as nombre, salario,comision, salario+isnull(comision,0) as total FROM empleados


17

Ordenación de las filas - ORDER BY

Para ordenar las las filas filas del del resultado de la consulta, tenemos la cláusula ORDER BY. BY. Con esta cláusula se altera el orden de visualización de las filas de la tabla pero en ningún caso se modifica el orden de las filas dentro de la tabla. La tabla no se modifica. Podemos indicar la columna por la que queremos ordenar utilizando su nombre de columna (nbcolumna) o utilizando su número de orden que orden que ocupa en la lista de selección (Nºcolumna). Ejemplo: SELECT apellido, oficio, salario FROM empleados ORDER BY oficio es equivalente a SELECT apellido, oficio, salario FROM empleados ORDER BY 2 orden será ascendente ( ASC)) (de menor a mayor si el campo es numérico, por Por defecto el orden será ascendente (ASC orden alfabético si el campo es de tipo texto, de anterior a posterior si el campo es de tipo fecha/hora. SELECT apellido, oficio, salario FROM empleados ORDER BY apellido -- Obtiene -- Obtiene un listado alfabético de los empleados Si queremos podemos alterar ese orden utilizando la cláusula DESC DESC (Descendente), (Descendente), en este caso el orden será el inverso al ASC. Ejemplos: SELECT apellido, oficio, salario FROM empleados ORDER BY oficio desc es equivalente a SELECT apellido, oficio, salario FROM empleados ORDER BY 2 desc columnas, en este caso se indican las columnas También podemos ordenar por varias columnas, separadas por comas. Se ordenan las filas por la primera columna de ordenación, para un mismo valor de la primera columna, se ordenan por la segunda colum na, y así sucesivamente. MICROSOFT SQL Server Gota a Gota en las Bases de Datos Relacionales Lic. Elvin German

18

ASC se La cláusula DESC o ASC se puede indicar para cada columna y así utilizar una ordenación distinta para cada columna. Por ejemplo ascendente por la primera columna y dentro de la primera columna, descendente por la segunda columna. Ejemplos: SELECT apellido, oficio, salario FROM empleados ORDER BY oficio,apellido es equivalente a SELECT apellido, oficio, salario FROM empleados ORDER BY 1,2 desc También podemos utilizar SELECT apellido, oficio, salario FROM empleados ORDER BY 1,apellido des

Selección de filas filas queremos visualizar . A continuación veremos las cláusulas que nos permiten indicar qué filas queremos

Las cláusulas DISTINCT / ALL Al incluir i ncluir la cláusula DISTINCT DISTINCT en en la SELECT, se eliminan eliminan del del resultado las repeticiones repeticiones d de e filas. Si por el contrario queremos que aparezcan todas las filas incluidas las duplicadas, duplicadas, podemos incluir la cláusula ALL ALL o o nada, ya que ALL es el valor que SQL asume por defecto. Por ejemplo queremos saber los códigos de los directores de oficina. SELECT director FROM empleados SELECT ALL director FROM empleados Lista los códigos de los directores de las oficinas. El director 7698 aparece en 3 empleados, por lo tanto aparecerá tres veces en el resultado de la consulta, al igual que otros empleados. SELECT DISTINCT director FROM empleados MICROSOFT SQL Server Gota a Gota en las Bases de Datos Relacionales Lic. Elvin German

19

En este caso el valor 7698 aparecerá una sola vez ya que le decimos que liste los distintos valores de directores

La cláusula TOP TOP permite filas de la tabla origen. No elige entre valores La cláusula TOP permite sacar las las n primeras filas de iguales, si pido los 25 primeros valores pero el que hace 26 es el mismo valor que el 25, entonces devolverá 26 registros en vez de 25 (o los que sea). Siempre se guia por la columna de ordenación, la que aparece en la cláusula ORDER BY o en su defecto la clave principal de la tabla. Por ejemplo queremos saber los dos empleados más antiguos de la empresa. SELECT TOP 2 emp_no, apellido fecha_alta FROM empleados Order by fecha_alta

La cláusula WHERE

La cláusula WHERE WHERE selecciona selecciona únicamente las filas filas que que cumplan cumplan la la condición de selección especificada. En la consulta sólo aparecerán las filas para las cuales la condición es verdadera (TRUE), los valores nulos (NULL) no se incluyen por lo tanto en las filas del resultado. La condición de selección puede ser cualquier condición válida o combinación de condiciones selección condiciones utilizando AND (y) OR (ó). los operadores NOT (no) AND (y) y OR (ó). Para empezar veamos un ejemplo sencillo: SELECT apellido FROM empleados WHERE dep_no = 10 SELECT nombre FROM empleados WHERE dep_no = 10 AND salario > 2500


20

CONDICIONES DE SELECCION: SELECCION: son las condiciones que pueden aparecer en la cláusula WHERE.. WHERE En SQL tenemos cinco condiciones básicas: test de comparación  test de rango  test de pertenencia a un conjunto  test de valor nulo   test de correspondencia con patrón

c ompara el valor de una expresión con el valor de otra. El test de comparación compara La sintaxis es la siguiente:

 

   

= igual que <>distinto de < menor que <= menor o igual > mayor que >= mayor o igual

SELECT emp_no, apellido,salario FROM empleados WHERE salario >= 1300 AND salario <=1500 Test de rango ( rango (BETWEEN BETWEEN). ). Examina si el valor de la expresión está comprendido entre entre los dos valores definidos valores definidos por exp1 y exp2. Tiene la siguiente sintaxis:

SELECT emp_no, apellido,salario FROM empleados WHERE salario BETWEEN 1300 AND 1500


21

Test de Test de pertenencia a conjunto ( conjunto (IN IN)) Examina si el valor de de la expresión es uno de los valores incluidos en la lista de valores. valores. Tiene la siguiente sintaxis:

SELECT emp_no, dep_no,apellido, oficio FROM empleados WHERE dep_no IN (10,30,1600) SELECT emp_no, dep_no,apellido, oficio FROM empleados WHERE oficio IN (‘analista’,’director’.’ingeniero’)

Test de valor nulo ( nulo (IS IS NULL) NULL) Una condición de selección puede dar como resultado el valor verdadero TRUE, falso FALSE o nulo NULL. Cuando una columna columna que que interviene en una condición de selección contiene contiene el el valor nulo, el nulo, el resultado de la condición no es verdadero ni falso, sino nulo resultado nulo,, sea cual sea el test test que se haya utilizado. Por eso si queremos listar las filas que tienen valor en una determinada columna, no podemos utilizar el test de comparación, la condición oficina = null devuelve el valor nulo sea cual sea el valor contenido en oficina. Si queremos preguntar si una columna especial , el test de valor nulo. contiene el valor nulo debemos utilizar un test especial, Tiene la siguiente sintaxis:

Ejemplo: SELECT emp_no, dep_no,apellido, oficio,comision FROM empleados Where comision is null SELECT emp_no, dep_no,apellido, oficio,comision FROM empleados Where comision is not null


22

Test de correspondencia con patrón ( patrón (LIKE LIKE)) Se utiliza cuando queremos utilizar caracteres comodines comodines para formar el valor con el comparar. Tiene la siguiente sintaxis:

COMODINES % Cualquier número de caracteres _ Para un carácter individual [ ] Para un conjunto de caracteres que esté dentro del corchete [ ^ ] Que el carácter individual que no esté e sté dentro del corchete EJEMPLO: LIKE ‘%een’ Muestra todos los caracteres que acaben con een EJEMPLO: LIKE ‘%een%’ Muestra todos los caracteres que contengan een en ese orden EJEMPLO: LIKE ‘_en’ Muestra todos los caracteres que contenga tres letras y acaben en en EJEMPLO: LIKE ‘[CK% ]’ Muestra todos los caracteres que empiecen por C o K EJEMPLO: LIKE ‘[S-V]ing’ Nombre de 4 letras cuya primera letra estuviera entre S o V y acabe en ing EJEMPLO: LIKE ‘M[^c]%’ Todos los que empiecen por M y segunda letra no sea una c. c. No hay limite de caracteres.

CONSULTAS CON LIKE 1. Seleccionar todos los empleados cuyo apellido apellido comience por M select * select * from from empleados empleados where where apellido apellido like 'M%'

2. Seleccionar todos los empleados cuyo apellido termine termine con la letra Z select * select * from from empleados empleados where where apellido apellido like '%z'

3. Seleccionar todos los empleados que contengan contengan en su apellido ER. select * select * from from empleados empleados where where apellido apellido like '%er%' MICROSOFT SQL Server Gota a Gota en las Bases de Datos Relacionales Lic. Elvin German

23

4. Mostrar todos los empleados cuyo nombre sea de 4 letras y su apellido apellido termine con la letra a select * select * from from empleados empleados where where apellido apellido like '___a'

5. Mostrar todos los empleados cuyo apellido comience entre las letras letras E y F. select * select * from from empleados empleados where where apellido apellido like '[E-F]%'

6. Mostrar todos los los empleados cuyo apellido comience por la letra A, contenga dentro de su apellido de la letra A a la M y que terminen en O. select * select * from empleados where where apellido apellido like 'A%[a-m]%o'

7. Mostrar todos los empleados cuyo apellido comience por la letra M y la segunda letra no sea una A. select * select * from empleados where where apellido apellido like 'M[Â]%'

8. Mostrar todos los empleados cuyo apellido sea de 5 letras y su tercera letra sea entra la A y la S terminando en Z. select * select * from empleados where where apellido apellido like '__[a-ñ]_z'

9. Mostrar todos los empleados cuyo apellido sea sea de 6 letras y no comience entre la A y la D. select * select * from empleados where where apellido apellido like '[â-d]_____'


24

10. Mostrar todos los que empiecen por la A y cuya cuarta cu arta letra no esté comprendida entre A – G select * select * from empleados where where apellido apellido like 'A__[â-g]%'

Utilización de alias

SELECT [ALL/DISTINCT] ExpresionColumna [AS ] AliasColumna]

[, ExpresionColumna [AS] AliasColumna] .... .... ]

FROM NombreTabla [AliasTabla] ;

La cláusula AS que asigna el alias puede omitirse Usar alias para una tabla es opcional cuando su finalidad consiste en simplificar su nombre original, y obligatorio en consultas cuya sintaxis lo requiera (más adelante lo utilizaremos) Usar alias para las columnas puede ser necesario porque los títulos o cabeceras que muestra la salida de una consulta para las columnas seleccionadas, se corresponden con los nombres de las columnas de las tablas o las expresiones y esto no siempre es muy visual. Para mejorar su legibilidad y estética se utilizan los alias de columna. También puede ser utilizado, en algunos casos, para renombrar la columna y utilizar este nombre posteriormente

Ejemplos de utilización de alias Ejemplos de alias de tabla

1 - Mostrar el apellido y la fecha de alta alta de todos los empleados. SELECT apellido [nombre del empleado], fecha_alta as fecha, oficio as “cargo” FROM empleados empleados;


25

FUNCIONES PREDEFINIDAS


26

Funciones predefinidas Son funciones incorporadas por el gestor y son muy utilizadas en SQL y dan mucha potencia al lenguaje. Estas funciones predefinidas devuelven un valor dependiendo del valor de un argumento que se pasa en la llamada. Cabe subrayar que las funciones no modifican los valores del argumento, indicado entre paréntesis, sino que devuelven un valor creado a partir de los argumentos que se le pasan en la llamada, y ese valor puede ser utilizado en cualquier parte de una sentencia SQL. Existen muchas funciones predefinidas para operar con todo tipo de datos. A continuación se indican las funciones predefinidas más utilizadas. Vamos a ver estas funciones agrupadas según el tipo de datos con los que operan y/o el tipo de datos que devuelven. Nota: estas funciones pueden variar según el s istema gestor que se utilice.

Funciones Matemáticas Funciones de cadena Funciones de fecha FUNCIONES MATEMATICAS ABS Es el valor Absoluto Select ABS Select ABS(-4) (-4) as 'VALOR ABSOLUTO'-->4 ABSOLUTO'-->4

POWER Devuelve el valor de la expresión indicada elevada a la potencia especificada. Select POWER POWER(3,2) (3,2) as '3 ELEVADO A 2'-->9 2'-->9

ROUND Devuelve una expresión numérica, redondeada a la longitud o precisión especificada. Round(Numero, Redondeo del Número) ROUND siempre devuelve un valor. Si length es un valor negativo y mayor que el número de dígitos anteriores al separador decimal, ROUND devuelve 0. Select ROUND(123.4567,2)-->123.4600 Select ROUND(123.4567,2) -->123.4600 Select ROUND(123.4567,-2) Select ROUND(123.4567,-2)-->100.0000 -->100.0000 Select ROUND(123.4567,0) Select ROUND(123.4567,0)-->123.0000 -->123.0000 Select ROUND(123.4567,-3) Select ROUND(123.4567,-3)--->0 --->0 MICROSOFT SQL Server Gota a Gota en las Bases de Datos Relacionales Lic. Elvin German

27

SQUARE Devuelve el cuadrado de la expresión especificada. Select SQUARE SQUARE(4) (4) as „Cuadrado‟-->16.0

SQRT Devuelve la raíz cuadrada de la expresión especificada. Select SQRT SQRT(4) (4) as as [RAIZ [RAIZ CUADRADA]-->2.0 CUADRADA]-->2.0

FUNCIONES DE CADENA ASCII Devuelve el código ASCII del carácter más a la izquierda de una expresión de caracteres. Select ASCII(('A' Select ASCII 'A')-->65 )-->65 Select ASCII Select ASCII(('a' 'a')-->97 )-->97 Select ascii ascii(('aula' 'aula')-->97 )-->97

CHAR Una función de cadena que convierte un código ASCII int en un carácter. select char (65)-->A (65)-->A select char (97)-->a (97)-->a

CHARINDEX Devuelve la posición inicial de la expresión especificada en una cadena de caracteres. CHARINDEX ( expression1 , expression2 [ , start_location ] ) Argumentos expression1 Es una expresión que contiene la secuencia de caracteres que se desea b uscar. Expression1 es una expresión del tipo de cadenas cortas de caracteres. Expression2 Es una expresión, normalmente una columna, en la que se busca la cadena especificada. Expression2 es de la categoría del tipo de datos cadena de caracteres. start_location Es la posición del carácter de expression2 en el que se empieza la búsqueda de expression1. Si no se especifica start_location, es un número negativo o es cero, la búsqueda empieza al principio de la cadena expression2. MICROSOFT SQL Server Gota a Gota en las Bases de Datos Relacionales Lic. Elvin German

28

Si expression1 no se encuentra en expression2, CHARINDEX devuelve 0. Si alguno de los dos es null, devuelve null select charindex charindex(('cie' 'cie',,'murcielago' 'murcielago')-->4 )-->4 select charindex charindex(('cie' 'cie',,'murcielago' 'murcielago',2)-->4 ,2)-->4 select charindex charindex(('cie' 'cie',,'murcielago' 'murcielago',5)-->0 ,5)-->0 select charindex charindex(('cie' 'cie',,'murcielago' 'murcielago',-6)-->4 ,-6)-->4

LEFT Devuelve la parte de una cadena de caracteres que comienza en un número de caracteres especificado a partir de la izquierda select left left(('murcielago' 'murcielago',5)-->murci ,5)-->murci

RIGHT Devuelve la parte de una cadena de caracteres que comienza en el número de caracteres especificado en integer_expression a partir de la derecha. select right right(('hola que tal',5)-->e tal',5)-->e tal

LEN Cuenta el número de caracteres que se incluyen en la cadena. select len len(('murcielago' 'murcielago')-->10 )-->10

LOWER Convierte a Minúsculas la cadena especificada select lower ('MurcIELaGO' 'MurcIELaGO')) as as [minusculas]-->murcielago [minusculas]-->murcielago

UPPER Convierte a Mayúsculas la cadena especificada select upper ('murcielago') ('murcielago') as [MAYUSCULAS]-->MURCIELAGO

RTRIM y LTRIM Elimina los espacios que existen a la izquierda y a la derecha respectivamente. select Rtrim Rtrim ( ('' murcielago ') AS [SIN ESPACIOS]-->murcielago select Ltrim Ltrim ( ('' murcielago ') AS [SIN ESPACIOS]-->murcielago select ltrim ltrim((rtrim rtrim((' hola '))+ '))+'.' '.'

REPLACE Reemplaza por una tercera expresión todas las aparici ones de la segunda expresión de cadena proporcionada en la primera expresión de cadena MICROSOFT SQL Server Gota a Gota en las Bases de Datos Relacionales Lic. Elvin German

29

select replace replace(('hola que tal estas', estas' ,'a' 'a',,'A' 'A')-->holA )-->holA que tAl estAs select replace replace(('buenos dias, que tal estas', estas','ue' 'ue',,'ñññ' 'ñññ')-->bñññnos )-->bñññnos dias,qñññ tal estas

SPACE Coloca el número de espacios que se le indiquen para entre una cadena de caracteres. select 'hola' 'hola'+ +space space(5)+ (5)+'que 'que tal'-->hola tal'-->hola

que tal

SUBSTRING Devuelve parte de una expresión de caracteres, binaria, de texto o de imagen. Sintaxis: SUBSTRING (Expresión , Comienzo , Duración ) Argumentos expression Es una cadena de caracteres, cadena binaria, texto, i magen, columna o expresión que incluye una columna. No deben usarse expresiones que incluyan funciones de agregado. start Es un entero que especifica el punto en que comienza la subcadena. length Es un entero que especifica la longitud de la subcadena (el número de caracteres o bytes que se devuelven). select substring substring(('murcielago' 'murcielago',3,5)-->rciel ,3,5)-->rciel select substring substring(('murcielago' 'murcielago',3, ,3,len len(('murcielago' 'murcielago'))-->rciel ))-->rciel

REVERSE Devuelve invertida una expresión de carácter. select reverse reverse(('hola' 'hola'))

REPLICATE Repite una expresión de caracteres un número especificado de veces. select replicate replicate(('murcielago' 'murcielago',5) ,5) replicate, replicate, replicate, replicate, replicate

STUFF Elimina el número de caracteres especificado e inserta otro conjunto de caracteres en un punto de inicio indicado. Select STUFF STUFF(('Murcielago' 'Murcielago',, 2, 3, 'ijklmn' 'ijklmn')) Mijklmnielago MICROSOFT SQL Server Gota a Gota en las Bases de Datos Relacionales Lic. Elvin German

30

FUNCIONES DE FECHA Parte de la fecha Year Quarter Month Dayofyear Day Week Hour Minute Second Millisecond

Abreviaturas yy, yyyy qq, q mm, m dy, y dd, d wk, ww Hh mi, n ss, s Ms

GetDate() select getdate getdate() () Funcion que recupera la fecha actual del sistema.

Convert, Cast Convierten explícitamente una expresión de un tipo de datos en otro. CAST y CONVERT proporcionan funciones similares. Convert(Tipodedatosdestino, Origen, Estilo) Tiposdedatosdestino: Siempre ha de ser tipo carácter o o Origen: Puede ser tipo fecha, numérico o moneda. Estilo: Opcional. Es un código que indica el formato en el que o devuelve la cadena de caracteres. Sintaxis: Convert(TipoDato,Dato) Cast (Dato as TipoDato) Ejemplo: declare @n int int,@palabra ,@palabra nvarchar(10) set @n = 1 set @palabra ='Número' print convert convert((nvarchar (2),@n) (2),@n) + ' ' + @palabra print cast cast(@n (@n as nvarchar (2)) (2)) + ' ' + @palabra MICROSOFT SQL Server Gota a Gota en las Bases de Datos Relacionales Lic. Elvin German

31

while (@n<11) begin print convert convert((nvarchar (2),@n) (2),@n) + ' ' + @palabra print cast cast(@n (@n as nvarchar (2)) (2)) + ' ' + @palabra set @n = @n + 1 end

DateName Devuelve una cadena de caracteres que representa la parte de la fecha especificada de la fecha especificada Los calculos para las horas no son exactos cuando se trata de SmallDateTime, por lo que devuelve 0. select datename datename((month month,,fecha_alta) fecha_alta) as 'Nombre mes' from empleados where emp_no = 7867  diciembre select datename datename((m,fecha_alta) as 'Nombre 'Nombre mes' from empleados where emp_no = 7867 diciembre

select datename datename((week week,fecha_alta) ,fecha_alta) as 'Numero Semana' from empleados where emp_no = 7867 51 select datename datename((week week,fecha_alta) ,fecha_alta) as 'Numero Semana' from empleados where emp_no = 7867  51 select datename datename(( weekday weekday,fecha_alta) ,fecha_alta) as 'Dia Semana' from empleados where emp_no=7867  miercoles select datename datename(dw,fecha_alta) (dw,fecha_alta) as 'Dia Semana' from empleados where emp_no=7867  miercoles

Horas: select datename datename(mi,fecha_alta) (mi,fecha_alta) as as 'Minutos' 'Minutos' from empleados where where emp_no emp_no = 7867 select datename datename(( minute minute,,getdate getdate()) ()) as as 'minutos' 'minutos' select datename datename(( mi,getdate mi,getdate()) ()) as as 'minutos' 'minutos' select datename datename(( hh,fecha_alta) as as 'hora' 'hora' from empleados where where emp_no emp_no = 7867 select datename datename(( hour ,getdate getdate()) ()) as as 'hora'-->17 'hora'-->17 select datename datename(( hh,getdate hh,getdate()) ()) as as 'hora'-->17 'hora'-->17


32

DatePart Devuelve la parte de la fecha u hora ho ra indicada. Sintaxis: Datepart(Valoradevolver, fecha) select datepart datepart(mm,fecha_alta) (mm,fecha_alta) as as 'Mes',apellido 'Mes',apellido from emp-->11 select datepart datepart(hh, (hh,getdate getdate()) ()) as as 'Hora'-->17 'Hora'-->17 select datepart datepart(mi, (mi,getdate getdate()) ()) as as 'Minutos'-->54 'Minutos'-->54

Nombres de Fechas Day(fecha) Devuelve un INT, equivale a datepart select day day((getdate getdate()) ()) as as dia-->12 dia-->12 select datepart datepart(dd, (dd,getdate getdate())-->12 ())-->12 select month month((getdate getdate())-->7 ())-->7 select datepart datepart(mm, (mm,getdate getdate())-->7 ())-->7 select year (getdate getdate())-->2002 ())-->2002 select year (fecha_alt) (fecha_alt) from from emp emp where emp_no = 7867 select datediff (yyyy,fecha_alt,getdate()) (yyyy,fecha_alt,getdate()) as „Diferencia‟ from from emp emp where where emp_no emp_no = 7867

DateAdd DateAdd( datepart , number, date ) Añade un número a la fecha puesta puesta DatePart es el formato de lo que queremos añadir. Number es el número que queremos incrementar la fecha expuesta. select convert convert((datetime datetime,,'1-1-02' '1-1-02')) select dateadd dateadd(dd,7, (dd,7,'1-1-02' '1-1-02'))

DateDiff Devuelve la diferencia entre dos fechas en el intervalo que le indiquemos. Sintaxis: DateDiff (DatoqueDevuelve, Fecha1, Fecha2) Datoquedevuelve : Indicamos como queremos que haga la o comparación y el tipo de dato que nos devolverá, años, días, minutos etc. select datediff (yyyy,fecha_alt,getdate (yyyy,fecha_alt,getdate()) ()) as as 'Diferencia' 'Diferencia' from from emp emp where where emp_no emp_no = 7867


33

Unidad 3. Las consultas multitablas Introducción multitabla llamadas así porque están basadas En este tema vamos a estudiar las consultas multitabla llamadas en más de una tabla. tabla. El SQL Server trabajar con diferentes grupos de consultas multitablas:  

la unión unión de de tablas la composición composición de de tablas

La unión de tablas tablas con las mismas columnas y columnas y queremos Esta operación se utiliza cuando tenemos dos tablas con tabla con las filas de la primera y las filas de la segunda. segunda . En este caso obtener una nueva tabla con la tabla resultante tiene las mismas columnas que la primera tabla (que son las mismas que las de la segunda tabla). Por ejemplo tenemos una tabla de libros nuevos y una tabla de libros antiguos y queremos una lista con todos los libros que tenemos. En este caso las dos tablas tienen las mismas columnas, lo único que varía son las filas, además queremos obtener una lista de libros (las columnas de una de las tablas) con las filas que están tanto en libros nuevos como las que están en libros antiguos, en este caso utilizaremos este tipo de operación. reales almacenadas en la base de datos o Cuando hablamos de tablas pueden ser tablas reales tablas lógicas lógicas (resultados de una consulta), esto nos permite utilizar la operación con más frecuencia ya que pocas veces tenemos en una base de datos tablas idénticas en cuanto a resultado es lógica. columnas. El resultado es siempre una tabla lógica. Por ejemplo queremos en un sólo listado los productos cuyas existencias sean iguales a cero y también los productos que aparecen en pedidos del año 90. En este caso tenemos unos productos en la tabla de productos y los otros en la tabla de pedidos, las tablas no tienen las mismas columnas no se puede hacer una union de ellas pero lo que interesa realmente es el identificador del producto (idfab,idproducto), luego por una parte sacamos los códigos de los productos con existencias cero (con una consulta), por otra parte los códigos de los productos que aparecen en pedidos del año 90 (con otra consulta), y luego unimos estas dos tablas lógicas. UNION.. El operador que permite realizar esta operación es el operador UNION


34

La composición de tablas La composición de tablas consiste en concatenar filas filas de una tabla con filas de otra. En este columnas de unidas a columnas de caso obtenemos una tabla con las columnas de la primera tabla unidas a las columnas de la segunda tabla, tabla, y las filas de la tabla resultante son concatenaciones concatenaciones d filas de de e filas de la primera tabla con con filas tabla. filas de la segunda tabla. El ejemplo anterior quedaría de la siguiente forma con la composición:

A diferencia de la unión la composición permite obtener una fila con datos de las dos tablas, esto es muy útil cuando queremos visualizar filas cuyos datos se encuentran en dos tablas. Por ejemplo queremos listar los pedidos con el nombre del representante que ha hecho el pedido, pues los datos del pedido los tenemos en la tabla de pedidos pero el nombre del representante está en la tabla de empleados y además queremos que aparezcan en la misma línea; en este caso necesitamos componer las dos tablas (Nota: en el ejemplo expuesto a continuación, hemos seleccionado las filas que nos interesan).

Existen distintos tipos de composición, aprenderemos a utilizarlos todos y a elegir el tipo más apropiado a cada caso. Los tipos de composición de composición de tablas son:  

: Indica que combine los campos con resultados comunes Inner J oin : Indica que combine todos los campos aunque los resultados Full Join

sean diferentes.


35



left Join:

Indica que muestre todos los resultados de la columna de la

izquierda 

Right Join:

Indica que muestre todos los resultados de la columna de

la derecha 

Muestra un producto cartesiano combinando todos los resultados de las dos tablas.

Cross Join:

El operador UNION Como ya hemos visto en la página anterior, el operador UNION UNION sirve sirve para obtener a partir de dos tablas tablas con con las mismas columnas, columnas, una nueva tabla con las filas filas de de la primera y las filas segunda. segunda de la . La sintaxis es la siguiente

Consulta puede ser un nombre de tabla, Consulta tabla, un nombre de consulta consulta (en estos dos casos el nombre debe estar precedido de la palabra TABLE TABLE), ), o una sentencia SELECT completa SELECT completa (en TABLE). SELECT puede este caso no se puede poner TABLE ). La sentencia SELECT puede ser cualquier sentencia SELECT con SELECT BY. con la única restricción de que no puede contener la cláusula ORDER BY. Después de la primera consulta viene la palabra UNION UNION y y a continuación la segunda consulta. La segunda consulta sigue las mismas reglas que la primera consulta. 

columnas pero las columnas Las dos consultas deben tener el mismo número de columnas llamarse de diferente forma tipos de distintos.. pueden llamarse de diferente forma y ser de tipos de datos distintos

columnas del resultado se llaman como consulta. Las columnas del resultado se llaman como las de la primera consulta.



Por defecto defecto la la unión no incluye filas repetidas, repetidas, si alguna fila está en las dos tablas, sólo aparece una vez en el resultado.



queremos que repeticiones de filas,, incluimos la Si queremos que aparezcan todas las filas incluso las repeticiones de filas ALL ( palabra ALL (todo en inglés). El empleo de ALL ALL tienen tienen una ventaja ventaja,, la consulta se ejecutará más rápidamente. rápidamente. Puede que la diferencia no se note con tablas pequeñas, pero si tenemos tablas con muchos registros (filas) la diferencia puede ser notable. 



dos tablas, para ello después de la segunda consulta Se puede unir más de dos repetimos la repetimos la palabra UNION y así sucesivamente. También podemos indicar que queremos el resultado ordenado por ordenado por algún criterio, en este caso se incluye la cláusula ORDER BY que BY que ya vimos en el tema anterior. La BY se escribe después después de consulta, al final de la sentencia; cláusula ORDER BY se de la última consulta, ordenación podemos utilizar su número de orden o orden o el para indicar las columnas de ordenación podemos 36 MICROSOFT SQL Server Gota a Gota en las Bases de Datos Relacionales Lic. Elvin German

nombre de la columna, columna, en este último caso se deben de utilizar los nombres de consulta ya que son los que se van a utilizar para nombrar las columna de la primera consulta ya columnas del resultado.

El producto cartesiano El producto cartesiano es un tipo de composición de tablas, aplicando el producto cartesiano a columnas de unidas a dos tablas se obtiene una tabla con las columnas de la primera tabla unidas a las columnas segunda tabla tabla, todas las posibles de la , y las filas de la tabla resultante son concatenaciones de filas filas de la primera tabla tabla con filas de la segunda tabla. tabla. La sintaxis es la siguiente:









El producto cartesiano se cartesiano se indica poniendo poniendo en en la FROM FROM las las tablas tablas que que queremos componer separadas por comas, comas, podemos obtener así el producto cartesiano de dos, tres, o más tablas. nbtabla puede ser un nombre de tabla nbtabla tabla o un nombre de consulta. consulta. Si todas las tablas están en una base de datos externa, añadiremos la cláusula IN basedatosexterna después después de tabla. Pero para mejorar el rendimiento y de la última tabla. facilitar el uso, se recomienda utilizar una tabla vinculada en lugar de la cláusula IN. Hay que tener en cuenta que el producto producto cartesiano obtiene obtiene todas las posibles combinaciones de filas por lo tanto si tenemos dos tablas de 100 registros cada combinaciones una, el resultado tendrá 100x100 filas, si el producto lo hacemos de estas dos tablas con una tercera de 20 filas, el resultado tendrá 200.000 filas (100x100x20) y estamos hablando de tablas pequeñas. Se ve claramente que el producto cartesiano es una operación costosa sobre costosa sobre todo si operamos con más de dos tablas o con tablas voluminosas. misma, en este caso es obligatorio obligatorio utilizar Se puede componer una una tabla consigo misma, utilizar alias por lo menos para una de las dos. un nombre de alias Por ejemplo: SELECT * FROM empleados, empleados emp

En este ejemplo obtenemos el producto cartesiano de la tabla de empleados con ella misma. Todas las posibles combinaciones de empleados con empleados.

SELECT * FROM empleados,departamentos


37

Se observa que tenemos las dos filas de la primera consulta combinadas con las dos filas de la segunda. 

no es de las más utilizadas, utilizadas, normalmente cuando queremos Esta operación no componer dos tablas es para añadir a las filas de una tabla, una fila de la otra tabla, por ejemplo añadir a los pedidos los datos del cliente correspondiente, o los datos del representante, esto equivaldría a un producto cartesiano con una selección de filas:

SELECT * FROM empleados,departamentos WHERE empleados_dep_no=departamentos.dep_no Combinamos todos los empleados con todos los departamentos pero luego seleccionamos los que cumplan que el código del departamento de la tabla de departamentos sea igual al código del empleado de la tabla de empleados, por lo tanto nos quedamos con los empleados combinados con los datos del departamento correspondiente. 



Las columnas que aparecen aparecen en la cláusula WHERE de nuestra consulta anterior anterior se emparejamiento ya que permiten emparejar las filas de las denominan columnas de emparejamiento ya dos tablas. Las columnas de emparejamiento no tienen por qué estar incluidas en la lista de selección. Normalmente emparejamos emparejamos tablas que están relacionadas entre sí y una de las columnas de emparejamiento es clave principal, pues en este caso, cuando una una de de las columnas de emparejamiento emparejamiento tienen un índice índice definido es más eficiente utilizar otro JOIN. tipo de composición, el INNER JOIN.

JOIN es otro tipo de composición de tablas, permite emparejar filas de filas de distintas tablas El INNER JOIN es eficiente que con el producto cartesiano cuando cuando una de las columnas de de forma más eficiente MICROSOFT SQL Server Gota a Gota en las Bases de Datos Relacionales Lic. Elvin German

38

emparejamiento está indexada emparejamiento indexada.. Ya que en vez de hacer el producto cartesiano completo y luego seleccionar la filas que cumplen la condición de emparejamiento, para cada fila de una de las tablas busca directamente en directamente en la otra tabla las filas que cumplen que cumplen la condición, con lo cual se emparejan sólo emparejan sólo las filas que luego aparecen en el resultado. La sintaxis es la siguiente:

Ejemplo: SELECT * FROM empleados e INNER JOIN departamentos d ON e.dep_no = d.dep_no 

tabla (nombre de tabla con alias o no, tabla1 y tabla2 son especificaciones de tabla nombre de consulta guardada), de las tablas cu yos registros se van a combinar.

tabla, en este caso es obligatorio obligatorio definir alias de Pueden ser las dos la misma tabla, definir al menos un alias de tabla. 

son las columnas de emparejamiento. emparejamiento. col1, col2 son

ON los nombres de columna columna deben ser nombres Observar que dentro de la cláusula ON cualificados (llevan cualificados (llevan delante el nombre de la tabla y un punto). Las columnas de emparejamiento emparejamiento deben deben contener la misma clase de datos, datos, las dos de tipo texto, de tipo fecha etc. Los campos numéricos deben ser de tipos similares. Por ejemplo, se puede combinar campos identityy Long puesto que son tipos similares, sin embargo, no se puede combinar campos de tipo Simple y Doble.





comp representa cualquier operador de comparación comp representa comparación ( ( =, <, >, <=, >=, o <> ) y se utiliza para establecer la condición de emparejamiento.

Se pueden definir varias condiciones de condiciones de emparejamiento unidas unidas por por los operadores AND AND y y R poniendo cada condición entre paréntesis paréntesis.. Ejemplo: SELECT * FROM pedidos INNER JOIN productos ON (pedidos.fab = productos.idfab) AND (pedidos.producto = productos.idproducto) Se pueden combinar más de más de dos tablas En este caso hay que sustituir en en la sintaxis una tabla tabla por por un INNER JOIN completo. completo. Por ejemplo: SELECT * FROM (pedidos INNER JOIN clientes ON pedidos.clie = clientes.numclie) INNER JOIN empleados ON pedidos.rep = empleados.numemp En vez de tabla1 hemos escrito un INNER JOIN completo, com pleto, también podemos escribir: SELECT * FROM clientes INNER JOIN (pedidos INNER JOIN empleados ON pedidos.rep = empleados.numemp) ON pedidos.clie = clientes.numclie MICROSOFT SQL Server Gota a Gota en las Bases de Datos Relacionales Lic. Elvin German

39

En este caso hemos sustituido tabla2 por un INNER JOIN completo

El LEFT JOIN y RIGHT JOIN El LEFT JOIN y RIGHT JOIN JOIN son otro tipo de composición de tablas, también denominada composición externa. externa. Son una extensión del INNER JOIN. JOIN. Las composiciones vistas hasta ahora (el producto cartesiano cartesiano y el INNER JOIN) JOIN) son composiciones internas ya internas ya que todos los valores de las filas del resultado son valores que están en las tablas que se combinan. Con una composición interna sólo se obtienen las filas que tienen al menos una fila de la otra tabla que cumpla la condición, veamos un ejemplo: Queremos combinar los empleados con las oficinas para saber la ciudad de la oficina donde trabaja cada empleado, si utilizamos un producto cartesiano tenemos: SELECT empleados.*,ciudad FROM empleados, oficinas WHERE empleados.oficina = oficinas.oficina Observar que hemos cualificado el nombre de columna oficina ya que ese nombre aparece en las dos tablas de la FROM. Con esta sentencia los empleados empleados que que no tienen una tienen una oficina oficina asignada asignada (un valor nulo en el campo oficina de la tabla empleados) no aparecen en el resultado resultado ya que la condición empleados.oficina = oficinas.oficina será oficinas.oficina será siempre nula para esos empleados. JOIN: Si utilizamos el INNER JOIN: SELECT empleados.*, ciudad FROM empleados INNER JOIN oficinas ON empleados.oficina = oficinas.oficina Nos pasa lo mismo, el empleado 110 tiene un valor nulo en el campo oficina y no aparecerá en el resultado. Pues en los casos en que queremos queremos que que también aparezcan las aparezcan las filas que no tienen una fila coincidente en coincidente en la otra tabla, utilizaremos utilizaremos el el LEFT LEFT o o RIGHT JOIN. JOIN. JOIN es la siguiente: La sintaxis del LEFT JOIN es

La descripción de la sintaxis es la misma misma que que la del INNER JOIN (ver JOIN (ver página anterior), lo único que cambia es la palabra INNER INNER por por LEFT LEFT ( (izquierda izquierda en en inglés). resultado del INNER JOIN JOIN las filas filas de la tabla tabla de la Esta operación consiste en añadir al resultado izquierda que no tienen correspondencia izquierda correspondencia en la otra tabla, y rellenar en esas filas los campos de campos de la tabla tabla de de la derecha derecha con con valores nulos. nulos. Ejemplo:


40

SELECT * FROM empleados LEFT JOIN oficinas ON empleados.oficina = oficinas.oficina Con el ejemplo anterior obtenemos una lista de los empleados con los datos de su oficina, y el empleado 110 que no tiene oficina aparece con sus datos normales y los datos de su oficina a nulos. La sintaxis del RIGHT JOIN es JOIN es la siguiente:

JOIN (ver página anterior), lo único que cambia es la La sintaxis es la misma que la del INNER JOIN (ver INNER por RIGHT ( derecha en palabra INNER por RIGHT (derecha en inglés). Esta operación consiste en añadir al resultado resultado del INNER JOIN JOIN las filas filas de la tabla tabla de la derecha que derecha que no tienen correspondencia en correspondencia en la otra tabla, y rellenar en en esas filas los campos de la tabla tabla de de la izquierda izquierda con con valores nulos. nulos. Ejemplo: SELECT * FROM empleados RIGHT JOIN oficinas ON empleados.oficina = oficinas.oficina Con el ejemplo anterior obtenemos una lista de los empleados con los datos de su oficina, y además aparece una fila por cada oficina que no está asignada a ningún empleado con los datos del empleado a nulos. Una operación LEFT operación LEFT JOIN JOIN o RIGHT JOIN se puede anidar dentro dentro de una operación INNER JOIN, pero una operación INNER INNER JOIN JOIN no no se se puede anidar anidar dentro dentro de LEFT LEFT JOIN JOIN o RIGHT JOIN.. Los anidamientos de JOIN JOIN JOIN de distinta naturaleza no funcionan siempre, a veces depende del orden en que colocamos las tablas, en estos casos lo mejor es probar y si no permite el anudamiento, cambiar el orden de las tablas ( y por tanto de los JOIN JOINs) s) dentro de la FROM.. cláusula FROM

Por ejemplo podemos tener: SELECT * FROM clientes INNER JOIN (empleados LEFT JOIN oficinas ON empleados.oficina = oficinas.oficina) ON clientes.repclie = empleados.numclie Combinamos empleados con oficinas para obtener los datos de la oficina de cada empleado, y luego añadimos los clientes de cada representante, así obtenemos los clientes que tienen un representante asignado y los datos de la oficina del representante asignado. INNER en LEFT no Si hubiéramos puesto INNER en vez de LEFT no saldrían los clientes que tienen el empleado 110 (porque no tiene oficina y por tanto no aparece en el resultado del LEFT JOIN y JOIN y por tanto no entrará en el cálculo del INNER JOIN con JOIN con clientes).

select apellido,oficio,dnombre select apellido,oficio,dnombre MICROSOFT SQL Server Gota a Gota en las Bases de Datos Relacionales Lic. Elvin German

41

from emp from emp inner join dept join dept on emp.dept_no=dept.dept_no on emp.dept_no=dept.dept_no order by dept.dnombre by dept.dnombre

Devuelve todos los Empleados que tengan asociado un departamento.

select apellido,oficio,dnombre select apellido,oficio,dnombre from empleados from empleados full join full join departamentos departamentos on empleados.dep_no= on empleados.dep_no= departamentos.dep_no order by dep.dnombre by dep.dnombre

La combinación Full Join muestra las coincidencias de la tabla Departamentos con Empleados, más los valores que no coincidan, como el Empleado SERRA que no tiene departamento y el departamento EDICIÓN, que no tiene empleados. Se podría decir que es como la suma de utilizar left join y right join.

Consultas Externas Al igual que las consultas de combinación internas, combina los valores comunes de los campos indicados y además de la tabla que queramos, devuelve también el resto de valores aunque no coincidan. Para ello usaremos las siguientes opciones combinadas con join: Sintaxis: Select Select tablaprincipal.campo, tabla acom binar.campo From tablaprincipal MICROSOFT SQL Server Gota a Gota en las Bases de Datos Relacionales Lic. Elvin German

42

left join / right join / cross join tabla on condición o

left Join:


izquierda o

Right Join:


derecha

o

Muestra un producto cartesiano combinando todos los resultados de las dos tablas.

Cross Join:

select apellido,oficio,dnombre select apellido,oficio,dnombre from empleados from empleados left outer join departamentos join departamentos on empleados.dep_no= on empleados.dep_no= departamentos.dep_no order by order by departamentos.dnombre departamentos.dnombre

El empleado Serra tiene el nombre del departamento con el valor null porque no tiene ningún departamento asociado y nosotros en la consulta le estamos diciendo que seleccione los empleados aunque no tengan departamento asociado, ponemos como principal la tabla de la izquierda (EMPLEADOS).

select apellido,oficio,dnombre select apellido,oficio,dnombre from empleados from empleados right outer join departamentos join departamentos on empleados.dep_no= on empleados.dep_no= departamentos.dep_no order by departamentos.dnombre by departamentos.dnombre


43

En esta consulta el departamento de edición tiene valores null porque le hemos dicho que seleccione la tabla de la derecha como principal (departamentos), con lo cual selecciona todos los campos de la tabla departamentos con coincidencias con emp o sin ellas. select apellido,oficio,dnombre select apellido,oficio,dnombre from empleados from empleados cross join departamentos join departamentos Realiza un producto cartesiano combinando todos los empleados con todos los departamentos.


44

Combinaciones con más de dos tablas Ya hemos visto como combinar 2 tablas con inner join, el siguiente ejemplo muestra como combinar las 3 tablas que tenemos en la base de datos. Podremos combinar tantas tablas como queramos usando inner join o full join. select p.apellido as [Apellido] ,s.nombre as [Sala] ,h.nombre as [Hospital], s.num_cama as [Nº de camas] from plantilla p inner join sala as s on p.hospital_cod = s.hospital_cod and p.sala_cod = s.sala_cod inner join hospital_cod as h on h.hospital_cod = p.hospital_cod

Podremos usar tantos inner join como queramos en nuestras consultas, pero habrá que tener cuidado a la hora de realizar las combinaciones para que no salgan productos cartesianos en la consulta. Esta consulta devuelve el nombre del empleado, el nombre de la sala donde trabaja, el nombre del hospital y el número de camas.

COMBINAR LOS VALORES DE UNA TABLA SOBRE SÍ S Í MISMA Para ello crearemos dos copias de la misma tabla poniéndole un alías, para posteriormente combinar los resultados de ambas copias. Obtener la lista de los empleados con los nombres de sus directores. SELECT e1.emp_no "NºEmpleado", e1.apellido "Nombre Empleado", e1.director "NºDirector", e2.apellido "Nombre Director" FROM empleados e1,empleados e2 WHERE e1.director=e2.emp_no -------------------------------------------------------------------No. | Nombre |Director | Nombre -------------------------------------------------------------------MICROSOFT SQL Server Gota a Gota en las Bases de Datos Relacionales Lic. Elvin German

45

7499 | ALONSO | 7698 | GARRIDO 7521 | LOPEZ | 7782 | MARTINEZ 7654 | MARTIN | 7698 | GARRIDO 7698 | GARRIDO | 7839 | REY 7782 | MARTINEZ | 7839 | REY 7844 | CALVO | 7698 | GARRIDO 7876 | GIL | 7782 | MARTINEZ 7900 | JIMENEZ | 7782 | MARTINEZ


46

Resumen de cuándo utilizar cada operación. Para saber en cada caso qué tipo de operación se debe utilizar, a continuación tienes un gráfico que indica qué preguntas se tienen que hacer y según la respuesta, qué operación utilizar. Para resumir hemos llamado T1 y T2 las tablas de las que queremos sacar los datos y R la tabla lógica que representa el resultado de consulta. T1 y T2 podrían ser tablas guardadas o consultas. En la última parte cuando se pregunta "En T1 hay filas que no tienen pareja en T2", la pregunta se debe de interpretar como "en alguna de las tablas hay filas que no tienen pareja".


47

Las consultas de agrupación En SQL y en la mayoría de los motores de bases de datos relacionales, podemos definir un tipo de consultas cuyas consultas cuyas filas resultantes son un resumen de las filas de la tabla origen, origen , por agrupacion , también se conocen como consultas eso las denominamos consultas de agrupacion, sumarias. Es importante entender que las filas del resultado de una consulta de agrupacion tienen una naturaleza distinta distinta a las filas de las demás tablas resultantes de consultas, ya que corresponden a varias filas de la tabla origen. Para simplificar, veamos el caso de una consulta basada en una sola tabla, una fila de una consulta 'no resumen' corresponde a una fila de la tabla origen, contiene datos que se encuentran en una sola fila del origen, mientras que una fila de una consulta de agrupacion corresponde a corresponde a un resumen de varias filas de filas de la tabla origen, origen, esta diferencia es lo que va a originar una serie de restricciones que sufren las consultas de resumen y que veremos a lo largo del tema. En el ejemplo que viene a continuación tienes un ejemplo de consulta normal en la que se visualizan las filas de la tabla oficinas ordenadas por region, en este caso cada fila del resultado se corresponde con una sola fila de la tabla oficinas, mientras que la segunda consulta es una consulta resumen, cada fila del resultado se corresponde con una o varias filas de la tabla oficinas.

cláusulas a la sentencia SELECT, la Las consultas de agrupacion introducen dos nuevas cláusulas cláusula GROUP BY y BY y la cláusula HAVING, HAVING, son cláusulas que sólo se pueden utilizar en una entre la consulta de agrupacion, se tienen que escribir entre la cláusula WHERE cláusula WHERE y la cláusula cláusula ORDER BY y BY y tienen la siguiente sintaxis:


48

Las detallaremos en las página siguientes sig uientes del tema, primero vamos a introducir otro concepto relacionado con las consultas de agrupacion, l as funciones de columna.

Funciones de columna AVG(expr)

Valor medio de “ expresión r” ignorando los valores nulos

COUNT( { * | expresión } } )

Número de veces que “expresión ” tiene un valor no nulo. La opción “*”

cuenta el número de filas seleccionadas. MAX(expresión ) MIN(expresión r) r) STDDEV(expresión r) r) SUM(expresión ) VARIANCE(expresión r) r)

Valor máximo de “ expresión r” Valor mínimo de “expresión ” Desviación típica de “expr” sin tener en cuenta los valores nulos Suma de “expresión ” Varianza de “expresión ” sin tener en cuenta los valores nulos.

Ejemplo:

1. Obtener la masa salarial mensual de todos los empleados. SELECT SUM(salario) FROM empleados; +--------------+ | SUM(salario) | +--------------+ | 23100.62 | +--------------+


49

2. Obtener los salarios máximo, mínimo y la diferencia existente entre ambos. SELECT MAX(salario)"Salario mas alto" , MIN(salario) "Salario mas bajo", MAX(salario)- MIN(salario) "Diferencia" FROM empleados; -----------------------------------------------Salario mas alto | Salario mas bajo | Diferencia -----------------------------------------------6000.00 1350.50

4649.50

3. Calcular el salario medio de los empleados. SELECT AVG(salario) "Salario medio" FROM empleados; ----------------Salario medio ----------------2566.735569

5. Calcular el salario medio de los empleados que sean ANALISTAS SELECT AVG(salario) "Salario medio" FROM empleados WHERE oficio = 'ANALISTA'; ----------------Salario medio ----------------3350.000000 -----------------

La cláusula GROUP BY

Hasta ahora las consultas de resumen que hemos visto utilizan todas las filas de la tabla y producen una única fila resultado. subtotales con la cláusula GROUP BY. BY. Una consulta con una cláusula Se pueden obtener subtotales GROUP BY BY se denomina consulta agrupada agrupada ya que agrupa los datos de la tabla origen y produce una única fila resumen por cada grupo formado. formado . Las columnas indicadas en el GROUP BY se BY se llaman columnas de agrupación. agrupación.


50

Ejemplo:

1. Obtener los salarios medios por departamento. SELECT dep_no "Departamento" , AVG(salario) "Salario medio" FROM empleados GROUP BY dep_no; | | |

10 | 20 | 30 |

3266.833333 | 2375.000000 | 2137.530029 |

2. Obtener cuántos empleados hay en cada oficio SELECT oficio "Oficio", COUNT(*) "Nº de Empleados" FROM empleados GROUP BY oficio; +------------+-----------------+ | Oficio | Nº de Empleados | +------------+-----------------+ | ANALISTA | 1 | | DIRECTOR | 2 | | EMPLEADO | 2 | | PRESIDENTE | 1 | VENDEDOR | 3 | +------------+-----------------+

La cláusula HAVING 





La cláusula HAVING HAVING nos permite seleccionar filas filas de la tabla resultante de una consulta de resumen. resumen. Para la condición de selección se pueden utilizar los mismos tests de comparación comparación WHERE,, también se pueden escribir condiciones compuestas descritos en la cláusula WHERE OR,, AND AND,, NOT NOT), (unidas por los operadores OR ), pero existe una restricción. En la condición de selección sólo pueden aparece: aparece :valores constantes funciones de columna columnas de agrupación agrupación (columnas que aparecen en la cláusula GROUP BY) o cualquier expresión basada en las anteriores. anteriores .


51

Ejemplo:

1. Seleccionar los oficios que tengan dos o más empleados: SELECT oficio, COUNT(*) FROM empleados GROUP BY oficio HAVING COUNT(*)>= 2; ----------------------| oficio | COUNT(*) |----------|----------| | DIRECTOR | 2 | EMPLEADO | 2 | VENDEDOR | 3 -----------------------

2. Seleccionar los oficios que tengan dos o más empleados, cuyo salario supere los 1400 euros. SELECT oficio, COUNT(*) FROM empleados WHERE salario > 1400 GROUP BY oficio HAVING COUNT(*) >= 2; ----------------------| oficio | COUNT(*) ----------------------| DIRECTOR | 2 | VENDEDOR | 2 -----------------------


52

Resumen del tema

¿Cómo se ejecuta internamente una consulta de agrupacion? Primero se forma la tabla origen de datos según la cláusula FROM FROM,,  se seleccionan del origen de datos las filas según la cláusula WHERE WHERE,,  BY,  se forman los grupos de filas según la cláusula GROUP BY,  por cada grupo se obtiene una fila en la tabla resultante con los valores que aparecen en las cláusulas GROUP BY, HAVING y HAVING y en la lista de selección, se seleccionan de la tabla resultante las filas según la cláusula cláusula HAVING HAVING,,   se eliminan de la tabla resultante las columnas que no aparecen en la lista de selección,  se ordenan las filas de la tabla resultante según la cláusula ORDER BY Una consulta se convierte en consulta de agrupación en cuanto aparece GROUP BY, BY, HAVING o una función de columna. En una consulta de agrupación, la lista de selección y la cláusula HAVING HAVING sólo sólo pueden contener: o o

o

valores constantes funciones de columna columnas de agrupación (columnas que aparecen en la cláusula GROUP BY) BY)


53

Unidad 5. El DDL, lenguaje de definición de datos Introducción Hasta ahora hemos estudiado las sentencias que forman parte del DML (Data Management Language) lenguaje de manipulación de datos, todas esas sentencias sirven para recuperar, insertar, borrar, modificar los datos almacenados en la base de datos; lo que veremos en este tema son las sentencias que afectan a la estructura de los datos. El DDL DDL ( (D Data Definition Language) lenguaje de definición de datos es datos es la parte del SQL que más varía de un sistema a otro otro ya que esa area tiene que ver con cómo se organizan internamente los datos y eso, cada sistema lo hace de una manera u otra.

CREATE TABLE La sentencia CREATE TABLE sirve para crear la estructura de una tabla no para rellenarla con datos, nos permite definir las columnas columnas que tiene y ciertas restricciones restricciones que deben cumplir esas columnas. La sintaxis sintaxis es es la siguiente

bre de la tab tabla nbtabla: nom omb la que estamos definiendo bre de la col columna nbcol: nom omb umna que estamos definiendo tipo: tipo de dato de dato de la columna, todos los datos almacenados en la columna deberán ser de ese tipo.

TIPOS DE DATOS -

-

-

-

Numéricos:  Enteros  int, tinyint, smallint, bigint  Decimales  numeric, decimal, money, smallmoney Coma Flotante  float, real  Fechas:  datetime  0,333 s  smalldatetime  1 minuto Caracteres: Ancho fijo: char, nchar  Ancho Variable: varchar, nvarchar  Texto e Imagen:  Text  Ntext MICROSOFT SQL Server Gota a Gota en las Bases de Datos Relacionales Lic. Elvin German

54

Rowversion



-

Binario:

-

Binary, varbinary  Valores tipo byte  Bit  Un solo bit (1 o ninguno) Identificadores Unicos: 

Representa un identificador global único (GUID) Si queremos que no se repita el dato en la base de datos, usamos este identificador Uniqueidentifier



-

IDENTITY

Es un tipo de dato que genera valores y los incrementa. Un contador vamos. Solo una columna con Identity, el tipo de dato debe ser entero int, numerico numeric o decimal, estos dos últimos con escala cero. Debe ser obligatorio que sea NOT NULL. NULL. Inicial e incremento son opcionales y por defecto comenzarian en 1,1.


55

RESTRICCION restricción consiste en la definición de una característica adicional que tiene una Una restricción columna o una combinación de columnas, suelen ser características como valores no nulos (campo requerido), definición de índice sin duplicados, definición de clave principal y definición de clave foránea (clave ajena o externa, campo que sirve para relacionar dos tablas entre sí). restricción1: una restricción de tipo 1 es una restricción que aparece dentro de la definición de la columna después columna después del tipo de dato y afecta afecta a columna, la que se está definiendo. a una columna, restricción2: una restricción de tipo 2 es una restricción que se define define después de definir todas las columnas de la tabla y afecta afecta a una columna a una columna o a una combinación de columnas. Para escribir una sentencia CREATE TABLE se TABLE se empieza por indicar el nombre el nombre de la tabla que tabla que queremos crear y a continuación entre paréntesis paréntesis indicamos separadas por comas las definiciones de cada columna de columna de la tabla, la definición de una columna consta de su de su nombre, el tipo de dato que dato que tiene y podemos añadir si queremos una serie de especificaciones que especificaciones que deberán cumplir los datos almacenados en la columna, columna, después después de definir cada una de las columnas que compone la tabla se pueden añadir una serie de restricciones restricciones,, esas restricciones son las mismas que se pueden indicar para cada columna pero ahora pueden afectar a más de una columna por columna por eso tienen una sintaxis ligeramente diferente.

RESTRICCION TIPO 1 Una restricción de tipo 1 se 1 se utiliza para indicar una característica de la columna que estamos definiendo, tiene la siguiente sintaxis:

NULL indica que la columna no podrá contener un valor nulo, nulo , es decir que La cláusula NOT NULL indica se deberá rellenar obligatoriamente y con un valor válido (equivale a la propiedad requerido Sí de las propiedades del campo). La cláusula CONSTRAINT CONSTRAINT sirve para definir una restricción restricción que se podrá eliminar cuando queramos sin tener que borrar la columna. A cada restricción se le asigna un nombre que se utiliza para identificarla y para poder eliminarla cuando se quiera. Como restricciones tenemos la de clave primaria (clave principal), la de índice único (sin duplicados), la de valor no nulo, y la de clave foránea. tabla. La cláusula PRIMARY cláusula PRIMARY KEY se utiliza para definir la columna como clave principal de la tabla. Esto supone que la columna no puede contener valores nulos ni pueden haber valores


56

duplicados en esa columna, es decir que dos filas no pueden tener el mismo valor en esa duplicados columna. principales , por lo que no podemos incluir la En una tabla no pueden haber varias claves principales, KEY más de una vez, en caso contrario la sentencia da un error. No hay cláusula PRIMARY KEY más que confundir la definición de varias claves principales con la definición de una clave principal compuesta por varias columnas, esto último sí está permitido y se define con una restricción de tipo 2. UNIQUE sirve La cláusula UNIQUE sirve para definir un índice único sobre la columna. Un índice único es duplicados, es decir que si una columna tiene definida un un índice que no permite valores duplicados, UNIQUE no restricción de UNIQUE no podrán haber dos filas con el mismo valor en esa columna. Se suele emplear para que el sistema compruebe el mismo que no se añaden valores que ya existen, por ejemplo si en una tabla de clientes queremos asegurarnos que dos clientes no puedan tener el mismo D.N.I. y la tabla tiene como clave principal un código de cliente, definiremos la columna dni con la restricción de UNIQUE UNIQUE.. NULL indica que la columna no puede contener valores nulos, cuando La cláusula NOT NULL queremos indicar que una columna no puede contener el valor nulo lo podemos hacer sin CONSTRAINT,, o utilizando una cláusula CONSTRAINT CONSTRAINT.. poner la cláusula CONSTRAINT La última restricción que podemos definir sobre una columna es la de clave foránea, una clave foránea es una columna columna o conjunto de columnas que contiene un valor que hace referencia a una fila de otra tabla, tabla , en una restricción de tipo 1 se puede definir con la REFERENCES,, después de la palabra reservada indicamos a qué tabla hace cláusula REFERENCES referencia, opcionalmente podemos indicar entre paréntesis el nombre de la columna donde tiene que buscar el valor de referencia, por defecto coge la clave principal de la tabla2, si el valor que tiene que buscar se encuentra en otra columna de tabla2, entonces debemos inidicar el nombre de esta columna entre paréntesis, además sólo podemos utilizar una columna que UNIQUE,, si la columna2 que indicamos no está definida esté definida con una restricción de UNIQUE sin duplicados, la sentencia CREATE CREATE nos nos dará un error. Ejemplo: CREATE TABLE tab1 ( col1 INTEGER CONSTRAINT pk PRIMARY KEY, col2 CHAR(25) NOT NULL, col3 CHAR(10) CONSTRAINT uni1 UNIQUE, col4 INTEGER, col5 INT CONSTRAINT fk5 REFERENCES tab2 ); Con este ejemplo estamos creando la tabla tab1 compuesta por: una columna llamada col1 de tipo entero definida como clave principal, una columna col2 que puede almacenar hasta 25 caracteres alfanuméricos y no puede contener valores nulos, una columna col3 de hasta 10 caracteres que no podrá contener valores repetidos, una columna col4 de tipo entero sin ninguna restricción, y una columna col5 de tipo entero clave foránea que hace referencia a valores de la clave principal de la tabla tab2 .

Ejemplos de definición de restricciones de tipo 1 Veremos un ejemplo de cada caso donde se van definiendo las columnas con las correspondientes restricciones. a)PRIMARY a)PRIMARY KEY. El numero de socio en la tabla socios MICROSOFT SQL Server Gota a Gota en las Bases de Datos Relacionales Lic. Elvin German

57

CREATE TABLE socios_1a (socio_no INT PRIMARY KEY, apellidos VARCHAR(14), telefono CHAR(9), fecha_alta DATETIME, direccion VARCHAR(20), codigo_postal INT); b) NOT NULL. NULL. La columna teléfono es obligatoria en la tabla socios , nunca irá sin información. CREATE TABLE socios_1b (socio_no INT PRIMARY KEY, apellidos VARCHAR(14), telefono CHAR(9) NOT NULL, fecha_alta DATETIME, direccion VARCHAR(20), codigo_postal INT); c) DEAFAULT. En ausencia de valor el campo fec ha _ alta tomará el valor de 1 de Noviembre de 20008 CREATE TABLE socios_1c (socio_no INT PRIMARY KEY, apellidos VARCHAR(14), telefono CHAR(9) NOT NULL, fecha_alta DATETIME DEFAULT '2008-11-01', direccion VARCHAR(20), codigo_postal INT); d) UNIQUE. La columna apellido será única en la tabla socios. CREATE TABLE socios_1d (socio_no INT PRIMARY KEY, apellidos VARCHAR(14) UNIQUE, telefono CHAR(9) NOT NULL, fecha_alta DATETIME DEFAULT '2008-11-01', direccion VARCHAR(20), codigo_postal INT ); e)CHECK. e)CHECK. Se comprobará que la columna cdigo _postal corresponde a Madrid (valores entre 28000 y 28999) CREATE TABLE socios_1e (socio_no INT PRIMARY KEY, apellidos VARCHAR(14) UNIQUE, telefono CHAR(9) NOT NULL, fecha_alta DATETIME DEFAULT '2008-11-01', direccion VARCHAR(20), codigo_postal INT CHECK (codigo_postal BETWEEN 28000 AND 28999));

RESTRICCION TIPO 2 MICROSOFT SQL Server Gota a Gota en las Bases de Datos Relacionales Lic. Elvin German

58

Una restricción de tipo 2 2 se utiliza para definir una característica que afecta a una columna o a una combinación de columnas de columnas de la tabla que estamos definiendo, se escribe después de haber definido todas las columnas de columnas de la tabla. sintaxis:: sintaxis La sintaxis de una restricción de tipo 2 es muy similar a la CONSTRAINT CONSTRAINT de de una restricción 1 la diferencia es que ahora tenemos que indicar sobre qué columnas queremos definir la restricción. Se utilizan obligatoriamente las restricciones de tipo 2 cuando la restricción afecta a CONSTRAINT para una un grupo de columnas o cuando queremos definir más de una CONSTRAINT columna (sólo se puede definir una restricción1 en cada columna). La cláusula PRIMARY KEY se KEY se utiliza para definir la clave principal de la tabla. Después de las palabras PRIMARY KEY se KEY se indica entre paréntesis el nombre de la columna o las columnas que forman la clave principal. Las columnas que forman la clave principal no pueden contener valores nulos ni pueden haber valores duplicados de la combinación de columnas, por ejemplo la tabla pedidos de nuestros ejemplos tiene una clave principal formada por idfab e idproducto, pues no pueden haber dos filas con la misma combinación de idfab con idproducto ( aci,0001 por ejemplo) pero sí pueden haber dos filas con el valor aci en la columna idfab si tienen valores diferentes en la columna idproducto, y pueden haber dos filas con el mismo idproducto pero distinto idfab. En una tabla no pueden haber varias claves principales, principales , por lo que no podemos indicar la KEY más de una vez, en caso contrario la sentencia da un error. cláusula PRIMARY KEY más La cláusula UNIQUE UNIQUE sirve para definir un índice único único sobre una columna o sobre una combinación de columnas. Un índice único es un índice que no permite valores duplicados. duplicados. Si el índice es sobre varias columnas no se puede repetir la misma combinación de valores en dos o más filas. Se suele emplear para que el sistema

La sintaxis de una restricción de tipo 2 es muy similar a la CONSTRAINT CONSTRAINT de de una restricción 1 la diferencia es que ahora tenemos que indicar sobre qué columnas queremos definir la restricción. Se utilizan obligatoriamente las restricciones de tipo 2 cuando la restricción afecta a un grupo de columnas o cuando queremos definir más de una CONSTRAINT CONSTRAINT para para una columna (sólo se puede definir una restricción1 en cada columna). La cláusula PRIMARY KEY se KEY se utiliza para definir la clave principal de la tabla. Después de las palabras PRIMARY KEY se KEY se indica entre paréntesis el nombre de la columna o las columnas 59 MICROSOFT SQL Server Gota a Gota en las Bases de Datos Relacionales Lic. Elvin German

que forman la clave principal. Las columnas que forman la clave principal no pueden contener valores nulos ni pueden haber valores duplicados de la combinación de columnas, por ejemplo la tabla pedidos de nuestros ejemplos tiene una clave principal formada por idfab e idproducto, pues no pueden haber dos filas con la misma combinación de idfab con idproducto ( aci,0001 por ejemplo) pero sí pueden haber dos filas con el valor aci en la columna idfab si tienen valores diferentes en la columna idproducto, y pueden haber dos filas con el mismo idproducto pero distinto idfab. En una tabla no pueden haber varias claves principales, principales , por lo que no podemos indicar la cláusula PRIMARY KEY más KEY más de una vez, en caso contrario la sentencia da un error. UNIQUE sirve para definir un índice único único sobre una columna o sobre una La cláusula UNIQUE duplicados. combinación de columnas. Un índice único es un índice que no permite valores duplicados. Si el índice es sobre varias columnas no se puede repetir la misma combinación de valores en dos o más filas. Se suele emplear para que el sistema compruebe el mismo que no se añaden valores que ya existen. KEY sirve para definir una clave foránea foránea sobre una columna o una La cláusula FOREIGN KEY combinación de columnas. Una clave foránea es una columna o conjunto de columnas que contiene un valor que hace referencia a una fila de otra tabla, tabla , en una restricción 1 se REFERENCES.. Para definir una clave foránea en una restricción puede definir con la cláusula REFERENCES KEY después indicamos entre de tipo 2 debemos empezar por las palabras FOREIGN KEY paréntesis la/s columna/s que es clave foránea, a continuación la palabra reservada REFERENCES seguida del nombre de la tabla a la que hace referencia, opcionalmente REFERENCES podemos indicar entre paréntesis el nombre de la/s columna/s donde tiene que buscar el valor de referencia, por defecto coge la clave principal de la tabla2, si el valor que tiene que buscar se encuentra en otra/s columna/s de tabla2, entonces debemos escribir el nombre de esta/s columna/s entre paréntesis, además sólo podemos utilizar una columna (o combinación de columnas) que esté definida con una restricción de UNIQUE UNIQUE,, de lo contrario la sentencia CREATE TABLE nos TABLE nos dará un error. Ejemplo: CREATE TABLE tab1 (col1 INTEGER, col2 CHAR(25) NOT NULL, col3 CHAR(10), col4 INTEGER, col5 INT, CONSTRAINT pk PRIMARY KEY (col1), CONSTRAINT uni1 UNIQUE (col3), CONSTRAINT fk5 FOREIGN KEY (col5) REFERENCES tab2 ); Con este ejemplo estamos creando la misma tabla tab1 del ejemplo de la página anterior pero ahora hemos definido las restricciones utilizando restricciones de tipo 2.


60

Ejemplos de definición de restricciones de tipo 2 a)PRIMARY a)PRIMARY KEY. El numero de socio será la clave primaria en la tabla socios. Será obligatoriamente no nulo y único. CREATE TABLE socios_2a (socio_no INT, apellidos VARCHAR(14), telefono CHAR(9), fecha_alta DATETIME, direccion VARCHAR(20), codigo_postal INT, CONSTRAINT PK_ socios_2a PRIMARY KEY (socio_no)); b) UNIQUE. El campo apellido es único. Tendrá valores diferentes en cada fila o el valor nulo CREATE TABLE socios_2b (socio_no INT, apellidos VARCHAR(14), telefono CHAR(9), fecha_alta DATE, direccion VARCHAR(20), codigo_postal INT(5), CONSTRAINT PK_SOCIOS PRIMARY KEY(socio_no), CONSTRAINT UQ_UNIQUE UNIQUE (apellidos)); c)CHECK. c)CHECK. La columna codigo_postal no admitirá como válidas aquellas filas en las que el código postal no tenga valores entre 28.000 y 28.999 ( correspondientes a Madrid) CREATE TABLE socios_2c (socio_no INT, apellidos VARCHAR(14), telefono CHAR(9), fecha_alta DATETIME, direccion VARCHAR(20), codigo_postal INT, CONSTRAINT PK_DEPARTAMENTOS PK_DEPARTAMENTOS PRIMARY KEY(socio_no), CONSTRAINT UQ_UNIQUE UNIQUE(apellidos), CONSTRAINT CK_CODIGO CHECK (codigo_postal BETWEEN 28000 AND 28999) );

ALTER TABLE TABLE sirve para modificar la estructura de una tabla que tabla que ya existe. La sentencia ALTER TABLE sirve Mediante esta instrucción podemos añadir columnas nuevas, eliminar columnas. Ten cuenta que cuando eliminamos una columna se pierden todos los datos almacenados en ella. También nos permite crear nuevas restricciones o borrar algunas existentes. La sintaxis puede parecer algo complicada pero sabiendo el significado de las palabras reservadas la


61

sentencia se aclara bastante; ADD (añade), ALTER (modifica), DROP (elimina), COLUMN (columna), CONSTRAINT (restricción). sintaxis es La sintaxis es la siguiente:

La sintaxis sintaxis de de restriccion1 restriccion1 es es idéntica a la restricción1 de la sentencia CREATE TABLE, TABLE, te la TABLE. describimos a continuación, si tienes alguna duda repasa la sentencia CREATE TABLE.

La sintaxis sintaxis de de restriccion2 restriccion2 es es idéntica a la restricción2 de la sentencia CREATE TABLE, TABLE, te la describimos a continuación, si tienes alguna duda repasa la sentencia CREATE TABLE

ADD permite nueva a la tabla. Como en la creación de tabla, La cláusula ADD permite añadir una columna nueva a hay que definir la columna indicando su nombre, tipo de datos que puede contener, y si lo queremos alguna restricción de valor no nulo, clave primaria, clave foránea, e índice único,


62

restriccion1 es opcional opcional e indica una restricción de tipo 1 que afecta a la columna que estamos definiendo. Ejemplo: ALTER TABLE tab1 ADD col3 int NOT NULL CONSTRAINT c1 UNIQUE Con este ejemplo estamos añadiendo a la tabla tab1 una columna llamada col3 de tipo entero, requerida (no admite nulos) y con un índice sin duplicados llamado c1. Cuando añadimos una columna lo mínimo que se puede poner sería: ALTER TABLE tab1 ADD col3 int En este caso la nueva columna admite valores nulos y duplicados. Para añadir una nueva restricción en restricción en la tabla podemos utilizar la cláusula ADD restriccion2 CONSTRAINT...). (ADD CONSTRAINT...). Ejemplo: ALTER TABLE tab1 ADD CONSTRAINT c1 UNIQUE (col3) Con este ejemplo estamos añadiendo a la tabla tab1 un índice único (sin duplicados) llamado c1 sobre la columna col3. Para borrar una columna columna basta con utilizar la cláusula DROP COLUMN (COLUMN es opcional) y el nombre de la columna que queremos borrar, se perderán todos los datos almacenados en la columna Ejemplo: ALTER TABLE tab1 DROP COLUMN col3 Para borrar una restricción basta restricción basta con utilizar la cláusula DROP CONSTRAINT y CONSTRAINT y el nombre de la restricción que queremos borrar, en este caso sólo se elimina la definición de la restricción pero los datos almacenados no se modifican ni se pierden. Ejemplo: ALTER TABLE tab1 DROP CONSTRAINT c1 Con esta sentencia borramos el índice c1 creado anteriormente pero los datos de la columna col3 no se ven afectados por el cambio.

DROP TABLE TABLE sirve para eliminar una tabla. tabla. No se puede eliminar una tabla si La sentencia DROP TABLE sirve está abierta, tampoco la podemos eliminar si el borrado infringe las reglas de integridad referencial (si interviene como tabla padre en una relación y tiene registros relacionados). sintaxis es La sintaxis es la siguiente:


63

Ejemplo:: Ejemplo DROP TABLE tab1 Elimina de la base de datos la tabla tab1.

CREATE INDEX SQL Server - Definición de los índices Se hara definición de índices en SQL Server - agrupados y no agrupados indexex, el optimizador de consultas, para crear un índice, para crear un índice único, para crear un índice agrupado, para crear índices de texto completo, para cambiar propiedades índice, Para cambiar el nombre de un índice, para eliminar un índice Cuando aumentan los volúmenes de datos, las organizaciones se enfrentan a problemas relacionados con la recuperación de datos y publicación. Sienten la necesidad de un mecanismo que aumentará la velocidad de acceso a datos. Un índice, al igual que el índice de un libro, permite recuperar la base de datos y presentar los datos al usuario final con facilidad. Un índice puede ser definido como un mecanismo para proporcionar un acceso rápido a las filas de tabla y de hacer cumplir las restricciones. Un índice puede ser creado mediante la selección de una o más columnas de una tabla que está siendo buscado. Es una especie de "en el disco 'estructura asociada a la tabla o vista y contiene las claves que se construyen a partir de una o varias de las columnas de la la tabla o vista. vista. Esta estructura conocida como como B-Tree ayuda a que el SQL Server busque la fila o filas asociadas a los valores de clave. Los índices se pueden crear en columnas calculadas o columnas xml también.

Los índices pueden ser agrupados en clúster o no. Un índice agrupado almacena los datos de las filas de la tabla en función de sus valores fundamentales. Cada tabla sólo puede tener un índice agrupado como los valores clave en las filas de datos son únicos y el índice se basa en la columna de clave única. Cuando una tabla tiene un índice agrupado, se conoce como una tabla de clúster. índices no agrupados tienen tienen estructuras que son diferentes de las filas de datos. Un índice no agrupado valor de clave se usa para apuntar a las filas filas de datos que contienen el valor clave. Este valor se conoce como localizador de fila. La estructura del localizador de fila está determinado sobre la la base del tipo de almacenamiento de las páginas de datos. Si la página de datos se almacena como un montón, un localizador de fila se convierte en un puntero MICROSOFT SQL Server Gota a Gota en las Bases de Datos Relacionales Lic. Elvin German

64

a una fila. Si la página de datos se almacena almacena en una tabla agrupada el localizador de fila fila es una clave del índice agrupado. índices agrupados y no agrupados pueden ser únicos y los índices se mantienen automáticamente para una tabla o vista cada vez que la tabla de datos se ha modificado.

SQL Server permite a los usuarios agregar columnas sin clave al nivel hoja del índice no agrupado en el índice existente que pasa los límites de claves y para ejecutar plenamente cubiertos consultas índice. Cuando las restricciones de clave principal y única de una columna de la tabla se definen un índice automática se crea.

El optimizador de consultas utiliza los índices para reducir disco I / O las operaciones y el uso de los recursos del sistema, mientras que en la consulta de datos. Las consultas que contienen instrucciones SELECT, UPDATE o DELETE requieren índices para un rendimiento óptimo. Cuando se ejecuta una consulta, cada método disponible se evalúa para la recuperación de datos y la más eficiente es seleccionado por el optimizador optimizador de consultas. La metodología metodología utilizada puede ser recorridos de tablas o exploraciones de índices. En la tabla I lecturas / operaciones de E son muchos y muchos recursos en todas las filas de una tabla se analizan para encontrar las relevantes. exploraciones de índices se se utilizan para buscar en el índice de columnas de clave para encontrar la ubicación de almacenamiento de filas que necesita la consulta y como el índice contiene muy pocas columnas, la consulta se ejecuta más rápido. SQL Server proporciona al usuario una nueva Transact-SQL DDL para la modificación de los índices relacionales y XML. XML. La instrucción CREATE CREATE INDEX INDEX se ha mejorado para admitir la sintaxis XML índice, la separación y las columnas incluidas. Un número de opciones sobre índices se han añadido nuevas incluyendo la opción en línea que permite el acceso de usuarios concurrentes a los datos subyacentes durante las operaciones de índice.

Para crear un índice 1. En Explorador de objetos, haga clic en la tabla para el que desea crear un índice y haga clic en Modificar.


65

2. La tabla se abre en el Diseñador de tablas.


66

3. En el menú Diseñador de tablas, haga clic en Índices / Claves.


67

4. En los Índices / Claves cuadro de diálogo, haga clic en Agregar.

5. Seleccione el nuevo índice en la Primaria Clave única seleccionada / o la lista de índice y establecer propiedades para el índice en la red a la derecha.


68

6. Especifique cualquier otra configuración para el índice y haga clic en Cerrar.

7. El índice se crea en la base de datos al guardar la tabla.

SQL Server permite a los usuarios crear índices únicos en columnas únicas como el número de identificación del empleado o estudiante o lo que es la clave única por la que que el componente de datos se identifican. Un conjunto de columnas también se pueden utilizar para crear un índice único. El DBA puede establecer la opción de ignorar duplicados de las llaves en un índice único si es necesario. El valor predeterminado es No.

Para crear un índice único 1. 2. 3. 4.

En Explorador de objetos, haga objetos, haga clic en la tabla y haga clic en Modificar. La tabla se abre en el Diseñador de tablas. En el menú Diseñador de tablas, haga tablas, haga clic en Índices / Claves. Haga clic en Agregar. La seleccionada Principal / Clave única o lista o lista muestra el índice asignado por el sistema de nombres del n uevo índice.


69

5. En la cuadrícula, haga clic en Tipo.


70

6. Elija Índice de la lista desplegable hacia abajo a la derecha de la propiedad.

7. En Nombre de columna, seleccione las columnas que desea indexar. Puede seleccionar hasta 16 columnas. Para un rendimiento óptimo, seleccione sólo una o dos columnas por índice. Para cada columna que seleccione, indicar si el índice organiza valores de esta columna en orden ascendente o descendente.


71

8. En la cuadrícula, haga clic en es único.


72

9. Elija Sí en la lista desplegable hacia abajo a la derecha de la propiedad.

10. Seleccione la opción Ignorar duplicados llaves si desea ignorar nuevo o actualizado los datos que permitiera crear un duplicado de la llave en el índice (con la instrucción INSERT o UPDATE).


73

11. El índice se crea en la base de datos al guardar la tabla o diagrama.


74

Tenga en cuenta que los índices únicos no se pueden crear en una sola columna si la columna contiene contiene NULL en más de una fila. Del mismo modo los los índices no se pueden crear en varias columnas si la combinación de las columnas contiene valores NULL en algunas filas. Los valores NULL se tratan como valores duplicados. índices agrupados se pueden crear en bases de datos datos SQL Server. En tales casos, el orden lógico del índice de valores de las claves será el mismo que el orden físico de las filas de la tabla. Una tabla sólo puede tener un índice agrupado.

Para crear un índice agrupado 1. En Explorador de objetos, haga clic en la tabla para el que desea crear un índice agrupado y haga clic en Modificar. 2. La tabla se abre en el Diseñador de tablas. 3. En el menú Diseñador de tablas, haga clic en Índices / Claves. 4. En los Índices / Claves cuadro de diálogo, haga clic en Agregar. 5. Seleccione el nuevo índice en la Selección de Primaria Clave única / o en lista la de índice. MICROSOFT SQL Server Gota a Gota en las Bases de Datos Relacionales Lic. Elvin German

75

6. En la cuadrícula, seleccione Crear como agrupado, y elija Sí en la lista desplegable hacia abajo a la derecha de la propiedad.

7. El índice se crea en la base de datos al guardar la tabla. Un índice de texto completo se usa cuando una búsqueda de texto completo es requerido para llevar a cabo en todo el texto basado en columnas de la base de datos. Este índice se basa en un índice regular regular que tiene que crearse antes de un índice de texto completo es creado. El índice regulares se crea en una, no solo de columna nulos. Por lo general, una columna con valores pequeños se selecciona para la indexación en un índice normal. A menudo, un catálogo se crea utilizando una herramienta externa, como SQL Server Management Studio. Los datos textuales de diferentes formatos de archivos de texto se almacenan como archivos de tipo imagen antes de la búsqueda de texto completo se puede hacer en los datos.

Para crear índices de texto completo c ompleto MICROSOFT SQL Server Gota a Gota en las Bases de Datos Relacionales Lic. Elvin German

76

1. 2. 3.

objetos, haga clic en la tabla p ara el que desea crear un índice de texto En Explorador de objetos, haga completo y haga clic en Modificar. La tabla se abre en el Diseñador de tablas. En el menú Diseñador de tablas, haga tablas, haga clic en Índice Texto completo.

4. El índice de texto completo cuadro de diálogo diálogo se abre. Si la base de datos no está habilitada para la indexación de texto completo el cuadro de diálogo tendrá en el botón añadir discapacitados. Para habilitar la indexación de texto texto completo de la base de datos, haga clic derecho la base de datos> Haga clic en Propiedades y compruebe el texto completo de indexación casilla de verificación.


77

5. A continuación, cree un catálogo con el botón derecho sobre almacenamiento Texto completo Catálogo> y la creación de un nuevo catálogo y de introducir la información requerida en el cuadro de diálogo que se abre.


78


79

6. Ahora abra la propiedad Index Texto completo cuadro de diálogo haciendo clic sobre ella en el menú Diseñador de tablas.

7. Haga clic en Agregar. 8. Seleccione el nuevo índice en el índice de texto Lista completa seleccionada y establecer propiedades para el índice en la red a la derecha 9. Su índice se guardan automáticamente en la base de de datos al guardar la tabla en el Diseñador de tablas. El índice está disponible para su modificación en cuanto se lo crea.

Para cambiar las propiedades del índice 1. En Explorador de objetos, haga clic en la tabla que desea abrir y haga clic en Modificar. 2. En el menú Diseñador de tablas, haga clic en Índices / Claves. 3. Seleccione el índice de la Selección de Primaria Clave única / o en la lista de índice. 4. Cambiar las propiedades de la cuadrícula. 5. Los cambios se guardan en la base de datos al guardar la tabla.


80

Sistema de nombres definidos se asignan a los índices basados en el nombre del archivo de base de datos. datos. Si se crean varios índices índices en una tabla tabla los nombres de índice se incrementan numéricamente con _1, _2, etc Un índice puede ser renombrado a ser único a una tabla. Dado que el índice creado automáticamente lleva el mismo nombre como la clave única o principal limitación de una tabla, otro índice no se puede cambiar más tarde para coincidir con la clave única o principal limitación.

Para cambiar el nombre de un índice 1. En Explorador de objetos, haga clic en la tabla con el índice que desea cambiar de nombre y haga clic en Modificar. 2. En el menú Diseñador de tablas, haga clic en Índices / Claves. 3. Seleccione el índice de la Selección la cuadrícula, haga clic de Primaria Clave única / o en la lista de índice. 4. En la en Nombre y escriba un nuevo nombre en el cuadro de texto.


81

5. Los cambios se guardan en la base de datos al guardar la tabla. Los índices se pueden eliminar. Por lo general, un índice es considerado para su eliminación cuando el cumplimiento de las instrucciones INSERT, UPDATE y DELETE operaciones se ven dificultadas por el Índice.

Para eliminar un índice 1. En Explorador de objetos, haga clic en la tabla con los índices que desea eliminar y haga clic en Modificar. 2. En el menú Diseñador de tablas, haga clic en Índices / Claves. 3. En los Índices / Claves cuadro de diálogo, seleccionar el índice que desea eliminar 4. Haga clic en Eliminar. 5. El índice se elimina elimina de la base de datos cuando la la tabla se guarda. guarda. 6. Un procedimiento similar se puede seguir para eliminar un índice índice de texto completo mediante la selección de índice de texto completo desde el Diseñador de tablas y seleccionando el nombre del índice y haciendo clic en el botón eliminar. Microsoft SQL Server utiliza base de datos de un factor de relleno para especificar cómo se se llena cada página de índice puede ser. El porcentaje de espacio libre concedido a un índice se define como el factor de relleno. Este es un aspecto importante de la indexación como la cantidad de espacio a ser ocupado por un índice debe ser determinado por el DBA para que el rendimiento no tiene retardo mental.


82

Unidad 6. Actualización de datos Introducción Hasta ahora hemos estudiado el cómo recuperar datos almacenados en las tablas de nuestra base de datos. En este tema vamos a tratar el de la actualización actualización de esos datos, es decir insertar nuevas filas, borrar filas o cambiar el contenido de las filas de una tabla. Estas estructura , ni su operaciones modifican los datos almacenados en las tablas pero no su estructura, definición. Empezaremos por ver cómo insertar nuevas filas (con la sentencia INSERT INTO), INTO), veremos una variante (la sentencia SELECT... INTO), INTO), después veremos cómo borrar filas filas de una tabla (con la sentencia DELETE DELETE)) y por último cómo modificar el el contenido de las filas de una tabla (con la sentencia UPDATE UPDATE). ). Si trabajamos en un entorno multiusuario, multiusuario, todas estas operaciones se podrán realizar siempre que tengamos los permisos correspondientes. correspondientes .

Insertar una fila INSERT INTO...VALUES tabla , la La inserción de nuevos datos en una tabla se realiza añadiendo realiza añadiendo filas enteras a la tabla, sentencia SQL que lo permite es la orden INSERT INTO. INTO. La inserción se puede realizar de una fila o de varias filas de golpe, veremos las dos opciones por separado y empezaremos por la inserción de una fila. La sintaxis es la siguiente

Esta sintaxis se utiliza para insertar una sola fila cuyos valores indicamos después de la VALUES.. En castellano la sentencia se leería: INSERTA EN palabra reservada VALUES destino...VALORES .... 









Los registros se agregan siempre agregan siempre al final de la tabla. tabla. Destino es el nombre de la tabla donde vamos a insertar la fila Destino fila también se puede utilizar un nombre de consulta, consulta, consulta que tenga como origen de datos única tabla. tabla. Al nombre de la tabla se le puede añadir la cláusula IN IN si una única una si la tabla se encuentra en otra base de datos (en una base de datos externa). VALUES se SELECT ( La palabra reservada VALUES se puede sustituir por la palabra SELECT ( en otros VALUES). SQLs se emplea únicamente VALUES ). A continuación de la palabra VALUES VALUES,, entre paréntesis se escriben los valores que queremos añadir . Estos valores se tienen que escribir de acuerdo al tipo de dato de la columna donde columna donde se van a insertar (encerrados entre comillas simples ' ' para valores de tipo texto, entre # # para valores de fecha...) la asignación de valores se realiza por posición, posición , el primer valor lo asigna a la primera columna, el segundo valor a la segunda columna, así sucesivamente. Cuando la tabla tiene una columna de tipo contador (AutoNumber), (AutoNumber), lo normal es no asignar valor a esa columna para que el sistema le asigne el valor que le toque MICROSOFT SQL Server Gota a Gota en las Bases de Datos Relacionales Lic. Elvin German

83

según el contador, si por el contrario queremos que la columna tenga un valor concreto, lo indicamos en la lista de valores. columnas después del destino, se asume por Cuando no se indica ninguna lista de columnas después tabla, en este caso, los valores se tienen que defecto todas las columnas de la tabla, orden en que aparecen las columnas en la ventana de especificar en el mismo orden diseño de dicha tabla, tabla , y se tiene que utilizar el valor NULL NULL para rellenar las columnas de las cuales no tenemos valores.



Ejemplo: INSERT INTO empleados VALUES (200, 'Juan López', 30, NULL, 'rep ventas', ‘06/23/01’,

NULL, 350000, 0) Observar en el ejemplo que los valores de tipo texto se encierran entre comillas simples ' ' y que la fecha de contrato contrato se encierra entre comillas simples con el formato dia/mes/año. Dependiendo del Regional que tenga su computador. Como no tenemos valor para los campos oficina y director (a este nuevo empleado todavía no se le ha asignado director ni oficina) utilizamos la palabra reservada NULL NULL.. Los valores numéricos se escriben tal cual, para separar la parte entera de la parte decimal hay que utilizar siempre el punto independientemente de la configuración que tengamos. ERRORES QUE SE PUEDEN PRODUCIR CUANDO SE EJECUTA LA SENTENCIA INSERT INTO 





Si la tabla de destino tiene clave principal principal y en ese campo intentamos no asignar valor, asignar el valor nulo o un valor que ya existe en la tabla, el motor de base de datos no añade la fila y da un mensaje de error de ' infracciones de clave'. Si tenemos definido tenemos definido un índice único (sin único (sin duplicados) e intentamos asignar un valor que ya existe en la tabla también devuelve el mismo error. Si la tabla está relacionada con otra, otra , se seguirán las reglas de integridad referencial.. referencial

Insertar varias filas INSERT INTO...SELECT filas con una sola sentencia SELECT INTO INTO si los Podemos insertar en una tabla varias filas valores a insertar se pueden obtener como resultado de una consulta, en este caso sustituimos la cláusula VALUES lista de valores por por una sentencia SELECT SELECT como como las que hemos visto hasta ahora. Cada ahora. Cada fila resultado de resultado de la SELECT forma una lista de valores que valores que son los que se insertan en una nueva fila de la tabla destino. Es como si tuviesemos una INSERT...VALUES por INSERT...VALUES por cada fila resultado de la sentencia SELECT SELECT.. La sintaxis es la siguiente



El origen origen de la SELECT SELECT puede ser el nombre de una consulta guardada, un nombre de tabla o una composición de varias tablas ( tablas ( mediante INNER JOIN, LEFT JOIN, RIGHT JOIN o producto cartesiano). MICROSOFT SQL Server Gota a Gota en las Bases de Datos Relacionales Lic. Elvin German

84















Cada fila devuelta por devuelta por la SELECT actúa como la lista de valores que valores que vimos con INSERT...VALUES por restricciones en cuanto a tipo la INSERT...VALUES por lo que tiene las mismas restricciones en posición por lo que la de dato, etc. La asignación de valores se realiza por posición SELECT debe devolver el mismo número de columnas SELECT columnas que las de la tabla destino y en el mismo orden, o el mismo número de columnas que indicamos en la lista de columnas después de destino. Las columnas de la SELECT no tienen porque llamarse igual que en la tabla destino ya que el sistema sólo se fija en los valores devueltos por la SELECT SELECT.. Si no queremos asignar valores a todas las columnas entonces tenemos que columnas a rellenar después del nombre del indicar entre paréntesis la lista de columnas a destino. consulta que El estándar ANSI/ISO especifica varias restricciones sobre la consulta aparece dentro de la sentencia INSERT INSERT:: la consulta no puede tener una cláusula ORDER BY, BY, INSERT no la tabla destino de la sentencia INSERT no puede aparecer en la cláusula FROM de la consulta o de ninguna subconsulta que ésta tenga. Esto prohibe insertar parte de una tabla en sí misma, la consulta no puede ser la UNION UNION de varias sentencias SELECT SELECT diferentes, el resultado de la consulta debe contener el mismo número de columnas que las indicadas para insertar y los tipos de datos deben ser compatibles columna a columna.

Ejemplo: Supongamos que tenemos una tabla llamada repres con la misma estructura que la tabla empleados, y queremos insertar en esa tabla los empleados que tengan como titulo rep ventas

INSERT INTO repres SELECT * FROM empleados WHERE titulo = 'rep ventas' Con la SELECT SELECT obtenemos obtenemos las filas correspondientes a los empleados con título rep ventas,y las insertamos en la tabla repres. Como las tablas tienen la misma estructura no hace falta SELECT.. poner la lista de columnas y podemos emplear * en la lista de selección de la SELECT Ejemplo: Supongamos ahora que la tabla repres tuviese las siguientes columnas numemp, oficinarep, nombrerep. En este caso no podríamos utilizar el asterisco, tendríamos que poner: INSERT INTO repres SELECT numemp, oficina, nombre FROM empleados WHERE titulo = 'rep ventas' O bien: INSERT INTO repres (numemp, oficinarep, nombrerep) SELECT numemp, oficina, nombre FROM empleados WHERE titulo = 'rep ventas'

Insertar filas en una nueva tabla SELECT ... INTO creando en ese momento la tabla donde se insertan las insertan las filas. Se Esta sentencia inserta sentencia inserta filas creando en SELECT . suele utilizar para guardar en una tabla el resultado de una SELECT. 85 MICROSOFT SQL Server Gota a Gota en las Bases de Datos Relacionales Lic. Elvin German

La sintaxis sintaxis es es la siguiente:

Las columnas de la nueva tabla tendrán tabla tendrán el mismo tipo y tamaño que las columnas origen, origen , y se llamarán con el nombre de alias de alias de la columna origen o en su defecto con el nombre de la columna origen, origen, pero no se transfiere ninguna otra propiedad del campo o de la tabla como por ejemplo las claves e índices. La sentencia SELECT SELECT puede ser cualquier sentencia SELECT sin ninguna restricción, restricción , UNION ... puede ser una consulta multitabla, una consulta d e resumen, una UNION ... Ejemplo: SELECT * INTO t2 FROM t1 Esta sentencia genera una nueva tabla t2 con todas las filas de la tabla t1. Las columnas se llamarán igual que en t1 pero t2 no será una copia exacta de t1 ya no tendrá clave principal ni relaciones con las otras tablas, ni índices si los tuviese t1 etc... 







Si en la base de datos hay ya una tabla del mismo nombre , el sistema nos avisa y avisa y nos pregunta si la queremos borrar. Si le contestamos que no, la SELECT no se ejecuta. Para formar una sentencia SELECT INTO lo INTO lo mejor es escribir la SELECT SELECT que que permite generar los datos que queremos guardar en la nueva tabla, y después añadir delante de la cláusula FROM FROM la la cláusula INTO cláusula INTO nuevatabla nuevatabla.. La sentencia SELECT INTO se suele utilizar para crear tablas de trabajo, trabajo , o tablas intermedias, las creamos para una determinada tarea y cuando hemos terminado esa tarea las borramos. También puede ser útil para sacar datos en una tabla para enviarlos a alguien. alguien . Por ejemplo: Queremos enviarle a un representante una tabla con todos los datos personales de sus clientes para que les pueda enviar cartas etc...

Ejemplo: SELECT numclie AS codigo, nombre, direccion, telefono INTO susclientes FROM clientes WHERE repclie = '103'; '103';

Modificar el contenido de las filas ( UPDATE ) La sentencia UPDATE modifica los valores de una o más columnas en las filas seleccionadas de una o varias tablas. tablas . La sintaxis es la siguiente:


86









Origen puede ser un nombre de tabla, un nombre de consulta o una Origen composición de tablas, tablas, también puede incluir la cláusula IN IN si si la tabla a modificar se encuentra en una base de datos externa La cláusula SET SET especifica qué columnas van a modificarse y qué valores asignar a esas columnas. columnas . nbcolumna, es el nombre de la columna a la cual queremos asignar un nuevo nbcolumna, es valor por por lo tanto debe ser una columna de la tabla origen. El SQL estándar exige nombres sin cualificar pero algunas implementaciones (como por ejemplo el SQL de Microsoft Jet que estamos estudiando) sí lo perm iten. La expresión expresión en cada asignación debe generar un valor del tipo de dato apropiado para apropiado para la columna indicada. La expresión debe ser calculable a partir de los valores de la fila que se está actualizando. actualizando . Expresión no puede ser una subconsulta.. subconsulta

Ejemplo: UPDATE oficinas INNER JOIN empleados ON oficinas.oficina = empleados.oficina SET cuota=objetivo*0.01; En este ejemplo queremos actualizar las cuotas de nuestros empleados de tal forma que la cuota de un empleado sea el 1% del objetivo de su oficina. La columna a actualizar es la cuota del empleado y el valor a asignar es el 1% del objetivo de la oficina del empleado, luego la SET será SET cuota = objetivo*0.01 o SET cuota = objetivo/100. objetivo/100. El origen debe cláusula SET contener la cuota del empleado y el objetivo de su oficina, luego el origen será el INNER JOIN de empleados con oficinas. La cláusula WHERE WHERE indica indica qué filas van a ser modificadas. modificadas. Si se omite la omite la cláusula WHERE se actualizan todas las filas. filas. 

subconsulta . En SQL standard En la condición del WHERE se puede incluir una subconsulta. la tabla que aparece en la FROM FROM de de la subconsulta no puede ser la misma que la tabla que aparece como origen

Ejemplo: Queremos poner a cero las ventas de los empleados de la oficina 12 UPDATE empleados SET ventas = 0 WHERE oficina = 12; Ejemplo: Queremos poner a cero el limite de credito de los clientes asignados a empleados de la oficina 12. UPDATE clientes SET limitecredito = 0 WHERE repclie IN (SELECT numemp FROM empleados WHERE oficina = 12);


87





Si para el cálculo de expresion se utiliza una columna que también se modifica, el valor que se utiliza es el antes de la modificación, lo mismo para la condición de búsqueda. Cuando se ejecuta una sentencia UPDATE primero se genera el origen y se seleccionan las filas según filas según la cláusula WHERE WHERE.. A continuación se coge una fila de la selección y se le aplica la cláusula SET SET,, se actualizan todas las columnas incluidas en la cláusula SET a la vez columnas vez por lo que los nombres de columna pueden especificarse en cualquier orden. Después se coge la siguiente SET,, así fila de la selección y se le aplica del mismo modo la cláusula SET sucesivamente con todas las filas de la selección.

Ejemplo: UPDATE oficinas SET ventas=0, objetivo=ventas; O bien: UPDATE oficinas SET objetivo=ventas, ventas=0; Los dos ejemplos anteriores son equivalentes ya que el valor de ventas que se asigna a objetivo es el valor antes de la actualización, se deja como objetivo las ventas que ha tenido la oficina hasta el momento y se pone a cero la columna ventas. 



Si actualizamos una columna definida como clave foránea, foránea, esta columna se podrá actualizar o no siguiendo las reglas de integridad referencial. referencial . El valor que se le asigna debe existir en la tabla de referencia. Si actualizamos una columna definida como columna principal de una relación entre dos tablas, tablas , esta columna se podrá actualizar o no siguiendo las reglas de integridad referencial.


88

Borrar filas (DELETE) La sentencia DELETE elimina filas de una tabla. tabla. La sintaxis es la siguiente:















Origen es el nombre de la tabla de donde vamos a borrar , podemos indicar un Origen es IN si la tabla se encuentra en una base de nombre de tabla, incluir la cláusula IN datos externa, también podemos escribir una composición de tablas. La opción tabla.* tabla.* se se utiliza cuando el origen está basado en varias tablas, tablas, y sirve para indicar en qué tabla vamos a borrar . defecto y se puede poner La opción * es opcional y es la que se asume asume por defecto unicamente cuando el origen es una sola tabla. tabla. WHERE sirve para especificar qué filas queremos borrar . Se La cláusula WHERE eliminaran de la tabla todas las filas que cumplan la condición. Si no se indica la cláusula WHERE WHERE,, se borran TODAS las filas de filas de la tabla. En la condición de búsqueda búsqueda de la sentencia DELETE DELETE,, se puede utilizar una subconsulta.. En SQL standard la tabla que aparece en la FROM subconsulta FROM de la FROM de la subconsulta no puede ser la misma que la tabla que aparece en la FROM DELETE pero DELETE pero en el SQL de Microsoft Jet sí se puede hacer. Una vez borrados, los registros no se pueden recuperar . (al menos que no hayamos puesto un Begin Transaction, para poder darle el ROLLBACK) Si la tabla donde borramos está relacionada con otras tablas se podrán borrar o no los registros siguiendo las reglas de integridad referencial definidas referencial definidas en las relaciones.

Ejemplo: DELETE * FROM pedidos WHERE clie IN (SELECT numclie FROM clientes WHERE nombre = 'Julian López'); O bien: DELETE pedidos.* FROM pedidos INNER JOIN clientes.numclie WHERE nombre = 'Julian López';

clientes

ON

pedidos.clie

=

Las dos sentencias borran los pedidos del cliente Julian López . En la segunda estamos obligados a poner pedidos.* porque el origen está basado en varias tablas DELETE * FROM pedidos; o pedidos; o DELETE FROM pedidos; Borra pedidos; Borra todas las filas de pedidos


89

Resumen del tema 











conocidos utilizamos la Si queremos añadir en una tabla una fila con valores conocidos valores) . sentencia INSERT INTO tabla VALUES (lista de valores). Si los valores a insertar se encuentran en en una o varias tablas tablas utilizamos INSERT INTO tabla SELECT ... Para crear una nueva tabla con el resultado de una consulta con la sentencia SELECT...INTO tabla FROM... tabla , tenemos que actualizar las Para cambiar los datos contenidos en una tabla, filas de dicha tabla con la sentencia UPDATE tabla SET asignación de nuevos valores.. valores Para eliminar filas de una tabla se tabla se utiliza la sentencia DELETE FROM tabla. tabla. Con la cláusula WHERE WHERE podemos podemos indicar a qué filas afecta la actualización o el borrado


90

Conceptos básicos de integridad referencial. Introducción La integridad referencial es un sistema de de reglas reglas que utilizan la mayoría de las bases de datos relacionales para asegurarse que los registros de tablas relacionadas son válidos y válidos y que no se borren o cambien datos relacionados de forma accidental produciendo errores de integridad. Primero repasemos un poco los tipos de relaciones. Entre dos tablas de cualquier base de datos relacional pueden haber dos tipos de relaciones, relaciones uno a uno y relaciones uno a muchos: Relación Uno a Uno: Uno: Cuando un registro de una tabla sólo puede estar relacionado con un único registro de la otra tabla y viceversa. viceversa . Por ejemplo: tenemos dos tablas una de profesores y otra de departamentos y queremos saber qué profesor es jefe de qué departamento, tenemos una relación uno a uno entre las dos tablas ya que un departamento tiene un solo jefe y un profesor puede ser jefe de un solo departamento. Relación Uno a Varios: Varios: Cuando un registro de una tabla (tabla tabla (tabla secundaria) sólo puede estar relacionado con un único registro de la otra tabla (tabla principal) y un registro de la tabla principal puede tener más de un registro relacionado en la tabla secundaria, en secundaria, en este caso se suele hacer referencia a la tabla principal como tabla 'padre' y a la tabla secundaria como tabla 'hijo', entonces la regla se convierte en 'un padre puede tener varios hijos pero un hijo solo tiene un padre (regla más fácil de recordar).

Tipos de relaciones. Por ejemplo: tenemos dos tablas una con los datos de diferentes poblaciones y otra con los habitantes, una población puede tener más de un habitante, pero un habitante pertenecerá (estará empadronado) en una única población. En este caso la tabla principal será la de poblaciones y la tabla secundaria será la de habitantes. Una población puede tener varios habitantes pero un habitante pertenece a una sola población. Esta relación se representa incluyendo en la tabla 'hijo' una columna que se corresponde con la clave principal de la tabla 'padre', esta columna es lo denominamos clave foránea (o clave ajena o clave externa). Una clave foránea es pues un campo de una tabla que contiene una referencia a un registro de otra tabla. Siguiendo nuestro ejemplo en la tabla habitantes tenemos una columna población que contiene el código de la población en la que está empadronado el habitante, esta columna es clave ajena de la tabla habitantes, y en la tabla poblaciones tenemos una columna codigo de poblacion clave principal de la tabla.


91

Relación Varios a Varios: Varios: Cuando un registro de una tabla puede estar relacionado con más de un registro de la otra tabla y viceversa. viceversa . En este caso las dos tablas no pueden estar relacionadas directamente, se tiene que añadir una tabla entre las dos que incluya los pares de valores relacionados entre sí. Por ejemplo: tenemos dos tablas una con los datos de clientes y otra con los artículos que se venden en la empresa, un cliente podrá realizar un pedido con varios artículos, y un artículo podrá ser vendido a más de un cliente. No se puede definir entre clientes y artículos, hace falta otra tabla (por ejemplo una tabla de pedidos) relacionada con clientes y con artículos. La tabla pedidos estará relacionada con cliente por una relación uno a muchos y también estará relacionada con artículos por un relación uno a muchos.

Integridad referencial Cuando se define una columna como clave foránea, las filas de la tabla pueden contener en esa columna o bien el valor nulo (ningún valor), o bien un valor que existe en la otra tabla, un error sería asignar a un habitante una población que no está en la tabla de poblaciones. Eso e s lo que se denomina integridad referencial y referencial y consiste en que los datos que referencian otros (claves foráneas) deben ser correctos . La integridad referencial hace que el sistema gestor de la base de datos se asegure de que no hayan en las claves foráneas valores que no estén en la tabla principal. La integridad referencial se activa en cuanto creamos una clave foránea y a partir de ese momento se comprueba cada vez que se modifiquen d atos que puedan alterarla. ¿Cuándo se pueden producir errores en los datos? 



Cuando insertamos una nueva fila en la tabla secundaria y el valor de la clave foránea no existe en la tabla principal. principal . insertamos un nuevo habitante y en la columna poblacion escribimos un código de poblacion que no está en la tabla de poblaciones (una población que no existe). Cuando modificamos el valor de la clave principal de un registro que tiene 'hijos', 'hijos' , modificamos el codigo de Valencia, sustituimos el valor que tenía (1) por un nuevo valor (10), si Valencia tenía habitantes asignados, qué pasa con esos habitantes, no pueden seguir teniendo el codigo de población 1 porque la población 1 ya no existe, en este caso hay dos alternativas, no dejar cambiar el codigo de Valencia o bien cambiar el codigo de población de todos los habitantes de Valencia y asignarles el código 10.

Cuando modificamos el valor de la clave foránea, el nuevo valor debe existir en la tabla principal.. Por ejemplo cambiamos la población de un habitante, tenía asignada la población 1 principal


92

(porque estaba empadronado en valencia) y ahora se le asigna la población 2 porque cambia de lugar de residencia. La población 2 debe existir en la tabla de poblaciones. 

Cuando queremos borrar una fila de la tabla principal y ese registro tiene 'hijos', 'hijos' , por ejemplo queremos borrar la población 1 (Valencia) si existen habitantes asignados a la población 1, estos no se pueden quedar con el valor 1 en la columna población porque tendrían asignada una población que no existe. En este caso tenemos dos alternativas, no dejar borrar la población 1 de la tabla de poblaciones, o bien borrarla y poner a valor nulo el campo poblacion de todos sus 'hijos'.

Asociada a la integridad referencial están los conceptos de actualizar los registros en cascada y eliminar registros en cascada.

Actualización y borrado en cascada El actualizar y/o eliminar registros en cascada, son opciones que se definen cuando definimos la clave foránea y que le indican al sistema gestor qué hacer en los casos comentados en el punto anterior ACTUALIZAR REGISTROS EN CASCADA: CASCADA: Esta opción le indica al sistema gestor de la base de datos que cuando se cambie un valor del campo clave de la tabla principal, principal, automáticamente cambiará el valor de la clave foránea de los registros relacionados en la tabla secundaria. secundaria . Por ejemplo, si cambiamos en la tabla de poblaciones (la tabla principal) el valor 1 por el valor 10 en el campo codigo (la clave principal), automáticamente se actualizan todos los habitantes (en la tabla secundaria) que tienen el valor 1 en el campo poblacion (en la clave c lave ajena) dejando 10 en vez de 1. Si no se tiene definida esta opción, no se puede cambiar los valores de la clave principal de la tabla principal. En este caso, si intentamos cambiar el valor 1 del codigo de la tabla de poblaciones , no se produce el cambio y el sistema nos devuelve un error o un mensaje que los registros no se han podido modificar por infracciones de clave. ELIMINAR REGISTROS EN CASCADA: CASCADA: Esta opción le indica al sistema gestor de la base de datos que cuando se elimina un registro de la tabla principal automáticamente principal automáticamente se borran también los registros relacionados en la tabla secundaria. secundaria. Por ejemplo: Si borramos la población Onteniente en la tabla de poblaciones, automáticamente todos los habitantes de Onteniente se borrarán de la tabla de habitantes. Si no se tiene definida esta opción, no se pueden borrar registros de la tabla principal si estos tienen registros relacionados en la tabla secundaria. En este caso, si intentamos borrar la población Ontenyente, no se produce el borrado y el sistema nos devuelve un error o un mensaje que los registros no se han podido eliminar por infracciones de clave.

Ejemplos de borrados y modificaciones en cascada Vamos a ver con un ejemplo como funciona el borrado en cascada. Creamos las tablas departamentos2 y empleados2 con una clave ajena con borrado en cascada e insertamos los valores desde las tablas departamentos y empleados


93

CREATE TABLE departamentos2 (dep_no INT, dnombre VARCHAR(14), localidad VARCHAR(10), CONSTRAINT PK2_DEP PRIMARY KEY (DEP_NO));

INSERT INTO departamentos2 SELECT dep_no, dnombre, localidad FROM departamentos; CREATE TABLE empleados2 (emp_no INT, apellido VARCHAR(8), oficio VARCHAR(15), director INT, fecha_alta DATETIME, dep_no INT, CONSTRAINT PK_EMPLEADOS_EMP_NO2 PK_EMPLEADOS_EMP_NO2 PRIMARY KEY (emp_no), CONSTRAINT FK_EMP_DEP_NO2 FK_EMP_DEP_NO2 FOREIGN KEY (dep_no) REFERENCES departamentos2(dep_no) ON DELETE CASCADE ON UPDATE CASCADE); INSERT INTO empleados2 SELECT emp_no, apellido, oficio, director, fecha_alta, dep_no FROM empleados; SELECT * FROM departamentos2; SELECT * FROM empleados2; SELECT * FROM empleados2;

Ahora vamos a borrar una fila f ila en la tabla departamentos. Si no existiese borrado en cascada, debido a la integridad referencial, no podríamos borrar ningún departamento que tuviese empleados. De esta forma al borrar un departamento se borrarán todos los empleados de ese departamento. DELETE FROM departamentos2 WHERE dep_no=10; SELECT * FROM departamentos2; SELECT * FROM departamentos2; +--------+---------------+-----------+ | dep_no | dnombre | localidad | +--------+---------------+-----------+ | 20 | INVESTIGACION | VALENCIA | | 30 | VENTAS | MADRID | | 40 | PRODUCCION | SEVILLA | +--------+---------------+-----------+


94

Unidad 7. Las subconsultas Definiciones Una subconsulta es una sentencia SELECT que aparece dentro de otra sentencia SELECT que SELECT que llamaremos consulta principal. principal. selección, en la cláusula WHERE o en la cláusula Se puede encontrar en la lista de selección, HAVING de HAVING de la consulta principal. Una subconsulta tiene la misma sintaxis que una sentencia SELECT normal exceptuando que paréntesis , no puede contener la cláusula ORDER BY, BY, ni puede ser aparece encerrada entre paréntesis, UNION de varias sentencias SELECT SELECT,, además tiene algunas restricciones en cuanto a la UNION número de columnas según el lugar donde aparece en la consulta principal. Estas restricciones las iremos describiendo en cada caso. Cuando se ejecuta una consulta que contiene una subconsulta, la subconsulta se ejecuta por cada fila de la consulta principal. principal . Se aconseja no utilizar campos calculados en las subconsultas, ralentizan la consulta. usuario . Las consultas que utilizan subconsultas suelen ser más fáciles de interpretar por el usuario. A menudo, es necesario, dentro del cuerpo de una subconsulta, hacer referencia al valor de una columna en la fila actual de la consulta principal, ese nombre de columna se denomina externa es un nombre de columna que estando en la referencia externa. Una Una referencia externa FROM de la subconsulta, no se refiere a ninguna columna de las tablas designadas en la FROM subconsulta sino a una columna de las tablas designadas en la FROM de la consulta principal. Como principal. Como la subconsulta se ejecuta por cada fila de la consulta principal, el valor de la referencia externa irá cambiando. Ejemplo: SELECT numemp, nombre, (SELECT MIN(fechapedido) FROM pedidos WHERE rep = numemp) FROM empleados; En este ejemplo la consulta principal es SELECT... FROM empleados. empleados. La subconsulta es ( SELECT MIN(fechapedido) FROM pedidos WHERE rep = numemp ) . En esta subconsulta tenemos una referencia externa ( numemp ) es un campo de la tabla empleados (origen de la consulta principal).


95

¿QUÉ PASA CUANDO SE EJECUTA LA CONSULTA PRINCIPAL? 

se coge el primer empleado y se calcula la subconsulta sustituyendo numemp por el valor que tiene en el primer empleado. La subconsulta obtiene la fecha más antigua en los pedidos del rep = 101,

se coge el segundo empleado y se calcula la subconsulta con numemp = 102 (numemp del segundo empleado)... y así sucesivamente hasta llegar al último empleado. Al final obtenemos una lista con el número, nombre y fecha del primer pedido de cada empleado. Si quitamos la cláusula WHERE cláusula WHERE de de la subconsulta obtenemos la fecha del primer pedido de todos los pedidos no del empleado correspondiente. 

Anidar subconsultas Las subconsultas pueden anidarse anidarse de de forma que una subconsulta aparezca en la cláusula WHERE (por WHERE subconsulta que a su vez forma parte de otra consulta principal. (por ejemplo) de otra subconsulta que En la práctica, una consulta consume mucho m ás tiempo y memoria cuando se incrementa el número de niveles de anidamiento. anidamie nto. La consulta resulta también más difícil de leer , comprender y mantener cuando contiene más de uno o dos niveles de subconsultas. Ejemplo: SELECT numemp, nombre FROM empleados WHERE numemp = (SELECT rep FROM pedidos WHERE clie = (SELECT numclie FROM clientes WHERE nombre = 'Julia Antequera')) En este ejemplo, por cada linea de pedido se calcula la subconsulta de clientes, y esto se repite por cada empleado, en el caso de tener 10 filas de empleados y 200 filas de pedidos (tablas realmente pequeñas), la subconsulta más interna se ej ecutaría 2000 veces (10 x 200).

Subconsulta en la lista de selección selección de la consulta principal, en este caso Cuando la subconsulta aparece en la lista de selección de columnas, de lo contrario se da un la subconsulta, no puede devolver varias filas ni varias columnas, mensaje de error. Muchos SQLs no permiten que una subconsulta aparezca en la lista de selección de la consulta principal pero eso no es ningún problema ya que normalmente se puede obtener lo mismo utilizando como origen de datos las dos tablas. El ejemplo anterior se puede obtener de la siguiente forma: SELECT numemp, nombre, MIN(fechapedido) FROM empleados LEFT JOIN pedidos ON empleados.numemp = pedidos.rep GROUP BY numemp, nombre MICROSOFT SQL Server Gota a Gota en las Bases de Datos Relacionales Lic. Elvin German

96

En la cláusula FROM En la cláusula FROM se puede encontrar una sentencia SELECT encerrada entre paréntesis pero más que subconsulta sería una consulta ya consulta ya que no se ejecuta para cada fila de la tabla origen sino que se ejecuta una sola vez al principio, su resultado se combina con las filas de la otra tabla para formar las filas origen de la SELECT primera y no admite referencias externas. En la cláusula FROM vimos que se podía poner un nombre de tabla o un nombre de consulta, pues en vez de poner un nombre de consulta se puede poner directam ente la sentencia SELECT correspondiente a esa consulta encerrada entre paréntesis.

Subconsulta en las cláusulas WHERE y HAVING Se suele utilizar subconsultas en las cláusulas WHERE o HAVING cuando los datos que queremos visualizar están en una tabla pero para seleccionar las filas de esa tabla necesitamos un dato que está en otra tabla. Ejemplo: SELECT numemp, nombre FROM empleados WHERE contrato = (SELECT MIN(fechapedido) FROM pedidos) En este ejemplo listamos el número y nombre de los em pleados cuya fecha de contrato sea igual a la primera fecha de todos los pedidos de la empresa. En una cláusula WHERE / HAVING HAVING tenemos tenemos siempre una condición y la subconsulta actúa de operando dentro de esa condición. En el ejemplo anterior se compara contrato con el resultado de la subconsulta. Hasta ahora las condiciones estudiadas tenían como operandos valores simples (el valor contenido en una columna de una fila de la tabla, el resultado de una operación aritmética...) ahora la subconsulta puede devolver una columna entera por lo que es necesario definir otro tipo de condiciones de condiciones especiales para especiales para cuando se utilizan con subconsultas.

Condiciones de selección con subconsultas Las condiciones de selección selección son las condiciones que pueden aparecer en la cláusula WHERE o HAVING HAVING.. La mayoría se han visto en el tema 2 pero ahora incluiremos las condiciones que utilizan una subconsulta como operando. En SQL tenemos cuatro nuevas condiciones: test de comparación con subconsulta  test de comparación cuantificada  test de pertenencia a un conjunto  test de existencia   test de comparación con subconsulta. subconsulta . MICROSOFT SQL Server Gota a Gota en las Bases de Datos Relacionales Lic. Elvin German

97

simple. Se utiliza para comparar un valor de la fila Es el equivalente al test de comparación simple. que se está examinado con un único valor producido por la subconsulta. La subconsulta debe devolver una única columna, columna, sino se produce un error. Si la subconsulta no produce ninguna fila fila o devuelve el valor nulo nulo,, el test devuelve el valor nulo,, si la subconsulta produce varias filas, nulo filas, SQL devuelve una condición de error . La sintaxis es la siguiente:

Lista las oficinas cuyo objetivo sea superior a la suma de las ventas de sus empleados. En este caso la subconsulta devuelve una única columna y una única fila (es un consulta de resumen sin GROUP BY) BY) SELECT oficina, ciudad FROM oficinas WHERE objetivo > (SELECT SUM(ventas) FROM empleados WHERE empleados.oficina = oficinas.oficina) test de comparación cuantificada. cuantificada . Este test es una extensión del test de comparación y del test de co njunto. Compara el valor de de la expresión con cada uno de los valores producidos valores producidos por la subconsulta. La subconsulta debe columna sino se produce un error. devolver una única columna sino ANY ( ALL ( Tenemos el test ANY (algún, alguno en inglés) y el test ALL (todos en inglés). La sintaxis es la siguiente:

test ANY. ANY. La subconsulta debe devolver una única columna sino se produce un error. Se evalúa la comparación con cada valor devuelto por la subconsulta. Si alguna de las comparaciones individuales produce el resultado verdadero, el test ANY devuelve el resultado verdadero. verdadero . ANY devuelve falso falso.. Si la subconsulta no devuelve ningún valor , el test ANY falso para valores de la columna, ANY ANY devuelve falso.. Si el test de comparación es falso para todos los valores de devuelve falso verdadero para ningún valor de la columna, y es nulo para nulo para al Si el test de comparación no es verdadero para MICROSOFT SQL Server Gota a Gota en las Bases de Datos Relacionales Lic. Elvin German

98

menos alguno de los valores, ANY ANY devuelve devuelve nulo nulo.. test ALL. ALL. La subconsulta debe devolver una única columna sino se produce un error. Se evalúa la comparación con cada valor devuelto por la subconsulta. Si todas las todas las comparaciones individuales, producen un resultado verdadero verdadero,, el test devuelve el valor verdadero verdadero.. Si la subconsulta no devuelve ningún valor el el test ALL ALL devuelve devuelve el valor verdadero verdadero.. (¡Ojo con esto!) falso para falso.. Si el test de comparación es falso para algún valor de la columna, el resultado es falso falso para ningún valor de la columna, pero es nulo nulo para Si el test de comparación no es falso para para alguno ALL devuelve de esos valores, el test ALL devuelve valor nulo. Test de Test de pertenencia a conjunto ( conjunto (IN IN). ). Examina si el valor de la expresión es uno de los valores incluidos en la lista de valores producida por la subconsulta . subconsulta. La subconsulta debe generar una única columna y las filas que sean. Si la subconsulta no produce ninguna fila, el test da falso. Tiene la siguiente sintaxis: test de existencia EXISTS. EXISTS. Examina si la subconsulta produce alguna al guna fila de resultados. Si la subconsulta contiene filas, filas, el test adopta el valor verdadero verdadero,, si la subconsulta no contiene ninguna fila, el test toma el valor falso, nunca puede tomar el valor nulo. Con este test la subconsulta puede tener varias columnas, columnas , no importa ya que el test se fija no en los valores devueltos sino en si hay o no fila en la tabla resultado de la subconsulta. Cuando se utiliza el test de existencia en la mayoría de los casos habrá que utilizar una referencia externa. Si no se utiliza una referencia externa la subconsulta devuelta siempre será la misma para todas las filas de la consulta principal y en este caso se seleccionan todas las filas de la consulta principal (si la subconsulta genera filas) o ninguna (si la subconsulta no devuelve ninguna fila) La sintaxis es la siguiente:

EXISTS suele dar mejor NOTA. Cuando se trabaja con tablas muy voluminosas el test EXISTS IN.. rendimiento que el test IN


99

Ejemplos: 1.Queremos saber la lista de empleados cuyo salario supere el salario medio. En primer lugar, tendríamos que averiguar el importe del salario medio: SELECT AVG(salario)"Salario Medio" FROM empleados; +---------------+ | Salario Medio +---------------+ | 2566.735569 +---------------+

2. Subconsulta que devuelven más de una expresión Obtener los empleados que tengan el mismo oficio y departamento que ALONSO SELECT emp_no, apellido, oficio, dep_no FROM empleados WHERE (dep_no, oficio) = (SELECT dep_no, oficio FROM empleados WHERE apellido = 'ALONSO'); +--------+----------+----------+--------+ | emp_no | apellido | oficio | dep_no | +--------+----------+----------+--------+ | 7499 | ALONSO | 7654 | MARTIN | 7844 | CALVO

| VENDEDOR | | VENDEDOR | | VENDEDOR |

30 | 30 | 30 |

3. Obtener todos los empleados que tienen el mismo oficio que GARRIDO SELECT emp_no "Nº Empleado", apellido, oficio FROM empleados WHERE oficio = (SELECT oficio FROM empleados WHERE apellido = 'GARRIDO'); +-------------+----------+----------+ | Nº Empleado | apellido | oficio | +-------------+----------+----------+ | 7698 | GARRIDO | DIRECTOR | | 7782 | MARTINEZ | DIRECTOR |

4.Obtener información de los empleados que ganan más que cualquier empleado del departamento 30. SELECT emp_no "Nº Empleado", apellido, salario, dep_no "Nº Departamento" FROM empleados MICROSOFT SQL Server Gota a Gota en las Bases de Datos Relacionales Lic. Elvin German

100

WHERE salario > (SELECT MAX(salario) FROM empleados WHERE dep_no=30); +-------------+----------+---------+-----------------+ | Nº Empleado | apellido | salario | Nº Departamento | +-------------+----------+---------+-----------------+ | 7839 | REY | 6000.00 | 10 | +-------------+----------+---------+-----------------+

5.Listar, en orden alfabético, aquellos empleados que no trabajen ni en Madrid ni en Barcelona. SELECT emp_no, apellido, dep_no FROM empleados WHERE dep_no IN (SELECT dep_no FROM departamentos WHERE localidad NOT LIKE 'MADRID' AND localidad NOT LIKE 'BARCELONA'); 'BARCELONA'); +--------+----------+--------+ | emp_no | apellido | dep_no | +--------+----------+--------+ | 7876 | GIL | 20 | | 7900 | JIMENEZ | 20 | +--------+----------+--------+

Ejemplos con el operador lógico EXISTS 1 - Visualizar los departamentos en los que hay más de un trabajador. SELECT dep_no, dnombre FROM departamentos d WHERE EXISTS ( SELECT * FROM empleados e WHERE e.dep_no = d.dep_no GROUP BY dep_no HAVING COUNT(*) > 1);

+--------+--------------+ | dep_no | dnombre | +--------+--------------+ | 10 | CONTABILIDAD | | 30 | VENTAS | +--------+--------------+


101

Ejemplos de subconsultas en la cláusula HAVING 1. Visualizar los departamentos en los los que el salario medio de sus empleados sea mayor o igual que la media de todos los salarios de la empresa. SELECT dep_no "Nº Departamento",AVG(salario)"Sala Departamento",AVG(salario)"Salario rio Medio" FROM empleados GROUP BY dep_no HAVING AVG(salario)>=(SELECT AVG(salario) FROM empleados); +-----------------+---------------+ | Nº Departamento | Salario Medio | +-----------------+---------------+ | 10 | 3266.833333 | +-----------------+---------------+

2. Visualizar el número y el nombre del departamento con más personal de oficio VENDEDOR. SELECT dep_no "No. Departamento", dnombre Departamento FROM departamentos WHERE dep_no=(SELECT dep_no FROM empleados WHERE oficio = 'VENDEDOR' GROUP BY dep_no HAVING COUNT(*)=(SELECT TOP 1 COUNT(*) FROM empleados WHERE oficio = 'VENDEDOR' GROUP BY dep_no ORDER BY 1 DESC ));

+-----------------+--------------+ | Nº Departamento | Departamento | +-----------------+--------------+ | 30 | VENTAS | +-----------------+--------------+


102

Resumen del tema SELECT que aparece en la lista de selección, o en las Una subconsulta es una sentencia SELECT WHERE o HAVING de SELECT.. cláusulas WHERE o HAVING de otra sentencia SELECT La subconsulta se ejecuta por cada fila de la consulta principal. Dentro de una consulta se puede utilizar una columna del origen de la consulta principal, una referencia externa. Aunque se puedan anidar subconsultas no es aconsejado más de un nivel de anidamiento. La subconsulta sufre una serie de restricciones según el lugar donde se encuentre. Las condiciones asociadas a las subconsultas son las siguientes: test de comparación con subconsulta test ANY test ALL test IN test EXISTS


103

Unidad 8. Vistas Definiciones Podemos definir una vista como una consulta almacenada en la base de datos que se utiliza como una tabla virtual. Se define asociadas a una o varias tablas y no almacena los datos sino que trabaja sobre los datos de las tablas sobre las que está definida, estando así en todo momento actualizada.

¿Qué son las vistas y para qué sirven?. Se trata de una perspectiva de la base de datos o ventana que permite a uno o varios usuarios ver solamente las filas y columnas necesarias para su trabajo. Entre las ventajas que ofrece la utilización de vistas cabe destacar:  Seguridad y confidencialidad: ya que la vista ocultará los datos confidenciales confidenciales o aquellos para los que el usuario no tenga permiso.  Comodidad: ya que solamente solamente muestra muestra los datos relevantes, permitiendo, incluso trabajar con agrupaciones de filas como si se tratase de una única fila o con composiciones de varias tablas como si se tratase de una única tabla. Independencia respecto a posibles cambios en los nombres de las columnas, de las las tablas,  etcétera. Por ejemplo, la siguiente consulta permite al departamento de VENTAS realizar la gestión de sus empleados ocultando la información relativa a los empleados de otros departamentos. SELECT * FROM EMPLEADOS WHERE dep_no=30; La siguiente consulta permite a cualquier empleado de la empresa obtener información no confidencial de cualquier otro empleado ocultando las columnas SALARIO y C OMISION: SELECT emp_no, apellido, oficio, director, fecha_alta, dep_no FROM empleados; Para ello crearemos vistas y permitiremos a los usuarios tener acceso a las vistas sin tenerlo de la tabla completa.

Creación y utilización de vistas Como hemos dicho son tablas virtuales resultado de una consulta realizadas sobre tablas ya existentes. Las vistas no ocupan espacio en la base de datos ya que lo único que se almacena es la definición de la vista. El gestor de la base de datos se encargará de comprobar los comandos SQL que hagan referencia a la vista, transformándolos en los comandos correspondientes referidos a las tablas originales, todo ello de forma transparente para el usuario.


104

Formato de la creación de vistas. Para crear una vista se utiliza el comando CREATE VIEW según el siguiente formato genérico:

CREATE VIEW [ schema_name . ] view_name vie w_name [ (column [ ,...n ] ) ] [ WITH [ ,...n ] ] AS select_statement [ WITH CHECK OPTION ] [ ; ] ::= { [ ENCRYPTION ] [ SCHEMABINDING ]

[ VIEW_METADATA ]

}

El siguiente ejemplo crea la vista emple_dep30 para la gestión de los empleados del departamento 30 mencionada en el apartado anterior. CREATE VIEW emple_dep30 AS SELECT * FROM EMPLEADOS WHERE DEP_NO = 30; CREATE VIEW emple_dep30 AS SELECT * FROM EMPLEADOS WHERE DEP_NO = 30; A continuación se muestra la sentencia que crea la vista datos_emple que contiene información de todos los empleados ocultando la información confidencial. CREATE VIEW datos_emple AS SELECT emp_no, apellido, oficio, director, fecha_alta, dep_no FROM empleados; Las vistas pueden a su vez definirse sobre otras vistas. Si ya tenemos creada las vista datos_emple, podríamos crear otra vista sobre ella: CREATE VIEW datos_emple_10 AS SELECT * FROM datos_emple WHERE dep_no=10;

Utilización de vistas Una vez definida puede ser utilizada para consultas de igual forma que una tabla. Con algunas restricciones, todos los formatos de selección vistos para las tablas son aplicables para la selección de filas en las vistas.


105

Por ejemplo: También podemos seleccionar solo algunas columnas y poner una condición SELECT apellido, director FROM datos_emple WHERE oficio = 'VENDEDOR'; ----------------------|apellido|director| ----------------------|ALONSO|7698 MARTIN | 7698 |CALVO|7698 ----------------------Pero debe tenerse en cuenta que si al definir la vista hemos indicado nuevos nombres para columnas y expresiones si podremos hacer referencia a ellos en las sentencias de selección, pero si hemos omitido la definición de las columnas y en la sentencia de creación hemos realizado la selección de todas las columnas (creada con select *) solo puede hacerse una selección de todas las columnas de la vista (con *) La vista emple_dep30 la creamos sin especificar nuevo nombre para las columnas de la vista y con una sentencia select *. Podemos obtener los datos de la vista si escribimos: SELECT * FROM emple_dep30; --------------------------------------------------------------------------------------------------------------------------------------------------------------------|EMP_NO |APELLIDO| OFICIO |DIRECTOR | FECHA_ALTA | SALARIO |COMISION| --------------------------------------------------------------------------------------------------------------------------------------------------------------------|7499 | ALONSO | VENDEDOR| 7698 | 1981-02-23 | 1400.00 | 400.00 | 30 | |7654 | MARTIN | VENDEDOR| 7698 | 1981-09-28 | 1500.00 |1600.00 | 30 | |7698 | GARRIDO | DIRECTOR| 7839 | 1981-05-01 | 3850.12 | NULL | 30 | |7844 | CALVO | VENDEDOR| 7698 | 1981-09-08 | 1800.00 | 0.00 | 30 | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------

DEP_NO |

Ejemplos creación y utilización de vistas Como hemos dicho una vez creada la vista se puede utilizar como si se tratase de una tabla (observando las restricciones anteriores). Veamos lo que podemos hacer con las vistas con los ejemplos. 1-El siguiente ejemplo crea la vista datos_vendedores que muestra solamente las columnas emp_no, apellido, director, fecha_alta, dep_no, de aquellos empleados cuyo oficio es VENDEDOR. CREATE VIEW datos_vendedores (num_vendedor, apellido, director, fecha_alta, dep_no) AS SELECT emp_no, apellido, director, fecha_alta, dep_no FROM empleados WHERE oficio = 'VENDEDOR';


106

Los datos accesibles mediante la vista creada serán: SELECT * FROM datos_vendedores; +--------------+----------+----------+------------+--------+ | num_vendedor | apellido | director | fecha_alta | dep_no | +--------------+----------+----------+------------+--------+ 7499 | ALONSO 7654 | MARTIN 7844 | CALVO

7698 | 1981-02-23 | 7698 | 1981-09-28 | 7698 | 1981-09-08 |

30 | 30 | 30 |

2- También se pueden crear vistas a partir de consultas que incluyen agrupaciones, como en el siguiente ejemplo: CREATE VIEW resumen_dep1 (dep_no, num_empleados, suma_salario, suma_comision) AS SELECT dep_no, COUNT(emp_no), SUM(salario), SUM(ISNULL(comision,0)) FROM empleados GROUP BY dep_no; En estos casos, cada fila de la vista corresponderá a varias filas en la tabla original tal como se puede comprobar en la siguiente consulta: SELECT * FROM resumen_dep1;

Eliminación de vistas La sentencia DROP VIEW permite eliminar la definición de una vista.

Ejemplos de borrado de vistas DROP VIEW resumen_emp_dep; emple_10' references invalid table(s) or column(s)


107

REQUISITOS DE INSTALACIÓN DE SQL SERVER Veremos las 5 versiones que hay de SQL Server, para que podemos usar cada una de ellas, las características de las versiones y los requerimientos de hardware, de Sistema Operativo y de red. Versiones 









SQL Server Enterprise Edition Edition (version en 32 y 64 bits): Diseñado para soportar entornos de grandes transacciones empresariales (OTLP), gran complejidad de analisis de datos, datawarehouse, y Web Sites muy activas. SQL Server Standard Edition Edition (version en 32 y 64 bits): Diseñado para comercio electronico, datawarehouse y soluciones de lineas de negocio de tamaño pequeño-mediano. SQL Server WorkGroup Edition (version Edition (version en 32 bits): Diseñado para pequeñas organizaciones que necesitan una base de datos que no tenga limite de usuarios, tambien se puede usar para pequeños web servers. SQL Server Developer Edition Edition (version en 32 y 64 bits): Incluye las mismas funcionalidades que el SQL Server Enterprise Edition, pero esta licenciado para servidores de desarrollo y test, no para servidores productivos. SQL Server Express Edition Edition (versión en 32 bits): Es gratuito, facil de usar y administrar base de datos, puede ser usado como cliente de base de datos en vez de servidor de base de datos.

Características de las versiones Funcionalidad Num. CPUS Memoria

Express 1 1 Gb

WorkGroup 2 3 Gb

Standard 4 Limitado por el S.O. Soporte 64 bits Con Con Windows Nativo Windows on on Windows Windows (WOW) (WOW) Tamaño máximo 4 Gb Sin limite Sin limite de BD Particionamiento Particionam iento No No No Database No No Si Mirroring Cluster No No Si (solo 2 nodos) Log Shipping No Si Si Management No Si Si Studio Database No No Si

Enterprise Ilimitado Limitado por el S.O. Nativo

Sin limite Si Si Si Si Si Si


108

Tunning Advisor Full Text Search SQL Server Agent Best Practices Analyzer Notification Services Service Broker Merge Replication Transactional Replication Oracle Replication

Web Services (HTTP EndPoints) Report Server Report Builder BI Development Studio

No No

Si Si

Si Si

Si Si

Si

Si

Si

SI

No

No

Si

Si

Si

Si

Si

Si

Si (Solo Si subscriptor) Si (Solo Si (Publicador Subscriptor) hasta 25 suscriptores) Si (Solo Si (Publicador Subscriptor) hasta 5 subscriptores) No No

Si

SI

No

No

No

Si

Si, replicacion transaccional con Oracle como un publicador Si

Si No Si (Solo Report Designer) No

Si Si Si (Solo Report Designer) Si

Si Si Si

Si SI Si

Si

SI

Si (Solo Reporting Services) No

Si

Si

Si

Si

Enterprise Management Tools Native Support Si (Solo for Web Services Reporting Services) Analysis No Services


109

Requerimientos de Hardware, Sistema Operativo y Red Versiones 32 bits CPU Todas las versiones de SQL Server requieren como minimo un Pentium III a 600 Mhz (o compatible) Sistema Operativo Enterprise Edition        

Windows Server 2003 Standard Edition con SP1 o superior Windows Server 2003 Enterprise Edition con SP1 o superior Windows Server 2003 DataCenter Edition con SP1 o superior Windows Small Business Server 2003 Standard Edition con SP1 o superior Windows Small Business Server 2003 Premium Edition con SP1 o superior Windows 2000 Server con SP4 Windows 2000 Advanced Server con SP4 Windows 2000 DataCenter Server con SP4

Standard Edition   

Las misma que la versión enterprise Windows 2000 Professional con SP4 Windows XP Profressional con SP2 o superior

WorkGroup Edition   

Las mismas que la versión Standard Windows XP Media Edition con SP2 o superior Windows XP Tablet Edition con SP2 o superior

Express Edition   

Las mismas que la versión WorkGroup Windows XP Home Edition con SP2 o superior Windows Server 2003 Web Edition con SP1 o Superior

Developer Edition  

Las mismas que la versión WorkGroup Windows XP Home Edition con SP2 o superior

Memoria Enterprise Edition: Edition: Minimo 512 MB, 1 GB o mas recomendada MICROSOFT SQL Server Gota a Gota en las Bases de Datos Relacionales Lic. Elvin German

110

Standard Edition: Edition: Minimo 512 MB, 1 GB o mas recomendada WorkGroup Edition: Edition: Minimo 512 MB, 1 GB o mas recomendada (maximo 3 Gb) Express Edition: Edition: Minimo 192 MB, 512 MB o mas recomendada (maximo 1 Gb) Developer Edition: Edition: Minimo 512 MB, 1 GB o mas recomendada Disco Duro Todas las versiones de SQL Server necesitan 350 Mb para la instalación completa y adicionalmente 390 Mb para las Base de Datos de ejemplos Red Componente Requerimiento Internet Software Internet Explorer 6.0 SP1 o superior es necesario para todas las instalaciones de SQL Server , porque el IE es necesario para la Microsoft Management Console (MMC) y el HTML Help. Una instalacion minima del IE es suficiente y no se tiene que configurar con navegador predeterminado. Si solo se usa la opcion de “Connectivity Only” y no se conecta a un servidor con encriptacion, con el IE 4.01 SP2 es suficiente IIS Si se escriben aplicaciones XML se debe de configurar el IIS. El IIS 5.0 o superior es requerido para la instalación del Reporting Services Red Debe de estar habilitado el TCP/IP

Versiones 64 bits CPU  

IA64: Minimo 1 GHz de procesador Itanium X64: Minimo 1 Ghz de procesador ADM Opteron, AMD Athlon 64, Intel Xeon con soporte Intel EM64T o Pentium IV con soprte EM64T

Sistema Operativo Enterprise Edition IA64, Standard Edition IA64 y Developer Edition IA64  

Windows Server 2003 64-Bit Itanium DataCenter Edition con SP1 o superior Windows Server 2003 64-Bit Itanium Enterprise Edition con SP1 o superior


111

Enterprise Edition X64, Standard Edition X64 y Developer Edition X64   

Windows Server 2003 64-Bit X64 DataCenter Edition con SP1 o superior Windows Server 2003 64-Bit X64 Enterprise Edition con SP1 o superior Windows Server 2003 64-Bit X64 Standard Edition con SP1 o superior

Express Edition  

Las mismas que las IA64 y las X64 Windows XP X64 Professional (64-Bit)

Memoria, Disco Duro y Red Los mismos requerimientos que la version 32-Bit


112

BIBLIOGRAFIA http://www.aulaclic.es/sql/b_6_1_1.htm http://www.aulaclic.es/sql/f_sql.htm Lic. Elvin German Paulina Barthelemy

http://misservidores.wordpress.com/2008/09/04/capitulo-1-instalacion-sql-server-/

www.monografias.com Prof. Manuel Torres Remon [email protected] [email protected] Docente de la Carrera Profesional de Computación e Informática Instituto Superior Tecnológico Publico Manuel Arevalo Caceres


113

ANEXOS NORMALIZACIÓN DE BASE DE DATOS 1. Descomposición y Normalización Siempre que un analista de sistemas de base de datos arma una base de datos, queda a su cargo descomponer dicha base en grupos y segmentos de registros. Este proceso es la descomposición; el mismo es necesario independientemente de la arquitectura de la base de datos - relacional, red o jerárquica-. Sin embargo, para la base de datos relacional, la acción correspondiente puede dividirse y expresarse en términos formales y se denomina normalización a la misma. La normalización convierte una relación en varias sub-relaciones, cada una de las cuales obedece a reglas. Estas reglas se describen en términos de dependencia. Una vez que hayamos examinado las distintas formas de dependencia, encontraremos encontraremo s procedimientos a aplicar a las relaciones de modo tal que las mismas puedan descomponerse de acuerdo a la dependencia que prevalece. Esto no llevará indefectiblemente a formar varias subrelaciones a partir de la única relación preexistente.

2. Dependencia Significado: Antes de entrar en el tópico principal de dependencia, vamos a ver algunos conceptos acerca de los individuos y acerca de las tuplas que los describen describen en la base de datos relacional (BDR). Restringiremos la discusión a la BDR, si bien la misma se aplica igualmente a las otras arquitecturas.

Existen tres tipos distintos de dependencia. 

Total uno-uno-sinónimo



Completa - subtupla



Transitiva - múltiple.


114

3. Normalización ¿Qué es normalización? Normalización es un proceso que clasifica relaciones, objetos, formas de relación y demás elementos en grupos, en base a las características que cada uno posee. Si se identifican ciertas reglas, se aplica un categoría; si se definen otras reglas, se aplicará otra categoría. Estamos interesados en particular en la clasificación de las relaciones BDR. La forma de efectuar esto es a través de los tipos de dependencias que podemos determinar dentro de la relación. Cuando las reglas de clasificación sean más y más restrictivas, diremos que la relación está en una forma normal más elevada. La relación que está en la forma normal más elevada posible es que mejor se adapta a nuestras necesidades debido a que optimiza las condiciones que son de importancia para nosotros: •

La cantidad de espacio requerido para almacenar los datos es la menor posible;

•

La facilidad para actualizar la relación es la mayor posible;

•

La explicación de la base de datos es la más sencilla posible.

4. Primera forma normal Para que una relación esté en primera forma normal (1 FN), debe ser solamente una relación propia, una matriz m por n, donde: •

Ninguna celda de la matriz está vacía;

•

El valor n cualquier columna está definido por el dominio para dicho atributo.

•

Cada tupla tiene una clave que la identifica en forma unívoca, pero dicha clave no significa orden.

La aplicación determina la relación


115

Para que una relación sea normalizada en pasos adicionales, debe encontrarse en la primera forma normal. Colocar los los datos en la primera forma normal está a cargo del diseñador de la aplicación. Estos datos se encuentran disponibles de alguna manera inicialmente. Si la aplicación existe en forma manual, o ha sido anteriormente computarizada pero no todavía como relación, el diseñador reorganiza los datos de modo de conformar una matriz 1FN. La segunda inicial más importante es la dimensión de la relación ¿cuántos componentes existen en la tupla o cuántas columnas en la tabla? ¿De qué manera se compara esto con el número de campos en el documento fuente?. La información convertida queda formada en tuplas. La próxima pregunta es cuantas tuplas representarán a la formación en esta forma. Debe notarse que el número de partes ordenadas varía de una factura o pedido a otro.

5. Segunda Forma Normal Una relación está en segunda forma normal (2FN) solamente

si todos los

atributos son dependientes en forma completa de la clave. Descripción De La Segunda Forma Normal (2 Fn) Su nombre ya nos indica el hecho de que la segunda forma normal es por lo general el próximo paso de normalización y descomposición. Para ser accesible a la normalización, y poder ser puesta en segunda forma normal, la relación debe poseer las siguientes propiedades: •

Debe estar en primera forma normal

•

Debe tener una clave compuesta.

La consecuencia inmediata de los requerimientos expresados más arriba es que cualquier relación en primera forma normal que tiene una clave simple, está automáticamente en segunda forma normal. Comencemos con un ejemplo en MICROSOFT SQL Server Gota a Gota en las Bases de Datos Relacionales Lic. Elvin German

116

forma de tabla de una relación consistente en 17 atributos, que se presenta en la figura. La misma se encuentra en primera forma normal y tiene una clave compuesta que consiste en dos atributos P y Q. Estos están subrayados en la figura para mostrar que sirven como clave. La tupla de relación puede también escribirse linealmente en forma simbólicamente: R = (A,B,C,D,E,F,G,H,I,L,M,N,O,P,Q)

6. Tercera forma normal Una relación se encuentra en tercera forma normal (EFN) si no existen transitividades entre sus atributos y si ya se encuentra en 2 FN. Descripción Una relación R a poner en tercera forma normal debe estar en la segunda forma normal. Es muy común que R sea una sub-relación; la relación original estaba en primera forma normal (para ponerla en segunda forma normal fue descompuesta en varias sub-relaciones). Estas son ahora candidatas a una descomposición adicional. Recordamos que las propiedades de la segunda forma normal (2Fn) son: •

Tenemos una matriz m x n con un valor determinado para cada componente de cada tupla.

•

Cada valor es obtenido a partir de un dominio propiamente definimos

•

Cada valor contiene una clave, ya sea simple o compuesta

•

Cada componente no clave es dependiente en forma completa de su clave.


117

En consecuencia es evidente que tenemos, o bien una clave simple, o una clave compuesta de la cual todos los componentes no clave son dependientes en forma completa. El objeto de esta fase es determinar todas las dependencias transitivas; la descomposición producirá

a continuación sub-relaciones para las cuales no

existirán dependencias transitivas -la definición de la tercera forma normal (EFN)-. Una dependencia transitiva abarca como mínimo tres componentes. Si los componentes fueran más, la dependencia múltiple puede derivarse en varias dependencias transitivas de tres componentes solamente dada una. Por lo tanto dirigiremos

nuestra

atención

a una dependencia depende ncia transitiva simple de tres

componentes. Tal dependencia puede expresarse como: Q ---> A ----> B En la cual se dice que B depende de A y que A depende de Q. La transitividad existe debido a que el valor de B depende en la última instancia del valor de Q.

7. Cuarta forma normal Dependencias multivaluadas La tercera forma normal toma en cuenta la dependencia transitiva y provee una reducción óptima universal, excepto para los casos infrecuentes de dependencia multivaluadas. Ha quedado claro en épocas recientes que es posible una reducción adicional en este caso, y esto es lo que se lleva a cabo mediante la cuarta forma normal. Existe una dependencia multivaluada cuando un valor de una variable está siempre asociado con varios valores de otra u otras variables dependientes que son siempre las mismas y están siempre presentes. Esto se ilustra mejor con el ejemplo presentado en la figura. La relación FAB describe tejidos. La variable MICROSOFT SQL Server Gota a Gota en las Bases de Datos Relacionales Lic. Elvin German

118

independiente (con respecto a las dependencias (multivaluadas) es el número de tejido FABNO. Con el se encuentra asociados un modelo (o patrón) y un color. En la figura, el tejido 345 vienen en dos modelos y entres combinaciones de modelo y color. En este caso se aplica el grafo de dependencia. Para hacer mas clara que esta es una dependencia multivariable, una cabeza doble de flecha apunta desde FABNO o PATRN y también desde FABNO a COLOR. La ineficiencia en el registro de información y se resulta clara al examinar las dos nuevas relaciones. La primera de éstas, FABPAT lista el número de tejido contra el modelo; en el segundo caso, FABCOL, lista el número de tejido contra las combinaciones de color. Dado que la regla es que todas las combinaciones combinacione s de las variables dependientes multivaluadas deben prevalecer, resulta simple reconstruir la relación FAB a partir de las dos sub-relaciones que resultaron. Descomposición Para poner una relación o sub-relación en la cuarta forma normal debe poder aplicarse lo siguiente: •

Debe estar en la tercera forma normal.

•

Deben existir una o más multidependencias.

Después de construir el grafo de dependencia, el próximo paso es ejecutar proyecciones utilizando

la variable independiente y una de las variables

multidependientes. FABPAT = proyectar FAB (FABNO, PATRN) FABCOL = proyectar FAB (FABNO, COLOR) El resultado son nuevas sub-relaciones que han sido utilizadas para ahorra espacio y permitir una más fácil actualización. Ejemplo de profesor y texto Consideremos otro ejemplo. Los cursos dictados en una escuela corresponden a un número de curso. Asociada a cada

número de curso se encuentra la


119

descripción del mismo. Para cada curso existe una selección de textos y una selección de profesores. Puede darse cualquier combinación de texto y profesor. El grafo de dependencia. El mismo nos muestra una dependencia total entre el número de curso y la descripción del curso. Existe una multidependencia entre texto y número de curso, y también entre profesor y número de curso. Para descomponer la sub-relación en sus relaciones más pequeñas, se efectúan tres proyecciones. Las sub-relaciones resultantes.


120

Compiladores - Primer Cuatrimestre-1

Recommend Documents