Информатика 1 курс 1 семестр / informatika_uchebnik_Makarova

: , ,

– .

,

, ,

.

2. .

:

,

,

.

:

ALTER TABLE

ADD {[COLUMN] [( )} [CONSTRAINT ]|

CONSTRAINT }

DROP {[COLUMN] i CONSTRAINT } }

ADD , DROP – ,

CONSTRAINT .

19.61.

ALTER TABLE ADD COLUMN [ ] (5)

:

CREATE [ UNIQUE ] INDEX

ON ( [,...])

[WITH { PRIMARY | DISALLOW NULL | IGNORE NULL }]

WITH , : DISALLOW NULL – ; IGNORE NULL – , .

19.62.

CREATE INDEX ON ([ ]) WITH DISALLOW NULL

3. . ( , )

:

DROP TABLE

( ) : DROP INDEX ON

19.63.

DROP INDEX ON

DROP TABLE

4.. :

INSERT INTO [( 1[, 2[,...]])]

VALUES 1[, 2[,...]);

621

, , ,

.

19.64.

INSERT INTO ([ ], [ ], [ ]) VALUES (" ", " ", 23/3/80)

( ), ( )

:

INSERT INTO [IN ]

SELECT [ .] ![, 2[,...]

FROM

WHERE

SELECT,

. SELECT -

, FROM

, , WHERE –

.

, .

( ) ,

, , , ).

19.65.

INSERT INTO SELECT [ ].* FROM [ -

]

[ ] .

. .

19.66.

INSERT INTO SELECT [ ].* FROM [ -

] WHERE [ ] >= #01/01/80#

[ ] ,

.

5. . INNER JOIN

FROM. ,

: ,

:

FROM 1 INNER JOIN 2 ON 1. 1 = 2. 2

(no e1; 2).

,

.

:

LEFT JOIN ( ) – " "

" " , ;

RIGHT JOIN ( ) – " "

" " , .

19.67.

SELECT .*, .* FROM INNER JOIN

622

ON .[ ] = .[ ];

SELECT .*, .* FROM LEFT JOIN

ON . [ ] = . [ ];

SELECT .*, .* FROM RIGHT JOIN

ON . [ ] = . [ ];

[ ].

, ( ) .

. – ,

.

JOIN .

19.68.

SELECT ., . . . [ ] FROM ( INNER JOIN ( INNER JOIN

( ON . [ ] =

. [ ])

ON .[ ]= . [ ])

[

]. , ,

.

].

, – ,

.

6. . ,

.

:

DELETE [ .*] FROM WHERE

:

DELETE * FROM

19.69.

DELETE * FROM

.

DELETE * FROM WHERE [ ]>#1.1.81#

, ]

.

, :

:

DELETE .* FROM INNER JOIN ._

ON , [ N = ._ .[ ] WHERE

19.70.

DELETE .* From INner JoIN [ ]

623

ON .[ ]= [ ]. [ ]

,

].

. Microsoft ACCESS

, .

7. ( ) .

, :

UPDATE SET WHERE

19.71.

UPDATE SET [ ] = "1212"

WHERE [ ] LIKE ' *' AND [ ] < = #01/01/81#

,

, 1212.

UPDATE INner JoIN [ ] ON . [ ]=

]. [ ] SET [ ]= [ ]&" "

,

], .

SQL

SELECT

SELECT -

.

, ,

, . ( . 19.9).

1 9 . 9 . SELECT

	:
	ALL – ;
	DISTINCT	–	, ;
	DISTINCTROW – ; –
	,
	ORDER BY
	,
1, 2	,				(
	)
1,
2
FROM		,
	( )
	–
[WHERE... ]	( )

624

[GROUP BY... ]		( – 10) ,
			;
				( ,			.) –
[HAVING... ]		(
		GROUP BY...) –
[ORDER BY... ]		,
		;
		. (ASC),
		(DESC)
[WITH	OWNERACCESS
OPTION]		, ,

, ,

. ,

, , ( ),

–

.

SELECT :

SELECT ] { * | .* | [ .] 1 [, .] 2.[,...]]} [AS 1 [, 2 [,...]]]

FROM [,...] [IN ]

[WHERE... ] [GROUP BY... ] [HAVING... ] [ORDER BY...]

[WITH OWNERACCESS OPTION]

SELECT .

– ,

.

SELECT . SELECT

, , .

:

;

;

;

;

SQL.

19.72.

SELECT [ ], [ ] FROM

SELECT TOP 5 [ ] FROM

SELECT TOP 5 [ ] FROM ORDER BY [ ]

.

, , .

5 , - 5

, .

, FROM,

. ( ): [ ].[ ]

].[ ] – .

AS.

19.73.

625

,

– . ,

] , , –

.

AS ( . ).

			WHERE	, ,
( . 19.10).
			1 9 . 1 0 .
	AND				Imp		Or
		(
		)						Or)
	Eqv				Not		r
								Or)

, : LIKE –

; BETWEEN...AND – ; IN –

; IS – Null

).

" " , . " " ,

.

19.74.

, 1.1.79.

, 1212 1213 1.1.79.

, ,

. ,

, , 1.1.79.

SQL

:

Avg – ,

;

Count – ;

Min, Max –

626

;

StDev, StDevPs -

;

Sum – ;

Var, VarPs –

.

GROUP BY.

HAVING .

19.75.

SELECT , vg( ) AS FROM

GROUP BY [ ]

SELECT [ ], vg( ) AS

FROM GROUP BY [ ]

, – .

19.76.

SELECT , Avg( ) AS FROM

GROUP BY [ ] HAVING vg( ) > 4.5 SELECT [ ], Avg( ) AS

FROM GROUP BY [ ] HAVINGvg( ) < 4

, , 4.5.

, 4.

SELECT SELECT, SELECT...INTO, INSERT...INTO, DELETE UPDATE. ,

.

SELECT

WHERE HAVING.

:

(ANY | ALL | SOME) ( );

[NOT] IN ( );

[NOT] EXISTS ( ).

– .

:

ANY – ( ). ALL – ( ).

SOME – ( ).

19.77.

SELECT* FROM WHERE [ ] > ANY (SELECT [ ] FROM

WHERE .[ ]="123124")

, ( )

123124.

– , ,

.

627

19.78.

SELECT * FROM WHERE [ ] IN

(SELECT [ ] FROM WHERE [ ] > =4) SELECT * FROM WHERE [ ] NOT IN (SELECT [ ] FROM )

, 4 .

, .

– SELECT, , EXISTS (

NOT) ,

.

19.79.

SELECT* FROM WHERE EXISTS

(SELECT * FROM WHERE .[ ]=

.[ ])

, .

	:
"
( )
	Microsoft Office
	( )
VBA
	:

dBASE QBE SQL

VBA (Visual Basic for Applications)

1.?

628

2.?

3.?

4.?

5.?

6.?

7.?

8.?

9.Microsoft Office.

10.Microsoft Office.

11.Microsoft Office.

12.Excel 5.0?

13.Word 6.0?

14.Excel 5.0?

15.?

16.Word 6.0?

17.Excel 5.0?

18." " ?

19." " Word 6.0?

20." " Excel 5.0?

21., .

22.?

23.?

24.?

25.Word 6.0?

26.Excel 5.0?

27.Word 6.0?

28.Excel 5.0?

29.Excel 5.0?

30.VBA.

31.VBA, .

32., , VBA.

33., ?

34.VBA.

35.VBA.

36.VBA.

37.VBA?

38.VBA.

39.?

40.?

41.VBA?

42..

43.?

44.?

45..

46.( )?

47.?

48..

49..

50..

51.dBASE- ?

52.QBE.

53.SQL?

54.SQL.

55.SELECT?

56.?

629

57.?

58.? ?

1.., . . – 2- ., . - .:

, 1995.

2.. Microsoft Office 4 ( " !") / . . - .: , 1996.

3. . Visual Basic Microsoft Office: . . - .:

, 1995.

4. . Visual Basic for Applications / . . - .: , 1996.

ABM	–
	–
AOH	-
	–
	–
	–
	–
	–
	–
	–
	–
	-
	–
	–
	–
	–
	–
	–
	– GFLOPS
	–
	-
	–
	-
	–
	-
	-
	– KOPS
	-
	-
	-
	-
	–
	-
	-
	– MIPS
	-
	- ( ) ( )
	- ( ) ( )
	-
	-
	-
	-
	-

630