《数据库系统及应用实验与课程设计指导实验报告》由会员分享，可在线阅读，更多相关《数据库系统及应用实验与课程设计指导实验报告（62页珍藏版）》请在金锄头文库上搜索。
1、实验一 SQL Server 2008 基本服务和信息一 实验目的 要求学生查看SQL Server 2008联机丛书的内容，了解SQL 的环境及基本操作，了解SQL的基本信息。二 实验内容查看书内容，环境并掌握其基本操作，服务器管理和注册查看SQL的目录结构，利用SQL的基本系统视图查看相关信息。三 实验指导select * from sys.serverswhere server_id=0select * from sys.databasesselect * from sys.filegroupsselect * from sys.master_filesselect * from sys
2、.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_de
3、sc=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=select * from sys.partitionswhere object_id=select * from sys.allocation_unitsselect * fro
4、m 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=33se
5、lect * from sys.index_columnswhere object_id=11select * from sys.database_principalsselect * from sys.types实验二 数据库的创建和管理一 实验目的 要求学生熟练的使用SQL创建和管理数据库，并学会使用SQL查询分析器接受和运行结果分析。二 实验内容创建数据库,查看和修改数据库的属性,修改数据库名称,删除数据库.三 实验指导-数据库的创建create database educ on primary( name= student_data, filename =C:Program Files
6、Microsoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAstudent_data.mdf, size=10MB, maxsize=50MB, filegrowth=1MB)log on( name =student_log, filename=C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAstudent_log.ldf, size=2MB, maxsize=5MB, filegrowth =1%)use educ go select database_id,is_read_o
7、nly,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
8、 jxgladd file( name =xs_data, filename=C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAjxglsysxs_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
9、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,
10、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, cla
11、ssno 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,王小明,王大明,大的,3)gouse educgoinsert into sc
12、(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)实验四 数据库约束实验一 实验目的1. 理解数据库完整性约束的概念2. 掌握SQL的完整性约束实验技术3. 了解SQL的违反完整性约束的处理措施二 实验内容1. 理解域完整性,实体完整性,参照完整性和用户定义完整性的意义2. 定义
13、和管理主键约束,外键约束,唯一性约束.3. 定义和管理检查约束,默认值约束,允许空值约束.create table s( sno char(9) primary key, sname char(8), sex char(2), age smallint, sdept varchar(50) -X*Y 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 #tmpgo -X*Y use educgoalter table swith nocheck add constraint ck_s_deptcheck(sdept in(cs,ma,is),constraint ck_s_age check(age=15 and age=15 and age=30)gouse e
