11. DDL
9
DDL – Data Defnition Language
Transcript of 11. DDL
7/24/2019 11. DDL
http://slidepdf.com/reader/full/11-ddl 1/9
DDL – Data Defnition
Language
7/24/2019 11. DDL
http://slidepdf.com/reader/full/11-ddl 2/9
DDL – Data Defnition Language
CREATE [GLOBAL TEMPORARY] TABLE[esquema.]taba !oumna "atat#$e [DE%A<e'$(] [!oumn)!onst(aint*s+] [,!oumna "atat#$e[,...]] tabe)!onst(aint tabe)(e-)!onst(aint [OCOMM/T 0DELETE1PRE2ER3E4 RO52]sto(age)o$tions [COMPRE22 int1OCOMPRE22][LOB)sto(age)!ause][6a((a#)!ause][neste")sto(age)!ause] [7ML)t#$e)!ause]Pa(titioning)!ause [[O]CAC8E][[O]RO5DEPEDEC/E2] [[O]MO/TOR/G][PARALLEL $a(ae)!ause] [EABLEenabe)!ause 1 D/2ABLE "isabe)!ause]0EABLE1D/2ABLE4 RO5 MO3EMET [A2subque(#]
/. C(ea!i9n "e Tabas
2inta'is
E engua:e "e "efni!i9n "e "atos *en ing;s Data Defnition Language, oDDL+, es e que se en!a(ga "e a mo"if!a!i9n "e a est(u!tu(a "e osob:etos "e a base "e "atos. E'isten !uat(o o$e(a!iones b<si!as= CREATE,ALTER, DROP # TR&CATE.
7/24/2019 11. DDL
http://slidepdf.com/reader/full/11-ddl 3/9
DDL – Data Defnition Language
!(eate tabe t)!ientes *num!ient numbe(,nomb(e 6a(!>a(?*@++
ate( tabe t)!ientesa"" !onst(aint $)t)!ientes$(ima(# e# *num!ient+
"(o$ tabe t)!ientes
!(eate tabe t)!ientes *num!ient numbe( $(ima(# e#,nomb(e 6a(!>a(?*@++
• ue"a mas !a(o !on unos e:em$os
@. Defnimos !ont(aint *$(ima(# e#+ $a(a nuest(a taba !(ea"a.
?. 2i "esea(amos asigna( su !onst(aint *$(ima(# e#+ amomento "e !(ea( a taba, tu6i;(amos a siguiente
sinta'is, !omo tenemos a taba !ientes !(ea"a abo((amos # $(o!e"emos a !(ea( *Cuan"o no e'iste taba+
uest(a taba !(ea"a no tiene ningFn!onst(aint.Casos=
7/24/2019 11. DDL
http://slidepdf.com/reader/full/11-ddl 4/9
DDL – Data Defnition Language• Pa(a nuest(o segun"o e:em$o, !(eamos ot(a taba. ue (ea!iona(emos
me"iante un -o(eign e# !on a taba !ientes!(eate tabe t)$e"i"os*num$e"i"o numbe(,-e!$e"i"o "ate,num!ient numbe(+
uest(a taba !(ea"a no tiene ningFn
!onst(aint.Casos=@. Defnimos !ont(aint *$(ima(# e#+ $a(a nuest(a taba !(ea"a.
ate( tabe t)$e"i"osa"" !onst(aint $)t)$e"i"os$(ima(# e# *num$e"i"o+
ate( tabe t)$e"i"osa"" !onst(aint -)t)$e"i"os)t)!ientes-o(eign e# *num$e"i"o+(e-e(en!es t)!ientes*num!ient+
?. Defnimos !ont(aint *%o(eign e#+ $a(a nuest(a taba !(ea"a.
7/24/2019 11. DDL
http://slidepdf.com/reader/full/11-ddl 5/9
DDL – Data Defnition Language
?. 2i "esea(amos asigna( os !onst(aints *$(ima(# e# #-o(eign e#+ a momento "e !(ea( a taba, tu6i;(amos asiguiente sinta'is, !omo tenemos a taba !ientes !(ea"aa bo((amos # $(o!e"emos a !(ea( *Cuan"o no e'istetaba+
D(o$ tabe t)$e"i"os
!(eate tabe t)$e"i"os *num$e"i"o numbe( $(ima(# e#,-e!$e"i"o "ate,num!ient numbe( (e-e(en!est)!ientes*num!ient++
7/24/2019 11. DDL
http://slidepdf.com/reader/full/11-ddl 6/9
DDL – Data Defnition Language//. Ate(a( Tabas
ALTER TABLE [esquema.]taba 0ADD1MOD/%Y1DROP4...
2inta'is=
Ate( tabe t)$e"i"os a"" "i(e!!ion 6a(!>a(?*+• AHa"i( una !oumna a una taba=
Ate( tabe t)$e"i"os mo"i-# "i(e!!ion 6a(!>a(?*@+• Cambia( e tamaHo "e una !oumna en una taba=
Ate( tabe t)$e"i"os mo"i-# "i(e!!ion not nu• 8a!e( OT &LL una !oumna en una taba=
Ate( tabe t)$e"i"os "(o$ !oumn "i(e!!ion
• Eimina( una !oumna a una taba=
Ate( tabe t)$e"i"os a"" *!anti"a" int, im$o(te numbe(+
• AHa"e "os !oumnas=
7/24/2019 11. DDL
http://slidepdf.com/reader/full/11-ddl 7/9
DDL – Data Defnition Language///. Eimina( Tabas D(o$
• Pa(a eimina( una taba "ete(mina"a, se usa a inst(u!!i9n DROP TABLE, "e a siguiente mane(a=
2LI DROP TABLE nomb(etaba
Con o !ua se eimina a taba nomb(etaba "e sistema.
• &n !aso es$e!ia se $(esenta si a (ea!i9n a se( eimina"a est<(e-e(en!ia"a $o( !a6es -o(<neas en ot(a*s+ (ea!i9n*es+. En este !aso,se "ebe >a!e( a ama"a "e a siguiente mane(a=
2LI DROP TABLE nomb(etaba CA2CADE CO2TRA/T2
De esta mane(a se eimina(<n to"as as tu$as en ot(as (ea!ionesque (e-e(en!ien a a !a6e $(ima(ia "e a (ea!i9n a se( eimina"a. 2i nose in!u#e e $a(<met(o CA2CADE CO2TRA/T2 # e'iste aguna(e-e(en!ia a una tu$a que se eimina(<, ORACLE (eto(na(< un
mensa:e "e e((o( # no eimina(< a (ea!i9n.
2inta'is=
2inta'is=
7/24/2019 11. DDL
http://slidepdf.com/reader/full/11-ddl 8/9
DDL – Data Defnition Language
/3. Eimina( Tabas T(un!ate
Este !oman"o t(un!a to"o e !onteni"o "e una taba
TR&CATE TABLE JJTABLA)OMBRE@JJ
ota=
La 6enta:a sob(e e !oman"o DROP, es que si se quie(e bo((a( to"o e!onteni"o "e a taba, es mu!>o m<s (<$i"o, es$e!iamente si a taba esmu# g(an"e. La "es6enta:a es que TR&CATE s9o si(6e !uan"o se quie(eeimina( absoutamente to"os os (egist(os, #a que no se $e(mite a!<usua 58ERE. 2i bien, en un $(in!i$io, esta senten!ia $a(e!e(a se( DML*Lengua:e "e Mani$ua!i9n "e Datos+, es en (eai"a" una DDL, #a queinte(namente, e !oman"o TR&CATE bo((a a taba # a 6ue6e a !(ea( # noe:e!uta ninguna t(ansa!!i9n.
Ot(a "i-e(en!ia es a siguiente= !uan"o a taba tiene un !am$oKauto)in!(ementK, si bo((amos to"os os (egist(os !on K"eeteK # uegoing(esamos un (egist(o, a !a(ga(se e 6ao( en e !am$oautoin!(ementabe, !ontinFa !on a se!uen!ia tenien"o en !uenta e 6ao(
ma#o( que se >aba gua("a"o si usamos Kt(un!ate tabeK $a(a bo((a( to"osos (egist(os, a ing(esa( ot(a 6e un (egist(o, a se!uen!ia "e !am$o
2inta'is=
7/24/2019 11. DDL
http://slidepdf.com/reader/full/11-ddl 9/9
Lins
• >tt$=o(a.uNN.!om""!(eate?tabe.>tm
• >tt$=.$(og(amatium.!ommanuaeso(a!e?.>tm
• >tt$=es.ii$e"ia.o(gii2L
• >tt$=o(a.uNN.!om""ate(?tabe.>tm
• >tt$=.$(og(amatium.!ommanuaeso(a!eN.>tm
