sql新建数据库

USE [master]

GO

--创建数据库 --FILENAME 是你数据库文件的存放路径 自己改

CREATE DATABASE [User] ON PRIMARY

( NAME = N'User', FILENAME = N'E:\test\User.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

LOG ON

( NAME = N'User_log', FILENAME = N'E:\test\User_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

GO

--创建表

USE [User]

GO

CREATE TABLE [dbo].[t_dept](

[deptno] [int] IDENTITY(1,1) NOT NULL,

[deptname] [nvarchar](500) NOT NULL,

CONSTRAINT [PK_t_dept] PRIMARY KEY CLUSTERED

(

[deptno] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[t_class](

[classno] [int] IDENTITY(1,1) NOT NULL,

[classname] [nvarchar](500) NOT NULL,

CONSTRAINT [PK_t_class] PRIMARY KEY CLUSTERED

(

[classno] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[t_user](

[userid] [int] NOT NULL,

[username] [nvarchar](500) NOT NULL,

[deptno] [int] NOT NULL,

[classno] [int] NOT NULL,

[password] [varchar](500) NOT NULL,

CONSTRAINT [PK_t_user] PRIMARY KEY CLUSTERED

(

[userid] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

--插入数据

use [user]

go

insert into t_class values('2004级1班')

insert into t_class values('2004级2班')

insert into t_class values('2004级3班')

insert into t_class values('2004级4班')

insert into t_class values('2004级5班')

insert into t_dept values('数学系')

insert into t_dept values('计算机系')

insert into t_dept values('历史系')

insert into t_dept values('经济系')

insert into t_dept values('中文系')

insert into t_user(userid,username,deptno,classno,password)

values(2004021,'用心',1,1,'yongxin')

insert into t_user(userid,username,deptno,classno,password)

values(2004022,'李牛',1,2,'yongxin')

insert into t_user(userid,username,deptno,classno,password)

values(2004023,'王虎',2,1,'yongxin')

insert into t_user(userid,username,deptno,classno,password)

values(2004024,'乖乖',2,2,'yongxin')

insert into t_user(userid,username,deptno,classno,password)

values(2004025,'帅哥',4,1,'yongxin')

--file name 你自己设置下路径

--密码没加密 写的明文 自己插入加密值

--测试版本ssms 2005

如果触发器请按照如下创建：

after update

declare @dnum ,@olddnum(类型的话和你学院信息表D中的DNUm列类型是一样的)

然后select @dnum = DNUM from inserted

select @olddnum = DNUM from deleted

然后update 学生信息表S set DNUM=@dnum where DNUM=@olddnum

ok，思路就是这个样子，可完善后执行。完善它应该没有问题吧？

其实这个可以不用自己写触发器，学生信息表中的DNUM肯定为外键。在设计表时，单击管理关系声明外键的时候请按照如下：

---