Aprendiendo SQL con PostgreSQL
1 - Aprendiendo SQL con PostgreSQLLa base de datos es el corazón de toda empresa, en ella se almacena y se gestiona toda la información que se necesita para operar y crecer. Las (buenas) bases de datos se manejan a través de SQL (Standar Query Lenguage) que es un conjunto de comandos a través de los cuales podemos insertar, borrar y editar la información de la base de datos además de hacer consultas (queries) sobre la información contenida. PostgreSQL (PgSQL) es un excelente y veloz gestor de base de datos de código abierto que posee características avanzadas como manejo de concurrencia, procedimientos almacenados e integridad referencial. Como en muchos otros casos en el mundo del software libre, uno se sorprende de que algo tan bueno como PostgreSQL pueda ser descargado y usado sin pagar nada.
2 - Para instalar PostgreSQL
# apt-get install postgresql
Al instalar PgSQL con apt-get también se instalará psql, el cliente de PostgreSQL, además se añadirá al sistema el usuario postgres. Este usuario será el dueño de la base de datos pues por seguridad no puede ser el root. Asignemos un nuevo password a este usuario con el comando:
# passwd postgres
Ahora cambiémonos a este usuario:
# su postgres
3 - Crearemos una nueva base de datos $createdb dbmiempresa
Verá el mensaje “CREATE DATABASE”. Una vez que hemos creado la base de datos podemos acceder a ella con el cliente de PgSQL :
$psql dbmiempresa
Al acceder a la base de datos verá el mensaje de bienvenida de PgSQL, un prompt con el nombre de la base de datos en la que se encuentra, seguido de un signo de igual y un sostenido:
4 - Tablas y campos
Como cualquier otra base de datos, PGSQL guarda los datos en tablas. Si usted ha usado una hoja de cálculo como Excel u OpenCalc usted ya posee el concepto de tabla: una serie de columnas y renglones en donde las intersecciones son llamadas “celdas”. De hecho, una hoja de cálculo puede considerarse un tipo de base de datos. La diferencia principal con una verdadera base de datos radica en que cada columna guarda un tipo de valor específico (números, decimales, fechas, alfanuméricos, ) y que en lugar de “celdas”, el lugar donde se guardan los datos son llamados “campos”. Por otra parte, a los renglones se les llama “registros”.
De este modo, para crear una tabla es necesario definir el tipo de información que se almacenara en cada columna. PgSQL posee varios tipos de datos, los más usados son:
VARCHAR(n) Almacena cadena de caracteres de longitud variable, de longitud máxima n.
CHAR(n). Almacena cadena de caracteres de longitud fija, de longitud n.
INTEGER. Almacena números enteros como 2,33,107,15228, etc. NUMERIC. Almacena números enteros y decimales.
DATE, almacena fechas.
FLOAT: numérico con signo de doble palabra y coma flotante. TEXT. Guarda textos largos.
SERIAL. Crea una secuencia y la asigna a una columna como llave de la tabla.
Para crear una tabla se usa el comando “CREATE TABLE nombre_de_tabla”. Por ejemplo, vamos a crear la tabla empleados. Corte y pegue las siguientes lineas :
CREATE TABLE empleados (
id serial, --Indice de la tabla
nombre varchar(50), --nombre del empleado
rfc varchar(20) UNIQUE, --registro de hacienda del empleado fecnac date, --fecha de nacimiento del empleado
sucursal varchar(30), --sucursal donde labora sueldo money NOT NULL --Sueldo
);
SQL acepta comentarios, simplemente escríbalos después de un doble guión (--). Verá un mensaje indicando que se ha creado una tabla y una secuencia para el campo id.
Al hacer uso de psql debemos concluir los comandos SQL con un punto y coma (;) de otro modo, si sólo damos enter, el comando no se ejecutará y el prompt cambiará de un signo de igual(=) a un guión, indicando que psql está en espera del punto y la coma para ejecutar el comando SQL. Por otra parte, si abre un paréntesis [(] y da enter sin cerrarlo, el prompt de psql mostrará un paréntesis de cierre indicando que debe cerrarlo.
Por convención (no sólo en PostgreSQL sino en todas las bases de datos) se usan las MAYÚSCULAS para escribir las palabras SQL en las sentencias, mientras que el nombre de los campos y las tablas se escriben en minúsculas, por ejemplo:
SELECT nombre, fecha FROM ventas;
Algunos diseñadores recomiendan nombrar a los campos de una tabla con el formato nbd_ntb_campo. Donde nbd es el nombre de la base de datos y ntb es el nombre de la tabla. Yo encuentro particularmente fastidioso usar este sistema de nombrado sobre todo en los queries de varias tablas. Sin embargo, es una buena practica hacerlo si usted esta trabajando con varias personas en un proyecto y desea evitar confusiones.
dbmiempresa=# \dt;
Este comando dt (describe tables) muestra las tablas (también llamadas “relaciones”) de la base de datos así como el dueño de la tabla (el usuario postgres). En este caso sólo veremos una tabla, la recién creada “empleados”. Para ver la descripción de una tabla específica utilice “\d tabla”. Por ejemplo:
De esta manera usted puede recuperar los detalles de una tabla, como el tipo de cada campo. Por otra parte el comando “\ds” (describe sequences) muestra las secuencias que existen en la BD.
Note, además que al crear la columna rfc añadimos la indicación UNIQUE que indica que los valores listados en esa columna deben ser únicos, pues dos empleados no pueden tener el mismo rfc. La indicación NOT NULL en la columna sueldo indica que al insertar un registro (o renglón) ese campo no puede quedar vacío y forzosamente debe tener un valor.
Ahora insertemos algunos registros a nuestra tabla, para ello usamos el comando INSERT
INTO, el cual posee el siguiente formato:
INSERT INTO tabla (columna1, columna2) VALUES ('valor uno', 'valor dos');
Corte y pegue las siguientes líneas:
INSERT INTO empleados (id, nombre,rfc, fecnac, sucursal, sueldo) VALUES
(NEXTVAL('empleados_id_seq'), 'Mario Lopez', 'MLG598777','11-06-1977', 'Merida', '9800.00'); INSERT INTO empleados (id, nombre,rfc, fecnac, sucursal, sueldo) VALUES
(NEXTVAL('empleados_id_seq'), 'Jorge Marles', 'MLG1545487','18-07-1969', 'Chiapas', '14500.00');
INSERT INTO empleados (id, nombre,rfc, fecnac, sucursal, sueldo) VALUES
(NEXTVAL('empleados_id_seq'), 'Manuel Montoya', 'ERT7890','11-06-1977', 'Toluca', '15900.00');
INSERT INTO empleados (id, nombre,rfc, fecnac, sucursal, sueldo) VALUES
(NEXTVAL('empleados_id_seq'), 'Luciano Perez', 'JKJ110777','11-06-1977', 'Guadalajara', '1000.00');
Los valores de los campos de tipo VARCHAR, DATE y MONEY deben ir entre comillas simples mientras los NUMERIC e INTEGER no las necesitan. Ahora que ya vio como se hace, agregue algunos empleados usted mismo.
5 - Queries simples
El querie o consulta más simple es "SELECT * FROM tabla". En nuestro caso:
El asterisco indica que deseamos ver todas las columnas de la tabla. Podemos revisar sólo algunas columnas con:
dbmiempresa=# SELECT id, nombre FROM empleados;
Esto sólo nos devolverá el campo id y el nombre del empleado.
Si hemos creado campos sin la indicación NOT NULL podemos insertar un nuevo registro dejándolos vacíos:
dbmiempresa=# INSERT INTO empleados (id, nombre,sueldo) VALUES (NEXTVAL('empleados_id_seq'), 'Julia Sanchez','16700.98'); 6 - Ordenar una consulta
Podemos ordenar el resultado de la consulta según diferentes criterios con el parámetro ORDER BY. Por ejemplo, que ordene nuestra tabla por nombre
dbmiempresa=#SELECT * FROM empleados ORDER BY nombre;
O por el sueldo:
dbmiempresa=# SELECT * FROM empleados ORDER BY sueldo;
Podemos ordenar la tabla como ascendente o descendente, por omisión el orden es ascendente ASC (de menor a mayor):
dbmiempresa=# SELECT * FROM empleados ORDER BY sueldo DESC; dbmiempresa=# SELECT * FROM empleados ORDER BY sueldo ASC;
Asimismo podemos limitar el número de renglones del query con el parámetro LIMIT, por ejemplo si sólo deseamos conocer a los dos empleados de mayor sueldo:
dbmiempresa=# SELECT nombre, sueldo FROM empleados ORDER BY sueldo DESC LIMIT 2;
7 - El condicional WHERE
Si deseamos conocer al empleado cuya sucursal se encuentra en Chiapas debemos usar el parámetro WHERE. Este parámetro indica que se deben listar sólo los registros que cumplen con una condición especificada:
dbmiempresa=# SELECT * FROM empleados WHERE sucursal ='Chiapas';
Si deseamos conocer a los empleados cuyo sueldo es mayor a 13,000 pesos:
dbmiempresa=# SELECT * FROM empleados WHERE sueldo > '13000.00' ORDER BY sueldo;
Si deseamos conocer a los empleados cuya sucursal es Toluca y el sueldo es igual o mayor a 10,000 pesos debemos usar AND para especificar más de una condición:
AND sucursal = 'Toluca';
Se pueden colocar tantos AND como columnas tenga nuestra tabla:
dbmiempresa=# SELECT nombre, fecnac FROM empleados WHERE sueldo >= '10000.00' AND sucursal = 'Toluca' AND id=3 AND
rfc='ERT7890'; 8 - Actualizar la tabla
Para modificar los datos de un registro de la tabla usamos el comando UPDATE:
dbmiempresa=# UPDATE empleados SET sueldo ='7500.00', rfc='YURT6734' WHERE id=4;
Para borrar el renglón de una tabla:
dbmiempresa=# DELETE FROM empleados WHERE id=5;
¿Ahora está claro para que creamos el campo id? Este campo nos sirve para realizar todas las operaciones sobre una tabla.
9 - Modificar una tabla
Agregar una columna a una tabla:
dbmiempresa=# ALTER TABLE empleados ADD COLUMN contrato INTEGER;
Renombrar la columna de una tabla:
dbmiempresa=# ALTER TABLE empleados RENAME COLUMN contrato TO tipocontra;
Eliminar una columna a una tabla:
dbmiempresa=# ALTER TABLE empleados DROP COLUMN tipocontra;
Borrar la tabla:
dbmiempresa=# DROP TABLE empleados;
Apartir de la versión 7.4 de PostgreSQL, al borrar una tabla las secuencias relacionadas con esta también se borran y ya no se quedan "huérfanas".
