Consultas III: UNION-EXCEPT-INTERSECT

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

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
Like	Selecciona los registros cuyo valor de campo se asemeje, no teniendo en cuenta mayúsculas y minúsculas.
In y Not In	Da 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 Null	Selecciona aquellos registros donde el campo especificado esta (o no) vacío.
Between...And	Selecciona los registros comprendidos en un intervalo
Distinct	Selecciona los registros no coincidentes
Desc	Clasifica 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ón	Descripció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

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

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:

tabla	Es 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.
tipo	Es el tipo de datos de campo en la nueva tabla. (Ver Tipos de Datos)
tamaño	Es 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 multicampos	Es 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:

nombre	Es el nombre del índice que se va a crear.
primarioN	Es el nombre del campo o de los campos que forman el índice primario.
únicoN	Es el nombre del campo o de los campos que forman el índice de clave única.
refN	Es el nombre del campo o de los campos que forman el índice externo (hacen referencia a campos de otra tabla).
tabla externa	Es el nombre de la tabla que contiene el campo o los campos referenciados en refN
campos externos	Es el nombre del campo o de los campos de la tabla externa especificados por ref1, ref2,... , refN

También:

Indice	Descripción
UNIQUE	Indica que los valores de la columna en referencia deben ser únicos.
PRIMARY KEY	Genera 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 KEY	Genera 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.

Creación de Tablas.

Actualmente los SGBD poseen editores prácticos y de rápido uso que permiten la creación de tabla sin mayor esfuerzo. Aun con todo esto, es necesario en muchas ocasiones crear tablas a nuestra medida de forma manual, por ejemplo cuando necesitamos crear tablas temporales, es muy útil conocer las sentencias requeridas para este fin.

SQL provee el lenguaje adecuado para poder crear nuevas tablas.

Bueno, para crear la tabla debemos de indicar los datos de esta, es decir su nombre, los nombres de los campos y sus características (tipo y capacidad). Ademas de indicar cuales de estos campos son PK (Primary Key).

La sintaxis del lenguaje puede variar de un SGBD a otro debido a las variedades anteriormente estudiadas.
Sin mas preámbulo la sintaxis de creación de una tabla.

Create Table nombre_tabla
(
nombre_campo_1    tipo_1
nombre_campo_2    tipo_2
nombre_campo_n    tipo_n
Key(campo_x,...)
)

Ahora crearemos por ejemplo la tabla PRUEBA con sus atributos, Nombre, Tipo, fecha, resultado

Create Table Prueba
(
id_prueba     INT(4)   NOT NULL  IDENTITY,
Nombre       INT(4)   ,
Tipo             INT(4)   ,
fecha            DATE,
resultado      INT(4),
, KEY(id_pedido)
)

Bueno creamos la tabla con sus campos específicos y estos con sus respectivos atributos y ademas creamos un campo clave identity. Este campo identity se incrementa según se creen nuevos registro, se incrementa siempre que hayan nuevos registros. Los números entre paréntesis indica el numero de caracteres que puede abarcar este numero o cadena según sea el caso.

Por otro lado como ya debemos de saber (es lo mínimo que debemos de saber) un campo clave no puede ser nulo, así que le agregamos las palabras reservadas NOT NULL

La fecha se puede almacenar en un Date o en un DateTime según sea el caso.

Y el key es finalmente creado mediante esa sentencia, existen otras maneras también pero todas aportan algo y quitan algo, es depende del criterio para usarlas y de las necesidades que tengamos en nuestra aplicación.

Otro ejemplo es la tabla Articulos:

Create Table articulos
(
id_articulo INT(4) NOT NULL IDENTITY,
titulo VARCHAR(50),
autor VARCHAR(25),
editorial VARCHAR(25),
precio REAL,
KEY(id_articulo)
)

Bueno y para acabar algunos datos y sus características.

Tipo	Bytes	Descripción
INT o INTEGER	4	Números enteros. Existen otros tipos de mayor o menor longitud específicos de cada base de datos.
DOUBLE o REAL	8	Números reales (grandes y con decimales). Permiten almacenar todo tipo de número no entero.
CHAR	1/caracter	Alfanuméricos de longitud fija predefinida
VARCHAR	1/caracter+1	Alfanuméricos de longitud variable
DATE	3	Fechas, existen multiples formatos específicos de cada base de datos
BLOB	1/caracter+2	Grandes textos no indexables
BIT o BOOLEAN	1	Almacenan un bit de información (verdadero o falso)

Clasificación de Sentencias y algo mas.

La clasificación de Sentencias SQL se da de acuerdo al uso que se les va a dar, gracias a este criterio se logra identificar tres grupos importantes.

DML: Lenguaje de Manipulación de Datos.
DDL: Lenguaje de Definición de Datos.
DCL: Lenguaje de Control de Datos.

   

SENTENCIA	DESCRIPCIÓN
DML Manipulación de datos SELECT INSERT DELETE UPDATE	Recupera datos de la base de datos. Añade nuevas filas de datos a la base de datos. Suprime filas de datos de la base de datos. Modifica datos existentes en la base de datos.
DDL Definición de datos CREATE TABLE DROP TABLE ALTER TABLE CREATE VIEW DROP VIEW CREATE INDEX DROP INDEX CREATE SYNOYM DROP SYNONYM	Añade una nueva tabla a la base de datos. Suprime una tabla de la base de datos. Modifica la estructura de una tabla existente. Añade una nueva vista a la base de datos. Suprime una vista de la base de datos. Construye un índice para una columna. Suprime el índice para una columna. Define un alias para un nombre de tabla. Suprime un alias para un nombre de tabla.
DCL Control de acceso GRANT REVOKE Control de transacciones COMMIT ROLLBACK	Concede privilegios de acceso a usuarios. Suprime privilegios de acceso a usuarios Finaliza la transacción actual. Aborata la transacción actual.
PLSQL SQL Programático DECLARE OPEN FETCH CLOSE	Define un cursor para una consulta. Abre un cursor para recuperar resultados de consulta. Recupera una fila de resultados de consulta. Cierra un cursor.

ALGO MAS
Los componentes sintácticos en la mayoría de sentencias SQL tienen la misma estructura.
Todas empiezan con un verbo (Select, Insert, Update, Create; todas estas las estudiaremos mas adelante) posteriormente los datos que se quieren trabajar, luego la sentencia From que indica de donde son estos datos y por ultimo Where que es condicional y utilizado como filtro.

                                     

Tipos de Campo y de Datos SQL

Las bases de datos almacenan datos, mediante registros, estos datos pueden ser distintos, en su característica, soporte, etc. Así que como un aspecto previo, es necesario conocer la naturaleza de los distintos tipos de datos.
Los SGBD proveen distintos tipos de datos, con los cuales podemos trabajar, sin embargo es necesario especificar cual nos conviene mas, y para eso debemos de saber sus características en búsqueda, capacidad, uso de los recursos, etc

Cada SGBD(Sistema de Gestión de Base de Datos) introduce tipos de valores de campos que no precisamente se encuentran presentes en otras. Sin embargo, existe un conjunto de estos que están representados en la totalidad de estos SGBD. Estos tipos comunes son los siguientes:

Alfanuméricos	Contienen cifras y letras. Presentan una longitud limitada (255 caracteres)
Numéricos	Existen de varios tipos, principalmente, enteros (sin decimales) y reales (con decimales).
Booleanos	Poseen dos formas: Verdadero y falso (Sí o No)
Fechas	Almacenan fechas facilitando posteriormente su explotación. Almacenar fechas de esta forma posibilita ordenar los registros por fechas o calcular los días entre una fecha y otra...
Memos	Son campos alfanuméricos de longitud ilimitada. Presentan el inconveniente de no poder ser indexados (veremos más adelante lo que esto quiere decir).
Autoincrementables	Son campos numéricos enteros que incrementan en una unidad su valor para cada registro incorporado. Su utilidad resulta más que evidente: Servir de identificador ya que resultan exclusivos de un registro.

Estos Tipos de Datos están presentes en cada SGBD, pero no son los únicos.

Tipo de Datos	Longitud	Descripción
BINARY	1 byte	Para consultas sobre tabla adjunta de productos de bases de datos que definen un tipo de datos Binario.
BIT	1 byte	Valores Si/No ó True/False
BYTE	1 byte	Un valor entero entre 0 y 255.
COUNTER	4 bytes	Un número incrementado automáticamente (de tipo Long)
CURRENCY	8 bytes	Un entero escalable entre 922.337.203.685.477,5808 y 922.337.203.685.477,5807.
DATETIME	8 bytes	Un valor de fecha u hora entre los años 100 y 9999.
SINGLE	4 bytes	Un valor en punto flotante de precisión simple con un rango de - 3.402823*1038 a -1.401298*10-45 para valores negativos, 1.401298*10- 45 a 3.402823*1038 para valores positivos, y 0.
DOUBLE	8 bytes	Un valor en punto flotante de doble precisión con un rango de - 1.79769313486232*10308 a -4.94065645841247*10-324 para valores negativos, 4.94065645841247*10-324 a 1.79769313486232*10308 para valores positivos, y 0.
SHORT	2 bytes	Un entero corto entre -32,768 y 32,767.
LONG	4 bytes	Un entero largo entre -2,147,483,648 y 2,147,483,647.
LONGTEXT	1 byte por carácter	De cero a un máximo de 1.2 gigabytes.
LONGBINARY	Según se necesite	De cero 1 gigabyte. Utilizado para objetos OLE.
TEXT	1 byte por carácter	De cero a 255 caracteres.

Y aquí algunos mas.

Tipo de Dato	Sinónimos
BINARY	VARBINARY
BIT	BOOLEAN LOGICAL LOGICAL1 YESNO
BYTE	INTEGER1
COUNTER	AUTOINCREMENT
CURRENCY	MONEY
DATETIME	DATE TIME TIMESTAMP
SINGLE	FLOAT4 IEEESINGLE REAL
DOUBLE	FLOAT FLOAT8 IEEEDOUBLE NUMBER NUMERIC
SHORT	INTEGER2 SMALLINT
LONG	INT INTEGER INTEGER4
LONGBINARY	GENERAL OLEOBJECT
LONGTEXT	LONGCHAR MEMO NOTE
TEXT	ALPHANUMERIC CHAR - CHARACTER STRING - VARCHAR
VARIANT (No Admitido)	VALUE

Estas tablas se irán actualizando según vayan creándose nuevos tipos de datos importantes (en la actualidad son muchísimos), como ejemplo les comento el tipo DATE y el tipo TIME que no existía en SQL Server pero luego fue implementado.

Porque SQL y Para que SQL y Bases de Datos

En el diario desarrollo de la tecnologia informatica y computacional se nota la importancia de contar con bases de datos actualizadas firmes consistentes y asimismo contar con profesionales Aptos para el mantenimiento de la misma. Son muchos los casos de Script que rigen el mundo de las bases de datos y su interrelacion con los sistemas de alrededor.

Existen un sin numero de Sistemas de Gestión de bases de datos y cada una de ellas posee una forma diferente de manejar su data, sin embargo con el paso de los años estos se fueron unificando y universalizando para dar paso a mejores técnicas y mejores formas de manejo. Así nació SQL.

Structured Query Language no es mas 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...).

A pesar de estar estandarizadas cada base de datos posee diferentes sentencias o palabras reservadas especificas para su uso, incluso se crean lenguajes propios para cada SGBD basados en SQL.

Este blog no peca de ambicioso pero si de soñador por una parte enseña a utilizar lo básico en SQL y ademas también pretende enseñar todo lo necesario para poder administrar y crear también una base de datos exitosa mente. Buscamos con ello ofrecer al webmaster un manual de referencia práctico y aplicado. Las bases de datos son mas que SQL, y lo vamos a estudiar.

Estrenamos Blog

Buen día a todos nuestros seguidores, el día de hoy estrenamos el  Blog "Base de Datos y SQL Jolunf " donde trataremos de la mejor manera de aprender más sobre este tema muy interesante, una cordial bienvenida a cada uno de ustedes y espero que los cursos que se impartan sean de su mayor agrado, trataremos que sea de la mejor manera y con la mejor forma de explicar las cosas. No olviden suscribirse y enlazarnos recomendandonos por distintos medios. Saludos.

SQL y Base de Datos