02-09-2014_16-40-43 / 2013 Пакеты прикладных программ
.pdfTa6n11:1.1,a 7.1
<l>y111m1111, np11MenJ1eMble ,!l)IJI 38,!J,aHllJI CBOHCTB HJlll ycJIOBHH Bbl60pKH
B DOJIJIX THDa J(ama/8peMH
Cl>YHKUIDI |
3ttaqe.HJ1e |
Day |
YcnrnaBJIHBaeT ycJIOBHe BhI6opKH no q11:cnaM Mec511.1, |
|
B ,[(Hamt30He OT 1 ,[(0 31 |
Month |
YcTitHaBJIHBaeT ycnoBHe BhI6opKH no Mec511.1,aM B.[(Ha- |
|
na3oHe OT 1 .IJ.O 12 |
Year |
y CTaHaBJIHBaeT ycJIOBHe BhI60pKH no fO,[(aM B ,[(Hana30- |
|
He OT 1()() .[(0 9999 |
Weekday |
y CTitHaBJIHBaeT YCJIOBHe Bhl60pKH no ,[(H51M He,[(eJIH OT |
|
1 (BOCiq>eCeHh51) ,[(O 7 (cy660Thl) |
Hour |
y CTaHaBJIHBaeT YCJIOBHe Bhl60pKH no qacaM CYTOK OT 0 |
|
,[(0 23 |
Datepart q mm ww y CTilHaBJIHBaeT ycJIOBHe Bhl60pKH COOTBeTCTBeHHO no
|
HOMepaM KBapnlJIOB H He,[(eJih ( q rrpHHHMaeT 3HaqeffH51 |
|
OT 1 ,[(0 4, a WW - OT 1 ,[(0 53) |
Date() |
YcTitHaBJIHBaeT TeKYmYIO .IJ.aTY KaK ycnoBHe Bh16opKH |
|
(HanpHMep, ycJIOBHe <Date()-15 03HaqaeT, qro 6Y.IJ.YT |
|
BhI6paHhl Bee 3anHCH, ,[(aTa KOTOphIX MeHhIIIe TeKYil.l,eH |
|
Ha 15.IJ.Heif) |
HMM IIOCTOHHHhIX 3arrpocoB. Tip11 KOHCTPYHPOBaHMM rrapaMeTp11qeCKHX 3arrpocoB BBO,U ycJIOBHM OT6opa ,UaHHhIX peKOMeH,UyeTCH rrpOM3BO,UHTh qepe3 <t>opMhl.
KoHCTPYHPOBaHHe nepeKpecTHLIX 3anpocoe. TiepeKpecTHhre Jarrpo-
ChI, pe3yJihTaTOM BhIIIOJIHeHMH KOTOphIX HBJIHIOTCH Kpocc-Ta6JIHJ:J;hl, r.i;eJiecoo6pa3HO co3,naBaTh ,UJIH aHaJIM3a q11cJIOBhIX rroJieM:. Harrp11Mep, He06XO,UMMO pa3pa60TaTh 3arrpoc, KOTOphIM II03BOJIHJI 6hI Ha OCHOBe Ta6JIMIJ;hl, CTPYKTYPa KOTOpoM: IIOKa3aHa Ha pMC. 7.6, IIOCMOTpeTh q11cJIO qacoB Jia6opaTOpHhIX pa6oT'rrpOBO,UMMhIX B pa3JiwqHhIX rpyrrrrax KruK,UhIM rrperro,naBaTeJieM.
B pe3yJihTaTe BhIIIOJIHeHMH 3arrpoca rroJiyqaIOT ,UMHaMwqecKYIO Ta6JIMIJ;Y (p11c. 7.7), B KOTOpoM: B 5.1qeM:Kax CTOJI6r.i;a <PHO 3arr11caHhl <t>a- MHJIMH H HHHIJ;HaJihl rrperro,naBaTeJIH, q11CJIO CTOJI6IJ;OB I'pynna 1 ...
I'pynna N COOTBeTCTByeT q11cJiy rpyrrrr B Ta6JIMIJ;e, rrpMBe,UeHHOM Ha pMC. 7.6, a IIO,UIIHCh K<DK)J,Oro CTOJI6r.i;a - HaMMeHOBaHMIO rpyIIIIhl. TaKHM o6pa3oM, rroJie <PHO B 06e11x rn6JI11r.i;ax MMeeT o,n11HaK0Boe Ha3HaqeHMe. 3HaqeHMH 3Toro IIOJIH B Kpocc-Ta6JIMIJ;e Ha3bIBaIOT 3aroJIOBKOM CTpOKH.
Ha3BaHMH cToJI6r.i;oB Kpocc-Ta6JIMIJ;hI HBJIHIOTCH 3HaqeHMHMM rroJIH
I'pynna Ha p11c. 7.6. B 5.[qeM:Kax CTOJI6r.i;oB r<:pocc-Ta6JIHIJ;hr 3arrHCaHhI qachr,
90
06paTHM BHHMaHHe Ha TO, qTO B nepeKpeCTHOM Janpoce ,ll;OJDKHbl 6b!Tb TpH noJUI. 0.n;Ho none npe.n;CTaBJI5IeT co6oH: JaronOBKH CTpOK, 1nopoe none onpe.n;eJUieT JaronoBKH cTon6u:os, a TpeTbe none .n;oJDKHo co.n;ep)l(aTh 3HaqeHHe COOTBeTCTB)'lOll.leronoJUI HCXO,ll;HOH Ta6nHU:bl. B paCCMOTpeHHOM npHMepe ,ll.IDI nepBblX .n;Byx noneH: B CTpOKe KOHCT-
PYKTOpa I'pynno6aR onepau,Wl BhI6paH napaMeTp I'pynnupo6Ka, a ,ll.IDI
rpeThero noJUI Jla6bl Ja,u:aH napaMeTp Sum (CYMMHpoBaHHe).
Ha pHc. 7.9 noKaJaH peJynhTaT BhlnonHeHIDI 3Toro Janpoca. qHcno CTpOK B Janpoce paBHO qHCJIY 3HaqeHHH noJUI <PHO. 06paTHM BHHMaHHe Ha cne.n;yiomee. B Hcxo.n;HoH: Ta6nHu;e Hazpy3Ka qffCno JaTIHceH: ,ll.IDI o.n;Horo npeno.n;asaTeJUI MO)l(eT 6hITb 6onbIIIe e.n;HHHU:hl H JaBHCHT OT
•-rncna qmaeMI>IX .n;HCU:HTIJIHH. IlpH BhlnOnHeHHH rpynnOBOH onepaUHH I'pynnup06KQ Bee JanHCH cl>opMHp)'lOTCRno cl>aMHJIIDIM npeno.n;a- BaTene:H: B O,ll;HY CTpOKY. .AHaJIOfliqHble .n;e:H:CTBIDI npOH3BO,wITCR ,ll.IDI
noJUI I'pynna.
7.4. ABTOM8TH38l-'HHpac'leTOBc nOMOll.lblO 3anpocoa
0.n;HHM HJ npHMeqarenhHhIX cso:H:cTB CYE,ll Access RBJUieTcH BOJMO)l(HOCTh aBTOMaTHJaU:HH paJJiffqHOro po.n;a BhNHcneHHH. HanpHMep, npou;ecc nepecqeTa u;eHhl TOBapa B py6JIHX Ha .n;eHe)l(Hbie 3KBHBaJieHThI B HHOCTpaHHbIX BaJilOTax. TaKHe pacqeTbl Heo6xo.n;HMbl B ToproBOH 11 cPHHaHCOBOH .n;eRTenhHOCTH npe.n;npHRTHH. Ilpou;ecc BblqHcneHHH c npHMeHeHHeM JanpOCOB OCHOBaH Ha C03,ll;aHHH cneu;HaJibHOfO pac- Y.eTHOfO noJUI npH KOHCTpYHPOBaHHH Janpoca.
3TOT cnoco6 cne.n;yeT npHMeHRTb B Tex cnyqaHX, Kor.n;a B HCXO,ll;HOH ra6nHu;e He npe.n;ycMoTpeHo pacqeTHoe none. TaKoe none MO)l(HO co- 3.n;aTh npH KOHCTpYffpOBaHHH Janpoca B pe)l(HMe KoHcmpyKmop, ,ll.IDI Y.ero Heo6xo.n;HMO BbITIOnHHTb cne.n;yiomHe .n;e:H:CTBIDI:
==> cocTaBHTh Janpoc B pe)l(HMe KoHcmpyKmop;
==> YCTaHOBHTb KYPCOp B nocne.n;HlOlO gqe:HKY CTpOKH Jlo!le H aKTHBH3HpOBaTb KOHTeKCTHOe MeHlO, menKffYB npaBOH KHOnKOH MbIIIIH
==> B nOHBHBIIIeMCR OKHe aKTHBH3HpOBaTb KOMaH.n;y Jlocmpoumb, B pe3ynhTaTe BbITIOnHeHIDI KOTOpo:H OTKpoeTCR OKHO nOCTpOHTeJUI BbIpaxeHHH, Ha OCHOBaHHH )'Ka3aHHHKOTOporo CTpOHTCR BbipaxeHHe
.n;JUI pacqeTa.
IlycTb, HanpHMep, HyxHO BblqHcnHTb JaTIJiaHHPOBaHHYIO Harpy3KY npeno.n;asareJUI no npe.n;MeTaM Ja seceHHHH ceMeCTp (p11c. 7.10). B 3TOM Janpoce HJ Ta6nHU:hl BhI6HpaeM TOnhKO TpH nomI: <!>HO, Ce- Mecmp, JlpeiJMem. ,llanee, noMeCTHB KYPCOp B CTpOKY Jlo!le qernepTOro CTOn6u:a KOHCTpYKTOpa, 3a,u:a,u:HM HMH pacqeTHOMY non10: Koll. "IOC06 no npeiJMemy. 3areM, Bbl3BaB noCTpOHTenb BbipaxeHHH (pHC. 7.11), coCTaBHM pacqeTH)'lOcl>opMyny, CYMMHPYIDlllYID JHaqeHIDI Bcex qHcnoBhIX noneH:. 3Ty cPOPMYJIY noMeCTHM B pacqeTHoe none Janpoca.
Ha pHc. noKaJaH peJynhTaT BhITIOnHeHIDI Janpoca.
93
ALL - KmoqeBoe CJIOBO, KOTopoe OJHaqaeT, qTo B peJyJihTMpyiorn;MH: Ha6op 3aTIMCe:ti: BKJIIOqaJOTCH Bee 3aTIMCM Ta6JIMUhl MJIM Janpoca,
KOTOpb1e y.uoBJieTBopHIOT ycJIOBMHM Janpoca;
DISTINCT - KJI10qeBoe cJioBo, KOTopoe OJHaqaeT, qTo B peJyJih- ™PYJOll.J;MH Ha6op JanMceH: He BKJIJOqaJOTCH IlOBTOpHlOll.J;MeCH 3aITMCM Ta6JIMUhI MJIM Janpoca.
|
Ta6n1n.i;a 7.2 |
|
OnepaTopbI onpe,ll.eJieHIUI ,ll.aHHblX |
Orreparop |
HaJHa'leirne |
CREATE TABLE |
Co3,1J.aeT HOBYJO Ta6nmzy E,LJ; |
DROP TABLE |
Y,n;aJIHeT Ta6nmzy H3 E,LJ; |
ALTER TABLE |
M3MeHHeT CTPYJCTYPY cymecTByiomeil: Ta6JIHl.J;bl |
|
HJIH orpaHl{qeHIDI QeJIOC1HOCTH, 3a)J;aruteMble |
|
.u;IDI Hee |
CREATE VIEW |
C03.n;aeT BHJYIYaJihH)'IOTa6Jimzy, coOTBeTCTBYJO- |
|
w;yio HeKoropoMY SQL-3anpocy |
ALTER VIEW |
M3MeHHeT pattee co3,!l;aHHoe npe,n;cTaBJieHHe |
DROP VIEW |
Y.n;aJIHeT paHee co3)1;aHHoe npe,n;cTaBJieHHe |
CREATE INDEX |
COJ.n;aeT HH.D;eKc .u;IDI HeKoropoil: Ta6JIHl.J;hl |
|
c QeJibIO o6ecneqeHHH 6bIC'lpOfO)J;OC'fYIIa |
|
K Heil: no COOTBeTCTB)'IOI.UHMa'lpH6yraM |
DROP INDEX |
Y.n;aJIHeT paHee cOJ.n;aHHhIH HH.D;eKc |
|
Ta6JIHQa 7.3 |
OnepaTopbl MaennyJinponaelUI ,1J.aeeblMH |
|
Orreparop |
HaJHa'leirne |
DELETE |
Y.n;aJIHeT o.u;ey HJIH HeCKOJihKO CTPQK, cooTBeTCT- |
|
B)'IOI.UHXyCJIOBHHM cpHJih'lpal.J;HH,H3 6a30BOH |
|
Ta6JIH1.J;hl. Ero npMMeHeHHe cornacyeTCH c npHH- |
|
l.J;HilaMH IlOMep.lKKH QeJIOC1HOCTH, Il03TOMY OH |
|
He BCer.u;a MO)J(eT 6bITh BbIIlOJIHeH KoppeKTHo, |
|
)J;a)J(e eCJIH CHHTaKCl{qeCKH 3aDHCaH npaBHJlhHO |
INSERT |
BcTaBJIHeT o.u;ey CTPOKY B 6aJoByio Ta6JIHey. ,LJ;o- |
|
nyCTHMhl ero MO)J;HcpHKal.J;HH, nepeHOCHI.UHe cpa- |
|
3y HeCKOJihKO C'lpOKH3 O.D;HOH Ta6JIHl.J;hl HJIH |
|
Janpoca B .u;pyryio |
UPDATE |
06HOBJIHeT 3Ha'leHHHO.D;HOfO HJIH HeCKOJihKHX |
|
CTOJI6l.J;OB B O.D;HOH HJIH HeCKOJlhKHX C'lpOKaX, |
|
COOTBeTCTBYIOI.UHX ycJIOBHHM cpHJib'lpaQHH |
98
|
Ta6n11ua 7.4 |
|
OnepaTOp 1anpocoa |
Orreparop |
Ha:ma'leHHe |
SELECT |
3aMeIDieT BCe oneparopbI peJUIIJ,HOHHOH anre6- |
|
pbl H Il03BOJUieT c¢iopMHpOBllTh pe3yJibTHpyio- |
|
mee ornornett11e, cooTBeTCTBYJOIIJ,ee 3anpocy |
|
Ta6n11ua 7.5 |
Oneparopw ynpllBJieHBJI ,11;eiiCTBHJ1MH (TPaB3~B) |
|
Orreparop |
Ha3Ha'leHHe |
CCOMMIT |
3a:eepwaeT KOMilJleKCHYJO B3aHMOCIDl3aHH)'IO |
|
o6pa6oncy HH¢iopMall,HH, ofu.e.ZJ;HHeHH)'IO |
|
B 'lpaH3aKIJ,HIO |
ROLLBACK |
OTMeHSieT H3MeHeHHH, npoBe,11;eHHb1e B xo,11;e |
|
BbIIIOJIHeHIDI 'lpaH3aKIJ,HH |
SAVEPOINT |
CoxpaHSieT npoMe:xcyTO'IHOeCOCTORHHe B,ll; H |
|
IlOMe'laeTero, 'IT06bIMO:>KHO 6bIDO B ,ZJ;aJibHeH- |
|
weM K HeMY BepttyTuCR |
|
Ta6n11ua 7.6 |
OnepaTopw a.ZJ;MBBHCTpapoaaeu ,11;auuwMH |
|
Orreparop |
HaJHa'leHHe |
ALTER DATABASE |
lfaMeIDieT Ha6op OCHOBHbIX 06'be!CTOBB 6a3e |
|
)1.aHHbIX H orpaHH'leHHH,KaCaIOIIJ,HXCR BCeH |
|
6a3bI JJ.aHHbIX |
ALTER DBAREA |
lfaMeHSieT paHee C03JJ.aHHYJO o6nacTu xpatteHHR |
ALTER PASSWORD |
lfaMeHSieT rrapOJib)J,JIR BCeH 6a3bl )1,aHHbIX |
CREATE DATABASE |
Co3)1,aeT HOBYJO 6a3y )1,aHHbIX |
CREATE DBAREA |
Co3)1.aeT HOBYJO o6nacTu xpatteHIDI 6a3bI JJ.aHHbIX |
DROP DATABASE |
YJJ.anReT 6a3y JJ.aHHbIX |
DROP DBAREA |
YJJ.anReT o6nacTuxpatteHHR 6a3bI JJ.aHHbIX |
GRANT |
IlpeJJ.OCTaBJIReT rrpaBll JJ.OCTYfia K 6a3e JJ.aHHbIX |
|
HJIH OT)J,eJibHblM ee 3JieMeHTaM |
REVOKE |
JI11rnaeT rrpasa JJ.OCTYfia K 6a3e JJ.aHHbIX HJIH |
|
OT)J,eJibHbIM ee 3JieMeHTaM |
99