    SQL    

     5
 
          ,   
   SQL (Structured Query Language   
 ).  SQL    ,   
    ,    
.   ,       
,      ,  - 
      SQL    
.   ,    -  
   ,       
  .         
     .     
       
      . 
        ,  ,   .  
      ,    
   .       
:            SQL. 
    ,      -    
         . 
        .    ,  "  
",        SQL.  
    ,     . 
    SQL,    
,    24, "   SQL". 

      
      ,   ,     
-   SQL.        
 ,      6, "   
 /" 

      
     ,       , 
  ,      -   , 
 SQL,      SQL  .  
     Local InterBase,  
  Delphi Client/Server,        
,   SQL ANSI'92. 

     5. SQL    75 

       

           SQL     
 .        ! 

      
              SQL  
    Sybase   ANSI.  Sybase SQL 
Server    , Microsoft SQL Server,  
 SQL,      ANSI. InterBase, 
Oracle         ANSI,   
       .    
    InterBase,      
 SQL Server      .  , 
  ,       Sybase,    
  Microsoft SQL Server.  ,   InterBase   
,        ANSI 
,   Oracle. 

      SQL 

          SQL  ,   
      SQL.      
  Windows Interactive SQL (WISQL),    
Local InterBase Server.       
.  Sybase SQL Server     
     ISQL,   Oracle  SQL* Plus. 
     , , ,  Desktop 
DBA  Datura  DB Artisan  Embarcadero.  ,  
  ,     SQL   
    . 

     

      SQL    :   
(statement terminator)    (batch terminator).   
 SQL         
. ,  InterBase  Oracle ,    
     (;).    
      SQL,    ,  
   SQL  . 
     ,     ,   
   SQL.     
       .  
       
 SQL,   . ,  ISQL Sybase ,  
   SQL   GO (   
,    ,  ).  ,  
GO      Transact-SQL Sybase    
    . 
        ,     WISQL 
InterBase,          
.          . 

      
      InterBase       , 
   SQL,    
 ,    SQL   . , 
  SQL,   CREATE PROCEDURE,     
SQL,            
  SQL.    ,     
   ,     .   
 ,    SET TERMINATOR,   
        
.       24. 

    76  I.  

       

     ,     -  ,  
    ,    .    
,      .   SQL    
  CREATE DATABASE.      
   .  ,   InterBase: 

    CREATE DATABASE ":\DATA\IB\ORDENT.GDB" 
    USER "SYSDBA" PASSWORD "masterkey"; 

        ISQL InterBase,     
  ,     .  
 WISQL        Create Database 
 File  .     . 
    1.    InterBase  Windows Interactice SQL  
   . 
    2.    File   Create Database. 
    3.   Database   Create Database   : 
\DATA\IB\ORDENT,    : \DATA\IB  ,   
     . 
    4.   Usemame  Password , ,   
 .         
SYSDBA,      masterkey.   InterBase   
,    ,      . 
    5.    . InterBase        
.           
    File=>Connect to Database. 

      

      ,      .   
      SQL CREATE TABLE.    
SQL         Run  . 

    CREATE TABLE CUSTOMER 
    ( 
    
    CustomerNumber     int          NOT NULL 
    LastName            char (30)     NOT NULL 
    FirstName           char (30)     NOT NULL 
    StreetAddress       char(30)     NOT NULL 
    City               char(20)     NOT NULL 
    State               char (2)      NOT NULL 
    Zip                char(10)     NOT NULL 
    ) 

        CUSTOMER,    
  .    ORDERS,    
   . 

    CREATE TABLE ORDERS 
    ( 
    
    OrderNumber        int                NOT NULL, 
    OrderDate          date               NOT NULL, 
    CustomerNumber     int                NOT NULL, 
    ItemNumber         int                NOT NULL, 
    Amount             numeric(9,2)        NOT NULL 
    ) 
    
       ITEMS,      . 

    CREATE TABLE ITEMS 
    ( 
    ItemNumber   int                 NOT NULL, 
    Description char(30)            NOT NULL, 
    Price        numeric(9,2)        NOT NULL 
    ) 


     5. SQL    77 


      
           NULL (NOT 
NULL).          . 
,  InterBase,     ANSI SQL   
   NULL.   SQL Server, ,  
   NOT NULL.      
. ,   .  InterBase   
   NULL,      .   
    ,   SQL  ,.


      

             INSERT. 
  ,       .  
     CUSTOMER ( INSERT    
  ) 

    INSERT INTO CUSTOMER 
    VALUES(1,'Doe','John','123 Sunnylane','Anywhere','OK','73115') 
    
    INSERT INTO CUSTOMER 
    VALUES(2,'Doe','Jane',423 Sunnylane','Anywhere','OK','73115') 
    
    INSERT INTO CUSTOMER 
    VALUES(3,'Citizen','John','57 Riverside','Reo','','65803') 
          ORDERS. 
    
    INSERT INTO ORDERS 
    VALUES(101,'07/07/97',1,1001,123.45) 
    
    INSERT INTO ORDERS 
    VALUES(102,'07/08/97',2, 1002,678.90) 
    
    INSERT INTO ORDERS 
    VALUES(103,'07/09/97',3,1003,86753. 9) 
    
    INSERT INTO ORDERS 
    VALUES(104,'07/10/97',1,1002,678.90) 


      
         Oracle     
.      Oracle     
      NLS_DATE__FORMAT.  
    'DD-MMM-YY' ,     
   INSERT   ORDERS    . 
    
    INSERT INTO ORDERS 
    VALUES(101,'07-JUL-97',1,1001,123.45) 
    
    INSERT INTO ORDERS 
    VALUES(102,'08-JUL-97',2,1002,678.90)      
    
    INSERT INTO ORDERS    
    VALUES(103,'09-JUL-97',3,1003,86753.09) 
    
    INSERT INTO ORDERS 
    VALUES(104,'10-JUL-97',1,1002,678.90) 


    78  I.  

        ,      
,   Oracle TO_DATE(). 
    
    INSERT INTO ORDERS 
    VALUES(101,TO_DATE('07/07/97','MM/DD/YY'),1,1001,123.45) 
    
    INSERT INTO ORDERS    
    VALUES(102,TO_DATE('07/08/97','MM/DD/YY'),2,1002,678.90) 
    
    INSERT INTO ORDERS 
    VALUES(103,TO_DATE('07/09/97','MM/DD/YY'),3,1003,86753.09) 
    
    INSERT INTO ORDERS 
    VALUES(104,TO_DATE('07/10/97','MM/DD/YY;) ,J-,102,678.90) 
    
    
    ,          ITEMS. 
    
    INSERT INTO ITEMS 
    VALUES(1001,'WIDGET A',123.45) 
    
    INSERT INTO ITEMS 
    VALUES(1002,'WIDGET B',678.90) 
    
    INSERT INTO ITEMS 
    VALUES(1003,'WIDGET C',87653.09) 
    
     :    INSERT    
,   .   ,   INSERT   
    ,      ,  
  .   , ,   
    ,     : 

    INSERT INTO ITEMS (ItemNumber, Price) VALUES(1001,123.45) 

     ,         
    .     
  ,       INSERT: 

    INSERT INTO ITEMS (Price, ItemNumber) VALUES(123.45, 1001) 


     SELECT 

              
SELECT.        SELECT * FROM ,   
     (    CUSTOMER, ORDERS  
ITEMS). ,      CUSTOMER  ITEMS    
,   ORDERS  . 
     . 5.1    CUSTOMER    
SELECT * FROM CUSTOMER  WISQL. 
     . 5.2    ORDERS   
 SELECT * FROM ORDERS. 
    ,  . 5.3    ITEMS  
  SELECT * FROM ITEMS. 
      SELECT    SELECT *,     
 .         , 
  , : 

    SELECT CustomerNumber, LastName, State FROM CUSTOMER 

            .  . 5.4 
    . 

     5. SQL    79 


    . 5.1.   CUSTOMER               

    . 5.2.   ORDERS 

         FROM  -.   
   ,    . ,   
SELECT  Oracle  InterBase   FROM  , 
      .   ,  Sybase SQL 
Server     FROM    ,  
   .     SELECT,   
    (   , , 
   ),   Sybase SQL Server   
FROM  . ,     SELECT  SQL Server 
  ,   getdate(),     
 FROM,     Oracle    SYSDATE  
  . 

    . 5.3.   ITEMS 

    . 5.4.   ,    SELECT 


    80  I.  

       

         SELECT      
 .        
(expression column), ,   ,  , 
    .    SQL  
  ,        
  . ,   Sybase  Microsoft SQL Server 
   ,     : 

    SELECT getdate() 

      InterBase     ,  
 ,     SQL (. 5.5): 

    SELECT UPPER(LastName), FirstName FROM CUSTOMER 


    Puc. 5.5.   UPPER   SELECT 


      

      (aggregate column)   ,    
    ,   .  
       COUNT ( 
), SUM (  ), AVG ( ), MIN 
( ), STDDEV ( ), VARIANCE 
()   ( ).    
  . 

    SELECT COUNT(*) FROM CUSTOMER 

           . 

    SELECT Max(Amount) FROM ORDERS 

             
.     ,      
   : 

    SELECT SUM(Amount) FROM ORDERS 


      WHERE 

      WHERE   SQL   ,  
   SELECT.   . 

    SELECT * FROM CUSTOMER WHERE State='OK' 

          (. 5.6),   . 
 (..  ,   State   , 
      ). 
       . 5.7,       
 ,       StreetAddress   Sunny. 

    SELECT LastName, StreetAddress FROM CUSTOMER WHERE StreetAddress LIKE '%Sunny%' 

           ,   
 $500 (.5.8). 

    SELECT OrderNumber, OrderDate, Amount FROM ORDERS WHERE Amount > 500 


     5. SQL    81 


    . 5.6.    WHERE   SELECT 

    . 5.7.    WHERE  LIKE   SELECT 

     ,  ,      , 
    8  9  1997 . (. 5.9). 

    SELECT OrderNumber, OrderDate, Amount FROM ORDERS WHERE OrderDate BETWEEN 
'07/08/97' AND '07/09/97' 

    . 5.8.       $500 

    . 5.9.    BETWEEN 


     

          WHERE   
        .  
    :     FROM   
SELECT   ,      
        WHERE.   
,      . 5.10. 

    82                                                             I.  


    SELECT CUSTOMER.CustomerNumber, ORDERS.Amount 
    FROM CUSTOMER, ORDERS 
    WHERE 
    CUSTOMER.CustomerNumber=ORDERS.CustomerNumber 


    . 5.10. ,     CUSTOMER  ORDERS 

     ,      FROM   CUSTOMER 
  ORDERS.        
        CustomerNumber  
CUSTOMER  ORDERS. ,     ,  
 (outer) ,  ,     ,  
 (inner).        , 
..   .   ,    
(left join) - ,       , 
     . 

        

            
  (inner join).      
,     .    
   (outer join), .. ,     
    .    
    ,    
,    NULL. 
             ,  
  ANSI SQL  .     
 .  Sybase  Microsoft SQL Server   . 

    SELECT CUSTOMER.CustomerNumber, ORDERS.Amount 
    FROM CUSTOMER, ORDERS 
    WHERE CUSTOMER.CustomerNumber *= ORDERS.CustomerNumber 

        ,       
     (*).   SQL Server  
     (left outer join).  
    ,      
 .   SQL Server     
 (right outer join)        
 ,   ,     
  . 

    SELECT CUSTOMER.CustomerNumber, ORDERS.Amount 
    FROM CUSTOMER, ORDERS 
    WHERE CUSTOMER.CustomerNumber =* ORDERS.CustomerNumber 

            , 
    ,    . 

      ANSI 

      ,   ANSI,  
  (. 5.11). 

    SELECT CUSTOMER.CustomerNumber, ORDERS.Amount 
    FROM CUSTOMER LEFT OUTER JOIN ORDERS 
    ON CUSTOMER.CustomerNumber=ORDERS.CustomerNumber 

      ANSI,       
   ,  , ,  
LEFT  RIGHT   OUTER  INNER. 

     5. SQL    83 

     InterBase    Sybase    
SELECT         WHERE,  
  ANSI.     , 
 InterBase    Sybase.

      

      (subquery)    SELECT,    
,    WHERE.     
  ,      
  SELECT.   ,   
   . 5.12. 

    SELECT * FROM CUSTOMER WHERE CustomerNumber IN (SELECT CustomerNumber FROM ORDERS) 


    Puc. 5.11.   CUSTOMER  ,      
WHERE   SELECT

    . 5.12.  ORDERS   ANSI SQL                  
 

    GROUP BY 

       SQL   ,   
  ,   ,    , 
   . ,     
,        . 
  dBASE      , 
      ,    .  
 ,    PC,    
       .  
  SQL     .   
SQL   ,       
Xbase   10   50   .  
  SQL       
  GROUP BY,        SQL. 
  . 

    SELECT CUSTOMER.CustomerNumber, sum(ORDERS.Amount) TotalOrders 
    FROM CUSTOMER, ORDERS 
    WHERE CUSTOMER.CustomerNumber=ORDERS.CustomerNumber 
    GROUP BY CUSTOMER.CustomerNumber 

             
   (. 5.13). 

     I.      84                                                                         

           ,    
GROUP BY?     ,       
,      SELECT,     
     .   
  SELECT.            

    SELECT CUSTOMER.CustomerNumber, 
    CUSTOMER.LastName, CUSTOMER.State, 
    sum(ORDERS.Amount) TotalOrders 
    FROM CUSTOMER, ORDERS 
    WHERE 
    CUSTOMER.CustomerNumber=ORDERS.CustomerNumber 

        ,    GROUP BY, 
   . 

    GROUP BY CUSTOMER.CustomerNumber, CUSTOMER.LastName, CUSTOMER.State 


    Puc. 5.13.      GROUP BY 

     ,  Oracle  InterBase,    
    ,   .  ,   
 SQL Server,   ,   ,  , 
 . , ,  . 

    SELECT CUSTOMER.LastName, COUNT(*) NumberWithName FROM CUSTOMER 

        ,       
 (last name)    CUSTOMER    ,  
     .     
,    .    ,  ,   
InterBase,     ,  ,   
,        GROUP BY.   , 
 SQL Server,   ,      
     .   ,  
       ,     
  .  ,      ,  
  ,      ,  
      .  :  
   ,   ""     
. ,       NumberWithName 
        .   
   ,      . 

    SELECT CUSTOMER.LastName, COUNT(*) NumberWithName 
    FROM CUSTOMER 
    GROUP BY CUSTOMER.LastName 

    ,  ,   . 5.14.    
,         ,    
   CUSTOMER. 

    HAVING 

      HAVING    ,   
  ,      GROUP BY. 
   HAVING  GROUP BY   ,   
  WHERE   SELECT.     ,  
HAVING   ,  , a WHERE    
 ,     (. 5.15). 
     , ,      HAVING,  
  ,    .   ,    
HAVING         ,  
     WHERE.   WHERE 
       ,    HAVING 
 .    SELECT,      HAVING.

     5. SQL    85 


    SELECT CUSTOMER.LastName, COUNT(*) NumberWithName 
    FROM CUSTOMER 
    GROUP BY CUSTOMER.LastName 
    HAVING CUSTOMER.LastName<>'Citizen' 


    . 5.14.       

    . 5.15.    VING   ,   


           ,   HAVING. 

    SELECT CUSTOMER.LastName, COUNT(*) NumberWithName 
    FROM CUSTOMER 
    WHERE CUSTOMER.LastName<>'Citizen' 
    GROUP BY CUSTOMER.LastName 

      (, Sybase SQL Server)   ,  
    HAVING,      
.      ,    
HAVING    ,     
 .   ,      HAVING, 
         
 ,  ,    . 

    ORDER RY 

      ORDER BY      
.  ,     . 5.16. 

    SELECT LastName, State FROM CUSTOMER ORDER BY State 

         ORDER BY. 

    SELECT FirstName, LastName FROM CUSTOMER ORDER BY LastName 


    Puc. 5.16.    ORDER BY 


    86  I.  


      

     , , ,      
  COUNT ()  SUM (),     
.      (column aliase)  
  ,      .  ANSI SQL 
        
    SELECT. ,  ,   
     GROUP BY,    
    COUNT ()   
NumberWithName.        , 
       . ,    
   LName  LastName. 

    SELECT CUSTOMER.LastName LName, COUNT(*) NumberWithName 
    FROM CUSTOMER 
    GROUP BY CUSTOMER.LastName 

         ,   ,  
  WHERE  GROUP BY,   .   
    . 
      SQL Server    ,   
     ,      . 

    SELECT CUSTOMER.LastName, NumberWithName=COUNT(*) 
    FROM CUSTOMER 
    GROUP BY CUSTOMER.LastName 

         Oracle  InterBase. 

      

                 
 SELECT,     .    
   (table alias)      
 FROM  SELECT.     . 

    SELECT .LastName, COUNT (*) NumberWithName FROM CUSTOMER  GROUP BY C.LastName 

     ,        
SELECT    .   ,      
       . 

      

    ,  "  " .   ,   
  SQL     ,    
 ,           SQL. 

        

           , 
   , ,   ,    ,  
    SQL.    ,  
 SQL,    24. 

     5. SQL    87 


     CONNECT 

       SQL  CONNECT    
"  " ,  ,        
 .     InterBase  Oracle.  
     DISCONNECT. 
      SQL Server       
 USE.    CONNECT,  USE     
      .   SQL Server  
   DISCONNECT       
   USE     . 

     UPDATE 

          ,    
  .      SQL UPDATE.  
      REPLACE ALL  dBASE. 
    UPDATE. 

    UPDATE CUSTOMER SET Zip='90210' WHERE City='Beverly Hills' 

         Zip   ,    
city  ,      WHERE.  
 WHERE,            
, : 

    UPDATE CUSTOMER SET State='CA' 

     ,      ,   
   , : 

    UPDATE ORDERS SET Amount=Amount+(Amount*.07) 

      SQL Server, Transact-SQL,      
,      , : 

    UPDATE ORDERS 
    SET Amount=Price 
    FROM ORDERS, ITEMS 
    WHERE ORDERS.ItemNumber=ITEMS.ItemNumber 


     DELETE 

     DELETE     .    
      : 

    DELETE FROM CUSTOMER 

              
 ,   ZAP  dBASE,    
   ,     .   SQL Server, 
,      : 

    TRUNCATE TABLE CUSTOMER 

      DELETE      WHERE, 
    , : 

    DELETE FROM CUSTOMER WHERE LastNameo'Doe' 

     COMMIT  ROLLBACK 

     ,    ,   
(transaction).    SQL COMMIT  ,    
 ,     .  ,   
           , -

    88  I.  

   .  ROLLBACK, ,   
     ,    
.        , 
      COMMIT      
,   . 
      ,  SQL Server,  ,    
   ,      
.  WISQL  InterBase    
     InterBase SET TRANSACTION.  
  ,     ,  
   .  ,      
        Commit Work  Rollback 
Work, ,    File . 

     

              SQL.  
  SQL     ,     
   ,  , ,    . 

     ... 

              
.    ,  ,      
,         ,  
  . 

     5. SQL    89 
