-- Crear una base de dades
CREATE DATABASE institut
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

-- Seleccionar la base de dades activa
USE institut;

-- Eliminar una base de dades
DROP DATABASE IF EXISTS institut;

CREATE TABLE alumne (
    id_alumne    INT             NOT NULL AUTO_INCREMENT,
    nom          VARCHAR(50)     NOT NULL,
    cognoms      VARCHAR(100)    NOT NULL,
    data_naix    DATE,
    email        VARCHAR(100)    UNIQUE,
    CONSTRAINT pk_alumne PRIMARY KEY (id_alumne)
);

-- Afegir una columna
ALTER TABLE alumne ADD COLUMN telefon VARCHAR(15);

-- Modificar el tipus d'una columna
ALTER TABLE alumne MODIFY COLUMN telefon VARCHAR(20);

-- Eliminar una columna
ALTER TABLE alumne DROP COLUMN telefon;

-- Renomenar una taula
ALTER TABLE alumne RENAME TO estudiant;

DROP TABLE IF EXISTS alumne;

-- Buidar una taula (elimina totes les files, però manté l'estructura)
TRUNCATE TABLE alumne;

CREATE TABLE matricula (
    id_matricula    INT             NOT NULL AUTO_INCREMENT,
    id_alumne       INT             NOT NULL,
    id_modul        INT             NOT NULL,
    curs            YEAR            NOT NULL DEFAULT (YEAR(CURDATE())),
    nota_final      DECIMAL(4,2)    CHECK (nota_final BETWEEN 0 AND 10),

    CONSTRAINT pk_matricula     PRIMARY KEY (id_matricula),
    CONSTRAINT uq_mat_alu_mod   UNIQUE (id_alumne, id_modul, curs),
    CONSTRAINT fk_mat_alumne    FOREIGN KEY (id_alumne)
                                REFERENCES alumne(id_alumne)
                                ON DELETE RESTRICT
                                ON UPDATE CASCADE,
    CONSTRAINT fk_mat_modul     FOREIGN KEY (id_modul)
                                REFERENCES modul(id_modul)
                                ON DELETE RESTRICT
                                ON UPDATE CASCADE
);

SELECT [DISTINCT] llista_columnes | *
FROM   taula [AS àlies]
[JOIN  ...]
[WHERE condició]
[GROUP BY columnes]
[HAVING condició_grups]
[ORDER BY columnes [ASC|DESC]]
[LIMIT n [OFFSET m]];

-- Totes les columnes
SELECT * FROM alumne;

-- Columnes específiques amb àlies
SELECT nom, cognoms AS 'Cognoms complets' FROM alumne;

-- Eliminar duplicats
SELECT DISTINCT curs FROM matricula;

-- Ordenació
SELECT nom, cognoms FROM alumne ORDER BY cognoms ASC, nom ASC;

-- Limitar resultats
SELECT * FROM alumne ORDER BY cognoms LIMIT 10 OFFSET 20;

-- Comparació
SELECT * FROM alumne WHERE id_alumne = 5;

-- Rang
SELECT * FROM matricula WHERE nota_final BETWEEN 5 AND 7;

-- Llista de valors
SELECT * FROM modul WHERE curs IN (1, 2);

-- Patrons de text
SELECT * FROM alumne WHERE cognoms LIKE 'Garcia%';
SELECT * FROM alumne WHERE email LIKE '%@gmail.com';

-- Valors nuls
SELECT * FROM alumne WHERE email IS NULL;
SELECT * FROM alumne WHERE email IS NOT NULL;

-- Operadors lògics
SELECT * FROM matricula WHERE nota_final >= 5 AND curs = 2024;
SELECT * FROM alumne WHERE nom = 'Pere' OR nom = 'Joan';
SELECT * FROM alumne WHERE NOT (nom = 'Pere');

-- Nombre total d'alumnes
SELECT COUNT(*) AS total_alumnes FROM alumne;

-- Nota mitjana per mòdul
SELECT id_modul, AVG(nota_final) AS nota_mitjana
FROM   matricula
WHERE  nota_final IS NOT NULL
GROUP  BY id_modul;

-- Nombre d'alumnes aprovats per mòdul (nota >= 5)
SELECT id_modul, COUNT(*) AS aprovats
FROM   matricula
WHERE  nota_final >= 5
GROUP  BY id_modul
HAVING COUNT(*) > 10
ORDER  BY aprovats DESC;

SELECT a.nom, a.cognoms, m.curs, mo.nom_modul, m.nota_final
FROM   matricula m
       INNER JOIN alumne a  ON m.id_alumne = a.id_alumne
       INNER JOIN modul  mo ON m.id_modul  = mo.id_modul
WHERE  m.curs = 2024
ORDER  BY a.cognoms;

-- Alumnes amb o sense matrícules
SELECT a.nom, a.cognoms, COUNT(m.id_matricula) AS num_matricules
FROM   alumne a
       LEFT JOIN matricula m ON a.id_alumne = m.id_alumne
GROUP  BY a.id_alumne, a.nom, a.cognoms;

SELECT mo.nom_modul, COUNT(m.id_matricula) AS num_alumnes
FROM   matricula m
       RIGHT JOIN modul mo ON m.id_modul = mo.id_modul
GROUP  BY mo.id_modul, mo.nom_modul;

SELECT a.nom, mo.nom_modul
FROM   alumne a CROSS JOIN modul mo;

-- Empleats i el nom del seu supervisor
SELECT e.nom AS empleat, s.nom AS supervisor
FROM   empleat e
       LEFT JOIN empleat s ON e.id_supervisor = s.id_empleat;

-- Alumnes amb nota superior a la mitjana del seu mòdul
SELECT a.nom, a.cognoms, m.nota_final
FROM   matricula m
       INNER JOIN alumne a ON m.id_alumne = a.id_alumne
WHERE  m.nota_final > (
           SELECT AVG(nota_final)
           FROM   matricula
           WHERE  id_modul = m.id_modul
       );

-- Alumnes que mai han suspès (amb NOT IN)
SELECT nom, cognoms
FROM   alumne
WHERE  id_alumne NOT IN (
           SELECT DISTINCT id_alumne
           FROM   matricula
           WHERE  nota_final < 5
       );

-- Mòduls amb més alumnes que la mitjana (amb EXISTS)
SELECT mo.nom_modul
FROM   modul mo
WHERE  EXISTS (
           SELECT 1
           FROM   matricula m
           WHERE  m.id_modul = mo.id_modul
           GROUP  BY m.id_modul
           HAVING COUNT(*) > (SELECT AVG(c) FROM (
               SELECT COUNT(*) AS c FROM matricula GROUP BY id_modul
           ) AS sub)
       );

-- UNION: elimina duplicats
SELECT nom FROM alumne
UNION
SELECT nom FROM professor;

-- UNION ALL: manté duplicats
SELECT nom FROM alumne
UNION ALL
SELECT nom FROM professor;

-- Inserir una fila especificant columnes
INSERT INTO alumne (nom, cognoms, data_naix, email)
VALUES ('Maria', 'Puig Soler', '2005-03-15', 'maria.puig@exemple.cat');

-- Inserir múltiples files
INSERT INTO modul (nom_modul, hores, curs) VALUES
    ('Gestió de bases de dades', 165, 1),
    ('Administració de sistemes', 198, 1),
    ('Xarxes locals', 165, 1);

-- Inserir a partir d'una consulta
INSERT INTO historial_matricula (id_alumne, id_modul, nota_final, any_hist)
SELECT id_alumne, id_modul, nota_final, YEAR(NOW())
FROM   matricula
WHERE  nota_final IS NOT NULL;

-- Actualitzar una fila concreta
UPDATE alumne
SET    email = 'maria.puig.nou@exemple.cat'
WHERE  id_alumne = 12;

-- Actualitzar múltiples files
UPDATE matricula
SET    nota_final = nota_final + 0.5
WHERE  id_modul = 3 AND curs = 2024 AND nota_final IS NOT NULL;

-- Actualitzar amb subconsulta
UPDATE alumne
SET    actiu = FALSE
WHERE  id_alumne NOT IN (
           SELECT DISTINCT id_alumne
           FROM   matricula
           WHERE  curs = YEAR(NOW())
       );

-- Eliminar una fila concreta
DELETE FROM alumne WHERE id_alumne = 12;

-- Eliminar amb condició
DELETE FROM matricula WHERE curs < 2020;

-- Eliminar amb subconsulta
DELETE FROM matricula
WHERE  id_alumne IN (
           SELECT id_alumne FROM alumne WHERE actiu = FALSE
       );

-- Inici de la transacció
START TRANSACTION;

-- Operacions
UPDATE compte SET saldo = saldo - 500 WHERE id_compte = 101;
UPDATE compte SET saldo = saldo + 500 WHERE id_compte = 205;

-- Si tot ha anat bé: confirmar
COMMIT;

-- Si hi ha hagut un error: desfer
ROLLBACK;

START TRANSACTION;

INSERT INTO comanda (id_client, data) VALUES (5, NOW());
SAVEPOINT sp_comanda;

INSERT INTO linia_comanda (id_comanda, id_producte, quantitat) VALUES (LAST_INSERT_ID(), 12, 3);

-- Si falla la línia, desfer només fins al savepoint
ROLLBACK TO sp_comanda;

-- Si tot va bé
COMMIT;

-- Veure el nivell actual
SELECT @@tx_isolation;

-- Canviar el nivell per a la sessió
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Bloqueig compartit (lectura): múltiples transaccions poden llegir simultàniament
SELECT * FROM alumne WHERE id_alumne = 5 LOCK IN SHARE MODE;

-- Bloqueig exclusiu (escriptura): cap altra transacció pot llegir ni escriure
SELECT * FROM alumne WHERE id_alumne = 5 FOR UPDATE;

DELIMITER //

CREATE PROCEDURE exemple_variables()
BEGIN
    -- Declaració de variables locals
    DECLARE v_nom       VARCHAR(50);
    DECLARE v_total     INT DEFAULT 0;
    DECLARE v_nota      DECIMAL(4,2);
    DECLARE v_data      DATE;

    -- Assignació de valors
    SET v_nom = 'Joan';
    SET v_total = v_total + 1;

    -- Assignació amb SELECT...INTO
    SELECT COUNT(*) INTO v_total FROM alumne;

    SELECT CONCAT('Total alumnes: ', v_total) AS resultat;
END //

DELIMITER ;

CALL exemple_variables();

DELIMITER //

CREATE PROCEDURE qualificacio(IN p_nota DECIMAL(4,2), OUT p_qualif VARCHAR(20))
BEGIN
    IF p_nota >= 9 THEN
        SET p_qualif = 'Excel·lent';
    ELSEIF p_nota >= 7 THEN
        SET p_qualif = 'Notable';
    ELSEIF p_nota >= 5 THEN
        SET p_qualif = 'Aprovat';
    ELSE
        SET p_qualif = 'Suspès';
    END IF;
END //

DELIMITER ;

CALL qualificacio(7.5, @resultat);
SELECT @resultat;

DELIMITER //

CREATE PROCEDURE torn_horari(IN p_hora INT, OUT p_torn VARCHAR(10))
BEGIN
    CASE
        WHEN p_hora BETWEEN 8  AND 14 THEN SET p_torn = 'Matí';
        WHEN p_hora BETWEEN 15 AND 21 THEN SET p_torn = 'Tarda';
        ELSE                               SET p_torn = 'Nit';
    END CASE;
END //

DELIMITER ;

DELIMITER //

CREATE PROCEDURE inserir_alumnes_prova(IN p_n INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= p_n DO
        INSERT INTO alumne (nom, cognoms)
        VALUES (CONCAT('Nom_', i), CONCAT('Cognom_', i));
        SET i = i + 1;
    END WHILE;
END //

DELIMITER ;

CALL inserir_alumnes_prova(10);

DELIMITER //

CREATE PROCEDURE exemple_repeat()
BEGIN
    DECLARE v_count INT DEFAULT 0;
    REPEAT
        SET v_count = v_count + 1;
    UNTIL v_count >= 5
    END REPEAT;
    SELECT v_count;
END //

DELIMITER ;

DELIMITER //

CREATE PROCEDURE llistar_alumnes_aprovats(IN p_id_modul INT)
BEGIN
    DECLARE v_nom       VARCHAR(50);
    DECLARE v_cognoms   VARCHAR(100);
    DECLARE v_nota      DECIMAL(4,2);
    DECLARE fi          BOOLEAN DEFAULT FALSE;

    DECLARE cur CURSOR FOR
        SELECT a.nom, a.cognoms, m.nota_final
        FROM   matricula m
               INNER JOIN alumne a ON m.id_alumne = a.id_alumne
        WHERE  m.id_modul = p_id_modul AND m.nota_final >= 5;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET fi = TRUE;

    OPEN cur;
    bucle: LOOP
        FETCH cur INTO v_nom, v_cognoms, v_nota;
        IF fi THEN LEAVE bucle; END IF;
        SELECT CONCAT(v_nom, ' ', v_cognoms, ' - ', v_nota) AS alumne_aprovat;
    END LOOP bucle;
    CLOSE cur;
END //

DELIMITER ;

# Còpia completa d'una base de dades
mysqldump -u root -p institut > /backup/institut_$(date +%Y%m%d).sql

# Còpia de múltiples bases de dades
mysqldump -u root -p --databases institut rrhh > /backup/multi_$(date +%Y%m%d).sql

# Còpia de totes les bases de dades
mysqldump -u root -p --all-databases > /backup/total_$(date +%Y%m%d).sql

# Còpia d'una taula concreta
mysqldump -u root -p institut alumne > /backup/alumne_$(date +%Y%m%d).sql

# Opcions útils:
# --single-transaction   → còpia consistent sense bloquejar (InnoDB)
# --routines             → inclou procediments i funcions
# --triggers             → inclou disparadors
# --events               → inclou esdeveniments planificats
mysqldump -u root -p --single-transaction --routines --triggers institut \
    > /backup/institut_complet_$(date +%Y%m%d).sql

# Restaurar una còpia
mysql -u root -p institut < /backup/institut_20241015.sql

# Crear la BD prèviament si no existeix
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS institut;"
mysql -u root -p institut < /backup/institut_20241015.sql

# Còpia completa
mariabackup --backup --target-dir=/backup/full --user=root --password=secret

# Preparar la còpia per a la restauració
mariabackup --prepare --target-dir=/backup/full

# Restaurar
systemctl stop mariadb
mariabackup --copy-back --target-dir=/backup/full
chown -R mysql:mysql /var/lib/mysql
systemctl start mariadb

SELECT id_alumne, nom, cognoms, email
FROM   alumne
INTO OUTFILE '/tmp/alumnes.csv'
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\n';

LOAD DATA INFILE '/tmp/alumnes.csv'
INTO TABLE alumne
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    (nom, cognoms, email);

# Exportar com a XML
mysql -u root -p --xml -e "SELECT * FROM alumne" institut > alumnes.xml

-- Variables relacionades amb els logs
SHOW VARIABLES LIKE '%log%';

-- Fitxers de log binari existents
SHOW BINARY LOGS;

-- Contingut d'un log binari
SHOW BINLOG EVENTS IN 'mariadb-bin.000001';

# Exemple amb pgloader (des de PostgreSQL a MariaDB)
pgloader postgres://usuari:pass@localhost/bd_origen \
         mysql://root:pass@localhost/bd_destino