creattable.txt

J2EE上课详细课件,精通J2EE编程教程.

-----------SQL Server银行账户表----------
CREATE TABLE account(
	account_id numeric(23, 0) NOT NULL primary key,
	account_name varchar(80)  ,
	account varchar(40)  ,
	account_money money NULL) 
----SQL Server银行账户表初始化数据--
insert into account values (1,’长沙市高新区博大科技有限公司’,’900567883452145675’,1200000)
insert into account values (2,’湖南新材料股份有限公司’,’810023452231233454’,1100000)
insert into account values (3,’邓子云’,’80190110223333234’,100000)

-------------Sybase银行账户表-------------
CREATE TABLE account(
	account_id numeric(23, 0) NOT NULL primary key,
	account_name varchar(80)  ,
	account varchar(40)  ,
	account_money money NULL)
------Sybase银行账户表初始化数据-----
insert into account values (1,'邓佳容','566556565345655',100000)
insert into account values (2,'黄婧婧','566356565345675',100000)
insert into account values (3,'邓平初','678556565345655',100000)


-----学生\课程\班级\班主任--------
---------------班级表--------------------
CREATE TABLE [dbo].[classTable] (
	[classId] [bigint] NOT NULL ,
	[className] [varchar] (50)  NULL 
) ON [PRIMARY]
GO
--------------课程表---------------------
CREATE TABLE [dbo].[lession] (
	[lessionId] [bigint] NOT NULL ,
	[lessionName] [varchar] (50)  NULL 
) ON [PRIMARY]
GO
----------------班主任表-----------------
CREATE TABLE [dbo].[masterTeacher] (
	[classId] [bigint] NOT NULL ,
	[masterTeacherName] [varchar] (50)  NULL 
) ON [PRIMARY]
GO
---------------学生表--------------------
CREATE TABLE [dbo].[student] (
	[studentId] [bigint] NOT NULL ,
	[studentName] [varchar] (50)  NULL ,
	[classId] [bigint] NULL 
) ON [PRIMARY]
GO
-------------学生与课程的关系表----------
CREATE TABLE [dbo].[studentLession] (
	[lessionId] [bigint] NULL ,
	[studentId] [bigint] NULL 
) ON [PRIMARY]
GO
-------------班级表主键约束--------------
ALTER TABLE [dbo].[classTable] WITH NOCHECK ADD 
	CONSTRAINT [PK_classTable] PRIMARY KEY  CLUSTERED 
	(
		[classId]
	)  ON [PRIMARY] 
GO
-------------课程表主键约束---------------
ALTER TABLE [dbo].[lession] WITH NOCHECK ADD 
	CONSTRAINT [PK_lession] PRIMARY KEY  CLUSTERED 
	(
		[lessionId]
	)  ON [PRIMARY] 
GO
------------班主任表主键约束------------------
ALTER TABLE [dbo].[masterTeacher] WITH NOCHECK ADD 
	CONSTRAINT [PK_masterTeacher] PRIMARY KEY  CLUSTERED 
	(
		[classId]
	)  ON [PRIMARY] 
GO
----------------学生表主键约束-------------------
ALTER TABLE [dbo].[student] WITH NOCHECK ADD 
	CONSTRAINT [PK_student] PRIMARY KEY  CLUSTERED 
	(
		[studentId]
	)  ON [PRIMARY] 
GO
--------------班主任表外键约束------------------
ALTER TABLE [dbo].[masterTeacher] ADD 
	CONSTRAINT [FK_masterTeacher_classTable] FOREIGN KEY 
	(
		[classId]
	) REFERENCES [dbo].[classTable] (
		[classId]
	) ON DELETE CASCADE 
GO
--------------学生表外键约束---------------------
ALTER TABLE [dbo].[student] ADD 
	CONSTRAINT [FK_student_classTable] FOREIGN KEY 
	(
		[classId]
	) REFERENCES [dbo].[classTable] (
		[classId]
	)
GO
--------------学生课程关系表外键约束---------
ALTER TABLE [dbo].[studentLession] ADD 
	CONSTRAINT [FK_studentLession_lession] FOREIGN KEY 
	(
		[lessionId]
	) REFERENCES [dbo].[lession] (
		[lessionId]
	),
	CONSTRAINT [FK_studentLession_student] FOREIGN KEY 
	(
		[studentId]
	) REFERENCES [dbo].[student] (
		[studentId]
	)
GO

-------初始化数据-----
-----插入班级表初始数据--------
insert into classTable(classId,className) values(1,'计算机05-1')
insert into classTable(classId,className) values(2,'计算机05-2')
insert into classTable(classId,className) values(3,'经济管理05-1')
insert into classTable(classId,className) values(4,'经济管理05-2')
----插入课程表初始数据--------
insert into lession(lessionId,lessionName) values(1,'软件工程')
insert into lession(lessionId,lessionName) values(2,'计算机组成原理')
insert into lession(lessionId,lessionName) values(3,'编译原理')
insert into lession(lessionId,lessionName) values(5,'哲学')
insert into lession(lessionId,lessionName) values(6,'JSP网络编程')
insert into lession(lessionId,lessionName) values(7,'经济法')
----插入学生表初始数据-------
insert into student(studentID,studentName,classId) values(1,'邓佳容',1)
insert into student(studentID,studentName,classId) values(2,'邓子云',1)
insert into student(studentID,studentName,classId) values(3,'黄婧',1)
insert into student(studentID,studentName,classId) values(4,'黄德湘',2)
insert into student(studentID,studentName,classId) values(5,'张利辉',2)
----插入学生课程关系表初始数据----
insert into studentLession(lessionId,studentId) values(1,1)
insert into studentLession(lessionId,studentId) values(1,2)
insert into studentLession(lessionId,studentId) values(1,3)
insert into studentLession(lessionId,studentId) values(2,1)
insert into studentLession(lessionId,studentId) values(2,2)
----插入班主任表初始数据------
insert into masterTeacher(classId,masterTeacherName) values(1,'陈明远')
insert into masterTeacher(classId,masterTeacherName) values(2,'李小花')
insert into masterTeacher(classId,masterTeacherName) values(3,'刘德明')
insert into masterTeacher(classId,masterTeacherName) values(4,'陈向东')