Skip to content

Latest commit

 

History

History
699 lines (619 loc) · 19.2 KB

dia120.md

File metadata and controls

699 lines (619 loc) · 19.2 KB
Raw

shieldsIO shieldsIO shieldsIO

WideImg

Master en Programación de Aplicaciones con JavaScript y Node.js

JS, Node.js, Frontend, Express, Patrones, IoT, HTML5_APIs, Asincronía, Websockets, ECMA6, ECMA7

Clase 120

logo img

https://shlomi-noach.github.io/awesome-mysql/

SQL

SQL (por sus siglas en inglés Structured Query Language) es un lenguaje declarativo de acceso a bases de datos relacionales que permite especificar diversos tipos de operaciones en ellas. Una de sus características es el manejo del álgebra y el cálculo relacional que permiten efectuar consultas con el fin de recuperar, de forma sencilla, información de bases de datos, así como hacer cambios en ellas.

  • Lenguaje de definición de datos (DDL).
    • creación (CREATE)
    • modificación (ALTER)
    • borrado (DROP)
    • componentes principales:
      • base de datos (DATABASE)
      • tablas (TABLE)
      • vistas (VIEW)
      • índices (INDEX)
      • procedimientos almacenados (PROCEDURE)
      • disparadores (TRIGGER).
  • Lenguaje de manipulación de datos (DML).
    • consultar (SELECT)
    • insertar (INSERT)
    • modificar (UPDATE)
    • borrar (DELETE)
  • Lenguaje de control de datos (DCL).
    • Confirmar la operacion (COMMIT)
    • Retroceder la operacion (ROLLBACK)
    • Dar permisos (GRANT)
    • Quitar permisos (REVOKE)

SQL: Tipos de datos

  • Fechas
    • DATE format YYYY-MM-DD
    • DATETIME format: YYYY-MM-DD HH:MI:SS
    • TIMESTAMP format: YYYY-MM-DD HH:MI:SS
    • YEAR format YYYY or YY
  • Numéricos
    • TINYINT permite números desde -128 hasta 127
      • UNSIGNED los valores posibles empiezan en 0 hasta 255
    • SMALLINT permite números desde -32768 hasta 32767
      • UNSIGNED los valores posibles empiezan en 0 hasta 65535
    • MEDIUMINT permite números desde -8388608 hasta 8388607
      • UNSIGNED los valores posibles empiezan en 0 hasta 16777215
    • INT permite números desde -2147483648 hasta 2147483647
      • UNSIGNED los valores posibles empiezan en 0 hasta 4294967295
    • BIGINT permite números desde -9223372036854775808 hasta 9223372036854775807
      • UNSIGNED los valores posibles empiezan en 0 hasta 1844674407370955161
    • FLOAT Pequeños flotantes
      • FLOAT(6,2) tendrá 4 dígitos enteros y 2 decimales, por ejemplo, 5467.67
    • DOUBLE permite almacenar grandes números decimales
      • DOUBLE(5,1) tendrá 4 dígitos enteros y 1 dígito decimal, por ejemplo, 5467.1
    • DECIMAL permite almacenar grandes números decimales de punto fijo, por tanto, los cálculos con este tipo DECIMAL son exactos
      • El número máximo de dígitos es de 65
      • DECIMAL(20,6) quiere decir que tendrá 14 dígitos enteros y 4 dígitos decimales
  • Texto
    • CHAR sirve para almacenar una cadena de datos de longitud fija
      • Longitud máxima 255
      • CHAR(50), será un campo de longitud fija de 50 posiciones
    • VARCHAR Sirve para almacenar una cadena de datos (caracteres, números y caracteres especiales) de longitud variable
      • La longitud máxima es de 255 caracteres
      • Hace un buen uso del espacio en disco, porque no reserva el espacio de la longitud máxima definida, si no que solo ocupa espacio el tamaño real de los datos almacenados en ese campo.
      • Es el tipo de dato más utilizado para campos pequeños
    • TINYTEXT Solo texto
      • Máximo 255 carácteres
    • TEXT para almacenar una cadena de caracteres de longitud máxima de 65,535 caracteres
    • BLOB para almacenar datos de tipo BLOB (Binary Large Object)
      • longitud máxima de 65,535 bytes de datos
    • MEDIUMTEXT Sirve para almacenar una cadena con una longitud máxima de 16.777.215 caracteres
    • MEDIUMBLOB Sirve para almacenar datos tipo BLOB con longitud máxima 16.777.215 bytes
    • LONGTEXT Sirve para almacenar una cadena de longitud máxima de 4.294.967.295 caracteres
    • LONGBLOB Sirve para almacenar un BLOB de longitud máxima de de 4.294.967.295 byte
    • ENUM Sirve para introducir una lista de posibles valores
      • La longitud máxima es de 65.535 posibles valores
      • Si se intenta introducir un valor en este campo, que no esté incluido en la lista, no se insertará nada y tendrá un valor vacío ('')
      • Por ejemplo si definimos una columna como ENUM('uno', 'dos'), entonces en esta columna solo puede almacenar los valores 'uno' o 'dos'.
    • SET es similar a ENUM pero la longitud máxima de valores posibles es de 64, y los valores posibles se pueden combinar.
    • Por ejemplo, si definimos una columna como SET ('uno', 'dos) entonces esa columna podrá tomar los siguientes valores 'uno' o 'dos' o 'uno,dos', 'dos,uno'.

SQL Básico

SQL: Creación y gestión

Crear una base de datos

  CREATE DATATABASE mibasedeatos;

Crear una tabla

  CREATE TABLE Persons
  (
  PersonID int,
  LastName varchar(255),
  FirstName varchar(255),
  Address varchar(255),
  City varchar(255)
  );

SQL: Consultas Básicas

SELECT - Realizar consultas

  • Traer todos los datos de una tabla
  SELECT * FROM nombretabla;
  • Traer solo alguna columnas de una tabla
  SELECT columna1, columna2 FROM nombretabla;

SQL: Filtrado de datos

WHERE - Condicciones de filtrado

  SELECT * FROM personas 
  WHERE nombre = 'ANTONIO'

AND OR - Condicciones lógicas

  • Sencillas
  SELECT * FROM personas 
  WHERE nombre = 'ANTONIO' 
  AND apellido1 = 'GARCIA'
  • Combinadas
  SELECT * FROM personas 
  WHERE nombre = 'ANTONIO' 
  AND (apellido1 = 'GARCIA' OR apellido1 = 'LOPEZ')

ORDER BY - Ordenación

  • Ascendente
  SELECT nombre, apellido1 
  FROM personas 
  ORDER BY apellido1 ASC;
  • Descendiente
SELECT nombre, apellido1 
FROM personas 
ORDER BY apellido1 DESC;

SQL: Añadiendo datos en la tabla

  • Manteniendo el orden de las columnas
  INSERT INTO nombre_tabla 
  VALUES (valor1, valor2, valor3);
  • Alterando el orden de las columnas
  INSERT INTO nombre_tabla (columna1, columna2, columna3,.) 
  VALUES (valor1, valor2, valor3);

SQL: Actualizamos datos en la tabla

  • Todas las filas
  UPDATE nombre_tabla 
  SET columna1 = valor1, columna2 = valor2;
  • Una fila especifica
  UPDATE personas 
  SET apellido2 = 'RODRIGUEZ' 
  WHERE nombre = 'ANTONIO' 
  AND apellido1 = 'GARCIA' 
  AND apellido2 = 'BENITO';

SQL: Vaciamos datos en la tabla

  • Todas los datos de una tabla
  DELETE * FROM nombre_tabla;
  • Una fila especifica
  DELETE FROM personas 
  WHERE nombre = 'LUIS' 
  AND apellido1 = 'LOPEZ' 
  AND apellido2 = 'PEREZ';
  • Con truncate
  TRUNCATE TABLE nombretabla

SQL: Eliminamos datos

  • Índice
  ALTER TABLE nombretabla 
  DROP INDEX nombreindice;
  • Tabla
  DROP TABLE nombretabla;
  • Base de datos
  DROP DATABASE nombrebasededatos;

SQL Avanzado

SQL Avanzado: Consultas Avanzadas

LIMIT - Limitar la cantidad de resutlados de una consulta

  • Traer 2 filas de la tabla
  SELECT * FROM personas LIMIT 2;

LIKE Y NOT LIKE - Limitar la cantidad de resutlados a un patrón (Wildcard)

  • Aquellos apellidos que contengan "Z"
  SELECT * FROM personas 
  WHERE nombre LIKE '%Z%'; // que contenga una zeta
  • Aquellos apellidos que no contengan "le"
  SELECT * FROM personas 
  WHERE nombre NOT LIKE '%le%'; // que contenga una zeta
  • Aquellos apellidos que terminen en "l"
  SELECT * FROM personas 
  WHERE nombre LIKE '%l'; // que contenga una zeta
  • Aquellas personas cuyo apellido1 empiece por P o G
  SELECT * FROM personas 
  WHERE apellido1 LIKE '[PG]%';
  • Aquellas personas cuyo apellido1 tenga PE(x)EZ como PEREZ
  SELECT * FROM personas 
  WHERE apellido1 LIKE 'PE_EZ';

IN - Seleccionar múltiples valores dentro de WHERE

  SELECT * FROM personas 
  WHERE apellido1 
  IN ('PEREZ','RUIZ');

BETWEEN y NOT BETWEEN - Seleccionar valores entre un rango de datos dentro de WHERE

  • Seleccionar personas cuyo apellido1 esté entre 'FERNANDEZ y 'HUERTAS'
  SELECT * 
  FROM personas 
  WHERE apellido1 
  BETWEEN 'FERNANDEZ' AND 'HUERTAS';
  • Seleccionar personas cuyo apellido1 NO esté entre 'FERNANDEZ y 'HUERTAS'
  SELECT * 
  FROM personas 
  WHERE apellido1 
  NOT BETWEEN 'FERNANDEZ' AND 'HUERTAS';

AS - Acortador de nombres

  SELECT p.apellido1, p.apellido2 
  FROM personas 
  AS p 
  WHERE p.nombre = 'ANTONIO';

SQL Avanzado: JOINS

SQL_JOINS

Tipos:

  • Interno
  • Externo
  • Cruzado

Usaremos Northwind database

INNER JOIN

  • Juntamos ambas tablas y sustituimos el codigo del departamento por su nombre
  • Join por defecto
  • Query
  SELECT Customers.CustomerName, Orders.OrderID
  FROM Customers
  INNER JOIN Orders
  ON Customers.CustomerID=Orders.CustomerID
  ORDER BY Customers.CustomerName;
  ------------------------------------------------
  | CustomerName                       | OrderID |
   ----------------------------------------------
  | Ana Trujillo Emparedados y helados | 10308   |
  | Antonio Moreno Taquería            | 10365   |
  ------------------------------------------------

LEFT JOIN

  • Devuelve todas las filas de la tabla de la izquierda, incluso si no coinciden con la tabla de la derecha
  • Query
  SELECT Customers.CustomerName, Orders.OrderID
  FROM Customers
  LEFT JOIN Orders
  ON Customers.CustomerID=Orders.CustomerID
  ORDER BY Customers.CustomerName;
  ------------------------------------------------
  | CustomerName                       | OrderID |
   ----------------------------------------------
  | Alfreds Futterkiste                | null    |
  | Ana Trujillo Emparedados y helados | 10308   |
  ------------------------------------------------

RIGHT JOIN

  • Devuelve todas las filas de la tabla de la derecha, incluso si no coinciden con la tabla de la izquierda
  • Query
  SELECT Orders.OrderID, Employees.FirstName
  FROM Orders
  RIGHT JOIN Employees
  ON Orders.EmployeeID=Employees.EmployeeID
  ORDER BY Orders.OrderID;
  ------------------------
  | OrderID  | FirstName |
   -----------------------
  |          | Adam      |
  | 10248    | Steve     |
  ------------------------

FULL OUTER JOIN

  • Devuelve todas las filas de la tabla de la derecha y de la izquierda incluso si no coinciden
  • Query
  SELECT Customers.CustomerName, Orders.OrderID
  FROM Customers
  FULL OUTER JOIN Orders
  ON Customers.CustomerID=Orders.CustomerID
  ORDER BY Customers.CustomerName;
  ------------------------------------------------
  | CustomerName                       | OrderID |
   ----------------------------------------------
  | Alfreds Futterkiste                |         |
  | Ana Trujillo Emparedados y helados | 10308   |
  |                                    | 10382   |
  ------------------------------------------------

SQL Avanzado: Juntar tablas sin JOINS

  • Con Joins
	SELECT R.departamento, SUM(P.precio) FROM registradoras R 
	JOIN ventas V ON R.id = V.registradora_id 
	JOIN productos P ON P.id = V.producto_id
	GROUP BY R.departamento;
  • Sin Joins
	/* SIN JOIN  */
	SELECT R.departamento, SUM(P.precio) 
	FROM registradoras R, ventas V, productos P
	WHERE R.id = V.registradora_id AND P.id = V.producto_id
	GROUP BY R.departamento;

SQL Avanzado: Acumulación de resultados

UNION - Acumula los resultados de dos sentencias SELECT

  • Seleccionar personas cuyo apellido1 esté entre 'FERNANDEZ y 'HUERTAS'
  SELECT City FROM Customers
  UNION
  SELECT City FROM Suppliers
  ORDER BY City;

SQL Avanzado: Copiando información de unas tablas a otras

INTO

  • Copiando toda una tabla en otra
  SELECT * 
  INTO personasBackup 
  FROM personas;
  • Copiando toda una tabla en otra base de datos
  SELECT * 
  INTO nuevatabla [IN nuevabasedatos] 
  FROM tablaactual;
  • Copiando resultados filtrados de una tabla en otra
  SELECT * 
  INTO personasBackup 
  FROM personas 
  WHERE nombre = 'ANTONIO';
  • Copiando el resutlado de un JOIN en otra tabla
  SELECT personas.nombre, personas.apellido1, departamentos.departamento 
  INTO personasInformatica 
  FROM personas INNER JOIN departamentos 
  ON personas.dep = 'INFORMATICA';

SQL Avanzado: Restricciones

  • Limitan el tipo de dato que puede recibir una columna NOT NULL
  • No acepta el valor NULL, siempre tiene que tener datos
  CREATE TABLE personas 
  { 
  nombre varchar(255) NOT NULL, 
  apellido1 varchar(255) NOT NULL, 
  apellido2 varchar(255) 
  };

UNIQUE

  • Evita que se use el mismo dato en entre las filas de una columna. Ideal para identificadores
  CREATE TABLE personas
  { 
  identificador int NOT NULL, 
  nombre varchar(255) NOT NULL,
  apellido1 varchar(255) NOT NULL,
  apellido2 varchar(255),
  UNIQUE (identificador)
  };
  • Crear una id en una tabla ya creada previamente
  ALTER TABLE Persons
  ADD UNIQUE (P_Id);
  • Eliminar la Restricción
  ALTER TABLE Persons
  DROP INDEX uc_PersonID

PRIMARY KEY

  • Clave primaria en una columna
  CREATE TABLE personas 
  { 
    identificador int NOT NULL, 
    nombre varchar(255) NOT NULL, 
    apellido1 varchar(255) NOT NULL, 
    PRIMARY KEY (identificador) 
  };
  • Clave primaria usando multiples columnas
  CREATE TABLE personas 
  { 
    identificador int NOT NULL, 
    nombre varchar(255) NOT NULL, 
    apellido1 varchar(255) NOT NULL, 
    CONSTRAINT pers PRIMARY KEY (identificador, nombre) 
  };

FOREIGN KEY

  • Relaciona la clave primaria de otra tabla
  CREATE TABLE departamentos 
  { 
  dep int NOT NULL PRIMARY KEY, 
  departamento varchar(255), 
  };
  
  CREATE TABLE personas 
  { 
  per int NOT NULL PRIMARY KEY, 
  nombre varchar(255), 
  apellido1 varchar(255), 
  dep int FOREIGN KEY REFERENCES departamentos (dep) 
  };

CHECK

  • Verifica que los valores cumplen ciertas condicciones antes de ser incluidos
  CREATE TABLE Persons
  (
  P_Id int NOT NULL,
  LastName varchar(255) NOT NULL,
  FirstName varchar(255),
  Address varchar(255),
  City varchar(255),
  CHECK (P_Id>0)
  );

DEFAULT

  • Valor por defecto
  CREATE TABLE Persons
  (
  P_Id int NOT NULL,
  LastName varchar(255) NOT NULL,
  FirstName varchar(255),
  Address varchar(255),
  City varchar(255) DEFAULT 'Sandnes'
  );
  • Valor por defecto usando funciones
  CREATE TABLE Orders
  (
  O_Id int NOT NULL,
  OrderNo int NOT NULL,
  P_Id int,
  OrderDate date DEFAULT GETDATE()
  );

SQL Avanzado: Índices

  • Acelerando las búsquedas (pueden repetirse los valores de la columna)
  CREATE INDEX nombreindice 
  ON nombretabla (nombrecolumna);
  • Acelerando las búsquedas (NO pueden repetirse los valores de la columna)
  CREATE UNIQUE INDEX nombreindice 
  ON nombretabla (nombrecolumna);
  • Índices en múltiples columnas
  CREATE INDEX indice2personas 
  ON personas(apellido1, apellido2);

SQL Avanzado: Autoincremento

  • Crear tabla con autoincremento
  CREATE TABLE personas 
  { 
    persona int NOT NULL AUTO_INCREMENT, 
    nombre varchar(255), 
    apellido1 varchar(255), 
    apellido2 varchar(255) 
  };
  • Autoincremento partiendo de un número diferente a 0
  ALTER TABLE personas AUTO_INCREMENT=100;

SQL Avanzado: Usando Vistas, una tabla virtual

  • Crear una vista
  CREATE VIEW [personas que se llaman ANTONIO] AS 
  SELECT nombre, apellido1, apellido2,edad 
  FROM personas 
  WHERE nombre = 'ANTONIO';
  • Consultar la vista
  SELECT * FROM [personas que se llama ANTONIO]
  • Remplazar la vista
  REPLACE VIEW [personas que se llama ANTONIO] AS 
  SELECT nombre, apellido1, apellido2, edad 
  FROM personas 
  WHERE edad > 20;
  • Eliminar la vista
  DROP VIEW nombrevista;

SQL Avanzado: Fechas

  • Funciones
    • NOW() Devuelve la fecha actual y la hora
    • CURDATE() Devuelve la fecha actual
    • CURTIME() devuelve la hora actual
    • DATE() Extrae la fecha de un dato tipo fecha
    • EXTRACT() Extrae una parte concreta (año, dia, hora, etc..) de un dato tipo fecha
    • DATE_ADD() Añade un tiempo determinado a una fecha en el formato deseado
    • DATE_SUB() Resta un tiempo determinado a una fecha en el formato deseado
    • DATEDIFF() Devuelve el periodo entre dos fechas
    • DATE_FORMAT() Devuelve formatos personalizados en las fechas
  • Ejemplo
  CREATE TABLE pedidos 
  ( 
  idpedido int NOT NULL, 
  nombreproducto varchar(50) NOT NULL, 
  fechapedido datetime NOT NULL 
  PRIMARY KEY (idpedido) 
  );

SQL Avanzado: NULL

  • Filtrando por NULL
  SELECT * FROM personas WHERE apellido2 IS NULL
  • Evitando NULL
  SELECT * FROM personas WHERE apellido2 IS NOT NULL
  • sustituyendo NULL
  SELECT producto, 
  preciounidad * (unidadesstock + IFNULL(unidadespedido, 0) 
  FROM productos;

SQL Avanzado: Funciones de escalado

  • UCASE() Convierte a mayúsculas
  • LCASE() Convierte a minúsculas
  • MID() Extrae los carácteres
  • LEN() Devuelve la logitud de un texto
  • ROUND() Redondea al número de decimales especificados
  • NOW() Retorna la hora y fecha actual del sistema
  • FORMAT() Define como se ofrmatearán los campos

SQL Avanzado: Funciones de agregado

  • AVG() Retorna el promedio
  • COUNT() Retorna el número de filas
  • FIRST() Retorna el primer/os valor/es
  • LAST() Retorna el último/s valor/es
  • MAX() Retorna el valor mayor
  • MIN() Retorna el valor menos
  • SUM() Suma

SQL Avanzado: Agrupadores

  • Agrupa contenidos
  SELECT Shippers.ShipperName,COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
  LEFT JOIN Shippers
  ON Orders.ShipperID=Shippers.ShipperID
  GROUP BY ShipperName;