SQL实验报告2.docx

SQL实验报告2.docx

《SQL实验报告2.docx》由会员分享，可在线阅读，更多相关《SQL实验报告2.docx（31页珍藏版）》请在冰豆网上搜索。

SQL实验报告2

SQLServer2000

实

验

报

告

姓名：

李奇轩

院系：

化工学院盐湖系

专业：

09因材施教

学号：

0922405012

实验一数据库的创建和管理

一、实验目的与要求

1）掌握用企业管理器和T-SQL语句创建数据库的方法。

2）掌握修改数据库，数据库更名的方法。

3）掌握删除数据库的方法。

二、实验过程设计及实验步骤

1．在SQLServer企业管理器中创建一个数据库，具体要求如下：

（1）数据库名称为Test1。

（2）主要数据文件：

逻辑文件名为Test1Data1，物理文件名为Test1Data1.mdf，初始容量为1MB，最大容量为10MB，递增量为1MB。

（3）次要数据文件：

逻辑文件名为Test1Data2，物理文件名为Test1Data2.ndf，初始容量为1MB，最大容量为10MB，递增量为1MB。

（4）事务日志文件：

逻辑文件名为Test1Log1，物理文件名为Test1Log1.ldf，初始容量为1MB，大容量为5MB，递增量为512KB。

2．在查询分析器中输入并执行一个CREATEDATABASE语句，具体要求如下：

（1）数据库名称为Test2；

（2）主要数据文件：

逻辑文件名为Test2Datal，物理文件名为Test2Datal.mdf，初始容量为1MB，最大容量为10MB，递增量为1MB；

（3）次要数据文件：

逻辑文件名为Test2Data2，物理文件名为Test2Data2.ndf，初始容量为1MB，最大容量为10MB，递增量为1MB；（4）事务日志文件：

逻辑文件名为Test2Logl，物理文件名为Test2Logl.Ldf，初始容量为512KB，最大容量为5MB，递增量为512KB。

createdatabaseTest2

on

primary

（name=Test2data1,

filename='d:

\Test2data1.mdf',

size=1,

maxsize=10,

filegrowth=1

）

filegroupff

（name=Test2data2,

filename='d:

\Test2data2.mdf',

size=1,

maxsize=10,

filegrowth=1）

logon

（name=Test2Log1,

filename='d:

\Test2data1.mdf',

size=1,

maxsize=5,

filegrowth=512kb）

3．按照下列要求在企业管理器中修改第2题中创建的Test2数据库：

（1）主要数据文件的容量为2MB，最大容量为20MB，递增量为2MB。

（2）次要数据文件的容量为2MB，最大容量为20MB，递增量为2MB。

（3）事务日志文件：

初始容量为1MB，最大容量为10MB，递增量为1MB。

alterdatabasetest2

modifyfile

（

name=Test2data1,

size=2,

maxsize=20,

filegrowth=2

）

alterdatabasetest2

modifyfile

（

name=Test2data2,

size=2,

maxsize=20,

filegrowth=2

）

alterdatabasetest2

modifyfile

（

name=Test2Log1,

size=1,

maxsize=10,

filegrowth=1

）

4．数据库更名，要求：

把TEST1数据库改名为new_TEST1。

alterdatabasetest1

modifyname=new_test1

5．收缩数据库，要求：

分别使用DBCCSHRINKDATABASE和DBCCSHRINKFILE进行收缩。

DBCCSHRINKDATABASE

（test2

target_percent

        [,{NOTRUNCATE|TRUNCATEONLY}]

    ）

6．删除数据库

（1）在企业管理器中删除new_TEST1数据库。

在Enterprisemanager可以在“new_TEST1”上单击右键，然后单击删除

（2）在查询分析器中用DROPDATABASE语句删除经过第3题修改以后的Test2数据库。

dropdatabaseTest2

三、SQL调试及结果

createdatabaseTest2

on

primary

（name=Test2data1,

filename='d:

\Test2data1.mdf',

size=1,

maxsize=10,

filegrowth=1

name=Test2data2,

filename='d:

\Test2data2.mdf',

size=1,

maxsize=10,

filegrowth=1）

logon

（name=Test2Log1,

filename='d:

\Test2data1.mdf',

size=512kb,

maxsize=5,

filegrowth=512kb）

上面那样做程序报错，次要数据文件的生成应该是用filegroup编写，

createdatabaseTest2

on

primary

（name=Test2data1,

filename='d:

\Test2data1.mdf',

size=1,

maxsize=10,

filegrowth=1

）

filegroupff

（name=Test2data2,

filename='d:

\Test2data2.mdf',

size=1,

maxsize=10,

filegrowth=1）

数据库改名字：

alterdatabasetest1

modifyname=new_test1

删除数据库：

在Enterprisemanager可以在要删除的数据库图标上单击右键，然后单击删除

用命令删除：

dropdatabaseTest2

四、实验体会

通过这次实验我们了解了EnterpriseManager和T-SQL在很多情况下具有相同的作用，学会了在两种情况下创建数据库（createdatabase）、修改数据库（alterdatabase）以及删除数据库（dropdatabase），在数据库的基础上又可以添加删除数据文件，以及对数据库各种属性的编辑。

利用T-SQL修改数据库的语法还不是很熟悉，主要是

alterdatabase<数据库名称>

modifyfile|

modifyname=New_name

生成文件的时候以语法格式，但是在次实验中

特别注意的是，在修改指定文件的属性的时候，一次只能用该语句修改一个文件的一个属性，修改后size应该比修改前大。

alterdatabase命令可以修改数据库包括：

添加文件、重命令、删除文件等。

但是该命令DBA或是具有createdatabase权限的数据库才可以用

还了解到一个数据库包含的文件种类为：

.mdf（数据文件）、ldf（日志文件）事务日志文件一般为数据文件的一半比较适中。

像SQL这样的可视化程序，我们既可以用代码编写，又可以直接在界面上编辑。

后者明显可以节约很多的时间，但是为了程序的健全性和使用者容易查看我们多数还是用代码。

实验二数据查询

一、实验目的与要求

1）掌握使用SELECT语句查询数据。

2）掌握在企业管理器中查询数据的方法。

二、实验过程设计及实验步骤

实验表结构如下:

学生表：

Student（Sno,Sname,Ssex,Sage,Sdept），其中Sno为主键

课程表：

Course（Cno,Cname,Ccredit），其中Cno为主键

学生选课表：

SC（Sno,Cno,Grade），其中Sno,Cno的组合为主键

要求：

首先创建数据库XSGL，在该数据库中创建以上三表,在各表中输入一些记录,然后进行下面的操作,写出相应的命令序列:

1）查询全体学生的学号和姓名。

程序代码：

selectSno,SnamefromStudent

2）查询全体学生的姓名、学号、所在系。

程序代码：

selectSno,Sname,SdeptfromStudent

3）查询全体学生的详细信息。

程序代码：

select*fromStudent

4）查询全体学生的姓名及其出生年份。

程序代码：

selectSname,（2008-Sage）asyearsfromStudent

5）查询软件工程系全体学生的名单。

程序代码：

selectSnamefromStudentwhere（Sdept='软件工程'）

6）查询所有年龄在20岁以下的学生姓名以及年龄。

程序代码：

selectSname,SagefromStudentwhere（Sage<20）

7）查询考试成绩不及格的学生的学号。

程序代码：

selectSno,GradefromSCwhere（Grade<60）

8）查询年龄在20-23岁（包括20，23）之间的学生的姓名、系别和年龄。

程序代码：

selectSname,Sage,SdeptfromStudent

where（Sage>=20andSage<=23）

9）查询不在信息系、数学系、也不在软件工程系学生的姓名和性别。

程序代码：

selectSname,Sno,SsexfromStudent

where（Sdept<>'信息'andSdept<>'数学'andSdept<>'软件工程'）

10）查询所有姓刘的学生的姓名、学号和性别。

程序代码：

selectSname,Sno,SsexfromStudentwhere（Snamelike'刘%'）

11）查询姓“欧阳”且全名为三个汉字的学生的姓名。

程序代码：

selectSnamefromStudentwhere（Snamelike'欧阳_'）

12）查询姓名中第2个字为“阳”字的学生的姓名和学号。

程序代码：

selectSnamefromStudentwhere（Snamelike'_阳%'）

13）查询所有不姓刘的学生的姓名、学号。

程序代码：

selectSname,Sno,SsexfromStudentwhere（Snamenotlike'刘%'）

14）查询缺少成绩的学生的学号和相应的课程号。

程序代码：

selectSno,CnofromSCwhere（Gradeisnull）

15）查询软件工程系年龄在20岁以下的学生姓名。

程序代码：

selectSname,SagefromStudent

where（Sdept='软件工程'andSage<20）

16）查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列。

程序代码：

selectSno,GradefromSCwhere（Cno='C3'）orderbyGradedesc

17）查询全体学生情况，结果按所在系的升序排列，同一系的按年龄降序排列。

程序代码：

select*fromStudentorderbySdeptasc,Sagedesc

18）统计学生总人数。

程序代码：

selectcount（Sname）asStuAllfromStudent

19）查询选修了课程的学生人数。

程序代码:

selectcount（distinctSno）fromSCwhere（Gradeisnotnull）

20）计算选修了1号课程的学生平均成绩。

程序代码selectavg（Grade）asCavgfromSCwhere（Cno='C1'）

21）查询选修了1号课程的学生最高分数。

程序代码：

selectmax（Grade）ashighestfromSCwhere（Cno='C1'）

22）求各课程号及相应的选课人数。

程序代码：

selectCno,count（*）fromSCgroupbyCno

23）查询选修3门以上课程的学生学号。

程序代码：

selectCnofromSCgroupbyCnohaving（count（*）>3）

24）查询每个学生及其选修课程的情况。

程序代码：

selectSname,Sdept,SC.Sno,SC.Cno,CnamefromStudent,SC,CoursewhereStudent.Sno=SC.SnoandSC.Cno=Course.Cno

25）查询选修2号课程且成绩在90分以上的所有学生。

程序代码：

selectSnofromSCwhere（Cno='C2'andGrade>=90）

26）查询每个学生的学号，姓名，选修的课程名和成绩。

程序代码：

selectSname,Student.Sno,Cno,GradefromStudent,SCwhereStudent.Sno=SC.Sno

27）查询所有选修了1号课程的学生姓名。

程序代码：

方法一：

selectSnamefromStudentwhere（Sno=any（selectSnofromSCwhereCno='C1'））

方法二：

selectSname,SC.Sno,CnofromStudent,SCwhereStudent.Sno=SC.SnoandCno='C1'

28）查询选修了课程名为“数据库”的学生的学号和姓名。

程序代码：

selectSname,SC.Sno,Cnamefromstudent,SC,Course

whereStudent.Sno=SC.SnoandCourse.Cno=SC.CnoandCname='数据库'

三、SQL调试及结果

1）程序代码：

selectSno,SnamefromStudent

运行结果：

如图4-1

图4-1

2）程序代码：

selectSno,Sname,SdeptfromStudent

运行结果：

如图4-2

图4-2

3）程序代码：

select*fromStudent

运行结果：

如图4-3

图4-3

4）程序代码：

selectSname,（2008-Sage）asyearsfromStudent

运行结果：

如图4-4

图4-4

5）程序代码：

selectSnamefromStudentwhere（Sdept='软件工程'）

运行结果：

如图4-5

图4-5

6）程序代码：

selectSname,SagefromStudentwhere（Sage<20）

运行结果：

如图4-6

图4-6

7）程序代码：

selectSno,GradefromSCwhere（Grade<60）

运行结果：

如图4-7

图4-7

8）程序代码：

selectSname,Sage,SdeptfromStudent

where（Sage>=20andSage<=23）

运行结果：

如图4-8

图4-8

9）程序代码：

selectSname,Sno,SsexfromStudent

where（Sdept<>'信息'andSdept<>'数学'andSdept<>'软件工程'）

运行结果：

如图4-9

图4-9

10）程序代码：

selectSname,Sno,SsexfromStudentwhere（Snamelike'刘%'）

运行结果：

如图4-10

图4-10

11）程序代码：

selectSnamefromStudentwhere（Snamelike'欧阳_'）

运行结果：

如图4-11

图4-11

12）程序代码：

selectSnamefromStudentwhere（Snamelike'_阳%'）

运行结果：

如图4-12

图4-12

13）程序代码：

selectSname,Sno,SsexfromStudentwhere（Snamenotlike'刘%'）

运行结果：

如图4-13

图4-13

14）程序代码：

selectSno,CnofromSCwhere（Gradeisnull）

运行结果：

如图4-14

图4-14

15）程序代码：

selectSname,SagefromStudent

where（Sdept='软件工程'andSage<20）

运行结果：

如图4-15

图4-15

16）程序代码：

selectSno,GradefromSC

where（Cno='C3'）orderbyGradedesc

selectSno,GradefromSCwhere（Cno='C3'）

orderbyGradedesc

运行结果：

如图4-16

图4-16

17）程序代码：

select*fromStudentorderbySdeptasc,Sagedesc

运行结果：

如图4-17

图4-17

18）程序代码：

selectcount（Sname）asStuAllfromStudent

运行结果：

如图4-18

图4-18

19）程序代码：

selectcount（distinctSno）fromSCwhere（Gradeisnotnull）

运行结果：

如图4-19

图4-19

20）程序代码：

selectavg（Grade）asCavgfromSCwhere（Cno='C1'）

运行结果：

如图4-20

图4-20

21）程序代码：

selectmax（Grade）ashighestfromSCwhere（Cno='C1'）

运行结果：

如图4-21

图4-21

22）程序代码：

selectCno,count（*）fromSCgroupbyCno

运行结果：

如图4-22

图4-22

23）程序代码：

selectCnofromSCgroupbyCnohaving（count（*）>3）

运行结果：

如图4-23

如图4-23

24）程序代码：

selectSname,Sdept,SC.Sno,SC.Cno,CnamefromStudent,SC,CoursewhereStudent.Sno=SC.SnoandSC.Cno=Course.Cno

运行结果：

如图4-24

图4-24

25）程序代码：

selectSnofromSCwhere（Cno='C2'andGrade>=90）

运行结果：

如图4-25

图4-25

26）程序代码：

selectSname,Student.Sno,Cno,GradefromStudent,SC

whereStudent.Sno=SC.Sno

运行结果：

如图4-26

图4-26

27）方法一

程序代码：

selectSnamefromStudent

where（Sno=any（selectSnofromSCwhereCno='C1'））

运行结果：

如图4-27

图4-27

方法二

程序代码：

selectSname,SC.Sno,CnofromStudent,SC

whereStudent.Sno=SC.SnoandCno='C1'

运行结果：

如图4-28

图4-28

28）程序代码：

selectSname,SC.Sno,Cnamefromstudent,SC,Course

whereStudent.Sno=SC.SnoandCourse.Cno=SC.Cno

andCname='数据库'

运行结果：

如图4-29

图4-29

四、实验体会

1）通过这次的上机实习，我学会了运用select语句来查询数据的各种命令以及语法。

2）Select*from<数据源>

3）Where<逐行条件>

4）Groupby<按照列分组>

5）Having<分组以条件过滤>

6）Orderby<排序DESC为降序排列，ASC为升序排列>

7）Distinct可以消去重复行

8）Count（*）可以用来统计元组个数

9）……

10）上述的都是在实际查询过程中经常需要用到的命令

11）此外，查询多个表中的数据一起来组合，我们通常会用到连接。

表之间满足一定条件的行进行里连接，where后面跟着连接的条件。

也可以运用关键字Join进行连接，当将Join关键字放于from子句中时，应有关键词on与之相对应，以表明连接的条件。

实验三视图的创建和使用

一、实验目的与要求

1）理解视图的概念

2）掌握利用企业管理器和CREATEVIEW命令创建视图方法。

3）熟悉修改视图、查看视图和删除视图的方法。

4）掌握通过视图修改数据表的方法

二、实验过程设计及实验步骤

创建视图是数据库应用中的常见需求，可以使用企业管理器创建、管理视图，也可以用T-SQL语句创建、管理视图。

1）在企业管理器中创建如下视图：

在sales数据库中使用表Categories和Products创建视图view_cate_prod，来查询每种类型的产品的总库存。

2）在查询分析器中创建视图：

将上题用createview来创建，视图名为view2

程序代码：

USESales

GO

CREATEVIEWview2

AS

SELECTProductName，Products.CategoryID，Description，Quantity

FROMProductsJOINCategories

ONProducts.CategoryID=Categories.CategoryID

3）修改视图，并加密：

将视图view2加密。

程序代码：

ALTERVIEWview2

WITHENCRYPTION

AS

SELECTProductName，Products.CategoryID，Description，Quantity

FROMProductsJOINCategories

ONProducts.CategoryID=Categories.CategoryID

4）查看视图信息：

使用系统存储过程sp_help、sp_helptext、sp_depends

5）通过视图修改数据：

修改产品编号为P02006的产品的名称（ProductName）及产品种类描述（Descript