MSSQL编程：Linux数据库年夜比拚(二)

因此，我们的第一个“米”，就是二进制日志binlog必须是rowbased的。在rowbase下，二进制日志binlog同时记录了更新前后的整行记录。为了测试并对照3个数据库办理体系，我固然必要一个数据库来办理他们。在读完了《SQL傻瓜书》后，我有了一些怎样计划实际的数据库的基础常识，因而我挑撰了一出格无聊的实在天下情况(一个因特网书店，其他？)而且写下了一个小型数据库计划以便切磋一点Sql的才能。

在这个数据库里有5个表，BOOK保留可失掉的书的挂号信息；CUSTOMER包括挂号的主顾。对每份定单，BOOKORDER创立一行，参照其主顾。对每一个定购的项目，在ORDER_POSITION里援用它的定单号。作为一种嘉奖，针对书的排名我增添了一RATING表。

以下的SQL代码可装进到一个数据库体系的SQL监控程序而且被承受应当没有任何成绩。一切的表在创立前被删除因而确保他们不会在创立之前已存在。

　

DROPTABLEBOOK;
CREATETABLEBOOK(
ARTICLE_NOINTEGERPRIMARYKEY,
AUTHOR_FIRST_NAMESCHARACTER(30),
AUTHOR_LAST_NAMESCHARACTER(30),
TITLECHARACTER(30),
ISBNCHARACTER(13)UNIQUE,
WHOLESALE_PRICENUMERIC(4,2),
RETAIL_PRICENUMERIC(4,2),
COPIES_AVAILABLEINTEGER
);
DROPTABLECUSTOMER;
CREATETABLECUSTOMER(
CUSTOMER_NOINTEGERPRIMARYKEY,
FIRST_NAMESCHARACTER(30),
LAST_NAMESCHARACTER(30),
STREETCHARACTER(30),
HOUSE_NOSMALLINT,
POSTCODECHARACTER(7),
TOWNCHARACTER(30),
ISO_COUNTRY_CODECHARACTER(2)
);
DROPTABLEBOOKORDER;
CREATETABLEBOOKORDER(
ORDER_NOINTEGERPRIMARYKEY,
CUSTOMER_NOINTEGERNOTNULL,
ORDEREDDATE,
DELIVERYDATE,
STATUSCHARACTER(8)
CHECK(STATUSIN(′ACCEPTED′,
′DELAYED′,
′SHIPPED′,
′RETURNED′,
′PAID′)),
CONSTRAINTCUSTOMER_FKFOREIGNKEY(CUSTOMER_NO)
REFERENCESKUNDE(KUNDENNAME)
);
DROPTABLEORDER_POSITION;
CREATETABLEORDER_POSITION(
POSITION_NOINTEGERPRIMARYKEY,
ORDER_NOINTEGERNOTNULL,
ARTICLE_NOINTEGERNOTNULL,
NUMBERSMALLINT,
CONSTRAINTORDER_FKFOREIGNKEY(ORDER_NO)
REFERENCESBOOKORDER(ORDER_NO),
CONSTRAINTBOOK_FKFOREIGNKEY(ARTICLE_NO)
REFERENCESBOOK(ARTICLE_NO)
);
DROPTABLERATING;
CREATETABLERATING(
RATING_NOINTEGERPRIMARYKEY,
ARTICLE_NOINTEGERNOTNULL,
SCORENUMERIC(1,0),
COMMENTCHARACTERVARYING(300),
CONSTRAINTBOOK_FKFOREIGNKEY(ARTICLE_NO)
REFERENCESBOOK(ARTICLE_NO)
);
固然，这是一个极为复杂的数据库。它看上往实在，可是它不合用于实在天下的应。它不保留主顾纪录或任何器材，而且它乃至没有书的出书商的列。它只是一个测试情况。

注重我不想花年夜力气强迫CUSTOMER.ISO_COUNTRY_CODE为明天是实践无效的编码。我在代码做了一点限定以测试数据库体系是不是承受他们；我没实验使数据库自作掩饰。

改动计划顺应PostgreSQL
当我将遵守ANSI尺度的CREATETABLE语句装进Postgresql的psql监控视程序是，我碰到的坚苦是很少的。我失掉一些告诫：内部关头字限定被承受但还没有完成，并且我不能不扩充RATING的COMMENT字段到255个字符，由于这是PostgreSQL的CHARACTERVARYING范例的字段的最年夜字段宽度。体系为存储大批数据供应BLOB数据范例，可是它们不在尺度版本内，因而我决意了不利用他们。别的的成绩是相称愚昧--由于我不克不及找到有关PostgreSQL怎样强迫NUMERIC到C数据范例，也由于我不想利用float以免舍进，我决意使得泉币字段为分值(cent)的整数数字。
我最初失掉了这个略有分歧的剧本：

DROPTABLEBOOK;
CREATETABLEBOOK(
ARTICLE_NOINTEGERPRIMARYKEY,
AUTHOR_FIRST_NAMESCHARACTER(30),
AUTHOR_LAST_NAMESCHARACTER(30),
TITLECHARACTER(30),
ISBNCHARACTER(13)UNIQUE,
WHOLESALE_PRICEINTEGER,
RETAIL_PRICEINTEGER,
COPIES_AVAILABLEINTEGER
);
DROPTABLECUSTOMER;
CREATETABLECUSTOMER(
CUSTOMER_NOINTEGERPRIMARYKEY,
FIRST_NAMESCHARACTER(30),
LAST_NAMESCHARACTER(30),
STREETCHARACTER(30),
HOUSE_NOSMALLINT,
POSTCODECHARACTER(7),
TOWNCHARACTER(30),
ISO_COUNTRY_CODECHARACTER(2)
);
DROPTABLEBOOKORDER;
CREATETABLEBOOKORDER(
ORDER_NOINTEGERPRIMARYKEY,
CUSTOMER_NOINTEGERNOTNULL,
ORDEREDDATE,
DELIVERYDATE,
STATUSCHARACTER(8)
CHECK(STATUSIN(′ACCEPTED′,
′DELAYED′,
′SHIPPED′,
′RETURNED′,
′PAID′)),
CONSTRAINTCUSTOMER_FKFOREIGNKEY(CUSTOMER_NO)
REFERENCESKUNDE(KUNDENNAME)
);
DROPTABLEORDER_POSITION;
CREATETABLEORDER_POSITION(
POSITION_NOINTEGERPRIMARYKEY,
ORDER_NOINTEGERNOTNULL,
ARTICLE_NOINTEGERNOTNULL,
NUMBERSMALLINT,
CONSTRAINTORDER_FKFOREIGNKEY(ORDER_NO)
REFERENCESBOOKORDER(ORDER_NO),
CONSTRAINTBOOK_FKFOREIGNKEY(ARTICLE_NO)
REFERENCESBOOK(ARTICLE_NO)
);
DROPTABLERATING;
CREATETABLERATING(
RATING_NOINTEGERPRIMARYKEY,
ARTICLE_NOINTEGERNOTNULL,
SCORESMALLINT,
COMMENTCHARACTERVARYING(255),
CONSTRAINTBOOK_FKFOREIGNKEY(ARTICLE_NO)
REFERENCESBOOK(ARTICLE_NO)
);
　

使计划顺应MySQL
MySQL象PostgreSQL一样疏忽内部关头字的限定，可是它弄了个UNIQUE限定。最初的剧本与PostgreSQL剧本差未几：

DROPTABLEBOOK;
CREATETABLEBOOK(
ARTICLE_NOINTEGERPRIMARYKEY,
AUTHOR_FIRST_NAMESCHARACTER(30),
AUTHOR_LAST_NAMESCHARACTER(30),
TITLECHARACTER(30),
ISBNCHARACTER(13),
WHOLESALE_PRICEINTEGER,
RETAIL_PRICEINTEGER,
COPIES_AVAILABLEINTEGER
);
DROPTABLECUSTOMER;
CREATETABLECUSTOMER(
CUSTOMER_NOINTEGERPRIMARYKEY,
FIRST_NAMESCHARACTER(30),
LAST_NAMESCHARACTER(30),
STREETCHARACTER(30),
HOUSE_NOSMALLINT,
POSTCODECHARACTER(7),
TOWNCHARACTER(30),
ISO_COUNTRY_CODECHARACTER(2)
);
DROPTABLEBOOKORDER;
CREATETABLEBOOKORDER(
ORDER_NOINTEGERPRIMARYKEY,
CUSTOMER_NOINTEGERNOTNULL,
ORDEREDDATE,
DELIVERYDATE,
STATUSCHARACTER(8),
CONSTRAINTCUSTOMER_FKFOREIGNKEY(CUSTOMER_NO)
REFERENCESKUNDE(KUNDENNAME)
);
DROPTABLEORDER_POSITION;
CREATETABLEORDER_POSITION(
POSITION_NOINTEGERPRIMARYKEY,
ORDER_NOINTEGERNOTNULL,
ARTICLE_NOINTEGERNOTNULL,
NUMBERSMALLINT,
CONSTRAINTORDER_FKFOREIGNKEY(ORDER_NO)
REFERENCESBOOKORDER(ORDER_NO),
CONSTRAINTBOOK_FKFOREIGNKEY(ARTICLE_NO)
REFERENCESBOOK(ARTICLE_NO)
);
DROPTABLERATING;
CREATETABLERATING(
RATING_NOINTEGERPRIMARYKEY,
ARTICLE_NOINTEGERNOTNULL,
SCORENUMERIC(1,0),
COMMENTCHARACTERVARYING(255),
CONSTRAINTBOOK_FKFOREIGNKEY(ARTICLE_NO)
REFERENCESBOOK(ARTICLE_NO)
);
使计划顺应mSQL
由于mSQL是一个精简的数据库办理器(切实其实，有些人大概嫌疑MySQL和mSQL是不是是数据库办理体系)，它保持了年夜多半Sql的功效而仅仅承受SQL的一个严厉限定的子集。如许，mSQL的剧本看上有很年夜分歧：

DROPTABLEBOOK
CREATETABLEBOOK(
ARTICLE_NOINTEGERNOTNULL,
AUTHOR_FIRST_NAMESCHARACTER(30),
AUTHOR_LAST_NAMESCHARACTER(30),
TITLECHARACTER(30),
ISBNCHARACTER(13),
WHOLESALE_PRICEMONEY,
RETAIL_PRICEMONEY,
COPIES_AVAILABLEINTEGER
)
DROPTABLECUSTOMER
CREATETABLECUSTOMER(
CUSTOMER_NOINTEGERNOTNULL,
FIRST_NAMESCHARACTER(30),
LAST_NAMESCHARACTER(30),
STREETCHARACTER(30),
HOUSE_NOSMALLINT,
POSTCODECHARACTER(7),
TOWNCHARACTER(30),
ISO_COUNTRY_CODECHARACTER(2)
)
DROPTABLEBOOKORDER
CREATETABLEBOOKORDER(
ORDER_NOINTEGERNOTNULL,
CUSTOMER_NOINTEGERNOTNULL,
ORDEREDDATE,
DELIVERYDATE,
STATUSCHARACTER(1)
)
DROPTABLEORDER_POSITION
CREATETABLEORDER_POSITION(
POSITION_NOINTEGERNOTNULL,
ORDER_NOINTEGERNOTNULL,
ARTICLE_NOINTEGERNOTNULL,
NUMBERSMALLINT
)
DROPTABLERATING
CREATETABLERATING(
RATING_NOINTEGERNOTNULL,
ARTICLE_NOINTEGERNOTNULL,
SCORESMALLINT,
COMMENTTEXT(255)
)
几近一切的束缚都不见了，而且NUMERIC和CHARACTERVARYING分离由MONEY和TEXT取代。

在mSQL的监督程序中有使人懊丧的不敷：它仿佛不克不及承受从尺度输出输出SQL剧本，如许,必要剪切/粘贴代码。mSQL也厌恶分号；终极我只能一个一个地输出命令并用g（“go”斜杠命令)停止每条命令。

完成测试客户
为了对照3个数据库办理器，我决意为实行在bookstore数据库上的买卖的目标用C写了一个测试客户。了局，我完成了一些操纵，它们能对照API。为了功能对照，我随后充实完成了它们，而且把一个非交互式形式到场客户程序，因而它能本人运转，发生随便的数据且随机实行买卖。

我决意了在样品数据库上完成以下举动：

增添一本旧书：INSERTINTOBOOK(...)VALUES(...);
删除一本存在的书：DELETEFROMBOOKWHEREARTICLE_NO=...;
增添一个主顾：INSERTINTOCUSTOMER(...)VALUES(...);
删除一个主顾：DELETEFROMCUSTOMERWHERECUSTOMER_NO=...;
订书的一个主顾：INSERTINTOBOOKORDER(...)VALUES(...);INSERTINTOORDER_POSITION(...)VALUES(...);;
评价一本书的一个主顾：INSERTINTORATING(...)VALUES(...);
改动一份定单的形态：UPDATEBOOKORDERSETSTATUS=...WHEREORDER_NO=...;
然后，能天生以下报表：

书本列表：SELECT*FROMBOOK;
主顾列表：SELECT*FROMCUSTOMER;
正在送达的交货表，按形态排序：SELECT*FROMBOOKORDERORDERBYSTATUS;
书本的利润额，最初有均匀值：SELECTRETAIL_PRICE-WHOLESALE_PRICEFROMBOOK;SELECTAVG(RETAIL_PRICE-WHOLESALE_PRICE)FROMBOOK;
书评、评级和为一本书的均匀评级：SELECT*FROMRATINGWHEREARTICLE_NO=...;SELECTAVG(SCORE)FROMRATINGWHEREARTICLE_NO=...;
将客户带进PostgreSQL的生存
关于用C编程PostgreSQL的优点是你能利用嵌进式SQL。（并且，最少我喜好它)它不是很好地文档化，可是ESQL预处置器ecpg运转并能发生PostgreSQL接口代码就好。Sql的定式头脑办法偶然妨害了我；可是，开辟客户程序并非很难的。

我说过“不是很好地文档化”吗？那是一个守旧说法。不然PostgreSQL完全的HTML文档在这方面十分缺少。我从书籍失掉的ESQL常识是低级的，并且联机文档没匡助太多，因而我不能不本人懂得怎样由ecpg将C的变量强迫转换为NUMERIC值--另有其他器材，并且，ESQL预处置器不是很具体，且不管什么时候它碰着任何小毛病，老是仿佛完整开释出来，这对任何处置又临时筹办的项目标人来讲将是一个耐久战。

在编程PostgreSQL的客户程序时，我碰着了一些小毛病。比方，假如文档纪录是大概的话，在声明一个光标（cursor)时，ecpg将不承受一个FORREADONLY子句。ORDERBY子句乃至没被完成。我碰见的成绩多数ecpg预处置器有关。Postgres有一个CAPI(不论怎样说，ESQL必要被编译进一些器材)，它多是优异的，可是我没利用它(这就是生存)。当有ESQL时，我筹办利用ESQL。

这是摘自postgres-client.pgc的list_books()函数：

voidlist_books(void)
{
EXECSQLBEGINDECLARESECTION;
intarticle_no;
charauthor_first_names[30];
charauthor_last_names[30];
chartitle[30];
charisbn[14];
intwholesale_price;
intretail_price;
intcopies_available;
EXECSQLENDDECLARESECTION;
EXECSQLDECLAREbook_cursorCURSORFOR
SELECTARTICLE_NO,AUTHOR_FIRST_NAMES,AUTHOR_LAST_NAMES,
TITLE,ISBN,WHOLESALE_PRICE,RETAIL_PRICE,
COPIES_AVAILABLEFROMBOOK;
EXECSQLOPENbook_cursor;
while(1)
{
EXECSQLFETCHNEXTFROMbook_cursor
INTO:article_no,:author_first_names,:author_last_names,
:title,:isbn,:wholesale_price,:retail_price,
:copies_available;
if(sqlca.sqlcode==100)/*100==NOTFOUND*/
break;/*bailout*/
printf("
Articleno.%d
",article_no);
printf("%s,%s:
",author_last_names,author_first_names);
printf("%s(%s)
",title,isbn);
printf("Boughtat%d;sellingat%d;%dcopiesavailable

",
wholesale_price,retail_price,copies_available);
};
EXECSQLCLOSEbook_cursor;
}
代码是相称直不雅。它声明一些宿主变量，在一个BEGIN/ENDDECLARESECTION机关中包装声明，翻开一个SELECT光标查询，而且然后一行一行地取到宿主变量中，光标然后封闭。

我利用了更旧的,遭到分歧否决的sqlcode变量而不是更古代的sqlstate，由于这类体例更简单反省一个NOTFOUND情况。

把客户带进MySQL的生存
Mysql的CAPI是相称易用的。中心元素是包括有关数据库毗连的信息和其形态的布局，它经由过程毗连MySQL服务器举行初始化。该布局的一根指针必需被传送给一切的MySQL客户函数。

查询以字符串提交；这意味着一团体必需处置C字符串变更功效，包括空字节()的数据应当能利用，情形变得更庞大了，由于随后传送了一个计数字符串而不是一个C字符串。

为了猎取查询了局，一个指向MYSQL_RES布局的指针和一个数值变量用得当的API函数初始化，然后将一行取进一个MYSQL_ROW变量，它是一个字符串数组，间接将了局放进整数变量，就像Postgresql的ESQL的完成能做的那样，但这是不成能的，了局缓冲区随后被开释。只需你能了解，语义几近与在ESQL利用光标不异。
list_books(void)
{
intcount;
MYSQL_RES*result;
mysql_query(&bookstore,"SELECTARTICLE_NO,AUTHOR_FIRST_NAMES,AUTHOR_LAST_NAMES,TITLE,ISBN,WHOLESALE_PRICE,RETAIL_PRICE,COPIES_AVAILABLEFROMBOOK");
result=mysql_store_result(&bookstore);
for(count=mysql_num_rows(result);count>0;count--)
{
MYSQL_ROWrecord;
record=mysql_fetch_row(result);
printf("
Articleno.%s
",record[0]);
printf("%s,%s:
",record[2],record[1]);
printf("%s(%s)
",record[3],record[4]);
printf("Boughtat%s;sellingat%s;%scopiesavailable

",
record[5],record[6],record[7]);
};
mysql_free_result(result);
}
mysql_free_result(了局);
}

API函数长篇大论，但充足了,Texinfo格局的文档作为Mysql文档的次要来历。

把客户带进mSQL的
mSQL和MysqlCAPI之间的不同十分十分小。如许,乃至大概有一个主动变更器。次要的不同是：

mSQL不存储毗连数据块,仅存一个数字(intbookstore)
一些mSQL函数不拿毗连作为一个参数
mSQL函数名字是Pascal作风(他们利用年夜写首字符而不是下划线）
便利的MONEY数据范例是一个有2个的十进制位的流动精度小数范例。为了使mSQL准确地在MONEY列中将分币(cent)存进整数数字里，我必要转换他们，强迫到float，分别他们而且在add_new_book()函数中的sprintf语句格局化他们。
这是list_books(),移植到mSQL：

　

void
list_books(void)
{
intcount;
m_result*result;
msqlQuery(bookstore,"SELECTARTICLE_NO,AUTHOR_FIRST_NAMES,AUTHOR_LAST_NAMES,TITLE,ISBN,WHOLESALE_PRICE,RETAIL_PRICE,COPIES_AVAILABLEFROMBOOK");
result=msqlStoreResult();
for(count=msqlNumRows(result);count>0;count--)
{
m_rowrecord;
record=msqlFetchRow(result);
printf("
Articleno.%s
",record[0]);
printf("%s,%s:
",record[2],record[1]);
printf("%s(%s)
",record[3],record[4]);
printf("Boughtat%s;sellingat%s;%scopiesavailable

",
record[5],record[6],record[7]);
};
msqlFreeResult(result);
}
　

mSQL的CAPI文档能够在mSQL手册里找到，它以PostScript和一个年夜的HTML文件与mSQL一同刊行。

一些初期结论
一切这3个会商的数据库体系是相称简单安装、设置和编程。完成CAPI的客户库是很小的；与当今的好比GUI工具箱，他们的巨细是能够疏忽的，而且在客户程序的二进制巨细或存储器脚印（footprint）没有太年夜的不同。

Postgresql的ESQLAPI的不休增添的冗杂和更长的筹办工夫经由过程少花些精神在转换字符串到非字符串后反过去举行填补。

到今朝为止,我没有说过任何干于功能的事变。我将在这个系列的下一部分做深切研讨。


上面我们说了DML的闪回方案。但对于DDL却无能为力，对于大多数的DDL，即使是rowbase格式，二进制日志binlog中仍只记录语句本身。对于删表操作，只记录一个语句droptablet。仅凭这句话，无法还原表的数据。

可以动态传入参数，省却了动态SQL的拼写。

但换公司用MSSQL2K感觉自己好像根本就不了解MSSQL。什么DTS触发器以前根本没用过。

groupby子句可以将查询结果分组，并返回行的汇总信息Oracle按照groupby子句中指定的表达式的值分组查询结果。

如果我们从集合论（关系代数）的角度来看，一张数据库的表就是一组数据元的关系，而每个SQL语句会改变一种或数种关系，从而产生出新的数据元的关系（即产生新的表）。

另一个是把SQL语句写到服务器端,就是所谓的SP(存储过程);

但是随着数据量的增大，这种成本差距会逐渐减小，趋于相等。（500万数量级只相差10%左右）

一个百万级别的基本信息表A，一个百万级别的详细记录表B，A中有个身份证id，B中也有身份id；先要找出A中在B的详细记录。

财务软件要用SQL也只是后台的数据库而已,软件都是成品的,当然多学东西肯定是有好处的..