实验一到实验七

（ 验证型实验 2学时）

1.目的要求：

了解SQL Server management studio的使用 2.实验内容：

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

1) 在 “已注册服务器窗口”中注册sql server数据库服务器

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

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

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

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

右击数据库sc，选择表，单击表，然后选择，新建表，并且给所新建的表，命名为student 4) 在查询窗口中里创建名为S_C的数据库 CREATE DATABASE ON ( NAME=sc1,

FILENAME=’E:\\mydb\\sc1.MDF, SIZE=5, MAXSIZE=10, SIZEGROWTH=10% )

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

CREATE TABLE Course (

Cno CHAR(4)PRIMARY KEY, Cname CHAR(40), Cpno CHAR(4), Ccredit SMALLINT

6) )

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

（1）PC

（2）Microsoft SQL Server 2005

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

（ 验证型实验 2学时）

1. 目的要求：

学会使用Create Table语句和Insert语句 2. 实验内容：

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

CREATE TABLE Student(

Sno char(9 )primary key, Sname char(40), Ssex char(2), Sage smallint, Sdepe[char](20), );

CREATE TABLE Course (

Cno CHAR(4)PRIMARY KEY, Cname CHAR(40), Cpno CHAR(4), Ccredit SMALLINT );

CREATE TABLE SC (

Sno CHAR(9), Cno CHAR(4), Gradit SMALLINT,

PRIMARY KEY (Sno ,Cno),

FOREIGN KEY (Sno) REFERENCES Student(Sno), FOREIGN KEY (Cno) REFERENCES Course(Cno), );

2)

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

4) 使用Insert语句向这四张表格里添加至少10条记录(数据如教材56页所示),如

果出现错误,分析错误原因

INSERT INTO [student].[dbo].[st] ([Sno] ,[Sname] ,[Ssex] ,[Sage]) VALUES ('1', 'lili', '1',

'2')

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

5) 在“对象资源管理器”中实现(1)题中的三张表

6) 单击资源对象管理器,选择资源对象管理器中的数据库,,然后单击数据库,选择新

建数据库,在对话框中填写数据库的名称,便可以建立一个数据库,然后建立表的算法,和1,2 题的算法一样算法如下:

CREATE TABLE Student(

Sno char(9 )primary key, Sname char(40), Ssex char(2), Sage smallint, Sdepe[char](20), );

CREATE TABLE Course (

Cno CHAR(4)PRIMARY KEY, Cname CHAR(40), Cpno CHAR(4), Ccredit SMALLINT );

CREATE TABLE SC (

Sno CHAR(9), Cno CHAR(4), Gradit SMALLINT,

PRIMARY KEY (Sno ,Cno),

FOREIGN KEY (Sno) REFERENCES Student(Sno), FOREIGN KEY (Cno) REFERENCES Course(Cno), );

7)

8) 在“对象资源管理器”中向这(1)题中的三张表添加至少10条记录(数据如教材 单击对象资源管理器下的数据库,然后再将数据库展开,寻找表,单击右键选择,修改表,insert到新建查询窗口,便可以对表进行插入数据,其插入数据的格式为,

INSERT INTO [sc1].[dbo].[Course] ([Cno] ,[Cname] ,[Cpno] ,[Ccredit]) VALUES

('7','数据库','1',2)

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

5

10) 56页所示),如果出现错误,分析错误原因 3. 主要仪器设备及软件：

（1）PC

（2）Microsoft SQL Server 2005

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

（ 验证型实验 4学时）

1. 目的要求：

用ALTER语句修改表结构：添加列，修改列定义，删除列。使用UPDATE和DELETE语句修改和删除Student,sc, course表格中的数据。 2. 实验内容：

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

Create table s(sno char(2) primary key,sname char(10)); 1) 向表中添加属性列status,数据类型为int

alter table student add status int

2) 向表中添加属性列city,数据类型为varchar(20),并限定其取值范围为“上海”，

“北京”，“天津”之一 3)

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

5) alter table student alter column status smallint 6) 删除（3）题中取值范围的约束 7)

8) 删除属性列status

alter table student drop column status

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

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

select *from sc where grade=0

2) 列出2号课程成绩在70分到80分学生的学号 select *from sc where cno='2'and grade>=70and grade<=80

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

4) select Sname ,Ssex,Sdepe from Student where Ssyear=2005;

5)

6) 查询计科系2004级全体学生的所有信息 select *from student where ssyear=2004 and sdepe='cs'

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

select Sname ,Ssex from Student where SUBSTRING(Sno,1,6)='200603' or SUBSTRING(Sno,1,6)='200604'

8) 查询所有以“数”打头的课程的名称和学分 select Cname,Ccredit from Course where Cname like '数%'

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

select Sname,ssex ,Sage from Student where Sdepe='ma'

由于建表时没有建立出身年月,所以我将出生年月改成了,学生的实际年龄 将course表中名为PASCAL语言的课程更名为“C语言”

update Course set Cname='C语言' where Cname='PASCALL'

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

update Course set Ccredit=Ccredit+1

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

delete from sc where cno is null

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

delete from Student where Sdepe='IS';

13) 删除所有的课程信息 Sp_constraint sc

alter table sc drop constraint Delete from course 3. 主要仪器设备及软件：

（1）PC

（2）Microsoft SQL Server 2005

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

（ 验证型实验 12学时）

1. 目的要求：

实现单表和多表的普通查询和嵌套查询。包括返回单值的子查询和返回多值的子查询。使用5个聚合函数以及GROUP BY子句和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) 输出所有员工的姓名和工资

select ename ,salary from emp

2) 输出薪水少于10 000或者大于100 000的雇员的名字

3) select ename from emp where salary<10000 or salary>100000

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

select ename,salary from emp where ename like '欧阳_ _'

5) 输出薪水在20 000和50 000之间的雇员的名字

select ename from emp where salary between 20000 and 50000

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

select dname ,floornum from dept where dname like '%\\_%'escape'\\'

7) 查询公司的员工数

select count(*) from emp

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

select did ,dname from dept where managerid is null

9)

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

select * from dept where floornum is not null

用连接查询完成如下操作： 1) 查询“电视”部门的职工人数

select COUNT(*) as total from dept ,works where dept.did=works .did and dname='电视'

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

select dname , AVG(salary)from dept,emp ,works where works.eid=emp.eid and works.did=dept.did group by dname

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

select did ,COUNT(eid) from works group by did

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

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

5) select ename from emp ,dept,works where works.eid=emp.eid and

works.did =dept.did and floornum='10' and salary<50000

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

7) 输出管理在同一层上10个以上部门的所有管理者的名字 8) 输出雇员“李丽丽”工作的部门的名字

select dname from emp ,dept ,works where dept .did=works.did and emp.eid=works .eid and ename ='李丽丽'

9)

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

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

select ename from emp where salary>=(select MAX(salary)from emp)

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

select ename , works.did,dname from emp,dept ,works where salary <=(select MIN(salary) from emp)and works.eid=emp.eid and dept.did= works.did

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

select ename ,eid ,salary from emp where eid in (select eid from works where did in (select did from works where eid =(select eid from emp where ename ='santa') ))

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

select ename from emp where exists (select did from dept where dname in ('电视部','玩具部')and emp.salary>=20000 )

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

select ename from emp where exists (select floornum from dept where did in (select did from works where eid in (select eid from emp where ename='李丽丽')))

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

select ename from emp where salary >(select salary from emp where eid in (select managerid from dept where managerid is not null ))

7) 输出满足如下条件的各个部门的名字：经理的姓为张，同时他的薪水既不是本部门最高

也不是最低

select dname from dept,emp ,works where managerid in(select managerid from dept where managerid in (select eid from emp where ename like '张%')and salary >(select min (salary) from emp )and salary <(select MAX(salary )from emp))

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

select ename from emp where salary >( select all( salary) from emp where eid in (select eid from works where did in ( select did from dept where dname ='玩具部') ) )

9)

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

select ename from emp where salary >( select AVG(salary ) from emp where eid in (select eid from works where did in ( select did from dept where dname ='电视') ) )

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

select dname , floornum from dept where did in (select did from works where eid is not null)

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

select did,dname from dept where did in (select did from works where eid is null)

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

14) select ename ,salary from emp where eid in (select eid from works

where did in (select did from dept where dname in ('糖果部' ,'玩具')))

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

（1）PC

（2）Microsoft SQL Server 2005

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

（ 验证型实验 4学时）

1. 目的要求：

使用ALTER语句和CREATE语句建立、修改、删除和查询约束 2. 实验内容

执行以下SQL语句，完成随后的操作，若有错误，分析错误原因并改正错误： Create table student(sno char(9) not null , sname char(10),ssex char(2),sage tinyint,sdept varchar(40));

Create table course(cno char(4) not null, cname varchar(30),cpno char(4),credit tinyint);

Create table sc(sno char(9),char(5),grade numeric(3,1));

执行第三条时，出现了错误。原因是数据类型char（5）前面，没有属性名，加上属性名后就可以成功执行

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

院”，“化学与材料科学学院”

alter table student add constraint a check (sdept in('计算机科学学院','数软学院','电子工程学院','化学与材料科学学院'))

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

alter table student drop column sage

alter table student add sage varchar(10) default(18)

3) 为student表建立主键

alter table student1 add constraint studentkey primary key (sno)

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

alter table course1 add constraint coursekey primary key (cno) alter table course1 add constraint C_FK_CPNO foreign key (cpno )references course1(cno)

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

alter table course1 drop column credit

alter table course1 add credit smallint constraint C1 check (credit in(2,3,4,5))

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

alter table course1 drop column cname

alter table course1 add cname char(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) Exec sp_help 2) Exec sp_help student 3) Exec sp_help course 4) Exec sp_helpconstaint sc 5) Exec sp_helpconstraint student

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）Microsoft SQL Server 2005

实验六 Sql Server数据库安全管理

（ 验证型实验 4学时）

1. 目的要求：

建立用户，为用户赋权限，收回权限，建立角色，给用户赋角色 2. 实验内容

分别在对象资源管理器和查询窗口中完成下列操作

1) 创建登录帐号john,并使其成为固定服务器角色dbcreator的一个成员 2) 授予john服务器权限：alter any login，create any database 3) John可以创建登录帐号吗？为什么？，若能，创建安全登录帐号帐户Mary 4) 创建用户Mary123,使用安全登录帐号Mary 5) 授予帐户Mary123查询和修改student表的权限 6) 创建角色teachers,拒绝teachers修改student表

7) 使帐户Mary123成为teachers的一个成员，Mary123能够查询和修改student表

吗？为什么？

8) 如果希望角色teachers能够修改student表，应该怎么做？ 9) 授予帐户Mary123在数据库sc中创建表和创建视图的权限

10) 若mary123分别是固定数据库角色db_accessadmin,db_datareader和

db_denydatawriter的成员，它有何能力，解释原因。

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

（1）PC

（2）Microsoft SQL Server 2005

实验七 使用ADO连接数据库 （综合型实验 4学时）

1. 目的要求：

在高级语言中通过ADO或者ADO.NET连接SQL SERVER数据库，做一些简单应用 2. 实验内容

在高级语言中使用ADO控件(ADO.NET控件)或ADO对象(ADO.NET对象)连接SQL SERVER数据库，实现对表格的基本操作，能与数据库交互，包括数据查询，元组的添加，删除和修改。

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

（1）PC

（2）SQL Server数据库，高级语言（如VB）