Tutorial de SQL Basado en MYSQL
Cargado por
juancolillabarraganTutorial de SQL Basado en MYSQL
Cargado por
juancolillabarraganTutorial de SQL basado en MySQL
1Historia del lenguaje SQL 5
2Funcionamiento 5
2.1Componentes de un entorno de ejecución SQL 5
2.2Formas de trabajar con SQL 5
2.3Procesamiento de instrucciones SQL 6
2.4Elementos del lenguaje 6
2.5Normas de escritura 6
3SQL DDL 7
3.1Definiciones previas 7
3.2Creación de bases de datos 7
3.3Creación de tablas 7
3.3.1Normas para la creación de tablas 7
3.3.2Sintaxis para crear tablas 8
3.3.3Cuadro resumen de tipos de datos más utilizados 8
3.4Restricciones básicas de columna 8
4Introducción al servidor MySQL 9
5Instalación 9
6Contraseña y primera conexión 10
6.1Configuración inicial 10
6.2Cambio de contraseña de root 12
6.3Reseteo de contraseña olvidada 12
6.4Conexión con el servidor 13
7Conexión a través de phpMyAdmin 13
7.1Configurar acceso sin contraseña 14
7.2Configurar acceso seguro a través de cookie 14
8Primeros pasos con MySQL 15
8.1Creación de una BD 15
8.2Selección de una BD 15
8.3Mostrar Bases de Datos/TABLAS 15
8.4Creación de usuarios 15
8.5Borrado de usuarios 15
8.6Dando permisos a usuarios 16
9Creación de tablas 16
10Tipos de datos en MySQL 17
10.1Tipos numéricos enteros 17
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 1 de 57
0.2Tipo autonumérico
1 17
10.3Tipos decimal - Coma fija 17
10.4Números en coma flotante 18
10.5Valores BIT 18
10.6Tipos para Fecha y hora 18
10.6.1Establecer fecha/hora actual al crear y al actualizar 19
10.6.2Funciones útiles para fechas/horas 19
10.7Tipos de datos para cadenas de texto 21
10.7.1Tipos Char y Varchar 21
10.7.2Tipos Text 21
10.7.3Funciones más importantes con cadenas de texto 21
11Restricciones de atributos 22
11.1Aceptar Nulos 23
11.2Claves primarias 23
11.3Claves ajenas 23
11.4Integridad referencial 24
11.5Valor por defecto 24
11.6Claves alternativas 24
12Modificación de la estructura de una tabla 24
12.1Renombrar una tabla 25
12.2Borrado de una tabla completa 25
12.3Añadir un atributo a una tabla existente 25
12.4Borrar un atributo 25
12.5Modificar un atributo sin cambiar su nombre 25
12.6Cambiar el nombre a un atributo/columna 25
12.77. Incluir claves ajenas una vez creada una tabla 25
12.8Borrar una clave ajena una vez creada 26
13DML: manipulación de registros en una tabla 26
13.1Inserción de un registro nuevo 26
13.2Inserción de varios registros de una sola vez 26
13.3Eliminación de todos los registros 27
13.4Eliminación de determinados registros 27
13.5Actualización de todos los registros de una tabla 27
13.6Actualización de determinados registros 27
14DQL: Consultas simples SQL 28
14.1Cláusula SELECT 28
14.1.1Select junto a campos calculados o literales 29
14.2Cláusula ALL/DISTINCT 29
14.3Creación de alias para campos y tablas con AS 29
14.4Cláusula WHERE 30
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 2 de 57
14.5Algunos operadores básicos para crear condiciones 30
14.5.1Precedencia de operadores 31
14.5.2Uso del operador LIKE 31
14.6Cláusula ORDER BY 32
14.7Cláusula LIMIT 32
15Uso de SELECT junto a CREATE e INSERT 33
15.1Crear una tabla como resultado de una consulta 33
15.2Insertar un conjunto de registros procedentes de una consulta 33
16Combinación de varias consultas con operadores de conjuntos 33
16.1Unión de consultas 33
16.2Intersección de consultas (no soportado por MySQL) 34
16.3Except/MINUS (No soportado por MySQL) 34
16.4Producto cartesiano 35
17Funciones de agregado 35
18Agrupamientos 36
18.1Condiciones para grupos 36
19Subconsultas 37
19.1Subconsultas en WHERE 37
19.1.1Operador de pertenencia IN 37
19.1.2Operadores de cuantificación ALL y ANY 38
19.1.3Operador de existencia EXISTS 38
19.2Subconsultas en el FROM → Tablas derivadas 39
19.3Subconsultas en el Select 39
20Consultas multitabla 39
20.1Cruzar tablas con INNER JOIN 40
20.1.1Varios cruces INNER JOIN en la misma consulta 41
20.2Otras variantes de JOIN 41
20.2.1LEFT JOIN 41
20.2.2RIGHT JOIN 42
20.2.3FULL OUTER JOIN (No funciona en MySQL) 42
20.2.4CROSS JOIN 43
21Estructuras de control dentro de una Consulta 44
21.1Case...When 44
21.2IF 45
21.3IFNULL 45
22JOIN + GROUP BY 45
22.0.1Cláusula HAVING 46
22.0.2Cláusula ORDER BY 47
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 3 de 57
23Creación de vistas 47
24DCL: Lenguaje de control de datos 48
24.1Creación y borrado de usuarios 48
24.2Sentencia GRANT 48
24.3Establecer un máximo de recursos por cuenta de usuario 50
24.4Sentencia REVOKE 50
24.5Mostrar privilegios existentes 50
25DTL: Lenguaje Control de Transacciones 51
25.1Transacciones 51
25.2Propiedades ACAD 51
25.3Gestión de transacciones en MySQL 51
25.4Formas de trabajar con transacciones en MySQL 53
25.5Aislamiento de transacciones 53
25.5.1Problemas derivados de la simultaneidad 54
25.5.2Niveles de aislamiento de transacciones 55
25.5.3Establecer niveles de aislamiento 56
25.6Reglas genéricas para el uso de transacciones 57
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 4 de 57
1Historia del lenguaje SQL
● 1970: E. F. Codd publica libro: "Un modelo de datos relacional para grandes bancos
de datos compartidos"que dictaría las directrices de las bases de datos relacionales.
● A lo largo de la décad de los 70: IBM (para quien trabajaba Codd) utiliza las
directrices de Codd para crear el Standard English Query Language (Lenguaje
Estándar Inglés para Consultas) al que se le llamó SEQUEL, desarrollado por D.
Chamberlin and Ray Boyce. Más adelante se le asignaron las siglas SQL (Standard
Query Language, lenguaje estándar de consulta). Algunos ingleses los siguen
pronunciando “siquel”.
● 1979: Oracle presenta la primera implementación comercial del lenguaje.
● 1986: SQL se convierte en lenguaje estándar por ANSI de los SGBD relacionales.
Un año después lo adopta ISO, lo que convierte a SQL en estándar mundial como
lenguaje de bases de datos relacionales.
● 1989: aparece el estándar ISO (y ANSI) llamado SQL-89 o SQL1.
● 1992: aparece la nueva versión estándar de SQL (a día de hoy sigue siendo la más
conocida) llamada SQL92.
● 1999: se aprueba un nuevo SQL estándar que incorpora mejoras que incluyen
triggers, procedimientos, funciones,… y otras características de las bases de datos
objeto-relacionales; dicho estándar se conoce como SQL99.
● 2011: Se publica el último estándar SQL (SQL-2011).
2Funcionamiento
2.1Componentes de un entorno de ejecución SQL
Según la normativa ANSI/ISO cuando se ejecuta SQL, existen los siguientes elementos a
tener en cuenta en todo el entorno involucrado en la ejecución de instrucciones SQL:
● Un agente SQL. Cualquier elemento que provoque la ejecución de instrucciones
SQL que serán recibidas por un cliente SQL
● Una implementación SQL. Procesador software capaz de ejecutar las instrucciones
pedidas por el agente SQL. Una implementación está compuesta por:
○ Cliente SQL. Software conectado al agente que funciona como interfaz entre
el agente SQL y el servidor SQL. Sirve para establecer conexiones entre sí
mismo y el servidor SQL.
○ Servidor SQL. El software encargado de manejar los datos a los que la
instrucción SQL lanzada por el agente hace referencia. Es el software que
realmente realiza la instrucción, los datos los devuelve al cliente.
2.2Formas de trabajar con SQL
● Ejecución directa. SQL interactivo Las instrucciones SQL se introducen a través de
un cliente que está directamente conectado al servidor SQL; por lo que las
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 5 de 57
instrucciones se traducen sin intermediarios y los resultados se muestran en el
cliente.
● Ejecución incrustada o embebida. Las instrucciones SQL se colocan como parte
del código de otro lenguaje que se considera anfitrión (C, Java, Pascal, Visual
Basic,...).
● Ejecución a través de clientes gráficos. Se trata de software que permite conectar
a la base de datos a través de un cliente. El software permite manejar de forma
gráfica la base de datos y las acciones realizadas son traducidas a SQL y enviadas
al servidor. Los resultados recibidos vuelven a ser traducidos de forma gráfica para
un manejo más cómodo.
● Ejecución dinámica. Se trata de SQL incrustado en módulos especiales que
pueden ser invocados una y otra vez desde distintas aplicaciones
2.3Procesamiento de instrucciones SQL
El proceso de una instrucción SQL, a grandes rasgos, es el siguiente:
1. Análisis sintáctico: se comprueba la sintaxis de la misma.
2. Análisis semántico: Si es correcta se valora si los metadatos de la misma son
correctos. Se comprueba esto en el diccionario de datos.
3. Optimización: Si es correcta, se optimiza, a fin de consumir los mínimos recursos
posibles.
4. Ejecución y devolución del resultado.
2.4Elementos del lenguaje
● DDL (lenguaje de definición de datos) se refiere a las declaraciones que sirven para
definir tablas y otros elementos. También aquellas que modifican la estructura o borran
dichos elementos. En resumen, son los comandos SQL que se pueden usar para definir
el esquema de la base de datos (bases de datos, tablas, claves, vistas ...).
● DML (lenguaje de manipulación de datos) se refiere a aquellas instrucciones que
permiten agregar / modificar / borrar datos en sí (registros/filas de las tablas).
● DQL (lenguaje de consulta de datos) se refiere a las declaraciones SELECT y SHOW
(consultas). Sirven para obtener los datos de una forma organizada. Algunos autores
incluyen el Select dentro del DML.
● DCL (lenguaje de control de datos) se refiere a las declaraciones GRANT y REVOKE
que sirven para otorgar / revocar permisos sobre bases de datos y sus contenidos.
● DTL (Data Transaction Language) se refiere a las instrucciones START
TRANSACTION, SAVEPOINT, COMMIT y ROLLBACK [TO SAVEPOINT] que se usan
para gestionar transacciones (operaciones que incluyen más instrucciones, ninguna de
las cuales se puede ejecutar si una de ellas falla).
2.5Normas de escritura
● SQL no distingue entre mayúsculas y minúsculas.
● Las instrucciones finalizan con el signo de punto y coma (;).
● Cualquier comando SQL (SELECT, INSERT,...) puede ser distribuido en varias líneas
antes de finalizar la instrucción.
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 6 de 57
● Los espacios no dan error.
● Se pueden tabular líneas para facilitar la lectura si fuera necesario.
● Los comentarios en el código SQL comienzan por /* y terminan por */ (en la
mayoría de SGBD).
3SQL DDL
SQL DDL es el lenguaje de definición de datos (Data-Definition-Language). Es la parte del
lenguaje SQL que nos va a servir para definir tablas y otros objetos de nuestra Base de
Datos.
3.1Definiciones previas
● Base de datos: es un conjunto de objetos pensados para gestionar datos.
● Esquemas: lugar donde están contenidos estos objetos. Pueden ir asociados al
perfil de un usuario en particular.
● Catálogo: almacén de los distintos esquemas. Así el nombre completo de un objeto
vendría dado por: catá[Link]
3.2Creación de bases de datos
Se utiliza la sintaxis CREATE DATABASE nombre_BD;
3.3Creación de tablas
3.3.1Normas para la creación de tablas
Deben cumplir las siguientes reglas (estas reglas son para el SGBD Oracle que se
ajusta bastante al estándar, en otros SGBD podrían cambiar):
● Deben comenzar con una letra (esto se aplica a Oracle, no siendo así en MySQL).
● No deben tener más de 30 caracteres.
● Sólo se permiten utilizar letras del alfabeto (inglés), números o el signo de subrayado
(también el signo $ y #, pero esos se utilizan de manera especial por lo que no son
recomendados).
● No puede haber dos tablas con el mismo nombre para el mismo esquema
(pueden coincidir los nombres si están en distintos esquemas).
● No puede coincidir con el nombre de una palabra reservada SQL (por ejemplo
no se puede llamar SELECT a una tabla)
● En el caso de que el nombre tenga espacios en blanco o caracteres nacionales
(permitido sólo en algunas bases de datos), se suele entrecomillar con comillas
dobles. En el estándar SQL 99 (respetado por Oracle) se pueden utilizar comillas
dobles al poner el nombre de la tabla a fin de hacerla sensible a las mayúsculas (se
diferenciará entre “FACTURAS” y “Facturas”). En MySQL, para determinados
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 7 de 57
comandos, se puede necesitar escapar los nombres de las columnas con comilla
invertida (`nombre`).
3.3.2Sintaxis para crear tablas
● CREATE TABLE nombre_tabla (nombre_columa1 tipo_datos(tamaño_datos) [restricciones],
nombre_columna2 tipo_dato(tamaño_datos),…..) [ AS (subconsulta)];
3.3.3Cuadro resumen de tipos de datos más utilizados
T. BÁSICOS TIPOS RELACIONADOS USADOS POR SGBD Tamaño habitual Descripción
BINARY VARBINARY 1 BYTE DATOS BINARIOS LONGITUD FIJA O
VARIABLE
LONGBINARY GENERAL/OLEOBJECT SEGÚN TAM. De 0 a 1Gb para objetos OLE
CHAR BYTE 8 bits Entero entre 0 y 255 para Caracteres.
VARCHAR(m) ALPHANUMERIC/STRING/NVARCHAR/CHARACT 8 bits/char/Var. Textos de 0 a 255 caracteres
ER/TEXT
BIT(m) BOOLEAN/LOGICAL/LOGICAL1/YESNO 1 BYTE VALORES SI Y NO, TRUE/FALSE
INT SMALLINT/TINYINT/SHORT 16 bits Entero corto: -32768 a 32768
LONG INT/INTEGER/BIGINT 32 bits Entero largo, hasta +-2mil millones.
COUNTER AUTO_INCREMENT 32 bits Número LONG incrementado automát.
SINGLE FLOAT4/IEEESINGLE/REAL 32 bits Valor coma flotante de precisión simple
DOUBLE FLOAT/FLOAT8/IEEEDOUBLE/NUMBER/NUMERI 64 bits Coma flotante de precisión doble
C/DECIMAL
DATETIME DATE/TIME/TIMESTAMP 64 bits Fechas/horas
CURRENCY MONEY 64 bits Entero para monedas
Ten en cuenta que, según el SGBD utilizado, aunque los tipos sean similares, la
nomenclatura cambia y por lo tanto los nombres de los tipos pueden ser diferentes en cada
uno.
3.4Restricciones básicas de columna
○ NULL/NOT NULL: especifica que la columna acepta o no valores NULL (por defecto
SI).
○ DEFAULT: indica un valor por defecto para la columna, en caso de que no se
especifique.
○ UNIQUE: exigen la unicidad de los valores de un conjunto de columnas.
○ PRIMARY KEY: columna es clave primaria. Según motor de BD, se pone después
del campo o bien al final de las declaraciones. Si queremos clave primaria de
varias columnas (compuesta), podemos poner una restricción NOMBRADA:
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 8 de 57
CONSTRAINT claveComp PRIMARY KEY (Campo1,Campo2)
○ FOREIGN KEY: clave externa: FOREIGN KEY (idcliente) REFERENCES CLIENTES
(ID)). Para mantener integridad referencial declarativa (DRI) ON UPDATE/ON
DELETE, usaremos:
■ NO ACTION: No permite borrar/actualizar clave ajena si tiene alguna referencia.
Normalmente este es el comportamiento por defecto si no especificamos
acción a realizar.
■ SET NULL: tuplas que tengan el valor de clave borrada/actualizada, se ponen a
NULL.
■ SET DEFAULT: Si se puso valor por defecto, éste rellena el hueco vacío que ha
quedado.
■ CASCADE: se actualizarán/borrarán también todas las filas que referencian en
cascada
4Introducción al servidor MySQL
MySQL es un sistema de gestión de bases de datos relacional considerada como la base
datos de código abierto más popular del mundo, y una de las más populares en general
junto a Oracle y Microsoft SQL Server. A continuación vemos un breve resumen de la
historia de MySQL:
- Inicialmente desarrollado por la empresa MySQL AB.
- Comprado por Sun Microsystems en 2008
- Comprado por Oracle Corporation en 2010.
MySQL es propiedad de una empresa privada, que posee el copyright de la mayor parte
del código. La base de datos se distribuye en varias versiones, una versión llamada
Community, distribuida bajo la Licencia GNU, y varias versiones Enterprise, para aquellas
empresas que quieran incorporarlo en productos privativos.
En 2009 algunos desarrolladores crearon un fork denominado MariaDB (incluidos algunos
desarrolladores originales de MySQL). Estos programadores estaban descontentos con el
modelo de desarrollo y el hecho de que una misma empresa controle a la vez MySQL y
Oracle Database.
En este tutorial vamos a basarnos en la versión para windows de XAMPP que ya incluye un
servidor MariaDB + Apache + PHP, con lo que nos permitirá realizar nuestros pequeños
proyectos con total funcionalidad.
5Instalación
La forma más sencilla de instalar MySQL y convertir un ordenador en un servidor de Bases
de Datos es instalar uno de los paquetes que incluyen: Apache (servidor web), OpenSSL
para soporte SSL, base de datos MySQL y el lenguaje de programación PHP. Estos
paquetes son denominados: WAMP (Windows, LAMP (linux), XAMPP (para todos los
sistemas, incluye scripts perl), MAMP (mac OS).
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 9 de 57
Estos paquetes son fácilmente descargables e instalables en cualquiera de estos sistemas
operativos.
6Contraseña y primera conexión
6.1Configuración inicial
Una vez instalado mySQL debemos asegurarnos de que tanto apache como php estén
corriendo adecuadamente. En algunas versiones de Windows, es necesario arrancar los
servicios “a mano”, desde el menú Inicio y dejarlos corriendo. Una forma sencilla de
comprobar que los servicios están funcionando es ejecutar, en el caso de XAMPP, el
“XAMPP Control Panel”, que nos ofrece una ventana como la siguiente:
Como vemos en la pantalla superior, se han iniciado los servicios Apache y MySQL, el
servicio Tomcat tiene algún problema, pero ya podríamos empezar a trabajar.
Para comprobar su funcionamiento, bastará con poner la dirección [Link] en un
navegador cualquiera. Si aparece una página que hace referencia a XAMPP, Apache o
cualquier herramienta web, habremos tenido éxito.
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 10 de 57
De forma predeterminada, la instalación de MySQL / MariaDB que se viene con XAMPP
establece el usuario root sin contraseña para conectarse al servidor de Base de Datos. Este
es un grave riesgo de seguridad, especialmente si se pretende utilizar XAMPP en
situaciones de producción.
Para cambiar la contraseña de root de MySQL / MariaDB, seguiremos estos pasos:
1. Asegúrate de que el servidor MySQL / MariaDB se está ejecutando.
2. Abre el símbolo del sistema de Windows haciendo clic en el botón "Shell" en el panel
de control de XAMPP.
3. Use la mysqladmin utilidad de línea de comandos para modificar la contraseña de
MySQL / MariaDB, usando la siguiente sintaxis:
mysqladmin --user=root password "minuevacontraseña"
Para el servicio MySQL y vuelve a arrancarlo
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 11 de 57
6.2Cambio de contraseña de root
1. Si ya hemos establecido una una contraseña y deseamos cambiarla por una nueva:
mysqladmin --user=root --password=contraseñavieja password
"nuevacontraseña"
2. Para comprobar que la nueva contraseña funciona, podemos intentar conectar con
mysql desde la misma línea de comandos. Para conectarse:
mysql --user=root --password=contraseña
También podemos usar la versión abreviada del comando:
mysql -u root -pcontraseña (no lleva espacio entre la p y la contraseña).
Si ponemos -p y no ponemos contraseña, nos la pedirá antes de empezar a trabajar.
6.3Reseteo de contraseña olvidada
Si hemos olvidado la contraseña de root, tenemos varias opciones para resetearla. En el
S.O. Windows, el sistema es muy sencillo.:
1. Creamos un fichero de texto con la siguiente sentencia:
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');
/* MyNewPass será sustituida por la contraseña deseada*/
y lo guardamos como un fichero de texto que llamaremos [Link]
2. Paramos el servidor mysql desde el XAMPP Control Panel.
3. Desde una línea de comandos, nos colocamos en la carpeta donde se encuentre
instalado el servidor mysql de xampp (normalmente c:\xampp\mysql\bin) y
ejecutamos: [Link] --init-file=[Link]
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 12 de 57
Se supone que previamente tenemos el archivo recuperar copiado en la carpeta.
Una vez arranque el servicio MySQL, ya tenemos el servidor con la nueva
contraseña establecida.
6.4Conexión con el servidor
Para conectarse al servidor MySQL, basta pulsar en el botón “Shell” de XAMPP Control
Panel.
Aparecerá una ventana de consola en la que ejecutaremos: mysql --user=root -p
A continuación nos pedirá la contraseña que hemos establecido anteriormente. Si hemos
hecho todo correcto veremos lo siguiente:
7Conexión a través de phpMyAdmin
XAMPP trae integrado un servidor apache y un gestor del servidor de Base de Datos
llamado phpMyAdmin que nos proporciona una interfaz más “amigable” para realizar
nuestro trabajo y consulas SQL directamente al servidor.
Para acceder a phpMyAdmin, basta tener arrancados los servicios y acceder a través de tu
navegador a la dirección: [Link]
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 13 de 57
Al entrar, dependiendo de la configuración que tengamos establecida, puede suceder que
se nos pida usuario/contraseña o no. Veamos cómo podemos asegurarnos de qué sucede
para entrar
7.1Configurar acceso sin contraseña
Para que no se nos pida la contraseña, podemos optar por incluirla en el archivo de
configuración de phpMyAdmin. Este método no es para nada seguro, pero nos permite
entrar salir de phpMyAdmin en caso de que estemos realizando pruebas o trabajando en
una base de datos en construcción. Cuando dejemos la BD en producción, evidentemente
este parámetro debería cambiarse o proteger el archivo apropiadamente.
● Editamos el fichero [Link] situado en el directorio donde se ha instalado
phpMyAdmin. En mi caso, está dentro de C:\XAMPP
● Buscamos la cadena: $cfg['blowfish_secret'] = '' ; /* YOU SHOULD CHANGE THIS FOR
A MORE SECURE COOKIE AUTH! */ y la ponemos en blanco ‘’
● Buscamos también la cadena siguiente y establecemos su valor a config:
/* Authentication type and info */
$cfg['Servers'][$i]['auth_type'] = 'config'
● Ahora, hay que poner los valores del usuario y contraseña en los campos:
$cfg['Servers'][$i]['user'] = 'root';
$cfg['Servers'][$i]['password'] = 'micontraseñaderoot'
7.2Configurar acceso seguro a través de cookie
En este caso, phpMyAdmin nos pedirá la contraseña cada vez que queramos iniciar
sesión. Esta opción es mucho más segura y más adecuada para entornos en producción.
Cambiamos los siguientes valores en el archivo [Link] señalado anteriormente.
$cfg['blowfish_secret'] = 'holaquetalestasejemplodefraselarga'; /* aquí podemos poner lo que
queramos, mientras que sea una frase larga, se usa como generador de nº aleatorios*/
$cfg['Servers'][$i]['auth_type'] = 'config';
$cfg['Servers'][$i]['user'] = 'root';
$cfg['Servers'][$i]['password'] = 'tucontraseñaderoot';
/*cambiar estos dos últimos valores por los que hayas puesto en tu servidor*/
En el primer parámetro basta con poner una frase larga aleatoria.
NOTA: Si tienes algún problema o estropeas el fichero de configuración, puedes
descargar un fichero de configuración de phpMyAdmin completo para modificarlo
aquí.
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 14 de 57
8Primeros pasos con MySQL
8.1Creación de una BD
Una vez conectados por primera vez al sistema, podemos crear nuestra primera base de
datos mediante el comando:
CREATE DATABASE MIBASE;
Ten en cuenta que el punto y coma es necesario al final de cada orden para que el
intérprete sepa dónde termina. Si pulsamos INTRO y la orden es correcta aparecerá en la
pantalla la frase: Query OK.
8.2Selección de una BD
En un SGBD como MySQL podemos tener varias bases de datos corriendo a la vez, si
deseamos trabajar con una de ellas debemos usar el comando: USE MIBASE;
A partir de ese momento, todas las consultas que ejecutemos se aplicarán a la base de
datos que hayamos seleccionado.
8.3Mostrar Bases de Datos/TABLAS
● Para mostrar todas las bases de datos existentes, ejecutaremos el comando: SHOW
DATABASES;
● Para mostrar todas las tablas existentes en una BD, una vez hayamos seleccionado
con el comando USE, tendremos que ejecutar el comando: SHOW TABLES;
8.4Creación de usuarios
Aunque para probar podemos usar el usuario root, lo lógico es crear un usuario para
conectarse a la Base de Datos/Tabla con la que vayamos a trabajar. Para ello, basta entrar
como root a la consola mysql con los comandos vistos anteriormente y ejecutar:
CREATE USER nombreusuario@localhost IDENTIFIED BY ‘micontraseña’
8.5Borrado de usuarios
DROP USER nombreusuario@localhost
En este caso debemos respetar las mayúsculas/minúsculas para que el usuario sea
identificado. Esto no sucede con los nombres de las bases de datos ni los de las
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 15 de 57
tablas/columnas, pues en este caso no es sensible a mayúsculas/minúsculas, aceptando
ambas.
8.6Dando permisos a usuarios
Cuando creemos un usuario, debemos darle al nuevo usuario los permisos para poder
trabajar con la Base de Datos/Tabla con la que vaya a operar. Como aún no hemos
estudiado los permisos, vamos a limitarnos a darle todos los permisos al nuevo usuario para
la Base de Datos con la que estemos trabajando.
GRANT ALL PRIVILEGES ON [Link] TO
nombreusuario@localhost;
Sustituiremos NOMBREBD por el nombre de nuestra Base de Datos y nombre tabla por el
nombre de la tabla donde queremos que pueda hacer operaciones. Si ponemos * se
entiende que serán todas las Bases de Datos y/o tablas disponibles.
GRANT ALL PRIVILEGES ON NOMBREBD.* TO nombreusuario@localhost;
9Creación de tablas
Una vez seleccionada una base de datos para trabajar, la sintaxis para crear una tabla es la
siguiente:
CREATE TABLE nombreTabla (
atributo1 tipodatos1 restricciones,
atributo2 tipodatos2 restricciones,
…,
restricciones_generales de tabla
);
MySQL provee un mecanismo para que una tabla no se cree si ya existe y es el siguiente:
CREATE TABLE IF NOT EXISTS nombreTabla (
atributo1 tipodatos1 restricciones,
atributo2 tipodatos2 restricciones,
…,
restricciones_generales de tabla
);
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 16 de 57
10Tipos de datos en MySQL
A la hora de crear una tabla, necesitamos conocer los tipos de datos disponibles en
MySQL/MariaDB. Estos tipos de datos difieren ligeramente de los que se pueden encontrar
en otros SGBD comerciales como ORACLE.
10.1Tipos numéricos enteros
Tipo Tamaño Valor mínimo Valor mínimo Valor Valor mínimo
(Bytes) con signo sin signo máximo con sin signo
signo
TINYINT 1 -128 0 127 255
SMALLINT 2 -32768 0 32767 65535
MEDIUMINT 3 -8388608 0 8388607 16777215
INT 4 -2147483648 0 2147483647 4294967295
BIGINT 8 -263 0 263-1 264-1
Para indicar si se trata de un tipo con signo o sin él, basta colocar la palabra UNSIGNED
detrás del tipo a utilizar.
10.2Tipo autonumérico
Si deseamos crear un entero que numerado automáticamente con valores que vayan
incrementándose (habitual para las claves principales), debemos utilizar un entero del
tamaño deseado tal y como hemos visto en la sección anterior y añadirle la restricción
AUTO_INCREMENT. Ej.: IDPEDIDO INT NOT NULL AUTO_INCREMENT
10.3Tipos decimal - Coma fija
El tipo de datos utilizado en MySQL para este propósito es DECIMAL(X,Y). Como podemos
ver, incluye dos parámetros X e Y que son números enteros y representan:
● X es la precisión: representa el número de dígitos a la izquierda del punto que se
almacenan para los valores.
● Y es la escala: número de dígitos que se pueden almacenar después del punto
decimal.
En MySQL, puede usarse también el tipo NUMERIC con el mismo propósito e igual sintaxis.
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 17 de 57
10.4Números en coma flotante
Los números en coma flotante son números decimales de los que conocemos su tamaño,
pero no el lugar que ocupa la coma y por lo tanto su precisión puede variar.
● MySQL permite una sintaxis no estándar. Podemos usar indistintamente
FLOAT(M,D), REAL(M,D) o DOUBLE PRECISION(M,D).
● (M,D) significa que los valores pueden almacenarse con hasta M dígitos en total,
de los cuales D dígitos pueden estar detrás de la coma decimal.
● Ejemplo: una columna definida como FLOAT(7,4) tomará como valor por defecto
-999.9999 cuando se muestre.
● MySQL realiza redondeo cuando almacena valores, por lo que si insertamos
999.00009 en un FLOAT(7,4), the valor que se almacenará es 999.0001.
10.5Valores BIT
El tipo de datos BIT se usa para guardar valores de BIT (0 o 1). Si ponemos BIT(M)
indicamos que queremos almacenar M valores de bit. M puede valer desde 1 a 64.
Para insertar valores de bit hay que usar la notación literal de bit: b'XXX'. Por ejemplo: b'111'
y b'10000000' representan el valor 7 y el 128, respectivamente.
10.6Tipos para Fecha y hora
● DATE: tipo para fechas sin hora. Reconoce y muestra fechas en el formato
'YYYY-MM-DD'. Podemos obtener otros formatos gracias a la función
DATEFORMAT. El rango admitido es '1000-01-01' a '9999-12-31'.
● TIME: tipo para horas sin fecha. Reconoce el formato 'HH:MM:SS'.
● DATETIME: fecha y hora en el formato 'YYYY-MM-DD HH: MM: SS' . El rango
admitido es '1000-01-01 00:00:00' a '9999-12-31 23:59:59'.
● TIMESTAMP: Fecha y hora con un rango de '1970-01-01 00:00:01' UTC a
'2038-01-19 03:14:07' UTC.
● YEAR: tipo para valores de años YYYY.
● **Diferencias principales entre DATETIME y TIMESTAMP:
○ Rango de valores posibles.
○ TIMESTAMP depende de las configuraciones/ajustes de la zona horaria.
DATETIME es constante.
○ TIMESTAMP es de cuatro bytes y DATETIME de ocho bytes, por lo que
TIMESTAMP es más ligero en la base de datos, con indexados más rápidos.
● Creación de fechas: MySQL permite usar fechas como cadenas de texto. Cualquier
signo de puntuación se toma como delimitador de las distintas partes de la fecha.
Ej.:'10:11:12' puede parecer una hora, pero MySQL lo toma como una fecha
'2010-11-12'. El valor '10:45:15' se convierte a '0000-00-00' porque '45' es un mes
no válido.
● Años con dos dígitos: Usar 95, 99, 00… puede ser ambiguo porque MySQL no
sabe el siglo al que pertenecen. Lo hace conforme a las siguientes reglas:
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 18 de 57
○ El rango 00-69 se convierte a 2000-2069.
○ El rango 70-99 se convierte a 1970-1999.
10.6.1Establecer fecha/hora actual al crear y al actualizar
Si deseamos que un atributo tome como valor por defecto la fecha/hora actual bastará
colocar la restricción DEFAULT CURRENT_TIMESTAMP tras su definición.
Si queremos que este atributo cambie cuando se actualice el registro bastará añadir la
restricción ON UPDATE CURRENT_TIMESTAMP
10.6.2Funciones útiles para fechas/horas
● NOW(): devuelve la fecha/hora actual.
● CURDATE(): devuelve la fecha actual.
● CURTIME(): devuelve la hora actual.
● DATE_FORMAT(date, format): Formatea una fecha según se exprese en la parte
“format”. En este parámetro se usan los comodines que se indican en la tabla
siguiente:
Formato Descripción
%a Día de la semana abreviado (En ingles → “Sun” a “Sat”)
%b Nombre del mes abreviado (Jan aDec)
%c Mes numérico (0 a 12)
%D Día del mes con valor numérico, seguido del sufijo anglosajón (1st, 2nd, 3rd, ...)
%d Día del mes numérico (01 a 31)
%e Día del mes numérico (0 a 31)
%f Microsegundos (000000 a 999999)
%H Hora (00 a 23)
%h Hora (00 a 12)
%I Hora (00 a 12)
%i Minutos (00 a 59)
%j Día del año en curso (001 a 366)
%k Hora (0 a 23)
%l Hora (1 a 12)
%M Nombre completo del mes (Enero a Diciembre)
%m Mes en valor numérico (00 a 12)
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 19 de 57
%p AM o PM
%r Hora en formato de 12 horas con AM o PM (hh:mm:ss AM/PM)
%S Segundos (00 a 59)
%s Segundos (00 a 59)
%T Hora en formato 24 horas (hh:mm:ss)
%U Número de semana del año, contada desde el domingo (00 a 53)
%u Número de semana del año, contada desde el lunes (00 a 53)
%V Número de semana del año, contada desde el domingo (01 a 53). Used with %X
%v Número de semana del año, contada desde el lunes (01 a 53). Used with %X
%W Día de la semana completo (Sunday a Saturday)
%w Número de día de la semana donde Domingo=0 y Sábado=6
%Y Año con 4 dígitos
%y Año con 2 dígitos
● DATEDIFF(fecha1,fecha2): devuelve el número de días de diferencia entre 2
fechas, siendo la fecha1 posterior. Si las ponenos al revés, devuelve un número de
días negativo.
● YEAR(fecha): Devuelve el año de una fecha.
● MONTH(fecha): Devuelve el mes de una fecha.
● DAY(fecha): Devuelve el día de una fecha.
● DAYOFWEEK(fecha): devuelve el día de la semana, siendo:
○ 1=Domingo, 2=Lunes, 3=Martes, 4=Miércoles, 5=Jueves, 6=Viernes,
7=Sábado.
● Ejemplo: Calcular la diferencia en años entre dos fechas: YEAR(date1) -
YEAR(date2) - (DATE_FORMAT(date1, '%m%d') < DATE_FORMAT(date2, '%m%d'))
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 20 de 57
10.7Tipos de datos para cadenas de texto
10.7.1Tipos Char y Varchar
● CHAR(tam_max): Cadena de longitud fija. Sirve para almacenar cadenas con
tamaño máximo indicado. Siempre se ocupa todo el tamaño porque se rellena con
espacios a la derecha. Estos espacios son eliminados por el SGBD antes de mostrar
las cadenas correspondientes.
● VARCHAR(tam_max): Cadena de longitud variable. Emplea 1 ó 2 bytes de prefijo
para indicar la longitud real de la cadena. No se rellena con espacios y sólo se usa el
tamaño que ocupe la cadena introducida. El tamaño máximo es de hasta 2¹⁶-1 =
65535 caracteres.
10.7.2Tipos Text
● TEXT: cadena de texto de longitud fija máxima 2¹⁶-1 = 65535 caracteres. A
diferencia del tipo varchar, en los tipos TEXT no se pueden poner índices, mientras
que en los varchar sí se puede. Este tipo puede aumentar o disminuir su logintud
máxima mediante el uso de: TINYTEXT, TEXT, MEDIUMTEXT, y LONGTEXT.
10.7.3Funciones más importantes con cadenas de texto
Nombre Descripción
BIT_LENGTH(cad) Return length of argument in bits
CHAR(int) Devuelve el carácter de un entero introducido
CHAR_LENGTH(cad) Retorna la longitud en caracteres del argumento.
CONCAT(cad1, cad2) Concatena varias cadenas
CONCAT_WS() Concatena con un separador
Return a string such that for every bit set in the value bits, you get an
EXPORT_SET()
on string and for every unset bit, you get an off string
Return the index (position) of the first argument in the subsequent
FIELD()
arguments
Devuelve un número decimal formateado según un número de
FORMAT()
decimales
INSERT() Inserta una subcadena en la posición especificada
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 21 de 57
Devuelve la posición de una subcadena buscada dentro de una
INSTR()
cadena.
LCASE() Sinónimo para LOWER()
Devuelve el número de caracteres indicado desde la izq. de una
LEFT()
cadena.
LENGTH() Devuelve el tamaño de una cadena en bytes.
Exactamente igual que INSTR(), pero permite elegir la posición desde
LOCATE()
donde buscar.
LOWER() Devuelve la cadena en minúsculas
Devuelve la cadena pasada, rellenada por la izquierda con el
LPAD()
argumento dado.
LTRIM() Elimina los espacios a la izquierda de una cadena.
MATCH Perform full-text search
MID() Devuelve una subcadena desde las posiciones dadas.
REPEAT() Repite una cadena un número de veces especificado.
REPLACE() Reemplaza las ocurrencias de una cadena indicada.
REVERSE() Da la vuelta a los caracteres de una cadena.
RIGHT() Devuelve los caracteres indicados, contados desde la derecha.
RLIKE Whether string matches regular expression
RPAD() Rellena una cadena con otra al final un número de veces.
RTRIM() Elimina espacios al final de una cadena.
SOUNDS LIKE Compara cómo suenan dos cadenas
SPACE() Devuelve un número de espacios determinado.
STRCMP() Compara dos cadenas.
SUBSTR() Devuelve la subcadena especificada entre dos posiciones
SUBSTRING() Idem a la anterior
TRIM() Elimina espacios al principio y al final
UCASE() Devuelve la cadena en mayúsculas.
UPPER() Ídem a la anterior
11Restricciones de atributos
A la hora de crear una tabla, una vez especificado el tipo de un atributo, podemos
especificar restricciones para modelar el comportamiento de estos atributos.
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 22 de 57
11.1Aceptar Nulos
Si deseamos que un atributo acepte valores nulos, no será necesario hacer nada, pues es
el comportamiento por defecto de MySQL. En caso contrario, habrá que indicar la restricción
NOT NULL. Ej.: marcacoche VARCHAR(22) NOT NULL
11.2Claves primarias
Una clave primaria se indica tras la definición de su atributo mediante la restricción
PRIMARY KEY. Si deseamos claves primarias de más de un atributo, habrá que crearlas en
las restricciones de tabla y no en las de atributo. Ej: idcoche INT PRIMARY KEY. La
restricción PRIMARY KEY ya lleva implícito lo siguiente: no acepta nulos, debe ser único y
se creará un índice asociado.
Si la clave primaria consta de más de un atributo (clave compuesta) habrá que indicarlo en
las restricciones de tabla, de la siguiente manera:
CREATE TABLE nombretabla (atributo1 tipo1,
atributo2 tipo2,
primary key (atributo1,atributo2)
);
11.3Claves ajenas
Para crear una clave ajena, podría pensarse que basta añadir la palabra foreign key tras el
atributo declarado, pero esto no funciona. De hecho, MariaDB acepta la palabra
REFERENCES tras un atributo en las sentencias ALTER TABLE and CREATE TABLE
statements, pero no hace absolutamente nada. Para poder crear una clave ajena, es
necesario especificarlo al final de la especificación de tabla:
CREATE TABLE nombretabla(
columna1 tipo1 restricciones,
columna2 tipo2 restricciones,
FOREIGN KEY (columna2) REFERENCES nombreotratabla(atributo)
)
Con esto se crea una restricción nombrada (CONSTRAINT), aunque el nombre lo establece
MariaDB automáticamente.
En otros SGBD comerciales sí es posible especificar una clave ajena en la propia
especificación del atributo.
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 23 de 57
11.4Integridad referencial
Cuando se incluyen claves ajenas, lo correcto es indicar qué va a suceder con los registros
de esa tabla relacionados cuando se borre la clave principal de la que dependan en la otra
tabla. Esto lo vamos a indicar con las palabras ON DELETE/ON UPDATE, tras lo cual
indicaremos: CASCADE (cascada), NO ACTION (restringido), SET NULL (puesta a nulos) y
SET DEFAULT (puesta a valor por defecto.
CREATE TABLE nombretabla(
columna1 tipo1 restricciones,
columna2 tipo2 restricciones,
FOREIGN KEY (columna2) REFERENCES nombreotratabla(atributo) ON
DELETE CASCADE
)
11.5Valor por defecto
Si deseamos establecer un valor por defecto para un atributo, basta incluir la palabra clave
DEFAULT y luego el valor que queramos darle para los nuevos registros que se creen sin
especificar ningún valor para este atributo en concreto. El tipo del valor por defecto debe
coincidir con el tipo que hemos declarado al crear el atributo.
CREATE TABLE personas(
id int primary key,
nombre varchar(22),
num_hijos INT DEFAULT 0
)
11.6Claves alternativas
Si queremos forzar a que un atributo tenga valores únicos (clave alternativa), usaremos la
palabra reservada UNIQUE tras la definición del atributo.
UNIQUE KEY además crea un índice para el atributo (agiliza las posibles búsquedas que se
hagan en el futuro.
12Modificación de la estructura de una tabla
En esta sección vamos a ver cómo podemos modificar tablas y sus atributos una vez éstos
ya fueron creados mediante CREATE TABLE. Para realizar algún cambio sobre una tabla
una vez creada, vamos a usar con frecuencia la sentencia ALTER TABLE
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 24 de 57
12.1Renombrar una tabla
RENAME TABLE mitabla TO nombrenuevo;
o también sería posible hacerlo así:
ALTER TABLE mitabla RENAME nombrenuevo;
12.2Borrado de una tabla completa
DROP TABLE nombretabla;
12.3Añadir un atributo a una tabla existente
ALTER TABLE mitabla ADD COLUMN nombre tipo restricciones;
Ej.: ALTER TABLE personas ADD COLUMN nombre varchar(20) NOT NULL;
12.4Borrar un atributo
ALTER TABLE mitabla DROP COLUMN nombre;
12.5Modificar un atributo sin cambiar su nombre
ALTER TABLE mitabla MODIFY nombreatributo TIPONUEVO NUEVASRESTRICCIONES;
Ejemplo: ALTER TABLE PERSONAS MODIFY COLUMN CODIGOVENDEDOR
VARCHAR(10) NULL UNIQUE;
→ Funciona con y sin column. Es obligatorio establecer el tipo, aunque sea el mismo que ya
tenía.
12.6Cambiar el nombre a un atributo/columna
ALTER TABLE PERSONAS CHANGE COLUMN CODIGO_VENDEDOR
CODIGOVENDEDOR VARCHAR(10);
ALTER TABLE PERSONAS CHANGE COLUMN CODIGO_VENDEDOR
CODIGOVENDEDOR VARCHAR(10);
→ Funciona con y sin column. Es obligatorio establecer el tipo, aunque sea el mismo que ya
tenía.
Si únicamente queremos modificar el nombre únicamente, podríamos también hacer lo
siguiente:
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 25 de 57
ALTER TABLE PERSONAS RENAME COLUMN FECHA_NACMIENTO TO FNAC;
12.77. Incluir claves ajenas una vez creada una tabla
Basta hacer:
ALTER TABLE nombretabla ADD FOREIGN KEY (nombreatributo) REFERENCES
NombreTabla(nombreatrib);
12.8Borrar una clave ajena una vez creada
Para hacer esto, debemos primero mostrar la sentencia de creación de tabla mediante la
ejecución de: SHOW CREATE TABLE nombretabla;
Esta sentencia nos dará en su resultado las sentencias de creación de tabla de la tabla que
queremos modificar. La restricción de clave ajena deseada se mostrará con un nombre
determinado que MariaDB puso automáticamente. Este nombre es el que hay que utilizar
para borrar:
ALTER TABLE nombretabla DROP FOREIGN KEY `nombreClaveajena`;
13DML: manipulación de registros en una tabla
13.1Inserción de un registro nuevo
Una vez esté preparado el esquema de nuestra BD, es el turno de crear nuevos registros,
para ello se usa la instrucción INSERT INTO seguida de los nombres de los atributos que
queremos asignar entre paréntesis y separados por comas. Después irá la palabra clave
VALUES seguida de los valores para dichos atributos entre paréntesis y separados por
comas. Debemos recordar que los valores literales para las cadenas deben ir indicados con
comilla simple.+-
INSERT INTO TIPOINMUEBLES (NOMBRE)
VALUES ('Vivienda');
No es necesario indicar todos los atributos, si no los indicamos, MySQL establecerá para
ellos el valor por [Link] por defecto no es lo mismo que valor NULO. En el caso de
los enteros, el valor por defecto es 0.
13.2Inserción de varios registros de una sola vez
Es posible insertar varias tuplas en una sola sentencia poniendo sus valores seguidos, de la
siguiente manera:
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 26 de 57
INSERT INTO TIPOINMUEBLES (NOMBRE)
VALUES
('Vivienda'),
('Finca'),
('Garaje');
13.3Eliminación de todos los registros
Para vaciar una tabla, eliminando sus datos y dejando la estructura tal cual, basta usar la
sentencia DELETE FROM seguida del nombre de la tabla que queramos vaciar.
DELETE FROM PISOS;
Un sinónimo para la orden anterior también es:
TRUNCATE NOMBRETABLA;
13.4Eliminación de determinados registros
Si queremos borrar un sólo registro habrá que hacer uso de la sentencia anterior seguida de
la palabra clave WHERE. Tras el WHERE habrá que poner entre paréntesis una condición
que cumplirá(n) el/los registro(s) que se desea eliminar.
Lo más cómodo para eliminar un registro y asegurarse de que sea sólo uno es poner su
valor para la clave principal en el WHERE.
DELETE FROM inmuebles WHERE (idimueble=2); /*Estamos seguros de que sólo
borrará un registro*/
DELETE FROM inmuebles WHERE (propietario=6); /*Borra todos aquellos inmuebles
cuyo propietario es el número 6*/
13.5Actualización de todos los registros de una tabla
Para actualizar registros, la sentencia es UPDATE. Su sintaxis es la siguiente:
UPDATE inmuebles SET (propietario=6); /*Pone todos los inmuebles a nombre del
propietario 6*/
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 27 de 57
13.6Actualización de determinados registros
Para actualizar sólo algún registro, usaremos la cláusula WHERE igual que en el ejemplo
visto en la sección del DELETE.
UPDATE COCHES SET DADO_DE_BAJA=FALSE WHERE IDCOCHE=3;
/*Pone el valor falso al atributo Dado_de_baja para el coche número 3*/
Es posible actualizar varios atributos en en una sola sentencia, separando las asignaciones
por comas:
UPDATE `clientes SET nombre = 'Homer Simpson', direccion = 'Evergreen Terrace
123' WHERE idcliente = 2; /*Realiza varias modificaciones para el cliente 2*/
14DQL: Consultas simples SQL
Como sabemos, las consultas SQL forman parte del SQL DQL (Lenguaje de Consulta de
Datos). Bien es cierto que muchos autores incluyen a la sentencia SELECT dentro de la
categoría DML (Data Manipulation Language). En cualquier caso, las consultas SQL se
realizan a través de la sentencia SELECT, quizás la sentencia más importante del lenguaje.
Su extraordinaria potencia nos permite obtener conjuntos de datos muy completos de una
manera relativamente intuitiva y sencilla.
Para realizar una consulta SELECT, obviamente, partimos de la base de que se tiene una
base de datos, y al menos una tabla (o varias) con un conjunto de datos que queremos
obtener. La sintaxis básica es:
SELECT [ ALL / DISTINCT ] [ * ] / [ListaColumnas_Expresiones] AS
[Expresion]
FROM Nombre_Tabla_o_Vista
WHERE Condiciones
ORDER BY ListaColumnas [ ASC / DESC ]
14.1Cláusula SELECT
SELECT conjunto_de_atributos FROM nombretabla;
Como vemos, hemos de indicar, al menos, los atributos que queremos obtener y el nombre
de la tabla de la que se desean obtener.
El conjunto de atributos debe ir separado por comas y debe corresponder con los nombres
de las columnas de la tabla en cuestión, siendo posible indicar uno, varios o todos los
atributos. No es importante respetar el orden en que fueron declarados los atributos.
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 28 de 57
Si deseamos seleccionar todos los atributos, basta colocar un asterisco (*) en lugar del
conjunto de atributos.
SELECT * FROM nombretabla;
Nos podemos referir a los atributos poniendo el nombre de la tabla seguido de un punto y el
atributo. Esto es útil si tenemos nombres de atributos que puedan llevar a confusión con el
nombre de la tabla o bien si hacemos consultas con varias tablas que tienen atributos con el
mismo nombre, como veremos en secciones más avanzadas de este tutorial.
SELECT [Link] FROM nombretabla;
14.1.1Select junto a campos calculados o literales
MySQL también acepta seleccionar expresiones y/o constantes como:
SELECT 3+2, “Esto es una suma” FROM CLIENTES;
Esto me devuelve una tabla con dos columnas, una con la suma y la otra con la cadena
literal.
14.2Cláusula ALL/DISTINCT
Después de SELECT, ALL especifica que el conjunto de resultados puede incluir filas
duplicadas. Por regla general nunca se utiliza, dado que es la opción por defecto.
SELECT ALL NOMBRE FROM PERSONAS;
/*obtiene los nombres de las personas incluyendo nombres duplicados*/
DISTINCT limita el resultado a filas únicas. Es decir, si al realizar una consulta hay registros
exactamente iguales que aparecen más de una vez, éstos se eliminan (Útil en muchas
ocasiones). Sólo tendrá sentido cuando hagamos una SELECT con un subconjunto de
atributos de la tabla separados por comas, porque si lo que hacemos es SELECT * las
reglas de los SGBD relacionales nos dicen que nunca habrá dos filas iguales.
SELECT DISTINCT [Link] FROM PERSONAS;
/*obtiene los nombres de las personas quitando nombres duplicados*/
14.3Creación de alias para campos y tablas con AS
La palabra clave AS sirve para crear alias para las columnas y las tablas y así poder
referirnos a ellas sin necesidad de especificar el nombre completo. También es útil para que
aparezca en la parte superior de cada columna un título diferente al nombre del atributo en
cuestión.
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 29 de 57
SELECT EDAD+5 AS EDAD_5 FROM PERSONAS AS P;
Esta consulta pone el nombre EDAD_5 a la columna y llama a la tabla personas P. Esta
cláusula es opcional, ya que SQL también acepta alias sin necesidad de indicar AS:
SELECT [Link] ANIOS FROM PERSONAS P;
/*En esta consulta hemos llamado a la tabla Personas como P y al atributo edad como
ANIOS*/
14.4Cláusula WHERE
Si sólo deseamos obtener registros que cumplan unas determinadas condiciones, es decir,
filtrar los resultados en base a algún criterio; debemos utilizar la cláusula WHERE, que
nos va a servir para especificar las condiciones que debe cumplir el conjunto de registros de
la tabla seleccionada.
SELECT * FROM PERSONAS WHERE (Expresión_lógica)
El funcionamiento de la cláusula WHERE es sencillo: se evalúa la expresión para cada
registro y, en cada caso, el resultado será CIERTO (TRUE) o FALSO (FALSE) (también son
válidos los valores 0 y 1). Si el resultado es cierto, el registro será mostrado, siendo excluido
dicho registro en caso contrario:
SELECT * FROM PERSONAS WHERE EDAD=27;
/*Obtiene los registros de la tabla de personas cuya edad es 27 años*/
Las expresión lógica puede incluir una o varias expresiones unidas por operadores lógicos.
SELECT * FROM PERSONAS WHERE EDAD=27 AND ALTURA>180;
14.5Algunos operadores básicos para crear condiciones
Tipo Categoría Operación Operador Ejemplos
Numéricos Aritméticos Suma + EDAD+3=5;
Resta - CANT-CANT2=3
Multiplicación * NUMPROD*CAT=90
División / METROS/HABITACIONES>=15
exacta
División DIV DOBLE DIV NUM=2
entera
Módulo o % y también PAR MOD 2=0
Resto MOD
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 30 de 57
Comparativos Distinto <> y también != EDAD<>18
Mayor o igual >= EDAD>=18
Menor o igual <= EDAD<=18
Igualdad = EDAD=5
Intervalo BETWEEN EDAD BETWEEN 18 AND 65
(también
funciona con
fechas)
Un valor IN EDAD IN (18,19,20)
dentro de una
secuencia
Cadenas Comparativos Igualdad = y también WHERE NOMBRE=’Laura’
entre cadenas función
STRCMP
Igualdad con LIKE NOMBRE LIKE “Laura”
patrón NOMBRE LIKE “L%”
....
Igual a un IN NOMBRE IN (‘Fernando’, Abel’,
valor dentro ‘Cristian’)
de una
secuencia
Cualquiera Operaciones Comprobar si IS [NOT] NULL
sobre valores un atributo
nulos tiene valor /*con not sería
nulo comprobar si no
es nulo*/
Bit Lógicos Y lógico AND y también
(Boolean) &&
O lógico OR y también ||
No lógico NOT y también !
14.5.1Precedencia de operadores
Para establecer prioridades entre operadores podemos y debemos usar paréntesis, que
darán prioridad a unas operaciones respecto de otras. Si no especificamos nada, las
operaciones aritméticas tienen precedencia respecto de las comparativas y éstas sobre las
lógicas. Ten en cuenta que la Multiplicación, División y el módulo tienen precedencia
respecto de la suma y resta. La operación NOT tiene precedencia por ser UNARIA sobre la
AND. Y ésta última tiene precedencia respecto de OR. En caso de duda, lo mejor es
siempre asegurarse poniendo, como digo, paréntesis a aquellas operaciones que deseamos
sean evaluadas en primer lugar.
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 31 de 57
14.5.2Uso del operador LIKE
Por su especial importancia, este operador merece un apartado dedicado de este tutorial.
Este operador lo vamos a utilizar con cadenas y es especialmente potente para buscar
cadenas que fueron escritas de formas diferentes o con espacios por el medio. Veamos
algunos ejemplos que nos ayuden a comprender su uso. Con LIKE podemos buscar
cadenas que:
● Sean iguales a otra cadena: CADENA LIKE “David”
● Empiezan por una secuencia de caracteres: CADENA LIKE ‘Dav%’. Esto obtiene
las cadenas que comienzan con las letras Dav seguidas de lo que sea.
● Acaban por una secuencia de caracteres: CADENA LIKE ‘%vid’ . Busca cadenas
que acaban por las letras vid.
● Empiezan por una secuencia seguida de un sólo carácter: CADENA LIKE
“PRESIDENT_” . Buscará tanto ‘presidente’ como ‘presidenta’.
● Tienen otra cadena contenida en ella: CADENA LIKE “%SÁNCHEZ%” Busca a
cualquiera que se llame Sánchez, bien de primer o de segundo apellido.
14.6Cláusula ORDER BY
Sirve para establecer el orden en que se mostrarán las del conjunto de resultados. Se
especifica el campo o campos (separados por comas) por los cuales queremos ordenar los
resultados.
SELECT * FROM PERSONAS ORDER BY EDAD DESC;
● ASC / DESC: ASC es el valor predeterminado, la columna indicada en la cláusula
ORDER BY se mostrará ordenada de forma ascendente (de menor a mayor). Si por
el contrario se especifica DESC se ordenará de forma descendente (de mayor a
menor).
14.7Cláusula LIMIT
La cláusula LIMIT sirve para mostrar sólo los N primeros resultados de una consulta. Si lo
combinamos con la cláusula ORDER BY, podemos controlar, en todo momento, qué
resultados se muestran.
Especialmente útil es este operador para obtener el registro que contenga el valor máximo o
mínimo de alguna de las columnas.
SELECT nombre,apellidos from personas ORDER BY edad DESC LIMIT 1;
/*MOSTRARÍA LA PERSONA DE MÁS EDAD*/
Esta cláusula es propia de MySQL, no siendo compatible con otros SGBD, donde
normalmente se utiliza el operador TOP, que va ubicado tras la palabra SELECT. Os
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 32 de 57
muestro un ejemplo que pondré en rojo y tachado, pues esta sintaxis no funciona en MySQL
y sí en otros sistemas como SQL Server.
SELECT TOP 1 nombre,apellidos FROM personas ORDER BY edad DESC;
15Uso de SELECT junto a CREATE e INSERT
15.1Crear una tabla como resultado de una consulta
Si queremos crear una tabla a partir de los registros de una consulta, es sencillo, basta
hacer una sentencia CREATE TABLE seguida de una SELECT. Veamos ejemplos:
CREATE TABLE COCKTAILS2 AS SELECT * FROM COCKTAILS;
/*Crea una tabla como copia exacta de otra anterior, con su misma estructura y registros*/
CREATE TABLE COCKTAILS_ID
(ID INT PRIMARY KEY)
AS SELECT ID FROM COCKTAILS;
/*En esta ocasión se crea una tabla sólo con un atributo de la tabla anterior, se nos permite
indicar las columnas y luego hacer la SELECT, aunque han de coincidir en tipos y nº de
atributos*/
5.2Insertar un conjunto de registros procedentes de una
1
consulta
Si deseamos insertar en una tabla el resultado de una consulta, basta hacer:
INSERT INTO mitabla (col1, col2, …)
SELECT col1, col2 FROM tabla2;
/*Los registros obtenidos de tabla2 quedan insertados en mitabla1*/
El número y tipo de columnas debe ser compatible, de otro modo, obtendremos un error.
6Combinación de varias consultas con
1
operadores de conjuntos
Se pueden combinar varias consultas en una sola utilizando los operadores básicos de
conjuntos.
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 33 de 57
16.1Unión de consultas
La unión de consultas incluye los registros del resultado de una consulta y de otra en la
misma salida. El operador utilizado para ello es UNION. Basta con crear la primera consulta
y poner la segunda precedida del operador UNION:
SELECT campo1 FROM tabla1 UNION SELECT campo2 FROM tabla2;
Importante para usar UNION:
● El resultado se mostrará en una única tabla. Es importante que el número de
columnas devueltas por ambas consultas sea el mismo. En caso contrario
obtendremos un error.
● Puedo unir varias consultas aunque las columnas sean de distintos tipos.
● Puedo unir todas las consultas que quiera.
● Sólo se admite un único ORDER BY en la última de las consultas.
● Si coloco alias a las columnas, optará por el alias que pongamos en la primera de
nuestras consultas.
● Se pueden colocar las distintas consultas entre paréntesis para asegurar su correcta
ejecución, aunque en principio no es estrictamente necesario.
● Sólo debemos colocar un único punto y coma al final.
16.2Intersección de consultas (no soportado por MySQL)
La intersección de consultas incluye sólo aquellos registros que fueron devueltos por ambas
consultas y no por una sola de ellas. Basta con crear la primera consulta y poner la segunda
precedida del operador INTERSECT:
SELECT campo1 FROM tabla1 INTERSECT SELECT campo2 FROM tabla2;
IMPORTANTE: Este operador no está soportado por MySQL y por lo tanto no
podemos utilizarlo, si bien es conveniente que conozcamos este operador por ser
muy habitual en otros SGBD.
El resultado se mostrará en una única tabla. Es importante que el número de columnas
devueltas por ambas consultas sea el mismo. En caso contrario obtendremos un error.
16.3Except/MINUS (No soportado por MySQL)
Existe un tercer operador que obtiene aquellos resultados presentes en la primera consulta
quitando aquellos que estén en la segunda (entendiéndose que éstos últimos están
presentes en ambas consultas). La sintaxis en otros SGBD como Oracle es:
(SELECT campo1 FROM tabla1) MINUS (SELECT campo2 FROM tabla2);
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 34 de 57
En otros SGBD como SQL Server, el operador equivalente es Except:
(SELECT campo1 FROM tabla1) EXCEPT (SELECT campo2 FROM tabla2);
16.4Producto cartesiano
Si en la cláusula FROM de una consulta SELECT ponemos varias tablas separadas por
comas, se obtiene el producto cartesiano de ambas tablas. Esto es, todas las tuplas de la
primera tabla combinadas con todas las tuplas de la segunda (cada 2 en una fila). De esta
manera se consigue una combinación de n x m filas, siendo n las filas de la primera y m las
de la segunda. Ésta es una práctica poco recomendable, pues es muy poco eficiente,
siendo mucho mejor las opciones que veremos en el apartado siguiente de combinaciones
con JOIN.
Ej.: SELECT tabla1.campo1, tabla2.campo1 FROM tabla1,tabla2 WHERE
tabla1.campo3=tabla2.campo5;
17Funciones de agregado
Las funciones de agregación en SQL nos permiten efectuar operaciones sobre un conjunto
de resultados, pero devolviendo un único valor agregado para todos ellos. Es decir, nos
permiten obtener medias, máximos, etc... sobre un conjunto de valores.
Las funciones de agregación básicas que soportan todos los gestores de datos son las
siguientes:
● COUNT: devuelve el número total de filas seleccionadas por la consulta.
○ COUNT(*): cuenta el número total de filas.
○ COUNT(DISTINCT nombrecampo): cuenta el número total de valores
distintos que existe para el campo seleccionado.
Ej.: SELECT COUNT(*) FROM PERSONAS WHERE PADRE_ID IS NULL;
/*Obtiene el número de personas que no tienen padre registrado*/
● MIN: devuelve el valor mínimo del campo que especifiquemos.
○ SELECT MIN(salario) FROM EMPLEADOS;
/*Obtiene el salario más bajo registrado en la tabla*/
Si lo usamos con cadenas, devuelve el primer valor en orden
alfabético.
● MAX: devuelve el valor máximo del campo que especifiquemos.
○ SELECT MAX(edad) FROM EMPLEADOS;
/*Obtiene la edad más longeva registrada en la tabla*/
Si lo usamos con cadenas, devuelve el último valor en orden
alfabético.
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 35 de 57
● SUM: suma los valores del campo que especifiquemos. Sólo se puede utilizar
en columnas numéricas.
○ Select sum(saldo) from cuentas; → Obtiene la suma aritmética de
todas las cuentas de la tabla.
● AVG: devuelve el valor promedio del campo que especifiquemos. Tanto este
operador como el anterior sólo se puede utilizar en columnas numéricas. Si
los usamos con otro tipo de datos, devolverá 0.
Es importante señalar que si hacemos uso de una función de agregado, perdemos la
posibilidad de mostrar los demás datos detallados en filas. Es decir, mediante el uso de una
función de agregado ya damos por sentado que deseamos aplicar un operador a un
conjunto de filas que a partir de ese momento llamaremos GRUPO. En caso de no aplicar
ningún agrupamiento, el resultado se muestra en una única fila. Las funciones de agregado
no pueden combinarse con atributos de las tablas si no las hemos agrupado previamente
según esos atributos.
18Agrupamientos
Un agrupamiento consiste en agrupar todas las filas que tengan el mismo valor para un
campo determinado. Esto es, si decido agrupar por dirección, todas las filas cuya dirección
sea la misma quedarán agrupadas en una sola. A partir de ese momento, lo único que
podré hacer será mostrar el campo por el que hemos agrupado o bien funciones de
agregado, que en esta ocasión será aplicado de forma individual para cada grupo.
Ej.: Select calle,COUNT(*) as total_que_viven_en_esa_calle from clientes group by calle;
Devuelve lo siguiente:
calle total_que_viven_en_esa_calle
Avda. Madrid 12
C/ Molinos 1
C/Avutarda 5
C/ Clavellinas 11
Es posible aplicar varios atributos para agrupar por todos ellos, teniendo en cuenta que
pertenecerán al grupo sólo aquellas tuplas que compartan valores para todos ellos, sin
importar el orden.
18.1Condiciones para grupos
Si deseamos filtrar para obtener sólo determinados grupos, existe una sentencia que nos va
a permitir hacerlo. Tras el GROUP BY, se escribe la sentencia HAVING y a continuación se
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 36 de 57
especifican la(s) condición(es) que deseemos para que los grupos sean sólo aquellos que
queramos obtener.
19Subconsultas
A veces puede ser necesario obtener datos previos para poder realizar una consulta y
dichos datos sólo pueden obtenerse haciendo otra consulta distinta. Para estos casos,
podemos incrustar consultas SQL dentro de otras consultas de tal manera que se haga todo
en una sola instrucción. Las subconsultas son una técnica efectiva para resolver
determinados problemas, si bien no conviene abusar de ellas pues computacionalmente son
bastante exigentes y por lo tanto su rendimiento es peor que otras técnicas como el cruce
de tablas mediante JOIN que veremos más adelante.
Lugares donde puede usarse una subconsulta:
19.1Subconsultas en WHERE
Esta es la forma más habitual de utilizar una subconsulta, la usamos en alguna de las
condiciones del WHERE, precedida de una columna y un operando que suele ser de
comparación o bien de existencia en un conjunto como IN.
Ej.: SELECT * FROM PERSONAS WHERE EDAD>(SELECT AVG(EDAD) FROM
PERSONAS);
/*Obtiene a aquellas personas con edades superiores a la media de la tabla*/
● Para poder utilizar subconsultas en el WHERE, es necesario que la subconsulta
devuelva EXACTAMENTE una sola columna y que dicha columna sea de un tipo de
datos compatible con el que estamos comparando.
● También es necesario que dicha consulta devuelva una única fila, pues en cualquier
otro caso podemos estar comparando con otro valor distinto al esperado. Si se
devuelve más de una fila y queremos que la condición sea posible para cada uno de
los valores devueltos, usaremos IN, ALL o ANY
19.1.1Operador de pertenencia IN
En este caso, la subconsulta me va a devolver más de un valor y el operador IN me
permite comparar una columna de mi tabla/consulta con todos y cada uno de los valores
devueltos. En este caso la condición de IN será cierta cuando se cumpla que el valor
comparado sea igual a cualquiera de ellos (bastando uno).
Ej.:
SELECT *
FROM PERSONAS
WHERE ID IN
(SELECT COMPRADOR_ID FROM VENTAS);
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 37 de 57
/*OBTIENE AQUELLAS PERSONAS QUE HAN COMPRADO ALGUNA VEZ (CUYO ID
APARECE COMO EL COMPRADOR DE ALGUNA VENTA)*/
19.1.2Operadores de cuantificación ALL y ANY
Para aquellas consultas que nos devuelvan más de un resultado, podemos obligar a que la
condición se cumpla para todos ellos (ALL) o bien para alguno de ellos (ANY).
SELECT *
FROM PERSONAS
WHERE
EDAD <= ALL(SELECT EDAD FROM PERSONAS);
/*Obtiene personas cuya edad sea inferior o igual a la de todas las de la tabla, en la práctica
se obtiene así la persona de menos edad de la misma*/
ELECT *
S
FROM PERSONAS
WHERE
EDAD > ANY(SELECT EDAD FROM PERSONAS);
/*Obtiene personas cuya edad SUPERIOR A ALGUNA de todas las de la tabla, en la
práctica se obtiene así a todas las personas de la tabla a excepción de la de menor edad*/
19.1.3Operador de existencia EXISTS
Este operador es un poco más difícil de entender pues devuelve cierto o falso por sí solo.
No es necesario comparar con nada. En este caso devolverá cierto si la subconsulta que
coloquemos después de él devuelve algún conjunto de registros no vacío. En caso contrario
(cjto. de registros vacío) devolverá falso. Es interesante combinar campos de la consulta
principal para que devuelva cierto o falso para cada uno de los registros individualmente.
Ej.:
SELECT *
FROM PERSONAS P
WHERE EXISTS
(SELECT * FROM VENTAS WHERE COMPRADOR_ID=[Link]);
Esta consulta devuelve lo mismo que el ejemplo visto en el apartado del operador IN. Sin
embargo, es bastante menos eficiente, pues requiere datos de la consulta principal para
poder ejecutarse y, por lo tanto se ejecuta NxN veces, ejecutándose la anterior 2N veces.
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 38 de 57
19.2Subconsultas en el FROM → Tablas derivadas
Es posible incluir una consulta SELECT dentro del from de otra consulta. En este caso, la
subconsulta se llama “Tabla derivada”, pues de ella vamos a obtener los registros de la
consulta principal. No hay muchas restricciones respecto de su uso, a saber:
● Es necesario colocar la subconsulta entre paréntesis.
● Es necesario darle un nombre a la tabla derivada mediante la instrucción AS o
bien directamente con un alias.
Ejemplo: SELECT [Link] FROM (SELECT * FROM PERSONAS) AS P1;
Como podemos observar, la tabla derivada queda nominalmente llamada P1 y los campos
que queramos obtener de ella deberán ir precedidos de este prefijo.
19.3Subconsultas en el Select
MySQL permite el uso de consultas select dentro de una sentencia SELECT, valga la
redundancia. Los requisitos para poder hacerlo son:
- La subconsulta debe colocarse entre paréntesis ().
- La subconsulta debe devolver una única fila.
- La consulta debe devolver una única columna.
Ejemplo:
SELECT
(SELECT AVG(IMPORTE) FROM VENTAS WHERE PROPIETARIO_ID=1) AS
MEDIA_DE_VENTAS, NOMBRE, APELLIDOS
FROM PERSONAS WHERE ID=2;
Evidentemente, esta práctica no es muy habitual y no suele ser necesario incluir
subconsultas dentro de un SELECT, pues siempre habrá opciones mucho más eficientes e
interesantes como las que vamos a explorar en la siguiente sección.
20Consultas multitabla
Cuando tenemos varias tablas relacionadas mediante claves externas, nos resulta
especialmente interesante obtener datos que pertenezcan a alguno de los registros de una
tabla y que estén en alguna de sus tablas relacionadas. Para ello vamos a utilizar el
operador JOIN
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 39 de 57
20.1Cruzar tablas con INNER JOIN
Inner join es quizás el operador más importante de la sintaxis SQL, pues nos va a servir
para cruzar tablas que tengan claves externas relacionadas. Se utiliza exclusivamente en el
FROM de una consulta SQL para conseguir leer varias tablas y tener los datos organizados
de acuerdo a algún criterio.
Veamos un ejemplo: tenemos una tabla de mascotas con un campo “Duenio” que es una
clave externa que apunta a una tabla “Personas”, que tiene como clave un identificador.
Para mostrar quién es el dueño de cada mascota necesitamos, por lo tanto, leer las dos
tablas. La sintaxis es la siguiente:
SELECT * FROM PERSONAS P INNER JOIN MASCOTAS M ON
[Link]=[Link]
Como puedes observar, INNER JOIN tiene dos partes: por un lado las tablas que
necesitamos obtener separadas por la cláusula y después de cada cruce hay que poner la
sentencia ON seguida de la condición que debe darse para organizar las filas (clave
externa=clave principal). El resultado es, cada fila de la tabla de personas seguida de una
mascota que sea de su propiedad. Si tuviera más de una, veríamos a esa persona
duplicada, triplicada, etc. en función del número de mascotas que tenga. En resumen,
tendremos tantas filas como combinaciones haya entre personas y mascotas.
IdPersona Nombre IdMascota Nombre Tipo Duenio
1 Paco 1 Boby Perro 1
1 Paco 2 Toly Perro 1
2 Sara 3 Polvorón Gata 3
Como podemos observar, se coloca cada registro de la tabla de mascotas al lado del
registro correspondiente de su dueño.
¿Qué pasa con aquellas mascotas que no tienen dueño? Suponemos que el valor del
campo “Duenio” es NULO o bien no existe en la tabla de personas. Simplemente, no
aparecerán en la consulta.
Si deseamos mostrar sólo los nombres de las mascotas seguidas del nombre de su dueño,
será muy sencillo poner los campos en el SELECT, ya que ahora podemos acceder a
ambas tablas:
SELECT [Link],[Link]
FROM PERSONAS P INNER JOIN MASCOTAS M ON [Link]=[Link];
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 40 de 57
20.1.1Varios cruces INNER JOIN en la misma consulta
Si tenemos varias relaciones 1 a N seguidas o bien una N:N con 3 tablas, necesitamos
hacer, como mínimo 2 inner join y así poder leer 3 tablas. Para hacerlo seguiremos la
misma sintaxis de arriba, salvo que ahora habrá que meter entre paréntesis el primer
INNER JOIN junto con su sentencia ON y luego meter entre paréntesis toda la sentencia
FROM.
Vamos a verlo con un ejemplo: obtener el nombre y la localidad de aquellas personas que
tengan inmuebles de más de 90 metros cuadrados.
Pongo los paréntesis enormes para que se note, ponemos, al principio, tantos paréntesis
como INNER JOIN VAYAMOS HACIENDO, luego, vamos cerrando cada uno cuando
termine.
SELECT [Link] AS PROPIETARIO,[Link]
AS LOCALIDAD
FROM ((INMUEBLES I INNER JOIN PERSONAS P ON
I.COMPRADOR_ID=[Link])
INNER JOIN MUNICIPIOS M ON [Link]=P.MUNICIPIO_ID )
WHERE [Link]>90;
20.2Otras variantes de JOIN
Existen también otros tipos de combinaciones que tienen en cuenta aquellos registros que
no tengan datos relacionados en otras tablas.
20.2.1LEFT JOIN
En este caso, se hace un inner join tal y como lo hemos explicado anteriormente. Sin
embargo, aquellos registros de la primera tabla que no tengan ningún registro
relacionado en la segunda, aparecerán en la consulta una sola vez y con el resto de
campos a NULL. En la consulta anterior, que combina las tablas de Personas y Mascotas, si
suponemos que existe una persona llamada Antonio que no tiene ninguna mascota,
haciendo left Join aparecería así:
SELECT * FROM PERSONAS P LEFT JOIN MASCOTAS M ON [Link]=[Link]
IdPersona Nombre IdMascota Nombre Tipo Duenio
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 41 de 57
1 Paco 1 Boby Perro 1
1 Paco 2 Toly Perro 1
2 Sara 3 Polvorón Gata 3
3 Antonio NULL NULL NULL NULL
Visto esto, parece sencillo obtener sólo aquellas personas que no tienen mascotas a su
cargo, basta con :
SELECT P.*
FROM PERSONAS LEFT JOIN MASCOTAS M ON [Link]=[Link]
WHERE [Link] IS NULL;
20.2.2RIGHT JOIN
En este caso, se hace un inner join tal y como lo hemos explicado anteriormente. Aquellos
registros de la segunda tabla que no tengan ningún registro relacionado en la primera,
aparecerán en la consulta una sola vez y con el resto de campos a NULL. En la consulta
anterior, que combina las tablas de Personas y Mascotas, suponiendo que existe una
mascota llamada “Duque” que no tiene ningún dueño, haciendo right join aparecería así:
SELECT * FROM PERSONAS P RIGHT JOIN MASCOTAS M ON [Link]=[Link]
IdPersona Nombre IdMascota Nombre Tipo Duenio
1 Paco 1 Boby Perro 1
1 Paco 2 Toly Perro 1
2 Sara 3 Polvorón Gata 3
NULL NULL 4 Duque Perro NULL
20.2.3FULL OUTER JOIN (No funciona en MySQL)
En algunos SGBD disponemos de la instrucción FULL OUTER JOIN (no siendo así en
MySQL). Esta instrucción permite el uso de una instrucción ON que une dos tablas, uniendo
los resultados de un INNER JOIN junto con un RIGHT JOIN y un LEFT JOIN. Esto es,
aparecerán en esta consulta los resultados de ambas tablas que no tengan registros
relacionados en la otra, rellenando las columnas con nulos. El ejemplo anterior arrojaría los
siguientes resultados:
SELECT * FROM PERSONAS P FULL OUTER JOIN MASCOTAS M ON [Link]=[Link];
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 42 de 57
(ten en cuenta que esta sentencia no funciona en MySQL y será necesario emularla
mediante la UNION de un RIGHT JOIN y un LEFT JOIN).
[Link] Nombre [Link] Nombre Tipo Duenio
1 Paco 1 Boby Perro 1
1 Paco 2 Toly Perro 1
2 Sara 3 Polvorón Gata 3
NULL NULL 4 Duque Perro NULL
4 Antonio NULL NULL NULL NULL
20.2.4CROSS JOIN
Cross Join, a diferencia del resto, no requiere ninguna condición en el ON y realiza el
producto cartesiano de todas las filas de una tabla junto a las de la otra, obteniendo N x N
filas en total.
SELECT * FROM PERSONAS CROSS JOIN MASCOTAS;
[Link] Nombre [Link] Nombre Tipo Duenio
1 Paco 1 Boby Perro 1
1 Paco 2 Toly Perro 1
1 Paco 3 Polvorón Gata 3
1 Paco 4 Duque Perro NULL
2 Sara 1 Boby Perro 1
2 Sara 2 Toly Perro 1
2 Sara 3 Polvorón Gata 3
2 Sara 4 Duque Perro NULL
4 Antonio 1 Boby Perro 1
4 Antonio 2 Toly Perro 1
4 Antonio 3 Polvorón Gata 3
4 Antonio 4 Duque Perro NULL
Como puede observarse, el resultado arroja las filas combinadas de todas las formas
posibles, sin seguir ninguna lógica. Si agregamos en la condición WHERE que la personas
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 43 de 57
coincidan con el dueño de la mascota, obtendremos los mismos resultados que con INNER
JOIN.
21Estructuras de control dentro de una Consulta
21.1Case...When
Esta estructura es una especie de sentencia CASE tal y como la conocemos de otros
lenguajes de programación. Es una alternativa múltiple en la que dada una condición que se
puede o no cumplir, vamos a devolver un valor sólo si dicha condición se cumple.
Se coloca dentro de la sentencia SELECT como si fuera una columna más en la que, antes
de obtener ningún valor vamos a comprobar si se cumple una determinada condición y
posteriormente devolveremos un valor si se cumple. Podemos colocar tantas condiciones y
valores como deseemos. Al final, podemos colocar una condición ELSE por si no se cumple
ninguna de las condiciones dadas. La sintaxis es la siguiente:
SELECT ...,
CASE [NOMBREDELCASE]
WHEN CONDICION THEN VALOR_DEVUELTO1
[WHEN CONDICION2 THEN VALOR_DEVUELTO2] ...
[ELSE VALOR_SI_NO_SE_CUMPLIO_NADA]
END
FROM ...
Ejemplo:
Obtener el nombre de las personas de una base de datos seguidas de la palabra “Moroso”
si tienen un saldo negativo y la palabra “No moroso” si tienen un saldo positivo:
SELECT P.*,
CASE WHEN ([Link]<0) THEN “MOROSO” ELSE “NO MOROSO” END AS
“¿MOROSO?”
FROM PERSONAS P;
Ejemplo 2: Si combinamos con la potencia de LEFT JOIN es fácil obtener, por ejemplo, las
personas que tienen mascota seguido de la frase “Tiene” y las que no tienen seguido de la
frase “No tiene”:
SELECT DISTINCT [Link], [Link], CASE WHEN ([Link] IS NULL) THEN "NO
TIENE" ELSE "TIENE" END AS TIENE_MASCOTA
FROM
PERSONAS P LEFT JOIN MASCOTAS M ON [Link]=[Link];
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 44 de 57
21.2IF
La estructura de control IF se utiliza para devolver un valor u otro en función de una
condición. La podemos utilizar dentro de una consulta SELECT. No es estándar en SQL y
por lo tanto puede estar presente en algunos SGBD y en otros no. Su sintaxis en MySQL es
la siguiente:
SELECT IF(500<1000, "SI", "NO");
SELECT ID, Fecha, Inmueble, IF(importe>90000, "CARO", "BARATO")
FROM Ventas;
21.3IFNULL
Esta estructura comprueba si el primer parámetro es NULL para posteriormente devolver el
segundo parámetro. En caso de que el primero no sea NULL, devolverá el primer
parámetro.
Ejemplo:
SELECT id, nombre, apellidos, IFNULL(padre_id,”SIN PADRE”) as PADRE from
personas;
22JOIN + GROUP BY
La verdadera dificultad estriba en la combinación de consultas multitabla con JOIN seguida
de algún tipo de agrupamiento para posteriormente sacarle partido a las consultas de
agregado: conteo, media, mínimo, máximo, etc.
Imaginemos que deseamos obtener cada persona de mi base de datos junto al número de
mascotas que posee. Hasta ahora, no podíamos hacer esto, pues sólo podíamos contar
filas completas, pero ahora, dado que podemos agrupar los registros, podemos agrupar por
los datos de la persona y obtener el conteo de filas que están en el grupo. Tenemos que
entender dos cosas:
● El group by se hace tras el WHERE y antes del select.
● Una vez se hace el group by, sólo podemos hacer uso de aquellos campos por los
que hayamos agrupado o bien de funciones de agregado.
● Se puede agrupar por varios campos, los grupos se harán en base a los valores de
todos esos campos. Ej.: si agrupo por ID y por nombre, haré grupos según los
valores de ID y nombre. Dos personas que tengan el mismo nombre no caerán en el
mismo grupo. Sin embargo, si agrupo por nombre sí caerían en el mismo grupo.
● Las funciones de agregado ahora sólo se aplican al grupo y no al conjunto de
filas. Esto quiere decir que si agrupo por nombre e ID de persona y hago uso de la
función COUNT(*) sólo contará las filas que tiene el grupo y no todas las filas y así
sucederá con cualquier función de agregado.
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 45 de 57
Veamos el ejemplo anterior:
SELECT [Link], P. APELLIDOS, COUNT(*) AS NUM_MASCOTAS
FROM PERSONAS P INNER JOIN MASCOTAS M ON [Link]=[Link]
GROUP BY [Link], [Link], [Link];
Como puede observarse, hemos agrupado por ID de persona, de tal manera que nos
garantizamos que, si hay dos personas que se llamen igual, se harían grupos de filas
distintos para ellas. Esto ya nos dejaría a cada persona en un grupo, pero, al agrupar,
perdemos el acceso al resto de datos de las personas. Para poder mostrar el nombre y los
apellidos en el SELECT, es necesario incluirlos en los campos a agrupar, tal y como hemos
hecho.
22.0.1Cláusula HAVING
Ahora es cuando cobra especial importancia la cláusula HAVING, pues se ejecuta
inmediatamente después del agrupamiento y podemos filtrar unos grupos u otros. Si
reflexionamos un poco, el filtrado de algunas características podría hacerse de forma
sencilla:
Obtener el número de mascotas de aquellas personas que viven en Cáceres:
SELECT [Link], [Link], COUNT(*) AS TOTAL_MASCOTAS
FROM PERSONAS P INNER JOIN MASCOTAS M ON [Link]=[Link]
GROUP BY [Link], [Link], P. APELLIDOS, [Link]
HAVING [Link] LIKE ‘CÁCERES’;
Esto no nos aporta gran cosa, pues el filtrado podríamos haberlo realizado de forma previa
en el WHERE de la consulta:
SELECT [Link], [Link], COUNT(*) AS TOTAL_MASCOTAS
FROM PERSONAS P INNER JOIN MASCOTAS M ON [Link]=[Link]
WHERE [Link] LIKE ‘CÁCERES’
GROUP BY [Link], [Link], P. APELLIDOS, [Link];
Lo verdaderamente interesante es que el HAVING se ejecuta después del agrupamiento, y,
por lo tanto, podemos hacer uso de funciones de agregado dentro del HAVING.
En este ejemplo veremos la verdadera potencia de esta estructura: Obtener aquellas
personas que tienen más de una mascota.
SELECT [Link], [Link]
FROM PERSONAS P INNER JOIN MASCOTAS M ON [Link]=[Link]
GROUP BY [Link], [Link], P. APELLIDOS, [Link]
HAVING COUNT(*) > 1;
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 46 de 57
Si hubiésemos hecho algún JOIN que pueda duplicar las filas, al contar contaría las
duplicadas, dando resultados erróneos, aunque este no es el caso anterior. Para
solucionarlo, se utiliza la cláusula DISTINCT dentro del count de la siguiente manera:
SELECT [Link], [Link]
FROM PERSONAS P INNER JOIN MASCOTAS M ON [Link]=[Link]
GROUP BY [Link], [Link], P. APELLIDOS, [Link]
HAVING COUNT(DISTINCT [Link]) > 1;
Como puede observarse, dentro de la función de agregado sí es posible acceder al resto de
atributos de la consulta pese a no haber sido agrupada por ellos.
22.0.2Cláusula ORDER BY
Otra de las posibilidades de usar agrupamientos es que podemos utilizar funciones de
agregado dentro de la cláusula ORDER BY. Esto es posible gracias a que dicha cláusula se
ejecuta con posterioridad a GROUP BY y por lo tanto, podremos ordenar usando funciones
de agregado.
Ej.: Obtener las personas ordenadas según el número de mascotas que tienen (primero las
que más tengan).
SELECT [Link], [Link], COUNT(*) AS NUMMASCOTAS
FROM PERSONAS P INNER JOIN MASCOTAS M ON [Link]=[Link]
GROUP BY [Link], [Link], [Link]
ORDER BY COUNT(*);
Si añadimos la potencia de LIMIT, podemos obtener de forma muy sencilla aquella persona
que más mascotas tiene de la BD:
SELECT [Link], [Link], COUNT(*) AS NUMMASCOTAS
FROM PERSONAS P INNER JOIN MASCOTAS M ON [Link]=[Link]
GROUP BY [Link], [Link], [Link]
ORDER BY COUNT(*) DESC LIMIT 1;
23Creación de vistas
Una vez que sabemos hacer consultas, podemos guardar estas consultas como VISTAS
gracias a la instrucción CREATE VIEW. Ejemplo: Guardar una vista con la consulta que
obtiene las personas con sus mascotas.
CREATE VIEW mivista AS
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 47 de 57
SELECT *
FROM PERSONAS P INNER JOIN MASCOTAS M ON [Link]=[Link]
[WITH CHECK OPTION];
Ejecutada esta instrucción, se crea una vista que me permite a partir de ahora usarla como
si se tratase de una tabla normal y corriente:
SELECT * FROM MIVISTA;
Sobre la vista se pueden hacer inserciones que afectan a la tabla base. Si indicamos la
opción WITH CHECK OPTION, la vista sólo podrá insertar tuplas que luego sean mostradas
en la vista. Ej.: Creamos una vista con los empleados que tienen un sueldo mayor de
1000€. Si la marcamos con CHECK OPTION, sólo podremos insertar en esta vista a
empleados que cumplan dicha condición.
24DCL: Lenguaje de control de datos
24.1Creación y borrado de usuarios
Aunque para probar podemos usar el usuario root, lo lógico es crear un usuario para
conectarse a la Base de Datos/Tabla con la que vayamos a trabajar. Para ello, basta entrar
como root a la consola mysql con los comandos vistos anteriormente y ejecutar:
CREATE USER nombreusuario@localhost IDENTIFIED BY ‘micontraseña’
Para borrar un usuario de mi Base de Datos que ya no vaya a utilizar, basta utilizar la
sentencia DROP USER:
DROP USER nombreusuario@localhost
En este caso debemos respetar las mayúsculas/minúsculas para que el usuario sea
identificado. Recordemos que esto no sucede con los nombres de las bases de datos ni los
de las tablas/columnas, pues en este caso no es sensible a mayúsculas/minúsculas,
aceptando ambas.
Si deseamos en cualquier momento, mostrar los usuarios creados en el servidor:
SELECT USER FROM [Link];
24.2Sentencia GRANT
Esta sentencia sirve para dar privilegios a un usuario para realizar determinadas acciones
sobre una o varias de las bases de datos de un servidor de BD. Dado que los usuarios se
crean a nivel de servidor (pueden conectarse a más de una BD si les damos permiso), los
permisos también se crean a nivel del servidor de BD.
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 48 de 57
Ejemplo → Crear un nuevo usuario llamado ‘hola’ y darle todos los permisos sobre la Base
de Datos llamada ‘inmuebles’.
CREATE USER hola@localhost IDENTIFIED BY 'contraseña';
GRANT ALL PRIVILEGES ON inmuebles.* TO hola@localhost
[WITH GRANT OPTION] → esto último permitiría que el usuario diera permisos a otros
(sólo podría dar como máximo aquellos que él tiene concedidos)
Si deseamos dar sólo permiso para determinadas cosas, tenemos que indicar aquello para
lo que queremos dar permiso al usuario. Vamos a revisar un listado con los permisos más
utilizados, aunque existen muchos más.
Privilege Permiso
ALL [PRIVILEGES] Todos los privilegios
ALTER Hacer ALTER
ALTER ROUTINE Crear procedimientos que hagan ALTER
CREATE Hacer Creates de BDs, tablas o índices
CREATE USER Creación de usuarios
CREATE VIEW Creación de vistas
DELETE Eliminación de registros
DROP Eliminar BD, tablas o vistas
DROP ROLE Drop_role_priv
EXECUTE Ejecución de procedimientos de almacenado
FILE Acceso a ficheros
GRANT OPTION El usuario puede, a su vez, dar permisos a otros.
INSERT Insert
LOCK TABLES Bloquear tablas
SELECT Hacer SELECT
SHOW DATABASES Hacer show de las bases de datos
SHOW VIEW Show de vistas
SHUTDOWN Apagado del servidor
SUPER Privilegios de superadministrador
TRIGGER Creación de triggers
UPDATE Hacer UPDATE
USAGE Sin privilegios, sinónimo para “NO PRIVILEGES”
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 49 de 57
Para ver la fuente de esta tabla y observar detenidamente todos aquellos
permisos/privilegios que podemos configurar en una BD, puedes consultar la
documentación oficial de MySQL aquí.
24.3Establecer un máximo de recursos por cuenta de usuario
Además de lo anterior, se puede establecer el número máximo de operaciones que puede
hacer un usuario mediante las siguientes opciones al crear un usuario:
MAX_QUERIES_PER_HOUR numero → Máximo de consultas por hora
MAX_UPDATE_PER_HOUR numero → Máximo de actualizaciones por hora
MAX_CONNECTIONS_PER_HOUR numero → Máximo de conexiones por hora
MAX_USER_CONNECTIONS numero → Máximo de conexiones simultáneas
Ejemplo: CREATE USER 'francis'@'localhost' IDENTIFIED BY 'frank'
-> WITH MAX_QUERIES_PER_HOUR 20
-> MAX_UPDATES_PER_HOUR 10
-> MAX_CONNECTIONS_PER_HOUR 5
-> MAX_USER_CONNECTIONS 2;
Si tras haber creado un usuario deseamos alterar estos máximos:
ALTER USER 'francis'@'localhost' WITH MAX_CONNECTIONS_PER_HOUR 0;
24.4Sentencia REVOKE
Para quitar algún privilegio, basta ejecutar la sentencia REVOKE. La forma de expresar
sobre qué objetos quitamos los privilegios es igual a la de GRANT, expresandolo en forma
de [Link]. Si ponemos asteriscos, se entiende que es sobre todas
las bases de datos/objetos.
Ejemplo: REVOKE ALTER ON *.* FROM paco@localhost;
**Ojo, se usa con FROM en lugar de con TO
24.5Mostrar privilegios existentes
Para mostrar los permisos que tiene un usuario, basta mostrar toda la información de la
tabla del sistema [Link]:
SELECT * FROM [Link];
Si sólo queremos mostrar los privilegios de un usuario en concreto, podemos usar
SELECT * FROM [Link] WHERE USER LIKE ‘Paco’;
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 50 de 57
25DTL: Lenguaje Control de Transacciones
25.1Transacciones
Una transacción es una unidad lógica de trabajo. Es un concepto que se refiere siempre al
lado servidor y que determina cuánto trabajo ha de realizar un SGBD antes de confirmar
los cambios.
Se diferencia del proceso por lotes, en que éste último es un concepto del lado cliente, y
se refiere a cuántas instrucciones deben ser enviadas al servidor para procesar de una vez.
(ponemos este concepto para ver la diferencia entre ambos).
Las transacciones son siempre necesarias si las consultas o instrucciones que vamos a
lanzar modifican datos. Las transacciones se basan en que deben cumplir con las 4
propiedades ACAD ó ACID (inglés).
25.2Propiedades ACAD
1. Atomicidad: o se realiza todo el trabajo o no se realiza nada. Se consigue con la
política de gestión de transacciones del SGBD. Si no se completa, se deshace.
2. Coherencia: debe dejar los datos en estado coherente. Gracias a la política de
gestión de transacciones y la pericia del programador de la BD, que evitará cambios
que dejen la BD en estado incoherente.
3. Aislamiento: Si hay transacciones simultáneas, los cambios que hagan unas no
deben ser vistos por las otras. Los cambios se podrán observar cuando concluyan
dichas transacciones, pero nunca en estado intermedio. Se consigue con servicios
de bloqueo que preservan datos.
4. Durabilidad: concluida una transacción, sus efectos permanecen aún en caso de
error. Se consigue con servicios de registro de transacciones.
25.3Gestión de transacciones en MySQL
○ START TRANSACTION: Es la manera de comenzar una transacción en MySQL.
También se puede poner BEGIN; o BEGIN WORK; (alias). inicio de transacción. La
transacción permanece abierta en el sistema mientras no se envíe un COMMIT o un
ROLLBACK. También se confirma si abrimos una nueva transacción.
○ COMMIT o COMMIT WORK: confirma los cambios realizados por una transacción.
Siempre se aplica a la últ. instr. START TRANSACTION anidada.
Ej.: START TRANSACTION;
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 51 de 57
INSERT INTO PERSONAS (nombre, apellidos, dni,municipio_id) VALUES
('Francisca','Giralda','9999999V',1);
COMMIT;
START TRANSACTION;
INSERT INTO PERSONAS (nombre, apellidos, dni,municipio_id) VALUES
('Francisco','Giraldo','9999998V',1);
SELECT * FROM PERSONAS;
ROLLBACK;
SELECT * FROM PERSONAS;
○ SAVEPOINT punto_guardado: Establece un punto de guardado. Guarda parte de una
transacción. Si después se hace ROLLBACK TO SAVEPOINT punto_guardado, se
deshace la hasta ese punto de guardado parcial. Un COMMIT o un ROLLBACK sin
nombre eliminan todos los puntos de guardado hasta ese momento.
Ejemplo:
START TRANSACTION /*indica el inicio de una transacción*/
INSERT INTO PERSONAS (nombre, apellidos, dni) VALUES (‘Francisca’,’Giralda’,’9999999V’);
SAVEPOINT paca_insertado;
DELETE FROM PERSONAS WHERE Dni LIKE ‘9999999V’;
ROLLBACK TO SAVEPOINT paca_insertado; /*Devuelve las tablas al estado del punto de guardado*/
SELECT * FROM PERSONAS; /*En este select obtenemos a Francisca Giralda*/
○ ROLLBACK o ROLLBACK WORK: deshace los cambios realizados por la última
transacción abierta. La Base de Datos, por lo tanto, vuelve al estado previo (las
variables modificadas durante la transacción NO RECUPERAN SU VALOR).
Ejemplo:
SELECT @A:=MIN([Link]) FROM VENTAS V;
START TRANSACTION;
SELECT @A:=MAX([Link]) FROM VENTAS V;
UPDATE VENTAS V SET IMPORTE=@A;
ROLLBACK;
SELECT @A;
SELECT * FROM VENTAS;
/*LA TABLA DE VENTAS NO SE VERÁ MODIFICADA GRACIAS A ROLLBACK, SIN EMBARGO LA
VARIABLE DECLARADA COMO @A, MANTENDRÁ SU ÚLTIMO VALOR ASIGNADO ANTES DEL
ROLLBACK*/
○ Procedimientos con transacciones:
■ Si se le llama desde transacción externa, y dentro del procedimiento se abre una
transacción, la transacción externa es ignorada, pues se confirman o deshacen
sus acciones en el COMMIT o ROLLBACK del procedimiento.
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 52 de 57
■ Si se llama en ausencia de transacción, el proced. confirma o revierte cambios al
final del mismo.
25.4Formas de trabajar con transacciones en MySQL
● Confirmación automática (autocommit): Cada instr. individual es 1 transacción. Por
defecto, MySQL trata cada instrucción de forma independiente como si fuera una
transacción, una vez realizada, ésta se deshace o se confirma. Este es el
comportamiento por defecto y puede ser establecido con la instrucción: SET autocommit
= 1;
start transaction; -- esto se encarga mysql de hacerlo
update personas set saldo=1.15;
commit; -- también se encarga de confirmar la transacción
● Transacciones explícitas: se indican manualmente con las instrucciones START
TRANSACTION...COMMIT o ROLLBACK.
start transaction; -- lo pongo yo
update personas set saldo=1.15;
rollback; -- lo escribo yo
● Transacciones implícitas: Si el modo autocommit está deshabilitado, la sesión siempre
tiene una transacción abierta. Una sentencia COMMIT o ROLLBACK finaliza la
transacción actual y comienza una nueva. Por eso se habla de transacción implícita,
porque cada vez que acaba 1 transacción, se inicia implícitamente una nueva. Este modo
se activa mediante instrucción con SET autocommit=0.
-- cada vez que abro sesión mysql hace automáticamente START TRANSACTION
update personas set saldo=1.15;
commit;
-- confirmamos los cambios y AUTOMATICAMENTE mysql vuelve a hacer START
TRANSACTION
update personas set saldo=1.15;
ROLLBACK;
-- deshacmos los cambios y AUTOMATICAMENTE mysql vuelve a hacer START
TRANSACTION
25.5Aislamiento de transacciones
Tenemos que tener en cuenta que un SGBD de mediana entidad va a necesitar realizar
múltiples operaciones al mismo tiempo que vienen desde orígenes o clientes distintos.
Tenemos que asegurar que, pase lo que pase, la Base de Datos siempre queda en un
estado coherente. Para ello, las transacciones nos permiten establecer niveles de
aislamiento.
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 53 de 57
● Seriabilidad: propiedad por la que el estado de una Base de Datos obtenido tras la
ejecución de varias transacciones simultáneas es equivalente al obtenido si dichas
transacciones se ejecutan en serie en cualquier orden.
● Niveles de aislamiento: nivel en que una transacción está preparada para aceptar
datos incoherentes. Si una transacción tiene un bajo nivel de aislamiento, eso
significa que pueden ejecutarse otras transacciones a la vez (alta simultaneidad),
pero todo ello a costa de tener que corregir errores constantemente (alta corrección
de los datos). Un nivel alto de aislamiento asegura que los datos son correctos, pero
afecta negativamente a la simultaneidad.
25.5.1Problemas derivados de la simultaneidad
START TRANSACTION;
UPDATE PERSONAS SET SUELDO=SUELDO*1.15 WHERE NOMBRE LIKE ‘JORDY’
AND APELLIDOS LIKE ‘BERIGUETE’;
COMMIT;
START TRANSACTION;
UPDATE PERSONAS SET SUELDO=SUELDO*1.05 WHERE NOMBRE LIKE ‘JORDY’
AND APELLIDOS LIKE ‘BERIGUETE’;
COMMIT;
○ ACTUALIZACIÓN PERDIDA: si dos usuarios modifican un dato al mismo tiempo, sólo
1 de las modificaciones quedará reflejada al final.
○ LECTURA NO CONFIRMADA O DEPENDENCIA NO CONFIRMADA: ocurre cuando
una transacción “A” lee un dato modificado por otra transacción “B” que no ha
terminado aún (el cambio no ha sido confirmado). La primera transacción “A” puede
acabar haciendo operaciones con datos incorrectos o que no existen.
PATRICIA: DAVID:
START TRANSACTION …..
UPDATE PERSONAS SET SUELDO=1.15; …..
IF …… START TRANSACTION
THEN…… UPDATE PERSONAS SET
SUELDO=SUELDO*1.05
ROLLBACK;
END IF
○ LECTURA NO REPETIBLE o ANÁLISIS INCOHERENTE: una transacción “A” lee
varias veces una fila y en alguna de las sucesivas lecturas, otra transacción “B” ha
modificado el dato, con lo que 2 lecturas sucesivas dan lugar a distinta información.
SELECT @ID:=ID FROM PERSONAS WHERE
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 54 de 57
NOMBRE LIKE ‘JOSE ANTONIO’;
IF … UPDATE PERSONAS SET ID=27
WHERE NOMBRE=’JOSE ANTONIO’
AND APELLIDOS=’ORTIZ’
(1,’JOSE ANTONIO’,’ORTIZ’)
WHILE ….
UPDATE PERSONAS SET SUELDO=SUELDO*1.05 WHERE ID=@ID;
○ LECTURA FANTASMA: Mientras una transacción “A” actualiza un grupo de registros,
otra transacción “B” inserta un registro que encaja en el grupo. Si la transacción vuelve
a leer el conjunto de registros, verá un “registro fantasma”.
SELECT @ID:=ID FROM PERSONAS WHERE
NOMBRE LIKE ‘JOSE ANTONIO’;
IF … INSERT INTO PERSONAS VALUES
(11,’JOSE ANTONIO’, ‘SUAREZ’);
WHILE ….
UPDATE PERSONAS SET SUELDO=SUELDO*1.05 WHERE NOMBRE LIKE ‘JOSE
ANTONIO’;
25.5.2Niveles de aislamiento de transacciones
● READ UNCOMMITTED: Las transacciones que se ejecutan en el nivel READ
UNCOMMITTED no emiten bloqueos compartidos para impedir que otras
transacciones modifiquen los datos ya leídos por la transacción actual. Tampoco hay
bloqueos para impedir que la transacción actual lea filas modificadas pero no
confirmadas por otras transacciones.
● READ COMMITED:
○ (1) Una transacción no puede leer datos que hayan sido modificados, pero no
confirmados, por otras transacciones. Esto evita las lecturas de datos
sucios.
○ (2) Sin embargo, otras transacciones SI pueden cambiar datos y confirmar
esos cambios entre cada una de las instrucciones de la transacción actual,
dando como resultado lecturas no repetibles y datos [Link] utilizan
bloqueos compartidos al leer.
● REPEATABLE READ: Las transacciones no pueden leer datos que han sido
modificados pero aún no confirmados por otras transacciones (evita “lectura sucia”) y
también que ninguna otra transacción puede modificar los datos leídos por la
transacción actual hasta que ésta finalice. Los bloqueos de lectura de los datos se
levantan cuando acaba la transacción y no cuando acaba la lectura.
● SERIALIZABLE:
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 55 de 57
○ (1) Las instrucciones no pueden leer datos que hayan sido modificados, pero
aún no confirmados, por otras transacciones (evita lectura sucia).
○ (2) Ninguna otra transacción puede modificar los datos leídos por la
transacción actual hasta que la transacción actual finalice.
○ (3) Otras transacciones no pueden insertar filas nuevas con valores de clave
que pudieran estar incluidos en el intervalo de claves leído por las
instrucciones de la transacción actual hasta que ésta finalice. Esto se
consigue con bloqueos a nivel de intervalos de búsqueda.
Problemas que pueden surgir en el nivel
Nivel de Instrucción Descripción
aislamiento Lectura diferida Lect. no Fantasma
(no confirmada) repetible
Lectura de no READ Si Si Si Se permite leer modificaciones todavía no
confirmadas UNCOMMITTED validadas (“en sucio” o “dirty read”), los datos
leídos serán incoherentes si la transacción se
anula (ROLLBACK).
Lectura de READ No Si Si No se permite la lectura en sucio. Se utilizan
confirmadas COMMITED bloqueos compartidos al leer.
Lectura REPEATABLE No No Si Sólo se aceptan modificaciones validadas. Los
repetible READ bloqueos de lectura de datos se anulan cuando
termina la transacción, no cuando termina la
lectura.
Serializable SERIALIZABLE No No No Datos leídos o modificados por 1 transacción
serán accesibles sólo para ella. Datos que sólo
se lean seguirán siendo accesibles en modo
lectura para las demás transacciones.
25.5.3Establecer niveles de aislamiento
● Las transacciones normalmente han de efectuarse en nivel de aislamiento de
lectura repetible o más alto para impedir que haya actualizaciones perdidas.
● El nivel por defecto de MySQL es LECTURA REPETIBLE.
● Establecer nivel de aislamiento durante una sesión MySQL: SET SESSION
TRANSACTION ISOLATION LEVEL valor_aislamiento
● El nivel de aislamiento no puede cambiarse mientras haya una transacción en
curso.
● Para ver el nivel de aislamiento en cualquier momento: SELECT
@@SESSION.TX_ISOLATION;
25.6Reglas genéricas para el uso de transacciones
Las transacciones, en general, son buenas para aumentar la seguridad, pero son malas
para la concurrencia de acceso a datos. Reduciremos bloqueos y aumentaremos la
eficiencia con estas reglas:
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 56 de 57
○ No devolver resultados dentro de una transacción. Se debe hacer la
recuperación y análisis de los datos siempre fuera.
○ No interactuar con el usuario dentro de una transacción. Si hay error: cerrar
primero la transacción y luego mostrar el mensaje.
○ Usar transacciones lo más cortas posibles y sólo si son necesarias. Es
conveniente abrirla justo cuando se vayan a realizar modificaciones y cerrarla justo
al acabarlas. No deben abrirse durante los análisis preliminares de los datos.
○ Cuando sea posible, hacer uso de niveles más bajos de aislamiento y para
aumentar niveles de simultaneidad.
○ Para minimizar bloqueos, se debe acceder a la menor cantidad de datos posible
durante la transacción.
Bases de Datos - 1º DAM - Tutorial de SQL basado en MySQL Página 57 de 57
También podría gustarte
- Historia y características de SQLAún no hay calificacionesHistoria y características de SQL14 páginas
- Tipos de Archivos en Bases de Datos MDFAún no hay calificacionesTipos de Archivos en Bases de Datos MDF5 páginas
- Comparativa de Sistemas de Bases de DatosAún no hay calificacionesComparativa de Sistemas de Bases de Datos4 páginas
- CursoCurso de PostgreSQL - Versión 1Aún no hay calificacionesCursoCurso de PostgreSQL - Versión 1132 páginas
- Introducción al Lenguaje SQL y ComandosAún no hay calificacionesIntroducción al Lenguaje SQL y Comandos14 páginas
- Introducción al Lenguaje SQL y su HistoriaAún no hay calificacionesIntroducción al Lenguaje SQL y su Historia11 páginas
- Introducción al Lenguaje SQL y Bases de DatosAún no hay calificacionesIntroducción al Lenguaje SQL y Bases de Datos57 páginas
- Herramientas Gráficas para Bases de DatosAún no hay calificacionesHerramientas Gráficas para Bases de Datos9 páginas
- Introducción a SQL Server y su arquitecturaAún no hay calificacionesIntroducción a SQL Server y su arquitectura20 páginas
- Iniciación a SQL Server para DBA OracleAún no hay calificacionesIniciación a SQL Server para DBA Oracle86 páginas
- Introducción a SQL y sus ComandosAún no hay calificacionesIntroducción a SQL y sus Comandos86 páginas
- Términos de SQL Que Los Principiantes Deben Conocer - LearnSQL - EsAún no hay calificacionesTérminos de SQL Que Los Principiantes Deben Conocer - LearnSQL - Es18 páginas
- Funciones y análisis de DBA y DBMSAún no hay calificacionesFunciones y análisis de DBA y DBMS6 páginas
- Tipos y Ejemplos de DBMS RelacionalesAún no hay calificacionesTipos y Ejemplos de DBMS Relacionales4 páginas
- Curso Básico de PostGIS y PostgreSQLAún no hay calificacionesCurso Básico de PostGIS y PostgreSQL195 páginas
- Introducción a Bases de Datos OracleAún no hay calificacionesIntroducción a Bases de Datos Oracle6 páginas
- Introducción a SQL y sus ComponentesAún no hay calificacionesIntroducción a SQL y sus Componentes9 páginas
- Sistemas de Gestión de Bases de Datos 2023Aún no hay calificacionesSistemas de Gestión de Bases de Datos 202311 páginas
- Sistema Integrado de Gestión de BibliotecasAún no hay calificacionesSistema Integrado de Gestión de Bibliotecas56 páginas
- Comparativa de SGBD: PostgreSQL, MySQL, FirebirdAún no hay calificacionesComparativa de SGBD: PostgreSQL, MySQL, Firebird5 páginas
- SQL-99: Esquemas, Consultas y VistasAún no hay calificacionesSQL-99: Esquemas, Consultas y Vistas30 páginas
- Comparativa de características: MySQL y PostgreSQLAún no hay calificacionesComparativa de características: MySQL y PostgreSQL3 páginas
- Introducción a los DBMS y su funciónAún no hay calificacionesIntroducción a los DBMS y su función27 páginas
- Características de IBM DB2 y LicenciasAún no hay calificacionesCaracterísticas de IBM DB2 y Licencias18 páginas
- Comparativa de Sistemas de Gestión de Bases de DatosAún no hay calificacionesComparativa de Sistemas de Gestión de Bases de Datos2 páginas
- Definición y características de SQLAún no hay calificacionesDefinición y características de SQL3 páginas
- Cronología de Bases de Datos ISAM y CodasylAún no hay calificacionesCronología de Bases de Datos ISAM y Codasyl7 páginas
- Introducción al Lenguaje SQL y su HistoriaAún no hay calificacionesIntroducción al Lenguaje SQL y su Historia7 páginas
- Sistemas de Gestión de Bases de Datos RelacionalesAún no hay calificacionesSistemas de Gestión de Bases de Datos Relacionales19 páginas
- Introducción a SQL para Gestión de DatosAún no hay calificacionesIntroducción a SQL para Gestión de Datos26 páginas
- Formas Normales en Bases de DatosAún no hay calificacionesFormas Normales en Bases de Datos55 páginas
- SQL: Lenguaje y Manipulación de DatosAún no hay calificacionesSQL: Lenguaje y Manipulación de Datos3 páginas
- Guía Completa de SQL y Bases de DatosAún no hay calificacionesGuía Completa de SQL y Bases de Datos4 páginas
- Introducción a SQL y sus ComponentesAún no hay calificacionesIntroducción a SQL y sus Componentes15 páginas
- Introducción a SQL en Gestión de DatosAún no hay calificacionesIntroducción a SQL en Gestión de Datos26 páginas
- Significado y características de SQLAún no hay calificacionesSignificado y características de SQL11 páginas
- Evolución de los Sistemas de Bases de DatosAún no hay calificacionesEvolución de los Sistemas de Bases de Datos2 páginas
- HeidiSQL: Administrador de bases de datosAún no hay calificacionesHeidiSQL: Administrador de bases de datos17 páginas
- Vide02brain - Bases de Datos NosqlAún no hay calificacionesVide02brain - Bases de Datos Nosql8 páginas
- Características Clave de PostgreSQLAún no hay calificacionesCaracterísticas Clave de PostgreSQL18 páginas
- Introducción al Lenguaje SQL en EspañolAún no hay calificacionesIntroducción al Lenguaje SQL en Español12 páginas
- Estructuras y Gestión en Oracle SOBDAún no hay calificacionesEstructuras y Gestión en Oracle SOBD10 páginas
- Límites de la Base de Datos FirebirdAún no hay calificacionesLímites de la Base de Datos Firebird13 páginas
- Perfil Profesional de Rosa DamianiAún no hay calificacionesPerfil Profesional de Rosa Damiani4 páginas
- Conexión a Firebird con Turbo DelphiAún no hay calificacionesConexión a Firebird con Turbo Delphi11 páginas
- Mostrar datos de varias tablas en AccessAún no hay calificacionesMostrar datos de varias tablas en Access248 páginas
- Taller de Sentencias DDL y DML en SQLAún no hay calificacionesTaller de Sentencias DDL y DML en SQL12 páginas
- Gestión de Usuarios en Oracle SQLAún no hay calificacionesGestión de Usuarios en Oracle SQL14 páginas
- Sistema de Gestión para Farmacia ChimúAún no hay calificacionesSistema de Gestión para Farmacia Chimú16 páginas
- Guía de Administración de Sybase SQL Server100% (2)Guía de Administración de Sybase SQL Server352 páginas
- Gestión de usuarios y permisos en MySQLAún no hay calificacionesGestión de usuarios y permisos en MySQL3 páginas
- Base de Datos para Panadería "BIMBO"Aún no hay calificacionesBase de Datos para Panadería "BIMBO"24 páginas
- Diseño Conceptual y Modelo E-R en BDAún no hay calificacionesDiseño Conceptual y Modelo E-R en BD10 páginas
- Tipos de Bases de Datos y CaracterísticasAún no hay calificacionesTipos de Bases de Datos y Características11 páginas
- Clases y Métodos en Programación SQLAún no hay calificacionesClases y Métodos en Programación SQL11 páginas
- Resultados del Examen de Análisis de NegociosAún no hay calificacionesResultados del Examen de Análisis de Negocios4 páginas
- Prevención de Inyección SQL en JavaAún no hay calificacionesPrevención de Inyección SQL en Java4 páginas
- Configuración de Oracle XE y SQL DeveloperAún no hay calificacionesConfiguración de Oracle XE y SQL Developer16 páginas
- Diseño de Bases de Datos RelacionalesAún no hay calificacionesDiseño de Bases de Datos Relacionales72 páginas
- Álgebra Relacional en SQL y MySQLAún no hay calificacionesÁlgebra Relacional en SQL y MySQL4 páginas
- Orden de eventos en Microsoft AccessAún no hay calificacionesOrden de eventos en Microsoft Access20 páginas
- Automatización de Tareas en SQL ServerAún no hay calificacionesAutomatización de Tareas en SQL Server9 páginas
- Aplicación Calzapp para ZapateríasAún no hay calificacionesAplicación Calzapp para Zapaterías10 páginas
- Parte 1 Cabina de Migración de SAP S4HANAAún no hay calificacionesParte 1 Cabina de Migración de SAP S4HANA18 páginas