D116. Mòdul 4. Pràctica 4 .Creació i gestió d'entorns web dinàmics (PHP)

Mòdul 4

Creació i gestió d'entorns web dinàmics (PHP)

Pràctica

Exercicis

Llenguatge SQL

En aquesta pràctica pretenem donar a conèixer les operacions bàsiques que es poden fer amb l'SQL i que tenen una aplicació directa amb la creació d'aplicacions web.

SQL: llenguatge estructurat de consulta

Les aplicacions a la xarxa són cada dia més nombroses i versàtils. En moltes ocasions, l'esquema bàsic d'operació és una sèrie de scripts que regeixen el comportament d'una base de dades.

Per la diversitat de llenguatges i de bases de dades existents, la manera de comunicar-se entre unes i altres seria realment complicada de gestionar, si no fos per l'existència d'estàndards que ens permeten fer les operacions bàsiques d'una manera universal.

L'empresa IBM a finals dels anys setanta va crear per al seu producte DB2 un llenguatge anomenat SQL ( Structured Query Language). Com sempre, empreses de la competència van dissenyar paral·lelament altres SQL a mida de les seves necessitats. Va ser prop dels anys vuitanta que el comitè ANSI va definir un estàndard per a l'SQL.

L'SQL no és un llenguatge per a procediments, en contra d'altres llenguatges que en aquells moments dominaven la programació de la manipulació de dades com el COBOL i C. L'SQL era un llenguatge amb un seguit de paraules reservades, que junt amb les dades s'utilitzava per fer una acció. Actualment, l'última revisió es va fer l'any 1992 (ANSI - 92).

Aquest llenguatge no és més que un llenguatge estàndard de comunicació amb base de dades. Parlem, per tant, d'un llenguatge normalitzat que ens permet treballar amb qualsevol tipus de llenguatge en combinació amb qualsevol tipus de bases de dades.

El fet que sigui estàndard no vol dir que sigui idèntic per a cada base de dades. De fet, determinades bases de dades implementen funcions específiques que no han de funcionar necessàriament en d'altres.

A més d'aquest universalitat, el llenguatge SQL posseeix una potència i versatilitat notables que contrasten, per altra banda, amb la seva accessibilitat d'aprenentatge.

Components de l'SQL

El llenguatge SQL està distribuït en quatre grans blocs, que es combinen per crear, actualitzar i manipular les bases de dades:

Comandes
Clàusules
Operadors
Funcions

Comandes

Hi ha dos tipus de comandes en SQL:

Les DLL, que permeten crear i definir noves bases de dades, camps i índexs.

Les DML, que permeten generar consultes per ordenar, filtrar i extreure informació de la base de dades.

Comanda DLL	Descripció
CREATE	Codi per crear noves taules, camps i índexs
ALTER	Utilitzat per modificar taules afegint camps o combinant la definició dels camps
DROP	Instrucció per eliminar taules, camps i índexs

Figura 4.4.1. SQL. Comandes DLL

Comanda DML	Descripció
SELECT	Codificació per consultar registres de la base de dades que compeixen un criteri determinat
INSERT	Utilitzat per carregar blocs d'informació a la base de dades
UPDATE	Instrucció que modifica els valors dels camps i registres especificats en els criteris
DELETE	Codi per el.liminar registres d'una taula de la base de dades

Figura 4.4.2. SQL. Comandes DML

Clàusules

Les clàusules són condicions de modificació utilitzades per definir les dades que es desitgen seleccionar o manipular.

Clàusula	Descripció
FROM	Utilitzada per especificar la taula de la qual se seleccionen els registres
WHERE	Clàusula per detallar les condicions que han de reunir els registres resultants
GROUP BY	Codi utilitzat per separar registres seleccionats en grups específics
HAVING	Utilitzada per expressar la condició que ha de complir cada grup
ORDER BY	Utilitzada per ordenar els registres seleccionats d'acord a una ordenació específica

Figura 4.4.3. SQL. Clàusules

Operadors

Operador lògic	Descripció
AND	Equival lògicament a i. Avalua dues condicions i retorna un valor cert, si ambdues condicions són certes
OR	Equival lògicament a o. Avalua dues condicions i retorna un valor cert, si alguna de les condicions és certa
NOT	Negació lògic. Retorna un valor contrari a l'expressió

Figura 4.4.4. SQL . Operadors lògics

Operadors de comparació	Descripció
<	[...] inferior que [...]
>	[...] més gran que [...]
<>	[...] diferent a [...]
<=	[...] més petit o igual que [...]
>=	[...] més gran o igual que [...]
=	[...] igual que [...]
BETWEEN	Utilitzat per especificar un interval de valors
LIKE	Codi utilitzat en la comparació d'un model
IN	Operador per especificar registres d'una taula

Figura 4.4.5. SQL. Operadors de comparació

Practiquem

En principi farem aquests exercicis des del PHPMyAdmin, la pestanya de 'Consulta o SQL ' o des de la finestra esquerra inferior "Finestra de consulta". Entrarem el codi SQL i l'executarem per veure'n els resultats. Això és necessari, ja que quan programem amb el PHP o amb qualsevol altre llenguatge, per extreure dades de la base de dades, ho haurem de fer amb les instruccions SQL.

Consultes

Per entrar en situació, proposem seleccionar tota la taula professor i veure què conté, la instrucció SQL serà la següent:

SELECT * FROM professor

Sentència SQL 1

El signe '*' vol dir que seleccioni tots els camps que contingui la taula, en aquest cas, professor. A més, la comanda SELECT pot portar adjunt un paràmetre [ ALL | DISTINCT | DISTINCT ROW ]. El valor per defecte és ALL, que admet el retorn de files duplicades, mentre que amb les altres dues opcions ometem en el resultat les files duplicades.

Figura 4.4.6. SQL. Creació de consulta1

Si premeu el botó Executar, obtindreu una visualització dels resultats de la vostra consulta, on visualitzem les dades que tenim entrades a la taula professor.

Com a nota interessant, si premeu el botó Examinar podreu guardar les instruccions SQL de la finestra de text en un fitxer amb extensió 'sql' si fos necessari.

Ara limitarem més la nostra consulta, ja que volem les dades de les professores; llavors haurem de filtrar la consulta pel camp 'sexe', tal com :

SELECT * FROM professor WHERE sexe=2

Sentència SQL 2

Si volem extreure el nom i els cognoms de les professores que estan d'alta al centre des de 1987, ordenat per nom, seria un codi SQL tal com:

SELECT nom, cognom1, cognom2 FROM professor WHERE ( sexe =2 AND anyalta >1987) ORDER BY nom

Sentència SQL 3

Que en llenguatge de carrer, vindria a esser : " SELECCIONA els camps nom, cognom1, cognom2 DE la taula professor, ON es compleixi que el camp sexe sigui igual a 2 (dona) I que el camp anyalta sigui més gran que 1987, tot això ORDENAT PER nom.

Figura 4.4.7. Resultat sentència SQL 3

Observeu que en aquest cas només observeu els camps nom, cognom1, cognom2 i ordenats per nom. Una ordenació a la qual estem força acostumats seria: ...ORDER BY cognom1, cognom2, nom.

Compliquem una mica més la consulta i anirem a buscar dades que es troben en dues taules, a la taula alumne, i alumany. De la taula alumne, extraurem nom, cognom1, cognom2 i de la taula alumany, extraurem curs_acad i promoció. Amdues taules van lligades pel camp matrícula que identifica cada alumne.

SELECT alumnes.nom, alumnes.cognom1, alumnes.cognom2, alumany.curs_acad, alumany.promocio
FROM alumnes, alumany
WHERE alumnes.matricula = alumany.matricula
ORDER BY alumnes.cognom1, alumnes.cognom2,alumnes.nom

Sentència SQL 4

La mateixa consulta en un altre format seria:

SELECT al1.nom, al1.cognom1, al1.cognom2, al2.curs_acad, al2.promocio
FROM alumnes AS al1, alumany AS al2
WHERE al1.matricula = al2.matricula
ORDER BY al1.cognom1

Sentència SQL 5

En aquesta sentència, hem afegit la clàusula 'AS', que serveix per definir un sobrenom o un àlias. Fixeu-vos que ara ens referim a la taula 'alumnes' com 'al1' en tota la sentència SQL.

A l'hora d'establir un vincle, s'utilitza la paraula reservada 'JOIN', que té un significat de lligam o vincle entre dues taules. Fixeu-vos que el nom de la columna cur_acad l'hem modificat per 'curs acadèmic'.

SELECT al1.nom, al1.cognom1, al1.cognom2, al2.curs_acad AS 'Curs acadèmic', al2.promocio
FROM alumnes AS al1
JOIN alumany AS al2
WHERE al1.matricula = al2.matricula
ORDER BY al1.cognom1

Sentència SQL 6

El resultat d'aquesta consulta donaria quelcom semblant a això:

Figura 4.4.8. Resultat sentència SQL 6

Ara afegirem el nom del segon tutor legal (taula tutlegal), al nom i cognoms de l'alumne/a.

SELECT al.nom, al.cognom1, al.cognom2, tl.nom AS 'Nom 2n tutor/a', tl.comunicats
FROM alumnes AS al
LEFT JOIN tutlegal AS tl ON al.idtutor2 = tl.idtutor
WHERE al.nom IN ( 'cristina', 'juan', 'jordi' )
ORDER BY al.cognom1

Sentència SQL 7

Observeu que hem modificat el JOIN per LEFT JOIN i hem afegit la partícula ON, que identifica els camps que fan de lligam, i en la clàusula WHERE hem afegit que filtri només els noms d'alumnes que siguin: cristina, juan, jordi. Observeu el resultat:

Figura 4.4.9. Resultat sentència SQL 7

Podem veure que la Cristina Arroniz no té segon tutor definit, i ens presenta un NULL (objecte buit). En aquest cas, la sentència SQL cerca de la taula alumnes els que compleixen les condicions del 'WHERE' , i mostra els respectius segons tutors. Modifiquem ara l'ordre de les taules al voltant de LEFT JOIN. Recordeu que 'LEFT' significa 'Esquerra'.

SELECT al.nom, al.cognom1, al.cognom2, tl.nom AS 'Nom 2n tutor/a', tl.comunicats
FROM tutlegal AS tl
LEFT JOIN alumnes AS al ON al.idtutor2 = tl.idtutor
WHERE al.nom IN ( 'cristina', 'juan', 'jordi' )
ORDER BY al.cognom1

Sentència SQL 8

Figura 4.4.10. Resultat sentència SQL 8

Observeu que la Cristina Arroniz no existeix. Això és perquè LEFT JOIN llegeix la taula de l'esquerra (en aquest segon cas: tutlegal) i només mostra els/les pares/mares que actuen com a segons tutors i els seus fills es diuen cristina, juan o jordi. Recordeu que la Cristina Arroniz no tenia segon/a tutor/a legal.

També podriem utilitzar la clàusula 'RIGHT JOIN', i ara el seu significat s'inverteix, ja que ordena que la taula predominant és la que es troba a la dreta del 'JOIN'. En alguns servidors de BD, aquesta clàusula no és permet, tot i seguir l'estandard SQL-92.

Crear una taula

Crearem mitjançant sentència SQL la taula seguiment, amb les instruccions següents; podem observar la instrucció fonamental CREATE TABLE seguida del nom de la taula. Dins del parèntesi ens trobem les característiques dels camps que la compondran (iden, matricula, curs_acad, data, origen, codiprof, comentari ); seguidament definim l'índex principal (iden) i un index secundari (matricula).

CREATE TABLE `seguiment` (
`IDEN` int(10) unsigned NOT NULL auto_increment,
`MATRICULA` int(11) NOT NULL default '',
`CURS_ACAD` year(4) NOT NULL default '0000',
`DATA` date NOT NULL default '0000-00-00',
`ORIGEN` enum('Tutor/a','Junta Avaluació','Equip Docent','Coordinació','Professor/a') NOT NULL default 'Tutor/a',
`CODIPROF` varchar(8) default NULL,
`COMENTARI` text,
PRIMARY KEY (`IDEN`),
KEY `MATRICULA` (`MATRICULA`)
) TYPE=MyISAM COMMENT='Dades seguiment de l''alumnat' AUTO_INCREMENT=1 ;

Sentència SQL 9

En alguns camps hem introduït el paràmetre NOT NULL, que força que aquest camp no pugui ser NUL quan donem d'alta un registre a la taula. És perfectament lògic que si introduïm un registre a la taula seguiment per força el camp 'matricula' i 'curs academic' no poden estar buits.

Quan definim la clau primària d'una taula, s'han de complir uns criteris:

El contingut és únic per cada registre i no es pot repetir.
Per cada nou registre s'obté immediatament un valor que no pot ser l'element buit.

Inserció de dades a la taula

La plantilla per inserir un registre en una taula és la següent:

INSERT [INTO] nom_taula [(camp1,camp2...)] VALUES (valor1,valor2...), .....

Afegim un registre a la taula 'professor', amb les dades següents. Introduïu aquest text seguit en la instrucció SQL, executeu i observeu la taula professors.

INSERT INTO `professor` ( `IDEN` , `CODI` , `NOM` , `COGNOM1` , `COGNOM2` , `SEXE` , `EMAIL` , `ANYALTA` , `ANYBAIXA`)
VALUES ( '' , 'XX-D116' , 'Jordi Xavier' , 'Montseny', 'Arrugat' , '1' , 'jxmont@d116.net' , '2002' , '2003' );

Sentència SQL 10

Figura 4.4.11. Resultat sentència SQL 10

Modificació de l'estructura de la taula

Per modificar una taula s'utilitza la comanda ALTER TABLE. La seva sintaxi és molt semblant a la de CREATE TABLE, però amb més opcions.

La plantilla és: ALTER TABLE nom_taula seguit d'alguna de les següents opcions

Opcions disponibles	Descripció
ADD [COLUM] camp tipus_dada [NOT NULL \|NULL] [DEFAULT valor_defecte] [AUTO_INCREMENT]	Afegeix una nova columna a la taula
ADD PRIMARY KEY (nom_index_primari)	Afegeix una nova clau primària a la taula
ADD INDEX [ nom_index, .......]
ALTER [COLUM] camp {SET DEFAULT \| DROP DEFAULT }	Introduïm el nou valor estàndard
CHANGE [COLUM] camp_vell definició_camp	Canvia un element del camp i modifica el nom de la columna
MODIFY [COLUM] definició_camp	Canvia la definició del camp, sense modificar el nom del camp
DROP [COLUM]	Elimina una columna
DROP [PRIMARY KEY]	Elimina una clau primària
DROP INDEX [ nom_index,....]
RENAME AS nom_nova taula	Reanomena el nom de la taula

Figura 4.4.12. Opcions de sentència ALTER TABLE

Ara hem d'afegir les columnes 'contrasenya' i 'nivell_seg' a la taula professor, amb els paràmetres següents:

ALTER TABLE professor ADD COLUMN `CONTRASENYA` varchar(40) NULL , ADD COLUMN `NIVELL_SEG` smallint(4) NULL

Sentència SQL 11

Figura 4.4.12. Resultat de l'estructura de la taula professor segons sentència SQL 11

Modificar dades d'una taula

La plantilla per modificar dades existents d'una taula és la següent:

UPDATE taula SET camp1=valor1 , [camp2=valor2] ... [WHERE expressió relativa]

UPDATE professor SET nom= 'Joan' WHERE codi= 'XX-D116'

Sentència SQL 12

Figurar 4.4.13. Resultat de la sentència SQL12

Esborrar dades de la taula

El format de la instrucció per esborrar dades d'una taula és:

DELETE FROM nom_taula [WHERE expressió_relativa]

Aneu amb compte: si esborreu dades d'una taula no es podran recuperar. En el cas que no utilitzeu el WHERE, s'esborraran totes les dades de la taula.

Esborrar taules

La plantilla per utilitzar la comanda d'esborrar taules és:

DROP TABLE [IF EXISTS] nom de la taula

Si utilitzeu aquesta instrucció en un programa, assegureu-vos de posar la clàusula IF EXIST, ja que si la taula no existeix, no executarà l'ordre i ens estalviarem un control d'error.

Pràctica

Exercicis