Создание базы данных "Wc3 Cybersport Data Base" (стр. 2 из 3)

SELECT *

FROM players

where [Full name]='?' or age=0

[Eldest Players]

SELECT *

FROM players

WHERE age between 20 and 30

[Count by Country]

Select Country, count(Nickname) as Players

From players

Group By country

Order by count(Nickname) desc

Group by [место проживания]))

[Domain Country in Clan]

DROP TABLE temp

CREATE TABLE temp

(clan CHAR (15),

players INT ,

country CHAR (60))

INSERT

INTO temp

select Clan, count(Nickname) as Players, Country

From players

Group by Clan, Country

select Clan, max(Players) as Players

From temp

Group by Clan

[Game Race count]

SELECT [Game Race], count([Game Race]) as Number

FROM players

Group by [Game Race]

[Champions]

Select Players.Nickname, Players.[full name], Players.country,

Events.name, Events.prize

From Players, Events

Where Players.nickname=Events.[player winner]

[Winners]

SELECT clans.[full name], clans.owner, events.name, events.prize

FROM clans, events

Where clans.name=events.[clan winner]

5. Интерфейс программы

Интерфейс программы состоит из одного окна, содержащего четыре основные вкладки При запуске программы открывается окно с активной вкладкой «Players» (рисунок 3).

Здесь содержится список всех игроков

Рисунок 3 - Окно программы

При нажатии на вкладку «Clans» выводится таблица, содержащая список кланов (рисунок 4).

Аналогично, нажав на вкладку «Events», выводится список всех событий (рисунок 5).

В каждой из таблиц можно удалять, добавлять записи, корректировать их, также можно свободно перемещаться по записям (переход в начало-конец, следующая-предыдущая запись).

Вкладка Query являет собой автономный генератор запросов использующий язык SQL, с помощью которого можно создавать, редактировать и удалять запросы.

Также вкладка содержит список созданных запросов.

Рисунок 9 – Отчет по запросу: «Все кланы победители»

Заключение

В этом курсовом проекте была разработана база данный по игрокам, кланам и главным событиям вселенной Варкрафт. Также оболочка данной БД может быть использована в качестве оболочки для любой базы данных, так как она включает в себя гибкий генератор запросов и автоматизированный генератор отчетов для любого запроса.

Список используемых источников

1. Терлецкая А.М. – лекции.

2. Т.Карпова – Базы данных: модели, разработка, реализация. Уч. пособие – СПб: Питер,2001.

Приложение А Листинг программы

unit Unit1;

interface

uses

Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,

Dialogs, Grids, DBGrids, DB, ADODB, ExtCtrls, ComCtrls, DBCtrls, Menus,

StdCtrls, Inifiles;

type

TForm1 = class(TForm)

ADOConnection1: TADOConnection;

ADOTable1: TADOTable;

DataSource1: TDataSource;

PageControl1: TPageControl;

TabSheet1: TTabSheet;

TabSheet2: TTabSheet;

TabSheet3: TTabSheet;

Players: TDBGrid;

DBNavigator1: TDBNavigator;

ADOTable2: TADOTable;

ADOTable3: TADOTable;

DataSource2: TDataSource;

DataSource3: TDataSource;

Events: TDBGrid;

Clans: TDBGrid;

MyQuery1: TADOQuery;

DataSource4: TDataSource;

TabSheet4: TTabSheet;

ListBox1: TListBox;

DBGrid1: TDBGrid;

StatusBar1: TStatusBar;

Button1: TButton;

PopupMenu1: TPopupMenu;

NewQuery1: TMenuItem;

Deletequery1: TMenuItem;

EditQuery1: TMenuItem;

Label1: TLabel;

Edit1: TEdit;

Button2: TButton;

Label2: TLabel;

RichEdit1: TRichEdit;

CheckBox1: TCheckBox;

MyQuery2: TADOQuery;

DataSource5: TDataSource;

ADOTable1Nickname: TStringField;

ADOTable1Clan: TStringField;

ADOTable1GameRace: TStringField;

ADOTable1FullName: TStringField;

ADOTable1Age: TBCDField;

ADOTable1Country: TStringField;

ADOTable2Name: TStringField;

ADOTable2FullName: TStringField;

ADOTable2Owner: TStringField;

ADOTable2Players: TBCDField;

ADOTable2Sponsor: TStringField;

ADOTable2FoundationDate: TBCDField;

ADOTable3Name: TStringField;

ADOTable3Sponsor: TStringField;

ADOTable3Prize: TBCDField;

ADOTable3Clanwinner: TStringField;

ADOTable3Playerwinner: TStringField;

ADOTable3Date: TDateTimeField;

PopupMenu2: TPopupMenu;

Report1: TMenuItem;

Button3: TButton;

Button4: TButton;

Procedure NewEditDelete(i:integer);

procedure NewQuery(Name:string; Query : Trichedit;Dodelete:integer);

procedure PageControl1Change(Sender: TObject);

procedure FormCreate(Sender: TObject);

procedure Button1Click(Sender: TObject);

procedure NewQuery1Click(Sender: TObject);

procedure Deletequery1Click(Sender: TObject);

procedure EditQuery1Click(Sender: TObject);

procedure FormClose(Sender: TObject; var Action: TCloseAction);

procedure Button2Click(Sender: TObject);

procedure ListBox1DblClick(Sender: TObject);

procedure RichEdit1Change(Sender: TObject);

procedure CheckBox1Click(Sender: TObject);

Procedure Normalize(Grid:TDBGrid; Source:TDatasource);

procedure Report1Click(Sender: TObject);

procedure Button3Click(Sender: TObject);

procedure Button4Click(Sender: TObject);

private

{ Private declarations }

public

{ Public declarations }

Name : string;

end;

var

Form1: TForm1;

ini :Tinifile;

implementation

uses Unit2;

{$R *.dfm}

Procedure TForm1.Normalize(Grid:TDBGrid; Source:TDatasource);

var

x:integer;

i:integer;

Begin

// ----------- Normalizing Column Width of DBGrid -----------

For x:=0 to grid.Columns.Count-1 do begin

i:=0;

source.DataSet.First;

repeat

if length(source.DataSet.Fields[x].Text)>i then i:=length(source.DataSet.Fields[x].Text);

source.DataSet.next;

until source.DataSet.Eof;

grid.Columns.Items[x].Width:= i+25;

end;

source.DataSet.First;

end;

Procedure TForm1.NewQuery(Name:string; Query :Trichedit; Dodelete : integer);

// DoDelete = 0 - Add or Edit

// DoDelete = 1 - Delete Query

Var

F : TextFile;

i : Integer;

x : Integer;

begin

AssignFile(F,Extractfilepath(Application.ExeName)+'QueryList.lst');

Rewrite(F);

case DoDelete of

0 : Begin

ini.WriteString(Name,'0',inttostr(richedit1.Lines.Count));

For i:=0 to query.Lines.Count-1 do begin

ini.WriteString(Name,inttostr(i+1),Query.Lines.Strings[i]);

end;

1 : ini.EraseSection(Name);

end;

For x:=0 to Listbox1.Items.Count-1 do begin

Writeln(F,Listbox1.items.strings[x]);

end;

CloseFile(F);

end;

Procedure TForm1.NewEditDelete(i:integer);

// I = 1 - Add Query

// I = 2 - Edit Query

// I = 3 - Delete Query

var

Del:string;

x:integer;

Label 1;

begin

case i of

1 : begin

Listbox1.Items.Add(edit1.Text);

NewQuery(Edit1.Text,richedit1,0);

end;

2 : Begin

For x:=0 to listbox1.Items.Count-1 do begin

If Listbox1.Selected[x] then Listbox1.Items.Strings[x]:=Edit1.Text;

end;

NewQuery(Edit1.Text,richedit1,0);

end;

3 : Begin

For x:=0 to listbox1.Items.Count-1 do begin

If Listbox1.Selected[x] then begin

Del := Listbox1.items.Strings[x];

Listbox1.DeleteSelected;

Goto 1;

end;

1: NewQuery(Del,richedit1,1);

end;

procedure TForm1.PageControl1Change(Sender: TObject);

begin

If Pagecontrol1.ActivePage=TabSheet1 then DBNavigator1.DataSource:=DataSource1;

If Pagecontrol1.ActivePage=TabSheet2 then DBNavigator1.DataSource:=DataSource2;

If Pagecontrol1.ActivePage=TabSheet3 then DBNavigator1.DataSource:=DataSource3;

If Pagecontrol1.ActivePage=TabSheet4 then DBNavigator1.DataSource:=DataSource4;

end;

procedure TForm1.FormCreate(Sender: TObject);

var

F2:TextFile;

i,x:integer;

s:string;

begin

AdoTable1.Active:=true;

AdoTable2.Active:=true;

AdoTable3.Active:=true;

// ----------- Normalizing Column Width of DBGrid -----------

Normalize(players,datasource1);

Normalize(clans,datasource2);

Normalize(events,datasource3);

ini := TiniFile.Create(extractfilepath(application.ExeName)+'Queryes.ini');

AssignFile(F2,Extractfilepath(Application.ExeName)+'QueryList.lst');

reset(F2);

Repeat

Readln(F2,s);

Listbox1.Items.Add(s);

until EOF(F2);

closefile(F2);

If Pagecontrol1.ActivePage=TabSheet1 then DBNavigator1.DataSource:=DataSource1;

If Pagecontrol1.ActivePage=TabSheet2 then DBNavigator1.DataSource:=DataSource2;

If Pagecontrol1.ActivePage=TabSheet3 then DBNavigator1.DataSource:=DataSource3;

If Pagecontrol1.ActivePage=TabSheet4 then DBNavigator1.DataSource:=DataSource4;

end;

procedure TForm1.Button1Click(Sender: TObject);

var

x :integer;

begin

statusbar1.SimpleText:='Adding new Query...';

Edit1.Text:='';

Richedit1.Text:='';

Button2.Caption:='Add';

for x:=125 to form1.Width+120 do begin

DBGrid1.Left:=DbGrid1.Left+1;

Application.ProcessMessages;

end;

procedure TForm1.NewQuery1Click(Sender: TObject);

begin

Button1.Click;

end;

procedure TForm1.Deletequery1Click(Sender: TObject);

begin

NewEditDelete(3);

statusbar1.SimpleText:='Deleted...';

end;

procedure TForm1.EditQuery1Click(Sender: TObject);

var

x,i :integer;

begin

richedit1.Clear;

Button2.Caption:='Edit';

For x:=0 to listbox1.Items.Count-1 do begin

If listbox1.Selected[x] then begin

Edit1.Text:=Listbox1.Items.Strings[x];

statusbar1.SimpleText:='Modifying '+edit1.Text+' Query...';

For i:=0 to strtoint(ini.ReadString(Listbox1.Items.Strings[x],'0',''))-1 do

begin

richedit1.Lines.add(ini.ReadString(Listbox1.Items.Strings[x],inttostr(i+1),''));

end;

for x:=125 to form1.Width+120 do begin

DBGrid1.Left:=DbGrid1.Left+1;

Application.ProcessMessages;

end;

procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);

begin

Ini.Free;

end;

procedure TForm1.Button2Click(Sender: TObject);

Var

x:integer;

begin

If Button2.Caption='Add' then

begin

for x:=0 to listbox1.Items.Count-1 do begin

if edit1.Text=listbox1.Items.Strings[x] then begin

messagedlg('Ïðîèçîøëà îøèáêà, íåâåðíî íàçâàíèå çàïðîñà',mtwarning,[mbok],0);

StatusBar1.SimpleText:='Error adding new Query...';

exit;

end;

NewEditDelete(1);

StatusBar1.SimpleText:='Query '+Edit1.Text+' has been succesfully created...'

end;

If Button2.Caption='Edit' then begin

NewEditDelete(2);

statusbar1.SimpleText:='Query '+edit1.Text+' has been succesfully modifyed...'

end;

for x:=form1.Width+120 downto 125 do begin

DBGrid1.Left:=DbGrid1.Left-1;

Application.ProcessMessages;

end;

procedure TForm1.ListBox1DblClick(Sender: TObject);

var

x,i:integer;

issecond : boolean;

begin

// ----- Organizing Query from selected in Listbox ----

MyQuery1.Active:=false;

MyQuery2.Active:=false;

MyQuery1.SQL.Clear;

MyQuery2.SQL.Clear;

issecond:=false;

For x:=0 to listbox1.Items.Count-1 do begin

If listbox1.Selected[x] then begin

Name:=Listbox1.Items.Strings[x];

For i:=0 to strtoint(ini.ReadString(Listbox1.Items.Strings[x],'0',''))-1 do

begin

if ini.ReadString(Listbox1.items.strings[x],inttostr(i+1),'') = 'Query2' then issecond:=true;

if not ((ini.ReadString(Listbox1.items.strings[x],inttostr(i+1),'') = 'Query2') or (issecond)) then MyQuery1.SQL.Add(ini.ReadString(Listbox1.Items.Strings[x],inttostr(i+1),''));

if not ((ini.ReadString(Listbox1.items.strings[x],inttostr(i+1),'') = 'Query2') or (issecond=false)) then MyQuery2.SQL.Add(ini.ReadString(Listbox1.Items.Strings[x],inttostr(i+1),''));

end;

MyQuery1.Active:=true;

If not (MyQuery2.SQL.text='') then begin

MyQuery2.Active:=true;

DBGrid1.DataSource:=Datasource5;

Normalize(DbGrid1,datasource5);

end

else Normalize(DbGrid1,datasource4);

StatusBar1.SimpleText:='Completed...';

Button4.Enabled:=true;

report1.enabled:=true;

end;

procedure TForm1.RichEdit1Change(Sender: TObject);

var

s,d:string;

x:integer;

Kur:TPoint;

begin

richedit1.SelAttributes.Color:=clblack;

//------------ Making Graphic Design -------------

kur:=Richedit1.CaretPos;

d:=Richedit1.Text;

d:=Lowercase(d);

For x:=1 to length(d) do begin

If (d[x]=' ') or (d[x]='(')or (d[x]=#10) or (ord(d[x])=13) then begin

If (s='from') or (s='where') or (s='group')

or (s='by') or (s='having') or (s='order')

or (s='select') or (s='as') or (s='like')

or (s='update') or (s='set') or (s='sum')

or (s='avg') or (s='max') or (s='min')

or (s='count') then

begin

richedit1.SelStart:=x-length(s)-1;

richedit1.SelLength:=length(s);

richedit1.SelAttributes.Color:=clNavy;

end;

s:='';

end else s:=s+d[x];

end;

Richedit1.CaretPos:=kur;

richedit1.SelAttributes.Color:=clblack

//------------------------------------------------

end;

procedure TForm1.CheckBox1Click(Sender: TObject);

begin

If checkbox1.Checked then begin

Adotable1.Active:=false;

Adotable1.IndexFieldNames:='clan';

Adotable1.MasterFields:='Name';

Adotable1.Active:=true;

end

else begin

Adotable1.Active:=false;

Adotable1.IndexFieldNames:='';

Adotable1.MasterFields:='';

Adotable1.Active:=true;

end;

procedure TForm1.Report1Click(Sender: TObject);

begin

Form2.Show;

end;

procedure TForm1.Button3Click(Sender: TObject);

var

s:string;

begin

if inputquery(‘Введите пароль','пароль',s) then

if s='asd' then begin

deletequery1.Enabled:=true;

editquery1.Enabled:=true;

end;

procedure TForm1.Button4Click(Sender: TObject);

begin

form2.show;

end;

end.

unit Unit2;

interface

uses

Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,

Dialogs, QuickRpt, ExtCtrls, QRCtrls, StdCtrls;

type

TForm2 = class(TForm)

QuickRep1: TQuickRep;

ColumnHeaderBand1: TQRBand;

PageFooterBand1: TQRBand;

PageHeaderBand1: TQRBand;

DetailBand1: TQRBand;

TitleBand1: TQRBand;

Button1: TButton;