数据库实验源代码大全
ASP数据库实验源代码清单
ASP数据库实验源代码清单1.直接插入数据（SQL Server）<html><head><title>直接插入数据实验</title></head><body><%set cn=server.createobject("adodb.connection")cn.open"driver={sql server};server=jcb;database=tms;uid=sa;pwd=123"'利用记录集recordset进行插入'set rs=server.createobject("adodb.recordset")'strin="insert into student(no,name,sex,dept,score)VALUES('99001','张三','男','信管',560)"'rs.open strin,cn'利用connection的execute函数进行插入strin="insert into student(no,name,sex,dept,score)VALUES('99002','赵敏','女','计算机',700)"cn.execute strin%></body></html>2.直接插入数据（Access）<html><head><title>直接插入数据实验</title></head><body><%set cn=server.createobject("adodb.connection")strconn="driver={microsoft access driver(*.mdb)};dbq="&server.mappath("tms.mdb") cn.open strconn'利用记录集recordset进行插入set rs=server.createobject("adodb.recordset")strin="insert into student(no,name,sex,dept,score)VALUES('99001','张三','男','信管',560)"rs.open strin,cn'利用connection的execute函数进行插入'strin="insert into student(no,name,sex,dept,score)VALUES('99003','赵敏','女','计算机',700)"'cn.execute strin%></body></html>3.通过网页插入数据（SQL Server）①formin.htm<html><head><title>通过表单插入信息</title></head><body><form action=formin.asp method=post><table border="1"width="40%"align=center><caption>通过交互表单提交信息</caption><tr><td align="right">学生学号：</td><td><input type="text"name="txtno"size="20"></td> </tr><tr><td align="right">学生姓名：</td><td><input type="text"name="txtname"size="20"></td> </tr><tr><td align="right">学生性别：</td><td><input type="radio"name="txtsex"value="男">男<input type="radio"name="txtsex"value="女">女</td></tr><tr><td align="right">所在院系：</td><td><select name="txtdept"size="1"><option selected value="管理学院">管理学院</option><option value="经济学院">经济学院</option><option value="文法学院">文法学院</option><option value="材料学院">材料学院</option><option value="外国语学院">外国语学院</option></select></td></tr><tr><td align="right">入学分数：</td><td><input type="text"name="txtscore"size="20"></td> </tr><tr><td align=center colspan="2"><input type="submit"value="确认"><input type="reset"value="提交"name="r1"></td></tr></table></form></body></html>②formin.asp<%tmpno=request.form("txtno")tmpname=request.form("txtname")tmpsex=request.form("txtsex")tmpdept=request.form("txtdept")tmpscore=request.form("txtscore")set cn=server.createobject("adodb.connection")cn.open"driver={sql server};server=jcb;database=tms;uid=sa;pwd=123" strin="insert into student(no,name,sex,dept,score)"strin=strin&"values('"&tmpno&"','"&tmpname&"','"&tmpsex&"','"&tmpdept&"',"&tmpscore &")"cn.execute strinresponse.write"<script language=JavaScript>"&"alert('记录插入成功！');"& "history.back();"&"</script>"%>4.插入数据并显示数据（SQL Server）1finsel.asp<html><head><title>插入同时显示信息</title></head><body><%'以下为向数据库中插入数据tmpno=request.form("txtno")tmpname=request.form("txtname")tmpsex=request.form("txtsex")tmpdept=request.form("txtdept")tmpscore=request.form("txtscore")set cn=server.createobject("adodb.connection")cn.open"driver={sql server};server=jcb;database=tms;uid=sa;pwd=123"strin="insert into student(no,name,sex,dept,score)"strin=strin&"values('"&tmpno&"','"&tmpname&"','"&tmpsex&"','"&tmpdept&"',"&tmpscore &")"cn.execute strin%><%'以下为将数据库的数据输出到网页set cn=server.createobject("adodb.connection")cn.open"driver={sql server};server=jcb;database=tms;uid=sa;pwd=123"set rs=server.createobject("adodb.recordset")selstr="select*from student"rs.open selstr,cn,1,1%><table border=1align=center><caption>学生信息表的所有信息</caption><tr><%for i=0to(rs.fields.count-1)%><th bgcolor=#CCCCCC valign=middle><%=rs(i).name%></th> <%next%></tr><%do while(not rs.eof)%><tr><%for i=0to(rs.fields.count-1)%><td valign=middle><%=rs(i).value%></td><%next%></tr><%rs.movenextloop%><%rs.closeset rs=nothingcn.closeset cn=nothing%></table></body></html>5.验证并插入数据（SQL Server）1checkin.htm<html><head><title>通过表单插入信息</title><script language="javascript">function checkform(){if(checkno()&&checkname()&&checksex()&&checkdept()&&checkscore()){ return true;}else{return false;}}//学号非空+长度+合法性验证function checkno(){var no=document.myform.txtno;if(no.value==""){alert("请输入学号");no.focus();return false;}if(no.value.length!=5){alert("学号长度为5位");no.select();return false;}if(isNaN(no.value)){alert("学号包含非法字符，只能包括数字");no.select();return false;}return true;function checkname(){var name=document.myform.txtname;if(name.value==""){alert("请输入姓名");name.focus();return false;}if(name.value.length>8){alert("姓名最多8个字符");name.select();return false;}return true;}function checksex(){if(document.myform.txtsex[0].checked==false&&document.myform.txtsex[1].check ed==false){alert("请选择性别");return false;}return true;}function checkdept(){if(document.myform.txtdept.selectedIndex==0){alert("请选择院系");return false;}return true;}function checkscore(){if(document.myform.txtscore.value==""){alert("请输入成绩");document.myform.txtscore.focus();return false;if(document.myform.txtscore.value<0||document.myform.txtscore.value>750){ alert("成绩输入错误，成绩必须在0—750之间");document.myform.txtscore.select();return false;}return true;}</script></head><body><form name=myform action=checkin.asp method=post onsubmit="return checkform()"><table border="1"width="40%"align=center><caption>通过交互表单提交信息</caption><tr><td align="right">学生学号：</td><td><input type="text"name="txtno"size="20"></td></tr><tr><td align="right">学生姓名：</td><td><input type="text"name="txtname"size="20"></td></tr><tr><td align="right">学生性别：</td><td><input type="radio"name="txtsex"value="男">男<input type="radio"name="txtsex"value="女">女</td></tr><tr><td align="right">所在院系：</td><td><select name="txtdept"size="1"><option value="no"selected>请选择院系</option><option value="管理学院">管理学院</option><option value="经济学院">经济学院</option><option value="文法学院">文法学院</option><option value="材料学院">材料学院</option><option value="外国语学院">外国语学院</option></select></td></tr><tr><td align="right">入学分数：</td><td><input type="text"name="txtscore"size="20"></td> </tr><tr><td align=center colspan="2"><input type="submit"value="确认"><input type="reset"value="重置"name="r1"></td></tr></table></form></body></html>2checkin.asp<%tmpno=request.form("txtno")tmpname=request.form("txtname")tmpsex=request.form("txtsex")tmpdept=request.form("txtdept")tmpscore=request.form("txtscore")set cn=server.createobject("adodb.connection")cn.open"driver={sql server};server=jcb;database=tms;uid=sa;pwd=123"set rs=server.createobject("adodb.recordset")selstr="select*from student where no='"&tmpno&"'"rs.open selstr,cnif not(rs.eof)thenresponse.write"<script language=JavaScript>"&"alert('该账号已被占用，请重新选择用户名！');"&"history.back();"&"</script>"rs.closeset rs=nothingcn.closeset cn=nothingelseset cn=server.createobject("adodb.connection")cn.open"driver={sql server};server=jcb;database=tms;uid=sa;pwd=123"strin="insert into student(no,name,sex,dept,score)"strin=strin&"values('"&tmpno&"','"&tmpname&"','"&tmpsex&"','"&tmpdept&"',"&tmpscore &")"cn.execute strinresponse.write"<script language=JavaScript>"&"alert('注册成功！');"& "history.back();"&"</script>"end if%>。
数据库powerbuilder实验1-9代码
面积公式：decimal r r=dec(sle_1.text) sle_2.text=string(3.14159*r*r) 计算器：1.声明变量：decimal add1 char s1 int flag2.数字：if flag=0 then sle_1.text="" flag=1 end ifsle_1.text=sle_1.text+this.text3.运算符：choose case s1case '*' sle_1.text=string(dec (sle_1.text)*add1)case'/' sle_1.text=string(add1/dec (sle_1.text))case'+' sle_1.text=string(dec (sle_1.text)+add1)case'-' sle_1.text=string(add1 - dec (sle_1.text))end choose add1=dec(sle_1.text) s1=this.text flag=04.清除：sle_1.text="" add1=0 s1='' flag=05.“=”：choose case s1case '*' sle_1.text=string(dec (sle_1.text)*add1)case'/' sle_1.text=string(add1/dec (sle_1.text))case'+' sle_1.text=string(dec (sle_1.text)+add1)case'-' sle_1.text=string(add1 - dec (sle_1.text))end choose flag=0 s1=''实验3-1：左击弹出窗口，右击每5秒弹出一消息对话窗，再次右击则不弹出。
数据结构上机实验源代码
数据结构上机实验源代码栈的应用十进制数转换为八进制数，逆序输出所输入的数实验代码：//stack.h，头文件class stack{public:stack();bool empty()const;bool full()const;error_code gettop(elementtype &x)const;error_code push(const elementtype x);error_code pop();private:int count;elementtype data[maxlen];};stack::stack(){count=0;}bool stack::empty()const{return count==0;}bool stack::full()const{return count==maxlen;}error_code stack::gettop(elementtype &x)const{if(empty())return underflow;else{x=data[count-1];return success;}}error_code stack::push(const elementtype x){if(full())return overflow;data[count]=x;count++;return success;}error_code stack::pop(){if(empty())return underflow;count--;return success;}//主程序#include<iostream.h>enum error_code{overflow,underflow,success};typedef int elementtype;const int maxlen=20;#include"stack.h"void read_write() //逆序输出所输入的数{stack s;int i;int n,x;cout<<"please input num int n:";cin>>n;for(i=1;i<=n;i++){cout<<"please input a num:";cin>>x;s.push(x);}while(!s.empty()){s.gettop(x);cout<<x<<" ";s.pop();}cout<<endl;}void Dec_to_Ocx(int n) //十进制转换为八进制{stack s1;int mod,x;while(n!=0){mod=n%8;s1.push(mod);n=n/8;}cout<<"the ocx of the dec is:";while(!s1.empty()){s1.gettop(x);cout<<x;s1.pop();}cout<<endl;}void main(){int n;// read_write();cout<<"please input a dec:";cin>>n;Dec_to_Ocx(n);}队列的应用打印n行杨辉三角实验代码：//queue.hclass queue{public:queue(){count=0;front=rear=0;}bool empty(){return count==0;}bool full(){return count==maxlen-1;}error_code get_front(elementtype &x){if(empty())return underflow;x=data[(front+1)%maxlen];return success;}error_code append(const elementtype x){if(full())return overflow;rear=(rear+1)%maxlen;data[rear]=x;count++;return success;}error_code serve(){if(empty())return underflow;front=(front+1)%maxlen;count--;return success;}private:int count;int front;int rear;int data[maxlen];};//主程序#include<iostream.h>enum error_code{overflow,underflow,success};typedef int elementtype;const int maxlen=20;#include"queue.h"void out_number(int n) //打印前n行的杨辉三角{int s1,s2;int i;int j;int k;queue q;for(i=1;i<=(n-1)*2;i++)cout<<" ";cout<<"1 "<<endl;q.append(1);for(i=2;i<=n;i++){s1=0;for(k=1;k<=(n-i)*2;k++)cout<<" ";for(j=1;j<=i-1;j++){q.get_front(s2);q.serve();cout<<s1+s2<<" ";q.append(s1+s2);s1=s2;}cout<<"1 "<<endl;q.append(1);}}void main(){int n;cout<<"please input n:";cin>>n;out_number(n);}单链表实验实验目的:实验目的（1）理解线性表的链式存储结构。
数据库设计源代码
//选择一种高级语言实现下列语句的功能。
//CREATE TABLE <表名>（<列名><数据类型>[<列完整性约束条件>][，<列名><数据//类型>[<列完整性约束条件>]…][，<表完整性约束条件>] ）//ALTER TABLE <表名> [ADD <新列名><数据类型>[<列完整性约束>]] [DROP<列完整//性约束名>][MODIFY <列名><数据类型>]//使用说明//1、将程序文件table.sql放在D盘根目录下。
//2、在C盘根目录下建立一个名为"数据库"的文件夹，用于存储表。
//3、建立的表存储路径为C:\数据库:\table.dbf。
//4、在程序文件table.sql中只有一条建表语句和三条修改表语句，在以程序方式执//行时注意执行的次数，慎重选择“是否继续执行”。
//5、程序输入的SQL语句格式如下：//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 student 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 0x44#define DOUBLE 0x45#define FLOAT 0x46#define SHORT 0x47#define INT 0x48#define TRUE 1#define FALSE 0//文件头结构体定义struct DbfHead{char dbFlag;char year;char month;char day;int recCounts;short firstRecAddr;short recLen;char undo[20];};typedef struct DbfHead DbfHead, *pDbfHead; //字段描述结构体定义struct FieldDcp{char fieldName[10];char undo1;char fieldType;short offset;char undo2[2];char fieldLen;char numDecis;char undo3[14];};typedef struct FieldDcp FieldDcp, *pFieldDcp; //字段数据结构体定义struct DbfField{char *fieldData;char fieldName[10];char fieldType;char fieldLen;char fieldDcis;short offset;};typedef struct DbfField DbfField, *pDbfField; //记录结构体定义struct DbfRecchar delFlag;DbfField field[MAX];int realCounts;};typedef struct DbfRec DbfRec, *pDbfRec;//dbf文件句柄定义struct DbfHand{char filename[50];DbfHead header;DbfRec rec;long curRecNo;long curFpAddr;FILE *fd;};typedef struct DbfHand DbfHand, *pDbfHand;//约束条件typedef struct Condition{int flag;//flag 用于区分约束条件(PARIMARY KEY 1 UNIQUE 2 NOT NULL 3) }Condition;pDbfHand f;DbfRec rec;char table_name[20];char sql[300],sql1[300];char GetDate(int getMode)//获取日期{if( DAY ==getMode)return 01;if(MONTH == getMode)return 06;if(YEAR == getMode)return 10;elseexit(0);}short GetHeadLength(pDbfRec rec)//获得文件头长度{return rec->realCounts*32+32+2;}short GetRecLength(pDbfRec rec)//获得文件体长度register int i=0;int count=0;for(i=0;i<rec->realCounts;i++){if(rec->field[i].fieldType=='D'){count+=8;continue;}else if(rec->field[i].fieldType=='I'){count+=4;continue;}else if(rec->field[i].fieldType=='T'){count+=6;continue;}count+=rec->field[i].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-> 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 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};field.offset = 0x01;for ( i = 0; i < rec-> realCounts; i++){field.fieldType = rec-> field[i].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;default:field.fieldLen = rec-> field[i].fieldLen;break;}strcpy(field.fieldName, rec-> field[i].fieldName);field.offset +=( short)rec-> field[i].fieldLen;if(-1 ==fwrite((char*)&field, sizeof(FieldDcp) , 1 , hand->fd))return NULL;}hand-> rec.delFlag = 0x20;hand-> rec.realCounts = rec-> realCounts;for (i = 0; i < rec-> realCounts; i++ )hand-> rec.field[i] = rec-> field[i];return hand;void WriteFieldEnd(pDbfHand hand)//写文件头结束标志{int i = 0;char buf[2] = {0x0D, 0x00};char versionBuf[FOX_VERISON_INFO] = {0};if (0 == fseek(hand-> fd, 0, SEEK_END)){if (-1== fwrite(buf, 2 , 1 , hand->fd))return;}return ;}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)//得到字段个数{int i = 0;int offset = 1;FieldDcp field ={0};char cEnd = 0;for (i=0;i<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 = 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.field[i].fieldName,field.fieldName,sizeof(field.fieldName));hand-> rec.field[i].fieldLen = field.fieldLen;hand-> rec.field[i].fieldType = field.fieldType;hand-> rec.field[i].fieldDcis = field.numDecis;hand-> rec.field[i].offset = field.offset;}for(i=0;i<hand->rec.realCounts;i++){for(j=0;hand->rec.field[i].fieldName[j]!='\0';j++){hand->rec.field[i].fieldName[j]=tolower(hand->rec.field[i].fieldName[j]);}}return hand;}char *SaveDate( char *str)//保存内容为日期时，格式华{int i=0, j=0;char temp[9]={0};if (strlen(str) != 10) //日期格式输入不合法return NULL;for ( i=0, j=0; i <10; i++ ){if ( i == 4 || i ==7 )continue;else{if(*(str+i) >= '0' && *(str+i) <= '9'){temp[j]=*(str+i);j++;}elsereturn NULL;}}str=NULL;str=( char *)realloc(str, 8);memset(str, 0x00, 8);memcpy(str, temp, 8);return str;}int WriteRecord(pDbfHand hand, pDbfRec record)//在当前位置写一条纪录,覆盖原有内容{int i = 0;char *buf = NULL;int curFieldLen = 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);buf[0] = ' ';for ( i = 0; i < nFiledCount; i++ ){if (hand-> rec.field[i].fieldType == DATE && record-> field[i].fieldData != NULL ) record-> field[i].fieldData = SaveDate(record-> field[i].fieldData);curFieldLen = hand-> rec.field[i].fieldLen;actDataLen = strlen(record-> field[i].fieldData);if ( actDataLen > curFieldLen)actDataLen = curFieldLen;memcpy(&buf[nPos], record-> field[i].fieldData, actDataLen);nPos += hand-> rec.field[i].fieldLen;}buf[hand-> header.recLen] = '\0 ';if ( -1 == fwrite(buf, hand-> header.recLen , 1 , hand->fd))rest = FALSE;return rest;}char *ReadDbfDate( char *str)//读日期时，格式化{int i=0, j=0;char temp[11] = {0};char *strRest = NULL;for ( i=0, j=0; i < 8; i++, j++ ){if ( i==4 || i==6){temp[j] = '/';j++;}temp[j]=*(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;str[i] =0x00;}return str;}/*pDbfField GetCurrentField(pDbfHand handle, pDbfRec rec ,int fieldId)//得到当前文件{return (pDbfField)&(rec-> field[fieldId]);}*/int GetFieldNum(pDbfHand hand,char* field_name)//得到文件个数{int flag=0;int j;for(j=0;j<hand->rec.realCounts;j++){if(0==strcmp(field_name,hand->rec.field[j].fieldName)){flag=1;break;}}if(flag)return j;elsereturn -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-> header.firstRecAddr;if (-1 == fseek(hand-> fd, hand-> curFpAddr, SEEK_SET))return FALSE;return TRUE;}int GotoBottom(pDbfHand hand)//移动到最后一条记录{int recLen = hand-> header.recLen;long offset = 0;offset = recLen * (hand-> header.recCounts -1);hand-> curRecNo = hand-> header.recCounts;hand-> curFpAddr = hand-> header.firstRecAddr + offset;if (-1 == fseek(hand-> fd, hand-> curFpAddr, SEEK_SET))return FALSE;return TRUE;}int GoNextRecord(pDbfHand hand)//移动到下一条记录{if (TRUE == IsBottomRecord(hand))return TRUE;else{hand-> curRecNo += 1;hand-> curFpAddr += hand-> header.recLen;if (-1 == fseek(hand-> fd, hand-> header.recLen, SEEK_CUR)) return FALSE;}return TRUE;}int GetCurRecord(pDbfHand hand,pDbfRec rec)//得到当前记录{int i = 0,m;char curFieldLen = 0;long fieldOffset = 0;char pBuffData[1024] = {0};char *recData;(*rec)=hand->rec;for(m=0;m<hand->rec.realCounts;m++){rec->field[m].fieldData=(char*)malloc(hand->rec.field[m].fieldLen+1);memset(rec->field[m].fieldData, 0x00, hand->rec.field[m].fieldLen+1);}if (-1== fseek(hand->fd,hand->curFpAddr, SEEK_SET)) //Move file pointer return 0;recData = pBuffData;recData = recData + 1;if (-1 == fread(recData,hand->header.recLen , 1 , hand->fd))return 0;rec->delFlag = recData[0];recData = recData + 1;for (i = 0; i < hand->rec.realCounts; i++, fieldOffset += curFieldLen){curFieldLen = hand->rec.field[i].fieldLen;memcpy(rec->field[i].fieldData,recData+fieldOffset,curFieldLen);rec->field[i].fieldData = DsdStrEndSpace(rec->field[i].fieldData, curFieldLen);if (rec->field[i].fieldType == DATE && rec->field[i].fieldData[0] != 0x20 ) rec->field[i].fieldData =ReadDbfDate(rec->field[i].fieldData);}return 1;}pDbfHand AddRec(pDbfHand hand, pDbfRec record)//向文件尾中插入一条记录{char dataEndFlag = 0x1A;if ((hand-> fd =fopen("c:\\数据库\\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-> curRecNo += 1;hand-> header.recCounts += 1;hand-> curFpAddr += hand-> header.recLen;if (NULL == UpdateHead(hand))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=(pDbfRec*)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++){rec[i]=(pDbfRec)malloc(sizeof(DbfRec));GetCurRecord(hand,rec[i]);GoNextRecord(hand);}for(i=0;i<hand->header.recCounts;i++){rec[i]->field[hand->rec.realCounts].fieldData=(char*)malloc(len+1);memset(rec[i]->field[hand->rec.realCounts].fieldData, 0x00, len+1);rec[i]->realCounts++;}}strcpy(hand->rec.field[hand->rec.realCounts].fieldName,field_name);hand->rec.field[hand->rec.realCounts].fieldName[strlen(field_name)]='\0';hand->rec.field[hand->rec.realCounts].fieldType=type;hand->rec.field[hand->rec.realCounts].fieldLen=len;hand->rec.field[hand->rec.realCounts].fieldDcis=dec;hand->rec.realCounts++;reccounts=hand->header.recCounts;hand->fd=fopen("c:\\数据库\\table.dbf","w");fclose(hand->fd);hand->fd=fopen("c:\\数据库\\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))for(i=0;i<reccounts;i++)AddRec(hand,rec[i]);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->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++){rec[i]=(pDbfRec)malloc(sizeof(DbfRec));GetCurRecord(hand,rec[i]);GoNextRecord(hand);rec[i]->field[num].fieldDcis=dec;rec[i]->field[num].fieldLen=len;rec[i]->field[num].fieldType=type;}}hand->rec.field[num].fieldType=type;hand->rec.field[num].fieldLen=len;hand->rec.field[num].fieldDcis=dec;reccounts=hand->header.recCounts;hand->fd=fopen("c:\\数据库\\table.dbf","w");fclose(hand->fd);hand->fd=fopen("c:\\数据库\\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,rec[i]);return hand;}pDbfHand 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 == fseek(hand-> fd,hand->curFpAddr, SEEK_SET))return FALSE;for(i=0;i<hand->header.recCounts;i++){rec[i]=(pDbfRec)malloc(sizeof(DbfRec));GetCurRecord(hand,rec[i]);GoNextRecord(hand);rec[i]->field[num].fieldDcis=rec[i]->field[num+1].fieldDcis;rec[i]->field[num].fieldLen=rec[i]->field[num+1].fieldLen;rec[i]->field[num].fieldType=rec[i]->field[num+1].fieldType;num++;}}if(num==-1){for(j=0;j<10;j++)hand->rec.field[num].fieldName[j]=0;hand->rec.field[num].fieldType=0;hand->rec.field[num].fieldLen=0;hand->rec.field[num].fieldDcis=0;}else{for(;num<n;num++){strcpy(hand->rec.field[num].fieldName,hand->rec.field[num+1].fieldName);hand->rec.field[num].fieldType=hand->rec.field[num+1].fieldType;hand->rec.field[num].fieldLen=hand->rec.field[num+1].fieldLen;hand->rec.field[num].fieldDcis=hand->rec.field[num+1].fieldDcis;}}hand->header.recCounts--;reccounts=hand->header.recCounts;hand->fd=fopen("c:\\数据库\\table.dbf","w");fclose(hand->fd);hand->fd=fopen("c:\\数据库\\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,rec[i]);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:\\数据库\\table.dbf","w+b"))!=NULL){if(NewDbfHead(hand, &rec) == NULL)return NULL;if(NULL == WriteFieldDicsribe(hand, &rec))return NULL;WriteFieldEnd(hand);}fclose(hand->fd);return hand;}pDbfHand OpenDbf()//打开DBF文件{pDbfHand hand = NULL;if ((hand = (pDbfHand) malloc(sizeof(DbfHand))) ==NULL) return NULL;memset(hand, 0x00, sizeof(DbfHand));if ((hand-> fd =fopen("c:\\数据库\\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("| 欢迎进入SQL系统|\n");printf("||\n");printf("+----------------------------------------------------------------------------+\n");}void Show(){printf(" 输入语句格式如下：\n");printf(" create table student\n");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 int\n");printf(" );\n");printf(" alter table student add CNO int NOT NULL;\n");printf(" alter table student alter column SAGE short;\n");printf(" alter table student drop SDEPT;\n");}int Read()//用于读取从键盘键入的SQL语句{char c;int i,j;printf("+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++\n");printf("请输入SQL语句:\n\n");for(i=0;(c=getch())!=';';i++){if(c==27)exit(0);if(c==8){i-=2;if(i<-1)i=-1;system("cls");Show();printf("+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++\n");printf("请输入SQL语句:\n\n");for(j=0;j<=i;j++){if(sql1[j]==13){puts("");}printf("%c",sql1[j]);}continue;}sql1[i]=c;if(c==13){puts("");sql[i]=' ';}else if(c=='('||c==')'||c=='\''){printf("%c",c);sql[i]=' ';}else{printf("%c",c);sql[i]=c;}}sql[i]=';';sql[i+1]='\0';printf(";");puts("");return 0;}int position;//文件指针的位置int Read1()//用于读取从程序文件中读取的SQL语句{FILE *fp;char c;int i,j;printf("+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++\n");printf("请输入SQL语句:\n\n");fp=fopen("d:\\table.sql","r");fseek(fp,position,0);for(i=0;(c=fgetc(fp))!=';';i++){if(c==27)exit(0);if(c==8){i-=2;if(i<-1)i=-1;system("cls");Show();printf("+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++\n");printf("请输入SQL语句:\n\n");for(j=0;j<=i;j++){if(sql1[j]==13)puts("");printf("%c",sql1[j]);}continue;}sql1[i]=c;if(c==13){puts("");sql[i]=' ';}else if(c=='('||c==')'||c=='\''){printf("%c",c);sql[i]=' ';}else{printf("%c",c);sql[i]=c;}}sql[i]=';';sql[i+1]='\0';printf(";");puts("");position=ftell(fp);return 0;}int CREATE(){char type[6][10]={"char","int","short","float","double"};char Type1[6]={'C','N','S','F','D'};FILE *fp=NULL;Condition con[MAX];//约束条件结构体int i,j,num=0,error=1;//num 字段数error输入错误标志char temp[10],condition[10];//table_name 表名condition 约束条件memset(con,0,sizeof(con[0])*MAX);//结构体中各值初始化为空for(i=0;sql[i]==' ';i++);for(j=0;sql[i]!=' '&&sql[i]!=';';i++,j++)temp[j]=tolower(sql[i]);temp[j]='\0';if(strcmp(temp,"create")!=0)//判断create是否写错{error=0;printf("\n你输入的'create'有误，请重新创建!\n");}else{for(;sql[i]==' ';i++);for(j=0;sql[i]!=' '&&sql[i]!=';';i++,j++)temp[j]=tolower(sql[i]);temp[j]='\0';if(strcmp(temp,"table")!=0)//判断table是否写错{error=0;printf("\n你输入的'table'有误，请重新创建!\n");}else{for(;sql[i]==' ';i++);for(j=0;sql[i]!=' '&&sql[i]!=';';i++,j++)//提取表名table_name[j]=tolower(sql[i]);table_name[j]='.'; //加后缀.dbftable_name[j+1]='d';table_name[j+2]='b';table_name[j+3]='f';table_name[j+4]='\0';do{int field_lengtf=0,flag=1;//field_lengtf 字段长度for(;sql[i]==' '||sql[i]==',';i++);if(sql[i]==';')break;for(j=0;sql[i]!=' ';i++,j++)//提取列名rec.field[num].fieldName[j]=sql[i];rec.field[num].fieldName[j]='\0';for(;sql[i]==' ';i++);for(j=0;sql[i]!=' '&&sql[i]!=',';i++,j++)//提取列数据类型temp[j]=tolower(sql[i]);temp[j]='\0';for(j=0;j<5;j++){if(strcmp(temp,type[j])==0){rec.field[num].fieldType=Type1[j];break;}}if(j==5)//列数据类型有误{error=0;printf("\n你输入的列数据类型有误(务必是char,int,short,float,double)，请重新创建!\n");break;}else{/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/if(rec.field[num].fieldType=='C'){//如果类型是char需要提取列长度，如果是int、float则固定列长度为4,double则固定列长度为8for(;sql[i]==' ';i++);for(;sql[i]!=' ';i++)//提取长度{if(sql[i]<48||sql[i]>58)//列长度必须是数字{flag=0;break;}elsefield_lengtf=field_lengtf*10+(sql[i]-48);}if(flag==0){error=0;printf("\n你输入的列长度有误，请重新创建!\n");break;}elserec.field[num].fieldLen=field_lengtf;}else if(rec.field[num].fieldType=='N'||rec.field[num].fieldType=='F') rec.field[num].fieldLen=4;else if(rec.field[num].fieldType=='D')rec.field[num].fieldLen=8;else if(rec.field[num].fieldType=='S')rec.field[num].fieldLen=2;field_lengtf=0,flag=1;for(;sql[i]==' ';i++);for(j=0;sql[i]!=' '&&sql[i]!=','&&sql[i]!=';';i++,j++)//提取列完整性约束condition[j]=tolower(sql[i]);condition[j]='\0';if(condition[0]!=','&&condition[0]!='\0'){if(strcmp(condition,"primary")==0)//判断是否为主键{for(;sql[i]==' ';i++);for(j=0;sql[i]!=' '&&sql[i]!=',';i++,j++)temp[j]=tolower(sql[i]);temp[j]='\0';if(strcmp(temp,"key")==0)con[num].flag=1;else{error=0;printf("\n你输入的'KEY'有误，请重新创建!\n");break;}}else if(strcmp(condition,"unique")==0)//判断是否取唯一值con[num].flag=2;else if(strcmp(condition,"not")==0)//判断是否非空{for(;sql[i]==' ';i++);for(j=0;sql[i]!=' '&&sql[i]!=',';i++,j++)condition[j]=tolower(sql[i]);condition[j]='\0';if(strcmp(condition,"null")==0)con[num].flag=3;else{error=0;printf("\n你输入的约束条件'NOT NULL'有误，请重新创建!\n");break;}}else//列完整性约束出错{error=0;printf("\n你输入的约束条件有误(必须PARIMARY KEY,UNIQUE,NOT NULL)，请重新创建!\n");break;}}}rec.field[num].fieldDcis=0;rec.field[num].offset=0;rec.realCounts=num+1;num++;//下一字段}while(sql[i]!=';');if((fp=fopen("d:\\student.dat","w+b"))==NULL)//将列完整性约束条件写入文件中{printf("打开文件失败！\n");error=0;}fwrite(&con,sizeof(Condition)*num,1,fp);fclose(fp);fp=NULL;}}if(error==0){memset(sql,0,sizeof(char)*300);//出错时清空输入的语句return 0;}else{if(f!=CreateDbf(rec))//建立.dbf文件return 1;elsereturn 0;}return 1;}int ALTER(){char type[6][10]={"char","int","short","float","double"};char Type1[6]={'C','N','S','F','D'};FILE *fp;int i,j,num,error=1;char T; //列数据类型的简写Condition con[MAX];//约束条件结构体char temp[10],field_name[10],f_type[10],condition[10];// field_name 列名f_type 列数据类型memset(&con,0,sizeof(con));//结构体中各值初始化为空for(i=0;sql[i]==' ';i++);for(j=0;sql[i]!=' ';i++,j++)temp[j]=tolower(sql[i]);temp[j]='\0';if(strcmp(temp,"alter")!=0)//判断alter是否写错{error=0;printf("\n你输入的'alter'有误，请重新输入SQL语句!\n");}else{for(;sql[i]==' ';i++);for(j=0;sql[i]!=' ';i++,j++)temp[j]=tolower(sql[i]);temp[j]='\0';if(strcmp(temp,"table")!=0)//判断table是否写错{error=0;printf("\n你输入的'table'有误，请重新输入SQL语句!\n");}else{for(;sql[i]==' ';i++);for(j=0;sql[i]!=' ';i++,j++)//提取表名table_name[j]=tolower(sql[i]);table_name[j]='.'; //加后缀.dbftable_name[j+1]='d';table_name[j+2]='b';table_name[j+3]='f';table_name[j+4]='\0';if((f=OpenDbf())==0) //判断该表是否存在{printf("\n你输入的表%s不存在，请重新输入SQL语句!\n",table_name);error=0;}else{for(;sql[i]==' ';i++);if(tolower(sql[i])=='a')//增加属性{for(j=0;sql[i]!=' ';i++,j++)//判断add是否写错temp[j]=tolower(sql[i]);temp[j]='\0';if(strcmp(temp,"add")==0){int field_lengtf=0,flag=1; //field_lengtf 列长度for(;sql[i]==' ';i++);for(j=0;sql[i]!=' ';i++,j++)//提取添加的列名field_name[j]=sql[i];field_name[j]='\0';for(;sql[i]==' ';i++);for(j=0;sql[i]!=' '&&sql[i]!=';';i++,j++)//提取列数据类型f_type[j]=tolower(sql[i]);f_type[j]='\0';for(j=0;j<5;j++){if(strcmp(f_type,type[j])==0){T=Type1[j];break;}}。
数据库源代码
insert into course (course_No,course_Name,course_Type,course_Des)values('13','汇编语言','限选','无');
insert into result(exam_No,student_ID,student_Name,class_No,course_Name,result)values('小测2','2','白志凯','3','Java','76');
insert into result(exam_No,student_ID,student_Name,class_,course_Name,result)values('小测3','3','张冰燕','2','Java','89');
create table result(
exam_No char(10)primary key,
student_ID int,
student_Name varchar(50),
class_No int,
course_Name varchar(30),
result float,
foreign key(student_ID)references student(student_ID),
数据库SQL实验代码
数据库S Q L实验代码-CAL-FENGHAI-(2020YEAR-YICAI)_JINGBIAN数据库前四个实验SQL代码--实验一、数据库的定义实验--1.创建XSCJGL数据库USE MASTERIF DB_ID('XSCJGL')IS NOT NULL DROP DATABASE XSCJGLCREATE DATABASE XSCJGL--2、刷新数据库目录后，选择新出现的XSCJGL数据库，在SQL操作窗口中，创建Student、SC、Course三表及表记录插入命令如下：use xscjglif object_id('sc','u')is not null drop table scif object_id('s','u')is not null drop table sif object_id('c','u')is not null drop table cif object_id('tsc','u')is not null drop table tscif object_id('ts','u')is not null drop table tsif object_id('tc','u')is not null drop table tcCreate Table S( Sno CHAR(2)NOT NULL PRIMARY KEY(Sno),Sname CHAR(6),sex CHAR(2)DEFAULT'男'CHECK(sex='男'OR sex='女'),age SMALLINT CHECK(age>=15 AND age<=45),sdept CHAR(6));Create Table C( Cno CHAR(2)NOT NULL PRIMARY KEY(Cno),Cname VARCHAR(20),cdept CHAR(6),Tname CHAR(6),ct SMALLINT CHECK((ct IS NULL)OR(ct BETWEEN 1 AND 5)),CPNO VARCHAR(20));Create Table SC( Sno CHAR(2)NOT NULL CONSTRAINT S_F FOREIGN KEY REFERENCES S(Sno),Cno CHAR(2)NOT NULL,GRADE SMALLINT CHECK((GRADE IS NULL)OR(GRADE BETWEEN 0 AND 100)),PRIMARY KEY(Sno,Cno),FOREIGN KEY(Cno)REFERENCES C(Cno));INSERT INTO S VALUES('S1','李涛','男',22,'统计');INSERT INTO S VALUES('S2','王林','女',18,'计算机');INSERT INTO S VALUES('S3','陈高','女',15,'自动化');INSERT INTO S VALUES('S4','张杰','男',17,'自动化');INSERT INTO S VALUES('S5','吴小丽','女',19,'统计');INSERT INTO S VALUES('S6','张敏敏','女',20,'计算机');INSERT INTO S VALUES('S7','郑冬','男',19,'数学');INSERT INTO S VALUES('S8','朱雨','男',20,'数学');INSERT INTO C VALUES('C1','C语言','计算机','汪小寒','4','计算机基础');INSERT INTO C VALUES('C2','R软件','自动化','周在莹','3','线性代数');INSERT INTO C VALUES('C3','精算学','统计','刘晓','3','金融数学');INSERT INTO C VALUES('C4','计算机算法','计算机','李杰','2','C语言');INSERT INTO C VALUES('C5','数据库应用','自动化','周有顺','4','R软件');INSERT INTO C VALUES('C6','金融数学','统计','黄旭东','3','数学分析');INSERT INTO C VALUES('C7','时间序列','统计','何道江','4','精算学');INSERT INTO C VALUES('C8','数学分析','数学','周文','4','高等数学');INSERT INTO C VALUES('C9','线性代数','数学','储茂权','2','高等数学');INSERT INTO SC VALUES('S1','C1',90);INSERT INTO SC VALUES('S1','C2',85);INSERT INTO SC VALUES('S2','C1',84);INSERT INTO SC VALUES('S2','C2',94);INSERT INTO SC VALUES('S2','C3',83);INSERT INTO SC VALUES('S3','C1',73);INSERT INTO SC VALUES('S3','C7',59);INSERT INTO SC VALUES('S3','C4',88);INSERT INTO SC VALUES('S3','C5',85);INSERT INTO SC VALUES('S4','C2',65);INSERT INTO SC VALUES('S4','C5',90);INSERT INTO SC VALUES('S4','C6',79);INSERT INTO SC VALUES('S5','C2',89);INSERT INTO SC VALUES('S5','C1',84);INSERT INTO SC VALUES('S6','C5',55);INSERT INTO SC VALUES('S6','C1',null);--实验二、数据库的查询实验--使用XSCJGL库,保存好并每次实验做好备份，以后实验要继续使用。
数据库实验源代码大全
数据库实验源代码大全实验一：数据定义语言create user U099074235 IDENTIFIED BY XHM123 DEFAULT TABLESPACE DXPDA TASPACE1,创建表StudentCREATE TABLE Student (SNO CHAR(5) ,SNAME CHAR(10) NOT NULL,SDEPT CHAR(2) NOT NULL,SCLASS CHAR(2) NOT NULL,SAGE NUMBER(2),SSEX CHAR(2),CONSTRAINT SNO_PK PRIMARY KEY(SNO));2，创建表CourseCREATE TABLE Course(CNO CHAR(3),CNAME V ARCHAR2(16) ,CTIME NUMBER(3),CONSTRAINT CNO_PK PRIMARY KEY(CNO))3,创建表TeachCREATE TABLE Teach(TNO V ARCHAR(6),TNAME V ARCHAR(8),TSEX CHAR(2),CNO CHAR(3),TDA TE DATE,TDEPT CHAR(2),CONSTRAINT TT_PK PRIMARY KEY(TNO),CONSTRAINT CNO_FK FOREIGN KEY(CNO) REFERENCES Course(CNO))CREATE TABLE Teach(TNO V ARCHAR(6),TNAME V ARCHAR(8),TSEX CHAR(2),CNO CHAR(3),TAGE NUMBER(2),TDEPT CHAR(2),CONSTRAINT TT_PK PRIMARY KEY(TNO),CONSTRAINT CNO_FK FOREIGN KEY(CNO) REFERENCES Course(CNO))4,创建表ScoreCREATE TABLE Score(SNO CHAR(5),CNO CHAR(3),SCORE NUMBER(5,2),CONSTRAINT SC_PK PRIMARY KEY(SNO,CNO),CONSTRAINT SNO_FK FOREIGN KEY(SNO) REFERENCES Student(SNO), CONSTRAINT CNOM_FK FOREIGN KEY(CNO) REFERENCES Course(CNO))二、插入数据1，StudentINSERT INTO Student V ALUES('96001','马小燕','CS','01',21,'女');INSERT INTO Student V ALUES('96002','黎明','CS','01',18,'男');INSERT INTO Student V ALUES('96003','刘东明','MA','01',18,'男');INSERT INTO Student V ALUES('96004','赵志勇','IS','02',20,'男');INSERT INTO Student V ALUES('97001','马蓉','MA','02',19,'女');INSERT INTO Student V ALUES('97002','李成功','CS','01',20,'男');INSERT INTO Student V ALUES('97003','黎明','IS','03',19,'女');INSERT INTO Student V ALUES('97004','李丽','CS','02',19,'女');INSERT INTO Student V ALUES('96005','司马志明','CS','02',18,'男');2，CourseINSERT INTO Course V ALUES('001','数学分析',144);INSERT INTO Course V ALUES('002','普通物理',144);INSERT INTO Course V ALUES('003','微机原理',72);INSERT INTO Course V ALUES('004','数据结构',72);INSERT INTO Course V ALUES('005','操作系统',64);INSERT INTO Course V ALUES('006','数据库原理',64);INSERT INTO Course V ALUES('007','DB_Design',48);INSERT INTO Course V ALUES('008','程序设计',56);3，TeachINSERT INTO Teach V ALUES('9401','王成钢','男','004',TO_DA TE( '1999-09-05', 'YYYY-MM-DD'),'CS');INSERT INTO Teach V ALUES('9402','李正科','男','003',TO_DA TE( '1999-09-05', 'YYYY-MM-DD'),'CS');INSERT INTO Teach V ALUES('9403','严敏','女','001',TO_DATE( '1999-09-05', 'YYYY-MM-DD'),'MA');INSERT INTO Teach V ALUES('9404','赵高','男','004',TO_DATE( '1999-09-05', 'YYYY-MM-DD'),'IS');INSERT INTO Teach V ALUES('9405','李正科','男','003',TO_DA TE( '2000-02-23', 'YYYY-MM-DD'),'MA');INSERT INTO Teach V ALUES('9406','李玉兰','女','006',TO_DA TE( '2000-02-23', 'YYYY-MM-DD'),'CS');INSERT INTO Teach V ALUES('9407','王成钢','男','004',TO_DA TE( '2000-02-23', 'YYYY-MM-DD'),'IS');INSERT INTO Teach V ALUES('9408','马悦','女','008',TO_DATE( '2000-09-06', 'YYYY-MM-DD'),'CS');INSERT INTO Teach V ALUES('9409','王成钢','男','007',TO_DA TE( '1999-09-05', 'YYYY-MM-DD'),'CS');INSERT INTO Teach V ALUES('9401','王成钢','男','004',35,'CS');INSERT INTO Teach V ALUES('9402','李正科','男','003',40,'CS');INSERT INTO Teach V ALUES('9403','严敏','女','001',33,'MA');INSERT INTO Teach V ALUES('9404','赵高','男','004',28,'IS');INSERT INTO Teach V ALUES('9405','李正科','男','003',32,'MA');INSERT INTO Teach V ALUES('9406','李玉兰','女','006',43,'CS');INSERT INTO Teach V ALUES('9407','王成钢','男','004',49,'IS');INSERT INTO Teach V ALUES('9408','马悦','女','008',35,'CS');INSERT INTO Teach V ALUES('9409','王成钢','男','007',48,'CS');to_date( '05-09-1999', 'DD-MM-YYYY' );4,ScoreINSERT INTO Score V ALUES('96001','001',77.5);INSERT INTO Score V ALUES('96001','003',89);INSERT INTO Score V ALUES('96001','004',86);INSERT INTO Score V ALUES('96001','005',82);INSERT INTO Score V ALUES('96002','001',88);INSERT INTO Score V ALUES('96002','003',92.5);INSERT INTO Score V ALUES('96002','006',90);INSERT INTO Score V ALUES('96005','004',92);INSERT INTO Score V ALUES('96005','005',90);INSERT INTO Score V ALUES('96005','006',89);INSERT INTO Score V ALUES('96005','007',76);INSERT INTO Score V ALUES('96003','001',69);INSERT INTO Score V ALUES('97001','001',96);INSERT INTO Score V ALUES('97001','008',95);INSERT INTO Score V ALUES('96004','001',87);INSERT INTO Score V ALUES('96003','003',91);INSERT INTO Score V ALUES('97002','003',91);INSERT INTO Score V ALUES('97002','004','');INSERT INTO Score V ALUES('97002','006',92);INSERT INTO Score V ALUES('97004','005',90);INSERT INTO Score V ALUES('97004','006',85);INSERT INTO Score V ALUES('97004','008',75);INSERT INTO Score V ALUES('97003','001',59);INSERT INTO Score V ALUES('97003','003',58)实验一、1，(建立数据库表) 建立教学数据库的四个数据库表，其中Student表中不包含SSEX(C,2) 字段，Sname 字段为Sname(C,8)且可为空ALTER TABLE Student DROP COLUMN SSEXALTER TABLE Student MODIFY SNAME VARCHAR(8) NULL例1-2：(修改数据库表) 在Student表中增加SEX(C,2) 字段。
数据库设计源代码
//选择一种高级语言实现下列语句的功能。
//CREATE TABLE <表名> （<列名><数据类型>[<列完整性约束条件>][，<列名><数据//类型>[<列完整性约束条件>]…][，<表完整性约束条件>] ）//ALTER TABLE <表名> [ADD <新列名><数据类型>[<列完整性约束>]] [DROP<列完整//性约束名>][MODIFY <列名><数据类型>]//使用说明//1、将程序文件table.sql放在D盘根目录下。
//2、在C盘根目录下建立一个名为"数据库"的文件夹，用于存储表。
//3、建立的表存储路径为C:\数据库:\table.dbf。
//4、在程序文件table.sql中只有一条建表语句和三条修改表语句，在以程序方式执//行时注意执行的次数，慎重选择“是否继续执行”。
//5、程序输入的SQL语句格式如下：//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 student 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 0x44#define DOUBLE 0x45#define FLOAT 0x46#define SHORT 0x47#define INT 0x48#define TRUE 1#define FALSE 0//文件头结构体定义struct DbfHead{char dbFlag;char year;char month;char day;int recCounts;short firstRecAddr;short recLen;char undo[20];};typedef struct DbfHead DbfHead, *pDbfHead; //字段描述结构体定义struct FieldDcp{char fieldName[10];char undo1;char fieldType;short offset;char undo2[2];char fieldLen;char numDecis;char undo3[14];};typedef struct FieldDcp FieldDcp, *pFieldDcp; //字段数据结构体定义struct DbfField{char *fieldData;char fieldName[10];char fieldType;char fieldLen;char fieldDcis;short offset;};typedef struct DbfField DbfField, *pDbfField; //记录结构体定义struct DbfRecchar delFlag;DbfField field[MAX];int realCounts;};typedef struct DbfRec DbfRec, *pDbfRec;//dbf文件句柄定义struct DbfHand{char filename[50];DbfHead header;DbfRec rec;long curRecNo;long curFpAddr;FILE *fd;};typedef struct DbfHand DbfHand, *pDbfHand;//约束条件typedef struct Condition{int flag;//flag 用于区分约束条件(PARIMARY KEY 1 UNIQUE 2 NOT NULL 3) }Condition;pDbfHand f;DbfRec rec;char table_name[20];char sql[300],sql1[300];char GetDate(int getMode)//获取日期{if( DAY ==getMode)return 01;if(MONTH == getMode)return 06;if(YEAR == getMode)return 10;elseexit(0);}short GetHeadLength(pDbfRec rec)//获得文件头长度{return rec->realCounts*32+32+2;}short GetRecLength(pDbfRec rec)//获得文件体长度register int i=0;int count=0;for(i=0;i<rec->realCounts;i++){if(rec->field[i].fieldType=='D'){count+=8;continue;}else if(rec->field[i].fieldType=='I'){count+=4;continue;}else if(rec->field[i].fieldType=='T'){count+=6;continue;}count+=rec->field[i].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-> 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 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};field.offset = 0x01;for ( i = 0; i < rec-> realCounts; i++){field.fieldType = rec-> field[i].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;default:field.fieldLen = rec-> field[i].fieldLen;break;}strcpy(field.fieldName, rec-> field[i].fieldName);field.offset +=( short)rec-> field[i].fieldLen;if(-1 ==fwrite((char*)&field, sizeof(FieldDcp) , 1 , hand->fd))return NULL;}hand-> rec.delFlag = 0x20;hand-> rec.realCounts = rec-> realCounts;for (i = 0; i < rec-> realCounts; i++ )hand-> rec.field[i] = rec-> field[i];return hand;void WriteFieldEnd(pDbfHand hand)//写文件头结束标志{int i = 0;char buf[2] = {0x0D, 0x00};char versionBuf[FOX_VERISON_INFO] = {0};if (0 == fseek(hand-> fd, 0, SEEK_END)){if (-1== fwrite(buf, 2 , 1 , hand->fd))return;}return ;}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)//得到字段个数{int i = 0;int offset = 1;FieldDcp field ={0};char cEnd = 0;for (i=0;i<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 = 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.field[i].fieldName,field.fieldName,sizeof(field.fieldName));hand-> rec.field[i].fieldLen = field.fieldLen;hand-> rec.field[i].fieldType = field.fieldType;hand-> rec.field[i].fieldDcis = field.numDecis;hand-> rec.field[i].offset = field.offset;}for(i=0;i<hand->rec.realCounts;i++){for(j=0;hand->rec.field[i].fieldName[j]!='\0';j++){hand->rec.field[i].fieldName[j]=tolower(hand->rec.field[i].fieldName[j]);}}return hand;}char *SaveDate( char *str)//保存内容为日期时，格式华{int i=0, j=0;char temp[9]={0};if (strlen(str) != 10) //日期格式输入不合法return NULL;for ( i=0, j=0; i <10; i++ ){if ( i == 4 || i ==7 )continue;else{if(*(str+i) >= '0' && *(str+i) <= '9'){temp[j]=*(str+i);j++;}elsereturn NULL;}}str=NULL;str=( char *)realloc(str, 8);memset(str, 0x00, 8);memcpy(str, temp, 8);return str;}int WriteRecord(pDbfHand hand, pDbfRec record)//在当前位置写一条纪录,覆盖原有内容{int i = 0;char *buf = NULL;int curFieldLen = 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);buf[0] = ' ';for ( i = 0; i < nFiledCount; i++ ){if (hand-> rec.field[i].fieldType == DATE && record-> field[i].fieldData != NULL ) record-> field[i].fieldData = SaveDate(record-> field[i].fieldData);curFieldLen = hand-> rec.field[i].fieldLen;actDataLen = strlen(record-> field[i].fieldData);if ( actDataLen > curFieldLen)actDataLen = curFieldLen;memcpy(&buf[nPos], record-> field[i].fieldData, actDataLen);nPos += hand-> rec.field[i].fieldLen;}buf[hand-> header.recLen] = '\0 ';if ( -1 == fwrite(buf, hand-> header.recLen , 1 , hand->fd))rest = FALSE;return rest;}char *ReadDbfDate( char *str)//读日期时，格式化{int i=0, j=0;char temp[11] = {0};char *strRest = NULL;for ( i=0, j=0; i < 8; i++, j++ ){if ( i==4 || i==6){temp[j] = '/';j++;}temp[j]=*(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;str[i] =0x00;}return str;}/*pDbfField GetCurrentField(pDbfHand handle, pDbfRec rec ,int fieldId)//得到当前文件{return (pDbfField)&(rec-> field[fieldId]);}*/int GetFieldNum(pDbfHand hand,char* field_name)//得到文件个数{int flag=0;int j;for(j=0;j<hand->rec.realCounts;j++){if(0==strcmp(field_name,hand->rec.field[j].fieldName)){flag=1;break;}}if(flag)return j;elsereturn -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-> header.firstRecAddr;if (-1 == fseek(hand-> fd, hand-> curFpAddr, SEEK_SET))return FALSE;return TRUE;}int GotoBottom(pDbfHand hand)//移动到最后一条记录{int recLen = hand-> header.recLen;long offset = 0;offset = recLen * (hand-> header.recCounts -1);hand-> curRecNo = hand-> header.recCounts;hand-> curFpAddr = hand-> header.firstRecAddr + offset;if (-1 == fseek(hand-> fd, hand-> curFpAddr, SEEK_SET))return FALSE;return TRUE;}int GoNextRecord(pDbfHand hand)//移动到下一条记录{if (TRUE == IsBottomRecord(hand))return TRUE;else{hand-> curRecNo += 1;hand-> curFpAddr += hand-> header.recLen;if (-1 == fseek(hand-> fd, hand-> header.recLen, SEEK_CUR)) return FALSE;}return TRUE;}int GetCurRecord(pDbfHand hand,pDbfRec rec)//得到当前记录{int i = 0,m;char curFieldLen = 0;long fieldOffset = 0;char pBuffData[1024] = {0};char *recData;(*rec)=hand->rec;for(m=0;m<hand->rec.realCounts;m++){rec->field[m].fieldData=(char*)malloc(hand->rec.field[m].fieldLen+1);memset(rec->field[m].fieldData, 0x00, hand->rec.field[m].fieldLen+1);}if (-1== fseek(hand->fd,hand->curFpAddr, SEEK_SET)) //Move file pointer return 0;recData = pBuffData;recData = recData + 1;if (-1 == fread(recData,hand->header.recLen , 1 , hand->fd))return 0;rec->delFlag = recData[0];recData = recData + 1;for (i = 0; i < hand->rec.realCounts; i++, fieldOffset += curFieldLen){curFieldLen = hand->rec.field[i].fieldLen;memcpy(rec->field[i].fieldData,recData+fieldOffset,curFieldLen);rec->field[i].fieldData = DsdStrEndSpace(rec->field[i].fieldData, curFieldLen);if (rec->field[i].fieldType == DATE && rec->field[i].fieldData[0] != 0x20 ) rec->field[i].fieldData =ReadDbfDate(rec->field[i].fieldData);}return 1;}pDbfHand AddRec(pDbfHand hand, pDbfRec record)//向文件尾中插入一条记录{char dataEndFlag = 0x1A;if ((hand-> fd =fopen("c:\\数据库\\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-> curRecNo += 1;hand-> header.recCounts += 1;hand-> curFpAddr += hand-> header.recLen;if (NULL == UpdateHead(hand))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=(pDbfRec*)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++){rec[i]=(pDbfRec)malloc(sizeof(DbfRec));GetCurRecord(hand,rec[i]);GoNextRecord(hand);}for(i=0;i<hand->header.recCounts;i++){rec[i]->field[hand->rec.realCounts].fieldData=(char*)malloc(len+1);memset(rec[i]->field[hand->rec.realCounts].fieldData, 0x00, len+1);rec[i]->realCounts++;}}strcpy(hand->rec.field[hand->rec.realCounts].fieldName,field_name);hand->rec.field[hand->rec.realCounts].fieldName[strlen(field_name)]='\0';hand->rec.field[hand->rec.realCounts].fieldType=type;hand->rec.field[hand->rec.realCounts].fieldLen=len;hand->rec.field[hand->rec.realCounts].fieldDcis=dec;hand->rec.realCounts++;reccounts=hand->header.recCounts;hand->fd=fopen("c:\\数据库\\table.dbf","w");fclose(hand->fd);hand->fd=fopen("c:\\数据库\\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))for(i=0;i<reccounts;i++)AddRec(hand,rec[i]);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->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++){rec[i]=(pDbfRec)malloc(sizeof(DbfRec));GetCurRecord(hand,rec[i]);GoNextRecord(hand);rec[i]->field[num].fieldDcis=dec;rec[i]->field[num].fieldLen=len;rec[i]->field[num].fieldType=type;}}hand->rec.field[num].fieldType=type;hand->rec.field[num].fieldLen=len;hand->rec.field[num].fieldDcis=dec;reccounts=hand->header.recCounts;hand->fd=fopen("c:\\数据库\\table.dbf","w");fclose(hand->fd);hand->fd=fopen("c:\\数据库\\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,rec[i]);return hand;}pDbfHand 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 == fseek(hand-> fd,hand->curFpAddr, SEEK_SET))return FALSE;for(i=0;i<hand->header.recCounts;i++){rec[i]=(pDbfRec)malloc(sizeof(DbfRec));GetCurRecord(hand,rec[i]);GoNextRecord(hand);rec[i]->field[num].fieldDcis=rec[i]->field[num+1].fieldDcis;rec[i]->field[num].fieldLen=rec[i]->field[num+1].fieldLen;rec[i]->field[num].fieldType=rec[i]->field[num+1].fieldType;num++;}}if(num==-1){for(j=0;j<10;j++)hand->rec.field[num].fieldName[j]=0;hand->rec.field[num].fieldType=0;hand->rec.field[num].fieldLen=0;hand->rec.field[num].fieldDcis=0;}else{for(;num<n;num++){strcpy(hand->rec.field[num].fieldName,hand->rec.field[num+1].fieldName);hand->rec.field[num].fieldType=hand->rec.field[num+1].fieldType;hand->rec.field[num].fieldLen=hand->rec.field[num+1].fieldLen;hand->rec.field[num].fieldDcis=hand->rec.field[num+1].fieldDcis;}}hand->header.recCounts--;reccounts=hand->header.recCounts;hand->fd=fopen("c:\\数据库\\table.dbf","w");fclose(hand->fd);hand->fd=fopen("c:\\数据库\\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,rec[i]);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:\\数据库\\table.dbf","w+b"))!=NULL){if(NewDbfHead(hand, &rec) == NULL)return NULL;if(NULL == WriteFieldDicsribe(hand, &rec))return NULL;WriteFieldEnd(hand);}fclose(hand->fd);return hand;}pDbfHand OpenDbf()//打开DBF文件{pDbfHand hand = NULL;if ((hand = (pDbfHand) malloc(sizeof(DbfHand))) ==NULL) return NULL;memset(hand, 0x00, sizeof(DbfHand));if ((hand-> fd =fopen("c:\\数据库\\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("| 欢迎进入SQL系统|\n");printf("||\n");printf("+----------------------------------------------------------------------------+\n");}void Show(){printf(" 输入语句格式如下：\n");printf(" create table student\n");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 int\n");printf(" );\n");printf(" alter table student add CNO int NOT NULL;\n");printf(" alter table student alter column SAGE short;\n");printf(" alter table student drop SDEPT;\n");}int Read()//用于读取从键盘键入的SQL语句{char c;int i,j;printf("+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++\n");printf("请输入SQL语句:\n\n");for(i=0;(c=getch())!=';';i++){if(c==27)exit(0);if(c==8){i-=2;if(i<-1)i=-1;system("cls");Show();printf("+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++\n");printf("请输入SQL语句:\n\n");for(j=0;j<=i;j++){if(sql1[j]==13){puts("");}printf("%c",sql1[j]);}continue;}sql1[i]=c;if(c==13){puts("");sql[i]=' ';}else if(c=='('||c==')'||c=='\''){printf("%c",c);sql[i]=' ';}else{printf("%c",c);sql[i]=c;}}sql[i]=';';sql[i+1]='\0';printf(";");puts("");return 0;}int position;//文件指针的位置int Read1()//用于读取从程序文件中读取的SQL语句{FILE *fp;char c;int i,j;printf("+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++\n");printf("请输入SQL语句:\n\n");fp=fopen("d:\\table.sql","r");fseek(fp,position,0);for(i=0;(c=fgetc(fp))!=';';i++){if(c==27)exit(0);if(c==8){i-=2;if(i<-1)i=-1;system("cls");Show();printf("+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++\n");printf("请输入SQL语句:\n\n");for(j=0;j<=i;j++){if(sql1[j]==13)puts("");printf("%c",sql1[j]);}continue;}sql1[i]=c;if(c==13){puts("");sql[i]=' ';}else if(c=='('||c==')'||c=='\''){printf("%c",c);sql[i]=' ';}else{printf("%c",c);sql[i]=c;}}sql[i]=';';sql[i+1]='\0';printf(";");puts("");position=ftell(fp);return 0;}int CREATE(){char type[6][10]={"char","int","short","float","double"};char Type1[6]={'C','N','S','F','D'};FILE *fp=NULL;Condition con[MAX];//约束条件结构体int i,j,num=0,error=1;//num 字段数error输入错误标志char temp[10],condition[10];//table_name 表名condition 约束条件memset(con,0,sizeof(con[0])*MAX);//结构体中各值初始化为空for(i=0;sql[i]==' ';i++);for(j=0;sql[i]!=' '&&sql[i]!=';';i++,j++)temp[j]=tolower(sql[i]);temp[j]='\0';if(strcmp(temp,"create")!=0)//判断create是否写错{error=0;printf("\n你输入的'create'有误，请重新创建!\n");}else{for(;sql[i]==' ';i++);for(j=0;sql[i]!=' '&&sql[i]!=';';i++,j++)temp[j]=tolower(sql[i]);temp[j]='\0';if(strcmp(temp,"table")!=0)//判断table是否写错{error=0;printf("\n你输入的'table'有误，请重新创建!\n");}else{for(;sql[i]==' ';i++);for(j=0;sql[i]!=' '&&sql[i]!=';';i++,j++)//提取表名table_name[j]=tolower(sql[i]);table_name[j]='.'; //加后缀.dbftable_name[j+1]='d';table_name[j+2]='b';table_name[j+3]='f';table_name[j+4]='\0';do{int field_lengtf=0,flag=1;//field_lengtf 字段长度for(;sql[i]==' '||sql[i]==',';i++);if(sql[i]==';')break;for(j=0;sql[i]!=' ';i++,j++)//提取列名rec.field[num].fieldName[j]=sql[i];rec.field[num].fieldName[j]='\0';for(;sql[i]==' ';i++);for(j=0;sql[i]!=' '&&sql[i]!=',';i++,j++)//提取列数据类型temp[j]=tolower(sql[i]);temp[j]='\0';for(j=0;j<5;j++){if(strcmp(temp,type[j])==0){rec.field[num].fieldType=Type1[j];break;}}if(j==5)//列数据类型有误{error=0;printf("\n你输入的列数据类型有误(务必是char,int,short,float,double)，请重新创建!\n");break;}else{/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++++++++++++++++++++++++*/if(rec.field[num].fieldType=='C'){//如果类型是char需要提取列长度，如果是int、float则固定列长度为4,double则固定列长度为8for(;sql[i]==' ';i++);for(;sql[i]!=' ';i++)//提取长度{if(sql[i]<48||sql[i]>58)//列长度必须是数字{flag=0;break;}elsefield_lengtf=field_lengtf*10+(sql[i]-48);}if(flag==0){error=0;printf("\n你输入的列长度有误，请重新创建!\n");break;}elserec.field[num].fieldLen=field_lengtf;}else if(rec.field[num].fieldType=='N'||rec.field[num].fieldType=='F') rec.field[num].fieldLen=4;else if(rec.field[num].fieldType=='D')rec.field[num].fieldLen=8;else if(rec.field[num].fieldType=='S')rec.field[num].fieldLen=2;field_lengtf=0,flag=1;for(;sql[i]==' ';i++);for(j=0;sql[i]!=' '&&sql[i]!=','&&sql[i]!=';';i++,j++)//提取列完整性约束condition[j]=tolower(sql[i]);condition[j]='\0';if(condition[0]!=','&&condition[0]!='\0'){if(strcmp(condition,"primary")==0)//判断是否为主键{for(;sql[i]==' ';i++);for(j=0;sql[i]!=' '&&sql[i]!=',';i++,j++)temp[j]=tolower(sql[i]);temp[j]='\0';if(strcmp(temp,"key")==0)con[num].flag=1;else{error=0;printf("\n你输入的'KEY'有误，请重新创建!\n");break;}}else if(strcmp(condition,"unique")==0)//判断是否取唯一值con[num].flag=2;else if(strcmp(condition,"not")==0)//判断是否非空{for(;sql[i]==' ';i++);for(j=0;sql[i]!=' '&&sql[i]!=',';i++,j++)condition[j]=tolower(sql[i]);condition[j]='\0';if(strcmp(condition,"null")==0)con[num].flag=3;else{error=0;printf("\n你输入的约束条件'NOT NULL'有误，请重新创建!\n");break;}}else//列完整性约束出错{error=0;printf("\n你输入的约束条件有误(必须PARIMARY KEY,UNIQUE,NOT NULL)，请重新创建!\n");break;}}}rec.field[num].fieldDcis=0;rec.field[num].offset=0;rec.realCounts=num+1;num++;//下一字段}while(sql[i]!=';');if((fp=fopen("d:\\student.dat","w+b"))==NULL)//将列完整性约束条件写入文件中{printf("打开文件失败！\n");error=0;}fwrite(&con,sizeof(Condition)*num,1,fp);fclose(fp);fp=NULL;}}if(error==0){memset(sql,0,sizeof(char)*300);//出错时清空输入的语句return 0;}else{if(f!=CreateDbf(rec))//建立.dbf文件return 1;elsereturn 0;}return 1;}int ALTER(){char type[6][10]={"char","int","short","float","double"};char Type1[6]={'C','N','S','F','D'};FILE *fp;int i,j,num,error=1;char T; //列数据类型的简写Condition con[MAX];//约束条件结构体char temp[10],field_name[10],f_type[10],condition[10];// field_name 列名f_type 列数据类型memset(&con,0,sizeof(con));//结构体中各值初始化为空for(i=0;sql[i]==' ';i++);for(j=0;sql[i]!=' ';i++,j++)temp[j]=tolower(sql[i]);temp[j]='\0';if(strcmp(temp,"alter")!=0)//判断alter是否写错{error=0;printf("\n你输入的'alter'有误，请重新输入SQL语句!\n");}else{for(;sql[i]==' ';i++);for(j=0;sql[i]!=' ';i++,j++)temp[j]=tolower(sql[i]);temp[j]='\0';if(strcmp(temp,"table")!=0)//判断table是否写错{error=0;printf("\n你输入的'table'有误，请重新输入SQL语句!\n");}else{for(;sql[i]==' ';i++);for(j=0;sql[i]!=' ';i++,j++)//提取表名table_name[j]=tolower(sql[i]);table_name[j]='.'; //加后缀.dbftable_name[j+1]='d';table_name[j+2]='b';table_name[j+3]='f';table_name[j+4]='\0';if((f=OpenDbf())==0) //判断该表是否存在{printf("\n你输入的表%s不存在，请重新输入SQL语句!\n",table_name);error=0;}else{for(;sql[i]==' ';i++);if(tolower(sql[i])=='a')//增加属性{for(j=0;sql[i]!=' ';i++,j++)//判断add是否写错temp[j]=tolower(sql[i]);temp[j]='\0';if(strcmp(temp,"add")==0){int field_lengtf=0,flag=1; //field_lengtf 列长度for(;sql[i]==' ';i++);for(j=0;sql[i]!=' ';i++,j++)//提取添加的列名field_name[j]=sql[i];field_name[j]='\0';for(;sql[i]==' ';i++);for(j=0;sql[i]!=' '&&sql[i]!=';';i++,j++)//提取列数据类型f_type[j]=tolower(sql[i]);f_type[j]='\0';for(j=0;j<5;j++){if(strcmp(f_type,type[j])==0){T=Type1[j];break;}。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
数据库实验源代码大全实验一：数据定义语言create user U099074235 IDENTIFIED BY XHM123 DEFAULT TABLESPACE DXPDA TASPACE1,创建表StudentCREATE TABLE Student (SNO CHAR(5) ,SNAME CHAR(10) NOT NULL,SDEPT CHAR(2) NOT NULL,SCLASS CHAR(2) NOT NULL,SAGE NUMBER(2),SSEX CHAR(2),CONSTRAINT SNO_PK PRIMARY KEY(SNO));2，创建表CourseCREATE TABLE Course(CNO CHAR(3),CNAME V ARCHAR2(16) ,CTIME NUMBER(3),CONSTRAINT CNO_PK PRIMARY KEY(CNO))3,创建表TeachCREATE TABLE Teach(TNO V ARCHAR(6),TNAME V ARCHAR(8),TSEX CHAR(2),CNO CHAR(3),TDA TE DATE,TDEPT CHAR(2),CONSTRAINT TT_PK PRIMARY KEY(TNO),CONSTRAINT CNO_FK FOREIGN KEY(CNO) REFERENCES Course(CNO))CREATE TABLE Teach(TNO V ARCHAR(6),TNAME V ARCHAR(8),TSEX CHAR(2),CNO CHAR(3),TAGE NUMBER(2),TDEPT CHAR(2),CONSTRAINT TT_PK PRIMARY KEY(TNO),CONSTRAINT CNO_FK FOREIGN KEY(CNO) REFERENCES Course(CNO))4,创建表ScoreCREATE TABLE Score(SNO CHAR(5),CNO CHAR(3),SCORE NUMBER(5,2),CONSTRAINT SC_PK PRIMARY KEY(SNO,CNO),CONSTRAINT SNO_FK FOREIGN KEY(SNO) REFERENCES Student(SNO), CONSTRAINT CNOM_FK FOREIGN KEY(CNO) REFERENCES Course(CNO))二、插入数据1，StudentINSERT INTO Student V ALUES('96001','马小燕','CS','01',21,'女');INSERT INTO Student V ALUES('96002','黎明','CS','01',18,'男');INSERT INTO Student V ALUES('96003','刘东明','MA','01',18,'男');INSERT INTO Student V ALUES('96004','赵志勇','IS','02',20,'男');INSERT INTO Student V ALUES('97001','马蓉','MA','02',19,'女');INSERT INTO Student V ALUES('97002','李成功','CS','01',20,'男');INSERT INTO Student V ALUES('97003','黎明','IS','03',19,'女');INSERT INTO Student V ALUES('97004','李丽','CS','02',19,'女');INSERT INTO Student V ALUES('96005','司马志明','CS','02',18,'男');2，CourseINSERT INTO Course V ALUES('001','数学分析',144);INSERT INTO Course V ALUES('002','普通物理',144);INSERT INTO Course V ALUES('003','微机原理',72);INSERT INTO Course V ALUES('004','数据结构',72);INSERT INTO Course V ALUES('005','操作系统',64);INSERT INTO Course V ALUES('006','数据库原理',64);INSERT INTO Course V ALUES('007','DB_Design',48);INSERT INTO Course V ALUES('008','程序设计',56);3，TeachINSERT INTO Teach V ALUES('9401','王成钢','男','004',TO_DA TE( '1999-09-05', 'YYYY-MM-DD'),'CS');INSERT INTO Teach V ALUES('9402','李正科','男','003',TO_DA TE( '1999-09-05', 'YYYY-MM-DD'),'CS');INSERT INTO Teach V ALUES('9403','严敏','女','001',TO_DATE( '1999-09-05', 'YYYY-MM-DD'),'MA');INSERT INTO Teach V ALUES('9404','赵高','男','004',TO_DATE( '1999-09-05', 'YYYY-MM-DD'),'IS');INSERT INTO Teach V ALUES('9405','李正科','男','003',TO_DA TE( '2000-02-23', 'YYYY-MM-DD'),'MA');INSERT INTO Teach V ALUES('9406','李玉兰','女','006',TO_DA TE( '2000-02-23', 'YYYY-MM-DD'),'CS');INSERT INTO Teach V ALUES('9407','王成钢','男','004',TO_DA TE( '2000-02-23', 'YYYY-MM-DD'),'IS');INSERT INTO Teach V ALUES('9408','马悦','女','008',TO_DATE( '2000-09-06', 'YYYY-MM-DD'),'CS');INSERT INTO Teach V ALUES('9409','王成钢','男','007',TO_DA TE( '1999-09-05', 'YYYY-MM-DD'),'CS');INSERT INTO Teach V ALUES('9401','王成钢','男','004',35,'CS');INSERT INTO Teach V ALUES('9402','李正科','男','003',40,'CS');INSERT INTO Teach V ALUES('9403','严敏','女','001',33,'MA');INSERT INTO Teach V ALUES('9404','赵高','男','004',28,'IS');INSERT INTO Teach V ALUES('9405','李正科','男','003',32,'MA');INSERT INTO Teach V ALUES('9406','李玉兰','女','006',43,'CS');INSERT INTO Teach V ALUES('9407','王成钢','男','004',49,'IS');INSERT INTO Teach V ALUES('9408','马悦','女','008',35,'CS');INSERT INTO Teach V ALUES('9409','王成钢','男','007',48,'CS');to_date( '05-09-1999', 'DD-MM-YYYY' );4,ScoreINSERT INTO Score V ALUES('96001','001',77.5);INSERT INTO Score V ALUES('96001','003',89);INSERT INTO Score V ALUES('96001','004',86);INSERT INTO Score V ALUES('96001','005',82);INSERT INTO Score V ALUES('96002','001',88);INSERT INTO Score V ALUES('96002','003',92.5);INSERT INTO Score V ALUES('96002','006',90);INSERT INTO Score V ALUES('96005','004',92);INSERT INTO Score V ALUES('96005','005',90);INSERT INTO Score V ALUES('96005','006',89);INSERT INTO Score V ALUES('96005','007',76);INSERT INTO Score V ALUES('96003','001',69);INSERT INTO Score V ALUES('97001','001',96);INSERT INTO Score V ALUES('97001','008',95);INSERT INTO Score V ALUES('96004','001',87);INSERT INTO Score V ALUES('96003','003',91);INSERT INTO Score V ALUES('97002','003',91);INSERT INTO Score V ALUES('97002','004','');INSERT INTO Score V ALUES('97002','006',92);INSERT INTO Score V ALUES('97004','005',90);INSERT INTO Score V ALUES('97004','006',85);INSERT INTO Score V ALUES('97004','008',75);INSERT INTO Score V ALUES('97003','001',59);INSERT INTO Score V ALUES('97003','003',58)实验一、1，(建立数据库表) 建立教学数据库的四个数据库表，其中Student表中不包含SSEX(C,2) 字段，Sname 字段为Sname(C,8)且可为空ALTER TABLE Student DROP COLUMN SSEXALTER TABLE Student MODIFY SNAME VARCHAR(8) NULL例1-2：(修改数据库表) 在Student表中增加SEX(C,2) 字段。