SQL   SQL    

SQL

( .., ..)

C. SQL

  2. DATE TIME
  3. FORMAT
  10. D.
  14. E. , SQL

SQL, ANSI ISO ( ), , .

. , , , .

, SQL, B. CHARACTER . , , , , . .

DATE TIME

2, DATE , . , mm/dd/yyyy. IBM . , , , , . , , .

, :

(ISO) yyyy-mm-dd 1990-10-31

(JIS) yyyy-mm-dd 1990-10-31

IBM (EUR) dd.mm.yyyy 10.31.1990

, , . , , , . ; , A < B , A B .

, , , :

(ISO) hh-mm-ss 21.04.37

(JIS) hh-mm-ss 21.04.37

IBM hh-mm-ss 21.04.37

IBM USA (USA) hh.mm AM/PM 9.04 PM

, . , , (CURDATE CURTIME) . USER (), .

? DATE , TIME.

, , TIMESTAMP, .

ANSI , . CHAR. . , , , , ; , .

, . , , UNION. VARCHAR LONG VARCHAR ( LONG). , CHAR , , VARCHAR , , SQL , .

VARCHAR , , . 254 2048 VARCHAR, 16000 LONG. LONG , ; VARCHAR , .

, VARCHAR CHAR. VARCHAR , , , CHAR. , VARCHAR ( ) .

, , , , , CHAR VARCHAR. , LONG . , "" SQL.

.

FORMAT

7, , SQL, . SQL , , FORMAT SQL, , . FORMAT :

FORMAT , , , . FORMAT , FORMAT . FORMAT:

FORMAT NULL '_ _ _ _ _ _ _';
FORMAT BTITLE 'Orders Grouped by Salesperson';
FORMAT EXCLUDE (2, 3);

NULL _ _ _ _ _ _ _ ; 'Orders Grouped by Salesperson' ; . , , ORDER BY, . FORMAT , .

, . SET FORMAT; , , . , FORMAT COLUMN :

COLUMN odate FORMAT dd-mon-yy;

10-Oct-90 , .

COMPUTE, , :

SELECT odate, amt
FROM Orders
WHERE snum = 1001
COMPUTE SUM (amt);

Peel, , .

, COMPUTE . ,

BREAK ON odate;

, odate . :

COMPUTE SUM OF amt ON odate;

ON BREAK.

SQL ANSI, , comm * 100. , , , .

SQL, . SELECT , , , . , . , , SELECT:

.

ABX(X)

X ( )

CEIL(X)

X , .

FLOOR (X)

X , .

GREATEST(X,Y)

.

LEAST(X,Y)

.

MOD(X,Y)

X Y.

POWER(X,Y)

X Y.

ROUND(X,Y)

X Y. Y , .

SING(X)

X < 0, X > 0.

SQRT(X)

X.

, , , .

LEFT(<string>,X)

() X .

RICHT(<string>,X)

X

ASCII(<string>)

ASCII .

CHR(<ASCIIcode>)

ASCII.

VALUE(<string>)

. CHAR VARCHAR, . VALUE('3') 3 INTEGER.

UPPER(<string>)

.

LOWER(<string>)

.

INlTCAP(<string>)

. PROPER.

LENGTH(<string>)

.

<string>||<string>

, . ( || ).

LPAD(<string>,X,'*')

'*', , , X.

RPAD(<string>,X, ")

LPAD, , .

SUBSTR(<string>,X,Y)

Y X.

.

DAY(<date>)

. MONTH (), YEAR (), HOUR (), SECOND () .

WEEKDAY(<date>)

.

.

NVL(<column>,<value>)

NVL (NULL TO VALUE) <value> NULL , <column>. <column> =NULL, NVL .

INTERSECT MINUS

UNION, 14, , . INTERSECT () MINUS (). INTERSECT , , MINUS , , . ,

SELECT *
FROM Salespeople
WHERE city = 'London'

INTERSECT

SELECT *
FROM Salespeople
WHERE 'London' IN (SELECT city
FROM Customers
WHERE Customers.snum = Salespeople.snum);

, , , , , , . ,

SELECT *
FROM Salespeople
WHERE city = 'London'

MINUS

SELECT *
FROM Salespeople
WHERE 'London' IN (SELECT city
FROM Customers
WHERE Customers.snum = Salespeople.snum);

, , , , . MINUS DIFFERENCE ().

14 , , UNION. . , "+" , , , . , , NULL , .

, , , , ( , ):

SELECT a.snum, sname, cname
FROM Salespeople a, Customers b
WHEREa.snum = b.snum(+);

(UNION):

SELECT a.snum, sname, cname
FROM Salespeople a, Customers b
WHERE a.snum = b.snum

UNION

SELECT snum, sname, '_ _ _ _ _ _ _ _ _ _'
FROM Salespeople
WHERE snum NOT IN (SELECT snum
FROM Customers);

, NULL (. FORMAT , NULL ).

SQL , , , . , :

Journaling () Auditing ().

.

Journaling . - , , - . . , , , , , , . , , , , . , , :

SET JOURNAL ON;

Auditing . , , , . , , . , . AUDIT:

AUDIT INSERT ON Salespeople BY Diane;

ON, BY , , , . AUDIT ALL, AUDIT INSERT, Diane .

D.

SQL. , SQL. , SQL; , . ( BNF ):

, (.,..) , , . , , (**) .

: , , ANSI. , .

, ANSI, , -. , <predicate> ANSI <predicate> <search condition>.

SQL

SQL. : .

; SQL , . <separator> ; <separator> . , SQL .

, , . , <separator>, SQL. , . , . . , <predicate>, , <predicate> . , <predicate> AND OR <predicate> .

<predicate> - . .

<separator>

<comment> | <space> | <newline>

<comment>

--<string> <newline>

<space>

<newline>

-

<identifier>

<letter>[{<letter or digit> | <underscore>}... ] : ANSI, , <identifier> 18- .

<underscore>

-

<percent sign>

%

<delimiter>

: , () < > . : = + " - | <> > = < = <string>

<string>

[ ] : <string>, ('') (').

<SQL term>

, . (* *)

SQL :

<query>

SELECT

<subquery>

SELECT , , , - .

<value expression>

<primary> | <primary> <operator> <primary> | <primary> <operator> <value expression>

<operator>

: + - / *

<primary>

<column name> | <literal> | <aggregate function> | <built-in constant> | <nonstandard function>

<literal>

<string> | <mathematical expression>

<built-in constant>

USER | <implementation-defined constant>

<table name>

<identifier>

<column spec>

[<table name> | <alias>]<column name>

<grouping column>

<column spec> | <integer>

<ordering column>

<column spec> | <integer>

<colconstraint>

NOT NULL | UNIQUE | CHECK (<predicate>) | PRIMARY KEY | REFERENCES <table name>[(<column name>)]

<tabconstraint>

UNIQUE (<column list>) | CHECK (<predicate>) | PRIMARY KEY (<column list>) | FOREIGN KEY (<column list>) REFERENCES <table name>[(<column list>)]

<defvalue>

= <value expression>

<data type>

(. B ANSI C .)

<size>

<data type> (. B.)

<cursor name>

<identifier>

<index name>

<identifier>

<synonym>

<identifier> (*nonstandard*)

<owner>

<Authorization ID>

<column list>

<column spec> .,..

<value list>

<value expression> .,..

<table reference>

{ <table name> [<alias>] } .,..

<predicate> :

<predicate> ::= [NOT]{ <comparison predicate> | <in predicate> | <null predicate> | <between predicate> | <like predicate> | <quantified predicate> | <exists predicate> } [AND | OR <predicate>]

<predicate> , , , <exists predicate> <null predicate>, .

, NULL . , NULL .

AND, OR NOT <predicate>. NOT = , NOT = , NOT = . AND OR , :

AND

OR

: , , , . AND, , () () , : AND = .

. . NOT , AND OR. <predicate> .

<comparison predicate> ( )

<value expresslon> <relational op> <value expresslon> | <subquery> <relatlonal op> ::= = | < | > | < | >= | <>

<value expression> = NULL, <comparison predicate> = ; , .

<relational op> ; , . <value expression> . <subquery> , <value expression> SELECT, <value expression> <comparision predicate>, <subquery> .

<between predicate>

<value expression> [NOT] BETWEEN <value expression> AND <value expression>

<between predicate> A BETWEEN B AND C, <predicate> (A >= B AND <= C). <between predicate> A NOT BETWEEN B AND C, NOT (BETWEEN B AND C). <value expression> <subquery> (*nonstandard*).

<in prediicate>

<value expression> [NOT] IN <value list> | <subquery>

<value list> , <value expression> . <subquery>, <value expression> SELECT ( , ANSI). <subquery> , - , , <value list> . , <in predicate> <value expression> <value list>, NOT. A NOT IN (B, C) NOT (A IN (B, C)).

<like predicate>

<charvalue> [NOT] LIKE <pattern> [ESCAPE <escapechar>]

<charvalue> ** <value expression> - . <charvalue> , , <column spec>. <pattern> <string> <charvalue>. <escapechar> - . , :

* <underscore> <pattern> <escapechar>, <charvalue>.

* <percent sign> <pattern> <escapechar>, <charvalue>.

* <escapechar> <pattern> <escapechar>, <charvalue>.

* <pattern>, <charvalue>.

, <like predicate> , NOT. NOT LIKE '' NOT (A LIKE '').

<null predicate>

<column spec> IS [NOT] NULL

<column spec> = IS NULL, NULL . <null predicate> NULL. <column spec> IS NOT NULL , NOT(<column spec> IS NULL).

<quantified predicate>

<value expression> <relational op> <quantifier> <subquery> <quantifier> ::= ANY | ALL | SOME

SELECT <subquery> <value expression>. <subquery> <result set>. <value expression> , <relational operator>, <result set>. :

<exists predicate>

:

EXISTS (<subquery>)

<subquery> , <exists predicate> ; , .

SQL

SQL. , .

: , EXEC SQL, <SQL term> SQL.

BEGIN DECLARE SECTION

( )

EXEC SQL BEGIN DECLARE SECTION <SQL term>
<host-language variable declarations>
EXEC SQL END DECLARE SECTION<SQL term>

, SQL. SQLCODE .

CLOSE CURSOR

( )

EXEC SQL CLOSE CURSOR <cursor name> <SQL term>;

, .

COMMIT (WORK)

( ())

COMMIT WORK;

, , , , .

CREATE INDEX (*NONSTANDARD*)

( ) (**)

CREATE [UNIQUE] INDEX <Index name> ON <table name> (<column list>);

. UNIQUE , () .

CREATE SYNONYM (*NONSTANDARD*)

( ) (**)

CREATE IPUBLICl SYNONYM <synonym> FOR <owner>.<table name>;

() . , , . , ( ) . PUBLIC , SYSTEM .

CREATE TABLE

( )

CREATE TABLE <table name> ({<column name> <data type>[<size>] [<colconstralnt> ...] [<defvalue>]} .,.. <tabconstraint> .,..);

. . .

<data type> . <data type> B; <data type>, C. <size> <data type>.

<colconstraint> <tabconstraint> , .

<defvalue> ( ) , . (. 17 CREATE TABLE 18 19 <defvalue>).

CREATE VIEW

( )

CREATE VIEW <table name> AS <query> [WITH CHECK OPTION];

SQL. <table name>, <query> , .

, , , <query>. WITH CHECK OPTION, <predicate> <query>.

DECLARE CURSOR

( )

EXEC SQL DECLARE <cursor name> CURSOR FOR <query><SQL term>

<cursor name>, <query>. (. OPEN CURSOR), <query> , ( FETCH) . , <query>, (. 25 ).

DELETE

()

DELETE FROM <table name> {[WHERE <predicate>];} | WHERE CURRENT OF <cursor name><SQL term>

WHERE , . WHERE <predicate>, , <predicate> . WHERE CURRENT OF () <cursor name>, <table name> <cursor name> . WHERE CURRENT SQL, .

EXEC SQL

( SQL)

EXEC SQL <embedded SQL command> <SQL term>

EXEC SQL , SQL, .

FETCH

()

EXEC SQL FETCH <cursorname> INTO <host-varlable llst><SQL term>

FETCH <query>, <host-variable list>, . <host-variable list> indicator (. 25.)

GRANT

( )

()

GRANT ALL [PRIVILEGES] | {SELECT | INSERT | DELETE | UPDATE [(<column llst>)] | REFERENCES [(<column llst>)l } .,.. ON <table name> .,.. TO PUBLIC | <Authorization ID> .,.. [WITH GRANT OPTION];

ALL (), PRIVILEGES (), . PUBLIC () .

. REFERENCES <column list> . , . UPDATE REFERENCES . GRANT OPTION .

()

GRANT DBA | RESOURCE | CONNECT .... TO <Authorization ID> .,.. [IDENTIFIED BY <password>]

CONNECT .

RESOURCE . DBA .

IDENTIFIED BY CONNECT, .

INSERT

()

INSERT INTO <table name> (<column llst>) VALUES (<value llst>) | <query>;

INSERT <table name>. VALUES , <table name>. <query> , <table name>. <column list> , <table name>, .

OPEN CURSOR

( )

EXEC SQL OPEN CURSOR <cursorname> <SQL term>

OPEN CURSOR , <cursor name>. FETCH.

REVOKE (*NONSTANDARD*)

( ) ()

REVOKE {ALL [PRIVILEGES] | <privilege> .,..} [ON <table name>] FROM { PUBLIC | <Authorization ID> .,..};

<privelege> GRANT. , REVOKE, , , GRANT. ON , .

ROLLBACK (WORK)

() ()

ROLLBACK WORK;

, . , , .

SELECT

()

SELECT { IDISTINCT | ALL] < value expression > . , . . } / *
[INTO <host variable list> (*embedded only*)]
FROM < table reference > . , . .
[WHERE <predicate>]
[GROUP BY <grouping column> . , . .]
[HAVING <predicate>]
[ORDER BY <ordering column> [ASC | DESC] . , . . ];

(. 3 14). :

- , <predicate> WHERE , , . GROUP BY , <value expression> -, , . GROUP BY , - , . <predicate> , <value expression> - . DISTINCT, () .

UNION

()

<query> {UNION [ALL] <query> } . . . ;

<query> . <query> <value expression> SELECT 1..n , <data type> <size> 1..n .

UPDATE

()

UPDATE <table name>
SET { <column name> = <value expression> } .,..
{[ WHERE <predlcate>]; } | {[WHERE CURRENT OF <cursorname>] <SQL term>]}

UPDATE <column name> <value expression>. WHERE <predicate>, <predicate> , . WHERE CURRENT OF, <table name> <cursor name> . WHERE CURRENT OF SQL, . WHERE .

WHENEVER

( )

EXEC SQL WHENEVER <SQLcond> <actlon> <SQL term>
<SQLcond> ::= SQLERROR | NOT FOUND | SQLWARNING
( )
<action> ::= CONTINUE | GOTO <target> | GOTO <target>
<target> ::=

E. , SQL

1:

snum

sname

city

comm

1001

Peel

London

.12

1002

Serres

San Jose

.13

1004

Motika

London

.11

1007

Rifkin

Barcelona

.15

1003

Axelrod

New York

.10

2:

cnum

cname

city

rating

snum

2001

Hoffman

London

100

1001

2002

Giovanni

Rome

200

1003

2003

Liu

San Jose

200

1002

2004

Grass

Berlin

300

1002

2006

Clemens

London

100

1001

2008

Cisneros

San Jose

300

1007

2007

Pereira

Rome

100

1004

3:

onum

amt

odate

cnum

snum

3001

18.69

10/03/1990

2008

1007

3003

767.19

10/03/1990

2001

1001

3002

1900.10

10/03/1990

2007

1004

3005

5160.45

10/03/1990

2003

1002

3006

1098.16

10/03/1990

2008

1007

3009

1713.23

10/04/1990

2002

1003

3007

75.75

10/04/1990

2004

1002

3008

4723.00

10/05/1990

2006

1001

3010

1309.95

10/06/1990

2004

1002

3011

9891.88

10/06/1990

2006

1001

  SQL   SQL    

, , Statechart diagram - - , .




 10.11.2021 - 12:37: - Personalias -> WHO IS WHO - - _.
10.11.2021 - 12:36: - Conscience -> . ? - _.
10.11.2021 - 12:36: , , - Upbringing, Inlightening, Education -> ... - _.
10.11.2021 - 12:35: - Ecology -> - _.
10.11.2021 - 12:34: , - War, Politics and Science -> - _.
10.11.2021 - 12:34: , - War, Politics and Science -> . - _.
10.11.2021 - 12:34: , , - Upbringing, Inlightening, Education -> , - _.
10.11.2021 - 09:18: - New Technologies -> , 5G- - _.
10.11.2021 - 09:18: - Ecology -> - _.
10.11.2021 - 09:16: - Ecology -> - _.
10.11.2021 - 09:15: , , - Upbringing, Inlightening, Education -> - _.
10.11.2021 - 09:13: , , - Upbringing, Inlightening, Education -> - _.
Bourabai Research - XXI Bourabai Research Institution