DDL

Dins de MySQL, a banda de taules, trobarem altres tipus d’objectes: índexs, columnes, àlies, vistes, procediments, etc.

Els noms dels objectes dins d’una base de dades, i les bases de dades mateixes, en MySQL actuen com a identificadors, i, com a tals no es podran repetir en un mateix àmbit. Per exemple, no podem tenir dues columnes d’una mateixa taula que s’anomenin igual, però sí en taules diferents.

En general, les taules i les bases de dades són not case sensitive, és a dir, que hi podem fer referència en majúscules o minúscules i no hi trobarem diferència, si el sistema operatiu sobre el qual estem treballant suporta not case sensitive.

Per exemple, en Windows podem executar indiferentment:

select * from emp;

O bé:

select * from EMP;

El que no acostumem a fer, però, és que dins d’una mateixa sentència ens referim a un mateix objecte en majúscules i en minúscules a la vegada:

select * from emp where EMP.EMP_NO=7499;

Els noms de columnes, índexs, procediments i disparadors (triggers), en canvi, sempre són not case sensitive.

Els noms dels objectes en MySQL admeten qualsevol tipus de caràcter, excepte / \ i .

De totes maneres, es recomana utilitzar caràcters alfabètics estrictament. Si el nom inclou caràcters especials és obligatori fer-hi referència entre cometes del tipus accent greu (`). Per exemple:

create table `ES UNA PROVA` (a int);

S’admeten també les cometes dobles (“) si activem el mode ANSI_QUOTES:

SET sql_mode='ANSI_QUOTES';
create table "ES UNA ALTRA PROVA" (a int);

Qualsevol objecte pot ser referit utilitzant les cometes, encara que no calgui, com ara en l’exemple:

select * from `empresa`.`emp` where `emp`.`emp_no`=7499;

Tot i que no és recomanable, es poden anomenar objectes amb paraules reservades del mateix llenguatge com ara SELECT, INSERT, DATABASE, etc. Aquests noms, però, s’hauran de posar obligatòriament entre cometes.

La longitud màxima dels objectes de la base de dades és 64 caràcters, excepte per als àlies, que poden arribar a ser de 256.

Finalment, vegem algunes indicacions per anomenar objectes:

• Utilitzar noms sencers, descriptius i pronunciables i, si no és factible, bones abreviatures. En anomenar objectes, sospeseu l’objectiu d’aconseguir noms curts i fàcils d’utilitzar davant l’objectiu de tenir noms que siguin descriptius. En cas de dubte, escolliu el nom més descriptiu, ja que els objectes de la base de dades poden ser utilitzats per molta gent al llarg del temps.
• Utilitzar regles d’assignació de noms que siguin coherents. Així, per exemple, una regla podria consistir a començar amb gc_ tots els noms de les taules que formen part d’una gestió comercial.
• Utilitzar el mateix nom per descriure la mateixa entitat o el mateix atribut en diferents taules. Així, per exemple, quan un atribut d’una taula és clau forana d’una altra taula, és molt convenient anomenar-lo amb el nom que té en la taula principal.

El servidor MySQL suporta tres estils de comentaris:

• # fins al final de la línia.
• — <espai en blanc> fins al final de la línia.
• /* fins a la propera seqüència */. Aquests tipus de comentaris admeten diverses línies de comentari.

Exemples dels diferents tipus de comentaris són els següents:

SELECT 1+1; # Aquest és el primer tipus de comentari
 
SELECT 1+1; -- Aquest és el segon tipus de comentari
 
SELECT 1 /* Aquest és un tipus de comentari que es pot posar enmig de la línia */ + 1;
 
SELECT 1+
/*
Aquest és un 
comentari
que es pot posar 
en diverses línies*/
1;

MySQL suporta diferents tipus d’emmagatzematge de taules (motors d’emmagatzemament o storage engines, en anglès). I quan es crea una taula cal especificar en quin sistema dels possibles el volem crear.

Per defecte, MySQL a partir de la versió 5.5.5 crea les taules de tipus InnoDB, que és un sistema transaccional, és a dir, que suporta les característiques que fan que una base de dades pugui garantir que les dades es mantindran consistents.

Les propietats que garanteixen els sistemes transaccionals són les característiques anomenades ACID. ACID és l’acrònim anglès d’atomicity, consistency, isolation, durability:

• Atomicitat: es diu que un SGBD garanteix atomicitat si qualsevol transacció o bé finalitza correctament (commit), o bé no deixa cap rastre de la seva execució (rollback).
• Consistència: es parla de consistència quan la concurrència de diferents transaccions no pot produir resultats anòmals.
• Aïllament (o isolament): cada transacció dins del sistema s’ha d’executar com si fos l’única que s’executa en aquell moment.
• Definitivitat: si es confirma una transacció, en un SGBD, el resultat d’aquesta ha de ser definitiu i no es pot perdre.

Evidentment, aquest sistema (InnoDB) d’emmagatzematge és el que sovint interessarà utilitzar per a les bases de dades que creem, però hi pot haver casos en què sigui interessant considerar altres tipus de motors d’emmagatzematge. Per això, MySQL també ofereix altres sistemes com ara, per exemple:

• MyISAM: era el sistema per defecte abans de la versió 5.5.5 de MySQL. S’utilitza molt en aplicacions web i en aplicacions de magatzem de dades (datawarehousing).
• Memory: aquest sistema emmagatzema tot en memòria RAM i, per tant, s’utilitza per a sistemes que requereixin un accés molt ràpid a les dades.
• Merge: agrupa taules de tipus MyISAM per optimitzar llistes i cerques. Les taules que cal agrupar han de ser de semblants, és a dir, han de tenir el mateix nombre i tipus de columnes.

Per obtenir una llista dels motors d’emmagatzemament suportats per la versió MySQL que tingueu instal·lada, podeu executar l’ordre SHOW ENGINES.

És una sentència que admet múltiples paràmetres, i la sintaxi completa es pot consultar en la documentació de l’SGBD que correspongui, però la sintaxi més simple i usual és aquesta:

create table [<nom_esquema>.]<nom_taula>
( <nom_columna> <tipus_dada> [default <expressió>][<llista_restriccions_pera_a_la_columna>],
<nom_columna> <tipus_dada> [default <expressió>][<llista_restriccions_per_a_la_columna>],
...
[<llista_restriccions_addicionals_per_a_una_o_vàries_columnes>]);

Fixem-nos que hi ha força elements que són optatius:

• Les parts obligatòries són el nom de la taula i, per cada columna, el nom i el tipus de dada.
• El nom de l’esquema en què es crea la taula és optatiu i, si no s’indica, la taula s’intenta crear dins l’esquema en què estem connectats.
• Cada columna té permès definir-hi un valor per defecte (opció default) a partir d’una expressió, el qual utilitzarà l’SGBD en les instruccions d’inserció quan no s’especifiqui un valor per a les columnes que tenen definit el valor per defecte. En MySQL el valor per defecte ha de ser constant, no pot ser, per exemple, una funció com ara NOW() ni una expressió com ara CURRENT_DATE.
• La definició de les restriccions per a una o més columnes també és optativa en el moment de procedir a la creació de la taula.

També és molt usual crear una taula a partir del resultat d’una consulta, amb la sintaxi següent:

create table [<nom_esquema>.]<nom_taula> [(<noms_dels_camps>]
as <sentència_select>;

En aquesta sentència, no es defineixen els tipus de camps que es corresponen amb els tipus de les columnes recuperades en la sentència SELECT. La definició dels noms dels camps és optativa; si no s’efectua, els noms de les columnes recuperades passen a ser els noms dels camps nous. Caldrà, però, afegir-hi les restriccions que corresponguin. La taula nova conté una còpia de les files resultants de la sentència SELECT.

A l’hora de definir taules, cal tenir en compte diversos conceptes:

• Els tipus de dades que l’SGBD possibilita.
• Les restriccions sobre els noms de taules i columnes.
• La integritat de les dades.

L’SGBD MySQL proporciona diversos tipus de restriccions (constraints en la nomenclatura que s’ha d’utilitzar en els SGBD) o opcions de restricció per facilitar la integritat de les dades. En general, es poden definir en el moment de crear la taula, però també es poden alterar, afegir i eliminar amb posterioritat.

Cada restricció porta associat un nom (únic en tot l’esquema) que es pot especificar en el moment de crear la restricció. Si no s’especifica, l’SGBD n’assigna un per defecte.

Vegem, a continuació, els diferents tipus de restriccions:

Si la clau primària és formada per una única columna, es pot especificar en la línia de definició de la columna corresponent, amb la sintaxi següent:

<columna> <tipus_dada> primary key

En canvi, si la clau primària és formada per més d’una columna, s’ha d’especificar obligatòriament en la zona final de restriccions sobre columnes de la taula, amb la sintaxi següent:

[constraint <nom_restricció>] primary key (col1,col2,...)

Les claus primàries que afecten una única columna també es poden especificar amb aquest segon procediment.

Aquesta restricció es pot indicar en la definició de la columna corresponent amb aquesta sintaxi:

<columna> <tipus_dada> [not null]

Per descomptat, no cal definir aquesta restricció sobre columnes que formen part de la clau primària, ja que formar part de la clau primària implica, automàticament, la impossibilitat de tenir valor nuls.

Si la unicitat s’especifica per a una única columna, es pot assignar en la línia de definició de la columna corresponent, amb la sintaxi següent:

<columna> <tipus_dada> unique

En canvi, si la unicitat s’aplica sobre diverses columnes simultàniament, cal especificar-la obligatòriament en la zona final de restriccions sobre columnes de la taula, amb la sintaxi següent:

[constraint <nom_restricció>] unique (col1, col2...)

Aquest segon procediment també es pot emprar per aplicar la unicitat a una única columna.

Per descomptat, no cal definir aquesta restricció sobre un conjunt de columnes que formen part de la clau primària, ja que la clau primària implica, automàticament, la unicitat de valors.

Aquesta restricció es pot indicar en la definició de la columna corresponent:

<columna> <tipus_dada> check (<condició>)

També es pot indicar en la zona final de restriccions sobre columnes de la taula, amb la sintaxi següent:

check (<condició>)

Podem definir les columnes numèriques amb l’opció AUTO_INCREMENT. Aquesta modificació permet que en inserir un valor null o no inserir valor explícitament en aquella columna definida d’aquesta manera, s’hi afegeixi un valor per defecte consistent en el més gran ja introduït incrementat en una unitat.

<columna> <tipus_dada> AUTO_INCREMENT 

Podem afegir comentaris a les columnes de manera que puguin quedar guardats a la base de dades i ser consultats. La manera de fer-ho és afegir la paraula COMMENT seguida del text que calgui posar com a comentari entre cometes simples.

<columna> <tipus_dada> COMMENT 'comentari' 

Si la clau forana és formada per una única columna, es pot especificar en la línia de definició de la columna corresponent, amb la sintaxi següent:

<columna> <tipus_dada> [constraint <nom_restricció>] references <taula> [(columna)] 

En canvi, si la clau forana és formada per més d’una columna, cal especificar-la obligatòriament en la zona final de restriccions sobre columnes de la taula, amb la sintaxi següent:

[constraint <nom_restricció>] foreign key (col1, col2...) 
references <taula> [(col1, col2...)]

Les claus foranes que afecten una única columna també es poden especificar amb aquest segon procediment.

En qualsevol dels dos casos, es fa referència a la taula principal de la qual estem definint la clau forana, la qual cosa es fa amb l’opció references <taula>.

La sintaxi per a la integritat referencial activa en MySQL és la següent (si s’utilitzen altres sintaxis, l’SGBD les reconeix però no les valida):

index [<nom_index>] (col1, col2...)
[constraint <nom_restricció>] foreign key (col1, col2...) references <taula> (col1, col2...)

La sintaxi que hem presentat per tal de definir la integritat referencial no és completa. Ens falta tractar un tema fonamental: l’actuació que esperem de l’SGBD davant possibles eliminacions i actualitzacions de dades en la taula principal, quan hi ha files en altres taules que hi fan referència.

La constraint foreign key es pot definir acompanyada dels apartats següents:

• on delete <acció>, que defineix l’actuació automàtica de l’SGBD sobre les files de la nostra taula que es veuen afectades per una eliminació de les files a les quals fan referència.
• on update <acció>, que defineix l’actuació automàtica de l’SGBD sobre les files de la nostra taula que es veuen afectades per una actualització del valor al qual fan referència.

Per si no us ha quedat clar, pensem en les taules DEPT i EMP de l’esquema empresa. La taula EMP conté la columna dept_no, que és clau forana de la taula DEPT. Per tant, en la definició de la taula EMP hem de tenir definida una constraint foreign key en la columna dept_no fent referència a la taula DEPT. En definir aquesta restricció de clau forana, el dissenyador de la base de dades va haver de prendre decisions respecte al següent:

• Com ha d’actuar l’SGBD davant l’intent d’eliminació d’un departament en la taula DEPT si hi ha files en la taula EMP que hi fan referència? Això es defineix en l’apartat on delete <acció>.
• Com ha d’actuar l’SGBD davant l’intent de modificació del codi d’un departament en la taula DEPT si hi ha files en la taula EMP que hi fan referència? Això es defineix en l’apartat on update <acció>.

En general, els SGBD ofereixen diverses possibilitats d’acció, però no sempre són les mateixes. Abans de conèixer aquestes possibilitats, també ens cal saber que alguns SGBD permeten diferir la comprovació de les restriccions de clau forana fins a la finalització de la transacció, en lloc d’efectuar la comprovació -i actuar en conseqüència- després de cada instrucció. Quan això és factible, la definició de la constraint va acompanyada del mot deferrable o not deferrable. L’actuació per defecte acostuma a ser no diferir la comprovació.

Per tant, la sintaxi de la restricció de clau forana es veu clarament ampliada. Si s’efectua en el moment de definir la columna, tenim el següent:

[constraint <nom_restricció>] foreign key (col1, col2, ...) references <taula> (col1, 
col2, ...) [on delete <acció>] [on update <acció>] 

Les opcions que ens podem arribar a trobar en referència a l’acció que acompanyi els apartats on update i on delete són aquestes: RESTRICT | CASCADE | SET NULL | NO ACTION

• NO ACTION o RESTRICT: són sinònims. És l’opció per defecte i no permet l’eliminació o actualització de dades en la taula principal.
• CASCADE: quan s’actualitza o elimina la fila pare, les files relacionades (filles) també s’actualitzen o eliminen automàticament.
• SET NULL: quan s’actualitza o elimina la fila pare, les files relacionades (filles) s’actualitzen a NULL. Cal haver-les definit de manera que admetin valors nuls, és clar.
• SET DEFAULT: quan s’actualitza o elimina la fila pare, les files relacionades (filles) s’actualitzen al valor per defecte. MySQL suporta la sintaxi, però no actua, davant d’aquesta opció.

L’opció CASCADE és molt perillosa en utilitzar-la amb on delete. Pensem què passaria, en l’esquema empresa, si algú decidís eliminar un departament de la taula DEPT i la clau forana en la taula EMP fos definida amb on delete cascade: tots els empleats del departament serien, immediatament, eliminats de la taula EMP.

De vegades, però, és molt útil acompanyant on delete. Pensem en la relació d’integritat entre les taules COMANDA i DETALL de l’esquema empresa. La taula DETALL conté la columna com_num, que és clau forana de la taula COMANDA. En aquest cas, pot tenir molt de sentit tenir definida la clau forana amb on delete cascade, ja que l’eliminació d’una ordre provocarà l’eliminació automàtica de les seves línies de detall.

A diferència de la caució en la utilització de l’opció cascade per a les actuacions on delete, s’acostuma a utilitzar molt per a les actuacions on update.

La taula mostra les opcions proporcionades per alguns SGBD actuals.

CREATE TABLE IF NOT EXISTS empresa.DEPT (
 DEPT_NO  TINYINT (2) UNSIGNED,
 DNOM     VARCHAR(14) NOT NULL UNIQUE,
 LOC      VARCHAR(14),
 PRIMARY KEY (DEPT_NO) ) ;
 
 
CREATE TABLE IF NOT EXISTS empresa.EMP (
 EMP_NO    SMALLINT (4) UNSIGNED,
 COGNOM    VARCHAR (10) NOT NULL,
 OFICI     VARCHAR (10),
 CAP       SMALLINT (4) UNSIGNED,
 DATA_ALTA DATE,
 SALARI    INT UNSIGNED,
 COMISSIO  INT UNSIGNED,
 DEPT_NO   TINYINT (2) UNSIGNED NOT NULL,
 PRIMARY KEY (EMP_NO),
 INDEX IDX_EMP_CAP (CAP),
 INDEX IDX_EMP_DEPT_NO (DEPT_NO),
 FOREIGN KEY (DEPT_NO) REFERENCES empresa.DEPT(DEPT_NO)) ;

USE empresa;

insert into EMP values (100, 'Rodríguez', 'Venedor', NULL, sysdate, -5000, NULL, 10)

CREATE TABLE IF NOT EXISTS empresa.CLIENT (
 CLIENT_COD          INT(6) UNSIGNED PRIMARY KEY,
 NOM                 VARCHAR (45) NOT NULL,
 ADREÇA              VARCHAR (40) NOT NULL,
 CIUTAT              VARCHAR (30) NOT NULL,
 ESTAT               VARCHAR (2),
 CODI_POSTAL         VARCHAR (9) NOT NULL,
 AREA                SMALLINT(3),
 TELEFON             VARCHAR (9),
 REPR_COD            SMALLINT(4) UNSIGNED,
 LIMIT_CREDIT        DECIMAL(9,2) UNSIGNED,
 OBSERVACIONS        TEXT,
 INDEX IDX_CLIENT_REPR_COD (REPR_COD),
 FOREIGN KEY (REPR_COD) REFERENCES empresa.EMP(EMP_NO));

CREATE TABLE IF NOT EXISTS empresa.PRODUCTE (
 PROD_NUM     INT (6) UNSIGNED PRIMARY KEY,
 DESCRIPCIO   VARCHAR (30) NOT NULL  UNIQUE);
 
CREATE TABLE IF NOT EXISTS empresa.COMANDA(
 COM_NUM             SMALLINT(4) UNSIGNED PRIMARY KEY,
 COM_DATA            DATE,
 COM_TIPUS           CHAR (1) CHECK (COM_TIPUS IN ('A','B','C')),
 CLIENT_COD          INT (6) UNSIGNED NOT NULL,
 DATA_TRAMESA        DATE,
 TOTAL               DECIMAL(8,2) UNSIGNED,
 INDEX IDX_COMANDA_CLIENT_COD (CLIENT_COD),  
 FOREIGN KEY (CLIENT_COD) REFERENCES empresa.CLIENT(CLIENT_COD) );
 
 
CREATE TABLE IF NOT EXISTS empresa.DETALL  (
 COM_NUM             SMALLINT(4) UNSIGNED,
 DETALL_NUM          SMALLINT(4) UNSIGNED,
 PROD_NUM            INT(6) UNSIGNED NOT NULL,
 PREU_VENDA          DECIMAL(8,2) UNSIGNED,
 QUANTITAT           INT (8),
 IMPORT              DECIMAL(8,2),
 CONSTRAINT DETALL_PK PRIMARY KEY (COM_NUM,DETALL_NUM),
 INDEX IDX_DETAL_COM_NUM (COM_NUM),
 INDEX IDX_PROD_NUM (PROD_NUM),
 FOREIGN KEY (COM_NUM) REFERENCES empresa.COMANDA(COM_NUM),
 FOREIGN KEY (PROD_NUM) REFERENCES empresa.PRODUCTE(PROD_NUM));

CREATE TABLE IF NOT EXISTS sanitat.HOSPITAL (
 HOSPITAL_COD   TINYINT (2) PRIMARY KEY,
 NOM            VARCHAR(10) NOT NULL,
 ADREÇA         VARCHAR(20),
 TELEFON        VARCHAR(8),
 QTAT_LLITS     SMALLINT(3) UNSIGNED DEFAULT 0 );
 
 
 
CREATE TABLE IF NOT EXISTS sanitat.SALA (
 HOSPITAL_COD  TINYINT (2),
 SALA_COD      TINYINT (2),
 NOM           VARCHAR(20) NOT NULL,
 QTAT_LLITS    SMALLINT(3) UNSIGNED DEFAULT 0,
 CONSTRAINT SALA_PK PRIMARY KEY (HOSPITAL_COD, SALA_COD),
 INDEX IDX_SALA_HOSPITAL_COD (HOSPITAL_COD),
 FOREIGN KEY (HOSPITAL_COD) REFERENCES sanitat.HOSPITAL(HOSPITAL_COD)) ;

USE sanitat;

CREATE TABLE IF NOT EXISTS sanitat.PLANTILLA (
 HOSPITAL_COD  TINYINT (2),
 SALA_COD      TINYINT (2),
 EMPLEAT_NO    SMALLINT(4) NOT NULL,
 COGNOM        VARCHAR (15) NOT NULL,
 FUNCIO        VARCHAR (10), 
 TORN          VARCHAR (1) CHECK (TORN IN ('M','T','N')),
 SALARI        INT (10),
 CONSTRAINT PLANTILLA_PK PRIMARY KEY (HOSPITAL_COD, SALA_COD, EMPLEAT_NO),
 INDEX IDX_PLANTILLA_HOSP_SALA (HOSPITAL_COD, SALA_COD),
 FOREIGN KEY (HOSPITAL_COD, SALA_COD) REFERENCES sanitat.SALA (HOSPITAL_COD, SALA_COD)) ;

CREATE TABLE IF NOT EXISTS sanitat.MALALT (
 INSCRIPCIO    INT (5) PRIMARY KEY,  
 COGNOM        VARCHAR (15) NOT NULL,
 ADREÇA        VARCHAR (20),
 DATA_NAIX     DATE,
 SEXE          CHAR (1) NOT NULL  CHECK (SEXE = 'H' OR SEXE = 'D'),
 NSS           CHAR(9)) ;
 
CREATE TABLE IF NOT EXISTS sanitat.INGRESSOS (
 INSCRIPCIO    INT (5) PRIMARY KEY,
 HOSPITAL_COD  TINYINT (2) NOT NULL,
 SALA_COD      TINYINT (2) NOT NULL,
 LLIT          SMALLINT(4) UNSIGNED,
 INDEX IDX_INGRESSOS_INSCRIPCIO (INSCRIPCIO),
 INDEX IDX_INGRESSOS_HOSP_SALA (HOSPITAL_COD, SALA_COD),
 FOREIGN KEY (INSCRIPCIO) REFERENCES sanitat.MALALT(INSCRIPCIO),
 FOREIGN KEY (HOSPITAL_COD, SALA_COD) REFERENCES sanitat.SALA (HOSPITAL_COD, SALA_COD));
 
 
CREATE TABLE IF NOT EXISTS sanitat.DOCTOR (
 HOSPITAL_COD  TINYINT (2),
 DOCTOR_NO     SMALLINT(3),
 COGNOM        VARCHAR(13) NOT NULL,
 ESPECIALITAT  VARCHAR(16) NOT NULL,
 CONSTRAINT DOCTOR_PK PRIMARY KEY (HOSPITAL_COD, DOCTOR_NO),
 INDEX IDX_DOCTOR_HOSP (HOSPITAL_COD),
 FOREIGN KEY (HOSPITAL_COD) REFERENCES sanitat.HOSPITAL(HOSPITAL_COD)) ;

La sintaxi de la sentència DROP TABLE és aquesta:

drop table [<nom_esquema>.]<nom_taula> [if exists];

L’opció if exists es pot especificar per tal d’evitar un error en cas que la taula no existeixi.

També es poden afegir les opcions cascade o restrict que en alguns SGBD fan que s’eliminin totes les definicions de restriccions d’altres taules que fan referència a la taula que es vol eliminar abans de fer-ho, o que s’impedeixi l’eliminació, respectivament. Sense l’opció cascade la taula que és referenciada per altres taules (a nivell de definició, independentment que hi hagi o no, en un moment determinat, files referenciades), l’SGBDR no l’elimina.

drop table dept;

use empresa;
drop table detall;
drop table comanda;
drop table producte;
drop table client;
drop table emp;
drop table dept;

De vegades, cal fer modificacions en l’estructura de les taules (afegir-hi o eliminar-ne columnes, afegir-hi o eliminar-ne restriccions, modificar els tipus de dades…).

La seva sintaxi és aquesta:

alter [IGNORE] table [<nom_esquema>.]<nom_taula>
<clàusules_de_modificació_de_taula>;

És a dir, una sentència alter table pot contenir diferents clàusules (com a mínim una) que modifiquin l’estructura de la taula. Hi ha clàusules de modificació de taula que poden anar acompanyades, en una mateixa sentència alter table, per altres clàusules de modificació, mentre que n’hi ha que han d’anar soles.

Cal tenir present que, per efectuar una modificació, l’SGBD no hauria de trobar cap incongruència entre la modificació que s’ha d’efectuar i les dades que ja hi ha en la taula. No tots els SGBD actuen de la mateixa manera davant aquestes situacions.

Així, l’SGBD MySQL, per defecte, no permet especificar la restricció d’obligatorietat (not null) a una columna que ja conté valors nuls (cosa lògica, no?) ni tampoc disminuir l’amplada d’una columna de tipus varchar a una amplada inferior a l’amplada màxima dels valors continguts en la columna.

En canvi, però, si s’activa l’opció ignore, la modificació especificada s’intenta fer, encara que calgui truncar o modificar dades de la taula ja existent. Per exemple, si intentem modificar la característica not null de la columna telèfon de la taula hospital, de l’esquema sanitat, atès que hi ha un valor nul, la sentència següent no s’executarà:

alter table hospital
  modify telefon varchar(8) not null;

I el resultat de l’execució d’aquesta modificació serà un missatge tipus Error: Data truncated for column telefon at row 5.

En canvi, si utilitzem l’opció ignore podem executar la sentència següent que ens permetrà modificar l’opció not null de telèfon de manera que posarà un string buit en lloc de valor nul en les columnes que no compleixin la condició:

alter ignore table hospital
  modify telefon varchar(8) not null;

De manera similar, si volem disminuir la mida de la columna adreça de la taula hospital:

alter table hospital
  modiy adreca varchar(7);

Aquest codi mostrarà un error similar a Error: Data truncated for column adreca at row 1 i no s’executarà la sentència. En canvi, si hi afegim l’opció ignore, el resultat serà la modificació de l’estructura de la taula i el truncament dels valors de les columnes afectades.

alter ignore table hospital
  modiy adreca varchar(7);

Vegem, a continuació, les diferents possibilitats d’alteració de taula, tenint en compte que en MySQL s’admeten diversos tipus d’alteracions en una mateixa clàusula d‘alter table, separades per coma:

1. Per afegir una columna

ADD [COLUMN] nom_columna definició_columna [FIRST | ALFTER nom_columna ]

O bé, si cal definir-ne unes quantes de noves:

 
ADD [COLUMN] (nom_columna definició_columna,...) 

2. Per eliminar una columna

DROP [COLUMN] <nom_columna> 

3. Per modificar l’estructura d’una columna

MODIFY [COLUMN] nom_columna definició_columna [FIRST | AFTER col_name]

O bé:

CHANGE [COLUMN] nom_columna_antic nom_columna_nou definició_columna
        [FIRST|AFTER nom_columna]

4. Per afegir restriccions

ADD [CONSTRAINT <nom_restricció>] <restricció>

Concretament, les restriccions que es poden afegir en MySQL són les següents:

ADD [CONSTRAINT [símbol]] PRIMARY KEY [tipus_index] (nom_columna_index,...) [opcions_index] ...

ADD [CONSTRAINT [símbol]] UNIQUE [INDEX|KEY] [nom_index] [tipus_index] (nom_columna_index,...) [opcions_index] ...

ADD [CONSTRAINT [símbol]] FOREIGN KEY [nom] (nom_columna1,...) REFERENCES taula (columna1, ....)

5. Per eliminar restriccions

DROP PRIMARY KEY
 
DROP {INDEX|KEY} nom_index
 
DROP FOREIGN KEY nom

6. Per afegir índexs

ADD {INDEX|KEY} [nom_index]
        [tipus_index] (nom_columna,...) [opcions_index] ...

7. Per habilitar o deshabilitar els índexs

DISABLE KEYS
 
ENABLE KEYS

8. Per reanomenar una taula

RENAME [TO] nom_nou_taula

9. Per reordenar les files d’una taula

ORDER BY nom_columna1 [, nom_columna2] ...

10. Per canviar o eliminar el valor per defecte d’una columna

ALTER [COLUMN] nom_columna {SET DEFAULT literal | DROP DEFAULT}

SQL> desc DEPT;
Name         Null     Type                                      
------------ -------- --------------
DEPT_NO      NOT NULL TINYINT(2)
DNOM         NOT NULL VARCHAR(14)
LOC                   VARCHAR(14)

alter table dept
modify loc varchar(14) not null,
add numEmps number(2) unsigned,
modify dnom varchar(20);

CREATE TABLE IF NOT EXISTS empresa.DEPT (
 DEPT_NO  TINYINT (2) UNSIGNED,
 DNOM     VARCHAR(14) NOT NULL UNIQUE,
 LOC      VARCHAR(14),
 PRIMARY KEY (DEPT_NO) ) ;
 
 
INSERT INTO empresa.DEPT VALUES (10, 'COMPTABILITAT', 'SEVILLA');
INSERT INTO empresa.DEPT VALUES (20, 'INVESTIGACIÓ', 'MADRID');
INSERT INTO empresa.DEPT VALUES (30, 'VENDES', 'BARCELONA');
INSERT INTO empresa.DEPT VALUES (40, 'PRODUCCIÓ', 'BILBAO');
 
 
CREATE TABLE IF NOT EXISTS empresa.EMP (
 EMP_NO    SMALLINT (4) UNSIGNED,
 COGNOM    VARCHAR (10) NOT NULL,
 OFICI     VARCHAR (10),
 CAP       SMALLINT (4) UNSIGNED,
 DATA_ALTA DATE,
 SALARI    INT UNSIGNED,
 COMISSIO  INT UNSIGNED,
 DEPT_NO   TINYINT (2) UNSIGNED NOT NULL,
 PRIMARY KEY (EMP_NO),
 INDEX IDX_EMP_CAP (CAP),
 INDEX IDX_EMP_DEPT_NO (DEPT_NO),
 FOREIGN KEY (DEPT_NO) REFERENCES empresa.DEPT(DEPT_NO)) ;
 
 
INSERT INTO empresa.EMP VALUES (7369,'SÁNCHEZ','EMPLEAT',7902, '1980-12-17', 104000,NULL,20);
INSERT INTO empresa.EMP VALUES (7499,'ARROYO','VENEDOR',7698, '1980-02-20', 208000,39000,30);
INSERT INTO empresa.EMP VALUES (7521,'SALA','VENEDOR',7698, '1981-02-22', 162500,65000,30);
INSERT INTO empresa.EMP VALUES (7566,'JIMÉNEZ','DIRECTOR',7839, '1981-04-02', 386750,NULL,20);
INSERT INTO empresa.EMP VALUES (7654,'MARTÍN','VENEDOR',7698, '1981-09-29', 162500,182000,30);
INSERT INTO empresa.EMP VALUES (7698,'NEGRO','DIRECTOR',7839, '1981-05-01', 370500,NULL,30);
INSERT INTO empresa.EMP VALUES (7782,'CEREZO','DIRECTOR',7839, '1981-06-09', 318500,NULL,10);
INSERT INTO empresa.EMP VALUES (7788,'GIL','ANALISTA',7566, '1981-11-09', 390000,NULL,20);
INSERT INTO empresa.EMP VALUES (7839,'REY','PRESIDENT',NULL, '1981-11-17', 650000,NULL,10);
INSERT INTO empresa.EMP VALUES (7844,'TOVAR','VENEDOR',7698, '1981-09-08', 195000,0,30);
INSERT INTO empresa.EMP VALUES (7876,'ALONSO','EMPLEAT',7788, '1981-09-23', 143000,NULL,20);
INSERT INTO empresa.EMP VALUES (7900,'JIMENO','EMPLEAT',7698, '1981-12-03', 123500,NULL,30);
INSERT INTO empresa.EMP VALUES (7902,'FERNÁNDEZ','ANALISTA',7566, '1981-12-03', 390000,NULL,20);
INSERT INTO empresa.EMP VALUES (7934,'MUÑOZ','EMPLEAT',7782, '1982-01-23', 169000,NULL,10);

ALTER TABLE empresa.EMP
ADD FOREIGN KEY (CAP) REFERENCES EMP(EMP_NO);

Els SGBD utilitzen índexs per accedir de manera més ràpida a les dades. Quan cal accedir a un valor d’una columna en què no hi ha definit cap índex, l’SGBD ha de consultar tots els valors de totes les columnes des de la primera fins a l’última. Això resulta molt costós en temps i, com més files té la taula en qüestió, més lenta és l’operació. En canvi, si tenim definit un índex en la columna de cerca, l’operació d’accedir a un valor concret resulta molt més ràpid, perquè no cal accedir a tots els valors de totes les files per trobar el que es busca.

MySQL utilitza índexs per facilitar l’accés a columnes que són PRIMARY KEY o UNIQUE i sol emmagatzemar els índexs utilitzant el tipus d’índex B-tree. Per a les taules emmagatzemades en MEMORY s’utilitzen, però, índexs de tipus HASH.

És lògic crear índexs per facilitar l’accés per a les columnes que necessitin accessos ràpids o molt freqüents. L’administrador de l’SGBD té, entre les seves tasques, avaluar els accessos que s’efectuen a la base de dades i decidir, si escau, l’establiment d’índexs nous. Però també és tasca de l’analista i/o dissenyador de la base de dades dissenyar els índexs adequats per a les diferents taules, ja que és la persona que ha ideat la taula pensant en les necessitats de gestió que tindran els usuaris.

La seva sintaxi simple és aquesta:

create index [<nom_esquema>.]<nom_índex>
on <nom_taula> (col1 [asc|desc], col2 [asc|desc], ...);

Tot i que la creació d’un índex té associades moltes opcions, que en MySQL poden ser les següents:

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX nom_index
  [USING {BTREE | HASH}
  ON nom_taula (columna1 [longitud][asc|desc], ....)
  [opcions_administració];

En MySQL podem definir índexs que mantinguin valors no repetits, especificant la clàusula UNIQUE. També podem indicar que indexin tenint en compte el camp sencer d’una columna de tipus TEXT, si utilitzem un motor d’emmagatzemament de tipus MyISAM. MySQL suporta índexs sobre els tipus de dades geomètriques que suporta (SPATIAL).

Les opcions USING BTREE i USING HASH permeten forçar la creació d’un índex d’un tipus o un altre (índex tipus B-tree o tipus hash).

La modificació ASC o DESC sobre cada columna, però, és suportada sintàcticament donant suport a l’estàndard SQL però no té efecte: tots els índexs en MySQL són ascendents.

La seva sintaxi és aquesta:

drop index [<nom_esquema>.]<nom_índex> on <nom_taula>;

-- Per tenir els empleats indexats pel cognom: 
 
create index EMP_COGNOM on EMP (COGNOM);
 
-- Per tenir els empleats indexats pel departament al qual estan assignats:
 
create index EMP_DEPT_NO_EMP on EMP (DEPT_NO,EMP_NO); 
 
-- Per tenir els clients indexats pel nom: 
 
create index CLIENT_NOM on CLIENT (NOM); 
 
-- Per tenir els clients indexats pel representant (+ codi de client): 
 
create index CLIENT_REPR_CLI on CLIENT (REPR_COD, CLIENT_COD); 
 
-- Per tenir les comandes indexades per la seva data (+ número de comanda): 
 
create index COMANDA_DATA_NUM on COMANDA (COM_DATA, COM_NUM); 
 
-- Per tenir les comandes indexades per la data de tramesa (+ número de comanda): 
 
create index COMANDA_DATA_TRAMESA on COMANDA (DATA_TRAMESA); 
 
-- Per tenir les línies de detall indexades per producte (+ comanda + número de línia): 
 
create index DETALL_PROD_COM_DET on DETALL (PROD_NUM,COM_NUM,DETALL_NUM);

-- Per tenir els hospitals indexats pel nom: 
 
CREATE INDEX HOSPITAL_NOM ON HOSPITAL (NOM); 
 
-- Per tenir les sales indexades pel nom dins cada hospital: 
 
CREATE INDEX SALA_HOSP_NOM ON SALA (HOSPITAL_COD, NOM); 
 
-- Per tenir la plantilla indexada per cognom dins cada hospital: 
 
CREATE INDEX PLANTILLA_HOSP_COGNOM ON PLANTILLA (HOSPITAL_COD, COGNOM);
 
-- Per tenir la plantilla indexada per la funció dins cada hospital: 
 
CREATE INDEX PLANTILLA_HOSP_FUNCIO ON PLANTILLA (HOSPITAL_COD, FUNCIO);
 
-- Per tenir la plantilla indexada per la funció (entre tots els hospitals sales): 
 
CREATE INDEX PLANTILLA_FUNCIO_HOSP_SALA ON PLANTILLA (FUNCIO, HOSPITAL_COD, SALA_COD); 
 
-- Per tenir els malalts indexats per data de naixement i cognom: 
 
CREATE INDEX MALALT_NAIX_COGNOM ON MALALT (DATA_NAIX, COGNOM); 
 
-- Per tenir els malalts indexats per cognom i data de naixement: 
 
CREATE INDEX MALALT_COGNOM_NAIX ON MALALT (COGNOM, DATA_NAIX); 
 
-- Per tenir els ingressats indexats per hospital sala: 
 
CREATE INDEX INGRESSOS_HOSP_SALA ON INGRESSOS (HOSPITAL_COD, SALA_COD); 
 
-- Per tenir els doctors indexats per la seva especialitat (entre tots els hospitals): 
 
CREATE INDEX DOCTOR_ESP_HOSP ON DOCTOR (ESPECIALITAT, HOSPITAL_COD); 
 
-- Per tenir els doctors indexats per la seva especialitat dins cada hospital: 
 
CREATE INDEX DOCTOR_HOSP_ESP ON DOCTOR (HOSPITAL_COD, ESPECIALITAT);

show indexes from [nom_esquema.]nom_taula

Una vista és una taula virtual per mitjà de la qual es pot veure i, en alguns casos canviar, informació d’una o més taules.

Una vista té una estructura semblant a una taula: files i columnes. Mai no conté dades, sinó una sentència SELECT que permet accedir a les dades que es volen presentar per mitjà de la vista. La gestió de vistes és semblant a la gestió de taules.

La seva sintaxi és aquesta:

create [or replace] view [<nom_esquema>.]<nom_vista> [(col1, col2...)]
as <sentència_select>
[with [cascaded| local] check option];

Com observareu, aquesta sentència és similar a la sentència per crear una taula a partir del resultat d’una consulta. La definició dels noms dels camps és optativa; si no s’efectua, els noms de les columnes recuperades passen a ser els noms dels camps nous. La sentència SELECT es pot basar en altres taules i/o vistes.

L’opció with check option indica a l’SGBD que les sentències INSERT i UPDATE que es puguin executar sobre la vista han de verificar les condicions de la clàusula where de la vista.

L’opció or replace en la creació de la vista permet modificar una vista existent amb una nova definició. Cal tenir en compte que aquesta és l’única via per modificar una vista sense eliminar-la i tornar-la a crear.

La seva sintaxi és aquesta, que permet eliminar una o diverses vistes:

drop view [<nom_esquema>.]<nom_vista> [,[<nom_esquema>.]<nom_vista>] ;

La sentència ALTER VIEW és la instrucció proporcionada per modificar vistes. La seva sintaxi és aquesta:

alter view <nom_vista> [(columna1, ....)]
  as <sentència_select>;

create view EMPD
as select emp_no, cognom, ofici, cap, data_alta, salari, comissio, e.dept_no, dnom
  from emp e, dept d
  where e.dept_no = d.dept_no;

SQL> select * from empd;
 
EMP_NO  COGNOM     OFICI      CAP  DATA_ALTA   SALARI  COMISSIÓ   DEPT_NO  DNOM           
------- ---------- ---------- ---- ----------- ------- ---------- -------- ----------------
7369    SÁNCHEZ    EMPLEAT    7902 17/12/1980  104000             20       INVESTIGACIÓ   
7499    ARROYO     VENEDOR    7698 20/02/1980  208000  39000      30       VENDES         
7521    SALA       VENEDOR    7698 22/02/1981  162500  65000      30       VENDES         
7566    JIMÉNEZ    DIRECTOR   7839 02/04/1981  386750             20       INVESTIGACIÓ   
7654    MARTÍN     VENEDOR    7698 29/09/1981  162500  182000     30       VENDES         
7698    NEGRO      DIRECTOR   7839 01/05/1981  370500             30       VENDES         
7782    CEREZO     DIRECTOR   7839 09/06/1981  318500             10       COMPTABILITAT  
7788    GIL        ANALISTA   7566 09/11/1981  390000             20       INVESTIGACIÓ   
7839    REY        PRESIDENT       17/11/1981  650000             10       COMPTABILITAT  
7844    TOVAR      VENEDOR    7698 08/09/1981  195000  0          30       VENDES         
7876    ALONSO     EMPLEAT    7788 23/09/1981  143000             20       INVESTIGACIÓ   
7900    JIMENO     EMPLEAT    7698 03/12/1981  123500             30       VENDES         
7902    FERNÁNDEZ  ANALISTA   7566 03/12/1981  390000             20       INVESTIGACIÓ   
7934    MUÑOZ      EMPLEAT    7782 23/01/1982  169000             10       COMPTABILITAT

create view DEPT_PARELL
as select * from DEPT where mod(dept_no,2) = 0;

Les operacions d’actualització (INSERT, DELETE i UPDATE) són, per als diversos SGBD, un tema conflictiu, ja que les vistes es basen en sentències SELECT en què poden intervenir moltes o poques taules i, fins i tot, altres vistes, i per tant cal decidir a quina d’aquestes taules i/o vistes correspon l’operació d’actualització sol·licitada.

Per a cada SGBD, caldrà conèixer molt bé les operacions d’actualització que permet sobre les vistes.

Cal destacar que les vistes en MySQL poden ser actualitzables o no actualitzables: les vistes en MySQL són actualitzables, és a dir, admeten operacions UPDATE, DELETE o INSERT com si es tractés d’una taula. Altrament són vistes no actualitzables.

Les vistes actualitzables han de tenir relacions un a un entre les files de la vista i les files de les taules a què fan referència. Així, doncs, hi ha clàusules i expressions que fan que les vistes en MySQL siguin no actualitzables, per exemple:

• Funcions d’agregació (SUM( ), MIN( ), MAX( ), COUNT( ), etc.)
• DISTINCT
• GROUP BY
• HAVING
• UNION
• Subconsultes en la sentència select
• Alguns tipus de join
• Altres vistes no actualitzables en la clàusula from
• Subconsultes en la sentència where que facin referència a taules de la clàusula FROM

Una vista que tingui diverses columnes calculades no és inserible, però sí que es poden actualitzar les columnes que contenen dades no calculades.

create view EMPD
as select emp_no, cognom, ofici, cap, data_alta, salari, comissio, e.dept_no, dnom
  from emp e, dept d
  where e.dept_no = d.dept_no;

update empd set comissio=10000 where emp_no=7782;

update empd set dnom='ASSESSORIA COMPTABLE' where emp_no=7782;

create view EMPD
as select emp_no, cognom, ofici, cap, data_alta, salari, comissio, e.dept_no, dnom
  from emp e, dept d
  where e.dept_no = d.dept_no;

delete from empd where emp_no=7782;

insert into empd values(7777,'PLAZA','VENEDOR',7698,'1984-05-01',200000,NULL,10,NULL); 

create view DEPT_PARELL
as select * from DEPT where mod(dept_no,2) = 0;

insert into DEPT_PARELL values (60,'INFORMÀTICA', 'BARCELONA');

insert into DEPT_PARELL values (55,'MAGATZEM','LLEIDA');

create or replace view DEPT_PARELL
  as select * from DEPT where mod(dept_no,2) = 0 with check option;
 
insert into DEPT_PARELL values (65,'MAGATZEM2','GIRONA');

create or replace view DEPT_PARELL
  as select * from DEPT where mod(dept_no,2) = 0;
 
update DEPT_PARELL set dept_no = dept_no+1 where dept_no = 50;

delete from DEPT_PARELL where dept_no IN (50, 55);

La seva sintaxi és aquesta:

rename <nom_actual> to <nou_nom> [, <nom_actual2> to <nou_nom2>, ....];

La seva sintaxi és aquesta:

truncate [table] <nom_taula>;

TRUNCATE és similar a delete de totes les files (sense clàusula where). Funciona, però, eliminant la taula (DROP TABLE) i tornant-la a crear (CREATE TABLE).

Per crear una base de dades o un esquema es pot utilitzar la sintaxi bàsica següent:

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] nom_bd;

Per modificar una base de dades o un esquema es pot utilitzar la sintaxi següent, que permet canviar el nom del directori en què està mapada la base de dades o el conjunt de caràcters:

ALTER {DATABASE | SCHEMA} nom_bd
  { UPGRADE DATA DIRECTORY NAME 
  | [DEFAULT] CHARACTER SET [=] nom_charset
  | [DEFAULT] COLLATE [=] nom_collation } ;

Per eliminar una base de dades o un esquema es pot utilitzar la sintaxi bàsica següent:

DROP {DATABASE | SCHEMA} [IF NOT EXISTS] nom_bd;

Una vegada que sabem definir taules, vistes, índexs o esquemes, i com modificar, en alguns casos, les definicions existents ens sorgeix un problema: com podem accedir de manera ràpida als objectes existents?

Es pot veure en la figura l’eina MySQL Workbench en l’apartat SQL Development com es pot navegar pels diferents objectes de les bases de dades que gestiona MySQL.

És important saber, també, que l’SGBD MySQL ens proporciona un conjunt de taules (que formen el diccionari de dades de l’SGBD) que permeten accedir a les definicions existents. N’hi ha moltes, però ens interessa conèixer les de la taula. Totes incorporen una gran quantitat de columnes, la qual cosa fa necessari esbrinar-ne l’estructura, per mitjà de la instrucció desc, abans d’intentar trobar-hi una informació.

Hi ha formes abreujades, però, de mostrar la informació d’aquestes taules del diccionari; per exemple, per mostrar les taules o les columnes de les taules, podem utilitzar aquestes formes simplificades:

SHOW TABLES
 
SHOW COLUMNS
  FROM nom_taula
  [FROM nom_base_dades]