Consultas III: UNION-EXCEPT-INTERSECT

viernes, 25 de mayo de 2012

Consultas de operaciones con conjuntos.


A partir de SQL Server 2005 se permiten tres tipos de operaciones con conjuntos:

UNION: Disponible en todas las versiones de SQL Server, es ademas el mas usado.
EXCEPT: Nuevo en SQL Server 2005.
INTERSECT: Nuevo en SQL Server 2005.

Para utilizar operaciones de conjuntos debemos cumplir una serie de normas.
Las consultas a unir deben tener el mismo número campos, y además los campos deben ser del mismo tipo.
Sólo puede haber una única clausula ORDER BY al final de la sentencia SELECT.

UNION


UNION devuelve la suma de dos o más conjuntos de resultados. 
El conjunto obtenido como resultado de UNION tiene la misma estructura que los conjuntos originales.
El siguiente ejemplo muestra el uso de UNION

SELECT Nombre, ApellPaterno , ApellMaterno, FechaNacimiento
FROM EMPLEADOS
UNION SELECT Nombre, ApellPaterno , ApellMaterno, FechaNacimiento  
FROM CLIENTES



Cuando realizamos una consulta con UNION internamente se realiza una operacion DISTINCT sobre el conjunto de resultados final. Si queremos obtener todos los valores debemos utilizar 
UNION ALL.

 UNION ALL

SELECT Nombre, ApellPaterno , ApellMaterno, FechaNacimiento
FROM EMPLEADOS
UNION ALLSELECT Nombre, ApellPaterno , ApellMaterno, FechaNacimiento  
FROM CLIENTES 

EXCEPT


EXCEPT devuelve la diferencia (resta) de dos o más conjuntos de resultados. El conjunto obtenido como resultado de EXCEPT tiene la misma estructura que los conjuntos originales.
El siguiente ejemplo muestra el uso de EXCEPT

SELECT Nombre, ApellPaterno , ApellMaterno, FechaNacimiento
FROM EMPLEADOS
EXCEPTSELECT Nombre, ApellPaterno , ApellMaterno, FechaNacimiento  
FROM CLIENTES 


El uso de EXCEPT, como norma general, es mucho más rápido que utilizar condiciones NOT IN o EXISTS en la clausula WHERE.

INTERSECT

Devuelve la intersección entre dos o más conjuntos de resultados en uno. 
El conjunto obtenido como resultado de INTERSECT tiene la misma estructura que los conjuntos originales. El siguiente ejemplo muestra el uso de INTERSECT

SELECT Nombre, ApellPaterno , ApellMaterno, FechaNacimiento
FROM EMPLEADOS
INTERSECTSELECT Nombre, ApellPaterno , ApellMaterno, FechaNacimiento  
FROM CLIENTES 

Consulta de Tablas II

martes, 15 de mayo de 2012

GROUP BY


En esta parte veremos a varias clausulas entre ellas al GROUP BY, que se encarga de agrupar los registros de una selección según se le indique (suma, valor mínimo o máximo...)
La clausula GROUP BY combina los registros devueltos por una consulta SELECT obteniendo uno o varios valores agregados(suma, valor mínimo y máximo ...).

Para cada registro se puede crear un valor agregado si se incluye una función SQL agregada, como por ejemplo Sum o Count, en la instrucción SELECT. Como recordaran la sintaxis es:

SELECT [ALL | DISTINCT ] [TOP <n> [WITH TIES]] <nombre_campo> [{,<nombre_campo>}] [{,<funcion_agregado>}] 
FROM <nombre_tabla>|<nombre_vista> [{,<nombre_tabla>|<nombre_vista>}] 
[WHERE <condicion> [{ AND|OR <condicion>}]] [GROUP BY <nombre_campo> [{,<nombre_campo >}]] [HAVING <condicion>[{ AND|OR <condicion>}]] [ORDER BY <nombre_campo>|<indice_campo> [ASC | DESC] [{,<nombre_campo>|<indice_campo> [ASC | DESC ]}]]


Si se utiliza GROUP BY pero no existe una función SQL agregada en la instrucción SELECT se obtiene el mismo resultado que con una consulta SELECT DISTINCT. Los valores Null en los campos GROUP BY se agrupan y no se omiten. Sin embargo, los valores Null no se evalúan en ninguna de las funciones SQL agregadas.


Todos los campos de la lista de campos de SELECT deben incluirse en la cláusula GROUP BY o como argumentos de una función SQL agregada.


HAVING



HAVING , esta clausula permita escoger solo algunos de la filas, Una vez que GROUP BY ha combinado los registros, HAVING muestra cualquier registro agrupado por la cláusula GROUP BY que satisfaga las condiciones de la cláusula HAVING. Se utiliza la cláusula WHERE para excluir aquellas filas que no desea agrupar, y la cláusula HAVING para filtrar los registros una vez agrupados. 

HAVING es parecido a WHERE, muestra qué registros se seleccionan pero después del agregado. Una vez que los registros se han agrupado utilizando GROUP BY, HAVING determina cuales de ellos se van a mostrar.
La sintaxis es la siguiente:
SELECT CLIENTES.NOMBRE, CLIENTES.APELLIDO1, CLIENTES.APELLIDO2, SUM(PRECIO) 
FROM DETALLE_PEDIDO
INNER JOIN PEDIDOS ON DETALLE_PEDIDO.CO_PEDIDO = PEDIDOS.CO_PEDIDO
INNER JOIN CLIENTES ON PEDIDOS.CO_CLIENTE = CLIENTES.CO_CLIENTE
WHERE CLIENTES.NOMBRE != 'UN NOMBRE'
GROUP BY CLIENTES.NOMBRE, CLIENTES.APELLIDO1, CLIENTES.APELLIDO2  
HAVING SUM(PRECIO) > 100


AVG

Calcula la media aritmética de un conjunto de valores contenidos en un campo especificado de una consulta. Su sintaxis es la siguiente


AVG(<expr>)


En donde expr representa el campo que contiene los datos numéricos para los que se desea calcular la media o una expresión que realiza un cálculo utilizando los datos de dicho campo. La media calculada por Avg es la media aritmética (la suma de los valores dividido por el número de valores). La función Avg no incluye ningún campo Null en el cálculo.


SELECT CLIENTES.NOMBRE,  CLIENTES.APELLIDO1, CLIENTES.APELLIDO2,AVG(PRECIO)  
FROM DETALLE_PEDIDO
INNER JOIN PEDIDOS ON DETALLE_PEDIDO.CO_PEDIDO = PEDIDOS.CO_PEDIDO
INNER JOIN CLIENTES ON PEDIDOS.CO_CLIENTE = CLIENTES.CO_CLIENTE
GROUP BY CLIENTES.NOMBRE, CLIENTES.APELLIDO1, CLIENTES.APELLIDO2




COUNT

Count, como indica su nombre cuenta las filas que existen en el resultado o la tabla

COUNT(<expr>) 

El siguiente ejemplo realiza una "cuenta" de los datos que hay en la tabla PRODUCTOS.

SELECT COUNT(*) FROM PRODUCTOS 
Otro Ejemplo

SELECT COUNT(*) FROM PEDIDOS

Otro mas:

SELECT CLIENTES.NOMBRE, COUNT(*) 
FROM PEDIDOS
INNER JOIN CLIENTES ON PEDIDOS.CO_CLIENTE = CLIENTES.CO_CLIENTE
GROUP BY CLIENTES.NOMBRE



MAX, MIN



Devuelven el mínimo o el máximo de un conjunto de valores contenidos en un campo especifico de una consulta. Su sintaxis es:


MIN(<expr>) MAX(<expr>)

En donde expr es el campo sobre el que se desea realizar el cálculo. Expr pueden incluir el nombre de un campo de una tabla, una constante o una función (la cual puede ser intrínseca o definida por el usuario pero no otras de las funciones agregadas de SQL).


SELECT CLIENTES.NOMBRE, MIN(PEDIDOS.FX_ALTA), MAX(PEDIDOS.FX_ALTA)
FROM PEDIDOS
INNER JOIN CLIENTES ON PEDIDOS.CO_CLIENTE = CLIENTES.CO_CLIENTE
GROUP BY CLIENTES.NOMBRE




SUM

Devuelve la suma del conjunto de valores contenido en un campo especifico de una consulta. Su sintaxis es:


SUM(<expr>) 

En donde expr respresenta el nombre del campo que contiene los datos que desean sumarse o una expresión que realiza un cálculo utilizando los datos de dichos campos. Los operandos de expr pueden incluir el nombre de un campo de una tabla, una constante o una función (la cual puede ser intrínseca o definida por el usuario pero no otras de las funciones agregadas de SQL).



SELECT CLIENTES.NOMBRE, SUM(PEDIDOS.TOTAL_PEDIDO) 
FROM PEDIDOS
INNER JOIN CLIENTES ON PEDIDOS.CO_CLIENTE = CLIENTES.CO_CLIENTE
GROUP BY CLIENTES.NOMBRE





Uso de Select TOP con consultas agregadas. Podemos utilizar SELECT TOP con consultas agregadas como con cualquier otra instruccion Transact SQL.
En estos casos, la clausula TOP se aplica despues de calcular el agregado, devolviendo las N filas indicadas.
En este escenario es posible que queramos obtener los N valores que satisfagan una condicion. Por ejemplo, queremos si queremos obtener los tres primeros clientes con mayores pedidos, usariamos una consulta parecida a esta:




SELECT TOP 3 CLIENTES.NOMBRE, SUM(DETALLE_PEDIDO.PRECIO)
FROM DETALLE_PEDIDO
INNER JOIN PEDIDOS ON DETALLE_PEDIDO.CO_PEDIDO = PEDIDOS.CO_PEDIDO
INNER JOIN CLIENTES ON PEDIDOS.CO_CLIENTE = CLIENTES.CO_CLIENTE
GROUP BY CLIENTES.NOMBRE
ORDER BY 2 -- SUM(DETALLE_PEDIDO.PRECIO_UNIDAD)



Sin embargo, puede darse el caso, de que el cuarto cliente devuelto por la consulta tenga un valor agragado identico al tercero, (es decir, estan empatados). El uso de TOP 3 discriminaría el cuarto registro. Para evitar este comportamiento, y que la consulta devuelva también al cuarto cliente utilizamos la clausula WITH TIES.

SELECT TOP 3 WITH TIES CLIENTES.NOMBRE, SUM(DETALLE_PEDIDO.PRECIO)
FROM DETALLE_PEDIDO
INNER JOIN PEDIDOS ON DETALLE_PEDIDO.CO_PEDIDO = PEDIDOS.CO_PEDIDO
INNER JOIN CLIENTES ON PEDIDOS.CO_CLIENTE = CLIENTES.CO_CLIENTE
GROUP BY CLIENTES.NOMBRE
ORDER BY 2 -- SUM(DETALLE_PEDIDO.PRECIO_UNIDAD)




Algunas Tablas para recordar:

Operadores matemáticos:
>Mayor que
<Menor que
>=Mayor o igual que
<=Menor o igual que
<>Distinto
=Igual


Operadores lógicos
And
Or
Not


Otros operadores
LikeSelecciona los registros cuyo valor de campo se asemeje, no teniendo en cuenta mayúsculas y minúsculas.
In y Not InDa un conjunto de valores para un campo para los cuales la condición de selección es (o no) valida
Is Null y Is Not NullSelecciona aquellos registros donde el campo especificado esta (o no) vacío.
Between...AndSelecciona los registros comprendidos en un intervalo
DistinctSelecciona los registros no coincidentes
DescClasifica los registros por orden inverso


Comodines
*Sustituye a todos los campos
%Sustituye a cualquier cosa o nada dentro de una cadena
_Sustituye un solo carácter dentro de una cadena

FunciónDescripción
Sum(campo)Calcula la suma de los registros del campo especificado
Avg(Campo)Calcula la media de los registros del campo especificado
Count(*)Nos proporciona el valor del numero de registros que han sido seleccionados
Max(Campo)Nos indica cual es el valor máximo del campo
Min(Campo)Nos indica cual es el valor mínimo del campo


Consulta de Tablas I

lunes, 14 de mayo de 2012

Para seleccionar Datos es importante conocer la palabra: SELECT


SELECT

La sentencia SELECT permite consultar los datos almacenados en la tabla deseada de la base de datos con la que se trabaja.
La sintaxis es simple:
SELECT  <nombre_campos>  FROM <nombre_tabla> WHERE <condicion>

Sin embargo para aquellos que ya conozcan un poco de sintaxis SQL pueden tomar como base esta sentencia mas completa:

SELECT [ALL | DISTINCT ][ TOP expression [ PERCENT ] [ WITH TIES ] ] 
<nombre_campos>
FROM <nombre_tabla>
[ INNER | LEFT [OUTER]| RIGHT [OUTER] | CROSS][JOIN ] <nombre_tabla> ON <condicion_join>[ AND|OR <condicion>][WHERE <condicion> [ AND|OR <condicion>]][GROUP BY <nombre_campos>][HAVING <condicion>[ AND|OR <condicion>]][ORDER BY <nombre_campo> [ASC | DESC]La cláusula WHERE



Poco a poco iremos viendo cada una de estas sentencias, mientras solo veremos la primera forma.

El ejemplo a continuación muestra una consulta sencilla y simple de selección de datos (todas las filas existentes) de una tabla llamada CURSOS, obtendremos el código y el nombre del curso.


SELECT COD_CURSO, NOM_CURSO FROM CURSO


Podemos también modificar los nombres de los campos por el uso de un asterisco, el uso del asterisco indica que queremos que la consulta devuelva todos los campos que existen en la tabla (ojo todas las columnas no todas las filas).


SELECT * FROM  CURSO 


INNER JOIN

Ahora seleccionaremos no solo el curso sino también el profesor y la sede


SELECT *
FROM CURSO
INNER JOIN PROFESOR
ON  PROFESOR .COD_PROFESOR = CURSO.COD_PROFESOR 
INNER JOIN SEDES
ON  SEDES .COD_SEDE=  CURSO .COD_SEDE


La combinación se realiza a través de la clausula INNER JOIN, que es una clasula exclusiva, es decir los cursos que no tengan profesores y sedes asociadas no se devolverán.

LEFT JOIN

Si queremos realizar la consulta para que no sea exclusiva, tenemos que utilizar LEFT JOIN. El uso de la palabra reservada OUTER es opcional.



SELECT *
FROM CURSO
LEFT OUTER  JOIN PROFESOR
ON  PROFESOR .COD_PROFESOR = CURSO.COD_PROFESOR 
LEFT JOIN SEDES
ON  SEDES .COD_SEDE=  CURSO .COD_SEDE



Los registros que no tengan datos relacionados en una consulta LEFT JOIN devolveran en valor null en los campos que correspondan a las tablas en las que no tienen dato.


CROSS JOIN



También podemos forzar un producto cartesiano (todos con todos) a través de CROSS JOIN.


SELECT * FROM CURSO
CROSS JOIN PROFESOR


WHERE

Gracias a la clausula WHERE podemos filtrar los resultados de nuestro query, veamos su uso.


SELECT COD_CURSO, NOM_CURSO FROM CURSO WHERE  COD_CURSO = 1


Podemos especificar varias condiciones para el WHERE:


SELECT COD_CURSO, NOM_CURSO 
FROM CURSO 
WHERE  COD_CURSO = 1 OR COD_CURSO = 2



La clausula WHERE se puede utilizar conjuntamente con INNER JOIN, LEFT JOIN ...



SELECT *
FROM CURSO
INNER JOIN PROFESOR
ON  PROFESOR .COD_PROFESOR = CURSO.COD_PROFESOR 
WHERE  CURSO . COD_CURSO   > 1


Siempre que incluyamos un valor alfanumerico para un campo en la condición WHERE este debe ir entre comillas simples:



SELECT *
FROM CURSO
WHERE  NOM_CURSO = 'CURSO 1'

IN

Gracias al IN es posible consultar varios valores en una sola condicion.


SELECT * FROM CURSO WHERE  COD_CURSO IN (1,2)


LIKE


Otra herramienta muy buena es la clausula LIKE que sirve para consultar campos alfanuméricos no exactos sino por aproximación.




SELECT * FROM  CURSO  WHERE NOM_CURSO LIKE 'CURSO %'


Los comodines que podemos utilizar en son los siguientes:
% , representa cualquier cadena de texto de cero o más caracteres de cualquier longitud.
_ , representa un caracter.
[a-d], representa cualquier caracter del intervalo a-d.
[abcd], representa cualquier caracter del grupo abcd.
[^a-d], representa cualquier caracter diferente del intervalo a-d.
[^abcd], representa cualquier caracter distinto del grupo abcd.


DISTINCT

También podemos obtener los valores distintos utilizando DISTINCT.


SELECT DISTINCT  NOM_CURSO   FROM CURSO 


Devuelve los distintos nombres de Curso (no se repiten)


TOP

Podemos limitar el número de registros que devuelve la consulta a través de la clausula TOP. La clausula TOP admite como parámetros un valor numérico entero o un porcentaje (sólo a partir de la version 2005)




SELECT TOP 10 * FROM CURSO  


Devuelve 10 registros


SELECT TOP 50 PERCENT * FROM CURSO  


Devuelve el 50% de los registros


La clausula TOP se puede combinar con WITH TIES en consultas agregadas.




ORDER BY

Podemos especificar el orden en el que serán devueltos los datos a través de la cláusula ORDER BY.


SELECT COD_ CURSO, NOM_CURSO
FROM CURSO  
ORDER BY  NOM_CURSO DESC


También podemos indicar el índice del campo en la lista de selección en lugar de su nombre :



SELECT COD_ CURSO, NOM_CURSO FROM CURSO  ORDER BY  2 DESC



Ordena por nombre de Curso, porque en la selección es la columna numero 2.

Borrar Datos: Delete

jueves, 10 de mayo de 2012


Delete

Para poder eliminar los datos de una tabla es necesaria la sentencia DELETE.
La sintaxis es asi:

DELETE FROM PAÍS

Esta sentencia borrara todas las filas de la tabla PAÍS
También podemos especificar que registros deseamos borrar a travez de la ya conocida pero aun no especificada clausula WHERE.
DELETE FROM PAÍS WHERE Id= '01'

Un dato Importante
Cuando borramos datos de una tabla, podemos obtener el número de filas que han sido afectadas por la instrucción a través de la variable @@RowCount.

El siguiente ejemplo ilustra el uso de @@RowCount.

DELETE FROM PAÍS WHERE Id=17
SELECT @@ROWCOUNT

Clausula OUTPUT

DECLARE @FILAS_BORRADAS TABLE
(
Id int,dato varchar(100),fx_alta datetime
)


DELETE FROM PAÍS 
OUTPUT DELETED.* INTO @FILAS_BORRADAS
WHERE Id=17
SELECT  *  from @FILAS_BORRADAS


Truncate Table

Tiene la misma función que DELETE sin el WHERE.

TRUNCATE TABLE PAÍS

Cuando trabajamos con TRUNCATE TABLE debemos tener en cuenta las siguientes consideraciones:
TRUNCATE TABLE no admite la clausula WHERE.
No podemos ejecutar TRUNCATE TABLE sobre tablas que sean "padres" en foreign keys.

Modificar Registros: Update

Para modificar los datos SQL provee una palabra reservada llamada UPDATE, esta sentencia permite la fácil actualización de uno o mas registros según sea el caso. La sintaxis es la siguiente:

UPDATE <nombre_tabla>
SET <campo1> = <valor1>
{[,<campo2> = <valor2>,...,<campoN> = <valorN>]}
[ WHERE <condicion>]



Ahora un Ejemplo:

UPDATE CLIENTES
SET
NOMBRE = 'Devjoker',
APELLIDO1 = 'Herrarte',
APELLIDO2 = 'Sánchez'
WHERE CO_CLIENTE = 10



Update INNER JOIN

En ocasiones queremos actaualizar los datos de una tabla con los datos de otra (muy común para desnormalizar un modelo de datos).
Habitualmente, usamos subconsultas para este proposito, pero Transact SQL permite la utilización de la sentencia UPDATE INNER JOIN.

UPDATE CLIENTES
SET
NOMBRE = FICHERO_CLIENTES.NOMBRE,
APELLIDO1 = FICHERO_CLIENTES.APELLIDO1,
APELLIDO2 = FICHERO_CLIENTES.APELLIDO2
FROM CLIENTES
INNER JOIN FICHERO_CLIENTES
ON FICHERO_CLIENTES.CO_CLIENTE = CLIENTES.CO_CLIENTE

Clausula OUTPUT

A partir de la version de SQL Server 2005 disponemos de la clausula OUTPUT para recuperar los valores que hemos insertado. Al igual que en un trigger disponemos de las tablas lógicas INSERTED y DELETED.
Las columnas con prefijo DELETED reflejan el valor antes de que se complete la instrucción UPDATE o DELETE. Es decir, son una copia de los datos "antes" del cambio.
DELETED no se puede utilizar con la cláusula OUTPUT en la instrucción INSERT.


DECLARE @FILAS_ACTUALIZADAS TABLE
( CO_CLIENTE int ,
NOMBRE varchar(100),
APELLIDO1 varchar(100),
APELLIDO2 varchar(100)
)


UPDATE CLIENTES
SET
NOMBRE = 'Devjoker',
APELLIDO1 = 'Herrarte',
APELLIDO2 = 'Sánchez'
OUTPUT DELETED.* INTO @FILAS_ACTUALIZADAS
WHERE CO_CLIENTE IN (10, 11, 12)


SELECT * FROM @FILAS_ACTUALIZADAS

Las columnas con prefijo INSERTED reflejan el valor después de que se complete la instrucción UPDATE o INSERT, pero antes de que se ejecuten los desencadenadores. Es decir, son una copia de los datos "despues" del cambio.
INSERTED no se puede utilizar con la cláusula OUTPUT en la instrucción DELETE.


DECLARE @FILAS_ACTUALIZADAS TABLE


( CO_CLIENTE int ,
NOMBRE varchar(100),
APELLIDO1 varchar(100),
APELLIDO2 varchar(100)
)


UPDATE CLIENTES
SET
NOMBRE = 'Devjoker',
APELLIDO1 = 'Herrarte',
APELLIDO2 = 'Sánchez'
OUTPUT INSERTED.* INTO @FILAS_ACTUALIZADAS
WHERE CO_CLIENTE IN (10, 11, 12)


SELECT * FROM @FILAS_ACTUALIZADAS

Nuevo Registro: Añadir Filas

Bueno, añadir registros es muy sencillo, ahora veremos 3 maneras de agregar registros y un valor agregado que veremos mas adelante, empecemos.

Inserción individual de filas.

La inserción individual de filas es la mas antigua y la mas común, su sentencia es INSERT INTO, y su sintaxis completa la vemos a continuación.

INSERT INTO <nombre_tabla>
[(<campo1>[,<campo2>,...])]
values
(<valor1>,<valor2>,...)


El siguiente ejemplo muestra la inserción de un registro en la tabla PRECIOS.

INSERT INTO MERCADERIA
(CODIGO, DESCRIPCION, MARCA, FABRICA)
VALUES
('M01', 'Polos de varon','UNAMARCA', 'Textiles Polos')

Inserción múltiple de filas.

También es posible insertar en una tabla el resultado de una consulta SELECT. De este modo se insertarán tantas filas como haya devuelto la consulta SELECT.

El siguiente ejemplo muestra la inserción multiple de filas.

INSERT INTO MERCADERIA (CODIGO, DESCRIPCION, MARCA, FABRICA)  
SELECT 
(CODIGO, DESCRIPCION, MARCA, FABRICA)   
FROM DETALLE_PEDIDO

Inserción de valores por defecto.

También podemos forzar a que la insercción se realice con los datos por defecto establecidos para la tabla (o null si no tienen valores por defecto).

INSERT INTO PRECIOS DEFAULT VALUES

En SQL Sever podemos marcar un campo de una tabla como autonumérico (identity), cuando insertamos un registro en dicha tabla el valor del campo se genera automaticamente. Para recuperar el valor generado disponemos de varios métodos:
Utilizar la funcion @@identity, que devuelve el último valor identidad insertado por la transacción:

DECLARE @Codigo int
INSERT INTO PRECIOS
(PRECIO, FX_INICIO, FX_FIN, CO_PRODUCTO)
VALUES (10, getdate(),getdate()+30, 1)
set @Codigo = @@Identity
PRINT @Codigo

El uso de @@Identity no siempre es válido, ya que al devolver el úlitmo valor identidad insertado por la transacción, no nos garantiza que el valor haya sido insertado en la tabla que nos interesa (por ejemplo la tabla podría tener un trigger que insertara datos en otra tabla con campos identidad).
En este tipo de escenarios debemos utilizar la función, SCOPE_IDENTITY.

DECLARE @Codigo int
INSERT INTO PRECIOS
(PRECIO, FX_INICIO, FX_FIN, CO_PRODUCTO)
VALUES (10, getdate(),getdate()+30, 1)
SET @Codigo = SCOPE_IDENTITY()
PRINT @Codigo

Clausula OUTPUT
Ahora lo prometido, a partir de la version de SQL Server 2005 disponemos de la clausula OUTPUT para recuperar los valores que hemos insertado. Al igual que en un trigger disponemos de las tablas lógicas INSERTED y DELETED.

Las columnas con prefijo DELETED reflejan el valor antes de que se complete la instrucción UPDATE o DELETE. Es decir, son una copia de los datos "antes" del cambio.

DELETED no se puede utilizar con la cláusula OUTPUT en la instrucción INSERT.

Las columnas con prefijo INSERTED reflejan el valor después de que se complete la instrucción UPDATE o INSERT, pero antes de que se ejecuten los desencadenadores. Es decir, son una copia de los datos "despues" del cambio.

INSERTED no se puede utilizar con la cláusula OUTPUT en la instrucción DELETE.
Entonces del siguiente modo: 

DECLARE @FILAS_INSERTADAS TABLE
( CO_PRECIO int, PRECIO decimal, FX_INICIO datetime, FX_FIN datetime, CO_PRODUCTO int
)

INSERT INTO PRECIOS
(PRECIO, FX_INICIO, FX_FIN, CO_PRODUCTO)
OUTPUT INSERTED.* INTO @FILAS_INSERTADAS
VALUES (10, getdate(),getdate()+30, 1)

SELECT * FROM @FILAS_INSERTADAS

Constraints, Indices y Modificaciones al diseño de la Tabla

Ya vimos como crear una nueva tabla, lo que vamos a ver ahora es como agregarle a nuestra tabla Constraints y ademas hacerle modificaciones.
Recordemos:

Creación de Tablas Nuevas

CREATE TABLE tabla (
campo1 tipo (tamaño) índice1,
campo2 tipo (tamaño) índice2,... ,
índice multicampo , ... )

En donde:
tablaEs el nombre de la tabla que se va a crear.
campo1
campo2
Es el nombre del campo o de los campos que se van a crear en la nueva tabla. La nueva tabla debe contener, al menos, un campo.
tipoEs el tipo de datos de campo en la nueva tabla. (Ver Tipos de Datos)
tamañoEs el tamaño del campo.
índice1
índice2
Es una cláusula CONSTRAINT que define el tipo de índice a crear. Esta cláusula en opcional.
índice multicamposEs una cláusula CONSTRAINT que define el tipo de índice multicampos a crear. Un índice multicampo es aquel que está indexado por el contenido de varios campos. Esta cláusula es opcional.


Ahora veamos el siguiente ejemplo.

Ejemplo:
 Vamos a simular una base de datos para un negocio de alquiler de coches, por lo que vamos a empezar creando una tabla para almacenar los coches que tenemos.

CREATE TABLE tCoches

matricula                  char(8)   not null,
marca                      varchar(255)  null,
modelo                     varchar(255)  null,
color                      varchar(255)  null,
numero_kilometros          numeric(14,2) null default 0,
constraint PK_Coches primary key (matricula)


En esta caso se utilizo lo que se llama un constraint esto crea una nueva condición en la tabla se puede crear un costraint al momento de crear la tabla o como veremos mas adelante cuando se modifica.
La sintaxis es:
Para los índices de campos múltiples:

CONSTRAINT nombre {PRIMARY KEY (primario1[, primario2 [,...]]) |
UNIQUE (único1[, único2 [, ...]]) |
FOREIGN KEY (ref1[, ref2 [,...]]) REFERENCES tabla externa
[(campo externo1 ,campo externo2 [,...])]}

En donde:
nombreEs el nombre del índice que se va a crear.
primarioNEs el nombre del campo o de los campos que forman el índice primario.
únicoNEs el nombre del campo o de los campos que forman el índice de clave única.
refNEs el nombre del campo o de los campos que forman el índice externo (hacen referencia a campos de otra tabla).
tabla externaEs el nombre de la tabla que contiene el campo o los campos referenciados en refN
campos externosEs el nombre del campo o de los campos de la tabla externa especificados por ref1, ref2,... , refN

También:
IndiceDescripción
UNIQUEIndica que los valores de la columna en referencia deben ser únicos.
PRIMARY KEYGenera un índice primario el campo o los campos especificados. Todos los campos de la clave principal deben ser únicos y no nulos, cada tabla sólo puede contener una única clave principal.
FOREIGN KEYGenera un índice externo (toma como valor del índice campos contenidos en otras tablas). Si la clave principal de la tabla externa consta de más de un campo, se debe utilizar una definición de índice de múltiples campos, listando todos los campos de referencia, el nombre de la tabla externa, y los nombres de los campos referenciados en la tabla externa en el mismo orden que los campos de referencia listados. Si los campos referenciados son la clave principal de la tabla externa, no tiene que especificar los campos referenciados, predeterminado por valor, el motor Jet se comporta como si la clave principal de la tabla externa estuviera formada por los campos referenciados.

En ocasiones puede ser necesario modificar la estructura de una tabla, comúnmente para añadir un campo o restricción. Para ello disponemos de la instruccción ALTER TABLE.


ALTER TABLE nos va a permitir: 
  • Añadir campos a la estructura inicial de una tabla. 
  • Añadir restricciones y referencias. 
Para añadir un campo a una tabla existente, Esta es la sintaxis:


ALTER TABLE <nombre_tabla>
ADD <nombre_campo> <tipo_datos(tamaño)>
[null |not null] [default <valor_por_defecto>]
{
, <nombre_campo> <tipo_datos(tamaño)>
[null |not null] [default <valor_por_defecto>]}

Por Ejemplo para nuestra tabla coches:

ALTER TABLE tCoches
ADD num_plazas integer null default 5

En este ejemplo añadimos el campo num_plazas a la tabla tCoches.

Para añadir una clave primaria vamos a crear una tabla de cliente y le añadiremos la clave primaria ejecutando una sentencia alter table:


CREATE TABLE tClientes
 ( codigo      integer            not null,
   nombre     varchar(255)  not null,
   apellidos   varchar(255)  null, 
   nif             varchar(10)    null, 
   telefono    varchar(9)       null, 
   movil        varchar(9)       null
 )

ALTER TABLE tClientes 
ADD CONSTRAINT PK_tClientes  primary key (codigo)


Creamos la tabla clientes y le añadimos una reestricción primary key a la que damos el nombre PK_tClientes en el campo codigo.

Solo podemos modificar una única tabla a la vez con ALTER TABLE, para modificar más de una tabla debemos ejecutar una sentencia ALTER TABLE por tabla.

Para añadir una clave externa (o foranea) necesitamos una tercera tabla en nuestra estructura. Por un lado tenemos la tabla tCoches y la tabla tClientes, ahora vamos a crear la tabla tAlquileres que será la encargada de "decirnos" que clientes han alquilado un coche.

CREATE TABLE tAlquileres 
( codigo                integer   not null,
  codigo_cliente    integer    not null,
  matricula            char(8)   not null,
  fx_alquiler          datetime not null, 
  fx_devolucion    datetime null 
)

ALTER TABLE tAlquileres 
ADD CONSTRAINT PK_tAlquileres primary key (codigo), 
         CONSTRAINT FK_Clientes foreign key (codigo_cliente) references tClientes (Codigo),
         CONSTRAINT FK_Coches foreign key (matricula) references tCoches (matricula); 

Bien, en este código creamos la tabla tAlquileres, y luego mediante una sentencia ALTER TABLE añadimos una clave primaria llamada PK_tAlquileres en el campo codigo, una clave externa llamada FK_Clientes referenciada al codigo de la tabla tClientes, y por último otra clave externa llamada FK_Coches referenciada al campo matricula de la tabla tCoches.

Nota:Cuando creamos una clave externa el campo referenciado y el que sirve de referencia deben ser del mismo tipo de datos. Si somos observadores nos daremos cuenta que los campos que sirven de referencia a las claves foraneas son las claves primarias de sus tablas. Sólo podemos crear claves externas que referencien claves primarias.

Al igual que ocurria con la sentencia CREATE TABLE cada gestor de bases de datos implementa sus mejoras, siendo la mejor forma de conocerlas recurrir a la documentación del gestor de bases de datos. 

Podemos eliminar los constraint y los PK y FK si utilizamos la sentencia DROP.

Por Ejemplo:

ALTER TABLE
Pedidos
DROP CONSTRAINT RelacionPedidos
(Elimina el índice de la tabla Pedidos.)


O tambien podemos agregar columnas y eliminarlas

ALTER TABLE
Empleados
ADD COLUMN Salario numeric(5,2)
(Agrega un campo Salario de tipo Moneda a la tabla Empleados.)


ALTER TABLE
Empleados
DROP COLUMN Salario
(Elimina el campo Salario de la tabla Empleados.)


Eliminación de tablas.


Podemos eliminar una tabla de una base de datos mediante la instruccion DROP TABLE.
La sintaxis es así:

DROP TABLE <nombre_tabla>;


La instrucción DROP TABLE elimina de forma permanente la tabla y los datos en ella contenida.

Si intentamos eliminar una tabla que tenga registros relacionados a través de una clave externa la instrucción DROP TABLE fallará por integridad referencial. Es decir por restricciones en sus relaciones porque cuando eliminamos una tabla eliminamos también sus índices.







Power by Jolunf. Con la tecnología de Blogger.
 
Support : Creating Website | Johny Template | Mas Template
Copyright © 2011. Bases de Datos Jolunf - All Rights Reserved
Template Created by Creating Website Inspired by Sportapolis Shape5.com
Proudly powered by Blogger