DML InterBase/FireBird
SELECT
SELECT SQL. SQL SELECT , SQL , SELECT .
. . SELECT SQL- , . , SQL-, , .
, () . , , () .
. , , , ( , , ). , , . .
, , SELECT.
SELECT
SELECT {* | < 1>[,< 2>,...]}
FROM < 1>[,< 2>,...];
< 1>, < 2>,... ;
< 1>, < 2>,... .
. , *.
, :
SELECT * FROM Lica;
:
SELECT Nlic, Name, Tip, Podr FROM Lica;
WHERE
, , . SELECT :
WHERE < >
:
|
|
|
< > = < ><>
<>={<|>|<+|>+|!<|!>|=|<>|!=}, .
: , 3:
SELECT * FROM Nalichie WHERE Predmet=3;
: AND, OR, NOT.
. SQL : SQL . , . SQL .
: , 5 12:
SELECT * FROM Nalichie WHERE Lico=5 AND Predmet=12;
, .
(NOT NULL) , :
< > IS [NOT] NULL
, ( ):
SELECT * FROM Lica WHERE Podr IS NOT NULL;
:
< 1> <> < 2>
, Lookup- .
, , , :
SELECT Lica.Name, Tipy.Name, Podrazd.Name
FROM Lica, Tipy, Podrazd
WHERE Tipy.NTip=Lica.Tip AND Lica.Podr=Podrazd.NPodr;
, . , ( ).
, WHERE , , .
.
: ( , , ).
. , :
1 1, 1 2, 1 3
1 1, 1 2, 2 3
1 1, 1 2, 3 3
...
1 1, 2 2, 1 3
|
|
|
1 1, 2 2, 2 3
...
2 1, 1 2, 1 3
2 1, 1 2, 2 3
2 1, 1 2, 3 3
...
( ) , . (, , SQL- , .)
, T1 T2 C1, C2, C3 C1, C2, . T2 T1, T2.C1 T1.C2:
T1
| C1 | C2 | C3 |
| a | ||
| b | ||
| c | ||
| d |
T2
| C1 | C2 |
| x | |
| y | |
| z |
:
SELECT T1.C1, T2.C2, T1.C3 FROM T1, T2 WHERE T1.C2=T2.C1;
:
T1*T2
| T1.C1 | T1.C2 | T1.C3 | T2.C1 | T2.C2 |
| a | x | |||
| a | y | |||
| a | z | |||
| b | x | |||
| b | y | |||
| b | z | |||
| c | x | |||
| c | y | |||
| c | z | |||
| d | x | |||
| d | y | |||
| d | z |
, T1.C2=T2.C1:
| T1.C1 | T2.C2 | T1.C3 |
| a | x | |
| b | x | |
| c | y |
1. , , ( , ). (, ), ( ).
2. . JOIN ON . :
SELECT Lica.Name, Tipy.Name, Podrazd.Name
FROM Lica
JOIN Tipy ON Tipy.NTip=Lica.Tip
JOIN Podrazd ON Lica.Podr=Podrazd.NPodr;
, ( JOIN ON) ( WHERE).
, , . , . FROM:
FROM < 1> < 1>[, < 2> < 2>,...]
:
SELECT L.Name, T.Name, P.Name
FROM Lica L, Tipy T, Podrazd P
WHERE T.NTip=L.Tip AND L.Podr=P.NPodr;
. : .*
, SELECT, , .
, SELECT :
|
|
|
ORDER BY < >
, , :
SELECT L.Name, T.Name, P.Name
FROM Lica L, Tipy T, Podrazd P
WHERE T.NTip=L.Tip AND L.Podr=P.NPodr
ORDER BY P.Name, L.Name;
1. Lookup- , , .
2. , .
( ), . , , , , , .
SELECT DISTINCT. ALL ( ). , .
, , :
SELECT DISTINCT P.Name FROM Nalichie N, Predmety P
WHERE P.NPredm=N.Predmet AND N.Kolvo>0
ORDER BY P.Name;
1. DISTINCT , SELECT. . , , .
2. DISTINCT , . , DISTINCT .
SELECT ( ). :
SELECT P.Name, N.Kolvo, P.Cena*N.Kolvo
FROM Nalichie N, Predmety P
WHERE P.NPredm=N.Predmet AND N.Lico=3
ORDER BY P.Name;
:
SELECT P.Name, N.Kolvo, P.Cena*N.Kolvo AS Stoimost
FROM Nalichie N, Predmety P
WHERE P.NPredm=N.Predmet AND N.Lico=3
ORDER BY P.Name;
WHERE. , , 1000 :
SELECT P.Name, N.Kolvo, P.Cena*N.Kolvo
FROM Nalichie N, Predmety P
WHERE P.NPredm=N.Predmet AND N.Lico=3 AND P.Cena*N.Kolvo>1000
ORDER BY P.Name;
:
COUNT(<>) ;
SUM(<>) ;
AVG(<>) ;
MIN(<>) ;
MAX(<>) .
, :
SELECT COUNT(*) FROM Predmety;
, :
SELECT COUNT(DISTINCT Predmet) FROM Nalichie
WHERE Kolvo>0;
3:
SELECT SUM(P.Cena*N.Kolvo) AS Vsego
|
|
|
FROM Nalichie N, Predmety P
WHERE P.NPredm=N.Predmet AND N.Lico=3;
:
SELECT AVG(P.Cena*V.Kolvo) AS SrSumma
FROM Provodki V, Predmety P
WHERE P.NPredm=V.Predmet AND
V.Date>=1-JAN-2012 AND V.Date<=31-JAN-2012;
1 3 :
SELECT P.Name, V.Date, MAX(P.Cena*V.Kolvo) AS MaxProvodka
FROM Provodki V, Predmety P
WHERE P.NPredm=V.Predmet AND V.Rashod=1 AND V.Prihod=3
AND V.Date>=1-JAN-2012 AND V.Date<=31-JAN-2012;
3.3.2.
, SQL, SQL, , . ( WHERE) , .
INSERT :
INSERT INTO _[(_1[, _2...])]
{VALUES(<_1>[, <_2>...]) |
< SELECT>};
, . , , , .
. , , .
, , . , VALUES, SELECT.
INSERT :
INSERT INTO _[(_1[, _2,...])]
VALUES(<_1>[, <_2>...]);
, _1 _1, _2 _2 .. ( ).
, 12 5 100 :
INSERT INTO Nalichie(Lico, Predmet, Kolvo)
VALUES(5, 12, 100);
- , (NULL).
( SELECT) INSERT , , SELECT. , INSERT SELECT. , ( , ) :
INSERT INTO Archive SELECT * FROM Provodki;
, . INSERT SELECT.
UPDATE :
UPDATE _
SET _1=< 1>[, _2=< 2>...]
[WHERE < >];
, . , ! WHERE, ! UPDATE , SELECT.
, 1000:
UPDATE Predmety SET Cena=Cena/1000;
1.01.2012 11.01.2012:
UPDATE Provodki SET Data=11-JAN-2012
WHERE Data=1-JAN-2012;
:
DELETE FROM _ [WHERE < >];
, , . , !
3.3.3.
InterBase/FireBird . , . :
CREATE GENERATOR _;
. , :
|
|
|
SET GENERATOR _ TO _;
:
GEN_ID (_, );
, . .
. , .
, :
CREATE GENERATOR PredmetN;
...
INSERT INTO Predmety(NPredm, Name, EdIzm, Cena)
VALUES(GEN_ID(PredmetN), -92, , 19.50);
1. DROP GENERATOR. , RDB$GENERATORS. .
2. , . , :
CREATE TRIGGER BI_Predmety FOR Predmety
ACTIVE BEFORE INSERT AS
BEGIN
NEW.NPredm=GEN_ID(PredmetN,1);
END
, , .
3. , :
CREATE PROCEDURE Get_PredmetN RETURNS(N Integer) AS
BEGIN
N=GEN_ID(PredmetN,1);
END
.
3.3.4.
- . , , , , , , . , .
, , .
, , . , : - , .
:
, SELECT, ( );
, .
:
CREATE PROCEDURE _
[(.1 _[,.2 _...])]
[RETURNS(.1 _[,.2
_...])]
AS < >;
. .
. , , .
, . .
:
[< >]
BEGIN
<>
[<>...]
END
.
InterBase /FireBird, PSQL. , , . .
. :
DECLARE VARIABLE _ _;
InterBase/FireBird. .
. , SELECT, INSERT, UPDATE DELETE . . .. , , , ( ). .
BEGIN... END
. -, , , -, , , .
1: BEGIN, END .
2: :
/* */
:
_=<>;
, , , , , .
: *, /, +, -; : AND, OR, NOT. || . :
Ans= - || TODAY;
UPPER. , :
IF (UPPER(Name)=) THEN...
Name, ..
CAST. CAST:
CAST({<> | NULL} AS _)
:
DECLARE VARIABLE Data CHAR(11);
...
Data=CAST(TODAY AS CHAR);
, :
NOW ;
TODAY ;
TOMORROW ;
YESTERDAY .
(). , (NULL), NULL .
, A IS NULL; B=1; A=A+1; B=A+B; NULL.
, , ( ) (NOT NULL), NULL:
IF (A IS NULL) THEN A=0; A=A+1;
-, (TRUE, FALSE, UNKNOWN). , IF THEN , TRUE, ELSE, FALSE UNKNOWN.
(MIN, MAX, SUM,...) UDF- (User Defined Function). DLL-. UDF- .
. InterBase FireBird ib_udf.dll fbudf.dll, . ib_udf.sql fbudf.sql. , .
:
1) ;
2) ;
3) ;
4) .
:
1) ;
2) ;
3) ;
4) .
. , . , .
:
1) =
2) <>
3) >
4) <
5) >=
6) <=
7)!>
8)!<
. ALL, SOME, EXISTS, IN, NULL, LIKE,... -.
:
1) NOT
2) AND
3) OR
. . .
:
IF (<>) THEN <_1> [ELSE <_2>]
, TRUE, FALSE UNKNOWN. TRUE, _1, FALSE UNKNOWN _2.
. ELSE .
. _1 _2 .
. S : S1 S2, (NULL):
IF (S1 IS NOT NULL) THEN
IF (S2 IS NOT NULL) THEN
S = S1 || S2;
ELSE
S = S1;
ELSE
IF (S2 IS NOT NULL) THEN
S = S2;
ELSE
S = ;
:
IF (S1 IS NULL) THEN S1=;
IF (S2 IS NULL) THEN S2=;
S = S1 || S2;
:
WHILE (<>) DO <>
, TRUE.
. :
CREATE PROCEDURE Factorial(N INTEGER)
RETURNS(F DOUBLE PRECISION) AS
DECLARE VARIABLE I INTEGER;
BEGIN
F = 1;
I = 2;
WHILE (I <= N) DO
BEGIN
F = F*I; I = I+1;
END
END
, SELECT, INTO, , .
. SELECT . .
. .
:
FOR < SELECT> DO <_1>
, SELECT, _1. SELECT INTO.
. ( ):
CREATE PROCEDURE KolvoPredm(Name VARCHAR(30))
RETURNS(Kolvo DOUBLE PRECISION) AS
DECLARE VARIABLE N INTEGER;
DECLARE VARIABLE K DOUBLE PRECISION;
BEGIN
Kolvo = 0;
FOR SELECT NPredm FROM Predmety WHERE UPPER(Name)=:Name
INTO:N DO
BEGIN
SELECT SUM(Kolvo) FROM Nalichie WHERE Predmet=:N
INTO:K;
IF (K IS NULL) THEN K = 0;
Kolvo = Kolvo + K;
END
END
SELECT , ( UPPER(Name) Name Predmety, :Name Name). , .
, , .
SUSPEND
.
SUSPEND, , RETURNS, .
: SUSPEND .
. , , , :
CREATE PROCEDURE KolvoPredmPoCene(Name VARCHAR(30))
RETURNS(Cena DOUBLE PRECISION, Kolvo DOUBLE PRECISION) AS
DECLARE VARIABLE N INTEGER;
BEGIN
FOR SELECT NPredm, Cena FROM Predmety
WHERE UPPER(Name)=:Name INTO:N,:Cena DO
BEGIN
SELECT SUM(Kolvo) FROM Nalichie WHERE Predmet=:N
INTO:Kolvo;
SUSPEND;
END
END
, ( ; ).
EXIT
END . EXIT , , .
SUSPEND, EXIT END
| SUSPEND | EXIT | END | |
| END | END | ||
| END | , |
.
, . 1000 ( ), , .
:
EXECUTE PROCEDURE _
[__1[,__2...]]
[RETURNING_VALUES __1
[, __2...]];
/ , . , .
, . .
. , ( ):
CREATE PROCEDURE AvgProvodka(NPr INTEGER)
RETURNS(AvgSumma DOUBLE PRECISION) AS
BEGIN
SELECT AVG(V.Kolvo*P.Cena) FROM Provodki V, Predmety P
WHERE V.Predmet=P.NPredm AND V.Predmet=:NPr
INTO:AvgSumma;
END
CREATE PROCEDURE SelectBigProvodki(NPredm INTEGER)
RETURNS(NameRashod CHAR(30), NamePrihod CHAR(30),
Data DATE, Kolvo DOUBLE PRECISION) AS
DECLARE VARIABLE AVS DOUBLE PRECISION;
BEGIN
EXECUTE PROCEDURE AvgProvodka:NPredm
RETURNING_VALUES:AVS;
FOR SELECT L1.Name, L2.Name, V.Data, V.Kolvo
FROM Provodki V
JOIN Lica L1 ON L1.Nlic=V.Rashod
JOIN Lica L2 ON L2.Nlic=V.Prihod
JOIN Predmety P ON P.NPredm=V.Predmet
WHERE P.Cena*V.Kolvo > 3*:AVS
INTO:NameRashod,:NamePrihod,:Data,:Kolvo DO
SUSPEND;
END
, SELECT . , (L1 L2) .
, SELECT , SELECT ( , , SELECT). , .
()
, .
:
1) SQL SQLCODE;
2) InterBase/FireBird GDSCODE;
3) , .
, , , . :
CREATE EXCEPTION __ <>;
.
, , :
EXCEPTION __;
:
...
CREATE EXCEPTION KolvoLessZero ;
...
IF (Kolvo < 0) THEN EXCEPTION KolvoLessZero;
...
:
DROP EXCEPTION __;
:
ALTER EXCEPTION __ <>;
( WHEN), , .
:
DROP PROCEDURE _;
: DROP CREATE, ALTER PROCEDURE, , CREATE PROCEDURE.
. ALTER PROCEDURE , - .
:
EXECUTE PROCEDURE _
[(__1[, __2...])];
, 15:
EXECUTE PROCEDURE AvgProvodka(15);
SELECT, FROM . SELECT (*) . , 12:
SELECT * FROM SelectBigProvodki(12);
:
SELECT DISTINCT NameRash, NamePrih FROM SelectBigProvodki(12);
. EXECUTE, . SELECT, , .
Delphi, InterBase/FireBird.
3.3.5.
, , , .
, , . , .
, . , , , .
, , , :
1) ;
2) ;
3) .
:
1) ;
2)
:
CREATE TRIGGER _ FOR _
[{ACTIVE | INACTIVE}] {BEFORE | AFTER}
{DELETE | INSERT | UPDATE} [POSITION ]
AS <_>;
_ , ;
ACTIVE | INACTIVE ( ). ACTIVE;
DELETE | INSERT | UPDATE ;
BEFORE | AFTER : ;
POSITION , ( 0 32767). .
, T1 :
CREATE TRIGGER Tr1 FOR T1 BEFORE INSERT POSITION 1 AS...
CREATE TRIGGER Tr2 FOR T1 BEFORE INSERT POSITION 0 AS...
CREATE TRIGGER Tr3 FOR T1 BEFORE INSERT POSITION 9 AS...
CREATE TRIGGER Tr4 FOR T1 AFTER INSERT POSITION 4 AS...
CREATE TRIGGER Tr5 FOR T1 AFTER INSERT POSITION 2 AS...
: Tr2, Tr1, Tr3, Tr5, Tr4.
, . , . OLD NEW.
OLD , . OLD .
NEW . .
1. , SELECT.
2. , , . , , AFTER INSERT NEW._, . , , . , , .
3. , , , . , , . , , , INSERT , .
4. , .
ALTER TRIGGER. :
1) , , , ;
2) ;
3) .
ALTER TRIGGER CREATE TRIGGER : FOR ( , , ).
ALTER TRIGGER , , . , Tr1 :
ALTER TRIGGER Tr1 INACTIVE;
(BEFORE, AFTER), (DELETE, INSERT, UPDATE).
ALTER TRIGGER AS.
:
DROP TRIGGER _;
, .
. . .
. , , :
CREATE TRIGGER _ FOR __
ACTIVE BEFORE UPDATE AS
BEGIN
IF (OLD.___ <> NEW.___) THEN
UPDATE __
SET ___ = NEW.___
WHERE ___ = OLD.___;
END
CREATE TRIGGER _ FOR __
ACTIVE BEFORE DELETE AS
BEGIN
DELETE FROM __
WHERE ___ = OLD.___;
END
. , ( ):
CREATE EXCEPTION BadProvodka ;
CREATE TRIGGER BI_Provodki FOR Provodki
ACTIVE BEFORE INSERT AS
DECLARE VARIABLE KolvoRash DOUBLE PRECISION;
DECLARE VARIABLE KolvoPrih DOUBLE PRECISION;
BEGIN
SELECT Kolvo FROM Nalichie
WHERE Predmet=NEW.Predmet AND Lico=NEW.Rashod
INTO KolvoRash;
SELECT Kolvo FROM Nalichie
WHERE Predmet=NEW.Predmet AND Lico=NEW.Prihod
INTO KolvoPrih;
IF (KolvoRash >= NEW.Kolvo) THEN
BEGIN
UPDATE Nalichie
SET Kolvo = Kolvo NEW.Kolvo
WHERE Predmet=NEW.Predmet AND Lico=NEW.Rashod;
IF (KolvoPrih IS NULL) THEN
INSERT INTO Nalichie(Lico, Predmet, Kolvo)
VALUES(NEW.Prihod, NEW.Predmet, NEW.Kolvo);
ELSE
UPDATE Nalichie
SET Kolvo = Kolvo + NEW.Kolvo
WHERE Predmet=NEW.Predmet AND Lico=NEW.Prihod;
END
ELSE
EXCEPTION BadProvodka;
END
-, , . , - :
CREATE TABLE LicaLog(
Data DATE,
Deistvie VARCHAR(3),
UserName VARCHAR(20),
OldName VARCHAR(30),
NewName VARCHAR(30));
, , , , .
:
...
AFTER INSERT
...
INSERT INTO LicaLog(Data, Deistvie, UserName, OldName, NewName)
VALUES(NOW, INS, USER, , NEW.Name);
...
...
AFTER UPDATE
...
INSERT INTO LicaLog(Data, Deistvie, UserName, OldName, NewName)
VALUES(NOW, UPD, USER, OLD.Name, NEW.Name);
...
...
AFTER DELETE
...
INSERT INTO LicaLog(Data, Deistvie, UserName, OldName, NewName)
VALUES(NOW, DEL, USER, OLD.Name, );
...
. , .
-. , ( 1, 3):
CREATE TRIGGER BI_Lica FOR Lica ACTIVE BEFORE INSERT AS
BEGIN
IF (NEW.Tip=1 OR NEW.Tip=3) THEN NEW.Podr=NULL;
END
3.3.6. SELECT
(, , ) , , . , .
SELECT :
GROUP BY _1[, _2...]
. GROUP BY .
, :
SELECT L.Name, SUM(N.Kolvo*P.Cena)
FROM Nalichie N, Lica L, Predmety P
WHERE L.NLic=N.Lico AND P.NPredm=N.Predmet
GROUP BY L.Name;
, :
SELECT P.Name, V.Data, COUNT(V.*)
FROM Provodki V, Predmety P
WHERE P.NPredm=V.Predmet
GROUP BY P.Name, V.Data;
. SELECT (.. ) , GROUP BY.
, , , GROUP BY :
HAVING <_>
_ , WHERE, : , WHERE, .
, , 1000 ., , :
SELECT L.Name, COUNT(N.*)
FROM Nalichie N, Lica L, Predmety P
WHERE L.NLic=N.Lico AND P.NPredm=N.Predmet
AND N.Kolvo>0
GROUP BY L.Name HAVING SUM(N.Kolvo*P.Cena)<1000;
. HAVING, SELECT , GROUP BY, .
WHERE
, :
<> [NOT] BETWEEN <_1> AND <_2>
, 1000 5000 :
SELECT Name, Cena FROM Predmety
WHERE Cena BETWEEN 1000 AND 5000;
:
<> [NOT] IN (<_1>[, <_2>...])
, 1, 2 3 2012.:
SELECT * FROM Provodki
WHERE Data IN (1-DEC-2012, 2-DEC-2012, 3-DEC-2012);
, :
<> [NOT] STARTING [WITH] <>
:
<> [NOT] CONTAINING <>
UPPER. , : , , ,...:
SELECT Name FROM Lica WHERE UPPER(Name) STARTING ͒;
SELECT . , :
SELECT Name, Cena FROM Predmety
WHERE Cena=(SELECT MAX(Cena) FROM Predmety);
SELECT, WHERE .
. , , ( ). , , IN. , ( , ):
SELECT * FROM Provodki WHERE Predmet IN (
SELECT NPredm FROM Predmety WHERE Cena=(
SELECT MAX(Cena) FROM Predmety));
, , . :
[NOT] EXISTS (<>)
, , ( 2):
SELECT Name FROM Lica L WHERE Tip=2 AND NOT EXISTS(
SELECT * FROM Nalichie N WHERE N.Lico=L.NLic);
, , :
SINGULAR(<>)
, , :
SELECT Name FROM Lica L WHERE Tip=2 AND SINGULAR(
SELECT * FROM Nalichie N WHERE N.Lico=L.NLic);
SELECT , :
<> <> {ALL|SOME|ANY} (<>)
ALL , , , , . SOME (ANY ) , .
, , , :
SELECT Name FROM Lica L WHERE 1000 > ALL(
SELECT P.Cena FROM Predmety P, Nalichie N
WHERE N.Lico=L.NLic AND P.NPredm=N.Predmet);
,
