Este curso con MariaDB está enfocado al aprendizaje desarrollando la base de datos completa del proyecto Learning Project, por ello en la lección 5 seguimos la implementación, utilizando los conceptos que hemos ido aprendiendo en las 4 primeras clases, para ello implementaremos las tablas cb_paymentmethod (métodos de pago) y cb_enterprise (empresas) en lo que denominas SQL práctico.
Repasaremos las consultas y avanzaremos un poco más explicando varios conceptos nuevos como la utilización de subconsultas, este es el índice de lo que vamos a ver:
- Creación de la tabla de métodos de pago: cb_paymentmethod
- Creación de la tabla de empresas: cb_enterprise
- SQL Consultas avanzadas
1. Creación de la tabla de métodos de pago: cb_paymentmethod
La tabla cb_paymentmethod es la tabla de métodos de pago que vamos a definir para los clientes, también se podría utilizar para las empresas:
CREATE TABLE cb_paymentmethod ( idpaymentmethod INT NOT NULL AUTO_INCREMENT, paymentmethod VARCHAR(100) NOT NULL, description VARCHAR(150), paymentterms VARCHAR(250), paymententity VARCHAR(50), CONSTRAINT pk_cb_paymentmethod PRIMARY KEY (idpaymentmethod), CONSTRAINT un_cb_paymentmethod_paymentmethod UNIQUE (paymentmethod) ) ENGINE=InnoDB COMMENT='Métodos de pago definidos para el cliente u otras entidades'; GRANT ALL ON TABLE cb_paymentmethod TO xulescode;
Este es el signicado de los campos:
- paymentmethod: identificador único del método de pago, es un nombre corto que identifica con su valor el significado del método.
- description: descripción o explicación del método de pago
- paymentterms: términos del método de pago, es decir, descripción de los días o duración del mismo
- paymententitiy: entidad del método de pago.
La tabla de métodos de pago tiene como clave primaria el campo idpaymentmethod en el que utilizamos un entero con AUTO_INCREMENT, para obtener una clave primaria de generación automática incremental:
idpaymentmethod INT NOT NULL AUTO_INCREMENT CONSTRAINT pk_cb_paymentmethod [Nombre para la clave primaria] PRIMARY KEY (idpaymentmethod) [Campo que forma la clave primaria]
Definimos paymentmethod como clave única, esto nos garantiza que este campo será único y no se podrá repetir en la creación de registros, la definición de está clave activa las reglas SQL de MariaDB impidiendo la introducción de valores repetidos:
CONSTRAINT un_cb_paymentmethod_paymentmethod [Nombre para la clave única] UNIQUE (paymentmethod)[Campo que forma la clave primaria]
Recordando el uso de INSERT aquí tenéis algún ejemplo de datos que podéis utilizar para las pruebas:
INSERT INTO customerdb.cb_paymentmethod (paymentmethod,description,paymentterms,paymententity) VALUES
('CONTADO','Contado','2 días','CONT'),
('30 DÍAS','Pago a 30 días','30','30D'),
('60 DÍAS','Pago 60 días','60 días','60D'),
('90 DÍAS','Pago a 90 días','Pago a 90 días','90D'),
('120 DÍAS','Pago a 120 días','Pago a 120 días','120D');
En este ejemplo utilizamos un INSERT múltiple de varios resultados, como genera la clave primaria de forma automática no incluimos información de la columna idpaymentmethod.
2. Creación de la tabla de empresas: cb_enterprises
La tabla cb_enterprise es la tabla de empresas de la base de datos Customerdb, con lo que cada empresa tendrá sus clientes, es el SQL con el que implementamos está tabla:
CREATE TABLE cb_enterprise
(
identerprise INT NOT NULL,
enterprise VARCHAR(150),
description VARCHAR(250),
enterprisealias VARCHAR(100),
contact VARCHAR(250),
estate VARCHAR(30),
balance DECIMAL(10,3),
ei VARCHAR(100),
enterprisepayer VARCHAR(20),
idcountry INT,
idcurrency INT,
idlanguage VARCHAR(6),
CONSTRAINT pk_enterprise PRIMARY KEY (identerprise),
CONSTRAINT cb_enterprise_idlanguage FOREIGN KEY (idlanguage)
REFERENCES cb_language (idlanguage) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_cb_enterprise_idcountry FOREIGN KEY (idcountry)
REFERENCES cb_country (idcountry) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_cb_enterprise_idcurrency FOREIGN KEY (idcurrency)
REFERENCES cb_currency (idcurrency) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
ENGINE=InnoDB
COMMENT='Tabla para controlar las empresas que se usan en la aplicación, la aplicación se desarrolla en función de estos parámetros ya que habrá algunas tablas que serán comunes a las empresa y otras que no, por ejemplo, cada empresa tendrá sus propios clientes, pero tendrá los mismos idiomas, monedas y países de trabajo.';
GRANT ALL ON TABLE cb_enterprise TO xulescode;
Usamos identerprise como clave primaria: CONSTRAINT pk_enterprise PRIMARY KEY (identerprise), en este caso dejamos que el valor de clave primaria sea introducido y no autogenerado como en otras tablas, hacemos esto en este caso para que se entienda el funcionamiento, y que si no usamos valores autogenerados debemos controlar la integridad al introducir valores desde la parte de aplicación por ejemplo si estamos desarrollando una web.
Definimos las foreign keys que nos relacionan los campos del idioma (cb_language), la moneda (cb_currency) y el país (cb_country) con sus correspondientes tablas de la misma forma que hicimos en las otra tablas.
En este caso es un decisión de diseño para este base de datos definir el idioma y la moneda en particular para la empresa ya que como vimos antes los valores los tenemos relacionados por país, en este caso, defino el idioma y la moneda con la que trabaja la empresa que no tiene porque coincidir con la del país.
Además se definen los siguientes campos que explicacamos un poco cual es su significado:
- enterprise : nombre de la empresa
- description : descripción de la empresa, por ejemplo, la actividad de la misma.
- enterprisealias : alias o nombre corto que identifica a la empresa.
- contact: nombre de contacto
- estate: estado de la empresa, por ejemplo: ACTIVA, BAJA, …, este campo en un diseño más avanzado se llevaría a otra tabla, para mantener la integridad de los estados.
- balance : un valor económico de la empresa, simplemente es para utilizar un valor decimal y ver como crearlo.
- ei : identificador fiscal.
- enterprisepayer: pagador, nombre fiscal de la empresa.
Una vez que ya conocemos el significado de los campos veamos la creación de algunos registros:
INSERT INTO customerdb.cb_enterprise (identerprise, enterprise,description,enterprisealias,contact,estate,balance,ei,enterprisepayer,idlanguage,idcurrency,idcountry) VALUES
(100, 'CÓDIGO XULES','Código Xules empressa de consultoría de aplicaciones empresariales',
'Código Xules','Julio Yáñez','ACTIVO',2000.000,'65789434F','Julio Yáñez','es_ES',7,275),
(200, 'Hermanos Sánchez','Empresa de transporte Hermanos Sánchez','HSANS',
'Jose Sánchez','ACTIVO',20000.000,'B89909117','Hermanos Sánchez','es_ES',7,345),
(300, 'Rueda e Hijos','Empresa de aviculutura de Rueda e Hijos','RUEDA',
'Luis Rueda','ACTIVO',12000.000,'B23909117','Rueda SA','es_ES',7,345),
(400, 'Código Limpio','Empresa de software y consultoría Código Limpio','CX',
'Juana de Arco','ACTIVO',15100.000,'B23129000','Juana de Arco','es_ES',7,345);
La estructura que E-R que tenemos ahora mismo generada para la tabla de empresas es la siguiente:
3. Consultas avanzadas y subconsultas
Consultas avanzadas
Repasasmos todo la aprendido, realizando la consulta que relaciona la tabla de empresas con todas las tablas relacionadas de países, idiomas y monedas. Como ya sabemos lo podemos hacer de dos formas, utilizando INNER JOIN o con comas, veamos las dos, el resultado final evidentemente será el mismo:
En la consulta utilizando INNER JOIN recordamos como a partir de la tabla principal en este caso cb_enterprise vamos relacionando cada tabla con la claúsula ON donde establecemos el campo de combinación entre las tablas:
SELECT ce.*, cc.country, cu.currency, cl.countrycode, cl.languageiso
FROM cb_enterprise ce
INNER JOIN cb_country cc ON cc.idcountry = ce.idcountry
INNER JOIN cb_currency cu ON cu.idcurrency = ce.idcurrency
INNER JOIN cb_language cl ON cl.idlanguage = ce.idlanguage
En los ejemplos que estamos viendo solo tenemos un campo de clave foránea para relacionar las tablas, pero esta relación podría ser múltiple, con lo que tendríamos que incluir los campos en la claúsula ON para establecer la relación.
Veamos la misma consulta ahora sin utilizar INNER JOIN:
SELECT ce.*, cc.country, cu.currency, cl.countrycode, cl.languageiso
FROM cb_enterprise ce
INNER JOIN cb_country cc ON cc.idcountry = ce.idcountry
INNER JOIN cb_currency cu ON cu.idcurrency = ce.idcurrency
INNER JOIN cb_language cl ON cl.idlanguage = ce.idlanguage
Este es el resultado:
Subconsultas
Las subconsultas nos sirven para extraer valores de una tabla relacionada de forma seleccionada, la principal limitación en Mariabd es que solo puedes devolver un registro.
Consite en integrar un consulta con SELECT como un campo más dentro de una consulta, de ahí el nombre de subconsulta, en el ejemplo vemos como obtenemos con una subconsulta el valor del nombre del idioma namelanguage.
SELECT ce.identerprise, ce.enterprise, ce.contact, ce.idcountry , ce.idcurrency, idlanguage ,
(SELECT cl.namelanguage FROM cb_language cl WHERE cl.idlanguage = ce.idlanguage) AS languagename
FROM cb_enterprise ce;
Las posibilidades con las subconsultas son bastantes grandes ya que por ejemplo podemos incluir dentro de la misma un cálculo sobre una tabla relacionada directamente, por ejemplo, un caso en que tengas 2 tablas una de pedidos y otra con las líneas de pedido, las subconsultas nos permiten obtener el número total de unidades directamente.
