數(shù)據(jù)庫(kù)設(shè)計(jì)源代碼

1、/選擇一種高級(jí)語(yǔ)言實(shí)現(xiàn)下列語(yǔ)句的功能。 /CREATE TABLE <表名>（<列名><數(shù)據(jù)類型><列完整性約束條件>，<列名><數(shù)據(jù)/類型><列完整性約束條件>，<表完整性約束條件> ） /ALTER TABLE <表名> ADD <新列名><數(shù)據(jù)類型><列完整性約束> DROP<列完整/性約束名>MODIFY <列名><數(shù)據(jù)類型>/使用說(shuō)明/1、將程序文件table.sql放在D盤根目錄下。/2、在C盤根目錄下建

2、立一個(gè)名為"數(shù)據(jù)庫(kù)"的文件夾，用于存儲(chǔ)表。/3、建立的表存儲(chǔ)路徑為C:數(shù)據(jù)庫(kù):table.dbf。/4、在程序文件table.sql中只有一條建表語(yǔ)句和三條修改表語(yǔ)句，在以程序方式執(zhí)/行時(shí)注意執(zhí)行的次數(shù)，慎重選擇“是否繼續(xù)執(zhí)行”。/5、程序輸入的SQL語(yǔ)句格式如下：/create table student/( /SNO int PRIMARY KEY, /SNAME char(10) UNIQUE， /SAGE int, /SDEPT char(20) NOT NULL, /COURSE char(20), /GRADE int/);/alter table studen

3、t add CNO int NOT NULL;/alter table student alter column SAGE short;/alter table student drop SDEPT;#include<stdio.h>#include<stdlib.h>#include<string.h>#include<conio.h>/宏定義#define YEAR 0#define MONTH 1#define DAY 2#define FOX_VERISON_INFO 262#define MAX 40/字段類型#define DATE

4、0x44#define DOUBLE 0x45#define FLOAT 0x46#define SHORT 0x47#define INT 0x48#define TRUE 1#define FALSE 0/文件頭結(jié)構(gòu)體定義struct DbfHead char dbFlag; char year; char month; char day; int recCounts; short firstRecAddr; short recLen; char undo20;typedef struct DbfHead DbfHead, *pDbfHead;/字段描述結(jié)構(gòu)體定義struct FieldD

5、cp char fieldName10; char undo1; char fieldType; short offset; char undo22; char fieldLen; char numDecis; char undo314;typedef struct FieldDcp FieldDcp, *pFieldDcp;/字段數(shù)據(jù)結(jié)構(gòu)體定義struct DbfField char *fieldData; char fieldName10; char fieldType; char fieldLen; char fieldDcis; short offset;typedef struct

6、DbfField DbfField, *pDbfField;/記錄結(jié)構(gòu)體定義struct DbfRec char delFlag; DbfField fieldMAX; int realCounts;typedef struct DbfRec DbfRec, *pDbfRec;/dbf文件句柄定義struct DbfHand char filename50; DbfHead header; DbfRec rec; long curRecNo; long curFpAddr; FILE *fd;typedef struct DbfHand DbfHand, *pDbfHand;/約束條件type

7、def struct Conditionint flag;/flag 用于區(qū)分約束條件(PARIMARY KEY 1 UNIQUE 2 NOT NULL 3)Condition;pDbfHand f;DbfRec rec;char table_name20;char sql300,sql1300;char GetDate(int getMode)/獲取日期if( DAY =getMode)return 01;if(MONTH = getMode)return 06;if(YEAR = getMode)return 10;elseexit(0); short GetHeadLength(pDbf

8、Rec rec)/獲得文件頭長(zhǎng)度return rec->realCounts*32+32+2; short GetRecLength(pDbfRec rec)/獲得文件體長(zhǎng)度register int i=0;int count=0;for(i=0;i<rec->realCounts;i+)if(rec->fieldi.fieldType='D')count+=8;continue;else if(rec->fieldi.fieldType='I')count+=4;continue;else if(rec->fieldi.fi

9、eldType='T')count+=6;continue;count+=rec->fieldi.fieldLen;return count+1;pDbfHand NewDbfHead(pDbfHand hand, pDbfRec rec)/新建文件頭int ret = -1;hand-> header.dbFlag = 0x03;hand-> header.day = GetDate(DAY);hand-> header.month = GetDate(MONTH);hand-> header.year = GetDate(YEAR);hand-

10、> header.recLen = GetRecLength(rec);hand-> header.recCounts = 0;hand-> header.firstRecAddr = GetHeadLength(rec);if (0!= fseek(hand-> fd, 0, SEEK_SET)return NULL;ret = fwrite(char*)&hand->header,sizeof(DbfHead) , 1 , hand->fd); if(ret != -1)return hand; elsereturn NULL;pDbfHand

11、UpdateHead(pDbfHand hand)/更新文件頭if (0!= fseek(hand-> fd, 0, SEEK_SET)return NULL;if (-1= fwrite(char *)&hand-> header, sizeof(DbfHead) , 1 , hand->fd)return NULL;fclose(hand->fd);return hand;pDbfHand WriteFieldDicsribe(pDbfHand hand, pDbfRec rec)/寫字段描述部分int i = 0;FieldDcp field=0;fiel

12、d.offset = 0x01;for ( i = 0; i < rec-> realCounts; i+)field.fieldType = rec-> fieldi.fieldType;field.numDecis = 0;switch ( field.fieldType)case DOUBLE: field.fieldLen = 8; break;case FLOAT:field.fieldLen = 4;break;case INT:field.fieldLen = 4;break; case SHORT:field.fieldLen = 2;break;defaul

13、t:field.fieldLen = rec-> fieldi.fieldLen;break;strcpy(field.fieldName, rec-> fieldi.fieldName);field.offset +=( short)rec-> fieldi.fieldLen;if(-1 =fwrite(char*)&field, sizeof(FieldDcp) , 1 , hand->fd)return NULL;hand-> rec.delFlag = 0x20;hand-> rec.realCounts = rec-> realCou

14、nts;for (i = 0; i < rec-> realCounts; i+ ) hand-> rec.fieldi = rec-> fieldi;return hand;void WriteFieldEnd(pDbfHand hand)/寫文件頭結(jié)束標(biāo)志int i = 0;char buf2 = 0x0D, 0x00;char versionBufFOX_VERISON_INFO = 0;if (0 = fseek(hand-> fd, 0, SEEK_END)if (-1= fwrite(buf, 2 , 1 , hand->fd)return;re

15、turn ;pDbfHand ReadDbfHead(pDbfHand hand)/讀文件頭信息int i=0,j=0;if (-1 != (fseek(hand-> fd, 0, SEEK_SET)if (0 != fread(char*)&hand-> header, 32 , 1 , hand->fd) return hand;return NULL;int GetFieldCount(pDbfHand hand)/得到字段個(gè)數(shù)int i = 0;int offset = 1;FieldDcp field =0;char cEnd = 0;for (i=0;i&

16、lt;MAX;i+)memset(char *)&field,0,sizeof(FieldDcp);if ( -1 = fseek(hand-> fd, (i+1)*32, SEEK_SET)return -1;if ( 0 != fread(char*)&field, 32 , 1 , hand->fd)fread(&cEnd,1 , 1 , hand->fd);if (0x0D = cEnd)return (i + 1);return -1;pDbfHand ReadFieldDiscribe(pDbfHand hand)/讀字段描述信息int i

17、 = 0,j=0;int fieldCount = GetFieldCount(hand);hand-> rec.realCounts = fieldCount;for ( i = 0; i < hand-> rec.realCounts; i+)FieldDcp field = 0;if (-1 = fseek(hand-> fd, (i+1)*32, SEEK_SET)return NULL;if (-1 = fread(char*)&field, 32 , 1 , hand->fd)return NULL;memcpy(hand-> rec.f

18、ieldi.fieldName,field.fieldName,sizeof(field.fieldName);hand-> rec.fieldi.fieldLen = field.fieldLen;hand-> rec.fieldi.fieldType = field.fieldType;hand-> rec.fieldi.fieldDcis = field.numDecis;hand-> rec.fieldi.offset = field.offset; for(i=0;i<hand->rec.realCounts;i+) for(j=0;hand-&g

19、t;rec.fieldi.fieldNamej!='0'j+) hand->rec.fieldi.fieldNamej=tolower(hand->rec.fieldi.fieldNamej); return hand;char *SaveDate( char *str)/保存內(nèi)容為日期時(shí)，格式華int i=0, j=0; char temp9=0;if (strlen(str) != 10) /日期格式輸入不合法 return NULL;for ( i=0, j=0; i <10; i+ )if ( i = 4 | i =7 )continue;elseif

20、(*(str+i) >= '0' && *(str+i) <= '9')tempj=*(str+i);j+;else return NULL;str=NULL;str=( char *)realloc(str, 8);memset(str, 0x00, 8);memcpy(str, temp, 8);return str;int WriteRecord(pDbfHand hand, pDbfRec record)/在當(dāng)前位置寫一條紀(jì)錄,覆蓋原有內(nèi)容int i = 0; char *buf = NULL;int curFieldLen

21、= 0;int actDataLen = 0;int nPos = 1;int nFiledCount = 0;int rest = TRUE;nFiledCount = hand-> rec.realCounts;buf = ( char*)malloc(hand-> header.recLen + 1 );memset(buf,0x20,hand-> header.recLen + 1);buf0 = ' 'for ( i = 0; i < nFiledCount; i+ )if (hand-> rec.fieldi.fieldType = D

22、ATE && record-> fieldi.fieldData != NULL )record-> fieldi.fieldData = SaveDate(record-> fieldi.fieldData);curFieldLen = hand-> rec.fieldi.fieldLen;actDataLen = strlen(record-> fieldi.fieldData);if ( actDataLen > curFieldLen)actDataLen = curFieldLen;memcpy(&bufnPos, reco

23、rd-> fieldi.fieldData, actDataLen);nPos += hand-> rec.fieldi.fieldLen;bufhand-> header.recLen = '0 'if ( -1 = fwrite(buf, hand-> header.recLen , 1 , hand->fd)rest = FALSE;return rest;char *ReadDbfDate( char *str)/讀日期時(shí)，格式化int i=0, j=0;char temp11 = 0;char *strRest = NULL;for (

24、i=0, j=0; i < 8; i+, j+ )if ( i=4 | i=6)tempj = '/'j+;tempj=*(str+i);str = (char *)calloc(11,sizeof(char);memset(str, 0x00, 11);memcpy(str,temp,10);return str;char *DsdStrEndSpace(char *str, int size)/去掉字符串的后面空格int i=0,flag=0;for(i=size-1; i>= 0; i-)if (*(str+i) != 0x20)break;stri =0x0

25、0;return str;/*pDbfField GetCurrentField(pDbfHand handle, pDbfRec rec ,int fieldId)/得到當(dāng)前文件return (pDbfField)&(rec-> fieldfieldId);*/int GetFieldNum(pDbfHand hand,char* field_name)/得到文件個(gè)數(shù) int flag=0;int j;for(j=0;j<hand->rec.realCounts;j+)if(0=strcmp(field_name,hand->rec.fieldj.fieldN

26、ame)flag=1;break;if(flag) return j;else return -1;int IsBottomRecord(pDbfHand hand)/判斷是否是第一條記錄int recCounts = hand-> header.recCounts;if (hand-> curRecNo != hand-> header.recCounts)return FALSE;return TRUE;int GotoTop(pDbfHand hand)/返回頂部hand-> curRecNo = 1;hand-> curFpAddr = hand->

27、 header.firstRecAddr;if (-1 = fseek(hand-> fd, hand-> curFpAddr, SEEK_SET)return FALSE;return TRUE;int GotoBottom(pDbfHand hand)/移動(dòng)到最后一條記錄int recLen = hand-> header.recLen; long offset = 0;offset = recLen * (hand-> header.recCounts -1);hand-> curRecNo = hand-> header.recCounts;hand

28、-> curFpAddr = hand-> header.firstRecAddr + offset;if (-1 = fseek(hand-> fd, hand-> curFpAddr, SEEK_SET)return FALSE;return TRUE;int GoNextRecord(pDbfHand hand)/移動(dòng)到下一條記錄if (TRUE = IsBottomRecord(hand)return TRUE;elsehand-> curRecNo += 1;hand-> curFpAddr += hand-> header.recLen;i

29、f (-1 = fseek(hand-> fd, hand-> header.recLen, SEEK_CUR)return FALSE;return TRUE;int GetCurRecord(pDbfHand hand,pDbfRec rec)/得到當(dāng)前記錄int i = 0,m;char curFieldLen = 0;long fieldOffset = 0;char pBuffData1024 = 0;char *recData;(*rec)=hand->rec;for(m=0;m<hand->rec.realCounts;m+)rec->fiel

30、dm.fieldData=(char*)malloc(hand->rec.fieldm.fieldLen+1);memset(rec->fieldm.fieldData, 0x00, hand->rec.fieldm.fieldLen+1);if (-1= fseek(hand->fd,hand->curFpAddr, SEEK_SET) /Move file pointerreturn 0;recData = pBuffData;recData = recData + 1;if (-1 = fread(recData,hand->header.recLen

31、 , 1 , hand->fd)return 0;rec->delFlag = recData0;recData = recData + 1;for (i = 0; i < hand->rec.realCounts; i+, fieldOffset += curFieldLen)curFieldLen = hand->rec.fieldi.fieldLen;memcpy(rec->fieldi.fieldData,recData+fieldOffset,curFieldLen);rec->fieldi.fieldData = DsdStrEndSpac

32、e(rec->fieldi.fieldData, curFieldLen);if (rec->fieldi.fieldType = DATE && rec->fieldi.fieldData0 != 0x20 )rec->fieldi.fieldData =ReadDbfDate(rec->fieldi.fieldData);return 1;pDbfHand AddRec(pDbfHand hand, pDbfRec record)/向文件尾中插入一條記錄 char dataEndFlag = 0x1A;if (hand-> fd =fop

33、en("c:數(shù)據(jù)庫(kù)table.dbf","r+b") = NULL)return NULL;if ( hand-> header.recCounts = 0)fseek(hand-> fd, -0L, SEEK_END);elsefseek(hand-> fd, -1L, SEEK_END);if (FALSE = WriteRecord(hand, record)return NULL;if ( -1 = fwrite(&dataEndFlag, 1 , 1 , hand->fd)return NULL;hand->

34、; curRecNo += 1;hand-> header.recCounts += 1;hand-> curFpAddr += hand-> header.recLen;if (NULL = UpdateHead(hand)return NULL;return hand;pDbfHand AddField(pDbfHand hand,char* field_name,char type,int len,int dec)/在文件中插入一條記錄 int i,reccounts;pDbfRec *rec;if(hand->header.recCounts)rec=(pDbf

35、Rec*)malloc(sizeof(pDbfRec)*hand->header.recCounts);hand->curFpAddr=hand->header.firstRecAddr;hand->curRecNo=1;if (-1 = fseek(hand-> fd,hand->curFpAddr, SEEK_SET)return FALSE;for(i=0;i<hand->header.recCounts;i+)reci=(pDbfRec)malloc(sizeof(DbfRec);GetCurRecord(hand,reci);GoNex

36、tRecord(hand);for(i=0;i<hand->header.recCounts;i+)reci->fieldhand->rec.realCounts.fieldData=(char*)malloc(len+1);memset(reci->fieldhand->rec.realCounts.fieldData, 0x00, len+1);reci->realCounts+;strcpy(hand->rec.fieldhand->rec.realCounts.fieldName,field_name);hand->rec.f

37、ieldhand->rec.realCounts.fieldNamestrlen(field_name)='0'hand->rec.fieldhand->rec.realCounts.fieldType=type;hand->rec.fieldhand->rec.realCounts.fieldLen=len;hand->rec.fieldhand->rec.realCounts.fieldDcis=dec;hand->rec.realCounts+;reccounts=hand->header.recCounts;hand

38、->fd=fopen("c:數(shù)據(jù)庫(kù)table.dbf","w");fclose(hand->fd);hand->fd=fopen("c:數(shù)據(jù)庫(kù)table.dbf","w+b");if(NewDbfHead(hand, &hand->rec) = NULL)return NULL;if (NULL = WriteFieldDicsribe(hand, &hand->rec)return NULL;WriteFieldEnd(hand);if(0!=fclose(hand-

39、>fd)return NULL;for(i=0;i<reccounts;i+) AddRec(hand,reci);return hand;pDbfHand AlterField(pDbfHand hand,char* field_name,char type,int len,int dec)/修改一條記錄int num,i,reccounts;pDbfRec *rec;num=GetFieldNum(hand,field_name);if(hand->header.recCounts)rec=(pDbfRec*)malloc(sizeof(pDbfRec)*hand->

40、;header.recCounts);hand->curFpAddr=hand->header.firstRecAddr;hand->curRecNo=1;if (-1 = fseek(hand-> fd,hand->curFpAddr, SEEK_SET)return FALSE;for(i=0;i<hand->header.recCounts;i+)reci=(pDbfRec)malloc(sizeof(DbfRec);GetCurRecord(hand,reci);GoNextRecord(hand);reci->fieldnum.fiel

41、dDcis=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:數(shù)據(jù)庫(kù)table.dbf","w");fclose(hand->fd);ha

42、nd->fd=fopen("c:數(shù)據(jù)庫(kù)table.dbf","w+b");if(NewDbfHead(hand, &hand->rec) = NULL)return NULL;if (NULL = WriteFieldDicsribe(hand, &hand->rec)return NULL;WriteFieldEnd(hand);if(0!=fclose(hand->fd)return NULL;for(i=0;i<reccounts;i+) AddRec(hand,reci);return hand;pD

43、bfHand DropField(pDbfHand hand,char* field_name,int n)/刪除一條記錄int num,i,j,reccounts;pDbfRec *rec;num=GetFieldNum(hand,field_name);if(hand->header.recCounts)rec=(pDbfRec*)malloc(sizeof(pDbfRec)*hand->header.recCounts);hand->curFpAddr=hand->header.firstRecAddr;hand->curRecNo=1;if (-1 = f

44、seek(hand-> fd,hand->curFpAddr, SEEK_SET)return FALSE;for(i=0;i<hand->header.recCounts;i+)reci=(pDbfRec)malloc(sizeof(DbfRec);GetCurRecord(hand,reci);GoNextRecord(hand);reci->fieldnum.fieldDcis=reci->fieldnum+1.fieldDcis;reci->fieldnum.fieldLen=reci->fieldnum+1.fieldLen;reci-

45、>fieldnum.fieldType=reci->fieldnum+1.fieldType;num+;if(num=-1) for(j=0;j<10;j+) hand->rec.fieldnum.fieldNamej=0; hand->rec.fieldnum.fieldType=0; hand->rec.fieldnum.fieldLen=0; hand->rec.fieldnum.fieldDcis=0;else for(;num<n;num+) strcpy(hand->rec.fieldnum.fieldName,hand->

46、;rec.fieldnum+1.fieldName); hand->rec.fieldnum.fieldType=hand->rec.fieldnum+1.fieldType; hand->rec.fieldnum.fieldLen=hand->rec.fieldnum+1.fieldLen; hand->rec.fieldnum.fieldDcis=hand->rec.fieldnum+1.fieldDcis; hand->header.recCounts-;reccounts=hand->header.recCounts;hand->f

47、d=fopen("c:數(shù)據(jù)庫(kù)table.dbf","w");fclose(hand->fd);hand->fd=fopen("c:數(shù)據(jù)庫(kù)table.dbf","w+b");if(NewDbfHead(hand,&hand->rec) = NULL)return NULL;if (NULL = WriteFieldDicsribe(hand,&hand->rec)return NULL;WriteFieldEnd(hand);if(0!=fclose(hand->fd)r

48、eturn NULL;for(i=0;i<reccounts;i+) AddRec(hand,reci);return hand;pDbfHand CreateDbf(DbfRec rec)/建立DBF文件pDbfHand hand = NULL;if (hand = (pDbfHand)malloc(sizeof(DbfHand) =NULL)return NULL;memset(hand, 0x00, sizeof(DbfHand);if(hand->fd=fopen("c:數(shù)據(jù)庫(kù)table.dbf","w+b")!=NULL)if(Ne

49、wDbfHead(hand, &rec) = NULL) return NULL;if(NULL = WriteFieldDicsribe(hand, &rec) return NULL;WriteFieldEnd(hand);fclose(hand->fd);return hand;pDbfHand OpenDbf()/打開(kāi)DBF文件pDbfHand hand = NULL;if (hand = (pDbfHand) malloc(sizeof(DbfHand) =NULL)return NULL;memset(hand, 0x00, sizeof(DbfHand);i

50、f (hand-> fd =fopen("c:數(shù)據(jù)庫(kù)table.dbf","r+b") = NULL)return NULL;if(NULL = ReadDbfHead(hand)return NULL;if(NULL = ReadFieldDiscribe(hand)return NULL;GotoTop(hand);return hand;void ShowBeginning()printf("+-+n");printf("| |n");printf("| 歡迎進(jìn)入SQL系統(tǒng) |n")

51、;printf("| |n");printf("+-+n");void Show()printf(" 輸入語(yǔ)句格式如下：n");printf(" create table studentn");printf(" (n");printf(" SNO int PRIMARY KEY,n");printf(" SNAME char(10) UNIQUE,n");printf(" SAGE int,n");printf(" SDEPT char(20) NOT NULL,n");printf(" COURSE char(20),n");printf(" GRADE intn");printf(" );n");printf(" alter table student add CNO int NOT NULL;n&quo