实验1-7数据库.doc

实验一 熟悉SQL SERVER的环境
（ 验证型实验 2学时）
1.目的要求：
了解SQL Server management studio的使用
2.实验内容：
回答下面每一个问题,写出实验步骤
1) 在 “已注册服务器窗口”中注册sql server数据库服务器

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

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

4) 在查询窗口中里创建名为S_C的数据库
点击左上角的新建查询，在查询窗口输入 create database s_c的命令，点击执行键。

5) 在查询窗口中使用sql语言创建名字为course的基本表
create table course(cno char(4),cname char(40),cpno char(4),ccredit smallint)；

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(20) unique,
ssex char(2),
sage smallint,
adept char(20)
);
create table course
(cno char(4) primary key,
cname char(40),
cpno char(4),
credit smallint,
foreign key (cpno) references course(cno)
);
create table sc
(sno char(9),
cno char(4),
grade smallint,
primary key (sno,cno),
foreign key (sno)references student (sno),
foreign key (cno) references course (cno));
2) 使用Insert语句向这四张表格里添加至少10条记录(数据如教材56页所示),如果出现错误,分析错误原因

insert into student (sno,sname,ssex,sage,sdept)
values('95001','李勇','男','20','cs');
insert into student (sno,sname,ssex,sage,sdept)
values('95002','刘晨','女','19','is');
insert into student (sno,sname,ssex,sage,sdept)
values('95003','王敏','女','18','ma');
insert into student (sno,sname,ssex,sage,sdept)
values('95004','张立','男','19','is');
insert into student (sno,sname,ssex,sage,sdept)
values('95005','王晶','女','222','ma');
insert into student (sno,sname,ssex,sage,sdept)
values('95006','风雨','男','22','is');
insert into student (sno,sname,ssex,sage,sdept)
values('95007','曹珊','女','19','ma');
insert into student (sno,sname,ssex,sage,sdept)
values('95008','彭佳明','男','23','is');
insert into student (sno,sname,ssex,sage,sdept)
values('95009','赵瑞','女','23','ma');
insert into student (sno,sname,ssex,sage,sdept)
values('950010','程荣亮','男','22','is');
有错误，十号排在了1号的后面。

3) 在“对象资源管理器”中实现(1)题中的三张表
实现步骤：
在对象资源管理器中选择数据库——右击——新建数据库——输入数据库名称——确定。
在数据库下选择“表”——右击——新建表——输入列名以及数据类型。

4) 在“对象资源管理器”中向这(1)题中的三张表添加至少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 s add stratus int;

2) 向表中添加属性列city,数据类型为varchar(20),并限定其取值范围为“上海”，“北京”，“天津”之一
alter table s
add city varchar(20)
check (city in ('上海','北京', '天津'));

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

alter table s alter column status smallint;
4) 删除（3）题中取值范围的约束

5) 删除属性列status
alter table s drop column status;

如教材85页所示，建立student, sc, course等表,并插入若干数据，完成如下操作：
1) 列出没有成绩的学生的学号和课程号

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

select sno
from sc
where cno=2 and grade between 70 and 80;

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

select sname,ssex,sdept
from student
where sno LIKE '2005%';

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

select *
from student
where sdept='计科'and sno LIKE '2004%';

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

select sname,ssex
from student
where sdept='计科'and
(sno LIKE '2006___3%'or sno like'2006___4%');

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

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

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

update course
set Cname='c语言'
where Cname='pascal' ;

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

update course
set credit=credit+1;

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

11) 删除“IS”系的所有学生信息
delete
from Student
where sdept='is';
12) 删除所有的课程信息
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) 输出所有员工的姓名和工资

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

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

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

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

6) 查询公司的员工数

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

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

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

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

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

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

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

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

7) 输出雇员“刘丽”工作的部门的名字
用嵌套查询完成如下操作：
1) 查询工资最高的雇员的名字

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

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

4) 找出薪水在20 000以上，并且在电视部门或者玩具部门工作的雇员的名字
select ename
from Emp
where salary>20000 and eid in (select eid from Dept,Works
where Works.did=Dept.did and Works.eid=Emp.eid and (dname='电视部' or dname='玩具部'));
5) 输出与刘丽在同一层工作的雇员的名字

6) 输出比所在部门的经理挣的还要多的雇员的名字
7) 输出满足如下条件的各个部门的名字：经理的姓为张，同时他的薪水既不是本部门最高也不是最低
8) 输出比“玩具”部门所有职工工资都高的雇员的姓名

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

10) 找出所有有职工的部门的名字和楼层号
11) 查询所有没有职工的部门编号和名字
12) 输出同时在玩具部门和糖果部门工作的雇员的名字和薪水
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));
给sc 表里的char(5)前加上cno

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

alter table student ADD CONSTRAINT sdept__l
check(sdept in('计算机科学学院','数软学院','电子工程学院','化学与材料科学学院'));

2) 在student表中sage有默认值18
alter table student add constraint sage_1 default 18 for sage;

3) 为student表建立主键
alter table student
add constraint c0 primary key(sno);

4) 为course表建立主键和外键，其中外键约束名为C_FK_CPNO
alter table course
add constraint c3 primary key(cno);
alter table course
add constraint C_FK_CPNO foreign key(cpno) references course(cno);
5) 为course表建立检查约束，限定credit的取值只能取3，2，4，5；
alter table course
add constraint c1
check(credit in('3','2','4','5'));

6) 为course表建立唯一约束，确保每们课程名字唯一
alter table course
add constraint c2 unique(cname);

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

add constraint pk_scno primary key(sno,cno) ,fk_sno char(9)

foreign key(sno)references student(sno) ,fk_cno char(4)

foreign key(cno)references course(cno);;

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

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

（2）使用系统存储过程， sp_help, sp_helpconstraint等对约束进行查询和管理
查阅联机帮助文档，选中“索引”选项卡，分别输入sp_help, sp_helpconstraint，阅读其帮助信息。
使SC数据库成为当前数据库，执行如下命令，简要解释执行结果
1) Exec sp_help
sp_help 过程仅在当前数据库中查找对象。

如果未指定 name，则 sp_help 将列出当前数据库中所有对象的对象名称、所有者和对象类型。
2) Exec sp_help student
列出student中所有对象的对象名称、所有者和对象类型。
3) Exec sp_help course
列出course中所有对象的对象名称、所有者和对象类型。
4) Exec sp_helpconstaint sc

5) Exec sp_helpconstraint student
6) 对存储过程sp_help和sp_helpconstraint进行总结，简要解释其用途
sp_help：报告有关数据库对象（sys.sysobjects 兼容视图中列出的所有对象）、用户定义数据类型或 SQL Server 2005 提供的数据类型的信息。

sp_helpconstraint：返回一个列表，其内容包括所有约束类型、约束类型的用户定义或系统提供的名称、定义约束类型时用到的列，以及定义约束的表达式（仅适用于 DEFAULT 和 CHECK 约束）。
（3）修改约束：关闭和打开某个约束。
1) 在course表中插入元组（4,数据结构，6，4），能够插入吗？为什么？
不能插入
2) 禁用约束C_FK_CPNO,再次插入元组（4,数据结构，6，4），能够插入吗？为什么？
可以插入
3) 重新启用约束C_FK_CPNO

alter table course

check constraint FK__course__C_FK_CPN__0F975522
3. 主要仪器设备及软件：
（1）PC
（2）Microsoft SQL Server 2005

实验六 Sql Server数据库安全管理
（ 验证型实验 4学时）

1. 目的要求：
建立用户，为用户赋权限，收回权限，建立角色，给用户赋角色
2. 实验内容
分别在对象资源管理器和查询窗口中完成下列操作
1) 创建登录帐号john,并使其成为固定服务器角色dbcreator的一个成员
create login john with password='123456'

exec sp_addsrvrolemember 'john', 'dbcreator'

2) 授予john服务器权限：alter any login，create any database
grant alter any login to john
grant create any database to john

3) John可以创建登录帐号吗？为什么？，若能，创建安全登录帐号帐户Mary
create login Mary with password='123456'

4) 创建用户Mary123,使用安全登录帐号Mary
create user Mary123 for login Mary

5) 授予帐户Mary123查询和修改student表的权限
grant select,alter
on student
to Mary123

6) 创建角色teachers,拒绝teachers修改student表
create role teachers

deny alter
on student
to teachers

7) 使帐户Mary123成为teachers的一个成员，Mary123能够查询和修改student表吗？为什么？
exec sp_addrolemember 'teachers', 'Mary123'
--能

8) 如果希望角色teachers能够修改student表，应该怎么做？
revoke alter
on student
from teachers

grant alter
on student
to teachers

9) 授予帐户Mary123在数据库sc中创建表和创建视图的权限
grant create table,create view
to Mary123

10) 若mary123分别是固定数据库角色db_accessadmin,db_datareader和db_denydatawriter的成员，它有何能力，解释原因。
--db_datareader和db_denydatawriter的成员，它有何能力，解释原因。
exec sp_addrolemember 'db_accessadmin' ,'mary123'
--已授予：ALTER ANY USER、CREATE SCHEMA 已授予：VIEW ANY DATABASE
exec sp_addrolemember 'db_datareader' ,'mary123'
--已授予：SELECT 已授予：VIEW ANY DATABASE
exec sp_addrolemember'db_denydatawriter' ,'mary123'
--已拒绝：DELETE、INSERT、UPDATE

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）
30 / 30