unit1.~pas

一个很小很实用的数据库模糊查询程序,可实现几种条件的查询.

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, Grids, DBGrids, StdCtrls, CheckLst, Buttons,DB, ComCtrls;

type
  TForm1 = class(TForm)
    grpbxQry: TGroupBox;
    cbxempid: TComboBox;
    cbxlname: TComboBox;
    cbxfname: TComboBox;
    cbxjobid: TComboBox;
    cbxminit: TComboBox;
    edtlname1: TEdit;
    ClQrySlt: TCheckListBox;
    edtjobid1: TEdit;
    cbxjoblvl: TComboBox;
    cbxpubid: TComboBox;
    edtempid1: TEdit;
    edtfname1: TEdit;
    edtfname2: TEdit;
    edtminit1: TEdit;
    edtminit2: TEdit;
    edtjoblvl1: TEdit;
    edtjoblvl2: TEdit;
    edtpubid1: TEdit;
    edtlname2: TEdit;
    edtjobid2: TEdit;
    edtpubid2: TEdit;
    DBGrid1: TDBGrid;
    grpbxZh: TGroupBox;
    rbtnAnd: TRadioButton;
    rbtnOr: TRadioButton;
    btnSearch: TBitBtn;
    edtempid2: TEdit;
    cbxdate: TComboBox;
    dtpDate: TDateTimePicker;
    dtpDate2: TDateTimePicker;
    procedure btnSearchClick(Sender: TObject);
    procedure cbxempidChange(Sender: TObject);
    procedure cbxfnameChange(Sender: TObject);
    procedure cbxminitChange(Sender: TObject);
    procedure cbxlnameChange(Sender: TObject);
    procedure cbxjobidChange(Sender: TObject);
    procedure cbxjoblvlChange(Sender: TObject);
    procedure cbxpubidChange(Sender: TObject);
    procedure cbxdateChange(Sender: TObject);
    procedure edtjobid1Exit(Sender: TObject);
    procedure FormShow(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

uses Unit2;

{$R *.dfm}
//查询
procedure TForm1.btnSearchClick(Sender: TObject);
var
  sqlstr,borh : string;
begin
  //若当前数据库不处于浏览状态，则不运行查询操作
  if dbgrid1.DataSource.DataSet.State<>dsBrowse then
  begin
    MessageDlg('正在操作数据，不能查询！', mtInformation,[mbOk], 0);
    exit;
  end;
  //选择"与"操作
  if rbtnAnd.Checked then
  begin
    //默认全选
    sqlstr := 'emp_id > ''0'' ';
    borh := 'And'
  end
  else
  //选择"或"操作
  begin
    //默认无记录
    borh := 'or';
    sqlstr := 'emp_id <= ''0'' ';
  end;
  //选择emp_id查询条件
  if ClQrySlt.Checked[0] then
  begin
    if cbxempid.ItemIndex > 0 then
    begin
      if cbxempid.itemindex > 1 then
        //为其他种类的连接符
        sqlstr := sqlstr + borh + ' emp_id ' + cbxempid.Items[cbxempid.itemindex] + ' ''' + edtempid1.Text + ''' '
      else
        //连接符为"Like"
        sqlstr := sqlstr + borh + ' emp_id ' + cbxempid.Items[cbxempid.itemindex] + ' ''%' + edtempid1.Text + '%'' ';
    end
    else
      //连接符为"Between"
      sqlstr := sqlstr +borh+ '(emp_id between ''' + edtempid1.Text + ''' and ''' + edtempid2.Text + ''' )';
  end;
  //选择fname查询条件
  if ClQrySlt.Checked[1] then
  begin
    if cbxfname.ItemIndex > 0 then
    begin
      if cbxfname.ItemIndex > 1 then
        sqlstr := sqlstr +borh+ ' fname ' + cbxfname.Items[cbxfname.itemindex] + '' + edtfname1.Text
      else
        sqlstr := sqlstr +borh+ ' fname ' + cbxfname.Items[cbxfname.itemindex] + ' ''%' + edtfname1.Text + '%'' ';
    end
    else
      sqlstr := sqlstr +borh+ '(fname between ''' + edtfname1.Text + ''' and ''' + edtfname2.Text + ''' )';
  end;
  //选择minit查询条件
  if ClQrySlt.Checked[2] then
  begin
    if cbxminit.ItemIndex > 0 then
    begin
      if cbxminit.ItemIndex > 1 then
        sqlstr := sqlstr +borh+ ' minit ' + cbxminit.Items[cbxminit.itemindex] + '' + edtminit1.Text
      else
        sqlstr := sqlstr +borh+ ' minit ' + cbxminit.Items[cbxminit.itemindex] + ' ''%' + edtminit1.Text + '%'' ';
    end
    else
      sqlstr := sqlstr +borh+ '(minit between ''' + edtminit1.Text + ''' and ''' + edtminit2.Text + ''' )';
  end;
  //选择lname查询条件
  if ClQrySlt.Checked[3] then
  begin
    if cbxlname.ItemIndex > 0 then
    begin
      if cbxlname.ItemIndex > 1 then
        sqlstr := sqlstr +borh+ ' lname ' + cbxlname.Items[cbxlname.itemindex] +  ' ''' + edtlname1.Text + ''' '
      else
        sqlstr := sqlstr +borh+ ' lname ' + cbxlname.Items[cbxlname.itemindex] + ' ''%' + edtlname1.Text + '%'' ';
    end
    else
      sqlstr := sqlstr +borh+ '(lname between ''' + edtlname1.Text + ''' and ''' + edtlname2.Text + ''' )';
  end;
  //选择job_id查询条件
  if ClQrySlt.Checked[4] then
  begin
    if cbxjobid.ItemIndex > 0 then
    begin
      if cbxjobid.ItemIndex > 1 then
        sqlstr := sqlstr +borh+ ' job_id ' + cbxjobid.Items[cbxjobid.itemindex] +  ' ''' + edtjobid1.Text + ''' '
      else
        sqlstr := sqlstr +borh+ ' job_id ' + cbxjobid.Items[cbxjobid.itemindex] + ' ''%' + edtjobid1.Text + '%'' ';
    end
    else
      sqlstr := sqlstr +borh+ '(job_id between ''' + edtjobid1.Text + ''' and ''' + edtjobid2.Text + ''' )';
  end;
  //选择job_lvl查询条件
  if ClQrySlt.Checked[5] then
  begin
    if cbxjoblvl.ItemIndex > 0 then
    begin
      if cbxjoblvl.ItemIndex > 1 then
        sqlstr := sqlstr +borh+ ' job_lvl ' + cbxjoblvl.Items[cbxjoblvl.itemindex] + '' + edtjoblvl1.Text
      else
        sqlstr := sqlstr +borh+ ' job_lvl ' + cbxjoblvl.Items[cbxjoblvl.itemindex] + ' ''%' + edtjoblvl1.Text + '%'' ';
    end
    else
      sqlstr := sqlstr +borh+ '(job_lvl between ''' + edtjoblvl1.Text + ''' and ''' + edtjoblvl2.Text + ''' )';
  end;
  //选择pub_id查询条件
  if ClQrySlt.Checked[6] then
  begin
    if cbxpubid.ItemIndex > 0 then
    begin
      if cbxpubid.ItemIndex > 1 then
        sqlstr := sqlstr +borh+ ' pub_id ' + cbxpubid.Items[cbxpubid.itemindex] +  ' ''' + edtpubid1.Text + ''' '
      else
        sqlstr := sqlstr +borh+ ' pub_id ' + cbxpubid.Items[cbxpubid.itemindex] + ' ''%' + edtpubid1.Text + '%'' ';
    end
    else
      sqlstr := sqlstr +borh+ '(pub_id between ''' + edtpubid1.Text + ''' and ''' + edtpubid2.Text + ''' )';
  end;
  //选择hire_date查询条件
  if ClQrySlt.Checked[7] then
  begin
    if cbxDate.ItemIndex > 0 then
      //为其他种类的连接符
      sqlstr := sqlstr + borh + ' hire_date ' + cbxDate.Items[cbxDate.itemindex] + ' ''' + DatetoStr(dtpDate.date) + ''' '
    else
      //连接符为"Between"
      sqlstr := sqlstr + borh + ' (hire_date between ''' + datetostr(dtpDate.date) + ''' and ''' + datetostr(dtpDate2.date) + ''')';
  end;
  try
    with Dm.ADOQuery1 do
    begin
      if active then
        close;
      sql.Clear;
      //将查询条件写入SQL语句中
      sql.Add('select * from employee where ' + sqlstr);
      //执行
      open;
      if Recordcount<= 0 then
      begin
        //若得到的记录为空，则取全部值列于表格中
        ShowMessage('记录数为空，请重新查询！');
        if active then
          close;
        sql.Clear;
        sql.Add('select * from employee');
        open;
      end;
    end;
  except
    ShowMessage('数据库[employee表]打开错误，请检查服务器！');
  end;
end;
//combobox变化出现betwween的情况
procedure TForm1.cbxempidChange(Sender: TObject);
begin
  if TCombobox(sender).ItemIndex > 0 then
    edtempid2.Visible := False
  else
    edtempid2.Visible := True;
end;
//combobox变化出现betwween的情况
procedure TForm1.cbxfnameChange(Sender: TObject);
begin
  if TCombobox(sender).ItemIndex > 0 then
    edtfname2.Visible := False
  else
    edtfname2.Visible := True;
end;
//combobox变化出现betwween的情况
procedure TForm1.cbxminitChange(Sender: TObject);
begin
  if TCombobox(sender).ItemIndex > 0 then
    edtminit2.Visible := False
  else
    edtminit2.Visible := True;
end;
//combobox变化出现betwween的情况
procedure TForm1.cbxlnameChange(Sender: TObject);
begin
  if TCombobox(sender).ItemIndex > 0 then
    edtlname2.Visible := False
  else
    edtlname2.Visible := True;
end;
//combobox变化出现betwween的情况
procedure TForm1.cbxjobidChange(Sender: TObject);
begin
  if TCombobox(sender).ItemIndex > 0 then
    edtjobid2.Visible := False
  else
    edtjobid2.Visible := True;
end;
//combobox变化出现betwween的情况
procedure TForm1.cbxjoblvlChange(Sender: TObject);
begin
  if TCombobox(sender).ItemIndex > 0 then
    edtjoblvl2.Visible := False
  else
    edtjoblvl2.Visible := True;
end;
//combobox变化出现betwween的情况
procedure TForm1.cbxpubidChange(Sender: TObject);
begin
  if TCombobox(sender).ItemIndex > 0 then
    edtpubid2.Visible := False
  else
    edtpubid2.Visible := True;
end;
//combobox变化出现betwween的情况
procedure TForm1.cbxdateChange(Sender: TObject);
begin
  if TCombobox(sender).ItemIndex > 0 then
    dtpdate2.Visible := False
  else
    dtpdate2.Visible := True;
end;

//限制只能输入数字
procedure TForm1.edtjobid1Exit(Sender: TObject);
var
  myedt:Tedit;
begin
  myedt:=sender as Tedit;
  if trim(myedt.Text)<>'' then
  begin
    try
      strtoint(myedt.Text);
    except
      showmessage('不能输入字符，请重新输入!');
      myedt.SelectAll;
      myedt.SetFocus;
    end;
  end;
end;
//窗体显示
procedure TForm1.FormShow(Sender: TObject);
begin
  //打开数据集
  Try
    if not Dm.ADOQuery1.Active then
      Dm.ADOQuery1.Active:=True;
  Except
    Showmessage('打开数据库失败！');
  end;
end;

end.