数据库实验内容-答案

实验内容：
实验一：数据库的操作
使用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 o=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 o=o
group by sno
实验五复杂查询(自连接和外连接不考）
1. 查询体育课成绩不及格的男生名单
select sname
from student,course,sc
where student.sno=sc.sno and o=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<all(select sage from student where
clno='04001')
6.查询没有选修数据库课程的学生的信息
select * from student
where sno not in( select sno
from sc,course
where o=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 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)
备注：查找的具体数据可以根据你所创建的具体表做相应调整-----精心整理，希望对您有所帮助!。