数据库实验内容--答案

实验内容：

实验一：数据库的操作

使用Management Studio和sql语句分别完成以下操作：

1.创建一个名为“SM”的数据库，数据文件初始大小为3MB，最大为50MB，数据库自动增长，增长方式按10%；

日志文件初始大小为2MB，数据大小不受限制，按1MB增长。

create database sm

on

(

name='smdata',

filename='e:\smdata.mdf',

size=3,

maxsize=50,

filegrowth=10%)

log on

(name='smlog',

filename='e:\smlog.ldf',

size=2,

maxsize=unlimited,

filegrowth=1)

2.修改数据库“SM”，将数据文件名改成“sm_data”,初始大小改成5MB

alter database sm

modify file

( name='smdata',

newname='sm_data',

size=5)

3.分别查看数据库“SM”，该数据库中的文件和文件组。

exec sp_helpfile sm

exec sp_helpfilegroup sm

4.删除数据库“SM”。

drop database sm

实验二：创建表

1. 在数据库SM中创建学生表student,课程表course,选课表sc

student(sid,sno,clno,sname,ssex,sage,sbir)

说明：sid int identity(1,1) 序号

sno 为主关系键，为字符类型学号

clno 字符类型，班级号

sname 字符类型，并不为空

ssex 字符类型，check的值的范围为男女

sbir 日期类型出生日期

sage int；

use sm

create table student

( sid int identity(1,1),

sno char(10) constraint pk_st primary key,

clno char(10),

sname varchar(20) not null,

ssex char(2) constraint ck_ssex check(ssex in('男','女')),

sbir datetime,

sage int

)

course(cno,cname,ccredits,ctno,cpno,ctime)

说明：cno 字符类型，主关系键

cname 字符类型，唯一键

ccredits 学分，精确数值型，精确长度为2，小数位为1

ctno ，cpno 字符类型

ctime 整型

create table course

(cno char(4) constraint pk_c primary key,

cname varchar(20) constaint uk_cname unique,

ccredit decimal(2,1),

ctno char(2),

cpno char(4),

ctime tinyint

)

sc(sno,cno,score)

说明：sno+cno为主键，并且sno是student的外部键，cno是course的外部键。 score精确数值型，精确长度为4，小数位为1

create table sc

( sno char(10) constraint fk_sno foreign key references student(sno), cno char(4) constriant fk_cno foreign key references course(cno), score decimal(4,1),

constraint pk_sc primary key(sno,cno)

)

2.使用Management Studio对数据库SM中的表插入数据

实验三：表的维护

1.用sql语句修改表course的列属性,将cname的长度改为40,且不允许空

alter table course

drop constraint uk_cname

alter table course

alter column cname char(40) not null

2.用sql语句向表student中增加列email,且要求输入的电子邮件地址必须包括"@" alter table student

add email varchar(20) constraint ck_email check(email like '%@%') 3.用sql语句删除表student中的列sbir

alter table student

drop column sbir

4.删除sname列上的约束。

alter table student

alter column sname varchar(20) null

5.删除表student

drop table student

实验四：简单数据查询

在实验二的基础上，再在sm数据库中新建表teacher，包括如下数据项

teacher(tno,tname,age,sal,dno)

tno为教职工编号，tname姓名，age年龄，sal为月薪，dno为部门号

在student，course，sc，teacher四张表中进行下列查询

1，查询所有0002部门职工的信息；

select * from teacher where dno='0002'

2，查询1984年和1985年出生的女生的信息；

select * from student where year(sbir) in(1984,1985)

and ssex='女'

3，查询0001部门、0002部门或0003部门的职工信息；

select * from teacher where dno in('0001','0002','0003')

4，查询学号为03004的同学正在学习的课程；

select cno from sc where sno='03004'

5，查询不姓王或李的同学的信息。

select * from student where sname like '[^王李]%'

6，查询有多少名学生的物理课成绩不及格；

select count(sno)

from sc,course

where https://www.360docs.net/doc/9817317879.html,o=https://www.360docs.net/doc/9817317879.html,o and cname='物理' and score<60

7，求女学生的学生总数；

select count(sno)

from student

where ssex='女'

8，求职工的最高工资、最低工资和平均工资；

select max(sal),min(sal),avg(sal)

from teacher

9，查询职工的年薪，并按年薪的升序排列；

select tname,sal*12 年薪

from teacher order by sal asc

10，求每个班的学生数

student(sid,sno,clno,sname,ssex,sage,sbir)

select count(sno)

from student

group by clno

11，查询每个学生已获得的学分（成绩及格即得相应课程的学分）。

select sno,sum(ccredit)

from sc,course

where score>60 and https://www.360docs.net/doc/9817317879.html,o=https://www.360docs.net/doc/9817317879.html,o

group by sno

实验五复杂查询(自连接和外连接不考）

1. 查询体育课成绩不及格的男生名单

select sname

from student,course,sc

where student.sno=sc.sno and https://www.360docs.net/doc/9817317879.html,o=https://www.360docs.net/doc/9817317879.html,o

and cname='体育' and ssex='男' and score<60

2. 将04001班全体学生的成绩置0

sc(sno,cno,score)

student(sno,clno...)

update sc set score=0

where sno in( select sno from student where clno='04001')

3. 删除04002班全体学生的选课记录

delete from sc where sno in( select sno from student where clno='04002') 4. 查询所有选修了001号课程的学生的姓名

select sname

from student,sc

where student.sno=sc.sno and cno='001'

5. 查询其他班中比04001班所有学生年龄都小的学生，并按年龄的降序输出

select * from student

where clno<>'04001' and sage

clno='04001')

6.查询没有选修数据库课程的学生的信息

select * from student

where sno not in( select sno

from sc,course

where https://www.360docs.net/doc/9817317879.html,o=https://www.360docs.net/doc/9817317879.html,o and cname='数据库')

实验六创建视图和数据操纵

1.建立04002班学生的视图

create view v_clno

as

select * from student

where clno='04002'

2.建立04001班学生的视图，输出其学号、姓名、和年龄，并且更换列名 create view v1(学号,姓名,年龄)

as

select sno,sname,sage from student

where clno='04001'

3.将学生的学号和平均成绩建立一个视图

create view v2(学号，平均成绩)

as

select sno,avg(score)

from sc

group by sno

4. 建立04003班学生选修了0001号课程的学生的视图.

create view v3(学号，姓名)

as

select sno,sname

from student,sc

where clno='04003' and student.sno=sc.sno and cno='0001'

5. 向数据表S添加一个学生信息

201320180309 刘可女 1998-2-15 经管

insert into s (sno,sname,ssex,sbirthday,sdept)

values('201320180309','刘可','女','1998-2-15',null)

6. 将张三的C语言课程成绩改为100分

update sc set score=100

where sno=(select sno from s where sname='张三')

and cno=(select cno from c where cname='c语言')

7. 删除1001号课程的选修信息

delete from sc where cno='1001'

实验七数据安全（角色部分不考）

1.在服务器中创建test数据库的用户yy和登陆账号。

sp_addlogin 'login1','pass1'

use test

sp_adduser 'login1','yy'

2.把对表sc的插入权授予给用户yy，并允许将此权限再授予其他用户

grant insert on sc to yy with option

3. 把在test数据库建表权限授予给用户yy。

grant create table to yy

4. 把查询表s和修改学生编号sno的权限授予给户yy。

grant select,update(sno) on s to yy

5. 收回用户yy在test数据库中的建表权。

revoke create table from yy

6. yy用户拒绝对C表的查看权限

deny select on c to yy

实验八索引的创建与使用，默认与规则的创建

1. 使用sql语句在表course的cname字段上创建一个唯一性的聚集索引，索引排列顺序为降序。

create unique clustered index index1 on course(cname desc)

2. 使用sql语句在表course的credit字段上创建一个非唯一性的非聚集索引。

create nonclustered index index2 on course(credit)

3. 在sm数据库中创建一个学生性别的默认值，并将默认值捆绑到sm数据库表student的sex列上。

use sm

create default df1 as '女'

exec sp_bindefault 'df1','student.sex'

4. 在sm数据库中定义规则，使用该规则的列的值被限制为必须大于0，并将规则捆绑到sm 数据库表teacher的sal列上。

最后对表teacher执行插入语句，查看执行结果。

use sm

create rule rule1 as @x>0

exec sp_bindrule 'rule1','teacher.sal'

实验九存储过程

1.在学生表中，根据姓名查询并显示该同学的信息，否则显示“查无此人”（使用if……else 语句）

create proc p2(@sname varchar(20)) --创建存储过程

as

if exists( select * from student where sname=@sname)

select * from student where sname=@sname

else

print '查无此人'

exec p2 '刘燕' --执行存储过程

2. 查看并显示选修指定课程的人数。

create proc p3(@cno char(4))

as

declare @n int

select @n=count(sno) from sc where cno=@cno

print '有'+convert(varchar(10),@n)+'个人选修这门课'

exec p3 '1001'

3. 1+2+...+100（使用while语句）

declare @i int,@s int

set @i=1

set @s=0

while (@i<=100)

begin

set @s=@s+@i

set @i=@i+1

end

print @s

4.使用case表达式，判断课程表中ctno列的值，如果为“00”，则显示“专业基础课”；如果为“01”，则显示“公共基础课”；如果为“02”，则显示“专业课”；否则显示“待定”。 select cno 课程号, cname 课程名，课程类型=

case ctno

when '00' then '专业基础课'

when '01' then '公共基础课'

when '02' then '专业课'

else '待定'

end

from course

5.给定学号，根据该学生的平均分，评定等级，90以上‘一等',80以上‘二等',70分以上'三等',其他'补考'

create proc p4 (@sno char(12))

as

declare @avg decimal

select @avg=AVG(score) from sc

where sno=@sno

print(@sno+

case

when @avg>=90 then '一等'

when @avg>=80 then '二等'

when @avg>=70 then '三等'

else '补考'

end

exec p4 '201320180305'

实验十触发器

s(sno,sname,ssex,sbirthday)

c(cno,cname,credit,ctime,cnum)

sc(sno,cno,score)

1.不允许向数据表s添加数据

create trigger tr1

on s

for insert

as

print '任何人不得添加数据'

rollback

create trigger tr1

on s

instead of insert

as

select * from s

2.不允许修改数据表s中李四的数据

inserted 表格中保存了即将被添加的记录行,结构与原表相同 deleted 表格中保存即将被删除的记录行,结构与原表相同修改数据相当于删除旧记录添加新记录

create trigger tr2

on s

for update

as

if exists(select * from deleted where sname='李四')

rollback

3.删除学生信息的同时删除该生的选课信息

create trigger tr3

on s

for delete

as

delete from sc

where sno

in (select sno from deleted)

4.每添加一条选课记录，课程表中相应课程的选修人数加1 * create trigger tr4

on sc

for insert

as

update c set cnum=cnum+1

where https://www.360docs.net/doc/9817317879.html,o = (select cno from inserted)

5. 每当添加、删除、修改s表中数据,相应班级人数进行更新 * s(sno,sname,ssex,sbirthday,clno)

class(clno,cnum)-- 班级表（班级号，班级人数)

create trigger tr5

on s

for insert,delete,update

as

update class set cnum=cnum+1

where class.clno=(select clno from inserted)

update class set cnum=cnum-1

where class.clno=(select clno from deleted)

备注：查找的具体数据可以根据你所创建的具体表做相应调整

欢迎您的下载，

资料仅供参考！

致力为企业和个人提供合同协议，策划案计划书，学习资料等等

打造全网一站式需求