[轉貼]http://www.study-area.org/coobila/tutorial_381.html

用SQL合併資料表

阿瑟 發表


今天小弟透過阿閔兄的協助總算把網站導覽列上的分類中文章數目擠出來了, 由於有些SQL的技巧是小弟今天才比較了解的, 可能要再花一些時間整理, 因此先將合併資料表方面的資料整理出來與大家分享.

合併資料表
這邊是教你用SQL去寫, 當然如果你有Access的話也可以用設計檢視去設計合併資料表. 用這個方法合併資料表不會永久合併, 而是在SQL執行的時候建立一份新的資料表來進行兩份資料表的合併工作. 因此不用擔心啦! Access裡面的查詢 (Query) 就可以做到這個功能.

INNER JOIN 方法
Sql = "SELECT * FROM 資料表1 INNER JOIN 資料表2 ON 條件"

SELECT * 的 * 代表所有欄位, 也就是從資料表選取所有欄位.
INNER JOIN的意思是將資料表1與資料表2合併, 而 ON 之後的條件要成立.
這條件怎麼寫呢?
假如說資料表1有一個class欄位而資料表2也有一個class欄位, 那ON你可以寫資料表1.class = 資料表2.class
xxx.yyy , xxx代表的是資料表, yyy代表的是欄位名稱.

現在如果將這整段寫出來的話:
Sql = "SELECT * FROM 資料表1 INNER JOIN 資料表2 ON 資料表1.class = 資料表2.class"

意思也就是如果資料表1的class欄位與資料表2的class欄位數值相等就將他們合併, 如果資料表1的class欄位含有資料表2的class欄位沒有的數值, 就將其剔除, 反推亦同. INNER JOIN的意思就是當兩個資料表的某欄位含有相同的資料再合併, 如果沒有就剔除.

執行上面這段SQL以後就會產生一個同時含有資料表1與資料表2的資料的新資料表, 中間的連結欄位是用兩個資料表的class欄位.

這個技巧可以怎麼使用呢? 就拿文章系統來講, 如果你有一個文章列表, 和一個文章分類.
文章列表中紀錄每一個文章的資料和分類, 而文章分類紀錄著分類資訊, 你可以用這個方法來將兩個資料表暫時合併 (在SQL被執行時合併) 來同時存取兩個資料表的資料.

RIGHT JOIN 方法
剛剛的 INNER JOIN是很棒沒有錯, 但是如果今天有一個文章分類裡面沒有任何的文章, 那由於文章列表中沒有對應的分類資料, 該分類就會被剔除...這...不大對吧?! 因此還有RIGHT JOIN的方法.
Sql = "SELECT * FROM 資料表1 RIGHT JOIN 資料表2 ON 資料表1.class = 資料表2.class"
其實大致上和剛剛是一樣的, 只是RIGHT JOIN會以資料表2, 也就是運算子的右邊的資料表為重, RIGHT JOIN會將所有右邊資料表 (也就是資料表2) 的資料都加進來, 而只將左邊資料表 (資料表1) 符合的資料加進來.

這樣在做文章分類清單的時候, 就算一個文章列表裡面沒有任何文章, 那該分類仍然會顯示出來.

LEFT JOIN 方法
有RIGHT JOIN, 當然就有 LEFT JOIN...
Sql = "SELECT * FROM 資料表1 LEFT JOIN 資料表2 ON 資料表1.class = 資料表2.class"

其實就是一樣的意思啦, 之前的RIGHT JOIN是包含所有右邊資料表的內容, 那LEFT JOIN當然就是包含所有左邊資料表的內容囉!

只要記得:
# INNER JOIN - 兩邊的資料表擁有相同的優先權, 要兩邊都有的資料才會被包含在新的資料表.
# RIGHT JOIN - 右邊的資料表擁有優先權, 右邊所有的資料都會被包含, 而左邊只有符合的資料才會被包含.
# LEFT JOIN - 左邊的資料表擁有優先權, 左邊所有的資料都會被包含, 而右邊只有符合的資料才會被包含.

感謝阿閔兄的指導

最後更新日期: 1/3/2003 7:03:33 PM

[轉貼 http://blog.darkhero.net/?p=166]

於是寫了一個簡單的函式，處理討論區新增會員的功能。

/**
* 新增 phpbb2 使用者
*
* @param string $username
* @param string $password
* @param string $email
*/
function add_phpbb2_user($username,$password,$email){
// 建立 phpbb2 資料庫連線物件
define(’IN_PHPBB’, true);
$phpbb_root_path = ‘./forum/’;
include_once($phpbb_root_path."config.php");
include_once($phpbb_root_path."extension.inc");
include_once($phpbb_root_path."includes/constants.php");
include_once($phpbb_root_path."includes/db.php");

// auth OK
$sql = "SELECT user_id, username, user_password, user_active, user_level FROM " . USERS_TABLE .
" WHERE username = ‘" . str_replace("’", "”", $username) . "’";
if ( !($result = $db->sql_query($sql)) )
{
message_die(GENERAL_ERROR, ‘Error in obtaining userdata’, ”, __LINE__, __FILE__, $sql);
}
if( !($row = $db->sql_fetchrow($result)) ) {
// no data found, first login
$sql = "SELECT MAX(user_id) AS total FROM " . USERS_TABLE;
if ( !($result = $db->sql_query($sql)) )
{
message_die(GENERAL_ERROR, ‘Could not obtain next user_id information’, ”, __LINE__, __FILE__, $sql);
}

if ( !($row = $db->sql_fetchrow($result)) )
{
message_die(GENERAL_ERROR, ‘Could not obtain next user_id information’, ”, __LINE__, __FILE_, $sql);
}
$user_id = $row['total'] + 1;

$sql = "INSERT INTO " . USERS_TABLE . " (" .
"user_id," .
"username," .
"user_regdate," .
"user_password," .
"user_email," .
"user_icq," .
"user_website," .
"user_occ," .
"user_from," .
"user_interests," .
"user_sig," .
"user_sig_bbcode_uid," .
"user_avatar," .
"user_avatar_type," .
"user_viewemail," .
"user_aim," .
"user_yim," .
"user_msnm," .
"user_attachsig," .
"user_allowsmile," .
"user_allowhtml," .
"user_allowbbcode," .
"user_allow_viewonline," .
"user_notify," .
"user_notify_pm," .
"user_popup_pm," .
"user_timezone," .
"user_dateformat," .
"user_lang," .
"user_style," .
"user_level," .
"user_allow_pm," .
"user_active," .
"user_actkey" .
") VALUES (" .
"$user_id," .
"’" . str_replace("’", "”", $username) . "’," .
time() . "," .
"’" . str_replace("’", "”", md5($password)) . "’," .
"’" . str_replace("’", "”", $email) . "’," .
"’" . str_replace("’", "”", "") . "’," .
"’" . str_replace("’", "”", "") . "’," .
"’" . str_replace("’", "”", "") . "’," .
"’" . str_replace("’", "”", "") . "’," .
"’" . str_replace("’", "”", "") . "’," .
"’" . str_replace("’", "”", "") . "’," .
"”," .
"”," .
"0," .
"0," .
"’" . str_replace("’", "”", str_replace(’ ‘, ‘+’, "")) . "’," .
"’" . str_replace("’", "”", "") . "’," .
"’" . str_replace("’", "”", "") . "’," .
"1," .
"1," .
"1," .
"1," .
"1," .
"0," .
"0," .
"1," .
"8.00," .
"’" . str_replace("’", "”", "D M d, Y g:i a") . "’," .
"’" . str_replace("’", "”", "chinese_traditional_taiwan") . "’," .
"1," .
"0," .
"1," .
"1," .
"”" .
")";
echo $sql;
if ( !($result = $db->sql_query($sql, BEGIN_TRANSACTION)) )
{
message_die(GENERAL_ERROR, ‘Could not insert data into users table’, ”, __LINE__, __FILE__, $sql);
}

$sql = "INSERT INTO " . GROUPS_TABLE . " (group_name, group_description, group_single_user, group_moderator)" .
" VALUES (”, ‘Personal User’, 1, 0)";

echo $sql;
if ( !($result = $db->sql_query($sql)) )
{
message_die(GENERAL_ERROR, ‘Could not insert data into groups table’, ”, __LINE__, __FILE__, $sql);
}

$group_id = $db->sql_nextid();

$sql = "INSERT INTO " . USER_GROUP_TABLE . " (user_id, group_id, user_pending)" .
" VALUES ($user_id, $group_id, 0)";

echo $sql;
if( !($result = $db->sql_query($sql, END_TRANSACTION)) )
{
message_die(GENERAL_ERROR, ‘Could not insert data into user_group table’, ”, __LINE__, __FILE__, $sql);
}
}
}

error creating cursor handle [轉貼自: http://blog.roodo.com/techcity/archives/4963939.html]

造成error creating cursor handle的原因？(TQuery元件使用Open()與ExecSQL()的時機與差異)

使用元件

TQuery、DBGrid、DataSource、BitBtn


原理解說

常會有使用者有這樣的疑問，為什麼我用TQuery及TADOQuery元件時，做新增(Insert)、刪除(Delete)、更新(Update)會出現錯誤？而做查詢(Select)時卻不會出現錯誤？

一般可以將資料操作語言分成四種：分別是新增(Insert)、刪除(Delete)、更新(Update)、查詢(Select)。

而以上四種僅只有Select會要求資料庫回傳資料，即便回傳的是空資料也行，另外其餘三種(Insert、Delete、Update)僅是要求資料庫執行某些動作，並不會回傳資料。

或許會有讀者說，我沒有使用Open()和ExecSQL()，我只使用Active = true，實際上這個動作做的就是Open()的動作。

設計步驟

建立一個新的Project。

* 1 放入一個TQuery元件，設定它的DatabaseName屬性為BCDEMOS。
* 2 放入一個TDataSource元件，設定它的DataSet屬性為Query1。
* 3 放入一個TDBGrid元件，設定它的DataSet屬性為Query1。
* 4 四個TBitBtn元件，設定四者的Caption屬性分別改為Insert、Delete、Update、Select，設定四者的Name屬性分別為 BitBtn_Insert、BitBtn_Delete、BitBtn_Update、BitBtn_Select。
* 5 程式實例：

void __fastcall TForm1::BitBtn_InsertClick(TObject *Sender) // 新增

{

try

{

Query1->Close();

Query1->SQL->Clear();

Query1->SQL->Text = "Insert into employee (EmpNo, LastName, FirstName, PhoneExt, Salary) values (200 , 'Bruce', 'White', '100', 40000) ";

Query1->ExecSQL();

ShowMessage("Success");

}

catch(...)

{

ShowMessage("Failure");

}

}

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

void __fastcall TForm1::BitBtn_DeleteClick(TObject *Sender) // 刪除

{

try

{

Query1->Close();

Query1->SQL->Clear();

Query1->SQL->Text = "Delete from employee where EmpNo = '200' ";

Query1->ExecSQL();

ShowMessage("Success");

}

catch(...)

{

ShowMessage("Failure");

}

}

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

void __fastcall TForm1::BitBtn_UpdateClick(TObject *Sender) // 更新

{

try

{

Query1->Close();

Query1->SQL->Clear();

Query1->SQL->Text = "Update employee set LastName = 'Ben' where EmpNo = '200' ";

Query1->ExecSQL();

ShowMessage("Success");

}

catch(...)

{

ShowMessage("Failure");

}

}

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

void __fastcall TForm1::BitBtn_SelectClick(TObject *Sender) // 查詢

{

try

{

Query1->Close();

Query1->SQL->Clear();

Query1->SQL->Text = "Select * from employee ";

Query1->Open();

ShowMessage("Success");

}

catch(...)

{

ShowMessage("Failure");

}

}

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

[轉貼]

Problem:
What is the TBatchMove component and how do I use it with InterBase (or any other database)?

Solution:
An Introduction to the TBatchMove component
=====================================

1. What is it?
TBatchMove is a VCL component that lets you perform operations on groups of records in a table.
The operations are named with intuitive names that basically tell what they do.
Listed below are the operations or "modes" that the TBatchMove component supports.

- batAppend => appends all records from the source table to the end of the destination
table.

- batAppendUpdate => Appends all records from the source table to the end of the destination
table and updates those records with the same primary index in the
destination table with the same records from the source table.

- batCopy => Copies a table and its records to a new table

- batDelete => Delete all records in the destination table that also appear in the source table.

- batUpdate => Update existing records in the destination table with their counterparts
in the source table.



2. How do I use it?
Listed below are steps to setup and run a TBatchMove component to upsize Paradox data to
InterBase.

a. Put two TTable objects on the form, one for source and one for destination.
1. Click on "Data Access" tab.

2. Click on table icon.

3. Click on form.

4. Press to go to the Object Inspector.

5. Define the Database property for table1 to be the BDE alias that points to the directory
where Paradox tables are located. In this case, we are using the DBDEMOS alias
that is installed by default when you do a full install of Delphi 3.

6. Define the TableName property in object inspector for table1 to be the Paradox table name,
In this example, the table name will be the "orders" sample table that comes with
Delphi. Since we have already specified the location of the tables, you can easily
fill in the TableName property setting by selecting the table from the picklist of the TableName
property.

7. Repeat steps 2.a.1 - 2.a.4 for table2

8. Define the database on table2 to be an alias that points to the InterBase database, in this case
employee.gdb, by selecting the BDE alias that points to the employee database.

9. Define TableName property for table2 to be the table name you want created in
InterBase. For simplicity, call it "orders" as well.

10. Configre the TBatchMove component
a. Put the TBatchMove component on the form.
1. Click on the Mode property's edit box.

2. Click on the BatchMove icon.

3. Click on the form.

b. Define the source and destination for TBatchMove
1. Hit to go to the object inspector.

2. Set the Source property of TBatchMove to be table1.

3. Set the Destination property of TBatchMove to be table2.

d. Specify the mode for TBatchMove
1. Click on the Mode property's edit box.
2. Click on the down arrow that appears to get a picklist of modes to choose from.
3. Click on "batCopy" to select it from the list.

d. Activate TBatchMove
1. Right click on TBatchMove component
2. Click on "Execute" on the pop-up menu that appears.

e. What happens when TBatchMove fails?

You can tell TBatchMove to document what happened when you run a batch move operation
by setting the following properties. They tell Delphi to create a local Paradox table containing
records that were not successfully part of the batchmove operation.

ChangedTablename => Records that were modified by the batch move operation

KeyvioltableName => Records that could not be added because they violated a uniqueness
constraint.

ProblemTableName => Records that could not be added for one reason or another.

Paradox type compared with mssql

Value Sql Description

ftUnknown Unknown or undetermined
ftString char Character or string field
ftSmallint smallint 16-bit integer field
ftInteger int 32-bit integer field
ftWord tinyint 16-bit unsigned integer field
ftBoolean bit Boolean field
ftFloat float、real Floating-point numeric field
ftCurrency Money field
ftBCD decimal、money、 Binary-Coded Decimal field
numeric、smallmoney
ftDate Date field
ftTime Time field
ftDateTime datetime、smalldatetime Date and time field
ftBytes binary、timestamp Fixed number of bytes (binary storage)
ftVarBytes varbinary Variable number of bytes (binary storage)
ftAutoInc Auto-incrementing 32-bit integer counter field
ftBlob image Binary Large OBject field
ftMemo text Text memo field
ftGraphic Bitmap field
ftFmtMemo Formatted text memo field
ftParadoxOle Paradox OLE field
ftDBaseOle dBASE OLE field
ftTypedBinary Typed binary field
ftCursor Output cursor from an Oracle stored procedure (TParam only)
ftFixedChar Fixed character field
ftWideString nchar、nvarchar Wide string field
ftLargeInt bigint Large integer field
ftADT Abstract Data Type field

ftArray Array field
ftReference REF field
ftDataSet DataSet field
ftOraBlob BLOB fields in Oracle 8 tables
ftOraClob CLOB fields in Oracle 8 tables
ftVariant sql_variant Data of unknown or undetermined type
ftInterface References to interfaces (IUnknown)
ftIDispatch References to IDispatch interfaces
ftGuid uniqueidentifier globally unique identifier (GUID) values

[Delphi] insert into paradox

procedure TForm1.Button2Click(Sender: TObject);
var
s:string;
i : integer;


begin
// status bar
ProgressBar1.Position := 0;
ProgressBar1.Max := 100;
// end of status bar

adoquery1.close;
adoquery1.SQL.clear;
s:= trim(memo1.text);
adoquery1.SQL.Add(s);
adoquery1.open;
createDB('c:\','sucks.db');

{
for i := 0 to 100 do
begin
ProgressBar1.Position := i;
Sleep(25);
//Application.ProcessMessages;
}

// start of sql query
// while not adoquery1.Eof do
// begin

Query1.Close;
Query1.SQL.Clear;
// Query1.SQL.Add('Insert Into "c:\test3.db" ' + ' Values(:s_date,:prn_code,:sec_code,:cat_code,:brand_code,:copyline_code,:ver,:paper,:color_type,:page,:week,:s_cost,:sp,:remark,:page_1,:ver_1)');

Query1.SQL.Add('Insert Into "c:\test3.db" (brand_code)' + ' values(:brand_code)');

Query1.ParamByName('brand_code').asstring := 'aaa';
{
Query1.ParamByName('s_date').AsDate := adoquery1.FieldByName('adsdate').AsDateTime;
Query1.ParamByName('Prn_CODE').asstring := adoquery1.FieldByName('engmedianame').AsString;
Query1.ParamByName('Sec_Code').asString := adoquery1.FieldByName('sectioncode').AsString;
Query1.ParamByName('cat_code').asString := adoquery1.FieldByName('categorycode').AsString;
Query1.ParamByName('Brand_Code').asstring := adoquery1.FieldByName('brandcode').AsString;
Query1.ParamByName('Copyline_CODE').asstring := adoquery1.FieldByName('copylinecode').AsString;
Query1.ParamByName('paper').AsInteger := adoquery1.FieldByName('printtypecode').AsInteger;
Query1.ParamByName('color_type').AsInteger := adoquery1.FieldByName('printcolorcode').AsInteger;
Query1.ParamByName('page').asstring := adoquery1.FieldByName('pageno').AsString;
Query1.ParamByName('s_cost').AsCurrency := adoquery1.FieldByName('cost').AsInteger;
}
Query1.ExecSQL;
// Query1.Close;
// Query1.Next;
// query1.sql.savetofile('c:\sql.txt');
// end; // end of while

// end; // end of for loop


end;

paradox symbol called

Paradox
Paradox 8 help provides summaries of the fields provided by

dBase Informix Interbase Oracle Sybase

and the rules that apply to each. To get this, search help for alphanumeric fields, adding, then click Related Topics

Field names can be up to 25 characters long.

There are 17 field types in version 7/8. Search help for field types and select Paradox field types and sizes.

Type Symbol Size
Alpha A Up to 255 characters
Memo M No limit
Formatted Memo F No limit
Graphic G .BMP, .PCX, .TIF, .GIF, or .EPS, but stored in separate files in bmp format.
Binary B Raw data of any size.
Bytes Y 1 to 255 bytes of raw data.
timestamp @ Contains both Data and Time
Date D January 1, 9999 BC to December 31, 9999 AD
Time T Number of milliseconds since midnight
Autoincrement + 4 bytes, Stored as a long integer. These increment by one each time a new record is added.
Logical L Size is not specified. By default displayed as True/False.
OLE O Word document, Spreadsheet, Picture, Sound, etc.

Numbers - the help does not always include the number of bytes used.

Type Symbol Bytes Digits Range
short S 2 4 +/- 32K
long integer I 4 9 +/- 2E9
number* N 8 15 +/- 10K
Money $ unk Size is not specified. Precision maintained to 6 decimal places.
BCD # unk 15 na

* - the number type has 15 significant figures, but a very small range

The following blob fields are stored in a separate file (.mb).

Memo FormattedMemo Graphic OLE Binary

This allows them to be any size while the table file (.db) contains only fixed size records.