Estudio/Laboratorio - Aprendiendo SQL (con RDBMS MySQL) - Parte 3

SQL avanzado
 

Operadores 

Bloques con los que se construyen consultas complejas:

• Operadores lógicos:

  Se reducen a aportar un resultado booleano true(1) o false(0).
  AND, &&       OR, ||      NOT,!

• Operadores aritméticos:

  Suma: +   Resta: -  Multiplicación: *   División: /   Módulo: %
  El módulo es el resto de la división entera: 5%2=1   5=2*2+1

• Operadores de comparación:
  

  Ver enlace: op. comparación.
  -   NULL=NULL --> NULL       NULL<=>NULL --> 1
  -   NULL=    0     --> NULL       NULL<=>    0     --> 0
  -   mysql> SELECT 4.5 BETWEEN 4 AND 5;       --> 1
  -   mysql> SELECT 5 BETWEEN 6 AND 4;          --> 0
  -   mysql> SELECT 'a' IN ('b','c','a');                     --> 1

  • Equivalencias de patrón con LIKE:
    

    Utilización de caracteres comodin como % (cualquier número de caracteres) o _ (cualquier carácter).
    - mysql> SELECT 'abcd' LIKE '%bc%';    -->  1
    - mysql> SELECT 'abcd' LIKE 'a___';      -->  1
    - mysql> SELECT 'abcd' LIKE '%a_';       -->  0

  • Expresiones regulares:
    

    Permiten realizar comparaciones muy potentes y complejas entre cadenas de caracteres.
    

    field REGEXP "^(https?://|www\\.)[\.A-Za-z0-9\-]+\\.[a-zA-Z]{2,4}"
    Patrón para discriminar urls como, www.google.il, http://google.com/,  http://ww.google.net/, www.google.com/index.php?test=data, https://yahoo.dk/as, http://goo.gle.com/,http://wt.a.x24-s.org/ye/, www.website.info

• Operadores bit a bit: (suelen utilizarse muy poco)
   

Combinaciones avanzadas

Utilizamos las tablas creadas anteriormente:
 

 

• Combinaciones internas
   
  • Se unen tablas con campos comunes en los que los registros coinciden en ambas.
    

    mysql> SELECT comercial, cliente, valor, nombre, apellido FROM ventas, comerciales WHERE codigo=1 AND ventas.comercial = comerciales.num_empleado;

    mysql> SELECT CONCAT(comerciales.nombre,' ',comerciales.apellido) as Comercial , valor, CONCAT(clientes.nombre,' ',clientes.apellido) AS Cliente FROM ventas, comerciales, clientes WHERE ventas.comercial = comerciales.num_empleado AND ventas.cliente = clientes.id ORDER BY valor;

  • Otra forma de realizarlo: INNER JOIN
    

    -  Las siguientes dos sentencias son equivalentes:

    mysql> SELECT comercial, cliente, valor, nombre, apellido FROM ventas INNER JOIN comerciales ON comercial=num_empleado WHERE codigo=1;

    mysql> SELECT comercial, cliente, valor, nombre, apellido FROM ventas, comerciales WHERE codigo=1 AND ventas.comercial = comerciales.num_empleado;

    - Un ejemplo utilizando dos inner join:

    mysql> SELECT CONCAT(comerciales.nombre,' ',comerciales.apellido) as Comercial , valor, CONCAT(clientes.nombre,' ',clientes.apellido) AS Cliente FROM ventas, comerciales, clientes WHERE ventas.comercial = comerciales.num_empleado AND ventas.cliente = clientes.id ORDER BY valor;

    mysql> SELECT CONCAT(comerciales.nombre,' ',comerciales.apellido) as Comercial , valor, CONCAT(clientes.nombre,' ',clientes.apellido) AS Cliente FROM comerciales INNER JOIN ( ventas INNER JOIN clientes on cliente = clientes.id) ON comercial = comerciales.num_empleado ORDER BY valor;

• Combinaciones externas (por la izquierda o por la derecha)

  Añadimos una venta en la que el pago se ha realizado al contado, el cliente no está registrado en el sistema y no ha querido apuntarse (se introduce un NULL en el campo del cliente asociado a la tabla ventas):
  mysql> INSERT INTO ventas() VALUE (7,2,NULL,670)
  Display all 768 possibilities? (y or n)
  mysql> INSERT INTO ventas(codigo,comercial,cliente,valor) VALUE (7,2,NULL,670);
  mysql> SELECT * FORM ventas;

  Si ejecutamos la instrucción anterior que combinaba de forma interna las tres tablas, al no encontrarse el valor NULL de la tabla de ventas en la tabla de clientes, esa venta no se reflejará en la consulta. Se deben utilizar combinaciones externas.

  Una combinación externa devuelve todas las filas de la tabla de la izquierda o derecha (LEFT JOIN o RIGHT JOIN respectivamente), tengan o no coincidencia con las de la otra tabla. 

   mysql> SELECT CONCAT(comerciales.nombre,' ',comerciales.apellido) as Comercial , valor, CONCAT(clientes.nombre,' ',clientes.apellido) AS Cliente FROM ventas LEFT JOIN comerciales ON comercial=comerciales.num_empleado LEFT JOIN clientes ON cliente = clientes.id ORDER BY valor; 

• Combinaciones externas completas

  Por ahora parece ser que MySQL no las soporta. Se pueden emular utilizando una unión de una selección completa por la izquierda y por la derecha.

• Combinaciones naturales (NATURAL JOIN [USING])

  Cuando dos tablas tienen el mismo nombre para el campo por el que se quieren combinar se puede utilizar NATURAL JOIN.

  mysql> ALTER TABLE ventas CHANGE cliente id INT;
  mysql> SELECT nombre, apellido, valor FROM clientes NATURAL JOIN ventas;
  mysql> SELECT nombre, apellido, valor FROM ventas NATURAL LEFT JOIN clientes;

  Cuando dos tablas tienen más de un campo idénticos se puede utilizar la palabra clave USING para indicar los campos que deben ser coincidentes para la combinación. Añadimos un comercial a la tabla de clientes. Queremos saber cual de nuestros comerciales es a la vez un cliente; buscando sólo por apellido es ambiguo; buscamos por nombre y apellido, campos comunes en ambas tablas:

  mysql> INSERT INTO clientes VALUE (5,'Sol','Rive');
  mysql> SELECT id, clientes.nombre, clientes.apellido FROM clientes INNER JOIN comerciales ON apellido = comerciales.apellido ;
  ERROR 1052 (23000): Column 'apellido' in on clause is ambiguous
  mysql> SELECT id, clientes.nombre, clientes.apellido FROM clientes INNER JOIN comerciales USING (apellido,nombre) ;
  mysql> DELETE FROM clientes WHERE id=5; 

• Datos que aparecen en una tabla pero no en otra.
  

  Se han visto como recuperar registros que aparecen en dos tablas, o también todos los registros que aparecen en una tabla y que pueden estar o no en otra. Otra posibilidad es la de obtener solamente los resultados que se encuentran en una pero en la otra no.

  Podemos realizar una combinación externa filtrando los resultados en los que exista NULL para obtener los registros que se encuentran en una tabla, pero en otra no.  Insertamos un nuevo comercial; éste no ha realizado ninguna venta.

  mysql> INSERT INTO comerciales VALUES (5,'Jomo','Ignesund',10,'22-11-29','1968-12-01');
  mysql> SELECT nombre,apellido,comercial FROM comerciales LEFT JOIN ventas ON num_empleado = ventas.comercial;
   mysql> SELECT nombre,apellido FROM comerciales LEFT JOIN ventas ON num_empleado = comercial WHERE comercial IS NULL;

  Con la combinación externa se buscan todos los comerciales existentes y sus ventas, y si no las tienen también se incluyen, pero el campo de unión será NULL. Aprovechando esto, se realiza un filtrado con WHERE y IS NULL.  

• Combinación de resultados (UNION)

  Combina los resultados de diferentes instrucciones SELECT, cada una de ellas debe constar con el mismo numero de columnas. Devuelve resultados únicos (como si se aplicara DISTINCT). Se utiliza UNION ALL para obtener todos los resultados, duplicados incluidos:
  Creamos una nueva tabla con clientes_antiguos y unimos una petición de las tabla de clientes con ella:

  mysql> CREATE TABLE clientes_ant(id INT, nombre VARCHAR(30), apellido VARCHAR(40));
  mysql> CREATE TABLE clientes_ant(id INT, nombre VARCHAR(30), apellido VARCHAR(40));
  mysql> SELECT id,nombre,apellido FROM clientes UNION SELECT id,nombre,apellido FROM clientes_ant;

  Podemos ordenar la consulta completa con ORDER BY al final. Si se desea sólo una de las consultas individuales ordenadas se deben utilizar paréntesis:

  mysql> SELECT id,nombre,apellido FROM clientes UNION SELECT id,nombre,apellido FROM clientes_ant ORDER BY apellido,nombre;
  mysql> SELECT id,nombre,apellido FROM clientes UNION (SELECT id,nombre,apellido FROM clientes_ant ORDER BY apellido,nombre);

  Diferencias entre UNION y UNION ALL:
  mysql> SELECT id FROM clientes UNION SELECT id FROM ventas;   --> 5 resultados (1,2,3,4,NULL), sólo id distintas.
  mysql> SELECT id FROM clientes UNION ALL SELECT id FROM ventas;  --> 11 resultados (1,2,3,4,1,3,3,...). Ambas columnas completas.

• Subselecciones
  

  Realizar una selección dentro de otra operación de selección:

  mysql> SELECT nombre, apellido FROM comerciales WHERE num_empleado IN (SELECT codigo FROM ventas WHERE valor>1000);

  Es lo mismo que una combinación interna, que resultan ser más eficientes para realizar consultas y los resultados se recuperan con mayor rapidez:

  mysql> SELECT DISTINCT nombre,apellido,valor FROM comerciales INNER JOIN ventas ON num_empleado = ventas.comercial WHERE valor>1000;

  O también:

  mysql> SELECT DISTINCT nombre,apellido,valor FROM comerciales,ventas WHERE comerciales.num_empleado = ventas.comercial AND valor>1000;

Como agregar registros a una tabla desde otras tablas con INSERT SELECT

La instrucción INSERT también permite agregar registros a una tabla desde otras. Por ejemplo, queremos una nueva tabla que contenga los clientes y el valor de todas las comprar realizadas:

mysql> SELECT nombre,apellido,sum(valor) FROM ventas NATURAL JOIN clientes GROUP BY nombre,apellido;

Creamos tabla que reciba los resultados:

mysql> CREATE TABLE clientes_compras_tot(nombre VARCHAR(30),apellido VARCHAR(40), compras_totales INT) ;
mysql> INSERT INTO clientes_compras_tot(nombre,apellido,compras_totales) SELECT nombre,apellido,sum(valor) FROM ventas NATURAL JOIN clientes GROUP BY nombre,apellido;
 mysql> SELECT * FROM clientes_compras_tot;

Mas sobre la agregación de registros

• SELECT permite una sintaxis similar a instrucción UPDATE:
  

  mysql> INSERT INTO clientes_compras_tot(nombre, apellido, compras_totales) VALUES ('Charles','Dube',0);
  Equivale a:
  mysql> INSERT INTO clientes_compras_tot SET nombre='Charles', apellido='Dube', compras_totales=0;

• Se puede llevar a cabo una forma limitada de cálculo al agregar registros:
  

  mysql> ALTER TABLE clientes_compras_tot ADD value2 INT;
  mysql> INSERT INTO clientes_compras_tot VALUES ('Gladis','Malherbe',5,compras_totales*2)

Mas sobre como eliminar registros (DELETE y TRUNCATE)

Se pueden eliminar todos los registros de una tabla con una instrucción DELETE sin utilizar ningún filtro con WHERE:

mysql> DELETE FROM clientes_compras_tot;

La mejor forma y más rápida de realizarlo es mediante TRUNCATE:

mysql> TRUNCATE clientes_compras_tot;  

Variables de usuario (SET ó SELECT  @_:=)

SQL consta de funciones que le permiten almacenar valores como variables temporales. Es habitual utilizar un lenguaje de programación para realizar este tipo de acciones, pero también pueden resultar útiles cuando se trabaja en la linea de comandos:

mysql> SELECT @avg := AVG(comision) FROM comerciales;
mysql> SELECT @avg;
mysql> SELECT nombre,apellido FROM comerciales WHERE comision>@avg;

Podemos asignar una variable de forma específica con SET:

mysql> SET @resultado=22/7;
mysql> SELECT @resultado;

Las variables de usuario se establecen en un subproceso dado y ningún otro proceso puede acceder a ellas. Al cerrar el proceso o perder la conexión las variables dejan de estar asignadas.

SQL y archivos  

• Modo de procesamiento por lotes

Guardamos en un archivo llamado test_archivo.sql las siguientes lineas para incluir dos clientes:

INSERT INTO clientes(id,nombre,apellido) VALUES (5,'Francouis','Papo')
INSERT INTO clientes(id,nombre,apellido) VALUES (6,'Neil','Benteke')

Podemos ejecutar estás líneas desde la linea de comandos del sistema operativo:

mysql practiceDB < test_archivo.sql

Si algunas de las líneas del archivo contiene algún error, mySQL interrumpirá el procesamiento del archivo. Para seguir procesando el resto del archivo aunque alguna de las líneas sea errónea se debe utilizar la opción force (-f): 

mysql -f practiceDB < test_archivo.sql 

• Usar archivo de procesamiento por lotes desde la terminar MySQL 
  Desde la terminar de MySQL se puede ejecutar los comandos almacenados en un archivo utilizando el comando SOURCE:
  

  mysql> SOURCE test_archivo.sql

•  Redireccionamiento de la salida hacia un archivo
   
  Modificamos el archivo test_archivo.sql:
  

  DELETE FROM clientes WHERE id>=5;
  INSERT INTO clientes(id,nombre,apellido) VALUES (5,'Francouis','Papo');
  INSERT INTO clientes(id,nombre,apellido) VALUES (6,'Neil','Benteke');
  SELECT * FROM clientes;

  Borra las entradas que hemos puesto, las vuelve a añadir y realizamos una petición para ver la tabla completa que hemos modificado. Redirigimos la salida estandar hacia un archivo:
  

  mysql practiceDB < test_archivo.sql > test_salida.txt

  Si abrimos el archivo test_salida.txt podemos observar que se ha guardado la tabla clientes, utilizando una tabulación (\t) para separar campos.
  Podemos activar el formato interactivo (el mismo que se muestra en la terminar de MySQL) en el archivo de salida con la opción -t:
  

  mysql -t practiceDB < test_archivo.sql > test_salida.txt

Transacciones y bloqueos