学生信息管理系统数据库设计

数据库技术实验报告学校专业年级学号姓名年月日学生信息管理系统数据库设计一、数据库的建立1. 建库说明数据库的建立用企业管理器，基本表的建立用SQL语言。

数据库名称为：学生信息管理系统。

2. 建立数据库命令如下：Create database 学生信息管理系统二、数据表的建立1. 建表命令：CREATE TABLE [dbo].[学生档案信息] ([Sno] [varchar] (50) NOT NULL ,[Sname] [char] (10) NOT NULL ,[Ssex] [char] (10) NOT NULL ,[Sclass] [char] (10) NOT NULL ,[Birth] [int] (4) NOT NULL ,[Saddress] [char] (10) NOT NULL ,[Sdept] [varchar] (50) NOT NULL ,[Stime] [int] (4) NOT NULL ,) ON [PRIMARY]GO命令：CREATE TABLE [dbo].[班级设置信息] ( [Sgrade] [char] (10) NOT NULL ,[Szclass] [int] (4) NOT NULL ,[Syear] [char] (5) NOT NULL ,[Scroom] [char] (5) NOT NULL ,[Steacher] [char] (10) NOT NULL ,[Stotal] [int] (4) NOT NULL ,[Ssub] [varchar] (8000) NOT NULL , ) ON [PRIMARY]GO命令：CREATE TABLE [dbo].[院系信息] ([Syname] [varchar] (50) NOT NULL ,[Spro] [varchar] (1000) NOT NULL ,[Snum] [int] (4) NOT NULL ,[Syclass] [varchar] (50) NOT NULL , ) ON [PRIMARY]GO命令：CREATE TABLE [dbo].[寝室信息] ([Shno] [char] (10) NOT NULL ,[Spno] [int] (4) NOT NULL ,[Sperno] [varchar] (1000) NOT NULL , [Smoney] [int] (4) NOT NULL ,) ON [PRIMARY]GO命令：CREATE TABLE [dbo].[用户信息] ([Smima] [char] (10) NOT NULL ,[Sown] [char] (10) NOT NULL ,[Sguanli] [char] (10) NOT NULL ,) ON [PRIMARY]GO2. 使用说明例：查询学生的所在院系名称，姓名，学号，年级，公寓楼号需要用到学生档案信息表，班级设置信息表，院系信息表，寝室信息表：SQL:select Syname,Sname,Sno,Sgrade,Shno from 学生档案信息，班级设置信息，院系信息，寝室信息三．程序主要功能实现代码：1. ConnectionFrame类：//连接数据库函数/*** 进行参数检查,进行数据库连接* @param e ActionEvent*/public void jButton1_actionPerformed(ActionEvent e) {driver=jTextField1.getText();URL=jTextField2.getText();table=jTextField3.getText();name=jTextField4.getText();password=jTextField5.getText();try{Class.forName(driver); //加载驱动程序con = DriverManager.getConnection(URL, name,password); //创建连接this.setVisible(false);new MainFrame();con.close();} catch (Exception e1) {JOptionPane.showMessageDialog(null, "数据库连接出错！请检查参数！", "系统提示",JOptionPane.INFORMATION_MESSAGE);}}2. MainFrame类：//主界面类,处理程序各种功能//作者信息public void jMenuItem1_actionPerformed(ActionEvent e) {JOptionPane.showMessageDialog(null,"学生: \n"+"学院: \n"+"专业： \n"+"学号： \n"+"指导老师： ","作者信息",RMATION_MESSAGE);}//版本信息public void jMenuItem5_actionPerformed(ActionEvent e) {JOptionPane.showMessageDialog(null,"学生管理系统数据库版本6.0","版本信息",RMATION_MESSAGE);}//退出系统public void jMenuItem2_actionPerformed(ActionEvent e) {Object ob=JOptionPane.showConfirmDialog(null,"真的要退出本系统吗?","系统提示",JOptionPane.YES_NO_OPTION);if(ob.equals(JOptionPane.OK_OPTION)){this.setVisible(false);System.exit(0);}}//按学号查询public void jButton1_actionPerformed(ActionEvent e) {Column col=queryDataSet1.getColumn("sno");sno=jTextField1.getText();formatter=col.getFormatter();choice=1;queryDataSet1.refilter();//表刷新}public void jButton2_actionPerformed(ActionEvent e) {choice=2;queryDataSet1.refilter();//表刷新}//刷新表public void queryDataSet1_filterRow(ReadRow readRow, RowFilterResponse rowFilterResponse) {switch(choice){case 1:if (formatter == null || sno.length() == 0)rowFilterResponse.add();else {String v;v = readRow.getString("sno");if (v.equals(sno))rowFilterResponse.add();elserowFilterResponse.ignore();}break;case 2:rowFilterResponse.add();break;}}public void jButton3_actionPerformed(ActionEvent e) { new CheckDialog();}每一个表中增加1条数据//增加数据public void jButton5_actionPerformed(ActionEvent e) { queryDataSet1.insertRow(true);new InsertDialog();}从每个表中删除1条数据//删除一行数据public void jButton4_actionPerformed(ActionEvent e) {Object ob=JOptionPane.showConfirmDialog(null,"永久性删除数据，真的要删除吗?","警告",JOptionPane.YES_NO_OPTION);if(ob.equals(JOptionPane.OK_OPTION)){queryDataSet1.deleteRow();queryDataSet1.saveChanges();}}}1.从每个表中删除1条数据delete fr om Students where sname=’张三’delete from Depts where Dnam=’计算机系’2.在每个表中修改1条数据update Students set sname=’李四’ where sname=’张三’3.每一个表中增加1条数据insert into Depts values(‘d01’,’计算机系’)4.查询计算机系的全体学生姓名select s.sname as 姓名 from Students s,Depts d where d.Dname='计算机系' and s.Dno=d.Dno5.查询张三的所在的系名select d.Dname from Students s,Depts d where s.Sname='张三' ands.Dno=d.Dno6.查询李四所选课程名称select ame from Course c,Students s,Reports r where s.sname=’李四’ and o=o and r.Sno=s.sno7.查询所有年龄在18-22岁之间的学生姓名select sname from Students where Sage between 18 and 228.查询每个学生的学号、姓名、选修的课程名、成绩select s.sno,s.sname,ame,r.grade from Reports r,Students s,Course c where o=o and r.Sno=s.Sno四、实验分析建表语句CREATE TABLE [dbo].[Reports]([Sno] [char](5) NOT NULL,[Cno] [char](6) NOT NULL,[Grade] [int] NULL,CONSTRAINT [PK_Reports] PRIMARY KEY CLUSTERED([Sno] ASC,[Cno] ASC) ON [PRIMARY]) ON [PRIMARY]CREATE TABLE [dbo].[Depts]([Dno] [char](5) NOT NULL,[Dname] [char](20) NOT NULL,CONSTRAINT [PK_Depts] PRIMARY KEY CLUSTERED ([Dno] ASC) ON [PRIMARY]) ON [PRIMARY]CREATE TABLE [dbo].[Course]([Cno] [char](6) NOT NULL,[Cname] [char](20) NULL,CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED ([Cno] ASC) ON [PRIMARY]CREATE TABLE [dbo].[Students]([Sno] [char](5) NOT NULL,[Sname] [char](20) NOT NULL,[Ssex] [char](2) NULL,[Sage] [int] NULL,[Dno] [char](5) NULL,CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED([Sno] ASC) ON [PRIMARY]) ON [PRIMARY]) ON [PRIMARY]五、实验结论这次实验报告持续了好一段时间，现在终于结束了。