SQLserver练习题之数据库sql文件

本文用来存放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