© Copyright , 1991-1995
moshkow@ipsun.ras.ru
SQL
Informix (Informix-SQL, Informix-4GL,
Informix-ESQL/C .).
SQL,
.
SQL Informix-4GL.
, 4GL SQL
( ) .
SQL .
, ,
. ( ;)
. .
: SQL
4GL INFORMIX-SQL,
(;) .
SQL ,
( SQL
4GL).
,
. .
{ },
-- ( ) .
( ) - , ,
, (_), -
(_). INFORMIX-4GL -
. i_Un1023Tt I_UN1023TT - .
10.
SQL - , ,
view() , - 18.
SQL 4 :
- :
CREATE, DROP, ALTER .
- :
INSERT, DELETE, SELECT, UPDATE .
- :
GRANT / REVOKE , LOCK / UNLOCK , SET LOCK MODE
- , .
, .
(-
), .
SQL :
(database);
(table);
(column);
(index);
(view);
(synonym).
- .
- .. ,
. -
(owner-name) : moshkow.table1
. "
4GL" (. ), "
Informix-4GL".
.
---------------------------------------------------------------
CREATE DATABASE zawod
---------------------------------------------------------------
-
- (CURRENT) - . DATABASE
,
. CLOSE DATABASE -
.
---------------------------------------------------------------
DATABASE zawod
. . . # zawod
DATABASE stanciq
. . . # stanciq
CLOSE DATABASE
#
---------------------------------------------------------------
kadry ceh, .
---------------------------------------------------------------
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) )
---------------------------------------------------------------
, -
, .
---------------------------------------------------------------
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))
---------------------------------------------------------------
-
. ,
, ,
ALTER "" ,
.
View - "" , .
---------------------------------------------------------------
CREATE VIEW poor AS SELECT tabnom, fio, datarovd FROM kadry
WHERE zarplata < 120
# view - ""
# kadry, zarplata 120 .
---------------------------------------------------------------
, ,
, ,
, view .
- ,
.
---------------------------------------------------------------
CREATE UNIQUE INDEX indkdtb ON kadry (tabnom)
# tabnom kadry.
# ,
# .
---------------------------------------------------------------
-
. SELECT .
---------------------------------------------------------------
ALTER INDEX indkdtb TO CLUSTER
---------------------------------------------------------------
.
SQL -
, , . -
( )
(owner-name) - , -
(CREATE) .
---------------------------------------------------------------
kadry.nomerceh # nomerceh kadry
ceh.nomerceh # nomerceh ceh
iwanow.table1.c1 # c1 table1, -
# iwanow
moshkow.table1.c1 # c1 (!) -
# table1, -
# moshkow
---------------------------------------------------------------
.
---------------------------------------------------------------
CREATE SYNONYM t1 FOR petrow.sostoqnie_postow
---------------------------------------------------------------
( )
petrow.sostoqnie_postow t1.
(logfile).
---------------------------------------------------------------
START DATABASE zawod WITH LOG IN "/udd/moshkow/logfile/zawod"
---------------------------------------------------------------
, -
audit trail.
---------------------------------------------------------------
CREATE AUDIT FOR kadry IN "/udd/moshkow/kadry.audit"
---------------------------------------------------------------
,
. ,
, ,
, .
---------------------------------------------------------------
DROP VIEW poor # view. -
# ,
# .
DROP TABLE kadry # .
DROP INDEX indkdtb
DROP SYNONYM t1
DROP DATABASE zawod #
#
---------------------------------------------------------------
-
, ( DBA ),
, (-
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
---------------------------------------------------------------
DBA ( , , )
DBA.
-
( ).
,
. -
.
---------------------------------------------------------------
BEGIN WORK
LOCK TABLE kadry
. . .
UNLOCK TABLE kadry
. . .
LOCK TABLE kadry EXCLUSIVE
---------------------------------------------------------------
, "". -
"
".
---------------------------------------------------------------
SET LOCK MODE TO WAIT
---------------------------------------------------------------
, -
.
,
.
"". -
.
.
---------------------------------------------------------------
START DATABASE zawod WITH LOG IN "/ARM/log/zawod"
---------------------------------------------------------------
( - )
:
---------------------------------------------------------------
UNIX
1. .
2. (,
) cpio INFORMIX dbimport.
3. INFORMIX :
DATABASE kadry EXCLUSIVE #
ROLLFORWARD DATABASE kadry #
#
CLOSE DATABASE #
---------------------------------------------------------------
---------------------------------------------------------------
BEGIN WORK #
. . . #
IF THEN COMMIT WORK
ELSE ROLLBACK WORK
END IF
---------------------------------------------------------------
"" INFORMIX -
.
. (SELECT)
( ), (DELETE)
, (INSERT) , (UPDATE)
.
kadry , -
4, ""
---------------------------------------------------------------
DELETE FROM kadry WHERE ceh=4 AND fio MATCHES "*"
---------------------------------------------------------------
4-
"", "", "" .
, INFORMIX
.
kadry, -
moshkow,
---------------------------------------------------------------
DELETE FROM moshkow.kadry
---------------------------------------------------------------
kadry ,
tabnum=345 . .
ceh, .
---------------------------------------------------------------
SELECT fio, dolvn, zarplata FROM kadry WHERE tabnom=345
SELECT * FROM ceh
SELECT kadry.fio, ceh.nameceh WHERE kadry.nomerceh=ceh.nomerceh
---------------------------------------------------------------
,
, , ceh.
,
INSERT INTO ... VALUES,
, SELECT .
---------------------------------------------------------------
INSERT INTO kadry VALUES (4,0,"",num,"10/25/1939",NULL)
INSERT INTO customer VALUES (ps_customer.*)
# ps_customer - RECORD -
# .
# ps_customer customer
INSERT INTO kadry (tabnom, fio, nomerceh, dolvnostx)
SELECT 0 , fio, 4, dolvnostx FROM kadryold
WHERE nomerceh=3 AND fio IS NOT NULL
#
---------------------------------------------------------------
, SERI-
AL ,
0.
( ),
NULL.
DELETE, UPDATE, SELECT WHERE
, ,
( ,
). WHERE .
, , WHERE -
.
---------------------------------------------------------------
UPDATE kadry SET fio="" WHERE fio=""
UPDATE ceh SET kod_ceha[1,4]=nameceh[5,8] WHERE
nomerceh BETWEEN 3 AND 5 OR nameceh IN ("","")
---------------------------------------------------------------
ceh 3,4,5 -
-
nameceh .
WHERE
DELETE, UPDATE, SELECT, ,
(, , -
).
WHERE.
WHERE , -
AND, OR NOT
:
1 < 2,
1 >= 2, ..,
:
column-name IS [NOT] NULL
[NOT] BETWEEN 1 AND 2
[NOT] IN (1 , ... [, ...] )
,
, .
- LIKE MATCHES.
- MATCHES ""
- LIKE ""
LIKE .
: (%) , (_)
.
. % _
, ESC
( (\)).
tab8 , -
string1 "+" -
- "". :
---------------------------------------------------------------
SELECT * FROM tab8 WHERE string1 LIKE "%+%_"
---------------------------------------------------------------
MATCHES : *,?,[,],^,-.
*
?
[...]
(-), (^)
[abH] - a, b, H
[^d-z] - , d,e,f,g, ... ,y,z
\ *,?,[,]
,
escape-char. escape-char="\", \?
?, \* *, \\
\ . (")
("").
G L, c.
(, ,
, .)
---------------------------------------------------------------
SELECT * FROM customer WHERE company MATCES"?[^G-L]c*"
---------------------------------------------------------------
.
---------------------------------------------------------------
SELECT * FROM customer
WHERE company MATCHES "*?*" ESCAPE""
---------------------------------------------------------------
ESC- "" -
"?"
. SELECT
{ALL | [ANY | SOME]} (SELECT-statement)
. ,
SELECT .
[NOT] IN (SELECT-statement)
. , - SELECT .
[NOT] EXISTS (SELECT-statement)
.
---------------------------------------------------------------
SELECT fio FROM kadry WHERE zarplata=
(SELECT MAX(zarplata) FROM kadry )
---------------------------------------------------------------
-
. SELECT
.
---------------------------------------------------------------
SELECT fio, shifr, organizaciq FROM zaqwki WHERE denxgi_rek is
not NULL and
gorod in (SELECT gorod FROM regiony WHERE region="")
---------------------------------------------------------------
, -
.
---------------------------------------------------------------
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)
---------------------------------------------------------------
( )
, -
.
, NOT, AND, OR.
UNLOAD
. ,
"|".
---------------------------------------------------------------
UNLOAD TO "kadry19.unl" SELECT * FROM kadry
---------------------------------------------------------------
kadry19.unl :
---------------------------------------------------------------
5|5|||100.0|31.12.1946|
4|6|||80.0||
. . .
---------------------------------------------------------------
LOAD -
. ,
.
---------------------------------------------------------------
LOAD FROM "kadry20.unl" INSERT INTO kadry
---------------------------------------------------------------
( WHERE) kadry,
( *),
( UNIQUE) -
(INTO TEMP) x,
, kadry.
---------------------------------------------------------------
SELECT UNIQUE * FROM kadry INTO TEMP x
---------------------------------------------------------------
. -
. -
, tab1 6 tab2 - 7 . -
- ,
7*6=42 .
---------------------------------------------------------------
SELECT tab1.a-tab2.b, tab1.a, tab2.b FROM tab1, tab2
---------------------------------------------------------------
,
. : -
(INTO TEMP) ,
( -
), ("" -
), (INTO)
( ).
()
()
---------------------------------------------------------------
SELECT a,b,c,d+e FROM tabl ORDER BY b,c
SELECT a,b,c,d+e FROM tabl ORDER BY 2,3
---------------------------------------------------------------
ORDER BY
(select-list). -
.
: ( lname
, ,
(@)
---------------------------------------------------------------
SELECT customer_num, @lname,city INTO cnum,lname,town
FROM customer
---------------------------------------------------------------
COUNT(*)
- , MAX(column) MIN(column) - -
, SUM(column) -
, AVG(column) - .
num orders,
customer_num 101:
---------------------------------------------------------------
SELECT COUNT(*) INTO num
FROM orders WHERE customer_num=101
---------------------------------------------------------------
.
300 ( zarplata -
), dolvnost
.
---------------------------------------------------------------
SELECT AVG (zarplata) FROM table1,table2
WHERE table1.dolvnost=table2.dolvnost and zarplata>300
---------------------------------------------------------------
"" ()
.
, WHERE
.
---------------------------------------------------------------
SELECT dolvnostx, COUNT(*), AVG(zarplata) FROM kadry
GROUP BY dolvnostx
---------------------------------------------------------------
.
---------------------------------------------------------------
SELECT dolvnostx, COUNT(*), AVG(zarplata) FROM kadry GROUP BY 1
---------------------------------------------------------------
.
HAVING
.
---------------------------------------------------------------
SELECT order_num, AVG(total_priece) FROM items
GROUP BY order_num HAVING COUNT(*) > 2
---------------------------------------------------------------
total_price ,
.
, (
OUTER)
, WHERE
. ,
,
. :
---------------------------------------------------------------
SELECT company, order_num
FROM customer c, OUTER orders o
WHERE c.customer_num=o.customer_num
---------------------------------------------------------------
,
. ,
, -
NULL. ( OUTER,
.)
- SQL.
kadry.
---------------------------------------------------------------
select unique * from kadry into temp kd
delete from kadry where 1=1
insert into kadry select * from kd
drop table kd
---------------------------------------------------------------
T b (kl int, pole char(20));
kl
kadry kadry.dolvnostx b.pole
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
---------------------------------------------------------------
UPDATE, :
---------------------------------------------------------------
UPDATE kadry SET
dolvnostx=(select pole from b where kadry.tabnom=b.kl)
WHERE tabnom IN (select kl from b)
---------------------------------------------------------------
, :
agent cia
+-----+---------+----+ +-----+ ---------+ ----+
|fio | har | cen| |fio | . . . har | . . . cen|
|John | |$300| |John | | $600|
|Piter| | | . . .
|Bob | |$25 | |Piter| CIA| $45 |
+-----+---------+----+ . . .
|Bob | | $15 |
|Ronny| | |
. . .
+-----+ ---------+ ----+
cia . -
agent,
cia.
, -
agent , 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