tema 01 - creació de taules amb sql

Create table

Miquel Boada25-11-2016

Desenvolupament aplicacions multiplataforma (DAM)

Mòdul 2 – Bases de dades - UF2 - llenguatges SQL: DML i DDL

Tema 1 – Introducció . Creació de taules

Objectius1. Creació de taules.

Coneixements previs• Model Entitat-Relació.• Model relacional.• Transformació del model Entitat-Relació al model relacional.• Sistemes gestors de bases de dades (SGBD)• Creació de base de dades

Requisits previs• Ordinador amb MySQL server instal·lat.• Client MySQL server (MySQL Workbench per la representació gràfica).• Connexió al servidor des del client • Una base de dades creada.

Autor: Miquel Boada Pàgina 1 de 18




Creació de taules amb SQLCreate table...............................................................................................................................................1Objectius....................................................................................................................................................1Coneixements previs................................................................................................................................1Requisits previs.........................................................................................................................................1Introducció................................................................................................................................................3Model físic..................................................................................................................................................3Transformació del model entitat-relació al relacional.........................................................................3

Símbols gràfics.....................................................................................................................................3Creació de taules de dades en SQL........................................................................................................3

Sintaxis «Create Table».......................................................................................................................4 Data Types............................................................................................................................................5

Tipus text: char i varchar...............................................................................................................5 Tipus char....................................................................................................................................6 Tipus varchar...............................................................................................................................7 Comparació emmagatzematge char varchar.........................................................................7

Tipus numèrics................................................................................................................................7 Tipus numèrics de precisió exacta...........................................................................................7

Integer.....................................................................................................................................8 Decimal....................................................................................................................................9

Tipus numèrics aproximats....................................................................................................10 Tipus data/hora.............................................................................................................................11

TIME...........................................................................................................................................12 DATE / DATETIME.....................................................................................................................12 DATETIME vs TIMESTAMP.......................................................................................................13

Creació taules – Exemples.....................................................................................................................15Webgrafia................................................................................................................................................16





IntroduccióEl model Entitat-Relació ens serveix per a poder representar mitjançant un llenguatge gràficuna estructura de dades associada a una determinada informació. La representació gràfica ésuna concreció del llenguatge natural a partir del qual s'ha generat el model. La conversió delmodel entitat-relació al model relacional aplicant les fòrmules de transformació ens permetobtenir les taules, els camps, la clau primària i altres elements que utilitzarem per a generarles taules.

Model físicEl model físic és la transformació del model relacional a instruccions executables en unsistema gestor de base de dades. Per a realitzar el model físic, cal tornar a recuperar algunsvistos en el model entitat-relació com els de domini, atribut identificador, valors NULL, atributcandidat ...

Transformació del model entitat-relació al relacional.La transformació del model entitat-relació al model relacional es pot realitzar utilitzant einesgràfiques que ajuden a la comprensió de l'estructura de les taules i milloren la documentacióde l'aplicació.MySQL Workbench és un client gràfic que porta un dissenyador gràfic per a dissenyar eldiagrama associat al model relacional. Per accedir al dissenyador gràfic cal crear un noumodel, ja sigui a través de l'opció de menú «File» i «New Model» o bé directament a través dela icona «+» associada a la pestanya «Home».

Símbols gràficsTot i que hi ha molts més símbols gràfics disponibles i possibilitats, el document només esmencionen aquells que es necessiten, ja que l'objectiu és crear les taules i les relacions entreelles sense necessitat de disposar de cap element gràfic d'ajuda. Tot i això, es considerainteressant també poder aprofitar les eines que faciliten la tasca quan el nombre de taulesd'una base de dades és important.La icona ens permet crear una taula. Les icones ens permeten relacionar lestaules. La selecció correcte del tipus de relació és important; el sistema crea de formaautomàtica els atributs i taules derivades del tipus de relació.

Creació de taules de dades en SQLTot i que es pugui uitlitzar l'eina gràfica per a crear la base de dades i taules associades, aquestsistema és lent i no aplicable quan s'han de realitzar modificacions a la base de dades: canvisen el tipus de dades, afegir noves columnes....





Per aquest motiu cal conéixer les instruccions per a crear i modificar l'estructura d'una taula.

Sintaxis «Create Table»CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

(create_definition,...)

create_definition:

col_name column_definition

column_definition:

data_type [NOT NULL | NULL] [DEFAULT default_value]

[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]

[COMMENT 'string']

data_type:

| INT[(length)] [UNSIGNED] [ZEROFILL]

| INTEGER[(length)] [UNSIGNED] [ZEROFILL]

| DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]

| FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]

| DECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL]

| NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL]

| DATE

| TIME[(fsp)]

| TIMESTAMP[(fsp)]

| DATETIME[(fsp)]

| CHAR[(length)] [BINARY]

[CHARACTER SET charset_name] [COLLATE collation_name]

| VARCHAR(length) [BINARY]

[CHARACTER SET charset_name] [COLLATE collation_name]

En el requadre anterior hi ha l'especificació de la sintaxis per l'ordre de creació d'una taula. Estracta d'una versió reduïda de les possibilitats que ens ofereix el llenguatge SQL per a lacreació i configuració de taules. Tot i així, hi ha les opcions més comunes en la seva utilització.





Data TypesEn relació als tipus de dades proposats s'han diferenciat en tres grans grups:

1. Caràcter2. Numèrics3. Data i temps

Cal indicar que a més d'aquests tre grans grups hi ha també el tipus d'objectes grans, que enspermeten emmagatzemar fitxers en una base de dades relacional. Es considera que per aemmagatzemar aquest tipus d'informació (fitxers, imatges, videos, audios...) és millor utilitzaruna base de dades documental i per aquest motiu s'ha obviat aquest tipus de dada. En unabase de dades relacional és millor emmagatzemar la ubicació física del fitxer (path) excepteque el sistema gestor de base de dades disposi de mecanismes per a indexar aquest tipusd'informació i poder buscar la informació pels camps on s'emmagatzemi aquest tipusd'informació.El modificador «binary» obliga a emmagatzemar la informació utilitzant el joc de caràctersbinari. Així, per exemple, si el sistema té configurat per defecte el joc de caràcter «latin1» is'especifica «binary» al camp, la codificació per aquest camp serà «latin1_bin» i per tant,s'utilitzarà aquest joc de caràcters per a les condicions i l'ordenació.

Tipus text: char i varcharEls camps de tipus char i varchar ens permeten emmagatzemar caràcters. La difere cia entreǹels dos tipus és el sistema que s'utilitza per a emmagatzemar la informació.En els camps de tipus char o varchar, després del nom del tipus cal indicar entre parèntesis lamida que es vol assignar al camp. Aquesta mida determina el nombre màxim de caràcters ques'hi podrà emmagatzemar.

Create table t1 ( c1 char(5), c2 varchar(10), primary key(c1)) engine=innodb;

La sentència anterior crea una taula «t1» amb 2 camps (c1 i c2). Al camp c1 se li ha assignatuna mida màxima de 5 caràcters mentre que el camp c2 permet emmagatzemar fins a 10caràcters.Si es sobrepassa la mida especificada pel camp alguns sistemes poden donar un error o béaltres simplement retallen la informació al màxim nombre de caràcters habilitats.En el cas de «mysql» el comportament depèn de la configuració del sistema. Si el sistema noestà configurat amb el mode estricte («strict mode»), la comanda s'executarà i les dadess'emmagatzemaran en la taula. Quan s'analitzi l'estat de l'operació s'observarà que aquesta hadonat un avís («warning») i que s'ha retallat («truncate») la informació emmagatzemada en elcamp. Si, per contra, s'habilita el mode estricte del sistema l'execució de la comanda finalitzasense actualitzar la informació en la taula: el resultat de la instrucció és la generació d'un errori la cancel·lació immediata de la instrucció.





En la següent imatge es mostra el comportament del sistema amb el mode estricte habilitat ideshabilitat.

Tipus char

El tipus «char» utilitza una mida de camp fixe. Així , per exemple, si declaro un camp de 8caràcters aquest sempre ocuparà 8 caràcters en el disc. Si d'aquests 8 caràcters nomésn'emplenem 5, els 3 restants s'ompliran mitjançant espais en blanc ocupant el corresponentespai en disc. L'avantatge d'aquest tipus de dada és que és més eficient que els tipus de dadade longitud variable1.Utilitzarem aquest tipus quan calgui emmagatzemar caràcters en un camp i la longitud de lainformació a emmagatzemar sigui fixe: un dni, una matrícula, un codi postal, un telèfon2 ….

1 Quan la longitud del registre és fixe.2 Veure tipus numèrics





Tipus varchar

El tipus «varchar» ens permet introduir informació de mida variable en un camp de la nostrabase de dades. S'utilitzarà aquest tipus quan calgui emmagatzemar text o caràcters en el campi no es pugui definir un nombre de fixe de caràcters: el nom d'una persona, d'una població, eltítol d'un llibre, d'una pel·lícula...

Comparació emmagatzematge char varchar

Value CHAR(4) Storage Required VARCHAR(4) Storage Required

'' ' ' 4 bytes '' 1 byte

'ab' 'ab ' 4 bytes 'ab' 3 bytes

'abcd' 'abcd' 4 bytes 'abcd' 5 bytes

'abcdefgh' 'abcd' 4 bytes 'abcd' 5 bytes

Tipus numèricsEls tipus numèrics els utilitzarem quan haguem d'emmagatzemar informació amb la qualhaguem de realitzar càlculs. Així, per exemple, si es vol emmagatzemar el codi postal d'unapoblació, un DNI, o un telèfon, tot i que aquesta informació estigui formada només per dígits,al no ser necessària la realització de càlculs amb aquesta informació s'utilitzarà un tipus«char». La informació relacionada amb preus, quantitats, imports, distàncies, …. sobre elsquals es podran realitzar càlculs, cal emmagatzemar-la utilitzant tipus numèrics.Els tipus numèrics es poden dividir en dos grans grups tipus: exactes i aproximats.

Tipus numèrics de precisió exacta

Els tipus numèrics de precisió exacta són aquells en els quals no hi ha una pèrdua de precisióquan es realitzen càlculs. La majoria de vegades s'utilitzaran aquests tipus, sobretot quan lainformació fa referència a imports monetaris.Dins els tipus numèrics de precisió exacte es poden diferenciar dos grans grups:

• INTEGER ( o INT)• DECIMAL (o NUMERIC)





Integer

Utilitzarem el tipus «integer» quan la dada a emmagatzemar sigui un valor enter. Tot i quel'especificació del tipus enter permet indicar la dimensió del número, aquesta no és necessàriai s'utilitza només per a formatejar el valor numèric a una mida fixa. El valor per defecteassociat a la mida és de 11 dígits. En el cas que s'indiqui una mida una mida específica, el valordel camp es visualitzarà utilitzant la mida indicada. Per tal que el sistema gestor de base dedades formategi el camp amb la mida fixa, afegint zeros a l'esquerra, cal especificar la directiva«zerofill» en la creació del camp.

En la imatge anterior es crea una taula amb 3 camps, n1, n2 i n3. Pels camps n1 i n2 no s'haespecificat la mida del camp, per tant, la seva dimensió serà de 11 dígits per a la visualització.En el camp n1, a més, no s'ha especificat cap formateig per a la visualització de la informació.Quan es consulten les dades de la taula, per la columna 1 només es retorna el valor del campnumèric, eliminant els zeros no representatius. La columna 2 es formateja el camp amb untotal de 11 dígits; s'afegeixen 10 zeros. Per la tercera columna, el camp es formateja amb 5dígits ja que aquesta és la mida indicada per a visualització del camp.Cal indicar que l'espai d'emmagatzematge del camp en disc és independent de la midaespecificada; només depèn del tipus de dada. En l'exemple de la imatge, el camp n1, n2 i n3ocupa cada un d'ells un total de 4 bytes.





TypeStorage Minimum Value Maximum Value

(Bytes) (Signed/Unsigned) (Signed/Unsigned)

TINYINTUnsigned

1 -128 127

0 255

SMALLINTUnsigned

2 -32768 32767

0 65535

MEDIUMINTUnsigned

3 -8388608 8388607

0 16777215

INTUnsigned

4 -2147483648 2147483647

0 4294967295

BIGINTUnsigned

8 -9223372036854775808 9223372036854775807

0 18446744073709551615

Tipus de dades enter, bytes d'emmagatzematge i valors mínims i màxims.

Decimal

El tipus decimal, a diferència del tipus enter, permet emmagatzemar els decimals númerosreals. En el tipus decimal se li associen dos valors numèrics, el primer és el total de dígits queté el número i el segon el nombre de dígits que s'utilitzaran per a especificar la part decimal.decimal (m,n) :

- m: total de dígits, incloent els decimals- n: dígits decimals

Per exempledecimal (5,2) :

- 3 dígits per la part entera (5-2=3).- 2 dígits per la part decimal.- -999.99 valor mínim que pot emmagatzemar el camp.- +999.99 valor màxim que pot emmagatzemar el camp.

Si no s'especifica cap dimensió en el camp només es podran emmagatzemar números enters.Si s'indica la directiva zerofill s'emplenaran amb valor zero els dígits que no s'hagin utilitzat.





Tipus numèrics aproximats

Els tipus aproximats són aquells que no emmagatzemen el valor exacte. Els tipus aproximatsestan formats bàsicament pels tipus float i real amb les seves variants. Quan es fa lacomparació utilitzant aquests tipus cal especificar sempre amb quina precisió es volen realitzarels càlculs; conseqüència de no emmagatzemar un tipus exacte.Mitjançant l'exemple de la imatge s'il·lustra quin és el significat «d'emmagatzemar un valor noexacte».

Com es pot observar, el resultat de l'operació no és 21.4 , sinó que al final s'ha emmagatzematun dígit decimal 6 adicional; causa de la no exactitud de l'operació. Aquest fet condiciona a laforma de realitzar les comparacions, ja que aquestes no es poden fer per igualtat. Perexemple, si sumem els dos valors de f1 i f2 el resultat és 21.4 . Per tant, una sentència del tipus«select f1, f2 from t2 where f1+f2=21.4» ens hauria de retornar la fila amb valors de f1 101.4 i-80 per f2. Aquest fet no és així donada la imprecisió del tipus. Per a obtenir el resultat desitjat cal indicar primer quina precisió es vol i després adaptar laconsulta a la precisió desitjada. Per exemple, si volem aproximar fins al 5è decimal es pot feruna consulta de l'estil:





«select f1, f2 from t2 where abs(f1+f2)<21.4000001» . L'utilització de l'operant < obliga a que esmostrin tots les files de la taula amb un resultat inferior a l'especificat. El valor abs obliga a queaquest valor sempre sigui positiu.En la següent imatge es mostra una simulació del funcionament per l'exemple explicat.

Cal indicar que el tractament amb els tipus de dades float i real és molt ràpid i, per tant,s'aconsella el seu ús quan la precisió no és un requisit important però sí la velocitat en eltractament de les dades.

Tipus data/horaPer a emmagatzemar dates i hores disposem dels tipus:

• DATE• TIME• DATETIME• TIMESTAMP

Sempre que es vulgui especificar una data o una hora o ambdues informacions es faràutilitzant cometes simples ( ' ). Per a separar els diferents elements de data utilitzarem elguionet ( - ) o bé la barra inclinada ( / ). La informació entre hores minuts i segons es faràutilitzant els dos punts ( : ). Si es volen indicar dècimes , centèsimes …. es farà utilitzant el puntcom a separador ( . ), tot i que mysql server no emmagatzemarà la part decimal.





TIME

Si el nostre camp ha d'emmagatzemar una hora determinada o bé un interval de temps l'únicaopció que es disposa és la de definir un camp utilitzant el tipus «time». Tota la resta de tipusrelacionats amb la data poden emmagatzemar només la data o bé la data i hora.Amb el tipus time es poden precisar fins als segons, però no hi ha possibilitatd'emmagatzemar dècimes o centèsimes de segons. Tot i que no sigui obligatori, per a introduir un temps sempre utilitzarem els dos punts com aseparador i introduirà l'hora amb tots els seus components seguint el format HH:MM:SS.En la imatge , tot i que no ha donat cap «warning» es pot observar com les mil·lèsimes desegon introduides en la segona hora s'han perdut en la recuperació de les dades.

DATE / DATETIME

Per a introduir una data sempre s'utilitzarà el format de data anglès. La data es pot introduirutilitzant els 4 dígits per a especificar l'any o bé utilitzant dos dígits: l'utilització de 4 dígits ajudaa la lectura de la informació. Quan el camp és de tipus «datetime» permet emmagatzemar unadata i una hora. En la imatge es mostren alguns exemples d'utilització dels diferents tipus de data utilitzantdiferents formats.





L'interval de dates permeses és des del'1000-01-01' fins al '9999-12-31' pels tipus«date» i «datetime». Pel tipus «timestamp» l'interval és bastant mésreduït i comprèn des de la data '1970-01-01 00:00:01' UTC3 fins a '2038-01-1903:14:07' UTC.

DATETIME vs TIMESTAMP

La diferència entre DATETIME i TIMESTAMP és que el tipus «timestamp» fa una conversió del'hora a format UTC per emmagatzemar la data i torna a convertir la data i hora a la zonahorària actual quan recupera la data. Així, per exemple, s'evita que quan hi ha una participacióentre foros els efectes dels canvis horaris entre les diferents regions.Per a mostrar el funcionament i la diferència del tipus «dateTime» i «timestamp» utilitzarem lataula «dates» creada en el punt interior i la funció introduïda. La configuració horària delservidor era GMT+1 en el moment d'introduir les dades. Per a canviar la zona horària delservidor , primer cal carregar les taules amb la informació horària al servidor. Des de laconsola de Linux cal executar la comanda:

Tot i que l'execució de la comanda ens mostri que hi ha algunes zones horàries que no hapogut carregar, sí que tenim les dades necessàries per a poder mostrar el funcionament i aldiferència entre els dos tipus de camps. Per a canviar la zona horària del servidor , tot i que es pugui fer a nivell de servidor, utilitzaremla comanda de MySQL «set zone=zonaHoraria», tal i com es mostra a la imatge. Especifiquemcom a zona horària «+1:00» que és la que té per defecte el servidor: no hi ha cap canvi entre lainformació introduïda i la visualitzada.

3 Coordinated Universal Time





Si, per contra, establim una zona horària +6:00 , mentre que la columna «datet» continuamostrant la mateixa hora, la columna «dates» ens dóna la informació per la nova zona horàriaestablerta (20:34:12) que correspon a l'hora +6.





Creació taules – ExemplesCREATE TABLE llibre ( isbn CHAR(13), titol VARCHAR(50) NOT NULL, dataAdquisicio DATE NOT NULL, preu DECIMAL(6,2) NOT NULL, PRIMARY KEY(isbn)) engine=innodb;

-- Els camps usuari i password han de ser sensibles a majúscules, minúscules i accentsCREATE TABLE registre( usuari VARCHAR(20) COLLATE 'latin1_general_cs', password VARCHAR(16) NOT NULL COLLATE 'latin1_general_cs', email VARCHAR(255) UNIQUE NOT NULL, -- No hi poden haver duplicats de correu (clau candidata) dataReg TIMESTAMP NOT NULL, -- Determinar ordre de registre punts INTEGER, -- Els punts inicialment poden ser NULL (diferent de zero) PRIMARY KEY(usuari)) engine=innodb;





Webgrafiahttp://dev.mysql.com/doc/refman/5.7/en/create-table.htmlhttp://dev.mysql.com/doc/refman/5.7/en/char.htmlhttp://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-stricthttp://dev.mysql.com/doc/refman/5.7/en/binary-varbinary.htmlhttp://dev.mysql.com/doc/refman/5.7/en/integer-types.htmlhttp://code.rohitink.com/2013/06/12/mysql-integer-float-decimal-data-types-differences/http://dev.mysql.com/doc/refman/5.7/en/choosing-types.htmlhttp://dev.mysql.com/doc/refman/5.7/en/datetime.html


http://dev.mysql.com/doc/refman/5.7/en/datetime.html

http://dev.mysql.com/doc/refman/5.7/en/choosing-types.html

http://code.rohitink.com/2013/06/12/mysql-integer-float-decimal-data-types-differences/

http://dev.mysql.com/doc/refman/5.7/en/integer-types.html

http://dev.mysql.com/doc/refman/5.7/en/binary-varbinary.html

http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-strict

http://dev.mysql.com/doc/refman/5.7/en/char.html

http://dev.mysql.com/doc/refman/5.7/en/create-table.html

tema 01 - creació de taules amb sql

Education