SQL Server by Jose Cifre

CURSO BASES DE DATOS SQL

CURSO SQL SERVER TEMARIO COMPLETO

CURSO BASES DE DATOS SQL

En este curso vamos a ver básicamente los siguientes puntos: -

Qué es SQL SERVER Instalación de SQL SERVER Crear una base de datos en SQL SERVER Objetos principales de las base de datos Lenguaje SQL. Comandos DLL y DML Fundamentos de programación Transact-SQL. Desencadenadores, Procedimientos almacenados y funciones. Administración básica de SQL SERVER

1. QUE ES SQL SERVER Antes de centrarnos en el SQL SERVER debemos tener claros los siguientes conceptos: Base de datos Es un conjunto de datos pertenecientes sistemáticamente para su posterior uso

mismo

contexto

almacenados

Base de datos Relacionales Este tipo de bases de datos son la que se usan hoy en día. La información es almacenada en tablas, y las tablas pueden relacionarse entre sí, con lo que evitamos la redundancia, y aseguramos la integridad referencial y por tanto la consistencia de la información. Sistemas de gestión de base de datos (SGBD) (En inglés: DBMS) Es un conjunto de programas que permiten crear y mantener Bases de datos, asegurando su integridad, confidencialidad y seguridad. Por tanto debe permitir: - Definir y construir una base de datos: especificar tipos, estructuras y restricciones de datos.. - Manipular la base de datos: realizar consultas, actualizarla, generar informes. Ejemplo de SGBD son Oracle y SQL Server de Microsoft . Características deseables: - Control de la redundancia: La redundancia de datos tiene varios efectos negativos (duplicar el trabajo al actualizar, deperdicia espacio en disco, puede provocar inconsistencia de datos) aunque a veces es deseable por cuestiones de rendimiento. - Restricción de los accesos no autorizados: cada usuario ha de tener unos permisos de acceso y autorización. - Cumplimiento de las restricciones de integridad: el SGBD ha de ofrecer recursos para definir y garantizar el cumplimiento de las restricciones de integridad. Ejemplos del SGBD son SQL Server, MySQL, ORACLE

INTRODUCCION A SQL SERVER

CURSO BASES DE DATOS SQL

SQL Server es un SGBDR, es decir un Sistema Gestor de Bases De Datos Relacional. Es una herramienta de servidor, lo que quiere decir que se instala y usa recursos del ordenador servidor para procesar, interpretar, ejecutar y devolver los resultados a aplicaciones cliente. Es por tanto una base de datos en la que la información es almacenada, procesada y devuelta por el ordenador servidor. Si comparamos esta forma de actuar, por ejemplo con Access, la diferencia es que en Sql-Server hay unos servicios que procesan y almacenan los datos, siendo los recursos usados por estos procesos del servidor, mientras que en el caso de Access, es casi solamente un formato de almacenamiento de datos. El esquema de comunicación podríamos, simplificándolo mucho, decir que más o menos funciona de la siguiente forma: El servidor Sql Server está a la escucha de posibles peticiones. Estas peticiones consisten en instrucciones SQL, que una de las aplicaciones de servidor se encarga de interpretar y ejecutar. La responsabilidad y el coste de esa ejecución recaen sobre los recursos del servidor. Una vez procesados y encontrados los resultados son enviados, mediante la red, los resultados al cliente que los ha demandado. Si estuviésemos ante una aplicación tipo Access, esto no sería así, el responsable de encontrar los datos, procesarlos y tomarlos sigue siendo el mismo cliente, teniendo que hacer todas estas operaciones en local, con los recursos de la CPU, local, la memoria local, etc. Esta diferencia es muy importante, nótese que si hay muchos datos, y solicitamos por ejemplo 'todos los clientes que uno de sus apellidos sea García ', en Sql-Server solo viajarán por la red los clientes demandados, mientras que en Access, tendrán que viajar todos para posteriormente seleccionar aquellos cuyo apellido sea García, ya en local. Podemos decir que SQL Server es un sistema de administración de bases de datos relacionales (RDBMS). A través de SQL Server podemos: - Administrar el almacenamiento de los datos para transacciones y análisis. - Responder a solicitudes de aplicaciones cliente. SQL Server utiliza "TRANSACT-SQL", XML, MDX, SQL-DMO para enviar solicitudes entre un cliente y SQL Server Los usuarios, no disponen de acceso directo a SQL Server sino que utilizan distintas aplicaciones que proporcionan dicho acceso En las bases de datos Sql Server, existe un ordenador servidor. Aunque Sql Server puede funcionar sobre Windows XP, Windows 2000 Profesional e incluso Windows 98, en las instalaciones profesionales, normalmente, funciona sobre sistemas operativos servidor, como Windows 2000 Server, Advanced Server,... SQL Server está totalmente integrado con Windows 2000 y aprovecha muchas de sus características: - Active directory - Seguridad - Compatibilidad con multiprocesador - Visor de sucesos de Microsoft - Servicios de componentes de Windows 2000 - Monitor de sistema de Windows 2000 - Servicios de Microsoft Internet Information server - Organización por clústeres de Windows

CURSO BASES DE DATOS SQL

2. INSTALACIÓN DE SQL SERVER Metemos el cd e instalamos siguiendo los pasos tal cual están predefinidos. Usuario local, no de dominio y también elegir instalación personalizada para que no instale los códigos de ejemplo. Tras la instalación, lo primero que vamos a hacer es verificar la correcta instalación. Para ello vamos a menú Programas  Microsoft SQL Server -> Administrador de servicios. Con esta herramienta podemos comprobar el estado de los servicios

Comprobamos que hay 2 servicios que debemos inicializar: MSSQL Server Este servicio es el motor de base de datos, este es el componente que procesa todas las sentencias del Transact-SQL y administra todos los archivos que comprometen las bases de datos del servidor, entre sus principales funciones podemos mencionar: • La asignación de recursos del servidor entre múltiples usuarios concurrentes. • Previene los problemas lógicos, como por ejemplo prevenir que los usuarios modifiquen la misma información al mismo tiempo. • Asegura la consistencia e integridad de datos. SQL Server Agent Este servicio trabaja junto al MSSQL Server para crear y administrar Alertas, Tareas (locales o multiserver) y Operadores. Entre sus principales funciones podemos mencionar: • Las alertas proveen información acerca del estado de un proceso, como por ejemplo indicar cuando finalizo una tarea con éxito o fracaso. • Este servicio incluye un motor que permite crear tareas y programarlos para que se ejecuten automáticamente. • Puede enviar correos electrónicos, puede indicar la ejecución de una tarea cuando una alerta ocurre. Otra manera de poder verificar la instalación de SQL Server es revisar los servicios que se cargan, para ello presione el botón del menú Inicio  Panel de control  Herramientas Administrativas  Servicios. Comprobar que los servicios están iniciados.

CURSO BASES DE DATOS SQL

Administrador Corporativo Es el administrador por excelencia del SQL SERVER. En la parte izquierda podemos ver grupos de servidores, y dentro los servidores en si. Nos conectamos al servidor que hay y vemos todas sus carpetas. Entramos en la carpeta Bases de Datos y vemos que lo servidores de SQL Server tienen dos tipos básicos de bases de datos: - Bases de datos de sistema - Bases de datos de usuario Las bases de datos de sistema se encargan del almacenaje de información acerca de SQL Server de forma global, las utiliza el sistema para operar y administrar su correcto funcionamiento. Durante el proceso de instalación, por defecto, de SQL Server crea, tanto bases de sistema como bases de usuario, a modo de ejemplo. Las Bases de datos del sistema son: - MASTER: Controla las bases de datos de Usuario, así como el funcionamiento global del sistema SQL Server (cuentas de usuario, variables de entorno, mensajes de error,...) - MODEL: Proporciona un prototipo para las nuevas bases de datos de usuario. - TEMPDB: Proporciona almacenamiento temporal y tablas temporales. - MSDB: Ofrece un área de almacenamiento para información de programación e historial de trabajos. - DISTRIBUTION: Almacena datos de históricos y de las transacciones que se utilizan en la replicación. Las Bases de datos de usuario son: - PUBS: Base de datos de ejemplo, para aprendizaje. - NORTHWIND: Base de datos de ejemplo, para aprendizaje. - USER1: identifica una base definida por el usuario.

CURSO BASES DE DATOS SQL

3. CREAR UNA BASE DE DATOS EN SQL SERVER Ejercicio 1 Para iniciar el asistente de creación de bases de datos podemos ir a 3 sitios: 1. Al icono de la barra de herramientas 2. Botón derecho encima de la carpeta Bases de datos 3. Ir al menú Herramientas  Herramientas Por norma general, una base de datos en SQL SERVER se compone de 2 ficheros: Un fichero primario con extensión .mdf. Aquí es donde realmente se almacena la información. Si hiciera falta mas ficheros para almacenar información, se utilizarían los llamados ficheros secundarios con extensión .ndf

Un fichero Log Transaccional o archivo de registro de transacciones, con extensión .ldf. Aquí se almacena un registro con todos los cambios realizados en la base de datos. El tamaño suele ser del 10 al 15 por cien de los ficheros de la base de datos. En la creación de la base de datos podemos establecer varias propiedades como: -

El tamaño para cada fichero de log y de datos. El minimo es de 512 KB para ambos. El tamaño especificado para el fichero principal debe ser como minimo tan grande como el principal de la base de datos model. El crecimiento de los ficheros. Podemos permitir el autocrecimiento de los ficheros. Tamaño máximo. Se puede fijar un tamaño máximo, por defecto no es conveniente.

CURSO BASES DE DATOS SQL

4. OBJETOS PRINCIPALES DE LAS BASES DE DATOS Las bases de datos se componen de varios objetos. En función del SGBD con el que trabajemos tendremos más o menos objetos. Tablas Es el objeto donde realmente se almacena la información de la BD. Éstas están organizadas en filas o registros y columnas o campos. Cada campo tiene un nombre y un tipo que identifica el tipo de información que puede contener. Los tipos de datos más comunes en SQL SERVER son: bit Varchar Text Datetime Float Int Money

Para Para Para Para Para Para Para

almacenar almacenar almacenar almacenar almacenar almacenar almacenar

datos de tipo Verdadero / Falso datos alfanuméricos menores de 8.192 caracteres datos alfanuméricos mayores de 8.192 caracteres fechas. números decimales. números enteros. cantidades monetarias

En función del tipo de campo, al crearlo también se puede añadir otras características como la longitud, restricciones o referencias a otras tablas. Al crear una tabla, en la mayoría de casos, es fundamental asignar un campo o conjunto de campos como clave principal de la tabla o PRIMARY KEY. Este campo(s) tendrá la propiedad de que será único en cada registro y por lo tanto nos permitirá identificar a un registro de forma unívoca. La clave principal no permite valores nulos ni duplicados. Para crear una nueva tabla nos ponemos encima de la bd, botón derecho nueva tabla, o nos ponemos encima de tablas botón derecho nueva tabla o situarnos encima de tablas y pulsar el botón estrella de la barra de herramientas. Debemos introducir el nombre del campo, el tipo de datos, la longitud si se puede y si queremos permitir valores nulos. Una vez creada: Para Modificarla, nos ponemos encima de ella, botón derecho, Diseñar tabla. Para Añadir datos, botón derecho, Abrir tabla, Devolver todas las filas. Después de añadir un registro hay que bajar con el ratón al registro siguiente o pulsar ctrl+ F4 para que se almacene la información. Para eliminar datos, nos ponemos en la parte izquierda de la tabla, seleccionamos el registro, y eliminamos Ejercicio 2 Mediante la propiedad IDENTITY conseguimos que el valor del campo se incremente automáticamente en un valor determinado por nosotros, evitando así al usuario tener que comprobar todos los valores introducidos con el fin de no repetir ninguno. Solo puede haber un campo con la propiedad IDENTITY por tabla. Podemos combinar un campo que sea PRIMARY KEY y asignarle la propiedad IDENTITY Ej. CodCliente int IDENTITY(1,1) PRIMARY KEY El campo CodCliente será entero, empezará en el 1 (primer 1), y aumentará de 1 en 1 (segundo 1). Será clave principal por lo que no admitirá ni duplicados ni nulos. Ejercicio: Crear una base de datos llamada Ventas con una tabla llamada Clientes. Esta tabla consta de 2 campos: CodCliente: Int, PK, Identity (1,1) Nombre: Ejercicio 3

CURSO BASES DE DATOS SQL

Una forma de asegurar la integridad de los datos es mediante las restricciones. Las restricciones más habituales que se suelen poner a los campos son: - No permitir duplicados. Para ello utilizamos la restricción UNIQUE. También se puede combinar con la propiedad IDENTITY. La diferencia con PRIMARY KEY es que UNIQUE admite nulos. La propiedad índice no crea índices. - No permitir valores nulos para evitar posibles errores - Utilizar un valor por defecto para no obligar al usuario a introducir un valor. - Restricciones CHECK como o Utilizar validaciones lógicas a los campos para evitar la introducción de valores ilógicos en un campo como puede ser un precio negativo. Ej Importe >= 0 o Imponer que el dato introducido sea uno de los contenidos en una serie de datos. Ej. Sexo IN (‘Masculino’, ‘ Femenino’) o Utilizar una máscara de entrada.  Ej. Banco LIKE '[0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9] [0-9][0-9] [09][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'  Ej. DNI LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][A-Z]' OR DNI LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]-[A-Z]' Código

Función

Dígito (0-9). Introducción obligatoria. No permite signos

Dígito o espacio. Introducción opcional.Las posiciones en blanco se convierten en espacios y se permiten los signos

Dígito o espacio. Introducción opcional. No permite signos

Letra (A-Z). Introducción obligatoria

Letra (A-Z). Introducción opcional.

Letra o dígito. Introducción obligatoria

Letra o dígito. Introducción opcional

Cualquier carácter o espacio. Introducción obligatoria

Cualquier carácter o espacio. Introducción opcional

.,:;-/

Marcador de posición decimal y separador de miles, fecha y hora.

Convierte los caracteres en minúsculas

Convierte los caracteres en mayúsculas

Hace que la máscara de entrada se rellene de derecha a izquierda

Hace que el carácter que venga a continuación se presente como un carácter literal

CURSO BASES DE DATOS SQL

Máscara de entrada (000) 000-0000

Valores de ejemplo (206) 555-0248 (206) 555-0248 ( ) 555-0248 (206) 555-TELE -20 2000 GREENGR339M3 MAY R 452B7 T2F 8M4 9811598115-3007 María Brendan SSN 555-55-5555 DB51392-0493

(999) 999-9999 (000) AAA-AAAA #999 >L????L?000L0 >L0L 0L0 00000-9999 >L<?????????????? SSN 000-00-0000 >LL00000-0000

Para crear restricciones check, dentro del diseño de tabla o hacemos botón derecho, restricciones Check o pulsamos el último botón de la barra de herramientas que es administrar restricciones. Para crearla, elegimos nueva, mantenemos el nombre y escribimos cual es la restricción, ejemplo edad < 100 Ejercicio 4, 5 y 6 Relaciones Son un tipo de restricción que se utiliza para relacionar unas tablas con otras. 2 tablas se relacionan siempre mediante un campo común en ambas. Gracias a las relaciones evitamos duplicar información, ya que relacionando 2 tablas obtenemos la información necesaria en una tabla principal desde otras tablas auxiliares. Ej. Tenemos una tabla con Clientes y otra con Facturas. Cuando hacemos una factura necesitamos toda la información del cliente, nombre, dirección, teléfono, … Para evitar duplicar información, lo que se hace es un campo común en ambas tablas por el que podamos relacionarlas. En este caso, en la tabla de clientes tendremos un campo que haga referencia al código de cliente, llamado CodCliente y en la tabla de facturas tendremos también un campo que haga referencia al código de cliente aunque no tiene porque llamarse igual, por ejemplo CodClienteFac. Relacionando ambas tablas por este campo, tenemos en la tabla de

CURSO BASES DE DATOS SQL

facturas toda la información del cliente, sin duplicar información con lo que conseguimos 2 cosas: o Ahorrar espacio. o Evitar errores que se puedan cometer al introducir la información Para crear una nueva relación, en el diseño de tabla elegimos botón derecho, relaciones o pinchamos en el botón correspondiente de la barra de herramientas. Para relacionar 2 tablas debemos ver cuál es la tabla principal, la que tiene el campo único que no se repite, y la secundaria. Escoger en ambas el campo común. Marcaremos normalmente la opción de actualizar en cascada y eliminar en cascada. Para poder ver una tabla de quien depende y quien depende de ella podemos ver las dependencias, seleccionando la tabla, botón derecho, ver dependencias. Ejercicio 7 y 8 Diagramas Representan gráficamente la estructura de la BD. Podemos ver sus tablas, su diseño y las relaciones. Desde el diagrama podemos trabaja más fácilmente para crear relaciones, índices y tal. Enseñar las <> visiones de las tablas, el zoom, como se ponen PK’s, como se hacen y modifican relaciones. Ejercicio 9 Columnas calculadas Es un tipo de columna donde la información se obtiene como cálculo de otras columnas mediante una fórmula. El tipo de datos se pone solo en función de la fórmula y los campos usados para ella. Consultas El principal motivo por el que guardamos la información es para poder tratar con ella posteriormente. Mediante el asistente de consultas de SQL SERVER podemos tratar la información de 4 tipos distintos: 1. Consulta simple, en la que recuperamos la información de la bd que cumpla con unas condiciones determinadas. 2. Consulta de Inserción de datos, que puede ser Insertar datos EN otra tabla o insertar datos DE otra tabla. 3. Consulta de Actualización, en la que actualizamos los valores de ciertos campos. 4. Consulta de Eliminación, mediante la que podemos eliminar registros de las tablas. Realmente, el asistente de consultas lo que hace es generar una sentencia SQL, que lo que realmente ejecutamos. Enseñar como funciona el asistente de consultas. (B. Derecho -> Abrir Tabla -> Consultas)

Ejercicio 10. Vistas Una vista es una tabla virtual que puede mostrar información de una o mas tablas. Se trabaja con ellas como si fueran una tabla mas, con la diferencia de que las vistas NO almacenan información, solo muestran la información de otras tablas. Enseñar como funciona el asistente de vistas. Establecer órdenes en las columnas. Ejercicio 11. Índices Son unas estructuras creadas a partir de tablas o vistas. Se asocian a campos y tienen la misma información que éstos. La finalidad de los índices es acelerar las operaciones de búsqueda. Ejemplo de funcionamiento: En la tabla de clientes, la PK, por defecto, crea un índice, lo que quiere decir que una búsqueda por ColCliente será rápida ya que los datos ser ordenan internamente por este campo. Sin embargo, una búsqueda por DNI será mas costosa, ya que si busco un cliente con

CURSO BASES DE DATOS SQL

DNI = 11.111.111-J por ejemplo, el proceso que seguirá será mirar todos los DNI’s desde el primero al último ya que los registros no están ordenados por DNI. Si tenemos pocos registros, el proceso será rápido, pero si hay muchos, el proceso será más lento. La velocidad de búsqueda podemos incrementarla considerablemente si creamos un índice asociado al campo DNI. Al crear este índice, se crea una tabla asociada con los campos DNI y PK (índice agrupado), en este caso, DNI Y CodCliente, ordenado por DNI. Cuando hagamos una búsqueda por DNI NO se hará en la tabla de clientes directamente, sino que se hará en esta nueva tabla, que al estar ordenado por DNI, se encuentra rápidamente, obtenemos el valor de la PK y buscamos con este valor en la tabla de clientes. Realmente, lo que hacemos son 2 búsquedas, pero más rápidas que buscar directamente por DNI. Lógicamente, esto sale rentable si tenemos muchos registros. Si tenemos muy pocos no vale la pena, incluso puede que tarde más una búsqueda con índice que sin él.

Los índices tienen la ventaja de mejorar sensiblemente la velocidad de búsqueda, pero al mismo tiempo disminuyen la velocidad al añadir o modificar un registro en la tabla principal, ya que al mismo tiempo que modificamos los valores de la tabla hay que modificar los valores en la tabla del índice. Por lo tanto, cuantos más índices tengamos en una tabla, más lentos serán estos procesos. Esta es la razón de porque no deben estar todos los campos de la tabla indexados. Hay que buscar un equilibrio y poner índices solo donde sea necesario. En el caso de no tener un índice agrupado, la tabla de índice contiene el dato ordenado y un identificador de fila, que es un puntero apuntando a la fila deseada de la tabla.

CONSEJOS SOBRE EL USO DE ÍNDICES

- Si una consulta accede a más del 20% de las filas de una tabla, un recorrido de tabla es más efectivo que usar un índice. - Se debe tratar que los índice tengan la mayor selectividad posible (es decir el número de filas obtenido por cada valor del clave del índice). A mayor selectividad del índice mayor probabilidad de que sea usado por el optimizador de consultas. - Se deben usar los índices con moderación, es decir, unos pocos índices pueden ser útiles pero demasiados índices pueden afectar negativamente al rendimiento porque hay que mantenerlos actualizados cada vez que se realizan operación de insercción, actualización y borrado en los datos. - No poner índices en tablas pequeñas.

CURSO BASES DE DATOS SQL

- Utilizar el menor número posible de columnas en el índice (índices estrechos), ya que estos ocupan menos espacio y necesitan menos sobrecarga de mantenimiento Nota: Un identificador de fila es un puntero que construye automáticamente SQL Server a partir del identificador de archivo, el número de página y el número de la fila de datos. Con el identificador de la fila, se pueden recuperar los datos con únicamente una operación de entrada/salida adicional. Debido a que se conoce qué página hay que recuperar y SQL Server conoce dónde está esa página, la página se lee en memoria con una única solicitud de entrada/salida La simplicidad de este proceso es la razón por la que los índices son tan eficientes a la hora de recuperar los datos y de proporcionar tan gran mejora en el rendimiento Tipos de índices Índices simples Un índice simple es un índice que se define en una sola columna de la tabla. Se debe hacer referencia a esta columna en la cláusula WHERE de la instrucción para que se use este índice para cumplir la instrucción. Un índice simple puede ser efectivo dependiendo del tipo de datos que se están ordenando, del número de elementos únicos de la columna y del tipo de instrucciones SQL que se usen. En otros casos se necesita un índice compuesto. Por ejemplo, si se está indexando una libreta de direcciones con miles de nombres y direcciones, la columna state no es una buena candidata para un índice simple ya que podría haber muchas entradas del mismo estado. Sin embargo, al añadir las columnas street y city al índice, por tanto creando un índice compuesto, se puede hacer que cada entrada sea casi única. Este paso puede ser útil si se usan consultas que buscan filas a través de la dirección. Índices compuestos Un índice compuesto es un índice que se define en más de una columna. Se puede acceder a un índice compuesto usando una o más claves de índice. Con SQL Server 2000, un índice puede abarcar hasta 16 columnas y sus columnas clave pueden ser de hasta 900 bytes. Para las consultas que formen parte de un índice compuesto, no es necesario que se incluyan todas las claves de índice en la cláusula WHERE de una instrucción SQL, pero es sensato utilizar más de una de ellas. Por ejemplo, si se crea un índice en las columnas a, b y c de una tabla, se puede acceder al índice usando una instrucción SELECT que contenga (a AND b AND c) o (a AND b) o a. Por supuesto, el uso de una cláusula WHERE más restrictiva, como la que contiene a AND b AND c, proporciona un mejor rendimiento. Recuperará menos filas de la base de datos ya que identifica una fila más específicamente. Si se usa a AND b o solamente a, sólo se iniciará un recorrido del índice. Un recorrido de índice ocurre debido a que más de una entrada de índice satisface la búsqueda. En un recorrido de índice, los nodos de un índice se exploran para recuperar vanos registros de datos. Además, el índice incluye parcialmente el valor que se ha seleccionado. Por ejemplo si el índice se ha creado para las columnas a, b y c, y la consulta solamente especifica la columna a, se devolverán todas las filas que satisfagan ese valor de a, para todos los valores de b y c. Debido a que las columnas en que se basa un índice están ordenadas numéricamente, el optimizador de consultas de SQL Server puede determinar el rango de páginas índices que pueden contener los datos deseados. Una vez que se conocen las páginas de comienzo y fin, se recuperan todas las páginas que contienen los valores de datos y se exploran los datos demandados. Nota: El índice solamente se puede utilizar si al menos una de las claves del índice están en la cláusula WHERE de la consulta SQL. Continuando con el ejemplo anterior, una consulta con solamente un nombre o un número de teléfono en la cláusula WHERE no usa el índice. En la

CURSO BASES DE DATOS SQL

mayoría de los casos un recorrido de índice puede ser bastante eficiente; sin embargo, si se accede a más del 20 por ciento de las filas de la tabla, es más eficiente un recorrido de tabla, en la cual se leen todas las filas de la tabla. La eficiencia de las consultas que usan un índice depende de cómo su utilice el índice y en la unicidad del índice. Unicidad de los índices En SQL Server se puede definir un índice como único o no único. En un índice único, cada valor de la clave del índice debe ser único. Un índice no único permite que las claves del índice estén duplicadas en los datos de la tabla. La efectividad, o eficiencia, de un índice no único depende de la selectividad del índice. Índices únicos Un índice único contiene solamente una fila de datos por cada clave del índice (en otras palabras, los valores de la clave del índice no pueden aparecer en el índice más de una vez). Los índices únicos trabajan bien debido a que garantizan que solamente se necesita una operación de entrada/salida más para recuperar los datos solicitados. SQL Server obliga a que se cumpla la propiedad de unicidad (UNIQUE) de un índice de una columna o combinación de columnas que constituyen la clave del índice. SQL no permite que se inserte en la base de datos un valor de clave duplicado. Si se intenta hacer, se produce un error. SQL Server crea índices únicos cuando se crea o una restricción PRIMARY KEY o una restricción UNIQUE en la tabla. Un índice se puede hacer único solamente si los datos son únicos. Si los datos de la columna no contienen valores únicos, todavía se puede crear un índice único usando un índice compuesto. Por ejemplo, la columna last name podría no ser única, pero combinando los datos de esta columna con las columnas first name y middle name, se podría crear un índice único en la tabla. Nota: Si se intenta insertar una fila en una tabla que cree un valor duplicado de la clave de índice de un índice único, la inserción fallará. Índices no únicos Un índice no único trabaja de la misma forma que un índice único, salvo que puede contener valores duplicados en los nodos hoja. Todos los valores duplicados se recuperan si cumplen los criterios especificados en la instrucción SELECT. Un índice no único no es tan eficiente como un índice único debido a que necesita procesamiento adicional (operaciones adicionales de entrada/salida) para recuperar los datos requeridos. Pero debido a que algunas aplicaciones necesitan utilizar claves duplicadas, algunas veces es imposible crear un índice único. En estos casos, un índice no único frecuentemente es mejor que no tener ningún índice.

CURSO BASES DE DATOS SQL

5. LENGUAJE SQL. COMANDOS DLL Y DML Que es SQL Es de eso de lo que trata el Structured Query Language que no es más que un lenguaje estándar de comunicación con bases de datos. Hablamos por tanto de un lenguaje normalizado que nos permite trabajar con cualquier tipo de lenguaje (ASP o PHP) en combinación con cualquier tipo de base de datos (MS Access, SQL Server, MySQL...). SQL en SQL SERVER Todas las operaciones que hacemos con los asistentes podemos hacerlo también con sentencias SQL. En SQL SERVER, el lugar que utilizamos para introducir las sentencias SQL es el analizador de consultas SQL. Comentar que a la izquierda vemos los objetos de bd, con F5 se ejecuta, distintas vista (grid, etc). Para hacer comentarios en el editor hay 2 formas: -- Esto es un comentario /* Esto es un comentario voy */ Podemos poner varias instrucciones una debajo de otra. Al pulsar F5 se ejecutarán una detrás de otra. Si solo queremos ejecutar una parte de las instrucciones, las subrayamos y al pulsar F5 solo se ejecuta ese trozo. Sentencias DLL Su finalidad es definir las estructuras de los datos. Con este tipo de sentencias podemos crear base de datos, tablas, índices. También podemos modificar y borrar las estructuras. Estas sentencias se basan en las instrucciones CREATE, ALTER y DROP. Sentencias DML Son sentencias que tratan directamente con los datos. Se basan en las instrucciones SELECT, UPDATE, DELETE o INSERT. Sentencias DDL Crear, Modificar, Renombrar y eliminar Bases de Datos Crear Base de datos CREATE DATABASE NombreBD ON (Parámetros) LOG ON (Parámetros) Los parámetros detrás de ON harán referencia al fichero de datos y los parámetros detrás de LOG ON harán referencia al fichero LOG. Estos parámetros son opcionales y son: NAME = Especifica el nombre del archivo. Nombre Lógico FILENAME = Nombre completo del archivo, Nombre Físico SIZE = Tamaño inicial por defecto en MB MAXSIZE = Tamaño Máximo por defecto en MB FILEGROWTH = Incremento Crecimiento. Se puede expresar en MB, KB o %. Por defecto es 10 % Quedaría de la siguiente forma CREATE DATABASE NombreBaseDatos ( ON Primary NAME = nombreArchivoLógico,

CURSO BASES DE DATOS SQL

FILENAME = 'nombreArchivoSO', SIZE = tamaño, MAXSIZE = tamañoMáximo, FILEGROWTH = incrementoCrecimiento ) ( LOG ON NAME = nombreArchivoLógico, FILENAME = 'nombreArchivoSO', SIZE = tamaño, MAXSIZE = TamañoMáximo, FILEGROWTH = incrementoCrecimiento ) SQL Server utiliza una copia de model para inicializar la bd. Cualquier objeto que creemos en model se copiara a las nuevas bd’s. Para ver un informe de la bd creada ejecutamos sp_helpdb En la tabla sysdatabases de master tenemos todas las bases de datos. Ejemplo 1 Crear la base de datos Prueba1 con los parámetros En forma predeterminada. Create Database Prueba1 Verifique la creación de la base de datos y note que automáticamente SQL Server asignó tamaños y nombres lógicos para los archivos. Para ello emplee el siguiente procedimiento almacenado del sistema: Sp_HelpDB Prueba1 Ejemplo 2 Crear la base de datos Prueba2 con un archivo de datos de 10Mb, un tamaño máximo de 20Mb y un crecimiento de 1Mb., el archivo de registro debe asumir los valores por defecto Create Database Prueba2 On (NAME = "Prueba2_Data", FILENAME = "C:\Archivos de programa\Microsoft SQL Server\MSSQL\data\Prueba2 _Data.Mdf", SIZE = 10Mb, MAXSIZE = 20Mb, FILEGROWTH= 1Mb) Verifique la creación de la base de datos anterior: Sp_HelpDB Prueba2 Ejemplo 3 Crear la base de datos Prueba3 especificando un archivo de datos con un tamaño inicial de 15Mb, un tamaño máximo de 30Mb y un crecimiento de 5Mb., el archivo de registro debe tener un tamaño inicial de 5MB y uno máximo de 10MB, el crecimiento debe ser de 1MB. Create Database Prueba3 On (NAME = "Prueba3_Data", FILENAME = "C:\Archivos de programa\Microsoft SQL Server\MSSQL\data\Prueba3_Data.Mdf", SIZE = 15Mb,

CURSO BASES DE DATOS SQL

MAXSIZE = 30Mb, FILEGROWTH= 5Mb) Log On (NAME = "Prueba3_Log", FILENAME = "C:\Archivos de programa\Microsoft SQL Server\MSSQL\data\Prueba3 _Log.Ldf", SIZE = 5Mb, MAXSIZE = 10Mb, FILEGROWTH= 1Mb) Otra de las formas de comprobar la creación de las bases de datos es mostrando las filas de la tabla del sistema SysDatabases. Use Master GO Select DbID, Name From SysDatabases GO Modificar bases de datos ALTER DATABASE NombreDB ALTER DATABASE NombreDB MODIFY FILE ( NAME = ‘NombreFichero’, FILENAME, FILEGROWTH, MAXSIZE o SIZE) Solo se puede modificar una propiedad a la vez. Ejemplo 4 Cambiar el tamaño maximo a 40 MB ALTER DATABASE Prueba2 MODIFY FILE ( NAME = ‘Prueba2_Data’, MAXSIZE = 40Mb) GO -- Para verificar el cambio Sp_HelpFile Prueba2_Data Eliminar bases de datos DROP DATABASE NombreDB1, Nombredb2, ….. Ejemplo 5 Para saber la bd’s que tenemos podemos usar Select DbID, Name From SysDatabases Eliminar las bd’s prueba1 y prueba 2 y comprobar que ya no están. Renombrar bases de datos Se utiliza Sp_RenameDB NombreAnterior, NombreNuevo Ejemplo 5 Renombrar la bd Prueba3 a LaUltimaBD Eliminar LaUltimaBD Sp_RenameDB "Prueba3", "NuevoNombre" DROP DATABASE LaUltimaBD

CURSO BASES DE DATOS SQL

CREAR, MODIFICAR Y BORRAR TABLAS Crear tablas La sentencia CREATE TABLE sirve para crear la estructura de una tabla, no para rellenarla con datos. Nos permite definir las columnas que tiene y ciertas restricciones que deben cumplir esas columnas. En un principio, la sintaxis para la instrucción CREATE TABLE es CREATE TABLE NombreTabla (NomColumna1 Tipo Dato (IDENTITY (x,y)) (NULL | NOT NULL), (NomColumna2 Tipo Dato (NULL | NOT NULL), …………… ) Tras el tipo de dato, ponemos las propiedades tales como si es un campo Identity o si es o no nulo. Por defecto, los campos admite nulos, por lo que no sería necesario indicarlos. Cuando en el analizador de sentencias SQL vamos a poner la instrucción de crear tabla, muchas veces es conveniente poner la instrucción de eliminar tabla, ya que si la creamos y ya existe nos da un error. Para eliminar una tabla utilizamos la intrucción: DROP TABLE NombreTabla También nos dará un error si tratamos de eliminar una tabla que no existe. Para que no se dé este error, lo que haremos es preguntar si existe antes de borrar. Para ello haremos lo siguiente: IF OBJECT_ID(' NombreTabla ') IS NOT NULL DROP TABLE NombreTabla Con lo que finalmente, utilizaremos IF OBJECT_ID(' NombreTabla ') IS NOT NULL DROP TABLE NombreTabla CREATE TABLE NombreTabla (NomColumna1 Tipo Dato (IDENTITY (x,y)) (NULL | NOT NULL), (NomColumna2 Tipo Dato (NULL | NOT NULL), …………… ) Finalmente, indicar que en la parte superior de la pantalla hay un desplegable donde podemos indicar con que base de datos vamos a trabajar. A veces, se nos puede olvidar o no darnos cuenta de que estamos trabajando en otra base de datos. Para no cometer este error, es conveniente que la primera instrucción que pongamos en en el ASS (Analizador de Sentencias SQL) sea USE NomBaseDatos Con lo que aseguramos que trabajamos con la correcta. Ejercicios 1, 2, 3, 4, 5 y 6

CURSO BASES DE DATOS SQL

MODIFICAR TABLAS Modificar una tabla realmente es modificar uno de sus campos, añadir o borrar uno. Utilizamos la instrucción: Para añadir campos ALTER TABLE NombreTabla ADD NombreCampo1 Tipo1, NombreCampo2 Tipo2, ….. Para Modificar campos ALTER TABLE NombreTabla ALTER Column NombreCampo Tipo Para Borrar campos ALTER TABLE NombreTabla DROP Column NombreCampo1, NombreCampo2, …. Ejercicios 7 AÑADIR CONSTRAINTS Vamos a ver como podemos añadir restricciones (CONSTRAINT) a una tabla PRIMARY KEY Seguiremos una construcción del tipo ALTER TABLE nombretabla ADD CONSTRAINT nombreConstraint PRIMARY KEY (campo1, campo2,..) Seguiremos la siguiente nomenclatura: PK_NombreTabla FOREIGN KEY ALTER TABLE NombreTabla1 ADD CONSTRAINT NombreConstraint FOREIGN KEY (campo1, campo2,..) REFERENCES NombreTabla2 (Campo1, Campo2,..) Seguiremos la siguiente nomenclatura: FK_NombreTabla1_NombreTabla2 Tabla2 será la tabla donde está el índice. Si se relaciona con la clave principal no es necesario poner los campos en NombreTabla2 Ejemplo: use ejemplo alter table Pedidos Add constraint fk_Pedidos_clientes foreign key (CodCliente) references clientes

CURSO BASES DE DATOS SQL

CHECK CONSTRAINTS ALTER TABLE NombreTabla ADD CONSTRAINT NombreConstraint CHECK (Regla a Validar) Seguiremos la siguiente nomenclatura: CK_NombreTabla_ReferenciaAlaregla DEFAULT CONSTRAINTS ALTER TABLE NombreTabla ADD CONSTRAINT NombreConstraint DEFAULT ValorPredeterminado FOR NombreCampo Seguiremos la siguiente nomenclatura: DF_NombreTabla_Campo Cuando una definición DEFAULT es agregada a una columna existente en una tabla, SQL Server (por defecto) aplica el valor por defecto solo a las filas nuevas que sean ingresadas de ahí en adelante. Las filas que tomaron el valor por defecto anterior no son afectadas. Cuando se agrega una nueva columna a una tabla existente, sin embargo, se puede especificar al SQL Server que inserte el valor por defecto (especificado en la definición de la nueva columna) en vez de un valor nulo en la nueva columna para las filas preexistentes de la tabla. Para ello debemos añadir al final WITH VALUES UNIQUE ALTER TABLE NombreTabla ADD CONSTRAINT NombreConstraint UNIQUE (NombreCampo) Seguiremos la siguiente nomenclatura: UQ_NombreTabla_Campo Ejercicios 8, 9, 10, 11 y 12

NOTAS: Cuando ponemos la restricción FOREIGN KEY, podemos indicar que sucederá con los datos que dependen de la tabla principal, tanto en el caso de borrado como en el de actualización. − Modo de borrado: Para determinar la acción que se debe realizar cuando se elimina una fila referenciada, se debe utilizar una regla de supresión opcional para la relación CASCADE − Modo de modificación: Una regla de actualización para la relación, que determina la acción que se debe realizar cuando se modifica la clave candidata de la fila referenciada CASCADE Por defecto la acción es NO ACTION

CURSO BASES DE DATOS SQL

Ejemplo:

CREATE TABLE areas ( codigo char(3) not null, nombre char(55) not null, departamento char(3) not null, Primary key(codigo), Foreign key(departamento) REFERENCES departamentos ON DELETE CASCADE ON UPDATE CASCADE); CREAR TABLAS INDICANDO LAS RESTRICCIONES AL MISMO TIEMPO. Al mismo tiempo que creamos las tablas podemos crear las restricciones. Cuando lo hacemos así, debemos distinguir 2 tipos de restricciones: -

Las de tipo 1, solo afectan a un campo y a un campo solo se le puede asignar una restricción. Las de tipo 2, que afectan a varios campos, o también la utilizamos cuando un campo tiene mas de una restricción.

Restricciones de Tipo 1 Su sintaxis sería CREATE TABLE NomTabla ( Campo1 Tipo CONSTRAINT NomConstraint PRIMARY KEY, Campo2 Tipo CONSTRAINT NomConstraint FOREIGN KEY REFERENCES NomTabla2 (Campo Tabla2), Campo3 Tipo CONSTRAINT NomConstrain UNIQUE, Campo4 Tipo CONSTRAINT NomConstrain CHECK (Regla a validar) ) Si no queremos asignar nosotros el nombre a la restricción, podemos dejar que SQL asigne los nombres que crea oportunos. Para no poner nombres debemos quitar ‘CONSTRAINT NomConstraint’ La sintaxis anterior quedaría de la forma: CREATE TABLE NomTabla ( Campo1 Tipo PRIMARY KEY, Campo2 Tipo FOREIGN KEY REFERENCES NomTabla2 (Campo Tabla2), Campo3 Tipo UNIQUE, Campo4 Tipo CHECK (Regla a validar) ) Podemos alternar unos campos dando nombre y otros no. Siempre detrás de estas restricciones podemos añadir IDENTITY, DEFAULT y NULL / NOT NULL Restricciones de Tipo 2 Su sintaxis sería CREATE TABLE NomTabla ( Campo1 Tipo, Campo2 Tipo, Campo3 Tipo, Campo4 Tipo , CONSTRAINT NomConstraint PRIMARY KEY (NomCampo1, NomCampo2,…), CONSTRAINT NomConstraint FOREIGN KEY (NomCampo1, NomCampo2,…) REFERENCES NomTabla2 (Campos Tabla2),

CURSO BASES DE DATOS SQL

)

CONSTRAINT NomConstraint UNIQUE (NomCampo1, NomCampo2,…), CONSTRAINT NomConstraint CHECK (Regla a validar)

Como en el caso de restricciones de Tipo 1, sino queremos dar nombre a las restricciones quitamos ‘CONSTRAINT NomConstraint) Ejercicio 13 ELIMINAR CONSTRAINTS Por norma general la sintaxis para eliminar una restricción será la siguiente: ALTER TABLE NombreTabla DROP CONSTRAINT NombreConstraint CREAR ÍNDICES La sintaxis para crear índices será la siguiente: CREATE [UNIQUE] INDEX NomIndex ON NomTabla (NomCampo [ASC|DESC]) ELIMINAR ÍNDICES Por norma general la sintaxis para eliminar un índice será la siguiente: DROP INDEX NombreIndice ON NomTabla Ejercicio 14

CURSO BASES DE DATOS SQL

NOTAS Para renombrar objetos utilizamos exec sp_rename [ @objname = ] 'object_name' , [ @newname = ] 'new_name' [ , [ @objtype = ] 'object_type' ] Ejemplo: exec sp_rename 'clientes', 'clientenou' exec sp_rename 'clientenou.Direccion', 'Dire' EXEC SP_RENAME 'EMPLEADOS.CODPOSTAL', 'COD' Comentar que en el browser podemos ver las restricciones, índices y tal Para ver los índices de una tabla Exec sp_helpindex tabla Para ver los objetos de la bd Exec sp_help Para ver los objetos de una tabla Exec sp_help tabla Para ver los objetos de la base de datos Exec sp_help Para hacer una copia de seguridad de la bd BACKUP DATABASE Ejemplo TO DISK = 'c:\backup\Ejemplo.bak' Para eliminar una base de datos DROP DATABASE Ejemplo Para recuperar una base de datos RESTORE DATABASE Ejemplo FROM DISK = 'c:\backup\Ejemplo.bak'

CURSO BASES DE DATOS SQL

Para saber los objetos de la bd select * from sysobjects select name as 'ForeignKeyConstraint Name' from sysobjects where xtype = 'PK' or xtype = 'U' By changing the object type, the following objects can be seen. • • • • • • • • • • • • • • • • • • • •

PK = PRIMARY KEY constraint C = CHECK constraint D = DEFAULT (constraint or stand-alone) F = FOREIGN KEY constraint UQ = UNIQUE constraint AF = Aggregate function (CLR) FS = Assembly (CLR) scalar function FT = Assembly (CLR) table-valued function PC = Assembly (CLR) stored procedure FN = SQL scalar function IF = SQL inlined table-valued function TF = SQL table-valued-function R = Rule (old-style, stand-alone) SN = Synonym TR = SQL DML trigger U = Table (user-defined) P = SQL stored procedure V = View X = Extended stored procedure IT = Internal table

select 'FK', o1.name as [FKName], o2.name [FKeyTable], c1.name, o3.name, c2.name from sysforeignkeys fk join sysobjects o1 on fk.constid = o1.id join sysobjects o2 on fk.fkeyid = o2.id join sysobjects o3 on fk.rkeyid = o3.id join syscolumns c1 on fk.fkey = c1.colid and fk.fkeyid = c1.id join syscolumns c2 on fk.rkey = c2.colid and fk.rkeyid = c2.id order by 4

CURSO BASES DE DATOS SQL

Sentencias DML Son sentencias que tratan directamente con los datos. Se basan en las instrucciones SELECT, UPDATE, DELETE o INSERT. INSERT INTO Agrega un registro a una tabla. Se puede utilizar de 2 formas: -

Insertar un único registro: La sintaxis es INSERT INTO NomTabla (Campo1, Campo2 ,… ) VALUES (Valor1, Valor2,..) Si vamos a poner valores para todos los campos no es necesario poner el nombre de los campos. La sintaxis quedaría: INSERT INTO NomTabla VALUES (Valor1, Valor2,..) Los valores alfanuméricos los introducimos entrecomillados Los numéricos sin comillas. Las fechas con comillas y con el formato ‘yyyymmdd’ . Ejemplo:

INSERT INTO Pedidos(NumPedido, CodCliente, CodEmpleado, FechaPedido, FechaEntregaPrevista) VALUES (1,'AAA',1,'20080220','20080221')

Crear tabla con datos También podemos crear una tabla al mismo tiempo que la llenamos de datos, con lo que los campos que formen la tabla serán del tipo de los datos que estamos añadiendo. La instrucción para hacer esto es: SELECT Campos INTO TablaNueva FROM TablaOrigen Ej: SELECT Nombre, Direccion INTO NomClientes FROM Clientes Ejercicio 1 BLOQUE 3 -

Insertar varios registros de otra tabla La sintaxis es la siguiente: INSERT INTO NomTabla (Campo1, Campo2 ,… ) SELECT Campo1, Campo2,... FROM Tabla Si la información está en otra base de datos, en lugar de tabla pondremos servidor1.BaseDatos1.dbo.Tabla Si vamos a poner valores para todos los campos no es necesario poner el nombre de los campos. La sintaxis quedaría: INSERT INTO NomTabla SELECT Campo1, Campo2,... FROM Tabla Ejemplo:

CURSO BASES DE DATOS SQL

INSERT INTO Categorias(Nombre) SELECT CategoryName FROM portatil_lp.Northwind.dbo.Categories Ejercicio 2 BLOQUE 3 SELECT Con la instrucción SELECT hacemos consultas propiamente dichas, para obtener información de la base de datos. La sintaxis más básica es la siguiente: SELECT Campos FROM Tabla La instrucción SELECT seguida de los campos que queremos obtener, luego la cláusula FROM y la tabla de origen. Si queremos obtener todos los campos de una tabla, haremos SELECT * FROM Tabla Después de tabla podemos añadir por que campo o campos queremos ordenar la selección. Los campos por los que ordenamos no deben ser necesariamente campos de la consulta. La sintaxis es: SELECT Campos FROM Tabla ORDER BY Campos [ASC|DESC] Si no indicamos nada, el orden será Ascendente. Hacer los ejercicios del 3 al 7 Funciones Asociadas a datos de tipo carácter Para ver los siguientes ejemplos, crearemos la tabla Usuarios con los valores indicados: Crear una base de datos llamada Ejemplo2 use ejemplo2 create table Usuarios ( Nombre varchar(100) , DNI Varchar(10), FechaNacimiento DateTime ) INSERT INTO Usuarios VALUES ('pepe lópez','123','19790215') INSERT INTO Usuarios VALUES ('pepe garcía','456','19730706') INSERT INTO Usuarios VALUES ('MANUEL AMO','789','19750913') Existen algunas funciones para trabajar con datos carácter como son: LOWER (Campo) Pone en minúscula el campo indicado Ej: SELECT LOWER (nombre) from usuarios; UPPER (Campo) Pone en mayúscula el campo indicado Ej. SELECT UPPER (nombre) from usuarios;

CURSO BASES DE DATOS SQL

REPLACE (Campo, str1, str2) En el campo seleccionado reemplaza por str2. No es case sensitive. Ejemplo: select replace(nombre, 'pe','lu') from usuarios; SUBSTRING (Campo, m, n) Devuelve la subcadena del campo que abarca desde m hasta el numero de caracteres dados por n. Ejemplo: select Substring(nombre, 2, 5) from usuarios; LEN (Campo) Devuelve el número de caracteres del campo. Ejemplo: select len(nombre) from usuarios; ROUND (Campo, Decimales) Redondea un resultado a los decimales que le digamos Para convertir datos tenemos 2 funciones, CONVERT y CAST CONVERT (Tipo Destino, Campo, [Estilo]) Ej: select 'El precio es ' + convert(varchar(15),precio) from detallepedido CAST (Campo as Tipo Destino) Ej: select 'El precio es ' + cast(precio as varchar(15)) from detallepedido COLUMNAS DE ENUMERACIÓN: Son columnas añadidas a la sentencia SQL que nos permiten enumerar y clasificar los resultados. Partimos de la siguiente situación: SELECT Ciudad, Count(*) FROM empleados GROUP BY Ciudad

ROW_NUMBER() OVER ([PARTITION BY Campo] ORDER BY Campo) Obtenemos una nueva columna donde enumeramos la lista de resultados. En el ORDER BY de la cláusula OVER le indicamos el orden de la enumeración. Si no decimos más, la consulta se ordena en ese orden. Devuelve el número secuencial de una fila de una partición de un conjunto de resultados, comenzando con 1 para la primera fila de cada partición. Ejemplo: SELECT ROW_NUMBER() OVER (ORDER BY Ciudad) AS Orden, Nombre, Ciudad, CodEmpleado FROM Empleados

CURSO BASES DE DATOS SQL

Podemos incluir la cláusula Partition By Campo. Si hacemos esto, la consulta se ordenará por ese campo, y como segunda ordenación la del Order By del OVER. La enumeración empezará por 1 cada vez que cambie el valor del campo que tengamos en Partition By Ejemplo: SELECT ROW_NUMBER() OVER (PARTITION BY Ciudad ORDER BY Nombre) AS Orden, Nombre, Ciudad, CodEmpleado FROM Empleados

En este caso, se añade la columna Orden, y la consulta se ordena por Ciudad y luego por Nombre. La columna orden empiezar por 1 y va incrementando hasta que el Ciudad cambia. Entonces vuelve a empezar por 1. RANK() OVER ([PARTITION BY Campo] ORDER BY Campo) En este caso obtenemos un número que es el mismo para todos los registros donde el campo de Order By es el mismo. SELECT RANK() OVER (ORDER BY Ciudad) AS Orden, Nombre, Ciudad, CodEmpleado FROM Empleados

En este caso, pone un número de orden para cada ciudad. Cuando cambia de ciudad le pone el orden que le correspondía en un orden normal. DENSE_RANK() OVER ([PARTITION BY Campo] ORDER BY Campo) Es igual que RANK pero para cada cambio de campo aumenta uno. SELECT DENSE_RANK() OVER (ORDER BY Ciudad) AS Orden, Nombre, Ciudad, CodEmpleado FROM Empleados

CURSO BASES DE DATOS SQL

Hacer los ejercicios 8 y 9

Trabajando con fechas A la hora de hacer una consulta en la base de datos es conveniente utilizar el formato de fecha ANSI, que es el siguiente: 'YYYYMMDD hh:mm:ss:nnn' Y: dígito de año. M: dígito de mes. D: dígito de día. h: dígito de hora. m: dígito de minuto. s: dígito de segundo. n: dígito de milésima de segundo Como formatear fechas. Hay que utilizar convert y un número del 101 al 113. Ejemplo: SELECT convert(char(24), getdate(), 101)

--06/27/2008

Probad todos los resultados leyendo el campo getdate() que nos devuelve la fecha de hoy. select select select select select select select select select select select select select

convert(char(24), convert(char(24), convert(char(24), convert(char(24), convert(char(24), convert(char(24), convert(char(24), convert(char(24), convert(char(24), convert(char(24), convert(char(24), convert(char(24), convert(char(24),

getdate(), getdate(), getdate(), getdate(), getdate(), getdate(), getdate(), getdate(), getdate(), getdate(), getdate(), getdate(), getdate(),

101) 102) 103) 104) 105) 106) 107) 108) 109) 110) 111) 112) 113)

--06/27/2008 --2008.06.27 --27/06/2008 --27.06.2008 --27-06-2008 --27 Jun 2008 --Jun 27, 2008 --13:11:06 --Jun 27 2008 1:11:06:200 --06-27-2008 --2008/06/27 --20080627 --27 Jun 2008 13:11:06:200

Funciones para trabajar con fechas: GETDATE () Devuelve la fecha y hora actuales del sistema en el formato interno estándar de Microsoft® SQL Server™ para los valores datetime.

CURSO BASES DE DATOS SQL

DAY Devuelve el día de la fecha especificada MONTH Devuelve el día de la fecha especificada YEAR Devuelve el año de la fecha especificada DATEADD Devuelve una nueva fecha que se basa en la suma de un intervalo a la fecha especificada. La sintaxis es: DATEADD(Abreviatura, Intervalo, Fecha)

Parte de la fecha Abreviaturas año

trimestre

mes

dia del año

da, a ¿

dia

semana

hora

minuto

segundo

milisegundo

Ejemplo: use ejemplo Select FechaNacimiento, DATEAdd(yy, 10, FechaNacimiento) AS años FROM Empleados DATEDIFF Devuelve la diferencia que hay entre dos fechas especificadas. La sintaxis es: DATEDIFF(Abreviatura, FechaMenor, FechaMayor) use ejemplo select DATEDIFF(mi, FechaNacimiento, getdate()) DATEDIFF(hh, FechaNacimiento, getdate()) DATEDIFF(dd, FechaNacimiento, getdate()) DATEDIFF(ww, FechaNacimiento, getdate()) DATEDIFF(mm, FechaNacimiento, getdate()) DATEDIFF(yy, FechaNacimiento, getdate()) FechaNacimiento, getdate(), year(FechaNacimiento), year(getdate()) FROM Empleados

AS AS AS AS AS AS

Minutos, Horas, Dias, Semanas, Meses, años,

CURSO BASES DE DATOS SQL

select DATEDIFF(ms, DATEDIFF(ss, DATEDIFF(mi, DATEDIFF(hh, DATEDIFF(dd,

getdate()-5, getdate()-5, getdate()-5, getdate()-5, getdate()-5,

getdate()) getdate()) getdate()) getdate()) getdate())

AS AS AS AS AS

MiliSegundos, Segundos, Minutos, Horas, Dias

Hacer los ejercicios del 10 al 13 Consultas con Predicado El predicado se incluye entre el SELECT y el primer campo a recuperar. Los distintos predicados que podemos utilizar son: -

ALL o * : Devuelve todos los campos de una tabla. El proceso es más rápido si enumeramos los campos en lugar de poner *. Ej. SELECT * FROM Clientes TOP: Devuelve un determinado número de registros o un determinado porcentaje. Ej SELECT TOP 20 Nombre FROM Clientes O SELECT TOP 20 PERCENT Nombre FROM Clientes. DISTINCT: Omite los registros que contienen valores duplicados en los campos seleccionados. Ej: SELECT DISTINCT Nombre FROM Clientes.

Alias Hay veces que es necesario, o simplemente porque nos facilita la compresión de datos, o por trabajar con nombres más cortos, asignar un nombre a un campo o a una tabla. La forma de hacerlo para un campo es: SELECT Campo AS Alias FROM Tabla La forma de hacerlo para una tabla es: SELECT Campo FROM Tabla AS Alias En SQL SERVER no es necesario usar AS Hacer los ejercicios 14 y 15

Cláusula WHERE. La utilizamos para indicar que registros de las tablas enumeradas en la claúsula FROM aparecerán en los resultados de la instrucción SELECT. WHERE es opcional, pero cuando se pone va detrás del FROM. La sintaxis queda de la siguiente forma: SELECT Campos FROM Tabla WHERE Condición ORDER BY Campos [ASC|DESC] Condición entre comillas para fecha y texto, y sin comillas para números Criterios de selección Después del WHERE hay que utilizar uno de los criterios de selección - Operadores lógicos

CURSO BASES DE DATOS SQL

Una condición está formada por una o varias expresiones condicionales conectadas por los operadores lógicos AND y OR. <expresión1> <operador relacional> <expresión2> Verifica si las dos expresiones satisfacen la comparación. Ejemplo: SELECT Nombre, FechaNacimiento FROM Empleados WHERE FechaNacimiento > ‘19591225' AND CodEmpleado > 3 - Intervalo de valores Devuelve los registros donde campo se encuentre entre valor1 y valor2, ambos incluidos Campo [NOT] BETWEEN valor1 and valor2 Ejemplo: SELECT Nombre, FechaNacimiento FROM Empleados WHERE FechaNacimiento BETWEEN '19530830' AND '19660127' ORDER BY FechaNacimiento -Operador IN Devuelve los registros donde campo se encuentre entre los valores de una lista Campo [NOT] IN (<lista-de valores>) Ejemplo: SELECT Nombre, FechaNacimiento, Ciudad FROM Empleados WHERE Ciudad IN ('LONDON', 'SEATTLE') -Operador LIKE Se utiliza para comparar una expresión con un modelo Expresión LIKE modelo Ejemplos: Ejemplo

Descripción

LIKE LIKE LIKE LIKE LIKE

Todo Todo Todo Todo Todo

‘A%’ ‘%A’ ‘_NG’ ‘[AF]%’ ‘[A-F]%’

lo lo lo lo lo

que que que que que

comienza por acaba por A comienza por comienza por comienza por

A cualquier carácter y luego siga NG AoF cualquier letra entre A y F

No tiene en cuenta mayúsculas y minúsculas. Podemos añadir el operador NOT para que nos muestre lo contrario de lo que pedimos. Ejemplo: SELECT Nombre, FechaNacimiento, Ciudad FROM Empleados WHERE Nombre LIKE 'A%' ORDER BY NOMBRE SELECT Nombre, FechaNacimiento, Ciudad FROM Empleados WHERE Nombre LIKE '_A%' ORDER BY NOMBRE

CURSO BASES DE DATOS SQL

SELECT Nombre, FechaNacimiento, Ciudad FROM Empleados WHERE Nombre LIKE '[JM]%' ORDER BY NOMBRE Como buscar caracteres especiales con el LIKE <nombre de columna> [NOT] LIKE "<string>" [ESCAPE "<carácter de escape>"] Verifica si el valor de la columna se adapta al patrón de búsqueda (string). Se admiten caracteres comodín ( "%" representa cero o más caracteres, "_" representa un único carácter). El carácter de escape permite referirse a los caracteres comodín como caracteres y no como comodines. Para poder buscar un carácter comodín como carácter y no como comodín, debemos poner el carácter de escape delante del carácter comodín. El carácter de escape lo definimos al final. Ejemplo. Queremos buscar un textos que contengan ‘5%’ SELECT Campos FROM Tabla WHERE Campo LIKE 'a#%' ESCAPE '#' -Operador NULL Verifica si el valor de la columna es nulo. <nombre de columna> IS [NOT] NULL Hacer los ejercicios 16, 17 y 18 Agrupamiento de Registros Se utiliza cuando se quieren realizar consultas agrupadas por una columna. Así, se puede pensar en estos conjuntos de filas como grupos, utilizándose funciones de agrupación en la forma: Select [column1, ] FuncionesDeAgrupacion(columna) From table [where condition] [group by column] [order by column] Las funciones de agregación más importantes son las siguientes:

En las expresiones anteriores es importante tener claro el sentido de DISTINCT y el de ALL. Cuando se usa DISTINCT se tienen en cuenta sólo la primera vez que aparecen los valores, mientras que con ALL se tienen en cuenta todas las veces que aparecen. Estas operaciones se pueden hacer sobre atributos que sean del tipo de dato: char, varchar2, number or Date. Por defecto, el servidor de Oracle ordena los valores en orden ascendente cuando se usa la cláusula GROUP BY (que también pueden ser utilizadas sin GROUP BY): Ejemplos:

CURSO BASES DE DATOS SQL

A continuación se van a proponer algunos ejemplos para ilustrar el uso de estas funciones de agregación. Supóngase la siguiente tabla de clientes, que representa a todos los comercios que pertenecen a una misma cadena de ámbito nacional con la estructura y el contenido mostrado:

Si se quiere saber el número de tiendas que tiene la cadena se podría poner: SELECT count(id)as “Número de Tiendas” from cliente Numero de Tiendas 7 Si se quisiera saber en cuántas ciudades distintas hay tiendas: SELECT count(distinct ciudad) as “Número de Ciudades Con Tiendas de la Cadena” from cliente NúmeroCiudadesConTiendasCadena 3 Se va a introducir ahora un ejemplo de utilización de funciones de agregación. Se está interesado en saber el número de tiendas que hay por ciudad. Para esto, tendríamos que agrupar los resultados de la consulta por ciudades. Para que quede más claro se va a incorporar en la consulta el nombre de la ciudad. Así la consulta: SELECT ciudad, count(distinct id) as "NumeroTiendasPorCiudad" from cliente group by ciudad Daría como resultado CIUDAD NumeroTiendasPorCiudad Cuenca 2 Teruel 2 Toledo 3 Si se quisiera obtener la media de las ventas realizadas por la cadena puedo obtenerlo con un select sobre toda la tabla: select avg(cant_ventas) from clientes; AVG(CANT_VENTAS) 300 Pero si lo que quiero son las ventas totales agrupadas por ciudades, entonces se debe realizar un agrupamiento por la columna ciudad. select ciudad, avg(cant_ventas) from clientes group by ciudad; CIUDAD AVG(CANT_VENTAS) Cuenca 150 Teruel 400

CURSO BASES DE DATOS SQL

Toledo

333,333333

select ciudad, Sum(cant_ventas) from clientes group by ciudad; Ventas totales agrupadas por ciudad: CIUDAD SUM(CANT_VENTAS) Cuenca 300 Teruel 800 Toledo 1000 Si se quisiesen conocer los máximos de ventas por ciudad: Select ciudad, max(cant_ventas) from cliente group by ciudad CIUDAD MAX(CANT_VENTAS) Cuenca 200 Teruel 600 Toledo 500 Y los mínimos: Select ciudad, min(cant_ventas) from cliente group by ciudad CIUDAD MIN(CANT_VENTAS) Cuenca 100 Teruel 200 Toledo 100 Y en la misma consulta: Select ciudad, min(cant_ventas), max (cant_ventas) from cliente group by ciudad CIUDAD MIN(CANT_VENTAS) MAX(CANT_VENTAS) Cuenca 100 200 Teruel 200 600 Toledo 100 500 select nombre, ciudad, sum(cant_ventas) from clientes group by nombre; Daría error ya que ciudad no está incluido en el group by. Correctamente sería: select nombre, ciudad, sum(cant_ventas) from clientes group by nombre, ciudad; Cláusula HAVING No se pueden usar funciones de agrupación en la cláusula WHERE de un SELECT. O sea, no se puede usar el WHERE para, de forma selectiva eliminar datos que no interesan del resultado de una consulta agrupada. Por ejemplo, en la tabla examenes(id_asignatura, nro_estudiante, nota, fecha) Si hacemos: SELECT nro_estudiante, avg(nota) FROM examenes WHERE avg (nota) >6 GROUP BY nro_estudiante; Daría un error por usar una función de agrupamiento en el WHERE La cláusula HAVING hace una función parecida a la del WHERE cuando se trabaja con este tipo de funciones. Así, para listar aquellos alumnos cuya media es mayor que 6 sería: SELECT nro_estudiante, avg(nota) FROM examenes GROUP BY nro_estudiante HAVING avg (nota) >6;

CURSO BASES DE DATOS SQL

El campo referenciado en la cláusula HAVING no puede tener más de un valor por grupo. Esto significa que, en la práctica, HAVING sólo puede referenciar a funciones de agregación y columnas que se están usando en el GROUP by Mas Ejemplos: SELECT NumPedido FROM DetallePedido GROUP BY NumPedido SELECT NumPedido, SUM(Precio*Unidades) FROM DetallePedido GROUP BY NumPedido SELECT NumPedido, SUM(Precio*Unidades),COUNT(*) FROM DetallePedido GROUP BY NumPedido SELECT NumPedido, SUM(Precio*Unidades),COUNT(*) FROM DetallePedido GROUP BY NumPedido HAVING COUNT(*) >2 COMPUTE Añade una fila al conjunto de datos que se está recuperando. Se utiliza para realizar c select Numpedido, LineaPedido, Precio, Unidades, Descuento, ((Precio * Unidades) - Descuento) AS TotalLinea From DetallePedido ORDER BY NumPedido, LineaPedido COMPUTE SUM((Precio * Unidades) - Descuento) BY (NumPedido) Composición de tablas: consultas a varias tablas. Consultas internas La mayor parte de las ocasiones, todos los datos no estarán en la misma tabla, sino que por diseño de la base de datos, habrán quedado repartidos en diferentes tablas. En esas ocasiones, para hacer las consultas correspondientes es necesario combinar todas las tablas en las que están los datos. El INNER JOIN es un tipo de composición de tablas, que permite emparejar filas de distintas tablas de forma más eficiente que con el producto cartesiano cuando una de las columnas de emparejamiento está 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 la otra tabla las filas que cumplen la condición, con lo cual se emparejan sólo las filas que luego aparecen en el resultado. Su sintaxis es: SELECT campos FROM tb1 INNER JOIN tb2 ON tb1.campo1 comp tb2.campo2 En donde: tb1, tb2 Son los nombres de las tablas desde las que se combinan los registros. campo1, campo2 Son los nombres de los campos que se combinan. Si no son numéricos, los campos deben ser del mismo tipo de datos y contener el mismo tipo de datos, pero no tienen que tener el mismo nombre. comp Es cualquier operador de comparación relacional : =, <, , <=, =, o <. Se puede utilizar una operación INNER JOIN en cualquier cláusula FROM. Esto crea una combinación por equivalencia, conocida también como unión interna. Las combinaciones Equi son las más comunes; éstas combinan los registros de dos tablas siempre que haya concordancia de valores en un campo común a ambas tablas. Se puede utilizar INNER JOIN con las tablas Departamentos y Empleados para seleccionar todos los empleados de cada departamento.

CURSO BASES DE DATOS SQL

Si queremos que aparezcan las filas de una tabla que no tienen una fila coincidente en la otra, utilizamos LEFT o RIGHT JOIN. Con LEFT añadimos las filas de tabla de la izquierda que no tienen correspondencia en la derecha y con RIGHT al revés. Si se intenta combinar campos que contengan datos Memo u Objeto OLE, se produce un error. Se pueden combinar dos campos numéricos cualesquiera, incluso si son de diferente tipo de datos. Por ejemplo, puede combinar un campo Numérico para el que la propiedad Size de su objeto Field está establecida como Entero, y un campo Contador. El ejemplo siguiente muestra cómo podría combinar las tablas Categorías y Productos basándose en el campo IDCategoria: SELECT Nombre_Categoría, NombreProducto FROM Categorias INNER JOIN Productos ON Categorias.IDCategoria = Productos.IDCategoria; En el ejemplo anterior, IDCategoria es el campo combinado, pero no está incluido en la salida de la consulta ya que no está incluido en la instrucción SELECT. Para incluir el campo combinado, incluir el nombre del campo en la instrucción SELECT, en este caso, Categorias.IDCategoria. También se pueden enlazar varias cláusulas ON en una instrucción JOIN, utilizando la sintaxis siguiente: SELECT campos FROM tabla1 INNER JOIN tabla2 ON tb1.campo1 comp tb2.campo1 AND ON tb1.campo2 comp tb2.campo2) OR ON tb1.campo3 comp tb2.campo3)]; También puede anidar instrucciones JOIN utilizando la siguiente sintaxis: SELECT campos FROM tb1 INNER JOIN (tb2 INNER JOIN [( ]tb3 [INNER JOIN [( ]tablax [INNER JOIN ...)] ON tb3.campo3 comp tbx.campox)] ON tb2.campo2 comp tb3.campo3) ON tb1.campo1 comp tb2.campo2; Un LEFT JOIN o un RIGHT JOIN puede anidarse dentro de un INNER JOIN, pero un INNER JOIN no puede anidarse dentro de un LEFT JOIN o un RIGHT JOIN. Ejemplo: SELECT * FROM Clientes INNER JOIN (Pedidos INNER JOIN Empleados ON Pedidos.CodEmpleado = Empleados.CodEmpleado ) ON Pedidos.CodCliente = Clientes.CodCliente Ejemplo SELECT DISTINCTROW Sum([Precio unidad] * [Cantidad]) AS [Ventas], [Nombre] & " " & [Apellidos] AS [Nombre completo] FROM [Detalles de pedidos], Pedidos, Empleados, Pedidos INNER JOIN [Detalles de pedidos] ON Pedidos. [ID de pedido] = [Detalles de pedidos].[ID de pedido], Empleados INNER JOIN Pedidos ON Empleados.[ID de empleado] = Pedidos.[ID de empleado] GROUP BY [Nombre] & " " & [Apellidos]; Crea dos combinaciones equivalentes: una entre las tablas Detalles de pedidos y Pedidos, y la otra entre las tablas Pedidos y Empleados. Esto es necesario ya que la tabla Empleados no contiene datos de ventas y la tabla Detalles de pedidos no contiene datos de los empleados. La consulta produce una lista de empleados y sus ventas totales. Si empleamos la cláusula INNER en la consulta se seleccionarán sólo aquellos registros de la tabla de la que hayamos escrito a la izquierda de INNER JOIN que contengan al menos un registro de la tabla que hayamos escrito a la derecha. Para solucionar esto tenemos dos cláusulas que sustituyen a la palabra clave INNER, estas cláusulas son LEFT y RIGHT. LEFT toma todos los registros de la tabla de la izquierda aunque no tengan ningún registro en la tabla de la izquierda. RIGHT realiza la misma operación pero al contrario, toma todos los registros de la tabla de la derecha aunque no tenga ningún registro en la tabla de la izquierda.

CURSO BASES DE DATOS SQL

Consultas de Unión Externas Se utiliza la operación UNION para crear una consulta de unión, combinando los resultados de dos o más consultas o tablas independientes. Su sintaxis es: [TABLE] consulta1 UNION [ALL] [TABLE] consulta2 [UNION [ALL] [TABLE] consultan [ ... ]] En donde: consulta1, consulta2, consultan Son instrucciones SELECT, el nombre de una consulta almacenada o el nombre de una tabla almacenada precedido por la palabra clave TABLE. Puede combinar los resultados de dos o más consultas, tablas e instrucciones SELECT, en cualquier orden, en una única operación UNION. El ejemplo siguiente combina una tabla existente llamada Nuevas Cuentas y una instrucción SELECT: TABLE [Nuevas Cuentas] UNION ALL SELECT * FROM Clientes WHERE [Cantidad pedidos] 1000; Si no se indica lo contrario, no se devuelven registros duplicados cuando se utiliza la operación UNION, no obstante puede incluir el predicado ALL para asegurar que se devuelven todos los registros. Esto hace que la consulta se ejecute más rápidamente. Todas las consultas en una operación UNION deben pedir el mismo número de campos, no obstante los campos no tienen porqué tener el mismo tamaño o el mismo tipo de datos. Se puede utilizar una cláusula GROUP BY y/o HAVING en cada argumento consulta para agrupar los datos devueltos. Puede utilizar una cláusula ORDER BY al final del último argumento consulta para visualizar los datos devueltos en un orden específico. SELECT [Nombre de compañía], Ciudad FROM Proveedores WHERE País = 'Brasil' UNION SELECT [Nombre de compañía], Ciudad FROM Clientes WHERE País = "Brasil" Recupera los nombres y las ciudades de todos proveedores y clientes de Brasil SELECT [Nombre de compañía], Ciudad FROM Proveedores WHERE País = 'Brasil' UNION SELECT [Nombre de compañía], Ciudad FROM Clientes WHERE País = 'Brasil' ORDER BY Ciudad Recupera los nombres y las ciudades de todos proveedores y clientes radicados en Brasil, ordenados por el nombre de la ciudad SELECT [Nombre de compañía], Ciudad FROM Proveedores WHERE País = 'Brasil' UNION SELECT [Nombre de compañía], Ciudad FROM Clientes WHERE País = 'Brasil' UNION SELECT [Apellidos], Ciudad FROM Empleados WHERE Región = 'América del Sur' Recupera los nombres y las ciudades de todos los proveedores y clientes de brasil y los apellidos y las ciudades de todos los empleados de América del Sur TABLE [Lista de clientes] UNION TABLE [Lista de proveedores] Recupera los nombres y códigos de todos los proveedores y clientes Subconsultas Esquema de trabajo que vamos a seguir para los ejemplos Personas Nombre

Ciudad

Edad

Pepe

Paco

Lola

Maria

Ciudad Código

Nombre

Cuenca

Toledo

Madrid

CURSO BASES DE DATOS SQL

CREATE TABLE Ciudad( Codigo Integer PRIMARY KEY, Nombre Varchar(50) )

CREATE TABLE Personas ( Nombre varchar(50) PRIMARY KEY, Ciudad Integer REFERENCES Ciudad, Edad Int );

Insert into ciudad values(1, ‘Cuenca’); Insert into ciudad values(2, ‘Toledo’); Insert into ciudad values(3, ‘Madrid’);

Insert into personas values (‘Pepe’, 3, 31); Insert into personas values (‘Paco’, 3, 19); Insert into personas values (‘Lola’, 1, 34); Insert into personas values (‘María’, 2, 17); Una subconsulta forma parte siempre de una condición de búsqueda en la cláusula WHERE o HAVING. Se pueden aplicar las siguientes condiciones de búsqueda en subconsultas: Test de comparación Compara el valor de una expresión con el valor producido por una subconsulta y devuelve un valor True si la comparación es cierta. Operadores: >, <>, <, <=, >, >= La subconsulta siempre debe ocupar la parte derecha del operador de comparación. Para poder aplicar la subconsulta con el test de comparación hay que tener en cuenta que la subconsulta debe volver UN SOLO VALOR. Ejemplo: Utilizando una subconsulta, seleccionar el nombre de todas las personas que viven en Cuenca SELECT Nombre FROM Personas WHERE Ciudad = ( SELECT Codigo FROM Ciudad WHERE Nombre='Cuenca' ); Salida: NOMBRE Lola Test de pertenencia a conjunto. Compara un único valor de datos con una columna de valores producida por una subconsulta y devuelve un resultado True si el valor coincide con uno de los valores de la columna. Operador: IN Ejemplo: Utilizando subconsultas, seleccionar el nombre de las personas que viven en Cuenca o Toledo. SELECT Nombre FROM Personas WHERE Ciudad IN ( SELECT Codigo FROM Ciudad WHERE Nombre IN ('Cuenca', 'Toledo') ); Salida: NOMBRE

CURSO BASES DE DATOS SQL

Lola María Test de comparación cuantificada. Se utilizan cuando una subconsulta devuelve más de un valor. Compara un valor de dato con la columna de valores producidos por una subconsulta. SOME y ANY son equivalentes y se utilizan para aplicar a la consulta cada resultado de una subconsulta. ALL se utiliza para comparar el valor del test con todos los resultados de una subconsulta, si todos devuelven TRUE, entonces se ejecutará la consulta. Operadores: SOME, ANY, ALL Ejemplo: Utilizando subconsultas, seleccionar el nombre de las personas que tienen una edad mayor que todas de las personas que viven en Madrid. SELECT Nombre FROM Personas WHERE Edad > ALL ( SELECT Edad FROM Personas, Ciudad WHERE Ciudad.Codigo = Personas.Ciudad AND Ciudad.Nombre = 'Madrid' ); Salida: NOMBRE Lola Nota: Lola tiene 34 años, que es mayor que las edades de Pepe (33años) y Paco (19 años), ambos residentes en Madrid. Ejemplo: Utilizando subconsultas, seleccionar el nombre de las personas que tienen una edad mayor que alguna de las personas que viven en Madrid. SELECT Nombre FROM Personas WHERE Edad > ANY ( SELECT Edad FROM Personas, Ciudad WHERE Ciudad.Codigo = Personas.Ciudad AND Ciudad.Nombre = 'Madrid' ); Salida: NOMBRE Pepe Lola Subconsultas en la cláusula HAVING Aunque las subconsultas suelen encontrarse tras la cláusula WHERE, también pueden ir tras la cláusula HAVING. Ejemplo. Mostrar las ciudades en las cuales, la edad media de sus habitantes es mayor que la edad de la persona más joven de la base de datos. SELECT Ciudad.Nombre AS Localidad, AVG(Personas.Edad) AS Media_Edad FROM Ciudad, Personas WHERE Ciudad.Codigo = Personas.Ciudad GROUP BY Ciudad.Nombre HAVING AVG(Personas.Edad) > ( SELECT MIN(Edad) FROM Personas ); LOCALIDAD

MEDIA_EDAD

CURSO BASES DE DATOS SQL

Cuenca

Madrid

UPDATE Crea una consulta de actualización que cambia los valores de los campos de una tabla especificada basándose en un criterio específico. Su sintaxis es: UPDATE Tabla SET Campo1=Valor1, Campo2=Valor2, ... CampoN=ValorN WHERE Criterio; Ejemplo: UPDATE Productos SET Stock = Stock + 12, Precio = Precio * 2 DELETE Crea una consulta de eliminación que elimina los registros de una o más de las tablas listadas en la cláusula FROM que satisfagan la cláusula WHERE. Esta consulta elimina los registros completos, no es posible eliminar el contenido de algún campo en concreto. Su sintaxis es: DELETE FROM Tabla WHERE criterio Ejemplo: DELETE FROM Productos WHERE Stock > 10 TRUNCATE El comando TRUNCATE TABLE es una forma rápida y no registrada de eliminar todas las filas en una tabla. Este método es casi siempre más rápido que un comando DELETE sin condiciones, porque DELETE graba un registro de transacciones por cada fila eliminada, mientras que TRUNCATE TABLE solo graba registro de liberación de las páginas de datos completas. El comando TRUNCATE TABLE inmediatamente libera el espacio ocupado por los datos de la tabla e índices. También se liberan las páginas de distribución de todos los índices. Como en el caso del comando DELETE, la definición de la tabla se mantiene en la base de datos después que se aplica un comando TRUNCATE TABLE (incluidos sus índices y objetos asociados). Para eliminar la definición de la tabla se debe usar el comando DROP TABLE. Ejemplo: TRUNCATE TABLE Productos CONSULTAS ESPECIALES BUSCAR DUPLICADOS SELECT YourColumn, COUNT(*) TotalCount FROM YourTable GROUP BY YourColumn HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC Ejemplo SELECT Nombre, COUNT(*) FROM Clientes GROUP BY Nombre HAVING COUNT(*) > 1 BORRA DUPLICADOS DELETE FROM Tabla WHERE ID NOT IN ( SELECT MAX(ID) FROM Tabla GROUP BY CampoDuplicado1, CampoDuplicado2,…)

CURSO BASES DE DATOS SQL

Ejemplo: DELETE FROM CLIENTES WHERE CODCLIENTE NOT IN (SELECT MAX(CodCliente) FROM Clientes GROUP BY Nombre) PROCEDIMIENTOS ALMACENADOS Son un conjunto de instrucciones SQL que se guardan en la base de datos para poder llamarlas y ejecutarlas en cualquier momento. Para crear un sp, utilizaremos la sintaxis: CREATE PROCEDURE Nombre AS Instrucciones SQL Ejemplo: CREATE PROC ListarClientes AS SELECT * FROM Clientes Podemos poner PROC o PROCEDURE Para ejecutarlo deberíamos hacer algo así: EXEC ListarClientes Dentro de un sp podemos utilizar variables. Éstas pueden ser declaradas dentro del sp o pueden ser pasadas por parámetro en la llamada al sp. Ejemplo: CREATE PROCEDURE PedidosEmpleados @Empleado as INT, @Fecha as DATETIME AS SELECT * FROM Pedidos WHERE CodEmpleado = @Empleado AND FechaPedido = @Fecha Para ejecutarlo deberíamos hacer algo así: EXEC PedidosEmpleados 5, '19960704' Podemos utilizar Instrucciones IF .. ELSE para validar condiciones y instrucciones BEGIN..END para crear un bloque de instrucciones. Ejemplo: CREATE PROCEDURE PedidosEmpleadosCondicional @Empleado as INT, @Fecha as DATETIME AS IF @Empleado = 0 SELECT * FROM Pedidos WHERE FechaPedido = @Fecha ELSE SELECT * FROM Pedidos WHERE CodEmpleado = @Empleado AND FechaPedido = @Fecha Podemos inicializar los parámetros, con lo que se trataría como un parámetro opcional.

CURSO BASES DE DATOS SQL

Ejemplo: Supongamos el ejemplo anterior, donde inicializamos @Empleado = 0. Si no pasamos valor para empleado, su valor será 0. En el caso anterior, sin inicializar, sino pasamos valor, nos devuelve un error. En este caso, si podemos ejecutar el sp sin necesidad de poner un valor al parámetro @Empleado, pero si hacemos esto no podemos pasar los parámetros por orden, habrá que hacerlo por nombre de la siguiente forma: EXEC sp_PedidosEmpleadosParametroOpcional @Fecha = '19960704' Los procedimientos almacenados pueden devolver información al procedimiento almacenado o cliente que realiza la llamada con parámetros de salida (variables designadas con la palabra clave OUTPUT). Para usar un parámetro de salida, debe especificarse la palabra clave OUTPUT en las instrucciones CREATE PROCEDURE y EXECUTE. Si se omite la palabra clave OUTPUT cuando se ejecuta el procedimiento almacenado, éste se ejecuta igualmente, pero no devuelve ningún valor. Ejemplo: En este ejemplo se crea un procedimiento almacenado que calcula el producto de dos números CREATE PROCEDURE Multiplica @m1 INT, @m2 INT, @res INT OUTPUT AS SET @res = @m1* @m2 Vemos como asignamos el resultado de la operación a la variable @res utilizando SET. Veamos cómo llamar a este sp. DECLARE @Resultado INT EXECUTE Multiplica 5,6, @Resultado OUTPUT SELECT El resultado es: ', @Resultado Creamos una variable llamada @Resultado mediante la instrucción DECLARE Ejecuto el sp, pasándole por parámetro @Resultado indicándole la propiedad OUTPUT Luego leo la variable @Resultado. ****************Ejemplo Devuelve el maximo CREATE PROC Maximo @Maxim as INT OUTPUT AS --SET @Maxim = (SELECT MAX(Precio) FROM Productos) SELECT @Maxim = MAX(Precio) FROM Productos DECLARE @Resultado INT EXECUTE Maximo @Resultado OUTPUT SELECT 'El resultado es: ', @Resultado --Ejemplo modificar precio CREATE PROC AumentarPrecio @Porcentaje AS REAL = 0 AS UPDATE Productos SET Precio = Precio * (1 +

@Porcentaje / 100)

CURSO BASES DE DATOS SQL

Ejemplo Return CREATE PROC EjemploReturn AS SELECT * FROM Productos RETURN @@ROWCOUNT DECLARE @Resultado INT EXECUTE @Resultado = EjemploReturn SELECT 'El resultado es: ', @Resultado

****************Ejemplo Devuelve el maximo y el mínmo CREATE PROC MaximoMinimo @Maxim as REAL OUTPUT, @Minim as REAL OUTPUT AS SELECT @Maxim = MAX(Precio), @Minim = MIN(Precio) FROM Productos DECLARE @Maxim REAL, @Minim REAL EXECUTE MaximoMinimo @Maxim OUTPUT, @Minim OUTPUT SELECT 'El resultado es: ', @Maxim, @Minim Ejercicio. Crear un procedimiento almacenado que muestre la fecha y el total de los pedidos en esa fecha para un Empleado en concreto y en un intervalo de fechas. El resultado puede ser Agrupado o No agrupado por Fecha. Para trabajar, este procedimiento almacenado necesitará que le pasen por parámetro el Codigo del empleado, las fechas iniciales y finales y si el resultado va a ser a agrupado o no. CREATE PROCEDURE PedidosPorFecha @Empleado as INT, @FechaInicial as DATETIME, @FechaFinal as DATETIME, @Agrupado as BIT = 0 AS IF @Agrupado = 1 SELECT FechaPedido, Precio * Unidades AS Total FROM Pedidos P INNER JOIN DetallePedido D ON P.NumPedido = D.NumPedido WHERE CodEmpleado = @Empleado AND FechaPedido BETWEEN @FechaInicial AND @FechaFinal ELSE SELECT FechaPedido, SUM(Precio * Unidades) AS Total FROM Pedidos P INNER JOIN DetallePedido D ON P.NumPedido = D.NumPedido WHERE CodEmpleado = @Empleado AND FechaPedido BETWEEN @FechaInicial AND @FechaFinal

CURSO BASES DE DATOS SQL

GROUP BY FechaPedido TRIGGERS CREATE TABLE CONTROL ( Texto NVARCHAR(200), Fecha DATETIME ) CREATE TRIGGER T_PRODUCTOS_D ON Productos FOR DELETE AS DECLARE @Nombre NVARCHAR (200) SET @Nombre=(SELECT Nombre FROM DELETED) VER CONEXIONES ACTIVAS SELECT db_name(dbid) AS [Base Datos], count(dbid) AS [Nro. Conexiones], loginame AS [Usuario] FROM sys.sysprocesses WHERE [dbid] > 0 GROUP BY [dbid], [loginame] UPDATE CON OTRA TABLA

UPDATE T1 SET T1.LOTES = T2.CAMPO2 FROM articulo T1 INNER JOIN articulo T2 ON T1.ARTICULO = T2.CODIGO WHERE T2.FAMILIA = 95