实验一到实验七.docx

实验一到实验七.docx

《实验一到实验七.docx》由会员分享，可在线阅读，更多相关《实验一到实验七.docx（17页珍藏版）》请在冰豆网上搜索。

实验一到实验七

实验一熟悉SQLSERVER的环境

（验证型实验2学时）

1.目的要求：

了解SQLServermanagementstudio的使用

2.实验内容：

回答下面每一个问题,写出实验步骤

1）在“已注册服务器窗口”中注册sqlserver数据库服务器

单击视图，在视图中选择，已注册服务器，打开已注册服务器界面后，单击右键选择注册新的sqlsever服务器。

2）在“对象资源管理器”中创建名字为sc的数据库

单击对象资源管理器中的，数据库标签，选择，新建数据库，然后建立名为sc的数据库

3）在sc数据库中创建一个名字为student的基本表

右击数据库sc，选择表，单击表，然后选择，新建表，并且给所新建的表，命名为student

4）在查询窗口中里创建名为S_C的数据库

CREATEDATABASE

ON

（

NAME=sc1,

FILENAME=’E:

\mydb\sc1.MDF,

SIZE=5,

MAXSIZE=10,

SIZEGROWTH=10%

）

5）在查询窗口中使用sql语言创建名字为course的基本表

CREATETABLECourse

（

CnoCHAR（4）PRIMARYKEY,

CnameCHAR（40）,

CpnoCHAR（4）,

CcreditSMALLINT

6））

3.主要仪器设备及软件：

（1）PC

（2）MicrosoftSQLServer2005

实验二建立表格，并插入若干记录

（验证型实验2学时）

1.目的要求：

学会使用CreateTable语句和Insert语句

2.实验内容：

1）使用sql语言建立student,course和sc共三张表格（包括主键,外码的指定）,

CREATETABLEStudent（

Snochar（9）primarykey,

Snamechar（40）,

Ssexchar

（2）,

Sagesmallint,

Sdepe[char]（20）,

）;

CREATETABLECourse

（

CnoCHAR（4）PRIMARYKEY,

CnameCHAR（40）,

CpnoCHAR（4）,

CcreditSMALLINT

）;

CREATETABLESC

（

SnoCHAR（9）,

CnoCHAR（4）,

GraditSMALLINT,

PRIMARYKEY（Sno,Cno）,

FOREIGNKEY（Sno）REFERENCESStudent（Sno）,

FOREIGNKEY（Cno）REFERENCESCourse（Cno）,

）;

2）

3）分析具体情况适当给出一些用户自定义的约束.

4）使用Insert语句向这四张表格里添加至少10条记录（数据如教材56页所示）,如果出现错误,分析错误原因

INSERTINTO[student].[dbo].[st]

（[Sno]

[Sname]

[Ssex]

[Sage]）

VALUES

（'1',

'lili',

'1',

'2'）

在这里我只写了在一个表中加入一个记录的语句，而其他表中的，插入数据，语句，和上述语句相同。

5）在“对象资源管理器”中实现

（1）题中的三张表

6）单击资源对象管理器,选择资源对象管理器中的数据库,,然后单击数据库,选择新建数据库,在对话框中填写数据库的名称,便可以建立一个数据库,然后建立表的算法,和1,2题的算法一样算法如下:

CREATETABLEStudent（

Snochar（9）primarykey,

Snamechar（40）,

Ssexchar

（2）,

Sagesmallint,

Sdepe[char]（20）,

）;

CREATETABLECourse

（

CnoCHAR（4）PRIMARYKEY,

CnameCHAR（40）,

CpnoCHAR（4）,

CcreditSMALLINT

）;

CREATETABLESC

（

SnoCHAR（9）,

CnoCHAR（4）,

GraditSMALLINT,

PRIMARYKEY（Sno,Cno）,

FOREIGNKEY（Sno）REFERENCESStudent（Sno）,

FOREIGNKEY（Cno）REFERENCESCourse（Cno）,

）;

7）

8）在“对象资源管理器”中向这

（1）题中的三张表添加至少10条记录（数据如教材

单击对象资源管理器下的数据库,然后再将数据库展开,寻找表,单击右键选择,修改表,insert到新建查询窗口,便可以对表进行插入数据,其插入数据的格式为,

INSERTINTO[sc1].[dbo].[Course]

（[Cno]

[Cname]

[Cpno]

[Ccredit]）

VALUES

（'7','数据库','1',2）

9）这里我只对一张表进行了修改,插入了一个内容,其余表的插入内容的方法与上面的5

10）56页所示）,如果出现错误,分析错误原因

3.主要仪器设备及软件：

（1）PC

（2）MicrosoftSQLServer2005

实验三修改表格结构，修改和删除表格中的数据

（验证型实验4学时）

1.目的要求：

用ALTER语句修改表结构：

添加列，修改列定义，删除列。

使用UPDATE和DELETE语句修改和删除Student,sc,course表格中的数据。

2.实验内容：

如下所示，创建表s，并完成随后的操作

Createtables（snochar

（2）primarykey,snamechar（10））;

1）向表中添加属性列status,数据类型为int

altertablestudentaddstatusint

2）向表中添加属性列city,数据类型为varchar（20）,并限定其取值范围为“上海”，“北京”，“天津”之一

3）

4）修改属性列status的数据类型为smallint

5）altertablestudentaltercolumnstatussmallint

6）删除（3）题中取值范围的约束

7）

8）删除属性列status

altertablestudentdropcolumnstatus

如教材85页所示，建立student,sc,course等表,并插入若干数据，完成如下操作：

1）列出没有成绩的学生的学号和课程号

select*fromscwheregrade=0

2）列出2号课程成绩在70分到80分学生的学号

select*fromscwherecno='2'andgrade>=70andgrade<=80

3）查询所有2005级的学生的姓名，性别和所在系

4）selectSname,Ssex,SdepefromStudentwhereSsyear=2005;

5）

6）查询计科系2004级全体学生的所有信息

select*fromstudentwheressyear=2004andsdepe='cs'

7）查询计科系2006级3班和4班学生的姓名和性别

selectSname,SsexfromStudentwhereSUBSTRING（Sno,1,6）='200603'orSUBSTRING（Sno,1,6）='200604'

8）查询所有以“数”打头的课程的名称和学分

selectCname,CcreditfromCoursewhereCnamelike'数%'

9）查询数学系所有学生的姓名，性别和出生年份

selectSname,ssex,SagefromStudentwhereSdepe='ma'

由于建表时没有建立出身年月,所以我将出生年月改成了,学生的实际年龄

将course表中名为PASCAL语言的课程更名为“C语言”

updateCoursesetCname='C语言'whereCname='PASCALL'

10）将所有课程的学分增加1分

updateCoursesetCcredit=Ccredit+1

11）删除没有选课成绩的选课记录

deletefromscwherecnoisnull

12）删除“IS”系的所有学生信息

deletefromStudentwhereSdepe='IS';

13）删除所有的课程信息

Sp_constraintsc

altertablescdropconstraint

Deletefromcourse

3.主要仪器设备及软件：

（1）PC

（2）MicrosoftSQLServer2005

实验四查询（多表查询，嵌套查询，分组查询）

（验证型实验12学时）

1.目的要求：

实现单表和多表的普通查询和嵌套查询。

包括返回单值的子查询和返回多值的子查询。

使用5个聚合函数以及GROUPBY子句和HAVING子句实现分组查询.

2.实验内容

有如下关系模式，分析每个关系模式的主码，外码，完成后面的查询

职员表：

Emp（eid:

integer;ename:

string,salary:

real）

部门表：

Dept（did:

integer,dname:

string,managerid:

integer,floornum:

integer）

职员与部分的关系表：

Works（eid:

integer,did:

integer）；

Works表表示：

一个职员可以在多个部门工作，一个部门有多个职员

Dept表中managerid可以取值null，表示尚未任命部门经理，floornum可以取值null，表示尚未分配工作地点

用单表查询完成如下操作：

1）输出所有员工的姓名和工资

selectename,salaryfromemp

2）输出薪水少于10000或者大于100000的雇员的名字

3）selectenamefromempwheresalary<10000orsalary>100000

4）输出所有姓“欧阳”，且全名为四个字的雇员的姓名和工资

selectename,salaryfromempwhereenamelike'欧阳__'

5）输出薪水在20000和50000之间的雇员的名字

selectenamefromempwheresalarybetween20000and50000

6）输出部门名字中含有“_”的所有部门的名字和楼层号

selectdname,floornumfromdeptwherednamelike'%\_%'escape'\'

7）查询公司的员工数

selectcount（*）fromemp

8）查询所有还没有部门经理的部门的名字和编号

selectdid,dnamefromdeptwheremanageridisnull

9）

10）查询所有已分配楼层的部门的所有信息

select*fromdeptwherefloornumisnotnull

用连接查询完成如下操作：

1）查询“电视”部门的职工人数

selectCOUNT（*）astotalfromdept,workswheredept.did=works.didanddname='电视'

2）输出每个部门的名字和平均工资

selectdname,AVG（salary）fromdept,emp,workswhereworks.eid=emp.eidandworks.did=dept.didgroupbydname

3）查询每个部门的部门编号，及其拥有的雇员的人数

selectdid,COUNT（eid）fromworksgroupbydid

由于在works表中只输入了四个部门的信息，所以，输出结果中，也只有四个部门的信息

4）查询在第10层工作，同时薪水少于￥50000的所有雇员的名字

5）selectenamefromemp,dept,workswhereworks.eid=emp.eidandworks.did=dept.didandfloornum='10'andsalary<50000

6）输出同时管理三个或者更多部门的管理者的名字

7）输出管理在同一层上10个以上部门的所有管理者的名字

8）输出雇员“李丽丽”工作的部门的名字

selectdnamefromemp,dept,workswheredept.did=works.didandemp.eid=works.eidandename='李丽丽'

9）

用嵌套查询完成如下操作：

1）查询工资最高的雇员的名字

selectenamefromempwheresalary>=（selectMAX（salary）fromemp）

2）查询工资最低的雇员的名字及其所在部门的编号和名字

selectename,works.did,dnamefromemp,dept,workswheresalary<=（selectMIN（salary）fromemp）andworks.eid=emp.eidanddept.did=works.did

3）输出与Santa工作部门相同的所有雇员的所有信息

selectename,eid,salaryfromempwhereeidin（selecteidfromworkswheredidin（selectdidfromworkswhereeid=（selecteidfromempwhereename='santa'）））

4）找出薪水在20000以上，并且在电视部门或者玩具部门工作的雇员的名字

selectenamefromempwhereexists（selectdidfromdeptwherednamein（'电视部','玩具部'）andemp.salary>=20000）

5）输出与刘丽在同一层工作的雇员的名字

selectenamefromempwhereexists（selectfloornumfromdeptwheredidin（selectdidfromworkswhereeidin（selecteidfromempwhereename='李丽丽'）））

6）输出比所在部门的经理挣的还要多的雇员的名字

selectenamefromempwheresalary>（selectsalaryfromempwhereeidin（selectmanageridfromdeptwheremanageridisnotnull））

7）输出满足如下条件的各个部门的名字：

经理的姓为张，同时他的薪水既不是本部门最高也不是最低

selectdnamefromdept,emp,workswheremanageridin（selectmanageridfromdeptwheremanageridin（selecteidfromempwhereenamelike'张%'）andsalary>（selectmin（salary）fromemp）andsalary<（selectMAX（salary）fromemp））

8）输出比“玩具”部门所有职工工资都高的雇员的姓名

selectenamefromempwheresalary>（selectall（salary）fromempwhereeidin（selecteidfromworkswheredidin（selectdidfromdeptwheredname='玩具部'）））

9）

10）输出比“电视”部门职工平均工资高的雇员的姓名

selectenamefromempwheresalary>（selectAVG（salary）fromempwhereeidin（selecteidfromworkswheredidin（selectdidfromdeptwheredname='电视'）））

11）找出所有有职工的部门的名字和楼层号

selectdname,floornumfromdeptwheredidin（selectdidfromworkswhereeidisnotnull）

12）查询所有没有职工的部门编号和名字

selectdid,dnamefromdeptwheredidin（selectdidfromworkswhereeidisnull）

13）输出同时在玩具部门和糖果部门工作的雇员的名字和薪水

14）selectename,salaryfromempwhereeidin（selecteidfromworkswheredidin（selectdidfromdeptwherednamein（'糖果部','玩具'）））

3.主要仪器设备及软件：

（1）PC

（2）MicrosoftSQLServer2005

实验五为表格建立约束，修改约束和查询约束

（验证型实验4学时）

1.目的要求：

使用ALTER语句和CREATE语句建立、修改、删除和查询约束

2.实验内容

执行以下SQL语句，完成随后的操作，若有错误，分析错误原因并改正错误：

Createtablestudent（snochar（9）notnull,snamechar（10）,ssexchar

（2）,sagetinyint,sdeptvarchar（40））;

Createtablecourse（cnochar（4）notnull,cnamevarchar（30）,cpnochar（4）,credittinyint）;

Createtablesc（snochar（9）,char（5）,gradenumeric（3,1））;

执行第三条时，出现了错误。

原因是数据类型char（5）前面，没有属性名，加上属性名后就可以成功执行

1）在student表中，使sdept只能取值“计算机科学学院”，“数软学院”，“电子工程学院”，“化学与材料科学学院”

altertablestudentaddconstraintacheck（sdeptin（'计算机科学学院','数软学院','电子工程学院','化学与材料科学学院'））

2）在student表中sage有默认值18

altertablestudentdropcolumnsage

altertablestudentaddsagevarchar（10）default（18）

3）为student表建立主键

altertablestudent1addconstraintstudentkeyprimarykey（sno）

4）为course表建立主键和外键，其中外键约束名为C_FK_CPNO

altertablecourse1addconstraintcoursekeyprimarykey（cno）

altertablecourse1addconstraintC_FK_CPNOforeignkey（cpno）referencescourse1（cno）

5）为course表建立检查约束，限定credit的取值只能取3，2，4，5；

altertablecourse1dropcolumncredit

altertablecourse1addcreditsmallintconstraintC1check（creditin（2,3,4,5））

6）为course表建立唯一约束，确保每们课程名字唯一

altertablecourse1dropcolumncname

altertablecourse1addcnamechar（20）unique

7）为sc表建立主键和外键，并给出相应的约束名

8）在course表中插入元组（1,数据库，5，4）和（2,数学，null,2）,若不能正确插入，分析原因，给出解决办法

第一个不能正确插入,因为第一个的先选课程是5号课程,但是课程表中根本就没有5号课程的信息,所以无法将5号课程作为先选课插入到选课表中.解决办法是:

先把1号课程的先选课设置为空,等到5号课程的记录插入了以后,再修改一号课程的先选课程的信息,就可以了,由于,一号课程没有插入,所以2号课程没法插入到课程表中,要想插入二号课程,就必须先插入一号课程的信息

9）

10）在sc表中插入元组（95001,1,92）和（95001，3，88），若不能正确插入，分析原因，给出解决办法

不能插入,因为,sc的外码是课程号,而

（2）使用系统存储过程，sp_help,sp_helpconstraint等对约束进行查询和管理

查阅联机帮助文档，选中“索引”选项卡，分别输入sp_help,sp_helpconstraint，阅读其帮助信息。

使SC数据库成为当前数据库，执行如下命令，简要解释执行结果

1）Execsp_help

2）Execsp_helpstudent

3）Execsp_helpcourse

4）Execsp_helpconstaintsc

5）Execsp_helpconstraintstudent

6）对存储过程sp_help和sp_helpconstraint进行总结，简要解释其用途

（3）修改约束：

关闭和打开某个约束。

1）在course表中插入元组（4,数据结构，6，4），能够插入吗？

为什么？

2）禁用约束C_FK_CPNO,再次插入元组（4,数据结构，6，4），能够插入吗？

为什么？

3）重新启用约束C_FK_CPNO

3.主要仪器设备及软件：

（1）PC

（2）MicrosoftSQLServer2005

实验六SqlServer数据库安全管理

（验证型实验4学时）

1.目的要求：

建立用户，为用户赋权限，收回权限，建立角色，给用户赋角色

2.实验内容

分别在对象资源