() . , , ER-. - .
:
1. .
2. .
3. . , , .
4. .
5. , .. .
() , , . , , .
, , : , , .
:
1 ; 2 ;
3 ; 4 .
- -
. , .
, () , .
- () , . , .
() . , . , , . , , .
|
|
( . Databasescheme) - , , (). , , .
. , .
- .
.
, .
, :
1) ; 2) , ;
3) NULL-.
, , . .
, , .
- : .
, , , : , .
, .
:
- ; - ;
- ; - .
.
, , .
, .
, .
, , .
, . , .
. , .
, .
: * ;
* .
, .
, " ", .
|
|
, . .
X Y , , Y X, X Y. X -> Y.
, X Y , , . , " ", .
X -> Y X ( ).
.
- , , , .
- . 1NF, 2NF, 3NF, BCNF
1NF, .
, , 1NF.
2NF, 1NF .
3NF, 2NF .
BCNF, 3NF .
(. denormalization) , , .
, , ( , ). .
. . , , .
, , , , . .
.
:
:
()
- . (QBE)
- , , - . , - . , , . . , , , , , , , .
|
|
QBE - . , .
- SQL,
SQL : , , . , SQL () - , . SQL/PSM , .SQL . SQL :
* (. DataDefinitionLanguage, DDL)
* (. DataManipulationLanguage, DML)
* (. DataControlLanguage, DCL)
* (. TransactionControlLanguage, TCL)
, . DML. SQL . , , .
Sql - , () SQL . , . Access SQL , , /SQL.
: , , , , , . . , . .
- SQL,
SQL SQL. SQL ( , , ) .
SQL :
;
() ;
, , ;
() , .
, , , 7- ASCII ( - ).
SQL () () , .., , SELECT, Select, select .
. ( ) .
- (, CREATE - , UPDATE - , SELECT - ..) ; ( ). . :
|
|
,
,
.
- () SQL. SQL
Embedded SQL
SQL- . SQL , () .
DB2- ( ) SQLJ.
Static SQL
. BIND, .
embedded SQL DB2- DBRM (DataBase Request Module) , SQL- . , , (BIND) SQL- , , DBRM , (PLAN). , , DBRM. , . (PACKAGE), DBRM. , . BIND . - , - .. .
static + embedded SQL PL/SQL, , , Transact-SQL.
Dynamic SQL
, (, ), , / . SQL- - . . ( prepare statement) . , prepare , prepared SQL. , .
Parameterized SQL
prepared SQL -.
SQL, -, - SQL- , - SQL . , "Parameterized SQL" . '?', , , , - . "" , SQLINJ.
- SQL
MicrosoftSQL Server 1989 . , , , .
Microsoft SQL Server, Microsoft Sybase, OS/2. Windows NT . .
Microsoft SQL Server:
1992 . v 4.2;
1996 . v 6.5;
1999 . v 7.0;
2000 . v 2000;
2005 . v 2005;
2008 . v 2008.
2000 XML (eXtensibleMarkupLanguage).
2000 6 : Enterprise, Standard, Developer, Personal, Desktop, Compact Edition.
, 2 : SQLEnterpriseManager, ISQL ( ).
TransactSQL (TSQL), SQL ANSISQL/92. TransactSQL () .
|
|
:
1) . SQL Server , , , .
2) , . ( ).
3) RAID-.
4) .
5) .
6) .
SQLServer 2 : *.mdf *.ldf ( ).
SQL Server - , "" . , , , .
- SELECT, () SELECT
SELECT SQL. () , .
SELECT .
SELECT <_>
[INTO <_>]
FROM <_>
[WHERE <_>]
[GROUP BY <_>]
[HAVING <_>]
[ORDER BY <_> [ASC | DESC] ]
FROM.
- SQL,
GROUP BY .
GROUP BY [ALL] < > [,,n]
ORDER BY , .
, . . .
C :
COUNT -NULL , .
SUM .
AVG .
MAX .
MIN .
- SQL
* - , .
CHAR CHAR(n) - . n. CHAR CHAR(1). n .
VARCHAR (n) - . , .
INTEGER INT - , , , 4 . (: , , "" ) - 2147483647 + 2147483648
SMALLINT - (2 ), - 32767 +32768
* - .
FLOAT SMALLFLOAT - ( 8 4 ).
DECIMAL (p) - FLOAT p.
DECIMAL (p,n) - , p - , n - .
* - , , . , DECIMAL(p,n).
MONEY (p,n) - DECIMAL(p,n). , .
* - , . , .
DATE - .
TIME - .
INTERVAL - .
DATETIME - ( + + + + + + ).
* - ( , ..). , :
BINARY BYTE BLOB
* - .
SERIAL - INTEGER, (, ). , .
, NULL - " ". , .
- , ,
INSERT . :
<_>::=INSERT INTO <_>
[(_ [,...n])] {VALUES ([,...n])| <SELECT_>}
_ , . INSERT VALUES . , . , ( ), , . INSERT , NULL, , DEFAULT. :
;
, , ..
.
DELETE .
:
<_>::=DELETE FROM <_>[WHERE <_>]
_ , . WHERE , , . WHERE, , .
UPDATE .
:
<_>::= UPDATE _ SET _=
<>[,...n] [WHERE <_>]
_ , . SET , . WHERE . , . WHERE , , . .
- , WHERE (HAVING) SELECT , WHERE . WHERE (HAVING) .. , , , , (, ).
. WHERE (HAVING) IN, EXISTS (= | <> | < | <= | > | >=). " ". . , , ..
. , , , ( ). , . .., , ( ).
: Lotus Approach, Microsoft Access, Borland dBase, Borland Paradox, Microsoft Visual FoxPro, Microsoft Visual Basic, Microsoft SQL Server Oracle, , -. , , , , , . , , , , , , .., ( SQL / VBA) . , . , - RAD ( RapidApplicationDevelopment), , . VisualBasic 4.0 Visual C++, , , , . -.
, - , . , .
- -,
- . , , , , , . , . , , , , .
- : SQL- . , , . , , -.
- 4 ():
1. ( , - );
2. ;
3. (, ) ;
4. ();
"-":
; ;
, ; , 10 000 , 2 , 2 ;
: , 100-500 ;
, , , , . , , ; , -.
; ;
- ; - , ; - , .
- MicrosoftSQL Server
MicrosoftSQL Server 1989 . , , , .
Microsoft SQL Server, Microsoft Sybase, OS/2. Windows NT . .
Microsoft SQL Server:
1992 . v 4.2;1996 . v 6.5;1999 . v 7.0;2000 . v 2000;2005 . v 2005;
2008 . v 2008.
2000 XML (eXtensibleMarkupLanguage).
2000 6 : Enterprise, Standard, Developer, Personal, Desktop, Compact Edition.
, 2 : SQLEnterpriseManager, ISQL ( ).
TransactSQL (TSQL), SQL ANSISQL/92. TransactSQL () .
:
1) . SQL Server , , , .
2) , . ( ).
3) RAID-.
4) .
5) .
6) .
SQLServer 2 : *.mdf *.ldf ( ).
SQL Server - , "" . , , , .
- Microsoft SQL Server Management Studio
SQL Server Management Studio , , , SQL Server. SQL Server Management Studio SQL Server
SQL Server Management Studio Enterprise Manager, Query Analyzer Analysis Manager, SQL Server. , SQL Server Management Studio SQL Server, Reporting Services, Integration Services, SQL Server Compact 3.5 1 (SP1). , , .
- - MS Access MS SQL Server
:
, ( , , , , );
, .
: , ( , , ..), - . , , . , , Access Access VBA (Visual BASIC forApplication).
- . ( ) , , . , , . - ( ). , , . , - : . , , , , ( ). - Microsoft SQL Server.