Linux数据库大比拚(3)

DROP TABLE BOOK;
CREATE TABLE BOOK (
ARTICLE_NO INTEGER PRIMARY KEY,
AUTHOR_FIRST_NAMES CHARACTER(30),
AUTHOR_LAST_NAMES CHARACTER(30),
TITLE CHARACTER(30),
ISBN CHARACTER(13) UNIQUE,
WHOLESALE_PRICE INTEGER,
RETAIL_PRICE INTEGER,
COPIES_AVAILABLE INTEGER
);
DROP TABLE CUSTOMER;
CREATE TABLE CUSTOMER (
CUSTOMER_NO INTEGER PRIMARY KEY,
FIRST_NAMES CHARACTER(30),
LAST_NAMES CHARACTER(30),
STREET CHARACTER(30),
HOUSE_NO SMALLINT,
POSTCODE CHARACTER(7),
TOWN CHARACTER(30),
ISO_COUNTRY_CODE CHARACTER(2)
);
DROP TABLE BOOKORDER;
CREATE TABLE BOOKORDER (
ORDER_NO INTEGER PRIMARY KEY,
CUSTOMER_NO INTEGER NOT NULL,
ORDERED DATE,
DELIVERY DATE,
STATUS CHARACTER(8)
CHECK (STATUS IN (′ACCEPTED′,
′DELAYED′,
′SHIPPED′,
′RETURNED′,
′PAID′)),
CONSTRAINT CUSTOMER_FK FOREIGN KEY (CUSTOMER_NO)
REFERENCES KUNDE (KUNDENNAME)
);
DROP TABLE ORDER_POSITION;
CREATE TABLE ORDER_POSITION (
POSITION_NO INTEGER PRIMARY KEY,
ORDER_NO INTEGER NOT NULL,
ARTICLE_NO INTEGER NOT NULL,
NUMBER SMALLINT,
CONSTRAINT ORDER_FK FOREIGN KEY (ORDER_NO)
REFERENCES BOOKORDER (ORDER_NO),
CONSTRAINT BOOK_FK FOREIGN KEY (ARTICLE_NO)
REFERENCES BOOK (ARTICLE_NO)
);
DROP TABLE RATING;
CREATE TABLE RATING (
RATING_NO INTEGER PRIMARY KEY,
ARTICLE_NO INTEGER NOT NULL,
SCORE SMALLINT,
COMMENT CHARACTER VARYING(255),
CONSTRAINT BOOK_FK FOREIGN KEY (ARTICLE_NO)
REFERENCES BOOK (ARTICLE_NO)
);

DROP TABLE BOOK;
CREATE TABLE BOOK (
ARTICLE_NO INTEGER PRIMARY KEY,
AUTHOR_FIRST_NAMES CHARACTER(30),
AUTHOR_LAST_NAMES CHARACTER(30),
TITLE CHARACTER(30),
ISBN CHARACTER(13),
WHOLESALE_PRICE INTEGER,
RETAIL_PRICE INTEGER,
COPIES_AVAILABLE INTEGER
);
DROP TABLE CUSTOMER;
CREATE TABLE CUSTOMER (
CUSTOMER_NO INTEGER PRIMARY KEY,
FIRST_NAMES CHARACTER(30),
LAST_NAMES CHARACTER(30),
STREET CHARACTER(30),
HOUSE_NO SMALLINT,
POSTCODE CHARACTER(7),
TOWN CHARACTER(30),
ISO_COUNTRY_CODE CHARACTER(2)
);
DROP TABLE BOOKORDER;
CREATE TABLE BOOKORDER (
ORDER_NO INTEGER PRIMARY KEY,
CUSTOMER_NO INTEGER NOT NULL,
ORDERED DATE,
DELIVERY DATE,
STATUS CHARACTER(8),
CONSTRAINT CUSTOMER_FK FOREIGN KEY (CUSTOMER_NO)
REFERENCES KUNDE (KUNDENNAME)
);
DROP TABLE ORDER_POSITION;
CREATE TABLE ORDER_POSITION (
POSITION_NO INTEGER PRIMARY KEY,
ORDER_NO INTEGER NOT NULL,
ARTICLE_NO INTEGER NOT NULL,
NUMBER SMALLINT,
CONSTRAINT ORDER_FK FOREIGN KEY (ORDER_NO)
REFERENCES BOOKORDER (ORDER_NO),
CONSTRAINT BOOK_FK FOREIGN KEY (ARTICLE_NO)
REFERENCES BOOK (ARTICLE_NO)
);
DROP TABLE RATING;
CREATE TABLE RATING (
RATING_NO INTEGER PRIMARY KEY,
ARTICLE_NO INTEGER NOT NULL,
SCORE NUMERIC(1,0),
COMMENT CHARACTER VARYING(255),
CONSTRAINT BOOK_FK FOREIGN KEY (ARTICLE_NO)
REFERENCES BOOK (ARTICLE_NO)
);

DROP TABLE BOOK
CREATE TABLE BOOK (
ARTICLE_NO INTEGER NOT NULL,
AUTHOR_FIRST_NAMES CHARACTER(30),
AUTHOR_LAST_NAMES CHARACTER(30),
TITLE CHARACTER(30),
ISBN CHARACTER(13),
WHOLESALE_PRICE MONEY,
RETAIL_PRICE MONEY,
COPIES_AVAILABLE INTEGER
)
DROP TABLE CUSTOMER
CREATE TABLE CUSTOMER (
CUSTOMER_NO INTEGER NOT NULL,
FIRST_NAMES CHARACTER(30),
LAST_NAMES CHARACTER(30),
STREET CHARACTER(30),
HOUSE_NO SMALLINT,
POSTCODE CHARACTER(7),
TOWN CHARACTER(30),
ISO_COUNTRY_CODE CHARACTER(2)
)
DROP TABLE BOOKORDER
CREATE TABLE BOOKORDER (
ORDER_NO INTEGER NOT NULL,
CUSTOMER_NO INTEGER NOT NULL,
ORDERED DATE,
DELIVERY DATE,
STATUS CHARACTER(1)
)
DROP TABLE ORDER_POSITION
CREATE TABLE ORDER_POSITION (
POSITION_NO INTEGER NOT NULL,
ORDER_NO INTEGER NOT NULL,
ARTICLE_NO INTEGER NOT NULL,
NUMBER SMALLINT
)
DROP TABLE RATING
CREATE TABLE RATING (
RATING_NO INTEGER NOT NULL,
ARTICLE_NO INTEGER NOT NULL,
SCORE SMALLINT,
COMMENT TEXT(255)
)

void list_books(void)
{
EXEC SQL BEGIN DECLARE SECTION;
int article_no;
char author_first_names[30];
char author_last_names[30];
char title[30];
char isbn[14];
int wholesale_price;
int retail_price;
int copies_available;
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE book_cursor CURSOR FOR
SELECT ARTICLE_NO, AUTHOR_FIRST_NAMES, AUTHOR_LAST_NAMES,
TITLE, ISBN, WHOLESALE_PRICE, RETAIL_PRICE,
COPIES_AVAILABLE FROM BOOK;
EXEC SQL OPEN book_cursor;
while (1)
{
EXEC SQL FETCH NEXT FROM book_cursor
INTO :article_no, :author_first_names, :author_last_names,
:title, :isbn, :wholesale_price, :retail_price,
:copies_available;
if (sqlca.sqlcode == 100) /* 100 == NOT FOUND */
break; /* bail out */
printf("\nArticle no. %d\n", article_no);
printf("%s, %s:\n", author_last_names, author_first_names);
printf(" %s (%s)\n", title, isbn);
printf("Bought at %d; selling at %d; %d copies available\n\n",
wholesale_price, retail_price, copies_available);
};
EXEC SQL CLOSE book_cursor;
}

list_books(void)
{
int count;
MYSQL_RES *result;
mysql_query(&bookstore, "SELECT ARTICLE_NO, AUTHOR_FIRST_NAMES,AUTHOR_LAST_NAMES, TITLE, ISBN, WHOLESALE_PRICE, RETAIL_PRICE,COPIES_AVAILABLE FROM BOOK");
result = mysql_store_result(&bookstore);
for(count = mysql_num_rows(result); count > 0; count--)
{
MYSQL_ROW record;
record = mysql_fetch_row(result);
printf("\nArticle no. %s\n", record[0]);
printf("%s, %s:\n", record[2], record[1]);
printf(" %s (%s)\n", record[3], record[4]);
printf("Bought at %s; selling at %s; %s copies available\n\n",
record[5], record[6], record[7]);
};
mysql_free_result(result);
}
mysql_free_result ( 结果 );
}

void
list_books(void)
{
int count;
m_result *result;
msqlQuery(bookstore, "SELECT ARTICLE_NO, AUTHOR_FIRST_NAMES,AUTHOR_LAST_NAMES, TITLE, ISBN, WHOLESALE_PRICE, RETAIL_PRICE,COPIES_AVAILABLE FROM BOOK");
result = msqlStoreResult();
for(count = msqlNumRows(result); count > 0; count--)
{
m_row record;
record = msqlFetchRow(result);
printf("\nArticle no. %s\n", record[0]);
printf("%s, %s:\n", record[2], record[1]);
printf(" %s (%s)\n", record[3], record[4]);
printf("Bought at %s; selling at %s; %s copies available\n\n",
record[5], record[6], record[7]);
};
msqlFreeResult(result);
}