ERwin - (Forward Engineering). ERwin , . Tools/Forward Engineering. - . Options - . - , -. Summary , Options.
Generate . . Generate Database Schema, .
. SQL. , , , -, . , , .
ERwin Report Browser. , , , -. , .
, .
Edit ERwin Report .
, , . - Export result set. :
CSV ;
HTML;
DDE MS Word MS Excel;
RPTwin .
31. . , , .
, . , . , . , , , . . , , ACID:
Ÿ (Atomicity) ;
Ÿ (Consistency) ;
|
|
|
Ÿ (Isolation) , , ;
Ÿ (Durability) .
() ( ). . , . : , . , , . , . , . :
Ÿ ;
Ÿ ;
Ÿ , , .
. , . .
32. SQL: . . . .
, SQL ( Structured Query Language) , . . , , . SQL , , . ANSI (, CHAR, VARCHAR) (INT, DEC) . . , . , . , .
::= ;... ; ::= ...
::= _ ...
SQL . Select. , , , . :
Select [ Distinct ] < > From <> [ Where < > ];
Distinct. , , (<, <=, =, <>, >=, >), between and (, ) (and, or, not). , . , . . in. . % , _ , like .
|
|
|
33. SQL: . , . .
() . :
Ÿ COUNT , null, ;
Ÿ SUM ;
Ÿ AVG ;
Ÿ MAX ;
Ÿ MIN .
Select . , , SUM AVG .. COUNT(*) , , . Distinct . All , .
Select COUNT(Distinct _) From ;
Select COUNT(All ) From ;
Select COUNT(*) From ;
Select SUM(/1000) From ;
Group By. . , .
Group By , . , .. - , , , . , Where: Where > . . Having.
Select , Sum() From Group By Having Sum()>5000;
34. SQL: , , . .
Select < > From r, s Where A 1 = B 1 and A 2 = B 2 and and Am = Bm;
|
- . . - , . , , , , , . . .
Select _, _, . From , Where .=. ;
, , . , , , . SQL , , . , , . . :
|
|
|
Select < > From r 1, s 2
Select < > From r As 1, s As 2
1 2 r s .
Select a._, b._, a. From a, b
Where a.=b.; and a._ < b._;
35. SQL: . .
SQL , . , , . , . , . , , , . .
Select * From Where _ = (Select _ From Where _ = ); // , . , .
Select From Where _ in (Select _ From Where _ in (Select _ From Where =));
36. SQL: . .
. .
Select * From a Where 03.10 in (Select From b Where a._=b._);
. : , . , , . , .
1. , , -.
2. , From.
3. . - .
4. .
5. .
37. SQL: , . .
SQL , : , (Exists), , (All) , , (Any, Some). Exists , () , . , . Exists .
, :
Select * From Where = and Exists (Select * From Where _ in (Select _ From Where =));
All All <> θ(<>), θ . , , θ. , : . , , . , in. , ..
|
|
|
, :
Select * From Where > All (Select From
Where =);
Any ( Some) Any <>θ(<>), θ . , , θ. , , ..
, , :
Select * From Where = Any (Select From );
All , Any Exists . null Any All , Exists .
38. SQL: . .
Union, . , , . , Select. , Union All.
, 200 3000:
Select _ From Where >200
Union Select _ From Where >3000 Order By _;
, :
(Select < 1> Union [All] Select < 2>)
Union [All]
(Select < 3> Union [All] Select < 4>)
39. SQL: .
, , :
Insert:
Insert Into <> Values (<1>, <2>, <N>);
( ):
Insert Into <> (<1>, <2>, <k>) Values (<1>, <2>, <k>);
.
.
.
.
() () .
.
<i> .
Delete.
Delete From <>;
Delete From <> Where <>;
Update
Update <> Set <1>=<1>, <2>=<2>, <k>=<k>;
Update <> Set <1>=<1>, <2>=<2>, <k>=<k> Where <>;
, , 0,1:
Update Set =+0.1 Where 3<(Select COUNT(Distinct _) From Where ._=._);
40. SQL: , , . .
( [] ):
Create Table <>
(<1> <1> [<(1)>] [<1>], <2> <2> [<(2)>] [<2>],... <N> <N> [<(N)>] [<N>]
[, Primary Key (< >)];
[, Foreign Key (< >) References <2> < >]);
: (not null), (unique), , (primary key) (references <2>(< >)), (check (<>)), (default =<>).
| (_ | Numeric(2) | primary key, |
| _ | Char(40) | not null, |
| Char(20) | default=, | |
| Numeric(4,2) | check(<1)); |
Create Table
|
|
|
Alter Table , , . :
Alter Table <> Add <> <> [<()>] [<>];
, , . :
Drop Table <>;
SQL . :
Create Index < > On <> (<1>, <2>, <k>);
, Create Index Create Unique Index.
Drop Index < >;
41. . . .
:
.
.
. . . , . , . , . , . , , . , (, ), .
. . (, ) , . , . . () , . . , , , , , , , . .
42. . - - .
, . , ( ), . , . . . ( ) , . , , , . , . -. ( ), . , , . , , , . . .
- . - , , . , , ( ) . , . , , , . . , , . , . . . : , - . : -, , - . : , , . , , . . . , , , , , . , . , .
. , , , . .
- . , . . . () , . , . , . . - , , . , , - . , . , , . , . , , . , , . , , , . . , , .
, . , ( , ). .
43. , . ().
. , , . , . , - . , , , , , .. , . , ( ) . . . , . , , . , - (, -) , . , . , ( ) ( ). , , , . , . , .
, . , , . N , N - , . , , . . , , . , , , , . , . , , , , . : , . , , . , . , . , , . . .
, 0,5 ( ). . , ( ).
44. , ().
. (-), , , , . , . . . , . , , . , , . . , - , , . , , . . , . , , . , . , . , . , -, . .
. M , k 𝝐 K K, a , h(k) -, : a=h(k), K [0, M -1]. , M . , k1 k2, k1≠k2 h(k1) = h(k2). , , , k1 k2 .
, -, , . . , .
:
