数据库设计源代码.docx

《数据库设计源代码.docx》由会员分享，可在线阅读，更多相关《数据库设计源代码.docx（73页珍藏版）》请在三一办公上搜索。

1、/选择一种高级语言实现下列语句的功能。/CREATETABLE表名(V列名数据类型歹IJ完整性约束条件习,列名数据/类型列完整性约束条件习ILV表完整性约束条件)/Altertable表名addV新列名数据类型列完整性约束DROPv列完整性约束名习MODIFYV列名V数据类型运用说明1、将程序文件table.sql放在D盘根书目下。2、在C盘根书目下建立一个名为“数据库”的文件夹，用于存储表。3、建立的表存储路径为C:数据库:table.dbf。4、在程序文件table.sql中只有一条建表语句和三条修改表语句，在以程序方式执/行时留意执行的次数，慎重选择“是否接着执行”。5、程序输入的SQL

2、语句格式如下：/createtablestudent/(/SNOintPRIMARYKEY,/SNAMEchar(10)UNIQUE,/SAGEint,/SDEPTchar(20)NOTNULL,/COURSEchar(20),/GRADEint/）；/altertablestudentaddCNOintNOTNULL;/altertablestudentaltercolumnSAGEshort;/altertablestudentdropSDEPT;#include#include#include#include/宏定义#defineYEARO#defineMONTH1#defineDAY2

3、#defineFOX_VERISONNFo262#defineMAX40/字段类型#defineDATE0x44#defineDOUBLE0x45#defineFLOAT0x46#defineSHORT0x47#defineINT0x48#defineTRUE1#defineFALSEO/文件头结构体定义structDbfHead(chardbFlag;charyear;charmonth;charday;intrecCounts;shortfirstRecAddr;shortrecLen;charundo20;)；*pDbfHead;typedefstructDbfHeadDbfHead,/

4、字段描述结构体定义structFieldDcp(charfieldNamel0;charundo1;charfieldType;shortoffset;charundo22;charfieldLen;charnumDecis;charundo314;）；typedefstructFieldDcpFieldDcp,*pFieldDcp;/字段数据结构体定义structDbfFieldchar*fieldData;charfieldNamel0;charfieldType;charfieldLen;charfieldDcis;shortoffset;）；typedefstructDbfFieldD

5、bfField,*pDbfField;记录结构体定义structDbfRec（chardelFlag;DbfFieldfieldMAX;intrealCounts;typedefstructDbfRecDbfRec,*pDbfRec;dbf文件句柄定义structDbfHandchar50;DbfHeadheader;DbfRecrec;longcurRecNo;longCurFpAddr;FILE*fd;)；typedefstructDbfHandDbfHand,*pDbfHand;约束条件typedefstructConditionintflag;/flag用于区分约束条件(PARIMAR

6、YKEY1UNIQUE2NOTNULL3)Condition;pDbfHandf;DbfRecrec;chartable_name20;charsql300,sql1300;charGetDate(intgetMode)/获得日期(if(DAY=getMode)return01;if(MONTH=getMode)return06;if(YEAR=getMode)return10;elseexit(0);shortGetHeadLength(pDbfRecrec)获得文件头长度(returnrec-realCounts*32+32+2;shortGetRecLength(pDbfRecrec)/

7、获得文件体长度(registerinti=0;intcount=0;for(i=0;irealCounts;i+)if(rec-fieldi.fieldType=,D,)count+=8;continue;elseif(rec-fieldi.fieldType=,)count+=4;continue;elseif(rec-fieldi.fieldType=,T,)count+=6;continue;count+=rec-fieldi.fieldLen;returncount+1;)pDbfHandNewDbfHead(pDbfHandhand,pDbfRecrec)/新建文件intret=-1

8、;hand-header.dbFlag=0x03;hand-header.day=GetDate(DAY);hand-header.month=GetDate(MONTH);hand-header.year=GetDate(YEAR);hand-header.recLen=GetRecLength(rec);hand-header.recCounts=O;hand-header.firstRecAddr=GetHeadLength(rec);if(O!=fseek(hand-fd,O,SEEK.SET)returnNULL;ret=fwrite(char*)&hand-header,Sizeo

9、f(DbfHead),1hand-fd);if(ret!=-1)returnhand;elsereturnNULL;)pDbfHandUpdateHead(pDbfHandhand)/更新文件头(if(O!=fseek(hand-fd,O,SEEK.SET)returnNULL;if(-1=fwrite(char*)&hand-header,Sizeof(DbfHead),1,hand-fd)returnNULL;fclose(hand-fd);returnhand;)pDbfHandWriteFieldDicsribe(pDbfHandhand,pDbfRecrec)/写字段描述部分inti

10、=0;FieldDcpfield=O;field,offset=0x01;for(i=0;irealCounts;i+)(field.fieldType=rec-fieldi.fieldType;field.numDecis=0;switch(field.fieldType)caseDOUBLE:field.fieldLen=8;break;caseFLOAT:field.fieldLen=4;break;caseINT:field.fieldLen=4;break;caseSHORT:field.fieldLen=2;break;default:field.fieldLen=rec-fiel

11、di.fieldLen;break;strcpy(field.fieldName,rec-fieldi.fieldName);field.offset+=(short)rec-fieldi.fieldLen;if(-l=fwrite(char*)sfield,Sizeof(FieldDcp),1,hand-fd)returnNULL;hand-rec.delFlag=0x20;hand-rec.realCounts=rec-realCounts;for(i=0;irealCounts;i+)hand-rec.fieldi=rec-fieldi;returnhand;voidWriteField

12、End(pDbfHandhand)/写文件头结束标记inti=0;charbuf2=OxOD,OxOO;charversionBufFOX_VERISONJNFO=0;if(0=fseek(hand-fd,0,SEEK.END)(if(-1=fwrite(buf,2,1,hand-fd)return;return;pDbfHandReadDbfHead(pDbfHandhand)读文件头信息inti=0,j=0;if(-1!=(fseek(hand-fd,0,SEEK.SET)(if(0!=fread(char*)&hand-header,32,1,hand-fd)returnhand;ret

13、urnNULL;intGetFieldCount(pDbfHandhand)/得至字段个数inti=0;intoffset=1;FieldDcpfield=0;charcEnd=0;for(i=O;ifd,(i+1)*32,SEEK.SET)return-1;if(O!=fread(char*)&field,32,1,hand-fd)fread(&cEnd,l,1,hand-fd);if(OxOD=cEnd)return(i+1);return-1;pDbfHandReadFieldDiscribe(pDbfHandhand)/读字段描述信息inti=0,j=0;intfieldCount=G

14、etFieldCount(hand);hand-rec.realCounts=fieldCount;for(i=0;irec.realCounts;i+)(FieldDcpfield=0;if(-1=fseek(hand-fd,(i+l)*32,SEEK.SET)returnNULL;if(-1=fread(char*)&field,32,1,hand-fd)returnNULL;memcpy(hand-rec.fieldi.fieldName,field.fieldName,sizeof(field.fieldName);hand-rec.fieldi.fieldLen=field.fiel

15、dLen;hand-rec.fieldi.fieldType=field.fieldType;hand-rec.fieldi.fieldDcis=field.numDecis;hand-rec.fieldi.offset=field.offset;for(i=0;irec.realCounts;i+)for(j=Ojhand-rec.fieldi.fieldNamej!=,0,d+)hand-rec.fieldi.fieldNamej=tolower(hand-rec.fieldi.fieldNamej);returnhand;char*SaveDate(Char*str)保存内容为日期时，格

16、式华inti=0,j=0;chartemp9=0;if(strlen(str)!=10)日期格式输入不合法returnNULL;for(i=0,j=0;i=,0,&*(str+i)rec.realCounts;buf=(char*)malloc(hand-header.recLen+1);memset(buf,0x20,hand-header.recLen1);bufO=,;for(i=O;irec.fieldi.fieldType=DATE&record-fieldi.fieldData!=NULL)record-fieldi.fieldData=SaveDate(record-fieldi

17、.fieldData);CurFieldLen=hand-rec.fieldi.fieldLen;actDataLen=strlen(record-fieldi.fieldData);if(actDataLenCurFieldLen)actDataLen=CurFieldLen;memcpy(&bufnPos,record-fieldi.fieldData,actDataLen);nPos+=hand-rec.fieldi.fieldLen;bufhand-header.recLen=,0;if(-1=fwrite(buf,hand-header.recLen,1,hand-fd)rest=F

18、ALSE;returnrest;char*ReadDbfDate(char*str)读日期时，格式化inti=0,j=O;chartemp11=0;char*strRest=NULL;for(i=0,j=0;i=0;i)if(*(str+i)!=0x20)break;stri=0x00;returnstr;*pDbfFieldGetCurrentField(pDbfHandhandle,pDbfRecrec,intfieldld)/得到当前文件(return(PDbfField)&(rec-fieldfieldld);)7intGetFieldNum(pDbfHandhand,char*fie

19、ld_name)/得到文件个数intfIag=O;intj;for(j=0;jrec.realCounts;j+)if(O=strcmp(field_name,hand-rec.fieldj.fieldName)flag=l;break;if(flag)returnj;elsereturn-1;)intIsBottomRecord(pDbfHandhand)推断是否是第一条记录(intrecCounts=hand-header.recCounts;if(hand-curRecNo!=hand-header.recCounts)returnFALSE;returnTRUE;intGotoTop(

20、pDbfHandhand)/返回顶部hand-curRecNo=1;hand-CurFpAddr=hand-header.firstRecAddr;if(-1=fseek(hand-fd,hand-curFpAddr,SEEK_SET)returnFALSE;returnTRUE;intGotoBottom(pDbfHandhand)/移动到最终一条记录intrecLen=hand-header.recLen;longoffset=0;offset=recLen*(hand-header.recCounts-1);hand-curRecNo=hand-header.recCounts;hand

21、-CurFpAddr=hand-header.firstRecAddr+offset;if(-1=fseek(hand-fd,hand-curFpAddr,SEEK_SET)returnFALSE;returnTRUE;)intGoNextRecord(pDbfHandhand)/移动到下一条记录(if(TRUE=IsBottomRecord(hand)returnTRUE;else(hand-curRecNo+=1;hand-CurFpAddr+=hand-header.recLen;if(-1=fseek(hand-fd,hand-header.recLen,SEEK_CUR)return

22、FALSE;returnTRUE;)intGetCurRecord(pDbfHandhand,pDbfRecrec)/得到当前记录(inti=0,m;charCurFieldLen=O;longfieldffset=O;charpBuffData1024=0;char*recData;(*rec)=hand-rec;for(m=0;mrec.realCounts;m+)(rec-fieldm.fieldData=(char*)malloc(hand-rec.fieldm.fieldLen+l);memset(rec-fieldm.fieldData,0x00,hand-rec.fieldm.f

23、ieldLen+l);if(-1=fseek(hand-fd,hand-curFpAddr,SEEK_SET)/Movereturn0;recData=pBuffData;recData=recData+1;if(-1=fread(recData,hand-header.recLen,1,hand-fd)returnO;rec-delFlag=recDataO;recData=recData+1;for(i=0;irec.realCounts;i+,fieldoffset+=CurFieldLen)(CurFieldLen=hand-rec.fieldi.fieldLen;memcpy(rec

24、-fieldi.fieldData,recData+fieldOffset,CurFieldLen)；rec-fieldi.AeldData=DsdStrEndSpace(rec-fieldi.fieldData,CurFieldLen);if(rec-fieldi.fieldType=DATE&rec-fieldi.fieldDataO!=0x20)rec-fieldi.fieldData=ReadDbfDate(rec-fieldi.fieldData);return1;pDbfHandAddRec(pDbfHandhand,pDbfRecrecord)向文件尾中插入一条记录(charda

25、taEndFlag=OxlA;if(hand-fd=fopen(,c:table.dbf,r+bu)=NULL)returnNULL;if(hand-header.recCounts=0)fseek(hand-fd,-0L,SEEK_END);elsefseek(hand-fd,-1L,SEEK_END);if(FALSE=WriteRecord(hand,record)returnNULL;if(-1=fwrite(&dataEndFlag,1,1,hand-fd)returnNULL;hand-curRecNo+=1;hand-header.recCounts+=1;hand-CurFpA

26、ddr+=hand-header.recLen;if(NULL=UpdateHead(hand)returnNULL;returnhand;pDbfHandAddField(pDbfHandhand,char*field_name,chartype,intlen,intdec)/在文件中插入一条记录inti,reccounts;pDbfRec*rec;if(hand-header.recCounts)rec=(pDbfRec*)malloc(sizeof(pDbfRec)*hand-header.recCounts)；hand-curFpAddr=hand-header.firstRecAdd

27、r;hand-curRecNo=1;if(-1=fseek(hand-fd,hand-curFpAddr,SEEK_SET)returnFALSE;for(i=0;iheader.recCounts;i+)reci=(pDbfRec)malloc(sizeof(DbfRec);GetCurRecord(hand,reci);GoNextRecord(hand);for(i=0;iheader.recCounts;i+)reci-fieldhand-rec.realCounts.fieldData=(char*)malloc(len+1)；memset(reci-fieldhand-rec.re

28、alCounts.fieldData,0x00,len+l);reci-realCounts+;strcpy(hand-rec.fieldhand-rec.realCounts.fieldName,field-name);hand-rec.fieldhand-rec.realCounts.fieldNamestrlen(field-name)=,0,;hand-rec.fieldhand-rec.realCounts.fieldType=type;hand-rec.fieldhand-rec.realCounts.fieldLen=len;hand-rec.fieldhand-rec.real

29、Counts.fieldDcis=dec;hand-rec.realCounts+;reccounts=hand-header.recCounts;hand-fd=fopen(c:J$table.dbfn,Mwn)；fclose(hand-fd);if(NewDbfHead(hand,&hand-rec)=NULL)returnNULL;if(NULL=WriteFieldDicsribe(hand,&hand-rec)returnNULL;WriteFieldEnd(hand);if(0!=fclose(hand-fd)returnNULL;for(i=0;iheader.recCounts

30、)rec=(pDbfRec*)malloc(sizeof(pDbfRec)*hand-header.recCounts)；hand-curFpAddr=hand-header.firstRecAddr;hand-curRecNo=1;if(-1=fseek(hand-fd,hand-curFpAddr,SEEK_SET)returnFALSE;for(i=0;iheader.recCounts;i+)reci=(pDbfRec)malloc(sizeof(DbfRec);GetCurRecord(hand,reci);GoNextRecord(hand);reci-fieldnum.field

31、Dcis=dec;reci-fieldnum.fieldLen=len;reci-fieldnum.fieldType=type;hand-rec.fieldnum.fieldType=type;hand-rec.fieldnum.fieldLen=len;hand-rec.fieldnum.fieldDcis=dec;reccounts=hand-header.recCounts;hand-fd=fopen(,c:数据库table.dbf,wu);fclose(hand-fd);hand-fd=fopen(c:数据库table.dbfJw+b);if(NewDbfHead(hand,&han

32、d-rec)=NULL)returnNULL;if(NULL=WriteFieldDicsribe(hand,&hand-rec)returnNULL;WriteFieldEnd(hand);if(0!=fclose(hand-fd)returnNULL;for(i=0;iheader.recCounts)(rec=(pDbfRec*)malloc(sizeof(pDbfRec)*hand-header.recCounts)；hand-curFpAddr=hand-header.firstRecAddr;hand-curRecNo=1;if(-1=fseek(hand-fd,hand-curF

33、pAddr,SEEK-SET)returnFALSE;for(i=0;iheader.recCounts;i+)reci=(pDbfRec)malloc(sizeof(DbfRec);GetCurRecord(hand,reci);GoNextRecord(hand);reci-fieldnum.fieldDcis=reci-fieldnum+l.fieldDcis;reci-fieldnum.fieldLen=reci-fieldnum+1.fieldLen;reci-fieldnum.fieldType=reci-fieldnum+l.fieldType;num+;if(num=-l)(f

34、or(j=0;jrec.fieldnum.fieldNamej=O;hand-rec.fieldnum.fieldType=O;hand-rec.fieldnum.fieldLen=O;hand-rec.fieldnum.fieldDcis=O;elsefor(;numrec.fieldnum.fieldName,hand-rec.fieldnum+1.fieldName);hand-rec.fieldnum.fieldType=hand-rec.fieldnum+1.fieldTypehand-rec.fieldnum.fieldLen=hand-rec.fieldnum+l.fieldLe

35、n;hand-rec.fieldnum.fieldDcis=hand-rec.fieldnum+l.fieldDcis;hand-header.recCounts-;reccounts=hand-header.recCounts;hand-fd=fopen(,cJtable.dbf,wu);fclose(hand-fd);hand-fd=fopen(c:fttable.dbf,“w+b);if(NewDbfHead(hand,&hand-rec)=NULL)returnNULL;if(NULL=WriteFieldDicsribe(hand,&hand-rec)returnNULL;Write

36、FieldEnd(hand);if(0!=fclose(hand-fd)returnNULL;for(i=0;ifd=fopen(c:数据库table.dbf,w+b)!=NULL)(if(NewDbfHead(hand,&rec)=NULL)returnNULL;if(NULL=WriteFieldDicsribe(hand,&rec)returnNULL;WriteFieldEnd(hand);fclose(hand-fd);returnhand;PDbfHandOPenDbf()/打开DBF文件pDbfHandhand=NULL;if(hand=(pDbfHand)malloc(size

37、of(DbfHand)=NULL)returnNULL;memset(hand,0x00,sizeof(DbfHand);if(hand-fd=fopen(,c:table.dbf,r+bu)=NULL)returnNULL;if(NULL=ReadDbfHead(hand)returnNULL;if(NULL=ReadFieldDiscribe(hand)returnNULL;GotoTop(hand);returnhand;voidShowBeginningOprintf(+nt);printf(,II11);printfCI欢迎进入SQL系统I11);printf(,II11);prin

38、tf(+nt);voidShowQprintfC输入语句格式如下：n”)；printf(createtablestudentn);printf(,(n);printf(,SNOintPRIMARYKEYAn”)；printf(,SNAMEchar(10)UNIQUE,11);printf(SAGEint,11);printf(,SDEPTchar(20)NOTNULL,11);printf(,COURSEchar(20),11u);printf(GRADEintn);pri11tf()；n);printf(,altertablestudentaddCNOintNOTNULL;nu)；printf(altertablestudentaltercolumnSAGEshort;nu);printf(,