SQL. Funciones. Manipulacion de datos. Transacciones.

Funciones: Similares a los operadores, con la diferencia que pueden operar con uno o mas argumentos. Se usan dentro de SELECT, WHERE, y ORDER BY. Como resultado puede dar una modificación de los valores o indicar algo sobre la información que actua.

Funciones arimeticas. Devuelven un valor del mismo que el de entrada, las demás devuelven valores de tipo float. Trabajan con datos tipo NUMERIC. 3 tipos:

Funciones de valores simple. Números.

- ABS(n). Valor absoluto de n.              

SELECT ABS(-1.0), ABS(0.0), ABS(1.0)                                                                                
SELECT APELLIDO,ABS(SALARIO-10000000) FROM EMPLE;                     

- CEILING(n). Numero entero superior o igual a n.

SELECT CEILING(20.7),CEILING(-20.2),CEILING(16) ;                      

- FLOOR(n). Numero entero inferior o igual a n.

SELECT FLOOR(20.7),FLOOR(-20.2),FLOOR(16);

- POWER(N,M). N elevado a M (N^M)                

SELECT POWER(3,4),POWER(3,-4),POWER(-3,4),POWER(4.5,2.4),POWER(4.5,2);

- ROUND(N,M,func). Redondea N a M decimales, func (funccion) tiene por defecto valor 0 si se déjà asi se redondea, si es distinto a 0 se trunca.

SELECT ROUND(1.5634,1),ROUND(1.5634,0),ROUND(1.2234,0), ROUND(1.2234,2),ROUND(1.2676,3);

- SING(N). Muestra el signo de N; 1 positivo, -1 negativo.

SELECT SIGN(-10), SIGN(10);                                                  

- SQRT(N). Raiz cuadrada de N.

SELECT SQRT(25),SQRT(25.6);                                                                                                   

Funciones de agregado. realizan un cálculo sobre un conjunto de valores y devuelven un solo valor. Menos COUNT todas ignoran valores NULL. Se usan con GROUP BY y SELECT.

- AVG ( [ ALL | DISTINCT ] expresion). Calcula la media de los valores.

SELECT AVG(SALARIO)FROM emple WHERE dept_no=10;                       

- COUNT( { [ [ ALL | DISTINCT ] expresion ] | * } ): Cuenta los elementos del grupo.

SELECT COUNT(oficio)FROM emple WHERE oficio='analista';                     

- MAX( [ ALL | DISTINCT ] expresion ). Calcula el máximo valor de la expresión.

SELECT MAX(SALARIO) FROM emple;                                                 

- MIN([ ALL | DISTINCT ] expresion): calcula el mínimo de expresión.

SELECT MIN(SALARIO) FROM emple;                                                  

 - SUM([ ALL | DISTINCT ] expresión). Suma de valores numéricos.

SELECT SUM(SALARIO) FROM emple;                                                     

* ALL Aplica la función de agregado a todos los valores. ALL es el valor predeterminado                          * DISTINCT Especifica que COUNT devuelva el número de valores únicos no NULL.

Funciones de cadenas de caracteres: Trabajan sobre datos tipo CHAR y VARCHAR.

- CHAR(N). Pasa un código ASCII int en un carácter. Podemos saber los simbolos que son números enteros.

SELECT CHAR(75),CHAR(65);

- LOWER(cadena). devuelve la cadena con todos las letras en minúsculas.

SELECT DISTINCT LOWER(oficio) FROM emple                                 

- UPPER(cadena). devuelve la cadena con las letras en mayúsculas.

SELECT UPPER(apellido) FROM emple WHERE dept_no=20;                               

- LTRIM(cadena). elimina los espacios iniciales de la cadena.

SELECT LTRIM('.........HOLA') + LTRIM(' ADIOS')                                

- RTRIM(cadena). Devuelve una expresión de caracteres tras quitar todos los espacios finales en blanco.

SELECT RTRIM('HOLA ')+ RTRIM('ADIOS ');

- ASCII(cadena). devuelve el valor ASCII de la primera letra de la cadena.

SELECT ASCII('A'),ASCII('AJA')                                                    

- LEN(cadena). Devuelve el numero de caracteres de la cadena, excluye los espacios finales.

- SUBSTRING (CharExp, Ini, Long ). Devuelve una cadena de caracteres de CharExp que se inicia en la posición indicada en Ini (de izquierda a derecha) de una longitud especificada en Long.    

SELECT SUBSTRING('ABCDEFG',3,2)                                                       

 - LEFT(CharExp ,N). Devuelve la parte izquierda de una cadena de caracteres con el número de caracteres especificado.                                                                                       

- RIGHT (CharExp,N).  Devuelve la parte derecha de una cadena de caracteres con el número de caracteres especificado.                                                                              

- REPLACE ( CharExp,CadOrg, RempCad ). Reemplaza todas las instancias de un valor de cadena especificado por otro valor de cadena.                                       

SELECT REPLACE('BLANCO Y NEGRO','O','A')                                         

- REPLICATE (expresión de char ,numero). Repite un valor de cadena un número  especificado de veces.                                                                                                                                

- CHARINDEX ( exp1 ,exp2 [ , inicio ] ). Devuelve la posición inicial de la expresión especificada en una cadena de caracteres. Donde exp1 es lo que se quiere buscar, exp2 donde se busca la exp1 (normalmente una columna), inicio es la posición donde empezar a buscar exp1 dentro de exp2, por defecto es 0. Si no se encuentra se devuelve 0.

    * Para concatenar cadenas se utiliza el operador +.                                 

Funciones de manejo de fechas.                                                                                                       

- GETDATE(). Devuelve la fecha y la hora del reloj del sistema.

SELECT getdate();                                                                                                                                     

- DATEDIFF ( unidad_fecha , fecha_inicio ,fecha_final ) Devuelve el número de unidades de fecha y hora entre dos fechas especificadas.                                                                                             

select DATEDIFF ( dd , '1-05-2010' ,getdate() );                                                             

- DATEADD (unidad de fecha , numero, fecha ) : Devuelve un valor datetime nuevo que se obtiene agregando un intervalo a la fecha especificada.                                                                                 

select DATEadd ( mm , 12 ,getdate() ); → la fecha dentro de 12 meses   

- DATENAME ( unidad_fecha ,fecha ): Devuelve una cadena de caracteres que representa launidad de fecha especificada de la fecha indicada.                                                                                    

select DATENAME ( dw, getdate() ); → dia de la seman actual                          

 - DATEPART ( unidad_fecha , fecha ): Devuelve un entero que representa la parte de la fecha especificada. select DATEPART ( dw, getdate() );                                                                                                                                                                                                                                 

- DAY ( fecha ): Devuelve un entero que representa la parte del día de la fecha especificada.               

select DAY ('1/03/2000' );                                               

 - MONTH ( date ): un entero que representa el numero del mes de la fecha especificada.

select MONTH ('1/03/2000' );                                                

- YEAR(fecha): un entero que representa el numero del año de la fecha especificada.                

select YEAR ('1/03/2000' );                                                                                                                               

*Las abreviaturas y su significado: yy(año), mm(mes), dd(dia), ww(semana), hh(hora), mi(minutos), ss(segundos).                                                                                                                  

Funciones de conversión. Para transformar un dato a otro.                                                                  

- CONVERT ( tipo_dato [ ( longitud ) ] , expresion [ , estilo ] ). Donde tipo_datos es el tipo de datos al que se va a convertir la expresión, longitud es opcional según el tipo de dato que vallamos a usar por defecto la longitud es de 30; expresión es cualquier expresión valida y estilo es la manera en la que se va a ver la información de tipo FLOAT, REAL o DATETIME, si el estilo es NULL el resultado también será NULL.                                                                                                     

*Estilos DATETIME: 1 (mm/dd/yy), 2 (yy.mm.dd), 3(dd/mm/yy)… 8(hh,mi,ss).                                    *Estilos FLOAT/REAL: 0(por defecto, max 6 digitos), 1 (8 digitos), 2(16 digitos).                                  

- TOP (expresion) [porcentaje]. Donde expresión especifica el numero de filas que se devolverán y porcentaje indica que la consulta devuelve sólo el primer porcentaje de filas de expresion del conjunto de resultados.

Manipulacion de datos. INSERT, UPDATE Y DELETE.

Insercion de datos.

INSERT. Añaden filas de datos a las tablas.

INSERT [INTO] nombretabla [(columna [, columna].....)]
VALUES (valor [, valor]....);

Se tienen que poner los valores en orden según las columnas, las columnas siguen el orden en las que las pongamos y si no se especifican se tienen que poner todos los valores para todas las columnas. Ademas los valores deben corresponder al tipo de valor de la colunma. Se tienen que poner todos los campos NOT NULL, si no salta error.

INSERT compradores VALUES ('13100987h','PinturasGomez','C/Prados/n','Burgos','09001','947887766');

INSERT INTO compradores (CIF_comprador, Telefono,Localidad) VALUES ('78012322K','987663200', 'Leon');

Modificacion de datos.

UPDATE nombretabla
[ TOP ( expression ) [ PERCENT ] ]
SET columna1=valor1, ... ,columnan=valorn
[ WHERE condición ];

TOP: especifica un numero o un porcentaje de las filas a modificar.

SET: indica las columnas cuyos valores se van a modificar.

WHERE: condición que selecciona las filas que se van a actualizar. Si se omite se actualizan todas las columnas.

UPDATE TEMA8.dbo.VENTAS
                 SET unidades_vendidas = unidades_vendidas+3

UPDATE  TEMA8.dbo.empleados
                SET Salario= Salario+100, Comision=Comision+2
                WHERE Dept_no=3;

UPDATE TEMA8.dbo.VENTAS
SET ARTICULO = N'Atún del Norte'
WHERE ARTICULO = N'Atún' ;

Borrado de filas. DELETE

DELETE [FROM] nombretabla
[WHERE condición];

Donde WHERE es la condición de las filas que queremos borrar. Tambien puede ser una subconsulta.

DELETE FROM TEMA8.dbo.ventas
                WHERE Unidades_vendidas > 1000;

Insertar y modificar filas con SELECT.

Insercion. INSERT. Las filas que devuelve la consula son las que se añaden. Se usan para pasar datos entre tablas.

INSERT [INTO] nombretabla1 [(columna [,columna] ....)
                               SELECT {columnaa [,columnab]... | *}
                               FROM nombretabla2
                               [CLAUSULAS DE SELECT];

INSERT TEMA8.dbo.EMPLE2
SELECT  * FROM TEMA8.dbo.EMPLEADOS
WHERE comision>0
ORDER BY emple2.emp_no;

Modificacion. UPDATE. Se usa para pasar datos entre tablas.

UPDATE nombretabla1
                               SET (columna1,columna2,..)=
                               (SELECT columnaa, columnab,...
                                               FROM nombretabla2
                                               [CLAUSULAS SELECT])
                                           WHERE condición;

 Ejemplo:

UPDATE VENTAS
SET FECHA_VENTA=
                (SELECT MAX(FECHA_PEDIDO)FROM PEDIDOS)

TRANSACCIONES.

Son un conjunto de operaciones que se consideran una sola y se ejecuta como tal. Ha de cumplir cuatro normas (ACID).

Atomicidad: Una transacción debe ser una unidad atómica de trabajo
Coherencia: Cuando finaliza, una transacción debe dejar los datos en estado coherente, es decir todas las estructuras internas deben estar correctas.
Aislamiento: Una transacción ve los datos en un determinado estado, pero nunca en un estado intermedio dependiendo de otra transacción.
Durabilidad: Una vez concluida una transacción sus efectos permanecen en el sistema

Una transacción comienza con la primera sentencia ejecutable del usuario y finaliza cuando el usuario valida o deshace las operaciones realizadas. 

Los cambios de la informacion se anotan en una zona de memoria y el usuario tiene dos posibilidades, o valida los cambios (Se guardan los datos en las tablas) o no validarlos (Los cambios se deshacen).

Es un buen mecanismo para garantizar la consistencia de los datos ya que si algo sale mal en el proceso los datos no se guardan. La transaccion mas simple es cualquier sentencia SQL.

Hay dos modos de manejar las transacciones.

Transacciones Explicitas: Se autoconfirman, la informacion pasa al fichero de transacciones y a continuacion realiza los cambios. AUTO COMMIT. Es la forma por defecto de operar.

                delete from pedidos where cod_arti like '%5%'

Transacciones Implictas: Es necesario confirmar las transacciones.

delete from pedidos where cod_arti like '%5%'
                COMMIT                

Antes de salir nos pedira confirmar o ponemos poner COMMIT al final de cada transaccion.

Desactivar AUTO COMMIT. Herramientas > Opciones > Ejecución de la consulta > SQL Server > Ansi > SET IMPLICIT_TRANSACTIONS.

Sentencias de transacción.

BEGIN TRANSACTION; /*Indica el comienzo de una transaccion*/

                select *from pedidos where cod_arti=13;

                delete from pedidos where cod_arti=13;

                select *from pedidos where cod_arti=13;

ROLLBACK TRANSACTION; /*Restaura la base de datos a como estaba antes de la transaccion, por si da errores.*/

               

COMMIT TRANSACTION; /*Al finalizar una transaccion de manera correcta la confirma.*/

GO. Comando que nos sirve para finalizar un lote de sentencias. Indica una sentencia que se ha de ejecutar antes de ir a la siguiente.

select *from empleados;

go

sp_helpconstraint empleados;