数据库实验内容--答案

1、实验内容：实验一：数据库的操作使用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:smlo

2、g.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 sm4.删除数据库“SM”。 drop database sm 实验二：创建表1. 在数据库SM

3、中创建学生表student,课程表course,选课表scstudent(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 c

4、har(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) con

5、straint 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 stu

6、dent(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 cna

7、me char(40) not null2.用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) nu

8、ll5.删除表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 wher

9、e 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，查询

10、有多少名学生的物理课成绩不及格； select count(sno) from sc,course where o=o and cname='物理' and score<607，求女学生的学生总数； 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 s

11、al asc10，求每个班的学生数 student(sid,sno,clno,sname,ssex,sage,sbir) select count(sno) from student group by clno11，查询每个学生已获得的学分（成绩及格即得相应课程的学分）。 select sno,sum(ccredit) from sc,course where score>60 and o=o group by sno 实验五 复杂查询(自连接和外连接不考）1. 查询体育课成绩不及格的男生名单 select sname from student,course,sc where stude

12、nt.sno=sc.sno and o=o and cname='体育' and ssex='男' and score<602. 将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 w

13、here 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.查询没有选修数据库课程

14、的学生的信息 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 st

15、udent where clno='04001'3.将学生的学号和平均成绩建立一个视图 create view v2(学号，平均成绩) as select sno,avg(score) from sc group by sno4. 建立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添加一个学生信息20

16、1320180309 刘可 女 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='

17、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。

18、grant create table to yy4. 把查询表s和修改学生编号sno的权限授予给户yy。 grant select,update(sno) on s to yy5. 收回用户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(c

19、name 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数据库

20、表teacher的sal列上。最后对表teacher执行插入语句，查看执行结果。 use sm create rule rule1 as x>0 exec sp_bindrule 'rule1','teacher.sal' 实验九 存储过程1.在学生表中，根据姓名查询并显示该同学的信息，否则显示“查无此人”（使用ifelse语句） create proc p2(sname varchar(20) -创建存储过程 as if exists( select * from student where sname=sname) select * from stud

21、ent 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 in

22、t,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 

23、9;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>

24、=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