内容发布更新时间 : 2024/12/23 3:33:08星期一 下面是文章的全部内容请认真阅读。
类型为主索引，表达式为课程号。
（3）创建成绩表的索引
成绩表：先点击右键，然后选择修改。在弹出的界面中选择索引页面，索引名为index2,通索引，表达式为课程号；索引名为xh,类型为主索引，表达式为学号。
（4）创建奖惩表的索引
奖惩表：先点击右键，然后选择修改。在弹出的界面中选择索引页面，索引名为index3,类型为主索引，表达式为学号。
（5）创建班级表的索引 （6）创建教师表的索引
2.2.4 数据表之间的逻辑关系
3、系统实现
3.1 数据库访问
（1）数据访问类及实现代码 1）触发器
触发器是用户定义在关系表上的一类由事件驱动的特殊过程。一旦定义，任何用户对表的增、删、改操作均由服务器自动激活相应的触发器，在DBMS核心层进行集中的完整性控制。由于本系统的触发器比较多，在这里我只写出了其
中的一部分触发器，具体如下所示。
数据更新:
CREATE TRIGGER studentupdate ON dbo.student FOR UPDATE AS
UPDATE STUDENT
SET SNO = (SELECT SNO FROM INSERTED) WHERE SNO = (SELECT SNO FROM DELETED);
CREATE TRIGGER courseupdate ON dbo.course / FOR UPDATE AS
UPDATE COURSE
SET SNO = (SELECT CNO FROM INSERTED) WHERE SNO = (SELECT CNO FROM DELETED);
CREATE TRIGGER scoreupdate ON dbo.student FOR UPDATE AS
UPDATE SCORE
SET SNO = (SELECT SNO FROM INSERTED) WHERE SNO = (SELECT SNO FROM DELETED);
CREATE TRIGGER student1 ON dbo.major FOR UPDATE AS
UPDATE STUDENT
SET MNO = (SELECT MNO FROM INSERTED) WHERE MNO = (SELECT MNO FROM DELETED);
CREATE TRIGGER student2update ON dbo.depart FOR UPDATE AS
UPDATE STUDENT
SET DMPNO = (SELECT DMPNO FROM INSERTED)
WHERE DMPNO = (SELECT DMPNO FROM DELETED); 数据删除:
CREATE TRIGGER teach1update ON dbo.teacher FOR DELETE AS
UPDATE TEACH SET TNO = NULL
WHERE TNO = (SELECT TNO FROM DELETED);
CREATE TRIGGER stu_majorupdate ON dbo.major FOR DELETE AS
UPDATE STUDENT SET MNO = NULL
WHERE MNO = (SELECT MNO FROM DELETED);
CREATE TRIGGER teacherupdate ON dbo.depart FOR DELETE AS
UPDATE TEACHER SET DMPNO = NULL
WHERE DMPNO = (SELECT DMPNO FROM DELETED)
2）java访问数据库及代码 package DBCONN; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class Conmysql { //这是mysql的连接字符串 private static String url =
\
static Connection conn = null;//定义一个空连接，后面用 static Statement stmt = null;//定义一个空查询语句，后面用 static ResultSet rs = null;//定义一个空的返回结果集，后面用 static Object result;
public static Object exqutesql(String sql) { try {
//注册mssql驱动
Class.forName(\//连接数据库
conn = DriverManager.getConnection(url, \//准备查询语句
stmt = conn.createStatement(); String beginsql = \
beginsql = sql.trim().substring(0, 1); // boolean execute = stmt.execute(sql);
//判断sql的类型，是insert/select/delete/update
//注意：只有select才能是executeQuery，其他三种都是executeUpdate if (beginsql.equalsIgnoreCase(\result = stmt.executeUpdate(sql);// // System.out.println(result); // }
if (beginsql.equalsIgnoreCase(\
result = stmt.executeQuery(sql);//保存查询结果到result，下同 }
if (beginsql.equalsIgnoreCase(\result = stmt.executeUpdate(sql); }
if (beginsql.equalsIgnoreCase(\result = stmt.executeUpdate(sql); System.out.println(result); }
// System.out.println(tag);
} catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace();
} catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally {
//注意：最后的时候要关闭所有连接，首先是stmt，然后是conn // try { // stmt.close(); // conn.close();
// } catch (SQLException e) { // // TODO Auto-generated catch block // e.printStackTrace(); // } }
return result; }
}
(2) SQL语句 USE [xsxjgl] GO
/****** 对象: Table [dbo].[student] SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
SET ANSI_PADDING ON GO
CREATE TABLE [dbo].[student]( [sno] [char](10) NOT NULL, [sname] [char](10) NULL, [ssex] [char](2) NULL, [sage] [int] NULL, [smz] [char](10) NULL, [sjg] [char](10) NULL, [sdepart] [char](30) NULL, [smajor] [char](30) NULL, [sgrade] [int] NULL, [sclass] [smallint] NULL, [szzmm] [char](10) NULL, [srxcj] [smallint] NULL,
脚本日期: 05/07/2014 11:07:39 ******/