SQL RDBMS Y MODELO RELACIONAL – PARTE 1

Definición de SQL
SQL (StructuredQueryLanguage) – lenguaje de consulta estructurado, que

sirve para almacenar, manipular y recuperar datos almacenados en bases de

datos relacionales. Puede utilizarse en los principales RDBMS

(RelationalDatabase Management System) actuales como Oracle o SQL

Server.

Breve explicación historia SQL

Antes de su comienzo, en 1970 Edgar FrankCodd desarrollo el modelo

relacional en la que consistía en una tabla bidimensional constituida por líneas

(tuplas) y columnas (atributos).

Y es en 1974 con Donald D. Chamberlin como uno de los responsables de

IBM, quien desarrolló un lenguaje con las características de las bases de datos

adoptando el modelo relacional. En un principio se llamaba SEQUEL

(Structured English QueryLanguage) y se implementó en un prototipo

llamado SEQUEL-XRM entre 1974 y 1975. Posteriormente en 1976 se cambió

el nombre por motivos legales a SQL.

En 1979 aparece ORACLE como primer gestor comercial basado en SQL y

van surgiendo otros productos como DB2, SQL/DS, INTERBASE, etc.

En 1981 IBM comienza a entregar sus productos relacionales.

SQL por fin es estandarizado por ANSI en 1986, publicando la primera

versión SQL-86 o SQL 1 y en 1987 se transforma el estándar ISO para

después dar lugar a SQL-89 en la que destacaron como mejoras las definición

de claves primarias y la integridad de datos. Posteriormente apareció SQL-

92 o SQL 2.

SQL 1999 o SQL 3 se agregan expresiones regulares, consultas recursivas y

algunas características orientadas a objetos.

SQL 2003 introduciendo características de XML, cambios en las funciones,

estandarización del objeto sequence y de las columnas auto numéricas.

SQL 2006 define las maneras en las cuales el SQL se puede utilizar

conjuntamente con XML.

SQL 2008 permite el uso de ORDER BY fuera de las definiciones de los

cursores. Incluye los disparadores del tipo INSTEAD OF y añade la

sentencia TRUNCATE.

SQL 2011 mejora para bases de datos temporales, tablas de períodos de

tiempo utilizando la anotación PERIOD FOR. Se añade también la

cláusula FETCH.

SQL 2016 permite funciones polimórficas (sin tipo de retorno predefinido),

funcionalidad JSON (crear documentos JSON y comprobar si una cadena

contiene datos JSON válidos), búsqueda de patrones y LISTAGG para

transformar valores de un grupo de filas en una cadena delimitada.

RDBMS (RelationalDatabase Management System)

Es un programa que nos permite gestionar bases de datos relacionales. Para

entender mejor este tipo de programa, con anterioridad a los RDBMS estaban

los DBMS (Relational Database Management System) para gestionar base de

datos. Se compone principalmente de:

 Interfaz. Una interfaz que permite al usuario comunicarse con los

DBMS
 Database language engine. Motor de base de datos para
interpretar las consultas y realizar las acciones necesarias usando
el lenguaje de consulta
 Query optimizer. Para optimizar el lenguaje de consulta, con el
objetivo de poder ejecutar las consultas lo más rápido posible
 Database engine. Para gestionar objetos de la base de datos
como pueden ser las tablas o vistas
DBMS Management Component. Son componentes de gestión
 para realizar copias de seguridad, monitorización de rendimiento,
seguridad, etc.

Los RDBMS son una mejora de los DBMS al introducir el modelo relacional,
permitiendo la relación entre tablas usando claves primarias, claves foráneas e
índices.
Como principales diferencias entre DBMS y RDBMS.
Los DBMS están orientados para aplicaciones que gestiones pocos datos,
pues puede aumentar la complejidad y lentitud a mayor volumen de datos,
debido entre cosas a que es muy común los datos redundantes. Como
sistemas DBMS tenemos LibreOffice Base y FoxPro.
Los RDBMS están orientados a gestionar un mayor volumen de datos gracias
al modelo relacional, permitiendo realizar consultas más rápidas y también se
evita los datos redundantes mediante el uso de claves de índices. Como
sistemas tenemos SQL Server, Oracle, MySQL, MariaDB, etc.
Modelo relacional
Se define por la estructura de tabla, teniendo un nombre y los atributos o
campos que la componen. Cuando se agregan elementos a la tabla se habla
de “instancia de relación».
Ejemplo, entidad Consola cuyos atributos describen a la consola (nombre, tipo,
marca) y cada tupla representa a una consola.

Para del modelo relacional, las tablas necesitan de una clave primaria (PK) que
identifique unívocamente una tupla, es decir, no puede repetirse y tampoco
puede tener valor nulo. Una clave primaria puede esta combinada por más de
un campo, o también muchas veces creamos un campo ficticio como clave
primeria, por ejemplo, Nº Persona como identificador. Puede llegar a ver varias
combinaciones para obtener la clave primaria, pero sólo una de ellas será la
escogida, las demás serán claves candidatas.
Otra característica es que los atributos deben ser “atómicos» es decir, los
valores de una columna no se pueden separar en varios valores, no se puede
tener un multivalor.
Por ejemplo: CONSOLA

Aquí vemos el campo JUEGOS no es atómico, por lo que debemos separar en

dos tablas.
CONSOLA

JUEGO

Como resultado tenemos ID_CONSOLA como clave primaria en la tabla

CONSOLA; ID_JUEGO como clave primaria de JUEGO y
también ID_CONSOLA siendo clave foránea. Una clave foránea es un atributo
o una combinación de atributos de una relación que son, a su vez, una clave
primaria para otra relación. Una característica de la clave foránea es que debe
corresponder a valores existentes en la clave primaria. A esto se le llama
integridad referencial.
Queda solucionado el problema de los atributos atómicos pero vemos 2 duplas
casi idénticas. Se puede optimizar aún más el resultado, al considerar que una
consola puede tener varios juegos y un juego puede estar publicado en más de
una consola, esto es una relación muchos a muchos, donde se puede crear
una tabla para cada identidad y otra para la relación entre las claves de ambas
tablas.
Puede darse otro tipo de situaciones, relación 1 a 1.
Un equipo sólo puede tener un entrenador y un entrenador pertenecer a un
sólo equipo.
La última tupla no puede cumplirse porque sólo puede haber un entrenador por
equipo. Por lo tanto, en este caso si es óptimo crear solo dos tablas, teniendo
la segunda una clave foránea que hace referencia a la clave primaria de la
tercera.

Relación 1 a muchos / muchos a 1

Un técnico solo da soporte de una aplicación y una aplicación puede ser
gestionada por varios técnicos.
La última tupla no es válida porque un técnico no puede dar soporte a más de
una aplicación

Relación 1 a 1 con restricción NOT NULL en ambas direcciones

Cuando vemos doble línea, nos está indicando que como mínimo de debe
tener un valor.
En este caso un equipo debe tener al menos un entrenador y un entrenador
debe pertenecer a un equipo.

La diferencia con el otro ejemplo de la relación 1 a 1, es que podemos tener

todo en la misma tabla pues no tendremos valores nulos y un entrenador no
puede estar en más de equipo. Al mismo tiempo ID_ENTRENADOR sería clave
candidata
Cuando definimos que una PK no puede ser repetida y tampoco puede tener
valores nulos, que puede llegar a ser una combinación de más de una atributo
o al crear una tabla puede tener un atributo que se le asigne un valor por
defecto, estas restricciones son conocidas como Restricciones Semánticas:
1. Primary Key
2. Unique
3. Not Null
4. Foreign Key
5. Default, se registra un atributos con un valor asignado
6. Check, verificación de datos debe cumplirse una condición
7. Assertion, la condición se establece sobre elementos distintas
relaciones
8. Triggers, si se cumple una condición se realizará una
determinada acción

Sin embargo, puede presentarse una restricción que no viene definida en el

Modelo Relacional y estas son conocidas como Restricción Semántica
Adicional. En el último caso tenemos una RSA pues estamos forzando que
toda ocurrencia de Equipo debe estar relacionado con una ocurrencia
de Entrenador y viceversa, es decir, sin Equipo no hay Entrador y
sin Entrenador no hay Equipo.
Y podrían darse más casos
Un equipo debe tener un entrenador y un entrenador no pertenecer a ningún
equipo.

Una consola puede tener varios juegos y un juego debe estar en varias
consolas.
Las relaciones entre tablas son muy importantes, dependiendo de lo que
necesitemos para el desarrollo de nuestra Base de Datos ya que podría haber
relación de más de 2 tablas.

Dentro del algebra relacional hay 5 operadores:

RESTRICT, PROJECT, TIMES, UNION y MINUS
Mas otros 3 operadores adicionales:
JOIN, INTERSECT y DIVIDE
Estos operadores reciben como argumento una relación o conjunto de
relaciones y se obtiene una única relación como resultado.
Como RDBMS elegido nos centraremos en SQL SERVER 2012.
Al comienzo vemos que nos pide elegir el tipo y nombre de servidor junto con la
autenticación.
Tipo de servidor tenemos a elegir: Database Engine, Analysis
Services, Reporting Services e Integration Services. Elegimos Database
Engine para poder gestionar la base de datos.

Nombre del servidor dejamos el que viene por defecto, coincide con el
nombre del equipo servidor

Para la autenticación tenemos Windows Authentication y SQL Server

Authentication. SQL Server tiene su propio sistema de seguridad para los
inicios de sesión, ésta puede ser independiente a los usuarios y grupos de
Windows. Se puede usar la seguridad de SQL y la gestión dentro de SQL
Server sin tener conexión entre los inicios se sesión y los usuarios de Windows
eligiendo SQL Server Authentication. Si se quiere conexión con los usuarios de
Windows entonces elegimos Windows Authentication, además de poder
realizar un seguimiento de lo que hace un usuario en particular, por contra, si
se elimina un usuario de Windows los datos SQL relacionados con ese usuario
no se actualizan
Para empezar elegimos Windows Authentication.

Al conectar nos aparecerá el Object Explorer (explorador de objetos) a la

izquierda

Creamos la base de datos

Y se nos muestra el panel para las consultas SQL.

Utilizaremos como ejemplo las tablas de juego y consolas con la relación

muchos a muchos. CREATE TABLE e INSERT INTO serán explicados más
tarde, dejo las sentencias utilizadas.
CREATE TABLE consola (id_consola bigint IDENTITY(1,1),
nombre varchar (50),
tipo varchar (15) DEFAULT ‘Sobremesa’,
marca varchar (50),
PRIMARY KEY (id_consola)
)
CREATE TABLE juego(id_juego bigint IDENTITY(1,1),
titulo varchar(50),
genero varchar(50),
PRIMARY KEY (id_juego)
)

CREATE TABLE consola_juego(id_consola bigint,

id_juego bigint,
PRIMARY KEY (id_consola, id_juego),
FOREIGN KEY (id_consola) REFERENCES consola(id_consola) ON UPDATE
CASCADE ON DELETE CASCADE,
FOREIGN KEY (id_juego) REFERENCES juego(id_juego) ON UPDATE
CASCADE ON DELETE CASCADE
)

INSERT INTO consola(nombre, marca) VALUES(‘PS4′,’Sony’);

INSERT INTO consola(nombre, marca) VALUES(‘XBOX ONE’,’Microsoft’);
INSERT INTO consola(nombre, tipo, marca)
VALUES(‘3DS’,’Portatil’,’Nintendo’);
INSERT INTO juego(titulo, genero) VALUES(‘Dark Souls III’, ‘ROL’);
INSERT INTO juego (titulo, genero) VALUES(‘Project Cars’, ‘Conduccion’);
INSERT INTO juego(titulo, genero) VALUES(‘Metal Gear Solid V’, ‘Accion’);
INSERT INTO juego (titulo, genero) VALUES(‘Killer Instinct’, ‘Lucha’);
INSERT INTO juego(titulo, genero) VALUES(‘Metroid Prime’, ‘Accion’);
INSERT INTO juego(titulo, genero) VALUES(‘Dragon Quest VII’, ‘ROL’);

INSERT INTO consola_juego (id_consola, id_juego, stock) VALUES(1,1, 45);

INSERT INTO consola_juego (id_consola, id_juego, stock) VALUES(1,2, 30);
INSERT INTO consola_juego (id_consola, id_juego, stock) VALUES(1,3, 15);
INSERT INTO consola_juego (id_consola, id_juego, stock) VALUES(2,3, 15);
INSERT INTO consola_juego (id_consola, id_juego, stock) VALUES(2,4, 19);
INSERT INTO consola_juego (id_consola, id_juego, stock) VALUES(3,5, 34);
INSERT INTO consola_juego (id_consola, id_juego, stock) VALUES(3,6, 23);
RESTRICT – Devuelve una relación producida por la restricción de filas al
utilizar la cláusula WHERE
SELECT * FROM consola WHERE tipo=’portatil’
En este caso solo devolverá como resultado aquellas consolas que sean de
tipo portátil, esta condición viene siendo la restricción de la consulta
PROJECT – Se especifica la lista de columnas que quieren ser proyectadas en
el comando SELECT
SELECT nombre FROM consola
Proyectamos sobre la columna nombre
TIMES o también PRODUCT – Producto cartesiano obtenido por dos tablas.
Cada fila de la primera tabla se concatena con cada fila de la segunda tabla
SELECT * FROM consola, juego
UNION – Se obtiene como resultado la combinación de 2 tablas con columnas
compatibles. Se consideran compatibles si son del mismo tipo
SELECT nombre FROM consola UNION SELECT titulo FROM juego

MINUS o también DIFFERENCE – Se obtiene como resultado aquellas filas de

la primera tabla cuya columna no coincida ninguna de la segunda tabla
Mostrar nombre de consolas que no coincidan con el título de un juego
SELECT nombre FROM consola MINUS SELECT titulo FROM juego (En
ORACLE)
SELECT nombre FROM consola EXCEPT SELECT titulo FROM juego (En SQL
SERVER)
Nos mostrará todos los nombres de consola porque ninguna coincide con el
título de un juego. Si la tabla Juego modificamos el titulo de un juego, por
ejemplo Dragon Quest VII por PS4…

Obtendríamos

PS4 al coincidir en ambas tablas se excluiría. Hacemos el cambio de valor para

que se entienda este ejemplo:

Mostrar solo los juegos que no están consola portátil

SELECT TITULO
FROM JUEGO
WHERE ID_JUEGO IN(SELECT ID_JUEGO
FROM CONSOLA_JUEGO
WHERE ID_CONSOLA NOT IN (SELECT ID_CONSOLA
FROM CONSOLA
WHERE TIPO=’PORTATIL’))
JOIN – Se obtiene la reunión de dos tablas relacionadas entre si.
SELECT column(s) FROM table1, table2 WHERE table1.col1 = table2.col2;
CONSOLA_JUEGO (agregamos campo stock a la tabla consola_juego)

Stock total de aquellos juegos que son de XBOX ONE

SELECT SUM(stock)
FROM consola_juego, consola
WHERE consola_juego.id_consola=consola.id_consola AND nombre=’XBOX
ONE’

Problema del producto cartesiano

SELECT juego.titulo, SUM(consola_juego.stock)
FROM juego, consola_juego
WHERE juego.id_juego IN(
SELECT id_juego
FROM consola_juego
GROUP BY id_juego
HAVING SUM(consola_juego.stock) > 20)
ORDER BY SUM(consola_juego.stock) DESC;
Como resultado solo mostrará un registro con la suma total de todos los stock
producida por el producto cartesiano.

SELECT juego.titulo, SUM(consola_juego.stock)

FROM juego, consola_juego
WHERE juego.id_juego IN(
SELECT id_juego
FROM consola_juego
GROUP BY id_juego
HAVING SUM(consola_juego.stock) > 20)
Como resultado mostrará todos los registros del producto cartesiano, sin
obtenerse si quiera el stock total de cada juego.
Solución con JOIN
SELECT juego.titulo, SUM(consola_juego.stock)
FROM juego
INNER JOIN consola_juego
ON juego.id_juego=consola_juego.id_juego
WHERE juego.id_juego IN(
SELECT consola_juego.id_juego
FROM consola_juego
GROUP BY id_juego
HAVING SUM(consola_juego.stock) > 20)
GROUP BY juego.titulo
ORDER BY SUM(consola_juego.stock) DESC;
INTERSECT – Similar a UNION pero muestra como resultado aquellas que
aparecen tanto en la primera tabla como en la segunda tabla
SELECT nombre FROM consola INTERSECT SELECT titulo FROM juego
Si dejamos los mismos valores en la tabla Juego como teníamos en el
ejemplo MINUS, obtenemos justo lo contrario.

DIVIDE – Devuelve como resultado todas las tuplas de la primera tabla que se
correspondan con la segunda tabla.
Consulta: Mostrar todos los juegos si hay consola PS4
SELECT DISTINCT titulo
FROM juego
WHERE EXISTS(
SELECT id_juego
FROM consola_juego
WHERE EXISTS (SELECT id_consola
FROM consola
WHERE consola.nombre=»PS4″)
)
Consulta: Mostrar todos los juegos de PS4
SELECT DISTINCT titulo
FROM juego
WHERE id_juego IN (SELECT id_juego
FROM consola_juego
WHERE consola_juego.id_consola IN (SELECT id_consola
FROM consola
WHERE consola.nombre=»PS4″)
)

SQL COMANDOS DDL Y DML – PARTE 2

Comandos de SQL
Los comandos de SQL son sentencias que se pueden utilizar para diferentes

tareas y están divididos en cuatro grupos: DDL, DML, DCL y TCL.

Nos centraremos en los comandos DDL y DML.

Comandos DDL (Data Definition Language – Lenguaje Definición de

Datos) para creación, modificación y eliminación de la estructura y objetos de

la base de datos.

CREATE – Utilizado para crear objetos (tablas, vistas, índices) en la base de

datos

CREATE DATABASE coleccion;

Al crear una tabla de base de datos debemos considerar

CREATE TABLE nombre_tabla (

nombre_columna tipo_columna [ cláusula_defecto ] [ vínculos_de_columna ]

[ , nombre_columna tipo_columna [ cláusula_defecto ] [ vínculos_de_columna ]

…]

[ , [ vínculo_de tabla] … ] )

Anuncios

INFORMA SOBRE ESTE ANUNCIO

Nombre del campo deben empezar por un carácter alfabético y ser lo más

comprensible posible para entender que datos se almacenan en ese campo.

Tipo del campo (lista de los más importante):

 Numérico: bigint, int, smallint, tinyint, numeric, bit, decimal, money,

smallmoney.

 Cadena de caracteres: char, varchar, text.

 Fecha y hora: date, datetime, time, timestamp.

 Cadenas binarias: binary, varbinary, image.

Cláusula defecto se le asigna valor por defecto al campo si no se le indica en

el momento que se inserta una fila. Puede iniciarse con un valor o nulo.

DEFAULT { valor | NULL }

Vínculos de integridad que se aplica a cada campo

 NOT NULL no permite valor NULL

 PRIMARY KEY para indicar que es la clave primaria de la tabla. Puede

formarse por más de un campo, directamente debe almacenar valor único y

que no sea NULL

 FOREIGN KEY indica la clave foránea haciendo referencia a otra tabla,

estableciendo la relación. Tiene las cláusulas ON DELETE y ON UPDATE

indican que acción debe ejecutarse en el caso que la clave foránea (a

quién hace referencia) es eliminada o borrada. Las acciones pueden ser:

 o CASCADE: elimina o modifica la tupa que tiene el campo referenciado

o SET DEFAULT: asigna valor por defecto a la columna referenciada

o SET NULL: asigna valor NULL a la columna referenciada

 Control de valor permite asignar o no un valor a la columna dependiendo

del resultado de la condición

CHECK { expresión_condicional }

Vínculos de integridad que se pueden aplicar a más campos de la tabla

 Clave primaria PRIMARY KEY ( columna1 [ , columna2 … ] )

 Clave foránea FOREIGN KEY ( columna1 [ , columna2 … ] )

CREATE TABLE consola (id_consola bigint IDENTITY(1,1),

nombre varchar (50),

tipo varchar (15) DEFAULT ‘Sobremesa’,

marca varchar (50),

PRIMARY KEY (id_consola)

CREATE TABLE juego(id_juego bigint IDENTITY(1,1),

titulo varchar(50),

genero varchar(50),

PRIMARY KEY (id_juego)

CREATE TABLE consola_juego(id_consola bigint,

id_juego bigint,

PRIMARY KEY (id_consola, id_juego),

FOREIGN KEY (id_consola) REFERENCES consola(id_consola) ON UPDATE

CASCADE ON DELETE CASCADE,

FOREIGN KEY (id_juego) REFERENCES juego(id_juego) ON UPDATE

CASCADE ON DELETE CASCADE

También podemos crear índices, se usan para encontrar un dato más

rápidamente mejorando la eficacia de la consulta

CREATE INDEX index_marca

ON consola (marca)

CREATE UNIQUE INDEX index_nombre_marca

ON consola (nombre, marca)

ALTER – Utilizado para modificar las tablas agregando o cambiando la

definición de los campos

ALTER TABLE consola ALTER COLUMN marca char (SQL Server)

ALTER TABLE consola MODIFY COLUMN marca char (MySQL)

ALTER TABLE consola ADD fecha_lanzamiento date

ALTER TABLE consola DROP COLUMN fecha_lanzamiento

¿Qué pasaría si creamos el campo fecha con valores por defecto y después

intentamos eliminar el propio campo?

ALTER TABLE consola ADD fecha_lanzamiento DATE NOT NULL DEFAULT

‘2017-01-01’

ALTER TABLE consola DROP COLUMN fecha_lanzamiento

Anuncios
INFORMA SOBRE ESTE ANUNCIO
Msg 5074, Level 16, State 1, Line 1

The object ‘DF__consola__fecha_l__276EDEB3’ is dependent on column

‘fecha_lanzamiento’.

Msg 4922, Level 16, State 9, Line 1

ALTER TABLE DROP COLUMN fecha_lanzamiento failed because one or

more objects access this column.

DECLARE @ConstraintName nvarchar(MAX)

SELECT @ConstraintName = Name FROM SYS.DEFAULT_CONSTRAINTS

WHERE PARENT_OBJECT_ID = OBJECT_ID(‘consola’)

AND PARENT_COLUMN_ID = (SELECT column_id FROM sys.columns

WHERE NAME = N’fecha_lanzamiento’

AND object_id = OBJECT_ID(N’consola’))

IF @ConstraintName IS NOT NULL

EXEC(‘ALTER TABLE consola DROP CONSTRAINT ‘ + @ConstraintName)

ALTER TABLE consola DROP COLUMN fecha_lanzamiento

DROP – Utilizado para eliminar objetos en la base de datos

Podemos eliminar un índice

DROP INDEX consola.index_nombre_marca (SQL Server)

ALTER TABLE consola DROP INDEX consola.index_nombre_marca (MySql)

O la tabla entera

DROP TABLE consola

TRUNCATE – Borra todo el contenido de una tabla

TRUNCATE TABLE consola

Msg 4712, Level 16, State 1, Line 1

Cannot truncate table ‘consola’ because it is being referenced by a FOREIGN

KEY constraint.

En este caso no nos lo permitirá al haber una clave foránea que hace

referencia a la tabla consola

COMMENT – Agregar comentarios al diccionario de datos sobre un objeto de la

base de datos

COMMENT ON COLUMN consola.marca

IS ‘desarrolladora del producto’; (ORACLE)

COMMENT ON COLUMN consola.marca (ORACLE)

IS ‘ ‘;

RENAME – Renombrar la tabla de la base de datos

ALTER TABLE consola RENAME TO consola1 (ORACLE y MySQL)

sp_rename ‘consola’,’consola1′; (SQL SERVER)

Caution: Changing any part of an object name could break scripts and stored

procedures.
Si modificamos el nombre, los script podrían dejar de funcionar. Hay que

tener en cuenta también las relaciones que hay en la tabla, en Oracle por

ejemplo si una tabla tiene una clave foránea, generará error.

Comandos DML (Data Manipulation Language – Lenguaje Manipulación de

Datos) para recuperar y trabajar con datos.

SELECT – Utilizado para consultar registros de la base de datos que satisfagan

un criterio determinado

SELECT [ ALL | DISTINCT ] lista_elementos_selección

FROM lista_referencias_tabla

[ WHERE expresión_condicional ]

[ GROUP BY lista_columnas ]

[ HAVING expresión_condicional ]

[ ORDER BY lista_columnas ]

Se puede buscar todos los valores de un campo o varios campos de una tabla,

o con DISTINCT eliminar aquellas filas cuyo campo se repite. Si no se

indica ALL | DISTINCT por defecto se realizará ALL. Nunca se debe

usar DISTINCT en la clave primaria, ni en las candidatas ya que de por sí son

únicas

WHERE se utiliza para generar una condición que debe cumplirse, aquellos

campos que no la cumplan no se seleccionarán.

GROUP BY para agrupar en una sola línea el campo o campos especificados.

HAVING del resultado de GROUP BY elimina las que no cumplan su expresión

condicional.

ORDER BY para ordenar el orden de las columnas de formas ascendente

(ASC) o descendente (DESC). Por defecto el orden se hace de forma

ascendente.

Además de la sintaxis, podemos encontrarnos con consultas que utilicen las

siguientes funciones de agregado: COUNT, SUM, AVG, MAX, MIN.

Operadores lógicos: AND, OR, NOT.

Operadores de comparación: >, <, , =, =, !=, !, BETWEEN para intervalo de

valores, LIKE para comparación entre cadenas de caracteres con los

pattern ‘%’ y ‘_’.

IN para especificar registros de una base de datos.

Para ver un ejemplo, vamos a ver las tablas de nuestra base de datos

agregando campo STOCK a la tabla CONSOLA_JUEGO, para saber cuántas

unidades hay de cada juego y por consola.

Consulta: Mostrar los tipos de consolas que hay registrados

SELECT DISTINCT tipo

FROM consola

Consulta: Nombre de los juegos que sólo están en 3DS

SELECT titulo

FROM juego

WHERE juego.id_juego IN(

SELECT id_juego

FROM consola_juego
WHERE consola_juego.id_consola IN(

SELECT id_consola

FROM consola

WHERE consola.nombre=’3DS’)

Consulta: Stock total de aquellos juegos cuyo género sea de Acción

SELECT SUM(consola_juego.stock)

FROM consola_juego

WHERE consola_juego.id_juego IN(

SELECT id_juego

FROM juego

WHERE juego.genero=’accion’)

Consulta: Titulo de aquellos juegos con stock total que supere las 20 unidades

SELECT juego.titulo

FROM juego

WHERE juego.id_juego IN(

SELECT id_juego

FROM consola_juego

GROUP BY id_juego

HAVING SUM(consola_juego.stock) > 20)

INSERT – Utilizado para cargar lotes de datos en la base de datos en una

única operación
INSERT INTO table_name

VALUES (value1,value2,value3,…);

INSERT INTO consola(nombre, marca) VALUES(‘PS4′,’Sony’);

INSERT INTO consola(nombre, marca) VALUES(‘XBOX ONE’,’Microsoft’);

INSERT INTO consola(nombre, tipo, marca)

VALUES(‘3DS’,’Portatil’,’Nintendo’);

INSERT INTO juego(titulo, genero) VALUES(‘Dark Souls III’, ‘ROL’);

INSERT INTO juego (titulo, genero) VALUES(‘Project Cars’, ‘Conduccion’);

INSERT INTO juego(titulo, genero) VALUES(‘Metal Gear Solid V’, ‘Accion’);

INSERT INTO juego (titulo, genero) VALUES(‘Killer Instinct’, ‘Lucha’);

INSERT INTO juego(titulo, genero) VALUES(‘Metroid Prime’, ‘Accion’);

INSERT INTO juego(titulo, genero) VALUES(‘Dragon Quest VII’, ‘ROL’);

INSERT INTO consola_juego (id_consola, id_juego, stock) VALUES(1,1, 45);

INSERT INTO consola_juego (id_consola, id_juego, stock) VALUES(1,2, 30);

INSERT INTO consola_juego (id_consola, id_juego, stock) VALUES(1,3, 15);

INSERT INTO consola_juego (id_consola, id_juego, stock) VALUES(2,3, 15);

INSERT INTO consola_juego (id_consola, id_juego, stock) VALUES(2,4, 19);

INSERT INTO consola_juego (id_consola, id_juego, stock) VALUES(3,5, 34);

INSERT INTO consola_juego (id_consola, id_juego, stock) VALUES(3,6, 23);

UPDATE – Utilizado para modificar los valores de los campos y registros

especificados

UPDATE table_name
SET column1=value1,column2=value2,…

WHERE some_column=some_value;

UPDATE juego

SET titulo=’The Legend of Zelda’

WHERE titulo=’Metroid Prime’

DELETE – Utilizado para eliminar registros de una tabla de una base de datos

DELETE FROM juego WHERE id_juego=5

MERGE – Cuando se quiere actualizar/insertar datos de una tabla, actualiza

cuando se cumple la condición e inserta cuando no se cumple

MERGE INTO [tabla_destino]

USING ([tabla o vista o consulta])

ON ([condición de existencia de registro])

WHEN MATCHED THEN [sentencia de actualización]

WHEN NOT MATCHED THEN [sentencia de inserción];

Para este ejemplo creamos la tabla consola_temporal con los siguientes

valores.

INSERT INTO consola_temporal(nombre, marca) VALUES(‘PS4′,’Sony’);

INSERT INTO consola_temporal(nombre, marca) VALUES(‘XBOX

ONE’,’Microsoft’);

INSERT INTO consola_temporal(nombre, tipo, marca) VALUES(‘NEW

3DS’,’Portatil’,’Nintendo’);
INSERT INTO consola_temporal(nombre, tipo, marca)

VALUES(‘PSVITA’,’Portatil’,’Sony’);

MERGE INTO consola C

USING (SELECT * FROM consola_temporal) CT

ON(C.id_consola=CT.id_consola)

WHEN MATCHED THEN UPDATE SET C.nombre=CT.Nombre

WHEN NOT MATCHED THEN insert (nombre, tipo, marca)

VALUES (CT.nombre, CT.tipo,CT.marca);

Si comprobamos los valores de la tabla consola, quedarán de la siguiente

forma.

CALL – Para ejecutar un procedimiento en PL/SQL o Java

Para el desarrollo de procedimientos se realiza con la siguiente sintaxis.

En MySQL

CREATE PROCEDURE nombre ([parámetro1,parámetro2,…])

[Atributos de la rutina]

BEGIN instrucciones

END

DELIMITER //

CREATE PROCEDURE contador_hasta_n (IN n INT)

BEGIN

DECLARE contador INT DEFAULT 1;

WHILE contador<=n DO

SELECT contador;

SET contador = contador + 1 ;

END WHILE;

END//

DELIMITER //

CREATE PROCEDURE mostrar_juegos()

BEGIN

SELECT * FROM juego;

END//

DELIMITER ;

Para ejecutarlo usamos el comando CALL

CALL contador_hasta_n(5)
En SQL SERVER

USE coleccion

GO

CREATE PROCEDURE mostrar_juegos

AS

SELECT * FROM juego;

GO

exec mostrar_juegos

Podemos borrar el proceso para modificarlo si fuese necesario. Si se

intenta crear el proceso con nombre ya existente, el RDBMS no lo

permitirá.

DROP PROCEDURE [IF EXISTS] nombre_procedimiento

DROP PROCEDURE IF EXISTS contador_hasta_n

DROP PROCEDURE mostrar_juegos

EXPLAIN PLAN – Nos permite analizar el plan de ejecución para poder

optimizar la ejecución de las consultas

EXPLAIN SELECT * FROM consola_juego

Como resultado obtenemos lo siguiente

Básicamente nos indica que no existen índices para la consulta y como

resultado tendremos 6 filas

Realizamos otra comprobación

EXPLAIN SELECT juego.titulo

FROM juego

WHERE juego.id_juego IN(

SELECT id_juego

FROM consola_juego

GROUP BY id_juego

HAVING SUM(consola_juego.stock) > 20)

Nos muestra que se realiza una consulta primaria y depende del resultado de la

subconsulta, utilizando id_juego como índex para filtrar los resultados.

LOCK TABLE – concurrencia de control, para controlar el flujo actual

LOCK TABLES

tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}

[, tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] …

UNLOCK TABLES

LOCK TABLES consola READ;

INSERT INTO consola(nombre, tipo, marca) VALUES(‘NEW

3DS’,’Portatil’,’Nintendo’);

Nos permite bloquear la tabla consola y en modo lectura, si se intenta insertar

sobre ella no lo permitirá indicando que la tabla está bloqueada y no se puede

actualizar. De no usarse o no poder usar transacciones, es recomendable

LOCK TABLE.

Para desbloquear ejecutamos.

UNLOCK TABLES;

LOCK TABLES consola READ;

SELECT * FROM consola;

INSERT INTO consola(nombre, tipo, marca) VALUES(‘NEW

3DS’,’Portatil’,’Nintendo’);

UNLOCK TABLES; (MySQL En modo lectura no ponemos UNLOCK TABLES;

porque sino entonces nos mostrar error pero nos mostrará mensaje de 0

columnas devueltas. Por defecto, la tabla después se libera)

LOCK TABLES consola WRITE;

SELECT * FROM consola;

INSERT INTO consola(nombre, tipo, marca) VALUES(‘NEW

3DS’,’Portatil’,’Nintendo’);

UNLOCK TABLES;
En este caso, nos mostrará mensaje de 0 columnas devueltas y al mismo

tiempo inserta los valores en la tabla.

Antes de continuar con los comandos, es necesario explicar las vistas en la

base de datos. La diferencia entre una tabla y una vista, es que las tablas

almacenan datos mientras que las vistas no. Si probamos ejecutar la siguiente

consulta:

CREATE VIEW “NOMBRE_VISTA» AS “Instrucción SQL»;

CREATE VIEW v_ejemplo AS

INSERT INTO consola(nombre, tipo, marca) VALUES(‘NEW2

3DS’,’Portatil’,’Nintendo’);

Nos dará error de sintaxis. Eso es porque las vistas no están para consultas de

inserción o modificación

Si probamos esta otra:

CREATE VIEW v_ejemplo AS

SELECT * FROM consola;

La dará por válida y se nos guardará la vista. En el momento que queramos

consultarla, ejecutará la sentencia SQL.

Si ejecutamos:

INSERT INTO v_ejemplo(nombre, tipo, marca) VALUES(‘NEW2

3DS’,’Portatil’,’Nintendo’);
Como la vista v_ejemplo tiene relación que la tabla consola, se insertará en la

tabla consola, de forma indirecta estamos aplicando cambios sobre la tabla

La gran utilidad que tienen las vistas es para guardar consultas que queramos

ejecutar en cualquier momento, sin tener que estar escribiéndola nuevamente

en la consola del RDBMS.

Lo próximo que veremos son los comandos DCL y TCL.

SQL COMANDOS DCL Y TCL – PARTE 3

Comandos de SQL
Finalizamos viendo los comando DCL y TCL.

Comandos DCL (Data Control Language -Lenguaje Control de Datos) para

controlar el acceso a los datos.

GRANT – Permite asignar permisos sobre el objeto de la base de datos. El

usuario quien crea el objeto es el propietario y por defecto tiene todos los

permisos, incluido el de dar permisos a otro usuario denominado permiso de

concesión. El usuario propietario puede asignar los permisos que desee a otro

usuario incluido el de concesión, si este usuario tiene el permiso de concesión

puede asignar permisos a otro usuario.

GRANT lista_privilegios ON objeto TO lista_usuarios [ WITH GRANT OPTION ]

Permisos que se pueden asignar:

 USAGE: para usar un objeto específico de la base de datos.

 SELECT: para acceder a tablas o vistas.

 INSERT [ (nombre_columna) ]: Si se especifica el

nombre_columna, se otorga permiso para insertar en la columna

especificada. Si se omite entonces se permite insertar valores en

todas las columnas.

 UPDATE [ (nombre_columna) ]: Lo mismo que INSERT para

modificar.

 DELETE: para eliminar registros de una tabla o vista.

 REFERENCES [ (nombre_columna) ]: Si se especifica

nombre_columna permite referirse a la columna indicada como

vínculo de integridad, si se omite se aplica a todas las comunas y

también a las que se añadan después.

 GRANT OPTION: permite dar permisos a otro usuario.

 ALL: otorga todos los permisos menos GRANT OPTION.

El objeto suele ser la tabla o vista.

La lista de usuarios son usuarios o grupos que reconoce el sistema. Puede

usarse PUBLIC para referirse a todos los usuarios y grupos del sistema. Para

un grupo especifico usaremos GROUP

Necesitaremos primero un usuario para aplicar el ejemplo, lo creamos

siguiendo la siguiente sintaxis.

Anuncios

INFORMA SOBRE ESTE ANUNCIO

CREACIÓN DE USUARIOS. VINCULACIÓN USUARIO-LOGIN

Si ahora intentamos hacer un Select a la tabla consola perteneciente a la base

de datos coleccion, nos mostrará mensaje de permiso denegado.

Los usuarios que crean una base datos al ser propietarios tienen asignados por

defecto todos los permisos, para los demás usuario se les puede asignar o

revocar permisos.
En este caso el usuario propietario le asignará el permiso SELECT mediante la

sentencia GRANT. Esto significa que solo podrá ver datos de la tabla, pero no

insertar o borrar.

Ejemplo: Asignamos permisos SELECT para mostrar registros de la tabla y

también INSERT sólo en los campos nombre y marca de la tabla consola al

usuario usergamer

GRANT SELECT, INSERT(nombre, marca) ON consola TO usergamer

REVOKE – Para quitar permisos.

REVOKE [ GRANT OPTION FOR ] lista_privilegios ON objeto FROM

lista_usuarios { RESTRIC | CASCADE }

GRANT OPTION FOR: para quitar permiso de concesión.

RESTRIC se usa cuando existe en un solo objeto, esta podría fallar en el caso

que el usuario al que se quiere quitar los permisos haya asignado permisos a

otros usuarios. Para ello se usa CASCADE que permitirá los permisos al

usuario y también a aquellos usuarios que les haya concedido permisos.

También se destruirán los objetos que hayan sido creados por esos permisos.

Denegamos el permiso insertar en la tabla consola al usuario usuario1

REVOKE INSERT ON consola TO usuario1

Comandos TCL (Transaction Control Language -Lenguaje Control de

Transacciones) para las transacciones de datos.

COMMIT – Finaliza la transacción y realiza los cambios hechos durante la

transacción. Las transacción bloqueadas sobre la tabla quedan liberadas.

commit

ROLLBACK – Rechaza la transacción y no aplica cambios, volviendo al estado

antes de iniciarse la transacción:

 ROLLBACK [WORK] [TO SAVEPOINT]

 WORK: retorna al comienzo del último BEGIN TRANSACTION

ejecutado

 TO SAVEPOINT: al crearse un punto de guardado, se puede

devolver los datos correspondientes al momento en que fue

creado el punto creado.

  SAVE POINT NAME: punto de rescate de una transacción que

nos permite volver.

SAVEPOINT– Crea un punto en la transacción que se pueda volver mediante

ROLLBACK.

Comenzamos una transacción salvando un punto que llamamos A, realizamos

la actualización en la tabla consola y después un ROLLBACK para volver al

momento exacto del punto de guardado. Cerramos la transacción

con COMMIT.
SET TRANSACTION – Inicializa una transacción en la base de datos,

indicando si quiere que sea de solo lectura o lectura/escritura.

SET TRANSACTION [ READ WRITE | READ ONLY ];

Argumentos de SET TRANSACTION en SQL SERVER:

 READ UNCOMMITTED: permite leer filas que han sido modificadas por

otras transacciones sin ser confirmadas, es decir, no existe ningún bloqueo

para que otras transacciones bloqueen la actual.

 READ COMMITED: no permite leer filas modificadas, pero no estar

confirmadas por otras transacciones.

 REPEATABLE RED: no permite leer filas modificadas, pero no estar

confirmadas por otras transacciones y ninguna otra transacción puede

modificar ningún dato leído de la transacción actual hasta que finalice.

 SNAPSHOT: la transacción solo reconoce datos confirmadas antes del

comienzo de la misma, las instrucciones que se ejecute en la transacción

actual no se verán afectadas por otras transacciones después del inicio de

la actual. Los datos estarían tal y como estaban al principio de la

transacción.

 SERIALIZABLE: no permite leer datos que hayan sido modificados, pero no

estar confirmados por otras transacciones; ninguna otra transacción puede

modificar los datos leídos por la transacción actual hasta que finalice; y

otras transacciones no pueden insertar filas nuevas con valores de clave

que estén en el intervalo de claves leído por instrucciones de la transacción

actual hasta que finalice.

 Se puede además aplicar SET TRANSACTION ISOLATION LEVEL, se

aplica cuando al devolver el control de un objeto, se restablece en el nivel

que fue invocado por el objeto.

Ejemplo con REPEATABLE RED

Comenzamos una transacción salvando un punto que llamamos A, realizamos

la actualización en la tabla consola y después un ROLLBACK para volver al

momento exacto del punto de guardado. Cerramos la transacción

con COMMIT.

Obtenemos los registros de ambas tablas.

Probamos la misma consulta añadiendo un INSERT INTO.

Y nos agrega la fila sin problemas.

Si con otro usuario hacemos un SELECT en la tabla juego (teniendo los

permisos para hacerlo)

Vemos que podemos hacer la consulta sin problemas

Ahora probamos con UPDATE

Y hemos modificado la fila que habíamos agregado antes.

Lo siguiente es ver si podemos consultar la tabla con el otro usuario.

Anuncios

INFORMA SOBRE ESTE ANUNCIO

Y no muestra nada, la consulta la intenta realizar pero no llega a terminar el

proceso. Donde apunta la flecha verde es para cancelar la consulta, la causa

de no poder ver el resultado de la consulta es porque al haber hecho un cambio

y ser confirmada la transacción, se encuentra bloqueado el objeto de la base

de datos, en este caso la tabla juego

Con esto terminamos los comandos SQL.