本文用来存放sql server 练习2的，原本是sql文件来的。要还原的话，复制以下代码到sql文件中去然后执行即可。
use [master]
go
/****** object: database [stumanage] script date: 2018/4/15 23:06:28 ******/
create database [stumanage]
containment = none
on primary
( name = n'stumanage2', filename = n'c:\program files\microsoft sql server\mssql11.mssqlserver\mssql\data\stumanage2.mdf' , size = 4096kb , maxsize = unlimited, filegrowth = 1024kb )
log on
( name = n'stumanage2_log', filename = n'c:\program files\microsoft sql server\mssql11.mssqlserver\mssql\data\stumanage2_log.ldf' , size = 5184kb , maxsize = 2048gb , filegrowth = 10%)
go
alter database [stumanage] set compatibility_level = 90
go
if (1 = fulltextserviceproperty('isfulltextinstalled'))
begin
exec [stumanage].[dbo].[sp_fulltext_database] @action = 'disable'
end
go
alter database [stumanage] set ansi_null_default off
go
alter database [stumanage] set ansi_nulls off
go
alter database [stumanage] set ansi_padding off
go
alter database [stumanage] set ansi_warnings off
go
alter database [stumanage] set arithabort off
go
alter database [stumanage] set auto_close off
go
alter database [stumanage] set auto_create_statistics on
go
alter database [stumanage] set auto_shrink off
go
alter database [stumanage] set auto_update_statistics on
go
alter database [stumanage] set cursor_close_on_commit off
go
alter database [stumanage] set cursor_default global
go
alter database [stumanage] set concat_null_yields_null off
go
alter database [stumanage] set numeric_roundabort off
go
alter database [stumanage] set quoted_identifier off
go
alter database [stumanage] set recursive_triggers off
go
alter database [stumanage] set disable_broker
go
alter database [stumanage] set auto_update_statistics_async off
go
alter database [stumanage] set date_correlation_optimization off
go
alter database [stumanage] set trustworthy off
go
alter database [stumanage] set allow_snapshot_isolation off
go
alter database [stumanage] set parameterization simple
go
alter database [stumanage] set read_committed_snapshot off
go
alter database [stumanage] set honor_broker_priority off
go
alter database [stumanage] set recovery full
go
alter database [stumanage] set multi_user
go
alter database [stumanage] set page_verify checksum
go
alter database [stumanage] set db_chaining off
go
alter database [stumanage] set filestream( non_transacted_access = off )
go
alter database [stumanage] set target_recovery_time = 0 seconds
go
exec sys.sp_db_vardecimal_storage_format n'stumanage', n'on'
go
use [stumanage]
go
/****** object: table [dbo].[course] script date: 2018/4/15 23:06:29 ******/
set ansi_nulls on
go
set quoted_identifier on
go
set ansi_padding on
go
create table [dbo].[course](
[cno] [char](6) not null,
[cname] [char](40) not null,
[cpno] [char](6) null,
constraint [pk_course] primary key clustered
(
[cno] 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
set ansi_padding off
go
/****** object: table [dbo].[course_class] script date: 2018/4/15 23:06:29 ******/
set ansi_nulls on
go
set quoted_identifier on
go
set ansi_padding on
go
create table [dbo].[course_class](
[ccno] [char](10) not null,
[cno] [char](6) null,
[term] [varchar](12) null,
[tno] [char](6) null,
[coursetype] [varchar](10) null,
[examtype] [varchar](10) null,
[credit] [smallint] null,
[room] [varchar](20) null,
[weektime] [varchar](20) null,
[validity] [bit] null,
constraint [pk_course_class] primary key clustered
(
[ccno] 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
set ansi_padding off
go
/****** object: table [dbo].[department] script date: 2018/4/15 23:06:29 ******/
set ansi_nulls on
go
set quoted_identifier on
go
set ansi_padding on
go
create table [dbo].[department](
[dno] [char](6) not null,
[dname] [varchar](20) null,
[dtel] [varchar](11) null,
constraint [pk_department] primary key clustered
(
[dno] 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
set ansi_padding off
go
/****** object: table [dbo].[major] script date: 2018/4/15 23:06:29 ******/
set ansi_nulls on
go
set quoted_identifier on
go
set ansi_padding on
go
create table [dbo].[major](
[mno] [char](6) not null,
[mname] [varchar](40) null,
[msubject] [varchar](40) null,
[dno] [char](6) null,
constraint [pk_major] primary key clustered
(
[mno] 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
set ansi_padding off
go
/****** object: table [dbo].[student] script date: 2018/4/15 23:06:29 ******/
set ansi_nulls on
go
set quoted_identifier on
go
set ansi_padding on
go
create table [dbo].[student](
[sno] [varchar](10) not null,
[sname] [varchar](20) not null,
[mno] [char](6) not null,
[ssex] [char](4) null,
[sbirth] [smalldatetime] null,
[snative] [varchar](20) null,
[sheight] [int] null,
[sweight] [int] null,
[sentime] [smalldatetime] null,
constraint [pk_student] primary key clustered
(
[sno] 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
set ansi_padding off
go
/****** object: table [dbo].[student_course] script date: 2018/4/15 23:06:29 ******/
set ansi_nulls on
go
set quoted_identifier on
go
set ansi_padding on
go
create table [dbo].[student_course](
[sno] [varchar](10) not null,
[ccno] [char](10) not null,
[normalmark] [tinyint] null,
[exammark] [tinyint] null,
[mark] [tinyint] null,
constraint [pk_student_course] primary key clustered
(
[sno] asc,
[ccno] 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
set ansi_padding off
go
/****** object: table [dbo].[teacher] script date: 2018/4/15 23:06:29 ******/
set ansi_nulls on
go
set quoted_identifier on
go
set ansi_padding on
go
create table [dbo].[teacher](
[tno] [char](6) not null,
[tname] [varchar](20) null,
[tsex] [char](4) null,
[tbirth] [datetime] null,
[trank] [varchar](20) null,
[tdegree] [char](6) null,
[dno] [char](6) null,
constraint [pk_teacher] primary key clustered
(
[tno] 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
set ansi_padding off
go
/****** object: view [dbo].[mark_rank_view] script date: 2018/4/15 23:06:29 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create view [dbo].[mark_rank_view] as
select top 10 student.sname,mark,'金融学' as '科目' from student,student_course,course_class,course
where student.sno = student_course.sno
and student_course.ccno = course_class.ccno
and course_class.cno = course.cno
and course.cname='金融学'
order by mark desc ;
go
/****** object: view [dbo].[student_mark_view] script date: 2018/4/15 23:06:29 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create view [dbo].[student_mark_view] as select sno,sum(mark) as 总成绩,max(mark) as 最高成绩 from student_course group by sno;
go
/****** object: view [dbo].[three_course_view] script date: 2018/4/15 23:06:29 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create view [dbo].[three_course_view]
as select student.sname,course.cname from student,student_course,course_class,course
where student.sno = student_course.sno
and student_course.ccno = course_class.ccno
and course_class.cno = course.cno;
go
/****** object: view [dbo].[three_course_view_noexists] script date: 2018/4/15 23:06:29 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create view [dbo].[three_course_view_noexists]
as select student.sname,course.cname from student,student_course,course_class,course
where student.sno = student_course.sno
and student_course.ccno = course_class.ccno
and course_class.cno = course.cno;
go
alter table [dbo].[course_class] with check add constraint [fk_course_class_course] foreign key([cno])
references [dbo].[course] ([cno])
go
alter table [dbo].[course_class] check constraint [fk_course_class_course]
go
alter table [dbo].[course_class] with check add constraint [fk_course_class_teacher] foreign key([tno])
references [dbo].[teacher] ([tno])
go
alter table [dbo].[course_class] check constraint [fk_course_class_teacher]
go
alter table [dbo].[major] with check add constraint [fk_major_department] foreign key([dno])
references [dbo].[department] ([dno])
go
alter table [dbo].[major] check constraint [fk_major_department]
go
alter table [dbo].[student] with check add constraint [fk_student_major] foreign key([mno])
references [dbo].[major] ([mno])
go
alter table [dbo].[student] check constraint [fk_student_major]
go
alter table [dbo].[student_course] with check add constraint [fk_student_course_course_class] foreign key([ccno])
references [dbo].[course_class] ([ccno])
go
alter table [dbo].[student_course] check constraint [fk_student_course_course_class]
go
alter table [dbo].[teacher] with check add constraint [fk_teacher_department] foreign key([dno])
references [dbo].[department] ([dno])
go
alter table [dbo].[teacher] check constraint [fk_teacher_department]
go
use [master]
go
alter database [stumanage] set read_write
go