SQL

Información general de lenguaje SQL, motores y herramientas.

• Información General - SQL.

La instrucción CREATE TABLE crea tablas con columnas siguiendo tipos de datos.

  CREATE TABLE tabla 
  ( columna1 tipo1 modificadores...
  , columna2 tipo2 modificadores...
  , ....
    PRIMARY KEY (tupla...)
  );

Este comando crea una tabla llamada tabla con N columnas, llamadas columna1...columnaN, con tipos y modificadores cada uno en su línea. Las llaves primarias de la tabla se designan juntando las columnas en una tupla....

Por ejemplo:

CREATE TABLE libro (
  isbn VARCHAR(20) NOT NULL,
  titulo VARCHAR(80) NOT NULL,
  autor INTEGER,
  edicion INTEGER,
  anyo INTEGER
  hardprint BOOLEAN,
  hardcover BOOLEAN,
  PRIMARY KEY (isbn)
);

Crea una tabla con la estructura siguiente:

                        Table "public.libro"
  Column   |         Type          | Collation | Nullable | Default 
-----------+-----------------------+-----------+----------+---------
 isbn      | character varying(20) |           | not null | 
 titulo    | character varying(85) |           |          | 
 author    | integer               |           |          | 
 edicion   | integer               |           |          | 
 anyo      | integer               |           |          | 
 hardprint | boolean               |           |          | 
 hardcover | boolean               |           |          | 
Indexes:
    "libro_pkey" PRIMARY KEY, btree (isbn)

Si se tiene los privilegios adecuados, una tabla creada se elimina con DROP tabla.

En MySQL es posible recuperar la sentencia que creó una tabla con SHOW CREATE TABLE tabla.

La instrucción SELECT es para buscar información.

  SELECT campos
  FROM tablas
  WHERE (condicion1 [AND/OR] condicion2 [AND/OR]...)
  [ORDER BY orden [ASC/DESC] ]
  ;

En la consulta:

• campos puede ser una lista de uno o más campos, conteos o fórmulas. Asterisco (“*”) se usa para indicar mostrar todos los campos de una tabla.
• tablas puede ser una lista de una o más tablas o uniones de tablas (JOIN, UNION, etc), con o sin su fuente de base de datos (por ejemplo, crux.mae_usuarios o simplemente mae_usuarios).
• condicion1, condicion2 es una lista opcional de condiciones en el lenguaje lógico de SQL. Una condición puede ser comparar el valor de un campo, por ejemplo “WHERE mae_nombre_empresa LIKE '%Lily%' ”.
• orden es una lista de campos o de fórmulas, del más prioritario al menos prioritario, para ordenar los resultados. Por ejemplo “ORDER BY cantidad_vendida, WEEKDAY(fecha_venta)” ordenaría una lista de ventas de la que vendió más a menos y luego por el día de semana de la venta.

Ojo: Distintos motores como MySQL y PostgreSQL pueden extender la sintaxis.

Ejemplos de búsquedas en bases de datos de Helpcom

Buscar aquellos clientes en una tabla de clientes que pertenecen a cierta comuna:

  SELECT * FROM mae_clientes 
  WHERE cli_comuna LIKE '%comuna%'

Top 5 de las comunas con más clientes:

  SELECT cli_comuna, COUNT(*) AS cantidad FROM mae_clientes 
  GROUP BY cli_comuna
  ORDER BY cantidad DESC LIMIT 5

Buscar todos aquellos folios de un CAF (identificado por su tipo de documento y folio de partida de ingreso) que no están en uso:

  SELECT dte_folios.* FROM dte_folios JOIN dte_folios_ingreso USING foi_id 
  WHERE tdo_id = 33 AND foi_desde = 123456 AND fol_usado = 'NO'

Buscar todos los RUT de los clientes que están mal ingresados porque no corresponden al formato del RUT:

  SELECT * from mae_clientes
  WHERE cli_rut NOT REGEXP '[1-9][0-9]+\-[0-9kK]'

Explicación del filtro:

A veces tener comillas simples o dobles como parte de un campo puede causar errores en bases de datos de MySQL no sanitizadas. Busquemos todos los clientes que tengan comillas dobles en la dirección (cosas como O'Brian, O'Higgins pero mal escritas: O"Brian, O"Higgins, como sucede en la base de datos de Rodeo) y veamos cómo se ven si les cambiamos las comillas simples por comillas tipográficas Unicode (“’”):

  SELECT cli_id, cli_rut, cli_direccion as nombre_antes, replace(cli_direccion, '"', '’') as nombre_despues FROM mae_clientes
  WHERE cli_direccion LIKE '%"%'

Las instrucciones INSERT y UPDATE agregan información.

  INSERT INTO tablas VALUES
  (fila1),(fila2), ...
  ;
 
  UPDATE tablas SET cambios...
  WHERE (condicion1 [AND/OR] condicion2 [AND/OR]...)
  ;

En la consulta:

• tablas es la lista de tablas que se desea modificar. Se coloca más de una tabla cuando el WHERE depende de un JOIN por ejemplo.
• cambios es la lista de cambios que se desea aplicar, de la forma campo = valor, ... o campo = formula(...), ... . Para cambiar campos de más de una tabla se usa el nombre completo del campo por ejemplo SET tabla1.campoA = valor, tabla2.campoB = valor, ....
• condicion1, condicion2 es una lista opcional de condiciones en el lenguaje lógico de MySQL. Una condición puede ser comparar el valor de un campo, por ejemplo “WHERE mae_nombre_empresa LIKE '%Lily%' ”.

Ejemplos de búsquedas en bases de datos de Helpcom

Actualizar el nombre de una comuna mal escrita:

  UPDATE mae_clientes 
  SET cli_comuna = 'LAUTARO' 
  WHERE cli_comuna = 'LAITARO'

Corregir todos los RUT con “-k” para que tengan mayúsculas:

  UPDATE mae_clientes
  SET cli_rut = replace(cli_rut, '-k', '-K')

Cambiar las comillas normales por comillas tipográficas, siguiendo el ejemplo del SELECT en la sección anterior:

  UPDATE mae_clientes
  SET cli_direccion= replace(cli_direccion, '"', '’') 
  WHERE cli_direccion LIKE '%"%';

Marcar un grupo de folios de nota de crédito DTE como no impresos:

  UPDATE dte_folios
  SET fol_archivo_generado = 'NO'
  WHERE tdo_id = 61 AND fol_numero IN (101, 102, 104, 107, 109)

Los siguientes son ejemplos de software que se están usando en Helpcom.

• MySQL Server Community - https://dev.mysql.com/downloads/mysql/ . Usado por productos como Crux.
• PostgreSQL Server - http://postgresql.org/ . Usado por productos como Orión.
• SQLite - https://www.sqlite.org/ - usado por productos como Draco.
• MongoDB
• VoltDB

• DBeaver Community - https://dbeaver.io/download/ .

DBeaver es un GUI (interface gráfica) para la gestión de base de datos en varios motores. Es abierto y multiplataforma basado en Java FX y está disponible en los recursos compartidos de Helpcom. Ofrecen descargas tanto para Linux como Windows.

Para la instalación ver Dbeaver.

• MySQL Query Browser
• MySQL Workbench

Query Browser es un GUI (interface gráfica) para la consulta de base de datos MySQL Server 4.1 a 5.5. MySQL Workbench es el sucesor de Query Browser y soporta modelos de bases de datos de MySQL 5.1 a 8.x, y está disponible en Linux en el gestor de paquetes con un nombre como mysql-tools-workbench o similar.

Para la instalación ver Query Browser.

• mysql

mysql es la línea de comandos de MySQL. Viene como opción en MySQL Community Server en Windows y en Linux viene disponible como un paquete con un nombre como mysql-client o similar.

• psql

psql es la línea de comandos de PostgreSQL. En Linux viene disponible como un paquete con un nombre como postgresql-client o similar.

• SQLitebrowser

SQLitebrowser es una interface gráfica para crear, gestionar y respaldar archivos de base de datos en SQLite2 (no en SQLite3). En Linux está disponible con el nombre de paquete sqlitebrowser o similar.