您的位置：360文档中心› 数据库实验源代码大全

数据库实验源代码大全

合集下载

ASP数据库实验源代码清单

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代码

数据库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('11','体育','必修','无');
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实验代码

数据库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;}。

实验报告源代码

实验报告源代码

实验名称：使用Python实现冒泡排序实验目的：掌握冒泡排序的原理，并使用Python实现冒泡排序算法。

实验环境：Python 3.7及以上版本实验步骤：1. 创建一个列表，存储待排序的数值。

2. 编写冒泡排序函数，实现排序过程。

3. 调用冒泡排序函数，对列表进行排序。

4. 输出排序后的列表。

```python# 定义冒泡排序函数def bubble_sort(arr):n = len(arr)# 遍历所有数组元素for i in range(n):# 最后i个元素已经是排序好的for j in range(0, n-i-1):# 遍历数组从0到n-i-1# 交换如果发现元素是逆序的if arr[j] > arr[j+1]:arr[j], arr[j+1] = arr[j+1], arr[j]# 创建一个待排序的列表data = [64, 34, 25, 12, 22, 11, 90]# 调用冒泡排序函数bubble_sort(data)# 输出排序后的列表print("排序后的列表：")for i in data:print("%d" % i, end=" ")```实验结果：排序后的列表：11 12 22 25 34 64 90实验总结：通过本次实验，我们掌握了冒泡排序的原理，并成功使用Python实现了冒泡排序算法。

在实验过程中，我们首先创建了一个待排序的列表，然后编写了冒泡排序函数，最后调用该函数对列表进行排序。

实验结果表明，冒泡排序能够将列表中的元素按照从小到大的顺序排列。

数据库及其应用实验代码sql

数据库及其应用实验代码sql

Sy4create view View1asselect worker.职工号,worker.姓名,部门编号,工资from worker join salaryon worker.职工号=salary.职工号where 日期='2004-02-03'create view View2asselect 职工号,姓名,avg(工资) as 平均工资from salary group by 职工号,姓名create view View3asselect 部门名称,avg(工资) as 平均工资from depart join worker join salaryon worker.职工号=salary.职工号on depart.部门编号=worker.部门编号group by 部门名称sy3.SELECT year(getdate())-year([出生日期]) as 年龄FROM worker2.select 部门编号,count(*) as 人数from worker where 党员否= '是' group by 部门编号3.select 姓名,工资from salary where 日期= '2001-04-04' order by 工资desc4.select top 5 * from salary where 日期= '2001-04-04' order by 工资5.select 职工号,姓名,avg(工资) from salary group by 职工号,姓名6.select salary.职工号,salary.姓名,部门编号,salary.工资from worker join salaryon worker.职工号= salary.职工号where 日期= '2001-04-04'order by 部门编号7.select 部门名称,avg(工资) from depart join worker join salaryon worker.职工号= salary.职工号on depart.部门编号= worker.部门编号group by 部门名称8.select 部门名称,avg(工资) from depart join worker join salaryon worker.职工号= salary.职工号on depart.部门编号= worker.部门编号group by 部门名称having avg(工资)>12009.select worker.职工号,worker.姓名,部门名称,日期,工资from depart join worker join salary on worker.职工号= salary.职工号on depart.部门编号= worker.部门编号where 工资= (select max(工资) from salary)10.select distinct 职工号,姓名from salary where 职工号in(select 职工号from salary group by 职工号having avg(工资)<(select avg(工资) from salary))11.select * from worker where 部门编号= (select 部门编号from worker where 姓名= '刘欣')12.insert into depart(部门编号,部门名称) values ('6','公关') select * from depart13.update depart set depart.部门名称= '销售处'where 部门编号= '5' select * from depart14.delete from depart where 部门编号= '5' select * from depart5.1declare @num int,@sname char(20)declare depart_cursor cursor for select 部门编号,部门名称from depart order by 部门名称open depart_cursorfetch from depart_cursor into @num,@snamewhile @@fetch_status = 0beginprint str(@num)+@snamefetch from depart_cursor into @num,@snameendclose depart_cursordeallocate depart_cursor5.2declare @work datetime,@num int,@sname char(20),@sex char(2),@dang char(2),@bumen int,@csrq datetimedeclare worker_cursor cursor for select 参加工作,职工号,姓名,性别,党员否,部门编号,出生日期from workeropen worker_cursorfetch from worker_cursor into @work,@num,@sname,@sex,@dang,@bumen,@csrqwhile @@fetch_status = 0beginprintconvert(varchar(30),@work,111)+str(@num)+@sname+@sex+@dang+str(@bumen)+convert(var char(30),@csrq,111)fetch from worker_cursor into @work,@num,@sname,@sex,@dang,@bumen,@csrqendclose worker_cursordeallocate worker_cursor6.1create procedure st_proc;1@x float(8)=null,@y float(8)=nullasif (@x is null or @y is null)beginprint'输入工资范围'return(1)endelsebeginselect depart.部门编号,部门名称,worker.职工号,worker.姓名,工资from depart join worker join salary on worker.职工号=salary.职工号on depart.部门编号=worker.部门编号where 工资between @x and @yreturn(0)endgoexecute st_proc;1 1000,1500create procedure st_proc;5@x float(8)=null,@y float(8)=nullasif (@x is null or @y is null)beginprint'输入工资范围'return(1)endelsebeginselect depart.部门编号,部门名称,worker.职工号,worker.姓名,工资from depart join worker join salary on worker.职工号=salary.职工号on depart.部门编号=worker.部门编号where 工资between @x and @yreturn(0)endgoexecute st_proc;5 10006.2create procedure st_proc;3@num char(4) = nullasif(@num is null)select * from workerelseselect * from worker where 职工号=@numgoexecute st_proc;3 '1002'create procedure st_proc;4@num char(4) = nullasif(@num is null)select * from workerelseselect * from worker where 职工号=@numgoexecute st_proc;47.1create trigger depart_d_trigger111on depart for deleteasprint'删除成功！'update workerset abc=nullwhere abc=(select 部门编号from deleted)delete from depart where 部门名称='人事处'select * from worker7.2create trigger worker_ins_triggeron worker for insertasif(select count(*) from depart w,inserted i where w.部门编号=i.部门编号)=0 beginrollback transactionprint '非法部门号'endinsert into worker .....select * from worker7.3create trigger worker_upd_triggeron worker for updateasdeclare @num1 char(10),@num2 char(10)if update (部门编号)beginif(select count(*) from worker w,inserted i where w.部门编号=i.部门编号)=0beginrollback transactionprint '非法部门号'endelsebeginselect @num1=部门编号from depart where 部门名称='市场部'select @num2=部门编号from insertedif @num1=@num2update salary set 工资= 工资*1.15endendupdate worker set 部门编号=？where 职工号='?'select * from workerselect * salary实验五declare cursor_depart1 cursorfor select 部门编号,部门名称from depart order by 部门编号declare @n char(10),@m char(10)open cursor_depart1fetch from cursor_depart1 into @n,@mwhile @@fetch_status=0beginprint @n+@mfetch from cursor_depart1 into @n,@mendclose cursor_depart1deallocate cursor_depart1declare cursor_depart2 cursorfor select 部门名称,职工号,姓名,性别,出生日期,党员否,参加工作from worker,depart where worker.部门编号=depart.部门编号order by depart.部门编号declare @dname char(8),@wnumber char(4),@wname char(8),@wsex char(2),@wbirthday datetime,@wtf bit,@waw datetimeopen cursor_depart2fetch from cursor_depart2 into @dname,@wnumber,@wname,@wsex,@wbirthday,@wtf,@waw while @@fetch_status=0beginPrint @dname+@wnumber+' '+@wname+@wsex+' '+convert(varchar(30),@wbirthday,111)+str(@wtf)+' '+convert(varchar(30),@waw,111)fetch from cursor_depart2 into @dname,@wnumber,@wname,@wsex,@wbirthday,@wtf,@wawendclose cursor_depart2deallocate cursor_depart2declare @n char(10),@m char(10)declare @wnumber char(4),@wname char(8),@wsex char(2),@wbirthday datetime,@wtf bit,@waw datetimedeclare cursor_depart1 cursorfor select 部门编号,部门名称from depart order by 部门编号open cursor_depart1fetch from cursor_depart1 into @n,@mwhile @@fetch_status=0beginprint '部门编号:'+@n+' '+'部门名称:'+@m+'的全体员工信息'print '职工号'+' '+'职工姓名'+' '+'性别'+' '+'出生日期'+' '+'党员否'+' '+'参加工作' declare cursor_depart2 cursorfor select 职工号,姓名,性别,出生日期,党员否,参加工作from worker where 部门编号=@n open cursor_depart2fetch from cursor_depart2 into @wnumber,@wname,@wsex,@wbirthday,@wtf,@wawwhile @@fetch_status=0beginPrint @wnumber+' '+@wname+' '+@wsex+' '+convert(varchar(30),@wbirthday,111)+str(@wtf)+' '+convert(varchar(30),@waw,111) fetch from cursor_depart2 into @wnumber,@wname,@wsex,@wbirthday,@wtf,@wawendprint ' 'close cursor_depart2deallocate cursor_depart2fetch from cursor_depart1 into @n,@mendclose cursor_depart1deallocate cursor_depart1。

数据库设计以及源代码

数据库设计以及源代码

系统建设详细设计1、数据库设计管理员用户表信息:学生用户表:学生信息表:班级文本50 所在班级专业文本50 所学专业2 系统程序文件设计与编写与数据库连接的代码:<% db="message、mdb"set Conn=server、createobject("adodb、Connection")conn、open "Provider=Microsoft、Jet、OLEDB、4、0;Data Source="&server、mappath(db)%>登陆界面代码:代码为:<!--#include file="conn、inc" --><%if request("Submit")<>"" thenset rs=server、createobject("adodb、recordset")sql="SELECT username,password from users where username='"&request("username")&"'"rs、open sql,conn,1,3if rs、bof and rs、eof thenmsg="错误:用户名不存在"elseif rs("password")=request("password") thenSession("admin")=trueresponse、redirect "index、asp?users=admin"elsemsg="错误:密码不正确"end ifend ifrs、closeset rs=nothingConn、closeSet conn = Nothingend if%><html><head><title>管理员</title><script language="JavaScript" type="text/JavaScript"><!--function MM_jumpMenu(targ,selObj,restore){ //v3、0eval(targ+"、location='"+selObj、options[selObj、selectedIndex]、value+"'");if (restore) selObj、selectedIndex=0;}//--></script><LINK href="style、css" rel=Stylesheet type=text/css></head><center><span class="style1”>学生信息管理系统</span></center></td></tr><tr><td colspan="2"><center>管理员:<input name="username" type="text" maxlength="10">密码:<input name="password" type="password" > <input type="submit" name="Submit" value="登录"><input type="reset" name="Submit2" value="重置">用户类型:<select name="menu1" onChange="MM_jumpMenu('parent',this,0)"><option value="admin_login、asp" selected>管理员</option><option value="student_login、asp">学生</option></select> <br> <fontcolor=red><%=msg%></font> </center> </td></tr></table></form></body></html>显示信息:代码为:<!--#include file=”conn、inc”<%if Session(“admin”)<>true and Session(“student”)<>true then Response、Redirect “student_login、asp”end if%><!doctype html public “-//W3C//DTD HTML 4、0 Transitional//EN”><html><head><title>学生信息</title><LINK href=”style、css” rel=Stylesheet type=text/css></style></head><table align=center><tr><td colspan=”2”><center><span class=”style1”>学生信息管理系统</span></center></td></tr><tr><td colspan=”5”><a href=”#”>[<b>首页</b>]</a><a href=”search、asp”>[查询]</a><a title=”需要管理员权限”href=”post、asp”>[添加]</a><a href=”admin_login、asp”>[管理]</a><a href=”logout、asp”>[退出]</a></td></tr></table><%users = Request(“users”)If request(“PageNo”)=”” thenPageNo=1ElsePageNo=int(request(“PageNo”))End Ifsql=”SELECT * from liuyan order by ID desc”set rs=server、createobject(“adodb、recordset”)rs、open sql,conn,1,3if not(rs、eof and rs、bof) thenrs、Pagesize=3rs、absolutepage=PageNox = 0For x = 1 to rs、Pagesizeif x mod 2 thentr_color=”#EBEBEB”elsetr_c olor=”#ffffff”end ifIf rs、eof thenExit ForElseif isNull(Rs(“Dateandtime”)) thenstrDateandtime = “”elsestrDateandtime = Cstr(Rs(“Dateandtime”))end if‘Username,Email,Content,Sex,DateandtimeResponse、Write “<tabl e width=50% border=0 align=center cellpadding=0 cellspacing=0 bgcolor=” + tr_color+ “>”Response、Write “<tr>”Response、Write “<td width=20% nowrap>姓名:</td>”Response、Write “<td width=60% class=Tab>” + Rs(“Username”) + “</td>”‘修改删除链接if us ers = “admin” thenResponse、Write “<td width=20% class=Tab align=right>” + “<a href=post、asp?users=” + users + “&id=” + cstr(Rs(“ID”)) + “><font color=blue>修改</font></a> <a href=delete、asp?users=” + users + “&id=” + cstr(Rs(“ID”)) + “><font color=blue>删除</font></a>” + “</td>”elseResponse、Write “<td width=20% class=Tab></td>”end ifResponse、Write “</tr>”Response、Write “<tr>”Response、Write “<td>性别:</td>”Response、Write “<td colspan=2>” + Rs(“Sex”) + “</td>”Response、Write “</tr>”Response、Write “<tr>”Response、Write “<td>班级:</td>”Response、Write “<td colspan=2>” + Rs(“Class”) + “</td>”Response、Write “</tr>”Response、Write “<tr>”Response、Write “<td>专业:</td>”Response、Write “<td colspan=2>” + Rs(“Major”) + “</td>”Response、Write “</tr>”Response、Write “<tr>”Response、Write “<td>入学时间:</td>”Response、Write “<td colspan=2>” + strDateandtime + “</td>”Response、Write “</tr>”Response、Write “<tr>”Response、Write “<td>评语:</td>”Response、Write “<td colspan=2>” + Rs(“Content”) + “</td>”Response、Write “</tr>”Response、Write “<tr>”Response、Write “<td>E-mail:</td>”Response、Write “<td colspan=2>” + Rs(“Email”) + “</td>”Response、Write “</tr>”Response、Write “</table>”Response、Write “<table border=0 align=center>”‘if users = “admin” then‘Response、Write “<tr>”‘Response、Write “<td align=right bgcolor=”+ tr_color+ “><a href=delete、asp?users=”+ users + “&id=”+ cstr(Rs(“ID”)) + “><font color=red>删除</font></a></td>”‘Response、Writ e “</tr>”‘end ifResponse、Write “<tr>”Response、Write “<td height=1></td>”Response、Write “</tr>”Response、Write “</table>”rs、MoveNextEnd IfNextend ifif rs、recordcount > 0 then<tr><td>共有<%=rs、recordcount%>条学生信息&nbsp;<%=rs、pagesize%>条/页&nbsp;共<%=rs、pagecount%>页</td><td align=”right” class=”Tab”>分页:<%for i=1 to rs、pagecountif pageno <> I thenResponse、Write “<A href=index、asp?users=” + users + “&pageno=” + cstr(i) + “>”&i&”</a>&nbsp;”elseResponse、Writ e “<b>” & I & “</b>&nbsp;”end ifnext%></td> </tr></table>Else<tr><td><marquee>目前,没有任何学生信息！</marquee></td> </tr></table>End if<%rs、closeset rs=nothingConn、closeSet conn = Nothing%></body></html>4、查询信息页面显示:代码为:<!--#include file="conn、inc" --><%if Session("admin")<>true and Session("student")<>true then Response、Redirect "student_login、asp"end if%>if studentid <>0 thensql="SELECT * from liuyan where ID="&studentidset rs=server、createobject("adodb、recordset")rs、open sql,conn,1,3<%rs、closeset rs=nothingConn、closeSet conn = Nothingend if%>。

数据库实训报告含代码

数据库实训报告含代码

一、实训目的本次数据库实训的主要目的是通过实际操作，使学生深入了解数据库的基本概念、原理和操作方法，掌握SQL语言的使用，提高数据库设计和实施的能力。

同时，通过本次实训，培养学生严谨的工作态度和团队协作精神。

二、实训环境1. 操作系统：Windows 102. 数据库管理系统：MySQL 5.73. 开发工具：MySQL Workbench三、实训内容1. 数据库设计2. 数据库创建与操作3. SQL语言基础4. 数据库查询5. 数据库安全与权限管理6. 数据库备份与恢复四、实训过程1. 数据库设计（1）需求分析：以学生信息管理系统为例，分析系统需求，确定数据库表结构。

（2）概念结构设计：使用E-R图描述实体关系，确定实体、属性和关系。

（3）逻辑结构设计：根据概念结构设计，将E-R图转换为关系模型，确定表结构。

（4）物理结构设计：选择合适的存储引擎，设计表空间、索引等。

2. 数据库创建与操作（1）创建数据库```sqlCREATE DATABASE student_info;```（2）创建表```sqlCREATE TABLE student (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50) NOT NULL,age INT NOT NULL,gender ENUM('male', 'female') NOT NULL,class_id INT NOT NULL,FOREIGN KEY (class_id) REFERENCES class(id));CREATE TABLE class (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50) NOT NULL);```（3）插入数据```sqlINSERT INTO student (name, age, gender, class_id) VALUES ('张三', 20, 'male', 1);INSERT INTO class (name) VALUES ('计算机科学与技术');```3. SQL语言基础（1）查询语句```sqlSELECT FROM student WHERE age > 20;```（2）更新语句```sqlUPDATE student SET age = 21 WHERE name = '张三';```（3）删除语句```sqlDELETE FROM student WHERE name = '李四';```4. 数据库查询（1）多表查询```sqlSELECT , FROM student JOIN class ONstudent.class_id = class.id;```（2）子查询```sqlSELECT FROM student WHERE class_id IN (SELECT id FROM class WHERE name = '计算机科学与技术');```5. 数据库安全与权限管理（1）创建用户```sqlCREATE USER 'user1'@'localhost' IDENTIFIED BY 'password';```（2）授权```sqlGRANT SELECT, INSERT, UPDATE, DELETE ON student_info. TO'user1'@'localhost';```（3）撤销权限```sqlREVOKE ALL PRIVILEGES ON student_info. FROM 'user1'@'localhost'; ```6. 数据库备份与恢复（1）备份```sqlmysqldump -u root -p student_info > student_info_backup.sql```（2）恢复```sqlmysql -u root -p student_info < student_info_backup.sql```五、实训总结通过本次数据库实训，我掌握了以下知识和技能：1. 数据库的基本概念、原理和操作方法；2. SQL语言的使用；3. 数据库设计、创建与操作；4. 数据库查询、安全与权限管理；5. 数据库备份与恢复。

数据库设计以及源代码

数据库设计以及源代码

数据库设计以及源代码系统建设详细设计1.数据库设计管理员用户表信息：学生用户表：学生信息表：电子文本50 学生的电子备注/ / 学生的其他信息性别文本50 性别入学时间文本50 学生入学的时间班级文本50 所在班级专业文本50 所学专业2 系统程序文件设计与编写与数据库连接的代码：<% db="message.mdb"set Conn=server.createobject("adodb.Connection")conn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&server.mappath(db)%>登陆界面代码：代码为：<%if request("Submit")<>"" thenset rs=server.createobject("adodb.recordset")sql="SELECT username,password from users where username='"&request("username")&"'"rs.open sql,conn,1,3if rs.bof and rs.eof thenmsg="错误：用户名不存在"elseif rs("password")=request("password") then Session("admin")=trueresponse.redirect "index.asp?users=admin" elsemsg="错误：密码不正确"end ifend ifrs.closeset rs=nothingConn.closeSet conn = Nothingend if%>。

数据库原理及其应用实验代码

数据库原理及其应用实验代码
(15)查询学号 S52 的学生的姓名和选修的课程名称及成绩。 SELECT Students.Sname,ame,Reports.Score
FROM Students,Courses,Reports
WHERE Students.Sno=Reports.Sno AND o=o AND Students.Sno='S52'
FROM Reports GROUP BY Sno HAVING COUNT(*)>=3 ) (10)查询编号 S26 的学生所选的全部课程的课程名和成绩。 SELECT ame,Reports.Score FROM Courses,Reports WHERE Reports.Sno='S26' AND o=o (11)查询所有选了“数据库原理及其应用”课程的学生编号和姓名。 SELECT Sno,Sname FROM Students WHERE Sno IN ( SELECT Reports.Sno
首先应按实际条件试焊合格后，方可成批焊接。并应对每个焊接 接头进行外观检查，逐批取试件进行接头抗拉强度、冷弯试验。
加工后的钢筋，其表面伤痕不应使钢筋截面减少5%以上。 冷拉调直钢筋，钢筋伸长率控制如下：圆钢筋伸长率≤2%,螺纹钢 伸长率≤1% 钢筋拉伸调直后不得有死弯（发现死弯，应截去），如发现钢筋 脆断、劈裂、拉不直等异常现象应及时对材质进行复查。每根钢筋总 的冷拉次数不得多于两次。 弯制过程中发现钢材接头开裂、脆断、太硬、回弹等异常现象应 及时找出原因谨慎处理。 钢筋绑扎应按设计要求绑扎，每根钢筋位置，一般要求如下： 所有钢筋交点均必须绑扎，钢筋弯折角与纵向分布筋交点应绑扎，主 筋绑扎用18#铁线，如有扭断、开口、失去紧固的情况时还应补绑。 钢筋进行双面搭接焊，焊缝长度不小于5d。 四、防胀措施 本工程施工过程中，将在高温季节施工，轨温超过锁定轨温10℃ （锁定轨温32℃），禁止进行扰动线路的施工作业。若轨温超过42℃ 并进行扰动线路的施工时，采取本防胀措施 ；若轨温没有超过42℃

数据库实验五代码

数据库实验五代码

定义:(未加事务,但没错)CREATE PROCEDURE sp_borrow@mybookid char(10),@mycardid char(10) , @bname char(30) output,@cname char(20) output,@mystate char(1) output/*,@mydate datetime output*/ ASif @mybookid is null or @mycardid is nullbeginprint'error:your bookid or cardid is null'returnendelsebeginif (select state from bookstore where bookid=@mybookid)='y'beginupdate bookstore set state='n' where bookid=@mybookidinsert into borrowlogvalues(@mycardid,@mybookid,getdate(),null)endelsebeginprint'error:book is borrowed!'endselect@bname=s.bookname,@cname=c.ownername,/*@mydate=l.borrowdate,*/@mystat e=s.statefrom bookstore s,borrowlog l, borrowcard cwhere s.bookid=@mybookid and c.cardid=@mycardid ands.bookid=l.bookid and c.cardid=l.cardidendGO执行:(只剩下借阅日期的与char类型转换没解决)DECLARE @bid varchar(10), @cid varchar(10),@bname char(30),@cname char(20)/*,@mydate datetime*/,@mystate char(1)SET @bid='2008112601' set @cid='200602001'EXECUTE sp_borrow @bid,@cid,@bname output,@cname output, /*@mydate output,*/@mystate outputSELECT '借阅人：'+@cname as '借阅人', '图书名称'+@bname as '图书名称'/*,@mydate as '借阅日期'*/,'借阅状态'+@mystate as'借阅状态'加了事务处理CREATE PROCEDURE sp_borrow@mybookid char(10),@mycardid char(10) , @bname char(30) output,@cname char(20) output,@mystate char(1) output,@mydate datetime outputASif @mybookid is null or @mycardid is nullbeginprint'error:your bookid or cardid is null'returnendelsebeginif (select state from bookstore where bookid=@mybookid)='y'beginbegin transactionupdate bookstore set state='n' where bookid=@mybookidIF ( @@ERROR <> 0 )BEGIN/**//*---------- 自定义错误输出 ----------*/RAISERROR( 'Insert data error!',16,1 )/**//*-------- 事务回滚 --------*/ROLLBACK TRANSACTIONENDinsert into borrowlogvalues(@mycardid,@mybookid,getdate(),null)IF ( @@ERROR <> 0 )BEGIN/**//*---------- 自定义错误输出 ----------*/RAISERROR( 'Insert data error!',16,1 )/**//*-------- 事务回滚 --------*/ROLLBACK TRANSACTIONENDcommitendelsebeginprint'error:book is borrowed!'endselect@bname=s.bookname,@cname=c.ownername,@mydate=l.borrowdate,@mystate=s. statefrom bookstore s,borrowlog l, borrowcard cwhere s.bookid=@mybookid and c.cardid=@mycardid ands.bookid=l.bookid and c.cardid=l.cardidendGO执行DECLARE @bid varchar(10), @cid varchar(10),@bname char(30),@cname char(20),@mydate datetime,@mystate char(1)SET @bid='2008112602' set @cid='200601001'EXECUTE sp_borrow @bid,@cid,@bname output,@cname output, @mydate output,@mystate outputSELECT '借阅人：'+@cname as '借阅人', '图书名称'+@bname as '图书名称',@mydate as '借阅日期','借阅状态'+@mystate as'借阅状态'触发器CREATE TRIGGER tgs_delete ON sFOR DELETEASdelete from spj where sno in (select sno from deleted)。

基础数据库采集代码表

基础数据库采集代码表

基础数据库采集代码表（教职工）一、性别男：1；女：2；二、职务职称教授：010 副教授：011 讲师：012 助教：013高级讲师：022 中教一级：053 中教二级：054高级实验师：072 实验师：073 助理实验师：074 实验员：075三、来源地区（代码为每位教职工身份证的前2位数+0000），如北京：110000 河北：130000 内蒙：150000辽宁：210000 吉林：220000 江苏：320000河南：410000 湖北：420000 湖南：430000………四、教职工类别专任教师：11；教辅人员：12；行政人员：13；工勤人员：14；科研机构人员：20；兼任（职）教师：50；外聘教师：53；其他教职工：99五、民族汉族：01 蒙古族：02 回族：03 藏族：04维吾尔族：05 苗族：06 彝族：07 壮族：08布依族：09 朝鲜族：10 满族：11 侗族：12瑶族：13 白族：14 土家族：15 傣族：18黎族：19 高山族：23 水族：25 土族：30六、政治面貌：党员：01；预备党员：02；团员：03；民革：04；民盟：05；民建：06；民进：07；农工：08；致工：09；九三学社：10；台湾民盟：11七、学历层次研究生：10；博士研究生：12；硕士研究生：13；研究生毕业：11；研究生肄业：19；大学本科：20；大学毕业：21；大学肄业：29；大学专科和专科学校：30；专科毕业：31；专科肄业：39；中专或中技：40；高中：60；初中：70八、所获学位博士：2哲学：201；经济学：202；法学：203；教育学：204；文学：205；历史学：206；理学：207；工学：208硕士：3哲学：301；经济学：302；法学：303；教育学：304；文学：305；历史学：306；理学：307；工学：308；农学：309；医学：310；军事学：311学士：4哲学：401；经济学：402；法学：403；教育学：404；文学：405；历史学：406；理学：407；工学：408；农学：409；医学：410；军事学：411九、研究生指导教师层次博士生导师：1；硕士生导师：2；博、硕士生导师：3；兼职博士生导师：4；兼职硕士生导师：5；兼职博、硕士生导师：6；其他：9十、授课课程文化课：00 专业课：10 信息技术类：17商贸与旅游类：19 财经类：20 文化艺术与体育类：21社会公共事业类：22 其他专业课：29 实习指导：30 其他：99十一、任课状况未任课教师：10；进修：11；科研：12；病休：13；其他原因未任课：19；任课教师：20；基础课21；专业课：22；实践技术指导：23；双师型：24；任其他课：29；其他不担任教学的教职工：99十二、教职工来源录用：10 军队转业：21 复员：22调入：30 系统内高校调入：31 系统内其他单位调入：32系统外调入：33 回国定居：40 其他：99十三、离岗原因出国（境）：1 事假：3 病休：4 国内进修学习：5 借调：6 不胜任工作：7 处分：8 其他：9基础数据库采集代码表（学生基础库）一、性别男：1；女：2；二、来源地区（代码为每个省身份证的前2位数+0000），如北京：110000 河北：130000 内蒙：150000辽宁：210000 吉林：220000 江苏：320000河南：410000 湖北：420000 湖南：430000………三、民族汉族：01 蒙古族：02 回族：03 藏族：04 维吾尔族：05 苗族：06 彝族：07 壮族：08 布依族：09 朝鲜族：10 满族：11 侗族：12 瑶族：13 白族：14 土家族：15 傣族：18 黎族：19 高山族：23 水族：25 土族：30四、政治面貌党员：01；预备党员：02；团员：03；民革：04；民盟：05；民建：06；民进：07；农工：08；致工：09；九三学社：10；台湾民盟：11五、学历层次大学本科：20；大学专科和专科学校：30；中专或中技：40；高中：60；初中：70六、专业代码会计学（本）：110203 会计620203国际经济与贸易：620303 电子商务：620405物流管理：620505 市场营销：620401旅游管理（本）：110206 旅游管理：640101涉外旅游：640102 酒店管理：640106空中乘务：520503汉语言文学（本）：050101 文秘：660112人力资源管理：650204 社会工作：650101学前教育：660214 艺术设计（本）：050408服装设计：610204纺织品装饰艺术设计：610206 电脑艺术设计：670104影视动画：670305 人物形象设计：670105音乐表演（本）：050403 音乐表演：670202播音与主持：670307舞蹈表演：670203 音乐教育：660209英语（本）：050201 英语教育：660203应用英语：660102 应用日语：660103计算机信息管理：590106 社会体育：660303七、培养方式国家任务：10 非定向：11 定向：12非国家任务：20 自筹经费：21 委托培养：22联系培养：23 协作：24 留学生：30八、学生类别本科生：22；专科生：24；函授本科生：31；函授专科生：32；夜大本科生：33；夜大专科生：34；成人脱产本科生：35；成人脱产专科生：36九、学籍异动公派留学：01；留级：02；降级：03；退学：06；休学：11；修学：13；保留入学资格：14；恢复入学资格：15；恢复学籍：16；取消学籍：17；保留学籍：18；转学（转出）：21；转学（转入）：22；转专业：23；退学：31；开除：42；死亡：51；其他：99十、学籍异动原因精神疾病：01；传染疾病：02；其他疾病：03；溺水死亡：04；自杀：05；他杀：06；事故死亡：07；打架斗殴致死：08；自动退学：09；成绩太差：10；自费留学：11；贫困：12；家长病重：13；停学实践（求职）14；非留学出国（境）：15；其他：99十一、所获学位学士：4哲学：401；经济学：402；法学：403；教育学：404；文学：405；历史学：406；理学：407；工学：408；。

数据库实验代码

数据库实验代码

实验四：6、UPDATE stu_infoSET sdept='会计学院'WHERE stu_id='2007070102';UPDATE stu_gradeSET grade=nullWHERE grade<60;7、DELETEFROM stu_infoWHERE address='河南洛阳';方法一：DELETEFROM stu_gradeWHERE stu_id IN(SELECT stu_idFROM stu_infoWHERE name='张元')AND course_id IN(SELECT course_idFROM course_infoWHERE course_name='操作系统'); 方法二：DELETEFROM stu_gradeWHERE stu_id=(SELECT stu_idFROM stu_infoWHERE name='张元')AND course_id=(SELECT course_idFROM course_infoWHERE course_name='操作系统'); 实验五：1、SELECT*FROM stu_infoSELECT name sex,address,sdeptFROM stu_infoWHERE stu_id='2007070102';SELECT stu_id'学号',name'姓名',sdept'院系' FROM stu_infoWHERE sex='女';select name,year(getdate())-year(birthday)年龄from stu_info;SELECT name,sex,addressFROM stu_infoWHERE address LIKE'%阳%';SELECT stu_idFROM stu_gradeWHERE course_id='701'AND grade>70 AND grade<80;方法二：SELECT stu_idFROM stu_gradeWHERE course_id='701'AND grade BETWEEN 70 AND 80;2、嵌套查询SELECT stu_id,name,sdeptFROM stu_infoWHERE stu_id IN(SELECT stu_idFROM stu_gradeWHERE course_id IN(SELECT course_idFROM course_infoWHERE course_name='计算机基础'));方法二：SELECT stu_id,name,sdeptFROM stu_infoWHERE stu_id IN(SELECT stu_idFROM stu_gradeWHERE course_id=(SELECT course_id--因为所查的课程号唯一,所以可以用"="FROM course_infoWHERE course_name='计算机基础'));SELECT stu_id,name,sdeptFROM stu_infoWHERE stu_id NOT IN(SELECT stu_idFROM stu_gradeWHERE course_id='701');SELECT stu_id,name,major,sdeptFROM stu_infoWHERE stu_id IN(SELECT stu_idFROM stu_gradeWHERE grade>(SELECT MAX(grade)FROM stu_gradeWHERE stu_id IN(SELECT stu_idFROM stu_infoWHERE sdept='会计学院')));3、连接查询SELECT stu_info.stu_id,name,course_name,grade只要有两列名相同,就要区分是那个表的属性列FROM stu_info,stu_grade,course_infoWHERE stu_info.stu_id=stu_grade.stu_id ANDstu_grade.course_id=course_info.course_id;SELECT stu_info.stu_id,name,course_name,gradeFROM stu_info,stu_grade,course_infoWHERE stu_info.stu_id=stu_grade.stu_id ANDstu_grade.course_id=course_info.course_id AND mark>575;SELECT stu_info.stu_id,name,course_name,gradeFROM stu_info,stu_grade,course_infoWHERE stu_info.stu_id=stu_grade.stu_id ANDstu_grade.course_id=course_info.course_id AND grade>90;4、数据汇总SELECT AVG(mark)信息学院同学入学平均分FROM stu_infoWHERE sdept='信息学院';SELECT MAX(mark)全体同学入学最高分,MIN(mark)全体同学入学最低分FROM stu_infoSELECT COUNT(stu_id)会计总人数FROM stu_infoWHERE sdept='会计学院';SELECT COUNT(stu_id)籍贯中带阳字的总人数FROM stu_infoWHERE address LIKE'%阳%';SELECT COUNT(course_id)选择课程数,AVG(grade)平均成绩,COUNT(grade)总成绩WHERE stu_id='2007070101';5、GROUP BYSELECT sdept,COUNT(stu_id)人数FROM stu_infoGROUP BY sdept;SELECT stu_id,COUNT(course_id)选课门数,AVG(grade)平均分FROM stu_gradeGROUP BY stu_id;错误操作:SELECT stu_grade.stu_id,name,COUNT(course_id)选课门数,AVG(grade)平均分FROM stu_grade,stu_infoWHERE stu_info.stu_id=stu_grade.stu_idGROUP BY stu_grade.stu_idHAVING AVG(grade)>80;选择列表中的列'stu_'无效，因为该列没有包含在聚合函数或GROUP BY子句中。

数据库设计源代码

数据库设计源代码

ieldType=='D'{count+=8;continue;}else ifrec->fieldi.fieldType=='I'{count+=4;continue;}else ifrec->fieldi.fieldType=='T'{count+=6;continue;}count+=rec->fieldi.fieldLen;}return count+1;}pDbfHand NewDbfHeadpDbfHand hand, pDbfRec recieldType;= 0;switch{case DOUBLE:= 8;break;case FLOAT:= 4;break;case INT:= 4;break;case SHORT:= 2;break;default:= rec-> fieldi.fieldLen;break;}strcpy, rec-> fieldi.fieldName;+= shortrec-> fieldi.fieldLen;if-1 ==fwritechar&field, sizeofFieldDcp , 1 , hand->fd return NULL;}hand-> = 0x20;hand-> = rec-> realCounts;for i = 0; i < rec-> realCounts; i++hand-> i = rec-> fieldi;return hand;}void WriteFieldEndpDbfHand handieldName,,sizeof;hand-> i.fieldLen = ;hand-> i.fieldType = ;hand-> i.fieldDcis = ;hand-> i.offset = ;}fori=0;i<hand->;i++{forj=0;hand->i.fieldNamej='\0';j++{hand->i.fieldNamej=tolowerhand->i.fieldNamej;}}return hand;}char SaveDate char strieldType == DA TE && record-> fieldi.fieldData = NULL record-> fieldi.fieldData = SaveDaterecord-> fieldi.fieldData;curFieldLen = hand-> i.fieldLen;actDataLen = strlenrecord-> fieldi.fieldData;if actDataLen > curFieldLenactDataLen = curFieldLen;memcpy&bufnPos, record-> fieldi.fieldData, actDataLen;nPos += hand-> i.fieldLen;}bufhand-> = '\0 ';if -1 == fwritebuf, hand-> , 1 , hand->fdrest = FALSE;return rest;}char ReadDbfDate char strieldName{flag=1;break;}}ifflagreturn j;elsereturn -1;}int IsBottomRecordpDbfHand handieldData=charmallochand->m.fieldLen+1;memsetrec->fieldm.fieldData, 0x00, hand->m.fieldLen+1;}if -1== fseekhand->fd,hand->curFpAddr, SEEK_SET ieldLen;memcpyrec->fieldi.fieldData,recData+fieldOffset,curFieldLen;rec->fieldi.fieldData = DsdStrEndSpacerec->fieldi.fieldData, curFieldLen;if rec->fieldi.fieldType == DATE && rec->fieldi.fieldData0 = 0x20rec->fieldi.fieldData =ReadDbfDaterec->fieldi.fieldData;}return 1;}pDbfHand AddRecpDbfHand hand, pDbfRec recordieldData=charmalloclen+1;memsetreci->fieldhand->.fieldData, 0x00, len+1;reci->realCounts++;}}strcpyhand->hand->.fieldName,field_name;hand->hand->.fieldNamestrlenfield_name='\0';hand->hand->.fieldType=type;hand->hand->.fieldLen=len;hand->hand->.fieldDcis=dec;hand->++;reccounts=hand->;hand->fd=fopen"c:\\数据库\\","w";fclosehand->fd;hand->fd=fopen"c:\\数据库\\","w+b";ifNewDbfHeadhand, &hand->rec == NULLreturn NULL;if NULL == WriteFieldDicsribehand, &hand->recreturn NULL;WriteFieldEndhand;if0=fclosehand->fdreturn NULL;fori=0;i<reccounts;i++AddRechand,reci;return hand;}pDbfHand AlterFieldpDbfHand hand,char field_name,char type,int len,int decieldDcis=dec;reci->fieldnum.fieldLen=len;reci->fieldnum.fieldType=type;}}hand->num.fieldType=type;hand->num.fieldLen=len;hand->num.fieldDcis=dec;reccounts=hand->;hand->fd=fopen"c:\\数据库\\","w";fclosehand->fd;hand->fd=fopen"c:\\数据库\\","w+b";ifNewDbfHeadhand, &hand->rec == NULLreturn NULL;if NULL == WriteFieldDicsribehand, &hand->recreturn NULL;WriteFieldEndhand;if0=fclosehand->fdreturn NULL;fori=0;i<reccounts;i++AddRechand,reci;return hand;}pDbfHand DropFieldpDbfHand hand,char field_name,int nieldDcis=reci->fieldnum+1.fieldDcis;reci->fieldnum.fieldLen=reci->fieldnum+1.fieldLen;reci->fieldnum.fieldType=reci->fieldnum+1.fieldType;num++;}}ifnum==-1{forj=0;j<10;j++hand->num.fieldNamej=0;hand->num.fieldType=0;hand->num.fieldLen=0;hand->num.fieldDcis=0;}else{for;num<n;num++{strcpyhand->num.fieldName,hand->num+1.fieldName;hand->num.fieldType=hand->num+1.fieldType;hand->num.fieldLen=hand->num+1.fieldLen;hand->num.fieldDcis=hand->num+1.fieldDcis;}}hand->;reccounts=hand->;hand->fd=fopen"c:\\数据库\\","w";fclosehand->fd;hand->fd=fopen"c:\\数据库\\","w+b";ifNewDbfHeadhand,&hand->rec == NULLreturn NULL;if NULL == WriteFieldDicsribehand,&hand->recreturn NULL;WriteFieldEndhand;if0=fclosehand->fdreturn NULL;fori=0;i<reccounts;i++AddRechand,reci;return hand;}pDbfHand CreateDbfDbfRec rec; bftable_namej+1='d';table_namej+2='b';table_namej+3='f';table_namej+4='\0';do{int field_lengtf=0,flag=1;ieldNamej=sqli;num.fieldNamej='\0';for;sqli==' ';i++;forj=0;sqli=' '&&sqli=',';i++,j++ieldType=Type1j;break;}}ifj==5ieldType=='C'{ieldLen=field_lengtf;}else ifnum.fieldType=='N'||num.fieldType=='F'num.fieldLen=4;else ifnum.fieldType=='D'num.fieldLen=8;else ifnum.fieldType=='S'num.fieldLen=2;field_lengtf=0,flag=1;for;sqli==' ';i++;forj=0;sqli=' '&&sqli=','&&sqli=';';i++,j++lag=1;else{error=0;printf"\n你输入的'KEY'有误,请重新创建\n";break;}}else ifstrcmpcondition,"unique"==0lag=2;else ifstrcmpcondition,"not"==0lag=3;else{error=0;printf"\n你输入的约束条件'NOT NULL'有误,请重新创建\n";break;}}elseieldDcis=0;num.offset=0;=num+1;num++;bf文件return 1;elsereturn 0;}return 1;}int ALTER{char type610={"char","int","short","float","double"};char Type16={'C','N','S','F','D'};FILE fp;int i,j,num,error=1;char T; ; bftable_namej+1='d';table_namej+2='b';table_namej+3='f';table_namej+4='\0';iff=OpenDbf==0 lag=1;else{error=0;printf"\n你输入的'KEY'有误,请重新输入SQL语句\n";}}else ifstrcmpcondition,"unique"==0lag=2;else ifstrcmpcondition,"not"==0lag=3;else{error=0;printf"\n你输入的约束条件'NOT NULL'有误,请重新输入SQL语句\n";}}else lag;iffp=fopen"d:\\","a+b"{printf"打开约束性条件文件失败,请重新输入SQL语句\n";return 0;}fwrite&con,sizeofCondition,1,fp;fclosefp;}}else ifstrcmptemp,"alter"==0ieldName=NULLprintf" %-6s|",f->i.fieldName;}printf"\n";printf"-----------+";fori=0;i<f->;i++printf"-------+";printf"\n";printf"完整性约束|";fori=0;i<f->;i++{switchconi.flag{case 1:printf" 主键|";break;case 2:printf"唯一值|";break;case 3:printf" 非空|";break;default:printf" |";}}printf"-----------+";fori=0;i<f->;i++printf"-------+";printf"\n";printf"TYPE |";fori=0;i<f->;i++{switchf->i.fieldType{case 'N':printf" int |";break;case 'C':printf" char |";break;case 'S':printf" short |";break;case 'F':printf" float |";break;case 'D':printf" double|";break;default:printf" |";}}printf"\n";printf"-----------+";fori=0;i<f->;i++printf"-------+";printf"\n";printf"LENGTH |";fori=0;i<f->;i++printf" %2d |",f->i.fieldLen;printf"\n";printf"-----------+";fori=0;i<f->;i++printf"-------+";printf"\n";}fori=0;i<f->;i++printf"\n";printf"表结构如下:\n";fori=0;i<f->;i++printf"-------+";printf"\n";fori=0;i<f->;i++printf" %-6s|",f->i.fieldName;printf"\n";fori=0;i<f->;i++printf"-------+";printf"\n";fclosefp;return 1;}int a{char mark,n;Show;while1{int i;char order;Read;fori=0;sqli==' ';i++;mark=tolowersqli;switchmark{case 'c':ifCREATEprintf"建表成功\n";else{printf"建表失败\n";continue;}break;case 'a':ifALTERprintf"修改表成功\n";else{printf"修改表失败\n";continue;}break;default:printf"输入错误,请重新输入\n";}printf"是否显示表,请选择y/n\n";whilen=getchar=='\n';whilegetchar='\n';whilen= 'n' &&n='N'&&n= 'y'&&n='Y'{printf"你输入错误,请重新选择y/n\n";whileorder=getchar=='\n';whilegetchar='\n';}ifn=='y'||n=='Y'{ifOutPutprintf"OK\n";elsecontinue;}printf"是否继续操作,请选择y/n\n";whileorder=getchar=='\n';whilegetchar='\n';whileorder = 'n' &&order='N'&& order = 'y'&&order='Y'{printf"你输入错误,请重新选择y/n\n";whileorder=getchar=='\n';whilegetchar='\n';}iforder=='n'||order=='N'break;}return 0;}int b{char mark,n;Show;while1{int i;char order;Read1;fori=0;sqli==' ';i++;mark=tolowersqli;switchmark{case 'c':ifCREATEprintf"建表成功\n";else{printf"建表失败\n";continue;}break;case 'a':ifALTERprintf"修改表成功\n";else{printf"修改表失败\n";continue;}break;default:printf"输入错误,请重新输入\n";}printf"是否显示表,请选择y/n\n";whilen=getchar=='\n';whilegetchar='\n';whilen= 'n' &&n='N'&&n= 'y'&&n='Y' {printf"你输入错误,请重新选择y/n\n";whileorder=getchar=='\n';whilegetchar='\n';}ifn=='y'||n=='Y'{ifOutPutprintf"OK\n";elsecontinue;}printf"是否继续操作,请选择y/n\n"; whileorder=getchar=='\n';whilegetchar='\n';whileorder = 'n' &&order='N'&& order = 'y'&&order='Y'{printf"你输入错误,请重新选择y/n\n";whileorder=getchar=='\n';whilegetchar='\n';}iforder=='n'||order=='N'break;}return 0;}int main{int n;ShowBeginning;while1{printf"请选择执行方式:\n";printf"1、以命令方式运行\n2、以程序方式运行\n3、退出\n";scanf"%d",&n;switchn{case 1:a;break;case 2:b;break;case 3:break;default:printf"你输入错误,请重新选择\n";break;}ifn==3break;}return 0;}。

数据库原理与设计大作业源代码

数据库原理与设计大作业源代码

数据库原理与设计大作业源代码(1) 用户登录界面运行超市管理信息系统后，首先进入用户登录界面，用户输入用户名和密码后，系统进行验证，验证通过进入程序的主界面。

在进行系统登录过程中，登录模块将调用数据库里的用户信息表，并对用户名和密码进行验证，只有输入了正确的账号和密码后，系统登录才会成功。

在登录模块中，对系统的尝试登录次数进行了限制，禁止用户无终止的进行系统登录尝试，在本系统中，当用户对系统的三次登录失败后，系统将自动机制登录，突出登录模块。

并在输入了错误的或者是不存在的账户和密码时，系统会给出出错信息提示，指明登录过程中的错误输入或者错误操作，以便用户进行正确的登录。

登录界面如图5-2所示。

图5-2 登录界面主要实现代码如下：//登录private void radBtnOk_Click(object sender, EventArgs e){try{if (radTxtBoxUser.Text.Trim() == ""){this.radLbInfo.Text = "请输入您的用户名！";}else if (radTxtBoxPsw.Text.Trim() == ""){this.radLbInfo.Text = "请输入您的密码！";}else{commandUnit com = new commandUnit();string str = @"select * from UserInfo where loginNo = '" + radTxtBoxUser.Text.ToString() + "'";DataTable table = com.GetDataSet(str);if (table.Rows.Count <= 0){this.radLbInfo.Text = "用户名不存在！";radTxtBoxUser.Text = "";radTxtBoxPsw.Text = "";return;}str = @"select * from UserInfo where loginNo = '" + radTxtBoxUser.Text.ToString() + "' and passWord = '" + radTxtBoxPsw.Text.ToString() + "'";DataTable tableUser = com.GetDataSet(str);if (tableUser.Rows.Count > 0){_currentUser = radTxtBoxUser.Text;_currentPsw = radTxtBoxPsw.Text;IsLogin = true;this.Close();}else{this.radLbInfo.Text = "密码错误！";radTxtBoxPsw.Text = "";}}}catch (System.Exception ex){throw ex;}}(2) 主界面系统登录成功后，进入主界面菜单。

  1. 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
  2. 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
  3. 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) 字段。

相关文档
最新文档