Kratkoe posobie po yazyku SQL
© Copyright Maksim Moshkov, 1991-1995
moshkow@ipsun.ras.ru
Operatory yazyka SQL ispol'zuyutsya vo vseh instrumental'nyh
sredstvah razrabotki Informix (Informix-SQL, Informix-4GL,
Informix-ESQL/C i dr.). Dlya kazhdogo instrumenta harakterny
opredelennye osobennosti ispol'zovaniya SQL, kotorye opisany v
sootvetstvuyushchej dokumentacii. V etom razdele dayutsya svedeniya ob
primenenii operatorov SQL v srede razrabotki Informix-4GL.
Soglasheniya o yazyke SQL i nachal'nye ponyatiya.
Programma, napisannaya na 4GL ispol'zuet predpisaniya yazyka SQL
(Strukturnogo YAzyka Zaprosov) dlya manipulyacij s bazoj dannyh.
Format zapisi operatorov SQL svobodnyj. Mozhno pisat' vse podryad
na odnoj stroke, odin operator na neskol'kih strokah, slova
operatorov mozhno razdelyat' proizvol'nym kolichestvom probelov i
kommentariev. Nikakimi znachkami (tipa ;) operatory razdelyat' ne
nuzhno. Okonchanie operatorov opredelyaetsya po kontekstu.
Primechanie: esli vy zapisyvaete predpisaniya SQL ne v programme
4GL a v srede INFORMIX-SQL, to razdelyat' operatory tochkoj s
zapyatoj (;) neobhodimo.
Ves' nabor klyuchevyh slov yazyka SQL zarezervirovan, ih nel'zya
zanimat' dlya drugih celej (na imena ob®ektov i peremennyh SQL i
4GL).
Kompilyatoru yazyka bezrazlichno, bol'shimi ili malen'kimi bukvami
pishutsya operatory. On ih ne razlichaet.
Kommentarii oboznachayutsya znakami { kommentarij },
ili znakom -- (dva znaka minus) do konca stroki.
Identifikator (imya ob®ekta) - eto slovo, sostoyashchee iz bukv,
cifr, i znakov podcherkivaniya (_), nachinayushcheesya s bukvy ili zna-
ka (_). V INFORMIX-4GL ne razlichayutsya malen'kie i bol'shie buk-
vy. Poetomu i_Un1023Tt i I_UN1023TT - odno i tozhe imya.
Imya bazy dannyh ne dlinnee 10.
Imena prochih ob®ektov SQL - tablic, stolbcov,
view(psevdotablic) , sinonimov - ne dlinnee 18.
* Gruppy operatory yazyka SQL * .
SQL soderzhit 4 gruppy operatorov:
- Operatory opisaniya dannyh:
CREATE, DROP, ALTER i dr.
- Operatory manipulyacii dannymi:
INSERT, DELETE, SELECT, UPDATE i dr.
- Operatory zadaniya prav dostupa v baze dannyh:
GRANT / REVOKE , LOCK / UNLOCK , SET LOCK MODE
- Operatory zashchity, vosstanovleniya dannyh i prochie operatory.
Ih obzorom my sejchas i zajmemsya, po poryadku.
1. OPERATORY OPISANIYA DANNYH * .
Operatory opisaniya dannyh prednaznacheny dlya opisaniya (sozda-
niya), izmeneniya opisaniya i unichtozheniya ob®ektov bazy dannyh.
V SQL razlichayutsya sleduyushchie vidy ob®ektov:
baza dannyh (database);
tablica (table);
stolbec (column);
indeks (index);
snimok (view);
sinonim (synonym).
Kazhdyj ob®ekt imeet sobstvennoe imya - identifikator. Kazhdyj
ob®ekt imeet vladeleca - t.e. togo pol'zovatelya, kotoryj ego
sozdal. Imya ob®ekta mozhno utochnyat' s pomoshch'yu imeni ego vladel'-
ca (owner-name) v takoj forme: moshkow.table1
Nizhe privodyatsya primery ispol'zovaniya vseh operatorov opisaniya
dannyh. Polnyj zhe ih sintaksis mozhno najti v "Kratkom
spravochnike po 4GL" (sm. Prilozheniya), libo v "Spravochnom
rukovodstve po Informix-4GL".
Sozdanie bazy dannyh.
---------------------------------------------------------------
CREATE DATABASE zawod
---------------------------------------------------------------
V lyuboj moment vremeni vy mozhete imet' dostup k ob®ektami tol'-
ko odnoj - TEKUSHCHEJ (CURRENT) - bazy dannyh. Operator DATABASE
delaet novuyu bazu tekushchej, zakryvaya pri etom dostup k ob®ektam
predydushchej tekushchej bazy. Operator CLOSE DATABASE prosto zakry-
vaet tekushchuyu bazu dannyh.
---------------------------------------------------------------
DATABASE zawod
. . . # tekushchej yavlyaetsya baza zawod
DATABASE stanciq
. . . # tekushchej yavlyaetsya baza stanciq
CLOSE DATABASE
# tekushchej bazy net
---------------------------------------------------------------
Sozdayutsya tablicy kadry i ceh, soderzhashchie stolbcy raznyh tipov.
---------------------------------------------------------------
CREATE TABLE kadry (
nomerceh INT,
tabnom SERIAL ,
fio CHAR(20) UNIQUE,
zarplata MONEY(16,2),
datarovd DATE,
pribytie DATETIME year TO minute )
CREATE TABLE ceh ( nomerceh int, nameceh char(20) )
---------------------------------------------------------------
V uzhe sushchestvuyushchej tablice my mozhem pomenyat' tip stolbca, doba-
vit' novyj, unichtozhit' staryj.
---------------------------------------------------------------
ALTER TABLE kadry ADD (dolvnostx CHAR(20) BEFORE zarplata),
DROP(pribytie), ADD CONSTRAINT UNIQUE(tabnom, fio) CONSTRAINT
tabnomfio
ALTER TABLE items MODIFY (manu_code char(4))
---------------------------------------------------------------
Izmenenie struktury tablicy privodit k fizicheskomu preobrazova-
niyu dannyh v nej. Esli izmenen tip stolbca, to dannye v nem
preobrazuyutsya k novomu tipu, i esli eto nevozmozhno osushchestvit',
to operator ALTER "valitsya" s kodom oshibki, a tablica ostaetsya
v neizmenennom sostoyanii.
View - "psevdo" tablica, baziruetsya na sushchestvuyushchih tablicah.
---------------------------------------------------------------
CREATE VIEW poor AS SELECT tabnom, fio, datarovd FROM kadry
WHERE zarplata < 120
# sozdano view - "psevdotablica" iz treh stolbcov soderzhashchaya
# stroki iz tablicy kadry, v kotoryh zarplata men'she 120 rublej.
---------------------------------------------------------------
Vedet sebya tochno tak zhe, kak nastoyashchaya tablica, tol'ko mesto na
diske pod nee ne otvodyatsya, poskol'ku dannye, lezhashchie v nej na
samom dele hranyatsya v tablice, na kotoroj eto view baziruetsya.
Indeks - dopolnitel'naya struktura k stolbcam tablicy, nuzhen dlya
uskoreniya poiska znachenij v stolbce.
---------------------------------------------------------------
CREATE UNIQUE INDEX indkdtb ON kadry (tabnom)
# sozdan indeks dlya stolbca tabnom iz tablicy kadry. Indeks
# unikal'nyj, znachit v stolbce ne mogut poyavit'sya odinakovye
# znacheniya.
---------------------------------------------------------------
My mozhem fizicheski uporyadochit' tablicu v sootvetstvii s indek-
som. V klasterizovannoj tablice SELECT rabotaet bystree.
---------------------------------------------------------------
ALTER INDEX indkdtb TO CLUSTER
---------------------------------------------------------------
Imena stolbcov v raznyh tablicah mogut sovpadat'. Esli v kakom
libo operatore SQL upominayutsya dva stolbca s odinakovymi nazva-
niyami, to ih nuzhno utochnyat' imenami tablic, ih soderzhashchih. Pe-
red imenem lyubogo ob®ekta mozhno (a inogda i neobhodimo) ukazat'
imya ego vladel'ca (owner-name) - vhodnoe imya pol'zovatelya, ko-
toryj sozdal (CREATE) etot ob®ekt.
---------------------------------------------------------------
kadry.nomerceh # stolbec nomerceh iz tablicy kadry
ceh.nomerceh # stolbec nomerceh iz tablicy ceh
iwanow.table1.c1 # stolbec c1 iz tablicy table1, vladel'-
# cem kotoroj yavlyaetsya iwanow
moshkow.table1.c1 # stolbec c1 iz drugoj (!) tablicy -
# tablicy table1, vladel'cem kotoroj yav-
# lyaetsya moshkow
---------------------------------------------------------------
Sinonim dlya imeni tablicy ispol'zuetsya dlya sokrashcheniya zapisi.
---------------------------------------------------------------
CREATE SYNONYM t1 FOR petrow.sostoqnie_postow
---------------------------------------------------------------
Teper' povsyudu mozhno (hotya i ne obyazatel'no) vmesto imeni
petrow.sostoqnie_postow ispol'zovat' imya t1.
Baza dannyh mozhet imet' sistemnyj zhurnal tranzakcij (logfile).
---------------------------------------------------------------
START DATABASE zawod WITH LOG IN "/udd/moshkow/logfile/zawod"
---------------------------------------------------------------
Esli net zhurnala tranzakcij dlya vsej bazy dannyh, to dlya ot-
del'nyh tablic v baze s toj zhe cel'yu mozhno sozdat' audit trail.
---------------------------------------------------------------
CREATE AUDIT FOR kadry IN "/udd/moshkow/kadry.audit"
---------------------------------------------------------------
Estestvenno, chto lyuboj sozdannyj v baze dannyh ob®ekt mozhno
unichtozhit'. Nado tol'ko pomnit', chto operatory opisaniya dannyh
ne otkatyvayutsya nazad, a potomu esli vy unichtozhili tablicu, ili
bazu dannyh to znajte, chto eto navsegda.
---------------------------------------------------------------
DROP VIEW poor # Unichtozhaetsya tol'ko view. S dannymi v tabli-
# cah, na kotoryh ono bazirovalos' nichego ne
# proishodit.
DROP TABLE kadry # unichtozhaet tablicu vmeste s dannymi.
DROP INDEX indkdtb
DROP SYNONYM t1
DROP DATABASE zawod # unichtozhaet bazu vmeste so vsemi dannymi i
# sistemnym zhurnalom
---------------------------------------------------------------
2. Operatory zadaniya prav dostupa v baze dannyh.
Vydavat' i zabirat' prava dostupa k tablice mozhet vladelec tab-
licy, Administrator Bazy Dannyh (imeyushchij DBA prava), a tak zhe
pol'zovatel', kotoromu bylo vydano pravo vydavat' prava (Opera-
torom GRANT WITH GRANT OPTIONS)
---------------------------------------------------------------
REVOKE ALL ON customer FROM PUBLIC
GRANT ALL ON customer TO iwanow, petrow WITH GRANT OPTION
GRANT UPDATE(fname,lname,company, sity),SELECT
ON customer TO PUBLIC
REVOKE CONNECT FROM sidorowa, root
REVOKE DBA FROM ivanov
---------------------------------------------------------------
Otobrat' u vas prava DBA (esli vy, konechno, im yavlyaetes') mozhet
tol'ko drugoj DBA.
Na vremya tranzakcii vse izmenennye stroki avtomaticheski bloki-
ruyutsya sistemoj ot izmeneniya (no ne ot prosmotra). Vy mozhete
yavno lokirovat' vsyu tablicu celikom, togda sistema ne budet
blokirovat' stroki po otdel'nosti. Vy mozhete blokirovat' tabli-
cu celikom ne tol'ko ot izmeneniya no i ot prosmotra.
---------------------------------------------------------------
BEGIN WORK
LOCK TABLE kadry
. . .
UNLOCK TABLE kadry
. . .
LOCK TABLE kadry EXCLUSIVE
---------------------------------------------------------------
Esli vash operator pytaetsya zapisat' v blokirovannuyu drugim
pol'zovatelem stroku, to operator "svalivaetsya". Vy mozhete us-
tanovit' dlya svoej programmy rezhim "ZHdat' razblokirovaniya
strok".
---------------------------------------------------------------
SET LOCK MODE TO WAIT
---------------------------------------------------------------
3. Operatory tranzakcij i vosstanovleniya dannyh.
V baze dannyh, ne imeyushchej sistemnogo zhurnala nevozmozhno vypol-
nenie tranzakcij i vosstanovleniya do tekushchej kontrol'noj tochki.
Poskol'ku za vse horoshee prihoditsya platit', nalichie sistemnogo
zhurnala u bazy dannyh vyzyvaet zametnyj rost nakladnyh rashodov
i zamedlenie raboty zaprosov. K tomu zhe pri aktivnoj rabote
s bazoj sistemnyj zhurnal bystro "raspuhaet". Za nim nuzhno sle-
dit' i periodicheski chistit'.
Ukazat' baze novyj sistemnyj zhurnal.
---------------------------------------------------------------
START DATABASE zawod WITH LOG IN "/ARM/log/zawod"
---------------------------------------------------------------
Vosstanavlivayut razrushennuyu (naprimer iz-za sboya oborudovaniya)
bazu dannyh tak:
---------------------------------------------------------------
V srede UNIX
1. Unichtozhayut ostatki bazy.
2. Zalivayut vmesto nih kopiyu bazy s lenty (naprimer, nedel'noj
davnosti) ispol'zuya komandu cpio ili utilitu INFORMIX dbimport.
3. Vhodyat v sredu INFORMIX i vypolnyayut operatory:
DATABASE kadry EXCLUSIVE # chtoby nikto ne lez
ROLLFORWARD DATABASE kadry # prognat' bazu vpered
# po sistemnomu zhurnalu
CLOSE DATABASE # teper' vsem mozhno rabotat'
---------------------------------------------------------------
Tranzakciya
---------------------------------------------------------------
BEGIN WORK # nachat' tranzakciyu
. . . # operatory
IF vse normal'no THEN COMMIT WORK
ELSE ROLLBACK WORK
END IF
---------------------------------------------------------------
Esli vo vremya tranzakcii programma "svalilas'" to INFORMIX av-
tomaticheski sdelaet otkatku.
* 4. OPERATORY MANIPULYACII DANNYMI * .
Sleduyushchaya gruppa operatorov prednaznachena dlya manipulirovaniya
dannymi v tablicah. V nee vhodyat operatory vybora (SELECT)
strok iz tablicy (ili tablic), unichtozheniya (DELETE) strok v
tablice, vstavki (INSERT) strok, i izmeneniya (UPDATE) znachenij
v sushchestvuyushchih v tablice strokah.
Unichtozhit' v tablice kadry vse stroki, v kotoryh nomer ceha ra-
ven 4, a familiya konchaetsya na bukvy "ov"
---------------------------------------------------------------
DELETE FROM kadry WHERE ceh=4 AND fio MATCHES "*ov"
---------------------------------------------------------------
V rezul'tate iz spiskov budut vycherknuty rabotniki 4-go ceha
"Petrov", "Ivanov", "Sidorov" i t.p
Kak vidim, INFORMIX predostavlyaet nacionalisticheski ozabochennym
rukovoditelyam moshchnye sredstva dlya voploshcheniya v zhizn' svoih idej.
A etot operator unichtozhit VSE stroki v tablice kadry, vladel'-
cem kotoroj yavlyaetsya moshkow, no ne samu tablicu
---------------------------------------------------------------
DELETE FROM moshkow.kadry
---------------------------------------------------------------
Prostejshaya forma operatora SELECT.
Pervyj primer nahodit v tablice kadry stroku, v kotoroj stolbec
tabnum=345 . Iz etoj stroki berutsya tol'ko tri ukazanyh stolbca.
Vtoroj primer vybiraet VSE stroki iz tablicy ceh, i vse stolbcy.
---------------------------------------------------------------
SELECT fio, dolvn, zarplata FROM kadry WHERE tabnom=345
SELECT * FROM ceh
SELECT kadry.fio, ceh.nameceh WHERE kadry.nomerceh=ceh.nomerceh
---------------------------------------------------------------
Tretij primer vybiraet familii rabotnikov iz tablicy kadry, a
nazvaniya cehov, v kotoryh oni rabotayut, iz tablicy ceh.
mozhet vstavit' v tablicu odnu stroku, esli ispol'zuetsya v forme
INSERT INTO ... VALUES, a mozhet vstavit' v tablicu celyj nabor
strok, vybrannyh podzaprosom SELECT iz drugoj tablicy.
---------------------------------------------------------------
INSERT INTO kadry VALUES (4,0,"Gric'ko",num,"10/25/1939",NULL)
INSERT INTO customer VALUES (ps_customer.*)
# ps_customer - peremennaya tipa RECORD - analog struktury v
# yazyke Si. |tot operator vstavlyaet znacheniya elementov zapisi
# ps_customer v sootvetstvuyushchie polya tablicy customer
INSERT INTO kadry (tabnom, fio, nomerceh, dolvnostx)
SELECT 0 , fio, 4, dolvnostx FROM kadryold
WHERE nomerceh=3 AND fio IS NOT NULL
# poslednij operator vstavlyaet srazu neskol'ko strok
---------------------------------------------------------------
Esli my hotim, chtoby pri vstavlenii stroki v stolbec tipa SERI-
AL avtomaticheski zanosilos' ocherednoe znachenie schetchika, nuzhno
vstavlyat' v etot stolbec konstantu 0.
Esli ne vo vse stolbcy vstavlyaemoj stroki vnositsya znachenie
(kak eto sdelano v tret'em operatore), to nezapolnennye stolbcy
zapolnyayutsya znacheniem NULL.
V operatorah DELETE, UPDATE, SELECT mozhet prisutstvovat' WHERE
predlozhenie, v kotorom mozhno zadat' usloviya na stroki, kotorye
trebuetsya obrabotat' (sootvetstvenno unichtozhit', izmenit' ili
vybrat'). Rassmotrim primery ispol'zovaniya WHERE predlozheniya.
menyaet znacheniya stolbcov, v strokah, udovletvoryayushchim WHERE us-
loviyu.
---------------------------------------------------------------
UPDATE kadry SET fio="Zykova" WHERE fio="Girusova"
UPDATE ceh SET kod_ceha[1,4]=nameceh[5,8] WHERE
nomerceh BETWEEN 3 AND 5 OR nameceh IN ("tokarnyj","litejnyj")
---------------------------------------------------------------
V tablice ceh v cehah nomer 3,4,5 a tak zhe v tokarnom i litej-
nom pervye chetyre simvola v kode ceha budut zameneny na podst-
roku polya nameceh iz toj zhe stroki.
Predlozhenie WHERE mozhet prisutstvovat' v lyubom iz operatorov
DELETE, UPDATE, SELECT, kogda nuzhno zadat' usloviya na stroki,
kotorye trebuetsya obrabotat' (sootvetstvenno, unichtozhit', izme-
nit' ili vybrat'). Rassmotrim strukturu i primery ispol'zovaniya
predlozhenij WHERE.
V predlozhenii WHERE pishetsya logicheskoe uslovie, kotoroe polucha-
etsya soedineniem s pomoshch'yu logicheskih operatorov AND, OR i NOT
elementarnyh sravnenij tipa:
vyrazhenie1 < vyrazhenie2,
vyrazhenie1 >= vyrazhenie2, i t.p.,
a tak zhe elementarnyh sravnenij special'nogo vida:
column-name IS [NOT] NULL
vyrazh [NOT] BETWEEN vyrazh1 AND vyrazh2
vyrazh [NOT] IN (vyrazh1 , ... [, ...] )
Mozhno vyyasnit', podhodit li simvol'naya stroka pod opredelennyj
shablon, ili net. Dlya etogo ispol'zuyutsya dve operacii sravneniya
po shablonu - LIKE i MATCHES.
simv-vyrazhenie MATCHES "shablon"
simv-vyrazhenie LIKE "shablon"
LIKE imeet bolee prostoj shablon. V nem ispol'zuyutsya tol'ko dva
specsimvola: (%) zameshchaet proizvol'noe kolichestvo simvolov, (_)
zameshchaet rovno odin simvol. Vse ostal'nye simvoly v shablone
oboznachayut sami sebya. Esli my hotim vklyuchit' v shablon % ili _
otmeniv ih special'nyj smysl, to pered nimi nado postavit' ESC
simvol (po umolchaniyu eto (\)).
Dopustim nam nuzhno vybrat' iz tablicy tab8 vse stroki, v koto-
ryh simvol'nyj stolbec string1 soderzhit simvol "+" a predpos-
lednyaya bukva v nem - "Y". Operator vyborki budet vyglyadet' tak:
---------------------------------------------------------------
SELECT * FROM tab8 WHERE string1 LIKE "%+%Y_"
---------------------------------------------------------------
MATCHES ispol'zuet takie specsimvoly shablona: *,?,[,],^,-.
* zamenyaet lyuboe kolichestvo simvolov
? zamenyaet odin lyuboj simvol
[...] zamenyaet odin simvol iz perechislennyh v skobkah
vozmozhno ukazanie ot i do (-), i ne (^)
[abH] - lyuboj iz simvolov a, b, H
[^d-z] - lyuboj simvol, isklyuchaya d,e,f,g, ... ,y,z
\ otmenyaet specsmysl specsimvolov *,?,[,]
Esli vy hotite vospol'zovat'sya specsimvolami kak obychnymi,
primenite escape-char. Esli escape-char="\", to \? oboznachaet
prosto simvol ?, \* oboznachaet prosto simvol *, \\ oboznachaet
prosto simvol \ . Zato znak kavychki (") vnutri shablona nuzhno
oboznachat' dvumya kavychkami ("").
Vybrat' vse dannye o zakazchikah v nazvanii kompanii kotoryh
vtoraya bukva ne lezhit v intervale ot G do L, a tret'ya bukva c.
(Kstati, kody russkih bukv na BESTE idut podryad, no v otlichie
ot latinskih bukv, russkie ne uporyadochenny po alfavitu.)
---------------------------------------------------------------
SELECT * FROM customer WHERE company MATCES"?[^G-L]c*"
---------------------------------------------------------------
Vybrat' vse dannye o zakazchikah v nazvanii kompanii kotoryh
prisutstvuet voprositel'nyj znak.
---------------------------------------------------------------
SELECT * FROM customer
WHERE company MATCHES "*YA?*" ESCAPE"YA"
---------------------------------------------------------------
V dannom primere ispol'zovalsya ESC-simvol "YA" dlya otmeny specs-
mysla simvola "?"
Esli v vy hotite
. Sravnit' vyrazhenie s rezul'tatom drugogo SELECT operatora
vyrazh sravn {ALL | [ANY | SOME]} (SELECT-statement)
. Opredelit', prinadlezhit li vyrazhenie rezul'tatam drugogo
SELECT operatora.
vyrazh [NOT] IN (SELECT-statement)
. Vyyasnit', vybral li hot' chto-nibud' drugoj SELECT operator.
[NOT] EXISTS (SELECT-statement)
to primenyajte usloviya s podzaprosom.
* USLOVIYA S PODZAPROSOM *
---------------------------------------------------------------
SELECT fio FROM kadry WHERE zarplata=
(SELECT MAX(zarplata) FROM kadry )
---------------------------------------------------------------
Zdes' podzapros vozvrashchaet edinstvennoe znachenie - maksimal'noe
znachenie zarplaty. A vneshnij SELECT operator nahodit familii
obladatelej onoj.
---------------------------------------------------------------
SELECT fio, shifr, organizaciq FROM zaqwki WHERE denxgi_rek is
not NULL and
gorod in (SELECT gorod FROM regiony WHERE region="Ural")
---------------------------------------------------------------
Zdes' zapros vyvodit dannye ob rukovoditelyah, poluchivshih finan-
sirovanie i rabotayushchih na Urale.
---------------------------------------------------------------
SELECT order_num,stock_num,manu_code, total_price FROM items x
WHERE total_price > (SELECT 2*MIN(total_price)
FROM items WHERE order_num=x.order_num)
---------------------------------------------------------------
|tot zapros (ispol'zuya svyazannyj podzapros) vyvodit spisok vseh
izdelij, ch'ya obshchaya cena ne menee chem v dva raza prevoshodit mi-
nimal'nuyu cenu izdelij perechislennyh v etom zhe ordere.
Vy mozhete soedinyat' lyuboe kolichestvo vysheperechislennyh uslovij
vmeste, ispol'zuya logicheskie operatory NOT, AND, OR.
Operator UNLOAD sbrasyvaet dannye iz tablicy v fajl v pechatnom
predstavlenii. Kazhdaya stroka preobrazuetsya v otdel'nuyu zapis',
znacheniya iz stolbcov razdelyayutsya simvolom "|".
Posle vypolneniya operatora
---------------------------------------------------------------
UNLOAD TO "kadry19.unl" SELECT * FROM kadry
---------------------------------------------------------------
v fajle kadry19.unl mozhno budet obnaruzhit' sleduyushchee:
---------------------------------------------------------------
5|5|tuev|zavhoz|100.0|31.12.1946|
4|6|petunin|kladovshchik|80.0||
. . .
---------------------------------------------------------------
Operator LOAD vypolnyaet obratnuyu operaciyu - schityvaet stroki iz
fajla i vstavlyaet ih v tablicu. Estestvenno, chto tipy i
kolichestvo znachenij v strokah fajla dolzhny sootvetstvovat'
stolbcam tablicy.
---------------------------------------------------------------
LOAD FROM "kadry20.unl" INSERT INTO kadry
---------------------------------------------------------------
* 5. SNOVA OPERATOR SELECT * .
Predlozheniya INTO, INTO TEMP, FROM.
Vybrat' vse stroki (net predlozheniya WHERE) iz tablicy kadry,
vzyat' v nih vse stolbcy (vmesto perechisleniya stolbcov stoit *),
ostavit' tol'ko razlichnye stroki (klyuchevoe slovo UNIQUE) i po-
mestit' rezul'tat vo vremennuyu tablicu (INTO TEMP) x, kotoraya
budet pri etom sozdana s takimi zhe stolbcami, chto i u kadry.
---------------------------------------------------------------
SELECT UNIQUE * FROM kadry INTO TEMP x
---------------------------------------------------------------
Vybirat' mozhno iz neskol'kih tablic. Pri etom berutsya vse voz-
mozhnye kombinacii strok iz pervoj tablicy so vtoroj. Predpolo-
zhim, chto tablice tab1 6 strok a v tab2 - 7 strok. Rezul'tat ni-
zheprivedennogo primera - tablica, soderzhashchaya tri stolbca i
7*6=42 stroki.
---------------------------------------------------------------
SELECT tab1.a-tab2.b, tab1.a, tab2.b FROM tab1, tab2
---------------------------------------------------------------
My sejchas ne budem utochnyat', kuda imenno rezul'tiruyushchaya tablica
pomeshchaetsya. No ispol'zovat' ee mozhno po raznomu: ee mozhno pe-
regnat' (INTO TEMP) vo vremennuyu tablicu, ee mozhno otdat' na
obrabotku drugomu operatoru (esli vyborku osushchestvlyal podzap-
ros), dlya nee mozhno sozdat' kursor ("bufer" s ukazatelem na te-
kushchuyu stroku), a mozhno polozhit' ee (INTO) v prostuyu programmnuyu
peremennuyu (esli vybrano ne bolee odnoj stroki).
Vybrannye stroki mozhno uporyadochit' po vozrastaniyu (ubyvaniyu)
znacheniya v stolbce (stolbcah)
---------------------------------------------------------------
SELECT a,b,c,d+e FROM tabl ORDER BY b,c
SELECT a,b,c,d+e FROM tabl ORDER BY 2,3
---------------------------------------------------------------
V ORDER BY predlozhenii vmesto imeni stolbca mozhno ukazyvat' ego
poryadkovyj nomer v spiske vyborki (select-list). Vyshepriveden-
nye operatory ekvivalentny.
Pomestit' znacheniya iz stolbcov v peremennye: (Poskol'ku lname
ispol'zuetsya i kak imya peremennoj, i kak imya stolbca, to imya
stolbca predvaryaetsya znakom (@)
---------------------------------------------------------------
SELECT customer_num, @lname,city INTO cnum,lname,town
FROM customer
---------------------------------------------------------------
K vybrannym strokam mozhno primenyat' agregatnye funkcii COUNT(*)
- kolichestvo, MAX(column) i MIN(column) - maksimal'noe i mini-
mal'noe znachenie v stolbce, SUM(column) - summa vseh znachenij v
stolbce, AVG(column) - srednee znachenie v stolbce.
Pomestit' v peremennuyu num kolichestvo strok v tablice orders, v
kotoryh stolbec customer_num raven 101:
---------------------------------------------------------------
SELECT COUNT(*) INTO num
FROM orders WHERE customer_num=101
---------------------------------------------------------------
Primer s ispol'zovaniem soedineniya tablic. Nahoditsya srednee
znachenie zarplaty prevoshodyashchej 300 (stolbec zarplata prinadle-
zhit odnoj iz tablic), pri uslovii sovpadeniya stolbcov dolvnost
v dvuh tablicah.
---------------------------------------------------------------
SELECT AVG (zarplata) FROM table1,table2
WHERE table1.dolvnost=table2.dolvnost and zarplata>300
---------------------------------------------------------------
Gruppirovka ispol'zuetsya dlya dlya "splyushchivaniya" gruppy (strok) v
odnu.
Rezul'tat zaprosa soderzhit odnu stroku dlya kazhdogo mnozhestva
strok, udovletvoryayushchih WHERE predlozheniyu i soderzhashchih odno i to
zhe znachenie v ukazannom stolbce.
---------------------------------------------------------------
SELECT dolvnostx, COUNT(*), AVG(zarplata) FROM kadry
GROUP BY dolvnostx
---------------------------------------------------------------
Poluchit' kolichestvo rabotayushchih i ih srednyuyu zarplatu po kazhdoj
dolzhnosti iz shtatnogo raspisaniya.
---------------------------------------------------------------
SELECT dolvnostx, COUNT(*), AVG(zarplata) FROM kadry GROUP BY 1
---------------------------------------------------------------
|kvivalentnaya zapis'.
Predlozhenie HAVING nakladyvaet dopolnitel'nye usloviya na
gruppu.
---------------------------------------------------------------
SELECT order_num, AVG(total_priece) FROM items
GROUP BY order_num HAVING COUNT(*) > 2
---------------------------------------------------------------
|tot zapros vozvrashchaet nomera orderov i srednee znachenie
total_price v zayavkah dlya vseh orderov, imeyushchih ne menee dvuh
zayavok.
Vneshnee soedinenie tablic.
Stroki iz tablicy, prisoedinennoj vneshnim obrazom (na vneshnee
soedinenie ukazyvaet klyuchevoe slovo OUTER) budut vybirat'sya ne
smotrya na to, udovletvoryayut oni usloviyam WHERE predlozheniya ili
net. V nekotoryh sluchayah eto polezno, kogda u vas est' glavnaya
tablica i est' vspomogatel'naya, i dannye iz glavnoj tablicy vam
nuzhno poluchit' v lyubom sluchae. Primer vneshnego soedineniya:
---------------------------------------------------------------
SELECT company, order_num
FROM customer c, OUTER orders o
WHERE c.customer_num=o.customer_num
---------------------------------------------------------------
Zapros nahodit nazvaniya kompanij i nomera orderov, kotorye oni
poslali. Esli zhe kompaniya orderov ne prisylala, to ee nazvanie
vse ravno budet vybrano, a nomer ordera v etoj stroke budet ra-
ven NULL. (A esli by my zapustili zapros bez parametra OUTER,
to nazvaniya etih kompanij voobshche by ne popali v vyborku.)
6. SLOZHNYE PRIMERY MANIPULYACII DANNYMI * .
Operatory manipulyacii dannymi - samaya moshchnaya sostavlyayushchaya SQL.
Sleduyushchij primer likvidiruet odinakovye stroki v tablice kadry.
---------------------------------------------------------------
select unique * from kadry into temp kd
delete from kadry where 1=1
insert into kadry select * from kd
drop table kd
---------------------------------------------------------------
Sleduyushchij primer izmenyaet informaciyu v strokah po znacheniyu
klyucha
Tablica b soderzhit (kl int, pole char(20));
prichem vse kl razlichny
V tablice kadry zamenit' kadry.dolvnostx na b.pole
v strokah gde kadry.tabnom=b.kl
---------------------------------------------------------------
SELECT b.kl,b.pole, nomerceh,dolvnostx,zarplata,datarovd
FROM kadry, b WHERE kadry.tabnom=b.kl into temp kd
DELETE FROM kadry WHERE tabnom in (SELECT kl FROM b)
INSERT INTO kadry SELECT * FROM kd
DROP TABLE kd
---------------------------------------------------------------
Tu zhe samuyu operaciyu mozhno prodelat' s pomoshch'yu odnogo operatora
UPDATE, ispol'zuyushchego podzapros:
---------------------------------------------------------------
UPDATE kadry SET
dolvnostx=(select pole from b where kadry.tabnom=b.kl)
WHERE tabnom IN (select kl from b)
---------------------------------------------------------------
Primer izmenyaet informaciyu v strokah po znacheniyu klyucha
pri vypolnenii uslovij , nalozhennyh na menyaemye stroki:
Tablica agent Tablica cia
+-----+---------+----+ +-----+ ---------+ ----+
|fio | har | cen| |fio | . . . har | . . . cen|
|John |lentyaj |$300| |John | trudyaga | $600|
|Piter|agent KGB| | . . .
|Bob |horoshij |$25 | |Piter| agent CIA| $45 |
+-----+---------+----+ . . .
|Bob | plohoj | $15 |
|Ronny| plohoj | |
. . .
+-----+ ---------+ ----+
V tablice cia hranyatsya svedeniya o sotrudnikah. Na osnove pos-
lednih issledovanij byla sostavlena tablica agent, s popravkami
k soderzhaniyu cia.
Strochka budet podmenyat'sya, esli za novuyu informaciyu o sotrudni-
ke v tablice agent zaplacheno bol'she, chem za hranyashchuyusya v cia.
---------------------------------------------------------------
UPDATE cia SET
(har,cen)=( (SELECT har,cen FROM agent WHERE cia.fio=agent.fio) )
WHERE fio IN (SELECT fio FROM agent) AND
cen < (SELECT cen FROM agent WHERE cia.fio=agent.fio);
Last-modified: Tue, 25 Nov 1997 07:15:47 GMT