Question

sql server分离数据库后,图标带小锁。我要交作业。把表拷给老师 ，老师能

sql server分离数据库后,图标带小锁。我要交作业。把表拷给老师 ，老师能加载出来吗？

Answer 1

USE master
GO
IF EXISTS (SELECT * FROM sysdatabases WHERE name = 'MySchool')
DROP DATABASE MySchool
GO

CREATE DATABASE MySchool
ON
(
NAME = ' MySchool_data', --主数据文件的逻辑名
FILENAME = 'D:\MySchool_data.mdf' , --主数据文件的物理名
SIZE = 10 MB, --主数据文件初始大小
FILEGROWTH = 20 %
)
LOG ON
(
NAME = 'MySchool_log',
FILENAME = 'D:\MySchool_log.ldf' ,
SIZE = 3MB,
MAXSIZE = 20MB,
FILEGROWTH = 1MB
)
GO

------------------------------------------------------------------创建表
USE MySchool
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name='Subject' )
DROP TABLE Subject
GO
CREATE TABLE Subject --创建课程表
(
SubjectNo int IDENTITY(1,1) NOT NULL,
SubjectName nvarchar(50) NOT NULL,
ClassHour int NOT NULL,
GradeId int NOT NULL
)
IF EXISTS (SELECT * FROM sysobjects WHERE name=' Result' )
DROP TABLE Result
GO
CREATE TABLE Result --创建成绩表
(
StudentNo int NOT NULL,
SubjectNo int NOT NULL,
StudentResult decimal(5,2) NOT NULL,
ExamDate datetime NOT NULL
)
GO

IF EXISTS(SELECT * FROM sysobjects WHERE name='Student')
DROP TABLE Student --------创建学生表
CREATE TABLE [dbo].[Student](
[StudentNo] [int] NOT NULL,
[LoginPwd] [nvarchar](50) NOT NULL,
[StudentName] [nvarchar](50) NOT NULL,
[Sex] bit NOT NULL,
[GradeId] [int] NOT NULL,
[Phone] [nvarchar](50) NULL,
[Address] [nvarchar](255) NULL,
[BornDate] [datetime] NOT NULL,
[Email] [nvarchar](50) NULL,
[IdentityCard] [varchar](18) NOT NULL
)

IF EXISTS(SELECT * FROM sysobjects WHERE name='Grade')
DROP TABLE Grade ---创建年级表
CREATE TABLE [dbo].[Grade](
[GradeId] [int] IDENTITY(1,1) NOT NULL,
[GradeName] [nvarchar](50) NOT NULL
)

------------------------------------------------------------------添加约束
ALTER TABLE Grade ADD CONSTRAINT PK_grade PRIMARY KEY(gradeId)

ALTER TABLE Student --主键约束
ADD CONSTRAINT PK_StuNo PRIMARY KEY (StudentNo)

ALTER TABLE Student --唯一约束（身份证号唯一）
ADD CONSTRAINT UQ_stuID UNIQUE (IdentityCard)

ALTER TABLE Student --默认约束（地址不详）
ADD CONSTRAINT DF_stuAddress DEFAULT ('地址不详') FOR Address

ALTER TABLE Student --检查约束（出生日期是自1980年1月1日以后）
ADD CONSTRAINT CK_stuBornDate CHECK(BornDate>='1980-1-1')

ALTER TABLE Student --添加外键约束
ADD CONSTRAINT FK_Grade
FOREIGN KEY(GradeID) REFERENCES Grade(GradeID)

ALTER TABLE Subject --主键约束（课程编号）
ADD CONSTRAINT PK_Subject PRIMARY KEY (SubjectNo)

ALTER TABLE Subject --非空约束（课程名称）
ADD CONSTRAINT CK_SubjectName CHECK (SubjectName is not null)

ALTER TABLE Subject WITH NOCHECK --检查约束（学时必须大于等于0）
ADD CONSTRAINT CK_ClassHour CHECK (ClassHour>=0)

ALTER TABLE Subject --外键约束（主表Grade和从表Subject建立引用关系）
ADD CONSTRAINT FK_GradeId
FOREIGN KEY (GradeId) REFERENCES Grade (GradeId)
GO

------------------------------------------------
ALTER TABLE Result --主键约束（学号、科目号、日期）
ADD CONSTRAINT PK_Result PRIMARY KEY
(StudentNo, SubjectNo, ExamDate)

ALTER TABLE Result --默认约束（日期为系统当前日期）
ADD CONSTRAINT CK_ExamDate DEFAULT (getdate()) FOR ExamDate

ALTER TABLE Result --检查约束（分数不能大于100，小于0）
ADD CONSTRAINT CK_StudentResult CHECK
(StudentResult BETWEEN 0 AND 100)

ALTER TABLE Result --外键约束（主表Student和从表Result建立关系）
ADD CONSTRAINT FK_StudentNo
FOREIGN KEY (StudentNo) REFERENCES Student (StudentNo)

ALTER TABLE Result --外键约束（主表Subject和从表Result建立关系）
ADD CONSTRAINT FK_SubjectNo
FOREIGN KEY (SubjectNo) REFERENCES Subject (SubjectNo)
GO
--------------------------------------------------------------

INSERT INTO Grade VALUES('S1')
INSERT INTO Grade VALUES('S2')
INSERT INTO Grade VALUES('Y2')

INSERT INTO Student
SELECT 10000,'123','郭靖',1,1,'8739874983','天津市河西区','1992-4-2','tain@34.com','12189234123453321' UNION
SELECT 10001,'abc','李文才',1,1,'22342434','地址不详','1990-3-2','dfesdf@34.com','22112189234533341' UNION
SELECT 10002,'555','李斯文',0,1,'745665656','河南洛阳','1992-8-4','4554@3gf4.com','32131218923414523' UNION
SELECT 10003,'1234','王爱家',0,1,'45645656','南京雨花台','1993-8-4','4554@3gf4.com','32131289214534123' UNION
SELECT 10004,'4444','刘宏',1,1,'32343434','成都谢菲联','1993-8-4','4554@3gf4.com','32131189245341223' UNION
SELECT 10005,'3333','孙甜甜',0,1,'2454554','杭州西湖','1993-8-4','4554@3gf4.com','32131214534123892' UNION
SELECT 20013,'123','黄药师',1,2,'45666434','北京市海淀区','1987-9-23','kjhjh@34.com','33411218924523321' UNION
SELECT 20014,'456','梅超风',0,2,'76774344','沈阳市和平区','1989-12-23','dsdsd@rtr.com','94531218321234123' UNION
SELECT 30025,'123456','周瑜',1,3,'32343535','上海市卢湾区','1991-4-3','fdfdf@sss.com','34533211218923412' UNION
SELECT 30026,'xyz','关羽',1,3,'74332434','广州是天河区','1990-11-12','erwer@343.com','13453321218923412'
INSERT INTO Subject
SELECT 'Winforms',20,1 UNION
SELECT 'Java Logic',34,1 UNION
SELECT 'SQL Base',16,1 UNION
SELECT 'HTML',48,1 UNION
SELECT 'Proc',8,1 UNION
SELECT 'C# OOP',46,2 UNION
SELECT 'Three Tier',32,2 UNION
SELECT 'Java OOP',56,2 UNION
SELECT 'JSP',42,2 UNION
SELECT 'ASP.NET',86,3 UNION
SELECT 'AJAX',46,3

INSERT INTO Result VALUES(10000,5,87.5,DEFAULT)
INSERT INTO Result VALUES(10000,5,67.5,'2011-3-5')
INSERT INTO Result VALUES(10001,5,48,'2010-8-9')
INSERT INTO Result VALUES(10002,5,65,DEFAULT)
INSERT INTO Result VALUES(10003,5,97.5,DEFAULT)

INSERT INTO Result VALUES(10004,5,60,DEFAULT)
INSERT INTO Result VALUES(10005,5,89,'2009-9-7')
INSERT INTO Result VALUES(30026,2,56,DEFAULT)
INSERT INTO Result VALUES(10000,3,77,'2011-11-2')
INSERT INTO Result VALUES(10001,3,45.5,DEFAULT)
INSERT INTO Result VALUES(10003,5,97.5,'2011-2-2')
INSERT INTO Result VALUES(20013,4,56,DEFAULT)
INSERT INTO Result VALUES(20014,4,43,DEFAULT)
INSERT INTO Result VALUES(10000,3,74,DEFAULT)
INSERT INTO Result VALUES(10001,11,45.5,'2010-12-3')

删除，更新，存储过程，照着书上随便写两条。。