
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 |
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 |
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 |
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
MySQL Parte2
By Alan
MySQL Parte2
- 198