学习 SQL 语句 - Select(3): 条件查询与模糊查询

本文来自：万一
Where 用来指定查询条件;

Like 和 Not Like 来指定模糊条件;

模糊条件中:
_ 表示任一字符;
% 表示任一字符串;
[] 表示一个集合.

---

本例效果图:

---

代码文件:

---

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, ExtCtrls, Grids, DBGrids, DB, ADODB;

type
  TForm1 = class(TForm)
    DBGrid1: TDBGrid;
    DataSource1: TDataSource;
    ADODataSet1: TADODataSet;
    Panel1: TPanel;
    Button1: TButton;
    Button2: TButton;
    Button3: TButton;
    Button4: TButton;
    Button5: TButton;
    Button6: TButton;
    Button7: TButton;
    Button8: TButton;
    Button9: TButton;
    Button10: TButton;
    Button11: TButton;
    Button12: TButton;
    Button13: TButton;
    Button14: TButton;
    procedure FormCreate(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure Button4Click(Sender: TObject);
    procedure Button5Click(Sender: TObject);
    procedure Button6Click(Sender: TObject);
    procedure Button7Click(Sender: TObject);
    procedure Button8Click(Sender: TObject);
    procedure Button9Click(Sender: TObject);
    procedure Button10Click(Sender: TObject);
    procedure Button11Click(Sender: TObject);
    procedure Button12Click(Sender: TObject);
    procedure Button13Click(Sender: TObject);
    procedure Button14Click(Sender: TObject);
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

//country 表中 Area<200000 的记录
procedure TForm1.Button1Click(Sender: TObject);
begin
  with ADODataSet1 do begin
    Close;
    CommandText := 'SELECT * FROM country WHERE Area<200000';
    Open;
  end;
end;

//country 表中 Continent="South America" 的记录; 字符串值应该在引号中(单引号、双引号均可).
procedure TForm1.Button2Click(Sender: TObject);
begin
  with ADODataSet1 do begin
    Close;
    CommandText := 'SELECT * FROM country WHERE Continent="South America"';
    Open;
  end;
end;

//country 表中 Name="Cuba" 或者 Name="Peru" 的记录
procedure TForm1.Button3Click(Sender: TObject);
begin
  with ADODataSet1 do begin
    Close;
    CommandText := 'SELECT * FROM country WHERE Name="Cuba" or Name="Peru"';
    Open;
  end;
end;

//country 表中 Continent="South America" 并且 Area>1000000 的记录
procedure TForm1.Button4Click(Sender: TObject);
begin
  with ADODataSet1 do begin
    Close;
    CommandText := 'SELECT * FROM country WHERE Continent="South America" and Area>1000000';
    Open;
  end;
end;

//country 表中 Name 是 c 开头的记录; 其中的 % 表示任意字符串
procedure TForm1.Button5Click(Sender: TObject);
begin
  with ADODataSet1 do begin
    Close;
    CommandText := 'SELECT * FROM country WHERE Name LIKE "c%"';
    Open;
  end;
end;

//country 表中 Name 是 b 或 c 开头的记录; 可以用 "," 隔开更多条件
procedure TForm1.Button6Click(Sender: TObject);
begin
  with ADODataSet1 do begin
    Close;
    CommandText := 'SELECT * FROM country WHERE Name LIKE "[b,c]%"';
    Open;
  end;
end;

//country 表中 Name 是 a 或 b 或 c 开头的记录
procedure TForm1.Button7Click(Sender: TObject);
begin
  with ADODataSet1 do begin
    Close;
    CommandText := 'SELECT * FROM country WHERE Name LIKE "[a-c]%"';
    Open;
  end;
end;

//country 表中 Name 不是 a 或 b 或 c 或 m 开头的记录
procedure TForm1.Button8Click(Sender: TObject);
begin
  with ADODataSet1 do begin
    Close;
    CommandText := 'SELECT * FROM country WHERE Name NOT LIKE "[a-c,m]%"';
    Open;
  end;
end;

//country 表中 Name 不是 a 或 b 或 c 开头的记录
procedure TForm1.Button9Click(Sender: TObject);
begin
  with ADODataSet1 do begin
    Close;
    CommandText := 'SELECT * FROM country WHERE Name NOT LIKE "[a-c]%"';
    Open;
  end;
end;

//country 表中 Name 包含 er 的记录
procedure TForm1.Button10Click(Sender: TObject);
begin
  with ADODataSet1 do begin
    Close;
    CommandText := 'SELECT * FROM country WHERE Name LIKE "%er%"';
    Open;
  end;
end;

//country 表中 Name 包含空格的记录
procedure TForm1.Button11Click(Sender: TObject);
begin
  with ADODataSet1 do begin
    Close;
    CommandText := 'SELECT * FROM country WHERE Name LIKE "% %"';
    Open;
  end;
end;

//country 表中 Name 第二个字符任意, 但第一字符是 p、第三字符是 r 的记录; "_" 表示任意字符
procedure TForm1.Button12Click(Sender: TObject);
begin
  with ADODataSet1 do begin
    Close;
    CommandText := 'SELECT * FROM country WHERE Name LIKE "p_r%"';
    Open;
  end;
end;

//country 表中 Name 是 4 个字符的记录
procedure TForm1.Button13Click(Sender: TObject);
begin
  with ADODataSet1 do begin
    Close;
    CommandText := 'SELECT * FROM country WHERE Name LIKE "____"';
    Open;
  end;
end;

//country 表中 Name 是 4 个字符, 但最后是 a 结尾的记录
procedure TForm1.Button14Click(Sender: TObject);
begin
  with ADODataSet1 do begin
    Close;
    CommandText := 'SELECT * FROM country WHERE Name LIKE "___a"';
    Open;
  end;
end;

procedure TForm1.FormCreate(Sender: TObject);
var
  mdbFile: string;
begin
  mdbFile := GetEnvironmentVariable('COMMONPROGRAMFILES');
  mdbFile := mdbFile + '\CodeGear Shared\Data\dbdemos.mdb';

  ADODataSet1.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' +
    mdbFile + ';Persist Security Info=False';

  DBGrid1.DataSource := DataSource1;
  DataSource1.DataSet := ADODataSet1;
end;

end.