unitstudentquery.~pas

delphi数据库基本利用

unit UnitStudentQuery;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, DB, DBTables, Grids, DBGrids, Mask, DBCtrls, dbcgrids;

type
  TFormStudentQuery = class(TForm)
    DBGrid1: TDBGrid;
    ButtonFirst: TButton;
    ButtonNext: TButton;
    ButtonPrev: TButton;
    ButtonLast: TButton;
    DataSource1: TDataSource;
    Label0: TLabel;
    Label1: TLabel;
    EditCondition1: TEdit;
    ComboBoxField: TComboBox;
    EditCondition2: TEdit;
    ComboBoxSign: TComboBox;
    ButtonQuery1: TButton;
    ButtonQuery2: TButton;
    ButtonClear: TButton;
    DBCtrlGrid1: TDBCtrlGrid;
    LabelID: TLabel;
    DBEditID: TDBEdit;
    LabelName: TLabel;
    DBEditName: TDBEdit;
    LabelSex: TLabel;
    DBEdit1: TDBEdit;
    Label2: TLabel;
    DBEdit2: TDBEdit;
    LabelAddress: TLabel;
    DBEditAddress: TDBEdit;
    RadioButton1: TRadioButton;
    RadioButton2: TRadioButton;
    Query1: TQuery;
    Query1ID: TIntegerField;
    Query1Name: TStringField;
    Query1Sex: TStringField;
    Query1Birthday: TDateTimeField;
    Query1Address: TStringField;
    Query1Picture: TBlobField;
    Query1comment: TStringField;
    Query1Telephone: TStringField;
    Query1ClassId: TIntegerField;
    Query1Name_1: TStringField;
    ButtonReturn: TButton;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormResize(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure ButtonFirstClick(Sender: TObject);
    procedure ButtonNextClick(Sender: TObject);
    procedure ButtonPrevClick(Sender: TObject);
    procedure ButtonLastClick(Sender: TObject);
    procedure ButtonQuery1Click(Sender: TObject);
    procedure ButtonQuery2Click(Sender: TObject);
    procedure ComboBoxFieldSelect(Sender: TObject);
    procedure EditCondition2KeyPress(Sender: TObject; var Key: Char);
    procedure ButtonClearClick(Sender: TObject);
    procedure RadioButton1Click(Sender: TObject);
    procedure RadioButton2Click(Sender: TObject);
    procedure ButtonReturnClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  FormStudentQuery: TFormStudentQuery;

implementation

uses UnitMain,QDialogs;

{$R *.dfm}
//响应学生查询窗口关闭事件
procedure TFormStudentQuery.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  Action:=caFree;
  FormMain.MenuStudentQuery.checked:=false;
  //工具条下压
  FormMain.ToolButtonStudentQuery.Down:=false;
end;

procedure TFormStudentQuery.FormResize(Sender: TObject);
begin
  //改变Dbgride的大小
  DBGrid1.Width :=FormStudentQuery.Width-150;
  DBGrid1.height :=FormStudentQuery.height-160;
  DBCtrlGrid1.Width :=FormStudentQuery.Width-150;
  DBCtrlGrid1.height :=FormStudentQuery.height-200;
  //设置下面按钮位置
  ButtonFirst.Top:=DBGrid1.height+100;
  ButtonNext.Top:=DBGrid1.height+100;
  ButtonPrev.Top:=DBGrid1.height+100;
  ButtonLast.Top:=DBGrid1.height+100;
end;

procedure TFormStudentQuery.FormShow(Sender: TObject);
begin
    FormResize(Sender);
end;

//响应首行记录事件
procedure TFormStudentQuery.ButtonFirstClick(Sender: TObject);
begin
  //指向表的第一行
  Query1.First();
end;

// 响应下一行记录事件
procedure TFormStudentQuery.ButtonNextClick(Sender: TObject);
begin
  //指向表的下一行
  Query1.Next();
end;

//响应前一行记录事件
procedure TFormStudentQuery.ButtonPrevClick(Sender: TObject);
begin
  //指向表的前一行
  Query1.Prior();
end;

//响应最后一行按钮事件
procedure TFormStudentQuery.ButtonLastClick(Sender: TObject);
begin
  //指向表的最后一行
  Query1.Last();
end;

procedure TFormStudentQuery.ButtonQuery1Click(Sender: TObject);
begin
  //清除SQL属性
  Query1.SQL.clear;
  //添加需要查询的条件
  if(Trim(EditCondition1.Text)='')then
    //添加需要查询的条件
    Query1.SQL.Add('select student.ID,student.Name,Sex,Birthday,Address,Telephone,Picture,Comment,student.ClassID,class.Name from student,class where student.classID=class.ID ')
  else
    //添加需要查询的条件
    Query1.SQL.Add('select student.ID,student.Name,Sex,Birthday,Address,Telephone,Picture,Comment,student.ClassID,class.Name from student,class where student.classID=class.ID '
  +'and '
  +EditCondition1.Text);
  //执行SQL语句
  Query1.ExecSQL();
  //重新打开SQL
  Query1.Open();
end;

procedure TFormStudentQuery.ButtonQuery2Click(Sender: TObject);
var
FieldName:string;
begin
  //获取对应的字段名称
  if(ComboBoxField.Text='学号')then
    FieldName:='student.ID'
  else if(ComboBoxField.Text='姓名')then
    FieldName:='Name'
  else if(ComboBoxField.Text='性别')then
    FieldName:='Sex'
  else if(ComboBoxField.Text='出生日期')then
    FieldName:='Birthday'
  else if(ComboBoxField.Text='家庭住址')then
    FieldName:='Address'
  else if(ComboBoxField.Text='联系电话')then
    FieldName:='telephone'
  else if(ComboBoxField.Text='班级号')then
    FieldName:='student.classID'
  else if(ComboBoxField.Text='评论')then
    FieldName:='comment';

  //清除SQL属性
  Query1.SQL.Clear;
  //设置带查询条件的SQL属性
  if(FieldName='student.ID')or (FieldName='student.classID')then
    Query1.SQL.Add('select student.ID,student.Name,Sex,Birthday,Address,Telephone,Picture,Comment,student.ClassID,class.Name from student,class where student.classID=class.ID '
    +'and '+FieldName+' '+ComboBoxSign.text+' '+EditCondition2.Text)
  else if(FieldName='Birthday')then
    Query1.SQL.Add('select student.ID,student.Name,Sex,Birthday,Address,Telephone,Picture,Comment,student.ClassID,class.Name from student,class where student.classID=class.ID '
     +'and '+FieldName+' '+ComboBoxSign.text+' #'+EditCondition2.Text+'#')
  else
  begin
    //判断是否为模糊查询
    if(ComboBoxSign.text='like')then
      Query1.SQL.Add('select student.ID,student.Name,Sex,Birthday,Address,Telephone,Picture,Comment,student.ClassID,class.Name from student,class where student.classID=class.ID '
      +'and '+FieldName+' '+ComboBoxSign.text+' ''%'+EditCondition2.Text+'%''')
    else
      Query1.SQL.Add('select student.ID,student.Name,Sex,Birthday,Address,Telephone,Picture,Comment,student.ClassID,class.Name from student,class where student.classID=class.ID '
      +'and '+FieldName+' '+ComboBoxSign.text+' '''+EditCondition2.Text+'''')
  end;

  //执行SQL语句
  Query1.ExecSQL();
  //重新打开Query1
  Query1.Open();

end;
procedure TFormStudentQuery.ComboBoxFieldSelect(Sender: TObject);
begin
//清空下拉项
  ComboBoxSign.Items.Clear();
  //根据字段值添加相应的下拉项
  if(ComboBoxField.Text='学号') or
    (ComboBoxField.Text='出生日期') or
    (ComboBoxField.Text='班级号')then
  begin
    ComboBoxSign.Items.Add('>');
    ComboBoxSign.Items.Add('>=');
    ComboBoxSign.Items.Add('<');
    ComboBoxSign.Items.Add('<=');
    ComboBoxSign.Items.Add('<>');
    ComboBoxSign.Items.Add('=');
  end
  else if  (ComboBoxField.Text='性别') then
    ComboBoxSign.Items.Add('=')
  else
  begin
    ComboBoxSign.Items.Add('=');
    ComboBoxSign.Items.Add('like');
  end
end;

procedure TFormStudentQuery.EditCondition2KeyPress(Sender: TObject;
  var Key: Char);
begin
  if (key=#13) then
    ButtonQuery2Click(Sender);
end;

procedure TFormStudentQuery.ButtonClearClick(Sender: TObject);
var result1:integer;
begin
  //输出提示信息
  result1:=MessageDlg('确认清空表?',mtConfirmation
              ,[mbYes,mbNo],0,mbYes);
  if result1=3 then
  begin
  //清除SQL属性
  Query1.SQL.Clear;
  //设置Delete语句
  Query1.SQL.Add('delete from student');
  //执行SQL语句
  Query1.ExecSQL();
  //清除SQL属性
  Query1.SQL.Clear;
  //设置Select语句
  Query1.SQL.Add('select student.ID,student.Name,Sex,Birthday,Address,Telephone,Picture,Comment,student.ClassID,class.Name from student,class where student.classID=class.ID ');
  //重新打开Query1
  Query1.Open();
  end
  else
    exit;
end;

procedure TFormStudentQuery.RadioButton1Click(Sender: TObject);
begin
  DBctrlGrid1.Visible :=true;
  DBGrid1.Visible :=false;
end;

procedure TFormStudentQuery.RadioButton2Click(Sender: TObject);
begin
  DBctrlGrid1.Visible :=false;
  DBGrid1.Visible :=true;
end;
//响应返回原表
procedure TFormStudentQuery.ButtonReturnClick(Sender: TObject);
begin
  //清除SQL属性
  Query1.SQL.clear;
  //输入SQL语句
  Query1.SQL.Add('select student.ID,student.Name,Sex,Birthday,Address,Telephone,Picture,Comment,student.ClassID,class.Name from student,class where student.classID=class.ID ');
  //执行SQL语句
  Query1.ExecSQL();
  //重新打开SQL
  Query1.Open();
end;

end.