數(shù)據(jù)庫(kù)系統(tǒng)及應(yīng)用實(shí)驗(yàn)與課程設(shè)計(jì)指導(dǎo)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-數(shù)據(jù)庫(kù)的創(chuàng)建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%)-創(chuàng)建測(cè)試數(shù)據(jù)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孫風(fēng) , 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語(yǔ)文 , 02)insert into course values(02 , N數(shù)學(xué) , 01)insert into course values(03 , N英語(yǔ) , 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 數(shù)據(jù)表名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數(shù)據(jù)表的創(chuàng)建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數(shù)據(jù)表的創(chuàng)建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數(shù)據(jù)表的創(chuàng)建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的學(xué)生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)-查詢計(jì)算機(jī)系的學(xué)生與年齡不大于19歲的學(xué)生的交集select *from studentwhere ssex=男intersect -except 此時(shí)是求的差集select *from studentwhere sage=19;select *from studentwhere ssex=男 and sage=19;-實(shí)驗(yàn)6 游標(biāo)操作 -6.1 逐條顯示use educgodeclare cs_cursor scroll cursorforselect s,sname,sage,ssexfrom student-for read onlyopen cs_cursorfetch from cs_cursorgo-關(guān)閉游標(biāo)close cs_cursordeallocate cs_cursor-顯示指定行數(shù)據(jù)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-顯示從游標(biāo)開始的第二條記錄fetch absolute 2 from cs_cursor-用游標(biāo)修改數(shù)據(jù)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-用游標(biāo)刪除數(shù)據(jù)use educgodeclare cs_cursor scroll cursorforselect sname,ssex from studentopen cs_cursorfetch absolute 2 from cs_cursordelete from student where current of cs_cursorgo-用游標(biāo)遍歷數(shù)據(jù)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-用游標(biāo)備份數(shù)據(jù)庫(kù)use educgodeclare name varchar(50) -數(shù)據(jù)庫(kù)名declare path varchar(256) -文件備份路徑declare fileName varchar(256) -備份文件名declare fileDate varchar(20) -用戶數(shù)據(jù)文件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-實(shí)驗(yàn)七 存儲(chǔ)過(guò)程的創(chuàng)建與應(yīng)用 -7.1創(chuàng)建存儲(chǔ)過(guò)程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-調(diào)用存儲(chǔ)過(guò)程use educgoexec s_agego-7.2 創(chuàng)建存儲(chǔ)過(guò)程，要求輸入名字，能從SC表中查到該學(xué)生的平均成績(jī)use educgocreate procedure proc_exp s_name char(20)asselect avg(score) as 平均成績(jī)from student join sc on student.s=sc.s and sname=s_namego-調(diào)用存儲(chǔ)過(guò)程,求“錢電”平均成績(jī)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-調(diào)用存儲(chǔ)過(guò)程s-infouse educgoexec s_info 錢電go-7.4-查看數(shù)據(jù)表的索引信息-創(chuàng)建use educ gocreate proc table_info table varchar(30)as