Diferencias SQL en MySQL, SQL Server, Oracle, PostgreSQL y SQLite

Veremos un diagrama EER de dos tablas y como se realizan las declaraciones de las mismas en los distintos gestores de bases de datos:

• MySql y MariaBD
• Oracle
• SQL Server
• PostgreSQL
• SQLite

MySql y MariaBD

CREATE TABLE IF NOT EXISTS inquilinos (
  id INT NOT NULL AUTO_INCREMENT,
  dni VARCHAR(8) NOT NULL,
  nombres VARCHAR(150) NOT NULL,
  paterno VARCHAR(150) NOT NULL,
  materno VARCHAR(150) NOT NULL,
  telefono VARCHAR(40) NULL,
  correo VARCHAR(200) NULL,
  deuda DECIMAL(10,2) NOT NULL,
  fecha_ingreso DATE NOT NULL,
  PRIMARY KEY (idinquilinos),
  UNIQUE INDEX dni_UNIQUE (dni ASC),
  UNIQUE INDEX correo_UNIQUE (correo ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
INSERT INTO inquilinos(dni, nombres, paterno, materno, 
telefono, fecha_ingreso, correo, deuda) VALUES 
('31378082','LUISA', 'PAUCAR','NARRO','999888777',
'2018-01-28','lpaucar@mail.com',0.00),
('43331042','AUGUSTO','SOTOMAYOR','NARVAJO','900800700',
'2019-10-08','asoto@mail.com',0.00);
SELECT * FROM inquilinos

Oracle

SQL Server

CREATE TABLE inquilinos (
  id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
  dni VARCHAR(8) UNIQUE NOT NULL,
  nombres VARCHAR(150) NOT NULL,
  paterno VARCHAR(150) NOT NULL,
  materno VARCHAR(150) NOT NULL,
  telefono VARCHAR(40) NULL,
  correo VARCHAR(200) UNIQUE NOT NULL,
  deuda MONEY NOT NULL,
  fecha_ingreso DATE NOT NULL DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO [inquilinos] ([dni],[nombres],[paterno],
   [materno],[telefono],[correo],[deuda],[fecha_ingreso]
   ) VALUES
	 ('31378082','LUISA', 'PAUCAR','NARRO','999888777',
        'lpaucar@mail.com',0.00,'2018-01-28'),
	 ('43331042','AUGUSTO','SOTOMAYOR','NARVAJO','900800700',
        'asoto@mail.com',0.00,'2019-10-08');
SELECT * FROM inquilinos

PostgreSQL

CREATE TABLE public."Usuarios"
(
    id serial NOT NULL,
    dni character(8) NOT NULL,
    nombres character varying(100) NOT NULL,
    apellidos character varying(150) NOT NULL,
    fecha_nacimiento date NOT NULL,
    PRIMARY KEY (id)
)
WITH (
    OIDS = FALSE
);
ALTER TABLE public."Usuarios"
    OWNER to postgres;
INSERT INTO Usuarios(
 dni, nombres, apellidos, fecha_nacimiento)
 VALUES ('71700011', 'Alan Damian', 'Toledo Higuchi', '1990-10-01');

SQLite

Creando una vista en MySQL

Diagrama EER

En este ejemplo vamos a implementar el código SQL para MySQL de la siguientes tablas que tenemos según el diagrama EER:

• Si quieres el código SQL de la tabla "inquilinos" has clic aquí
• Si quieres el código SQL de la tabla "pagos" has clic aquí

Lo que deseamos es crear una vista, esta vista actúa como una tabla que nosotros podemos personalizar a partir de consultas SELECT, son muy útiles para representar información de dos o más tablas que se relacionan en una sola. Ojo una vista no es una tabla propiamente dicha y solo se le puede aplicar SELECT, es decir solo ver sus datos, no se puede agregar, actualizar o eliminar registros de una vista, estos son registros de sus respectivas tablas y ahí se deben ejecutar dichas sentencias

Código SQL

Primero debemos tener la consulta que deseamos convertir en una vista, en este caso relacionar un pago con un inquilino, de forma que yo pueda ver el código del pago, el DNI del inquilino, sus nombres y apellidos en un solo campo, el pago que realizo, la fecha y hora (por separado) que lo hizo. El código resultante será el siguiente:

SELECT 
  pagos.idpagos,
  inquilinos.dni,
  CONCAT_WS(" ", inquilinos.nombres, inquilinos.paterno, inquilinos.materno),
  pagos.monto
  DATE(pagos.fecha),
  TIME(pagos.fecha)
FROM pagos INNER JOIN inquilinos 
  ON pagos.inquilino = inquilinos.idinquilinos

• La función "CONCAT_WS" nos permite concatenar dos o más campos, indicando como primer parámetro el caracter de separación, en este caso el espacio en blanco (" ").
• Podemos extraer la fecha de un DATETIME con la función YEAR.
• Podemos extraer la hora de un DATETIME con la función TIME.

Ahora debemos asignar un "alias" a cada campo para tener un mejor orden:

SELECT 
  pagos.idpagos id,
  inquilinos.dni dni,
  CONCAT_WS(" ", inquilinos.nombres, inquilinos.paterno, inquilinos.materno) datos,
  pagos.monto monto
  DATE(pagos.fecha) fecha,
  TIME(pagos.fecha) hora
FROM pagos INNER JOIN inquilinos 
  ON pagos.inquilino = inquilinos.idinquilinos

Ahora podemos proceder a crear la vista anteponiendo lo siguiente: "CREATE VIEW ________ AS" de la siguiente manera

CREATE VIEW pagos_view AS
SELECT 
  pagos.idpagos id,
  inquilinos.dni dni,
  CONCAT_WS(" ", inquilinos.nombres, inquilinos.paterno, inquilinos.materno) datos,
  pagos.monto monto,
  DATE(pagos.fecha) fecha,
  TIME(pagos.fecha) hora
FROM pagos INNER JOIN inquilinos 
  ON pagos.inquilino = inquilinos.idinquilinos

• El nombre de la vista es "pagos_view".
• Podemos aplicarle consultas SELECT donde el nombre de los campos son los "alias" que le elegimos.

Probando

SELECT * FROM pagos_view;

Nos debe devolver algo así:

id	dni	datos	monto	fecha	hora
1	31378082	LUISA PAUCAR NARRO	400.00	2019-10-21	21:34:35
2	43331042	AUGUSTO SOTOMAYOR NARVAJO	300.00	2019-10-18	18:30:00

Creando una tabla con clave foránea en MySQL

Diagrama EER

En este ejemplo vamos a implementar el código SQL para MySQL de la siguientes tablas que tenemos según el diagrama EER. Si quieres el código SQL de la tabla "inquilinos" has clic aquí

Código SQL

El código resultante será el siguiente:

CREATE TABLE IF NOT EXISTS pagos (
   idpagos INT NOT NULL AUTO_INCREMENT,
   inquilino INT NOT NULL,
   monto DECIMAL(10,2) NOT NULL,
   fecha DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (idpagos),
   CONSTRAINT fk_pago_inquilino
     FOREIGN KEY (inquilino)
     REFERENCES inquilinos (idinquilinos)
       ON DELETE NO ACTION
       ON UPDATE NO ACTION
)ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

• El campo "inquilino" sera nuestra clave foránea, lo que significa que estará vinculado a la tabla "inquilinos", de tal manera que para agregar un pago, primero debemos tener por lo menos un inquilino, ya que el campo es "NOT NULL".
• La clave "fk_pago_inquilino" es el nombre de la clave foránea debe ser única, lo que implica que no se podrá repetir este nombre en ningún campo o nombre de relación de esta base de datos.
• Se usa las siguientes sentencias:
• ON DELETE NO ACTION: Implica que si se desea eliminar un registro de "inquilinos" que tenga por lo menos un pago, saltará un error y no dejará borrarlo.
• ON UPDATE NO ACTION: Implica que si se desea actualizar la clave primaria de un registro de "inquilinos" que tenga por lo menos un pago, saltará un error y no dejará cambiar la clave, pero ¿Por qué querríamos hacer eso?
• ENGINE = InnoDB, nos garantiza que podremos utilizar claves foráneas y soporte del "commit" y "rollback"
• DEFAULT CHARACTER SET = utf8; nos permite ingresar caracteres especiales como la "ñ" o vocales tildadas a nuestros registros.

Insertando datos de prueba

INSERT INTO pagos(inquilino, monto, fecha) 
   VALUES (1,400,CURRENT_TIMESTAMP),
          (2,300,"2019-10-18 18:30:00");

• En el caso del campo "fecha", se emplea en el primer caso "CURRENT_TIMESTAMP" que devuelve la fecha y hora actual para el servidor MySQL, en el segundo caso se especifica la fecha y hora en el formato "YYYY-MM-DD HH:MM:SS".

Creando una tabla en MySQL

Diagrama EER

En este ejemplo vamos a implementar el código SQL para MySQL de la siguiente tabla que tenemos según el diagrama EER

Código SQL

El código resultante será el siguiente:

CREATE TABLE IF NOT EXISTS inquilinos (
  idinquilinos INT NOT NULL AUTO_INCREMENT,
  dni VARCHAR(8) NOT NULL,
  nombres VARCHAR(150) NOT NULL,
  paterno VARCHAR(150) NOT NULL,
  materno VARCHAR(150) NOT NULL,
  telefono VARCHAR(40) NULL,
  correo VARCHAR(200) NULL,
  deuda DECIMAL(10,2) NOT NULL,
  fecha_ingreso DATE NOT NULL,
  PRIMARY KEY (idinquilinos),
  UNIQUE INDEX dni_UNIQUE (dni ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

• El campo "idinquilinos" sera "AUTO_INCREMENT", lo que significa que iniciará en 1 y cada registro que se inserte generará automáticamente un nuevo valor sin que necesitemos especificarlo. Al ser un dato numérico simple y no un código se obtiene mayor velocidad en consultas a la base de datos.
• El campo "idinquilinos" sera "PRIMARY KEY", lo que implica que será la clave primaria es decir cada registro tendrá un único identificador que es este campo y nos servirá para realizar búsquedas o emplearlo como referencias para claves foráneas a otras tablas.
• El campo "dni" es de tipo "UNIQUE", lo que implica que no se podrá repetir este valor en ningún registro de esta tabla.
• ENGINE = InnoDB, nos garantiza que podremos utilizar claves foráneas y soporte del "commit" y "rollback"
• DEFAULT CHARACTER SET = utf8; nos permite ingresar caracteres especiales como la "ñ" o vocales tildadas a nuestros registros.

Insertando datos de prueba

INSERT INTO inquilinos(dni, nombres, paterno, materno, 
telefono, fecha_ingreso, correo, deuda) VALUES 
('31378082','LUISA', 'PAUCAR','NARRO','999888777',
'2018-01-28','lpaucar@mail.com',0.00),
('43331042','AUGUSTO','SOTOMAYOR','NARVAJO','900800700',
'2019-10-08','asoto@mail.com',0.00);