linux程式設計——執行SQL語句（第八章）

標籤：

8.3    使用C語言訪問MySQL資料4.處理返回的資料
MySQL返回兩種類型的資料
1.從表中提取的資訊,也就是列資料
2.關於資料的資料,即所謂的中繼資料(metadata),例如列名和類型.
mysql_field_count函數提供了一些關於查詢結果的基本資料,它接受連線物件,並返回結果集中的欄位(列)數目:
unsigned int mysql_field_count(MYSQL *connection);
在更通用的情況下,可以用mysql_field_count做其他事情,比如判斷為何mysql_store_result的調用會失敗.例如,如果mysql_store_result返回,但是mysql_field_count返回一個正數,可以推測出這個是一個提取錯誤.但是如果mysql_field_count返回0,則表示沒有列可以提取,這可以解釋為何儲存結果會失敗.對於通用查詢處理模組或任何隨意構造查詢的情況,這個函數都非常有用.
編寫程式select3.c輸出資料
輸出的資料不是很整潔,如果需要格式化的資料,則要同時得到MySQL返回的資料和中繼資料.使用mysql_fetch_field來同時將中繼資料和資料提取到一個新的結構中:
MYSQL_FIELD *mysql_fetch_field(MYSQL_RES *result);
需要重複調用此函數,直到返回表示資料結束的NULL值為止.然後,可以使用指向欄位結構的資料的指標來得到關於列的資訊.結構MYSQL_FIELD定義在mysql.h中.
MYSQL_FIELD中的成員                說明
char *name;                        列名,為字串
char *table;                    列所屬的表名.當一個查詢要使用多個表時,特別有用.
char *def;                        如果調用mysql_list_fields,它將包含該列的預設值
enum enum_field_types type;        列類型
unsigned int length;            列寬
unsigned int max_length;        如果使用mysql_store_result,它將包含以位元組為單位的提取的最長列值的長度
unsigned int flags;                列定義的標誌    
unsigned int decimals;            小數點後的數字個數.
此外,使用下面的函數覆蓋當前的欄位編號:
MYSQL_FIELD_OFFSET mysql_field_seek(MYSQL_RES *result, MYSQL_FIELD_OFFSET offset);
該編號會隨每次mysql_fetch_field調用而自動增加.如果給參數offset傳遞值0,將跳回第一列.
編寫程式select4.c
8.3.4更多的函數樣本API調用                                        說明
char *mysql_get_client_info(void);                返回用戶端使用的庫的版本資訊
char *mysql_get_host_info(MYSQL *connection);    返回伺服器串連資訊
char *mysql_get_server_info(MYSQL *connection);    返回當前已連線的服務器的資訊
char *mysql_info(MYSQL *connection);            返回最近執行的查詢資訊
int mysql_select_db(MYSQL *connection, const char *dbname);
如果使用者擁有許可權，則把預設資料庫改為參數指定的資料庫
int mysql_shutdown(MYSQL *connection, enum mysql_enum_shutdown_level);
如果使用者擁有許可權，則關閉串連的資料庫伺服器。
8.4    CD資料庫應用程式建立一個新的資料庫，然後將其作為當前的資料庫：
create database blpcd;
use blpcd;
只考慮資料的兩種最重要的關係
首先，每張CD由不同數目的曲目組成，所以將把曲目資料存在一個獨立於其他CD資料的表中。
其次，每位藝術家經常有多張專輯，所以將藝術家的資訊儲存一次，然後單獨提取屬於該藝術家的所有CD。
8.4.1 建立表需要去確定表的實際結構。從主表——CD表開始，它儲存大部分的資訊。需要儲存一個CD ID、一個分類號、一個標題以及一些標註。還需要一個來自artist表的ID號來表明是哪位藝術家製作了這張專輯。
artist表很簡單，它僅僅儲存藝術家的名字和一個唯一的藝術家ID號。track表也很簡單，只需要一個CD ID來表明曲目屬於哪張CD、一個曲目號和一個曲目標題。
首先是CD表：
CREATE TABLE cd(
        id int auto_increment NOT NULL primary key,
        title varchar(70) NOT NULL,
        artist_id int NOT NULL,
        catalogue varchar(30) NOT NULL,
        notes varchar(100)
);
建立表cd,它包含下面一些列：
id列，包含一個自動增加的整數，它是表的主鍵
最長為70個字元的title
artist_id，在artist表中使用的一個整數
最長為30個字元的catalogue號
最長為100個字元的notes
只有notes列可以為NULL，其他列都必須包含值
其次是artist表：
CREATE TABLE artist(
        id int auto_increment NOT NULL primary key,
        name varchar(100) NOT NULL
);
最後是track表：
CREATE TABLE track(
        cd_id int NOT NULL,
        track_id int NOT NULL,
        title varchar(70),
        primary key(cd_id, track_id)
);
這次使用不同的方法來聲明主鍵，track表的不同之處在於每張CD的ID會出現多次，而對於任何指定曲目的ID，例如曲目1，也會在不同CD中出現多次。但是，這兩者的結合將永遠是唯一的，所以將主鍵聲明為這兩列的結合，這被稱為聯合鍵。
將這些SQL語句儲存在檔案create_table.sql中，並將該檔案儲存在目前的目錄中，然後開始建立資料庫以及其中的表。
8.4.2 添加資料編寫insert_data.sql檔案，使用\.命令執行。
注意這個檔案將刪除資料庫blpcd中所有的資料以確保指令碼是乾淨的
此外，這個檔案在ID欄位中插入數值，而不是讓auto_increment自動分配的。
使用mysql命令用戶端和一些SQL語句來檢查。首先，從資料庫中選出每張專輯的頭兩首曲目：
SELECT artist.name, cd.title AS "CD Title", track.track_id, track.title AS "Track" FROM artist, cd, track WHERE artist.id = cd.artist_id AND track.cd_id = cd.id AND track.track_id < 3;
這條SQL語句的第一部分是:
SELECT artist.name, cd.title, track.track.id, track.title
它通過使用標記tablename.column來說明想要顯示哪些列。
SELECT語句的AS部分SELECT artist.name, cd.title AS "CD Title", track.track_id和track.title AS "Track"只是在輸出時重新命名列名。因此，來自cd表的title列（cd.title)的標題列被命名為"CD Title"，track.title列被命名為"Track"。AS的使用給了更好的輸出，它是在命令列中針對SQL語句的一個有用的字句，但是當通過其他程式設計語言來調用SQL語句時，幾乎不會用到它。
FROM artist, cd, track
它告訴伺服器使用的表名
WHERE子句是需要點技巧的部分：
WHERE artist.id = cd.artist_id AND track.cd_id = cd.id AND track.track_id < 3
第一部分告訴伺服器artist表中的ID應與cd表中的artist_id相同。第二部分告訴伺服器track表的cd_id列應該與cd表中的id列相同，第三部分track.track_id<3減少了返回資料的數量以使得僅僅從每張CD中得到曲目1和曲目2.最後，使用AND把3個條件結合起來，因為需要這3個條件同時都為真。
8.4.3 使用C語言訪問資料編寫類似於CD應用程式的一個常見問題是無法知道返回的結果數，以及如何在用戶端代碼和訪問資料庫的代碼間傳遞這些結果。在這個應用程式中，為了保持簡單並專註於資料庫介面，將使用固定大小的結構。但在實際的程式中，這可能是不能接受的。一種常見的解決辦法（它同時也有助於減少網路流量）是每次總是提取一行資料，正如前面介紹的mysql_use_result和mysql_fetch_row一樣。
1.介面定義
先從標頭檔app_mysql.h開始，它定義了結構和函數：
首先是一些結構：
/* A simplistic structure to represent the current CD, excluding the track information */
struct current_cd_st{
    int artist_id;
    int cd_id;
    char artist_name[100];
    char title[100];
    char catalogue[100];
};
/* A simplistic track details structure */
struct current_tracks_st{
    int cd_id;
    char track[20][100];
};
#define MAX_CD_RESULT 10
struct cd_search_st{
    int cd_id[MAX_CD_RESULT];
}
然後是一對函數，它們用於串連資料庫以及從資料庫中斷連線
/* Database backend functions */
int database_start(char *name, char *password);
void database_end();
現在，轉向操縱資料的函數，注意，沒有建立或三處藝術家的函數，將在後台實現它，根據需要建立藝術家條目，然後當它們不在被任何專輯使用的時候將其刪除。
/* Functions for adding a CD */
int add_cd(char *artist, char *title, char *catalogue, int *cd_id);
int add_tracks(struct current_tracks_st *track);
/* Functions for finding and retrieving a CD */
int find_cds(char *search_str, struct cd_search_st *results);
int get_cd(int cd_id, struct current_cd_st *dest);
int get_cd_tracks(int cd_id, struct current_tracks_st *dest);
/* Function for deleting items */
int delete_cd(int cd_id);
搜尋函數相當通用：當傳遞一個字串，然後它將在artist、title或catalogue條目中搜尋該字串。
2.測試應用程式介面
在實現介面之前，先編一些代碼使用它，這看起來有點奇怪，但在開始實現介面之前瞭解一下它將如何運轉通常是個好辦法。
下面是app_test.c的原始碼，首先是一些includes和structs：
#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#include "app_mysql.h"
int main(){
    struct current_cd_st cd;
    struct cd_search_st cd_res;
    struct current_tracks_st ct;
    int cd_id;
    int res, i;
    /* 應用程式的第一件事始終是初始化一個資料庫連接並提供一個正確的使用者名稱和密碼 */
    database_start("rick", "secret");
    /* 測試添加一張CD */
    res = add_cd("Mahler", "Symphony No 1", "4596102", &cd_id);
    printf("Result of adding a cd was %d, cd_id is %d\n", res, cd_id);

    memset(&ct, 0, sizeof(ct));
    ct.cd_id = cd_id;
    strcpy(ct.track[0], "Langsam Schleppend");
    strcpy(ct.track[1], "Krafig bewag");
    strcpy(ct.track[2], "Feierlich und gemeseen");
    strcpy(ct.track[3], "Sturmish bewegt");
    add_tracks(&ct);
    /* 現在搜尋CD,並從找到的第一張CD中提取資訊 */
    res = find_cds("Symphony", &cd_res);
    printf("Found %d cds, first has ID %d\n", res, cd_res.cd_id[0]);

    res = get_cd(cd_res.cd_id[0], &cd);
    printf("get_cd returned %d\n", res);

    memset(&ct, 0, sizeof(ct));
    res = get_cd_tracks(cd_res.cd_id[0], &ct);
    printf("get_cd_tracks returned %d\n", res);
    printf("Title: %s\n", cd.title);
    i = 0;
    while(i < res){
        printf("\ttrack %d is %s\n", i, ct.track[i]);
        i++;
    }

    /* 最後刪除CD */
    res = delete_cd(cd_res.cd_id[0]);
    printf("Delete_cd returned %s\n", res);
    /* 中斷連線並退出 */
    database_end();
    return EXIT_SUCCESS;
}
3.實現介面
實現指定的介面，這些包含在檔案app_mysql.c中
首先是一些基本的includes、需要的全域串連結構和一個標誌dbconnected，使用它來確保程式不會在沒有建立串連的情況下嘗試訪問資料。

linux程式設計——執行SQL語句（第八章）