常用sql.txt

delphi的学习资料

   模糊查询  where 字段名 like %串%       %作为通配符号，不一定是2个 
             where 字段名 like '[N-R]%'   [N-R]代表任何处在N到R之间的单个字符

Select COUNT(*) From BDat Where Typ=4 AND mNo=4010

Select AVG(VM) From BDat Where Typ=4 AND mNo=4010  类似的有 SUM、MAX、MIN



WHERE vote BETWEEN 7 AND 10 等效于   WHERE vote>6  and  vote<11
WHERE vote IN (1,10) ???????? 



Alter Domain d_vq  set Default -1

Alter Table BDat Add fAlarm char(1)

Alter Table BDat Drop fAlarm





▲SQL  Select

    SQL.Text:='Select HostNo,DsNo,sMeter From ArchUser A,IndexHost B Where A.UsNo<>''0'''
              +' And A.HostNo=B.HostNo';
    Open;


    SQL.Text:='Select * From ArchScatt Where Region In (Select Region From IndexHost)'
              +'Order By Region,UsNo';
    Open;
        // 倒排序       Order By Region DESC, UsNo DESC     //正序 ASC


    SQL.Text:='Select HostNo,DsNo From ArchUser Where UsNo<>''0'' And HostNo In '
              +'(Select HostNo From ArchHost Where '+FiltA+')';


    SQL.Text:='Select Region From IndexHost Where Region In (Select Region From'
              +' ArchScatt) Order By Region';
    Open;

   ▲ SQL.Text:='Select AVG(y1),SUM(y1),MAX(y1),MIN(y1) From XXXX Into :a,:s,:Max,:Min;
 
   ▲ SQL.Text:='Select COUNT(*) From XXXX Into :a;
      SQL.Text:='Select COUNT(ALL) From XXXX Into :a; 
      SQL.Text:='Select COUNT(y1) From XXXX  Into :a;
      SQL.Text:='Select COUNT(DISTINCT y1) From XXXX  Into :a;
      SQL.text:='SELECT Max(BlkNo) FROM SegDat';









▲SQL  Insert
   SQL.Text:='Insert Into DefNote (Note,NoteTxt) Values(''0'',''无注解'')';
   ExecSQL;

▲SQL  Delete
   SQL.Text:='Delete From ArchHost Where HostNo='+HostNo;
   ExecSQL;


▲SQL  Update
   SQL.Text:='Update IniHost Set Success=''Y'', fLineRAM=''L'' Where HostNo='+HostNo;
   ExecSQL;


   SQL.Text:='Update ArchUser Set UsNo='+sUsNo+', mType='+smType+' Where HostNo='+HostNo+' And DsNo='+sDsNo;
   ExecSQL;


▲Create View vDat As Select * From DayDat

▲
Alter Domain d_vq  set Default -1

Alter Table BDat Add fAlarm char(1)

Alter Table BDat Drop fAlarm

▲Alter Table XXX
    ADD y1  Char(3) Not Null Default '0',
    ADD y2  Char(3) Not Null Default '1',
    Drop y3;


▲
▲
▲