数据库系统及应用实验与课程设计指导SQL2008.doc

select * from sys.serverswhere server_id=0select * from sys.databasesselect * from sys.filegroupsselect * from sys.master_filesselect * from sys.database_filesselect * from sys.data_spacescreate table test( id int not null, name char(100) null, constraint pk_test primary key clustered ( id asc)create nonclustered index ix_test on test(name)select *from sys.objectswhere type_desc=user_table and name=testselect * from sys.objectswhere type_desc=user_table and name=testor parent_object_id in( select object_id from sys.objectswhere type_desc=user_table and name=test)select * from sys.columnswhere object_id =12select * from sys.indexeswhere object_id=12325345select * from sys.partitionswhere object_id=12325345select * from sys.allocation_unitsselect * from sys.allocation_units u,sys.partitions pwhere u.type in (1,3) and u.container_id=p.hobt_id and p.object_id=33union allselect * from sys.allocation_units u,sys.partitions pwhere u.type = 2 and u.container_id=p.partition_id and p.object_id=33select * from sys.dm_db_partition_statswhere object_id=33select * from sys.index_columnswhere object_id=11select * from sys.database_principalsselect * from sys.types-数据库的创建create database educ on primary( name= student_data, filename =C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAstudent_data.mdf, size=10MB, maxsize=50MB, filegrowth=1MB)log on( name =student_log, filename=C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAstudent_log.ldf, size=2MB, maxsize=5MB, filegrowth =1%)-创建测试数据use educgocreate table student(s varchar(10),sname nvarchar(10),sage datetime,ssex nvarchar(10)insert into student values(01 , N赵雷 , 1990-01-01 , N男)insert into student values(02 , N钱电 , 1990-12-21 , N男)insert into student values(03 , N孙风 , 1990-05-20 , N男)insert into student values(04 , N李云 , 1990-08-06 , N男)insert into student values(05 , N周梅 , 1991-12-01 , N女)insert into student values(06 , N吴兰 , 1992-03-01 , N女)insert into student values(07 , N郑竹 , 1989-07-01 , N女)insert into student values(08 , N王菊 , 1990-01-20 , N女)create table course(c varchar(10),cname nvarchar(10),t varchar(10)insert into course values(01 , N语文 , 02)insert into course values(02 , N数学 , 01)insert into course values(03 , N英语 , 03)create table teacher(t varchar(10),tname nvarchar(10)insert into teacher values(01 , N张三)insert into teacher values(02 , N李四)insert into teacher values(03 , N王五)create table sc(s varchar(10),c varchar(10),score decimal(18,1)insert into sc values(01 , 01 , 80)insert into sc values(01 , 02 , 90)insert into sc values(01 , 03 , 99)insert into sc values(02 , 01 , 70)insert into sc values(02 , 02 , 60)insert into sc values(02 , 03 , 80)insert into sc values(03 , 01 , 80)insert into sc values(03 , 02 , 80)insert into sc values(03 , 03 , 80)insert into sc values(04 , 01 , 50)insert into sc values(04 , 02 , 30)insert into sc values(04 , 03 , 20)insert into sc values(05 , 01 , 76)insert into sc values(05 , 02 , 87)insert into sc values(06 , 01 , 31)insert into sc values(06 , 03 , 34)insert into sc values(07 , 02 , 89)insert into sc values(07 , 03 , 98)gouse educ go select database_id,is_read_only,collation_name,compatibility_levelfrom sys.databases where name = educ gouse educ go select databasepropertyex(educ,isautoshrink) gouse jxgl go select object_name(id) as 数据表名from sysobjectswhere xtype = uand objectproperty(id ,ismsshipped)=0gouse educgoexec sp_spaceusedgouse jxglgoalter database jxgladd file( name =xs_data, filename=C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAjxglsysxs_data.ndf, size =3MB, maxsize =10MB)gouse jxglgo alter database jxgladd log file( name =xs_log, filename=,filegrowth=10%)gouse educgoalter database educremove file xs_loggogoalter database educmodify name=xjglgogoexec sp_renamedb xjgl,educgo/Dept_info数据表的创建use educgocreate table Dept_info( dno char(4) primary key, dname char(16) not null, d_chair char(8), d_address varchar(50), tel char(12)GO/class_info数据表的创建use educgocreate table class_info( classno char(4) primary key, dname char(16) not null, monitor char(8), instructor char(8), tel char(12), dno char(4), foreign key(dno) references Dept_info(dno)GO/student_info数据表的创建use educgocreate table Student_info( sno char(8) primary key, sname char(8) not null, sex char(2), s_native varchar(50), birthday smalldatetime, classno char(4), entime smalldatetime, home varchar(50), tel char(12), dno char(4), foreign key(dno) references Dept_info(dno), foreign key(classno) references class_info(classno),)GOuse educgoinsert into class_info(classno,dname,monitor,instructor,tel)values(S13,王小明,王大明,大的,123456789123)gouse educgoinsert into sc(sno,cno,garde) values(s13,c12,88)gouse educgoinsert int sc(sno,cno) values(ss3,d9)gocreate table s( sno char(9) primary key, sname char(8), sex char(2), age smallint, sdept varchar(50)use educ gocreate table s( sno int identity(1,1), sname char(8), sex char(2), age smallint, sdept varchar(50)use educgoselect identity(int ,1,1) as #id,*into #tmp from s-SELECT IDENTITY(int,1,1) as PID,empID*1 as empID,empName INTO #T FROM sselect * from #tmpgouse educgoselect identitygocreate table s2( sno char(9) not null primary key, sname char(8) not null constraint s_sno unique, sex char(2), age smallint, sdept varchar(50)use educgocreate table s( sno char(9) not null primary key, sname char(8) not null constraint s_sno unique, sex char(2), age smallint check(age=15 and age=15 and age=15 and age=15 and age=15 and age=15)or(sdept=maand sdept=28;-查询选修了课程1或者课程2的学生select snofrom scwhere cno=1union select snofrom scwhere cno=2;select snofrom scwhere cno=1 and sno in( select sno from sc where cno=2)-查询计算机系的学生与年龄不大于19岁的学生的交集select *from studentwhere ssex=男intersect -except 此时是求的差集select *from studentwhere sage=19;select *from studentwhere ssex=男 and sage=19;-实验6 游标操作 -6.1 逐条显示use educgodeclare cs_cursor scroll cursorforselect s,sname,sage,ssexfrom student-for read onlyopen cs_cursorfetch from cs_cursorgo-关闭游标close cs_cursordeallocate cs_cursor-显示指定行数据use educgodeclare cs_cursor scroll cursorforselect s,sname ,sage,ssexfrom studentwhere s=02open cs_cursorfetch first from cs_cursorgofetch last from cs_cursorfetch prior from cs_cursor-显示从游标开始的第二条记录fetch absolute 2 from cs_cursor-用游标修改数据use educgodeclare cs_cursor scroll cursorforselect sname,ssexfrom studentwhere ssex=男for update of snameopen cs_cursorfetch absolute 2 from cs_cursorupdate student set sname=玩玩吧where current of cs_cursorfetch absolute 2 from cs_cursorgo-用游标删除数据use educgodeclare cs_cursor scroll cursorforselect sname,ssex from studentopen cs_cursorfetch absolute 2 from cs_cursordelete from student where current of cs_cursorgo-用游标遍历数据use educgodeclare cs_cursor scroll cursorforselect student.s,sname,sage from student,sc where student.s=sc.sopen cs_cursordeclare s char(9)declare sname char(8)declare sage intfetch next from cs_cursor into s,sname,sagewhile fetch_status=0begin print s+str(sage) fetch next from cs_cursor into s,sname,sageendclose cs_cursordeallocate cs_cursorgo-用游标备份数据库use educgodeclare name varchar(50) -数据库名declare path varchar(256) -文件备份路径declare fileName varchar(256) -备份文件名declare fileDate varchar(20) -用户数据文件set path=C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAD:educselect fileDate=convert(varchar(20),getdate(),112)declare db_cursor cursor forselect namefrom educ.dbo.sysdatebaseswhere name not in(master,model,msdb,tempdb)open db_cursor into namewhile fetch_status=0begin set filename=path+name+_+fileDate+.bak backup datebase name to disk =fileName fetch next from cs_cursor into nameendclose cs_cursordeallocate cs_cursorgo-实验七 存储过程的创建与应用 -7.1创建存储过程use educgocreate procedure s_ageasselect student.s,sname,ssex,sagefrom student join sc on student.s=sc.s join course on sc.s=course.cgo-调用存储过程use educgoexec s_agego-7.2 创建存储过程，要求输入名字，能从SC表中查到该学生的平均成绩use educgocreate procedure proc_exp s_name char(20)asselect avg(score) as 平均成绩from student join sc on student.s=sc.s and sname=s_namego-调用存储过程,求“钱电”平均成绩use educgoexec proc_exp 钱电go-7.3- create a table name is s_info, into the name,output the courses and avguse educgocreate procedure s_info s_name char(8)asdeclare s_count intdeclare s_avg realselect s_count=count(c),s_avg=avg(score)from student join sc on student.s=sc.s and sname=s_nameprint s_name +the student all have+str(s_count) +courses.And the average is :+str(s_avg)go-调用存储过程s-infouse educgoexec s_info 钱电go-7.4-查看数据表的索引信息-创建use educ gocreate proc table_info table varchar(30)as