SQL

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

• Información General - SQL.
• Cosas más específicas para Helpcom, por motor: MySQL-sintaxis, PostgreSQL-sintaxis, SQLite-sintaxis.

Un schema es una unidad lógica estructural que reúne tablas, vistas y funciones. Un schema está bien definido en SQ93 y en PostgreSQL; en MySQL, un schema es equivalente a una base de datos.

CREATE SCHEMA helpcomschema OWNER usuario; -- crear un schema
GRANT USAGE ON SCHEMA helpcomschema TO usuario2; -- permitir conectar
DROP SCHEMA helpcomschema; -- bajar un schema si no hay dependencias
DROP SCHEMA helpcomschema CASCADE; -- bajar un schema y todos sus objetos.

En PostgreSQL se muestra los schemas de una conexión con \dn y los schemas foráneos con \det+.

En SQLite y MySQL no existe una forma estandarizada de mostrar los schemas de una conexión.

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 UNIQUE,
  titulo TEXT NOT NULL,
  autor_id INTEGER,
  edicion_id INTEGER,
  "año" SMALLINT UNSIGNED,
  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     | text                  |           |          | 
 author_id  | integer               |           |          | 
 edicion_id | integer               |           |          | 
 año        | smallint unsigned     |           |          | 
 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.

En PostgreSQL, SQLite, etc, y en SQ92 no existe una sentencia directa para observar el schema. Aunque la tabla mostrada anteriormente se puede componer usando los registros del schema de sistema information_schema:

SELECT column_name,data_type,column_default AS "def?",is_nullable AS "null?", (CASE WHEN data_type LIKE '%character%' THEN character_maximum_length WHEN data_type LIKE '%int%' THEN numeric_precision ELSE '0' END ) AS "variable", generation_expression 
FROM information_schema.columns 
WHERE table_catalog='database' AND table_schema='helpcomschema' AND TABLE_NAME='libro';

Lo que resulta en la siguiente salida:

 column_name |     data_type     | def? | null? | variable | generation_expression 
-------------+-------------------+------+-------+----------+-----------------------
 isbn        | character varying |      | NO    |       20 | 
 titulo      | text              |      | NO    |        0 | 
 autor_id    | integer           |      | YES   |       32 | 
 edicion_id  | integer           |      | YES   |       32 | 
 año         | smallint          |      | YES   |       16 | 
 hardprint   | boolean           |      | YES   |        0 | 
 hardcover   | boolean           |      | YES   |        0 | 

Mostrar tablas en el schema:

En lenguaje SQL no hay una forma estandarizada para listar tablas. En distintos motores existen distintos mecanismos.

• Postgres: \dt , \det y similares.
• MySQL: show tables; y similares.
• SQite: .tables.

En SQL92 se puede obtener una vista generalizada de tablas de un schema consultando information_schema.tables:

SELECT TABLE_NAME, table_type 
FROM information_schema.tables 
WHERE table_catalog='mibdd' AND table_schema='helpcomschema';

Las tablas con tipo BASE TYPE son creadas con CREATE TABLE. Objetos de otros tipos, como LOCAL TABLE, son creados por otras instrucciones.

Eliminar una tabla

DROP TABLE libro; -- eliminar si no hay depedencias
DROP TABLE libro CASCADE; -- eliminar la tabla, y todas sus filas y tablas dependientes.

Una vista es una tabla virtual cuya estructura calza la definición de tipo de datos de una consulta, es decir está definida en términos de un SELECT:

  CREATE VIEW vista AS  
  SELECT f(x,y) AS columna1, CAST(g(x,y) AS INTEGER) AS columna2 
  FROM x JOIN Y ON ...
  ORDER BY ...
  ;

Para que una vista esté definida, la consulta debe ser resoluble al momento de crear la vista, ergo todos los nombres, funciones y schemas usados en la vista deben existir.

Eliminar una vista

DROP VIEW vista;

:: TBD

Los siguientes tipos de datos son globales en SQL:

Cada motor agrega sus tipos de datos propios. Algunos tipos que son bastante comunes son VARCHAR(n) (un array de tamaño dinámico de caracteres parseado como texto); DATETIME, DATE y TIME (estampas de fecha y hora); DECIMAL(m,d) (un número de punto flotante de d decimales) y ENUM (variable que toma valores dentro de una lista de valores discretos).

:: TBD

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 la tupla de tablas nombradas en el FROM.
• 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.

Las instrucciones INSERT y UPDATE agregan o actualizan información.

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

La sintaxis en general es la misma entre MySQL, PostgreSQL y SQLite.

MySQL y SQLite permiten INSERT IGNORE, que inserta una fila si se puede hacer sin errores y termina sin hacer nada y sin errores si no se puede.

La instrucción DELETE permite eliminar registros (filas) de una tabla en la base de datos.

  DELETE FROM tablas 
  WHERE condiciones...
  ;

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

• MariaDB SQL Server - https://downloads.mariadb.org/ - Usado para Crux.
• MySQL Server Community - https://dev.mysql.com/downloads/mysql/ .
• 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/ .
• MySQL Query Browser (desahuciado en 2019).
• SQYog Community.
• MySQL Workbench.
• mysql - línea de comandos para un servicio de MySQL o MariaDB.
• psql - línea de comandos para un servicio de PostgreSQL.
• sqlite3 - línea de comandos para procesar un archivo de SQLite.
• SQLitebrowser

Páginas que pueden tener información relacionada: