Bases de dades. Apunts

EST CPBD DIPLOMATURA D'ESTADÍSTICA BASES DE DADES Marta Franquesa Niubó r UNIVERSITAT POLITECNICA DE CATALUNYA Biblioteca [ l�IIH'IH(IIHI FACULTAT DE MATEMÁTIQUES I ESTADÍSTICA Apunts del Structured Query Language S.Q.L. Departament de LLenguatges i Sistemes Informatics Autora: J\·forta Franqnesa Niubó Cms 1003-!)4 1 1. Breu descripció del llenguatge SQL El StrueturPd Query Langm1gP, a partir d'ara SQL, é:, un llenguatge estructurat de consulta. Si be és eert (1ue e:, pot considerar com Pl llenguatge standard per el model de Bases ele Daeles rPlaeional (ANS/X3H2), també ho és que encara exi:;teixen certes variants depenent del sistema en el qual es treballa. En aque:;ts apunts es presenta el que podriem anomenar el nudi comú, tot i aixú e:, poden donar caso:, en que la sintaxi de les comandes sigui un pel diferent. Les bases de dades podt>n visnalitzar-s1·· 1·om 1111 ,·oujunt el,, taules amh files i colunmes. La interneccicí cl'una fila i una columna c1·1111a taula constitueix el que anomenem una dada. En SQL l'usuari indica les dades tllll' dPsitja d'mia. taula, perc'i no el procediment per a n·rnpt>rar-lc>i;. Es 1•1 sisl PIIHl el , ¡tw d1·tnmi11a la fornm de for-ho. SQL és nn llengnatge nou-prnc:ednrrd, la qnnl crn;a si_e;uilfr,¡, qtt<·' només dedilres allcÍ qué vols i no c-Óm ho vals, Cl :;ia. és un lleugu.�tg1• clPdara.ti11, E11 d'ultn·s ll1�nguatges dP bases de dades, l'usuari ha ele coneixer com s'enunagatzema la infnnnarió per tal de proporcionar els pa.<,;sos necessaris per a visualitzar les dades. El llenguatgP SQL consta ele rdativanw11t poqnes c1mu111des, i tot i així compta amb totes les opPraeions ue,•pssiiries per a ddinir tanle:, i llisti>s, i ¡wr a consultar, actualitzar, esborrar oinsPrir informació. Les dues da.rrerPs característiques converteixen a aq1wst llengnatgP en un dels mé.s potents que es coneixPn a l'actualitat. Amb SQL es pot trPballar de dues nwneres, o lw de forma interactiva, o be introduint les comandes en un programa Pscrit en un altre llengnatge. Quan es treballa de forma interactiva, les coma.ndPs s'Pxecuten d'una en una i els resultats aparPixen inmecliatament després a la pantalla.. Existeix la possibilitat d'enuna.gatZf�mar els re:mltats cl'una consulta Pn un fitxPr auxili,lr. Cada com,uHla o sentPncia. SQL comenca amb una paraula clau que dona nom a l'o¡wració q11e cal n·,ditzar. Cada. comauda ha de rPalitzar due:- funcions: •Especificar les dadPs q11e inter<->sse11 •Indicar la tasca (lUP cal rPalitzm muli aq11c0stes rlades 2. Creació de taules en SQL Les taules sc'm els elemP1lts h;1sics cl'm1a. hase de dncfos SQL. Les tanles poden definir-se ele formes diferents. La c'.omauda CREATE TABLE s'utilitza per definir !'estructura de la taula. La. comaucla ALTER TABLE s·en·eix ¡ier afegir eolunmes a. una taula ja Pxistent. El forma.t de la comawla de clefi11icic'> íle l'estr11ct11rn rl'11na tanla és : CREA.TE TABLE <nom de la ta.nla> ( <nom de lH colunma> <ti¡rns de clacles>) [,<nom rle la columna> <ti¡ms rle daclt>s> ... ]); l On el tipus de dades pot sPr : Euters N Úmeros l'f'als Serif' df' n c.arúctf'rs INTEGER. FL0.4.T CHAR.(n) DATE LOGICAL Tipus data, format ¡wr clt>fel'te : DD�HvIAA Tipus l<'>gic o boold1 S'han presf'ntat els tipus de dadf's m<�s n:ma.ls. Per més informa.ció consulten el manual de SQL-VAX/Digital. Anem a veure un exemplt, de l'.l'f'aciú cl'una taula t�ll SQL. Per aixó utilitzarem el cas que es presf'nta a l'annex (vt>un' a.imf'x). Suposem qw� volf'm crear la taula DIRECTOR, aleshores caldria for: CREATE TABLE dirt>ctor (dn CHAR(2), nom CHAR(20), qtydoc INTEGER, ¡mis CHAR(lO)); Com ja hem clit aba.ns, és possililf' afegir uoves columnes a una ta.ula ja creada amb la utilitza.ció de la cnnrnnda ALTER. Vei<�lll un exempl<�. Suposem que volem afegir la data de naixement del din�ctor a. la ta.ula: ALTER TABLE dired.or ADD (data DATE); 3. Consultes SQL La comanda que s'utilitza ¡wr tal de realitzar consultes és SELECT. Amb una sola comanda SELECT es pot indicar al SQL qu<� proporcioni qualsevol conjunt de dades de una o més taules. La sintaxi completa dt> la rnman<la és : SELECT <clausula> FROM <clausula> [WHERE <clausula>] [GROUP BY <clausula>] [HAVIN G < clausula> [UNION subsdecció] ... (ORDER BY <clausula>]; La consulta més senzilla qrn"' t'S pot realitzar amb una comanda SQL és la segiient : SELECT <rnlmnua> FRO:vl <taula>; Per exemple, ¡wr fer una <'onsnlta <l<-' t.ots t>ls títols disponibles a la. taula docum (veure atlllt'X), només cal fer : 2 SELECT titol FRO:\I donun: El resultat de la ,·011s11lta <pw es mostrn rles¡m;s de la comanda. de SELECT no s'emmagatzema. ui es pot cous11ltar cles¡m�s rl'acaharla 1't•xc•c•ucic1. Malgrat a.ixó, per la ca<;a AX/Dip.;ital, t s 11ot. 11tilit,z,1r l;i c·rn11,111<lr1: SET OUTP ºT N<Jtf.EXT per emmagatzemar <'l n·snltal en tlll Htxn nw11np11.1t NO:-.I.EXT de ma1wrn q11e gnardt'm el resulta de la 1·nus1t!t· per tt-11.ir-lo rlisprnul,J,. si t·l m•1·,·ssit.1·'m mPs i>ndava.nt. Caldria fer: SQL> SET OUTPUT 110111.ext SQL> SELECT ... SQL> SET NOOUTPUT Cmn ja. s 'lrn esmentat al 'inici dds ap11uts, no tots ds sistenws treballen d'igua.l manera amb SQL. L 'opeic'> de ficar les dades n�sult.ants d 'nna consulta en un fitxer és una de les més depenents de cacla fahricant. En a,¡11est. cHs s'lm presentat l'opci<'i que tenim al nostre abast, és adir, \!AX/Digital. La forma de seleccionar totes les rnlnnuws d 'mm taula és utilitzant el simbo! * : SELECT * FROM tanla; Per tal de que en nna tanla 1wmPs hi apnreixin fill's rlift•rP1lts, és a rlir, sPnse repeticions cal utilitzar la pa.ra.nla dan : DISTINCT en la rfousnla SELECT.Per exemple per a consultar el:, anys en quP s'lw rodat els clonm1eutals rle la tanla docum (veure annex), caldria fer la consulta: SELECT DISTINCT c1uy FR(Hd rlornm; D'aquesta manera enea.ra qne en nn matPix any s'lrngin roda.t varis documenta.Is el numero ele l'any llOlllP.S a.pareixera una. Vl'gada. La clausula \VHERE ens pernwt dc�finir collílicious rPlatives a les files que cal visua.litzar. Per exemplP si volem ccms1tltar f'ls títols <lf'ls clc><'lllllPlltals roda.ts a. l 'any 1990 ( veure annex) caldria fer SELECT titol FROJv1 donun \:VHERE any=l9!Jü; El llengua.tgP SQL ,lisposa rl'm1 rnu,iuut d'opermlors de <'.ompa.ració i opera.clors lógics amb els que podem definir els l'Pq11erinwnts rle les ronrlirious quP han ch� sa.tisfer les columnes que volem seleccionar. Així matPix incorpora. uu ,·onjnnt de funcions pr<'ipies. •Operadors de comparació = < > <= >= <> Igual :tvIPs pet.it qne :'.viés gran qne 1ifruor o igual lvh�s grnu o igual Diferent 3 •Operadors logics Negaci<'> NOT AND OR Comhinaei<Í ele ronclicions nmlJ I Comhinnri<Í de> rondirions mnh O •Funcions SQL COUNT() SUM() MIN() MAX() AVG() Compta el número de filps seleC<'.Íonacles Suma els valors cl'una. cohunna num1�ri1'.a Troha el valor mínim d\urn colmnna. Troba el valor mú.xim d'una columna Cúkul de la mitjana d 'uua columna Anem a veure a.lgnns exemplc>s d 111tilitzacic'i d 'aquestes funcions pel cas que es presenta a l'a.nnex. -Esbrina.r el ni'mwro d1° docnmc>utals de que es 1lisposa : SELECT COUNT(*) FRO:i\'l donun; -Eshrinar el nÚmf'ro dP projp1•cions PnwsPs < lP tots els documenta.Is : SELECT SUM(nprojc>c) FROM donun; -Esbrinar quin P.S d dornnwntal rp1P ha estat Pn11'..s lllPS cops : SELECT titol FROM donun WHERE nprojec'.=MAX(nprojec); eAltres predicats BETVVEEN IN LIKE Comprova. si m1 v<1lor es troha dins d 'uns límits indicats Comprova si nu valor roinricleix amh a.lgun element d'una llista Compara una columna de� tipus carúcter amb una serie indicada Exemples cl'utilitzaci<'> d'aqnests pn·clieats -Seleccionar els t.ítols dels donmwuta.ls que> hau esta.t projt\ctats entre 10 i 20 vega.eles: SELECT titol FROM docum WHERE nprojec BETvVEEN 10 AND 20; -Sdeccionar els títols d<>ls donmwnt.als que liau estat roda.ts a Espa.nya o Italia: SELECT titol FROM clonun WHERE ¡mis IN ('Espanya.', 'Iti,lia.'); Seleccionar tots ds títols dels donunentals <pw ha.gin estat rodats en un país que comenci per la lletra I: SELECT titol FROM dornm vVHERE pais LIKE 'L'; 3.1 Ordenació de les dades Si f'S de:;itja. modificar l'orrln° eu c¡w-· apan�ixeu l1!s clacles n�snltants de fer una consulta, cal utilitzar In d:\11s11la ORDER BY " h1 sn1t.1 '..111·ia SELECT. La sintaxi t's ele la forma: 4 SELECT colmuun l. cohmurn2, ... nilnmw1?\ FR O �,f ta ula ORDER BY colnmual, cohuuua.J: Així el res11ltat de·• la cousnlt.a n¡ian··ix<·'rit. f'!l <mln° ascf'nclent dels valors de la columna!, i en cas d'amhigiíitat ¡wr orclre ele• la columua.J, Ptc:. Si 110 s'es¡wcifica res més l'ordre q1w Ps iwguirit. s1•dt ;1se·1•uckut ( q1w 1�s l'ordre ¡wr deft>rte ). Si es desitja canviar el criteri d'ordPnació cal explicitar-110, posant DESC. Un Pxemple f'll el cas clPls donmw1it.als (anuex) poclria ser : -St->lc,·,·imwr ,•Is títols ch·b dorn111e·11tals ele· la t,111!:1 DOCUM per onlre el any dP rodatge, en p] ,·as 1hqn1-• we'..s c\"1u1 don1111entnl s'haµ;i n•alitza.t Pn un matf'ix auy, or lenar-los per uúnwro el,· ¡n·ojen·ious P!1H'se•s: SELECT titol. auy, u¡irnjp,· FROM donun ORDER BY any. nprojec; 3.2 Agrupació de registres Les clausules GROUP BY i HAVING, ¡wmwten organitzar les files per grups. La clausula GROUP BY coml,iua fil1·s cl'mw taula ele resnltats en grups. Un grup es compasa per aq11dls Plenu.,uts ele• la colnmua ep1e t.em'n d matPiX valor. Cada grup es redueix a una sola fila. de la tan la I le• resnlt.a ts. La rlánsnla HA VIN G sdecciona els grups de forma similar a \�HEnE al sPle 0<.-cionar filPs. A<pll'sl,a dúnsula indica una condició que cada grup cal que complcixi ,d,nns d'a¡ian·ix<T eu el llist at ck res11ltats. Per PXPmple, si es desitja olJt1'nir f•l rndi (lds directors que han dirigit més de dos documenta.Is, caldria fer: SELECT dn FR01vl donun GROUP BY clu HAVING COUNT(*)>2: Finalment, la clausula UNION ¡wrmd cmnl,iw11' cous11ltPs de dnes o més sentencies SELECT difPrt'nts diminaut tot.es ks fil,-•s dn¡ilic·¡Hlt-'s. Per exemple, si volem visnalitzar t.ots ds pai"sos ck totes les taules, tant de documentals com de diredors, fariem: SELECT pais FROM doe11m UNION SELECT pais FR.01·! director; ;J 4. Actualització en SQL Les coma.ueles Il\'SERT. l:PDATE i DELETE pennl't<"ll afi>µ;ir, eauviar o eliminar dades di> les taules. La sintaxi di> la ,·omanda d'iuserci<Í INSERT el'una o varies files a una taula és: INSERT INTO <nom de la tanla> (<llista ele la columna>] VALUES <llista ele valors>; Per exi>mple, per afegir 1111 non clin>rtor a la ta.ula ele l'.mnf'X : INSERT INTO dirertor VALUES ('D5', ·ROIG'. 4, 'ITALIA'); D 'aquesta manera s 'indonra a la taula din·ctor d uou director de 1·odi D5, de nom Roig, que ha clirigit 4 clo1·1mw11tals i qtw n·sicleix a IU1lia. La rnmanda INSERT pennet insertar files d'altres taules utilitzant la <·011u1111la SELECT. Es possihJ<, efectuar una HC't.11alitz¡¡1•ic'i <IP les dael1°s dins <k, la tanla, amb la comanda UPDATE. Per PXempl<, si nil1·m inc.n•nwntar el nnnwro dP prnjPccions del documental que te per codi pcodi='Pí'. de la tanla doeum : UPDATE docum SET ( n¡ irojt'c=nproj<·c+ 1) WHERE ¡wodi='P7'; Per tal d'esborra.r files <l'uua tanla col utilitzar la 1·omct111la DELETE. Aquesta operació elimina les files dPtennina.des ¡wr lc1 dimsnla de roueli,·ii'i. La seva sintaxi és: DELETE FROlvf <nom de la taula> ,i\THERE <eomlieio>; G Rt->sultat: X.TITOL LA DONA LA GRAN.TA EL CAJvIP EL SOL HO:tvIE 5 rows sdert.e<l Y.:\'(>:\f GARCIA GARC'IA PAOLETTI PAOLETTI PAOLETTI Y.PAIS ESPA�YA ESPANYA ITALIA ITALIA ITALIA Nota.: Obsf'n•em ljlH' X i Y s 'iudonc-·11 pt'l' P,-itar mnliip,iiita ts Qll. Esbriuar de• <[ltnuts din•ct.ors difrn,ut.s tc·uim iufonnacic'> SELECT COlJNT(*) FR01I DIRECTOR; Resultat: 5 Q12. Ohtt>uir per ordre 1Tmwlo,1!;i1· 1l1• roclatgc> ds tít.ols rlds docunwntals, l'any en que s'han roda.t, i el país de,! rodntgt->. SELECT TITOL, ANY, PAIS FROM DOCU:M ORDER BY ANY, TITOL. PAIS: Resultat: TITCL ANY PAIS LA GRANJA 107S FRANCA LA VACA 1084 FRA:\'C'A EL CAMP 198G ESPANYA ARBRE 1!)!)0 ITALIA EL RllJ 1990 ITALIA LA DONA 19!)1 ESPANYA LA LLUNA l!)!) 1 FRA�C'A HOME 1992 ESPANYA LA :MAR 1992 ESPANYA EL SOL 1993 ESPANYA 10 rows sdectecl 14 5. Practica amb S.Q.L. •Dis:•a-'ny1-'\\ 1111a lrnsl' ck dacl1's ¡wr h1 uost.rn t·ompauyia de-' donnnentals utilitza.nt el llt�nguatgP ch� c·011s11lta S(2L. Cal ddiuir lc•s ta11lc"s DOCU:tvI i DIRECTOR. •Un c.op 1,r1•acla la lrnst• 1k elades aml> totps les dades ele don1mP1itals i ele elirectors, realitzPu les rlotzP c·o11s11lks el 'c�x1·mple el,� l'apartat antt>rior i contrastP11 els resultats. •R,�a.litzeu ks segiieuts cons11lfrs: Q13. Olit.enir ds tít.ols, auy i país ele roclatge cl,,[s rlonmwutals ro<la.ts elespres ele 1990. El resultat ha d'apm·eix,�r ¡wr onln� 1T0110logic· Q14. OhtPnir d nombre de., projeccious emPsPs l'!l prnmig de carla 1rnís, juntament amb el nom dd país ele rodatgt·'. El l'l-':-mlt.at lrn de s1·r t•u onln· umneric. Q15. Obteuir els melis dc•ls clin•c·tors. qm' no signin el D3, cp1e hagin dirigit dos o mes elocunwntals ¡wr la uostra c·ompn11yi11. Q16. Ohtenir el uom clds din°1·tors <[1H" resirlei::,Tu al nrnteix país <!Uf' Pn Rovira quP han dirigit m1�nys documPntals rpH' ,.,¡¡_ Ql 7. ObtPnir 1111 llistat. amli d cocli cltclin•dor i d títol de <loc11mP1ital de tots els docnmPnta.ls. Aqw,st llistat. ha d 'a¡>,irl'iX1'r orcle1wt. ¡wr codi ch• dirPdor i en segon criteri per titol de <loc11nwutal. Q18. Obtt·'uir ds coelis de elirel"t.ors c¡1w han dirigit mt•s d'nu donm1Pntal finaw;at per la nostra. rompanyw. Q19. Obtenir tots 1-'!s cloc·nnH'Utals ,,[ats a Frnurn i dirigits ¡wr nn clirPctor qne hagi dirigit mes de 3 <loc'.\lllll-'llt.a.ls l'll l.1 :--1•v¡¡ vicl,1 profrssioual. Q20. Obtn1ir ds títols i pni"sos ele-' rncl.1t.p;e ele t.ots t'ls clonnnentals cptP han estat projectats mes dt-> 3 vep;ac ks. Q21. Eslirinar tots ds auys t�ll q1w ds clonmw1it,ds rodats han e::;tat projectats mes de 4 vagades eu r.onjunt. Q22. Quants dot·1mwnt.als lw dirip;it. t•l diret·tm amh rndi DO ¡wr la nosta eompanyia'? Q23. Obtf'liil' Pls uoms i rnclis deis elirel'tms q1w no n'sicleixen a Espa.nya i que han dirigit mPs d'un d()(:1mwntal ¡wr )¡¡ uostra t·ompauyia. Q24. Quius docnnwutals lw clirip;it. d seuym P11old.ti ¡wr la nostrn. companyia'?. En concret es d<�mana 1111 llistat. amh d títol. auy i uúnwni ck projeccious orclenat ¡wr d numero de projeccions, any i tít.ol. Q25. Obtc.,11ir l'ls wims clt•ls elin•c·t.1J1s c¡1H·' lim1 clirip;it 11ws cl'nn clocnnwntal finarn;at per la uo:;tra t'lllllp,1uyin .í\i_1�t"-11c!J n\tVl1•ix•t11" Iti,lia. 15