Views

Son consultas almacenadas(SELECT) que cuando son utilizada producen un "result set".

También se les considera tablas virtuales.

Sintaxis básica:

CREATE VIEW nombre_vista [(lista_de_columnas)] AS select

Views

Algunas ventajas de los views son:

  • Ahorran reproceso
  • Ocupan menos espacio ya que no almacenan copia de los datos
  • Ocultan la complejidad de los datos
  • Manejo de privilegios separado de las tablas de datos

Views

Algunas desventajas de los views son:

  • No se pueden crear views temporales
  • No se pueden asociar triggers a los views
  • Para que un view funcione se necesita que las tablas a las que hace referencia sigan existiendo

Views

¿Cuando no es posible actualizar un view?

Se tiene que tener una relación uno a uno de las filas del view y las filas de la tabla subyacente .

Tampoco es posible si contiene algo de lo siguiente:

  • Aggregate functions(SUM, MIN, MAX, etc)
  • DISTINCT
  • GROUP BY
  • HAVING
  • UNION o UNION ALL
  • Subqueris

Views

Tampoco es posible si contiene algo de lo siguiente:

  • Algunos tipos de join
  • Referencias a views que no se puedan actualizar
  • Cuando se hace referencia a valores, es decir, sin tabla subyacente
  • Multiples referencias a cualquier columna de la tabla base (falla con INSERT, funciona con UPDATE y DELETE).

Views

CREATE VIEW v1 AS 
SELECT x, y, z FROM t ORDER BY 2;
CREATE VIEW v2 AS 
SELECT x, 1, z FROM t ORDER BY 2;


CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
SELECT * FROM v WHERE qty = 5;

User-Defined Variables

Las variables definidas por el usuario normalmente se utilizan para almacenar un dato en una sentencia para su utilización en otra.

Estas de declara de la forma @nombre_variable

Estas variables son especificas de la sesión, por lo tanto son destruidas cuando el clienta libera la conexión.

La forma mas comun de asignarles valor es mediante la sentencia SET:

  • SET @nombre_var = expr

Funciones de fecha

Funcion Descripcion
ADDDATE() Agrega intervalos de tiempo a una fecha
MONTH() Regresa el mes de la fecha especificada
MAKEDATE() Crea una fecha apartir de un año y el día del año
DATEDIFF() Substrae una fecha de otra
DAYOFYEAR() Regresa el día del año

http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html​​

Aggregate functions

Funcion Descripcion
AVG() Regresa el promedio del argumento
MAX() Regresa el valor maximo
MIN() Regresa el valor minimo
COUNT() Regresa la cantidad de filas encontradas
SUM() Regresa la suma

http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html

Funciones string

Funcion Descripcion
CHAR() Regresa el caracter del numero entero
CONCAT() Concatena las cadenas
FORMAT() Regresa un numero formateado con la cantidad de decimales especificadas
HEX() Regresa la representación hexadecimal del dato
RTRIM() Remueve los espacios a la derecha

http://dev.mysql.com/doc/refman/5.7/en/string-functions.html​

Ejemplos de uso

ADDDATE(date, INTERVAL expresion unit) ADDDATE(expresion, dias)

SELECT ADDDATE('2008-01-02', INTERVAL 31 DAY);//2008-02-02
SELECT ADDDATE('2008-01-02', 31);//2008-02-02

DATEDIFF(expresion1, expresion2)

SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');// 1
SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');// -31

Ejemplos de uso

SELECT student_name, AVG(test_score) FROM student 
GROUP BY student_name;

SELECT COUNT(*) FROM student;

SELECT student_name, MIN(test_score), MAX(test_score) FROM student
GROUP BY student_name;

Funciones creadas por el usuario

CREATE FUNCTION nombre_funcion([parametros,...])

RETURNS tipo [caracteristicas]

cuerpo_de_la_función

 

parametros => nombre_parametro tipo

Funciones

Características:

  • COMMENT 'string'
  • | LANGUAGE SQL
  • |[NOT] DETERMINISTIC
  • | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  • | SQL SECURITY { DEFINER | INVOKER }

Funciones

ALTER FUNCTION nombre_funcion [caracteristicas]

Características:

  • COMMENT 'string'
  • | LANGUAGE SQL
  • | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  • | SQL SECURITY { DEFINER | INVOKER }

Un alter de una función no puede cambiar sus parámetros o el cuerpo de la función.

Funciones

No podemos hacer referencia a la misma tabla dentro y fuera de una función ya que esto genera un error.

CREATE FUNCTION f3 ()
RETURNS INT
BEGIN
	INSERT INTO t VALUES (0);
	RETURN 5;
END; //
UPDATE t SET s1 = f3()//

Funciones

Un procedimiento o función se considera determinista si siempre produce el mismo resultado cuando se utilizan los mismos parámetros de entrada.

Si no se especifica DETERMINISTIC en la función, se toma por defecto NOT DETERMINISTIC.

Funciones

CREATE FUNCTION factorial (n DECIMAL(3,0))
	RETURNS DECIMAL(20,0)
	DETERMINISTIC
BEGIN
	DECLARE factorial DECIMAL(20,0) DEFAULT 1;
	DECLARE counter DECIMAL(3,0);
	SET counter = n;
	factorial_loop: REPEAT
		SET factorial = factorial * counter;
		SET counter = counter - 1;
	UNTIL counter = 1
	END REPEAT;
	RETURN factorial;
END //

Stored procedures

Un procedimiento almacenado es una subrutina que se almacena dentro de la base de datos.

Estos son muy similares a las funciones creadas por el usuario, la diferencia mas grande es que las funciones se pueden usar como cualquier otra expresión de SQL y los SP son llamados mediante la sentencia CALL o EXECUTE.

  • CALL procedure_name(...)
  • EXECUTE procedure_name(...)

Stored procedures

Los parametros de un sp pueden ser declarados como IN, OUT o INOUT, tampoco es obligatorio que estos tengan un "return".

 

Su principal desventaja es que son específicos para cada aplicación de base de datos, por lo cual normalmente es necesario reescribir estos al migrar a otro sistema de base de datos.

Stored procedures

CREATE PROCEDURE nombre_procedure
(IN parametro1 INTEGER)
BEGIN
    DECLARE variable1 CHAR(10);
    IF parametro1 = 17 THEN
        SET variable1 = 'pajaro';
    ELSE
        SET variable1 = 'bestia';
    END IF;
    INSERT INTO table1 VALUES (variable1);
END

Stored procedures

La clausula DELIMITER sirve para especificar que caracter va servir como delimitador, es decir, que va indicar cuando una linea se considera completa.

El delimitador por defecto es ; esto es un problema particular en los sp ya que si se quieren escribir varias sentencias, cada una debe terminar con un ;.

Por ejemplo: DELIMITER &

Stored procedures

Formas de utilizar parámetros en sp:

  • CREATE PROCEDURE pro()
  • CREATE PROCEDURE pro([IN] nombre tipo)
  • CREATE PROCEDURE pro(OUT nombre tipo)
  • CREATE PROCEDURE pro(INOUT nombre tipo)

Las variables IN solo permiten recibir valores, las variables OUT solo pueden devolver valores y las variables INOUT ambas.

Stored procedures

En un SP las variables locales se declaran mediante la sentencia DECLARE, estas se definen entre las sentencias BEGIN y END.

De la forma:

  • DECLARE nombre tipo;

Stored procedures

delimiter //
CREATE PROCEDURE simpleproc (OUT param1 INT)
BEGIN
	SELECT COUNT(*) INTO param1 FROM t;
END//
delimiter ;

CALL simpleproc(@a);
SELECT @a;

Condiciones

La condicional IF se utiliza en MySQL de la siguiente manera:

  • IF variable1 comparador valor_comparar THEN
              codigo_if...
    END IF;
  • IF variable1 comparador valor_comparar THEN
              codigo_if...
    ELSE
              codigo_else
    END IF;

Condiciones

La condicional CASE funciona muy similar a un switch:

  • CASE variable
         WHEN valor THEN sentencia;
         WHEN valor2 THEN ​sentencia2;
         ELSE sentencia_else;
    END CASE;

Loops

El ciclo while se utiliza en MySQL de la siguiente manera:

  • WHILE variable comparador valor DO
              codigo_while;
    END WHILE;

Loops

El ciclo repeat se utiliza en MySQL de la siguiente manera:

  • [label_inicio:] REPEAT
              codigo_repeat;
    UNTIL variable comparador valor
    END REPEAT [label_fin];

Loops

El ciclo loop se utiliza en MySQL de la siguiente manera:

  • label_loop: LOOP
           codigo_loop;
           IF x > 5 THEN
                    LEAVE label_loop;
           END IF;
    END LOOP;

Triggers

Un trigger o disparador es una forma de regla que se activa con las actualizaciones de la tabla, es decir, cuando en una fila en la tabla se inserta (INSERT), se actualiza (UPDATE), o se borra (DELETE), conlleva a la ejecución de algunas operaciones adicionales sobre otras tablas, el envío de mensajes, etcétera.

Para establecer el momento de ejecución del disparador se especifica BEFORE o AFTER, que la sentencia que lo activa.

Triggers

No se permite que en la misma tabla existan dos disparadores con igual momento de ejecución y sobre la misma sentencia, por ejemplo, no puede haber dos disparadores AFTER UPDATE, pero sí es permitido especificar AFTER UPDATE y AFTER DELETE o BEFORE UPDATE y AFTER UPDATE.

Triggers

Para referenciar las columnas de antes y después de que el evento se haya disparado, se usan las palabras clave OLD y NEW. Con la sentencia INSERT solo se permite NEW, con UPDATE se permiten ambas y con DELETE solo OLD.

Las palabras clave OLD y NEW son de exclusivas de MySQL, otros gestores de BD tienen su propia manera de referencias datos antes o después de ser actualizados.

Triggers

Sintaxis:

CREATE TRIGGER nombre
          {BEFORE|AFTER}
          {INSERT|UPDATE|DELETE}
          ON nombretabla FOR EACH ROW
          BEGIN
              sentenciasSQL
          END; 

Triggers

Evento Tiempo Tabla virtual
INSERT - NEW
UPDATE BEFORE OLD, NEW
UPDATE AFTER OLD, NEW
DELETE - OLD

Joins

La clausula JOIN se utiliza para combinar filas de dos o mas tablas, utilizando un campo en común entre ellos.

  • CROSS JOIN
    • Producto cartesiano
  • INNER JOIN
    • Default
  • OUTER JOIN
    • FULL
    • LEFT
    • RIGHT

Cross Joins

Una combinación cruzada que no tenga una cláusula WHERE genera el producto cartesiano de las tablas involucradas en la combinación.

El tamaño del conjunto de resultados de un producto cartesiano es igual al número de filas de la primera tabla multiplicado por el número de filas de la segunda tabla.

Cross Joins

SELECT p.BusinessEntityID, t.Name AS Territory
FROM Sales.SalesPerson p
CROSS JOIN Sales.SalesTerritory t
ORDER BY p.BusinessEntityID

SELECT p.BusinessEntityID, t.Name AS Territory
FROM Sales.SalesPerson p, Sales.SalesTerritory t
ORDER BY p.BusinessEntityID

O

El conjunto de resultados contiene 170 filas (SalesPerson tiene 17 filas y SalesTerritory tiene 10; 17 multiplicado por 10 es igual a 170).

Cross Joins

SELECT p.BusinessEntityID, t.Name AS Territory 
FROM Sales.SalesPerson p 
CROSS JOIN Sales.SalesTerritory t
WHERE p.TerritoryID = t.TerritoryID
ORDER BY p.BusinessEntityID; 
SELECT p.BusinessEntityID, t.Name AS Territory 
FROM Sales.SalesPerson p 
INNER JOIN Sales.SalesTerritory t 
ON p.TerritoryID = t.TerritoryID 
ORDER BY p.BusinessEntityID;

O

Si se utiliza el Where, el cross join se comporta como inner join

Inner join

En un inner join las columnas que se van a combinar se comparan mediante un operador de comparación.

En el estándar ISO, los inner join se pueden especificar en la cláusula FROM o en la cláusula WHERE. Éste es el único tipo de combinación que ISO admite en la cláusula WHERE.

Los inner join especificados en la cláusula WHERE se conocen como combinaciones internas al estilo antiguo.

Inner join

Old school ↓

SELECT * 
FROM HumanResources.Employee AS e 
INNER JOIN Person.Person AS p 
ON e.BusinessEntityID = p.BusinessEntityID 
ORDER BY p.LastName
SELECT * 
FROM HumanResources.Employee AS e, Person.Person AS p 
WHERE e.BusinessEntityID = p.BusinessEntityID 
ORDER BY p.LastName

Outer join

En los outer join devuelven todas las filas de una de las tablas o vistas mencionadas en la clausula FROM, como mínimo, siempre que estos datos cumplan con las condiciones del WHERE o HAVING.

  • LEFT OUTER JOIN: Obtiene todas las filas de la tabla izquierda
  • RIGHT OUTER JOIN: Idem solo que el lado derecho
  • FULL OUTER JOIN: Todas las filas de ambas tablas

Outer join

SELECT p.Name, pr.ProductReviewID 
FROM Production.Product p 
LEFT OUTER JOIN Production.ProductReview pr
ON p.ProductID = pr.ProductID
SELECT p.Name, pr.ProductReviewID 
FROM Production.Product p, Production.ProductReview pr 
WHERE p.ProductID *= pr.ProductID

Combinacion de Product y ProductReview mendiate ProductID, se incluyen todos los productos independientemente si se ha escrito alguna reseña de ellos.

Old school ↓

Outer join

SELECT st.Name AS Territory, sp.BusinessEntityID
FROM Sales.SalesTerritory st
RIGHT OUTER JOIN Sales.SalesPerson sp 
ON st.TerritoryID = sp.TerritoryID ;
SELECT st.Name AS Territory, sp.BusinessEntityID
FROM Sales.SalesTerritory st, Sales.SalesPerson sp 
WHERE st.TerritoryID =* sp.TerritoryID ;

Combinacion de SalesTerritory y SalesPersion mendiate TerritoryID, se incluyen todos los vendedores independientemente si están asignados a un territorio de ventas.

Old school ↓

Outer join

SELECT p.Name, sod.SalesOrderID 
FROM Production.Product p 
FULL OUTER JOIN Sales.SalesOrderDetail sod
ON p.ProductID = sod.ProductID
WHERE p.ProductID IS NULL
OR sod.ProductID IS NULL
ORDER BY p.Name ;

Combinación de Product y SalesOrderDetail mendiate ProductID, se incluyen todos los resultados de ambas tablas, independientemente que no haya coincidencia entre ellas.

Autocombinación

Una tabla se puede combinar consigo misma, a esto le llamamos autocombinación.

Para incluir una tabla dos veces en la misma consulta, debe proporcionar un alias de tabla para al menos una de las instancias del nombre de tabla.

Buscar los productos suministrados por mas de un proveedor.Puesto que esta consulta conlleva una combinación de la tabla ProductVendor consigo misma, la tabla ProductVendor aparece en dos roles. Para distinguir estos roles, debe dar a la tabla ProductVendor dos alias distintos (pv1 y pv2) en la cláusula FROM.

Autocombinación

SELECT DISTINCT pv1.ProductID, pv1.BusinessEntityID
FROM Purchasing.ProductVendor pv1     
INNER JOIN Purchasing.ProductVendor pv2     
ON pv1.ProductID = pv2.ProductID         
AND pv1. BusinessEntityID <> pv2.BusinessEntityID
ORDER BY pv1.ProductID
Made with Slides.com