实验三 创建数据库和数据表 实验报告
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
实验三
1、建立数据库university
2、建表：
1)Student:
USE [university]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[student](
[snum] [nchar](6)COLLATE Chinese_PRC_CI_AS NOT NULL,
[sname] [nchar](10)COLLATE Chinese_PRC_CI_AS NULL,
[sex] [nchar](2)COLLATE Chinese_PRC_CI_AS NULL,
[dept] [nchar](20)COLLATE Chinese_PRC_CI_AS NULL,
[birthday] [datetime] NULL,
[telephone] [nchar](12)COLLATE Chinese_PRC_CI_AS NULL, CONSTRAINT [PK_student] PRIMARY KEY CLUSTERED
(
[snum] ASC
)WITH(PAD_INDEX =OFF, IGNORE_DUP_KEY =OFF)ON [PRIMARY]
)ON [PRIMARY]
GO
ALTER TABLE [dbo].[student] WITH CHECK ADD CONSTRAINT [CK_sex] CHECK (([sex]='女'OR [sex]='男'))
GO
ALTER TABLE [dbo].[student] CHECK CONSTRAINT [CK_sex]
GO
ALTER TABLE [dbo].[student] WITH CHECK ADD CONSTRAINT [CK_snum] CHECK ((substring([snum],(1),(1))='S'))
GO
ALTER TABLE [dbo].[student] CHECK CONSTRAINT [CK_snum]
GO
ALTER TABLE [dbo].[student] WITH CHECK ADD CONSTRAINT [CK_tel] CHECK ((isnumeric(substring([telephone],(1),(3)))=(1)AND
isnumeric(substring([telephone],(5),(8)))=(1)))
GO
ALTER TABLE [dbo].[student] CHECK CONSTRAINT [CK_tel]
视图：
2)Course:
USE [university]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[course](
[cnum] [nchar](6)COLLATE Chinese_PRC_CI_AS NOT NULL,
[cname] [nchar](20)COLLATE Chinese_PRC_CI_AS NULL,
[credits] [smallint] NULL,
[descr] [nchar](10)COLLATE Chinese_PRC_CI_AS NULL,
[dept] [nchar](20)COLLATE Chinese_PRC_CI_AS NULL,
[textbook] [nchar](40)COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_course] PRIMARY KEY CLUSTERED
(
[cnum] ASC
)WITH(PAD_INDEX =OFF, IGNORE_DUP_KEY =OFF)ON [PRIMARY]
)ON [PRIMARY]
GO
ALTER TABLE[dbo].[course] WITH CHECK ADD CONSTRAINT[FK_course_course] FOREIGN KEY([cnum])
REFERENCES [dbo].[course] ([cnum])
GO
ALTER TABLE [dbo].[course] CHECK CONSTRAINT [FK_course_course]
GO
ALTER TABLE[dbo].[course] WITH CHECK ADD CONSTRAINT[CK_course] CHECK ((substring([cnum],(1),(1))='c'))
GO
ALTER TABLE [dbo].[course] CHECK CONSTRAINT [CK_course]
GO
ALTER TABLE [dbo].[course] WITH CHECK ADD CONSTRAINT [CK_course_1]
CHECK(([credits]>(0)AND [credits]<(8)))
GO
ALTER TABLE [dbo].[course] CHECK CONSTRAINT [CK_course_1]
3)Sc:
USE [university]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[sc](
[snum] [nchar](6)COLLATE Chinese_PRC_CI_AS NOT NULL,
[secnum] [nchar](8)COLLATE Chinese_PRC_CI_AS NOT NULL,
[score] [int] NULL,
CONSTRAINT [PK_sc] PRIMARY KEY CLUSTERED
(
[snum] ASC,
[secnum] ASC
)WITH(PAD_INDEX =OFF, IGNORE_DUP_KEY =OFF)ON [PRIMARY]
)ON [PRIMARY]
GO
ALTER TABLE [dbo].[sc] WITH CHECK ADD CONSTRAINT [FK_sc_section] FOREIGN KEY([secnum])
REFERENCES [dbo].[section] ([secnum])
GO
ALTER TABLE [dbo].[sc] CHECK CONSTRAINT [FK_sc_section]
GO
ALTER TABLE [dbo].[sc] WITH CHECK ADD CONSTRAINT [FK_sc_student] FOREIGN KEY([snum])
REFERENCES [dbo].[student] ([snum])
GO
ALTER TABLE [dbo].[sc] CHECK CONSTRAINT [FK_sc_student]
GO
ALTER TABLE[dbo].[sc] WITH CHECK ADD CONSTRAINT[CK_Table_score] CHECK