• Author / Uploaded
• Lluni

• Categories
• SQL
• Tabla (base de datos)
• Servidor SQL de Microsoft
• Bases de datos
• Gestión de datos

Citation preview

LENGUAJE SQL - Bases de Datos relacionales

Módulo 2. El lenguaje SQL ...................................................................................................................... 2 1. Bases de datos .................................................................................................................................... 2 2. Normalización de datos ....................................................................................................................... 2 3. SQL...................................................................................................................................................... 4 4. El lenguaje DML .................................................................................................................................. 4 4.1. Consultas. SELECT ...................................................................................................................... 4 4.1.1. Consultas simples. ................................................................................................................. 4 4.1.2. WHERE .................................................................................................................................. 6 4.1.3. ORDER BY............................................................................................................................. 9 4.1.4. GROUP BY ............................................................................................................................ 9 4.1.5. HAVING .................................................................................................................................. 9 4.1.6. Subconsultas .......................................................................................................................... 9 4.1.7. JOIN ..................................................................................................................................... 10 4.1.8. Combinaciones de consultas ............................................................................................... 12 4.1.9. UNION .................................................................................................................................. 12 4.1.10. TOP .................................................................................................................................... 13 4.1.11. ANY, SOME, ALL ............................................................................................................... 14 4.1.12. EXISTS............................................................................................................................... 15 4.2. Inserción de datos. INSERT ....................................................................................................... 15 4.3. Modificar Datos. UPDATE .......................................................................................................... 17 4.4. Borrar Datos. DELETE................................................................................................................ 18 4.5. Vistas. Uso .................................................................................................................................. 19 5. Programación con SQL. Scripts ....................................................................................................... 21 5.1. Sintaxis ....................................................................................................................................... 21 5.1.1. Sentencias ............................................................................................................................ 21 5.1.2. Variables .............................................................................................................................. 22 5.1.3. Operadores y funciones del sistema .................................................................................... 23 5.1.4. Estructuras Condicionales ................................................................................................... 24 5.1.5. Estructuras iterativas o bucles ............................................................................................. 26 5.1.6. Cursores ............................................................................................................................... 27 5.1.7. Tablas temporales ................................................................................................................ 30 5.2. Funciones ................................................................................................................................... 31 5.3. Procedimientos almacenados ..................................................................................................... 34 5.4. Desencadenadores o Triggers ................................................................................................... 36 5.5. Errores ........................................................................................................................................ 41 6. El lenguaje DDL ................................................................................................................................. 42 6.1. Base de datos ............................................................................................................................. 42 6.2. Tablas ......................................................................................................................................... 42 6.2.1. Create Table......................................................................................................................... 42 6.2.1.1. Tipos de datos ............................................................................................................... 42 6.2.1.2. Permitir/ prohibir valores nulos ..................................................................................... 43 6.2.1.3. Valores por defecto. DEFAULT ..................................................................................... 43 6.2.1.4. Clave primaria e identidad. PRIMARY KEY, IDENTITY ............................................... 43 6.2.1.5. Columnas sin repeticiones. UNIQUE ............................................................................ 43 6.2.1.6. Restricciones. CHECK () ............................................................................................... 43 6.2.1.7. Claves externas ............................................................................................................. 44 6.2.1.8. Columnas calculadas .................................................................................................... 44 6.2.2. Alter Table ........................................................................................................................ 45 6.2.3. Drop Table............................................................................................................................ 46 6.3. Vistas .......................................................................................................................................... 46 6.3.1. Create view .......................................................................................................................... 46 6.3.2. Alter view .............................................................................................................................. 47 6.3.3. Drop view ............................................................................................................................. 47 6.4. Índices ......................................................................................................................................... 47 6.4.1. Create index ......................................................................................................................... 47 6.4.2. Alter index ............................................................................................................................ 48 6.4.3. Drop index ............................................................................................................................ 49

SQL

Página 1

Módulo 2. El lenguaje SQL 1. Bases de datos Una base de datos BD (en inglés: database DB) es un conjunto de datos pertenecientes a un mismo contexto y almacenados sistemáticamente para su posterior uso. Por ejemplo el catálogo de libros de una biblioteca o los usuarios de la misma. Las bases de datos ofrecen una manera eficiente y práctica de almacenar y trabajar con grandes cantidades de datos. Para trabajar con las DB se utilizan los llamados Sistemas Gestores de Bases de Datos, SGBD, que permiten almacenar y posteriormente acceder a los datos de forma rápida y estructurada. Ejemplos de SGBD son MySQL, PostgreSQL, Oracle, MS SQL Server, etc. La manera en que se estructuran los datos se puede resumir como: SGDB

Base de Datos 2

Base de Datos 1 Tabla

Tabla

Tabla

Tabla

Tabla

Tabla

Los datos se agrupan en entidades llamadas tablas con alguna relación entre sí. Las tablas, a su vez también se pueden relacionar entre ellas formando DB. Y por encima de estas se encuentra el SGBD que se encarga de gestionar el almacenamiento, acceso y gestión de los datos. En las tablas los datos se organizan en filas y columnas. Las columnas representan los tipos de datos introducidos, por ejemplo el nombre, los apellidos, el DNI, etc. Mientras, en cada fila se guardan los datos concretos. ID Cliente 123 456 789

Nombre Rachel James María

Apellido Ingram Wright Fernández

2. Normalización de datos Para facilitar el trabajo con las tablas y su manipulación, se definen una serie de reglas que se conocen como formas normales. Aunque existen 5 formas o grados de normalización, en la mayoría de aplicaciones basta con llegar hasta la tercera. La normalización permite la disminución de las redundancias y evitar la duplicación innecesaria de datos. Las tres primeras dicen: 1. Una relación está en primera forma normal si, y sólo si, todos los dominios de la misma contienen valores atómicos, es decir, no hay grupos repetitivos. Cada fila de la tabla debe tener un único campo (clave primaria) que lo identifique, en cada campo sólo ha de haber un dato y no tienen que repetirse las filas. 2. Una relación está en segunda forma normal si, y sólo si, está en 1FN y, además, cada atributo que no está en la clave primaria es completamente dependiente de la clave primaria.

2

La 2FN se aplica a las relaciones que tienen claves primarias compuestas por dos o más atributos. Si una relación está en 1FN y su clave primaria es simple (tiene un solo atributo), entonces también está en 2FN. Las relaciones que no están en 2FN pueden sufrir anomalías cuando se realizan actualizaciones. 3. Una relación está en tercera forma normal si, y sólo si, está en 2FN y, además, cada atributo que no está en la clave primaria no depende transitivamente de la clave primaria. Esta norma consiste en eliminar las columnas que no dependan de la clave principal. Ejemplo: Nombre

Apellido

Teléfono

TipoTeléfono

Raquel

Pérez

935478892

fijo

Pere

Guardiola

912345567

fijo

Pere

Guardiola

665333559

Móvil

María

Fernández

658244123,931246678

Móvil, fijo

Si observamos la tabla vemos que no se cumple la primera norma porque en los campos tipoteléfono y teléfono hay más de un dato por celda, se repiten filas y no hay una clave que pueda identificar sin error las filas. Para solucionarlo se parte la tabla en dos: Tabla cliente: IDC

Nombre

Apellido

1

Raquel

Pérez

2

Pere

Guardiola

3

María

Fernández

Tabla teléfono: Teléfono

IDC

TipoTeléfono

935478892

1

fijo

912345567

2

fijo

665333559

2

Móvil

658244123

3

Móvil

931246678

3

fijo

Si analizamos ambas tablas vemos que en la segunda hay datos que aparecen repetidos y que además no tienen nada que ver directamente con la clave teléfono. Por tanto volveremos a dividir esta tabla en dos. Tabla teléfono: Teléfono

IDC

IDTT

935478892

1

1

912345567

2

1

665333559

2

2

658244123

3

2

931246678

3

1

Tabla TipoTeléfono:

3

IDTT

TipoTeléfono

1

fijo

2

móvil

Finalmente tendríamos una estructura de relaciones entre tablas como la que sigue: cliente PK

Teléfono

idc

PK

telefono

nombre apellidos

FK1 FK2

idc idtt

TipoTeléfono PK

idtt tipotelefono

3. SQL SQL es un lenguaje interactivo y de programación estándar que permite manipular y obtener información de una base de datos y actualizarla. Aunque el SQL es un estándar tanto ANSI como ISO, muchos SGDB de datos soportan el SQL con extensiones propias del lenguaje estándar. Para MS SQL Server se llama Transact-SQL SQL se divide en dos lenguajes: • •

DDL (Data Description Language o Lenguaje de Descripción de Datos) que sirve para crear y manipular las bases de datos, tablas, vistas, etc. DML (Data Manipulation Languatge o Lenguaje de Manipulación de Datos) que sirve para consultar y modificar los datos.

4. El lenguaje DML DML nos permite interactuar con los datos almacenados en una DB. Las operaciones básicas que se pueden realizar son: • • • •

Seleccionar datos SELECT Añadir datos nuevos INSERT Borrar datos DELETE Modificar datos UPDATE

4.1. Consultas. SELECT Las consultas SELECT permiten extraer subconjuntos de datos de una o más tablas según unos criterios preestablecidos. Sintaxis: SELECT [ALL | DISTINCT] select_list [ INTO new_table ] [ FROM table_source ] [ WHERE search_condition ] [ GROUP BY group_by_expression ] [ HAVING search_condition ] [ ORDER BY order_expression [ ASC | DESC ] ] 4.1.1. Consultas simples. La consulta más simple que se puede hacer es de la forma: SELECT Lista_ columnas FROM nombre_tabla En caso que se quieran seleccionar todas las filas de una tabla, la sintaxis será SELECT * FROM nombre_tabla

4

Ejemplo: Tabla_direcciones_empleados id 1 2 3 4

DNI 23987739A 45623890F 43567893D 23456789G

Apellidos García López Macías García

Nombre Antonio Juan Jordi Laura

Direccion C. Mayor 2 Pl. Ayuntamiento C. Bull C. Lepant

Poblacion Valencia Alicante Sant Cugat Gavà

Provincia Valencia Alicante Barcelona Barcelona

Consulta de todos los datos, todos los registros: SELECT id, DNI, Apellidos, Nombre, Direccion, Poblacion, Provincia FROM Tabla_direcciones_empleados

SELECT * FROM Tabla_direcciones_empleados Selección de sólo algunas columnas, todos los registros: SELECT ID,DNI,Nombre,Apellidos FROM Tabla_direcciones_empleados id 1 2 3 4

DNI 23987739A 45623890F 43567893D 23456789G

Nombre Antonio Juan Jordi Laura

Apellidos García López Macías García

Funciones y operaciones sobre columnas También es posible hacer cálculos sobre alguna de las columnas devueltas. Para ello se utilizarán las funciones y operaciones que permite SQL. Ej.: select id,Nombre + ' ' + apellidos from tabla_direcciones_empleados. Esta sentencia devolvería 1 2 3 4

Antonio García Juan López Jordi Macías Laura García

select id,Nombre,LEN(Nombre ) from tabla_direcciones_empleados. Esta sentencia devuelve id, el nombre y el número de caracteres del nombre 1 2 3 4

Antonio Juan Jordi Laura

7 4 5 5

Otras operaciones de columna son: MIN: Calcula el valor mínimo de una columna. MAX: Calcula el valor máximo de una columna. AVG: Calcula la media aritmética de una columna. SUM: Calcula la suma de todos los campos de una columna. COUNT: Cuenta el n° de filas de una columna. AS En el caso anterior se puede asignar un nombre a la columna o alias usando la palabra reservada AS a la columna donde se calcula la longitud del nombre select id,Nombre,LEN(Nombre) as LongitudNombre from tabla_direcciones_empleados

5

id 1 2 3 4

Nombre Antonio Juan Jordi Laura

LongitudNombre 7 4 5 5

También se puede canviar el nombre de una columna por otro cualquiera. DISTINCT Distinct sirve para devolver todos los registros de una tabla pero sin duplicados. Por ejemplo en la sentencia select Provincia from tabla_direcciones_empleados seleccionaríamos las provincias de los trabajadores Provincia Valencia Alicante Barcelona Barcelona Como vemos, Barcelona se repite. Si sólo quisiéramos las provincias sin repeticiones se usaría DISTINCT. select distinct(Provincia) from tabla_direcciones_empleados Provincia Valencia Alicante Barcelona ALL ALL sirve para ver todos los registros (filas) incluidas las repeticiones. select ALL Provincia from tabla_direcciones_empleados Esta expresión es equivalente a: select Provincia from tabla_direcciones_empleados Provincia Valencia Alicante Barcelona Barcelona 4.1.2. WHERE Con las sentencias que hemos usado hasta ahora no es posible seleccionar un conjunto de datos según una condición concreta. Para ello se utiliza WHERE select * from tabla_direcciones_empleados where Nombre>'c' 2 3 4

45623890F 43567893D 23456789G

López Macías García

Juan Jordi Laura

Pl. Ayuntamiento C. Bull C. Lepant

Alicante Sant Cugat Gavà

Alicante Barcelona Barcelona

< search_condition > ::= { [ NOT ] | ( ) } [ { AND | OR } [ NOT ] { | ( ) } ] [ ,...n ]

6

::= { expression { = | < > | ! = | > | > = | ! > | < | < = | ! < } expression | string_expression [ NOT ] LIKE string_expression [ ESCAPE 'escape_character' ] | expression [ NOT ] BETWEEN expression AND expression | expression IS [ NOT ] NULL | CONTAINS ( { column | * } , '< contains_search_condition >' ) | FREETEXT ( { column | * } , 'freetext_string' ) | expression [ NOT ] IN ( subquery | expression [ ,...n ] ) | expression { = | < > | ! = | > | > = | ! > | < | < = | ! < } { ALL | SOME | ANY} ( subquery ) | EXISTS ( subquery ) } Argumentos

Especifica las condiciones de las filas devueltas en el conjunto de resultados de una instrucción SELECT, una expresión de consulta o una subconsulta. En una instrucción UPDATE, especifica las filas que se van a actualizar. En una instrucción DELETE, especifica las filas que se van a eliminar. No hay límite en el número de predicados que se pueden incluir en una condición de búsqueda de una instrucción Transact-SQL. NOT Niega la expresión booleana que especifica el predicado. AND Combina dos condiciones y se evalúa como TRUE cuando ambas condiciones son TRUE. OR Combina dos condiciones y se evalúa como TRUE cuando alguna de las condiciones es TRUE. < predicate > Es una expresión que devuelve TRUE, FALSE o UNKNOWN. expression Es un nombre de columna, una constante, una función, una variable, una subconsulta escalar o cualquier combinación de nombres de columna, constantes y funciones conectados mediante uno o varios operadores o una subconsulta. La expresión también puede contener la expresión CASE. = Es el operador que se utiliza para probar la igualdad entre dos expresiones. Es el operador que se utiliza para probar si dos expresiones no son iguales entre sí. != Es el operador que se utiliza para probar si dos expresiones no son iguales entre sí. > Es el operador que se utiliza para probar si una expresión es mayor que la otra. >= Es el operador que se utiliza para probar si una expresión es mayor o igual que la otra expresión. !> Es el operador que se utiliza para probar si una expresión no es mayor que la otra expresión. < Es el operador que se utiliza para probar si una expresión es menor que la otra. 'c' and Apellidos>'j' 2 3

45623890F 43567893D

López Macías

Juan Jordi

Pl. Ayuntamiento C. Bull

Alicante Sant Cugat

Alicante Barcelona

select * from tabla_direcciones_empleados where Nombre like 'j%' 2 3

45623890F 43567893D

López Macías

Juan Jordi

Pl. Ayuntamiento C. Bull

Alicante Sant Cugat

Alicante Barcelona

select * from tabla_direcciones_empleados where Nombre like '%or%' 3

43567893D

Macías

Jordi

C. Bull

Sant Cugat

Barcelona

select * from tabla_direcciones_empleados where Provincia between 'A' and 'M' 2

45623890F

López

Juan

Pl. Ayuntamiento

Alicante

Alicante

8

3 4

43567893D 23456789G

Macías García

Jordi Laura

C. Bull C. Lepant

Sant Cugat Gavà

Barcelona Barcelona

select * from tabla_direcciones_empleados where Provincia in ('Alicante','Madrid','Valencia') 1 2

23987739A 45623890F

García López

Antonio Juan

C. Mayor 2 Pl. Ayuntamiento

Valencia Alicante

Valencia Alicante

4.1.3. ORDER BY ORDER BY sirve para escoger el orden de las filas de la consulta. Su sintaxis es: ORDER BY col1 [ASC | DESC], col2 [ASC | DESC]… Por defecto el orden siempre es ascendente select * from tabla_direcciones_empleados order by apellidos asc, Nombre Desc 4 1 2 3

23456789G 23987739A 45623890F 43567893D

García García López Macías

Laura Antonio Juan Jordi

C. Lepant C. Mayor 2 Pl. Ayuntamiento C. Bull

Gavà Valencia Alicante Sant Cugat

Barcelona Valencia Alicante Barcelona

4.1.4. GROUP BY Agrupa un conjunto de filas seleccionado en un conjunto de filas de resumen de acuerdo con los valores de una o más columnas o expresiones. Se devuelve una fila para cada grupo. No se puede poner en GROUP BY un campo que no se haya incluido en la sentencia SELECT. select COUNT(id) as empleadosProvincia, Provincia from tabla_direcciones_empleados group by provincial empleadosProvincia Provincia 1 Alicante 2 Barcelona 1 Valencia 4.1.5. HAVING Especifica una condición de búsqueda para un grupo o agregado. HAVING sólo se puede utilizar con la instrucción SELECT. Normalmente, HAVING se utiliza en una cláusula GROUP BY. Cuando no se utiliza GROUP BY, HAVING se comporta como una cláusula WHERE. select COUNT(id) as empleadosProvincia, Provincia from tabla_direcciones_empleados group by provincia having Provincia 'Alicante' empleadosProvincia 2 1

Provincia Barcelona Valencia

4.1.6. Subconsultas Una subconsulta es una consulta dentro de otra. Por ejemplo imaginemos ahora que tenemos una segunda tabla provincias con los siguientes datos: id 1 2 3 4

provincia Álava Albacete Alicante Almería

9

5 6 7

Barcelona Lugo Valencia

Si queremos sacar la lista de provincias que empiezan por a hacemos: select provincia from provincia where provincia like 'A%' Albacete Alicante Almería Ahora queremos sacar de la tabla tabla_direcciones_empleados la lista de empleados que viven en una de las provincias de la consulta anterior. select id, Nombre, Apellidos, Provincia from Tabla_direcciones_empleados where Provincia in ( select provincia from provincia where provincia like 'A%' ) 2

Juan

López

Alicante

4.1.7. JOIN Las combinaciones externas devuelven todas las filas de una de las tablas o vistas mencionadas en la cláusula FROM, como mínimo, siempre que tales filas cumplan con alguna de las condiciones de búsqueda de WHERE o HAVING. Todas las filas se recuperarán de la tabla izquierda a la que se haya hecho referencia con una combinación externa izquierda, y de la tabla derecha a la que se haya hecho referencia con una combinación externa derecha. En una combinación externa completa, se devuelven todas las filas de ambas tablas. SQL Server usa las siguientes palabras clave ISO para las combinaciones externas especificadas en una cláusula FROM: • • •

LEFT OUTER JOIN o LEFT JOIN RIGHT OUTER JOIN o RIGHT JOIN FULL OUTER JOIN o FULL JOIN

LEFT JOIN Imagine una combinación de la tabla Product y de la tabla ProductReview en sus columnas ProductID. Los resultados muestran solamente los productos para los que se han escrito revisiones. Para incluir todos los productos, independientemente de si se ha escrito una revisión para alguno de ellos, utilice una combinación externa izquierda ISO. Ésta es la consulta: USE AdventureWorks; GO SELECT p.Name, pr.ProductReviewID FROM Production.Product p LEFT OUTER JOIN Production.ProductReview pr ON p.ProductID = pr.ProductID LEFT OUTER JOIN incluye en el resultado todas las filas de la tabla Product, tanto si hay una coincidencia en la columna ProductID de la tabla ProductReview como si no la hay. Observe que en

10

los resultados donde no hay un Id. de revisión de producto coincidente para un producto, la fila contiene un valor nulo en la columna ProductReviewID. select t1.id, Nombre, Apellidos, t2.provincia from Tabla_direcciones_empleados as t1 left join provincia as t2 on t1.id_provincia = t2.id order by t1.id 1 2 3 4

Antonio Juan Jordi Laura

García López Macías García

Valencia Alicante Barcelona Barcelona

RIGHT JOIN Imagine una combinación de la tabla SalesTerritory y de la tabla SalesPerson en sus columnas TerritoryID. Los resultados pueden mostrar cualquier territorio de ventas asignado a un vendedor. El operador ISO de combinación externa derecha, RIGHT OUTER JOIN, indica que todas las filas de la segunda tabla se deben incluir en los resultados, con independencia de si hay datos coincidentes en la primera tabla. Para incluir a todos los vendedores en los resultados, independientemente de si están asignados a un territorio de ventas, utilice una combinación externa derecha ISO. A continuación se muestra la consulta Transact-SQL y los resultados de la combinación externa derecha: USE AdventureWorks; GO SELECT st.Name AS Territory, sp.SalesPersonID FROM Sales.SalesTerritory st RIGHT OUTER JOIN Sales.SalesPerson sp ON st.TerritoryID = sp.TerritoryID ; Una combinación externa puede restringirse más mediante el uso de un predicado. Este ejemplo contiene la misma combinación externa derecha, pero sólo incluye los territorios de ventas con un volumen de ventas inferior a 2.000.000 de dólares: USE AdventureWorks; GO SELECT st.Name AS Territory, sp.SalesPersonID FROM Sales.SalesTerritory st RIGHT OUTER JOIN Sales.SalesPerson sp ON st.TerritoryID = sp.TerritoryID WHERE st.SalesYTD < $2000000;Para obtener más información acerca de estos predicados, vea WHERE (Transact-SQL). FULL OUTER JOIN Para retener la información que no coincida al incluir las filas no coincidentes en los resultados de una combinación, utilice una combinación externa completa. SQL Server proporciona el operador de combinación externa completa, FULL OUTER JOIN, que incluye todas las filas de ambas tablas, con independencia de que la otra tabla tenga o no un valor coincidente. Imagine una combinación de la tabla Product y de la tabla SalesOrderDetail en sus columnas ProductID. Los resultados sólo muestran los productos para los que se han efectuado pedidos de venta. El operador ISO de combinación externa completa, FULL OUTER JOIN, indica que todas las filas de ambas tablas se van a incluir en los resultados, con independencia de que haya datos coincidentes en las tablas. Puede incluir una cláusula WHERE con una combinación externa completa para devolver solamente las filas donde no hay datos coincidentes entre las tablas. La siguiente consulta sólo devuelve los productos que no tienen pedidos de venta que coincidan, así como los pedidos de venta que no

11

coinciden con ningún producto (aunque todos los pedidos de venta, en este caso, coincidan con un producto). 4.1.8. Combinaciones de consultas Además de JOIN se pueden hacer consultas con DB relacionales juntando dos o más tablas de la siguiente manera: select t1.id,nombre,apellidos,provincia from Tabla_direcciones_empleados as t1, provincia where id_provincia=provincia.id order by id 1 2 3 5

García López Macías García

Antonio Juan Jordi Laura

Barcelona Alicante Barcelona Barcelona

Sin embargo realizar este tipo de consultas es más lento que usar la cláusula JOIN. 4.1.9. UNION Combina los resultados de dos o más consultas en un solo conjunto de resultados que incluye todas las filas que pertenecen a las consultas de la unión. La operación UNION es distinta de la utilización de combinaciones de columnas de dos tablas. A continuación se muestran las reglas básicas para combinar los conjuntos de resultados de dos consultas con UNION: El número y el orden de las columnas deben ser idénticos en todas las consultas. Los tipos de datos deben ser compatibles. Sintaxis { | ( ) } UNION [ ALL ] | ! = | > | > = | ! > | < | < = | ! < } { SOME | ANY| ALL } ( subquery ) Argumentos scalar_expression Es cualquier expresión válida. { = | | != | > | >= | !> | < | all (select edad from premio ) 2

López

Juan

34

Selecciona todos los empleados que tengan derecho a premio por su edad. select t1.id, nombre, Apellidos, edad from Tabla_direcciones_empleados as t1 where edad = any (select edad from premio ) 1 3

García Macías

Antonio Jordi

21 23

4.1.12. EXISTS Se define para comprobar la existencia o ausencia del valor devuelto por una subconsulta. Una expresión con EXIST devuelve Cierto si la subconsulta nos devuelve al menos un valor. Ejemplo: devolver empleados que trabajan en una provincia que empiece por B o superior select nombre, apellidos from Tabla_direcciones_empleados as t1 where exists ( select provincia from provincia where provincia.id=t1.id_provincia and provincia>'B' ) García Macías García

Antonio Jordi Laura

Nota: se puede obtener el mismo resultado con otras expresiones como join o in. 4.2. Inserción de datos. INSERT La cláusula INSERT sirve para añadir una o varias nuevas filas a una tabla o una vista. Sintaxis INSERT [ INTO ]

15

table_or view_name [ ( column_list ) ] VALUES ( { DEFAULT | NULL | expression } [ ,...n ] ) [ ,...n ] INTO Es una palabra clave opcional que se puede utilizar entre INSERT y la tabla de destino. table_or view_name Es el nombre de la tabla o la vista que va a recibir los datos. Se puede utilizar una variable table en su ámbito como origen de tabla en una instrucción INSERT. ( column_list ) Es una lista de una o más columnas en las que se insertarán los datos. column_list se debe incluir entre paréntesis y delimitar con comas. Si la columna no se incluye en column_list, Database Engine (Motor de base de datos) debe ser capaz de proporcionar un valor basado en la definición de la columna; en caso contrario, no se puede cargar la fila. Database Engine (Motor de base de datos) proporciona automáticamente un valor para la columna si esta: • • • • •

Tiene una propiedad IDENTITY. Se usa el valor de identidad incremental siguiente. Tiene un valor predeterminado. Se usa el valor predeterminado de la columna. Tiene un tipo de datos timestamp. Se utiliza el valor actual de marca de tiempo. Acepta valores NULL. Se usa un valor NULL. Es una columna calculada. Se utiliza el valor calculado.

Se debe utilizar column_list y la lista VALUES al insertar valores explícitos en una columna de identidad. La opción SET IDENTITY_INSERT debe ser ON para la tabla. VALUES Presenta la lista o listas de valores de datos que se van a insertar. Debe haber un valor de datos por cada columna en column_list, si se especifica, o en la tabla. La lista de valores debe ir entre paréntesis. Si los valores de la lista VALUES no están en el mismo orden que las columnas de la tabla o no contienen un valor para cada columna de la tabla, se debe usar column_list para especificar de forma explícita la columna que almacenará cada valor de entrada. SQL Server 2008 incorpora el constructor de filas de Transact-SQL (que también se denomina constructor con valores de tabla) para especificar varias filas en una única instrucción INSERT. El constructor de filas se compone de una única cláusula VALUES con varias listas de valores escritos entre paréntesis y separados por una coma. La inserción de varias filas de valores requiere que la lista VALUES esté en el mismo orden que las columnas de la tabla, para tener un valor en cada columna, o que en column_list se especifique de forma explícita la columna en la que se almacena cada uno de los valores de entrada. El número máximo de filas que se pueden insertar en una instrucción INSERT única es 1000. Para insertar más de 1000 filas, cree varias instrucciones INSERT, o realice una importación masiva de datos mediante la utilidad bcp o la instrucción BULK INSERT. Nota: Cuando se hace referencia a los tipos de datos de caracteres Unicode nchar, nvarchar y ntext, debe agregarse como prefijo la letra mayúscula 'N' a 'expression'. Si no se especifica 'N', SQL Server convierte la cadena a la página de códigos que se corresponde con la intercalación predeterminada de la base de datos o columna. Los caracteres que no se encuentren en esta página de códigos se perderán. Ejemplo. Insertar los siguientes datos en la tabla provincias: Ciudad Real, Soria, Teruel, Cádiz.

16

use alumnetarda insert into provincia (provincia) values ('Ciudad Real'), ('Soria'), ('Teruel'), ('Cádiz') id 1 2 3 4 5 6 7 8 9 10 11

provincia Álava Albacete Alicante Almería Barcelona Lugo Valencia Ciudad Real Soria Teruel Cádiz

Añadir un nuevo empleado con los siguientes datos: Raquel Bustamante, DNI 45367953F, C. del Pollo Frito, L'Hospitalet, Barcelona, edad 20. use alumnetarda insert into Tabla_direcciones_empleados (DNI,Nombre,Apellidos,Direccion,Poblacion,id_provincia,edad) values ('45367953F','Raquel','Bustamante','C. del Pollo Frito','Hospitalet',5,20) 1 2 3 5 6

23987739A 45623890F 43567893D 23456789G 45367953F

García López Macías García Raquel

Antonio Juan Jordi Laura Bustamante

C. Mayor 2 Pl. Ayuntamiento C. Bull C. Lepant C. del Pollo Frito

Valencia Alicante Sant Cugat Gavà Hospitalet

7 3 5 5 5

21 34 23 26 20

4.3. Modificar Datos. UPDATE Cambia los datos de una tabla o vista. Sintaxis UPDATE table SET { column_name = { expression | DEFAULT | NULL } | column_name { += | -= | *= | /= | %= | &= | ^= | |= } expression } [ ,...n ] [ WHERE { }] [;] SET Especifica la lista de nombres de variable o de columna que se van a actualizar. Table Nombre de la tabla a modificar column_name Es una columna que contiene los datos que se van a cambiar. column_name debe existir en table_or view_name. Las columnas de identidad no se pueden actualizar.

17

expression Es una variable, un valor literal, una expresión o una instrucción de subselección entre paréntesis que devuelve un solo valor. El valor devuelto por expression sustituye al valor existente en column_name o @variable. DEFAULT Especifica que el valor predeterminado definido para la columna debe reemplazar al valor existente en esa columna. Esta operación también puede utilizarse para cambiar la columna a NULL si no tiene asignado ningún valor predeterminado y se ha definido para aceptar valores NULL. { += | -= | *= | /= | %= | &= | ^= | |= } Operador de asignación compuesta: • • • • • • • •

+= Sumar y asignar -= Restar y asignar *= Multiplicar y asignar /= Dividir y asignar %= Módulo y asignar &= AND bit a bit y asignar ^= XOR bit a bit y asignar |= OR bit a bit y asignar

WHERE Especifica las condiciones que limitan las filas que se actualizan.

Especifica la condición que debe cumplirse para que se actualicen las filas. La condición de búsqueda también puede ser la condición en la que se basa una combinación. El número de predicados que pueden incluirse en una condición de búsqueda no tiene límite. Ejemplo: Modifica la población de Raquel Bustamante y cambia L’Hospitalet por Viladecans use alumnetarda update Tabla_direcciones_empleados set Poblacion='Viladecans' where Nombre='Raquel' AND Apellidos='Bustamante' Id 1 2 3 5 6

DNI 23987739A 45623890F 43567893D 23456789G 45367953F

Nombre García López Macías García Raquel

Apellidos Antonio Juan Jordi Laura Bustamante

Direccion C. Mayor 2 Pl. Ayuntamiento C. Bull C. Lepant C. del Pollo Frito

Poblacion Valencia Alicante Sant Cugat Gavà Viladecans

id_provincia 7 3 5 5 5

edad 21 34 23 26 20

4.4. Borrar Datos. DELETE Quita filas de una tabla o vista. Sintaxis DELETE [ FROM < table_or view_name > ] [ WHERE { 30 select nombre, distancia from planeta else

24

select nombre, periodo,situacion from planeta En Transact-SQL no existe elseif, pero si se pueden hacer if anidados Ejemplo 2: condiciones anidadas declare @dis float = 20; declare @per float = 1; if @dis > 30 if @per>= 5 print 'dis y per mayores' else print 'dis mayor y per menor' else if @per>= 5 print 'dis menor y per mayor' else print 'dis y per menores' CASE Evalúa una lista de condiciones y devuelve una de las varias expresiones de resultado posibles. CASE admite un argumento ELSE opcional. CASE se puede utilizar en cualquier instrucción o cláusula que permite una expresión válida. Por ejemplo, puede utilizar CASE en instrucciones como SELECT, UPDATE, DELETE y SET, y en cláusulas como select_list, IN, WHERE, ORDER BY y HAVING. SQL Server sólo permite 10 niveles de anidamiento en las expresiones CASE. Sintaxis CASE input_expression WHEN expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END Ejemplo: En la BD sistema solar sacar un listado de planetas. Sustituir el campo situación por ‘exterior’ si vale ‘E’ e ‘interior’ si vale ‘I’ select nombre,situacion= case situacion when 'E' then 'Exterior' else 'Interior' end ,periodo, distancia from planeta Ejemplo: En la BD sistema solar sacar un listado de planetas. Añadir un campo DistanciaAlSol que valga ‘Muy lejano’ si distancia es mayor que 80, ‘Lejano’ si distancia entre 1 y 80, y ‘Próximo’ si distancia insertar nuevo satelite insert into satelite (nombre,id_planeta) values (@satelite,@idp) end -- else -- sí existe --> no hacer nada end else -- @idp is null --> no existe el planeta begin -- 2.2. No existe --> insertamos el planeta y luego los datos del satelite -- 2.2.1. insertamos nuevo planeta insert into planeta (nombre, situacion, distancia, periodo) values (@planeta, @situacion, @distancia, @periodo) -- 2.2.2. insertamos nuevo satelite -- @@identity contiene el último valor introducido en la db insert into satelite (nombre,id_planeta) values (@satelite,@@identity) end fetch next from fila into @planeta, @satelite, @situacion, @distancia, @periodo end deallocate fila; END Si intentamos ejecutar una sentencia insert en la vista veremos como se crean nuevos planetas si es necesario y se añaden los satelites: insert into PlanetasySatellites (satelite,planeta,situacion,distancia,periodo)

39

values ('sevilla', 'andalucia', 'E', 23.5,12.9), ('granada', 'andalucia', 'E', 28.5,23.9) Ejemplo 2: Envia run correo electrónico a una dirección. Nota: este código no funcionará si no se configuran correctamente el servidor SQL y el cortafuegos de windows. CREATE TRIGGER aviso ON planeta AFTER INSERT, UPDATE, DELETE AS begin EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Sistema ', @recipients = '[email protected]', @body = 'Se ha modificado correctamente la tabla planetas', @subject = 'SQL server mensaje'; end Más información en http://technet.microsoft.com/es-es/library/ms190307.aspx Ejemplo 3: Borrar datos en una vista. En la DB del sistema solar se va a crear un desencadenador instead of para borrar datos de un planeta y su satelite en la vista ‘PlanetasySatellites’ CREATE TRIGGER [dbo].[BorrarPlanetasySatelites] ON [dbo].[PlanetasySatellites] INSTEAD OF DELETE AS BEGIN -- 1. se pase lo que se pase al leer la tabla deleted obtendremos el planeta. -- 2. Ante la incapacidad de saber que se ha pasado se opta por borrar siempre el planeta. -- 3. Por integridad referencial, al borrar el planeta se borran los satélites correspondientes declare @satelite varchar(20), @planeta varchar(20); declare fila cursor for select planeta, satelite from deleted open fila fetch next from fila into @planeta, @satelite while @@FETCH_STATUS = 0 -- no última fila begin if @planeta is not null delete from planeta where nombre = @planeta fetch next from fila into @planeta, @satelite end deallocate fila; END Ejemplo 4: Modificar datos en una vista. En la DB del sistema solar se va a crear un desencadenador instead of para moodificar los datos de un planeta y su satelite en la vista ‘PlanetasySatellites’. CREATE TRIGGER [dbo].[ModificarPlanetasySatelites] ON [dbo].[PlanetasySatellites] INSTEAD OF UPDATE AS BEGIN -- Sólo se harán los cambios si el planeta y el satélite existen -- en la DB. En cualquier otro caso no se hará nada -- necesitamos dos cursores, uno paara los valores antíguos y otro para los modernos

40

declare @idp int=0, @ids int=0; declare @inssatelite varchar(20), @insplaneta varchar(20), @inssituacion varchar(1); declare @insdistancia float,@insperiodo float; declare @delsatelite varchar(20), @delplaneta varchar(20), @delsituacion varchar(1); declare @deldistancia float,@delperiodo float; -- dades noves declare insfila cursor for select planeta, satelite, situacion, distancia, periodo from inserted open insfila fetch next from insfila into @insplaneta, @inssatelite, @inssituacion, @insdistancia, @insperiodo -- dades antigues declare delfila cursor for select planeta, satelite, situacion, distancia, periodo from deleted open delfila fetch next from delfila into @delplaneta, @delsatelite, @delsituacion, @deldistancia, @delperiodo while @@FETCH_STATUS = 0 -- no última fila begin -- comprobamos si el planeta, satelite está en las tabla planeta, satelite -- en caso que no devuelva nada @idp, @ids valdrán NULL select @idp = (select id from planeta where nombre = @delplaneta) select @ids = (select id from satelite where nombre = @delsatelite) if @idp > 0 and @ids > 0 begin -- existen el planeta y el satelite --> modificar datos update planeta set nombre = @insplaneta, situacion = @inssituacion, distancia = @insdistancia, periodo = @insperiodo where id = @idp update satelite set nombre = @inssatelite where id = @ids end fetch next from insfila into @insplaneta, @inssatelite, @inssituacion, @insdistancia, @insperiodo fetch next from delfila into @delplaneta, @delsatelite, @delsituacion, @deldistancia, @delperiodo end deallocate insfila; deallocate delfila; END 5.5. Errores Para concer el resultado de una consulta se dispone d edos variables del sistema: • •

@@ROWCOUNT. Devuelve el número de filas afectadas por la última instrucción. Si es 0 no se hacho ninguna operación @@ERROR. Devuelve el número de error de la última instrucción ejecutada. Si es 0 no se ha producido error. Para obtener más información sobre una lista de mensajes de error (y sus números) que SQL Server puede devolver, vea las columnas de descripción y error de la tabla de sistema sysmessages en la base de datos master de SQL Server.

Ejemplo: borrar satelite ‘bujero’ delete from satelite where nombre = 'bujero' if @@ERROR 0 print 'Error de sintaxis en DELETE'

41

else begin if @@ROWCOUNT=0 print 'no existe el satelite en la tabla' else print 'se ha borrado correctamente el satélite' end

6. El lenguaje DDL DDL, Data Definition Language o lenguaje de definición de datos, es un conjunto de instucciones de SQL que sirve para crear, modificar y borrar bases de datos, tablas, vistas, índices, funciones y procedimientos. Las principales órdenes son: • • •

CREATE crear ALTER modificar DROP borrar. Drop no borra datos, borra elementos de la base de datos

Todas las órdenes que veremos en este tema se pueden realizar desde el entorno gráfico de SQL Server. 6.1. Base de datos CREATE DATABASE database_name [;] ALTER DATABASE database_name MODIFY NAME = new_database_name [;] DROP DATABASE database_name [;] Argumentos database_name, new_database_name Es el nombre de la nueva base de datos. Los nombres de base de datos deben ser únicos en una instancia de SQL Server y cumplir las reglas de los identificadores. database_name puede tener 123 caracteres como máximo más 5 más reservados por defecto parav el nombre de archivo lógico. Ejemplo: creamos la base de datos del sistema solar CREATE DATABASE SistemaSolar 6.2. Tablas 6.2.1. Create Table Sintaxis CREATE TABLE [nom_base_dades]. nom_taula ( nombre_columna tipo, nombre_columna tipo, … nombre_columna tipo ) [;] 6.2.1.1. Tipos de datos Los tiposde datos de las tablas son los mismos que se explicaron al hablar de tipos de variables de programación con SQL. Ejemplo: create table SistemaSolar.planeta

42

( nombre varchar(20), distancia float ) 6.2.1.2. Permitir/ prohibir valores nulos create table SistemaSolar.planeta ( nombre varchar(20) NOT NULL, distancia float, /* no poner nada equivale a poner que se admiten nulos*/ periodo float NULL ) 6.2.1.3. Valores por defecto. DEFAULT DEFAULT da un valor por defecto cuando se inserta una nueva columna. Esto es independiente de si en la columna se admiten o no nulos. create table planeta3 ( nombre varchar(20) NOT NULL, distancia float NOT NULL, periodo float DEFAULT 3.2, radio float NULL, habitable varchar(1) NOT NULL DEFAULT 'N' ) 6.2.1.4. Clave primaria e identidad. PRIMARY KEY, IDENTITY Con PRIMARY KEY e IDENTITY (inicio, incremento) se marca una clave primaria como autonumérica. create table planeta4 ( id int PRIMARY KEY IDENTITY (1,1), nombre varchar(20) NOT NULL, distancia float NOT NULL, periodo float DEFAULT 3.2, radio float NULL, habitable varchar(1) NOT NULL DEFAULT 'N' ) 6.2.1.5. Columnas sin repeticiones. UNIQUE create table planeta5 ( id int PRIMARY KEY IDENTITY (1,1), nombre varchar(20) UNIQUE NOT NULL, distancia float NOT NULL, periodo float DEFAULT 3.2, radio float NULL, habitable varchar(1) NOT NULL DEFAULT 'N' ) 6.2.1.6. Restricciones. CHECK () Las restricciones permiten acotar los valores posibles para las columnas. Por ejemplo, no tiene sentido hablar de distancias negativas o habitable sólo puede valer S o N. create table planeta6 ( id int PRIMARY KEY IDENTITY (1,1), nombre varchar(20) UNIQUE NOT NULL, distancia float NOT NULL check (distancia>=0), periodo float DEFAULT 3.2 check (periodo>=0), radio float NULL check (radio>=0),

43

habitable varchar(1) NOT NULL DEFAULT 'N' check (habitable in ('N','S')) ) 6.2.1.7. Claves externas Las claves externas sirven para definir relaciones entre tablas. Ejemplo: clave externa de satelite que relaciona la tabla planetas y la tabla satelite create table satelite2 ( id int PRIMARY KEY IDENTITY (1,1), nombre varchar(20) UNIQUE NOT NULL, id_planeta int NOT NULL REFERENCES planeta(id) ON DELETE CASCADE ON UPDATE CASCADE -- o -- id_planeta int NOT NULL FOREIGN KEY REFERENCES planeta(id) -- ON DELETE CASCADE ON UPDATE CASCADE ) o alternativamente: create table satelite7 ( id int PRIMARY KEY IDENTITY (1,1), nombre varchar(20) UNIQUE NOT NULL, id_planeta int NOT NULL, CONSTRAINT FK_Planeta_Satelite7 FOREIGN KEY (id_planeta) REFERENCES planeta(id) ON DELETE CASCADE ON UPDATE CASCADE ) Restricciones para las claves externas: ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } NO ACTION El Database Engine (Motor de base de datos) produce un error y se revierte la acción de eliminación de la fila de la tabla primaria. CASCADE Se eliminan las filas correspondientes de la tabla a la que se hace referencia si la fila se elimina de la tabla primaria. SET NULL Todos los valores que forman la clave externa se establecen en NULL si se elimina la fila correspondiente de la tabla primaria. Para ejecutar esta restricción, las columnas de clave externa deben admitir valores NULL. SET DEFAULT Todos los valores que forman la clave externa se establecen en los valores predeterminados si se elimina la fila correspondiente de la tabla primaria. Para ejecutar esta restricción, todas las columnas de clave externa deben tener valores predeterminados. Si la columna admite valores NULL y no hay ningún valor predeterminado establecido de forma explícita, NULL se convierte en el valor predeterminado implícito de la columna. 6.2.1.8. Columnas calculadas También es posible definir una columna como calculada a partir de otras columnas de la tabla. Es una expresión que define el valor de una columna calculada. Una columna calculada es una columna virtual que no está almacenada físicamente en la tabla, a menos que la columna esté marcada con PERSISTED. La columna se calcula a partir de una expresión que utiliza otras columnas de la misma tabla. La expresión puede ser un nombre de columna no calculada, una

44

constante, una función, una variable o cualquier combinación de estos elementos conectados mediante uno o más operadores. La expresión no puede ser una subconsulta ni contener tipos de datos de alias. Las columnas calculadas se pueden utilizar en listas de selección, cláusulas WHERE, cláusulas ORDER BY u otras ubicaciones en que se puedan utilizar expresiones regulares, con las siguientes excepciones: • Una columna calculada no puede utilizarse como definición de restricción DEFAULT o FOREIGN KEY ni como definición de restricción NOT NULL. • Una columna calculada no puede ser el destino de una instrucción INSERT o UPDATE. create table planeta6 ( id int PRIMARY KEY IDENTITY (1,1), nombre varchar(20) UNIQUE NOT NULL, distancia float NOT NULL check (distancia>=0), periodo float DEFAULT 3.2 check (periodo>=0), radio float NULL check (radio=0), habitable varchar(1) NOT NULL DEFAULT 'N' check (habitable in ('N','S')), Len_planeta AS LEN(nombre) ) 6.2.2. Alter Table Cambiar el nombre de una tabla EXEC sp_rename ‘nombre_antiguo’, ‘nombre_nuevo’[;] Añadir columnas Alter table nombre_tabla add nombre_columna tipo restricciones Ejemplo: alter table satelite add radio float not null; Eliminar columnas Alter table nombre_tabla drop nombre_columna Ejemplo: alter table satelite add radio float not null; Una columna no puede quitarse cuando: • • • •

Se utiliza en un índice. Se utiliza en una restricción CHECK, FOREIGN KEY, UNIQUE o PRIMARY KEY. Está asociada con un valor predeterminado definido con la palabra clave DEFAULT o enlazada a un objeto predeterminado. Está enlazada a una regla.

Alterar columnas Alter table nombre_tabla ALTER COLUMN nombre_col nuevo_tipo restricciones Sólo se puede cambiar el tipo o las restricciones. La columna modificada no puede ser ninguna de las siguientes: • • •

Ser una columna con un tipo de datos timestamp. Ser una columna calculada ni utilizarse en una columna calculada. Una que se use en un índice, a menos que la columna sea del tipo de datos varchar, nvarchar o varbinary, el tipo de datos no se cambie, el nuevo tamaño sea igual o mayor que el tamaño anterior, y el índice no sea el resultado de una restricción PRIMARY KEY.

45

•

• •

Una que se use en las estadísticas generadas por la instrucción CREATE STATISTICS a menos que la columna sea del tipo de datos varchar, nvarchar o varbinary, el tipo de datos no se cambie y el nuevo tamaño sea igual o mayor que el tamaño anterior, o si la columna se cambia de NOT NULL a NULL. Utilizarse en una restricción PRIMARY KEY o [FOREIGN KEY] REFERENCES. Utilizarse en una restricción CHECK o UNIQUE. Sin embargo, se permite el cambio de longitud de una columna de longitud variable en una restricción CHECK o UNIQUE.

Ejemplo: añadir una clave externa a la tabla satelite alter table satelite add CONSTRAINT FK_Planeta_Satelite2 FOREIGN KEY (id_planeta) REFERENCES planeta(id) ON DELETE CASCADE ON UPDATE CASCADE Ejemplo: añadir una restricción a la tabla planeta alter table planeta add CONSTRAINT CK_Planeta_distancia check (distancia>=0) Renombrar columnas EXEC sp_rename ‘tabla.nom_columna’, 'nuevo_nombre', 'COLUMN'; Ejemplo: EXEC sp_rename 'satelite.nombre’, 'nom', 'COLUMN'; Activar/ desactivar trigger ALTER TABLE nom_taula [ENABLE | DISABLE] TRIGGER nombre_trigger; También se pueden poner y quitar índices, restricciones check, claves externas, etc. 6.2.3. Drop Table Quita una o varias definiciones de tabla y todos los datos, índices, desencadenadores, restricciones y especificaciones de permisos de esas tablas. Las vistas o procedimientos almacenados que hagan referencia a la tabla quitada se deben quitar explícitamente con DROP VIEW o DROP PROCEDURE. DROP TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name [ ,...n ] [ ; ] No se puede utilizar DROP TABLE para quitar una tabla a la que haga referencia una restricción FOREIGN KEY. Primero se debe quitar la restricción FOREIGN KEY o la tabla de referencia. Si la tabla de referencia y la tabla que tiene la clave principal se van a quitar en la misma instrucción DROP TABLE, la tabla de referencia debe aparecer primero. Ejemplo: Drop table satelite 6.3. Vistas 6.3.1. Create view CREATE VIEW [ schema_name . ] view_name AS select_statement [ ; ] Ejemplo: create view PlanetasySatellites as SELECT TOP (100) PERCENT dbo.satelite.id, dbo.satelite.nombre AS satelite, dbo.planeta.nombre AS planeta, dbo.planeta.situacion, dbo.planeta.distancia, dbo.planeta.periodo FROM dbo.planeta INNER JOIN dbo.satelite ON dbo.planeta.id = dbo.satelite.id_planeta ORDER BY planeta, satelite

46

6.3.2. Alter view ALTER VIEW [ schema_name . ] view_name AS select_statement [ ; ] Ejemplo: ALTER VIEW PlanetasySatellites AS SELECT TOP (100) PERCENT dbo.satelite.id, dbo.satelite.nombre AS satelite, dbo.planeta.nombre AS planeta, dbo.planeta.situacion, dbo.planeta.distancia, dbo.planeta.periodo FROM dbo.planeta INNER JOIN dbo.satelite ON dbo.planeta.id = dbo.satelite.id_planeta ORDER BY planeta, satelite 6.3.3. Drop view DROP VIEW view_name Ejemplo: Dropp View PlanetasySatellites 6.4. Índices Los índices son objetos que van ligados a las tablas o vistas y sirven para agilizar las búsquedas en las tablas. Cuando se realiza una búsqueda en una tabla por una columna no ordenada, esta se ha de hacer recorriendo todas las filas de la tabla hasta el final Si estuvieran ordenadas, la búsqueda sería más rápida ya que se podrían aplicar algoristmos de búsqueda. Con los índices, se crea una estructura asociada a la taabla original donde se guardan únicamente las columnas indizadas de forma ordenada. La presencia de índices mejora mucho el rendimiento en búsquedas, pero penaliza el resto de operaciones (borrado, inserción y modificación). En tablas con pocos datos, no es conveniente indexar porque no se gana mucha eficiencia. Directrices generales para diseñar índices: http://msdn.microsoft.com/es-es/library/ms191195.aspx 6.4.1. Create index CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON ( column [ ASC | DESC ] [ ,...n ] ) [ INCLUDE ( column_name [ ,...n ] ) ] [;] ::= { [ database_name. [ schema_name ] . | schema_name. ] table_or_view_name }

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON ( column_name [ ASC | DESC ] [ ,...n ] ) ::= { [ database_name. [ owner_name ] . | owner_name. ] table_or_view_name } Argumentos UNIQUE

47

Crea un índice único en una tabla o vista. Un índice único es aquel en el que no se permite que dos filas tengan el mismo valor de clave del índice. El índice clúster de una vista debe ser único. Las columnas que se utilizan en un índice único se deben establecer en NOT NULL, dado que varios valores NULL se consideran duplicados cuando se crea un índice único. CLUSTERED Crea un índice en el que el orden lógico de los valores de clave determina el orden físico de las filas correspondientes de la tabla. El nivel inferior, u hoja, de un índice clúster contiene las filas de datos reales de la tabla. Una tabla o vista permite un índice clúster al mismo tiempo. Si no se especifica CLUSTERED, se crea un índice no clúster. NONCLUSTERED Crea un índice que especifica la ordenación lógica de una tabla. Con un índice no clúster, el orden físico de las filas de datos es independiente del orden indizado. El valor predeterminado es NONCLUSTERED. index_name Es el nombre del índice. Los nombres de índice deben ser únicos en una tabla o vista, pero no es necesario que sean únicos en una base de datos. column Es la columna o columnas en las que se basa el índice. Especifique dos o más nombres de columna para crear un índice compuesto sobre los valores combinados de las columnas especificadas. Enumere las columnas que desee incluir en el índice compuesto (en orden de prioridad) entre paréntesis después de table_or_view_name. [ ASC | DESC ] Determina la dirección ascendente o descendente del orden de la columna de índice determinada. El valor predeterminado es ASC. INCLUDE ( column [ ,... n ] ) Especifica las columnas sin clave que se agregarán en el nivel hoja del índice no clúster. El índice no clúster puede ser único o no único. Ejemplo: Crear un índice no agrupado con el título de la tabla canción. CREATE NONCLUSTERED INDEX ix_titulo ON dbo.cancion (titulo ASC) 6.4.2. Alter index Modifica un índice existente de una tabla o una vista (relacional o XML) mediante su deshabilitación, regeneración o reorganización, o mediante el establecimiento de sus opciones. Sintaxis ALTER INDEX { index_name | ALL } ON table_or_view_name { REBUILD | DISABLE | REORGANIZE | SET ( [ ,...n ] ) } [;] Argumentos index_name Es el nombre del índice. Los nombres de índice deben ser únicos en una tabla o vista, pero no es necesario que sean únicos en una base de datos. ALL

48

Especifica todos los índices asociados a la tabla o vista independientemente del tipo de índice. table_or_view_name Es el nombre de la tabla o vista asociada al índice. REBUILD Especifica que el índice se volverá a generar con unas columnas, un tipo de índice, un atributo de unicidad y un criterio de ordenación idénticos. El proceso de volver a generar un índice quita y vuelve a crear el índice. Quita la fragmentación, utiliza espacio en disco al compactar las páginas según el valor de factor de relleno especificado o existente y vuelve a ordenar las filas del índice en páginas contiguas. Cuando se especifica ALL, todos los índices de la tabla se quitan y se vuelven a generar en una única transacción. DISABLE Marca el índice como deshabilitado y no disponible para Database Engine (Motor de base de datos). Cualquier índice puede estar deshabilitado. La deshabilitación de un índice evita que los usuarios obtengan acceso al índice y, en los índices clúster, a los datos de la tabla subyacente. REORGANIZE Especifica que el nivel hoja del índice se va a reorganizar. No es posible especificar REORGANIZE para un índice deshabilitado o con ALLOW_PAGE_LOCKS establecido en OFF. La reorganización de un índice utiliza muy pocos recursos del sistema. Desfragmenta el nivel hoja de los índices clúster y no clúster de las tablas y las vistas al volver a ordenar físicamente las páginas de nivel hoja para que coincidan con el orden lógico de los nodos hoja, de izquierda a derecha. La reorganización también compacta las páginas de índice. 6.4.3. Drop index DROP INDEX ix_titulo ON cancion

49