Salta el contingut

2. DDL - Definició de dades

El Llenguatge de Definició de Dades (DDL) de SQL permet definir els objectes de les nostres bases de dades. Amb ell podrem crear bases de dades, taules, claus, etc, així com crear vistes parcials de les taules i gestionar els diferents permisos per a cada usuari o grup d'usuaris, etc.

Nota

Potser varia un poc d'un SGBD a altre (tipus de dades, etc). Nosaltres ens basarem en el MySQL.

2.1. Instruccions sobre bases de dades

  • Per a mostrar les bases de dades que tenim:

    SQL
    1
    SHOW DATABASES;

  • Per a crear una base de dades nova:

    SQL
    1
    CREATE DATABASE nom_base_de_dades;

  • Per a treballar amb una base de dades (que ja estiga creada):

    SQL
    1
    USE nom_base_de_dades;

  • Per a consultar amb quina base de dades estem treballant:

    SQL
    1
    SELECT database();

  • Per a eliminar una base de dades que ja no volem:

    SQL
    1
    DROP DATABASE nom_base_de_dades;

Abans de vore les instruccions sobre les taules, vorem primer els tipus de dades que accepta MySQL per a crear les columnes de les taules.

2.2. Tipus de dades

Alguns dels tipus de dades de MySQL són:

Classificació Tipus de dades Descripció
ENTERS TINYINT Enter entre -127 i 127
BOOL Igual que TINYINT. True=1. False=0
SMALLINT Fins 65.535
MEDIUMINT Fins 8 milions (+-)
INT o INTEGER Fins 4000 milions (+-)
BIGINT Fins 18 trilions (+-)
DECIMALS DECIMAL(m, d)
FLOAT
DOUBLE
REAL
DATA I HORA DATE Guarda dates en any-mes-dia (0000-00-00)
TIME Guarda les hores en hora:minuts:segons (00:00:00). Amb la funció DATE_FORMAT() es pot mostrar en AM i PM.
DATETIME o TIMESTAMP Data i hora. Format: any-mes-dia hora:minuts:segons (0000-00-00 00:00:00).
YEAR 4 dígits
TEXT CHAR(n) Fins 255 caràcters abans de versió 5.03. Fins 65.000 en versions posteriors.
VARCHAR(n) Ocupa menys que CHAR però les consultes són més lentes.
ALTRES BLOB Informació binària (imatges, sons...)

2.3. Instruccions sobre taules

Mostrar informació de les taules existents

SQL
1
2
3
4
SHOW TABLES;                     -- Mostra les taules de la BD en ús
DESCRIBE nom_taula;              -- Mostra els camps d'una taula (o DESC o EXPLAIN)
SHOW CREATE TABLE nom_taula;     -- Mostra el CREATE TABLE de la taula
SHOW INDEX FROM nom_taula;       -- Mostra els índexs d'una taula

Crear/modificar/eliminar les taules

SQL
1
2
3
4
CREATE TABLE nom_taula ...             -- Crear una taula
CREATE TEMPORARY TABLE nom_taula ...   -- Crear una taula temporal
ALTER TABLE nom_taula ...              -- Modificar estructura d'una taula
DROP TABLE nom_taula;                  -- Eliminar una taula

Ara vorem en detall estes últimes.

2.3.1. CREATE TABLE

Creació de taules sense claus

SQL
1
2
3
4
CREATE TABLE alumnes (
    codi INT,
    nom VARCHAR(20)
);

Nota

Els noms (de taules, camps, etc) han de començar per lletra i no poden contindre espais o caràcters de puntuació especials.

Creació de taules amb claus primàries i alternatives

Quan creem una taula, si volem indicar que un camp és clau primària utilitzarem PRIMARY KEY. Per a indicar que és clau alternativa, usarem: UNIQUE.

SQL
1
2
3
4
5
6
CREATE TABLE persones (
    codi   INT          PRIMARY KEY AUTO_INCREMENT,
    dni    VARCHAR(10)  UNIQUE,
    nom    VARCHAR(40)  NOT NULL,
    deute  INT          DEFAULT 0
);

La clau primària i/o les claus alternatives també es poden indicar després de l'última columna:

SQL
1
2
3
4
5
6
7
CREATE TABLE persones (
    codi   INT          AUTO_INCREMENT,
    dni    VARCHAR(10),
    nom    VARCHAR(40)  NOT NULL,
    PRIMARY KEY (codi),
    UNIQUE (dni)
);

En eixe cas, també podem posar noms a les restriccions (que han de ser únics en tota la base de dades). Una utilitat és poder esborrar després una restricció indicant el seu nom.

SQL
1
2
3
4
5
6
7
CREATE TABLE persones (
    codi   INT          AUTO_INCREMENT,
    dni    VARCHAR(10),
    nom    VARCHAR(40)  NOT NULL,
    CONSTRAINT cpri_persones PRIMARY KEY (codi),
    CONSTRAINT calt_persones UNIQUE (dni)
);

Després podríem esborrar una restricció amb:

SQL
1
DROP INDEX calt_persones ON persones;

O bé:

SQL
1
ALTER TABLE persones DROP INDEX calt_persones;

Esta forma d'indicar les claus al final de la taula és necessària si volem indicar que una clau és composta. Per exemple:

SQL
1
2
3
4
5
6
CREATE TABLE liniesFactura (
    num    INT,
    linia  INT,
    ...,
    PRIMARY KEY (num, linia)
);

Notes

  • AUTO_INCREMENT: si no indiquem el codi quan inserim una persona, automàticament li assignarà un més del màxim codi dels alumnes inserits.
  • NOT NULL: per a impedir que un camp tinga valors nuls.
  • DEFAULT: per a posar un valor per defecte.

Creació de taules amb claus alienes

Per a usar-les en MySQL cal crear les taules amb el motor InnoDB.

Motors d'emmagatzematge de MySQL

El motor d'emmagatzematge és la forma en què MySQL emmagatzema les taules. Els motors més importants són MyISAM i InnoDB (cadascun amb els seus avantatges i inconvenients).

Nosaltres utilitzarem InnoDB perquè suporta claus alienes i transaccions.

Per a especificar el motor s'indica al final de la creació de cada taula, amb la paraula ENGINE (o bé TYPE, és el mateix).

SQL
1
2
3
CREATE TABLE persones (
    ...
) ENGINE = InnoDB;

Si no s'especifica el motor en una taula, es crea amb MyISAM en versions de MySQL anteriors a la 5.5. En versions posteriors, el motor per defecte és InnoDB. Per a saber la versió de MySQL que tenim, podem vore-ho amb: show variables like 'version'

Una sentència SELECT pot barrejar diferents tipus de taules.

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
CREATE TABLE pobles (
    cpo INT PRIMARY KEY,
    nom CHAR(30)
) ENGINE = InnoDB;

CREATE TABLE clients (
    codi  INT PRIMARY KEY,
    nom   VARCHAR(40) NOT NULL,
    poble INT,
    tel   VARCHAR(15),
    FOREIGN KEY (poble) REFERENCES pobles(cpo)
) ENGINE = InnoDB;

En este exemple veiem com seria la definició d'una clau aliena composta:

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
CREATE TABLE cintes (
    cod_pel     INT,
    num_copia   INT,
    rebobinada  BOOL,
    PRIMARY KEY (cod_pel, num_copia)
) ENGINE = InnoDB;

CREATE TABLE prestecs (
    pel    INT,
    copia  INT,
    data   DATE,
    soci   INT,
    PRIMARY KEY (pel, copia, data),
    FOREIGN KEY (pel, copia) REFERENCES cintes(cod_pel, num_copia)
) ENGINE = InnoDB;

Notes

  • També es pot posar un nom a la clau aliena, amb el CONSTRAINT.

  • És opcional indicar la columna de la taula referenciada (cpo) a no ser que en la taula pare fóra clau alternativa (en compte de clau principal):

    SQL
    1
    FOREIGN KEY (poble) REFERENCES pobles (cpo)

  • El motor MyISAM també permet establir les claus alienes, però no serveix de res: no fa control d'existència de les referències.

  • La clau aliena també es pot posar en la pròpia definició del camp en la taula, però no fa absolutament res (almenys en MySQL):

    SQL
    1
2
3
4
5
    CREATE TABLE clients (
    ...
    poble INT REFERENCES pobles (cpo),
    ...
)

    Només es posa com a "comentari per al programador" de que eixa columna fa referència a altra columna d'altra taula.

Exemple: funcionament de claus primàries i alienes

Omplim la taula pobles:

SQL
1
2
INSERT INTO pobles VALUES (46410, 'Sueca');
INSERT INTO pobles VALUES (55555, 'Ciutat de prova');

Omplim la taula clients:

SQL
1
2
3
INSERT INTO clients VALUES (1, 'Pep', 46410);   -- No dóna problemes
INSERT INTO clients VALUES (1, 'Pepa', 46410);  -- Error per clau primària
INSERT INTO clients VALUES (2, 'Pepa', 77777);  -- Error per clau aliena

Esborrem registre de la taula de pobles:

SQL
1
2
DELETE FROM pobles WHERE cpo = 55555;   -- No dóna problemes
DELETE FROM pobles WHERE cpo = 46410;   -- Error per la clau aliena

Accions associades a la clau aliena

Quan definim una clau aliena podem especificar que, si s'esborra un registre de la taula pare (la de pobles), que s'esborren també, automàticament, els registres relacionats de la taula filla (és a dir: que s'eliminen també els clients d'eixe poble).

O bé, podem indicar que, quan modifiquem un codi postal en la taula de pobles, que també es modifique eixe codi en els corresponents clients que tenien eixe poble.

Eixes dos coses (i més) es poden fer posant-ho en la restricció de clau aliena de la taula. Per exemple:

SQL
1
2
3
4
5
6
CREATE TABLE clients (
    ...
    FOREIGN KEY (poble) REFERENCES pobles(cpo)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Estes són les accions que podem fer en un ON DELETE o en un ON UPDATE:

  • CASCADE: Els registres dependents també s'esborren o es modifiquen.
  • SET NULL: Els registres dependents es posen a null (si no tenien la restricció de NOT NULL, clar).
  • SET DEFAULT: Els registres dependents es posen al valor per defecte (si el tenien, clar). El motor InnoDB ho accepta, però sense cap efecte.
  • RESTRICT: No deixa esborrar/modificar de la taula pare si té registres dependents. És l'opció per defecte (si no posem ON UPDATE o ON DELETE).
  • NO ACTION: En MySQL fa exactament el mateix que RESTRICT. Ara bé, en altres SGBD com Oracle o PostgreSQL, el NO ACTION deixa esborrar/modificar de la taula pare però no fa res en els registres dependents. Això trencaria la definició de clau aliena, però el que fa realment és endarrerir les comprovacions de clau aliena per a quan acabe la transacció (ja vorem les transaccions més endavant).

Exercicis. CREATE DATABASE i CREATE TABLE (BD cultius)

1) Per a fer un estudi sobre els tipus de cultius que es donen en cada comarca del món, s'ha creat el següent esquema ER, amb el posterior esquema relacional:

Text Only
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
TIPUS_CULTIUS = codi + nom

CULTIVAR = cultiu + comarca
    C. Ali: cultiu → TIPUS_CULTIUS (codi)
            comarca → COMARQUES (codi)

COMARQUES = codi + nom + prov + país
    C. Ali: (prov + país) → PROVÍNCIES (prov + país)
    VNN (prov)
    VNN (país)

PROVÍNCIES = prov + país + n_hab
    C. Ali: país → PAÏSOS (cod)

PAÏSOS = nom + continent

Escriu les instruccions DDL necessàries per a crear la base de dades cultius amb les taules corresponents, tenint en compte que:

  • Abans de crear una taula amb claus alienes, caldrà crear primer les taules de les quals depén. Si no, et donarà error quan intentes crear eixa taula.
  • El codi dels tipus de cultius serà un char(3). El codi de comarques un char(5). La quantitat d'habitants (n_hab) serà un enter. Els altres camps seran un varchar(30).
  • Caldrà indicar les claus alienes de forma que:
    • Si modifiquem el codi d'un país o província en la taula de províncies, també haurà de canviar en la taula de comarques.
    • Si modifiquem un codi de cultiu en la taula tipus_cultius, també s'haurà de modificar en la taula cultivar.
    • Si esborrem un tipus de cultiu en la taula tipus_cultius, també s'hauran d'esborrar els registres de cultivar que tinguen eixe cultiu.

2.3.2. ALTER TABLE

S'utilitza per a modificar l'estructura (no les dades) d'una taula ja existent.

a) Canviar el nom d'una taula

SQL
1
ALTER TABLE nom_antic RENAME TO nom_nou;

b) Afegir un camp

SQL
1
2
3
ALTER TABLE taula ADD COLUMN camp INT NOT NULL;
ALTER TABLE taula ADD COLUMN camp VARCHAR(20) NOT NULL FIRST;
ALTER TABLE taula ADD COLUMN camp INT NOT NULL AFTER camp_existent;

Nota

El COLUMN és opcional en ADD, DROP, CHANGE i ALTER.

c) Eliminar un camp

SQL
1
ALTER TABLE taula DROP COLUMN camp_a_esborrar;

d) Modificar un camp

  • Canvi de nom (i/o del tipus de dades, de posició...):

    SQL
    1
2
    ALTER TABLE taula CHANGE COLUMN camp_actual
                               camp_nou INT NOT NULL AFTER camp_exi;

  • Posar o llevar el valor per defecte:

    SQL
    1
2
    ALTER TABLE taula ALTER COLUMN camp
                   [SET DEFAULT valor | DROP DEFAULT];

e) Afegir claus o índexos

SQL
1
2
3
4
ALTER TABLE taula ADD PRIMARY KEY (camps);                            -- 1 o més camps separats per comes
ALTER TABLE taula ADD FOREIGN KEY (camps) REFERENCES taula_pare (camps);
ALTER TABLE taula ADD UNIQUE [nom_index] (camps);                     -- Clau alternativa
ALTER TABLE taula ADD INDEX [nom_index] (camps);                      -- Per a recerques més ràpides

Nota

També podem posar el CONSTRAINT per a posar nom a les restriccions.

f) Esborrar claus o índexos

SQL
1
2
3
ALTER TABLE taula DROP PRIMARY KEY;                    -- Esborra clau primària
ALTER TABLE taula DROP FOREIGN KEY nom_clau_ali;       -- Esborra clau aliena
ALTER TABLE taula DROP INDEX nom_index;                -- Esborra clau alternativa o índex

2.3.3. DROP TABLE

SQL
1
DROP TABLE [IF EXISTS] nom_taula;    -- Elimina una taula

Exercicis. ALTER TABLE (BD empresa)

1) Per a practicar l'ALTER TABLE primer crearem una nova BD amb un parell de taules. Crea la base de dades de nom empresa amb les taules següents:

Text Only
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
a. Taula d'empleats (**emp**) amb les columnes:

| Columna | Tipus / Restricció |
|---------|--------------------|
| `num` | enter |
| `nom` | 10 caràcters, camp obligatori |
| `cap` | enter |
| `comissio` | enter |
| `dept` | enter, camp obligatori |

b. Taula de departaments (**dept**) amb les columnes:

| Columna | Tipus / Restricció |
|---------|--------------------|
| `codi` | enter, clau primària |
| `nom` | 15 caràcters, camp obligatori, únic |
| `poble` | 20 caràcters |

2) Fes que el camp num de la taula emp siga la clau principal.

3) Afegeix una nova columna anomenada salari a la taula emp, de tipus enter i amb la condició de no poder prendre valors nuls.

4) Incorpora la condició de que la columna dept de la taula emp és clau aliena respecte a la taula dept (camp codi). Fes que quan s'esborre un departament, també s'esborren els empleats associats a eixe departament.

5) Fes que la columna cap de la taula emp siga clau aliena, referenciant a la seua pròpia taula.

6) Intenta eliminar la columna cap de la taula emp.

7) Elimina la condició de que la columna codi de dept és clau primària.