SQL & Bases de Datos

Esta información es un resumen introductorio bien delimitado sobre el lenguage de consulta estructurado (SQL) utilizado por motores de bases de datos. El objetivo es brindar un panorama de la anatomía de SQL o noción esencial sobre este lenguaje de consulta. Plantear un horizonte inicial o tener esta información como memoria te resultará simplificado, ya que nos centraremos principalmente en las sentencias esenciales de manipulación de datos del lenguaje (conocidas como DML) citando las cuatro siguientes: INSERT, UPDATE, DELETE y SELECT.

El contexto de las Bases de Datos

Dado que hablamos de software enfocado en gestionar los datos para aplicaciones que lo requieran, las bases de datos SQL giran en torno a la manipulación de estructuras que se conocen como tablas, semejante a una pestaña en hojas de cálculo donde se definen columnas, pero su concepto se fundamenta en colecciones o conjuntos de datos diversos que para nuestro asunto siempre han de ser bien definidos o estructurados y de allí que se usa un lenguaje estructurado. Actualmente, también existe alternativa denominada NoSQL (Not Only SQL) que no requiere estructuras fijas y/o plantea una propuesta distinta a SQL (por ejemplo, una API específica según el motor de base de datos), por lo tanto no tendría que ver con este documento.

Las bases de datos SQL han sido la respuesta a lenguajes antiguos (como COBOL) que desde los año 60 ya permitían gestionar archivos (incluso papel denominado tarjetas perforadas) que contenían datos. En ese contexto histórico, con el lenguaje SQL se abre un panorama de tecnologías para facilitar el acceso a la información de un modo estándar y más sencillo (eso sí, basándose en estructuras o tablas). Durante la década de los noventa su acogida marcó la tendencia y mientras las empresas soporten sistemas basados en SQL puede ser un requisito para quién se ocupe como analista o programador. Distinto si planteas una nueva tecnología sin importar el criterio técnico, por ejemplo, una plataforma web para las masas, o si te arriesgas a la acogida de tecnología innovadora considerando que diversas empresas tienen cierta tendencia a seguir el camino más conocido para ellas.

CREATE TABLE & DROP TABLE

Antes de revisar las sentencias esenciales de manipulación de datos del lenguaje (DML) INSERT, UPDATE, DELETE y SELECT, es necesario definir la estructura de datos con la sentencia CREATE TABLE. Esto se apoya en teorías informáticas para la definición de tablas debidamente organizadas con registros o filas identificables, asociado a las bases de datos relacionales. Puede pensarse en principios como una hoja de datos o cuadrícula con columnas, las cuales se establecen con la sentencia CREATE TABLE. Veamos:

CREATE TABLE table_name (
    column1 number,
    column2 varchar,
    column3 varchar,
    constraint pk_column1 primary key (column1)
);

Cada columna tiene un tipo de datos, bien sea numérico (number) texto (varchar) u otro como el caso de fechas, y se define una columna con identificación única (constraint...primary key (...)). Una vez creada se pueden insertar datos (con INSERT).

Para eliminar una tabla que se ha creado por error o no es necesaria se usaría:

DROP TABLE table_name;

INSERT

Sentencia usada para insertar filas o registros de datos

INSERT INTO table_name (campos separados por comas) VALUES (valores separados por comas);

Los campos se separan por comas y los valores igual teniendo presente si son cadenas de carecteres o números (el tipo de datos)

UPDATE

Sentencia usada para actualizar filas o registros de datos conforme una condición indicada

UPDATE table_name SET (asignaciones de campos) WHERE (condiciones);

Las asignaciones utilizan el signo igual (=, por ejemplo: column = 'x') y se separan por comas. Para los valores debe tenerse presente si son cadenas de carecteres o números (el tipo de datos). Las condiciones son similares (se usa =) pero pueden ser compuestas (usando operadores como AND, OR, BETWEEN, IN, NOT IN, entre otros que se invita a investigar).

DELETE

Sentencia usada para eliminar filas o registros de datos conforme una condición indicada

DELETE FROM table_name WHERE (condiciones);

Las condiciones utilizan el signo igual (=, por ejemplo: column = 'x') y pueden ser compuestas (usando operadores como AND, OR, BETWEEN, IN, NOT IN, entre otros que se invita a investigar).

SELECT

Es la sentencia más usada, permite extraer o consultar datos dado un criterio o condición

SELECT * FROM table_name WHERE (condiciones);

Las condiciones utilizan el signo igual (=, por ejemplo: column = 'x') y pueden ser compuestas (usando operadores como AND, OR, BETWEEN, IN, NOT IN, entre otros que se invita a investigar).

Veamos un ejemplo:

SELECT product_id, product_name, price
FROM product
WHERE price > 1000
ORDER BY price DESC

Lo que se está indicando a Oracle es que seleccione ciertos datos (SELECT...) de productos (FROM...) en dónde el precio sea mayor a 1000 (WHERE...).
ORDER BY permite ordenar por una columna, y al indicar DESC produce un ordenamiento descendente.

Las opciones que soporta esta sentencia son variadas y su complejidad puede incrementar, sobretodo cuando se combinan varias tablas (usando JOIN), lo cual amerita mayor material de conocimiento y toda una práctica. Existen bases de datos NoSQL como Cassandra, OrientDB o Elasticsearch (quizás otras) que son cercanas a SQL y precisamente usan la sentencia SELECT semejante a la forma expresada (sin lugar al JOIN), o Couchbase que es más cercana a SQL (incluso admite JOIN). Si se comprende que coinciden los fundamentos (al menos los más elementales) se tiene acceso a variedad de proveedores de bases de datos, es por esto que el presente documento se expone de la manera más simple a modo de abrebocas para que continúes tu iniciación si así lo requieres.

Ejemplo de JOIN en la cláusula SELECT

JOIN es la palabra reservada que se usa con SELECT para relacionar y combinar datos en gestores de bases de datos relacionales y es uno de los factores diferenciadores de este lenguaje, por lo que no es fácil encontrar un paralelo justo en bases de datos NoSQL, ni existe en bases dedatos cercanas a SQL como Cassandra, OrientDB o Elasticsearch. Como principio las tablas que se consultan generan conjuntos de datos y se pueden relacionar con JOIN. Veamos esto con un ejemplo:

SELECT b.name, a.salary
FROM employee AS a
INNER JOIN person AS b
ON a.person_id = b.id

Tanto en FROM como en INNER JOIN se pueden identificar las tablas que se relacionan (employee + person). Se usa ON (semejante a WHERE en este caso) para establecer el criterio de combinación de los conjuntos de datos seleccionados, que corresponde a los identificadores de las tablas. Por supuesto que se puede agregar filtros de selección usando también WHERE.

Mientras INNER JOIN se usa para devolver la información que coincide estrictamente en los dos conjuntos de datos, existe OUTER JOIN para los casos en los que se requiere que se retorne filas de datos aunque no exista coincidencia en el otro conjunto, devolviendo esas columnas vacías (con nulos). Veamos el siguiente ejemplo:

SELECT a.name, b.name
FROM person AS a
LEFT OUTER JOIN person AS b
ON b.id = a.spouse

Algunos RDBMS (motores de bases de datos)

Sistemas de gestión de base de datos relacionales

A continuación podemos ilustrar de modo esencial como introducirnos con algunos RDBMS (Relational Database Management System).

Tips sobre MariaDB

mysql -u root -p es el comando para ingresar a gestionar la base de datos MariaDB desde una terminal (teniendo la contraseña respectiva, y por supuesto, teniendo instalada MariaDB). En el sistema Debian suele usarse simplemente sudo mysql.

Independentemente del sistema operativo, una vez se tiene acceso al servicio de éste motor de base de datos y su gestor (desde la línea de comandos, usando: mysql -u root -p), de requerirse se puede crear la base datos o esquema, el usuario que la gestiona y asignar los privilegios respectivos, por ejemplo, usando los siguientes comandos:

CREATE DATABASE IF NOT EXISTS mytest;
CREATE USER 'mytest'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON mytest.* TO 'mytest'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

El esquema se crea con la sentencia CREATE DATABASE ... dónde mytest corresopndería al nombre que se asigne.
El usuario respectivo se crea con la sentencia CREATE USER ... dónde password corresponde a la palabra de acceso, y si se usa @'localhost' sólo se otorga acceso local, no público (siendo una aplicación o una terminal la que accede al mismo servidor internamente, más si es otro servidor físico o para acceso externo no funcionaría).
El comando GRANT otorga los privilegios sobre la base de datos específica.

Por otra parte, el conjunto de caracteres juega un papel clave sobre los datos debido a los caracteres admitidos y su almacenamiento. De modo convencional solía usarse lo siguiente:

ALTER DATABASE xybox CHARACTER SET utf8 COLLATE utf8_general_ci;

Actualmente, con la globalización y el uso de emojis podría ser preferible usar: ALTER DATABASE xybox CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Si estos comandos se tienen preparados en un archivo de script (con extensión .sql), entonces en su lugar se invoca con SOURCE el archivo respectivo, por ejemplo:

SOURCE script.sql

Para salir de este programa se usa exit. También puedes crear tablas con CREATE TABLE ... o hacer operaciones sobre los datos (SELECT, INSERT, UPDATE, DELETE). Veamos un ejemplo sencillo de CREATE TABLE para definir lo que se conoce como una tabla, así:

CREATE TABLE IF NOT EXISTS mytable (
    id int not null auto_increment,
    firstname varchar(30) not null,
    lastname varchar(30),
    constraint pktable primary key (id)
);

Tips sobre PostgreSQL

psql es el comando para ingresar a gestionar la base de datos PostgreSQL desde una terminal

Para establecer una base de datos ingresas desde la linea de comandos psql y podemos citar el siguiente ejemplo:

CREATE DATABASE mytest;
CREATE ROLE mytest WITH LOGIN;
ALTER USER mytest PASSWORD 'password';
GRANT ALL PRIVILEGES ON DATABASE mytest TO mytest;
CREATE TABLESPACE mytest LOCATION '/var/lib/postgresql/data/mytest.dbf';
CREATE SCHEMA mytest;
\q

Si estos comandos se tienen preparados en un archivo de script (con extensión .sql), entonces en su lugar se invoca con \i el archivo respectivo desde psql, por ejemplo:

\i script.sql

Incluso puede invocarse desde una linea de comandos asi:

psql -f script.sql

Tips sobre OracleXE

Puede considerarse “Oracle 18c XE” que es un motor de base de datos empresarial líder, y actualmente cuenta con una versión disponible para su uso (free hasta 12GB en disco, 2GB de RAM y 2 CPU) que puedes instalar en caso de que tengas conocimientos o planes de aprender. Esta versión es sencilla de instalar en Windows, simplemente se decargá y se ejecuta el instalador.

Si deseas usar una versión de Oracle en la nube debes evaluar la adquisicion de licencia en un entorno de producción o aplicar al servicio de Oracle Cloud.

Para establecer una base de datos ingresas desde la linea de comandos sqlplus y podemos citar el siguiente ejemplo:

CONNECT system/manager as sysdba
CREATE TABLESPACE ONE LOGGING DATAFILE 'C:\oracle\oradata\one.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TWO LOGGING DATAFILE 'C:\oracle\oradata\two.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE USER MYTEST PROFILE DEFAULT IDENTIFIED BY "password" DEFAULT TABLESPACE ONE TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;
GRANT CONNECT, RESOURCE, CREATE SESSION, UNLIMITED TABLESPACE, CREATE TABLE, CREATE ANY INDEX TO MYTEST;
QUIT

Si estos comandos se tienen preparados en un archivo de script (con extensión .sql), entonces en su lugar se invoca con start (o @) el archivo respectivo desde sqlplus, por ejemplo:

start script.sql

Incluso puede invocarse desde una linea de comandos asi:

echo "start script.sql" | sqlplus / as sysdba

Es posible evitar usar el usuario propietario en un programa, aplicación o micro-servicio, combinando el uso de un rol (el cual se usa para agrupar privilegios), otro usuario y ciertos privilegios sobre tablas. Si pensamos en un script con los privilegios necesarios aplicados a un rol y a un usuario adicional destinado para una aplicacion, prodríamos considerar un código como el siguiente:

CREATE ROLE MYROLE;
GRANT CONNECT, RESOURCE, CREATE SESSION, EXECUTE ANY PROCEDURE TO MYROLE;
CREATE USER MYAPP PROFILE DEFAULT IDENTIFIED BY "password" ACCOUNT UNLOCK;
GRANT MYROLE TO MYAPP;

-- Tables
BEGIN
    FOR ITEM IN (SELECT * FROM DBA_TABLES WHERE OWNER = 'MYTEST') 
    LOOP  
        EXECUTE IMMEDIATE 'GRANT SELECT, UPDATE, INSERT, DELETE ON ' || ITEM.OWNER || '.' || ITEM.TABLE_NAME || ' TO MYROLE';    
    END LOOP;
END;
/

Lo que se busca es separar conceptos teniendo un usuario propietario de la base de datos (MYTEST), un rol (MYROLE) al cual se asignan privilegios que se otorgan a un usuario para la aplicación (MYAPP), el cual será usado para conectarse desde un programa o micro-servicio.

Nótese que existe un bloque de código para procesar privilegios sobre cualquier tabla del usuario propietario (MYTEST). En caso de tener sequencias (usadas para incrementar un id) se puede hacer algo semejante a lo ilustrado para las tablas, es decir, se usaría SELECT * FROM DBA_SEQUENCES WHERE SEQUENCE_OWNER = 'MYTEST' y luego EXECUTE IMMEDIATE 'GRANT SELECT ON ' || ITEM.SEQUENCE_OWNER || '.' || ITEM.SEQUENCE_NAME || ' TO MYROLE'; respectivamente.

© 2019 by César Arcila