Dominando SQL: Consultas Avanzadas, Gestión de Usuarios y Operaciones de Bases de Datos

Ejemplos Prácticos de Consultas SQL y Gestión de Usuarios

Gestión de Usuarios y Permisos

2. Usuarios

A continuación, se detallan los comandos para la creación y asignación de permisos a diferentes tipos de usuarios en la base de datos.

Administrador con todos los permisos

CREATE USER 'admin'@'localhost' IDENTIFIED BY 'admin123';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

Empleado con permisos limitados

CREATE USER 'empleado'@'localhost' IDENTIFIED BY 'empleado123';
GRANT SELECT, DELETE ON Hardware.Ordenador TO 'empleado'@'localhost';

Consultas de Selección (SELECT)

Ejemplos de consultas SQL para la recuperación y filtrado de datos.

3. Ordenadores I5 o I7 entre 500 y 1500€

SELECT * FROM Ordenador WHERE procesador IN ('I5', 'I7') AND precio BETWEEN 500 AND 1500;

4. Top 5 ordenadores más caros con I3 o I5

SELECT * FROM Ordenador WHERE procesador IN ('I3', 'I5')
ORDER BY precio DESC
LIMIT 5;

5. Precio medio, máximo y mínimo de componentes (sin Francia ni Alemania)

SELECT paisFabricacion, AVG(precio), MAX(precio), MIN(precio)
FROM Componente
WHERE paisFabricacion NOT IN ('Francia', 'Alemania')
GROUP BY paisFabricacion;

6. Ordenador más caro con más de 16GB de RAM

SELECT * FROM Ordenador
WHERE memoria > 16
ORDER BY precio DESC
LIMIT 1;

7. Modelo y procesador con algún componente con más de 2 unidades

SELECT DISTINCT modelo, procesador
FROM Ordenador o
JOIN Contiene c ON o.idOrdenador = c.idOrdenador WHERE c.unidades > 2;

8. Unidades totales de componentes fabricados en Japón o Taiwán

SELECT c.idComponente, c.nombre, SUM(co.unidades) AS total
FROM Componente c
JOIN Contiene co ON c.idComponente = co.idComponente WHERE paisFabricacion IN ('Japón', 'Taiwán')
GROUP BY c.idComponente, c.nombre;

9. Ordenadores con precio superior a cualquiera con procesador I7

SELECT * FROM Ordenador
WHERE precio > ANY (SELECT precio FROM Ordenador WHERE procesador = 'I7');

10. Nombre del componente y número de ordenadores que lo contienen (si es más de 1)

SELECT c.nombre, COUNT(DISTINCT co.idOrdenador) AS cantidad
FROM Componente c
JOIN Contiene co ON c.idComponente = co.idComponente
GROUP BY c.idComponente, c.nombre
HAVING COUNT(DISTINCT co.idOrdenador) > 1;

🛠️ DDL (Data Definition Language)

El Lenguaje de Definición de Datos (DDL) se utiliza para definir o modificar la estructura de la base de datos.

Crear tabla

CREATE TABLE nombre_tabla (
  id INT PRIMARY KEY,
  nombre VARCHAR(100) NOT NULL,
  capital VARCHAR(100),
  poblacion BIGINT,
  pib BIGINT
);

Clave Foránea (FOREIGN KEY)

Define una relación entre dos tablas, asegurando la integridad referencial.

FOREIGN KEY (campo) REFERENCES otra_tabla(campo_clave)

Modificar tabla (ALTER TABLE)

Comandos para alterar la estructura de una tabla existente.

Añadir columna

ALTER TABLE tabla ADD nueva_columna tipo;

Eliminar columna

ALTER TABLE tabla DROP COLUMN columna;

Modificar tipo de columna

ALTER TABLE tabla MODIFY COLUMN columna NUEVO_TIPO;

Renombrar tabla

ALTER TABLE tabla RENAME TO nuevo_nombre;

Restricciones (Constraints)

Gestión de restricciones, como claves foráneas.

Añadir restricción de clave foránea

ALTER TABLE tabla ADD CONSTRAINT nombre FOREIGN KEY (campo) REFERENCES tabla_ref(clave);

Eliminar restricción de clave foránea

ALTER TABLE tabla DROP FOREIGN KEY nombre;

✏️ DML (Data Manipulation Language)

El Lenguaje de Manipulación de Datos (DML) se utiliza para gestionar los datos dentro de las tablas.

INSERT (Insertar datos)

INSERT INTO tabla (col1, col2) VALUES ('valor1', 'valor2');

UPDATE (Actualizar datos)

UPDATE tabla SET col = 'nuevo_valor' WHERE condicion;
-- ⚠️ Sin WHERE → cambia todos los registros

Con subconsulta

UPDATE tabla SET col = 'valor' WHERE otra_col = (SELECT MAX(col) FROM tabla);

DELETE (Eliminar datos)

DELETE FROM tabla WHERE condicion;

🧢 DTL (Transacciones)

El Lenguaje de Control de Transacciones (DTL) permite gestionar las transacciones en la base de datos, asegurando la atomicidad, consistencia, aislamiento y durabilidad (ACID).

START TRANSACTION;
UPDATE cuentas SET saldo = saldo - 200 WHERE id = 1;
UPDATE cuentas SET saldo = saldo + 200 WHERE id = 2;
INSERT INTO transferencias (id_origen, id_destino, monto) VALUES (1, 2, 200);
COMMIT;
-- Si algo falla: ROLLBACK;

📊 Índices

Los índices mejoran la velocidad de recuperación de datos en las bases de datos.

Crear índice

CREATE INDEX nombre_indice ON tabla (columna);

Crear índice único

CREATE UNIQUE INDEX nombre_indice ON tabla (columna);